
How to Improve Your Analytical Report With Conditional Formatting In Pandas
Last Updated on July 3, 2022 by Editorial Team
Author(s): Hrishikesh Patel
Originally published on Towards AI the World’s Leading AI and Technology News and Media Company. If you are building an AI-related product or service, we invite you to consider becoming an AI sponsor. At Towards AI, we help scale AI and technology startups. Let us help you unleash your technology to the masses.
How To Improve Your Analytical Report With Conditional Formatting Inย Pandas
Excel-like Conditional Formatting in Pandas Using Stylerย API

Clearly communicating analytical insights with stakeholders is crucial for data scientists/analysts.
The use of conditional formatting in analytical reports can help in quickly identifying insights into a data frameย (table).
Letโs start with an example first. The following pivot table shows the total sales of different products from 2016 toย 2022.

- Can you identify the largest selling ๐ฐ product in 2016?โโโYes it is Product_B with a total sale of 169 but itโs difficult to identify just by looking๐ at theย table.
Now letโs color the largest selling๐ฐ product for each year. After highlighting, it becomes so much easier to answer the above question, isnโtย it?

Let me show you how to do this inย Pandas.
Topics:
1. Highlight missing values
2. Highlight the maximum (or minimum) value in each row/column
3. Highlight values within a range
4. Plot in-column bar chart
5. Highlight values using a color gradient
6.ย Bonus๐
Note: I strongly recommend using the latest version of Pandas. You can run pip install –upgrade pandas to get Pandasโ latest stableย release.
1. Highlight missingย values
Using dataframe.style.highlight_null() you can color null values as shown below. I stored the pivot table in the variable df_pivotedย .

Itโs okay๐ if you donโt prefer red. Letโs customize the text and background color of missing values using the argument props=โcolor:white;background-color:blackโย .

After highlighting, we can quickly get the insight that Product_H was not sold inย 2018.
2. Highlight maximum (or minimum)ย values
To highlight maximum values in each column, you can use dataframe.style.highlight_max()ย . The method by default colors maximum values in each column as illustrated in the belowย image.

To color max values in each row, you can specify the argument axis=1ย .

Note: Similarly you can use the method dataframe.style.highlight_min() with proper arguments to color minimum values in rows/columns.
3. Highlight values within aย range
Letโs consider that we want to highlight values between 100 and 200โโโitโs quite easy to using dataframe.style.highlight_between(left, right)ย .

4. Plot in-column barย chart
A bar chart plotted within a column can be visually appealing and useful. Such bar charts can be created using dataframe.style.bar() the method as shownย below.

Letโs customize the bar chart to change its color andย size.

5. Highlight values using a colorย gradient
What if you want to highlight the entire column with a color gradient. It can be done using dataframe.style.background_gradient() as depicted below. In the image, the color changes from red to green as the value increases. You can set subset=None to apply the gradient to the entire dataย frame.

6. Bonusย ๐
How can we highlight min, max, and missing values together in the data? Well, you can define a function as illustrated below. The function highlights min, max, and nan values in the column โProduct_Cโ. By setting subset=Noneย , it highlights the values in the entire data frame. Isnโt this function really cool? Let me know your thoughts in the comments!

Please feel free to explore highlighting methods in Pandas documentation.
Before youย go!
I hope you have enjoyed the story and found it useful. Follow me on Medium if youโd like more stories like this and subscribe to me to get my new stories directly into yourย inbox.
My other stories you mightย enjoyโฆ
- Try These Pandas Display Configurations in Your Next Analysis
- Regular Expression (RegEx) in Pythonย : The Basics
How to Improve Your Analytical Report With Conditional Formatting In Pandas was originally published in Towards AI on Medium, where people are continuing the conversation by highlighting and responding to this story.
Join thousands of data leaders on the AI newsletter. Itโs free, we donโt spam, and we never share your email address. Keep up to date with the latest work in AI. From research to projects and ideas. If you are building an AI startup, an AI-related product, or a service, we invite you to consider becoming aย sponsor.
Published via Towards AI