Writing multiple dataframes to multiple sheets in an Excel file

Question

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)

Dominic · Accepted Answer · 2024-06-28 02:39:36Z

14

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)

edited Jun 28, 2024 at 2:39

answered Jul 22, 2022 at 17:38

Dominic

5533 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

DJC Over a year ago

This worked for me, only difference is that I stored the names/dataframes in a dictionary to keep everything into a single object, then referenced the dictiontary.items() in the loop.

Dominic Over a year ago

Honestly that makes it easier than what I wrote, good idea!

John Over a year ago

Can you post your dictionary.items() solution?

Dominic Over a year ago

@John, added to my reply

Dominic Over a year ago

@John, take a look at this link: xlsxwriter.readthedocs.io/example_pandas_positioning.html

|

John La Rooy · Accepted Answer · 2024-05-07 00:04:30Z

1

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)

answered May 7, 2024 at 0:04

John La Rooy

306k54 gold badges378 silver badges513 bronze badges

Collectives™ on Stack Overflow

Writing multiple dataframes to multiple sheets in an Excel file

2 Answers 2

9 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related