How to Append a New DataFrame to an Existing Excel Sheet in Python Using Pandas?

Append Existing Excel Sheet with New Dataframe Using Python Pandas

In this article, we will explore how to append a new dataframe to an existing Excel spreadsheet using Python Pandas.

Problem:

Appending a new dataframe to an existing Excel sheet using the Pandas to_excel() function overwrites the existing data. The goal is to append the new data to the end of the current sheet, maintaining the existing content.

Solution:

To address this issue, we can leverage the following steps:

1. Load the Existing Workbook:

   • Use the openpyxl package to load the existing Excel workbook.
   • Save the existing sheet names in a list.

2. Prepare the New Dataframe:

   • Remove any unnecessary rows or columns from the new dataframe.

3. Create a New Workbook Writer:

   • Create an ExcelWriter object using Pandas, specifying the existing workbook as an output.
   • Set engine to "openpyxl", mode to "a", and if_sheet_exists to "new" if the existing sheet doesn't exist.

4. Write the New Dataframe:

   • Write the new dataframe to the new sheet created by the ExcelWriter.
   • Adjust the cell formatting as needed.

5. Copy Cells from New to Existing Sheet:

   • Since Pandas does not support in-place appending, we use openpyxl to copy the cells from the new sheet to the existing sheet, starting at the end of the existing data.

6. Remove the New Sheet:

   • After copying the data, remove the new sheet that was created for writing the new dataframe.

7. Save and Close the Workbook:

   • Save the workbook and close it.

Example:

import pandas as pd
import openpyxl
from openpyxl.utils import get_column_letter

# Load existing workbook
workbook = openpyxl.load_workbook("existing_excel.xlsx")
sheet_names = workbook.sheetnames

# Prepare new dataframe
new_df = pd.DataFrame({
    "Name": ["Alice", "Bob", "Carol"],
    "Age": [25, 30, 35]
})

# Create new workbook writer
with pd.ExcelWriter("existing_excel.xlsx", engine="openpyxl", mode="a", if_sheet_exists="new") as writer:
    # Write new dataframe
    new_df.to_excel(writer, sheet_name="NewData", index=False)

    # Get worksheet objects
    new_sheet = writer.sheets["NewData"]
    existing_sheet = workbook["ExistingData"]

    # Get last row in existing sheet
    last_row = existing_sheet.max_row

    # Copy cells from new sheet to existing sheet
    copy_excel_cell_range(
        src_ws=new_sheet,
        tgt_ws=existing_sheet,
        src_min_row=2,
        src_max_row=new_sheet.max_row,
        tgt_min_row=last_row + 1,
        with_style=True
    )

    # Remove temporary sheet
    workbook.remove(new_sheet)

# Save and close
workbook.save("existing_excel.xlsx")

By following this approach, you can seamlessly append new data to an existing Excel sheet without overwriting the existing content.

The above is the detailed content of How to Append a New DataFrame to an Existing Excel Sheet in Python Using Pandas?. For more information, please follow other related articles on the PHP Chinese website!