python - append dataframe to excel with pandas

Question

I desire to append dataframe to excel

This code works nearly as desire. Though it does not append each time. I run it and it puts data-frame in excel. But each time I run it it does not append. I also hear openpyxl is cpu intensive but not hear of many workarounds.

import pandas
from openpyxl import load_workbook

book = load_workbook('C:\\OCC.xlsx')
writer = pandas.ExcelWriter('C:\\OCC.xlsx', engine='openpyxl')
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)

df1.to_excel(writer, index = False)

writer.save()

I want the data to append each time I run it, this is not happening.

Data output looks like original data:

A   B   C
H   H   H

I want after run a second time

A   B    C
H   H    H
H   H    H

Apologies if this is obvious I new to python and examples I practise did not work as wanted.

Question is - how can I append data each time I run. I try change to xlsxwriter but get AttributeError: 'Workbook' object has no attribute 'add_format'

Answer 1

first of all, this post is the first piece of the solution, where you should specify startrow=: Append existing excel sheet with new dataframe using python pandas

you might also consider header=False. so it should look like:

df1.to_excel(writer, startrow = 2,index = False, Header = False)

if you want it to automatically get to the end of the sheet and append your df then use:

startrow = writer.sheets['Sheet1'].max_row

and if you want it to go over all of the sheets in the workbook:

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

btw: for the writer.sheets you could use dictionary comprehension (I think it's more clean, but that's up to you, it produces the same output):

writer.sheets = {ws.title: ws for ws in book.worksheets}

so full code will be:

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()

Answer 2

You can use the append_df_to_excel() helper function, which is defined in this answer:

Usage examples:

filename = r'C:\OCC.xlsx'

append_df_to_excel(filename, df)

append_df_to_excel(filename, df, header=None, index=False)

append_df_to_excel(filename, df, sheet_name='Sheet2', index=False)

append_df_to_excel(filename, df, sheet_name='Sheet2', index=False, startrow=25)

Answer 3

I tried to read an excel, put it in a dataframe and then concat the dataframe from excel with the desired dataframe. It worked for me.

def append_df_to_excel(df, excel_path):
    df_excel = pd.read_excel(excel_path)
    result = pd.concat([df_excel, df], ignore_index=True)
    result.to_excel(excel_path, index=False)

df = pd.DataFrame({"a":[11,22,33], "b":[55,66,77]})
append_df_to_excel(df, r"<path_to_dir>\<out_name>.xlsx")

Answer 4

All examples here are quite complicated. In the documentation, it is much easier:

def append_to_excel(fpath, df, sheet_name):
    with pd.ExcelWriter(fpath, mode="a") as f:
        df.to_excel(f, sheet_name=sheet_name)

append_to_excel(<your_excel_path>, <new_df>, <new_sheet_name>)