I have a dataframe and I want to return values from the database matching the defined column in pandas. e.g.
df_test:
country league home_odds draw_odds away_odds home_score away_score home_team away_team datetime
0 USA USL League Two 2.30 3.71 2.53 NaN NaN Colorado Rush Ogden City 2021-07-10 01:00:00
1 World Club Friendly 2.12 3.85 2.85 NaN NaN Lyngby Helsingor 2021-07-10 11:00:00
2 Belarus Pershaya Liga 1.67 3.53 4.72 NaN NaN Arsenal Dzyarzhynsk Lokomotiv Gomel 2021-07-10 11:30:00
3 Sweden Division 1 - Norra 1.35 5.47 6.66 NaN NaN Dalkurd Taby 2021-07-10 12:00:00
and
df_train:
home_odds draw_odds away_odds country league home_team away_team home_score away_score datetime
0 1.20 6.06 9.87 Â USA USL League Two Cedar New Jersey Copa 1 1 2021-06-10 00:00:00
1 2.14 3.69 2.79 Â USA USL League Two Chicago United Kaw Valley 4 0 2021-06-10 00:00:00
2 2.56 3.46 2.40 Â USA USL League Two Grand Rapids Flint City 1 1 2021-06-10 00:00:00
3 1.36 4.31 7.66 Â Brazil Copa do Nordeste Sport Recife Imperatriz 2 2 2020-02-07 00:00:00
4 2.62 3.30 2.48 Â Brazil Copa do Nordeste ABC America RN 2 1 2020-02-02 22:00:00
5 5.19 3.58 1.62 Â Brazil Copa do Nordeste Frei Paulistano Nautico 0 2 2020-02-02 00:00:00
6 2.46 3.63 2.35 Â World Club Friendly Atl. Victoria Tacoronte 2 1 2019-11-12 22:00:00
7 5.37 4.59 1.42 Â World Club Friendly Women Achilles 29 W Duisburg W 0 5 2017-08-11 13:30:00
8 1.51 3.89 5.68 Â Belarus Pershaya Liga Naftan Oshmyany 1 1 2020-06-14 14:00:00
9 1.73 3.48 4.48 Â Belarus Pershaya Liga FC Slonim Chimik Svetlogorsk 1 0 2020-06-14 13:00:00
10 1.48 3.89 6.10 Â Sweden Division 1 - Norra AFC Eskilstuna Akropolis 0 0 2011-09-02 17:00:00
11 1.37 4.35 6.69 Â Sweden Division 1 - Norra Sirius V. Syrianska 2 1 2011-08-29 17:00:00
12 1.50 3.97 5.50 Â Sweden Division 1 - Norra Umea FC IFK Lulea 3 0 2011-08-29 17:00:00
After I do a Vlookup like operation in pandas, on the columns country and league the expected dataframe would be:
df_join:
home_odds draw_odds away_odds country league home_team away_team home_score away_score datetime
0 1.20 6.06 9.87 Â USA USL League Two Cedar New Jersey Copa 1 1 2021-06-10 00:00:00
1 2.14 3.69 2.79 Â USA USL League Two Chicago United Kaw Valley 4 0 2021-06-10 00:00:00
2 2.56 3.46 2.40 Â USA USL League Two Grand Rapids Flint City 1 1 2021-06-10 00:00:00
3 2.46 3.63 2.35 Â World Club Friendly Atl. Victoria Tacoronte 2 1 2019-11-12 22:00:00
4 5.37 4.59 1.42 Â World Club Friendly Women Achilles 29 W Duisburg W 0 5 2017-08-11 13:30:00
5 1.51 3.89 5.68 Â Belarus Pershaya Liga Naftan Oshmyany 1 1 2020-06-14 14:00:00
6 1.73 3.48 4.48 Â Belarus Pershaya Liga FC Slonim Chimik Svetlogorsk 1 0 2020-06-14 13:00:00
7 1.48 3.89 6.10 Â Sweden Division 1 - Norra AFC Eskilstuna Akropolis 0 0 2011-09-02 17:00:00
8 1.37 4.35 6.69 Â Sweden Division 1 - Norra Sirius V. Syrianska 2 1 2011-08-29 17:00:00
9 1.50 3.97 5.50 Â Sweden Division 1 - Norra Umea FC IFK Lulea 3 0 2011-08-29 17:00:00
When I use examples provided here I get an empty dataframe
df_test = df_input_test.merge(df_input_train, on='country')
Empty DataFrame
Columns: [country, league_x, home_odds_x, draw_odds_x, away_odds_x, home_score_x, away_score_x, home_team_x, away_team_x, datetime_x, home_odds_y, draw_odds_y, away_odds_y, league_y, home_team_y, away_team_y, home_score_y, away_score_y, datetime_y]
Index: []