How to Export Pandas Dataframe to Multiple Excel Sheets

Python pandas is a powerful module that allows you to easily work with data. It allows you to import data from files, perform complex transformations and processing and export results back to files. Sometimes you may need to export pandas dataframe to multiple excel sheet. In this article, we will learn how to export pandas dataframe to multiple excel sheets.

How to Export Pandas Dataframe to Multiple Excel Sheets

To export pandas dataframe to multiple excel sheets, you need to use ExcelWriter() function available out-of-the-box in pandas library. Let us say you have multiple dataframes df1, df2, df3 then here is a simple piece of code to help you export these dataframes to separate sheets of Excel document data.xlsx located at /home/ubuntu.

from pandas import ExcelWriter
# from pandas.io.parsers import ExcelWriter

writer = ExcelWriter('/home/ubuntu/data.xlsx')
df1.to_excel(writer, 'sheet1')
df2.to_excel(writer, 'sheet2')
df3.to_excel(writer, 'sheet3')
writer.save()

In the above code, we import ExcelWriter() from pandas. We have also mentioned alternative command to import this module as a comment. It is to be used, depending on your Pandas version.

Next, we create a writer object using this function. After that, we call to_excel() function for each dataframe. It is available by default for all dataframes, to export the dataframe to excel sheet. In to_excel(), we pass writer object and sheet name where you want to export it. Finally, we call writer.save() function to save all the changes.

If you have many dataframes to be exported, you can simple save them in a list and loop through them one by one to export them. Here is an example.

from pandas import ExcelWriter
list_dfs=[df1, df2, df3..., df100]
xls_path='/home/ubuntu/data.xlsx'

with ExcelWriter(xls_path) as writer:
     for n, df in enumerate(list_dfs):
         df.to_excel(writer,'sheet%s' % n)
     writer.save()

In the above code, we create a list of all dataframes list_dfs, and use enumerate function to add a counter to our iterable list, so that it is easy for loop through it.

In each iteration, we call to_excel() function on dataframe and pass the ExcelWriter object along with the sheet name where you want to export this dataframe.

If you want to create a function to do the above tasks, here is a simple code you can use.

from pandas import ExcelWriter
list_dfs=[df1, df2, df3..., df100]
xls_path='/home/ubuntu/data.xlsx'

def save_xls(list_dfs, xls_path):
    with ExcelWriter(xls_path) as writer:
        for n, df in enumerate(list_dfs):
            df.to_excel(writer,'sheet%s' % n)
        writer.save()

save_xls(list_dfs,xls_path)

In this article, we have learnt how to export dataframes to Excel sheets in Python.

Also read:

How to Export Pandas Dataframe to Excel
How to Export Pandas Dataframe to PDF
How to Run Python Script in Apache Web Server
Shell Script to Clear/Delete Log Files
How to Exclude Requests from Apache