1

I have created a panda data frame for the output values and saved it in an excel workbook on several sheets. For a single run, the time and speed is fine for the computation and writing the excel file. However, when I try to run this code inside for loop (at least 1000 times), the time to write the new data, in the excel file, increases. So, is there any faster way to write the new data? 

for _ in range(0,1000):
            # code
            df1 = pd.DataFrame({'': [_],'Pp': [pp], 'Qq': [qq], 'Pl': [pl], 'Ql': [ql]})
            df2 = pd.DataFrame({'MP': [i for i in mp]})
            df3 = pd.DataFrame({'MQ': [j for j in mq]})
            df4 = pd.DataFrame({'LP': [i for i in lp]})
            df5 = pd.DataFrame({'LQ': [j for j in lq]})
            writer = pd.ExcelWriter('data.xlsx', engine='openpyxl')
            writer.book = load_workbook('data.xlsx')
            writer.sheets = dict((ws.title, ws) for ws in writer.book.worksheets)
            reader = pd.read_excel(r'data.xlsx')
            df1.to_excel(writer, index=False, header=False, startrow=len(reader) + 1, sheet_name='Sheet1')
            df2.to_excel(writer, index=False, header=False, startcol=len(reader) + 1, sheet_name='Sheet2')
            df3.to_excel(writer, index=False, header=False, startcol=len(reader) + 1, sheet_name='Sheet3')
            df4.to_excel(writer, index=False, header=False, startcol=len(reader) + 1, sheet_name='Sheet4')
            df5.to_excel(writer, index=False, header=False, startcol=len(reader) + 1, sheet_name='Sheet5')
            writer.close()
AMC
  • 2,642
  • 7
  • 13
  • 35
MAAHE
  • 169
  • 6
  • 1
    maybe this can help [https://stackoverflow.com/questions/38074678/append-existing-excel-sheet-with-new-dataframe-using-python-pandas](https://stackoverflow.com/questions/38074678/append-existing-excel-sheet-with-new-dataframe-using-python-pandas) – tonino Apr 10 '20 at 16:00
  • Have you done any profiling or benchmarking? I'm not sure how much we can do with so little information. – AMC Apr 10 '20 at 17:18
  • 1
    Does this answer your question? [Append existing excel sheet with new dataframe using python pandas](https://stackoverflow.com/questions/38074678/append-existing-excel-sheet-with-new-dataframe-using-python-pandas) – AMC Apr 10 '20 at 17:18
  • THANKS! these questions aren't what I am looking for. But, your idea of appending itself helped to get what I am looking for. – MAAHE Apr 10 '20 at 18:58

1 Answers1

0

Don't add each data DF individually, collect everything by-sheet and add it in a single step (perhaps another loop which iterates over the sheets rather than the new lines).

Plausibly if you only have simple data types you may be able to simplify your logic by exporting to CSV as an intermediate step before attempting to bring it in, even if it's not quite so efficient.

ti7
  • 16,375
  • 6
  • 40
  • 68