How to Write Pandas DataFrames to Multiple Excel Sheets?

Last Updated : 12 Jan, 2022

In this article, we will see how to export different DataFrames to different excel sheets using python.

Pandas provide a function called xlsxwriter for this purpose. ExcelWriter() is a class that allows you to write DataFrame objects into Microsoft Excel sheets. Text, numbers, strings, and formulas can all be written using ExcelWriter(). It can also be used on several worksheets.

Syntax:

pandas.ExcelWriter(path, date_format=None, mode='w')

Parameter:

path: (str) Path to xls or xlsx or ods file.
date_format: Format string for dates written into Excel files (e.g. ‘YYYY-MM-DD’). str, default None
mode: {‘w’, ‘a’}, default ‘w’. File mode to use (write or append). Append does not work with fsspec URLs.

The to_excel() method is used to export the DataFrame to the excel file. To write a single object to the excel file, we have to specify the target file name. If we want to write to multiple sheets, we need to create an ExcelWriter object with target filename and also need to specify the sheet in the file in which we have to write. The multiple sheets can also be written by specifying the unique sheet_name. It is necessary to save the changes for all the data written to the file.

Syntax:

DataFrame.to_excel(excel_writer, sheet_name='Sheet1',index=True)

Parameter:

excel_writer: path-like, file-like, or ExcelWriter object (new or existing)
sheet_name: (str, default ‘Sheet1’). Name of the sheet which will contain DataFrame.
index: (bool, default True). Write row names (index).

Create some sample data frames using pandas.DataFrame function. Now, create a writer variable and specify the path in which you wish to store the excel file and the file name, inside the pandas excelwriter function.

Example: Write Pandas dataframe to multiple excel sheets

Python3

# import the python pandas package
import pandas as pd

# create  data_frame1 by creating a dictionary
# in which values are stored as list
data_frame1 = pd.DataFrame({'Fruits': ['Appple', 'Banana', 'Mango',
                                       'Dragon Fruit', 'Musk melon', 'grapes'],
                            'Sales in kg': [20, 30, 15, 10, 50, 40]})

# create  data_frame2 by creating a dictionary 
# in which values are stored as list
data_frame2 = pd.DataFrame({'Vegetables': ['tomato', 'Onion', 'ladies finger',
                                           'beans', 'bedroot', 'carrot'],
                            'Sales in kg': [200, 310, 115, 110, 55, 45]})

# create  data_frame3 by creating a dictionary 
# in which values are stored as list
data_frame3 = pd.DataFrame({'Baked Items': ['Cakes', 'biscuits', 'muffins',
                                            'Rusk', 'puffs', 'cupcakes'],
                            'Sales in kg': [120, 130, 159, 310, 150, 140]})

print(data_frame1)
print(data_frame2)
print(data_frame3)

# create a excel writer object
with pd.ExcelWriter("path to file\filename.xlsx") as writer:
  
    # use to_excel function and specify the sheet_name and index 
    # to store the dataframe in specified sheet
    data_frame1.to_excel(writer, sheet_name="Fruits", index=False)
    data_frame2.to_excel(writer, sheet_name="Vegetables", index=False)
    data_frame3.to_excel(writer, sheet_name="Baked Items", index=False)

Output:

The output showing the excel file with different sheets got saved in the specified location.

Example 2: Another method to store the dataframe in an existing excel file using excelwriter is shown below,

Create dataframe(s) and Append them to the existing excel file shown above using mode= 'a' (meaning append) in the excelwriter function. Using mode 'a' will add the new sheet as the last sheet in the existing excel file.

Python3

# import the python pandas package
import pandas as pd

# create  data_frame1 by creating a dictionary
# in which values are stored as list
data_frame1 = pd.DataFrame({'Fruits': ['Appple', 'Banana', 'Mango',
                                       'Dragon Fruit', 'Musk melon', 'grapes'],
                            'Sales in kg': [20, 30, 15, 10, 50, 40]})

# create  data_frame2 by creating a dictionary 
# in which values are stored as list
data_frame2 = pd.DataFrame({'Vegetables': ['tomato', 'Onion', 'ladies finger',
                                           'beans', 'bedroot', 'carrot'],
                            'Sales in kg': [200, 310, 115, 110, 55, 45]})

# create  data_frame3 by creating a dictionary 
# in which values are stored as list
data_frame3 = pd.DataFrame({'Baked Items': ['Cakes', 'biscuits', 'muffins',
                                            'Rusk', 'puffs', 'cupcakes'],
                            'Sales in kg': [120, 130, 159, 310, 150, 140]})


# create  data_frame3 by creating a dictionary 
# in which values are stored as list
data_frame4 = pd.DataFrame({'Cool drinks': ['Pepsi', 'Coca-cola', 'Fanta',
                                            'Miranda', '7up', 'Sprite'],
                            'Sales in count': [1209, 1230, 1359, 3310, 2150, 1402]})

# create a excel writer object as shown using
# Excelwriter function
with pd.ExcelWriter("path_to_file.xlsx", mode="a", engine="openpyxl") as writer:
    
    # use to_excel function and specify the sheet_name and index to 
    # store the dataframe in specified sheet
    data_frame4.to_excel(writer, sheet_name="Cool drinks")

Output:

Writing Large Pandas DataFrame to excel file in a zipped format.

If the output dataframe is large, you can also store the excel file as a zipped file. Let's save the dataframe which we created for this example. as excel and store it as a zip file. The ZIP file format is a common archive and compression standard.

Syntax:

ZipFile(file, mode='r')

Parameter:

file: the file can be a path to a file (a string), a file-like object, or a path-like object.
mode: The mode parameter should be 'r' to read an existing file, 'w' to truncate and write a new file, 'a' to append to an existing file, or 'x' to exclusively create and write a new file.

Import the zipfile package and create sample dataframes. Now, specify the path in which the zip file has to be stored, This creates a zip file in the specified path. Create a file name in which the excel file has to be stored. Use to_excel() function and specify the sheet name and index to store the dataframe in multiple sheets

Example: Write large dataframes in ZIP format

Python3

# import zipfile package
import zipfile

# import the python pandas package
import pandas as pd

# create  data_frame1 by creating a dictionary 
# in which values are stored as list
data_frame1 = pd.DataFrame({'Fruits': ['Appple', 'Banana', 'Mango',
                                       'Dragon Fruit', 'Musk melon', 'grapes'],
                            'Sales in kg': [20, 30, 15, 10, 50, 40]})

# create  data_frame2 by creating a dictionary 
# in which values are stored as list
data_frame2 = pd.DataFrame({'Vegetables': ['tomato', 'Onion', 'ladies finger',
                                           'beans', 'bedroot', 'carrot'],
                            'Sales in kg': [200, 310, 115, 110, 55, 45]})

# create  data_frame3 by creating a dictionary 
# in which values are stored as list
data_frame3 = pd.DataFrame({'Baked Items': ['Cakes', 'biscuits', 'muffins',
                                            'Rusk', 'puffs', 'cupcakes'],
                            'Sales in kg': [120, 130, 159, 310, 150, 140]})


# create  data_frame3 by creating a dictionary 
# in which values are stored as list
data_frame4 = pd.DataFrame({'Cool drinks': ['Pepsi', 'Coca-cola', 'Fanta',
                                            'Miranda', '7up', 'Sprite'],
                            'Sales in count': [1209, 1230, 1359, 3310, 2150, 1402]})

# specify the path in which the zip file has to be stored
with zipfile.ZipFile("path_to_file.zip", "w") as zf:
  
    # in open function specify the name in which 
    # the excel file has to be stored
    with zf.open("filename.xlsx", "w") as buffer:
        with pd.ExcelWriter(buffer) as writer:
          
            # use to_excel function and specify the sheet_name and 
            # index to store the dataframe in specified sheet
            data_frame1.to_excel(writer, sheet_name="Fruits", index=False)
            data_frame2.to_excel(writer, sheet_name="Vegetables", index=False)
            data_frame3.to_excel(writer, sheet_name="Baked Items", index=False)
            data_frame4.to_excel(writer, sheet_name="Cool Drinks", index=False)