如何基于条件匹配两个Pandas DataFrame实现球员数据关联？

问题 #

我有两个Pandas DataFrame：dfname（存储球员的不同名称版本）和dfgoals（存储球员及其进球信息）。需要根据以下条件生成结果DataFrame，每个球员对应一行：

• 先检查dfname的name1值是否存在于dfgoals的actual_name列中，若存在则返回首个匹配行；否则检查name2值并返回首个匹配行
• 无论匹配的是name1还是name2，都要返回dfname中的name列值

示例数据 #

import pandas as pd

dfname = pd.DataFrame({
    "name": ["ryan", "bill", "saka", "Henry","Rooney"], 
    "name1": ["ryan 112", "Bill Matt Cdevaca", "Bukayo Saka", "Super Henry","Rooney"], 
    "name2": ["NaN", "XXVaca", "Bukayo", "Thierry","Rooney"]
})

dfgoals = pd.DataFrame({
    "actual_name": ["ryan 112", "XXVaca", "Bukayo", "Thierry", "Ronaldo", "Messi"], 
    "goals": [0, 2, 5, 10, 100, 200], 
    "matches": [22, 100, 200, 300, 100, 90]
})

# 期望输出
answerdf = pd.DataFrame({
    "actual_name": ["ryan 112", "XXVaca", "Bukayo", "Thierry", "Rooney"], 
    "goals": [0, 2, 5, 10, "NaN"], 
    "matches": [22, 100, 200, 300, "NaN"],
    "name_from_dfname": ["ryan", "bill", "saka", "Henry", "Rooney"]
})

Rooney的数值为NaN是因为没有他的进球记录

我尝试了以下代码，但无法正确检查name1和name2的值，例如仅能匹配到ryan的进球数据，其他球员因名称格式问题无法匹配：

df = dfgoals
values_to_check = ['ryan', 'Bill Matt Cdevaca', 'saka', 'henry', 'Rooney']

filtered_rows = []

# Iterate through the DataFrame rows to find matches and concatenate values
for index, row in dfgoals.iterrows():
    matched_values = [value for value in values_to_check if value.lower() in row['actual_name'].lower()]
    if matched_values:
        row['concatenated_values'] = '|'.join(matched_values)
        filtered_rows.append(row)

# Create a new DataFrame from the filtered rows
result_df = pd.DataFrame(filtered_rows)
result_df['concatenated_values'] = pd.Categorical(result_df['concatenated_values'], categories=values_to_check, ordered=True)

# Sort the DataFrame based on the 'concatenated_values' column
result_df.sort_values(by = "concatenated_values")

解决方法 #

可以通过**逐行遍历dfname，优先匹配name1，再匹配name2**的逻辑实现，同时保留dfname中的name字段。具体步骤如下：

1. 将dfgoals转换为字典，以actual_name为键，提升查找效率
2. 遍历dfname的每一行，依次检查name1和name2是否在dfgoals的actual_name中
3. 收集匹配到的行数据，若都不匹配则填充NaN
4. 整理成目标DataFrame

实现代码 #

import pandas as pd

# 预处理：把dfgoals转换成字典，key是actual_name，value是对应的行数据
goals_dict = dfgoals.set_index('actual_name').to_dict('index')

# 初始化结果列表
result_list = []

# 遍历dfname的每一行
for _, row in dfname.iterrows():
    player_name = row['name']
    name1 = row['name1']
    name2 = row['name2']
    
    # 优先匹配name1
    if name1 in goals_dict:
        match_data = goals_dict[name1].copy()
        match_data['actual_name'] = name1
    # 匹配name2（排除name2为"NaN"字符串的情况）
    elif name2 in goals_dict and name2 != 'NaN':
        match_data = goals_dict[name2].copy()
        match_data['actual_name'] = name2
    # 都不匹配的情况，填充NaN
    else:
        match_data = {
            'actual_name': name1 if name1 != 'NaN' else name2,
            'goals': 'NaN',
            'matches': 'NaN'
        }
    
    # 添加name_from_dfname字段
    match_data['name_from_dfname'] = player_name
    result_list.append(match_data)

# 转换为DataFrame
answerdf = pd.DataFrame(result_list)
print(answerdf)

代码说明 #

• 使用字典goals_dict可以避免多次遍历dfgoals，大幅提升查找效率
• 严格遵循name1优先、name2次之的匹配顺序，符合需求
• 处理了name2为"NaN"字符串的特殊情况，避免错误匹配
• 确保每个球员对应一行，即使无匹配数据也会保留该行并填充NaN

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