使用 Pandas 在 Excel 中添加基于列比较的状态列

使用 Pandas 在 Excel 中添加基于列比较的状态列

本文档旨在指导您如何使用 pandas 库在 excel 文件中添加一个 “Status” 列,该列的值基于对不同 Excel 文件中特定列的比较结果。我们将详细介绍如何读取 Excel 数据,比较指定列,并根据比较结果生成 “Pass” 或 “Fail” 状态,最后将结果写入新的 Excel 文件。同时,还将展示如何使用样式突出显示比较结果,以便更直观地查看数据差异。

1. 准备工作

在开始之前,请确保您已经安装了 Pandas 库。如果没有安装,可以使用以下命令进行安装:

pip install pandas

此外,您需要准备两个 Excel 文件,例如 source.xlsx 和 target.xlsx,它们分别包含需要比较的数据。确保这两个文件包含一个共同的键列,用于合并数据。

2. 代码实现

以下代码展示了如何使用 Pandas 实现上述功能:

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):         """         初始化 ExcelComparator 类。          Args:             src_file_name (str): 源 Excel 文件名。             src_sheet_name (str): 源 Excel 工作表名。             src_pk (str): 源 Excel 主键列名。             src_cols_to_compare (list): 源 Excel 需要比较的列名列表。             tgt_cols_to_compare (list): 目标 Excel 需要比较的列名列表。             tgt_file_name (str): 目标 Excel 文件名。             tgt_sheet_name (str): 目标 Excel 工作表名。             tgt_pk (str): 目标 Excel 主键列名。             target_excel (str): 目标 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):         """         根据比较结果突出显示单元格。          Args:             row (pd.Series): DataFrame 的一行数据。          Returns:             list: 包含单元格样式的列表。         """         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):         """         计算状态列的值,如果所有比较的列都匹配,则为 'Pass',否则为 'Fail'。          Args:             row (pd.Series): DataFrame 的一行数据。          Returns:             str: 'Pass' 或 'Fail'。         """         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):         """         读取 Excel 数据,比较指定列,并添加状态列。         """         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. 代码详解

  1. ExcelComparator 类:

    • __init__ 方法:初始化类的各种参数,包括源文件和目标文件的文件名、工作表名、主键列名以及需要比较的列名。
    • highlight_cells 方法:根据比较结果,为单元格添加背景颜色。绿色表示匹配,黄色表示空值,红色表示不匹配。
    • calculate_status 方法:计算每一行的状态,如果所有比较的列都匹配,则状态为 “Pass”,否则为 “Fail”。
    • read_and_compare 方法:读取源文件和目标文件的数据,使用主键列进行合并,选择需要的列,计算状态列,并使用样式突出显示单元格,最后将结果写入新的 Excel 文件。
  2. 示例用法:

    • 创建 ExcelComparator 类的实例,并传入相应的参数。
    • 调用 read_and_compare 方法,执行比较操作并生成结果文件。

4. 注意事项

  • 确保源文件和目标文件存在,并且包含指定的工作表。
  • 确保主键列在两个文件中都存在,并且数据类型一致。
  • 确保需要比较的列在两个文件中都存在,并且数据类型可以进行比较。
  • 可以根据实际需求修改代码,例如修改比较的列名、修改状态的判断逻辑、修改单元格的样式等。
  • 代码中的 highlight_cells 方法使用了 style.apply 函数,可以根据需要自定义样式。

5. 总结

本文档详细介绍了如何使用 Pandas 库在 Excel 文件中添加基于列比较的状态列。通过定义 ExcelComparator 类,我们可以方便地读取 Excel 数据,比较指定列,并根据比较结果生成 “Pass” 或 “Fail” 状态。此外,我们还展示了如何使用样式突出显示比较结果,以便更直观地查看数据差异。希望本文档能够帮助您更好地使用 Pandas 处理 Excel 数据。

© 版权声明
THE END
喜欢就支持一下吧
点赞12 分享