Pandas: Create column in a df dependig on a column of a different df

Question

I have this two dataframes:

df1:

    Team      Goals   Goal Against
1    Oly       20        5
2    PSG        6        4
3    Lil        3        6
4    Ars       10        3
5    Bar        8        9

df2:

    Player     Team    Passes     Shots
1   Player 1    Bar       20        5
2   Player 2    PSG        6        4
3   Player 3    Ars        3        6

I would like to add to df2 a new column 'Team Goal Against' which includes the info of 'Goal Against' of the df1. This column would read the info of the column Team of df2 and if it's the same of Team in df2 it would show the info of 'Goal Against' in the new column 'Team Goal Against'. The result in df2 would be:

    Player     Team    Passes     Shots   Team Goal Against
1   Player 1    Bar       20        5         9
2   Player 2    PSG        6        4         4
3   Player 3    Ars        3        6         3

Any suggestion about how to do it? Thanks in advance!

score 1 · Answer 1 · answered Jun 11 '21 at 15:02

df2.merge(df1[["Team","Goal Against"]], on="Team")

Working example:

import pandas as pd
df1 = pd.DataFrame([{"Team": "Team 1", "Goal Against": 1}])
df2 = pd.DataFrame([{"Player": "Player 1", "Team": "Team 1"}])
df_final = df2.merge(df1[["Team","Goal Against"]], on="Team")

     Player    Team  Goal Against
0  Player 1  Team 1             1

Pandas: Create column in a df dependig on a column of a different df

1 Answers1