0

I am trying to update old excel file with new one with the data by comparing date in two excel files.

Objective is to update the previous dates columns and add new dates found in new excel into the old excel.

Also copy the formatting from old column into new columns that were added.

What I did, I tried to merge the dataframes from two excels. I still do need help on the logic.

Excel Old File

Excel New File

Please someone help in this

Binayak Chatterjee
  • 196
  • 2
  • 10

1 Answers1

0

The formatting is kind of worth a separate question. Here's my approach to the merge that i think you are seeking. In quick summary we use combine_first()

df_old_excel = pd.read_excel(r'C:\temp\Excel_Old_File.xlsx',header =1)

enter image description here

df_new_excel = pd.read_excel(r'C:\temp\Excel_New_File.xlsx',header =1)

enter image description here

df_old_excel = df_old_excel.set_index('DATE')
df_new_excel.index = df_old_excel.index
df_new_excel.combine_first(df_old_excel)

enter image description here

Dickster
  • 2,969
  • 3
  • 23
  • 29
  • Thanks for your help. Thanks a lot. Can you suggest on the formatting? – Binayak Chatterjee Jul 05 '18 at 14:51
  • see this other post: https://stackoverflow.com/questions/51068361/setting-default-number-format-when-writing-to-excel-from-pandas/51072652#51072652 – Dickster Jul 05 '18 at 15:55
  • For the other post examples it is setting the format not actually copying the styling format or any. – Binayak Chatterjee Jul 06 '18 at 06:21
  • The formatting is not my expertise via python. Actually I'll say my candid opinion. Pandas and python are cool for many things but if its heavy interaction with Excel D.O.M. you want then better to use MS.NET. – Dickster Jul 06 '18 at 23:43