Excel 分析师的 Python：组合表格

这是本系列博文的最后一篇，它将教你如何使用 Python 代码处理数据表格。本博文将介绍一个用于准备数据进行分析的常见操作——组合表格。

作为 Microsoft Excel 分析师，您可能使用过 Excel 函数，如 VLOOKUP() 和 XLOOKUP() 来组合数据表格。

Python 世界也是如此。组合数据表格（即“联接”）是一种非常常见的操作。使用 Python 联接数据表格在这方面为您提供了三个显著的优势

• pandas 库提供了一套完整的各种表格联接操作，这些操作通常用于准备数据以进行高级分析。
• 对如何联接数据表格有更大的控制权。
• 标准化联接逻辑，以便其他人可以快速复制它。

如果您不熟悉 pandas 库，请查看本博文系列的第 1 部分。

本系列中的每篇博文都附带一个 Microsoft Excel 工作簿，您可以下载并使用它来提升您的技能。本博文的工作簿可供此处下载。

为了方便起见，以下列出了本系列中所有博文的链接

• 第 1 部分 - 基础知识
• 第 2 部分 - 使用列
• 第 3 部分 - 过滤表格
• 第 4 部分 - 数据清理和整理
• 第 5 部分 - 组合表格（本博文）

注意：要复制本博文中的示例，请安装 Excel 中的 Python 试用版。

在 Excel 中组合表格

虽然不是组合数据表格的唯一方法，但 Excel 的 VLOOKUP() 函数在历史上使用得最多。其他选项包括 XLOOKUP()  和 INDEX() 与 MATCH() 函数的组合。

本博文将使用 VLOOKUP() 来探讨联接数据表格的相关概念，然后将这些概念映射到 pandas DataFrame 的功能。

数据表格

本博文将使用比以前博文使用的更现实的数据表格示例。

首先是 InternetSales 表格

图 1 - InternetSales Excel 表格

如图 1 所示，InternetSales 表格更能代表您在现实世界中可能看到的数据。例如，如果数据来自数据仓库。

接下来是 ProductInfo 表格

图 2 - ProductInfo Excel 表格

这两个表格代表了联接数据的典型场景 - InternetSales 的 ProductKey 列允许查找存储在 ProductInfo 表格中的产品的文本描述。

传统上，VLOOKUP() 函数已被用于此场景。

使用 VLOOKUP 组合表格

假设您想向 InternetSales 添加 ProductCategoryName 列，以方便您想要执行的数据分析。实现这一点的 Excel 代码很简单

图 3 - 使用 VLOOKUP 联接表格的 Excel 代码

按下 <Enter> 键将在 InternetSales 表格的行中填充 Excel 代码。查看 ProductCategoryName 列的筛选器对话框，显示了在联接中填充到该列的所有唯一值

图 4 - 新 ProductCategoryName 列的筛选器对话框

虽然这是一个人为的示例，但它为将您的 Excel 知识映射到使用 pandas 库联接 DataFrame 提供了一个坚实的基础。

映射您的 VLOOKUP 知识

将我们的知识映射到第一个概念是将要联接的两个数据表格视为联接的“右侧”和“左侧”。

在上面的示例中，InternetSales 是左侧表格，ProductInfo 是右侧表格：

图 5 - 联接的左侧和右侧表格

下一个概念称为“联接条件”。使用图 5 中所示的 Excel 代码，联接条件是左侧表格的 ProductKey 列的值（例如，单元格 D3）与右侧表格的 ProductKey 列的值（即，单元格 $K$3:$K$199）匹配的位置。

在 Excel 中运行联接时，请注意新 ProductCategoryName 列中的并非每个单元格都包含值

图 6 - ProductCategoryName 列中的缺失数据

VLOOKUP() 函数的行为是在联接条件匹配时返回值，在没有联接条件匹配时返回无值（即，#N/A）。这种行为在许多编程语言（例如，SQL、Python、R 等）中称为“左联接”。

顺便说一下 - 当使用 pandas 时，值的缺失通常表示为“null”或“NaN”（“非数字”。

最后，请注意 VLOOKUP() 函数默认情况下只联接一列数据。正如您将看到的，这并不是联接 pandas DataFrame 的默认行为。

在 Python 中组合表格

在探讨如何执行联接之前，必须将 Excel 表格数据加载到 pandas DataFrame 对象中。

首先，使用 PY() 函数创建您的 Python 公式

图 7 - 调用 Excel PY() 函数

接下来，当您键入“（”时，它将指示它包含 Python 代码

图 8 - Excel Python 单元格

以下代码将 InternetSales Excel 表格加载为 pandas DataFrame

图 9 - 从 InternetSales Excel 表格构建 DataFrame

在键盘上按下 <Ctrl+Enter> 将执行代码。假设代码输入正确，您将看到如下内容

图 10 - 成功将 InternetSales 数据加载为 DataFrame

单击卡片将提供 DataFrame 对象的预览

图 11 - internet_sales DataFrame 的卡片

需要对 ProductInfo Excel 表格重复此过程。以下是代码

图 12 - 从 ProductInfo Excel 表格构建 DataFrame

构建了 DataFrame 对象后，就可以执行一些联接操作了。

左联接

pandas DataFrame 类有一个 join() 和一个 merge() 方法来组合 DataFrame。事实证明，merge() 方法在概念上更像 Microsoft Excel，因此在本博文中将使用 merge() 而不是 join()。

顺便说一下 - 如果您想了解更多信息，请查看有关merge() 和join() 的在线文档。

如前一节所述，组合表格最常用的方法之一是使用左联接（例如，类似于 VLOOKUP）。在概念上，使用 pandas DataFrame 执行左联接的方式如下

• 右侧表格的数据将在匹配的地方添加到左侧表格。
• 在没有匹配的地方，将向左侧表格添加“NaN”（即，“非数字”）。
• 匹配使用指定的联接条件。

以下 Python 代码使用 VLOOKUP() 实现前一节中显示的相同左联接

图 13 - 使用左联接组合 internet_sales 和 product_info DataFrame

注意 - 图 13 中所示的代码显示在多行上，因为单元格使用 Excel 功能区设置为 换行。

如图 13 所示，merge() 方法返回一个包含组合数据的新的 DataFrame 对象。原始 DataFrame 保持不变。

执行代码并单击单元格的卡片将提供 sales_left_merge DataFrame 的预览

图 14 - sales_left_merge DataFrame 的卡片

注意 - Python “NaN” 值在 Excel 中显示为 #NUM!，表示缺失值。

将图 14 中所示的结果与图 3 中 VLOOKUP() 的结果进行比较。默认情况下，VLOOKUP() 只联接右侧表格的一列，而 DataFrame 的 merge() 方法联接右侧表格的所有列。

左联接在数据分析场景中非常常用，但它们并不是唯一常用的联接。

内联接

数据分析中另一种常用的联接是“内联接”。在概念上，使用 pandas DataFrame 执行内联接的方式如下

• 右侧表格的数据将在匹配的地方添加到左侧表格。
• 在没有匹配的地方，将向左侧表格添加“NaN”（即，“非数字”）。
• 左侧表格中所有包含右侧表格列的 NaN 的行都将被删除。
• 匹配使用指定的联接条件。

左联接和内联接之间的区别在于保留的行数。对于左联接，将保留左侧表格中的所有行，而内联接将删除左侧表格中没有匹配项的行。

以下是使用 internet_sales 和 product_info DataFrame 之间的内联接的代码

图 15 - 使用内联接组合 internet_sales 和 product_info DataFrame

注意 - 图 15 中所示的代码显示在多行上，因为单元格使用 Excel 功能区设置为 换行。

内联接代码运行后，单击卡片将提供 sales_inner_merge DataFrame 的以下预览

图 16 - sales_inner_merge DataFrame 的卡片

比较图 11、图 14 和图 16 中的卡片。图 11 和图 14 的行数相同，都是 60,398 行。

相比之下，图 16 的行数为 54,970 行。这意味着 internet_sales DataFrame 中的 5,428 行无法与 product_info DataFrame 中的数据匹配。

虽然使用 DataFrames 可以进行更多类型的连接，但大多数情况下使用左连接和内连接。

更多连接技巧

这篇文章介绍了如何连接 pandas DataFrames。merge() 方法提供了比本文中介绍的更多功能（例如，使用多个连接条件）。

如果您有兴趣了解更多信息，请查看 在线文档。

下一步是什么？

本系列文章为使用 Python 处理数据表奠定了良好的基础，但还有很多内容需要学习。以下是一份逐步学习指南。

首先，还有很多关于如何使用 pandas 准备数据进行分析的内容需要学习。如果您喜欢本系列文章，请查看我的 Anaconda 认证课程 Excel 中使用 Python 进行数据分析。

其次，学习更多关于数据清洗和整理的知识将对您有帮助。最好的分析来自最好的数据。要了解更多信息，Anaconda 提供 使用 pandas 进行数据清洗 课程。

接下来是数据可视化。Python 提供了轻松创建强大的数据可视化的能力，使您可以从数据中获取洞察力。数据可视化不仅本身是一种分析技术，而且也是进行高级分析的关键步骤。要了解更多信息，请查看 Anaconda 的课程 Python 数据可视化入门。

最后，使用 Python 打开了高级分析的世界。开始高级分析之旅的最佳地点是 机器学习入门。

我希望您发现本系列文章有用，并对在 Microsoft Excel 中运行 Python 代码的可能性感到兴奋。

下次再见，保持健康，快乐地进行数据探索！

免责声明：截至本文发布之日，Microsoft Excel 中的 Python 集成处于测试阶段。功能和功能可能会发生变化。 如果您在此页面上发现错误，请随时联系我们。

简介

Dave Langer 是 Dave on Data 的创始人，他在那里提供针对任何专业人士开发数据分析技能的培训。多年来，Dave 已经培训了数千名专业人士。此前，Dave 在 Schedulicity、Data Science Dojo 和 Microsoft 提供了推动业务战略的见解。