highlighted changed words in python

user_8318946 0
Viewed 276

I've found a function that compares Excel spreadsheets and highlights changes. How can I modify the script in a way that only changed words will be highlighted?

Below you can see the logic I would like to apply.

Dataframe 1 enter image description here

DataFrame 2 enter image description here

DataFrame 3 enter image description here

The original script is on GitHub, link is here. As an output I would like to keep everything in 1 workbook as in code below. The only change that needs to be made is to somehow mark all changed words and export them to DataFrame 3 (dfDiff).

import pandas as pd
from pathlib import Path
import os


def excel_diff(path_OLD, path_NEW, index_col):
    df_OLD = pd.read_excel(path_OLD, index_col=index_col).fillna(0)
    df_NEW = pd.read_excel(path_NEW, index_col=index_col).fillna(0)

    # Perform Diff
    dfDiff = df_NEW.copy()
    droppedRows = []
    newRows = []

    cols_OLD = df_OLD.columns
    cols_NEW = df_NEW.columns
    sharedCols = list(set(cols_OLD).intersection(cols_NEW))

    for row in dfDiff.index:
        if (row in df_OLD.index) and (row in df_NEW.index):
            for col in sharedCols:
                value_OLD = df_OLD.loc[row, col]
                value_NEW = df_NEW.loc[row, col]
                if value_OLD == value_NEW:
                    dfDiff.loc[row, col] = df_NEW.loc[row, col]
                else:
                    dfDiff.loc[row, col] = ('{}→{}').format(value_OLD, value_NEW)
        else:
            newRows.append(row)

    for row in df_OLD.index:
        if row not in df_NEW.index:
            droppedRows.append(row)
            dfDiff = dfDiff.append(df_OLD.loc[row, :])

    dfDiff = dfDiff.sort_index().fillna('')
    print(dfDiff)
    print('\nNew Rows:     {}'.format(newRows))
    print('Dropped Rows: {}'.format(droppedRows))

    # Save output and format
    # fname = '{} vs {}.xlsx'.format(path_OLD.stem, path_NEW.stem)
    fname = (os.path.dirname(os.path.abspath(__file__)) + '/uploads/differences.xlsx'.format(path_OLD.stem, path_NEW.stem))
    writer = pd.ExcelWriter(fname, engine='xlsxwriter')

    dfDiff.to_excel(writer, sheet_name='differences', index=True)
    df_NEW.to_excel(writer, sheet_name=path_NEW.stem, index=True)
    df_OLD.to_excel(writer, sheet_name=path_OLD.stem, index=True)

    # get xlsxwriter objects
    workbook = writer.book
    worksheet = writer.sheets['differences']
    worksheet.hide_gridlines(2)
    worksheet.set_default_row(15)

    # define formats
    grey_fmt = workbook.add_format({'font_color': '#E0E0E0'})
    highlight_fmt = workbook.add_format({'font_color': '#ff6666', 'bg_color': '#ffff00'})
    new_fmt = workbook.add_format({'font_color': '#32CD32', 'bold': True})

    # set format over range
    ## highlight changed cells
    worksheet.conditional_format('A1:ZZ1000', {'type': 'text',
                                               'criteria': 'containing',
                                               'value': '→',
                                               'format': highlight_fmt})

    # highlight new/changed rows
    for row in range(dfDiff.shape[0]):
        if row + 1 in newRows:
            worksheet.set_row(row + 1, 15, new_fmt)
        if row + 1 in droppedRows:
            worksheet.set_row(row + 1, 15, grey_fmt)

    # save
    writer.save()
    print('\nDone.')


def main():
    path_OLD = Path(os.path.dirname(os.path.abspath(__file__)) + '/uploads/old_content.xlsx')
    path_NEW = Path(os.path.dirname(os.path.abspath(__file__)) + '/uploads/new_content.xlsx')

    # get index col from data
    df = pd.read_excel(path_NEW)
    index_col = df.columns[0]
    print('\nIndex column: {}\n'.format(index_col))
    excel_diff(path_OLD, path_NEW, index_col)


main()
xlsxwriter pandas python
1 Answers

I wrote a simpler implementation of what you want to achieve. Take in mind that this code requires both dataframes to have the same structure (same number of rows and columns with the same structure), if dataframes are different you need to apply some more logic.

import pandas as pd

# Create out test dataframes
df_old = pd.DataFrame({'ID': [1,2,3], 'upload_date': ['15/03/2021','16/03/2021','17/03/2021'],
                    'product_ASIN': ['1234567890','1234567891','1234567892'], 'Project_name': ['Name1','Name2','Name3'],
                    'Country': ['Poland','Poland','Poland'], 'Brand': ['Hugo Boss','Hugo Boss','Hugo Boss'],
                    'Category': ['Perfumes','Perfumes','Perfumes'], 'Title': ['title 1','title 2','title 3'],
                    'Description': ['Description 1','Description 2','Description 3']})

df_new = pd.DataFrame({'ID': [1,2,3], 'upload_date': ['15/03/2021','16/03/2021','17/03/2021'],
                    'product_ASIN': ['1234567890','1234567891','1234567892'], 'Project_name': ['Name1','Name2','Name3'],
                    'Country': ['Poland','Poland','Poland'], 'Brand': ['Hugo Boss','Hugo Boss','Hugo Boss'],
                    'Category': ['Perfumes','Perfumes','Perfumes'], 'Title': ['title 1','title changed','title 3'],
                    'Description': ['Description 1','Description 2','description']})

# Declare a list in which we will store the indexes of the cells which are different
differencesList = []

# Iterate through the cells of the old df and compare its values with the new one's values
# If they are not equal add them to the list
for row in range(0, df_old.shape[0]):
    for col in range(0, df_old.shape[1]):
        if df_old.iloc[row,col] != df_new.iloc[row,col]:
            differencesList.append([row,col])

# Pass the new df to xlsxwriter as this is the one we want to keep
writer = pd.ExcelWriter('df_diff.xlsx', engine='xlsxwriter')
df_new.to_excel(writer, sheet_name='Sheet1', index=False)
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

# Define a format object with red color
cell_format_red = workbook.add_format({'font_color': 'red'})

# Iterate through the cells again and check if the values are contained in the list
# If they do write them with and apply the format, else write the value without any format
for row in range(0, df_new.shape[0]):
    for col in range(0, df_new.shape[1]):
        if [row,col] in differencesList:
            worksheet.write(row + 1, col, df_new.iloc[row,col], cell_format_red)
        else:
            worksheet.write(row + 1, col, df_new.iloc[row,col], None)

writer.save()

Output: enter image description here

Finally if you only want to format the part of the string that has changed (as in your screenshot) you need to use rich string ( https://xlsxwriter.readthedocs.io/worksheet.html#worksheet-write-rich-string ).

user_115953930
Related