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

Question

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.

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

Some non-answer advice: {ws.title: ws for ws in writer.book.worksheets} should be slightly faster and more readable than dict((ws.title, ws) for ws in writer.book.worksheets), and you can use pd.ExcelWriter() in a with block instead of closing it explicitly (with pd.ExcelWriter('Bill Charge Report.xlsx', engine='openpyxl') as writer: ...) — dericke
– dericke, Commented Feb 1, 2021 at 18:24
@dericke, Thanks for the suggestion, I am glad for your advice. thanks — Praveen K M
– Praveen K M, Commented Feb 2, 2021 at 3:31

Miguel Trejo · Accepted Answer · 2021-02-01 18:08:35Z

1

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:

answered Feb 1, 2021 at 18:08

Miguel Trejo

6,7376 gold badges30 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related