外接Pandas数据帧

Question

我正在尝试外部连接（在df1上）两个熊猫数据帧。以下是示例数据帧：

df1:
Index   Team 1   Team 2   Team1_Score    Team2_Score
 0       A        B        25              56
 1       B        C        30              55
 2       D        E        35              75

df2:
Index   Team 1   Team 2   Team1_Avg     Team2_Avg
 0       A        B        5              15
 1       G        F        10             25
 2       C        B        15             35

dfcombined
Index   Team 1   Team 2   Team1_Score    Team2_Score    Team2_Avg     Team1_Avg
 0       A        B        25              56           5             15
 1       B        C        30              55           35            15
 2       D        E        35              75        

我试图使用pandasql模块，但是我不确定如何处理df1中的索引1和df2中的索引2的情况，因为团队的顺序颠倒了。通过pandasql模块，我不知道如果团队的顺序相反，如何切换组合数据帧中的Team Avg值。在

如果你能帮上忙我会很感激的。在

Answer 1

您可以通过翻转列名，用pd.concat()复制df2。可以通过使用rename设置它们来完成此操作

df3 = df2.rename(columns={'Team 1':'Team 2','Team 2':'Team 1', 
        'Team1_Avg':'Team2_Avg','Team2_Avg':'Team1_Avg'})

现在我们可以对df2和新创建的merge和{}执行左df3

^{pr2}$

给你想要的数据帧

  Team 1 Team 2  Team1_Score  Team2_score  Team1_Avg  Team2_Avg
0      A      B           25           56        5.0       15.0
1      B      C           30           55       35.0       15.0
2      D      E           25           75        NaN        NaN

Answer 2

设置-

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来执行此操作。在

^{pr2}$

现在，对df2重复相同的过程，使用Team *和{}。在

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