How to Append Data to an Existing Excel File Using Pandas Without Overwriting?

Writing to Existing Excel Files Without Overwriting Data with Pandas

When adding new data to an existing Excel file using pandas, the default behavior is for the new data to overwrite any existing content. This can be problematic if you want to preserve the existing data on other sheets.

The Problem

Consider the following code:

import pandas

writer = pandas.ExcelWriter('Masterfile.xlsx') 

data_filtered.to_excel(writer, "Main", cols=['Diff1', 'Diff2'])

writer.save()

When this code executes, it will add the data in data_filtered to a new sheet named "Main" in the Excel file Masterfile.xlsx. However, it will also delete all other sheets in the file.

The Solution

To avoid overwriting existing data, you can use the engine='openpyxl' option in ExcelWriter. This allows you to access the underlying openpyxl object which gives more control over the process.

Here's how to modify the above code to append to an existing Excel file without overwriting:

import pandas
from openpyxl import load_workbook

book = load_workbook('Masterfile.xlsx')
writer = pandas.ExcelWriter('Masterfile.xlsx', engine='openpyxl') 
writer.book = book

## ExcelWriter for some reason uses writer.sheets to access the sheet.
## If you leave it empty it will not know that sheet Main is already there
## and will create a new sheet.

writer.sheets = dict((ws.title, ws) for ws in book.worksheets)

data_filtered.to_excel(writer, "Main", cols=['Diff1', 'Diff2'])

writer.save()

By explicitly setting writer.sheets to a dictionary of existing sheets, we ensure that ExcelWriter is aware of the existing sheets and will not overwrite them.

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