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

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

Answer

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']]