How to Write Pandas DataFrames to Multiple Excel Sheets


Often you may have multiple pandas DataFrames that you’d like to write to multiple Excel sheets within the same workbook. 

Fortunately this is fairly to do using the pandas ExcelWriter() function. In order to use this function, you first need to make sure you have xlsxwriter installed:

pip install xlsxwriter

You also need to make sure you have xlwt installed:

pip install xlwt

Once those are installed, you can easily write several pandas DataFrames to multiple Excel sheets:

import pandas as pd

#create three DataFrames
df1 = pd.DataFrame({'dataset': ['A', 'B', 'C', 'D', 'E']})
df2 = pd.DataFrame({'dataset': [13, 15, 15, 17, 22, 24, 29, 30]})
df3 = pd.DataFrame({'dataset': [3, 6, 6]})

#create a Pandas Excel writer using XlsxWriter as the engine
writer = pd.ExcelWriter('dataframes.xlsx', engine='xlsxwriter')

#write each DataFrame to a specific sheet
df1.to_excel(writer, sheet_name='first dataset')
df2.to_excel(writer, sheet_name='second dataset')
df3.to_excel(writer, sheet_name='third dataset')

#close the Pandas Excel writer and output the Excel file
writer.save()

The resulting Excel workbook will have each of the pandas DataFrames stored in a separate sheet:

The first DataFrame:

Pandas multiple DataFrames to multiple Excel sheets

The second DataFrame:

Pandas export to multiple Excel sheets

The third DataFrame:

pandas multiple Excel worksheets

Additional Resources

How to Combine Multiple Excel Sheets in Pandas
How to Read Excel Files with Pandas
How to Read CSV Files with Pandas

Leave a Reply

Your email address will not be published.