How to save Pandas data into Excel multiple sheets?

To save Pandas DataFrames into multiple excel sheets, we can use the pd.ExcelWriter() method. Make sure you have the openpyxl package installed before using ExcelWriter().

Steps

• Create a two-dimensional, size-mutable, potentially heterogeneous tabular data, df1.
• Print the input DataFrame, df1.
• Create another DataFrame, df2, and print it.
• Use ExcelWriter() method to write the given formatted cells into an Excel sheet.

Example

import pandas as pd

df1 = pd.DataFrame(
   [[5, 2], [4, 1]],
   index=["One", "Two"],
   columns=["Rank", "Subjects"]
)

df2 = pd.DataFrame(
   [[15, 21], [41, 11]],
   index=["One", "Two"],
   columns=["Rank", "Subjects"]
)

print "DataFrame 1:\n", df1
print "DataFrame 2:\n", df2

with pd.ExcelWriter('output.xlsx') as writer:
df1.to_excel(writer, sheet_name='Sheet_name_1')
df2.to_excel(writer, sheet_name='Sheet_name_2')

Output

DataFrame 1:

     Rank  Subjects
One    5     2
Two    4     1

DataFrame 2:

     Rank Subjects
One   15    21
Two   41    11

It will also create an Excel file called "output.xlsx" in your project directory and save the DataFrame values in two different sheets.

Rishikesh Kumar Rishi

Updated on: 04-Oct-2023

21K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started