Pandas DataFrame, merging two DF together with awkward data-CodePudding

I'm trying to merge data from dataframe A onto dataframe B with limited knowledge of panda.

DF A:

      game_id  team_id      score
0  2000020001       21  39.703125
1  2000020001       25  38.386148
2  2000020002        6  35.527686
3  2000020002        9  45.862678
4  2000020003        7  35.376138
5  2000020003        7  32.786932

DF B:

            away_team_id home_team_id  away_score  home_score
game_id
2000020001            21           25
2000020002             6            9
2000020003             7            4

Final Result Wanted:

            away_team_id home_team_id   away_score  home_score
game_id
2000020001            21           25    39.703125   38.386148
2000020002             6            9    35.527686   45.862678
2000020003             7            4    35.376138   32.786932

How would one go about doing so? Any help would be appreciated.

CodePudding user response：

First we do reset_index

dfb = dfb.reset_index()

Then we can merge

dfb = dfb.merge(dfa.rename(columns = {'team_id':'away_team_id','score':'away_score'}))
dfb = dfb.merge(dfa.rename(columns = {'team_id':'home_team_id','score':'home_score'}))