基于Jaro距离匹配字典地址字符串，添加reference_id字段方案问询

现有包含SiteID与Address的字典列表，需使用jellyfish.jaro_distance算法计算所有地址间的相似度，将相似度高于0.8的记录关联到地址信息最完整（长度最长）的SiteID，为每条记录添加reference_id字段指向该参考ID。当前代码逻辑不完整，寻求实现思路。

原始字典示例 #

address_dict = [
    {'SiteID': 123, 'Address': '350- Maxwell Rd'},
    {'SiteID': 124, 'Address': '350 Maxwell Rd Ste 500'},
    {'SiteID': 125, 'Address': '350 Maxwell Road'},
    {'SiteID': 126, 'Address': '350 Maxwell Road 500'}
]

目标字典示例 #

address_dict = [
    {'SiteID': 123, 'Address': '350- Maxwell Rd', 'reference_id': 124},
    {'SiteID': 124, 'Address': '350 Maxwell Rd Ste 500', 'reference_id': 124},
    {'SiteID': 125, 'Address': '350 Maxwell Road', 'reference_id': 124},
    {'SiteID': 126, 'Address': '350 Maxwell Road 500', 'reference_id': 124}
]

当前未完成代码 #

counter = 0
for item in address_dict:
    
    ## Can't figure out how to loop over the record one with two, three and four
    similarity = jellyfish.jaro_distance(item['Address'], address_dict[])
    
    ## Get the record with the greater length
    
    
    ## Find the similarity and maps to the reference ID
    if similarity > 0.8:
        address_dict[counter]['reference_id'] = item['SiteID']
    
    counter+=1

实现思路 #

1. 预存地址长度：给每条记录添加地址长度字段，避免重复计算，方便后续筛选最完整地址。
2. 构建相似分组：通过双重循环遍历所有记录组合，计算Jaro相似度，把相似度高于0.8的记录归为同一组，用已访问标记避免重复处理。
3. 确定组内参考ID：在每个相似组中，筛选出地址长度最长的记录（长度相同时取SiteID最小的），将其SiteID设为组内所有记录的reference_id。

完整代码实现 #

import jellyfish

address_dict = [
    {'SiteID': 123, 'Address': '350- Maxwell Rd'},
    {'SiteID': 124, 'Address': '350 Maxwell Rd Ste 500'},
    {'SiteID': 125, 'Address': '350 Maxwell Road'},
    {'SiteID': 126, 'Address': '350 Maxwell Road 500'}
]

# 预存每条记录的地址长度
for item in address_dict:
    item['address_length'] = len(item['Address'])

# 构建相似记录分组
groups = []
visited = set()

for i in range(len(address_dict)):
    if i in visited:
        continue
    current_group = [i]
    visited.add(i)
    current_addr = address_dict[i]['Address']
    
    for j in range(i + 1, len(address_dict)):
        if j in visited:
            continue
        compare_addr = address_dict[j]['Address']
        sim_score = jellyfish.jaro_distance(current_addr, compare_addr)
        if sim_score > 0.8:
            current_group.append(j)
            visited.add(j)
    groups.append(current_group)

# 为每个分组设置reference_id
for group in groups:
    # 筛选组内地址最长的记录，长度相同则取SiteID最小的
    reference_item = max(
        [address_dict[idx] for idx in group],
        key=lambda x: (x['address_length'], -x['SiteID'])
    )
    reference_id = reference_item['SiteID']
    
    # 给组内所有记录添加reference_id，并移除临时长度字段
    for idx in group:
        address_dict[idx]['reference_id'] = reference_id
        del address_dict[idx]['address_length']

# 输出结果
for item in address_dict:
    print(item)

代码说明 #

• 预存长度：提前计算地址长度，减少重复计算开销。
• 分组逻辑：通过已访问集合确保每条记录只被分到一个组，避免重复处理。
• 参考ID筛选：用自定义排序键优先按地址长度降序，再按SiteID升序，保证选到最完整的参考记录。

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