Copying a Pandas Dataframe onto an existing Excel sheet

Question

I know this has already been potentially answered elsewhere but I use the answers to tailor my issue. I have manage to merge two dataframes to look like the following:

 |Store number| Week 14 | Week 15 | Week 16 |
 |:-----------|:--------|:--------|:--------|
 |     A      |     2   |     4   |      4  |
 |     B      |     1   |     2   |     2   |
 |     C      |     2   |     2   |     2   |

I need to paste this into an Excel that has several data tabs that need to be populate but on the above the Sheet is called All Sales. I need to overwrite the data in Week 14 (as can somethimes change) and add in Week 15 and Week 16. This has to be added to a current Excel as I have a topline sheet that lookups onto all the data tabs. The sheet it needs to be pasted to:

Hope that makes sense. When i run my current to_excel code using Excelwriter and the xlsxwriter engine the file just creates a new sheet and deletes all other sheets. I can get the data into the correct place on the sheet (ie row 6 and under correct weeks on the above image but cannot get it to paste into the current worksheet!

Answer 1

There is some other answers out there about how to maintain sheets/tabs data while updating another sheet/tabs.

This one seemed the most useful: Can Pandas read and modify a single Excel file worksheet (tab) without modifying the rest of the file?

As for updating your specific file, you should read the original excel file into Python as a dataframe, drop the Week 14 Column data, then merge the excel dataframe with the new data.

As for selecting the sheet_name, this can be done when reading the excel

import pandas as pd

date_dict= {'Store number': ['A', 'B', 'C'], 'Week 14': [2, 1, 2], 'Week 15': [4,2,2], 'Week 16' : [4,2,2]
       }

df = pd.DataFrame(data=date_dict)

df2 = pd.read_excel('test.xlsx', sheet_name="All Sales")

df2 = df2.drop('Week 14', axis = 1)

df2 = df2.merge(df, left_on='Store number', right_on ='Store number')

df2.to_excel('test.xlsx', index=False, sheet_name='All Sales')

So that test.xlsx looks like this before the script runs:

And test.xlsx looks like this after the script runs:

Note the change in column Week 14 from the original set to the new set.