1

I have two data frames with schemas as below

df1 =

First_name Second_name team_name
Granit Xhaka Arsenal
Kalvin Phillips Man City

df2 =

home_team team_away
Arsenal Leeds
Brentford Man City

I want to replicate the following excel formula. It matches the team_name column from df1 to the home_team column in df2 and then copys the team in the.team_away column from the same row and adds it to a new column in df1 called GW1, if their is not a match for the row it then checks the team_name column for a match in the team_away column. If there is a match it then copys the value from the home_team into the same GW1 column the result should look like

First_name Second_name team_name. GW1
Granit Xhaka Arsenal. Leeds
Kalvin Phillips Man City Brentford

The excel function is =IFNA(VLOOKUP(F2,C$11:D$17,2,FALSE),INDEX(C$11:C$17,MATCH(F2,D$11:D$17,0)))

I've tried a merge function but although i can get close, i cannot replicate the function as above.

rob simo
  • 11
  • 2
  • 1
    @QuangHoang this is not a simple merge, OP needs to preprocess a bit the reference to have the key/value also as value/key – mozway Apr 20 '23 at 20:50

1 Answers1

0

Create a duplication of the reversed reference with concat to have both the home or away team to act as key, then merge:

df1.merge(pd.concat([df2.rename(columns={'home_team': 'team_name', 'team_away': 'GW1'}),
                     df2.rename(columns={'home_team': 'GW1', 'team_away': 'team_name'})]),
          on='team_name', how='left')

Output:

  First_name Second_name team_name        GW1
0     Granit       Xhaka   Arsenal      Leeds
1     Kalvin    Phillips  Man City  Brentford
mozway
  • 194,879
  • 13
  • 39
  • 75
  • thanks for this. It has worked perfectly, i would never of pulled this together but i can see the premise when looking at the documentation. – rob simo Apr 21 '23 at 19:32
  • @rob you're welcome, don't hesitate to ask if you have questions. – mozway Apr 21 '23 at 19:44