使用 Pandas 比较 Excel 数据并添加状态列-Python教程-PHP中文网

使用 Pandas 比较 Excel 数据并添加状态列

霞舞

发布： 2025-08-20 16:04:01

原创

186人浏览过

使用 pandas 比较 excel 数据并添加状态列

本文档旨在指导你如何使用 Pandas 比较两个 Excel 文件中的数据，并基于比较结果添加一个 "Status" 列。我们将通过一个完整的 Python 脚本示例，演示如何读取 Excel 文件、合并数据、比较指定列，并根据比较结果生成 "Pass" 或 "Fail" 状态。此外，还会介绍如何使用 Pandas 的样式功能来高亮显示不同的单元格，以便更直观地查看比较结果。

Pandas Excel 数据比较及状态列添加教程

本教程将详细介绍如何使用 Pandas 库比较两个 Excel 文件的数据，并添加一个基于比较结果的 "Status" 列。我们将通过一个 Python 类来封装整个流程，使其更具可重用性和可维护性。

1. 环境准备

首先，确保你已经安装了 Pandas 库。如果没有安装，可以使用 pip 进行安装：

pip install pandas openpyxl

登录后复制

注意，这里同时安装了 openpyxl，因为它是 Pandas 用于读写 Excel 文件的常用引擎。

2. 代码实现

下面是完整的 Python 代码示例：

序列猴子开放平台

具有长序列、多模态、单模型、大数据等特点的超大规模语言模型

查看详情

import pandas as pd

class ExcelComparator:
    def __init__(self, src_file_name, src_sheet_name, src_pk, src_cols_to_compare, tgt_cols_to_compare, tgt_file_name,
                 tgt_sheet_name, tgt_pk, target_excel):
        self.src_file_name = src_file_name
        self.src_sheet_name = src_sheet_name
        self.src_pk = src_pk
        self.src_cols_to_compare = src_cols_to_compare
        self.tgt_cols_to_compare = tgt_cols_to_compare
        self.tgt_file_name = tgt_file_name
        self.tgt_sheet_name = tgt_sheet_name
        self.tgt_pk = tgt_pk
        self.target_excel = target_excel

    def highlight_cells(self, row):
        styles = [''] * len(row)
        pk_cols = [col for col in row.index if col in [self.src_pk, self.tgt_pk]]

        for i, col in enumerate(row.index):
            if i % 2 == 0 and col not in pk_cols:
                src_col = col
                tgt_col = row.index[i - 1]  # Adjusted to get the previous column

                if row[src_col] == row[tgt_col]:
                    styles[i], styles[i - 1] = 'background-color:lightgreen', 'background-color:lightgreen'
                elif pd.isnull(row[src_col]) or pd.isnull(row[tgt_col]):
                    styles[i], styles[i - 1] = 'background-color:yellow', 'background-color:yellow'
                else:
                    styles[i], styles[i - 1] = 'background-color:lightcoral', 'background-color:lightcoral'

        return styles

    def calculate_status(self, row):
        for i in range(len(row.index) - 1, 0, -2):
            src_col = row.index[i]
            tgt_col = row.index[i - 1]

            if row[src_col] != row[tgt_col]:
                return 'Fail'

        return 'Pass'

    def read_and_compare(self):
        src_df = pd.read_excel(self.src_file_name, sheet_name=self.src_sheet_name)
        tgt_df = pd.read_excel(self.tgt_file_name, sheet_name=self.tgt_sheet_name)

        result = src_df.merge(tgt_df, how='inner', left_on=self.src_pk, right_on=self.tgt_pk)
        result_columns = [self.src_pk] + [col for pair in zip(self.src_cols_to_compare, self.tgt_cols_to_compare) for col
                                         in pair]

        result = result[result_columns]
        result['Status'] = result.apply(self.calculate_status, axis=1)

        result.style.apply(self.highlight_cells, axis=1).to_excel(self.target_excel)


# Example usage
comparator = ExcelComparator(
    src_file_name='source.xlsx',
    src_sheet_name='Sheet1',
    src_pk='ID',
    src_cols_to_compare=['Name', 'Salary'],
    tgt_cols_to_compare=['FirstName', 'Sal'],
    tgt_file_name='target.xlsx',
    tgt_sheet_name='Sheet1',
    tgt_pk='EMP_ID',
    target_excel='result.xlsx'
)

comparator.read_and_compare()

登录后复制

3. 代码详解

3.1 ExcelComparator 类

该类封装了 Excel 文件比较的所有逻辑。

__init__ 方法: 初始化类的各种参数，包括源文件、目标文件、主键列、需要比较的列等。
highlight_cells 方法: 用于高亮显示比较结果。如果源列和目标列的值相等，则使用浅绿色；如果值为空，则使用黄色；否则，使用浅珊瑚色。注意，这里调整了索引，使得颜色可以正确对应。
calculate_status 方法: 用于计算每一行的 "Status"。如果任何一对比较列的值不相等，则状态为 "Fail"；否则，状态为 "Pass"。
read_and_compare 方法: 读取源 Excel 文件和目标 Excel 文件，使用主键进行内连接，选择需要比较的列，计算 "Status" 列，并使用 highlight_cells 方法进行高亮显示，最后将结果写入目标 Excel 文件。

3.2 使用示例

在代码的最后，我们创建了一个 ExcelComparator 类的实例，并调用了 read_and_compare 方法来执行比较操作。你需要根据你的实际情况修改以下参数：

src_file_name: 源 Excel 文件名。
src_sheet_name: 源 Excel 文件中的 sheet 名。
src_pk: 源 Excel 文件的主键列名。
src_cols_to_compare: 源 Excel 文件中需要比较的列名列表。
tgt_cols_to_compare: 目标 Excel 文件中需要比较的列名列表。
tgt_file_name: 目标 Excel 文件名。
tgt_sheet_name: 目标 Excel 文件中的 sheet 名。
tgt_pk: 目标 Excel 文件的主键列名。
target_excel: 结果 Excel 文件名。

4. 注意事项

确保源 Excel 文件和目标 Excel 文件都存在，并且指定的 sheet 名和列名都是正确的。
主键列的值必须是唯一的，否则可能会导致连接结果不正确。
如果需要比较的列的数据类型不一致，可能会导致比较结果不正确。建议在比较之前将数据类型转换为一致的类型。
本示例使用了内连接，这意味着只有在源 Excel 文件和目标 Excel 文件中都存在的主键值才会被包含在结果中。如果需要使用其他类型的连接，可以修改 merge 方法的 how 参数。