Python Excel 指南（二）

原文：zh.annas-archive.org/md5/4cbcfc6fe67ce954227c7b15eef2b4c0

译者：飞龙

协议：CC BY-NC-SA 4.0

第五章：使用 pandas 进行数据分析

本章将为您介绍 pandas，即 Python 数据分析库，或者——我喜欢这样说——具有超能力的基于 Python 的电子表格。它非常强大，以至于我曾与一些公司合作时，他们完全放弃了 Excel，而是用 Jupyter 笔记本和 pandas 的组合来替代它。然而，作为本书的读者，我假设您会继续使用 Excel，在这种情况下，pandas 将作为在电子表格中获取数据的接口。pandas 使在 Excel 中特别痛苦的任务变得更加简单、快速和少出错。其中一些任务包括从外部源获取大型数据集以及处理统计数据、时间序列和交互式图表。pandas 最重要的超能力是向量化和数据对齐。正如我们在上一章中看到的使用 NumPy 数组一样，向量化使您能够编写简洁的基于数组的代码，而数据对齐则确保在处理多个数据集时不会出现数据不匹配的情况。

这一章涵盖了整个数据分析过程：从清洗和准备数据开始，然后通过聚合、描述统计和可视化来理解更大的数据集。在本章末尾，我们将看到如何使用 pandas 导入和导出数据。但首先，让我们从介绍 pandas 的主要数据结构开始：DataFrame 和 Series！

DataFrame 和 Series

DataFrame 和 Series 是 pandas 中的核心数据结构。在本节中，我将介绍它们，并重点介绍 DataFrame 的主要组成部分：索引、列和数据。DataFrame 类似于二维 NumPy 数组，但它带有列和行标签，每列可以容纳不同的数据类型。通过从 DataFrame 中提取单列或单行，您会得到一个一维 Series。同样，Series 类似于带有标签的一维 NumPy 数组。当您查看图 5-1 中 DataFrame 的结构时，您会很容易想象到，DataFrame 就是您基于 Python 的电子表格。

图 5-1. 一个 pandas Series 和 DataFrame

要展示从电子表格转换到 DataFrame 有多容易，请考虑下面的 Excel 表格图 5-2，它显示了在线课程的参与者及其分数。您可以在伴随仓库的 xl 文件夹中找到相应的文件 course_participants.xlsx。

图 5-2. course_participants.xlsx

要在 Python 中使用这个 Excel 表格，首先导入 pandas，然后使用它的read_excel函数，该函数返回一个 DataFrame：

In``[``1``]:``import``pandas``as``pd

In``[``2``]:``pd``.``read_excel``(``"xl/course_participants.xlsx"``)

Out[2]: user_id name age country score continent 0 1001 Mark 55 Italy 4.5 Europe 1 1000 John 33 USA 6.7 America 2 1002 Tim 41 USA 3.9 America 3 1003 Jenny 12 Germany 9.0 Europe

PYTHON 3.9 下的 READ_EXCEL 函数

如果你在使用 Python 3.9 或更高版本运行pd.read_excel，请确保至少使用 pandas 1.2，否则在读取 xlsx 文件时会出错。

如果你在 Jupyter 笔记本中运行这段代码，DataFrame 将以 HTML 表格的形式进行漂亮的格式化，这使得它与 Excel 中的表格更加接近。我将在第七章中详细介绍使用 pandas 读写 Excel 文件，因此这只是一个介绍性的示例，展示电子表格和 DataFrame 确实非常相似。现在让我们从头开始重新创建这个 DataFrame，而不是从 Excel 文件中读取它：创建 DataFrame 的一种方法是提供数据作为嵌套列表，并为columns和index提供值：

In``[``3``]:``data``=``[[``"Mark"``,``55``,``"Italy"``,``4.5``,``"Europe"``],``[``"John"``,``33``,``"USA"``,``6.7``,``"America"``],``[``"Tim"``,``41``,``"USA"``,``3.9``,``"America"``],``[``"Jenny"``,``12``,``"Germany"``,``9.0``,``"Europe"``]]``df``=``pd``.``DataFrame``(``data``=``data``,``columns``=``[``"name"``,``"age"``,``"country"``,``"score"``,``"continent"``],``index``=``[``1001``,``1000``,``1002``,``1003``])``df

Out[3]: name age country score continent 1001 Mark 55 Italy 4.5 Europe 1000 John 33 USA 6.7 America 1002 Tim 41 USA 3.9 America 1003 Jenny 12 Germany 9.0 Europe

通过调用info方法，您将获得一些基本信息，最重要的是数据点的数量和每列的数据类型：

In``[``4``]:``df``.``info``()

<class 'pandas.core.frame.DataFrame'> Int64Index: 4 entries, 1001 to 1003 Data columns (total 5 columns): # Column Non-Null Count Dtype --- ------ -------------- ----- 0 name 4 non-null object 1 age 4 non-null int64 2 country 4 non-null object 3 score 4 non-null float64 4 continent 4 non-null object dtypes: float64(1), int64(1), object(3) memory usage: 192.0+ bytes

如果你只对列的数据类型感兴趣，请运行df.dtypes。字符串或混合数据类型的列将具有数据类型object。1 现在让我们更详细地看一下 DataFrame 的索引和列。

索引

DataFrame 的行标签称为索引。如果没有有意义的索引，请在构造 DataFrame 时将其省略。pandas 将自动创建从零开始的整数索引。我们在从 Excel 文件读取 DataFrame 的第一个示例中看到了这一点。索引将允许 pandas 更快地查找数据，并对许多常见操作至关重要，例如合并两个 DataFrame。您可以像以下方式访问索引对象：

In``[``5``]:``df``.``index

Out[5]: Int64Index([1001, 1000, 1002, 1003], dtype='int64')

如果有意义，给索引起个名字。让我们按照 Excel 表格的方式，并给它命名为user_id：

In``[``6``]:``df``.``index``.``name``=``"user_id"``df

Out[6]: name age country score continent user_id 1001 Mark 55 Italy 4.5 Europe 1000 John 33 USA 6.7 America 1002 Tim 41 USA 3.9 America 1003 Jenny 12 Germany 9.0 Europe

与数据库的主键不同，DataFrame 索引可以具有重复项，但在这种情况下查找值可能较慢。要将索引转换为常规列，请使用reset_index，要设置新索引，请使用set_index。如果您不想在设置新索引时丢失现有索引，请确保首先重置它：

In``[``7``]:``# "reset_index"将索引转换为列，用默认索引替换``# 从最开始加载的 DataFrame 对应的数据框``df``.``reset_index``()

Out[7]: user_id name age country score continent 0 1001 Mark 55 Italy 4.5 Europe 1 1000 John 33 USA 6.7 America 2 1002 Tim 41 USA 3.9 America 3 1003 Jenny 12 Germany 9.0 Europe

In``[``8``]:``# "reset_index"将"user_id"转换为常规列，而``# "set_index"将列"name"转换为索引``df``.``reset_index``()``.``set_index``(``"name"``)

Out[8]: user_id age country score continent name Mark 1001 55 Italy 4.5 Europe John 1000 33 USA 6.7 America Tim 1002 41 USA 3.9 America Jenny 1003 12 Germany 9.0 Europe

使用df.reset_index().set_index("name")时，您正在使用方法链接：因为reset_index()返回一个 DataFrame，所以您可以直接调用另一个 DataFrame 方法，而不必先编写中间结果。

DATAFRAME METHODS RETURN COPIES

每当您在 DataFrame 上调用形式为df.method_name()的方法时，您将获得一个应用了该方法的 DataFrame 副本，保留原始 DataFrame 不变。我们刚刚通过调用df.reset_index()做到了这一点。如果您想要更改原始 DataFrame，您需要将返回值分配回原始变量，如下所示：

df = df.reset_index()

由于我们没有这样做，这意味着我们的变量 df 仍然保留其原始数据。接下来的示例也调用了 DataFrame 方法，即不更改原始 DataFrame。

要更改索引，请使用 reindex 方法：

In``[``9``]:``df``.``reindex``([``999``,``1000``,``1001``,``1004``])

Out[9]: name age country score continent user_id 999 NaN NaN NaN NaN NaN 1000 John 33.0 USA 6.7 America 1001 Mark 55.0 Italy 4.5 Europe 1004 NaN NaN NaN NaN NaN

这是数据对齐的第一个示例：reindex 将接管所有匹配新索引的行，并将在不存在信息的地方引入带有缺失值（NaN）的行。你留下的索引元素将被删除。稍后在本章中，我将适当地介绍 NaN。最后，要对索引进行排序，请使用 sort_index 方法：

In``[``10``]:``df``.``sort_index``()

Out[10]: name age country score continent user_id 1000 John 33 USA 6.7 America 1001 Mark 55 Italy 4.5 Europe 1002 Tim 41 USA 3.9 America 1003 Jenny 12 Germany 9.0 Europe

如果你想按一列或多列对行进行排序，请使用 sort_values：

In``[``11``]:``df``.``sort_values``([``"continent"``,``"age"``])

Out[11]: name age country score continent user_id 1000 John 33 USA 6.7 America 1002 Tim 41 USA 3.9 America 1003 Jenny 12 Germany 9.0 Europe 1001 Mark 55 Italy 4.5 Europe

该示例展示了如何首先按 continent，然后按 age 进行排序。如果只想按一列排序，也可以将列名作为字符串提供：

df.sort_values("continent")

这涵盖了索引如何工作的基础知识。现在让我们将注意力转向其水平对应物，即 DataFrame 的列！

列

要获取 DataFrame 的列信息，请运行以下代码：

`In[12]:df.columns``

Out[12]: Index(['name', 'age', 'country', 'score', 'continent'], dtype='object')

如果在构建 DataFrame 时没有提供任何列名，pandas 将使用从零开始的整数为列编号。然而，对于列来说，这几乎从来不是一个好主意，因为列表示变量，因此易于命名。你可以像设置索引一样给列头分配一个名称：

In``[``13``]:``df``.``columns``.``name``=``"properties"``df

Out[13]: properties name age country score continent user_id 1001 Mark 55 Italy 4.5 Europe 1000 John 33 USA 6.7 America 1002 Tim 41 USA 3.9 America 1003 Jenny 12 Germany 9.0 Europe

如果你不喜欢列名，可以重命名它们：

In``[``14``]:``df``.``rename``(``columns``=``{``"name"``:``"First Name"``,``"age"``:``"Age"``})

Out[14]: properties First Name Age country score continent user_id 1001 Mark 55 Italy 4.5 Europe 1000 John 33 USA 6.7 America 1002 Tim 41 USA 3.9 America 1003 Jenny 12 Germany 9.0 Europe

如果要删除列，请使用以下语法（示例显示如何同时删除列和索引）：

In``[``15``]:``df``.``drop``(``columns``=``[``"name"``,``"country"``],``index``=``[``1000``,``1003``])

Out[15]: properties age score continent user_id 1001 55 4.5 Europe 1002 41 3.9 America

DataFrame 的列和索引都由一个Index对象表示，因此可以通过转置 DataFrame 来将列变为行，反之亦然：

In``[``16``]:``df``.``T``# df.transpose()的快捷方式

Out[16]: user_id 1001 1000 1002 1003 properties name Mark John Tim Jenny age 55 33 41 12 country Italy USA USA Germany score 4.5 6.7 3.9 9 continent Europe America America Europe

值得在这里记住的是，我们的 DataFrame df 仍然没有改变，因为我们从未将方法调用返回的 DataFrame 重新分配给原始的 df 变量。如果想要重新排列 DataFrame 的列，可以使用我们与索引一起使用的reindex方法，但通常更直观的是按所需顺序选择列：

In``[``17``]:``df``.``loc``[:,``[``"continent"``,``"country"``,``"name"``,``"age"``,``"score"``]]

Out[17]: properties continent country name age score user_id 1001 Europe Italy Mark 55 4.5 1000 America USA John 33 6.7 1002 America USA Tim 41 3.9 1003 Europe Germany Jenny 12 9.0

最后这个例子需要解释的地方很多：关于loc以及数据选择工作方式的所有内容都是下一节的主题。

数据操作

现实世界中的数据很少是一成不变的，因此在处理数据之前，您需要清理数据并将其转换为可消化的形式。我们将从查找如何从 DataFrame 中选择数据开始，如何更改数据，以及如何处理缺失和重复数据。然后，我们将对 DataFrame 执行几个计算，并查看如何处理文本数据。最后，我们将了解 pandas 在返回数据视图与副本时的情况。本节中有许多概念与我们在上一章中使用 NumPy 数组时所见的概念相关。

数据选择

让我们首先在查看其他方法之前，通过标签和位置访问数据，包括布尔索引和使用 MultiIndex 选择数据。

按标签选择

访问 DataFrame 数据的最常见方法是引用其标签。使用属性 loc，代表位置，指定要检索的行和列：

df``.``loc``[``row_selection``,``column_selection``]

loc 支持切片表示法，因此可以接受冒号来分别选择所有行或列。另外，您还可以提供标签列表以及单个列或行名称。请查看表格 5-1 以查看从我们的样本 DataFrame df 中选择不同部分的几个示例。

表 5-1. 按标签选择数据

选择	返回数据类型	示例
单个值	标量	`df.loc[1000, "country"]`
单列（1d）	Series	`df.loc[:, "country"]`
单列（2d）	DataFrame	`df.loc[:, ["country"]]`
多列	DataFrame	`df.loc[:, ["country", "age"]]`
列范围	DataFrame	`df.loc[:, "name":"country"]`
单行（1d）	Series	`df.loc[1000, :]`
单行（2d）	DataFrame	`df.loc[[1000], :]`
多行	DataFrame	`df.loc[[1003, 1000], :]`
行范围	DataFrame	`df.loc[1000:1002, :]`

标签切片具有闭合间隔

使用标签的切片表示法与 Python 和 pandas 中其他一切的工作方式不一致：它们包括上限端点。

应用我们从表格 5-1 中获得的知识，让我们使用 loc 来选择标量、Series 和 DataFrames：

In``[``18``]:``# 对行和列选择使用标量返回标量``df``.``loc``[``1001``,``"name"``]

Out[18]: 'Mark'

In``[``19``]:``# 在行或列选择上使用标量返回 Series``df``.``loc``[[``1001``,``1002``],``"age"``]

Out[19]: user_id 1001 55 1002 41 Name: age, dtype: int64

In``[``20``]:``# 选择多行和多列返回 DataFrame``df``.``loc``[:``1002``,``[``"name"``,``"country"``]]

Out[20]: properties name country user_id 1001 Mark Italy 1000 John USA 1002 Tim USA

重要的是，您要理解 DataFrame 与 Series 之间的区别：即使有单个列，DataFrame 也是二维的，而 Series 是一维的。DataFrame 和 Series 都有索引，但只有 DataFrame 有列标题。当您将列选择为 Series 时，列标题将成为 Series 的名称。许多函数或方法将同时适用于 Series 和 DataFrame，但在执行算术计算时，行为会有所不同：对于 DataFrame，pandas 根据列标题对齐数据—稍后在本章中会详细介绍。

列选择的快捷方式

由于选择列是一个如此常见的操作，pandas 提供了一个快捷方式。而不是：

df``.``loc``[:,``column_selection``]

你可以这样写：

df``[``column_selection``]

例如，df["country"] 从我们的示例 DataFrame 返回一个 Series，而 df[["name", "country"]] 返回一个包含两列的 DataFrame。

按位置选择

通过位置选择 DataFrame 的子集对应于我们在本章开始时使用 NumPy 数组所做的事情。但是，对于 DataFrame，你必须使用 iloc 属性，它代表整数位置：

df``.``iloc``[``row_selection``,``column_selection``]

在使用切片时，你要处理标准的半开区间。表 5-2 给出了与我们之前在表 5-1 中查看的相同案例。

表 5-2. 按位置选择数据

选择	返回数据类型	示例
单个值	Scalar	`df.iloc[1, 2]`
一列（1d）	Series	`df.iloc[:, 2]`
一列（2d）	DataFrame	`df.iloc[:, [2]]`
多列	DataFrame	`df.iloc[:, [2, 1]]`
列范围	DataFrame	`df.iloc[:, :3]`
一行（1d）	Series	`df.iloc[1, :]`
一行（2d）	DataFrame	`df.iloc[[1], :]`
多行	DataFrame	`df.iloc[[3, 1], :]`
行范围	DataFrame	`df.iloc[1:3, :]`

这是如何使用 iloc 的方法——与之前使用 loc 的样本相同：

In``[``21``]:``df``.``iloc``[``0``,``0``]``# 返回一个 Scalar

Out[21]: 'Mark'

In``[``22``]:``df``.``iloc``[[``0``,``2``],``1``]``# 返回一个 Series

Out[22]: user_id 1001 55 1002 41 Name: age, dtype: int64

In``[``23``]:``df``.``iloc``[:``3``,``[``0``,``2``]]``# 返回一个 DataFrame

Out[23]: properties name country user_id 1001 Mark Italy 1000 John USA 1002 Tim USA

按标签或位置选择数据并不是访问 DataFrame 子集的唯一方式。另一种重要的方式是使用布尔索引；让我们看看它是如何工作的！

按布尔索引选择

布尔索引是指使用仅包含 True 或 False 的 Series 或 DataFrame 来选择 DataFrame 的子集。布尔 Series 用于选择 DataFrame 的特定列和行，而布尔 DataFrame 用于选择整个 DataFrame 中的特定值。最常见的用法是用布尔索引来过滤 DataFrame 的行。可以把它看作是 Excel 中的自动筛选功能。例如，这是如何筛选只显示住在美国且年龄超过 40 岁的人的 DataFrame 的方法：

In``[``24``]:``tf``=``(``df``[``"age"``]``>``40``)``&``(``df``[``"country"``]``==``"USA"``)``tf``# 这是一个仅包含 True/False 的 Series

Out[24]: user_id 1001 False 1000 False 1002 True 1003 False dtype: bool

In``[``25``]:``df``.``loc``[``tf``,``:]

Out[25]: properties name age country score continent user_id 1002 Tim 41 USA 3.9 America

这里有两件事需要解释。首先，由于技术限制，你不能在数据框（DataFrames）中像第三章中那样使用 Python 的布尔运算符。相反，你需要使用如表 5-3 所示的符号。

表 5-3. 布尔运算符

基本 Python 数据类型	数据框和 Series
`and`	`&`
`or`	`\|`
`not`	`~`

其次，如果你有多个条件，请确保将每个布尔表达式放在括号中，以避免运算符优先级成为问题：例如，&的运算符优先级高于==。因此，如果没有括号，示例中的表达式将被解释为：

df``[``"age"``]``>``(``40``&``df``[``"country"``])``==``"USA"

如果你想要过滤索引，你可以引用它作为df.index：

In``[``26``]:``df``.``loc``[``df``.``index``>``1001``,``:]

Out[26]: properties name age country score continent user_id 1002 Tim 41 USA 3.9 America 1003 Jenny 12 Germany 9.0 Europe

如果你想在基本的 Python 数据结构（如列表）中使用in操作符，那么在 Series 中使用isin来过滤你的数据框（DataFrame）以选择来自意大利和德国的参与者：

In``[``27``]:``df``.``loc``[``df``[``"country"``]``.``isin``([``"Italy"``,``"Germany"``]),``:]

Out[27]: properties name age country score continent user_id 1001 Mark 55 Italy 4.5 Europe 1003 Jenny 12 Germany 9.0 Europe

当你使用loc来提供一个布尔 Series 时，数据框提供了一个特殊的语法，无需loc即可选择给定完整布尔 DataFrame 的值：

df``[``boolean_df``]

如果你的数据框（DataFrame）仅包含数字，这将特别有帮助。提供一个布尔 DataFrame 将在布尔 DataFrame 为False时在数据框中返回NaN。稍后将更详细地讨论NaN。让我们从创建一个名为rainfall的新样本数据框开始，其中只包含数字：

In``[``28``]:``# 这可能是以毫米为单位的年降雨量``rainfall``=``pd``.``DataFrame``(``data``=``{``"City 1"``:``[``300.1``,``100.2``],``"City 2"``:``[``400.3``,``300.4``],``"City 3"``:``[``1000.5``,``1100.6``]})``rainfall

Out[28]:    City 1 City 2 City 3          0   300.1   400.3 1000.5          1   100.2   300.4 1100.6

In``[``29``]:``rainfall``<``400

Out[29]:    City 1 City 2 City 3          0    True   False   False          1    True    True   False

In``[``30``]:``rainfall``[``rainfall``<``400``]

Out[30]:    City 1 City 2 City 3          0   300.1     NaN     NaN          1   100.2   300.4     NaN

注意，在这个例子中，我使用了字典来构建一个新的 DataFrame——如果数据已经以这种形式存在，这通常很方便。以这种方式使用布尔值通常用于过滤特定值，如异常值。

结束数据选择部分之前，我将介绍一种特殊类型的索引称为 MultiIndex。

使用 MultiIndex 进行选择

一个MultiIndex是一个具有多个层级的索引。它允许你按层次分组数据，并轻松访问子集。例如，如果将我们示例 DataFrame df 的索引设置为 continent 和 country 的组合，你可以轻松选择特定大陆的所有行：

In[31]: # MultiIndex 需要排序df_multi=df.reset_index().set_index([``"continent"``,``"country"``])df_multi=df_multi.sort_index()``df_multi

Out[31]: properties user_id name age score continent country America USA 1000 John 33 6.7 USA 1002 Tim 41 3.9 Europe Germany 1003 Jenny 12 9.0 Italy 1001 Mark 55 4.5

In[32]: df_multi``.``loc``[``"Europe"``,``:]

Out[32]: properties user_id name age score country Germany 1003 Jenny 12 9.0 Italy 1001 Mark 55 4.5

请注意，pandas 通过不重复左侧索引级别（大陆）来美化 MultiIndex 的输出。而是在每行更改时仅打印大陆。通过提供元组来选择多个索引级别：

In[33]: df_multi.loc``[(``"Europe"``,``"Italy"``),``:]

Out[33]: properties user_id name age score continent country Europe Italy 1001 Mark 55 4.5

如果要选择性地重置 MultiIndex 的部分，请提供级别作为参数。从左侧开始，零是第一列：

In[34]: df_multi.reset_index(level=0)`

Out[34]: properties continent user_id name age score country USA America 1000 John 33 6.7 USA America 1002 Tim 41 3.9 Germany Europe 1003 Jenny 12 9.0 Italy Europe 1001 Mark 55 4.5

虽然我们在本书中不会手动创建 MultiIndex，但像groupby这样的某些操作将导致 pandas 返回带有 MultiIndex 的 DataFrame，因此了解它是很好的。我们将在本章后面介绍groupby。

现在你知道了各种选择数据的方法，现在是时候学习如何更改数据了。

数据设置

更改 DataFrame 数据的最简单方法是使用loc或iloc属性为特定元素分配值。这是本节的起点，在转向操作现有 DataFrame 的其他方法之前：替换值和添加新列。

通过标签或位置设置数据

正如本章前面所指出的，当你调用df.reset_index()等数据框方法时，该方法总是应用于一个副本，保持原始数据框不变。然而，通过loc和iloc属性赋值会改变原始数据框。由于我想保持我们的数据框df不变，因此在这里我使用了一个称为df2的副本。如果你想改变单个值，请按照以下步骤操作：

In``[``35``]:``# 先复制数据框以保留原始数据不变``df2``=``df``.``copy``()

In``[``36``]:``df2``.``loc``[``1000``,``"name"``]``=``"JOHN"``df2

Out[36]: properties name age country score continent user_id 1001 Mark 55 Italy 4.5 Europe 1000 JOHN 33 USA 6.7 America 1002 Tim 41 USA 3.9 America 1003 Jenny 12 Germany 9.0 Europe

你也可以同时更改多个值。改变 ID 为 1000 和 1001 的用户的分数的一种方法是使用列表：

In``[``37``]:``df2``.``loc``[[``1000``,``1001``],``"score"``]``=``[``3``,``4``]``df2

Out[37]: properties name age country score continent user_id 1001 Mark 55 Italy 4.0 Europe 1000 JOHN 33 USA 3.0 America 1002 Tim 41 USA 3.9 America 1003 Jenny 12 Germany 9.0 Europe

通过位置使用iloc来改变数据的方式与此相同。现在我们继续看看如何通过布尔索引来改变数据。

通过布尔索引设置数据

布尔索引，我们用来过滤行的方式，也可以用来在数据框中赋值。想象一下，你需要匿名化所有年龄低于 20 岁或来自美国的人的姓名：

In``[``38``]:``tf``=``(``df2``[``"age"``]``<``20``)``|``(``df2``[``"country"``]``==``"USA"``)``df2``.``loc``[``tf``,``"name"``]``=``"xxx"``df2

Out[38]: properties name age country score continent user_id 1001 Mark 55 Italy 4.0 Europe 1000 xxx 33 USA 3.0 America 1002 xxx 41 USA 3.9 America 1003 xxx 12 Germany 9.0 Europe

有时，你有一个数据集，需要跨整个数据框替换某些值，即不特定于某些列。在这种情况下，再次使用特殊语法，并像这样提供整个数据框与布尔值（此示例再次使用rainfall数据框）。

In``[``39``]:``# 先复制数据框以保留原始数据不变``rainfall2``=``rainfall``.``copy``()``rainfall2

Out[39]: 城市 1 城市 2 城市 3 0 300.1 400.3 1000.5 1 100.2 300.4 1100.6

In``[``40``]:``# 将低于 400 的值设为 0``rainfall2``[``rainfall2``<``400``]``=``0``rainfall2

Out[40]: City 1 City 2 City 3 0 0.0 400.3 1000.5 1 0.0 0.0 1100.6

如果只想用另一个值替换一个值，有一种更简单的方法，我将在下面展示给你。

通过替换值设置数据

如果要在整个 DataFrame 或选定列中替换某个值，请使用replace方法：

In``[``41``]:``df2``.``replace``(``"USA"``,``"U.S."``)

Out[41]: properties name age country score continent user_id 1001 Mark 55 Italy 4.0 Europe 1000 xxx 33 U.S. 3.0 America 1002 xxx 41 U.S. 3.9 America 1003 xxx 12 Germany 9.0 Europe

如果您只想在country列上执行操作，您可以改用以下语法：

df2``.``replace``({``"country"``:``{``"USA"``:``"U.S."``}})

在这种情况下，由于USA只出现在country列中，它产生了与前一个示例相同的结果。让我们看看如何向 DataFrame 添加额外列，以结束这一节。

通过添加新列设置数据

要向 DataFrame 添加新列，请为新列名称分配值。例如，您可以使用标量或列表向 DataFrame 添加新列：

In``[``42``]:``df2``.``loc``[:,``"discount"``]``=``0``df2``.``loc``[:,``"price"``]``=``[``49.9``,``49.9``,``99.9``,``99.9``]``df2

Out[42]: properties name age country score continent discount price user_id 1001 Mark 55 Italy 4.0 Europe 0 49.9 1000 xxx 33 USA 3.0 America 0 49.9 1002 xxx 41 USA 3.9 America 0 99.9 1003 xxx 12 Germany 9.0 Europe 0 99.9

添加新列通常涉及矢量化计算：

In``[``43``]:``df2``=``df``.``copy``()``# 让我们从头开始复制``df2``。``df2``.``loc``[:,``"birth year"``]``=``2021``-``df2``[``"age"``]``df2

Out[43]: properties name age country score continent birth year user_id 1001 Mark 55 Italy 4.5 Europe 1966 1000 John 33 USA 6.7 America 1988 1002 Tim 41 USA 3.9 America 1980 1003 Jenny 12 Germany 9.0 Europe 2009

我稍后会向你展示更多关于 DataFrame 计算的内容，但在我们到达那之前，请记住我已经多次使用了NaN吗？下一节将为您提供有关缺失数据主题的更多背景。

缺失数据

缺失数据可能会影响数据分析结果的偏差，从而使你的结论不够健壮。然而，在数据集中有空白是非常常见的，你需要处理它们。在 Excel 中，通常需要处理空单元格或 #N/A 错误，但是 pandas 使用 NumPy 的 np.nan 表示缺失数据，显示为 NaN。NaN 是浮点数的标准表示为“非数字”。对于时间戳，使用 pd.NaT，对于文本，pandas 使用 None。使用 None 或 np.nan，你可以引入缺失值：

In``[``44``]:``df2``=``df``.``copy``()``# 让我们从一个新的副本开始``df2``.``loc``[``1000``,``"score"``]``=``None``df2``.``loc``[``1003``,``:]``=``None``df2

Out[44]: properties name age country score continent user_id 1001 Mark 55.0 Italy 4.5 Europe 1000 John 33.0 USA NaN America 1002 Tim 41.0 USA 3.9 America 1003 None NaN None NaN None

清理 DataFrame，通常需要删除具有缺失数据的行。这很简单：

In``[``45``]:``df2``.``dropna``()

Out[45]: properties name age country score continent user_id 1001 Mark 55.0 Italy 4.5 Europe 1002 Tim 41.0 USA 3.9 America

然而，如果你只想删除所有值都缺失的行，请使用 how 参数：

In``[``46``]:``df2``.``dropna``(``how``=``"all"``)

Out[46]: properties name age country score continent user_id 1001 Mark 55.0 Italy 4.5 Europe 1000 John 33.0 USA NaN America 1002 Tim 41.0 USA 3.9 America

要获得一个布尔 DataFrame 或 Series，根据是否存在 NaN，使用 isna：

In``[``47``]:``df2``.``isna``()

Out[47]: properties name age country score continent user_id 1001 False False False False False 1000 False False False True False 1002 False False False False False 1003 True True True True True

要填充缺失值，使用 fillna。例如，将分数列中的 NaN 替换为其平均值（稍后我将介绍描述统计信息如 mean）：

In``[``48``]:``df2``.``fillna``({``"score"``:``df2``[``"score"``]``.``mean``()})

Out[48]: properties name age country score continent user_id 1001 Mark 55.0 Italy 4.5 Europe 1000 John 33.0 USA 4.2 America 1002 Tim 41.0 USA 3.9 America 1003 None NaN None 4.2 None

缺失数据不是唯一需要清理数据集的条件。对于重复数据也是如此，所以让我们看看我们的选择！

重复数据

像缺失数据一样，重复项会对分析的可靠性产生负面影响。要删除重复行，请使用drop_duplicates方法。您可以选择提供一列子集作为参数：

In``[``49``]:``df``.``drop_duplicates``([``"country"``,``"continent"``])

Out[49]: properties name age country score continent user_id 1001 Mark 55 Italy 4.5 Europe 1000 John 33 USA 6.7 America 1003 Jenny 12 Germany 9.0 Europe

默认情况下，这将保留第一次出现。要查找某列是否包含重复项或获取其唯一值，请使用以下两个命令（如果您希望在索引上运行此操作，请使用df.index而不是df["country"]）：

In``[``50``]:``df``[``"country"``]``.``is_unique

Out[50]: False

`In[51]:df["country"].unique()

Out[51]: array(['Italy', 'USA', 'Germany'], dtype=object)

最后，要了解哪些行是重复的，请使用duplicated方法，它返回一个布尔值系列：默认情况下，它使用参数keep="first"，保留第一次出现并仅标记重复为True。通过设置参数keep=False，它将对所有行返回True，包括第一次出现，从而轻松获取包含所有重复行的 DataFrame。在以下示例中，我们查看country列是否有重复，但实际上，您经常查看索引或整个行。在这种情况下，您必须使用df.index.duplicated()或df.duplicated()代替：

In``[``52``]:``# 默认情况下，它仅标记重复项为 True，即没有第一次出现``df``[``"country"``]``.``duplicated``()

Out[52]: user_id 1001 False 1000 False 1002 True 1003 False Name: country, dtype: bool

In``[``53``]:``# 要获取所有重复"country"的行，请使用``keep=False``df``.``loc``[``df``[``"country"``]``.``duplicated``(``keep``=``False``),``:]

Out[53]: properties name age country score continent user_id 1000 John 33 USA 6.7 America 1002 Tim 41 USA 3.9 America

一旦您通过删除缺失和重复数据清理了您的 DataFrame，您可能希望执行一些算术运算-下一节将为您介绍如何执行这些操作。

算术运算

像 NumPy 数组一样，DataFrame 和 Series 利用向量化。例如，要将数字添加到rainfall DataFrame 中的每个值，只需执行以下操作：

In``[``54``]:``rainfall

Out[54]: City 1 City 2 City 3 0 300.1 400.3 1000.5 1 100.2 300.4 1100.6

In``[``55``]:``rainfall``+``100

Out[55]: City 1 City 2 City 3 0 400.1 500.3 1100.5 1 200.2 400.4 1200.6

然而，pandas 的真正力量在于其自动数据对齐机制：当你使用多个 DataFrame 进行算术运算时，pandas 会自动根据它们的列和行索引进行对齐。让我们创建第二个具有一些相同行和列标签的 DataFrame，然后进行求和操作：

In``[``56``]:``more_rainfall``=``pd``.``DataFrame``(``data``=``[[``100``,``200``],``[``300``,``400``]],``index``=``[``1``,``2``],``columns``=``[``"City 1"``,``"City 4"``])``more_rainfall

Out[56]: City 1 City 4 1 100 200 2 300 400

In``[``57``]:``rainfall``+``more_rainfall

Out[57]: City 1 City 2 City 3 City 4 0 NaN NaN NaN NaN 1 200.2 NaN NaN NaN 2 NaN NaN NaN NaN

结果 DataFrame 的索引和列是两个 DataFrame 索引和列的并集：那些两个 DataFrame 中都有值的字段显示其和，而其余的 DataFrame 显示 NaN。如果你来自 Excel，这可能是你需要适应的地方，因为在 Excel 中，当你在算术运算中使用空单元格时，它们会自动转换为零。要获得与 Excel 中相同的行为，请使用 add 方法并使用 fill_value 替换 NaN 值为零：

In``[``58``]:``rainfall``.``add``(``more_rainfall``,``fill_value``=``0``)

Out[58]: City 1 City 2 City 3 City 4 0 300.1 400.3 1000.5 NaN 1 200.2 300.4 1100.6 200.0 2 300.0 NaN NaN 400.0

这对于其他算术运算符也适用，如表 Table 5-4 所示。

Table 5-4. 算术运算符

Operator	Method
`*`	`mul`
`+`	`add`
`-`	`sub`
`/`	`div`
`**`	`pow`

当你在计算中有一个 DataFrame 和一个 Series 时，默认情况下 Series 会沿着索引进行广播：

In``[``59``]:``# 从一行中提取的 Series``rainfall``.``loc``[``1``,``:]

Out[59]: City 1 100.2 City 2 300.4 City 3 1100.6 Name: 1, dtype: float64

In``[``60``]:``rainfall``+``rainfall``.``loc``[``1``,``:]

Out[60]: City 1 City 2 City 3 0 400.3 700.7 2101.1 1 200.4 600.8 2201.2

因此，要按列添加一个 Series，你需要使用 add 方法并显式指定 axis 参数：

In``[``61``]:``# 从一列中提取的 Series``rainfall``.``loc``[:,``"City 2"``]

Out[61]: 0 400.3 1 300.4 Name: City 2, dtype: float64

In``[``62``]:``rainfall``.``add``(``rainfall``.``loc``[:,``"City 2"``],``axis``=``0``)

Out[62]: City 1 City 2 City 3 0 700.4 800.6 1400.8 1 400.6 600.8 1401.0

虽然本节讨论的是带有数字的 DataFrame 在算术运算中的行为，但下一节将展示你在处理 DataFrame 中文本时的选项。

处理文本列

正如我们在本章开头所见，包含文本或混合数据类型的列具有数据类型object。要对包含文本字符串的列执行操作，请使用str属性，该属性使您可以访问 Python 的字符串方法。我们在第三章中已经了解了一些字符串方法，但查看一下Python 文档中提供的方法也无妨。例如，要去除前导和尾随空格，请使用strip方法；要将所有首字母大写，可以使用capitalize方法。将这些方法链在一起将清理手动输入数据产生的混乱文本列：

In``[``63``]:``# 让我们创建一个新的 DataFrame``users``=``pd``.``DataFrame``(``data``=``[``" mArk "``,``"JOHN "``,``"Tim"``,``" jenny"``],``columns``=``[``"name"``])``users

Out[63]: name 0 mArk 1 JOHN 2 Tim 3 jenny

In``[``64``]:``users_cleaned``=``users``.``loc``[:,``"name"``]``.``str``.``strip``()``.``str``.``capitalize``()``users_cleaned

Out[64]: 0 Mark 1 John 2 Tim 3 Jenny Name: name, dtype: object

或者，要查找所有以“J”开头的名称：

In``[``65``]:``users_cleaned``.``str``.``startswith``(``"J"``)

Out[65]: 0 False 1 True 2 False 3 True Name: name, dtype: bool

字符串方法很容易使用，但有时您可能需要以不内置的方式操作 DataFrame。在这种情况下，创建自己的函数并将其应用于 DataFrame，如下一节所示。

应用函数

数据框提供了applymap方法，该方法将应用于每个单独的元素，如果没有 NumPy ufuncs 可用，则非常有用。例如，没有用于字符串格式化的 ufuncs，因此我们可以像这样格式化 DataFrame 的每个元素：

In``[``66``]:``rainfall

Out[66]: City 1 City 2 City 3 0 300.1 400.3 1000.5 1 100.2 300.4 1100.6

In``[``67``]:``def``format_string``(``x``):``return``f``"{x:,.2f}"

In``[``68``]:``# 注意，我们传递函数时不要调用它，即 format_string 而不是 format_string()！``rainfall``.``applymap``(``format_string``)

Out[68]: City 1 City 2 City 3 0 300.10 400.30 1,000.50 1 100.20 300.40 1,100.60

要分解这个过程：下面的 f-string 将x返回为一个字符串：f"{x}"。要添加格式化，将冒号附加到变量后面，然后是格式化字符串,.2f。逗号是千位分隔符，.2f表示小数点后两位的固定点表示法。要获取有关如何格式化字符串的更多详细信息，请参阅格式规范迷你语言，它是 Python 文档的一部分。

对于这种用例，lambda 表达式（见侧边栏）被广泛使用，因为它们允许你在一行内写出同样的内容，而无需定义单独的函数。利用 lambda 表达式，我们可以将前面的例子重写为以下形式：

In``[``69``]:``降雨量``.``applymap``(``lambda``x``:``f``"{x:,.2f}"``)

Out[69]: 城市 1 城市 2 城市 3 0 300.10 400.30 1,000.50 1 100.20 300.40 1,100.60

LAMBDA 表达式

Python 允许你通过 lambda 表达式在一行内定义函数。Lambda 表达式是匿名函数，这意味着它是一个没有名称的函数。考虑这个函数：

def``函数名``(``参数 1``,``参数 2``,``...``):``return``返回值

这个函数可以重写为如下的 lambda 表达式：

lambda``参数 1``,``参数 2``,``...``:``返回值

本质上，你用 lambda 替换 def，省略 return 关键字和函数名，并把所有内容放在一行上。就像我们在 applymap 方法中看到的那样，在这种情况下，这样做非常方便，因为我们不需要为仅被使用一次的事情定义一个函数。

我已经提到了所有重要的数据操作方法，但在我们继续之前，理解 pandas 何时使用数据框的视图和何时使用副本是很重要的。

视图 vs. 副本

你可能还记得上一章节中，切片 NumPy 数组返回一个视图。但是对于数据框而言，情况更加复杂：loc 和 iloc 是否返回视图或副本往往难以预测，这使得它成为比较令人困惑的话题之一。因为改变视图和数据框副本是很大的区别，当 pandas 认为你以不合适的方式设置数据时，它经常会提出如下警告：SettingWithCopyWarning。为了避免这种颇为神秘的警告，这里有一些建议：

在原始数据框上设置值，而不是在从另一个数据框切片得到的数据框上设置值
如果你想要在切片后得到一个独立的数据框，那么要显式地复制：
选择``=``df``.``loc``[:,``[``"国家"``,``"大陆"``]]``.``copy``()

虽然在处理 loc 和 iloc 时情况复杂，但值得记住的是，所有数据框方法如 df.dropna() 或 df.sort_values("列名") 总是返回一个副本。

到目前为止，我们大多数时间都是在处理一个数据框。接下来的章节将展示多种将多个数据框合并为一个的方法，这是 pandas 提供的一个非常常见的强大工具。

合并数据框

在 Excel 中组合不同的数据集可能是一个繁琐的任务，通常涉及大量的VLOOKUP公式。幸运的是，pandas 的合并 DataFrame 功能是其杀手级功能之一，其数据对齐能力将极大地简化你的生活，从而大大减少引入错误的可能性。合并和连接 DataFrame 可以通过各种方式进行；本节只讨论使用concat、join和merge的最常见情况。虽然它们有重叠之处，但每个函数都使特定任务变得非常简单。我将从concat函数开始，然后解释使用join的不同选项，最后介绍最通用的merge函数。

连接

简单地将多个 DataFrame 粘合在一起，concat函数是你的好帮手。正如函数名称所示，这个过程有一个技术名字叫做连接。默认情况下，concat沿着行将 DataFrame 粘合在一起，并自动对齐列。在下面的示例中，我创建了另一个 DataFrame more_users，并将其附加到我们样本 DataFrame df的底部：

In``[``70``]:``data``=``[[``15``,``"法国"``,``4.1``,``"贝基"``],``[``44``,``"加拿大"``,``6.1``,``"莉安"``]]``more_users``=``pd``.``DataFrame``(``data``=``data``,``columns``=``[``"年龄"``,``"国家"``,``"得分"``,``"姓名"``],``index``=``[``1000``,``1011``])``more_users

Out[70]: 年龄国家得分姓名 1000 15 法国 4.1 贝基 1011 44 加拿大 6.1 莉安

In``[``71``]:``pd``.``concat``([``df``,``more_users``],``axis``=``0``)

Out[71]: 姓名年龄国家得分大陆 1001 马克 55 意大利 4.5 欧洲 1000 约翰 33 美国 6.7 美洲 1002 蒂姆 41 美国 3.9 美洲 1003 珍妮 12 德国 9.0 欧洲 1000 贝基 15 法国 4.1 NaN 1011 莉安 44 加拿大 6.1 NaN

现在你注意到，由于concat在指定轴（行）上将数据粘合在一起，并且仅在另一个轴（列）上对齐数据，所以你现在有重复的索引元素！即使两个 DataFrame 中的列名不同序，它们也会自动匹配列名！如果你想沿着列将两个 DataFrame 粘合在一起，请设置axis=1：

In``[``72``]:``data``=``[[``3``,``4``],``[``5``,``6``]]``more_categories``=``pd``.``DataFrame``(``data``=``data``,``columns``=``[``"测验"``,``"登录"``],``index``=``[``1000``,``2000``])``more_categories

Out[72]: 测验登录 1000 3 4 2000 5 6

In``[``73``]:``pd``.``concat``([``df``,``more_categories``],``axis``=``1``)

Out[73]: name age country score continent quizzes logins 1000 John 33.0 USA 6.7 America 3.0 4.0 1001 Mark 55.0 Italy 4.5 Europe NaN NaN 1002 Tim 41.0 USA 3.9 America NaN NaN 1003 Jenny 12.0 Germany 9.0 Europe NaN NaN 2000 NaN NaN NaN NaN NaN 5.0 6.0

concat 的特殊且非常有用的特性是它可以接受超过两个 DataFrame。我们将在下一章节中使用它将多个 CSV 文件合并成一个单独的 DataFrame：

pd``.``concat``([``df1``,``df2``,``df3``,``...``])

另一方面，join 和 merge 仅适用于两个 DataFrame，下面我们将看到。

连接与合并

当你连接两个 DataFrame 时，你将每个 DataFrame 的列合并到一个新的 DataFrame 中，同时根据集合理论决定行的处理方式。如果你之前有过与关系数据库的工作经验，那么这与 SQL 查询中的 JOIN 子句是相同的概念。图 5-3 显示了内连接、左连接、右连接和外连接四种连接类型如何通过使用两个示例 DataFrame df1 和 df2 进行操作。

图 5-3. 连接类型

使用 join 方法，pandas 使用两个 DataFrame 的索引来对齐行。内连接返回仅在索引重叠的行的 DataFrame。左连接获取左侧 DataFrame df1 的所有行，并在右侧 DataFrame df2 上匹配索引。在 df2 中没有匹配行的地方，pandas 将填充 NaN。左连接对应于 Excel 中的 VLOOKUP 情况。右连接获取右表 df2 的所有行，并将它们与 df1 的行在索引上匹配。最后，全外连接（即完全外连接）获取两个 DataFrame 的索引的并集，并在可能的情况下匹配值。表 5-5 是文本形式中图 5-3 的等效内容。

表 5-5. 连接类型

类型	描述
`inner`	仅包含索引存在于两个 DataFrame 中的行
`left`	从左 DataFrame 中获取所有行，匹配右 DataFrame 的行
`right`	从右 DataFrame 中获取所有行，匹配左 DataFrame 的行
`outer`	从两个 DataFrame 中获取所有行的并集

让我们看看实际操作中的情况，将图 5-3 中的示例活现出来：

In``[``74``]:``df1``=``pd``.``DataFrame``(``data``=``[[``1``,``2``],``[``3``,``4``],``[``5``,``6``]],``columns``=``[``"A"``,``"B"``])``df1

Out[74]:    A B          0 1 2          1 3 4          2 5 6

In``[``75``]:``df2``=``pd``.``DataFrame``(``data``=``[[``10``,``20``],``[``30``,``40``]],``columns``=``[``"C"``,``"D"``],``index``=``[``1``,``3``])``df2

Out[75]:     C   D          1 10 20          3 30 40

In``[``76``]:``df1``.``join``(``df2``,``how``=``"inner"``)

Out[76]:    A B   C   D          1 3 4 10 20

In``[``77``]:``df1``.``join``(``df2``,``how``=``"left"``)

Out[77]:    A B     C     D          0 1 2   NaN   NaN          1 3 4 10.0 20.0          2 5 6   NaN   NaN

In``[``78``]:``df1``.``join``(``df2``,``how``=``"right"``)

Out[78]:      A    B   C   D          1 3.0 4.0 10 20          3 NaN NaN 30 40

In``[``79``]:``df1``.``join``(``df2``,``how``=``"outer"``)

Out[79]:      A    B     C     D          0 1.0 2.0   NaN   NaN          1 3.0 4.0 10.0 20.0          2 5.0 6.0   NaN   NaN          3 NaN NaN 30.0 40.0

如果你想要根据一个或多个 DataFrame 列进行连接而不是依赖索引，使用merge而不是join。merge接受on参数作为连接条件：这些列必须存在于两个 DataFrame 中，并用于匹配行：

In``[``80``]:``# 给两个 DataFrame 都添加一个名为"category"的列``df1``[``"category"``]``=``[``"a"``,``"b"``,``"c"``]``df2``[``"category"``]``=``[``"c"``,``"b"``]

In``[``81``]:``df1

Out[81]:    A B category          0 1 2        a          1 3 4        b          2 5 6        c

In``[``82``]:``df2

Out[82]:     C   D category          1 10 20        c          3 30 40        b

In``[``83``]:``df1``.``merge``(``df2``,``how``=``"inner"``,``on``=``[``"category"``])

Out[83]:    A B category   C   D          0 3 4        b 30 40          1 5 6        c 10 20

In``[``84``]:``df1``.``merge``(``df2``,``how``=``"left"``,``on``=``[``"category"``])

Out[84]:    A B category     C     D          0 1 2        a   NaN   NaN          1 3 4        b 30.0 40.0          2 5 6        c 10.0 20.0

由于join和merge接受许多可选参数来适应更复杂的场景，我建议你查看官方文档以了解更多信息。

你现在知道如何操作一个或多个 DataFrame，这将引导我们数据分析旅程的下一步：理解数据。

描述性统计和数据聚合

在理解大数据集的一种方法是计算描述统计，如总和或平均值，可以针对整个数据集或有意义的子集。本节首先介绍了在 pandas 中如何进行这种操作，然后介绍了两种数据聚合到子集的方式：groupby方法和pivot_table函数。

描述性统计

描述性统计允许你通过使用定量的方法对数据集进行总结。例如，数据点的数量是一个简单的描述性统计量。像均值、中位数或众数这样的平均数也是其他流行的例子。DataFrame 和 Series 允许你通过诸如sum、mean和count之类的方法方便地访问描述性统计信息。在本书中你将会遇到许多这样的方法，并且完整的列表可以通过pandas 文档获取。默认情况下，它们返回沿着axis=0的 Series，这意味着你得到了列的统计信息：

In``[``85``]:``rainfall

Out[85]: City 1 City 2 City 3 0 300.1 400.3 1000.5 1 100.2 300.4 1100.6

In``[``86``]:``rainfall``.``mean``()

Out[86]: City 1 200.15 City 2 350.35 City 3 1050.55 dtype: float64

如果需要每行的统计信息，请提供axis参数：

In``[``87``]:``rainfall``.``mean``(``axis``=``1``)

Out[87]: 0 566.966667 1 500.400000 dtype: float64

默认情况下，描述性统计不包括缺失值，这与 Excel 处理空单元格的方式一致，因此在带有空单元格的范围上使用 Excel 的AVERAGE公式将给出与在具有相同数字和NaN值而不是空单元格的 Series 上应用mean方法相同的结果。

有时仅仅获取 DataFrame 所有行的统计数据是不够的，你需要更详细的信息——例如每个类别的平均值。让我们看看如何实现！

分组

使用我们的示例 DataFrame df，让我们再次找出每个大陆的平均分数！为此，首先按大陆分组行，然后应用mean方法，该方法将计算每个组的平均值。所有非数字列将自动排除：

In``[``88``]:``df``.``groupby``([``"continent"``])``.``mean``()

Out[88]: properties age score continent America 37.0 5.30 Europe 33.5 6.75

如果包括多于一个列，生成的 DataFrame 将具有层次化索引——我们之前遇到的 MultiIndex：

In``[``89``]:``df``.``groupby``([``"continent"``,``"country"``])``.``mean``()

Out[89]: properties age score continent country America USA 37 5.3 Europe Germany 12 9.0 Italy 55 4.5

你可以使用大多数由 pandas 提供的描述性统计量，如果想使用自己的函数，可以使用agg方法。例如，这是如何获取每个组最大值与最小值之差的方法：

In``[``90``]:``df``.``groupby``([``"continent"``])``.``agg``(``lambda``x``:``x``.``max``()``-``x``.``min``())

Out[90]: properties age score continent America 8 2.8 Europe 43 4.5

在 Excel 中，获取每个分组的统计数据的一种流行方式是使用数据透视表。它们引入了第二个维度，非常适合从不同角度查看数据。pandas 也有数据透视表功能，我们接下来会看到。

数据透视和数据融合

如果您在 Excel 中使用数据透视表，可以毫不费力地应用 pandas 的pivot_table函数，因为它的使用方式基本相同。下面的 DataFrame 中的数据组织方式与通常在数据库中存储记录的方式类似；每一行显示了特定水果在特定地区的销售交易：

In``[``91``]:``data``=``[[``"Oranges"``,``"North"``,``12.30``],``[``"Apples"``,``"South"``,``10.55``],``[``"Oranges"``,``"South"``,``22.00``],``[``"Bananas"``,``"South"``,``5.90``],``[``"Bananas"``,``"North"``,``31.30``],``[``"Oranges"``,``"North"``,``13.10``]]``sales``=``pd``.``DataFrame``(``data``=``data``,``columns``=``[``"Fruit"``,``"Region"``,``"Revenue"``])``sales

Out[91]: Fruit Region Revenue 0 Oranges North 12.30 1 Apples South 10.55 2 Oranges South 22.00 3 Bananas South 5.90 4 Bananas North 31.30 5 Oranges North 13.10

要创建数据透视表，将 DataFrame 作为pivot_table函数的第一个参数提供。index和columns定义了 DataFrame 的哪一列将成为数据透视表的行和列标签。values将根据aggfunc聚合到生成的 DataFrame 的数据部分中，aggfunc是一个可以作为字符串或 NumPy ufunc 提供的函数。最后，margins对应于 Excel 中的Grand Total，即如果不指定margins和margins_name，则不会显示Total列和行：

In``[``92``]:``pivot``=``pd``.``pivot_table``(``sales``,``index``=``"Fruit"``,``columns``=``"Region"``,``values``=``"Revenue"``,``aggfunc``=``"sum"``,``margins``=``True``,``margins_name``=``"Total"``)``pivot

Out[92]: Region North South Total Fruit Apples NaN 10.55 10.55 Bananas 31.3 5.90 37.20 Oranges 25.4 22.00 47.40 Total 56.7 38.45 95.15

总之，对数据进行透视意味着获取某一列的唯一值（在我们的例子中是Region），并将其转换为数据透视表的列标题，从而聚合另一列的值。这样可以轻松地查看所关心维度的汇总信息。在我们的数据透视表中，您立即可以看到北部地区没有销售苹果，而南部地区的大部分收入来自橙子。如果您希望反过来，将列标题转换为单列的值，请使用melt。从这个意义上说，melt是pivot_table函数的反义词：

In``[``93``]:``pd``.``melt``(``pivot``.``iloc``[:``-``1``,:``-``1``]``.``reset_index``(),``id_vars``=``"Fruit"``,``value_vars``=``[``"North"``,``"South"``],``value_name``=``"Revenue"``)

Out[93]: Fruit Region Revenue 0 Apples North NaN 1 Bananas North 31.30 2 Oranges North 25.40 3 Apples South 10.55 4 Bananas South 5.90 5 Oranges South 22.00

在这里，我将我们的透视表作为输入提供，但我使用iloc来去除总行和列。我还重置索引，以便所有信息都作为常规列可用。然后，我提供id_vars以指示标识符，并提供value_vars以定义我要“解构”的列。如果您希望准备数据以便将其存储回预期以此格式存储的数据库中，熔断可能会很有用。

使用聚合统计数据有助于理解数据，但没有人喜欢阅读满篇数字。为了使信息易于理解，创建可视化效果最有效，这是我们接下来要讨论的内容。虽然 Excel 使用术语图表，但 pandas 通常称之为图。在本书中，我会交替使用这些术语。

绘图

绘图允许您可视化数据分析的发现，可能是整个过程中最重要的一步。对于绘图，我们将使用两个库：首先是 Matplotlib，pandas 的默认绘图库，然后是 Plotly，这是一个现代绘图库，在 Jupyter 笔记本中提供更交互式的体验。

Matplotlib

Matplotlib 是一个长期存在的绘图包，包含在 Anaconda 发行版中。您可以使用它生成多种格式的图表，包括高质量打印的矢量图形。当您调用 DataFrame 的plot方法时，pandas 默认会生成一个 Matplotlib 图。

要在 Jupyter 笔记本中使用 Matplotlib，您需要首先运行两个魔术命令中的一个（参见侧边栏“魔术命令”）：%matplotlib inline或%matplotlib notebook。它们配置笔记本以便在笔记本本身中显示图表。后者命令增加了一些交互性，允许您更改图表的大小或缩放系数。让我们开始，并使用 pandas 和 Matplotlib 创建第一个图表（见图 5-4）：

In``[``94``]:``import``numpy``as``np``%``matplotlib``inline``# Or %matplotlib notebook

In``[``95``]:``data``=``pd``.``DataFrame``(``data``=``np``.``random``.``rand``(``4``,``4``)``*``100000``,``index``=``[``"Q1"``,``"Q2"``,``"Q3"``,``"Q4"``],``columns``=``[``"East"``,``"West"``,``"North"``,``"South"``])``data``.``index``.``name``=``"Quarters"``data``.``columns``.``name``=``"Region"``data

Out[95]: Region East West North South Quarters Q1 23254.220271 96398.309860 16845.951895 41671.684909 Q2 87316.022433 45183.397951 15460.819455 50951.465770 Q3 51458.760432 3821.139360 77793.393899 98915.952421 Q4 64933.848496 7600.277035 55001.831706 86248.512650

`In[96]:data.plot()# 快捷方式用于 data.plot.line()``

Out[96]: <AxesSubplot:xlabel='Quarters'>

图 5-4. Matplotlib 绘图

请注意，在此示例中，我使用了 NumPy 数组来构建 pandas DataFrame。提供 NumPy 数组允许你利用上一章介绍的 NumPy 构造函数；在这里，我们使用 NumPy 根据伪随机数生成了一个 pandas DataFrame。因此，当你在自己的环境中运行示例时，会得到不同的值。

魔术命令

我们在 Jupyter 笔记本中使用的 %matplotlib inline 命令是一个魔术命令。魔术命令是一组简单的命令，可以让 Jupyter 笔记本单元格以特定方式运行，或者使繁琐的任务变得异常简单，几乎像魔术一样。你可以像写 Python 代码一样在单元格中编写这些命令，但它们以 %% 或 % 开头。影响整个单元格的命令以 %% 开头，而仅影响单行的命令以 % 开头。

我们将在接下来的章节中看到更多的魔术命令，但如果你想列出当前所有可用的魔术命令，请运行 %lsmagic，并运行 %magic 获取详细说明。

即使使用 %matplotlib notebook 魔术命令，你可能会注意到 Matplotlib 最初设计用于静态绘图，而不是在网页上进行交互体验。这就是为什么接下来我们将使用 Plotly，这是一个专为 Web 设计的绘图库。

Plotly

Plotly 是基于 JavaScript 的库，自版本 4.8.0 起，可以作为 pandas 的绘图后端，具有出色的交互性：你可以轻松缩放，单击图例以选择或取消选择类别，并获取有关悬停数据点的更多信息。Plotly 不包含在 Anaconda 安装中，因此如果尚未安装，请通过运行以下命令安装：

(base)> conda install plotly

运行此单元格后，整个笔记本的绘图后端将设置为 Plotly，如果重新运行之前的单元格，它也将呈现为 Plotly 图表。对于 Plotly，你只需在能够绘制 5-5 和 5-6 图形之前将其设置为后端：

In``[``97``]:``# 将绘图后端设置为 Plotly``pd``.``options``.``plotting``.``backend``=``"plotly"

`In[98]:data.plot()

图 5-5. Plotly 折线图

In``[``99``]:``# 显示相同数据的条形图``data``.``plot``.``bar``(``barmode``=``"group"``)

图 5-6. Plotly 条形图

绘图后端的差异

如果您使用 Plotly 作为绘图后端，您需要直接在 Plotly 文档中检查绘图方法的接受参数。例如，您可以查看 Plotly 条形图文档中的 barmode=group 参数。

pandas 和底层绘图库提供了丰富的图表类型和选项，可以以几乎任何期望的方式格式化图表。也可以将多个图表安排到子图系列中。总览见表 5-6 显示了可用的绘图类型。

表 5-6. pandas 绘图类型

类型	描述
`line`	线性图表，默认运行 `df.plot()` 时的默认设置
`bar`	垂直条形图
`barh`	水平条形图
`hist`	直方图
`box`	箱线图
`kde`	密度图，也可以通过 `density` 使用
`area`	面积图
`scatter`	散点图
`hexbin`	六角形箱形图
`pie`	饼图

此外，pandas 还提供了一些由多个独立组件组成的高级绘图工具和技术。详情请见 pandas 可视化文档。

其他绘图库

Python 的科学可视化领域非常活跃，除了 Matplotlib 和 Plotly 外，还有许多其他高质量的选择，可能更适合特定用例：

Seaborn

Seaborn 建立在 Matplotlib 之上。它改进了默认样式，并添加了额外的绘图，如热图，通常简化了您的工作：您可以仅使用几行代码创建高级统计图。

Bokeh

Bokeh 类似于 Plotly 在技术和功能上：它基于 JavaScript，因此在 Jupyter 笔记本中也非常适合交互式图表。Bokeh 包含在 Anaconda 中。

Altair

Altair 是一个基于 Vega 项目的统计可视化库。Altair 也是基于 JavaScript 的，提供一些像缩放这样的交互功能。

HoloViews

HoloViews 是另一个基于 JavaScript 的包，专注于使数据分析和可视化变得简单。只需几行代码，您就可以实现复杂的统计图。

我们将在下一章节创建更多的图表来分析时间序列，但在此之前，让我们通过学习如何使用 pandas 导入和导出数据来结束本章节！

导入和导出数据帧

到目前为止，我们使用嵌套列表、字典或 NumPy 数组从头构建了 DataFrame。了解这些技术很重要，但通常数据已经可用，您只需将其转换为 DataFrame。为此，pandas 提供了各种读取函数。但即使您需要访问 pandas 不提供内置读取器的专有系统，您通常也可以使用 Python 包连接到该系统，一旦获取数据，将其转换为 DataFrame 就很容易。在 Excel 中，数据导入通常是使用 Power Query 处理的工作类型。

分析和更改数据集后，您可能希望将结果推回数据库或将其导出为 CSV 文件或者——考虑到本书的标题——将其呈现在 Excel 工作簿中供您的经理查看。要导出 pandas DataFrame，请使用 DataFrame 提供的导出器方法之一。表格 5-7 显示了最常见的导入和导出方法概述。

表格 5-7. 导入和导出数据框

数据格式/系统	导入：pandas（pd）函数	导出：DataFrame（df）方法
CSV 文件	`pd.read_csv`	`df.to_csv`
JSON	`pd.read_json`	`df.to_json`
HTML	`pd.read_html`	`df.to_html`
剪贴板	`pd.read_clipboard`	`df.to_clipboard`
Excel 文件	`pd.read_excel`	`df.to_excel`
SQL 数据库	`pd.read_sql`	`df.to_sql`

我们将在第十一章中遇到 pd.read_sql 和 pd.to_sql，在那里我们将把它们作为案例研究的一部分使用。并且由于我打算在整个第七章中专门讨论使用 pandas 读取和写入 Excel 文件的主题，因此在本节中我将重点讨论导入和导出 CSV 文件。让我们从导出现有的 DataFrame 开始！

导出 CSV 文件

如果您需要将 DataFrame 传递给可能不使用 Python 或 pandas 的同事，以 CSV 文件的形式传递通常是个好主意：几乎每个程序都知道如何导入它们。要将我们的示例 DataFrame df 导出到 CSV 文件，使用 to_csv 方法：

In``[``100``]:``df``.``to_csv``(``"course_participants.csv"``)

如果您想将文件存储在不同的目录中，请提供完整路径作为原始字符串，例如，r"C:\path\to\desired\location\msft.csv"。

在 Windows 上使用原始字符串处理文件路径

在字符串中，反斜杠用于转义某些字符。这就是为什么在 Windows 上处理文件路径时，您需要使用双反斜杠（C:\\path\\to\\file.csv）或在字符串前加上r来将其转换为原始字符串，以字面上解释字符。这在 macOS 或 Linux 上不是问题，因为它们在路径中使用正斜杠。

通过仅提供文件名如我所做的那样，它将在与笔记本相同的目录中生成文件course_participants.csv，内容如下：

user_id,name,age,country,score,continent 1001,Mark,55,Italy,4.5,Europe 1000,John,33,USA,6.7,America 1002,Tim,41,USA,3.9,America 1003,Jenny,12,Germany,9.0,Europe

现在您已经了解如何使用df.to_csv方法，让我们看看如何导入 CSV 文件！

导入 CSV 文件

导入本地 CSV 文件就像将其路径提供给read_csv函数一样简单。MSFT.csv 是我从 Yahoo! Finance 下载的 CSV 文件，包含了微软的日常历史股价 —— 你可以在伴随的存储库中的 csv 文件夹找到它：

In``[``101``]:``msft``=``pd``.``read_csv``(``"csv/MSFT.csv"``)

经常需要向read_csv提供比仅文件名更多的参数。例如，sep参数允许您告诉 pandas CSV 文件使用的分隔符或分隔符，以防它不是默认的逗号。在下一章中，我们将使用更多的参数，但是为了全面了解，请查看pandas 文档。

现在我们正在处理具有数千行的大型 DataFrame，通常第一步是运行info方法以获取 DataFrame 的摘要信息。接下来，您可能希望使用head和tail方法查看 DataFrame 的前几行和最后几行。这些方法默认返回五行，但可以通过提供所需行数作为参数来更改。您还可以运行describe方法获取一些基本统计信息：

In``[``102``]:``msft``.``info``()

<class 'pandas.core.frame.DataFrame'> RangeIndex: 8622 entries, 0 to 8621 Data columns (total 7 columns): #   Column     Non-Null Count Dtype --- ------     -------------- ----- 0   Date       8622 non-null   object 1   Open       8622 non-null   float64 2   High       8622 non-null   float64 3   Low        8622 non-null   float64 4   Close      8622 non-null   float64 5   Adj Close 8622 non-null   float64 6   Volume     8622 non-null   int64 dtypes: float64(5), int64(1), object(1) memory usage: 471.6+ KB

In``[``103``]:``# 由于空间问题，我只选择了几列``# 您也可以直接运行：msft.head()``msft``.``loc``[:,``[``"Date"``,``"Adj Close"``,``"Volume"``]]``.``head``()

Out[103]:          Date Adj Close      Volume           0 1986-03-13   0.062205 1031788800           1 1986-03-14   0.064427   308160000           2 1986-03-17   0.065537   133171200           3 1986-03-18   0.063871    67766400           4 1986-03-19   0.062760    47894400

In``[``104``]:``msft``.``loc``[:,``[``"Date"``,``"Adj Close"``,``"Volume"``]]``.``tail``(``2``)

Out[104]:             Date   Adj Close    Volume           8620 2020-05-26 181.570007 36073600           8621 2020-05-27 181.809998 39492600

In``[``105``]:``msft``.``loc``[:,``[``"Adj Close"``,``"Volume"``]]``.``describe``()

Out[105]:          Adj Close        Volume           count 8622.000000 8.622000e+03           mean     24.921952 6.030722e+07           std      31.838096 3.877805e+07           min       0.057762 2.304000e+06           25%       2.247503 3.651632e+07           50%      18.454313 5.350380e+07           75%      25.699224 7.397560e+07           max     187.663330 1.031789e+09

Adj Close 代表调整后的收盘价格，校正了股票价格如股票拆分等公司行动。 Volume 是交易的股票数量。我总结了本章中看到的各种 DataFrame 探索方法在表 5-8 中。

表格 5-8. DataFrame 探索方法和属性

DataFrame（df）方法/属性	描述
`df.info()`	提供数据点数量、索引类型、数据类型和内存使用情况。
`df.describe()`	提供基本统计信息，包括计数、均值、标准差、最小值、最大值和百分位数。
`df.head(n=5)`	返回 DataFrame 的前 n 行。
`df.tail(n=5)`	返回 DataFrame 的最后 n 行。
`df.dtypes`	返回每列的数据类型。

read_csv 函数还可以接受 URL 而不是本地 CSV 文件。以下是直接从配套仓库读取 CSV 文件的方法：

In``[``106``]:``# URL 中的换行符仅是为了使其适合页面``url``=``(``"https://raw.githubusercontent.com/fzumstein/"``"python-for-excel/1st-edition/csv/MSFT.csv"``)``msft``=``pd``.``read_csv``(``url``)

In``[``107``]:``msft``.``loc``[:,``[``"Date"``,``"Adj Close"``,``"Volume"``]]``.``head``(``2``)

Out[107]: Date Adj Close Volume 0 1986-03-13 0.062205 1031788800 1 1986-03-14 0.064427 308160000

在下一章关于时间序列的章节中，我们将继续使用此数据集和 read_csv 函数，将 Date 列转换为 DatetimeIndex。

结论

本章充满了分析 pandas 数据集的新概念和工具。我们学会了如何加载 CSV 文件，如何处理缺失或重复数据，以及如何利用描述性统计信息。我们还看到了如何将 DataFrame 转换为交互式图表。虽然消化这些内容可能需要一些时间，但加入 pandas 工具箱后，您将很快理解其强大之处。在此过程中，我们将 pandas 与以下 Excel 功能进行了比较：

自动筛选功能

参见 “通过布尔索引进行选择”。

VLOOKUP 公式

参见 “连接和合并”。

数据透视表

参见 “数据透视和融合”。

Power Query

这是 “导入和导出数据框”、“数据操作” 和 “合并数据框” 的结合。

下一章讲述的是时间序列分析，这一功能使得 pandas 在金融行业广泛应用。让我们看看为什么 pandas 的这一部分比 Excel 更具优势！

1 pandas 1.0.0 引入了专门的string数据类型，以使某些操作更容易且与文本更一致。由于它仍处于实验阶段，我在本书中不打算使用它。

第六章： pandas 时间序列分析

时间序列是沿着基于时间的轴的一系列数据点，在许多不同的场景中发挥着核心作用：交易员使用历史股票价格来计算风险度量，天气预报基于传感器生成的时间序列，这些传感器测量温度、湿度和气压。数字营销部门依赖于由网页生成的时间序列，例如每小时的页面浏览量的来源和数量，并将其用于从中得出关于其营销活动的结论。

时间序列分析是数据科学家和分析师开始寻找比 Excel 更好的替代方案的主要推动力之一。以下几点总结了这一举措背后的一些原因：

大型数据集

时间序列往往会快速超出 Excel 每个工作表大约一百万行的限制。例如，如果你在 tick 数据级别上处理股票的分钟价格，你通常会处理数十万条记录——每支股票每天！

日期和时间

正如我们在第三章中所看到的，Excel 在处理日期和时间时存在各种限制，这是时间序列的基础。缺少对时区的支持以及仅限于毫秒的数字格式是其中一些限制。pandas 支持时区，并使用 NumPy 的datetime64[ns]数据类型，该类型可提供高达纳秒的分辨率。

缺失的功能

Excel 甚至缺乏基本工具，无法以体面的方式处理时间序列数据。例如，如果你想将每日时间序列转换为每月时间序列，尽管这是一项非常常见的任务，但却没有简单的方法来实现这一点。

DataFrames 允许你使用各种基于时间的索引：DatetimeIndex是最常见的一个，表示一个带有时间戳的索引。其他索引类型，比如PeriodIndex，基于时间间隔，比如小时或月份。然而，在本章中，我们只关注DatetimeIndex，我将在接下来更详细地介绍它。

DatetimeIndex

在本节中，我们将学习如何构建一个DatetimeIndex，如何将这样的索引筛选到特定的时间范围，并如何处理时区。

创建一个 DatetimeIndex

要构建一个DatetimeIndex，pandas 提供了date_range函数。它接受一个开始日期、一个频率，以及要么周期数，要么结束日期：

In``[``1``]:``# 让我们从导入本章中使用的包开始``# 并将绘图后端设置为 Plotly``import``pandas``as``pd``import``numpy``as``np``pd``.``options``.``plotting``.``backend``=``"plotly"

In``[``2``]:``# 这将根据开始时间戳、周期数和频率（"D" = daily）创建一个 DatetimeIndex。``daily_index``=``pd``.``date_range``(``"2020-02-28"``,``periods``=``4``,``freq``=``"D"``)``daily_index

Out[2]: DatetimeIndex(['2020-02-28', '2020-02-29', '2020-03-01', '2020-03-02'],          dtype='datetime64[ns]', freq='D')

In``[``3``]:``# 根据起始/结束时间戳创建 DatetimeIndex。频率设置为每周日一次 ("W-SUN")。``weekly_index``=``pd``.``date_range``(``"2020-01-01"``,``"2020-01-31"``,``freq``=``"W-SUN"``)``weekly_index

Out[3]: DatetimeIndex(['2020-01-05', '2020-01-12', '2020-01-19', '2020-01-26'],          dtype='datetime64[ns]', freq='W-SUN')

In``[``4``]:``# 基于 weekly_index 构建 DataFrame。这可以是只在星期日开放的博物馆的访客计数。``pd``.``DataFrame``(``data``=``[``21``,``15``,``33``,``34``],``columns``=``[``"visitors"``],``index``=``weekly_index``)

Out[4]:             visitors         2020-01-05        21         2020-01-12        15         2020-01-19        33         2020-01-26        34

现在让我们回到上一章的微软股票时间序列。当您仔细查看列的数据类型时，您会注意到Date列的类型是object，这意味着 pandas 将时间戳解释为字符串：

In``[``5``]:``msft``=``pd``.``read_csv``(``"csv/MSFT.csv"``)

In``[``6``]:``msft``.``info``()

<class 'pandas.core.frame.DataFrame'> RangeIndex: 8622 entries, 0 to 8621 Data columns (total 7 columns): # Column Non-Null Count Dtype --- ------ -------------- ----- 0 Date 8622 non-null object 1 Open 8622 non-null float64 2 High 8622 non-null float64 3 Low 8622 non-null float64 4 Close 8622 non-null float64 5 Adj Close 8622 non-null float64 6 Volume 8622 non-null int64 dtypes: float64(5), int64(1), object(1) memory usage: 471.6+ KB

有两种方法可以修复这个问题并将其转换为datetime数据类型。第一种方法是在该列上运行to_datetime函数。如果您希望在源数据框中更改它，请确保将转换后的列重新赋值给原始 DataFrame：

In``[``7``]:``msft``.``loc``[:,``"Date"``]``=``pd``.``to_datetime``(``msft``[``"Date"``])

In``[``8``]:``msft``.``dtypes

Out[8]: Date datetime64[ns] Open float64 High float64 Low float64 Close float64 Adj Close float64 Volume int64 dtype: object

另一种可能性是告诉read_csv哪些列包含时间戳，使用parse_dates参数。parse_dates需要一个列名或索引的列表。此外，您几乎总是希望将时间戳转换为 DataFrame 的索引，因为这将使您能够轻松地过滤数据，稍后我们将看到。为了避免额外的set_index调用，请通过index_col参数提供您希望用作索引的列名或索引：

In``[``9``]:``msft``=``pd``.``read_csv``(``"csv/MSFT.csv"``,``index_col``=``"Date"``,``parse_dates``=``[``"Date"``])

In``[``10``]:``msft``.``info``()

<class 'pandas.core.frame.DataFrame'> DatetimeIndex: 8622 entries, 1986-03-13 to 2020-05-27 Data columns (total 6 columns): # Column Non-Null Count Dtype --- ------ -------------- ----- 0 Open 8622 non-null float64 1 High 8622 non-null float64 2 Low 8622 non-null float64 3 Close 8622 non-null float64 4 Adj Close 8622 non-null float64 5 Volume 8622 non-null int64 dtypes: float64(5), int64(1) memory usage: 471.5 KB

正如info所显示的，你现在正在处理一个具有DatetimeIndex的 DataFrame。如果你需要更改另一个数据类型（比如你想要Volume是float而不是int），你有两个选项：要么将dtype={"Volume": float}作为参数提供给read_csv函数，要么像下面这样应用astype方法：

In``[``11``]:``msft``.``loc``[:,``"Volume"``]``=``msft``[``"Volume"``]``.``astype``(``"float"``)``msft``[``"Volume"``]``.``dtype

Out[11]: dtype('float64')

在处理时间序列时，在开始分析之前，确保索引已经正确排序是个好主意：

In``[``12``]:``msft``=``msft``.``sort_index``()

最后，如果你需要访问DatetimeIndex的部分，比如只需要日期部分而不需要时间，可以像这样访问date属性：

In``[``13``]:``msft``.``index``.``date

Out[13]: array([datetime.date(1986, 3, 13), datetime.date(1986, 3, 14), datetime.date(1986, 3, 17), ..., datetime.date(2020, 5, 22), datetime.date(2020, 5, 26), datetime.date(2020, 5, 27)], dtype=object)

替代date，你也可以使用日期的部分，比如year、month、day等。要访问具有数据类型datetime的常规列上的相同功能，你需要使用dt属性，例如df["column_name"].dt.date。

有了排序的DatetimeIndex，让我们看看如何筛选 DataFrame 来选择特定的时间段！

筛选DatetimeIndex

如果你的 DataFrame 具有DatetimeIndex，可以通过使用格式为YYYY-MM-DD HH:MM:SS的字符串在loc中选择特定时间段的行。pandas 会将此字符串转换为切片，以涵盖整个时间段。例如，要选择 2019 年的所有行，请提供年份作为字符串，而不是数字：

In``[``14``]:``msft``.``loc``[``"2019"``,``"Adj Close"``]

Out[14]: Date 2019-01-02 99.099190 2019-01-03 95.453529 2019-01-04 99.893005 2019-01-07 100.020401 2019-01-08 100.745613 ... 2019-12-24 156.515396 2019-12-26 157.798309 2019-12-27 158.086731 2019-12-30 156.724243 2019-12-31 156.833633 Name: Adj Close, Length: 252, dtype: float64

让我们更进一步，绘制 2019 年 6 月至 2020 年 5 月之间的数据（见图表 6-1）：

In``[``15``]:``msft``.``loc``[``"2019-06"``:``"2020-05"``,``"Adj Close"``]``.``plot``()

Figure 6-1. MSFT 的调整收盘价

将鼠标悬停在 Plotly 图表上以读取提示信息，并通过鼠标绘制矩形来放大。双击图表以返回默认视图。

下一节将使用调整后的收盘价来了解时区处理。

处理时区

微软在纳斯达克证券交易所上市。纳斯达克位于纽约，市场在下午 4 点关闭。要将此附加信息添加到 DataFrame 的索引中，首先通过DateOffset将收盘小时添加到日期中，然后通过tz_localize将正确的时区附加到时间戳中。由于收盘时间仅适用于收盘价，让我们创建一个新的 DataFrame：

In``[``16``]:``# 将时间信息添加到日期中``msft_close``=``msft``.``loc``[:,``[``"Adj Close"``]]``.``copy``()``msft_close``.``index``=``msft_close``.``index``+``pd``.``DateOffset``(``hours``=``16``)``msft_close``.``head``(``2``)

Out[16]: Adj Close Date 1986-03-13 16:00:00 0.062205 1986-03-14 16:00:00 0.064427

In``[``17``]:``# 将时间戳转换为时区感知时间``msft_close``=``msft_close``.``tz_localize``(``"America/New_York"``)``msft_close``.``head``(``2``)

Out[17]: Adj Close Date 1986-03-13 16:00:00-05:00 0.062205 1986-03-14 16:00:00-05:00 0.064427

如果您想将时间戳转换为 UTC 时区，请使用 DataFrame 方法tz_convert。UTC 代表协调世界时，是格林威治标准时间（GMT）的继任者。请注意，根据纽约是否实行夏令时（DST），UTC 中的收盘时间会发生变化：

In``[``18``]:``msft_close``=``msft_close``.``tz_convert``(``"UTC"``)``msft_close``.``loc``[``"2020-01-02"``,``"Adj Close"``]``# 21:00 没有夏令时

Out[18]: Date 2020-01-02 21:00:00+00:00 159.737595 Name: Adj Close, dtype: float64

In``[``19``]:``msft_close``.``loc``[``"2020-05-01"``,``"Adj Close"``]``# 20:00 包含夏令时

Out[19]: Date 2020-05-01 20:00:00+00:00 174.085175 Name: Adj Close, dtype: float64

准备这样的时间序列将允许您即使时间信息缺失或以本地时区陈述，也能比较来自不同时区证券交易所的收盘价。

现在您已经了解了什么是DatetimeIndex，让我们在下一节中尝试一些常见的时间序列操作，例如计算和比较股票表现。

常见的时间序列操作

在本节中，我将向您展示如何执行常见的时间序列分析任务，例如计算股票回报率、绘制各种股票的表现，并在热图中可视化它们的回报相关性。我们还将看到如何更改时间序列的频率以及如何计算滚动统计数据。

Shifting and Percentage Changes

在金融领域，股票的对数收益率通常被假设为正态分布。通过对数收益率，我指的是当前价格与上一个价格的比率的自然对数。为了对每日对数收益率的分布有所了解，让我们绘制一个直方图。但首先，我们需要计算对数收益率。在 Excel 中，通常使用涉及来自两行的单元格的公式，如图 6-2 所示。

图 6-2. 在 Excel 中计算对数收益率

Excel 和 Python 中的对数

Excel 使用LN表示自然对数，LOG表示以 10 为底的对数。然而，Python 的 math 模块和 NumPy 使用log表示自然对数，log10表示以 10 为底的对数。

使用 pandas，而不是使用一个访问两个不同行的公式，你可以使用shift方法将值向下移动一行。这样可以让你在单个行上操作，因此你的计算可以利用矢量化。shift接受一个正数或负数，将时间序列向下或向上移动相应数量的行。让我们首先看看shift如何工作：

In``[``20``]:``msft_close``.``head``()

Out[20]: 调整后收盘价日期 1986-03-13 21:00:00+00:00 0.062205 1986-03-14 21:00:00+00:00 0.064427 1986-03-17 21:00:00+00:00 0.065537 1986-03-18 21:00:00+00:00 0.063871 1986-03-19 21:00:00+00:00 0.062760

In``[``21``]:``msft_close``.``shift``(``1``)``.``head``()

Out[21]: 调整后收盘价日期 1986-03-13 21:00:00+00:00 NaN 1986-03-14 21:00:00+00:00 0.062205 1986-03-17 21:00:00+00:00 0.064427 1986-03-18 21:00:00+00:00 0.065537 1986-03-19 21:00:00+00:00 0.063871

现在你可以编写一个简单的基于向量的公式，易于阅读和理解。要获取自然对数，请使用 NumPy 的log ufunc，它应用于每个元素。然后我们可以绘制直方图（见图 6-3）：

In``[``22``]:``returns``=``np``.``log``(``msft_close``/``msft_close``.``shift``(``1``))``returns``=``returns``.``rename``(``columns``=``{``"Adj Close"``:``"returns"``})``returns``.``head``()

Out[22]: returns 日期 1986-03-13 21:00:00+00:00 NaN 1986-03-14 21:00:00+00:00 0.035097 1986-03-17 21:00:00+00:00 0.017082 1986-03-18 21:00:00+00:00 -0.025749 1986-03-19 21:00:00+00:00 -0.017547

In``[``23``]:``# 用每日对数收益率绘制直方图``returns``.``plot``.``hist``()

图 6-3. 直方图绘制

要获得简单的收益率，使用 pandas 内置的pct_change方法。默认情况下，它计算与上一行的百分比变化，这也是简单收益率的定义：

In``[``24``]:``simple_rets``=``msft_close``.``pct_change``()``simple_rets``=``simple_rets``.``rename``(``columns``=``{``"Adj Close"``:``"简单回报"``})``simple_rets``.``head``()

Out[24]: 简单回报日期 1986-03-13 21:00:00+00:00 NaN 1986-03-14 21:00:00+00:00 0.035721 1986-03-17 21:00:00+00:00 0.017229 1986-03-18 21:00:00+00:00 -0.025421 1986-03-19 21:00:00+00:00 -0.017394

到目前为止，我们只看过微软股票。在下一节中，我们将加载更多时间序列，以便查看需要多个时间序列的其他数据框方法。

重新基准化和相关性

当我们处理多个时间序列时，情况会变得稍微有趣。让我们加载亚马逊（AMZN）、谷歌（GOOGL）和苹果（AAPL）的一些额外调整后的收盘价格，这些数据也是从 Yahoo! Finance 下载的。

In``[``25``]:``parts``=``[]``# 用于收集各个数据框的列表``for``ticker``in``[``"AAPL"``,``"AMZN"``,``"GOOGL"``,``"MSFT"``]:``# usecols参数允许我们只读取日期和调整后的收盘价``adj_close``=``pd``.``read_csv``(``f``"csv/{ticker}.csv"``,``index_col``=``"Date"``,``parse_dates``=``[``"Date"``],``usecols``=``[``"Date"``,``"Adj Close"``])``# 将列名改为股票代号``adj_close``=``adj_close``.``rename``(``columns``=``{``"Adj Close"``:``ticker``})``# 将该股票的数据框附加到 parts 列表``parts``.``append``(``adj_close``)

In``[``26``]:``# 将这 4 个数据框合并为一个数据框``adj_close``=``pd``.``concat``(``parts``,``axis``=``1``)``adj_close

Out[26]: AAPL AMZN GOOGL MSFT 日期 1980-12-12 0.405683 NaN NaN NaN 1980-12-15 0.384517 NaN NaN NaN 1980-12-16 0.356296 NaN NaN NaN 1980-12-17 0.365115 NaN NaN NaN 1980-12-18 0.375698 NaN NaN NaN ... ... ... ... ... 2020-05-22 318.890015 2436.879883 1413.239990 183.509995 2020-05-26 316.730011 2421.860107 1421.369995 181.570007 2020-05-27 318.109985 2410.389893 1420.280029 181.809998 2020-05-28 318.250000 2401.100098 1418.239990 NaN 2020-05-29 317.940002 2442.370117 1433.520020 NaN [9950 行 x 4 列]

你看到concat的威力了吗？pandas 自动对齐了各个时间序列的日期。这就是为什么在那些不如 Apple 的股票中你会得到NaN值的原因。而且由于MSFT在最近日期有NaN值，你可能已经猜到我比其他股票提前两天下载了 MSFT.csv 文件。通过日期对齐时间序列是一种典型的操作，在 Excel 中非常繁琐且容易出错。删除所有包含缺失值的行将确保所有股票具有相同的数据点：

In``[``27``]:adj_close=``adj_close``.``dropna``()``adj_close``.``info``()

<class 'pandas.core.frame.DataFrame'> DatetimeIndex: 3970 entries, 2004-08-19 to 2020-05-27 Data columns (total 4 columns): # Column Non-Null Count Dtype --- ------ -------------- ----- 0 AAPL 3970 non-null float64 1 AMZN 3970 non-null float64 2 GOOGL 3970 non-null float64 3 MSFT 3970 non-null float64 dtypes: float64(4) memory usage: 155.1 KB

现在我们将价格重新基准化，使所有时间序列从 100 开始。这样可以在图表中比较它们的相对表现；见图 6-4。要重新基准化时间序列，将每个值除以其起始值并乘以 100，这是新的基准。如果在 Excel 中进行此操作，通常会编写一个结合绝对和相对单元格引用的公式，然后将该公式复制到每行和每个时间序列。在 pandas 中，由于矢量化和广播技术，你只需处理一个公式：

In``[``28``]:# 使用 2019 年 6 月至 2020 年 5 月的样本adj_close_sample=adj_close.loc["2019-06":"2020-05",:]rebased_prices=``adj_close_sample``/``adj_close_sample``.``iloc``[``0``,``:]``*``100``rebased_prices``.``head``(``2``)

Out[28]: AAPL AMZN GOOGL MSFT Date 2019-06-03 100.000000 100.000000 100.00000 100.000000 2019-06-04 103.658406 102.178197 101.51626 102.770372

In``[``29``]:rebased_prices.plot``()

图 6-4. 重新基准化的时间序列

要查看不同股票的回报独立性，可以使用corr方法查看它们的相关性。不幸的是，pandas 没有提供内置的绘制热力图形式的相关矩阵的方法，因此我们需要直接通过其plotly.express接口使用 Plotly（见图 6-5）：

In``[``30``]:# 每日对数收益率的相关性returns=np.log(adj_close/adj_close.shift(1))returns.corr()

Out[30]: AAPL AMZN GOOGL MSFT AAPL 1.000000 0.424910 0.503497 0.486065 AMZN 0.424910 1.000000 0.486690 0.485725 GOOGL 0.503497 0.486690 1.000000 0.525645 MSFT 0.486065 0.485725 0.525645 1.000000

In``[``31``]:importplotly.expressas`px

In``[``32``]:``fig``=``px``.``imshow``(``returns``.``corr``(),``x``=``adj_close``.``columns``,``y``=``adj_close``.``columns``,``color_continuous_scale``=``list``(``reversed``(``px``.``colors``.``sequential``.``RdBu``)),``zmin``=-``1``,``zmax``=``1``)``fig``.``show``()

如果你想详细了解imshow的工作原理，请查看Plotly Express API 文档。

图 6-5. 相关性热力图

到目前为止，我们已经学到了关于时间序列的许多知识，包括如何组合和清理它们，如何计算收益率和相关性。但是，如果你决定日收益率不适合你的分析基础，想要月度收益率呢？如何改变时间序列数据的频率将成为下一节的主题。

重新采样

时间序列的常见任务包括上采样和下采样。上采样意味着将时间序列转换为频率更高的序列，而下采样则意味着将其转换为频率较低的序列。例如，在财务报表中，通常显示月度或季度表现。要将日度时间序列转换为月度时间序列，请使用resample方法，该方法接受频率字符串如M表示月末或BM表示工作日结束。你可以在pandas 文档中找到所有频率字符串的列表。类似于groupby的工作方式，然后链接定义如何重新采样的方法。我使用last来始终获取该月的最后观测值：

In``[``33``]:``end_of_month``=``adj_close``.``resample``(``"M"``)``.``last``()``end_of_month``.``head``()

Out[33]: AAPL AMZN GOOGL MSFT Date 2004-08-31 2.132708 38.139999 51.236237 17.673630 2004-09-30 2.396127 40.860001 64.864868 17.900215 2004-10-31 3.240182 34.130001 95.415413 18.107374 2004-11-30 4.146072 39.680000 91.081078 19.344421 2004-12-31 3.982207 44.290001 96.491493 19.279480

你可以选择除last外的任何在groupby上有效的方法，如sum或mean。还有ohlc，它方便地返回该期间的开盘价、最高价、最低价和收盘价。这可能用作创建股票价格常用的蜡烛图表的源。

如果你只有那些月末时间序列数据，并且需要生成周度时间序列，你需要对时间序列进行上采样。通过使用asfreq，你告诉 pandas 不要应用任何转换，因此你将看到大多数值显示为NaN。如果你想要向前填充最后已知值，使用ffill方法：

In``[``34``]:``end_of_month``.``resample``(``"D"``)``.``asfreq``()``.``head``()``# No transformation

Out[34]: AAPL AMZN GOOGL MSFT 日期 2004-08-31 2.132708 38.139999 51.236237 17.67363 2004-09-01 NaN NaN NaN NaN 2004-09-02 NaN NaN NaN NaN 2004-09-03 NaN NaN NaN NaN 2004-09-04 NaN NaN NaN NaN

`In[35]:end_of_month.resample("W-FRI").ffill().head()# 前向填充

Out[35]: AAPL AMZN GOOGL MSFT 日期 2004-09-03 2.132708 38.139999 51.236237 17.673630 2004-09-10 2.132708 38.139999 51.236237 17.673630 2004-09-17 2.132708 38.139999 51.236237 17.673630 2004-09-24 2.132708 38.139999 51.236237 17.673630 2004-10-01 2.396127 40.860001 64.864868 17.900215

降采样数据是平滑时间序列的一种方式。计算滚动窗口中的统计数据是另一种方式，我们将在下面看到。

滚动窗口

当计算时间序列统计数据时，通常需要一个滚动统计量，如移动平均线。移动平均线查看时间序列的子集（比如 25 天），并在将窗口向前移动一天之前从该子集中取平均值。这将产生一个新的时间序列，更平滑且不易受到异常值的影响。如果你从事算法交易，可能会关注移动平均线与股票价格的交点，并将其（或其变体）作为交易信号。DataFrame 具有rolling方法，接受观察次数作为参数。然后，将其与要使用的统计方法链接起来——对于移动平均线，就是mean。通过查看图 6-6，你可以轻松比较原始时间序列与平滑移动平均线：

In``[``36``]:``# 使用 2019 年数据为 MSFT 绘制移动平均线``msft19``=``msft``.``loc``[``"2019"``,``[``"Adj Close"``]]``.``copy``()``# 将 25 天移动平均线作为新列添加到 DataFrame``msft19``.``loc``[:,``"25day average"``]``=``msft19``[``"Adj Close"``]``.``rolling``(``25``)``.``mean``()``msft19``.``plot``()

图 6-6. 移动平均线图

你可以使用许多其他统计方法来替代mean，包括count、sum、median、min、max、std（标准差）或var（方差）。

到目前为止，我们已经看到了 pandas 最重要的功能。同样重要的是要理解 pandas 的局限性，即使它们可能现在仍然很远。

pandas 的局限性

当你的 DataFrame 开始变得更大时，了解 DataFrame 能容纳的上限是个好主意。与 Excel 不同，Excel 的每个工作表都有大约一百万行和一万二千列的硬性限制，而 pandas 只有软性限制：所有数据必须适合计算机可用的内存。如果情况不是这样，可能有一些简单的解决方案：只加载你需要的数据集中的那些列，或者删除中间结果以释放一些内存。如果这些方法都不起作用，有一些项目可能会让 pandas 用户感到熟悉，但可以处理大数据。其中一个项目是Dask，它在 NumPy 和 pandas 之上工作，允许你通过将数据拆分成多个 pandas DataFrame，并将工作负载分配到多个 CPU 核心或机器上来处理大型数据集。其他与某种 DataFrame 类似的大数据项目包括Modin，Koalas，Vaex，PySpark，cuDF，Ibis和PyArrow。我们将在下一章简要介绍 Modin，但除此之外，在本书中我们不会进一步探索这个主题。

结论

时间序列分析是我认为 Excel 落后最多的领域，所以在阅读本章之后，你可能会明白为什么 pandas 在金融领域如此成功，金融行业严重依赖时间序列。我们已经看到了处理时区、重采样时间序列或生成相关矩阵是多么容易，而这些功能在 Excel 中要么不支持，要么需要繁琐的解决方案。

然而，了解如何使用 pandas 并不意味着你必须放弃 Excel，因为这两个世界可以非常好地配合：pandas DataFrame 是在两个世界之间传输数据的好方法，正如我们将在下一部分中看到的，该部分将介绍绕过 Excel 应用程序完全读写 Excel 文件的方式。这非常有帮助，因为它意味着你可以在 Python 支持的所有操作系统上使用 Python 操作 Excel 文件，包括 Linux。为了开始这段旅程，下一章将向你展示如何使用 pandas 来自动化繁琐的手动流程，比如将 Excel 文件聚合成摘要报告。

第三部分：无需使用 Excel 读写 Excel 文件

第七章：使用 pandas 进行 Excel 文件操作

在经历了六章对工具、Python 和 pandas 的激烈介绍之后，我将给你一个休息时间，并从一个实际案例研究开始本章，让你能够将你新获得的技能应用到实际中：仅需十行 pandas 代码，你就可以将数十个 Excel 文件合并成一份 Excel 报告，准备发送给你的经理们。案例研究之后，我将更深入地介绍 pandas 提供的处理 Excel 文件的工具：read_excel 函数和 ExcelFile 类用于读取，to_excel 方法和 ExcelWriter 类用于写入 Excel 文件。pandas 不依赖 Excel 应用程序来读写 Excel 文件，这意味着本章中的所有代码示例都可以在 Python 运行的任何地方运行，包括 Linux。

案例研究：Excel 报告

这个案例研究受到我在过去几年参与的几个真实报告项目的启发。尽管这些项目涉及完全不同的行业—包括电信、数字营销和金融—但它们仍然非常相似：起点通常是一个包含需要处理为 Excel 报告的 Excel 文件的目录—通常是每月、每周或每日的基础上。在伴随的存储库中，在 sales_data 目录中，你会找到用于电信供应商销售不同套餐（Bronze、Silver、Gold）的虚构销售交易的 Excel 文件，这些套餐分布在美国的几家商店中。对于每个月，都有两个文件，一个在 new 子文件夹中用于新合同，另一个在 existing 子文件夹中用于现有客户。由于这些报告来自不同的系统，它们有不同的格式：新客户以 xlsx 文件的形式交付，而现有客户则以旧的 xls 格式交付。每个文件最多有 10,000 笔交易，我们的目标是生成一个 Excel 报告，显示每个商店和月份的总销售额。要开始，请查看 new 子文件夹中的 January.xlsx 文件中的 Figure 7-1。

图 7-1. January.xlsx 的前几行

existing 子文件夹中的 Excel 文件看起来几乎相同，只是缺少 status 列，并存储在传统的 xls 格式中。作为第一步，让我们使用 pandas 的 read_excel 函数读取 1 月份的新交易：

In``[``1``]:``import``pandas``as``pd

`In[2]:df=pd.read_excel("sales_data/new/January.xlsx")df.info()

<class 'pandas.core.frame.DataFrame'> RangeIndex: 9493 entries, 0 to 9492 Data columns (total 7 columns): # Column Non-Null Count Dtype --- ------ -------------- ----- 0 transaction_id 9493 non-null object 1 store 9493 non-null object 2 status 9493 non-null object 3 transaction_date 9493 non-null datetime64[ns] 4 plan 9493 non-null object 5 contract_type 9493 non-null object 6 amount 9493 non-null float64 dtypes: datetime64ns** float64(1), object(5) memory usage: 519.3+ KB

使用 Python 3.9 的 READ_EXCEL 函数

这与第 5 章中的警告相同：如果您使用 Python 3.9 或更高版本运行pd.read_excel，请确保至少使用 pandas 1.2，否则在读取 xlsx 文件时会出错。

如您所见，pandas 已正确识别了所有列的数据类型，包括transaction_date的日期格式。这使我们能够在不需要进一步准备数据的情况下处理数据。由于此示例故意简单，我们可以继续创建一个名为 sales_report_pandas.py 的短脚本，如示例 7-1 所示。此脚本将从两个目录中读取所有 Excel 文件，聚合数据，并将汇总表写入新的 Excel 文件中。使用 VS Code 自己编写脚本，或者从伴随的代码库中打开它。如需了解如何在 VS Code 中创建或打开文件，请再次参考第 2 章。如果您自己创建，请确保将其放在 sales_data 文件夹旁边——这样可以在无需调整任何文件路径的情况下运行脚本。

示例 7-1. sales_report_pandas.py

from``pathlib``import``Path``import``pandas``as``pd``# 本文件的目录``this_dir``=``Path``(``__file__``)``.``resolve``()``.``parent# 从 sales_data 的所有子文件夹中读取所有 Excel 文件``parts``=``[]``for``path``in``(``this_dir``/``"sales_data"``)``.``rglob``(``"*.xls*"``):print``(``f``'读取 {path.name}'``)``part``=``pd``.``read_excel``(``path``,``index_col``=``"transaction_id"``)``parts``.``append``(``part``)``# 将每个文件的 DataFrame 合并为单个 DataFrame``# pandas 会正确对齐列``df``=``pd``.``concat``(``parts``)``# 将每个商店转换为列，并按日期汇总所有交易``pivot``=``pd``.``pivot_table``(``df``,``index``=``"transaction_date"``,``columns``=``"store"``,``values``=``"amount"``,``aggfunc``=``"sum"``)``# 重新采样到月底并分配索引名称``summary``=``pivot``.``resample``(``"M"``)``.``sum``()``summary``.``index``.``name``=``"Month"``# 将汇总报告写入 Excel 文件``summary``.``to_excel``(``this_dir``/``"sales_report_pandas.xlsx"``)

在本章之前，我一直使用字符串来指定文件路径。通过使用标准库的 pathlib 模块中的 Path 类，你可以获得一套强大的工具：路径对象使您能够通过连接个别部分使用斜杠来轻松构建路径，正如下面四行代码中使用 this_dir / "sales_data" 一样。这些路径适用于各种平台，并允许您像在下一点解释的那样使用 rglob 等过滤器。当您运行时，__file__ 将解析为源代码文件的路径——使用其 parent 将因此给出此文件所在目录的名称。我们在调用 parent 之前使用的 resolve 方法将路径转换为绝对路径。如果您在 Jupyter 笔记本中运行此代码，则必须使用 this_dir = Path(".").resolve() 替换此行，其中点表示当前目录。在大多数情况下，接受字符串形式路径的函数和类也接受路径对象。

从特定目录递归读取所有 Excel 文件的最简单方法是使用路径对象的 rglob 方法。glob 是通配符扩展的简写，用于使用通配符进行路径名扩展。? 通配符代表一个字符，而 * 代表任意数量的字符（包括零个）。rglob 中的 r 表示递归通配符，即它将在所有子目录中查找匹配的文件——相应地，glob 将忽略子目录。使用 *.xls* 作为通配符表达式确保可以找到旧的和新的 Excel 文件，因为它匹配 .xls 和 .xlsx。通常建议稍微增强表达式，例如 [!~$]*.xls*。这会忽略临时 Excel 文件（它们的文件名以 ~$ 开头）。有关如何在 Python 中使用通配符扩展的更多背景信息，请参阅 Python 文档。

运行脚本，例如，通过点击 VS Code 右上角的“运行文件”按钮。脚本将需要一段时间来完成，完成后，Excel 工作簿 sales_report_pandas.xlsx 将出现在与脚本相同的目录中。Sheet1 的内容应该像图 7-2 中所示。这对于只有十行代码的结果相当令人印象深刻——即使您需要调整第一列的宽度以查看日期！

图 7-2. sales_report_pandas.xlsx（原样，不调整任何列宽）

对于像这样简单的情况，pandas 提供了处理 Excel 文件的非常简便的解决方案。但是，我们可以做得更好——毕竟，一个标题、一些格式（包括列宽和一致的小数位数）以及一个图表都不会有什么坏处。这正是我们将在下一章中通过直接使用 pandas 底层使用的写入库来解决的问题。然而，在我们到达那里之前，让我们更详细地看看如何使用 pandas 读取和写入 Excel 文件。

使用 pandas 读写 Excel 文件

该案例研究使用 read_excel 和 to_excel 的默认参数来简化操作。在本节中，我将介绍使用 pandas 读写 Excel 文件时最常用的参数和选项。我们将从 read_excel 函数和 ExcelFile 类开始，然后再看看 to_excel 方法和 ExcelWriter 类。在此过程中，我还会介绍 Python 的 with 语句。

read_excel 函数和 ExcelFile 类

该案例研究使用的是 Excel 工作簿，数据方便地位于第一个工作表的 A1 单元格。实际情况下，您的 Excel 文件可能没有那么好组织。在这种情况下，pandas 提供了参数来优化读取过程。接下来的示例中，我们将使用 companion 存储库的 xl 文件夹中的 stores.xlsx 文件。第一个工作表显示在 Figure 7-3 中。

图 7-3. stores.xlsx 的第一个工作表

通过使用 sheet_name、skiprows 和 usecols 参数，我们可以告诉 pandas 我们想要读取的单元格范围。通常情况下，通过运行 info 方法查看返回的 DataFrame 的数据类型是个好主意：

In``[``3``]:``df``=``pd``.``read_excel``(``"xl/stores.xlsx"``,``sheet_name``=``"2019"``,``skiprows``=``1``,``usecols``=``"B:F"``)``df

Out[3]: Store Employees Manager Since Flagship 0 New York 10 Sarah 2018-07-20 False 1 San Francisco 12 Neriah 2019-11-02 MISSING 2 Chicago 4 Katelin 2020-01-31 NaN 3 Boston 5 Georgiana 2017-04-01 True 4 Washington DC 3 Evan NaT False 5 Las Vegas 11 Paul 2020-01-06 False

`In[4]:df.info``()

<class 'pandas.core.frame.DataFrame'> RangeIndex: 6 entries, 0 to 5 Data columns (total 5 columns): # Column Non-Null Count Dtype --- ------ -------------- ----- 0 Store 6 non-null object 1 Employees 6 non-null int64 2 Manager 6 non-null object 3 Since 5 non-null datetime64[ns] 4 Flagship 5 non-null object dtypes: datetime64ns** int64(1), object(3) memory usage: 368.0+ bytes

除了 Flagship 列之外，一切看起来都很好——它的数据类型应为 bool 而不是 object。为了修复这个问题，我们可以提供一个转换函数，处理该列中的问题单元格（而不是编写 fix_missing 函数，我们也可以使用 lambda 表达式代替）：

In``[``5``]:``def``fix_missing``(``x``):``return``False``if``x``in``[``""``,``"MISSING"``]``else``x

In``[``6``]:``df``=``pd``.``read_excel``(``"xl/stores.xlsx"``,``sheet_name``=``"2019"``,``skiprows``=``1``,``usecols``=``"B:F"``,``converters``=``{``"Flagship"``:``fix_missing``})``df

Out[6]: Store Employees Manager Since Flagship 0 纽约 10 莎拉 2018-07-20 False 1 旧金山 12 尼赖亚 2019-11-02 False 2 芝加哥 4 凯特琳 2020-01-31 False 3 波士顿 5 乔治安娜 2017-04-01 True 4 华盛顿特区 3 伊万 NaT False 5 拉斯维加斯 11 保罗 2020-01-06 False

`In[7]:# Flagship 列现在的 Dtype 是 "bool"df.info()

<class 'pandas.core.frame.DataFrame'> RangeIndex: 6 entries, 0 to 5 Data columns (total 5 columns): # Column Non-Null Count Dtype --- ------ -------------- ----- 0 分店 6 non-null object 1 员工数量 6 non-null int64 2 经理 6 non-null object 3 自从 5 non-null datetime64[ns] 4 是否旗舰店 6 non-null bool dtypes: bool(1), datetime64ns** int64(1), object(2) memory usage: 326.0+ bytes

read_excel 函数还接受一个工作表名称列表。在这种情况下，它将返回一个字典，其中 DataFrame 作为值，工作表名称作为键。要读取所有工作表，您需要提供 sheet_name=None。还请注意，我在使用 usecols 时提供了表格列名的略微变体：

In``[``8``]:``sheets``=``pd``.``read_excel``(``"xl/stores.xlsx"``,``sheet_name``=``[``"2019"``,``"2020"``],``skiprows``=``1``,``usecols``=``[``"分店"``,``"员工数量"``])``sheets``[``"2019"``]``.``head``(``2``)

Out[8]: 分店员工数量 0 纽约 10 1 旧金山 12

如果源文件没有列标题，请将 header=None 设置，并通过 names 提供它们。请注意，sheet_name 还接受工作表索引：

In``[``9``]:``df``=``pd``.``read_excel``(``"xl/stores.xlsx"``,``sheet_name``=``0``,``skiprows``=``2``,``skipfooter``=``3``,``usecols``=``"B:C,F"``,``header``=``None``,``names``=``[``"分店"``,``"员工数量"``,``"是否旗舰店"``])``df

Out[9]: 分店员工数量是否旗舰店 0 纽约 10 False 1 旧金山 12 MISSING 2 芝加哥 4 NaN

要处理 NaN 值，请使用 na_values 和 keep_default_na 的组合。下一个示例告诉 pandas 仅将包含 MISSING 字样的单元格解释为 NaN，而不是其他任何内容：

In``[``10``]:``df``=``pd``.``read_excel``(``"xl/stores.xlsx"``,``sheet_name``=``"2019"``,``skiprows``=``1``,``usecols``=``"B,C,F"``,``skipfooter``=``2``,``na_values``=``"MISSING"``,``keep_default_na``=``False``)``df

Out[10]: 分店员工数量是否旗舰店 0 纽约 10 False 1 旧金山 12 NaN 2 芝加哥 4 3 波士顿 5 True

pandas 提供了另一种通过使用ExcelFile类读取 Excel 文件的方法。这主要在您想要从遗留 xls 格式文件中读取多个工作表时有所不同：在这种情况下，使用ExcelFile将更快，因为它防止 pandas 多次读取整个文件。ExcelFile可以作为上下文管理器使用（参见侧边栏），因此文件将再次被正确关闭。

上下文管理器和with语句

首先，Python 中的with语句与 VBA 中的With语句没有任何关系：在 VBA 中，它用于在同一对象上运行一系列语句，而在 Python 中，它用于管理文件或数据库连接等资源。如果要加载最新的销售数据以便进行分析，可能需要打开文件或建立到数据库的连接。在完成数据读取后，尽快关闭文件或连接是最佳实践。否则，您可能会遇到无法打开另一个文件或建立另一个数据库连接的情况——文件处理程序和数据库连接是有限资源。手动打开和关闭文本文件的方法如下（w表示以写模式打开文件，如果文件已存在则替换它）：

`In[11]:f=open("output.txt","w")f.write("Some text")f.close()

运行此代码将在与正在运行代码的笔记本相同的目录中创建一个名为 output.txt 的文件，并将“Some text”写入其中。要读取文件，您将使用r而不是w，要在文件末尾追加内容，使用a。由于文件也可以从程序外部进行操作，此类操作可能会失败。您可以通过使用我将在第十一章中介绍的 try/except 机制来处理此问题。然而，由于这是一个常见的操作，Python 提供了with语句以简化操作：

In``[``12``]:``with``open``(``"output.txt"``,``"w"``)``as``f``:``f``.``write``(``"Some text"``)

当代码执行离开with语句的主体时，无论是否发生异常，文件都会自动关闭。这确保资源得到适当的清理。支持with语句的对象称为上下文管理器；这包括本章中的ExcelFile和ExcelWriter对象，以及我们将在第十一章中查看的数据库连接对象。

让我们看看ExcelFile类的实际应用：

In``[``13``]:``with``pd``.``ExcelFile``(``"xl/stores.xls"``)``as``f``:``df1``=``pd``.``read_excel``(``f``,``"2019"``,``skiprows``=``1``,``usecols``=``"B:F"``,``nrows``=``2``)``df2``=``pd``.``read_excel``(``f``,``"2020"``,``skiprows``=``1``,``usecols``=``"B:F"``,``nrows``=``2``)``df1

Out[13]: Store Employees Manager Since Flagship 0 纽约 10 莎拉 2018-07-20 False 1 旧金山 12 尼莉亚 2019-11-02 MISSING

ExcelFile还允许您访问所有工作表的名称：

In``[``14``]:``stores``=``pd``.``ExcelFile``(``"xl/stores.xlsx"``)``stores``.``sheet_names

Out[14]: ['2019', '2020', '2019-2020']

最后，pandas 允许您从 URL 直接读取 Excel 文件，类似于我们在第五章中读取 CSV 文件的方法。让我们直接从伴随存储库中读取：

In``[``15``]:``url``=``(``"https://raw.githubusercontent.com/fzumstein/"``"python-for-excel/1st-edition/xl/stores.xlsx"``)``pd``.``read_excel``(``url``,``skiprows``=``1``,``usecols``=``"B:E"``,``nrows``=``2``)

Out[15]: Store Employees Manager Since 0 纽约 10 莎拉 2018-07-20 1 旧金山 12 尼莉亚 2019-11-02

通过 pandas 读取 XLSB 文件

如果你使用版本低于 1.3 的 pandas，读取 xlsb 文件需要在read_excel函数或ExcelFile类中显式指定引擎：

pd``.``read_excel``(``"xl/stores.xlsb"``,``engine``=``"pyxlsb"``)

这需要安装pyxlsb包，因为它不是 Anaconda 的一部分——我们将在下一章节介绍其它引擎时详细讨论。

总结一下，表格 7-1 展示了最常用的read_excel参数。你可以在官方文档中找到完整列表。

Table 7-1. read_excel的选定参数

参数	描述
`sheet_name`	你可以提供一个零基索引的表名，例如，`sheet_name=0`。如果你设置`sheet_name=None`，pandas 将读取整个工作簿，并返回形如`{"sheetname": df}`的字典。如果要读取选择的几个表，可以提供一个包含表名或索引的列表。
`skiprows`	这允许您跳过指定数量的行。
`usecols`	如果 Excel 文件包含列标题的名称，请以列表形式提供它们来选择列，例如，`["Store", "Employees"]`。或者，可以是列索引的列表，例如，`[1, 2]`，或者一个包含 Excel 列名（包括范围）的字符串，例如，`"B:D,G"`。还可以提供一个函数：例如，只包含以`Manager`开头的列，请使用：`usecols=lambda x: x.startswith("Manager")`。
`nrows`	你要读取的行数。
`index_col`	指示应该作为索引的列，可以是列名或索引，例如，`index_col=0`。如果提供多列的列表，将创建一个分层索引。
`header`	如果设置`header=None`，则分配默认整数标题，除非通过`names`参数提供所需的名称。如果提供索引列表，将创建分层列标题。
`names`	以列表形式提供您的列的期望名称。
`na_values`	默认情况下，pandas 将以下单元格值解释为`NaN`（我在第五章介绍了`NaN`）：空单元格、`#NA`、`NA`、`null`、`#N/A`、`N/A`、`NaN`、`n/a`、`-NaN`、`1.#IND`、`nan`、`#N/A N/A`、`-1.#QNAN`、`-nan`、`NULL`、`-1.#IND`、`<NA>`、`1.#QNAN`。如果您想要添加一个或多个值到该列表，请通过`na_values`提供它们。
`keep_default_na`	如果您想忽略 pandas 解释为`NaN`的默认值，请设置`keep_default_na=False`。
`convert_float`	Excel 将所有数字内部存储为浮点数，并且默认情况下，pandas 会将没有意义小数的数字转换为整数。如果您想改变这种行为，请设置`convert_float=False`（这可能会稍快一些）。
`converters`	允许您为每列提供一个函数以转换其值。例如，要使某一列中的文本变为大写，使用以下内容：`converters={"column_name": lambda x: x.upper()}`。

读取 Excel 文件与 pandas 相关的内容就这么多，接下来我们转向另一方面，学习如何在下一节中写入 Excel 文件！

to_excel 方法和 ExcelWriter 类

使用 pandas 写入 Excel 文件的最简单方法是使用 DataFrame 的to_excel方法。它允许您指定要将 DataFrame 写入的哪个工作表的哪个单元格。您还可以决定是否包括 DataFrame 的列标题和索引，以及如何处理 Excel 中没有等价表示的数据类型，如np.nan和np.inf。让我们从创建具有不同数据类型的 DataFrame 开始，并使用其to_excel方法：

In``[``16``]:``import``numpy``as``np``import``datetime``as``dt

`In[17]:data=[[dt.datetime(2020,1,1,10,13),2.222,1,True],[dt.datetime(2020,1,2),np.nan,2,False],[dt.datetime(2020,1,2),np.inf,3,True]]df=pd.DataFrame(data=data,columns=["Dates","Floats","Integers","Booleans"])df.index.name="index"``df

Out[17]: 日期浮点数整数布尔值索引 0 2020-01-01 10:13:00 2.222 1 True 1 2020-01-02 00:00:00 NaN 2 False 2 2020-01-02 00:00:00 inf 3 True

In``[``18``]:``df``.``to_excel``(``"written_with_pandas.xlsx"``,``sheet_name``=``"Output"``,``startrow``=``1``,``startcol``=``1``,``index``=``True``,``header``=``True``,``na_rep``=``"<NA>"``,``inf_rep``=``"<INF>"``)

运行 to_excel 命令将创建 Excel 文件，如 Figure 7-4 所示（您需要扩展列 C 以正确查看日期）。

图 7-4. written_with_pandas.xlsx

如果要将多个 DataFrame 写入同一张或不同的工作表中，需要使用 ExcelWriter 类。以下示例将相同的 DataFrame 写入 Sheet1 的两个不同位置，并再次写入 Sheet2：

`In[19]:使用pd.ExcelWriter("written_with_pandas2.xlsx")作为writer:df.to_excel(writer,sheet_name="Sheet1",startrow=1,startcol=1)df.to_excel(writer,sheet_name="Sheet1",startrow=10,startcol=1)df.to_excel(writer,sheet_name="Sheet2")

由于我们使用 ExcelWriter 类作为上下文管理器，当退出上下文管理器时（即缩进停止时），文件会自动写入磁盘。否则，您需要显式调用 writer.save()。要获取 to_excel 接受的最常用参数摘要，请参阅 Table 7-2。您可以在官方文档中找到所有参数的完整列表。

表 7-2. to_excel 的选定参数

参数	描述
`sheet_name`	要写入的工作表名称。
`startrow` 和 `startcol`	`startrow` 是 DataFrame 将写入的第一行，`startcol` 是第一列。这使用从零开始的索引，因此如果要将 DataFrame 写入到 B3 单元格，使用 `startrow=2` 和 `startcol=1`。
`index` 和 `header`	如果要隐藏索引和/或标题，将它们分别设置为 `index=False` 和 `header=False`。
`na_rep` 和 `inf_rep`	默认情况下，`np.nan` 将转换为空单元格，而 `np.inf`（NumPy 表示的无穷大）将转换为字符串 `inf`。提供值可以更改此行为。
`freeze_panes`	通过提供一个元组来冻结前几行和列：例如 `(2, 1)` 将冻结前两行和第一列。

如您所见，使用 pandas 轻松读写简单的 Excel 文件效果很好。然而，有一些限制——让我们来看看是哪些限制！

使用 pandas 处理 Excel 文件的限制

使用 pandas 接口读写 Excel 文件在简单情况下效果很好，但也有一些限制：

当将 DataFrame 写入文件时，无法包含标题或图表。
在 Excel 中无法更改默认的标题和索引格式。
在读取文件时，pandas 自动将带有诸如 #REF! 或 #NUM! 的错误单元格转换为 NaN，这使得在电子表格中搜索特定错误变得不可能。
处理大型 Excel 文件可能需要额外的设置，通过直接使用读取器和写入器包更容易控制，我们将在下一章中讨论。

结论

pandas 的好处在于，它提供了一个一致的接口来处理所有支持的 Excel 文件格式，无论是 xls、xlsx、xlsm 还是 xlsb。这使得我们能够轻松读取一个目录中的 Excel 文件，聚合数据，并将摘要信息仅用十行代码转储到 Excel 报告中。

pandas，然而，并不是自己完成繁重的工作：在幕后，它选择一个读取器或写入器包来完成工作。在下一章中，我将向你展示 pandas 使用哪些读取器和写入器包，并且如何直接或与 pandas 结合使用它们。这将使我们能够解决我们在前一节中看到的限制。

第八章：使用读取器和写入器包进行 Excel 文件操作

本章介绍了 OpenPyXL、XlsxWriter、pyxlsb、xlrd 和 xlwt：这些是可以读写 Excel 文件的包，并且在调用 read_excel 或 to_excel 函数时被 pandas 底层使用。直接使用读取器和写入器包可以让您创建更复杂的 Excel 报告，并且可以微调读取过程。此外，如果您曾经需要在一个只需要读写 Excel 文件而不需要其他 pandas 功能的项目中工作，安装完整的 NumPy/pandas 堆栈可能会有些过火。我们将从学习何时使用哪个包以及它们的语法工作开始本章，然后再讨论一些高级主题，包括如何处理大型 Excel 文件以及如何将 pandas 与读取器和写入器包结合起来以改进 DataFrame 的样式。最后，我们将再次回顾上一章开头的案例研究，并通过格式化表格和添加图表来增强 Excel 报告。与上一章类似，本章不需要安装 Excel，这意味着所有的代码示例都可以在 Windows、macOS 和 Linux 上运行。

读取器和写入器包

读取器和写入器的使用场景可能有些令人不知所措：本节将介绍不少于六个包，因为几乎每种 Excel 文件类型都需要不同的包。每个包使用不同的语法，通常与原始的 Excel 对象模型有很大不同，这并不让人更容易——我将在下一章中更多地讨论 Excel 对象模型。这意味着即使您是经验丰富的 VBA 开发人员，您也可能需要查找很多命令。本节从概述何时需要使用哪个包开始，然后介绍一个辅助模块，使使用这些包变得稍微容易一些。之后，它以食谱的形式呈现每个包，您可以查阅最常用命令的工作方式。

何时使用哪个包

本节介绍了以下六个用于读取、写入和编辑 Excel 文件的包：

要了解哪个包可以做什么，请查看表 8-1。例如，要读取 xlsx 文件格式，您将需要使用 OpenPyXL 包：

表 8-1. 何时使用哪个包

Excel 文件格式	读取	写入	编辑
`xlsx`	OpenPyXL	OpenPyXL, XlsxWriter	OpenPyXL
`xlsm`	OpenPyXL	OpenPyXL, XlsxWriter	OpenPyXL
`xltx`, `xltm`	OpenPyXL	OpenPyXL	OpenPyXL
`xlsb`	pyxlsb	-	-
`xls`, `xlt`	xlrd	xlwt	xlutils

如果你想写入 xlsx 或 xlsm 文件，你需要在 OpenPyXL 和 XlsxWriter 之间做出选择。这两个包都涵盖了类似的功能，但每个包可能有一些其他包没有的独特功能。由于这两个库都在积极开发中，这种情况随着时间的推移而变化。以下是它们的区别的高级概述：

OpenPyXL 可以读取、写入和编辑，而 XlsxWriter 只能写入。
OpenPyXL 更容易生成带有 VBA 宏的 Excel 文件。
XlsxWriter 的文档更好。
XlsxWriter 的速度比 OpenPyXL 快，但根据你写入的工作簿的大小，差异可能不显著。

XLWINGS 在哪里？

如果你想知道表表 8-1 中的 xlwings 在哪里，答案是无处不在或无处不在，这取决于你的用例：与本章中的任何包不同，xlwings 依赖于 Excel 应用程序，这通常是不可用的，例如，如果你需要在 Linux 上运行你的脚本。另一方面，如果你愿意在 Windows 或 macOS 上运行你的脚本，那么你就可以访问安装的 Excel，xlwings 确实可以作为本章中所有包的替代品使用。由于 Excel 依赖性是 xlwings 与所有其他 Excel 包之间的基本差异，我会在下一章中介绍 xlwings，这一章开始了本书的第四部分。

pandas 使用它能找到的 writer 包，如果你同时安装了 OpenPyXL 和 XlsxWriter，XlsxWriter 就是默认的。如果你想选择 pandas 应该使用哪个包，请在 read_excel 或 to_excel 函数的 engine 参数中指定，或者在 ExcelFile 和 ExcelWriter 类中分别指定。引擎是包名的小写形式，因此要使用 OpenPyXL 而不是 XlsxWriter 写文件，运行以下命令：

df``.``to_excel``(``"filename.xlsx"``,``engine``=``"openpyxl"``)

一旦你知道你需要哪个包，就会有第二个挑战在等着你：大多数这些包需要你写相当多的代码来读取或写入一系列单元格，而且每个包都使用不同的语法。为了让你的生活更轻松，我创建了一个辅助模块，我将在下面介绍。

excel.py 模块

我创建了 excel.py 模块，以便在使用读取器和写入器包时让你的生活更轻松，因为它解决了以下问题：

包交换

不得不切换读取器或写入器包是一个相对常见的情况。例如，Excel 文件往往会随着时间的推移而增大，许多用户通过将文件格式从 xlsx 切换到 xlsb 来对抗这一点，因为这可以大幅减小文件大小。在这种情况下，你将不得不从 OpenPyXL 切换到 pyxlsb。这会迫使你重写 OpenPyXL 代码以反映 pyxlsb 的语法。

数据类型转换

这与前面的观点有关：在切换包时，不仅需要调整代码的语法，还需要注意这些包针对相同单元格内容返回的不同数据类型。例如，OpenPyXL 对空单元格返回 None，而 xlrd 返回空字符串。

单元格循环

读取器和写入器包是低级别包：这意味着它们缺乏能让你轻松处理常见任务的便利函数。例如，大多数包要求你循环遍历每个要读取或写入的单元格。

你可以在配套的仓库中找到 excel.py 模块，并且我们将在即将展示的章节中使用它。作为预览，这里是读取和写入值的语法：

import``excel``values``=``excel``.``read``(``sheet_object``,``first_cell``=``"A1"``,``last_cell``=``None``)``excel``.``write``(``sheet_object``,``values``,``first_cell``=``"A1"``)

read 函数接受来自以下包之一的 sheet 对象：xlrd、OpenPyXL 或 pyxlsb。它还接受可选参数 first_cell 和 last_cell。它们可以以 A1 表示法或带有 Excel 基于一的索引的行列元组 (1, 1) 提供。first_cell 的默认值是 A1，而 last_cell 的默认值是已使用范围的右下角。因此，如果只提供 sheet 对象，它将读取整个工作表。write 函数工作方式类似：它期望来自 xlwt、OpenPyXL 或 XlsxWriter 的 sheet 对象以及作为嵌套列表的值，以及一个可选的 first_cell，它标记了嵌套列表将写入的左上角位置。excel.py 模块还展示了如表 8-2 所示的数据类型转换的协调工作。

表 8-2. 数据类型转换

Excel 表示	Python 数据类型
空单元格	`None`
带有日期格式的单元格	`datetime.datetime`（除了 pyxlsb）
布尔值单元格	`bool`
带有错误的单元格	`str`（错误消息）
字符串	`str`
浮点数	`float` 或 `int`

现在我们装备好使用 excel.py 模块，准备深入探讨以下几个包：OpenPyXL、XlsxWriter、pyxlsb 和 xlrd/xlwt/xlutils。这些部分都采用了食谱式的样式，让你可以快速入门每个包。建议你根据表 8-1 选择你需要的包，然后直接跳转到相应的章节，而不是顺序阅读。

WITH 语句

本章中我们会在多个场合使用 with 语句。如果需要温习，请参阅第七章侧边栏 “上下文管理器和 with 语句”。

OpenPyXL

OpenPyXL 是本节唯一一个既能读取又能写入 Excel 文件的包。你甚至可以用它来编辑 Excel 文件——尽管只能是简单的文件。让我们先来看看如何进行读取操作！

使用 OpenPyXL 进行读取

下面的示例代码展示了在使用 OpenPyXL 读取 Excel 文件时执行常见任务的方法。要获取单元格的值，需要使用 data_only=True 打开工作簿。默认为 False，这会返回单元格的公式而不是值：

In``[``1``]:``import``pandas``as``pd``import``openpyxl``import``excel``import``datetime``as``dt

In``[``2``]:``# 打开工作簿以读取单元格值。``# 加载数据后文件会自动关闭。``book``=``openpyxl``.``load_workbook``(``"xl/stores.xlsx"``,``data_only``=``True``)

In``[``3``]:``# 根据名称或索引（从 0 开始）获取工作表对象``sheet``=``book``[``"2019"``]``sheet``=``book``.``worksheets``[``0``]

In``[``4``]:``# 获取包含所有工作表名称的列表``book``.``sheetnames

Out[4]: ['2019', '2020', '2019-2020']

In``[``5``]:``# 遍历工作表对象。``# openpyxl 使用 "title" 而不是 "name"。``for``i``in``book``.``worksheets``:``print``(``i``.``title``)

2019 2020 2019-2020

In``[``6``]:``# 获取工作表的维度，即工作表的使用范围``sheet``.``max_row``,``sheet``.``max_column

Out[6]: (8, 6)

In``[``7``]:``# 读取单个单元格的值``# 使用 "A1" 表示法和单元格索引（基于 1 的索引）``sheet``[``"B6"``]``.``value``sheet``.``cell``(``row``=``6``,``column``=``2``)``.``value

Out[7]: 'Boston'

In``[``8``]:``# 使用我们的 excel 模块读取单元格值的范围``data``=``excel``.``read``(``book``[``"2019"``],``(``2``,``2``),``(``8``,``6``))``data``[:``2``]``# 打印前两行的数据

Out[8]: [['Store', 'Employees', 'Manager', 'Since', 'Flagship'], ['New York', 10, 'Sarah', datetime.datetime(2018, 7, 20, 0, 0), False]]

使用 OpenPyXL 进行写入

OpenPyXL 在内存中构建 Excel 文件，并在调用 save 方法时写出文件。以下代码生成了图 8-1 中显示的文件：

In``[``9``]:``import``openpyxl``from``openpyxl.drawing.image``import``Image``from``openpyxl.chart``import``BarChart``,``Reference``from``openpyxl.styles``import``Font``,``colors``from``openpyxl.styles.borders``import``Border``,``Side``from``openpyxl.styles.alignment``import``Alignment``from``openpyxl.styles.fills``import``PatternFill``import``excel

`In[10]:# 实例化一个工作簿book=openpyxl.Workbook()# 获取第一个工作表并命名sheet=book.activesheet.title="Sheet1"# 使用 A1 表示法和单元格索引（从 1 开始）写入单个单元格sheet["A1"].value="Hello 1"sheet.cell(row=2,column=1,value="Hello 2")# 格式化：填充颜色，对齐，边框和字体font_format=Font(color="FF0000",bold=True)thin=Side(border_style="thin",color="FF0000")sheet["A3"].value="Hello 3"sheet["A3"].font=font_formatsheet["A3"].border=Border(top=thin,left=thin,right=thin,bottom=thin)sheet["A3"].alignment=Alignment(horizontal="center")sheet["A3"].fill=PatternFill(fgColor="FFFF00",fill_type="solid")# 数字格式化（使用 Excel 的格式字符串）sheet["A4"].value=3.3333sheet["A4"].number_format="0.00"# 日期格式化（使用 Excel 的格式字符串）sheet["A5"].value=dt.date(2016,10,13)sheet["A5"].number_format="mm/dd/yy"# 公式：必须使用公式的英文名称# 并使用逗号作为分隔符sheet["A6"].value="=SUM(A4, 2)"# 图像sheet.add_image(Image("images/python.png"),"C1")# 二维列表（我们正在使用我们的 excel 模块）data=[[None,"North","South"],["Last Year",2,5],["This Year",3,6]]excel.write(sheet,data,"A10")# 图表chart=BarChart()chart.type="col"chart.title="按地区销售"chart.x_axis.title="地区"chart.y_axis.title="销售额"chart_data=Reference(sheet,min_row=11,min_col=1,max_row=12,max_col=3)chart_categories=Reference(sheet,min_row=10,min_col=2,max_row=10,max_col=3)# from_rows 解释了数据，与在 Excel 中手动添加图表的方式相同chart.add_data(chart_data,titles_from_data=True,from_rows=True)chart.set_categories(chart_categories)sheet.add_chart(chart,"A15")# 保存工作簿将文件保存到磁盘book.save("openpyxl.xlsx"``)

如果你想要编写一个 Excel 模板文件，在保存之前，你需要将template属性设置为True：

In``[``11``]:``book``=``openpyxl``.``Workbook``()``sheet``=``book``.``active``sheet``[``"A1"``]``.``value``=``"这是一个模板"``book``.``template``=``True``book``.``save``(``"template.xltx"``)

正如您在代码中所见，OpenPyXL 通过提供类似FF0000的字符串来设置颜色。该值由三个十六进制值（FF、00和00）组成，分别对应所需颜色的红/绿/蓝值。十六进制表示十六进制数，使用十六进制基数而不是我们标准十进制系统使用的十进制基数。

查找颜色的十六进制值

要在 Excel 中找到所需颜色的十六进制值，请单击用于更改单元格填充颜色的油漆下拉菜单，然后选择更多颜色。现在选择您的颜色，并从菜单中读取其十六进制值。

图 8-1. 由 OpenPyXL 编写的文件（openpyxl.xlsx）

使用 OpenPyXL 进行编辑

没有读取/写入包可以真正编辑 Excel 文件：实际上，OpenPyXL 读取文件时理解其中的一切，然后再次从头开始编写文件——包括您在其中进行的任何更改。这对于主要包含格式化单元格及数据和公式的简单 Excel 文件非常有用，但对于包含图表和其他更高级内容的电子表格而言，其功能有限，因为 OpenPyXL 要么会更改它们，要么完全删除它们。例如，截至 v3.0.5，OpenPyXL 会重命名图表并删除它们的标题。这里是一个简单的编辑示例：

In``[``12``]:``# 读取 stores.xlsx 文件，更改单元格# 并将其存储在新位置/名称下。``book``=``openpyxl``.``load_workbook``(``"xl/stores.xlsx"``)``book``[``"2019"``][``"A1"``]``.``value``=``"modified"``book``.``save``(``"stores_edited.xlsx"``)

如果您想要编写一个 xlsm 文件，OpenPyXL 必须基于一个现有文件进行操作，您需要使用keep_vba参数设置为True加载该文件：

In``[``13``]:``book``=``openpyxl``.``load_workbook``(``"xl/macro.xlsm"``,``keep_vba``=``True``)``book``[``"Sheet1"``][``"A1"``]``.``value``=``"Click the button!"``book``.``save``(``"macro_openpyxl.xlsm"``)

示例文件中的按钮调用了一个显示消息框的宏。 OpenPyXL 涵盖的功能远比我在本节中能够覆盖的要多；因此，建议查看官方文档。在本章末尾，当我们再次接手上一章的案例研究时，我们还将看到更多功能。

XlsxWriter

正如其名称所示，XlsxWriter 只能编写 Excel 文件。以下代码生成了与我们先前用 OpenPyXL 生成的相同工作簿，如图 8-1 所示。请注意，XlsxWriter 使用从零开始的单元格索引，而 OpenPyXL 使用从一开始的单元格索引——如果您在这两种包之间切换，请务必注意这一点：

In``[``14``]:``import``datetime``as``dt``import``xlsxwriter``import``excel

In``[``15``]:``# 实例化一个工作簿``book``=``xlsxwriter``.``Workbook``(``"xlxswriter.xlsx"``)``# 添加一个工作表并命名``sheet``=``book``.``add_worksheet``(``"Sheet1"``)``# 使用 A1 表示法和单元格索引（从 0 开始）写入单个单元格``sheet``.``write``(``"A1"``,``"Hello 1"``)``sheet``.``write``(``1``,``0``,``"Hello 2"``)``# 格式化：填充颜色、对齐、边框和字体``formatting``=``book``.``add_format``({``"font_color"``:``"#FF0000"``,``"bg_color"``:``"#FFFF00"``,``"bold"``:``True``,``"align"``:``"center"``,``"border"``:``1``,``"border_color"``:``"#FF0000"``})``sheet``.``write``(``"A3"``,``"Hello 3"``,``formatting``)``# 数字格式化（使用 Excel 的格式化字符串）``number_format``=``book``.``add_format``({``"num_format"``:``"0.00"``})``sheet``.``write``(``"A4"``,``3.3333``,``number_format``)``# 日期格式化（使用 Excel 的格式化字符串）``date_format``=``book``.``add_format``({``"num_format"``:``"mm/dd/yy"``})``sheet``.``write``(``"A5"``,``dt``.``date``(``2016``,``10``,``13``),``date_format``)``# 公式：您必须使用公式的英文名称，并以逗号作为分隔符``sheet``.``write``(``"A6"``,``"=SUM(A4, 2)"``)``# 图像``sheet``.``insert_image``(``0``,``2``,``"images/python.png"``)``# 二维列表（我们使用我们的 excel 模块）``data``=``[[``None``,``"North"``,``"South"``],``[``"Last Year"``,``2``,``5``],``[``"This Year"``,``3``,``6``]]``excel``.``write``(``sheet``,``data``,``"A10"``)``# 图表：请参阅伴随存储库中的 "sales_report_xlsxwriter.py" 文件，了解如何使用索引而不是单元格地址``chart``=``book``.``add_chart``({``"type"``:``"column"``})``chart``.``set_title``({``"name"``:``"Sales per Region"``})``chart``.``add_series``({``"name"``:``"=Sheet1!A11"``,``"categories"``:``"=Sheet1!B10:C10"``,``"values"``:``"=Sheet1!B11:C11"``})``chart``.``add_series``({``"name"``:``"=Sheet1!A12"``,``"categories"``:``"=Sheet1!B10:C10"``,``"values"``:``"=Sheet1!B12:C12"``})``chart``.``set_x_axis``({``"name"``:``"Regions"``})``chart``.``set_y_axis``({``"name"``:``"Sales"``})``sheet``.``insert_chart``(``"A15"``,``chart``)``# 关闭工作簿会在磁盘上创建文件``book``.``close``()

与 OpenPyXL 相比，XlsxWriter 在编写 xlsm 文件时需要采取更复杂的方法，因为它是一个纯写入包。首先，您需要在 Anaconda Prompt 中从现有的 Excel 文件中提取宏代码（示例使用的是 macro.xlsm 文件，在伴随存储库的 xl 文件夹中）：

Windows

首先进入 xl 目录，然后找到 vba_extract.py 的路径，这是一个附带 XlsxWriter 的脚本：

(base)> cd C:\Users\``username``\python-for-excel\xl (base)> where vba_extract.py C:\Users\``username``\Anaconda3\Scripts\vba_extract.py

然后在以下命令中使用此路径：

(base)> python C:\...\Anaconda3\Scripts\vba_extract.py macro.xlsm

macOS

在 macOS 上，该命令可作为可执行脚本使用，并可以像这样运行：

(base)> cd /Users/``username``/python-for-excel/xl (base)> vba_extract.py macro.xlsm

这将在运行命令的目录中保存文件 vbaProject.bin。我还在伴随的存储库的 xl 文件夹中包含了提取的文件。我们将在下面的示例中使用它来编写一个带有宏按钮的工作簿：

In``[``16``]:``book``=``xlsxwriter``.``Workbook``(``"macro_xlxswriter.xlsm"``)``sheet``=``book``.``add_worksheet``(``"Sheet1"``)``sheet``.``write``(``"A1"``,``"点击按钮！"``)``book``.``add_vba_project``(``"xl/vbaProject.bin"``)``sheet``.``insert_button``(``"A3"``,``{``"macro"``:``"Hello"``,``"caption"``:``"Button 1"``,``"width"``:``130``,``"height"``:``35``})``book``.``close``()

pyxlsb

与其他读取库相比，pyxlsb 提供的功能较少，但它是读取二进制 xlsb 格式 Excel 文件的唯一选择。pyxlsb 不是 Anaconda 的一部分，因此如果尚未安装，您需要安装它。目前，它也不支持通过 Conda 安装，因此请使用 pip 安装：

(base)> pip install pyxlsb

您可以按以下方式读取工作表和单元格值：

In``[``17``]:``import``pyxlsb``import``excel

In``[``18``]:``# 循环遍历工作表。使用 pyxlsb，工作簿和工作表对象可以用作上下文管理器。book.sheets 返回的是工作表名称列表，而不是对象！要获取工作表对象，请使用 get_sheet()。``with``pyxlsb``.``open_workbook``(``"xl/stores.xlsb"``)``as``book``:``for``sheet_name``in``book``.``sheets``:``with``book``.``get_sheet``(``sheet_name``)``as``sheet``:``dim``=``sheet``.``dimension````print``(``f``"工作表 '{sheet_name}' 有 "``f``"{dim.h} 行 {dim.w} 列"``)

工作表 '2019' 有 7 行 5 列工作表 '2020' 有 7 行 5 列工作表 '2019-2020' 有 20 行 5 列

In``[``19``]:``# 通过我们的 excel 模块读取一段单元格范围的值。与其使用"2019"，您也可以使用它的索引（从 1 开始）。``with``pyxlsb``.``open_workbook``(``"xl/stores.xlsb"``)``as``book``:``with``book``.``get_sheet``(``"2019"``)``as``sheet``:``data``=``excel``.``read``(``sheet``,``"B2"``)``data``[:``2``]``# 打印前两行

Out[19]: [['Store', 'Employees', 'Manager', 'Since', 'Flagship'], ['纽约', 10.0, '莎拉', 43301.0, False]]

pyxlsb 目前没有识别日期单元格的方法，因此您必须手动将日期格式的单元格值转换为datetime对象，如下所示：

In``[``20``]:``from``pyxlsb``import``convert_date``convert_date``(``data``[``1``][``3``])

Out[20]: datetime.datetime(2018, 7, 20, 0, 0)

请记住，如果您使用的是低于 1.3 版本的 pandas，需要显式指定引擎来读取 xlsb 文件格式：

In``[``21``]:``df``=``pd``.``read_excel``(``"xl/stores.xlsb"``,``engine``=``"pyxlsb"``)

xlrd、xlwt 和 xlutils

xlrd、xlwt 和 xlutils 的组合为旧版 xls 格式提供了与 OpenPyXL 对 xlsx 格式提供的大致相同的功能：xlrd 读取、xlwt 写入、xlutils 编辑 xls 文件。这些包已经不再积极开发，但只要仍然有 xls 文件存在，它们可能还会有用。xlutils 不是 Anaconda 的一部分，所以如果还没有安装它，请安装：

(base)> conda install xlutils

让我们开始阅读部分！

使用 xlrd 进行读取

下面的示例代码向您展示如何使用 xlrd 从 Excel 工作簿中读取数值：

In``[``22``]:``import``xlrd``import``xlwt``from``xlwt.Utils``import``cell_to_rowcol2``import``xlutils``import``excel

In``[``23``]:``# 打开工作簿以读取单元格数值。在加载数据后文件会自动关闭。``book``=``xlrd``.``open_workbook``(``"xl/stores.xls"``)

In``[``24``]:``# 获取所有工作表的名称列表``book``.``sheet_names``()

Out[24]: ['2019', '2020', '2019-2020']

In``[``25``]:``# 遍历工作表对象``for``sheet``in``book``.``sheets``():``print``(``sheet``.``name``)

2019 2020 2019-2020

In``[``26``]:``# 按名称或索引（从 0 开始）获取工作表对象``sheet``=``book``.``sheet_by_index``(``0``)``sheet``=``book``.``sheet_by_name``(``"2019"``)

In``[``27``]:``# 维度``sheet``.``nrows``,``sheet``.``ncols

Out[27]: (8, 6)

In``[``28``]:``# 使用 "A1" 表示法或使用单元格索引（从 0 开始）读取单元格的值。``# "*" 将 cell_to_rowcol2 返回的元组展开为单独的参数。``sheet``.``cell``(``*``cell_to_rowcol2``(``"B3"``))``.``value``sheet``.``cell``(``2``,``1``)``.``value

Out[28]: 'New York'

In``[``29``]:``# 使用我们的 excel 模块读取单元格数值的范围``data``=``excel``.``read``(``sheet``,``"B2"``)``data``[:``2``]``# 打印前两行

Out[29]: [['Store', 'Employees', 'Manager', 'Since', 'Flagship'], ['New York', 10.0, 'Sarah', datetime.datetime(2018, 7, 20, 0, 0), False]]

使用范围

与 OpenPyXL 和 pyxlsb 不同，xlrd 在使用 sheet.nrows 和 sheet.ncols 时返回带有值的单元格的维度，而不是工作表的使用范围。Excel 返回的使用范围通常包含底部和右侧的空白行和列。例如，当您删除行内容（通过按删除键）而不是删除行本身（通过右键单击并选择删除）时，就会发生这种情况。

使用 xlwt 进行写入

下面的代码复现了我们之前用 OpenPyXL 和 XlsxWriter 完成的工作，如图 8-1 所示。但是，xlwt 无法生成图表，只支持 bmp 格式的图片：

In``[``30``]:``import``xlwt``from``xlwt.Utils``import``cell_to_rowcol2``import``datetime``as``dt``import``excel

In``[``31``]:``# 实例化工作簿``book``=``xlwt``.``Workbook``()``# 添加一个工作表并给它命名``sheet``=``book``.``add_sheet``(``"Sheet1"``)``# 使用 A1 表示法和单元格索引（从 0 开始）写入单个单元格``sheet``.``write``(``*``cell_to_rowcol2``(``"A1"``),``"Hello 1"``)``sheet``.``write``(``r``=``1``,``c``=``0``,``label``=``"Hello 2"``)``# 格式化：填充颜色、对齐、边框和字体``formatting``=``xlwt``.``easyxf``(``"font: bold on, color red;"``"align: horiz center;"``"borders: top_color red, bottom_color red,"``"right_color red, left_color red,"``"left thin, right thin,"``"top thin, bottom thin;"``"pattern: pattern solid, fore_color yellow;"``)``sheet``.``write``(``r``=``2``,``c``=``0``,``label``=``"Hello 3"``,``style``=``formatting``)``# 数字格式化（使用 Excel 的格式字符串）``number_format``=``xlwt``.``easyxf``(``num_format_str``=``"0.00"``)``sheet``.``write``(``3``,``0``,``3.3333``,``number_format``)``# 日期格式化（使用 Excel 的格式字符串）``date_format``=``xlwt``.``easyxf``(``num_format_str``=``"mm/dd/yyyy"``)``sheet``.``write``(``4``,``0``,``dt``.``datetime``(``2012``,``2``,``3``),``date_format``)``# 公式：您必须使用逗号作为分隔符的公式的英文名称``sheet``.``write``(``5``,``0``,``xlwt``.``Formula``(``"SUM(A4, 2)"``))``# 二维列表（我们使用我们的 excel 模块）``data``=``[[``None``,``"North"``,``"South"``],``[``"Last Year"``,``2``,``5``],``[``"This Year"``,``3``,``6``]]``excel``.``write``(``sheet``,``data``,``"A10"``)``# 图片（仅允许添加 bmp 格式）``sheet``.``insert_bitmap``(``"images/python.bmp"``,``0``,``2"``)``# 将文件写入磁盘``book``.``save``(``"xlwt.xls"``)

使用 xlutils 进行编辑

xlutils 充当了 xlrd 和 xlwt 之间的桥梁。这明确说明这不是一个真正的编辑操作：通过 xlrd（通过设置 formatting_info=True）读取包括格式的电子表格，然后由 xlwt 再次写出，包括在其中进行的更改：

In``[``32``]:``import``xlutils.copy

In``[``33``]:``book``=``xlrd``.``open_workbook``(``"xl/stores.xls"``,``formatting_info``=``True``)``book``=``xlutils``.``copy``.``copy``(``book``)``book``.``get_sheet``(``0``)``.``write``(``0``,``0``,``"changed!"``)``book``.``save``(``"stores_edited.xls"``)

现在，您知道如何以特定格式读取和编写 Excel 工作簿了。下一节将介绍一些高级主题，包括处理大型 Excel 文件以及将 pandas 和读取器和写入器包一起使用。

高级读取器和写入器主题

如果你的文件比我们迄今为止在示例中使用的简单 Excel 文件更大更复杂，仅依赖默认选项可能不再足够。因此，我们从讨论如何处理更大的文件开始这一部分。然后，我们将学习如何将 pandas 与 reader 和 writer 包一起使用：这将使你能够以想要的方式设计 pandas 数据帧的样式。最后，我们将运用本章学到的所有知识，使上一章案例研究中的 Excel 报告看起来更加专业。

处理大型 Excel 文件

处理大文件可能会导致两个问题：读写过程可能会很慢，或者您的计算机可能会因内存不足而崩溃。通常，内存问题更加令人担忧，因为它会导致程序崩溃。文件何时被认为是大文件始终取决于系统上的可用资源以及您对慢的定义。本节展示了各个包提供的优化技术，使你能够处理极限的 Excel 文件。我将首先讨论写入库的选项，然后是读取库的选项。在本节末尾，我将向你展示如何并行读取工作簿的工作表以减少处理时间。

使用 OpenPyXL 进行写入

当使用 OpenPyXL 写入大文件时，请确保已安装 lxml 包，因为这会加快写入过程。它已包含在 Anaconda 中，因此你无需做任何操作。然而，关键的选项是write_only=True标志，它确保内存消耗保持较低水平。但是，它强制你通过使用append方法逐行写入，并且不再允许你单元格单独写入：

In``[``34``]:``book``=``openpyxl``.``Workbook``(``write_only``=``True``)``# 使用 write_only=True，book.active 不起作用``sheet``=``book``.``create_sheet``()``# 这将生成一个包含 1000 x 200 个单元格的工作表``for``row``in``range``(``1000``):``sheet``.``append``(``list``(``range``(``200``)))``book``.``save``(``"openpyxl_optimized.xlsx"``)

使用 XlsxWriter 写入

XlsxWriter 有一个类似于 OpenPyXL 的选项叫做constant_memory。它也强制你按顺序写入行。你可以通过提供一个像这样的options字典来启用该选项：

In``[``35``]:``book``=``xlsxwriter``.``Workbook``(``"xlsxwriter_optimized.xlsx"``,``options``=``{``"constant_memory"``:``True``})``sheet``=``book``.``add_worksheet``()``# 这将生成一个包含 1000 x 200 个单元格的工作表``for``row``in``range``(``1000``):``sheet``.``write_row``(``row``,``0``,``list``(``range``(``200``)))``book``.``close``()

使用 xlrd 读取

当读取旧版 xls 格式的大文件时，xlrd 允许你按需加载工作表，就像这样：

In``[``36``]:``with``xlrd``.``open_workbook``(``"xl/stores.xls"``,``on_demand``=``True``)``as``book``:``sheet``=``book``.``sheet_by_index``(``0``)``# 只加载第一个工作表

如果你不像我们这样使用工作簿作为上下文管理器，你将需要手动调用 book.release_resources() 来正确地再次关闭工作簿。要以这种模式使用 xlrd 和 pandas，像这样使用：

In``[``37``]:``with``xlrd``.``open_workbook``(``"xl/stores.xls"``,``on_demand``=``True``)``as``book``:``with``pd``.``ExcelFile``(``book``,``engine``=``"xlrd"``)``as``f``:``df``=``pd``.``read_excel``(``f``,``sheet_name``=``0``)

使用 OpenPyXL 读取

使用 OpenPyXL 读取大型 Excel 文件时，为了控制内存使用，你应该以 read_only=True 加载工作簿。由于 OpenPyXL 不支持 with 语句，所以在完成后你需要确保关闭文件。如果你的文件包含对外部工作簿的链接，你可能还想使用 keep_links=False 来加快速度。keep_links 确保保留对外部工作簿的引用，这可能会在你只想读取工作簿的值时不必要地减慢过程：

In``[``38``]:``book``=``openpyxl``.``load_workbook``(``"xl/big.xlsx"``,``data_only``=``True``,``read_only``=``True``,``keep_links``=``False``)``# 在此执行所需的读取操作``book``.``close``()``# 使用 read_only=True 时需要关闭

并行读取工作表

当你使用 pandas 的 read_excel 函数读取大型工作簿的多个工作表时，你会发现这需要很长时间（我们稍后会举一个具体的例子）。原因在于 pandas 会顺序地读取工作表，即一个接一个地。为了加快速度，你可以并行读取工作表。虽然由于文件内部结构的原因，写入工作簿的并行化没有简单的方法，但读取多个工作表并行却很简单。然而，由于并行化是一个高级话题，我没有在 Python 介绍中涉及，并且在这里也不会详细讨论。

在 Python 中，如果你想利用每台现代计算机都具备的多个 CPU 核心，你可以使用标准库中的multiprocessing包。这将会生成多个 Python 解释器（通常每个 CPU 核心一个），并行处理任务。与逐个处理表格不同，你可以让一个 Python 解释器处理第一个表格，同时第二个 Python 解释器处理第二个表格，以此类推。然而，每个额外的 Python 解释器启动需要一些时间，并且使用额外的内存，所以如果你有小文件，当你并行化读取过程时，它们可能比串行化处理更慢。但对于包含多个大表格的大文件，multiprocessing可以显著加快处理速度——前提是你的系统具备足够的内存来处理工作负载。如果像第二章展示的那样在 Binder 上运行 Jupyter 笔记本，你可能没有足够的内存，因此并行化版本的运行速度会较慢。在伴随的存储库中，你会找到parallel_pandas.py，它是使用 OpenPyXL 作为引擎的简单实现，用于并行读取表格。使用起来非常简单，因此你不需要了解multiprocessing的任何内容。

import``parallel_pandas``parallel_pandas``.``read_excel``(``filename``,``sheet_name``=``None``)

默认情况下，它会读取所有表格，但你可以提供一个要处理的表格名称列表。像 pandas 一样，该函数返回一个字典，形式如{"表格名称": df}，即键是表格名称，值是 DataFrame。

%%TIME 魔术命令

在接下来的示例中，我将使用%%time单元格魔术命令。我在第五章中与 Matplotlib 一起介绍了魔术命令。%%time是一个非常有用的单元格魔术命令，可以用于简单的性能调优，因为它可以轻松比较包含不同代码片段的两个单元格的执行时间。墙时间是从程序（即单元格）开始到结束的经过时间。如果你在 macOS 或 Linux 上，你不仅会得到墙时间，还会得到类似以下这样的 CPU 时间额外行：

CPU times: user 49.4 s, sys: 108 ms, total: 49.5 s

CPU 时间测量 CPU 上花费的时间，这可能低于墙时间（如果程序必须等待 CPU 可用）或高于墙时间（如果程序在多个 CPU 核心上并行运行）。为了更准确地测量时间，请使用%%timeit代替%%time，它会多次运行单元格并取所有运行的平均值。%%time和%%timeit是单元格魔术命令，即它们需要在单元格的第一行，并测量整个单元格的执行时间。如果你想测量单独一行代码的时间，可以使用%time或%timeit。

让我们看看并行化版本在读取 companion repo 的 xl 文件夹中的 big.xlsx 文件时快多少：

In``[``39``]:``%%``time``data``=``pd``.``read_excel``(``"xl/big.xlsx"``,``sheet_name``=``None``,``engine``=``"openpyxl"``)

Wall time: 49.5 s

In``[``40``]:``%%``time``import``parallel_pandas``data``=``parallel_pandas``.``read_excel``(``"xl/big.xlsx"``,``sheet_name``=``None``)

Wall time: 12.1 s

要获取代表 Sheet1 的 DataFrame，你可以在两种情况下写 data["Sheet1"]。通过比较两个样本的墙上时间，你会发现，并行化版本在我的笔记本上，对于这个特定的工作簿和 6 个 CPU 内核，比 pd.read_excel 快了数倍。如果你想要更快的速度，直接并行化 OpenPyXL：在 companion repository（parallel_openpyxl.py）中也有一个实现，还有一个用于以并行方式读取传统 xls 格式的 xlrd 实现（parallel_xlrd.py）。通过直接使用底层包而不是 pandas，你可以跳过转换成 DataFrame 或者只应用你需要的清理步骤，这很可能会帮助你加快速度，如果这是你最关心的问题的话。

使用 MODIN 并行读取表格

如果你只读取一个巨大的表格，那么看看 Modin 值得一试，这是一个可以替代 pandas 的项目。它并行化了单表的读取过程，并提供了令人印象深刻的速度改进。由于 Modin 需要特定版本的 pandas，在安装时可能会降级 Anaconda 自带的版本。如果你想测试它，我建议你为此创建一个单独的 Conda 环境，以确保不会影响到基础环境。有关如何创建 Conda 环境的更详细说明，请参阅附录 A：

(base)> conda create --name modin python=3.8 -y (base)> conda activate modin (modin)> conda install -c conda-forge modin -y

在我的机器上，使用 big.xlsx 文件运行以下代码大约需要五秒钟，而 pandas 则需要大约十二秒钟：

import``modin.pandas``data``=``modin``.``pandas``.``read_excel``(``"xl/big.xlsx"``,``sheet_name``=``0``,``engine``=``"openpyxl"``)

现在你知道如何处理大文件了，让我们继续看看如何将 pandas 和低级包一起使用，以改进写入 DataFrame 到 Excel 文件时的默认格式！

格式化 Excel 中的 DataFrame

要按我们想要的方式格式化 Excel 中的 DataFrame，我们可以编写代码，使用 pandas 与 OpenPyXL 或 XlsxWriter 结合使用。我们首先使用这种组合为导出的 DataFrame 添加一个标题。然后在包装这一部分之前，格式化 DataFrame 的标题和索引。将 pandas 与 OpenPyXL 结合用于读取有时也可能很有用，所以让我们从这里开始：

In``[``41``]:``with``pd``.``ExcelFile``(``"xl/stores.xlsx"``,``engine``=``"openpyxl"``)``as``xlfile``:``# 读取 DataFrame``df``=``pd``.``read_excel``(``xlfile``,``sheet_name``=``"2020"``)``# 获取 OpenPyXL 的工作簿对象``book``=``xlfile``.``book``# 从这里开始是 OpenPyXL 代码``sheet``=``book``[``"2019"``]``value``=``sheet``[``"B3"``]``.``value``# 读取单个值

在写入工作簿时，它的功能类似，允许我们轻松地向我们的 DataFrame 报告添加一个标题：

In``[``42``]:``with``pd``.``ExcelWriter``(``"pandas_and_openpyxl.xlsx"``,``engine``=``"openpyxl"``)``as``writer``:``df``=``pd``.``DataFrame``({``"col1"``:``[``1``,``2``,``3``,``4``],``"col2"``:``[``5``,``6``,``7``,``8``]})``# 写入 DataFrame``df``.``to_excel``(``writer``,``"Sheet1"``,``startrow``=``4``,``startcol``=``2``)``# 获取 OpenPyXL 的工作簿和表对象``book``=``writer``.``book``sheet``=``writer``.``sheets``[``"Sheet1"``]``# 从这里开始是 OpenPyXL 代码``sheet``[``"A1"``]``.``value``=``"这是一个标题"``# 写入单元格值

这些示例使用了 OpenPyXL，但概念上与其他包相同。接下来我们继续了解如何格式化 DataFrame 的索引和标头。

格式化 DataFrame 的索引和标头

要完全控制索引和列标题的格式化，最简单的方法是直接编写它们。以下示例分别演示了如何使用 OpenPyXL 和 XlsxWriter 做到这一点。您可以在图 8-2 中查看输出。让我们从创建一个 DataFrame 开始：

In``[``43``]:``df``=``pd``.``DataFrame``({``"col1"``:``[``1``,``-``2``],``"col2"``:``[``-``3``,``4``]},``index``=``[``"row1"``,``"row2"``])``df``.``index``.``name``=``"ix"``df

Out[43]: col1 col2 ix row1 1 -3 row2 -2 4

要使用 OpenPyXL 格式化索引和标头，请按以下步骤操作：

In``[``44``]:``from``openpyxl.styles``import``PatternFill

使用 Openpyxl 格式化索引/标题的示例 In[45]：

如果要使用 XlsxWriter 格式化索引和标题，请稍微调整代码：

使用 XlsxWriter 格式化索引/标题的示例 In[46]：

现在索引和标题都已格式化好了，让我们看看如何对数据部分进行样式设置！

图 8-2. 默认格式的 DataFrame（左）和自定义格式（右）

格式化 DataFrame 数据部分

格式化 DataFrame 数据部分的可能性取决于您使用的包：如果使用 pandas 的to_excel方法，OpenPyXL 可以对每个单元格应用格式，而 XlsxWriter 只能按行或列的方式应用格式。例如，要设置单元格的数字格式为三位小数并居中显示内容，如图 8-3 中所示，使用 OpenPyXL 可以按以下步骤进行：

In``[``47``]:``from``openpyxl.styles``import``Alignment

In``[``48``]:``with``pd``.``ExcelWriter``(``"data_format_openpyxl.xlsx"``,``engine``=``"openpyxl"``)``as``writer``:``# 写出 DataFrame``df``.``to_excel``(``writer``)``# 获取工作簿和工作表对象``book``=``writer``.``book``sheet``=``writer``.``sheets``[``"Sheet1"``]``# 格式化单个单元格``nrows``,``ncols``=``df``.``shape``for``row``in``range``(``nrows``):``for``col``in``range``(``ncols``):``# +1 考虑到标题/索引``# +1 因为 OpenPyXL 基于 1``base````cell``=``sheet``.``cell``(``row``=``row``+``2``,``column``=``col``+``2``)``cell``.``number_format``=``"0.000"``cell``.``alignment``=``Alignment``(``horizontal``=``"center"``)

对于 XlsxWriter，调整代码如下：

In``[``49``]:``with``pd``.``ExcelWriter``(``"data_format_xlsxwriter.xlsx"``,``engine``=``"xlsxwriter"``)``as``writer``:``# 写出 DataFrame``df``.``to_excel``(``writer``)``# 获取工作簿和工作表对象``book``=``writer``.``book``sheet``=``writer``.``sheets``[``"Sheet1"``]``# 格式化列（无法格式化单个单元格）``number_format``=``book``.``add_format``({``"num_format"``:``"0.000"``,``"align"``:``"center"``})``sheet``.``set_column``(``first_col``=``1``,``last_col``=``2``,``cell_format``=``number_format``)

图 8-3. 数据部分格式化的 DataFrame

作为替代方案，pandas 为 DataFrame 的style属性提供了实验性支持。实验性意味着语法随时可能更改。由于样式用于在 HTML 格式中格式化 DataFrame，因此它们使用 CSS 语法。CSS 代表层叠样式表，用于定义 HTML 元素的样式。要应用与前面示例相同的格式（三位小数和居中对齐），您需要通过applymap函数将函数应用于Styler对象的每个元素。您可以通过df.style属性获得Styler对象：

In``[``50``]:``df``.``style``.``applymap``(``lambda``x``:``"number-format: 0.000;"``"text-align: center"``)``\ .``to_excel``(``"styled.xlsx"``)

这段代码的输出结果与图 8-3 中显示的一样。有关 DataFrame 样式方法的更多详细信息，请直接参阅样式文档。

无需依赖样式属性，pandas 提供了对日期和日期时间对象进行格式化的支持，如图 8-4 所示：

In``[``51``]:``df``=``pd``.``DataFrame``({``"Date"``:``[``dt``.``date``(``2020``,``1``,``1``)],``"Datetime"``:``[``dt``.``datetime``(``2020``,``1``,``1``,``10``)]})``with``pd``.``ExcelWriter``(``"date.xlsx"``,``date_format``=``"yyyy-mm-dd"``,``datetime_format``=``"yyyy-mm-dd hh:mm:ss"``)``as``writer``:``df``.``to_excel``(``writer``)

图 8-4。带有格式化日期的数据框

其他读取器和写入器包

除了本章中我们已经查看过的软件包之外，还有一些其他的软件包可能对特定的用例有所帮助：

pyexcel

pyexcel提供了一种在不同的 Excel 包和其他文件格式（包括 CSV 文件和 OpenOffice 文件）之间统一的语法。

PyExcelerate

PyExcelerate的目标是以最快的速度编写 Excel 文件。

pylightxl

pylightxl可以读取 xlsx 和 xlsm 文件，并写入 xlsx 文件。

styleframe

styleframe封装了 pandas 和 OpenPyXL，以产生带有精美格式的数据框的 Excel 文件。

oletools

oletools并不是一个传统的读取器或写入器包，但可以用于分析 Microsoft Office 文档，例如，用于恶意软件分析。它提供了一种方便的方法来从 Excel 工作簿中提取 VBA 代码。

现在你知道如何在 Excel 中格式化数据框了，是时候重新审视上一章的案例研究，看看我们是否能够运用本章的知识来改进 Excel 报告了！

案例研究（再访）：Excel 报告

翻译到了本章的最后，您已经了解足够的知识，可以回到上一章的 Excel 报告，并使其在视觉上更具吸引力。如果您愿意，可以回到附带存储库中的 sales_report_pandas.py，并尝试将其转换为 Figue 8-5 中显示的报告。

红色数字是低于 20,000 的销售数字。本章未涉及所有格式设置的每个方面（比如如何应用条件格式设置），因此您需要使用您选择的软件包的文档。为了比较您的解决方案，我在附带的存储库中包含了生成此报告的两个脚本版本。第一个版本基于 OpenPyXL（sales_report_openpyxl.py），另一个版本基于 XlsxWriter（sales_report_xlsxwriter.py）。并排查看脚本可能还能让您更明智地决定下一次要选择哪个软件包来完成您的写入任务。我们将在下一章再次回到这个案例研究：在那里，我们将依赖于 Microsoft Excel 的安装来处理报告模板。

图 8-5。由 sales_report_openpyxl.py 创建的重新审视的销售报告

结论

在本章中，我向您介绍了 pandas 在底层使用的读取器和写入器包。直接使用它们允许我们读取和写入 Excel 工作簿，而无需安装 pandas。然而，与 pandas 结合使用使我们能够通过添加标题、图表和格式来增强 Excel DataFrame 报告。虽然当前的读取器和写入器包非常强大，但我仍然希望有一天能看到一个像 "NumPy 时刻" 那样将所有开发者的努力统一到一个项目中。能够在不必首先查看表格的情况下知道使用哪个包，并且在不必为每种类型的 Excel 文件使用不同的语法的情况下使用它，这将是很棒的。从这个意义上说，从 pandas 开始，只有在需要 pandas 未涵盖的附加功能时才退而使用读取器和写入器包，这是有道理的。

然而，Excel 不仅仅是一个数据文件或报告：Excel 应用程序是最直观的用户界面之一，用户可以输入几个数字，并让它显示他们正在寻找的信息。自动化 Excel 应用程序而不是读写 Excel 文件，开启了我们将在第四部分探索的全新功能范围。下一章将通过向您展示如何从 Python 远程控制 Excel 来开始这段旅程。

第四部分：使用 xlwings 编程 Excel 应用程序

第九章：Excel 自动化

到目前为止，我们已经学会了如何用 pandas 替换典型的 Excel 任务（第 II 部分），以及如何将 Excel 文件作为数据源和报告文件格式（第 III 部分）。本章开启了第 IV 部分，在这一部分中，我们不再使用读者和写者包来操作 Excel 文件，而是开始使用 xlwings 自动化 Excel 应用程序。

xlwings 的主要用途是构建交互式应用程序，其中 Excel 电子表格充当用户界面，允许您通过单击按钮或调用用户定义函数来调用 Python —— 这种功能不被读取器和写入器包覆盖。但这并不意味着 xlwings 不能用于读写文件，只要您在 macOS 或 Windows 上安装了 Excel。xlwings 在这方面的一个优势是能够真正编辑 Excel 文件，而不改变或丢失任何现有内容或格式。另一个优势是，您可以从 Excel 工作簿中读取单元格值，而无需先保存它。然而，将 Excel 读取器/写入器包和 xlwings 结合使用也是完全合理的，正如我们将在第七章的报告案例研究中看到的那样。

本章将首先介绍 Excel 对象模型以及 xlwings：我们将首先学习如何连接工作簿、读写单元格数值等基础知识，然后深入了解转换器和选项是如何允许我们处理 pandas 数据帧和 NumPy 数组的。我们还将看看如何与图表、图片和定义名称进行交互，然后转向最后一节，解释 xlwings 在幕后的工作原理：这将为您提供所需的知识，使您的脚本性能更高，并解决缺少功能的问题。从本章开始，您需要在 Windows 或 macOS 上运行代码示例，因为它们依赖于本地安装的 Microsoft Excel。1

开始使用 xlwings

xlwings 的一个目标是作为 VBA 的替代品，允许您在 Windows 和 macOS 上从 Python 与 Excel 进行交互。由于 Excel 的网格是显示 Python 数据结构（如嵌套列表、NumPy 数组和 pandas DataFrame）的理想布局，xlwings 的核心特性之一是尽可能地简化从 Excel 读取和写入这些数据结构。我将从介绍 Excel 作为数据查看器开始这一节——当您在 Jupyter 笔记本中与 DataFrame 交互时，这非常有用。然后我将解释 Excel 对象模型，然后使用 xlwings 进行交互式探索。最后，我将向您展示如何调用可能仍在遗留工作簿中的 VBA 代码。由于 xlwings 是 Anaconda 的一部分，我们不需要手动安装它。

使用 Excel 作为数据查看器

在前几章中，您可能已经注意到，默认情况下，Jupyter 笔记本会隐藏更大的 DataFrame 的大部分数据，仅显示顶部和底部的行以及前几列和最后几列。了解数据的更好方法之一是绘制它——这使您能够发现异常值或其他不规则情况。然而，有时，能够滚动查看数据表确实非常有帮助。在阅读第七章之后，您已经了解如何在 DataFrame 上使用to_excel方法。虽然这样做可以实现，但可能有些繁琐：您需要为 Excel 文件命名，找到它在文件系统中的位置，打开它，在对 DataFrame 进行更改后，您需要关闭 Excel 文件，并重新运行整个过程。更好的方法可能是运行df.to_clipboard()，它将 DataFrame df复制到剪贴板，使您可以将其粘贴到 Excel 中，但更简单的方法是使用 xlwings 提供的view函数：

In``[``1``]:``# 首先，让我们导入本章将要使用的包``import``datetime``as``dt``import``xlwings``as``xw``import``pandas``as``pd``import``numpy``as``np

`In[2]:# 让我们创建一个基于伪随机数的 DataFrame，并且有足够的行数，以至于只显示头部和尾部df=pd.DataFrame(data=np.random.randn(100,5),columns=[f"试验 {i}"foriinrange(1,6)])df

Out[2]: Trial 1 Trial 2 Trial 3 Trial 4 Trial 5 0 -1.313877 1.164258 -1.306419 -0.529533 -0.524978 1 -0.854415 0.022859 -0.246443 -0.229146 -0.005493 2 -0.327510 -0.492201 -1.353566 -1.229236 0.024385 3 -0.728083 -0.080525 0.628288 -0.382586 -0.590157 4 -1.227684 0.498541 -0.266466 0.297261 -1.297985 .. ... ... ... ... ... 95 -0.903446 1.103650 0.033915 0.336871 0.345999 96 -1.354898 -1.290954 -0.738396 -1.102659 0.115076 97 -0.070092 -0.416991 -0.203445 -0.686915 -1.163205 98 -1.201963 0.471854 -0.458501 -0.357171 1.954585 99 1.863610 0.214047 -1.426806 0.751906 -2.338352 [100 rows x 5 columns]

In``[``3``]:``# 在 Excel 中查看 DataFrame``xw``.``view``(``df``)

view 函数接受所有常见的 Python 对象，包括数字、字符串、列表、字典、元组、NumPy 数组和 pandas 数据框。默认情况下，它会打开一个新工作簿，并将对象粘贴到第一个工作表的 A1 单元格中——甚至可以使用 Excel 的自动调整功能调整列宽。您还可以通过将 xlwings 的 sheet 对象作为第二个参数提供给 view 函数，以重复使用同一个工作簿：xw.view(df, mysheet)。如何获取这样的 sheet 对象以及它如何适配到 Excel 对象模型中，这就是我接下来将要解释的内容。2

MACOS：权限和偏好

在 macOS 上，请确保从 Anaconda Prompt（即通过终端）运行 Jupyter 笔记本和 VS Code，如第二章所示。这样可以确保第一次使用 xlwings 时会弹出两个弹窗：第一个是“终端想要控制系统事件”，第二个是“终端想要控制 Microsoft Excel”。您需要确认这两个弹窗以允许 Python 自动化 Excel。理论上，任何从中运行 xlwings 代码的应用程序都应该触发这些弹窗，但实际上，通常并非如此，因此通过终端运行它们可以避免麻烦。此外，您需要打开 Excel 的偏好设置，并取消“打开 Excel 时显示工作簿库”选项，该选项在“常规”类别下。这样可以直接在空工作簿中打开 Excel，而不是首先打开库，这样当您通过 xlwings 打开新的 Excel 实例时就不会受到干扰。

Excel 对象模型

当你以编程方式使用 Excel 时，你会与其组件进行交互，比如工作簿或工作表。这些组件在 Excel 对象模型中组织，这是一个层次结构，代表了 Excel 的图形用户界面（见图 9-1）。Microsoft 在所有官方支持的编程语言中基本上使用相同的对象模型，无论是 VBA、Office 脚本（Excel 在 Web 上的 JavaScript 接口）还是 C#。与第八章中的读写包相比，xlwings 非常紧密地遵循了 Excel 对象模型，只是稍微有所创新：例如，xlwings 使用app代替application，book代替workbook：

一个app包含books集合
一个book包含sheets集合
一个sheet提供对range对象和集合（如charts）的访问
一个range包含一个或多个连续的单元格作为其项目

虚线框是集合，包含同一类型的一个或多个对象。一个app对应于一个 Excel 实例，即运行为单独进程的 Excel 应用程序。高级用户有时会并行使用多个 Excel 实例打开同一工作簿，例如，为了并行计算带有不同输入的工作簿。在更近期的 Excel 版本中，Microsoft 稍微增加了手动打开多个 Excel 实例的复杂性：启动 Excel，然后在 Windows 任务栏中右键单击其图标。在出现的菜单中，同时按住 Alt 键单击 Excel 条目（确保在释放鼠标按钮之后继续按住 Alt 键）——一个弹出窗口会询问是否要启动新的 Excel 实例。在 macOS 上，没有手动启动多个相同程序实例的方式，但是可以通过 xlwings 在编程方式下启动多个 Excel 实例，稍后我们将看到。总之，Excel 实例是一个隔离环境，这意味着一个实例无法与另一个实例通信。3 sheet对象让您访问诸如图表、图片和定义名称等集合——这些是我们将在本章第二部分中探讨的主题。

图 9-1. 由 xlwings 实现的 Excel 对象模型（节选）

语言和区域设置

本书基于 Excel 的美国英语版本。我偶尔会提到默认名称如“Book1”或“Sheet1”，如果你使用其他语言的 Excel，则名称会不同。例如，法语中的“Sheet1”称为“Feuille1”，西班牙语中称为“Hoja1”。此外，列表分隔符，即 Excel 在单元格公式中使用的分隔符，取决于您的设置：我将使用逗号，但您的版本可能需要分号或其他字符。例如，不是写=SUM(A1, A2)，而是在具有德国区域设置的计算机上写=SUMME(A1; A2)。

在 Windows 上，如果您想将列表分隔符从分号更改为逗号，需要在 Excel 之外的 Windows 设置中更改它：点击 Windows 开始按钮，搜索“设置”（或点击齿轮图标），然后转到“时间与语言” > “地区与语言” > “附加日期、时间和区域设置”，最后点击“区域” > “更改位置”。在“列表分隔符”下，您将能够将其从分号更改为逗号。请注意，仅当您的“小数符号”（在同一菜单中）不是逗号时，此设置才有效。要覆盖系统范围内的小数和千位分隔符（但不更改列表分隔符），请在 Excel 中转到“选项” > “高级”，在“编辑选项”下找到相关设置。

在 macOS 上，操作类似，不过你无法直接更改列表分隔符：在 macOS 的系统偏好设置（而非 Excel）中，选择“语言与地区”。在那里，为 Excel（在“应用程序”选项卡下）或全局（在“常规”选项卡下）设置特定的地区。

要熟悉 Excel 对象模型，通常最好是通过互动方式来操作。让我们从 Book 类开始：它允许您创建新工作簿并连接到现有工作簿；参见表格 9-1 以获取概述。

表格 9-1. 使用 Excel 工作簿

命令	描述
`xw.Book()`	返回一个表示活动 Excel 实例中新 Excel 工作簿的`book`对象。如果没有活动实例，Excel 将会启动。
`xw.Book("Book1")`	返回一个表示未保存的名为 Book1 的工作簿的`book`对象（不带文件扩展名）。
`xw.Book("Book1.xlsx")`	返回一个表示已保存的名为 Book1.xlsx 的工作簿的`book`对象（带有文件扩展名）。文件必须是打开的或者在当前工作目录中。
`xw.Book(r"C:\path\Book1.xlsx")`	返回一个表示已保存的完整文件路径的工作簿的`book`对象。文件可以是打开的或关闭的。使用前缀 `r` 将字符串转换为原始字符串，使得 Windows 下的反斜杠（`\`）被直接解释（我在第五章介绍了原始字符串）。在 macOS 上，不需要 `r` 前缀，因为文件路径使用正斜杠而不是反斜杠。
`xw.books.active`	返回活动 Excel 实例中活动工作簿的`book`对象。

让我们看看如何从 book 对象逐步遍历对象模型层次结构到 range 对象：

In``[``4``]:``# 创建一个新的空工作簿并打印其名称。这是我们将在本章中大多数代码示例中使用的book。``book``=``xw``.``Book``()``book``.``name

Out[4]: 'Book2'

In``[``5``]:``# 访问工作表集合``book``.``sheets

Out[5]: Sheets([<Sheet [Book2]Sheet1>])

In``[``6``]:``# 通过索引或名称获取工作表对象。如果您的工作表名称不同，您需要调整Sheet1。``sheet1``=``book``.``sheets``[``0``]``sheet1``=``book``.``sheets``[``"Sheet1"``]

In``[``7``]:``sheet1``.``range``(``"A1"``)

Out[7]: <Range [Book2]Sheet1!$A$1>

通过 range 对象，我们已经到达了层次结构的底部。尖括号中打印的字符串为您提供有关该对象的有用信息，但通常要使用具有属性的对象，如下一个示例所示：

In``[``8``]:``# 最常见的任务：写入值...``sheet1``.``range``(``"A1"``)``.``value``=``[[``1``,``2``],``[``3``,``4``]]``sheet1``.``range``(``"A4"``)``.``value``=``"Hello!"

In``[``9``]:``# ...和读取值``sheet1``.``range``(``"A1:B2"``)``.``value

Out[9]: [[1.0, 2.0], [3.0, 4.0]]

In``[``10``]:``sheet1``.``range``(``"A4"``)``.``value

Out[10]: 'Hello!'

正如您所见，xlwings 的 range 对象的 value 属性默认接受和返回两维范围的嵌套列表和单个单元格的标量。到目前为止，我们几乎与 VBA 完全一致：假设 book 分别是 VBA 或 xlwings 工作簿对象，这是如何从 A1 到 B2 的单元格访问 value 属性的方法：

book``.``Sheets``(``1``)``.``Range``(``"A1:B2"``)``.``Value``# VBA``book``.``sheets``[``0``]``.``range``(``"A1:B2"``)``.``value``# xlwings

差异在于：

属性

Python 使用小写字母，可能带有下划线，如 PEP 8 所建议的 Python 样式指南，我在第 3 章中介绍过。

索引

Python 使用方括号和从零开始的索引来访问 sheets 集合中的元素。

表 9-2 提供了 xlwings range 接受的字符串的概述。

表 9-2. 使用 A1 表示法定义范围的字符串

引用	描述
`"A1"`	单个单元格
`"A1:B2"`	从 A1 到 B2 的单元格
`"A:A"`	A 列
`"A:B"`	A 到 B 列
`"1:1"`	第 1 行
`"1:2"`	1 到 2 行

索引和切片适用于 xlwings 的 range 对象 — 注意尖括号中的地址（打印的对象表示）以查看您最终使用的单元格范围：

In``[``11``]:``# 索引``sheet1``.``range``(``"A1:B2"``)[``0``,``0``]

Out[11]: <Range [Book2]Sheet1!$A$1>

In``[``12``]:``# 切片``sheet1``.``range``(``"A1:B2"``)[:,``1``]

Out[12]: <Range [Book2]Sheet1!$B$1:$B$2>

索引对应于在 VBA 中使用 Cells 属性：

book``.``Sheets``(``1``)``.``Range``(``"A1:B2"``)``.``Cells``(``1``,``1``)``# VBA``book``.``sheets``[``0``]``.``range``(``"A1:B2"``)[``0``,``0``]``# xlwings

相反地，您也可以通过索引和切片sheet对象来获取range对象，而不是显式地使用range作为sheet对象的属性。使用 A1 表示法可以减少输入，使用整数索引可以使 Excel 工作表感觉像 NumPy 数组：

In``[``13``]:``# 单个单元格：A1 表示法``sheet1``[``"A1"``]

Out[13]: <Range [Book2]Sheet1!$A$1>

In``[``14``]:``# 多个单元格：A1 表示法``sheet1``[``"A1:B2"``]

Out[14]: <Range [Book2]Sheet1!$A$1:$B$2>

In``[``15``]:``# 单个单元格：索引``sheet1``[``0``,``0``]

Out[15]: <Range [Book2]Sheet1!$A$1>

In``[``16``]:``# 多个单元格：切片``sheet1``[:``2``,``:``2``]

Out[16]: <Range [Book2]Sheet1!$A$1:$B$2>

有时，通过引用范围的左上角和右下角单元格来定义范围可能更直观。下面的示例分别引用了单元格范围 D10 和 D10:F11，使您可以理解索引/切片sheet对象与处理range对象之间的区别：

In``[``17``]:``# 通过工作表索引访问 D10``sheet1``[``9``,``3``]

Out[17]: <Range [Book2]Sheet1!$D$10>

In``[``18``]:``# 通过 range 对象访问 D10``sheet1``.``range``((``10``,``4``))

Out[18]: <Range [Book2]Sheet1!$D$10>

In``[``19``]:``# 通过 sheet 切片访问 D10:F11``sheet1``[``9``:``11``,``3``:``6``]

Out[19]: <Range [Book2]Sheet1!$D$10:$F$11>

In``[``20``]:``# 通过 range 对象访问 D10:F11``sheet1``.``range``((``10``,``4``),``(``11``,``6``))

Out[20]: <Range [Book2]Sheet1!$D$10:$F$11>

使用元组定义range对象与 VBA 中的Cells属性非常相似，如下面的比较所示——假设book再次是 VBA 工作簿对象或 xlwings 的book对象。让我们首先看看 VBA 版本：

With``book``.``Sheets``(``1``)``myrange``=``.``Range``(.``Cells``(``10``,``4``),``.``Cells``(``11``,``6``))``End``With

这与以下 xlwings 表达式等效：

myrange``=``book``.``sheets``[``0``]``.``range``((``10``,``4``),``(``11``,``6``))

零索引与一索引

作为 Python 包，xlwings 在通过 Python 的索引或切片语法访问元素时始终使用零索引。然而，xlwings 的range对象使用 Excel 的一索引行和列索引。与 Excel 用户界面具有相同的行/列索引有时可能是有益的。如果您希望仅使用 Python 的零索引，请简单地使用sheet[row_selection, column_selection]语法。

下面的示例向您展示如何从range对象(sheet1["A1"])获取到app对象。请记住，app对象代表一个 Excel 实例（尖括号中的输出表示 Excel 的进程 ID，因此在您的机器上可能会有所不同）：

In``[``21``]:``sheet1``[``"A1"``]``.``sheet``.``book``.``app

Out[21]: <Excel App 9092>

已经到达 Excel 对象模型的顶端，现在是时候看看如何处理多个 Excel 实例了。如果您想在多个 Excel 实例中打开相同的工作簿，或者特别是出于性能原因希望在不同实例中分配您的工作簿，那么您需要明确使用app对象。使用app对象的另一个常见用例是在隐藏的 Excel 实例中打开工作簿：这使得您可以在后台运行 xlwings 脚本，同时又不会阻碍您在 Excel 中进行其他工作：

In``[``22``]:``# 从打开的工作簿获取一个应用对象``# 并创建一个额外的不可见应用实例``visible_app``=``sheet1``.``book``.``app``invisible_app``=``xw``.``App``(``visible``=``False``)

In``[``23``]:``# 使用列表推导列出每个实例中打开的书名``[``visible_app``.``books``中的book.name`]

Out[23]: ['Book1', 'Book2']

In``[``24``]:``[``invisible_app``.``books``中的book.name`]

Out[24]: ['Book3']

`In[25]:# 应用密钥表示进程 ID（PID）xw.apps.keys()``

Out[25]: [5996, 9092]

`In[26]:# 也可以通过 pid 属性访问xw.apps.active.pid

Out[26]: 5996

`In[27]:# 在不可见的 Excel 实例中处理工作簿invisible_book=invisible_app.books[0]invisible_book.sheets[0]["A1"].value="由不可见应用程序创建。"``

In``[``28``]:``# 将 Excel 工作簿保存在 xl 目录中``invisible_book``.``save``(``"xl/invisible.xlsx"``)

In``[``29``]:``# 退出不可见的 Excel 实例``invisible_app``.``quit``()

MACOS：以编程方式访问文件系统

如果您在 macOS 上运行save命令，Excel 会弹出授权文件访问的提示窗口，您需要点击“选择”按钮确认，然后再点击“授权访问”。在 macOS 上，Excel 是沙盒化的，这意味着您的程序只能通过确认此提示才能访问 Excel 应用程序外的文件和文件夹。确认后，Excel 将记住位置，在下次运行脚本时再次运行时不会再打扰您。

如果您在两个 Excel 实例中打开相同的工作簿，或者想要指定在哪个 Excel 实例中打开工作簿，就不能再使用xw.Book了。相反，您需要使用在表 9-3 中描述的books集合。请注意，myapp代表一个 xlwings 的app对象。如果您用xw.books替换myapp.books，xlwings 将使用活动的app。

表 9-3. 使用books集合操作

命令	描述
`myapp.books.add()`	在`myapp`所引用的 Excel 实例中创建一个新的 Excel 工作簿，并返回相应的`book`对象。
`myapp.books.open(r"C:\path\Book.xlsx")`	如果该书已打开，则返回`book`，否则首先在`myapp`引用的 Excel 实例中打开。请记住，前导的`r`将文件路径转换为原始字符串，以字面上的方式解释反斜杠。
`myapp.books["Book1.xlsx"]`	如果该书已打开，则返回`book`对象。如果尚未打开，则会引发`KeyError`。如果你需要知道工作簿在 Excel 中是否已打开，请使用这个。请确保使用名称而不是完整路径。

在我们深入探讨 xlwings 如何替代你的 VBA 宏之前，让我们看看 xlwings 如何与你现有的 VBA 代码交互：如果你有大量的遗留代码，没有时间将所有内容迁移到 Python，这可能非常有用。

运行 VBA 代码

如果你有大量的带有 VBA 代码的遗留 Excel 项目，将所有内容迁移到 Python 可能需要很多工作。在这种情况下，你可以使用 Python 来运行你的 VBA 宏。下面的示例使用了伴随库的 xl 文件夹中的 vba.xlsm 文件。它在 Module1 中包含以下代码：

Function``MySum``(``x``As``Double``,``y``As``Double``)``As``Double``MySum``=``x``+``y``End``Function

Sub``ShowMsgBox``(``msg``As``String``)``MsgBox``msg``End``Sub

要通过 Python 调用这些函数，你首先需要实例化一个 xlwings macro对象，然后调用它，使其感觉像是本地 Python 函数：

In``[``30``]:``vba_book``=``xw``.``Book``(``"xl/vba.xlsm"``)

In``[``31``]:``# 用 VBA 函数实例化宏对象``mysum``=``vba_book``.``macro``(``"Module1.MySum"``)``# 调用 VBA 函数``mysum``(``5``,``4``)

Out[31]: 9.0

In``[``32``]:``# 使用 VBA Sub 过程同样有效``show_msgbox``=``vba_book``.``macro``(``"Module1.ShowMsgBox"``)``show_msgbox``(``"Hello xlwings!"``)

In``[``33``]:``# 再次关闭该书（确保先关闭 MessageBox）``vba_book``.``close``()

不要将 VBA 函数存储在工作表和此工作簿模块中

如果你将 VBA 函数MySum存储在工作簿模块ThisWorkbook或工作表模块（例如Sheet1）中，你必须将其称为ThisWorkbook.MySum或Sheet1.MySum。然而，你将无法从 Python 访问函数的返回值，所以请确保将 VBA 函数存储在通过在 VBA 编辑器中右键单击模块文件夹插入的标准 VBA 代码模块中。

现在你知道如何与现有的 VBA 代码交互了，我们可以继续探索 xlwings 的使用方法，看看如何与数据框、NumPy 数组和图表、图片以及已定义名称等集合一起使用它。

转换器、选项和集合

在本章的介绍性代码示例中，我们已经通过使用 xlwings 的 range 对象的 value 属性来读取和写入 Excel 中的字符串和嵌套列表。在深入研究允许我们影响 xlwings 读取和写入值的 options 方法之前，我将向您展示如何使用 pandas DataFrames 进行操作。我们继续处理图表、图片和已定义名称，这些通常可以从 sheet 对象访问。掌握这些 xlwings 基础知识后，我们将再次审视第七章中的报告案例。

处理数据框

将数据框写入 Excel 与将标量或嵌套列表写入 Excel 没有任何区别：只需将数据框分配给 Excel 范围的左上角单元格：

In``[``34``]:``data``=``[[``"Mark"``,``55``,``"Italy"``,``4.5``,``"Europe"``],``[``"John"``,``33``,``"USA"``,``6.7``,``"America"``]]``df``=``pd``.``DataFrame``(``data``=``data``,``columns``=``[``"name"``,``"age"``,``"country"``,``"score"``,``"continent"``],``index``=``[``1001``,``1000``])``df``.``index``.``name``=``"user_id"``df

Out[34]: name age country score continent user_id 1001 Mark 55 Italy 4.5 Europe 1000 John 33 USA 6.7 America

In``[``35``]:``sheet1``[``"A6"``]``.``value``=``df

如果您想抑制列标题和/或索引，则使用以下options方法：

In``[``36``]:``sheet1``[``"B10"``]``.``options``(``header``=``False``,``index``=``False``)``.``value``=``df

将 Excel 范围作为数据框读取要求您在 options 方法中将 DataFrame 类作为 convert 参数提供。默认情况下，它期望您的数据具有标题和索引，但您可以再次使用 index 和 header 参数进行更改。而不是使用转换器，您还可以首先将值读取为嵌套列表，然后手动构建数据框，但使用转换器可以更轻松地处理索引和标题。

THE EXPAND METHOD

在下面的代码示例中，我将介绍 expand 方法，该方法使得读取一个连续的单元格块变得简单，提供与在 Excel 中执行 Shift+Ctrl+Down-Arrow+Right-Arrow 相同的范围，不同之处在于 expand 会跳过左上角的空单元格。

In``[``37``]:``df2``=``sheet1``[``"A6"``]``.``expand``()``.``options``(``pd``.``DataFrame``)``.``value``df2

Out[37]:          name   age country score continent          user_id          1001.0   Mark 55.0   Italy    4.5    Europe          1000.0   John 33.0     USA    6.7   America

In``[``38``]:``# 如果您希望索引是整数索引，则可以更改其数据类型``df2``.``index``=``df2``.``index``.``astype``(``int``)``df2

Out[38]:       name   age country score continent          1001 Mark 55.0   Italy    4.5    Europe          1000 John 33.0     USA    6.7   America

In``[``39``]:``# 通过设置 index=False，它将把所有从 Excel 中获取的值放入 DataFrame 的数据部分，并使用默认索引``sheet1``[``"A6"``]``.``expand``()``.``options``(``pd``.``DataFrame``,``index``=``False``)``.``value

Out[39]:    user_id name   age country score continent          0   1001.0 Mark 55.0   Italy    4.5    Europe          1   1000.0 John 33.0     USA    6.7   America

读取和写入 DataFrame 是转换器和选项如何工作的第一个示例。接下来我们将看一下它们是如何正式定义以及如何在其他数据结构中使用的。

转换器和选项

正如我们刚才所看到的，xlwings range 对象的 options 方法允许您影响从 Excel 读取和写入值的方式。也就是说，只有在调用 range 对象的 value 属性时才会评估 options。语法如下（myrange 是一个 xlwings 的 range 对象）：

myrange``.``options``(``convert``=``None``,``option1``=``value1``,``option2``=``value2``,``...``)``.``value

表 9-4 显示了内置转换器，即 convert 参数接受的值。它们被称为内置，因为 xlwings 提供了一种方法来编写自己的转换器，如果需要重复应用额外的转换（例如在写入或读取值之前）时，这将非常有用——要了解它的工作原理，请参阅 xlwings 文档。

表 9-4. 内置转换器

转换器	描述
`dict`	简单的无嵌套字典，即 `{key1: value1, key2: value2, ...}` 的形式
`np.array`	NumPy 数组，需要 `import numpy as np`
`pd.Series`	pandas Series，需要 `import pandas as pd`
`pd.DataFrame`	pandas DataFrame，需要 `import pandas as pd`

我们已经在 DataFrame 示例中使用了 index 和 header 选项，但还有更多的选项可用，如表 9-5 所示。

表 9-5. 内置选项

选项	描述
`empty`	默认情况下，空单元格被读取为 `None`。通过为 `empty` 提供值来更改这一点。
`date`	接受应用于日期格式单元格值的函数。
`number`	接受应用于数字的函数。
`ndim`	维度数：在读取时，使用 `ndim` 强制将范围的值按特定维度到达。必须是 `None`、`1` 或 `2`。在读取值作为列表或 NumPy 数组时可用。
`transpose`	转置值，即将列转换为行或反之。
`index`	用于 pandas 的 DataFrame 和 Series：在读取时，用于定义 Excel 范围是否包含索引。可以是`True`/`False`或整数。整数定义将多少列转换为`MultiIndex`。例如，`2`将使用最左边的两列作为索引。在写入时，可以通过将`index`设置为`True`或`False`来决定是否写出索引。
`header`	与`index`相同，但应用于列标题。

让我们更仔细地看看ndim：默认情况下，从 Excel 读取单个单元格时，您会得到一个标量（例如，浮点数或字符串）；当从列或行读取时，您会得到一个简单的列表；最后，当从二维范围读取时，您会得到一个嵌套的（即二维的）列表。这不仅在自身上是一致的，而且等同于 NumPy 数组中切片的工作方式，正如在第四章中所见。一维情况是特例：有时，列可能只是否则是二维范围的边缘案例。在这种情况下，通过使用ndim=2强制范围始终以二维列表形式到达是有意义的：

In``[``40``]:``# 水平范围（一维）``sheet1``[``"A1:B1"``]``.``value

Out[40]: [1.0, 2.0]

In``[``41``]:``# 垂直范围（一维）``sheet1``[``"A1:A2"``]``.``value

Out[41]: [1.0, 3.0]

In``[``42``]:``# 水平范围（二维）``sheet1``[``"A1:B1"``]``.``options``(``ndim``=``2``)``.``value

Out[42]: [[1.0, 2.0]]

In``[``43``]:``# 垂直范围（二维）``sheet1``[``"A1:A2"``]``.``options``(``ndim``=``2``)``.``value

Out[43]: [[1.0], [3.0]]

In``[``44``]:``# 使用 NumPy 数组转换器的行为相同：``# 垂直范围导致一维数组``sheet1``[``"A1:A2"``]``.``options``(``np``.``array``)``.``value

Out[44]: array([1., 3.])

In``[``45``]:``# 保留列的方向``sheet1``[``"A1:A2"``]``.``options``(``np``.``array``,``ndim``=``2``)``.``value

Out[45]: array([[1.], [3.]])

In``[``46``]:``# 如果需要垂直写出列表，则transpose选项非常方便``sheet1``[``"D1"``]``.``options``(``transpose``=``True``)``.``value``=``[``100``,``200``]

使用ndim=1强制将单个单元格的值读取为列表而不是标量。在 pandas 中，不需要ndim，因为 DataFrame 始终是二维的，Series 始终是一维的。这里还有一个例子，展示了empty、date和number选项的工作方式：

In``[``47``]:``# 写入一些示例数据``sheet1``[``"A13"``]``.``value``=``[``dt``.``datetime``(``2020``,``1``,``1``),``None``,``1.0``]

In``[``48``]:``# 使用默认选项读取它``sheet1``[``"A13:C13"``]``.``value

Out[48]: [datetime.datetime(2020, 1, 1, 0, 0), None, 1.0]

In``[``49``]:``# 使用非默认选项将其读取回来``sheet1``[``"A13:C13"``]``.``options``(``empty``=``"NA"``,``dates``=``dt``.``date``,``numbers``=``int``)``.``value

Out[49]: [datetime.date(2020, 1, 1), 'NA', 1]

到目前为止，我们已经使用了book、sheet和range对象。现在让我们继续学习如何处理从sheet对象访问的图表等集合！

图表、图片和定义名称

在本节中，我将向您展示如何处理通过sheet或book对象访问的三个集合：图表、图片和定义名称。4 xlwings 仅支持最基本的图表功能，但由于您可以使用模板工作，您可能甚至不会错过太多内容。而且，为了补偿，xlwings 允许您将 Matplotlib 绘图嵌入为图片——您可能还记得来自第五章的信息，Matplotlib 是 pandas 的默认绘图后端。让我们从创建第一个 Excel 图表开始吧！

Excel 图表

要添加新图表，请使用charts集合的add方法，然后设置图表类型和源数据：

In``[``50``]:``sheet1``[``"A15"``]``.``value``=``[[``无``,``"北"``,``"南"``],``[``"上年度"``,``2``,``5``],``[``"今年"``,``3``,``6``]]

In``[``51``]:``chart``=``sheet1``.``charts``.``add``(``top``=``sheet1``[``"A19"``]``.``top``,``left``=``sheet1``[``"A19"``]``.``left``)``chart``.``chart_type``=``"column_clustered"``chart``.``set_source_data``(``sheet1``[``"A15"``]``.``expand``())

这将生成左侧显示的图表，位于图 9-2。要查看可用的图表类型，请参阅xlwings 文档。如果你更喜欢使用 pandas 绘图而不是 Excel 图表，或者想使用 Excel 中没有的图表类型，xlwings 已经为你准备好了——让我们看看吧！

图片：Matplotlib 绘图

当您使用 pandas 的默认绘图后端时，您正在创建一个 Matplotlib 绘图。要将这样的绘图移至 Excel，您首先需要获取其figure对象，然后将其作为参数提供给pictures.add——这将把绘图转换为图片并发送至 Excel：

In``[``52``]:``# 将图表数据读取为 DataFrame``df``=``sheet1``[``"A15"``]``.``expand``()``.``options``(``pd``.``DataFrame``)``.``value``df

Out[52]: 北南上年度 2.0 5.0 今年 3.0 6.0

In``[``53``]:``# 通过使用 notebook 魔术命令启用 Matplotlib，并切换到"seaborn"`风格%matplotlibinlineimportmatplotlib.pyplotaspltplt.style.use("seaborn"``)

`In[54]:# pandas 绘图方法返回一个“axis”对象，您可以从中获取图表。"T" 转置 DataFrame 以使绘图达到所需方向ax=df.T.plot.bar()fig=ax.get_figure()``

In``[``55``]:``# 将图表发送到 Excel``plot``=``sheet1``.``pictures``.``add``(``fig``,``name``=``"SalesPlot"``,``top``=``sheet1``[``"H19"``]``.``top``,``left``=``sheet1``[``"H19"``]``.``left``)``# 让我们将图表缩放到 70%``plot``.``width``,``plot``.``height``=``plot``.``width``*``0.7``,``plot``.``height``*``0.7

要使用新图表更新图片，只需使用update方法和另一个figure对象——这实际上将替换 Excel 中的图片，但会保留其所有属性，如位置、大小和名称：

In``[``56``]:``ax``=``(``df``+``1``)``.``T``.``plot``.``bar``()``plot``=``plot``.``update``(``ax``.``get_figure``())

图 9-2. Excel 图表（左）和 Matplotlib 图表（右）

图 9-2 显示了 Excel 图表和 Matplotlib 图表在更新调用后的比较。

确保安装了 PILLOW

在处理图片时，请确保安装了Pillow，Python 的图片处理库：这将确保图片以正确的大小和比例到达 Excel 中。Pillow 是 Anaconda 的一部分，因此如果您使用其他发行版，则需要通过运行conda install pillow或pip install pillow来安装它。请注意，pictures.add还可以接受磁盘上图片的路径，而不是 Matplotlib 图表。

图表和图片是通过sheet对象访问的集合。下面我们将看看如何访问定义名称集合，可以从sheet或book对象中访问。让我们看看这样做有什么区别！

定义名称

在 Excel 中，通过为范围、公式或常量分配名称来创建定义名称。5 将名称分配给范围可能是最常见的情况，称为命名范围。使用命名范围，您可以在公式和代码中使用描述性名称而不是形如A1:B2的抽象地址来引用 Excel 范围。与 xlwings 一起使用它们可以使您的代码更加灵活和稳固：从命名范围读取和写入值使您能够重新组织工作簿而无需调整 Python 代码：名称会粘附在单元格上，即使您通过插入新行等操作移动它。定义名称可以设置为全局工作簿范围或局部工作表范围。工作表范围的名称优势在于，您可以复制工作表而无需担心重复命名范围的冲突。在 Excel 中，您可以通过转到公式 > 定义名称或选择范围，然后在名称框中写入所需名称来手动添加定义名称——名称框位于公式栏左侧，默认显示单元格地址。以下是如何使用 xlwings 管理定义名称的方法：

In``[``57``]:``# 默认作用域为工作簿范围``sheet1``[``"A1:B2"``]``.``name``=``"matrix1"

In``[``58``]:``# 对于工作表范围，请使用叹号将工作表名称前缀``sheet1``[``"B10:E11"``]``.``name``=``"Sheet1!matrix2"

In``[``59``]:``# 现在你可以通过名字访问范围``sheet1``[``"matrix1"``]

Out[59]: <Range [Book2]Sheet1!$A$1:$B$2>

In``[``60``]:``# 如果您通过"sheet1"对象访问名称集合，``# 它仅包含该工作表范围内的名称``sheet1``.``names

Out[60]: [<Name 'Sheet1!matrix2': =Sheet1!$B$10:$E$11>]

In``[``61``]:``# 如果您通过"book"对象访问名称集合，``# 它包含所有名称，包括书籍和工作表范围``book``.``names

Out[61]: [<Name 'matrix1': =Sheet1!$A$1:$B$2>, <Name 'Sheet1!matrix2': =Sheet1!$B$10:$E$11>]

In``[``62``]:``# 名称具有各种方法和属性。例如，您可以获取相应的范围对象。``book``.``names``[``"matrix1"``]``.``refers_to_range

Out[62]: <Range [Book2]Sheet1!$A$1:$B$2>

In``[``63``]:``# 如果您想要为常量或公式分配名称，请使用"add"方法``book``.``names``.``add``(``"EURUSD"``,``"=1.1151"``)

Out[63]: <Name 'EURUSD': =1.1151>

查看通过公式 > 名称管理器打开的 Excel 中生成的定义名称（见 Figure 9-3）。请注意，macOS 上的 Excel 没有名称管理器，而是转到公式 > 定义名称，在那里你将看到现有的名称。

图 9-3. 在 xlwings 添加了几个定义名称后的 Excel 名称管理器

现在，您知道如何使用 Excel 工作簿的最常用组件。这意味着我们可以再次从 Chapter 7 看看报告案例研究：让我们看看当我们引入 xlwings 时会发生什么变化！

Case Study (Re-Revisited): Excel Reporting

能够通过 xlwings 真正编辑 Excel 文件使我们能够处理模板文件，无论其多么复杂或存储在何种格式中，都将完全保留，例如，您可以轻松编辑 xlsb 文件，这是当前所有之前章节中的写入包都不支持的情况。当您查看配套存储库中的 sales_report_openpxyl.py 时，您将看到在准备 summary DataFrame 后，我们需要编写将近四十行代码来创建一个图表并使用 OpenPyXL 样式化一个 DataFrame。而使用 xlwings，您只需六行代码即可实现相同效果，如 Example 9-1 所示。能够处理 Excel 模板中的格式将为您节省大量工作。然而，这也是有代价的：xlwings 需要安装 Excel 才能运行——如果您需要在自己的机器上偶尔创建这些报告，这通常是可以接受的，但如果您试图作为 Web 应用程序的一部分在服务器上创建报告，则可能不太理想。

首先，您需要确保您的 Microsoft Office 许可证覆盖了服务器上的安装，其次，Excel 并不适用于无人值守自动化，这意味着您可能会遇到稳定性问题，尤其是在短时间内需要生成大量报告时。话虽如此，我见过不止一个客户成功地做到了这一点，因此，如果由于某种原因不能使用写入包，将 xlwings 运行在服务器上可能是一个值得探索的选择。只需确保通过 app = xw.App() 在新的 Excel 实例中运行每个脚本，以规避典型的稳定性问题。

您可以在附属存储库中的 sales_report_xlwings.py 中找到完整的 xlwings 脚本（前半部分与我们使用的 OpenPyXL 和 XlsxWriter 相同）。它也是一个完美的示例，展示了如何将读取包与 xlwings 结合使用：尽管 pandas（通过 OpenPyXL 和 xlrd）在从磁盘读取多个文件时更快，但 xlwings 更容易填充预格式化的模板。

示例 9-1. sales_report_xlwings.py（仅第二部分）

# 打开模板，粘贴数据，调整列宽``# 并调整图表数据源。然后以不同的名称保存。``template``=``xw``.``Book``(``this_dir``/``"xl"``/``"sales_report_template.xlsx"``)``sheet``=``template``.``sheets``[``"Sheet1"``]``sheet``[``"B3"``]``.``value``=``summary``sheet``[``"B3"``]``.``expand``()``.``columns``.``autofit``()``sheet``.``charts``[``"Chart 1"``]``.``set_source_data``(``sheet``[``"B3"``]``.``expand``()[:``-``1``,``:``-``1``])``template``.``save``(``this_dir``/``"sales_report_xlwings.xlsx"``)

当您在 macOS 上首次运行此脚本（例如通过在 VS Code 中打开并点击“运行文件”按钮），您将再次确认弹出窗口以授予文件系统访问权限，这是本章早些时候已经遇到的内容。

使用格式化的 Excel 模板，你可以非常快速地创建漂亮的 Excel 报告。你还可以使用 autofit 等方法，这是写入包（如 writer packages）所不具备的功能，因为它依赖 Excel 应用程序进行的计算：这使得你可以根据单元格内容适当设置它们的宽度和高度。图 9-4 展示了由 xlwings 生成的销售报告的上部分，其中包括自定义表头以及应用了 autofit 方法的列。

当你开始使用 xlwings 不仅仅是填充模板中的几个单元格时，了解其内部机制会对你有所帮助：接下来的部分将深入探讨 xlwings 在幕后的工作原理。

图 9-4. 基于预格式化模板的销售报告表格

高级 xlwings 主题

本节将向您展示如何使您的 xlwings 代码更高效，并解决缺少功能的问题。不过，要理解这些主题，我们首先需要简要介绍 xlwings 与 Excel 通信的方式。

xlwings 基础知识

xlwings 依赖于其他 Python 包来与操作系统的自动化机制进行通信：

Windows

在 Windows 上，xlwings 依赖于 COM 技术，即组件对象模型。COM 是一种允许两个进程进行通信的标准——在我们的案例中是 Excel 和 Python。xlwings 使用 Python 包 pywin32 处理 COM 调用。

macOS

在 macOS 上，xlwings 依赖于 AppleScript。AppleScript 是苹果的脚本语言，用于自动化可脚本化的应用程序——幸运的是，Excel 就是这样一个可脚本化的应用程序。为了运行 AppleScript 命令，xlwings 使用 Python 包 appscript。

WINDOWS：如何避免僵尸进程

在 Windows 上使用 xlwings 时，有时会注意到 Excel 看起来完全关闭了，但是当您打开任务管理器（右键单击 Windows 任务栏，然后选择任务管理器）时，在进程选项卡的背景进程下会看到 Microsoft Excel。如果您没有看到任何选项卡，请首先点击“更多详情”。或者，转到详细信息选项卡，在那里您将看到 Excel 列为“EXCEL.EXE”。要终止僵尸进程，请右键单击相应行，然后选择“结束任务”以强制关闭 Excel。

因为这些进程是未终止的不死进程，通常被称为僵尸进程。保留它们会消耗资源，并可能导致不良行为：例如，当您打开新的 Excel 实例时，可能会出现文件被阻塞或加载项未能正确加载的情况。Excel 有时无法正常关闭的原因在于只有在没有 COM 引用（例如 xlwings 的 app 对象形式）时，进程才能被终止。通常，在终止 Python 解释器后，您会遇到 Excel 僵尸进程，因为这会阻止它正确清理 COM 引用。在 Anaconda Prompt 中考虑以下示例：

(base)> python >>> import xlwings as xw >>> app = xw.App()

一旦新的 Excel 实例正在运行，请通过 Excel 用户界面再次退出它：虽然 Excel 关闭了，但任务管理器中的 Excel 进程将继续运行。如果您通过运行 quit() 或使用 Ctrl+Z 快捷键来正确关闭 Python 会话，Excel 进程最终会被关闭。然而，如果您在关闭 Excel 之前杀死 Anaconda Prompt，您会注意到该进程作为僵尸进程存在。如果在运行 Jupyter 服务器并在其中一个 Jupyter 笔记本单元格中保持了 xlwings 的 app 对象时杀死 Anaconda Prompt，情况也是如此。为了最小化出现 Excel 僵尸进程的可能性，这里有几个建议：

从 Python 中运行 app.quit() 而不是手动关闭 Excel。这样可以确保引用被正确清理。
当你使用 xlwings 时，不要关闭交互式 Python 会话，例如，如果你在 Anaconda Prompt 上运行 Python REPL，请通过运行quit()或使用 Ctrl+Z 快捷键来正确关闭 Python 解释器。当你使用 Jupyter 笔记本时，通过在网页界面上点击退出来关闭服务器。
在交互式 Python 会话中，避免直接使用app对象是有帮助的，例如，可以使用xw.Book()代替myapp.books.add()。即使 Python 进程被终止，这样做也应该能正确地终止 Excel。

现在你对 xlwings 的基础技术有了了解，让我们看看如何加快慢脚本的速度！

提高性能

为了保持 xlwings 脚本的性能，有几种策略：最重要的是尽量减少跨应用程序调用。使用原始值可能是另一种选择，最后，设置正确的app属性也可能有所帮助。让我们逐个讨论这些选项！

尽量减少跨应用程序调用

至关重要的是要知道，从 Python 到 Excel 的每个跨应用程序调用都是“昂贵的”，即很慢。因此，应该尽可能减少此类调用。最简单的方法是通过读取和写入整个 Excel 范围而不是遍历单个单元格来实现这一点。在以下示例中，我们首先通过遍历每个单元格，然后通过一次调用处理整个范围，读取和写入 150 个单元格：

In``[``64``]:``# 添加一个新工作表并写入 150 个值``# 以便有点东西可以操作``sheet2``=``book``.``sheets``.``add``()``sheet2``[``"A1"``]``.``value``=``np``.``arange``(``150``)``.``reshape``(``30``,``5``)

In``[``65``]:``%%``time``# 这进行了 150 次跨应用程序调用``for``cell``in``sheet2``[``"A1:E30"``]:``cell``.``value``+=``1

Wall time: 909 ms

In``[``66``]:``%%``time``# 这只进行了两次跨应用程序调用``values``=``sheet2``[``"A1:E30"``]``.``options``(``np``.``array``)``.``value``sheet2``[``"A1"``]``.``value``=``values``+``1

Wall time: 97.2 ms

在 macOS 上，这些数字甚至更加极端，第二个选项比我的机器上的第一个选项快大约 50 倍。

原始值

xlwings 主要设计用于方便使用，而不是速度。但是，如果处理大型单元格范围，可能会遇到可以通过跳过 xlwings 数据清理步骤来节省时间的情况：例如，在读写数据时，xlwings 会遍历每个值，以在 Windows 和 macOS 之间对齐数据类型。通过在 options 方法中使用字符串 raw 作为转换器，可以跳过此步骤。尽管这应该使所有操作更快，但除非在 Windows 上写入大数组，否则差异可能不显著。但是，使用原始值意味着你不能再直接使用 DataFrame 进行工作。相反，你需要将你的值提供为嵌套的列表或元组。此外，你还需要提供写入范围的完整地址——仅提供左上角的单元格不再足够：

In``[``67``]:``# 使用原始值时，必须提供完整的目标范围，sheet["A35"] 不再有效``sheet1``[``"A35:B36"``]``.``options``(``"raw"``)``.``value``=``[[``1``,``2``],``[``3``,``4``]]

应用程序属性

根据工作簿的内容，更改 app 对象的属性也可以帮助加快代码运行速度。通常，你需要查看以下属性（myapp 是 xlwings 的 app 对象）：

myapp.screen_updating = False
myapp.calculation = "manual"
myapp.display_alerts = False

在脚本末尾，确保将属性设置回它们的原始状态。如果你在 Windows 上，通过 xw.App(visible=False) 在隐藏的 Excel 实例中运行脚本，可能还会稍微提高性能。

现在你知道如何控制性能了，让我们看看如何扩展 xlwings 的功能。

如何解决缺失功能

xlwings 为最常用的 Excel 命令提供了 Pythonic 接口，并使其在 Windows 和 macOS 上都能正常工作。然而，Excel 对象模型中有许多方法和属性目前尚未被 xlwings 原生支持，但并非没有办法！xlwings 通过在任何 xlwings 对象上使用 api 属性，让你可以访问 Windows 上的 pywin32 对象和 macOS 上的 appscript 对象。这样一来，你就可以访问整个 Excel 对象模型，但也失去了跨平台兼容性。例如，假设你想清除单元格的格式。下面是如何操作：

检查 xlwings range 对象上是否有可用的方法，例如，在 Jupyter notebook 中在 range 对象的末尾加上点后使用 Tab 键，通过运行 dir(sheet["A1"]) 或搜索 xlwings API 参考。在 VS Code 中，可用方法应自动显示在工具提示中。
如果所需功能不可用，请使用 api 属性获取底层对象：在 Windows 上，sheet["A1"].api 将给出一个 pywin32 对象，在 macOS 上，将得到一个 appscript 对象。
查看 Excel VBA 参考中的 Excel 对象模型。要清除范围的格式，您将最终进入 Range.ClearFormats。
在 Windows 上，在大多数情况下，您可以直接使用 VBA 方法或属性与您的 api 对象。如果是方法，请确保在 Python 中加上括号：sheet["A1"].api.ClearFormats()。如果您在 macOS 上进行此操作，则更复杂，因为 appscript 使用的语法可能很难猜测。您最好的方法是查看作为 xlwings 源代码一部分的开发者指南。然而，清除单元格格式很容易：只需按照 Python 的语法规则使用小写字符和下划线处理方法名称：sheet["A1"].api.clear_formats()。

如果您需要确保 ClearFormats 在两个平台上都能正常工作，可以按以下方式执行（darwin 是 macOS 的核心，并由 sys.platform 用作其名称）：

import``sys``if``sys``.``platform``.``startswith``(``"darwin"``):``sheet``[``"A10"``]``.``api``.``clear_formats``()``elif``sys``.``platform``.``startswith``(``"win"``):``sheet``[``"A10"``]``.``api``.``ClearFormats``()

无论如何，值得在 xlwings 的 GitHub 仓库上提一个问题，以便在将来的版本中包含该功能。

结论

本章向您介绍了 Excel 自动化的概念：通过 xlwings，您可以使用 Python 完成传统上在 VBA 中完成的任务。我们了解了 Excel 对象模型以及 xlwings 如何允许您与其组件如 sheet 和 range 对象交互。掌握了这些知识，我们回到了第七章的报告案例研究，并使用 xlwings 填写了一个预先格式化的报告模板；这展示了您可以在读取器包和 xlwings 并行使用的情况。我们还了解了 xlwings 底层使用的库，以了解如何改进性能并解决缺少功能的问题。我最喜欢的 xlwings 功能是它在 macOS 和 Windows 上同样出色。这更令人兴奋，因为 macOS 上的 Power Query 还没有 Windows 版本的所有功能：无论缺少什么功能，您都应该能够轻松用 pandas 和 xlwings 的组合替代它。

现在您已经了解了 xlwings 的基础知识，可以准备好进入下一章了：在那里，我们将迈出下一步，并从 Excel 本身调用 xlwings 脚本，使您能够构建由 Python 驱动的 Excel 工具。

1   在 Windows 上，您至少需要 Excel 2007，在 macOS 上，您至少需要 Excel 2016。或者，您可以安装 Excel 的桌面版，这是 Microsoft 365 订阅的一部分。查看您的订阅以获取有关如何执行此操作的详细信息。

2   注意，xlwings 0.22.0 引入了 xw.load 函数，它类似于 xw.view，但工作方向相反：它允许您轻松将 Excel 范围加载到 Jupyter 笔记本中作为 pandas DataFrame，详见文档。

3   有关单独的 Excel 实例以及其重要性的更多信息，请参见 “什么是 Excel 实例，以及为什么这很重要？”。

4   另一个流行的集合是 tables。要使用它们，至少需要 xlwings 0.21.0；请参阅文档。

5   带有公式的定义名称也用于 lambda 函数，这是一种在不使用 VBA 或 JavaScript 的情况下定义用户自定义函数的新方法，微软在 2020 年 12 月宣布为 Microsoft 365 订阅用户的新功能。

Python-Excel-指南-二-