Append pandas dataframe to excelsheet, not overwrite it

Question

I want to append the contents of a panda dataframe df to an excelsheet.

This is what I did;

df.to_excel(excel_writer="target.xlsx", sheet_name="sheet_1")

The problem with this code is that it overwrites target.xlsx. I lost all my old data in target.xlsx as a result. What I want the code to do is to append, not overwrite the excel sheet.

I am using python 3.7.

Possible duplicate of [Append existing excel sheet with new dataframe using python pandas](https://stackoverflow.com/questions/38074678/append-existing-excel-sheet-with-new-dataframe-using-python-pandas) — Sociopath, Oct 01 '19 at 05:54
Wouldn't it make sense to read all data from your excel file, combine it in python and then write it back to excel? — Sosel, Oct 01 '19 at 05:55
@Sosel, My excel sheet has several other worksheets unrelated to the dataframe. — guagay_wk, Oct 01 '19 at 05:56
@user781486 is this related to your question? [How to write to an existing excel file without overwriting data (using pandas)](https://stackoverflow.com/questions/20219254/how-to-write-to-an-existing-excel-file-without-overwriting-data-using-pandas) — Caio Rocha, Apr 26 '20 at 18:11
This might help you: https://stackoverflow.com/questions/61412725/how-do-you-append-a-new-row-in-an-excisting-excel-file-using-python-with-multipl/61436236#61436236 — Vishal Upadhyay, Apr 27 '20 at 13:59
[This](https://stackoverflow.com/a/47740262/9375102) answer by MaxU worked for me when I did more excel & python stuff. — Umar.H, Apr 29 '20 at 18:23

score 4 · Answer 1 · answered Apr 26 '20 at 17:37

I think the easiest way to do this is:

import pandas as pd
import numpy as np
import xlsxwriter

workbook = xlsxwriter.Workbook('arrays.xlsx')
worksheet = workbook.add_worksheet() # a created excel sheet

array = pd.read_csv('array.csv')
array = array.tolist()

row = 0

for col, data in enumerate(array):
    worksheet.write_column(row, col, data)

workbook.close()

Here is the xlsxwriter documentation.

score 4 · Accepted Answer · edited Jul 01 '22 at 14:28

4

with pd.ExcelWriter('target.xlsx', mode='a') as writer:  
    df.to_excel(writer, sheet_name='sheet_1')

Source: Pandas Dataframe to Excel

edited Jul 01 '22 at 14:28

mbh86

6,078
3
18
31

answered May 02 '20 at 13:31

kaushik karan

351
2
7

This is probably the best answer. – Daniel Scott May 02 '20 at 22:07

score 3 · Answer 3 · answered Apr 27 '20 at 13:40

3

Load 'target.xlsx' in target_df. Then

new_df = target_df.append(df)

It should work fine I guess. Then save it back as 'target.xlsx'.

answered Apr 27 '20 at 13:40

Subir Verma

393
1
3
10

score 3 · Answer 4 · answered May 02 '20 at 08:06

If you want to append a new sheet, here's your answer.

import pandas as pd

# Open the existing excel file with all sheets
df_dict = pd.read_excel('target.xlsx',sheet_name=None)

with pd.ExcelWriter(fp) as writer:

     # Save the original sheets
     for sheet in df_dict:
          df[sheet].to_excel(writer, sheet_name=sheet)

     # If the new sheet name doesn't exist, append it
     if 'sheet_1' not in df_dict:
          df.to_excel(writer, sheet_name='sheet_1')

score 2 · Answer 5 · answered May 03 '20 at 11:34

You can simply read the data stored in target.xlsx and store it in a data frame. Now, using pandas concat() method, you can merge the two data frames (new data frame and old data frame) and finally store them in the same excel file.

Code Snippet:

import pandas as pd
old_df = pd.read_excel('target.xlsx')  
frames = [old_df, new_df]
result = pd.concat(frames)
result.to_excel("target.xlsx", sheet_name="sheet_1", index=False)

I hope it helps.

score 0 · Answer 6 · answered May 02 '20 at 20:41

Try this:

from openpyxl import load_workbook
writer = pandas.ExcelWriter('target.xlsx', engine='openpyxl') 
writer.book = load_workbook('target.xlsx')
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)
df.to_excel(excel_writer=writer, sheet_name="sheet_1")
writer.save()

Append pandas dataframe to excelsheet, not overwrite it

6 Answers6