通宵翻译Pandas官方文档，写了这份Excel万字肝货操作！

阅读须知

这篇万字长文，是黄同学辛苦为大家辛苦翻译排版。希望大家一定从头到尾学习，否则，可能会找不到操作的数据源。

由于许多潜在的 Pandas 用户对 Excel 电子表格有一定的了解，因此本页旨在提供一些案例，说明如何使用 Pandas 执行各Excel电子表格的各种操作。

如果您不熟悉 Pandas，您可能需要先阅读 10 Minutes的官方文档，以熟悉该库。

按照惯例，我们按如下方式，导入 pandas 和 NumPy：

import pandas as pd
import numpy as np

数据结构

1. 通用术语翻译

2. DataFrame

Pandas 中的 DataFrame 类似于 Excel 工作表。虽然 Excel 工作簿可以包含多个工作表，但 Pandas DataFrames 独立存在。

3. Series

序列是表示 DataFrame 的一列的数据结构。使用序列类似于引用电子表格的列。

4. Index

每个 DataFrame 和 Series 都有一个索引，它们是数据行上的标签。在 Pandas 中，如果未指定索引，则默认使用 RangeIndex（第一行 = 0，第二行 = 1，依此类推），类似于电子表格中的行标题/数字。

在 Pandas 中，索引可以设置为一个（或多个）唯一值，这就像在工作表中有一列用作行标识符一样。与大多数电子表格不同，这些索引值实际上可用于引用行。（请注意，这可以在带有结构化引用的 Excel 中完成。）例如，在电子表格中，您可以将第一行引用为 A1:Z1，而在 Pandas 中，您可以使用population.loc['Chicago']。

索引值也是持久的，所以如果你对 DataFrame 中的行重新排序，特定行的标签不会改变。

5. 副本与就地操作

大多数 Pandas 操作返回 Series/DataFrame 的副本。要使更改“保持不变”，您需要分配给一个新变量。

sorted_df = df.sort_values("col1")

或覆盖原来的。

df = df.sort_values("col1")

如果您想就地操作，您将看到某些方法可用的 inplace=True 关键字参数。

df.sort_values("col1", inplace=True)

数据输入和输出

1. 利用值构造一个数据框DataFrame

在Excel电子表格中，值可以直接输入到单元格中。

我们可以用多种不同的方式构建一个DataFrame，但对于少量的值，通常将其指定为 Python 字典会很方便，其中键是列名，值是数据。

df = pd.DataFrame({"x": [1, 3, 5], "y": [2, 4, 6]})
df

结果如下：

2. 读取外部数据

Excel 和 pandas 都可以从各种来源以各种格式导入数据。

CSV

让我们从 Pandas 测试中加载并显示提示数据集，这是一个 CSV 文件。在 Excel 中，您将下载并打开 CSV。在 pandas 中，您将 CSV 文件的 URL 或本地路径传递给 read_csv()。

url = ("https://raw.github.com/pandas-dev/pandas/master/pandas/tests/io/data/csv/tips.csv")
tips = pd.read_csv(url)
tips

结果如下：

与 Excel 的文本导入向导一样，read_csv 可以采用多个参数来指定应如何解析数据。例如，如果数据改为制表符分隔，并且没有列名，pandas 命令将是：

tips = pd.read_csv("tips.csv", sep="\t", header=None)
# 或者，read_table 是带有制表符分隔符的 read_csv 的别名
tips = pd.read_table("tips.csv", header=None)

Excel文件

Excel 通过双击或使用打开菜单打开各种 Excel 文件格式。在 Pandas 中，您使用特殊方法从/向 Excel 文件读取和写入。

让我们首先基于上面示例中的数据框，创建一个新的 Excel 文件。

tips.to_excel("./tips.xlsx")

如果您希望随后访问 tips.xlsx 文件中的数据，您可以使用以下命令将其读入您的模块。

tips_df = pd.read_excel("./tips.xlsx", index_col=0)

您刚刚就使用 Pandas 读取了 Excel 文件！

3. 限制输出

Excel电子表格程序一次只显示一屏数据，然后允许您滚动，因此实际上没有必要限制输出。在 Pandas 中，您需要更多地考虑控制 DataFrame 的显示方式。

默认情况下，pandas 会截断大型 DataFrame 的输出以显示第一行和最后一行。这可以通过更改 pandas 选项或使用 DataFrame.head() 或 DataFrame.tail() 来覆盖。

tips.head(5)

结果如下：

4. 导出数据

默认情况下，桌面电子表格软件将保存为其各自的文件格式（.xlsx、.ods 等）。但是，您可以保存为其他文件格式。

pandas 可以创建 Excel 文件、CSV 或许多其他格式。

数据操作

1. 列操作

在电子表格中，公式通常在单个单元格中创建，然后拖入其他单元格以计算其他列的公式。在 Pandas 中，您可以直接对整列进行操作。

pandas 通过在 DataFrame 中指定单个系列来提供矢量化操作。可以以相同的方式分配新列。DataFrame.drop() 方法从 DataFrame 中删除一列。

tips["total_bill"] = tips["total_bill"] - 2
tips["new_bill"] = tips["total_bill"] / 2
tips

结果如下：

请注意，我们不必告诉它逐个单元地进行减法——pandas 会为我们处理。

2. 过滤

在 Excel 中，过滤是通过图形菜单完成的。

可以通过多种方式过滤数据框，其中最直观的是使用布尔索引。

tips[tips["total_bill"] > 10]

结果如下：

上面的语句只是将一系列 True/False 对象传递给 DataFrame，返回所有带有 True 的行。

is_dinner = tips["time"] == "Dinner"
is_dinner.value_counts()
tips[is_dinner]

结果如下：

3. If/then逻辑

假设我们想要根据 total_bill 是小于还是大于 10 美元，来创建一个具有低值和高值的列。

在Excel电子表格中，可以使用条件公式进行逻辑比较。我们将使用 =IF(A2 < 10, "low", "high")的公式，将其拖到新存储列中的所有单元格。

使用 numpy 中的 where 方法可以完成 Pandas 中的相同操作。

tips["bucket"] = np.where(tips["total_bill"] < 10, "low", "high")
tips

结果如下：

4. 日期功能

本节将提到“日期”，但时间戳的处理方式类似。

我们可以将日期功能分为两部分：解析和输出。在Excel电子表格中，日期值通常会自动解析，但如果您需要，还有一个 DATEVALUE 函数。在 Pandas 中，您需要在从 CSV 读取时或在 DataFrame 中读取一次时，将纯文本显式转换为日期时间对象。

解析后，Excel电子表格以默认格式显示日期，但格式可以更改。在 Pandas 中，您通常希望在使用日期进行计算时将日期保留为日期时间对象。输出部分日期（例如年份）是通过电子表格中的日期函数和 Pandas 中的日期时间属性完成的。

给定电子表格 A 列和 B 列中的 date1 和 date2，您可能有以下公式：

等效的Pandas操作如下所示。

tips["date1"] = pd.Timestamp("2013-01-15")
tips["date2"] = pd.Timestamp("2015-02-15")
tips["date1_year"] = tips["date1"].dt.year
tips["date2_month"] = tips["date2"].dt.month
tips["date1_next"] = tips["date1"] + pd.offsets.MonthBegin()
tips["months_between"] = tips["date2"].dt.to_period("M") - tips["date1"].dt.to_period("M")
tips[["date1", "date2", "date1_year", "date2_month", "date1_next", "months_between"]]

结果如下：

5. 列的选择

在Excel电子表格中，您可以通过以下方式选择所需的列：

• 隐藏列；
• 删除列；
• 引用从一个工作表到另一个工作表的范围；

由于Excel电子表格列通常在标题行中命名，因此重命名列只需更改第一个单元格中的文本即可。

相同的操作在下面的Pandas中表示。

保留某些列

tips[["sex", "total_bill", "tip"]]

结果如下：

删除某些列

tips.drop("sex", axis=1)

结果如下：

重命名列

tips.rename(columns={"total_bill": "total_bill_2"})

结果如下：

6. 按值排序

Excel电子表格中的排序，是通过排序对话框完成的。

pandas 有一个 DataFrame.sort_values() 方法，它需要一个列列表来排序。

tips = tips.sort_values(["sex", "total_bill"])
tips

结果如下：

字符串处理

1. 查找字符串长度

在电子表格中，可以使用 LEN 函数找到文本中的字符数。这可以与 TRIM 函数一起使用以删除额外的空格。

=LEN(TRIM(A2))

您可以使用 Series.str.len() 找到字符串的长度。在 Python 3 中，所有字符串都是 Unicode 字符串。len 包括尾随空格。使用 len 和 rstrip 排除尾随空白。

tips["time"].str.len()
tips["time"].str.rstrip().str.len()

结果如下：

请注意，这仍然会在字符串中包含多个空格，因此不是 100% 等效的。

2. 查找子串的位置

FIND电子表格函数返回子字符串的位置，第一个字符为 1。

您可以使用 Series.str.find() 方法查找字符串列中字符的位置。find 搜索子字符串的第一个位置。如果找到子字符串，则该方法返回其位置。如果未找到，则返回 -1。请记住，Python 索引是从零开始的。

tips["sex"].str.find("ale")

结果如下：

3. 按位置提取子串

电子表格有一个 MID 公式，用于从给定位置提取子字符串。获取第一个字符：

=MID(A2,1,1)

使用 Pandas，您可以使用 [] 表示法按位置位置从字符串中提取子字符串。请记住，Python 索引是从零开始的。

tips["sex"].str[0:1]

结果如下：

4. 提取第n个单词

在 Excel 中，您可以使用文本到列向导来拆分文本和检索特定列。（请注意，也可以通过公式来做到这一点。）

在 Pandas 中提取单词最简单的方法是用空格分割字符串，然后按索引引用单词。请注意，如果您需要，还有更强大的方法。

firstlast = pd.DataFrame({"String": ["John Smith", "Jane Cook"]})
firstlast["First_Name"] = firstlast["String"].str.split(" ", expand=True)[0]
firstlast["Last_Name"] = firstlast["String"].str.rsplit(" ", expand=True)[0]
firstlast

结果如下：

5. 大小写转换

Excel电子表格提供 UPPER、LOWER 和 PROPER 函数，分别用于将文本转换为大写、小写和标题大小写。

等效的 Pandas 方法是 Series.str.upper()、Series.str.lower() 和 Series.str.title()。

firstlast = pd.DataFrame({"string": ["John Smith", "Jane Cook"]})
firstlast["upper"] = firstlast["string"].str.upper()
firstlast["lower"] = firstlast["string"].str.lower()
firstlast["title"] = firstlast["string"].str.title()
firstlast

结果如下：

合并

合并示例中将使用以下表格：

df1 = pd.DataFrame({"key": ["A", "B", "C", "D"], "value": np.random.randn(4)})
df2 = pd.DataFrame({"key": ["B", "D", "D", "E"], "value": np.random.randn(4)})

结果如下：

在 Excel 中，可以通过 VLOOKUP 完成表格的合并。

pandas DataFrames 有一个 merge() 方法，它提供了类似的功能。数据不必提前排序，不同的连接类型是通过 how 关键字完成的。

inner_join = df1.merge(df2, on=["key"], how="inner")
left_join = df1.merge(df2, on=["key"], how="left")
right_join = df1.merge(df2, on=["key"], how="right")
outer_join = df1.merge(df2, on=["key"], how="outer")

结果如下：

与 VLOOKUP 相比，merge 有许多优点：

• 查找值不需要是查找表的第一列；
• 如果匹配多行，则每个匹配都会有一行，而不仅仅是第一行；
• 它将包括查找表中的所有列，而不仅仅是单个指定的列；
• 它支持更复杂的连接操作；

其他注意事项

1. 填充柄

在一组特定的单元格中按照设定的模式创建一系列数字。在电子表格中，这将在输入第一个数字后通过 shift+drag 或通过输入前两个或三个值然后拖动来完成。

这可以通过创建一个系列并将其分配给所需的单元格来实现。

df = pd.DataFrame({"AAA": [1] * 8, "BBB": list(range(0, 8))})
series = list(range(1, 5))
df.loc[2:5, "AAA"] = series
df

结果如下：

2. 删除重复项

Excel 具有删除重复值的内置功能。熊猫通过 drop_duplicates() 支持这一点。

df = pd.DataFrame({
     "class": ["A", "A", "A", "B", "C", "D"],
     "student_count": [42, 35, 42, 50, 47, 45],
     "all_pass": ["Yes", "Yes", "Yes", "No", "No", "Yes"]})
df.drop_duplicates()
df.drop_duplicates(["class", "student_count"])

结果如下：

3. 数据透视表

电子表格中的数据透视表可以通过重塑和数据透视表在 Pandas 中复制。再次使用提示数据集，让我们根据聚会的规模和服务器的性别找到平均小费。

在 Excel 中，我们对数据透视表使用以下配置：

等效的Pandas代码。

pd.pivot_table(
     tips, 
     values="tip", 
     index=["size"], 
     columns=["sex"], 
     aggfunc=np.average)

结果如下：

4. 添加一行

假设我们使用 RangeIndex（编号为 0、1 等），我们可以使用 DataFrame.append() 在 DataFrame 的底部添加一行。

df
new_row = {"class": "E", "student_count": 51, "all_pass": True}
df.append(new_row, ignore_index=True)

结果如下：

5. 查找和替换

Excel 查找对话框将您带到匹配的单元格。在 Pandas 中，这个操作一般是通过条件表达式一次对整个列或 DataFrame 完成。

tips
tips == "Sun"
tips["day"].str.contains("S")

结果如下：

pandas 的 replace() 相当于 Excel 的 Replace All。

tips.replace("Thu", "Thursday")

结果如下：

万水千山总是情，点个 ? 行不行。

推荐阅读

···  END  ···