How to Append a Pandas DataFrame to an Existing Excel Sheet Without Overwriting Data?

Append Existing Excel Sheet with New Dataframe Using Python Pandas

Challenge:

Append new dataframe to the end of an existing Excel sheet without overwriting the existing data.

Solution:

Prior to Pandas version 1.4.0, appending to an existing Excel sheet involved manually matching up the index of the new data with the existing sheet and saving it back out.

Improved Solution for Pandas >= 1.4.0:

Pandas 1.4.0 and later versions include an "overlay" option in the ExcelWriter function that allows for appending to an existing sheet without overwriting the existing contents.

appended_data.to_excel(os.path.join(newpath, 'master_data.xlsx'), sheet_name='Sheet1', mode='a', if_sheet_exists='overlay')

Alternative Solution for Pandas < 1.4.0:

def append_df_to_excel(filename, df, sheet_name='Sheet1', startrow=None, **to_excel_kwargs):
    """
    Append a DataFrame [df] to existing Excel file [filename]
    into [sheet_name] Sheet.
    If [filename] doesn't exist, then this function will create it.
    """
    writer = pd.ExcelWriter(filename, engine='openpyxl', mode='a')
    if sheet_name in writer.book.sheetnames:
        # try to open an existing workbook
        writer.book = load_workbook(filename)
        # truncate sheet
        if startrow is None and sheet_name in writer.book.sheetnames:
            startrow = writer.book[sheet_name].max_row
        # index of [sheet_name] sheet
        idx = writer.book.sheetnames.index(sheet_name)
        # remove [sheet_name]
        writer.book.remove(writer.book.worksheets[idx])
        # create an empty sheet [sheet_name] using old index
        writer.book.create_sheet(sheet_name, idx)
        # copy existing sheets
        writer.sheets = {ws.title: ws for ws in writer.book.worksheets}
    else:
        # file doesn't exist, we are creating a new one
        startrow = 0
    # write out the DataFrame to an ExcelWriter
    df.to_excel(writer, sheet_name=sheet_name, **to_excel_kwargs)
    writer.close()
    writer.save()

appended_data.to_excel(os.path.join(newpath, 'master_data.xlsx'), sheet_name='Sheet1', mode='a', if_sheet_exists='overlay')

Example:

import pandas as pd

# Existing data
existing_df = pd.DataFrame({
    'Name': ['John', 'Mary', 'Bob'],
    'Age': [20, 25, 30]
})

# New data to append
new_df = pd.DataFrame({
    'Name': ['Alice', 'Tom'],
    'Age': [35, 40]
})

append_df_to_excel('master_data.xlsx', new_df, sheet_name='Sheet1', startrow=existing_df.shape[0] + 1)

Additional Considerations:

• You can specify the starting row for appending using the startrow parameter.
• You can pass other keyword arguments to the to_excel() method within the to_excel_kwargs dictionary.
• This solution should work for all versions of Pandas, regardless of the Excel engine being used (e.g., xlrd, openpyxl).

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