How can I stop pandas overwriting old data in Excel file?-CodePudding

I have a dataframe that I'm creating within Python and want this dataframe to be appended to the end of an existing file every day. I currently have the code below but this overwrites any data on the existing sheet, despite me specifying the mode as append. How can I modify this so that the existing data is not modified, only new data is added to the end.

with pd.ExcelWriter(r'C:\Users\XXX\Downloads\Digitalisation\mat_flow\reblend.xlsx', engine="openpyxl", mode="a") as writer:
    df.to_excel(writer, sheet_name = ft_tags_final[i][j])

CodePudding user response：

please try this:

append new data to existing data before writing

df_old = pd.read_excel(r'C:\Users\XXX\Downloads\Digitalisation\mat_flow\reblend.xlsx',sheet_name = ft_tags_final[i][j])
df = df_old.append(df)
with pd.ExcelWriter(r'C:\Users\XXX\Downloads\Digitalisation\mat_flow\reblend.xlsx', engine="openpyxl", mode="a") as writer:
    df.to_excel(writer, sheet_name = ft_tags_final[i][j])