How to append dataframe to excel-CodePudding

I have a dataframe which I save it into an excel file at a certain location. Currently I do this way:

df.to_excel(r'C:\Users\user_name\Downloads\test.xlsx')

Issue I am facing is when I insert the new dataframe it overwrites old ones. I want to append the new data. I tried several SOF answers but nothing seems to be working.

CodePudding user response：

You can first read_excel, append and then write back to_excel:

filename = r'C:\Users\user_name\Downloads\test.xlsx'
existing = df.read_excel(filename)
output = existing.append(df)
output.to_excel(filename)

To check if the file exists before reading, you can use:

import os

filename = r'C:\Users\user_name\Downloads\test.xlsx'
if os.path.exists(filename):
    existing = df.read_excel(filename)
    output = existing.append(df)
else:
    output = df
output.to_excel(filename)

CodePudding user response：

One way to handle it is that you could read what is in Excel and combine it with your data frame, then overwrite the excel file/ generate it again basically.

Here's a sample of a similar question asked where a solution can be found with excel writer. Instead of overwriting the existing data, they just carefully set a startrow by reading the existing file for the startrow.

The last row/ start row can be found with the command writer.sheets[sheetname].max_row

append dataframe to excel with pandas

import pandas
from openpyxl import load_workbook

book = load_workbook('test.xlsx')
writer = pandas.ExcelWriter('test.xlsx', engine='openpyxl')
writer.book = book
writer.sheets = {ws.title: ws for ws in book.worksheets}

for sheetname in writer.sheets:
    df1.to_excel(writer,sheet_name=sheetname, startrow=writer.sheets[sheetname].max_row, index = False,header= False)

writer.save()