is there a way to compare two excels and perform operations like insert row as per comparison?

user_16582713 0
Viewed 125

I'm trying to solve a problem using python. I have two excels let's say Old_excel and New_excel. New_excel is kind of a version of Old_excel such that few rows in New_excel have been inserted which are not present in Old_excel and fews rows are deleted from New_excel.

Example of Old_excel and New_excel

I am looking for a solution which can insert a blank row in Old_excel if new row has been inserted in New_excel and insert blank row in New_excel if row has been deleted from New_excel as mentioned below:-

The two excels should look like

Any help would be appreciated.

Note: Yellow color in output excel examples is to help viewer understand the problem statement quickly. Hence not required in solution.

dataframe excel algorithm python
1 Answers

You will need to install pandas and openpyxl packages.

Then we will find differences between two files. This is just reading the rows and hashing them:

import pandas as pd

df1 = pd.read_excel('old.xlsx', engine='openpyxl', header=None, dtype=str)
df2 = pd.read_excel('new.xlsx', engine='openpyxl', header=None, dtype=str)
h1 = df1.apply(lambda x: hash(str(x.values)), axis=1)
h2 = df2.apply(lambda x: hash(str(x.values)), axis=1)

ch2 = h1[~h1.isin(h2)]
ch1 = h2[~h2.isin(h1)]

Now we will read the excel files once again (in order to preserve original styling) and change the style of relevant cells:

from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl import load_workbook
from openpyxl.styles import PatternFill

fill = PatternFill("solid", fgColor="fdff32")
wb1 = load_workbook('old.xlsx')  
wb2 = load_workbook('new.xlsx')  

ws1 = wb1.active
ws2 = wb2.active

# if number of rows is different
while ws2.max_row>ws1.max_row:
    ws1.append([None])
while ws1.max_row>ws2.max_row:
    ws2.append([None])

# define highltning style 
fill = PatternFill("solid", fgColor="fdff32")

for ix in ch1.index:
    for cell in list(ws1.rows)[ix]:
        cell.fill = fill
for ix in ch2.index:
    for cell in list(ws2.rows)[ix]:
        cell.fill = fill
wb1.save('old_.xlsx') 
wb2.save('new_.xlsx')
user_85058170
Related