How to apply conditional formatting to a DataFrame?

This post becomes the first-ever quick read tip of the new upcoming section on my blog. My goal of posting quick reads is to provide an easy, simple, uncomplicated, and clear understanding of some, of course, data science-related topics.

Styling DataFrames

Sometimes it’s required to present data in text tables, and highlight some values, control the precision of floats or simply show data in the format that is most convenient for you. Reading this post, you will find some ways to style your DataFrames. Let’s start!

Loading a tiny dataset

import pandas as pd
clicks = pd.read_csv('clicks.csv')
clicks.head()

def highlight_max(s):
    '''
    highlight max number of variable
    '''
    is_max = s == s.max()
    return ['background-color: yellow' if v else '' for v in is_max]


def color_negative_red(val):
    """
    Takes a scalar and returns a string with
    the css property `'color: red'` for negative
    strings, blue for 2019 or 2020 values,
    black otherwise.
    """
    color = 'red' if val < 0 else 'black'
    if val < 0:
        color = 'red'
    elif val == 2019 or val == 2020:
        color = 'blue'
    else:
        color = 'black'
    
    return 'color: %s' % color

Coloring negative numbers in red and years in blue

clicks.style.applymap(color_negative_red)

Highlighting max number in all columns

clicks.style.apply(highlight_max)

Highlighting max number in specific columns

clicks.style.apply(highlight_max, subset=['clicks', 'roas'])

Highlighting max number in specific columns of a subset of original data frame

print('Maximum Average Numbers per Year and Quarter')

max_variables = clicks[['date_quarter','date_year','clicks',
                      'cost','revenue','cpc','roas']].groupby(['date_year',
                                                               'date_quarter']).mean()

# Highlighting max number in all columns
max_variables.style.apply(highlight_max)

Formatting a specific column

clicks.style.format({'cpc':'{:.2%}'})

Coloring null values

clicks.style.highlight_null(null_color='red')

Different format to specific columns and using lambda

clicks.style.format({'cpc':'{:.2%}', 
                  'cost': lambda x:"{:.2f}".format(abs(x))})

Summary

So far, you have seen how to style your text tables using functions listed below. If you want to learn more about the Style object, you can take a look at the documentation.

style.applymap()
style.format()
style.apply()
style.highlight_null()
and two user functions

Visit my GitHub repo to access to this Jupyter Notebook.