使用merge在大Pandas中的VLookup

Question

我有两个数据帧：

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

其他数据帧：

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

我希望合并数据集以获得最终数据帧，如下所示：

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

公共密钥是df_dict.Bet365 我正在尝试合并pd.merge，但无法获得正确的键和正确的联接

非常感谢您的帮助

Answer 1

通过Series将^{}用于两个列，并将Bet365列转换为索引：

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

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