Adding color by string to a pandas dataframe

Question

I'm working on a dataframe that I output to an html file. I would like to add colors to certain strings:

df1 = df1[(
    df1['Visitor'].str.contains('^TOR|^MTL|^CGY|^WPG|^VAN|^EDM|^OTT', na=False)
) | (df1['Home'].str.contains('^TOR|^MTL|^CGY|^WPG|^VAN|^EDM|^OTT', na=False))]

df1.fillna('', inplace=True)
df1.to_html('schedule.html', index=False)

The above code removes all rows that don't contain any of the listed teams. I would like to add colors to the rows that are left by string. for example 'TOR' would be colored blue.

As seen in another thread, this is what I tried but nothing changed:

    def styler(col):
  
    if col.name != 'Visitor vs Home':
        return [''] * len(col)

    bg_color = col.map({
        'TOR': 'blue',
        'MTL': 'red',
        'VAN': 'green',
    }).fillna('')  
    return 'background-color:' + bg_color


df1.style.apply(styler)

Any tips or suggestions are very much welcome.

Thanks!

What have you tried and what were your results? – G. Anderson Oct 21 '21 at 18:50 — G. Anderson, Oct 21 '21 at 18:50

score 3 · Accepted Answer · answered Oct 21 '21 at 20:07

Series.map can only be used for exact string matching. If needing to test for string contains, use str.contains with the appropriate patterns.

We can then use np.select to allow mapping of pattern to colour. Lastly, we can take advantage of the subset parameter of Styler.apply to only style specific columns instead of having to check the name of each column passed in:

def color_col(col):
    return np.select(
        [col.str.contains('TOR', na=False),
         col.str.contains('MTL', na=False),
         col.str.contains('VAN', na=False)],
        ['background-color: blue',
         'background-color: red',
         'background-color: green'],
        default=''
    ).astype(str)


df.style.apply(color_col, subset=['Visitor vs Home'])

We can also further parameterize our styling function so that we can pass a dictionary of patterns and corresponding colours instead:

def color_col(col, pattern_map, default=''):
    return np.select(
        [col.str.contains(k, na=False) for k in pattern_map.keys()],
        [f'background-color: {v}' for v in pattern_map.values()],
        default=default
    ).astype(str)


df.style.apply(color_col,
               pattern_map={'TOR': 'blue',
                            'MTL': 'red',
                            'VAN': 'green'},
               subset=['Visitor vs Home'])

Both options produce:

Sample Data and imports:

import numpy as np
import pandas as pd

df = pd.DataFrame({
    'Visitor': ['TOR', 'MTL', 'VAN', 'WPG', 'EDM'],
    'Home': ['CGY', 'WPG', 'OTT', 'TOR', 'MTL'],
    'Visitor vs Home': ['String with TOR',
                        'String with MTL',
                        'String with VAN',
                        'String with no match',
                        np.NaN]
})

hmm I tried both options but neither of them worked for me. I don't get any errors, it still just exports with no colours — Rocky5, Oct 21 '21 at 20:26
What exports with no colours? How are you exporting? You've saved an instance of the styler to use to export? Or you added your export to the derivative styles? [Example here](https://stackoverflow.com/a/68749678/15497888) — Henry Ecker, Oct 21 '21 at 20:26
I understand. How are you exporting? You did in 2 steps `styler = df.style.apply(...)` `styler.to_html()` or added to the end of the styler `df.style.apply(...).to_html()`? — Henry Ecker, Oct 21 '21 at 20:31
ah ok I got it to work by adding styler = df.style.apply(...) although it made the html table lose all its borders hahah. I will continue to look for solutions. Thank you for your help! — Rocky5, Oct 21 '21 at 20:39

Adding color by string to a pandas dataframe

1 Answers1