How to write to an existing excel file without overwriting data (using pandas)?

Question

I use pandas to write to excel file in the following fashion:

import pandas

writer = pandas.ExcelWriter('Masterfile.xlsx') 

data_filtered.to_excel(writer, "Main", cols=['Diff1', 'Diff2'])

writer.save()

Masterfile.xlsx already consists of number of different tabs. However, it does not yet contain "Main".

Pandas correctly writes to "Main" sheet, unfortunately it also deletes all other tabs.

Answer 1

This will work fine:

import pandas

from openpyxl import load_workbook

book = load_workbook('Masterfile.xlsx')

writer = pandas.ExcelWriter('Masterfile.xlsx', engine='openpyxl') 

writer.book = book

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

data_filtered.to_excel(writer, "Main", cols=['Diff1', 'Diff2'])

writer.save()

If You want to learn data science with python visit this data science tutorial and data science certification by Intellipaat.

Comments

Thank you for this solution! The line concerning the sheets attribute did the trick over other solutions present elsewhere.