Table of Contents

To write multiple Pandas DataFrames to separate Excel sheets, first import the Pandas library and load the desired data into separate DataFrames. Then, create a Pandas Excel writer object and specify the file name for the Excel sheet. Next, use the writer object to write each DataFrame to a separate sheet by specifying the sheet name. Finally, save the Excel file using the writer object. This process allows for easy organization and manipulation of multiple Pandas DataFrames within a single Excel file.

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