使用Pandas高效合并多Excel文件中的指定工作表数据

在日常数据处理中，我们经常会遇到需要从多个Excel文件中提取特定数据并进行整合的场景。这些Excel文件可能包含多个工作表，而我们只对其中某些特定名称的工作表感兴趣。本教程将引导您使用Pandas库，以专业且高效的方式完成这项任务。

1. 准备工作与核心概念

在开始之前，请确保您已安装了Pandas库。如果尚未安装，可以使用以下命令进行安装：

pip install pandas openpyxl

openpyxl是Pandas读取.xlsx文件所需的引擎。

核心概念：

• os.walk(): 用于遍历指定目录及其子目录下的所有文件和文件夹。
• pandas.ExcelFile(): Pandas提供的一个强大工具，用于加载整个Excel工作簿，使其能够访问所有工作表的名称和内容。
• xls_file.sheet_names: ExcelFile对象的一个属性，返回一个包含所有工作表名称的列表。
• xls_file.parse(): ExcelFile对象的方法，用于将指定名称的工作表解析为DataFrame。

2. 详细实现步骤

我们将通过一个具体的代码示例来演示如何合并数据。

2.1 导入必要的库

首先，我们需要导入os库用于文件系统操作，以及pandas库用于数据处理。

import os
import pandas as pd

2.2 定义Excel文件路径

指定存放所有Excel文件的根目录。请将'your/excel/files/path'替换为您的实际路径。

酷表ChatExcel

北大团队开发的通过聊天来操作Excel表格的AI工具

48

查看详情

# 定义Excel文件所在的路径
path = 'your/excel/files/path' 

2.3 初始化数据存储字典

我们使用一个字典df_dict_flex来存储从各个Excel文件中提取出的DataFrame。字典的键可以是工作表名称，值是对应的DataFrame。

# 初始化一个字典，用于存储DataFrames
df_dict_flex = {}

2.4 遍历文件目录并处理Excel文件

使用os.walk()遍历指定路径下的所有文件。对于每个找到的文件，我们需要进行以下操作：

1. 构建完整文件路径：os.path.join(root, fname)将目录路径和文件名组合成完整的路径。
2. 筛选Excel文件：通过检查文件扩展名（.xlsx或.xls）来确保我们只处理Excel文件。
3. 加载Excel文件：使用pd.ExcelFile(file_path)加载整个Excel工作簿。这是解决AttributeError的关键一步。
4. 遍历工作表：通过xls_file.sheet_names获取所有工作表名称，并逐一遍历。
5. 条件筛选与解析：根据预设条件（例如，工作表名称是否为'Portfolios'或'SP Search Term Req'）来选择性地解析工作表数据。
6. 存储DataFrame：将解析后的DataFrame存储到df_dict_flex字典中。

# 遍历包含Excel文件的目录
for root, dirs, files in os.walk(path):
    for fname in files:
        # 构造完整的文件路径
        file_path = os.path.join(root, fname)

        # 确保只处理Excel文件
        if file_path.endswith('.xlsx') or file_path.endswith('.xls'):
            try:
                # 加载Excel文件
                xls_file = pd.ExcelFile(file_path)
                print(f"正在处理文件: {file_path}")

                # 提取并遍历每个Excel文件中的工作表名称
                for sheet_name in xls_file.sheet_names:
                    print(f"  发现工作表: {sheet_name}")

                    # 检查工作表名称是否符合给定条件
                    # 此处示例是选择名称为 'Portfolios' 或 'SP Search Term Req' 的工作表
                    if sheet_name == 'Portfolios' or sheet_name == 'SP Search Term Req':
                        # 将特定工作表解析为DataFrame
                        df = xls_file.parse(sheet_name)
                        # 将DataFrame存储在字典中，以工作表名称为键
                        # 注意：如果多个文件有同名且符合条件的工作表，后面的会覆盖前面的。
                        # 若需合并同名工作表数据，请参考“注意事项”部分。
                        df_dict_flex[sheet_name] = df
                        print(f"    已解析并存储工作表: {sheet_name}")
            except Exception as e:
                print(f"处理文件 {file_path} 时发生错误: {e}")

# 此时，df_dict_flex 字典中包含了所有符合条件的工作表的DataFrames
print("\n所有符合条件的工作表已合并到 df_dict_flex 字典中。")
print("字典键 (工作表名称):", df_dict_flex.keys())

3. 解决 AttributeError: 'str' object has no attribute 'sheet_names'

原始代码中出现的AttributeError: 'str' object has no attribute 'sheet_names'错误，是因为您尝试在一个字符串对象（path，即文件路径）上调用sheet_names()方法。sheet_names是pandas.ExcelFile对象的一个属性，而不是字符串的属性。

错误原因分析： 在原始代码片段中，for sheet_name in path.sheet_names(): 这一行，path变量实际上是一个字符串，代表文件路径。字符串对象没有sheet_names这个方法或属性，因此Python抛出了AttributeError。

正确做法： 您必须先使用pd.ExcelFile(file_path)将文件路径加载为一个ExcelFile对象，然后才能访问该对象的sheet_names属性。修正后的代码通过xls_file = pd.ExcelFile(file_path)创建了xls_file对象，后续对xls_file.sheet_names的访问才是正确的。

4. 注意事项与进阶应用

• 路径替换：务必将代码中的'your/excel/files/path'替换为您的实际文件路径。
• 错误处理：在文件操作中，使用try-except块来捕获潜在的错误（例如文件不存在、文件损坏、权限问题等）是一个良好的编程习惯，可以提高代码的健壮性。
• 工作表名称匹配：
  • 如果您需要排除某些工作表，可以将条件改为if sheet_name != '排除工作表1' and sheet_name != '排除工作表2':。
  • 如果您需要更灵活的匹配（例如包含特定子字符串的工作表），可以使用if '关键词' in sheet_name:。
  • 对于复杂的模式匹配，可以考虑使用正则表达式。
• 同名工作表处理：当前代码逻辑是，如果多个Excel文件中有同名且符合条件的工作表，df_dict_flex[sheet_name] = df会覆盖之前的数据。如果您希望将所有同名工作表的数据合并（例如，追加到同一个DataFrame中），您需要修改逻辑，例如：

  if sheet_name in df_dict_flex:
      df_dict_flex[sheet_name] = pd.concat([df_dict_flex[sheet_name], df], ignore_index=True)
  else:
      df_dict_flex[sheet_name] = df

  登录后复制

  这会将所有名为sheet_name的工作表数据堆叠起来。

• 数据进一步处理：df_dict_flex现在包含了您所需的所有DataFrame。您可以根据需要对这些DataFrame进行进一步的分析、合并或保存。例如，如果您想将所有提取出的DataFrame合并成一个大的DataFrame，并且它们结构相似，可以这样做：

  # 假设所有提取的DataFrames结构相似，可以合并
  final_combined_df = pd.concat(df_dict_flex.values(), ignore_index=True)
  print("\n所有提取的DataFrame合并后的总数据:")
  print(final_combined_df.head())

  登录后复制

5. 总结

本教程提供了一个健壮且灵活的Python Pandas解决方案，用于从多个Excel文件中提取并合并特定工作表的数据。通过理解os.walk()和pandas.ExcelFile()的用法，以及正确处理AttributeError，您可以高效地自动化复杂的Excel数据整合任务。根据您的具体需求，可以进一步扩展代码以实现更复杂的筛选、合并和数据处理逻辑。

以上就是使用Pandas高效合并多Excel文件中的指定工作表数据的详细内容，更多请关注php中文网其它相关文章！