使用Pandas合并多Excel文件并记录源文件名

在数据分析和处理的场景中，我们经常需要整合来自多个文件的数据。当这些数据源自不同的excel文件时，一个常见的需求是在合并后的数据集中，能够追溯每一行数据来源于哪个原始文件。本教程将指导您如何使用python的pandas库实现这一目标，即将多个excel文件合并为一个dataframe，并为每条记录添加一个包含其原始文件名的列。

1. 环境准备

首先，确保您已经安装了必要的Python库：pandas 用于数据处理，glob 用于文件路径匹配。如果尚未安装，可以使用以下命令：

pip install pandas openpyxl

openpyxl 是Pandas读取和写入.xlsx格式文件所需的引擎。

2. 核心思路

实现这一目标的核心思路是：

1. 使用glob模块查找指定目录下的所有Excel文件。
2. 遍历这些文件。
3. 在每次循环中，读取一个Excel文件到Pandas DataFrame。
4. 在读取的DataFrame中，新增一个列，其值设为当前正在处理的文件名。
5. 将这个带有文件名的DataFrame追加到总的DataFrame中。
6. 所有文件处理完毕后，将最终合并的DataFrame导出。

3. 代码实现

以下是实现上述功能的Python代码示例：

import glob
import pandas as pd
import os # 导入os模块，用于获取文件名

# 1. 定义文件路径模式
# 假设所有Excel文件都在 'content' 目录下，且以 '.xlsx' 结尾
# 请根据您的实际文件路径进行修改
excel_files_pattern = '../content/*.xlsx' 

# 2. 使用 glob 查找所有匹配的Excel文件
# glob.glob() 返回一个文件路径列表
files = glob.glob(excel_files_pattern)

# 3. 初始化一个空的DataFrame用于存储合并后的数据
combined_df = pd.DataFrame()

# 4. 遍历每个文件，读取并添加文件名列
for file_path in files:
    try:
        # 提取文件名（不包含路径和扩展名）
        # os.path.basename() 获取带扩展名的文件名
        # os.path.splitext()[0] 进一步移除扩展名
        file_name = os.path.splitext(os.path.basename(file_path))[0]

        # 读取Excel文件
        # skiprows=1 表示跳过第一行（如果您的文件有标题行需要跳过）
        # usecols 指定要读取的列，提高效率和准确性
        current_df = pd.read_excel(file_path,
                                   skiprows=1,
                                   usecols=['Уровень','Код WBS','Код','Тип','Название'])

        # 确保只保留需要的列，即使usecols已指定，这里也可作为二次确认或列顺序调整
        current_df = current_df[['Уровень','Код WBS','Код','Тип','Название']]

        # 添加新的 'filename' 列，值为当前处理的文件名
        current_df['filename'] = file_name

        # 将当前DataFrame追加到总的DataFrame中
        # ignore_index=True 重新生成连续的索引，避免索引重复
        combined_df = pd.concat([combined_df, current_df], ignore_index=True)

    except Exception as e:
        print(f"处理文件 {file_path} 时发生错误: {e}")

# 5. 将合并后的DataFrame导出到新的Excel文件
output_file_path = "../content/multiplesheet_with_filenames.xlsx"
try:
    combined_df.to_excel(output_file_path, sheet_name='CombinedData', index=False)
    print(f"所有文件已成功合并并导出到: {output_file_path}")
except Exception as e:
    print(f"导出文件 {output_file_path} 时发生错误: {e}")

4. 代码解析

• import glob 和 import pandas as pd 和 import os: 导入所需的库。glob 用于查找文件，pandas 用于数据操作，os 用于处理文件路径。
• *`excel_files_pattern = '../content/.xlsx'**: 定义一个模式字符串，glob将根据这个模式来查找文件。../content/表示当前脚本所在目录的上一级目录下的content文件夹。*.xlsx表示所有以.xlsx` 结尾的文件。
• files = glob.glob(excel_files_pattern): 这行代码会返回一个列表，其中包含了所有符合 excel_files_pattern 模式的文件路径。
• combined_df = pd.DataFrame(): 创建一个空的Pandas DataFrame，用于逐步累积所有Excel文件的数据。
• for file_path in files:: 循环遍历 files 列表中每一个文件的完整路径。
• file_name = os.path.splitext(os.path.basename(file_path))[0]: 这行代码用于从完整的文件路径中提取出不带扩展名的文件名。
  • os.path.basename(file_path): 获取路径的最后一部分，即文件名（例如：'file1.xlsx'）。
  • os.path.splitext(...): 将文件名分割成根和扩展名（例如：('file1', '.xlsx')）。
  • [0]: 获取分割后的第一部分，即不带扩展名的文件名。
• current_df = pd.read_excel(...): 使用 pd.read_excel() 函数读取当前循环到的Excel文件。
  • skiprows=1: 跳过文件中的第一行，这在数据从第二行开始时很有用。
  • usecols=['Уровень','Код WBS','Код','Тип','Название']: 指定只读取这些列，这可以减少内存使用并提高读取效率。
• current_df['filename'] = file_name: 这是关键一步，为当前读取的DataFrame添加一个名为 filename 的新列，其所有行的值都设置为当前文件的名称。
• combined_df = pd.concat([combined_df, current_df], ignore_index=True): 使用 pd.concat() 函数将当前处理的 current_df 追加到 combined_df 中。
  • ignore_index=True: 确保合并后的DataFrame拥有一个连续的、新的索引，而不是保留原始文件的索引，这可以避免索引冲突。
• combined_df.to_excel(...): 将最终合并的DataFrame导出为一个新的Excel文件。
  • sheet_name='CombinedData': 指定导出文件的Sheet名称。
  • index=False: 不将DataFrame的索引写入Excel文件。
• 错误处理 (try...except): 增加了基本的错误处理，当某个文件处理失败时，会打印错误信息而不是中断整个程序。

5. 注意事项与优化

• 文件路径: 确保 excel_files_pattern 正确指向您的Excel文件所在目录。相对路径（如 ../content/）是相对于脚本执行位置的。

• 列名一致性: 确保所有Excel文件中您希望合并的列具有相同的名称和数据类型。Pandas在合并时会根据列名进行匹配。如果列名不一致，合并后可能会出现新的列，且对应位置为NaN。

• skiprows 和 usecols: 根据您的实际Excel文件结构调整 skiprows 和 usecols 参数。usecols 可以显著提高大型文件的读取效率。

• 内存管理: 对于非常大的文件集合，pd.concat 在循环中反复创建新的DataFrame可能会导致内存效率不高。对于极大规模的数据，可以考虑使用list comprehension先将所有DataFrame存储在一个列表中，最后一次性调用pd.concat，或者使用Dask等分布式计算库。

  # 优化内存的合并方式
  all_dfs = []
  for file_path in files:
      # ... (读取文件和添加filename列的代码不变) ...
      all_dfs.append(current_df)
  
  combined_df = pd.concat(all_dfs, ignore_index=True)

  登录后复制

• 错误处理: 示例中包含了基本的 try...except 块，但在生产环境中，您可能需要更健壮的错误日志记录或特定的错误处理逻辑。

总结

通过本教程，您已经掌握了如何使用Python的Pandas库高效地合并多个Excel文件，并为每条记录添加一个指示其来源文件的新列。这种方法不仅简化了数据整合过程，还极大地增强了数据追溯能力，是数据分析和报告工作中一个非常实用的技巧。

以上就是使用Pandas合并多Excel文件并记录源文件名的详细内容，更多请关注php中文网其它相关文章！