How Can I Append Pandas Data to an Existing Excel File Without Overwriting Existing Sheets?

Saving Pandas Data to Existing Excel Files Using Openpyxl

When working with existing Excel files, it's crucial to avoid overwriting data. In Pandas, you can encounter this issue when writing to an existing file using the to_excel() method.

Problem

The following code attempts to write a DataFrame to a specific sheet in an existing Excel file:

import pandas

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

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

writer.save()

The problem arises because Pandas deletes all other sheets in the file, leaving only the newly created sheet named "Main."

Solution

To address this issue, we can leverage the openpyxl library, which Pandas uses internally for handling XLSX files. By loading the existing workbook using openpyxl.load_workbook() and assigning it to the ExcelWriter object, we can modify the sheet dictionary to ensure the existing sheets are retained.

Here's the modified code:

import pandas
from openpyxl import load_workbook

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

# Assign the sheet dictionary to preserve existing sheets
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)

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

writer.save()

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