List of multiple dataframes to separate Excel sheets

Question

I have a list of dataframes of variable length, and I want to convert each list of dataframes to each excel sheet.

Here is my code:

for i in range(1, len(dfs)):
    frames = {'sheetName_i' : dfs[i]}
for sheet, df in frames.items():
    dfs[i].to_excel(writer, index=False)
#df is the whole dataframe whereas dfs is the list of df

I got only the last list at my output. Is there any way I can convert all the lists to separatel Excel sheets?

I have used your suggestion as below:

for i, df in enumerate(dfs, 1):
   for n in group: #containe the names that I want to name the sheets amd the names equal to the number of sheets.
       df.to_excel(writer, index=False, sheet_name=n.format(i))

Sheetnames are changed to the names, which I need. Whereas the data of every sheet is similar, some content of data is missed and some data is merged in the single sheet and same data is repeated in every sheet. Is there any way I can get the correct output.

Pleased to hear some suggestions. Thank you very much in advance.

jezrael · Accepted Answer · 2018-11-19 11:36:01Z

I think you need:

writer = pd.ExcelWriter('output.xlsx')
for i, df in enumerate(dfs, 1):
    #python 3.6+
    df.to_excel(writer, index=False,sheet_name=f'sheetName_{i}')
    #below python 3.6   
    #df.to_excel(writer, index=False,sheet_name='sheetName_{}'.format(i))
writer.save()

Sample:

df = pd.DataFrame({
        'A':list('abcdef'),
         'B':[4,5,4,5,5,4],
         'C':[7,8,9,4,2,3],
         'D':[1,3,5,7,1,0],
         'E':[5,3,6,9,2,4],
         'F':list('aaabbb')
})

#sample list of DataFrames
dfs = [df[['A','B']], df[['A','B','C']],df[['A','F']]]

writer = pd.ExcelWriter('output.xlsx')
for i, df in enumerate(dfs, 1):
    #python 3.6+
    df.to_excel(writer, index=False,sheet_name=f'sheetName_{i}')
    #below python 3.6   
    #df.to_excel(writer, index=False,sheet_name='sheetName_{}'.format(i))
writer.save()

EDIT:

If need write custom names of sheetnames:

writer = pd.ExcelWriter('output.xlsx')
names = ['a','d','b']
for df, n in zip(dfs, names):
    #python 3.6+
    df.to_excel(writer, index=False,sheet_name=f'sheetName_{n}')
    #below python 3.6   
    #df.to_excel(writer, index=False,sheet_name='sheetName_{}'.format(n))
writer.save()

I got the syntax error at the sheet name, can you please explain me what is f at the sheet name
@Vishali - sample dfs = [pd.DataFrame(), pd.DataFrame({'a':[21, 10,20]}), pd.DataFrame()] dfs = [x for x in dfs if not x.empty]

Collectives™ on Stack Overflow

List of multiple dataframes to separate Excel sheets

1 Answer 1

21 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

21 Comments

Linked

Related