Conditionally formatting a column using a dictionary (Pandas)

Question

I've scoured the forums and am still struggling with this.

Say I have a dictionary of keys with html colours as values, i.e:

colours = {'a': '#2DB2EC',
           'b': '#A71AB7',
           'c': '#EB4141',
           'd': '#EB4141',
           'e': '#36D22C',
           'f': '#2C4ED2',
           'g': '#137C15',
           'h': '#F4EA06'}

I also have a dataframe with two columns, one of which (col1) corresponds to the keys in my "colours" dictionary:

col1     col2
a        900
d        1000
h        800
z        750
m        100
l        50

How do I use the Pandas style.applymap to colour-code the second column based on the dictionary value of column one (leaving it blank/white if no value found)? I've been playing around with some lambda functions but am having real difficulty in getting anything to work.

Sorry if this is a basic question and thanks in advance.

score 1 · Answer 1 · answered Apr 14 '21 at 16:29

1

Try with:

df.style.apply(lambda r: [f"background-color:{colours.get(r['col1'],'')}"]*len(r), axis=1)

Output:

answered Apr 14 '21 at 16:29

Quang Hoang

146,074
10
56
74

Thanks - I'm not sure that my 'f' string is working as it should because when I use your code it shows everything within the square brackets as a string, except for the f itself... – SlowlyLearning Apr 14 '21 at 16:35

score 1 · Accepted Answer · answered Apr 14 '21 at 17:02

1

colour-code the second column based on the dictionary value of column one (leaving it blank/white if no value found)

You can also try series.map and then assign blank color to the col1 when done.

def col_fun(df_c):
    df_c = df_c.copy()
    df_c['col2'] = df_c['col1'].map(colours).fillna('').radd("background-color:")
    df_c['col1'] = 'background-color:'
    return df_c
df.style.apply(col_fun,axis=None)
#or if you have more cols: df.style.apply(col_fun,axis=None,subset=['col1','col2'])

answered Apr 14 '21 at 17:02

anky

74,114
11
41
70

Thanks - I think this is working because when I break it down I can see df_c having mapped the correct 'background colours' to each of the dataframe cells however when I then apply it back to the original dataframe the original becomes type 'io.formats.style.Styler' and I'm unable to view it through my variable explorer (Spyder). Am I missing something? – SlowlyLearning Apr 14 '21 at 18:54
1

@SlowlyLearning Check this, you can try using a Jupyter notebook : https://stackoverflow.com/questions/58116600/pandas-styler-not-printing-dataframe-to-spyders-console-as-expected and https://stackoverflow.com/questions/55660958/why-does-excel-styling-not-work-in-pandas – anky Apr 15 '21 at 02:45

Conditionally formatting a column using a dictionary (Pandas)

2 Answers2