Pandas: Style a specific cell based on another column in a dataframe

Question

I want to highlight the cells based on the values of another column in a dataframe. Like in the below example, I want to highlight the prodkey1 and product1 cells if the value of p1_ourproduct is equal to 1, and same for product 2 and 3.

How can I do that?

import pandas as pd

data = { 'prodkey1': [101,102,103,104,105],
    'prdouct1': ['a','b','c','d','e'],
    'prodkey2': [201,202,203,204,205],
    'prdouct2': ['f','g','h','i','j'],
    'prodkey3': [301,302,303,304,305],
    'prdouct3': ['k','l','m','n','o'],
    'p1_ourproduct': [1,0,1,0,0],
    'p2_ourproduct': [0,0,1,1,0],
    'p3_ourproduct': [0,1,0,0,1],
    }

df = pd.DataFrame(data)

Expected output:

Answer 1

You can write a custom highlighter function and apply it:

import re

def highlighter(col):
    if "ourproduct" in col.name:
        return ""
    else:
        last_digits = re.search(r"(\d+)$", col.name).group(1)
        lookup_column = f"p{last_digits}_ourproduct"
        return ["background-color: yellow"
                if our_prod
                else ""
                for our_prod in df[lookup_column]]

df.style.apply(highlighter)

First we look at the passed column's name: if it contains ourproduct then return empty string i.e., no styling. Else, get the last digits in the column name with a regex (\d+)$ and lookup the corresponding ourproduct column. Then form a list comprehension: if the value in the corresponding lookup column is 1 (the if our_prod part), then return a background color styling with yellow; otherwise no styling.

to get