Different Python package import patterns using __init__.py file
2022-03-25
Interpreting coefficients of Dummy Variables in a Linear Regression Model
2022-03-27
Show all

Styling Pandas dataframes using Styler

7 mins read

What is styling and why care?

The basic idea behind styling is that a user will want to modify the way the data is presented but still preserve the underlying format for further manipulation. The most straightforward styling example is using a currency symbol when working with currency values. For instance, if your data contains the value 25.00, you do not immediately know if the value is in dollars, pounds, euros, or some other currency. If the number is $25 then the meaning is clear. Percentages are another useful example where formatting the output makes it simpler to understand the underlying analysis. For instance, which is quicker to understand: .05 or 5%? Using the percentage sign makes it very clear how to interpret the data. The key item to keep in mind is that styling presents the data so a human can read it but keeps the data in the same pandas data type so you can perform your normal pandas math, date, or string functions.

Pandas styling also includes more advanced tools to add colors or other visual elements to the output. The pandas documentation has some really good examples but it may be a bit overwhelming if you are just getting started. The rest of this article will go through examples of using styling to improve the readability of your final analysis.

Styling the data

Let’s get started by looking at some data. For this example, we will use some 2018 sales data for a fictitious organization. We will pretend to be an analyst looking for high-level sales trends for 2018. All of the data and example notebook are on github. Please note that the styling does not seem to render properly in GitHub but if you choose to download the notebooks it should look fine.

Import the necessary libraries and read the data:

import numpy as np
import pandas as pd

df = pd.read_excel('2018_Sales_Total.xlsx')

The data includes sales transaction lines that look like this:

account numbernameskuquantityunit priceext pricedate
0740150Barton LLCB1-200003986.693380.912018-01-01 07:21:51
1714466Trantow-BarrowsS2-77896-163.16-63.162018-01-01 10:00:47
2218895Kulas IncB1-699242390.702086.102018-01-01 13:24:58
3307599Kassulke, Ondricka and MetzS1-654814121.05863.052018-01-01 15:05:22
4412290Jerde-HilpertS2-34077683.21499.262018-01-01 23:26:55

Given this data, we can do a quick summary to see how much the customers have purchased from us and what their average purchase amount looks like:

df.groupby('name')['ext price'].agg(['mean', 'sum'])
meansum
name
Barton LLC1334.615854109438.50
Cronin, Oberbrunner and Spencer1339.32164289734.55
Frami, Hills and Schmidt1438.466528103569.59
Fritsch, Russel and Anderson1385.366790112214.71
Halvorson, Crona and Champlin1206.97172470004.36

For the sake of simplicity, I am only showing the top 5 items and will continue to truncate the data through the article to keep it short.

As you look at this data, it gets a bit challenging to understand the scale of the numbers because you have 6 decimal points and somewhat large numbers. Also, it is not immediately clear if this is in dollars or some other currency. We can fix that using the DataFrame style.format.

(df.groupby('name')['ext price']
 .agg(['mean', 'sum'])
 .style.format('${0:,.2f}'))

Here is what it looks like now:

Styling example 1

Using the format function, we can use all the power of python’s string formatting tools on the data. In this case, we use ${0:,.2f} to place a leading dollar sign, add commas, and round the result to 2 decimal places.

For example, if we want to round to 0 decimal places, we can change the format to ${0:,.0f}

(df.groupby('name')['ext price']
 .agg(['mean', 'sum'])
 .style.format('${0:,.0f}'))
Styling example 2

If you are like me and always forget how to do this, I found the Python String Format Cookbook to be a good quick reference. String formatting is one of those syntax elements that I always forget so I’m hoping this article will help others too.

Now that we have done some basic styling, let’s expand this analysis to show off some more styling skills.

If we want to look at total sales by each month, we can use the grouper to summarize by month and also calculate how much each month is as a percentage of the total annual sales.

monthly_sales = df.groupby([pd.Grouper(key='date', freq='M')])['ext price'].agg(['sum']).reset_index()
monthly_sales['pct_of_total'] = monthly_sales['sum'] / df['ext price'].sum()
Styling example 3

We know how to style our numbers but now we have a combination of dates, percentages, and currency. Fortunately, we can use a dictionary to define a unique formatting string for each column. This is really handy and powerful.

format_dict = {'sum':'${0:,.0f}', 'date': '{:%m-%Y}', 'pct_of_total': '{:.2%}'}
monthly_sales.style.format(format_dict).hide_index()
Styling example 4

I think that is pretty cool. When developing final output reports, having this type of flexibility is pretty useful. Astute readers may have noticed that we don’t show the index in this example. The hide_index function suppresses the display of the index – which is useful in many cases.

In addition to styling numbers, we can also style the cells in the DataFrame. Let’s highlight the highest number in green and the lowest number in color Trinidad (#cd4f39).

(monthly_sales
 .style
 .format(format_dict)
 .hide_index()
 .highlight_max(color='lightgreen')
 .highlight_min(color='#cd4f39'))
Styling example 5

One item to highlight is that I am using method chaining to string together multiple function calls at one time. This is a very powerful approach for analyzing data and one I encourage you to use as you get further in your pandas proficiency. I recommend Tom Augspurger’s post to learn much more about this topic.

Another useful function is the background_gradient which can highlight the range of values in a column.

(monthly_sales.style
 .format(format_dict)
 .background_gradient(subset=['sum'], cmap='BuGn'))
Styling example 6

The above example illustrates the use of the subset parameter to apply functions to only a single column of data. In addition, the cmap argument allows us to choose a color palette for the gradient. The matplotlib documentation lists all the available options.

Styling with Bars

The pandas styling function also supports drawing bar charts within the columns.

Here’s how to do it:

(monthly_sales
 .style
 .format(format_dict)
 .hide_index()
 .bar(color='#FFA07A', vmin=100_000, subset=['sum'], align='zero')
 .bar(color='lightgreen', vmin=0, subset=['pct_of_total'], align='zero')
 .set_caption('2018 Sales Performance'))
Styling example 7

This example introduces the bar function and some of the parameters to configure the way it is displayed in the table. Finally, this includes the use of the set_caption to add a simple caption to the top of the table.

The next example is not using pandas styling but I think it is such a cool example that I wanted to include it. This specific example is from Peter Baumgartner and uses the sparkline module to embed a tiny chart in the summary DataFrame.

Here’s the sparkline function:

import sparklines

def sparkline_str(x):
    bins=np.histogram(x)[0]
    sl = ''.join(sparklines(bins))
    return sl
sparkline_str.__name__ = "sparkline"

We can then call this function like a standard aggregation function:

df.groupby('name')['quantity', 'ext price'].agg(['mean', sparkline_str])

I think this is a really useful function that can be used to concisely summarize data. The other interesting component is that this is all just text, you can see the underlying bars as lines in the raw HTML. It’s wild.

Conclusion

The pandas style API is a welcome addition to the pandas library. It is beneficial when you get towards the end of your data analysis and need to present the results to others. There are a few tricky components to string formatting so hopefully the items highlighted here are useful to you.

Source:

https://pbpython.com/styling-pandas.html

Amir Masoud Sefidian
Amir Masoud Sefidian
Machine Learning Engineer

Comments are closed.