在数据分析和处理的日常工作中，我们经常需要整合来自多个文件的数据。当这些数据分散在多个excel文件中时，一个常见的需求是不仅将它们合并，还要能够追溯每条记录的原始文件来源。本文将介绍如何使用python的pandas库实现这一目标，为合并后的数据添加一个包含来源文件名的列。
1. 问题背景与解决方案概述
设想您有多个Excel文件，每个文件都包含相似结构的数据，例如销售记录、项目进度或库存信息。您希望将所有这些文件的数据汇总到一个单一的DataFrame中。在此基础上，为了后续的数据分析或审计，您还需要知道每一行数据最初来源于哪个Excel文件。
传统的合并方法可能只关注数据的堆叠，而忽略了来源信息的保留。通过在数据读取和合并的循环中，为每个文件的数据帧动态添加一个新列，并将当前文件名作为该列的值，我们可以优雅地解决这个问题。
2. 核心实现步骤
实现这一功能的关键在于迭代处理每个文件时，在将文件内容添加到总的DataFrame之前，为其添加一个包含文件名的列。
2.1 导入必要的库
首先，我们需要导入glob库用于查找文件路径，以及pandas库用于数据处理。
import glob import pandas as pd
2.2 查找目标文件
使用glob模块可以方便地根据模式匹配文件路径。例如，查找特定目录下所有的.xlsx文件。
# 定义目标文件所在的目录和文件类型 # 假设Excel文件位于 'content' 目录下 files = [item for item in glob.glob(r'../content/*.xlsx')] # 打印查找到的文件列表（可选） # print(files)
这里r'../content/*.xlsx'是一个原始字符串，..表示上一级目录，content是目标文件夹名，*.xlsx表示所有以.xlsx结尾的文件。
2.3 迭代读取、添加文件名并合并
这是实现核心功能的步骤。我们将遍历查找到的每个文件，读取其内容，然后为每个文件的DataFrame添加一个名为filename的新列，最后将处理后的DataFrame合并到总的DataFrame中。
combined = pd.DataFrame() # 初始化一个空的DataFrame用于存储合并后的数据
# 定义需要读取的列名，这里以示例中的俄语列名为例
# 这些列名应与您的Excel文件中的实际列名相匹配
target_columns = ['Уровень', 'Код WBS', 'Код', 'Тип', 'Название']
for file_path in files:
# 1. 读取Excel文件
# skiprows=1 表示跳过第一行，通常用于跳过标题行或不必要的信息
# usecols 指定只读取我们关心的列，提高效率
file_df = pd.read_excel(file_path,
skiprows=1,
usecols=target_columns)
# 2. 确保只包含目标列（如果usecols已指定，此步可省略，但保留可增加代码健壮性）
# file_df = file_df[target_columns] # 此行在usecols已指定时是冗余的
# 3. 添加新的 'filename' 列
# file_path 变量存储了当前文件的完整路径
file_df['filename'] = file_path
# 4. 将当前文件的DataFrame与总的DataFrame合并
# pd.concat 用于沿特定轴连接Pandas对象，axis=0 是默认值，表示按行堆叠
combined = pd.concat([combined, file_df], ignore_index=True) # ignore_index=True 重置索引
# 5. 将合并后的数据保存到新的Excel文件
output_path = "../content/multiplesheet_with_filename.xlsx"
combined.to_excel(output_path, sheet_name='CombinedData', index=False) # index=False 不保存DataFrame索引
print(f"所有文件已合并并保存至: {output_path}")3. 完整示例代码
将上述步骤整合在一起，得到如下完整代码：
import glob
import pandas as pd
# 定义目标文件所在的目录和文件类型
# 假设Excel文件位于 'content' 目录下
files = [item for item in glob.glob(r'../content/*.xlsx')]
combined = pd.DataFrame() # 初始化一个空的DataFrame用于存储合并后的数据
# 定义需要读取的列名，这里以示例中的俄语列名为例
target_columns = ['Уровень', 'Код WBS', 'Код', 'Тип', 'Название']
for file_path in files:
try:
# 读取Excel文件
file_df = pd.read_excel(file_path,
skiprows=1,
usecols=target_columns)
# 添加新的 'filename' 列，值为当前文件的完整路径
file_df['filename'] = file_path
# 将当前文件的DataFrame与总的DataFrame合并
combined = pd.concat([combined, file_df], ignore_index=True)
print(f"成功处理文件: {file_path}")
except Exception as e:
print(f"处理文件 {file_path} 时发生错误: {e}")
continue # 继续处理下一个文件
# 将合并后的数据保存到新的Excel文件
output_path = "../content/multiplesheet_with_filename.xlsx"
try:
combined.to_excel(output_path, sheet_name='CombinedData', index=False)
print(f"所有文件已合并并保存至: {output_path}")
except Exception as e:
print(f"保存合并文件时发生错误: {e}")
4. 注意事项与最佳实践
- 错误处理： 在实际应用中，文件可能损坏、格式不一致或不存在。使用try-except块可以增强代码的健壮性，防止程序因单个文件的问题而崩溃。
- 内存管理： 对于非常大的Excel文件或大量文件，pd.concat在循环中重复创建新的DataFrame可能会导致内存效率低下。对于海量数据，可以考虑先将所有DataFrame存储在一个列表中，然后一次性调用pd.concat([df1, df2, ...])。
- 列名一致性： 确保所有Excel文件的目标列名一致。如果列名不一致，pd.read_excel可能会报错，或者合并后的DataFrame中会出现额外的列或NaN值。
- 路径处理： file_path会包含完整的路径信息。如果只需要文件名本身（不带目录），可以使用os.path.basename(file_path)来提取。
- 索引重置： pd.concat的ignore_index=True参数非常重要，它会在合并后重置DataFrame的索引，避免出现重复索引或非连续索引的问题。
- 输出格式： to_excel的index=False参数可以避免将DataFrame的索引作为一列写入Excel文件，保持输出的整洁。
5. 总结
通过本教程介绍的方法，您可以高效地合并多个Excel文件，并在合并过程中为每条记录添加其原始文件名。这不仅简化了数据整合流程，更重要的是，它为后续的数据分析和溯源提供了极大的便利。掌握这一技巧，将使您在处理分散数据源时更加得心应手。
