Conditional Formatting in Python

Introduction

I know a lot of you still love Excel. I won’t deny that I love it too because it is so simple yet efficient. However, Python has proved multiple times that can do more when it comes to handle data and do analysis.

One feature that I really like in Excel is Conditional Formatting. Conditional Formatting allows us to apply conditions to specific cells and then a certain style, mostly coloring of the background. Why I like it? Because it gives the opportunity to quickly visualize all the important information.

In this article, I will share with you how to apply Conditional Formatting in Dataframes!

The Dataset

In this short tutorial we use an Online Retail dataset that can be found in the article’s repository.

Let’s take a look at the data:

The online retail data

Import library & Load Data

import pandas as pd

data = pd.read_csv(‘online_retail.csv’)

Conditional Formatting

Before we begin provide the code, we should describe each component that will do the “job”.

data.style.applymap()

data: Our dataset

data.style: Creates a Styler object that uses CSS to customize various components of a Dataframe.

data.style.applymap(): All these components are passing to the Styler object from a style function using applymap(). So, because we are referring to CSS, we would use coding like:

background-color: white;
font-size: red;

Finally, the code provided to the style function should be string containing attribute-value pair as shown above. If no value should be given, then ‘’ is applied.

Highlight the cells that meet a certain criteria

def highlight_cells(value):
if value == 2.55:
color = ‘yellow’
else:
color = ”
return ‘background-color:{}’.format(color)data.style.applymap(highlight_cells)

Result 1

In order to maintain efficient coding style, we create the style function and pass it as parameter to applymap() function.

As we see, once the value of any cell is 2.55, then the background of the cell is going to be yellow, otherwise we don’t change anything.

Highlight the cells based on condition

def highlight_cells(value, color_true, color_false, criteria):
if value == criteria:
color = color_true
else:
color = color_false
return ‘background-color: {}’.format(color)data.style.applymap(highlight_cells, color_true = ‘green’, color_false = ‘yellow’, criteria = 2.55)

Result 2

Here the difference is that we apply to all cells a condition. Once we found a specific value in any of the cells, then we color it green. In any other case (cell), we color it yellow.

Highlight the cells that meet a condition in specific column(s) only

data.style.applymap(highlight_cells, color_true=’green’, color_false=’yellow’, criteria = 2.55, subset=[‘UnitPrice’])

Result 3

Here we apply conditional formatting to only one column (can be more of course).

Highlight rows based on categorical values

Let’s find the unique Countries (the categorical variable)data.Country.unique()

Countries

Now, let’s apply a specific coloring based on the Country:

def highlight_rows(row):
value = row.loc[‘Country’]
if value == ‘United Kingdom’:
color = ‘red’
elif value == ‘France’:
color = ‘green’
else:
color = ‘blue’

return [‘background-color: {}’.format(color) for i in row]data.style.apply(highlight_rows, axis=1)

Conditional text formatting

In this case, we can customize the following:

  • font color: ‘color: <color/hex code>’
  • font type: ‘font-family: <font_type>’
  • font size: ‘font-size: <font size in px>’
  • font weight: ‘font-weight: <font weight>’

def highlight_cells(value, color_true, color_false, criteria):
if value == criteria:
color = color_true
else: ”return ‘color: {}’.format(color)data.style.applymap(highlight_cells, color_true = ‘green’, color_false = ”, criteria = 2.55)

Result 4

Extra | Highlight any text that meet a condition (color, font size and weight)

def highlight_cells(value, color_true, background_true, size_true, weight_true, criteria):
if value == criteria:
color = color_true
background = background_true
size = size_true
weight = weight_true
else:
color = ”
background = ”
size = ”
weight = ”
return ‘color: {}’.format(color) + ‘;background-color: {}’.format(background) + ‘;font-size: {}’.format(size) +’;font-weight: {}’.format(weight)data.style.applymap(highlight_cells, color_true = ‘green’, background_true= ‘yellow’, size_true =’16px’, weight_true = ‘bold’, criteria = 2.55, subset=[‘UnitPrice’])

Extra result

Conclusion

Conditional formatting is a very useful capability of Excel and it is very nice that we can apply this in Python too. So, next time you think to use Excel to apply conditional formatting and observe even more better some cells, don’t do it! Python is here for you 😊