42
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:
The second DataFrame:
The third DataFrame:
Additional Resources
How to Combine Multiple Excel Sheets in Pandas
How to Read Excel Files with Pandas
How to Read CSV Files with Pandas