Conditional formatting with Python Pandas

One of the features of spreadsheets I’ve always found useful is conditional formatting. You can easily highlight if a value is above or below a certain amount, for example to highlight negative figures. This can can dramatically improve readability. I had this requirement for a client who needed to highlight a status row in red in a Pandas dataframe sent by email, if the status column contained the word ‘Failed’ or not. Heres how I did it, building up from a simple example:

1. When using the dataframe .to_html() method, we can specify a formatter function for a particular column (named ‘Status’)

df.to_html(formatters={'status': redden})

This formatter function called ‘redden’ is what we need to write, and the result of each function must be a unicode string. (as per the docs https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_html.html). We can now create the function ‘redden’ that returns a string, which is whatever the input its, wrapped in the html code to style it red.

def redden(x):
    x = f'<span class="significant" style="color: red;">{x}</span>'
    return str(x)

Result:

2. Conditional formatting of the columm

Our condition is that the red formatting should only be applied if the text is ‘Failed’, so we can modify our function to add a simple if condition:

def redden(x):
    if x = 'Failed':
        x = f'<span class="significant" style="color: red;">{x}</span>'
        return str(x)

Result:

3. Simplify with an inline lamda function

This simple function is ripe for simplifying even further by using a lambda function:

redden = lambda x: f'<span class="significant" style="color: red;">{x}</span>' if x == "Failed" else str(x)

The result is the same, showing the ‘Failed’ status as red.

4. Making the entire row red

If you need to make the entire row red, not just the cell, this does not seem to be possible using the .to_html method, so instead we have to use styling instead, and then using the .render method to get the html:

df.style.apply(redden, axis=1).render()

Our redden function is now a little different as the function applies a style, directly:

 redden = lambda x: ['color:red']*len(x) if x.status == 'Failed' else ['']*len(x)

Result:

Well worth taking the time to learn and apply this for end user readability!