合并多个Excel文件，Python相当轻松

标签：Python与Excel,pandas

下面是一个应用场景：

我在保险行业工作，每天处理大量数据。有一次，我受命将多个Excel文件合并到一个“主电子表格”中。每个Excel文件都有不同的保险单数据字段，如保单编号、年龄、性别、投保金额等。这些文件有一个共同的列，即保单ID。在过去，我只会使用Excel和VLOOKUP公式，或者Power Query的合并数据函数。这些工具工作得很好，然而，当我们需要处理大型数据集时，它们就成了一种负担。

此时，Python可以上场了。

注意：本文讨论的是合并具有公共ID但不同数据字段的Excel文件。

Excel文件

下面是一些模拟的电子表格，这些数据集非常小，仅用于演示。

图1：投保基本信息.xlsx

图2：投保金与类型.xlsx

图3：到期保单.xlsx

注：如果你不想自己输入，那么可以到知识星球完美Excel社群下载这些演示工作簿。

注意到“保险ID”列包含一个称为“唯一密钥标识符”的内容，该标识符可用于链接三个电子表格中的保单。由于熟悉Excel，我的第一反应是：这很容易，VLOOKUP函数将能完成这项工作。我可以使用VLOOKUP查找每个“保险ID”的值，并将所有数据字段合并到一个电子表格中！结果证明这是个坏主意，因为我要处理数十万条记录，我花了大约一整天的时间用数百万的VLOOKUP和其他公式构建了一个庞大的电子表格。

这是我创建过的最糟糕的Excel文件之一。电子表格的大小是150MB，每当我进行更改时，重新计算大约需要30分钟。真是浪费时间和精力，太可怕了！

如果当时了解Python，那么可以为我节省大量的时间和精力。（即等待电子表格重新计算）

使用Python

像往常一样，先导入pandas库，然后将所有三个Excel文件读入Python。

图4

我们知道，pandas数据框架是一个表格数据对象，它看起来完全像Excel电子表格——行、列和单元格。

图5：pandas数据框架，看起来就像Excel电子表格一样

pandas有一个方法.merge()来高效地合并多个数据集。

df_combine = df_1.merge(df_2, left_on=’保险ID’, right_on=’ID’)

df_combine = df_combine.merge(df_3, on=’保险ID’)

第一次合并

这里，df_1称为左数据框架，df_2称为右数据框架，将df_2与df_1合并基本上意味着我们将两个数据帧框架的所有数据合并在一起，使用一个公共的唯一键匹配df_2到df_1中的每条记录。

就像Excel VLOOKUP公式一样，只是我们用一行代码而不是数百万个公式获得了相同的结果！

注意，在第一个Excel文件中，“保险ID”列包含保险编号，而在第二个Excel文件中，“ID”列包含保险编号，因此我们必须指定，对于左侧数据框架（df_1），希望使用“保险ID”列作为唯一键；而对于右侧的数据框架（df_2），我们希望使用“ID”列作为唯一键。

df_1和df_2中的记录数相同，因此我们可以进行一对一的匹配，并将两个数据框架合并在一起。

图6：合并数据框架，共21行和8列

第二次合并

我们获取第一次合并操作的结果，然后与另一个df_3合并。这一次，因为两个df都有相同的公共列“保险ID”，所以我们只需要使用on='保险ID'来指定它。最终的组合数据框架有8行11列。

图7

关于最终组合数据框架的一些有趣的观察结果：

“保险ID”（来自df_1）和“ID”（来自df_2）都被带到了数据框架中，我们必须删除一个来清理数据。

有两个“保单现金值”列，保单现金值_x（来自df_2）和保单现金值_y（来自df_3）。当有两个相同的列时，默认情况下，pandas将为列名的末尾指定后缀“_x”、“_y”等。我们可以通过在merge()方法中使用可选参数suffixes=('_x'，'_y')来更改后缀。

最终数据框架中只有8行，这是因为df_3只有8条记录。默认情况下，merge()执行”内部”合并，使用来自两个数据框架的键的交集，类似于SQL内部联接。

放在一起

import pandas as pd
# 从Excel文件装载数据集
df_1 = pd.read_excel('D:\投保基本信息.xlsx')
df_2 = pd.read_excel('D:\投保金与类型.xlsx')
df_3 = pd.read_excel('D:\到期保单.xlsx')
# 合并数据集
df_combine = df_1.merge(df_2,left_on='保险ID', right_on='ID')
df_combine = df_combine.merge(df_3,on='保险ID')
# 输出回Excel
df_combine.to_excel('D:\combine_df.xlsx')

合并操作的性能：Excel与Python

你可能已经熟悉Excel，并且知道如果有数千个查找公式，它会有多慢，而此时Python合并两个大型数据集的速度会飞快。

通过使用Python处理数据需求，你的工作效率会有质的提高。我想，是时候开始使用它了！