基于循环迭代的Python字符串匹配及DataFrame分数计算实现问询

现有两个DataFrame：df1包含Place和Location列，df2包含Target1、Target2、Strength列。需要实现以下逻辑：

• 对df1的每一行，将Place的每个元素与Location的元素进行匹配
  • 直接匹配（Place元素在Location列表中）得1分
  • 未直接匹配时，在df2中以该Place元素为Target1，查找对应Location元素作为Target2的最高Strength值作为得分；无匹配项得0分
• 最终平均分为所有Place元素得分之和除以Place元素的数量

示例数据 #

df1 #

Place                       Location
Delhi,Punjab,Jaipur         Delhi,Punjab,Noida,Lucknow
Delhi,Punjab,Jaipur         Delhi,Bhopal,Jaipur,Rajkot  
Delhi,Punjab,Kerala         Delhi,Jaipur,Madras

df2 #

Target1   Target2    Strength
Jaipur    Rajkot     0.94
Jaipur    Punjab     0.84
Jaipur    Noida      0.62 
Jaipur    Jodhpur    0.59
Punjab    Amritsar   0.97
Punjab    Delhi      0.85
Punjab    Bhopal     0.91
Punjab    Jodhpur    0.75
Kerala    Varkala    0.85
Kerala    Kochi      0.88

预期输出 #

Place                              Location                   Avg. Score
Delhi,Punjab,Jaipur         Delhi,Punjab,Noida,Lucknow       (1+1+0.84)/3 = 0.95
Delhi,Punjab,Jaipur         Delhi,Bhopal,Jaipur,Rajkot       (1+0.91+1)/3 = 0.97 
Delhi,Punjab,Kerala         Delhi,Jaipur,Madras              (1+0.85+0)/3 = 0.67

用户代码尝试 #

data1 = df1['Place'].to_list()
data2 = df1['Location'].to_list()

dict3 = {}
exac_match = []
for el in data1:
    #print(el)
    el=[x.strip() for x in el.split(',')]
   
    for ell in data2:
        ell=[x.strip() for x in ell.split(',')]
        dict1 = {}
        dict2 = {}
        for elll in el:
            if elll in ell:
                #print("Exact match:::", elll)
                dict1[elll]=1
                dict2[elll]=elll

完整实现代码 #

import pandas as pd

# 构建示例数据（若已有df1、df2可跳过此部分）
data_df1 = {
    'Place': ['Delhi,Punjab,Jaipur', 'Delhi,Punjab,Jaipur', 'Delhi,Punjab,Kerala'],
    'Location': ['Delhi,Punjab,Noida,Lucknow', 'Delhi,Bhopal,Jaipur,Rajkot', 'Delhi,Jaipur,Madras']
}
df1 = pd.DataFrame(data_df1)

data_df2 = {
    'Target1': ['Jaipur', 'Jaipur', 'Jaipur', 'Jaipur', 'Punjab', 'Punjab', 'Punjab', 'Punjab', 'Kerala', 'Kerala'],
    'Target2': ['Rajkot', 'Punjab', 'Noida', 'Jodhpur', 'Amritsar', 'Delhi', 'Bhopal', 'Jodhpur', 'Varkala', 'Kochi'],
    'Strength': [0.94, 0.84, 0.62, 0.59, 0.97, 0.85, 0.91, 0.75, 0.85, 0.88]
}
df2 = pd.DataFrame(data_df2)

# 预处理df2：构建Target1到(Target2: 最高Strength)的映射表
strength_map = df2.groupby(['Target1', 'Target2'])['Strength'].max().unstack(fill_value=0)

# 定义单行得分计算函数
def calculate_avg_score(row):
    places = [p.strip() for p in row['Place'].split(',')]
    locations = [l.strip() for l in row['Location'].split(',')]
    total_score = 0.0
    place_count = len(places)
    
    for place in places:
        # 直接匹配得1分
        if place in locations:
            total_score += 1.0
            score_item = '1'
        else:
            # 查找对应最高Strength，无匹配则0分
            if place in strength_map.index:
                max_strength = strength_map.loc[place, locations].max()
                total_score += max_strength
                score_item = str(round(max_strength, 2))
            else:
                score_item = '0'
    
    # 生成和预期一致的计算字符串
    score_calc = f"({'+'.join([str(1) if p in locations else (str(round(strength_map.loc[p, locations].max(),2)) if p in strength_map.index else '0') for p in places])})/{place_count} = {round(total_score/place_count, 2)}"
    return score_calc

# 应用函数到df1每行
df1['Avg. Score'] = df1.apply(calculate_avg_score, axis=1)

# 输出结果
print(df1)

代码说明 #

1. 预处理df2：通过分组取最大值，构建一个以Target1为索引、Target2为列的映射表，避免重复查询，提升效率。
2. 行处理逻辑：拆分每行的Place和Location列表，逐个判断每个Place元素的得分：
   • 直接匹配则加1分
   • 未匹配时从映射表中提取对应Location元素的最高Strength，无匹配项加0分
3. 结果格式化：生成和预期输出一致的计算过程字符串，同时保留两位小数的平均分。

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