Failed to save list of DataFrames to multisheet Excel spreadsheet

Question

I was trying to do the same thing with the question here, and followed the method given by the answers. Here is my code:

import pandas as pd
import xlsxwriter

mylist=[df_1,df_2,df_3]

for n,df in enumerate(mylist):
    df=df[df['Name']=='Anna'].copy() # filter each dataframe in the list according to some condition
    # print(df) 
    writer = pd.ExcelWriter('data.xlsx', engine='xlsxwriter')
    df.to_excel(writer,'sheet%s' % n)
    writer.save()
    writer.close()

I was using print(df) in jupyter notebook to double check if the loop works and each dataframe is successfully filtered, and it does. My problem is, in the output excel file there's only the first filtered data frame df_1 in sheet0 has been saved, the other data frames in the list have not been saved.

What's the problem with my code?

You need to move the `pd.ExcelWriter()` and the `save()` and `close()` outside the `for` loop. — jmcnamara, Dec 13 '20 at 20:18

score 1 · Answer 1 · answered Dec 13 '20 at 04:51

1

You have some pieces inside the loop that should be outside. Reference the docs here: https://xlsxwriter.readthedocs.io/example_pandas_multiple.html

writer = pd.ExcelWriter('data.xlsx', engine='xlsxwriter')
mylist=[df_1,df_2,df_3]
for n,df in enumerate(mylist):
    df=df[df['Name']=='Anna'].copy() # filter each dataframe in the list according to some condition
    # print(df) 
    df.to_excel(writer,'sheet%s' % n)
writer.save()

answered Dec 13 '20 at 04:51

Jonathan Leon

5,440
2
6
14

Doesn't work, still only the first data frame is saved. – Anthony Dec 13 '20 at 06:28
Works for me. Is it saving all the sheets or just one? Or one sheet? Repaste the code your using please. – Jonathan Leon Dec 13 '20 at 16:07

Failed to save list of DataFrames to multisheet Excel spreadsheet

1 Answers1