基于双条件匹配DataFrame值：车队赛事骑手积分计算需求

需求说明 #

• 以df2的TEAM、RACE为匹配维度，关联df1中对应RACE下骑手的POINTS积分值
• 为df2的PLAYER1、PLAYER2字段分别匹配对应积分，无匹配项时自动填充0
• 不得修改原始DataFrame，需新建df3存储最终结果
• 优先使用原生Pandas操作，不建议用.map()或lambda方法

示例数据 #

import pandas as pd

# 构造积分数据df1
players = ['X_ER', 'Y_ER', 'Z_ER', 'W_ER', 'X_ER', 'Y_ER']
races = ['RACE_1','RACE_1','RACE_1','RACE_1', 'RACE_2', 'RACE_2']
points = [100, 50, 20, 10, 50, 100]

df1 = pd.DataFrame({'PLAYER':players,
                    'RACE':races,
                    'POINTS':points})

# 构造车队参赛数据df2
teams = ['AAA', 'BBB', 'CCC', 'DDD', 'AAA', 'BBB']
races = ['RACE_1','RACE_1','RACE_1','RACE_1', 'RACE_2', 'RACE_2']
player1 = ['X_ER', 'W_ER', 'Y_ER', 'W_ER', 'X_ER', 'Y_ER']
player2 = ['Z_ER', 'Y_ER', 'D_ER', 'K_ER', 'Y_ER', 'X_ER']

df2 = pd.DataFrame({'TEAM':teams,
                    'RACE':races,
                    'PLAYER1':player1,
                    'PLAYER2':player2})

原DataFrame输出 #

df1输出： #

PLAYER    RACE  POINTS
0    X_ER  RACE_1     100
1    Y_ER  RACE_1      50
2    Z_ER  RACE_1      20
3    W_ER  RACE_1      10
4    X_ER  RACE_2      50
5    Y_ER  RACE_2     100

df2输出： #

TEAM    RACE PLAYER1 PLAYER2
0  AAA  RACE_1    X_ER    Z_ER
1  BBB  RACE_1    W_ER    Y_ER
2  CCC  RACE_1    Y_ER    D_ER
3  DDD  RACE_1    W_ER    K_ER
4  AAA  RACE_2    X_ER    Y_ER
5  BBB  RACE_2    Y_ER    X_ER

预期结果 #

df3构造代码： #

teams = ['AAA', 'BBB', 'CCC', 'DDD', 'AAA', 'BBB']
races = ['RACE_1','RACE_1','RACE_1','RACE_1', 'RACE_2', 'RACE_2']
result1 = [100, 10, 50, 10, 50, 100]
result2 = [20, 50, 0, 0, 100, 50]

df3 = pd.DataFrame({'TEAM':teams,
                    'RACE':races,
                    'RESULT1':result1,
                    'RESULT2':result2})

df3输出： #

TEAM    RACE  RESULT1  RESULT2
0  AAA  RACE_1      100       20
1  BBB  RACE_1       10       50
2  CCC  RACE_1       50        0
3  DDD  RACE_1       10        0
4  AAA  RACE_2       50      100
5  BBB  RACE_2      100       50

实现代码（无lambda/Map版本） #

通过两次merge操作完成匹配，全程使用Pandas原生方法，避免冗余代码：

# 第一步：匹配PLAYER1的积分
df_temp = df2.merge(
    df1,
    left_on=['RACE', 'PLAYER1'],
    right_on=['RACE', 'PLAYER'],
    how='left'
).rename(columns={'POINTS': 'RESULT1'})

# 第二步：匹配PLAYER2的积分
df3 = df_temp.merge(
    df1,
    left_on=['RACE', 'PLAYER2'],
    right_on=['RACE', 'PLAYER'],
    how='left'
).rename(columns={'POINTS': 'RESULT2'})

# 保留目标列，空值填充为0
df3 = df3[['TEAM', 'RACE', 'RESULT1', 'RESULT2']].fillna(0)

# 可选：将积分转为整数类型（如果需要）
df3[['RESULT1', 'RESULT2']] = df3[['RESULT1', 'RESULT2']].astype(int)

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