0

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.

Raspberry Lemon
  • 113
  • 9
  • Have you contemplated reading the xlsx file to python as a data frame, appending the data in python, and then saving the resultant df over the old file? – j__carlson Oct 04 '21 at 15:32

2 Answers2

1

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)
not_speshal
  • 22,093
  • 2
  • 15
  • 30
  • In certain cases the excel might not exist, the above command I use creates an excel file as well. – Raspberry Lemon Oct 04 '21 at 15:32
  • 1
    You can use **os.path.isfile** to check whether a file exist. Then use the output boolean and either append or create the file. – Odhian Oct 04 '21 at 15:34
0

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()
Ben
  • 61
  • 5