Pandas对比Excel数据并添加状态列技巧

还在手动对比Excel数据？本文教你使用Pandas轻松搞定！想要快速比较两个Excel文件的数据差异，并自动添加状态列吗？本文详细介绍了如何利用Python的Pandas库，实现Excel数据的自动化对比和分析。通过本文提供的完整代码示例，你将学会如何读取Excel文件、合并数据、指定比较列，并根据比较结果自动生成"Pass"或"Fail"状态列。更进一步，我们还将介绍如何使用Pandas的样式功能，高亮显示不同的单元格，使数据差异一目了然。告别繁琐的手工操作，提升数据分析效率，就从掌握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 参数。

5. 总结

通过本教程，你学习了如何使用 Pandas 比较两个 Excel 文件的数据，并添加一个基于比较结果的 "Status" 列。你还学习了如何使用 Pandas 的样式功能来高亮显示不同的单元格，以便更直观地查看比较结果。这个方法可以帮助你快速有效地比较大量 Excel 数据，并找出其中的差异。

今天带大家了解了的相关知识，希望对你有所帮助；关于文章的技术知识我们会一点点深入介绍，欢迎大家关注golang学习网公众号，一起学习编程~