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:

Mustafa Aydın · Accepted Answer · 2021-05-25 09:32:24Z

4

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

answered May 25, 2021 at 9:32

Mustafa Aydın

18.4k4 gold badges21 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Becky Over a year ago

thanks very much! in my actual dataframe, there are 2 columns in front which are "weekending" and "region", and instead of "prodkey1" and "product1", they are"1st_prodkey" and "1st_product" etc. May I ask how can I amend the function and the regex bit? sorry I am a beginner in coding, not too sure how I can do that.

Mustafa Aydın Over a year ago

@Becky glad to be of help! Change in regex is: re.search(r"^(\d+)", col.name).group(1) so that we look the digits at the beginning not at the end. As far as I understand, you'd like to ignore first two columns in styling. Then, you can change the first if in the function to account for that: if "ourproduct" in col.name or "region" in col.name or "weekending" in col.name:.

Becky Over a year ago

I am getting this error: ValueError: Function <function highlighter at 0x000001CC0BC29E50> returned the wrong shape. Result has shape: (11,) Expected shape: (5, 11) - is there anything I need to amend in the function?

Mustafa Aydın Over a year ago

@Becky I see, for some reason it doesn't broadcast with the new columns. A remedy is to return [""] * len(col) in the first if of the function instead of "" only. This will ensure the resultant shape by returning an empty string for each of the entries for styling.

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related