How to compare two dataframes and update with the new value

Question

Using Panda Dataframes, I would like to compare two DFs and update the excel sheet. The problem, I'm seeing from my code, it only appends new row. It doesn't update pervious row. For example: DF1:

{'Sheet1': [{'ID': 1.0, 'NAME': 'hostname1', 'IP_ADDRESS': '192.168.1.1', 'STATUS': 'completed'}, {'ID': 2.0, 'NAME': 'hostname2', 'IP_ADDRESS': '192.168.1.2', 'STATUS': 'completed'}, {'ID': 3.0, 'NAME': 'hostname3', 'IP_ADDRESS': '192.168.1.3', 'STATUS': 'in_progress'}, {'ID': 4.0, 'NAME': 'hostname4', 'IP_ADDRESS': '192.168.1.4', 'STATUS': 'completed'}]}

DF2:

{'Sheet1': [{'ID': 1.0, 'NAME': 'hostname1', 'IP_ADDRESS': '192.168.1.1', 'STATUS': 
'completed'}, {'ID': 2.0, 'NAME': 'hostname2', 'IP_ADDRESS': '192.168.1.2', 'STATUS': 'problem'}, {'ID': 3.0, 'NAME': 'hostname3', 'IP_ADDRESS': '192.168.1.3', 'STATUS': 'rma'}, {'ID': 4.0, 'NAME': 'hostname4', 'IP_ADDRESS': '192.168.1.4', 'STATUS': 'completed'},
{'ID': 5.0, 'NAME': 'hostname5', 'IP_ADDRESS': '192.168.1.5', 'STATUS': 'future'}]}

As you can see DF2 has different values.

here is the code, it show two DFs are different but doesn't updates the sheet.

def writexlsx_multi_sheets(filename, data, sheet_name, **to_excel_kwargs):

from openpyxl import load_workbook

orginalDF = pd.read_excel(filename, sheet_name=sheet_name, index=False)
newDF = pd.DataFrame.from_dict(data)
if 'engine' in to_excel_kwargs:
    to_excel_kwargs.pop('engine')

writer = pd.ExcelWriter(filename, engine='openpyxl')
writer.book = load_workbook(filename)
writer.sheets = {ws.title:ws for ws in writer.book.worksheets}

if not orginalDF.equals(newDF):
    result = orginalDF.combine_first(newDF)
    result.to_excel(writer, sheet_name=sheet_name, index=False)
    writer.save()
else:
    return False

score 1 · Answer 1 · answered Jun 10 '20 at 10:03

If you don't need to use openpyxl for anything specific then you could just stick with pandas:

import pandas as pd

df_o = pd.read_excel(file_name)
df_n = pd.read_csv(file_path + 'df_b.csv')

df_o

   ID   HOSTNAME           IP       STATUS
0   1  hostname1  192.168.0.1    completed
1   2  hostname2  192.168.0.2    completed
2   3  hostname3  192.168.0.3  in_progress
3   4  hostname4  192.168.0.4    completed

df_n

   ID   HOSTNAME           IP     STATUS
0   1  hostname1  192.168.0.1  completed
1   2  hostname2  192.168.0.2    problem
2   3  hostname3  192.168.0.3        rma
3   4  hostname4  192.168.0.4  completed
4   5  hostname5  192.168.0.5     future

Then your combined dataframe:

if not df_o.equals(df_n):
    df = df_n.combine_first(df_o)
    df.to_excel(file_name, index=False)

df_c = pd.read_excel(file_name)

df_c

   ID   HOSTNAME           IP     STATUS
0   1  hostname1  192.168.0.1  completed
1   2  hostname2  192.168.0.2    problem
2   3  hostname3  192.168.0.3        rma
3   4  hostname4  192.168.0.4  completed
4   5  hostname5  192.168.0.5     future

Openpyxl has nice stream writing support if you're appending records real time like a logfile, or web scraping. Maybe your use case is more akin to CRUD though, and if you're not dealing with a tremendous amount of data, using pandas to overwrite the file probably would do the trick.

Anyhow I believe this line result = orginalDF.combine_first(newDF) is backwards. It should read result = newDF.combine_first(originalDF). The documentation for pandas is decent check out the combine_first() function here.

How to compare two dataframes and update with the new value

1 Answers1

Linked