Pandas合并Excel文件并自动添加文件名列

在日常数据处理中，我们经常会遇到需要将多个结构相似的Excel文件合并成一个统一数据集的场景。更进一步的需求是，在合并后的数据中，能够清晰地识别每条记录来源于哪个原始文件。本文将详细阐述如何利用Pandas库实现这一目标，为合并后的数据添加一个“文件名”列，从而增强数据溯源能力。

准备工作

在开始之前，请确保你的Python环境中已经安装了pandas库。如果尚未安装，可以通过以下命令进行安装：

pip install pandas openpyxl

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

我们还需要glob库来查找指定目录下的所有Excel文件，以及os库来处理文件路径。

import glob
import pandas as pd
import os

假设所有待合并的Excel文件都位于一个特定目录下，例如../content/。

核心实现：合并文件与添加文件名

实现此功能的关键在于遍历每个文件，在读取数据后，立即为其添加一个表示来源文件名的列，然后将处理后的数据追加到总的DataFrame中。

以下是实现此功能的详细步骤和代码：

1. 查找所有目标文件： 使用glob.glob()函数根据文件模式查找所有匹配的文件路径。
2. 初始化合并后的DataFrame： 创建一个空的Pandas DataFrame，用于存储所有文件的合并数据。
3. 遍历并处理每个文件：
   • 在循环中，逐一读取每个Excel文件。
   • 在读取数据时，可以指定跳过的行数（例如skiprows=1，跳过标题行）和需要使用的列（usecols）。
   • 关键步骤： 为当前文件读取到的DataFrame添加一个名为filename的新列，其值设置为当前文件的基本名称（不包含路径）。
   • 将处理后的当前文件DataFrame追加到之前初始化的总DataFrame中。
4. 保存合并结果： 将最终合并的DataFrame保存为一个新的Excel文件。

import glob
import pandas as pd
import os

# 定义待处理Excel文件的目录和文件模式
# 假设所有Excel文件都以.xlsx结尾，并位于 '../content/' 目录下
file_pattern = '../content/*.xlsx'
files = glob.glob(file_pattern)

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

# 定义我们感兴趣的列名，这些列将从每个Excel文件中读取
# 请根据实际Excel文件的列名进行调整
target_columns = ['Уровень', 'Код WBS', 'Код', 'Тип', 'Название']

print(f"发现 {len(files)} 个Excel文件待处理。")

# 遍历每个文件
for file_path in files:
    try:
        # 1. 读取Excel文件
        # skiprows=1 表示跳过第一行（通常是标题或不必要的信息）
        # usecols 指定只读取我们需要的列，提高效率并确保数据一致性
        df = pd.read_excel(file_path,
                           skiprows=1,
                           usecols=target_columns)

        # 2. 确保DataFrame只包含目标列，并按照指定顺序排列
        # 这一步在usecols已经指定的情况下，主要是为了明确列的顺序
        df = df[target_columns]

        # 3. 添加文件名列
        # os.path.basename(file_path) 用于提取文件名，不包含路径
        df['filename'] = os.path.basename(file_path)

        # 4. 将当前文件的数据追加到合并后的DataFrame
        # ignore_index=True 会重置索引，避免不同文件数据索引重复
        combined_df = pd.concat([combined_df, df], ignore_index=True)
        print(f"已成功处理文件: {os.path.basename(file_path)}")

    except FileNotFoundError:
        print(f"错误：文件未找到 - {file_path}")
    except KeyError as e:
        print(f"错误：文件 {os.path.basename(file_path)} 中缺少必要的列：{e}")
    except Exception as e:
        print(f"处理文件 {os.path.basename(file_path)} 时发生未知错误: {e}")
        # 可以选择跳过当前文件并继续处理下一个
        continue

# 定义输出文件的路径
output_file = "../content/multiplesheet.xlsx"

# 将合并后的数据保存到新的Excel文件
try:
    # index=False 避免将DataFrame的索引作为一列写入Excel文件
    combined_df.to_excel(output_file, sheet_name='Sheet', index=False)
    print(f"\n所有文件已成功合并并保存到: {output_file}")
except Exception as e:
    print(f"\n保存合并文件 {output_file} 时发生错误: {e}")

注意事项

1. 文件路径与模式： glob.glob()支持通配符，如*.xlsx表示所有.xlsx文件，**/*.xlsx表示递归查找子目录中的所有.xlsx文件（需设置recursive=True）。请根据你的文件组织结构调整file_pattern。
2. 列选择与跳过行：
   • skiprows参数非常有用，可以跳过Excel文件开头的非数据行，确保只读取到有效数据。
   • usecols参数可以指定需要读取的列名或列索引。这不仅可以减少内存消耗，还能避免读取不必要的列，并且在某些列不存在时，Pandas会抛出KeyError，有助于发现数据结构问题。
   • 代码中df = df[target_columns]这一行，在usecols已经指定列的情况下，主要作用是确保列的顺序与target_columns列表中的顺序一致。如果usecols已经保证了顺序且没有其他不需要的列，这一行可以省略。
3. 文件名处理： 使用os.path.basename(file_path)可以只获取文件名部分，而不是完整的路径，这使得filename列的数据更加简洁和易读。如果需要完整的路径，可以直接使用file_path。
4. 性能优化： 对于处理大量Excel文件（例如成百上千个）或单个文件非常大的情况，pd.concat在循环中频繁调用可能会导致性能问题。更优化的方法是先将每个文件读取到的DataFrame存储在一个列表中，然后在循环结束后一次性调用pd.concat：

   all_dfs = []
   for file_path in files:
       # ... 读取df，添加filename列 ...
       all_dfs.append(df)
   combined_df = pd.concat(all_dfs, ignore_index=True)

5. 错误处理： 在实际应用中，文件可能不存在、文件格式不正确或缺少预期的列。使用try-except块可以捕获这些异常，防止程序崩溃，并提供有用的错误信息。
6. 索引重置： pd.concat函数中的ignore_index=True参数至关重要。它会在合并后为新的DataFrame生成一个连续的默认整数索引，避免了原始DataFrame索引的重复或混乱。
7. 输出文件： to_excel()函数中的index=False参数用于防止将DataFrame的内部索引作为一列写入到输出的Excel文件中。

总结

通过本教程，我们学习了如何利用Python的Pandas库高效地合并多个Excel文件，并为合并后的数据自动添加一个表示其来源文件名的列。这一方法不仅简化了数据整合过程，更重要的是，它为数据分析和溯源提供了极大的便利。掌握这一技巧，将使你在处理批量Excel数据时更加得心应手。