Conditionally format cells in each column based on columns in another dataframe

Question

I have a dataframe that contains threshold values for 20+ elements that's formatted like so

df1:

	Li	Se	Be
Upper	30	40	10
Lower	10	5	1

I have another dataframe which contains values for those elements

df2:

	Li	Se	Be
Sample 1	50.8	100	20
Sample 2	-0.01	2	-1

If the values in df2 are greater than the Upper threshold I want the background color of the cell in df2 be to red when it is written to an excel file. If the value is lower than the lower threshold I want the cell to be colored yellow.

So in the example, 50.8 background color should be red because 50.8 > 30.

I've done this before when comparing a single value like so

df.style.apply(lambda x: 'background-color : red' if x>=value else '')

But I'm lost on how to apply it column wise based on the columns in df1

You're trying to color both dataframes based on df2? Just color df1 based on df2. the other way around? — Henry Ecker, Jul 20 '21 at 18:27

Henry Ecker · Accepted Answer · 2021-07-20T18:44:04.487

Can use np.select to compare dataframes and set results for conditions:

def bounded_highlights(df):
    conds = [df > df1.loc['Upper'], df < df1.loc['Lower']]
    labels = ['background-color:red', 'background-color: yellow']
    return np.select(conds, labels, default='')


df2.style.apply(bounded_highlights, axis=None)

DataFrames and Imports (slightly modified df2 so not all are highlighted):

import numpy as np
import pandas as pd


df1 = pd.DataFrame({'Li': {'Upper': 30, 'Lower': 10},
                    'Se': {'Upper': 40, 'Lower': 5},
                    'Be': {'Upper': 10, 'Lower': 1}})

df2 = pd.DataFrame({
    'Li': {'Sample 1': 50.8, 'Sample 2': -0.01},
    'Se': {'Sample 1': 100, 'Sample 2': 6},
    'Be': {'Sample 1': 9, 'Sample 2': -1}
})

modified df2:

             Li   Se  Be
Sample 1  50.80  100   9
Sample 2  -0.01    6  -1

How the np.select code works:

conds = [df2 > df1.loc['Upper'], df2 < df1.loc['Lower']]
labels = ['background-color:red', 'background-color: yellow']
styles = np.select(conds, labels, default='')

conds:

[             Li     Se     Be
Sample 1   True   True  False
Sample 2  False  False  False,
              Li     Se     Be
Sample 1  False  False  False
Sample 2   True  False   True]

styles labels are applied based on the True values in conds:

[['background-color:red' 'background-color:red' '']
 ['background-color: yellow' '' 'background-color: yellow']]

I think this should work. It runs without issue but when I print to excel the cells aren't colored. Any ideas? — Gingerhaze, Jul 20 '21 at 19:14
I don't know how you're sending to excel but you'll need to pass the _styler_ to excel not the dataframe `df2.style.apply(bounded_highlights, axis=None).to_excel(...)` — Henry Ecker, Jul 20 '21 at 19:16
This worked but I had to edit the function in order for it to return a df with the same cols and index as the target df instead of an array — Gingerhaze, Jul 22 '21 at 13:33

score 1 · Answer 2 · answered Jul 20 '21 at 18:40

You can do as suggested here: How to define color of specific cell in pandas dataframe based on integer position (e.g., df.iloc[1,1]) with df.style?. The basic idea is to make a dataframe of styles you want to use, and apply that:

styles = pd.DataFrame('', index=df2.index, columns=df2.columns)
styles[df2 > df1.loc['Upper']] = 'background-color : red'
df2.style.apply(styles, axis=None)

This is similar to what @Henry Ecker suggests, except it does not use np.select, instead applying the conditions manually.

Conditionally format cells in each column based on columns in another dataframe

2 Answers2