盘一盘 Python 特别篇 15 - Pivot Table

从功能上讲，Pandas 中用透视表 (pivot table) 和 Excel 里面的透视表是一样的。透视表是一种做多维数据分析的工具，还记得 Pandas 的 split-apply-combine 三部曲吗？首先用 groupby 分组，再平行将某个函数应用到各组上，最后自动连接成一个总表。今天介绍的 pivot_table() 函数可以将上面“拆分-应用-结合”三个步骤用一行来完成。

先看一张图：

Pivot 字面意思是支点，即上图中的 index 和 columns 指定的行和列标签，支点可想理解成数据 (values) 在哪个维度上做整合 (aggfunc)，再吧 NaN 值用 fill_value 替代，按行按列加总 (margin=True)。

理解还有些难度？没问题，看完本帖就懂了。

数据

首先从 csv 读数据。

df = pd.read_csv('PB Sales.csv')df

设置“单行”为 Pivot

创建透视表的 pivot_table() 函数里面的参数设置很多，学习它最有效的方式是每一步设置一个参数，检查结果是否符合预期。

先从最简单的语法开始，只设置 index='Account'，通用语法如下：

pd.pivot_table(df, index=label_str)

pd.pivot_table( df, index='Account' )

从上表结果看，Price 和 Quantity 两列按照 Account 以某种方式合并了。看原 df 最后两行，账户 MM729833 合并成一行，对应的 Price 和 Quantity 是 42500 和 200，看来某种方式是求平均。现在大概可以猜出 pivot_table() 函数中有个参数用来设置整合方式，而默认值为平均。

设置“多行”为 Pivot

上例设置单个 index，接下来看看设置多个 index 的结果是什么样的。这时用列表来存储多个 index。通用语法如下：

pd.pivot_table(df, index=label_list)

pd.pivot_table( df, index=['Account','Trader','Counterparty'] )

上表显示着每个 Account 对应的交易员和交易对手的信息，但有点乱。

一个交易员管理一个或多个账户，多个交易员可以和一个交易对手交易，改变 index 里面的标签顺序，先按 Counterparty 合并，再按 Trader 合并。

pd.pivot_table( df, index=['Counterparty','Trader'] )

到目前为止，我们只设置了 index，那为什么只在 Price 和 Quantity 两列上做整合呢？因为这两列的值是数值型 (int, float)，而其他例的值是非数值型 (object)，用 df.dtypes 就可看出。

df.dtypes

Account object
Trader object
Counterparty object
Ticker object
Quantity int64
Value int64
Category object
dtype: object

设定被整合的数据

如果只看 Price 列下的整合结果，只需设置 values='Price' 或者 values=['Price']，通用语法如下：

pd.pivot_table(df, index=label_list, values=label_list)

pd.pivot_table( df, index=['Counterparty','Trader'], values='Value' )

设置整合函数

默认整合函数是求平均，如果要用求和的函数需要设置 aggfunc=np.sum，通用语法为

pd.pivot_table(df, index=label_list, values=label_list, aggfunc=func)

pd.pivot_table( df, index=['Counterparty','Trader'],                     values=['Value'],                     aggfunc=np.sum )

aggfunc 参数可以被设置为多个函数，用列表储存，通用语法为

pd.pivot_table(df, index=label_list, values=label_list, aggfunc=func_list)

pd.pivot_table( df, index=['Counterparty','Trader'],                     values=['Value'],                    aggfunc=[len, np.sum] )

设定“列” 为 Pivot 

如果进一步想看按产品类别划分后的整合结果，可以设置 columns=['Category]，通用语法为，

pd.pivot_table(df, index=label_list, values=label_list, columns=label_list, aggfunc=func_list)

pd.pivot_table( df, index=['Counterparty','Trader'],                     values=['Value'],                    columns=['Category'],                    aggfunc=[len, np.sum] )

上表结果中的 NaN 不好看，可设置 fill_value=0 用零替代。

pd.pivot_table( df, index=['Counterparty','Trader'], values=['Value'], columns=['Category'], aggfunc=[len, np.sum], fill_value=0 )

除此之外还可以把产品类别放在 index 中，改变结果的展示方式而已 (那些结果为零都没显示了，看起来更舒服点)。

pd.pivot_table( df, index=['Counterparty','Trader','Category'],                     values=['Value'],                    aggfunc=[len, np.sum],                    fill_value=0 )

设置“显示”总和

要看总计怎么办？设置 margins=True 就可以了。

pd.pivot_table( df, index=['Counterparty','Trader','Category'], values=['Value','Quantity'], aggfunc=[len, np.sum], fill_value=0, margins=True )

整合函数的不同设置方式

aggfunc 参数还可以传进一个字典来实现不同列下应用不同的整合函数，语法如下：

aggfunc = {col_1:func_1, col_2:func_2, ... col_n:func_n}

pd.pivot_table( df, index=['Counterparty','Trader','Category'], values=['Value','Quantity'], aggfunc={'Value':np.sum, 'Quantity':len}, fill_value=0, margins=True )

再进一步，不同列还可以应用多个函数，只需把函数名称变成函数列表就可以了。语法如下：

aggfunc = {col_1:func_1, col_2:func_list, ... col_n:func_n}

假设第二列传入一个函数列表。

pd.pivot_table( df, index=['Counterparty','Trader','Category'], values=['Value','Quantity'], aggfunc={'Value':[np.sum, min, max], 'Quantity':len}, fill_value=0 )

查询终表

一次性做出这样的表不容易，但按步摸索 (一次设置一次参数) 的方式却很简单。

一旦得到最终结果，它本质还是个数据帧，因此可以使用所有标配函数。下例用 query() 函数来查询名叫 Steven Wang 和 Sherry Zhang 的交易员。

table = pd.pivot_table( df, index=['Counterparty','Trader','Category'],                             values=['Value','Quantity'],                            aggfunc={'Value':np.sum, 'Quantity':len},                            fill_value=0 )table.query('Trader == ['Steven Wang', 'Sherry Zhang']')

查询所有期权和基金产品相关的信息。

table.query('Category == ['Fund', 'Option']')

总结，一图胜千言！现在再看下图可视化 pivot_table() 函数的用法，是不是都能理解了？

Stay Tuned！