利用 Pandas 轻松创建透视表：掌握 pivot_table 函数

介绍：

你或许曾在 Excel 中使用过数据透视表，但你可能不知道 Pandas 也提供了类似的功能，名为 pivot_table。尽管 pivot_table 非常实用，但为了格式化输出所需内容，你可能需要经常查阅其使用语法。因此，本文将重点介绍 Pandas 中的 pivot_table 函数，并指导你如何利用它进行数据分析。

如果你对这一概念不熟悉，可以参考维基百科的解释。顺便提一下，你知道微软为 PivotTable（透视表）注册了商标吗？以前我也不知道。但我们今天讨论的透视表并非 PivotTable。

作为额外福利，我还创建了一个简单的 pivot_table 备忘单，你可以在文章末尾找到。希望它对你有所帮助。如果确实有帮助，请告诉我。

数据：

使用 Pandas 中 pivot_table 的一个挑战是，你需要确保理解你的数据，并清楚知道你想通过透视表解决什么问题。虽然 pivot_table 看似简单，但它能够快速而强大地分析数据。

在本文中，我将追踪销售渠道（也称为销售漏斗）。基本问题是，一些销售周期很长（例如“企业软件”、“资本设备”等），而管理者希望更详细地了解一整年的情况。

典型问题包括：

• 本渠道收入是多少？
• 渠道的产品是什么？
• 谁在什么阶段有什么产品？
• 我们在年底前结束交易的可能性有多大？

许多公司可能使用 CRM 工具或其他销售软件来跟踪此过程。尽管他们可能拥有有效的工具来分析数据，但肯定有人需要将数据导出到 Excel，并使用透视表工具来总结数据。

使用 Pandas 的透视表将是个不错的选择，因为它具有以下优点：

• 更快（一旦设置完成）
• 自解释性强（通过查看代码，你能了解它做了什么）
• 生成报告或电子邮件更容易
• 更灵活，因为你可以定义定制的聚合函数

阅读数据：

首先，让我们搭建所需环境。

如果你想跟随我继续，可以下载此 Excel 文件。

<code>import pandas as pd import numpy as np</code>

版本提醒：

由于 pivot_table API 随时间变化，为了确保示例代码正常工作，请确保你安装了最新版本的 Pandas（>0.15）。本文还使用了 category 数据类型，也需要确保是最新版本。

首先，将我们销售渠道的数据读入数据框。

<code>df = pd.read_excel("../in/sales-funnel.xlsx") df.head()</code>

为了方便起见，我们将上表中的“Status”列定义为 category，并按我们希望的顺序设置。

虽然这并非必须，但这样做有助于在分析数据的整个过程中保持我们想要的顺序。

<code>df["Status"] = df["Status"].astype("category") df["Status"].cat.set_categories(["won","pending","presented","declined"], inplace=True)</code>

数据处理：

既然我们要建立数据透视表，最好一步一个脚印地进行。添加项目并检查每一步，以验证你得到的结果是否符合预期。为了找到最符合你需要的外观，不要害怕调整顺序和变量。

最简单的透视表必须有一个数据框和一个索引。在本例中，我们将使用“Name”列作为我们的索引。

<code>pd.pivot_table(df, index=["Name"])</code>

此外，你也可以有多个索引。实际上，大多数 pivot_table 参数都可以通过列表来接受多个值。

<code>pd.pivot_table(df, index=["Name","Rep","Manager"])</code>

虽然这很有趣，但并不是特别实用。我们可能希望通过将“Manager”和“Rep”设置为索引来查看结果。

要实现这一点，只需要改变索引即可。

<code>pd.pivot_table(df, index=["Manager","Rep"])</code>

你会发现，透视表相当智能，它已经开始通过将“Rep”列和“Manager”列进行对应分组来实现数据聚合和总结。现在，让我们一起看看数据透视表能为我们做些什么。

为此，“Account”和“Quantity”列对于我们来说并不重要。因此，通过在“values”域显式地定义我们关心的列，可以删除那些无关的列。

<code>pd.pivot_table(df, index=["Manager","Rep"], values=["Price"])</code>

“Price”列将自动计算数据的平均值，但我们也可以对该列元素进行计数或求和。要添加这些功能，使用 aggfunc 和 np.sum 就很容易实现。

<code>pd.pivot_table(df, index=["Manager","Rep"], values=["Price"], aggfunc=np.sum)</code>

aggfunc 可以包含许多函数。下面让我们尝试一种方法，即使用 numpy 中的 mean 和 len 函数进行计数。

<code>pd.pivot_table(df, index=["Manager","Rep"], values=["Price"], aggfunc=[np.mean,len])</code>

如果我们想通过不同产品来分析销售情况，那么变量“columns”将允许我们定义一个或多个列。

列 vs. 值：

在 pivot_table 中一个令人困惑的地方是“columns”和“values”的使用。请记住，变量“columns”是可选的，它提供一种额外的方法来分割你所关心的实际值。然而，聚合函数 aggfunc 最后是被应用到了变量“values”中你所列举的项目上。

<code>pd.pivot_table(df, index=["Manager","Rep"], values=["Price"], columns=["Product"], aggfunc=np.sum)</code>

然而，NaN 有点分散注意力。如果想移除它们，我们可以使用“fill_value”将其设置为0。

<code>pd.pivot_table(df, index=["Manager","Rep"], values=["Price"], columns=["Product"], aggfunc=np.sum, fill_value=0)</code>

其实，我觉得添加“Quantity”列将对我们有所帮助，所以将“Quantity”添加到“values”列表中。

<code>pd.pivot_table(df, index=["Manager","Rep"], values=["Price","Quantity"], columns=["Product"], aggfunc=np.sum, fill_value=0)</code>

<code>pd.pivot_table(df, index=["Manager","Rep","Product"], values=["Price","Quantity"], aggfunc=np.sum, fill_value=0)</code>

对于这个数据集，这种显示方式看起来更有意义。但如果我想查看一些总和数呢？“margins=True”就可以为我们实现这种功能。

<code>pd.pivot_table(df, index=["Manager","Rep","Product"], values=["Price","Quantity"], aggfunc=[np.sum,np.mean], fill_value=0, margins=True)</code>

下面，让我们以更高的管理者角度来分析此渠道。根据我们前面对 category 的定义，注意现在“Status”是如何排序的。

<code>pd.pivot_table(df, index=["Manager","Status"], values=["Price"], aggfunc=np.sum, fill_value=0, margins=True)</code>

一个很方便的特性是，为了对你选择的不同值执行不同的函数，你可以向 aggfunc 传递一个字典。不过，这样做有一个副作用，那就是必须将标签做的更加简洁才行。

<code>pd.pivot_table(df, index=["Manager","Status"], columns=["Product"], values=["Quantity","Price"], aggfunc={"Quantity":len,"Price":np.sum}, fill_value=0)</code>

此外，你也可以提供一系列的聚合函数，并将它们应用到“values”中的每个元素上。

<code>table = pd.pivot_table(df, index=["Manager","Status"], columns=["Product"], values=["Quantity","Price"], aggfunc={"Quantity":len,"Price":[np.sum,np.mean]}, fill_value=0) table</code>

也许，同时将这些东西全都放在一起会有点令人望而生畏，但是一旦你开始处理这些数据，并一步一步地添加新项目，你将能够领略到它是如何工作的。我一般的经验法则是，一旦你使用多个“group by”，那么你需要评估此时使用透视表是否是一种好的选择。

高级透视表过滤：

一旦你生成了需要的数据，数据将存在于数据框中。因此，你可以使用自定义的标准数据框函数来对其进行过滤。

如果你只想查看一个管理者（例如 Debra Henley）的数据，可以这样：

<code>table.query('Manager == ["Debra Henley"]')</code>

我们也可以查看所有的暂停（pending）和成功（won）的交易：

<code>table.query('Status == ["pending","won"]')</code>

这是 pivot_table 中一个非常强大的特性，所以一旦你得到了你需要的 pivot_table 格式的数据，就不要忘了此时你就拥有了 Pandas 的强大威力。

备忘单：

为了试图总结所有这一切，我创建了一个备忘单，希望它能够帮助你记住如何使用 Pandas 的 pivot_table。