Pandas合并Excel文件并保留来源信息

还在为合并多个Excel文件而烦恼吗？本文将详细介绍如何利用Python的Pandas库，高效合并多个Excel文件，并巧妙地保留数据来源信息。通过为每条记录动态添加包含原始文件名的列，实现数据的可追溯性，提升数据整合的效率和可管理性。适用于需要整合分散数据源的场景，例如销售记录、项目进度或库存信息等。本文提供详细的代码示例和步骤说明，助您轻松解决数据整合难题，为后续的数据分析和审计工作提供便利。掌握这一技巧，让您在处理分散数据源时更加得心应手，告别繁琐的手动合并，提升工作效率！

在数据分析和处理的日常工作中，我们经常需要整合来自多个文件的数据。当这些数据分散在多个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文件，并在合并过程中为每条记录添加其原始文件名。这不仅简化了数据整合流程，更重要的是，它为后续的数据分析和溯源提供了极大的便利。掌握这一技巧，将使您在处理分散数据源时更加得心应手。

好了，本文到此结束，带大家了解了《Pandas合并Excel文件并保留来源信息》，希望本文对你有所帮助！关注golang学习网公众号，给大家分享更多文章知识！