CODEWITHSUNDEEP
pythonexcelpandas
ali
ali

Reputation: 151

List of multiple dataframes to separate Excel sheets

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.

Upvotes: 2

Views: 2514

Answers (1)

jezrael
jezrael

Reputation: 863431

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()

Upvotes: 6

Related Questions