How to save Pandas data into Excel multiple sheets?

To save Pandas DataFrames into multiple Excel sheets, we can use the pd.ExcelWriter() method. This allows you to write multiple DataFrames to different sheets within a single Excel file. Make sure you have the openpyxl package installed before using ExcelWriter().

Basic Example

Let's create two DataFrames and save them to different sheets in an Excel file ?

import pandas as pd

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

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

print("DataFrame 1:")
print(df1)
print("\nDataFrame 2:")
print(df2)

# Save both DataFrames to different sheets
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')
    
print("\nExcel file 'output.xlsx' created successfully!")

DataFrame 1:
     Rank  Subjects
One     5         2
Two     4         1

DataFrame 2:
     Rank  Subjects
One    15        21
Two    41        11

Excel file 'output.xlsx' created successfully!

Advanced Example with Multiple DataFrames

You can save multiple DataFrames with custom formatting and different sheet names ?

import pandas as pd

# Create sample data for different departments
sales_data = pd.DataFrame({
    'Product': ['Laptop', 'Mouse', 'Keyboard'],
    'Revenue': [50000, 1500, 3000],
    'Units': [50, 100, 75]
})

employee_data = pd.DataFrame({
    'Name': ['John', 'Alice', 'Bob'],
    'Department': ['Sales', 'HR', 'IT'],
    'Salary': [60000, 55000, 70000]
})

inventory_data = pd.DataFrame({
    'Item': ['Laptop', 'Mouse', 'Keyboard'],
    'Stock': [25, 200, 150],
    'Reorder_Level': [10, 50, 30]
})

# Save all DataFrames to different sheets
with pd.ExcelWriter('company_data.xlsx', engine='openpyxl') as writer:
    sales_data.to_excel(writer, sheet_name='Sales', index=False)
    employee_data.to_excel(writer, sheet_name='Employees', index=False)
    inventory_data.to_excel(writer, sheet_name='Inventory', index=False)

print("Company data saved to 'company_data.xlsx' with multiple sheets!")
print(f"Sales sheet: {len(sales_data)} rows")
print(f"Employees sheet: {len(employee_data)} rows") 
print(f"Inventory sheet: {len(inventory_data)} rows")

Company data saved to 'company_data.xlsx' with multiple sheets!
Sales sheet: 3 rows
Employees sheet: 3 rows
Inventory sheet: 3 rows

Key Parameters

Important Notes

• Engine Selection: Use engine='openpyxl' for .xlsx files
• File Creation: The Excel file will be created in your current working directory
• Sheet Names: Must be unique within the same workbook
• Index Parameter: Set index=False to exclude row indices from the output

Conclusion

Using pd.ExcelWriter() provides an efficient way to save multiple DataFrames to different sheets in a single Excel file. This method is particularly useful for creating comprehensive reports with organized data across multiple sheets.