基于多字段（含空值）识别重复记录并标记主记录的实现需求

• 检测规则：基于name、postcode、street、housenumber字段识别重复记录，允许上述字段为空值
• 主记录选择：优先保留nr_rel_a数值最大的记录；若nr_rel_a相同，则选择date_modified最新的记录
• 输出要求：标记需保留的主记录，同时标注各记录是否存在重复、重复数量及关联主记录ID，汇总重复记录的nr_rel_a与nr_rel_b至主记录

输入数据 #

id,name,postcode,street,housenr,nr_rel_a,nr_rel_b,date_modified
1,abc,1000,a,4,10,5,01-01-2020
2,abc,1000,a,4,7,5,01-01-2019
3,abc,,,,5,5,01-01-2009
4,d,2000,d,5,3,5,01-01-2020
5,efg,3000,c,6,22,5,01-01-2020
6,efg,3000,,,2,5,01-01-2009
7,h,4000,d,7,56,5,01-01-2018
8,ijk,5000,e,8,50,5,01-01-2019
9,ijk,5000,e,8,50,5,01-01-2018
10,ijk,5000,e,,5,5,01-01-2009

期望输出数据 #

id,name,postcode,street,housenr,nr_rel_a,nr_rel_b,date_modified,keep,nr_Dup,category,referenceID,Master_nr_rel_a,Master_nr_rel_b
1,abc,1000,a,4,10,5,01-01-2020,FALSE,2,MasterDup,1,22,15
2,abc,1000,a,4,7,5,01-01-2019,TRUE,,Dup,1,,
3,abc,,,,5,5,01-01-2009,TRUE,,Dup,1,,
4,d,2000,d,5,3,5,01-01-2020,FALSE,0,Master,4,4,5
5,efg,3000,c,6,22,5,01-01-2020,FALSE,1,MasterDup,5,24,10
6,efg,3000,,,2,5,01-01-2009,TRUE,,Dup,5,,
7,h,4000,d,7,56,5,01-01-2018,FALSE,0,Master,7,56,5
8,ijk,5000,e,8,50,5,01-01-2019,FALSE,2,MasterDup,8,105,15
9,ijk,5000,e,8,50,5,01-01-2018,TRUE,,Dup,8,,
10,ijk,5000,e,,5,5,01-01-2009,TRUE,,Dup,8,,

• 尝试通过排序后使用df.duplicated(keep='first')标记主记录，但无法处理空值字段的重复检测
• 不清楚如何添加referenceID及汇总nr_rel_a、nr_rel_b的信息
• 请求提供适合初学者的代码示例或实现提示，数据集规模小于20k条

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