Pandas styled dataframe - colour bars by categorical column

Question

I have the following dataframe:

# define categorical column.
grps = pd.DataFrame(['a', 'a', 'a', 'b', 'b', 'b']) 

# generate dataframe.
df = pd.DataFrame(np.random.randn(18).reshape(6, 3))

# concatenate categorical column and dataframe.
df = pd.concat([grps, df], axis = 1)

# Assign column headers.
df.columns = ['group', 1, 2, 3]

Generally my dataframes may contain changing numbers of levels for the category column i.e. 'a', 'b', 'c', 'd'...etc.

I can then generate a styled pandas dataframe using the .bar() method and then write to html file:

# style the dataframe.
style_df = (df.style.bar(align = 'zero', color = '#FFA07A'))

# write styled dataframe to html.
df_html = style_df.hide_index().render()
with open("style_df.html","w") as fp:
    fp.write(df_html)

How can I colour the bars for each numerical column by the group category column?

I have tried to use pd.IndexSlice to create subsets of the main dataframe by 'groups' and then pass them to the .bar() method as in Pandas style.bar color based on condition?. However, I get the following error: IndexingError: Too many indexers. Even if this did work, it isn't ideal as I would need to manually add consecutive .bar() methods to the styler. Ideally i'd like the code to react to the different group levels for any given dataframe.

I think conditional formatting using the inbuilt Styler.apply method might be the best option, but haven't got anything to work based on the examples here: https://pandas.pydata.org/pandas-docs/stable/user_guide/style.html. They are all based on formatting the back-ground colour of cells or the values themselves.

Any pointers would be greatly appreciated.

score 0 · Answer 1 · answered Mar 01 '20 at 17:03

I have found a way adapting code from this post: How to set a cell not column or row in a dataframe with color?

It made more sense to colour the background of the cells by group categories, and not colour the bars by group categories. I wanted that to help as visual queue for bigger tables.

I had to define a function that carries out this process, which I can then apply to the table within the pandas styler method.

import pandas as pd
import numpy as np

# define categorical column.
grps = pd.DataFrame(['a', 'a', 'a', 'b', 'b', 'b']) 

# generate dataframe.
df = pd.DataFrame(np.random.randn(18).reshape(6, 3))

# concatenate categorical column and dataframe.
df = pd.concat([grps, df], axis = 1)

# Assign column headers.
df.columns = ['group', 1, 2, 3]

Function for highlighting rows by class variable.

def highlight_rows(x):
    """ Function to apply alternating colour scheme to table cells by rows
    according to groups. 

    Parameters:
    x: dataframe to be styled.

    Returns:
    Styled dataframe

    """
    # ----------------------------------------------------------------------- #
    ### Set initial condition.

    # Generate copy of input dataframe. This will avoid chained indexing issues.
    df_cpy = x.copy()

    # ----------------------------------------------------------------------- #
    ### Define row index ranges per experimental group.

    # Reset numerical index in dataframe copy. Generates new column at
    # position 1 called 'index' and consisting of index positions.
    df_cpy = df_cpy.reset_index()

    # Generate dictionary of key:value pairs corresponding to 
    # grouped experimental class:index range as numerical list, respectively.
    grp_indexers_dict = dict(tuple((df_cpy.groupby('group')['index'])))

    # Generate list of series from dictionary values.
    indexers_series_lst = list(grp_indexers_dict.values())

    # Drop first column - 'index'. This is necessary to avoid 'ValueError' 
    # issue at a later stage. This is due to the extra column causing dataframe 
    # mismatching when this function is called from 'style_df()' function.
    df_cpy = df_cpy.drop('index', axis = 1)

    # ----------------------------------------------------------------------- #
    ### Initiate 'try' block.

    try:
    # Set default color as no colour.
       df_cpy.loc[:,:] = '' 

       # Set row colour by referencing elements of a list of series.
       # Each series corresponds to the numerical row index positions
       # for each group class. They therefore represent each class. 
       # They are generated dynamically from the input dataframe group column
       # in the 'style_df()' function, from which this function is called.
       # Numerical series can be used to slice a dataframe and specifically 
       # pass colour schemes to row subsets.
       # Note: 4 experimental groups defined below in order to account
       # for higher number of group levels. The idea is that these should 
       # always be in excess of total groups.

       # Group - 1.
       df_cpy.iloc[indexers_series_lst[0], ] = 'background-color: #A7CDDD'
       # Group - 2.
       df_cpy.iloc[indexers_series_lst[1], ] = 'background-color: #E3ECF8'
       # Group - 3.
       df_cpy.iloc[indexers_series_lst[2], ] = 'background-color: #A7CDDD'
       # Group - 4.
       df_cpy.iloc[indexers_series_lst[3], ] = 'background-color: #E3ECF8'

       # Return styled dataframe if total experimental classes equal
       # to total defined groups above.
       return(df_cpy)

    # ----------------------------------------------------------------------- #
    ### Initiate 'except' block.

    # Catches index error generated when there are fewer experimental
    # groups than defined in preceding 'try' block. 
    except IndexError:

       # Return styled dataframe.
       return(df_cpy)

Pass the function to the styler and generate styled html table.

# style the dataframe.
style_df = (df.style
            .bar(align = 'zero', color = '#FFA07A')
            # Call 'highlight_rows()' function to colour rows by group class.
            .apply(highlight_rows, axis=None))

# write styled dataframe to html.
df_html = style_df.hide_index().render()
with open("style_df.html","w") as fp:
    fp.write(df_html)enter code here

Although this works well for the type of data I deal with (very unlikley to go beyond 10 groups, hence having upto 10 indexers in the function in my real code), it isn't as elegant as having the function react dynamically to the number of groups.

I would still be interesetd if someone works out a way to do that, I just couldn't work it out. I hope this helps someone with their stylers!

Pandas styled dataframe - colour bars by categorical column

1 Answers1