Python 用于 Excel 分析师：数据清理和整理

这是关于如何使用 Python 代码处理数据表的一系列博客文章中的第四篇。这篇文章的主题是数据分析中最关键的操作之一：清理和整理数据。

如果您不熟悉，以下是来自维基百科的定义

“数据整理，有时被称为数据清理，是指将数据从一种“原始”数据形式转换为另一种形式的过程，目的是使其更适合各种下游用途，例如分析。”

作为 Excel 分析师，您无疑已经多次整理数据。整理数据是您获取最具影响力数据分析的原材料的方式。

使用 Python pandas 库清理和整理数据为您提供了两大优势：

• 高级分析（如机器学习）所需的数据整理技术
• 标准化您的整理过程，以便其他人可以快速复制它

如果您不熟悉 pandas 库，请查看本博客系列的第 1 部分：基础知识。

本系列中的每篇文章都附带一个 Microsoft Excel 工作簿供下载和使用，以培养您的技能。本文的工作簿可在此处下载。

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

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

注意：要复制本文中的示例，请安装Python in Excel 试用版。如果您喜欢本博客系列，请查看我的 Anaconda 认证课程，使用 Python 在 Excel 中进行数据分析。

添加列

数据整理最常见的形式之一是添加根据一个或多个现有表列中的数据创建的新列。此类数据整理的示例包括

• 执行计算（例如，从一个列中减去另一个列）
• 从字符串列中提取子字符串
• 创建二进制指示器（例如，数据最初丢失）

添加列的目的是提高数据对特定分析技术的实用性。

例如，在机器学习中，此过程称为“特征工程”。特征工程旨在创建对构建预测模型最有用数据表示形式。

在 Excel 中添加列

InternetSales 表提供了关于销售订单财务方面的两个列：TotalProductCost 和 SalesAmount。添加一个新列（GrossProfit）将提供在许多分析中很有用的其他信息。

使用 Microsoft Excel 添加新列的过程很简单

创建 GrossProfit 列后，需要使用数据填充它。GrossProfit 的值应通过从表的每一行的 SalesAmount 中减去 TotalProductCost 来计算。

Excel 用户填充 GrossProfit 列的最常见方法是使用单元格引用公式

Microsoft Excel 还支持使用结构化引用。例如，GrossProfit 列可以使用基于 TotalProductCost 和 SalesAmount 列的结构化引用的公式填充

使用图 3 中的公式并按<enter> 键会自动为 InternetSales 表的每一行填充公式

向 pandas DataFrame 添加列在概念上类似于使用 Microsoft Excel 结构化引用公式。

使用 Python 添加列

以下是如何向 InternetSales 表添加 GrossProfit 列的示例

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

接下来，当您键入“(“ 时，单元格将指示它包含 Python 代码

以下 Python 代码添加了 GrossProfit 列，并使用 internet_sales DataFrame 的每一行的计算值填充它

注意：图 7 中显示的代码出现在多行上，因为单元格设置为使用 Excel 功能区中的自动换行。

从概念上讲，图 7 中的代码的工作原理如下

1. 代码 internet_sales[‘GrossProfit’] 告诉 internet_sales DataFrame 您要访问 GrossProfit 列。
2. 由于 internet_sales DataFrame 不存在 GrossProfit 列，因此等号被解释为创建新列。
3. 等号右侧的代码逐行应用。
4. GrossProfit 使用每行的 SalesAmount 减去 TotalProductCost 的计算值填充。

在键盘上按<Ctrl+Enter> 执行 Python 公式，在 Excel 工作表中生成以下内容

由于图 7 中的 Python 代码没有返回任何内容（即，代码更改了 internet_sales DataFrame），因此图 8 中显示的 NoneType 是预期的。

运行以下 Python 代码将允许您检查已更改的 DataFrame

要查看已更改的 DataFrame，请使用鼠标悬停在卡片上

单击卡片将显示 internet_sales 的内容

如图 11 所示，Python 代码产生的输出与图 3 中的 Excel 结构化引用公式相同。

考虑图 7 中的代码。当执行此代码时，计算会自动逐行执行。这是 pandas DataFrame 的“矢量化”行为。

作为 Excel 分析师，您熟悉矢量化：它是 Excel 表格的默认行为。对 pandas DataFrame 的矢量化支持使得在 Python 中使用数据表变得非常容易。

在 Python 中清理数据

上一节介绍了最常见的数据整理场景之一：添加新列。本节将介绍另一种常见的数据整理场景：清理现有列中的数据。

从概念上讲，清理数据包括三个步骤

1. 识别需要清理的列
2. 对于需要清理的列，执行各种清理操作
3. 将原始列数据覆盖为需要清理的列的清理数据

识别需要清理的列的最快方法是获取 DataFrame 的摘要。

DataFrame 摘要

DataFrame 提供了 info() 方法，用于为您提供 DataFrame 内容的摘要

执行图 12 中的代码不会返回任何内容。相反，调用 info() 方法将触发 Excel 打开“诊断”窗格以显示 DataFrame 摘要：

info() 方法提供了有用的信息，可以帮助您识别需要清理的列。查看图 13，提供了以下信息

• DataFrame 中有 60,398 个条目（即，行）。
• DataFrame 中有九列。
• 每列都有 60,398 个非空值。
• 以下列是数字：SalesOrderLineNumber、OrderQuantity、TotalProductCost、SalesAmount 和 GrossProfit。
• OrderDate 列是日期时间。
• 以下列是字符串：SalesOrderNumber、ProductSubcategoryName 和 ProductName

首先要识别的事情之一是是否存在任何缺失值。在大多数分析中，必须清理缺失数据（例如，用默认值替换）。

在 info() 方法输出中，缺失数据表示为 null。由于 DataFrame 的每一列的非空值数量与行数完全相同，因此没有数据丢失。

下一步是对数字数据和字符串数据进行分析。

分析数字数据

由于 internet_sales DataFrame 的数字列中没有缺失数据，因此您需要确定数字值是否适合业务流程。

如本博客系列的第 2 部分所述，分析数字数据的最快方法是使用 pandas Series（即，列）的 describe() 方法。

DataFrame 也有一个 describe() 方法，您可以使用它

DataFrame 的 describe() 方法返回一个包含概要分析详细信息的 DataFrame。单击公式单元格中的卡片将显示概要分析结果。

使用图 15 中描述的信息，您可以快速回答以下问题

• 是否有任何最小/最大值对于业务流程没有意义？
• 值的分布（即分布）对于业务流程是否有意义？

对上述问题的回答可以识别可能需要数据清理的列（例如，替换极值或删除包含极值的行）。

例如，OrderQuantity 列仅包含值 1。根据业务流程，这可能是预期的，也可能表示数据质量问题。

概要分析字符串数据

字符串数据的列通常需要清理，尤其是在数据由人工输入时（例如，街道地址）。

在概要分析字符串数据方面，pandas Series（即列）的 value_counts() 方法是了解字符串数据值的最佳方法。

value_counts() 方法返回一个 Series。返回的 Series 对象包含列中包含的唯一字符串值的计数。默认情况下，计数值按降序排列。

单击公式单元格的卡片将显示 Series 数据。

如图 17 所示，pandas DataFrame 和 Series 对象默认显示前五行和后五行。

在这种情况下，这并不十分有用，因此另一种方法是告诉 Excel 将 Series 数据的全部内容直接返回到工作表中，作为 Excel Value。

更改 Python 公式单元格输出会生成许多行数据。

图 19 描述了清理字符串数据中常见的场景：使用了特定的格式。例如，各种类型的自行车（例如，“Mountain-100 Black, 42”）遵循型号、颜色和尺寸的格式。

典型的清理数据操作是转换这些字符串格式。

清理数据

假设您有兴趣对自行车销售进行分析，但自行车颜色不是一个因素。ProductName 列的当前格式包含颜色，这将使这些分析变得困难。

清理 ProductName 列以删除颜色将使您能够进行必要的分析。正如在本博客系列的 第 2 部分 中所示，实现此目标的最简单方法是对 ProductName 字符串列使用 replace() 方法。

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

查看数据清理结果的最简单方法是再次使用 value_counts() 方法，并将 Series 对象输出返回到工作表中，作为 Excel Value。

图 22 描述了如何使用清理后的 ProductName 数据来分析与自行车颜色无关的自行车销售。

pandas Series 数据类型提供了一个广泛的库来处理字符串数据。有关更多信息，请查看 在线文档。

注意：本文中使用的清理数据示例很常见，但当然不是清理数据的唯一方法。有关清理数据的更多资源，请查看这些 Anaconda 课程：pandas 数据分析入门 和 使用 pandas 清理数据。

下一步是什么？

这篇文章简要介绍了使用 pandas 进行数据整理。

pandas 库提供了大量用于清理和整理数据的功能。这包括您过去在 Microsoft Excel 中使用过的一切功能，以及更多功能。

通常，大多数数据分析 Python 代码都专注于获取、清理和整理数据。培养 Python 数据整理技能将对您大有帮助。

本系列的最后一篇文章将介绍创建最佳数据分析的另一个重要操作：连接数据表（想想 VLOOKUP）。使用 Python 连接数据表可以在这方面为您提供三个显著优势。

• 一整套用于准备高级分析数据的各种常用表连接操作
• 对如何连接数据表有更多控制权
• 标准化您的连接逻辑，以便其他人可以快速复制它

如果您想了解有关使用 pandas 处理数据表的更多信息，请参加 pandas 数据分析入门 的初学者课程，并查看官方的 pandas 用户指南。

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

作者介绍

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