Pandas高效合并多Excel工作簿数据

引言

在日常数据分析和报告工作中，我们经常需要处理大量分散在多个 Excel 文件中的数据。这些文件可能包含多个工作表，并且我们需要从中提取特定工作表的数据进行整合。手动操作不仅效率低下，还容易出错。Python 的 Pandas 库提供了强大的数据处理能力，能够自动化这一复杂过程。本文将深入探讨如何利用 Pandas 优雅地解决多 Excel 文件、多工作表的数据合并问题。

环境准备

在开始之前，请确保您的 Python 环境中已安装 Pandas 和用于读取 Excel 文件的引擎库（如 openpyxl 或 xlrd）。如果尚未安装，可以通过以下命令进行安装：

pip install pandas openpyxl xlrd

理解常见错误：AttributeError: 'str' object has no attribute 'sheet_names'

在处理 Excel 文件时，一个常见的错误是 AttributeError: 'str' object has no attribute 'sheet_names'。这个错误通常发生在尝试对一个文件路径字符串（str 类型）直接调用 sheet_names 方法时。sheet_names 是 pandas.ExcelFile 对象的属性，而不是文件路径字符串的属性。

错误原因示例：

path = "your_excel_file.xlsx"
# 错误：path 是字符串，没有 sheet_names 属性
for sheet_name in path.sheet_names: 
    pass

正确做法：

在使用 sheet_names 之前，必须先将文件路径传递给 pd.ExcelFile() 构造函数，创建一个 ExcelFile 对象。

file_path = "your_excel_file.xlsx"
xls = pd.ExcelFile(file_path) # 创建 ExcelFile 对象
for sheet_name in xls.sheet_names: # 现在可以访问 sheet_names 属性
    pass

理解这一点是避免此类错误的关键，也是本文核心解决方案的基础。

核心解决方案：使用 Pandas 合并多文件多工作表数据

我们的目标是遍历指定目录下的所有 Excel 文件，识别并合并其中符合特定条件（例如，名称匹配）的工作表数据。最终，我们将把来自不同文件的同名工作表数据合并成一个独立的 DataFrame，并存储在一个字典中。

解决方案概述

1. 指定根目录：确定存放 Excel 文件的最上层目录。
2. 遍历文件系统：使用 os.walk 遍历根目录及其所有子目录，查找 Excel 文件。
3. 加载 Excel 文件：对每个找到的 Excel 文件，使用 pd.ExcelFile() 加载。
4. 获取工作表名称：通过 xls.sheet_names 获取当前 Excel 文件中所有工作表的名称。
5. 条件筛选与解析：根据预设条件（如工作表名称）筛选工作表，并使用 xls.parse() 将其解析为 Pandas DataFrame。
6. 数据整合：将来自不同文件的同名工作表数据收集起来，并使用 pd.concat() 进行纵向合并。
7. 存储结果：将合并后的 DataFrame 存储在一个字典中，以工作表名称作为键。

示例代码

以下是一个完整的 Python 函数，实现了上述数据合并逻辑：

import os
import pandas as pd

def merge_excel_sheets(base_path, target_sheet_names=None):
    """
    合并指定路径下多个Excel文件中符合条件的工作表。

    Args:
        base_path (str): 包含Excel文件的根目录路径。
        target_sheet_names (list, optional): 一个列表，包含需要合并的工作表名称。
                                              如果为None，则合并所有非排除工作表。

    Returns:
        dict: 键为工作表名称，值为合并后的DataFrame的字典。
              每个DataFrame包含来自所有Excel文件中同名工作表的数据。
    """
    # 临时存储每个工作表名称下的所有DataFrame列表
    all_sheet_data_lists = {} 

    print(f"开始遍历目录: {base_path}")

    # 遍历指定目录及其子目录
    for root, _, files in os.walk(base_path):
        for fname in files:
            file_path = os.path.join(root, fname)

            # 确保只处理Excel文件（.xlsx 或 .xls 扩展名）
            if fname.endswith(('.xlsx', '.xls')):
                try:
                    # 使用 pd.ExcelFile 加载 Excel 文件，而不是直接操作字符串路径
                    xls = pd.ExcelFile(file_path)
                    print(f"\n正在处理文件: {fname}")

                    # 遍历当前Excel文件中的所有工作表
                    for sheet_name in xls.sheet_names:
                        # 根据 target_sheet_names 筛选工作表
                        if target_sheet_names and sheet_name not in target_sheet_names:
                            continue # 跳过不符合条件的工作表

                        print(f"  - 发现并处理工作表: '{sheet_name}'")

                        try:
                            # 解析指定工作表到 DataFrame
                            df = xls.parse(sheet_name)

                            # 将当前 DataFrame 添加到对应工作表名称的列表中
                            if sheet_name not in all_sheet_data_lists:
                                all_sheet_data_lists[sheet_name] = []
                            all_sheet_data_lists[sheet_name].append(df)
                        except Exception as e:
                            print(f"    - 警告: 无法解析工作表 '{sheet_name}' 在文件 '{fname}' 中: {e}")
                            continue
                except Exception as e:
                    print(f"  - 错误: 无法加载Excel文件 '{fname}': {e}")
                    continue
            else:
                print(f"  - 跳过非Excel文件: {fname}")

    # 将每个工作表名称下的所有DataFrame列表合并成一个DataFrame
    final_merged_dict = {}
    for sheet_name, df_list in all_sheet_data_lists.items():
        if df_list:
            # 使用 pd.concat 纵向合并所有 DataFrame
            final_merged_dict[sheet_name] = pd.concat(df_list, ignore_index=True)
            print(f"\n成功合并工作表 '{sheet_name}' 的数据。总行数: {len(final_merged_dict[sheet_name])}")
        else:
            print(f"警告: 工作表 '{sheet_name}' 未找到任何数据进行合并。")

    return final_merged_dict

# --- 使用示例 ---
# 请将 'your/excel/files/path' 替换为你的Excel文件所在的实际路径
# 确保该路径下包含多个Excel文件，且这些文件内有同名的工作表。
excel_directory_path = 'your/excel/files/path' 

# 示例：合并名为 'Portfolios' 和 'SP Search Term Req' 的工作表
# 如果希望合并所有工作表，可以将 target_sheet_names 设置为 None
target_sheets_to_merge = ['Portfolios', 'SP Search Term Req'] 

# 调用函数执行合并操作
merged_dataframes = merge_excel_sheets(excel_directory_path, target_sheet_names=target_sheets_to_merge)

# 打印合并结果的概览
if merged_dataframes:
    print("\n--- 合并结果概览 ---")
    for sheet_name, df in merged_dataframes.items():
        print(f"\n工作表 '{sheet_name}' 合并后的数据 (前5行):")
        print(df.head())
        print(f"总行数: {len(df)}")
else:
    print("\n未找到符合条件的工作表数据进行合并。")

# 如果需要将所有合并后的DataFrame进一步整合成一个大的DataFrame
# all_combined_dfs = list(merged_dataframes.values())
# if all_combined_dfs:
#     final_single_df = pd.concat(all_combined_dfs, ignore_index=True)
#     print("\n所有符合条件的工作表合并成一个大DataFrame的概览 (前5行):")
#     print(final_single_df.head())
#     print(f"总行数: {len(final_single_df)}")

代码详解

• import os 和 import pandas as pd: 导入所需的 os 模块用于文件系统操作，以及 pandas 模块用于数据处理。
• merge_excel_sheets(base_path, target_sheet_names=None) 函数:
  • base_path: Excel 文件所在的根目录路径。
  • target_sheet_names: 一个可选列表，包含您希望合并的工作表名称。如果为 None，则会尝试合并所有发现的工作表（请注意，这可能会导致大量数据）。
  • all_sheet_data_lists = {}: 这是一个字典，用于临时存储。它的键是工作表名称，值是一个列表，该列表包含了来自不同 Excel 文件的同名工作表的 DataFrame。
• os.walk(base_path): 这是一个生成器，它会递归地遍历 base_path 下的所有目录和文件。每次迭代返回一个三元组 (root, dirs, files)，其中 root 是当前目录的路径，dirs 是 root 下的子目录列表，files 是 root 下的文件列表。
• os.path.join(root, fname): 用于构建文件的完整路径，确保跨平台兼容性。
• fname.endswith(('.xlsx', '.xls')): 检查文件扩展名，确保只处理 Excel 文件。
• pd.ExcelFile(file_path): 关键步骤。它将 Excel 文件加载为一个 ExcelFile 对象。只有通过这个对象，我们才能访问文件的元数据（如 sheet_names）和内容。
• xls.sheet_names: 返回当前 ExcelFile 对象中所有工作表的名称列表。
• 条件判断 if target_sheet_names and sheet_name not in target_sheet_names:: 根据 target_sheet_names 列表筛选需要处理的工作表。
• xls.parse(sheet_name): 从 ExcelFile 对象中解析指定名称的工作表，并将其转换为一个 Pandas DataFrame。
• 数据收集 all_sheet_data_lists[sheet_name].append(df): 将解析出的 DataFrame 添加到 all_sheet_data_lists 字典中对应工作表名称的列表中。
• pd.concat(df_list, ignore_index=True): 在遍历完所有文件并收集到所有同名工作表的 DataFrame 列表后，使用 pd.concat 将这些 DataFrame 纵向堆叠（即行追加），ignore_index=True 会重置合并后的 DataFrame 的索引。
• 错误处理 try...except: 捕获在加载 Excel 文件或解析工作表时可能发生的错误，提高代码的健壮性。

注意事项

1. 文件路径准确性：请务必将示例代码中的 'your/excel/files/path' 替换为您的 Excel 文件所在的实际路径。路径错误是导致程序无法运行的常见原因。
2. 内存消耗：如果您的 Excel 文件数量庞大或单个工作表数据量巨大，pd.concat 操作可能会消耗大量内存。在这种情况下，可以考虑：
   • 分批处理文件。
   • 在解析时指定 dtype 参数以优化 DataFrame 的数据类型，减少内存占用。
   • 如果数据量过大，考虑使用 Dask 等大数据处理库。
3. 数据结构一致性：当合并多个 Excel 文件中的同名工作表时，最好确保这些工作表的列结构（列名、列顺序）大致相同。如果列名不一致，pd.concat 默认会保留所有列，并在缺失值处填充 NaN。
4. 错误处理与日志记录：示例代码中包含了基本的 try-except 块来处理文件加载和工作表解析错误。在生产环境中，建议加入更详细的日志记录，以便追踪问题。
5. 空文件或空工作表：代码会尝试处理所有 Excel 文件。如果存在空文件或空工作表，xls.parse() 可能会返回空的 DataFrame，这在 pd.concat 中通常