How to Add New Sheets to an Existing Excel File Using Pandas?

Generating New Sheets in an Existing Excel File with Pandas

When dealing with Excel data in Python, users may encounter the challenge of saving new sheets to an existing Excel file. This guide provides a solution using the Pandas library, covering the limitations of the "xlsxwriter" engine and the implementation of the "openpyxl" engine.

Understanding the Issue

In the given code, the user creates an Excel file with two sheets, "x1" and "x2." However, attempting to add new sheets, "x3" and "x4," overrides the original data. This occurs because the "xlsxwriter" engine only saves data to a new file or overwrites an existing one.

Solution Using the "openpyxl" Engine

To preserve existing data while adding new sheets, use the "openpyxl" engine. The following code demonstrates this approach:

<code class="python">import pandas as pd
import numpy as np
from openpyxl import load_workbook

path = r"C:\Users\fedel\Desktop\excelData\PhD_data.xlsx"

book = load_workbook(path)  # Load the existing Excel file
writer = pd.ExcelWriter(path, engine='openpyxl')  # Create a Pandas writer connected to the workbook
writer.book = book  # Assign the workbook to the Pandas writer

x3 = np.random.randn(100, 2)
df3 = pd.DataFrame(x3)

x4 = np.random.randn(100, 2)
df4 = pd.DataFrame(x4)

df3.to_excel(writer, sheet_name='x3')  # Write the new dataframes to the existing file
df4.to_excel(writer, sheet_name='x4')

writer.close()  # Save the changes to the file</code>

Explanation

1. Load the existing Excel file: This line reads the existing Excel file into a workbook object using the load_workbook function.
2. Create a Pandas writer: Here, a Pandas ExcelWriter is created with the engine='openpyxl' parameter, which specifies the use of the "openpyxl" engine.
3. Assign the workbook to the writer: The writer.book attribute is set to the loaded workbook object, linking the Pandas writer to the existing file.
4. Generate new dataframes: Similar to the original code, new dataframes ("x3" and "x4") are created.
5. Write new dataframes: The new dataframes are saved to the existing file using the to_excel method, specifying the sheet names ("x3" and "x4").
6. Save changes: Finally, the changes made by the Pandas writer are saved to the Excel file by calling the writer.close() method.

WebSocket, ws, and Dict

In the suggested code from the given link:

• WebSocket (ws): This refers to each worksheet in the loaded workbook.
• ws.title: It represents the name of a specific worksheet within the workbook.
• Dict: The code uses a dictionary to create a mapping between worksheet names and worksheet objects. This allows the Pandas writer to access specific sheets within the loaded workbook.

The above is the detailed content of How to Add New Sheets to an Existing Excel File Using Pandas?. For more information, please follow other related articles on the PHP Chinese website!