Compare values and color Pandas cells based on values from other columns

Question

I am working on a project where the final values (in column 'Amount') are compared against values in other columns to determine whether the final values are breached the pre-determined threshold. If breached, then the final value will be colored based on the breached threshold, if not, then the final value will be colored green. Sample:

 df

    Product     Components  Amount  Yellow  Orange  Red     Bound
       A             a1      61        50    55     60       Upper
       A             a2      28        60    30     20       Lower
       A             a3      37        10     5     0        Lower
       B             b1      89        90    100    110      Upper
       B             b2      91       100    95     90       Lower
       C             c1       5       10    15      20       Upper
       C             c2      29       30    20      10       Lower
       C             c3      15       100   200     300      Upper
       C             c4     201       500   400     300      Lower

Expected:

Attempt with code:

 def highlight(df):
    r = 'red'
    g = 'green'
    o = 'orange'
    y = 'yellow'

    yellow_up = (df['Amount'] > df['Yellow']) & (df['Amount'] < df['Orange']) & (df['Amount'] < df['Red']) & (df['Bound']=='Upper')
    orange_up = (df['Amount'] > df['Yellow']) & (df['Amount'] > df['Orange']) & (df['Amount'] < df['Red']) & (df['Bound']=='Upper')
    red_up = (df['Amount'] > df['Yellow']) & (df['Amount'] > df['Orange']) & (df['Amount'] > df['Red']) & (df['Bound']=='Upper')

    yellow_down = (df['Amount'] < df['Yellow']) & (df['Amount'] > df['Orange']) & (df['Amount'] > df['Red']) & (df['Bound']=='Lower')
    orange_down = (df['Amount'] < df['Yellow']) & (df['Amount'] < df['Orange']) & (df['Amount'] > df['Red']) & (df['Bound']=='Lower')
    red_down = (df['Amount'] < df['Yellow']) & (df['Amount'] < df['Orange']) & (df['Amount'] < df['Red']) & (df['Bound']=='Lower')

    df1 = pd.DataFrame('background-color: ', index = df.index, columns = df.columns)
    df1['Amount'] = np.where(yellow_up, 'background-color: {}'.format(y), 'background-color: {}'.format(g))
    df1['Amount'] = np.where(orange_up, 'background-color: {}'.format(o), 'background-color: {}'.format(g))
    df1['Amount'] = np.where(red_up, 'background-color: {}'.format(r), 'background-color: {}'.format(g))

    df1['Amount'] = np.where(yellow_down, 'background-color: {}'.format(y), 'background-color: {}'.format(g))
    df1['Amount'] = np.where(orange_down, 'background-color: {}'.format(y), 'background-color: {}'.format(g))
    df1['Amount'] = np.where(red_down, 'background-color: {}'.format(y), 'background-color: {}'.format(g))

    return df1

df.style.apply(highlight, axis = None)

However, this doesn't work as expected. Thank you in advance for your help!

Answer 1

I would write the function as this:

def highlight(row):
    color_map = {'Yellow':'yellow','Orange':'orange', 'Red':'red'}
    name = row.name

    thresh = df.loc[name, ['Yellow','Orange','Red']]
    direction = -1 if df.at[name, 'Bound']=='Upper' else 1

    value = row['Amount'] * direction
    thresh = (thresh * direction).sort_values()

    if (thresh > value).any():
        color = color_map[(thresh > value).idxmax()]
    else:
        color = 'green'

    return [f'background-color:{color}']

# apply
df.style.apply(highlight, subset=['Amount'], axis=1)

Output: