Writing multiple dataframes to multiple sheets in an Excel file

July 22, 2022

I have two data frames that I would like to each write to its own sheet in an Excel file. The following code accomplishes what I want:

import pandas as pd

df_x = pd.DataFrame({'a':[1, 2, 3]})
df_y = pd.DataFrame({'b':['a', 'b', 'c']})

writer = pd.ExcelWriter('df_comb.xlsx', engine='xlsxwriter')

df_x.to_excel(writer, sheet_name='df_x', index=False)
df_y.to_excel(writer, sheet_name='df_y', index=False)

writer.save()
writer.close()

However, in my actual use case, I have a large number of dataframes and do not want to write a to_excel statement for each. Is there anyway to loop over a list of dataframes to accomplish this, something along the lines of:

for i in [df_x, df_y]:
    i.to_excel(writer, sheet_name = i, index=False)

>Solution :

What you have is almost there, I think you’ll run into problems trying to assign the sheet_name to be the DataFrame as well. I would suggest also having a list of names that you’d like the sheets to be. You could then do something like this:

names = ["df_x", "df_y"]
dataframes = [df_x, df_y]
for i, frame in enumerate(dataframes):
   frame.to_excel(writer, sheet_name = names[i], index=False)

If you aren’t familiar with it, enumerate makes a tuple where the values are the index and the item in the list, allowing you to index another list.