在日常数据处理中，我们经常会遇到需要将多个结构相似的excel文件合并成一个统一数据集的场景。更进一步的需求是，在合并后的数据中，能够清晰地识别每条记录来源于哪个原始文件。本文将详细阐述如何利用pandas库实现这一目标，为合并后的数据添加一个“文件名”列，从而增强数据溯源能力。
准备工作
在开始之前，请确保你的Python环境中已经安装了pandas库。如果尚未安装，可以通过以下命令进行安装：
pip install pandas openpyxl
openpyxl是Pandas读取和写入.xlsx格式Excel文件所需的引擎。
我们还需要glob库来查找指定目录下的所有Excel文件，以及os库来处理文件路径。
import glob import pandas as pd import os
假设所有待合并的Excel文件都位于一个特定目录下，例如../content/。
核心实现：合并文件与添加文件名
实现此功能的关键在于遍历每个文件，在读取数据后，立即为其添加一个表示来源文件名的列，然后将处理后的数据追加到总的DataFrame中。
以下是实现此功能的详细步骤和代码：
- 查找所有目标文件： 使用glob.glob()函数根据文件模式查找所有匹配的文件路径。
- 初始化合并后的DataFrame： 创建一个空的Pandas DataFrame，用于存储所有文件的合并数据。
-
遍历并处理每个文件：
- 在循环中，逐一读取每个Excel文件。
- 在读取数据时，可以指定跳过的行数（例如skiprows=1，跳过标题行）和需要使用的列（usecols）。
- 关键步骤： 为当前文件读取到的DataFrame添加一个名为filename的新列，其值设置为当前文件的基本名称（不包含路径）。
- 将处理后的当前文件DataFrame追加到之前初始化的总DataFrame中。
- 保存合并结果： 将最终合并的DataFrame保存为一个新的Excel文件。
import glob
import pandas as pd
import os
# 定义待处理Excel文件的目录和文件模式
# 假设所有Excel文件都以.xlsx结尾，并位于 '../content/' 目录下
file_pattern = '../content/*.xlsx'
files = glob.glob(file_pattern)
# 初始化一个空的DataFrame，用于存储所有合并后的数据
combined_df = pd.DataFrame()
# 定义我们感兴趣的列名，这些列将从每个Excel文件中读取
# 请根据实际Excel文件的列名进行调整
target_columns = ['Уровень', 'Код WBS', 'Код', 'Тип', 'Название']
print(f"发现 {len(files)} 个Excel文件待处理。")
# 遍历每个文件
for file_path in files:
try:
# 1. 读取Excel文件
# skiprows=1 表示跳过第一行（通常是标题或不必要的信息）
# usecols 指定只读取我们需要的列，提高效率并确保数据一致性
df = pd.read_excel(file_path,
skiprows=1,
usecols=target_columns)
# 2. 确保DataFrame只包含目标列，并按照指定顺序排列
# 这一步在usecols已经指定的情况下，主要是为了明确列的顺序
df = df[target_columns]
# 3. 添加文件名列
# os.path.basename(file_path) 用于提取文件名，不包含路径
df['filename'] = os.path.basename(file_path)
# 4. 将当前文件的数据追加到合并后的DataFrame
# ignore_index=True 会重置索引，避免不同文件数据索引重复
combined_df = pd.concat([combined_df, df], ignore_index=True)
print(f"已成功处理文件: {os.path.basename(file_path)}")
except FileNotFoundError:
print(f"错误：文件未找到 - {file_path}")
except KeyError as e:
print(f"错误：文件 {os.path.basename(file_path)} 中缺少必要的列：{e}")
except Exception as e:
print(f"处理文件 {os.path.basename(file_path)} 时发生未知错误: {e}")
# 可以选择跳过当前文件并继续处理下一个
continue
# 定义输出文件的路径
output_file = "../content/multiplesheet.xlsx"
# 将合并后的数据保存到新的Excel文件
try:
# index=False 避免将DataFrame的索引作为一列写入Excel文件
combined_df.to_excel(output_file, sheet_name='Sheet', index=False)
print(f"\n所有文件已成功合并并保存到: {output_file}")
except Exception as e:
print(f"\n保存合并文件 {output_file} 时发生错误: {e}")
注意事项
- 文件路径与模式： glob.glob()支持通配符，如*.xlsx表示所有.xlsx文件，**/*.xlsx表示递归查找子目录中的所有.xlsx文件（需设置recursive=True）。请根据你的文件组织结构调整file_pattern。
-
列选择与跳过行：
- skiprows参数非常有用，可以跳过Excel文件开头的非数据行，确保只读取到有效数据。
- usecols参数可以指定需要读取的列名或列索引。这不仅可以减少内存消耗，还能避免读取不必要的列，并且在某些列不存在时，Pandas会抛出KeyError，有助于发现数据结构问题。
- 代码中df = df[target_columns]这一行，在usecols已经指定列的情况下，主要作用是确保列的顺序与target_columns列表中的顺序一致。如果usecols已经保证了顺序且没有其他不需要的列，这一行可以省略。
- 文件名处理： 使用os.path.basename(file_path)可以只获取文件名部分，而不是完整的路径，这使得filename列的数据更加简洁和易读。如果需要完整的路径，可以直接使用file_path。
-
性能优化： 对于处理大量Excel文件（例如成百上千个）或单个文件非常大的情况，pd.concat在循环中频繁调用可能会导致性能问题。更优化的方法是先将每个文件读取到的DataFrame存储在一个列表中，然后在循环结束后一次性调用pd.concat：
all_dfs = [] for file_path in files: # ... 读取df，添加filename列 ... all_dfs.append(df) combined_df = pd.concat(all_dfs, ignore_index=True) - 错误处理： 在实际应用中，文件可能不存在、文件格式不正确或缺少预期的列。使用try-except块可以捕获这些异常，防止程序崩溃，并提供有用的错误信息。
- 索引重置： pd.concat函数中的ignore_index=True参数至关重要。它会在合并后为新的DataFrame生成一个连续的默认整数索引，避免了原始DataFrame索引的重复或混乱。
- 输出文件： to_excel()函数中的index=False参数用于防止将DataFrame的内部索引作为一列写入到输出的Excel文件中。
总结
通过本教程，我们学习了如何利用Python的Pandas库高效地合并多个Excel文件，并为合并后的数据自动添加一个表示其来源文件名的列。这一方法不仅简化了数据整合过程，更重要的是，它为数据分析和溯源提供了极大的便利。掌握这一技巧，将使你在处理批量Excel数据时更加得心应手。
