How to Append Existing Excel Sheet with New DataFrame Using Python Pandas

As a data scientist or software engineer, you may often encounter a situation where you need to append new data to an existing Excel sheet. This can be a time-consuming and tedious task if done manually, especially when dealing with large datasets. Fortunately, with Python’s powerful data manipulation library Pandas, appending new data to an existing Excel sheet can be done quickly and easily.

In this article, we will walk you through the step-by-step process of how to append a new DataFrame to an existing Excel sheet using Python Pandas.

Table of Contents

1. Prerequisites
2. Step 1: Import the Required Libraries
3. Step 2: Load the Excel Sheet into a Pandas DataFrame
4. Step 3: Append the New Data to the Existing Sheet
5. Step 4: Verify the Results
6. Common Errors and Solutions
7. Conclusion

Prerequisites

Before we dive into the tutorial, you will need to have the following prerequisites installed:

• Python 3.x
• Pandas library
• OpenPyXL library

You can install these libraries using pip, a package manager for Python. Open your command prompt or terminal and type the following commands:

pip install pandas
pip install openpyxl

Let’s say we have the following existing Excel data named existing_sheet.xlsx:

   Name  Age         City
0  John   25     New York
1  Jane   30  Los Angeles
2   Doe   22      Chicago

And we want to add the following DataFrame to the above-mentioned Excel:

    Name  Age           City
0  Alice   28  San Francisco
1    Bob   35        Seattle

Let’s explore how to do it, step-by-step.

Step 1: Import the Required Libraries

Once you have installed the required libraries, it’s time to import them into your Python script. In your Python script, type the following code:

# imports the Pandas library
import pandas as pd

Step 2: Load the Excel Sheet into a Pandas DataFrame

Next, we will load the existing Excel sheet into a Pandas DataFrame. In your Python script, type the following code:

# Replace "existing_sheet.xlsx" with the name of your existing Excel sheet
existing_sheet = pd.read_excel("existing_sheet.xlsx")

# Replace "new_data.csv" with the name of your new data file
new_data = pd.read_csv("new_data.csv")

• The first line reads the existing Excel sheet into a Pandas DataFrame and assigns it to the variable existing_sheet.
• The second line reads the new data file into a Pandas DataFrame and assigns it to the variable new_data.

Step 3: Append the New Data to the Existing Sheet

Now that we have loaded both the existing Excel sheet and the new data into Pandas DataFrames, we can append the new data to the existing sheet. In your Python script, type the following code:

with pd.ExcelWriter('example.xlsx', engine='openpyxl', mode='a') as writer:
    new_df.to_excel(writer, sheet_name='Sheet2', index=False, header=None)

In this example, Sheet2 is the name of the new DataFrame to appear in the Excel file. Adjust it to match your sheet’s name.

Step 4: Verify the Results

Finally, it’s time to verify that the new data has been successfully appended to the existing Excel sheet. Open the existing Excel sheet and navigate to the sheet you specified in the ExcelWriter object. You should see that the new data has been appended to the end of the sheet.

Common Errors and Solutions

Error 1: PermissionError: [Errno 13] Permission denied

This error occurs when the file is open in another program or lacks write permissions.

Solution: Close the file in Excel or other programs and ensure write permissions.

# Example Code
with pd.ExcelWriter('example.xlsx', engine='openpyxl', mode='a') as writer:
    try:
        new_df.to_excel(writer, sheet_name='Sheet1', index=False, header=None)
    except PermissionError:
        print("Close the file in Excel and try again.")

Error 2: ValueError: Sheet 'Sheet1' already exists but overwrite is False

This error occurs when trying to append to a sheet that already exists without specifying mode='a'.

Solution: Set mode='a' to append to the existing sheet.

# Example Code
with pd.ExcelWriter('example.xlsx', engine='openpyxl', mode='a') as writer:
    new_df.to_excel(writer, sheet_name='Sheet1', index=False, header=None)

Error 3: AttributeError: 'NoneType' object has no attribute 'save'

This error can occur if the openpyxl library is not installed.

Solution: Install the openpyxl library using pip install openpyxl.

# Example Code
import openpyxl

Conclusion

In this tutorial, we have shown you how to append a new DataFrame to an existing Excel sheet using Python Pandas. By following the simple steps outlined in this article, you can save time and effort when dealing with large datasets. If you have any questions or comments, feel free to leave them below.

SHARE:

About Saturn Cloud

Saturn Cloud is your all-in-one solution for data science & ML development, deployment, and data pipelines in the cloud. Spin up a notebook with 4TB of RAM, add a GPU, connect to a distributed cluster of workers, and more. Request a demo today to learn more.