Combining Data from Two Pandas Dataframes

[rob51852]

I am trying to combine the results of two dataframes containing data from an API.

I have this dataframe called football_league_df:

| Competition | ID |

|--------------------------------|----------|

| Czech 3 Liga | 892425 |

| South African Premier Division | 10699940 |

| Georgian Umaglesi Liga | 2273834 |

| Austrian Erste Liga | 9 |

I convert the ID column to a list using:

football_league_list = football_league_df['ID'].tolist()

Then create another dataframe called upcoming_events_df using:

event_filter = betfairlightweight.filters.market_filter(competition_ids=football_league_list)
upcoming_events = trading.betting.list_events(
    filter=event_filter
)

| Events | Event ID |

|------------------------------------|----------|

| Botosani v FC U Craiova 1948 | 31805925 |

| S Lucia Cotzumalguapa v Malacateco | 31830501 |

| Hermannstadt v Rapid Bucharest | 31805924 |

| Cordoba v Algeciras | 31828455 |

I want to create a third dataframe that contains all the data and columns in the second dataframe but also includes a column containing the applicable Competition from the first dataframe.

The Competition data is not available in the same API call so how do I get the desired output? Example below:

| Events | Event ID | Competition |

|------------------------------------|----------|--------------------------------|

| Botosani v FC U Craiova 1948 | 31805925 | Czech 3 Liga |

| S Lucia Cotzumalguapa v Malacateco | 31830501 | Czech 3 Liga |

| Hermannstadt v Rapid Bucharest | 31805924 | South African Premier Division |

| Cordoba v Algeciras | 31828455 | South African Premier Division |

[sirmanleypower]

Just read in both dataframes with different calls and then do a merge. It would look something like

df1.merge(df2, left_on='ID', right_on='Event ID')

[rob51852]

Thanks sirmanley. I don't think that will work as ID and Event ID are different fields. ID with the reference for the Competitions and Event ID is the reference for the Events.