How to append dataframe to existing Excel file based on headers with python

Viewed 2173

I want to append the data frame to the existing excel file based on the header.

my code is:

import pandas as pd
from openpyxl import load_workbook


df = pd.DataFrame({'id': [5,6,7,8],
                   'name': ['name 5','name 6','name 7','name 8'],
                    'place': ['place 1','place 2','place 3','place 4'],
                    'age': [7,34,78,23]})
print(df)


writer = pd.ExcelWriter('Bill Charge Report.xlsx', engine='openpyxl')

# try to open an existing workbook
writer.book = load_workbook('Bill Charge Report.xlsx')

# copy existing sheets
writer.sheets = dict((ws.title, ws) for ws in writer.book.worksheets)

# read existing file
reader = pd.read_excel(r'Bill Charge Report.xlsx')
# write out the new sheet
df.to_excel(writer,index=False,header=False,startrow=len(reader)+1)
writer.close()

This will append the data frame as It is. but now I want to append the data based on the header names.

For example, my workbook doesn't contain a "place" header, so I want to ignore the headers which are not available in the workbook.

enter image description here

Note: here 'place' is just an example, I need a generic solution that will automatically ignore the unwanted headers. and append the data frame only to existing headers. Thanks in advance

1 Answers

Just pass the desired columns you want to write to to_excel

df.to_excel(writer, index=False, header=False,startrow=len(reader)+1, columns=reader.columns)

where columns is a list with id, name, age.

Final result:

enter image description here

Related