如何用Python对比两个嵌套DataFrame并生成差异原因列？

现有两个以字典形式定义的DataFrame：输入表data与验证报告表master，需要在data中添加**'Reason for diff'**列，列值需按照以下逻辑生成：

• 同一Viewname+Column Name组合中，除最后一条外，其余标记为Duplicate Row
• 若视图和列在两张表中均存在，标记为Matched
• 优先判断视图：若视图在master中不存在，标记为View Not Found；若视图存在但列不存在，标记为Col not found

输入示例 #

data = {'Viewname': ['V1', 'V1', 'V2', 'V2', 'V3'],
        'Column Name': ['c1', 'c1', 'C7', 'C4', 'C5'],
        'Col Desc': ['abc', 'def', 'dfg', 'tyh', 'gth']}

master = {'Viewname2': ['V1', 'V1', 'V2', 'V2'],
          'Column Name2': ['c1', 'c1', 'C2', 'C4'],
          'Col Desc2': ['abc', 'def', 'dfg', 'tyh']}

预期输出 #

data = {'Viewname': ['V1', 'V1', 'V2', 'V2', 'V3'],
        'Column Name': ['c1', 'c1', 'C7', 'C4', 'C5'],
        'Col Desc': ['abc', 'def', 'dfg', 'tyh', 'gth'],
        'Reason for diff': ['Duplicate Row', 'Matched', 'Col not found', 'Matched', 'View Not Found']}

master = {'Viewname2': ['V1', 'V1', 'V2', 'V2'],
          'Column Name2': ['c1', 'c1', 'C2', 'C4'],
          'Col Desc2': ['abc', 'def', 'dfg', 'tyh']}

解决方案 #

借助pandas库可实现需求，代码如下：

import pandas as pd

# 将字典转换为DataFrame
df_data = pd.DataFrame(data)
df_master = pd.DataFrame(master)

# 标记同一Viewname+Column Name组合的非最后一行
df_data['is_duplicate'] = df_data.duplicated(subset=['Viewname', 'Column Name'], keep='last')

# 提取master中存在的视图集合、(视图,列)组合集合
master_views = set(df_master['Viewname2'].unique())
master_col_pairs = set(df_master[['Viewname2', 'Column Name2']].itertuples(index=False, name=None))

# 按规则生成Reason for diff列
def get_diff_reason(row):
    if row['is_duplicate']:
        return 'Duplicate Row'
    if row['Viewname'] not in master_views:
        return 'View Not Found'
    if (row['Viewname'], row['Column Name']) in master_col_pairs:
        return 'Matched'
    return 'Col not found'

df_data['Reason for diff'] = df_data.apply(get_diff_reason, axis=1)

# 转换回字典格式（按需选择）
data = df_data.to_dict('list')

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