Append data to the last row of the Excel sheet using Pandas

user_6636867 0
Viewed 1647

I have an excel data for three variables (Acct, Order, Date) in a Sheet name called Orders

I have created a data frame by reading this Sheet

   import pandas as pd
   sheet_file=pd_ExcelFile("Orders.xlsx", engine="openpyxl")
   for sheet_name in worksheets:
       df=pd.read_excel(sheet_file,sheet_name,header=1)
       append_data.append(df)

    append_data=pd.concat(append_data)

I have another Excel file called "Total_Orders.xlsx" with ~100k rows and I need to append the above dataframe to this excel file (Sheet Name="Orders")

   with pd.ExcelWriter('Total_Orders.xlsx',sheet_name='Orders',engine="openpyxl") as writer:
        append_data.to_excel(writer,startrow=2,header=False,index=False)
        writer.save()

The above is overwriting the data instead of appending it. I know startrow is the key here but I am not sure how to fix this. Any help is much appreciated

pandas.excelwriter pandas export-to-excel append python
1 Answers

Have you tried in mode="a", along these lines:

with pd.ExcelWriter("Total_Orders.xlsx", mode="a", engine="openpyxl") as writer:
    append_data.to_excel(writer, sheet_name="Orders")

EDIT - in response to comment

import pandas as pd
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl import load_workbook

append_data = pd.DataFrame([{'Acct':3, 'Order':333, 'Note':'third'},
                           {'Acct':4, 'Order':444, 'Note':'fourth'}])
    
wb = load_workbook(filename = "stackoverflow.xlsx")
ws = wb["Orders"]
for r in dataframe_to_rows(append_data, index=False, header=False):  #No index and don't append the column headers
    ws.append(r)
wb.save("stackoverflow.xlsx")

The stackoverflow.xlsx before: enter image description here

The stackoverflow.xlsx after (the 'Other' sheet was not affected): enter image description here

user_109436370
Related