【python】基于pandas的EXCEL合并方法

前言

在数据分析和处理工作中，经常会遇到需要合并多个Excel文件的情况。本文介绍了一种使用Python编程语言中的Pandas库和Glob模块来自动化合并Excel文件的方法。通过编写简洁的脚本，我们可以高效地搜索、读取、合并以及保存大量Excel文件，极大地提高了数据处理的效率。

关键词：Python, Pandas, Glob, Excel文件合并

正文

一、引言

在处理大规模数据集时，数据往往分散在多个Excel文件中。手动合并这些文件不仅耗时，而且容易出错。自动化这一过程可以节省大量时间，并减少人为错误。本文将展示如何使用Python中的Pandas库和Glob模块来实现这一目标。

二、方法

1. 导入必要的库：

import pandas as pd
import glob

2. 初始化一个列表来存储找到的Excel文件路径：

file_paths = []

3. 使用Glob模块搜索指定目录下所有的Excel文件，并将路径存储到列表中：

file_paths = glob.glob(r'./test/*.xlsx')

4. 打印找到的文件列表，以确认文件已被正确识别。

5. 读取列表中的第一个Excel文件，并初始化一个DataFrame用于存储合并后的数据：

first_file = file_paths[0]
initial_data = pd.read_excel(first_file)

6. 遍历剩余的Excel文件，并将它们逐个合并到初始DataFrame中：

for file_path in file_paths[1:]:
    additional_data = pd.read_excel(file_path)
    initial_data = pd.concat([initial_data, additional_data], ignore_index=True)

7. 打印最终DataFrame的索引，验证数据是否正确合并。

8. 使用Pandas的ExcelWriter功能，将合并后的数据写入一个新的Excel文件中：

with pd.ExcelWriter('all.xlsx') as writer:
    initial_data.to_excel(writer, sheet_name='Sheet1', index=False)

三、小结

执行上述脚本后，所有的Excel文件将被合并到一个名为“all.xlsx”的新文件中。该文件将包含所有原始文件的数据，且不包含原始索引。

本文介绍的方法提供了一种快速、自动化的方式来合并Excel文件，特别适用于需要处理大量数据的情况。使用Python的Pandas库和Glob模块，我们可以轻松地扩展此脚本，以适应不同的文件路径和文件类型。

自动化合并Excel文件的过程不仅提高了数据处理的效率，还减少了人为错误的可能性。这种方法可以广泛应用于数据清洗、预处理和分析的各个阶段。

import pandas as pd
import glob

# 初始化用于存储文件路径的列表
file_paths = glob.glob(r'./test/*.xlsx')

# 打印找到的文件列表
for file_path in file_paths:
    print(f"Found file: {file_path}")

# 读取第一个 Excel 文件并初始化结果 DataFrame
first_file = file_paths[0]
initial_data = pd.read_excel(first_file)

# 合并剩余的 Excel 文件到结果 DataFrame 中
for file_path in file_paths[1:]:
    additional_data = pd.read_excel(file_path)
    # 使用 concat 函数合并数据，忽略索引并保持数据顺序
    initial_data = pd.concat([initial_data, additional_data], ignore_index=True)

# 打印最终 DataFrame 的索引，以验证数据是否正确合并
print("Final DataFrame index:", initial_data.index)

# 使用 ExcelWriter 将合并后的数据写入新的 Excel 文件
with pd.ExcelWriter('all.xlsx') as writer:
    initial_data.to_excel(writer, sheet_name='Sheet1', index=False)

print("Data has been successfully merged and saved to 'all.xlsx'.")

这段代码首先使用glob模块搜索当前目录下所有的.xlsx格式的Excel文件，并将它们的路径存储在一个列表中。然后，它读取列表中的第一个文件，并将其数据初始化为一个DataFrame。接着，代码遍历剩余的文件，将它们的数据添加到最初的DataFrame中，并使用concat函数合并数据，同时忽略索引以保持数据顺序。最后，使用ExcelWriter将合并后的数据写入一个新的Excel文件中.

如何使用pandas的merge函数而不是concat函数来合并多个Excel文件？

Pandas的merge函数与concat函数的区别

Pandas的merge函数和concat函数都是用于合并数据的工具，但它们的工作原理和适用场景有所不同。

concat函数

concat函数主要用于沿特定轴线（行或列）将多个DataFrame对象连接在一起。它不会基于列名或行索引进行合并，而是简单地将一个DataFrame堆叠在另一个DataFrame的上面或旁边。concat函数适合于将具有相同结构的DataFrame进行垂直或水平堆叠，但它不会根据列的值进行合并。

merge函数

merge函数则是基于一个或多个键将两个DataFrame对象进行连接，类似于SQL中的JOIN操作。它可以执行不同类型的连接，包括内连接（只保留两个DataFrame中都有匹配的行）、左连接（保留左侧DataFrame的所有行，并根据连接键匹配右侧DataFrame的行）、右连接（保留右侧DataFrame的所有行，并根据连接键匹配左侧DataFrame的行）和外连接（保留两个DataFrame中的所有行）。

如何使用merge函数合并多个Excel文件

要使用merge函数合并多个Excel文件，您需要首先使用pandas.read_excel函数读取每个Excel文件到DataFrame中，然后使用merge函数根据共享的键进行合并。以下是一个简单的示例：

import pandas as pd

# 读取两个Excel文件
df1 = pd.read_excel('./test/test.xlsx')
df2 = pd.read_excel('./test/test2.xlsx')

# 合并DataFrame
merged_df = pd.merge(df1, df2, on=['id', 'age', 'sex', 'region', 'income', 'married'], how='outer')

# 保存合并后的数据到Excel文件
merged_df.to_excel('merged_file.xlsx', index=False)

在上述代码中，on=['','']指定了用于合并的键，how='inner'指定了合并的类型（在内连接的情况下，只有共享键相匹配的行才会被合并）。最后，使用to_excel函数将合并后的DataFrame保存到新的Excel文件中。

请注意，使用merge函数时，确保合并的键在所有要合并的DataFrame中都存在，并且它们的数据类型是兼容的。如果键在不同的DataFrame中有不同的数据类型，可能需要在合并前进行类型转换。此外，如果合并的DataFrame中有重复的键值，可能需要处理这些重复值，以免影响合并结果。

参考文献

[1] McKinney, W. (2010). Data Structures for Statistical Computing in Python. In Proceedings of the 9th Python in Science Conference. Austin, TX.
[2] Pandas-Development-Team. (n.d.). Pandas Documentation. Retrieved from https://pandas.pydata.org/pandas-docs/stable/