CODEWITHSUNDEEP
pythonexcelpandasdataframe
Boosted_d16
Boosted_d16

Reputation: 14112

python + pandas - dump df to excel in a loop

I have a dict with 100s of panda dfs.

I want to loop through each df in the dict and dump it into excel, all on a single sheet, one after another with 1 blank row inbetween.

My attempt:

writer = pd.ExcelWriter('output.xlsx', engine='xlsxwriter')
workbook = writer.book

for key, values in dd.iteritems():
    df = dd[key]['chart_data']
    df.to_excel(writer, sheet_name='Sheet 1', index=False)

writer.save()
workbook.close()

I think it overwrites the dfs.

Any suggestions?

Upvotes: 2

Views: 2910

Answers (1)

Charles Landau
Charles Landau

Reputation: 4275

startrow sounds like your solution:

start_row = 0
for key, values in dd.iteritems():
    df = dd[key]['chart_data']
    df.to_excel(writer, sheet_name='Sheet 1', index=False, startrow=start_row)
    # Edited to respect your requirement for 1 blank row
    # after each df
    start_row = start_row + len(df) + 1 # or df.shape[0] et cetera

It simply picks which row to start dumping into. You may also want to specify startcol, which works on the same principle, but I think this works as-is.

Edit: another, perhaps better way is to concat. Something like:

df = pd.concat([dd[key]["chart_data"] for key, values in dd.iteritems()])
df.to_excel(...)

But that would only work if your df fits in memory.

Upvotes: 5

Related Questions