CODEWITHSUNDEEP
pythonexcelpandaspandas-styles
Gingerhaze
Gingerhaze

Reputation: 676

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

Upvotes: 1

Views: 1085

Answers (2)

Henry Ecker
Henry Ecker

Reputation: 35626

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)

styled df2

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

Upvotes: 2

Mad Physicist
Mad Physicist

Reputation: 114230

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.

Upvotes: 1

Related Questions