How to colorize all rows of a dataframe based on values of a column dynamically?

Let’s say I have a dataframe like the following;

df = pd.DataFrame({'Sample': ['A', 'B', 'C', 'D','E','F'],
                    'NFW': [8.16, 8.63, 9.25, 8.97, 7.5, 8.21],
                    'Qubit': [55, 100, 229, 30, 42, 33],
                    'Lane': ['1', '1', '2', '2', '3', '3']})

I want to colorize the background of all rows based on the values of the Lane column dynamically. Also, I’ll write this dataframe to an excel file and I need to keep all style changes in there too.

>Solution :

IIUC, you can use a colormap (for example from matplotlib) and map it to each row using a Categorical and style.apply:

import matplotlib

cmap = matplotlib.cm.get_cmap('Pastel1')
colors = [matplotlib.colors.rgb2hex(cmap(i)) for i in range(cmap.N)]
# ['#fbb4ae', '#b3cde3', '#ccebc5', '#decbe4', '#fed9a6', '#ffffcc', '#e5d8bd', '#fddaec', '#f2f2f2']

def color(df, colors=colors):
    # get unique values as category
    s = df['Lane'].astype('category')
    # map the colors and create CSS string
    s = ('background-color: '
         +s.cat.rename_categories(colors[:len(s.cat.categories)])
           .astype(str)
        )
    # expand to DataFrame size
    return pd.DataFrame(np.tile(s, (df.shape[1], 1)).T,
                        index=df.index, columns=df.columns)
    
df.style.apply(color, axis=None)