基于主键对比相邻列并实现高亮状态判定的Pandas方案求助

以下是满足需求的完整实现代码，包含外连接、状态判断和单元格高亮逻辑：

import pandas as pd
import numpy as np

# 初始化原始数据
exam_1 = {
    'ID1': ['1', '2', '3', '3', '5', '5'],
    'ID2': ['1', '1', '4', '5', '1', '2'],
    'Mat': [80, 75, 50, 93, 88, 90],
    'Science': [96, 97, 99, 87, 90, 88],
}
exam_2 = {
    'ID1': ['1', '2', '3', '3', '5'],
    'ID2': ['1', '1', '4', '5', '1'],
    'Mat': [80, np.nan, 50, 93, 88],
    'Science': [50, 60, 85, 90, 66],
}

df_1 = pd.DataFrame(exam_1)
df_2 = pd.DataFrame(exam_2)

# 1. 外连接并整理列顺序（确保同名列相邻）
cmp = pd.merge(df_1, df_2, how="outer", on=['ID1', 'ID2'], suffixes=("_1", "_2"))
cmp = cmp.set_index(['ID1', 'ID2']).sort_index(axis=1).reset_index()

# 2. 计算Status：所有成对列均匹配且无空值则Pass，否则Fail
subjects = list(set(col.split('_')[0] for col in cmp.columns if '_' in col))

def check_pass(row):
    for subj in subjects:
        col1, col2 = f"{subj}_1", f"{subj}_2"
        # 空值或值不相等都判定为不匹配
        if pd.isna(row[col1]) or pd.isna(row[col2]) or row[col1] != row[col2]:
            return 'Fail'
    return 'Pass'

cmp['Status'] = cmp.apply(check_pass, axis=1)

# 3. 实现单元格高亮逻辑
def highlight_cells(df):
    style = pd.DataFrame('', index=df.index, columns=df.columns)
    # 预存主键集合，提升判断效率
    df1_keys = set(zip(df_1['ID1'], df_1['ID2']))
    df2_keys = set(zip(df_2['ID1'], df_2['ID2']))
    
    for idx, row in df.iterrows():
        current_key = (row['ID1'], row['ID2'])
        # 场景1：df_1主键存在于df_2中
        if current_key in df2_keys:
            for subj in subjects:
                col1, col2 = f"{subj}_1", f"{subj}_2"
                val1, val2 = row[col1], row[col2]
                # 场景1c：任一单元格为空
                if pd.isna(val1) or pd.isna(val2):
                    style.loc[idx, [col1, col2]] = 'background-color: #ffff99'
                # 场景1a：值匹配
                elif val1 == val2:
                    style.loc[idx, [col1, col2]] = 'background-color: #90ee90'
                # 场景1b：值不匹配
                else:
                    style.loc[idx, [col1, col2]] = 'background-color: #ffcccb'
        # 场景2：df_1主键不存在于df_2中
        elif current_key in df1_keys:
            non_key_cols = [col for col in df.columns if col not in ['ID1', 'ID2']]
            style.loc[idx, non_key_cols] = 'background-color: #ffff99'
    return style

# 应用样式并展示结果
styled_df = cmp.style.apply(highlight_cells, axis=None)
display(styled_df)

关键逻辑说明 #

状态计算（Status列） #

• 提取所有需要对比的科目前缀（如Mat、Science），确保支持扩展更多科目。
• 逐行检查每对列：只要存在空值或值不相等，直接标记为Fail；所有列对都匹配且无空值，标记为Pass。

单元格高亮 #

• 预生成主键集合，避免重复遍历DataFrame提升效率。
• 场景1：针对df_1主键在df_2中存在的行，按列对的匹配状态分别标记绿/红/黄。
• 场景2：针对df_1独有主键的行，除主键外整行标记黄色。

内容的提问来源于stack exchange，提问作者Priyanka Kumari