CODEWITHSUNDEEP
pythonpandas
DJC
DJC

Reputation: 1611

Writing multiple dataframes to multiple sheets in an Excel file

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)

Upvotes: 7

Views: 13648

Answers (2)

Dominic
Dominic

Reputation: 513

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.

Below is a solution using a dictionary of the dataframes and looping across dictionary.items().

dataframes = {"df_x": df_x, "df_y": df_y}
for name, frame in dataframes.items():
   frame.to_excel(writer, sheet_name = name, index=False)

Upvotes: 10

John La Rooy
John La Rooy

Reputation: 304413

Just a variation of the above using zip instead of enumerate.

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

Upvotes: 1

Related Questions