Save Multiple Sheets to One Excel in Python Pandas – Python Pandas Tutorial

By | December 5, 2022
0 Comment

It is easy to save a sheet to an excel file by python pandas. Her is the tutorial:

Python Pandas Append Data to Excel: A Step Guide – Python Pandas Tutorial

However, if we have a serial of sheets, and we plan to save them in an excel file. How to do? In this tutorial, we will discuss this topic.

How to save multiple sheets to one excel by python pandas?

We will create a function to do it.

For example:

import  pandas  as pd

#list_sheet_data={"sheet_name":{'Name':['Tom', 'Jack', 'Steve', 'Ricky'],'Age':[28,34,29,42]}}
def save_multiple_sheet_to_excel(excel_name, list_sheet_data):
    with pd.ExcelWriter(excel_name) as writer:
        for sheet_name in list_sheet_data:
            data = list_sheet_data[sheet_name]
            columns = []
            for k, v in data.items():
                columns.append(k)

            df = pd.DataFrame(data, index=None)
            df.to_excel(writer, sheet_name=sheet_name, index=False, columns=columns)

Here list_sheet_data is a python dictionary, the key is sheet name, the value is the data in this sheet.

For example:

data1 = {'Name':['Tom', 'Jack', 'Steve', 'Ricky'],'Age':[28,34,29,42]}
data2 = {'Name':['Tom', 'Jack', 'Steve', 'Ricky'],'Age':[28,34,29,42],'City':["Beijin","Shanghai","New york", "London"]}

list_sheet_data = {}
list_sheet_data["sheet1"] = data1
list_sheet_data["sheet2"] = data2

Here we will create two sheets, the names of them are sheet1 and sheet2. list_sheet_data is an python dictionary.

data1 and data2 are also dictionary, both of them contain the data in sheet1 and sheet2 separately.

Then, we can save these two sheets as follows:

excel_name = "excel_test.xlsx"
save_multiple_sheet_to_excel(excel_name, list_sheet_data)

Run this code, we will see:

Save Multiple Sheets to One Excel in Python Pandas - Python Pandas Tutorial