在数据分析和处理的日常工作中，我们经常会遇到需要将分散在多个 excel 文件中的数据整合到一起的情况。这些文件通常具有相同的结构，但包含了不同时间段或不同来源的数据。在合并这些数据时，一个常见的需求是保留每条记录的原始文件来源信息，以便后续的数据溯源、分组分析或问题排查。本教程将详细阐述如何使用 python 的 pandas 库和 glob 模块实现这一目标。
1. 准备工作
首先，确保您的 Python 环境中安装了 pandas 库。如果尚未安装，可以使用以下命令进行安装：
pip install pandas openpyxl
openpyxl 是 pandas 读取 .xlsx 格式 Excel 文件所需的引擎。
2. 核心思路
合并多个 Excel 文件并添加源文件名列的核心思路如下：
- 使用 glob 模块查找指定路径下的所有目标 Excel 文件。
- 创建一个空的 pandas DataFrame，用于存储所有合并后的数据。
- 遍历每一个找到的 Excel 文件：
- 读取当前 Excel 文件到临时的 DataFrame 中。
- 在临时 DataFrame 中新增一个列，将当前文件的完整路径（或文件名）作为该列的值赋给所有行。
- 将带有源文件名列的临时 DataFrame 追加到总的合并 DataFrame 中。
- 将最终合并的数据保存到新的 Excel 文件。
3. 代码实现
以下是实现上述功能的 Python 代码示例：
import glob
import pandas as pd
import os # 导入 os 模块以获取文件名
def combine_excel_files_with_filename(input_path_pattern, output_file, sheet_name='Sheet', skip_rows=0, use_cols=None):
"""
合并指定路径下的所有 Excel 文件，并为每条记录添加源文件名列。
Args:
input_path_pattern (str): 用于匹配 Excel 文件的路径模式，例如 '../content/*.xlsx'。
output_file (str): 合并后数据输出的 Excel 文件路径，例如 '../content/multiplesheet.xlsx'。
sheet_name (str): 输出 Excel 文件的 sheet 名称，默认为 'Sheet'。
skip_rows (int): 读取每个 Excel 文件时跳过的行数，默认为 0。
use_cols (list): 读取每个 Excel 文件时使用的列名列表，默认为 None (读取所有列)。
"""
# 1. 使用 glob 查找所有匹配的 Excel 文件
files = glob.glob(input_path_pattern)
if not files:
print(f"警告：未找到匹配 '{input_path_pattern}' 的文件。")
return
combined_df = pd.DataFrame() # 初始化一个空的 DataFrame 用于存储合并结果
print(f"开始处理 {len(files)} 个文件...")
for file_path in files:
try:
# 2. 读取当前 Excel 文件
# 注意：skiprows 和 usecols 参数根据实际文件结构调整
current_df = pd.read_excel(file_path, skiprows=skip_rows, usecols=use_cols)
# 如果指定了 use_cols，确保DataFrame中只有这些列，
# 这一步在 read_excel 的 usecols 参数已经处理，但如果 use_cols 是一个子集，
# 并且想确保顺序，可以再次筛选。原始问题中包含了这一步，这里保留。
if use_cols:
current_df = current_df[use_cols]
# 3. 添加源文件名列
# os.path.basename(file_path) 可以只获取文件名，不包含路径
current_df['filename'] = os.path.basename(file_path)
# 4. 将当前 DataFrame 追加到总的 DataFrame 中
combined_df = pd.concat([combined_df, current_df], ignore_index=True)
print(f"已处理文件: {os.path.basename(file_path)}")
except Exception as e:
print(f"处理文件 '{os.path.basename(file_path)}' 时发生错误: {e}")
continue # 继续处理下一个文件
# 5. 将合并后的数据保存到新的 Excel 文件
if not combined_df.empty:
try:
combined_df.to_excel(output_file, sheet_name=sheet_name, index=False)
print(f"\n所有文件已成功合并并保存到: {output_file}")
except Exception as e:
print(f"保存合并文件到 '{output_file}' 时发生错误: {e}")
else:
print("\n没有数据被成功合并。")
# 示例调用
if __name__ == "__main__":
# 假设您的 Excel 文件位于 '../content/' 目录下
# 并且每个文件都跳过第一行，并只使用指定列
input_pattern = r'../content/*.xlsx'
output_path = "../content/multiplesheet.xlsx"
# 原始问题中指定的列
columns_to_use = ['Уровень','Код WBS','Код','Тип','Название']
combine_excel_files_with_filename(
input_path_pattern=input_pattern,
output_file=output_path,
skip_rows=1, # 根据原始问题，跳过第一行
use_cols=columns_to_use
)4. 关键步骤解析
- glob.glob(input_path_pattern): 这个函数用于查找所有与指定模式匹配的文件路径。例如，'../content/*.xlsx' 会匹配 ../content/ 目录下所有以 .xlsx 结尾的文件。
-
pd.read_excel(file_path, skiprows=1, use_cols=['...']): 这是 pandas 读取 Excel 文件的核心函数。
- file_path：要读取的 Excel 文件路径。
- skiprows=1：表示在读取数据时跳过文件的第一行，这通常用于跳过标题行或不相关的数据。
- use_cols=['...']：指定只读取 Excel 文件中的哪些列。这有助于减少内存使用并聚焦于所需数据。
- current_df['filename'] = os.path.basename(file_path): 这是添加源文件名列的关键一步。os.path.basename(file_path) 函数从完整的文件路径中提取出文件名（不包含目录路径）。这个文件名会被赋值给 current_df 中新创建的名为 'filename' 的所有行。
-
pd.concat([combined_df, current_df], ignore_index=True): pd.concat 函数用于将两个或多个 DataFrame 沿指定轴连接起来。
- [combined_df, current_df]：要连接的 DataFrame 列表。
- ignore_index=True：在连接后重新生成索引，避免重复的索引值。
5. 注意事项
文件路径管理: 确保 input_path_pattern 和 output_file 的路径是正确的。使用相对路径（如 ../content/）时，要明确脚本运行的当前工作目录。
-
内存效率: 对于数量巨大或单文件很大的 Excel 文件，频繁使用 pd.concat 在循环内部可能会导致性能下降，因为它每次都会创建新的 DataFrame。一个更高效的方法是先将每个 current_df 存储在一个列表中，然后在循环结束后一次性调用 pd.concat：
# 优化后的合并部分 all_dfs = [] for file_path in files: try: current_df = pd.read_excel(file_path, skiprows=skip_rows, use_cols=use_cols) if use_cols: current_df = current_df[use_cols] current_df['filename'] = os.path.basename(file_path) all_dfs.append(current_df) print(f"已处理文件: {os.path.basename(file_path)}") except Exception as e: print(f"处理文件 '{os.path.basename(file_path)}' 时发生错误: {e}") continue if all_dfs: combined_df = pd.concat(all_dfs, ignore_index=True) else: combined_df = pd.DataFrame() # 确保在没有文件时 combined_df 仍然是空的 DataFrame这种方式在处理大量文件时能显著提高性能。
错误处理: 在示例代码中，我们添加了 try-except 块来捕获文件读取或处理过程中可能出现的错误，例如文件损坏或格式不正确。这增强了程序的健壮性。
列名与数据类型: 确保所有 Excel 文件的列名和数据类型一致，否则合并后可能会出现不一致的数据类型或额外的列。use_cols 参数有助于强制列的一致性。
索引: ignore_index=True 是非常重要的，它会为合并后的 DataFrame 创建一个全新的、连续的索引，避免了原始文件索引的混淆。
6. 总结
通过本教程，您已经掌握了如何使用 Python 的 pandas 库和 glob 模块高效地合并多个 Excel 文件，并在合并过程中为每条记录动态添加一个指示其来源文件的新列。这一技巧在数据集成、报告生成和数据追溯等场景中非常实用，能够极大地提高您的数据处理效率和分析能力。记住，根据实际数据量和性能需求，可以选择不同的合并策略（如先收集列表再 concat）来优化代码。
