Home > Backend Development > Python Tutorial > How Can I Append Pandas Data to an Existing Excel File Without Overwriting Existing Sheets?

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

Linda Hamilton
Release: 2024-12-11 11:09:11
Original
997 people have browsed it

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:

1

2

3

4

5

6

7

import pandas

 

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

 

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

 

writer.save()

Copy after login

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:

1

2

3

4

5

6

7

8

9

10

11

12

13

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()

Copy after login

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!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template