3000 words long article, Pandas beautifies your Excel table!

#This article will show how to beautify numbers in a Pandas DataFrame and use some More advanced Pandas-style visualization options to improve your ability to analyze data using Pandas.

Common examples include:

• Use currency symbols when processing currency values. For example, if your data contains the value 25.00, you don't immediately know whether that value is Chinese yuan, US dollars, British pounds, or some other currency.
• Percentage is another useful indicator, 0.05 or 5%? Use the percent symbol to make it very clear how to interpret the data.
• Pandas styles also include more advanced tools for adding color or other visual elements to the output.

Case Analysis

This article will use a virtual data to explain it to everyone. The data is 2018 sales data for a fictional organization.

The data set link is as follows:
https://www.aliyundrive.com/s/Tu9zBN2x81c

1. Import related libraries , and read the data

import numpy as np
import pandas as pd

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

The effect is as follows:After reading these data, we can make a quick summary to see how much the customer purchased from us and What is their average purchase amount. For the sake of simplicity, I have intercepted the first 5 data here.

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

The result is as follows:

#2. Add the currency symbol

when you When looking at this data, it's a little difficult to understand the scale of the numbers because you have 6 decimal points and some larger numbers. Additionally, it is unclear whether this is USD or another currency. We can solve this problem using DataFrame style.format.

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

The results are as follows: Using the format function, you can use all the functions of Python's string formatting tools on the data. In this case, we use ${0:,.2f} to put the leading dollar sign, add a comma and round the result to two 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}'))

The result is as follows:

3. Add the percentage

if we want To view total sales by month, we can use grouper to summarize by month and calculate each month's percentage of 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()

The results are as follows: In order to display this percentage more clearly, we'd better convert it into a percentage.

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

结果如下：

4. 突出显示数字

除了样式化数字，我们还可以设置 DataFrame 中的单元格样式。让我们用绿色突出显示最高的数字，用彩色突出显示最高、最低的数字。

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

结果如下：

5. 设置渐变色

另一个有用的函数是 background_gradient，它可以突出显示列中的值范围。

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

结果如下：

6. 设置数据条

pandas样式功能还支持在列内绘制条形图。

(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'))

结果如下：

7. 绘制迷你图

我认为这是一个很酷的功能。

import sparklines

def sparkline_str(x):
    bins=np.histogram(x)[0]
    sl = ''.join(sparklines(bins))
    return sl
    
sparkline_str.__name__ = "sparkline"
df.groupby('name')['quantity', 'ext price'].agg(['mean', sparkline_str])

结果如下：