使用Python将多个Excel文件合并到一个主电子表格中

标签：Python与Excel,pandas

本文展示如何使用Python将多个Excel文件合并到一个主电子表格中。假设你有几十个具有相同数据字段的Excel文件，需要从这些文件中聚合工作表。我们知道，手工完成这项工作效率非常低，而使用Python自动化合并文件将为你节省大量时间。

当然，你也可以使用Excel VBA来实现，但Python更简洁，效率更高。

工作流程

要解决此问题，我们需要遵循以下工作流程：

1.识别我们需要合并的文件。

2.从文件中获取数据。

3.将数据从步骤2移动到主数据集（我们称之为“数据框架”）。

4.对多个文件，重复步骤2-3。

5.将主数据框架保存到Excel电子表格。

导入库

现在，让我们看看如何用Python实现上述工作流程。我们需要使用两个Python库：os和pandas。（你可以到知识星球完美Excel社群下载示例工作簿。这里使用了3个示例工作簿来演示，当然你可以根据需要合并任意多个Excel工作簿文件。）

os库提供了一种使用操作系统相关功能的方法，例如操控文件夹和文件路径。我们使用这个库获取所有Excel文件名，包括它们的路径。

pandas库是数据分析和处理的黄金标准，它快速、强大、灵活。我们使用这个库将Excel数据加载到Python中，操作数据，并重新创建主电子表格。

我们将从导入这两个库开始，然后查找指定目录中的所有文件名。

import os

import pandas as pd

os.chdir(‘D:\\test’)

cwd = os.path.abspath(‘’)

files = os.listdir(cwd)

图1

使用chdir方法改变当前目录，变量cwd代表当前工作目录，变量files是指定工作目录中所有文件名的列表。注意，存在非Excel文件，我们不想打开这些文件，因此要处理这些文件。

将多个Excel文件合并到一个电子表格中

接下来，我们创建一个空数据框架df，用于存储主电子表格的数据。我们遍历指定目录中的所有文件，但只处理名称以“.xlsx”结尾的Excel文件，这是由下面的代码完成的：

if file.endswith（'.xlsx'）：

read_excel()将excel数据读入Python并将其存储为DataFrame对象。注意，默认情况下，此方法仅读取Excel文件的第一个工作表。

append()将数据从一个文件追加/合并到另一个文件。考虑从一个Excel文件复制一块数据并粘贴到另一个Excel文件中。数据存储在计算机内存中，而不打开Excel。

图2

上述代码执行以下操作：

1.循环遍历当前工作目录中的所有文件，通过检查以“.xlsx”结尾的文件名来确定文件是否为Excel文件。

2.如果是，则读取文件内容（数据），并将其追加/添加到名为df的主数据框架变量中。

3.将主数据框架保存到Excel电子表格中。

可以通过检查df.head()来检查主数据框架，它显示了数据的前5行，如上图2所示。

还可以做另一个快速检查，以确保我们已经加载了数据框架中的所有内容。df.shape将向我们显示数据的大小（36行，5列）：

图3

一切顺利！最后将数据输出回Excel，最后一行df.to_excel()将执行此操作。

合并同一Excel文件中的多个工作表

在《使用Python pandas读取多个Excel工作表》中，讲解了两种技术，这里不再重复，但会使用稍微不同的设置来看一个示例。

我们有2个文件，每个文件包含若干个工作表。我们不知道每个文件中有多少个工作表，但知道所有工作表的格式都是相同的。目标是将所有工作表聚合到一个电子表格（和一个文件）中。

工作流程如下：

1.获取所有Excel文件。

2.循环遍历Excel文件。

3.对于每个文件，循环遍历所有工作表。

4.将每个工作表读入一个数据框架，然后将所有数据框架组合在一起。

df_total = pd.DataFrame()

#遍历所有Excel文件

for file in files:

if file.endswith(‘.xlsx’):

excel_file = pd.ExcelFile(file)

sheets= excel_file.sheet_names

#遍历Excel文件中的工作表

for sheet in sheets:

df = excel_file.parse(sheet_name = sheet)

df_total = df_total.append(df)

df_total.to_excel(‘combined_file.xlsx’)

把它们放在一起

下面是完整的代码。简洁的几行代码将帮助你将所有Excel文件或工作表合并到一个主电子表格中。

图4

注：本文学习整理自pythoninoffice.com。

欢迎在下面留言，完善本文内容，让更多的人学到更完美的知识。