pandas中的透视表详解

2020年10月22日 07:13 by wst

数据处理

---

从网上看了很多例子，总感觉摸不着它。这里单独对其进行整理，包含数据说明、函数说明、例子解析。

数据说明

这里使用的示例数据是一个销售漏斗表，内容如下：

全是英文，可能会懵掉（表头代表啥啊？内容代表啥？）。查了资料并根据自己的理解，大概意思如下：

如要获得以上文档，文末有获取方法。

 

函数说明

pandas.pivot_table(data, values=None, index=None, columns=None, aggfunc='mean', fill_value=None, margins=False, dropna=True, margins_name='All', observed=False)

参数说明：

在正常使用中，data为一个dataframe。dataframe本身具有pivot_table方法，df.pivot_table() 直接使用即可。

values:  需要对其进行聚合的列，即要透视查看的列，一般为数值列。

index：查看数据时用哪些维度（列）看，即需要分组的列。

columns：需要把行数据变为列数据的列。

aggfunc：聚合函数，分组后使用的聚合函数，比如求平均(np.mean)、总和(np.sum)。

fill_value：填充值，分组后某些肯能没有值，用什么填充。比如0，空字符''。

margins：是否需要边框，即分别对行、列求和，作为新行、新列附加到结果中。

dropna：是否删除空值。

margins_name：求和数据使用的列、行名。

observed：不常用，暂不说明，想了解的请看官方文档

 

例子解析

让我们带着问题看示例。问题如下：

1. 每个客户消费了多少钱？

2. 每个销售经理的业绩是多少？

3. 每一种产品的销售额是多少？

4. 每个用户买了几种商品，金额是多少？

 

数据预处理

df = pd.read_excel("sales-funnel.xlsx")
# 处理为类别数据，并设置顺序
df["Status"] = df["Status"].astype("category")
df["Status"].cat.set_categories(["won","pending","presented","declined"],inplace=True)
df['Account']=df['Account'].astype(str) # 账号不应参与计算，处理为字符串

这里处理的为类别数据，为什么要把Status列处理为类别数据？

官方给出的原因是：

1. 字符串变量由有限个值组成，这样处理后可以节省内存；

2.  自然顺序不同于逻辑顺序，比如列表【良，优，中，差】，逻辑顺序是【优，良，中，差】；

3. 告诉其他库，这个列要作为类别变量处理；

 

每个客户消费了多少钱

df['cost']=df['Quantity']*df['Price']
df.pivot_table(values='cost',index=['Account','Name'],aggfunc=sum).reset_index()
# 输出如下：
  Account                          Name    cost
0   141962                    Herman LLC  130000
1   146832                  Kiehn-Spinka  130000
2   163416                   Purdy-Kunde   30000
3   218895                     Kulas Inc   90000
4   239344                    Stokes LLC   15000
5   307599   Kassulke, Ondricka and Metz   21000
6   412290                 Jerde-Hilpert   10000
7   688981                   Keeling LLC  500000
8   714466               Trantow-Barrows   50000
9   729833                     Koepp Ltd  140000
10  737550  Fritsch, Russel and Anderson   35000
11  740150                    Barton LLC   35000

每个销售经理的业绩是多少

df.pivot_table(values='cost',index=['Manager'],aggfunc=sum).reset_index()
# 输出如下：
         Manager    cost
0   Debra Henley  350000
1  Fred Anderson  836000

每一种产品的销售额是多少

df.pivot_table(values='cost',index=['Product'],aggfunc=sum).reset_index()
# 输出如下：
       Product     cost
0          CPU  1100000
1  Maintenance    46000
2      Monitor    10000
3     Software    30000

每个用户买了几种商品，金额是多少

df.pivot_table(values='cost',index=['Account','Name'],columns='Product', aggfunc=sum, fill_value=0).reset_index() 
# 输出如下：
Product Account                          Name     CPU  Maintenance  Monitor  Software
0        141962                    Herman LLC  130000            0        0         0
1        146832                  Kiehn-Spinka  130000            0        0         0
2        163416                   Purdy-Kunde   30000            0        0         0
3        218895                     Kulas Inc   80000            0        0     10000
4        239344                    Stokes LLC       0         5000        0     10000
5        307599   Kassulke, Ondricka and Metz       0        21000        0         0
6        412290                 Jerde-Hilpert       0        10000        0         0
7        688981                   Keeling LLC  500000            0        0         0
8        714466               Trantow-Barrows   30000        10000        0     10000
9        729833                     Koepp Ltd  130000            0    10000         0
10       737550  Fritsch, Russel and Anderson   35000            0        0         0
11       740150                    Barton LLC   35000            0        0         0

 

附加问题解析

在实际问题中，经常出现分级索引的情况，在这里举个实际例子：

在广告业务中，请求、响应、曝光、点击在明细表中都是行数据。

即便是统计后，也仍然是行数据。

需求往往是某个广告位的请求(ckads)、响应(exads)、曝光(radac)、点击(rads)是多少？

gf = df.pivot_table(['pv'], index=['codeid', 'adfrom', 'slotid'], columns='actname',aggfunc="sum", fill_value=0).reset_index()
# gf内容如下：
                codeid adfrom            slotid     pv                        
actname                                          ckads   exads   radac    rads
0                           0                        3      38      38      38
1                           5  1021308925797542      5      41      65      44
2                           5  4091200173278730      0       0       1       1
3        (null)+110000      5                        3      47     415     400
4        (null)+110027      5                        0       2     415     407
..                 ...    ...               ...    ...     ...     ...     ...
785               open      5                    21998  664520      30  675396
786               open      5  2050238408812559      0       0  765187       0
787               open      5  3070057019496439      0       0  228653       0
788               open      5  7040073166303660      2      12       0      12
789               open      5  8030393060449846      0       0    9033       0

# 去除多层索引
kk = gf.columns.to_list()
kk = [(col[1] if col[0] == 'pv' else col[0]) for col in kk]
gf.columns = kk

# gf内容如下：
          codeid adfrom            slotid  ckads  exads  radac  rads
0                     0                        3     38     38    38
1                     5  1021308925797542      5     41     65    44
2                     5  4091200173278730      0      0      1     1
3  (null)+110000      5                        3     47    415   400
4  (null)+110027      5                        0      2    415   407

 

总结

这里主要通过示例对透视表的典型功能进行了解析，以及对多层索引的处理方法。

如果有其他问题，请留言；

如果你有更好的处理方法，也请告诉我，谢谢！

附：

关注如下公众号，回复“sales-funnel”获取表格数据。

---