如何在DataFrame中匹配Source/Target行对并添加第三列Result

我有一个包含Source（源）和Target（目标）行对的DataFrame，需要判断每组行对是否匹配，添加Result列作为第三列：Source行显示Pass（匹配）或Fail（不匹配），Target行留空。

输入示例 #

观测值 | 数据集 | Col1 | Col2 | Col3
----------------------------------
1   | Source  | A    | 10   | X
2   | Target  | A    | 10   | X
3   | Source  | B    | 20   | Y
4   | Target  | B    | 20   | Y
5   | Source  | C    | 30   | Z
6   | Target  | D    | 30   | Z

预期输出 #

观测值 | 数据集 | Result | Col1 | Col2 | Col3
--------------------------------------------
1   | Source  | Pass   | A    | 10   | X
2   | Target  |        | A    | 10   | X
3   | Source  | Pass   | B    | 20   | Y
4   | Target  |        | B    | 20   | Y
5   | Source  | Fail   | C    | 30   | Z
6   | Target  |        | D    | 30   | Z

现有如下Python代码，请问如何调整以实现需求？

import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import PatternFill

class ExcelHighlighter:
    def __init__(self, file_path, sheet_name):
        self.file_path = file_path
        self.sheet_name = sheet_name
        self.light_green_fill = PatternFill(start_color='00FF00', end_color='00FF00', fill_type='solid')
        self.light_coral_fill = PatternFill(start_color='FF8080', end_color='FF8080', fill_type='solid')

    def highlight_and_save(self, output_path='output.xlsx'):
        df = pd.read_excel(self.file_path, sheet_name=self.sheet_name)

        with pd.ExcelWriter(output_path, engine='openpyxl') as writer:
            df.to_excel(writer, index=False, sheet_name=self.sheet_name)
            workbook = writer.book
            sheet = writer.sheets[self.sheet_name]

            for row in range(2, df.shape[0] + 1, 2):
                for col in range(3, df.shape[1]):
                    cell_value_source = df.iloc[row - 2, col]
                    cell_value_target = df.iloc[row - 1, col]

                    if cell_value_source != cell_value_target:
                        sheet.cell(row=row - 1, column=col + 1).fill = self.light_coral_fill
                        sheet.cell(row=row, column=col + 1).fill = self.light_coral_fill

            df['Result'] = ''

            for row in range(2, df.shape[0] + 1, 2):
                for col in range(3, df.shape[1]):
                    cell_value_source = df.iloc[row - 2, col]
                    cell_value_target = df.iloc[row - 1, col]

                    if cell_value_source == cell_value_target:
                        # Set 'Pass' in the 'Result' column for both Source and Target
                        df.at[row - 1, 'Result'] = 'Pass'
                        df.at[row, 'Result'] = 'Pass'

                    elif cell_value_source != cell_value_target:
                        # Set 'Fail' in the 'Result' column for both Source and Target
                        df.at[row - 1, 'Result'] = 'Fail'
                        df.at[row, 'Result'] = 'Fail'

            df = pd.concat([df.iloc[:, :3], df['Result'], df.iloc[:, 3:-1]], axis=1)

            workbook.save(output_path)

原代码存在Result列赋值不符合需求、匹配逻辑易覆盖标记、先写Excel再改DataFrame导致数据不一致等问题，以下是修改后的完整代码：

import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import PatternFill

class ExcelHighlighter:
    def __init__(self, file_path, sheet_name):
        self.file_path = file_path
        self.sheet_name = sheet_name
        self.light_green_fill = PatternFill(start_color='00FF00', end_color='00FF00', fill_type='solid')
        self.light_coral_fill = PatternFill(start_color='FF8080', end_color='FF8080', fill_type='solid')

    def highlight_and_save(self, output_path='output.xlsx'):
        df = pd.read_excel(self.file_path, sheet_name=self.sheet_name)
        # 初始化Result列为空
        df['Result'] = ''
        
        # 遍历每一组Source-Target行对
        for i in range(0, df.shape[0], 2):
            # 获取当前组的源行和目标行
            source_row = df.iloc[i]
            target_row = df.iloc[i+1]
            
            # 比较除观测值、数据集外的所有列是否完全匹配
            compare_cols = df.columns[2:]
            is_match = all(source_row[compare_cols] == target_row[compare_cols])
            
            # 仅给Source行设置Result值
            df.at[i, 'Result'] = 'Pass' if is_match else 'Fail'
        
        # 调整列顺序：将Result列放到第三列位置
        cols = df.columns.tolist()
        cols.insert(2, cols.pop(cols.index('Result')))
        df = df[cols]
        
        # 写入Excel并设置高亮
        with pd.ExcelWriter(output_path, engine='openpyxl') as writer:
            df.to_excel(writer, index=False, sheet_name=self.sheet_name)
            workbook = writer.book
            sheet = writer.sheets[self.sheet_name]
            
            # 标记不匹配的单元格
            for i in range(0, df.shape[0], 2):
                source_excel_row = i + 2  # Excel行号从1开始，表头占1行
                target_excel_row = i + 3
                
                compare_cols = df.columns[3:]
                for col_idx, col_name in enumerate(compare_cols):
                    excel_col = col_idx + 4  # Result是第三列，比较列从第4列开始
                    
                    source_val = df.iloc[i][col_name]
                    target_val = df.iloc[i+1][col_name]
                    
                    if source_val != target_val:
                        sheet.cell(row=source_excel_row, column=excel_col).fill = self.light_coral_fill
                        sheet.cell(row=target_excel_row, column=excel_col).fill = self.light_coral_fill

关键修改点说明 #

1. 调整处理顺序：先修改DataFrame添加Result列，再写入Excel，确保保存的是更新后的数据。
2. 优化匹配逻辑：判断一组行对的所有目标列是否完全匹配，避免逐列覆盖Result值的问题。
3. 修正Result赋值规则：仅给Source行设置Pass/Fail，Target行保持为空。
4. 简化列顺序调整：通过列表操作直接将Result列插入到指定位置，替代原代码中复杂的concat写法。
5. 修正高亮坐标：调整Excel行号和列号的对应关系，确保高亮的是正确的不匹配单元格。

内容的提问来源于stack exchange，提问作者Sweta Rawani