Python conditional formatting for coloring of string values

Question

I have a dataframe with object columns shown below. The columns contain string values with a score and a delta score in brackets

Manager     engage_final   engage_q1_final    engage_q2_final    engage_q3_final   
John        64% (+5)       66% (+4)           65% (+6)           64% (+5)
Peter       92% (-2)       90% (-1)           91% (-7)           93% (-4) 
Jennifer    98% (nan)      96% (nan)          97% (nan)          96% (nan)
Patricia    95% (0)        95% (-1)           96% (+1)           94% (0)  
Melissa     88% (+3)       85% (+2)           84% (0)            90% (-6)

I would like to apply some conditional formatting in terms of the delta scores before I output this into excel. The conditional formatting involves having some colors applied based on the values in the brackets. Darkest green for more than +5, dark green for +5, lighter green for +4, darkest orange for less than -5, dark orange for -5, lighter orange for -4 etc.

I have tried the following code which is not the most elegant and also, it does not work. Can't seem to find many solutions for conditional formatting. Any form of help is much appreciated, thank you.

columns = [col for col in engage_df.columns if 'engage' in col]


def highlight (engage_df):
    if engage_df[columns].str.contains("+5"):
        return ['background-color: #64A064']
    elif engage_df[columns].str.contains("+4"):
        return ['background-color: #78B478']
    elif engage_df[columns].str.contains("+3"):
        return ['background-color: #8CC88C']
    elif engage_df[columns].str.contain("+2"):
        return ['background-color: #A0DCA0']
    elif engage_df[columns].str.contains("+1"):
        return ['background-color: #B4F0B4']
    elif engage_df[columns].str.contains("+"):
        return ['background-color: #508C50']
    elif engage_df[columns].str.contains("-5"):
        return ['background-color: #F48200']
    elif engage_df[columns].str.contains("-4"):
        return ['background-color: #FF9600']
    elif engage_df[columns].str.contains("-3"):
        return ['background-color: #FFAA00']
    elif engage_df[columns].str.contains("-2"):
        return ['background-color: #FFBE00']
    elif engage_df[columns].str.contains("-1"):
        return ['background-color: #FFD200']
    elif engage_df[columns].str.contains("-"):
        return ['background-color: #FF6E00']
    else:
        return ['background-color: white']

engage_df.style.apply(highlight, axis=1)

user_2901002 · Answer

Create dictionaries for mapping values ans pass to Styler.apply only for columns in subset parameter:

def highlight(x):
    #get +-INT values between ()
    s1 = x.str.extract('$([+-]*\d+)$', expand=False)
    #Series with default values
    s = pd.Series('background-color: white', index=x.index)
    #dictionary for match
    d = {'+5':'background-color: #64A064', '+4': 'background-color: #78B478'}
    #if match colot between () add value from dictionary
    for k, v in d.items():
        s.loc[s1 == k] = v
         
    #replace >5 and <-5 values comparing floats values
    s.loc[s1.astype(float) > 5] = 'background-color: #508C50'  
    s.loc[s1.astype(float) < -5] = 'background-color: #FF6E00'  
    return s


cols = [col for col in engage_df.columns if 'engage' in col]
engage_df.style.apply(highlight, subset=cols, axis=1).to_excel('file.xlsx')

Python conditional formatting for coloring of string values

1 Answers