Name: Towards AI Legal Name: Towards AI, Inc. Description: Towards AI is the world's leading artificial intelligence (AI) and technology publication. Read by thought-leaders and decision-makers around the world. Phone Number: +1-650-246-9381 Email: [email protected]
228 Park Avenue South New York, NY 10003 United States
Website: Publisher: https://towardsai.net/#publisher Diversity Policy: https://towardsai.net/about Ethics Policy: https://towardsai.net/about Masthead: https://towardsai.net/about
Name: Towards AI Legal Name: Towards AI, Inc. Description: Towards AI is the world's leading artificial intelligence (AI) and technology publication. Founders: Roberto Iriondo, , Job Title: Co-founder and Advisor Works for: Towards AI, Inc. Follow Roberto: X, LinkedIn, GitHub, Google Scholar, Towards AI Profile, Medium, ML@CMU, FreeCodeCamp, Crunchbase, Bloomberg, Roberto Iriondo, Generative AI Lab, Generative AI Lab Denis Piffaretti, Job Title: Co-founder Works for: Towards AI, Inc. Louie Peters, Job Title: Co-founder Works for: Towards AI, Inc. Louis-François Bouchard, Job Title: Co-founder Works for: Towards AI, Inc. Cover:
Towards AI Cover
Logo:
Towards AI Logo
Areas Served: Worldwide Alternate Name: Towards AI, Inc. Alternate Name: Towards AI Co. Alternate Name: towards ai Alternate Name: towardsai Alternate Name: towards.ai Alternate Name: tai Alternate Name: toward ai Alternate Name: toward.ai Alternate Name: Towards AI, Inc. Alternate Name: towardsai.net Alternate Name: pub.towardsai.net
5 stars – based on 497 reviews

Frequently Used, Contextual References

TODO: Remember to copy unique IDs whenever it needs used. i.e., URL: 304b2e42315e

Resources

Unlock the full potential of AI with Building LLMs for Productionβ€”our 470+ page guide to mastering LLMs with practical projects and expert insights!

Publication

How to Improve Your Analytical Report With Conditional Formatting In Pandas
Latest

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

Image byΒ author

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.

Pivot table showing total sales of products from 2016 to 2022β€Šβ€”β€Šcan you identify max sales in 2016? (image byΒ author)
  • 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?

Highlighting maximum selling products in each yearβ€Šβ€”β€Šnow it’s easy to identify Product_B was the largest-selling product of 2016 with sales of 169 (image byΒ author)

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Β .

Highlighting nan values in red using `.highlight_null` (image byΒ author)

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’ .

`props` argument allows customizing text and background color of highlights (image byΒ author)

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.

`.highlight_max` by default colors max values in each column (image byΒ author)

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

Setting axis=1 in `.highlight_max` colors max values in each row (image byΒ author)

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)Β .

Values between 100 and 200 are highlighted in yellow using the `.highlight_between` method (image byΒ author)

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.

Bar chart plotted within each column using the `dataframe.style.bar` method (image byΒ author)

Let’s customize the bar chart to change its color andΒ size.

The customized bar charts in columns (image byΒ author)

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.

Column β€˜Product_C’ is colored using a gradient of red, yellow, and green colors (image byΒ author)

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!

Defining and using a function to highlight minimum, maximum, and missing values in the data frame (image byΒ author)

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…


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

Feedback ↓