Highlight column in DataFrame by comparing with other column

user_15382939 0
Viewed 99

I have a excel which I am reading into DataFrame, comparing the columns and highlight one column and writing to excel. Below is the code and it's not working. I am not seeing cell highlighted. Not sure what I am doing wrong as I learning python.

file = Path(path to excel)
frm_df = pd.read_excel(file)

def highlight(row):
    if row['AMOUNT A'] != row['AMOUNT B'] and row['AMOUNT C'] != row['AMOUNT D']:
    color = 'red'
    background = ['background-color: {}'.format(color) for _ in row]
   return background
frm_df.style.apply(highlight)

writer = pd.ExcelWriter(path to excel)
frm_df.to_excel(writer, 'data')
writer.save()

DataFrame before applying highlight:

AMOUNT A    AMOUNT B    AMOUNT C    AMOUNT D
1400            1400        
3000            3000        
1500            2500    2400        2300
3500            3500

Below is how I want in Excel:

enter image description here

pandas python
1 Answers

Here is one way to do it:

import numpy as np
import pandas as pd

df = pd.DataFrame(
    {
        "AMOUNT A": {0: 1400, 1: 3000, 2: 1500, 3: 3500},
        "AMOUNT B": {0: 1400, 1: 3000, 2: 2500, 3: 3500},
    }
)


def highlight(df, col1, col2):
    mask = df[col1] != df[col2]
    new_df = pd.DataFrame("background-color: white", index=df.index, columns=df.columns)
    new_df[col2] = np.where(mask, "color: {}".format("red"), new_df[col2])
    return new_df


df.style.apply(lambda df: highlight(df, "AMOUNT A", "AMOUNT B"), axis=None).to_excel(
    "filename.xlsx", engine="xlsxwriter"
)

In filename.xlsx:

enter image description here

user_112460560
Related