这是系列博客文章的最后一篇,教您如何使用 Python 代码处理数据表格。这篇文章的主题是用于准备数据以进行分析的常见操作——合并表格。 

作为 Microsoft Excel 分析师,您可能已经使用过 Excel 函数(如 VLOOKUP()XLOOKUP())来合并数据表格。

在 Python 世界中也是如此。合并数据表格(即“连接”)是非常常见的操作。在这方面,使用 Python 连接数据表格为您提供三个显著的优势

  • pandas 库提供了一整套用于准备数据以进行高级分析的各种表格连接操作。
  • 更高级别地控制数据表格的连接方式。 
  • 标准化您的连接逻辑,以便其他人可以快速重现它。

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

本系列文章的每篇文章都有一个随附的 Microsoft Excel 工作簿供您下载并用于提升您的技能。这篇文章的工作簿可在此处下载

为了方便起见,以下是本系列博客文章的所有链接

注意:要重现本文中的示例,请安装 Excel 中的 Python 试用版

在 Excel 中合并表格

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

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

数据表格

与之前的博文中使用的数据表格相比,这篇博文将使用更真实的数据表格示例。

首先是 InternetSales 表格

图 1 – InternetSales Excel 表格

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

接下来是 ProductInfo 表格

图 2 – ProductInfo Excel 表格

这两个表格代表了连接数据的经典场景 – InternetSalesProductKey 列允许查找存储在 ProductInfo 表格中的产品文本描述。

传统上,VLOOKUP() 函数一直用于这种场景。

使用 VLOOKUP 合并表格

假设您想向 InternetSales 添加 ProductCategoryName 列,以方便您执行数据分析。实现此目的的 Excel 代码非常简单

图 3 – 使用 VLOOKUP 连接表格的 Excel 代码

<Enter> 键将 Excel 代码向下填充到 InternetSales 表格的行中。查看 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_salesproduct_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 中的数据匹配。

虽然使用 DataFrame 可以使用更多类型的连接,但左连接和内连接是最常用的。 

更多连接的优点

这篇博文是对连接 pandas DataFrame 的介绍。merge() 方法提供了比本文可以涵盖的更多的功能(例如,使用多个连接条件)。 

如果您有兴趣了解更多信息,请务必查看在线文档

下一步是什么?

本博客系列为使用 Python 处理数据表格奠定了良好的基础,但还有很多东西需要学习。请将以下内容视为下一步学习的分步指南。

首先,关于如何使用 pandas 准备数据以进行分析,还有很多东西需要学习。如果您喜欢这个博客系列,请查看我的 Anaconda 认证课程Excel 中的 Python 数据分析。 

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

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

最后,使用 Python 开启了高级分析的世界。开始您的高级分析之旅的一个好地方是人工智能和机器学习入门

我希望您发现这个博客系列很有用,并对在 Microsoft Excel 中运行 Python 代码的可能性感到兴奋。

下次再见,祝您保持健康,数据挖掘愉快!

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

个人简介

Dave Langer 创立了 Dave on Data,他在那里提供旨在帮助任何专业人士发展数据分析技能的培训。多年来,Dave 培训了数千名专业人士。此前,Dave 提供了洞察