Python中两个DataFrame对比：重复行标记功能失效问题排查

给定两组数据：

data1 = {'View name': ['v1', 'v3'],
         'view desc': ['abc', 'fgt'],
         'Reason for Diff': ['Duplicate row', 'View not found']}

data2 = {'View name': ['v1', 'v1', 'v2'],
         'view Desc': ['abc', 'xyz', 'bnm']}

需求：对比基于data1生成的df1和基于data2生成的df2，标记规则如下：

• 若某条记录的View name在另一数据中不存在，标记为View not found
• 对于View name重复的记录，除了该View name的最后一条记录外，其余均标记为Duplicate row

当前代码仅能识别View not found，无法正确标记重复行，现有代码如下：

def compare_columns_vd(row):
    if row['View Name'] not in selected_cols_df1['View Name'].astype(str).values:
        return 'View Not Found'   
    return 'NA'

view_desc_diff_df['Reason for Diff'] = view_desc_diff_df.apply(compare_columns_vd, axis=1)

if 'NA' in view_desc_diff_df['Reason for Diff'].values:
    found_values = set(view_desc_df['View Description'])

    status_list = []

    for idx, row in vd_df_ip.iterrows():
        value = row['View Description']
        status = 'NA' if value in found_values else 'Duplicate Row'
        status_list.append(status)

    view_desc_diff_df['Reason for Diff'] = status_list
    
    #print(view_desc_df['status'])
    if len(status_list) == len(view_desc_df):
        view_desc_df['status'] = status_list
    else:
        print("Length of status_list does not match the length of view_desc_df")

    #view_desc_diff_df['Reason for Diff'] = view_desc_df['status']
    view_desc_diff_df.loc[view_desc_diff_df['Reason for Diff'] == 'NA', 'Reason for Diff'] = view_desc_df['status']

现有代码的核心问题在于判断重复行的逻辑错误：它通过检查View Description是否在集合中来判断，这和需求中基于View name重复、保留最后一条的规则不符。

修正思路：

1. 先处理View not found的标记
2. 针对View name重复的记录，标记除最后一条外的所有重复项为Duplicate row
3. 合并两个标记结果

修正后的代码示例（假设对比的是df2相对于df1的差异，可根据实际调整对比方向）：

import pandas as pd

# 先将数据转为DataFrame，统一列名避免大小写/命名不一致问题
df1 = pd.DataFrame(data1).rename(columns={'view desc': 'View Description', 'View name': 'View Name'})
df2 = pd.DataFrame(data2).rename(columns={'view Desc': 'View Description', 'View name': 'View Name'})

# 初始化差异结果列
df2['Reason for Diff'] = ''

# 标记View not found：df2中View Name不在df1中的记录
df2.loc[~df2['View Name'].isin(df1['View Name']), 'Reason for Diff'] = 'View not found'

# 标记Duplicate row：同一View Name中，除最后一条外的所有记录
# 按View Name分组，给每组内的行倒序编号，编号>1的即为非最后一条的重复项
df2['row_num'] = df2.groupby('View Name').cumcount(ascending=False) + 1
df2.loc[(df2['row_num'] > 1) & (df2['Reason for Diff'] == ''), 'Reason for Diff'] = 'Duplicate row'

# 剩余未标记的（存在且是该组最后一条）设为NA
df2['Reason for Diff'] = df2['Reason for Diff'].replace('', 'NA')

# 删除辅助列row_num
df2 = df2.drop('row_num', axis=1)

print(df2)

代码说明 #

• 统一列名避免大小写或命名不一致导致的匹配错误
• 用isin方法高效判断View Name是否存在，替代循环提升性能
• 利用groupby.cumcount(ascending=False)给每组行倒序编号，编号>1的就是非最后一条的重复项，直接标记
• 逻辑分层清晰，完全贴合需求规则

运行后df2的输出结果：

View Name View Description   Reason for Diff
0        v1              abc  Duplicate row
1        v1              xyz               NA
2        v2              bnm  View not found

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