python - VLookup in Pandas using merge

Question

I have 2 dataframes:

df_dict:



 Bet365                Team (Dataset)      Record ID
--  --------------------  ----------------  -----------
 0  Lincoln City          Lincoln                    50
 1  Peterborough          Peterboro                  65
 2  Cambridge Utd         Cambridge                  72
 3  Harrogate Town        Harrogate                  87
 4  Cologne               FC Koln                   160
 5  Hertha Berlin         Hertha                    167
 6  Arminia Bielefeld     Bielefeld                 169
 7  Schalke               Schalke 04                173
 8  TSG Hoffenheim        Hoffenheim                174
 9  SC Freiburg           Freiburg                  175
10  Zulte-Waregem         Waregem                   320
11  Royal Excel Mouscron  Mouscron                  325

Other dataframe:

df_odds:

DateTime                    League                  HomeTeam           AwayTeam                B365H    B365D    B365A
--  --------------------------  ----------------------  -----------------  --------------------  -------  -------  -------
 0  2021-01-09 12:30:00.000001  England League 1        Lincoln City       Peterborough             2.29     3.4      3.1
 1  2021-01-09 15:00:00         England League 2        Cambridge Utd      Harrogate Town           2.29     3.2      3.25
 2  2021-01-09 15:14:59.999999  Belgium First Division  Zulte-Waregem      Royal Excel Mouscron     1.85     3.75     3.8
 3  2021-01-09 14:29:59.999999  Germany Bundesliga 1    SC Freiburg        Cologne                  1.9      3.75     3.75
 4  2021-01-09 14:29:59.999999  Germany Bundesliga 1    Schalke            TSG Hoffenheim           3.8      3.8      1.85
 5  2021-01-10 17:00:00.000001  Germany Bundesliga 1    Arminia Bielefeld  Hertha Berlin            4        3.5      1.9
 6  2021-01-16 14:29:59.999999  Germany Bundesliga 1    Cologne            Hertha Berlin            3.2      3.3      2.25

I would like to merge the dataset to get the final dataframe as:

df_expected

 DateTime                    League                  HomeTeam    AwayTeam      B365H    B365D    B365A
--  --------------------------  ----------------------  ----------  ----------  -------  -------  -------
 0  2021-01-09 12:30:00.000001  England League 1        Lincoln     Peterboro      2.29     3.4      3.1
 1  2021-01-09 15:00:00         England League 2        Cambridge   Harrogate      2.29     3.2      3.25
 2  2021-01-09 15:14:59.999999  Belgium First Division  Waregem     Mouscron       1.85     3.75     3.8
 3  2021-01-09 14:29:59.999999  Germany Bundesliga 1    Freiburg    FC Koln        1.9      3.75     3.75
 4  2021-01-09 14:29:59.999999  Germany Bundesliga 1    Schalke 04  Hoffenheim     3.8      3.8      1.85
 5  2021-01-10 17:00:00.000001  Germany Bundesliga 1    Bielefeld   Hertha         4        3.5      1.9
 6  2021-01-16 14:29:59.999999  Germany Bundesliga 1    FC Koln     Hertha         3.2      3.3      2.25

The common key is the df_dict.Bet365 I am trying merge pd.merge but I am unable to get the right keys and the correct join

Help would be greatly appreciated

Answer 1

Use Series.map for both columns by Series with Bet365 column converted to index:

s = df_dict.set_index('Bet365')['Team (Dataset)']

df_odds['HomeTeam'] = df_odds['HomeTeam'].map(s)
df_odds['AwayTeam'] = df_odds['AwayTeam'].map(s)