Pandas 合并Excel文件并添加来源列

在数据分析和处理的日常工作中，我们经常会遇到需要将分散在多个 Excel 文件中的数据整合到一起的情况。这些文件通常具有相同的结构，但包含了不同时间段或不同来源的数据。在合并这些数据时，一个常见的需求是保留每条记录的原始文件来源信息，以便后续的数据溯源、分组分析或问题排查。本教程将详细阐述如何使用 Python 的 pandas 库和 glob 模块实现这一目标。

1. 准备工作

首先，确保您的 Python 环境中安装了 pandas 库。如果尚未安装，可以使用以下命令进行安装：

pip install pandas openpyxl

openpyxl 是 pandas 读取 .xlsx 格式 Excel 文件所需的引擎。

2. 核心思路

合并多个 Excel 文件并添加源文件名列的核心思路如下：

1. 使用 glob 模块查找指定路径下的所有目标 Excel 文件。
2. 创建一个空的 pandas DataFrame，用于存储所有合并后的数据。
3. 遍历每一个找到的 Excel 文件：
   • 读取当前 Excel 文件到临时的 DataFrame 中。
   • 在临时 DataFrame 中新增一个列，将当前文件的完整路径（或文件名）作为该列的值赋给所有行。
   • 将带有源文件名列的临时 DataFrame 追加到总的合并 DataFrame 中。
4. 将最终合并的数据保存到新的 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）来优化代码。