Setup -
df1
Team 1 Team 2 Team1_Score Team2_Score
Index
0 A B 25 56
1 B C 30 55
2 D E 35 75
df2
Team 1 Team 2 Team1_Avg Team2_Avg
Index
0 A B 5 15
1 F G 25 10
2 B C 35 15
首先,我们需要对Team *
列,并相应地排序Team*_Score
列以同样的方式。我们将使用argsort
这样做。
i = np.arange(len(df1))[:, None]
j = np.argsort(df1[['Team 1', 'Team 2']], axis=1).values
df1[['Team 1', 'Team 2']] = df1[['Team 1', 'Team 2']].values[i, j]
df1[['Team1_Score', 'Team2_Score']] = df1[['Team1_Score', 'Team2_Score']].values[i, j]
现在,重复相同的过程df2
, with Team *
and Team*_Avg
.
j = np.argsort(df2[['Team 1', 'Team 2']], axis=1).values
df2[['Team 1', 'Team 2']] = df2[['Team 1', 'Team 2']].values[i, j]
df2[['Team1_Avg', 'Team2_Avg']] = df2[['Team1_Avg', 'Team2_Avg']].values[i, j]
现在,执行左外merge
-
df1.merge(df2, on=['Team 1', 'Team 2'], how='left')
Team 1 Team 2 Team1_Score Team2_Score Team1_Avg Team2_Avg
0 A B 25 56 5 15
1 B C 30 55 35 15
2 D E 35 75