Pandas - map values from one column into a new column-CodePudding

I have a dataframe with all individual team matchups, where some matches are repeated, like so:

team        adversary     xG
Liverpool   City          1.80
...
City        Liverpool     1.21
...

How do I keep track of both xG values for all team-adversary pairs, ending up with:

team        adversary     xG_team    xG_adversary
Liverpool   City          1.80       1.21
...
City        Liverpool     1.21       1.80
...

CodePudding user response：

You can do a self-merge. I think the most straightforward way is to set_index on the righthand df:

df.merge(
    df.set_index(['adversary', 'team']),
    left_on=['team', 'adversary'],
    right_index=True,
    suffixes=('_team', '_adversary'),
    how='left')

Result:

        team  adversary  xG_team  xG_adversary
0  Liverpool       City     1.80          1.21
1       City  Liverpool     1.21          1.80