Look Up IDs and Values from Another Dataframe

Question

Assuming we have two dataframes (Baseball_ID and Baseball_Games), how do we add two columns (Home_City and Away_City) to Baseball_Games and include the full name of "City" (e.g. Boston, MA) from Baseball_ID for each team?

Baseball_ID:

  Team_ID    City     
  BOS        Boston, MA   
  DET        Detroit, MI
  NYY        New York, NY
  PIT        Pittsburgh, PA

Baseball_Games:

  Home      Away    Home_City    Away_City 
  NYY       DET     ??           ??
  PIT       BOS     ??           ??

Its a dupe, you can use map. games['Home_City'] = games['Home'].map(ID.set_index('Team_ID').City) — Vaishali, Apr 02 '18 at 03:17
... [Pandas add column from one dataframe to another based on a join](https://stackoverflow.com/q/46211967/2823755) - cannot propose as a dupe because it doesn't have an accepted answer. — wwii, Apr 02 '18 at 03:38

TsurG · Answer 1 · 2018-04-02T08:29:35.450

I think you are looking for the 'merge' function:

Baseball_Games = pd.merge(Baseball_Games, Baseball_id, how='inner', left_on='Home', 
  right_on='Team_ID').drop('Team_ID', axis=1).rename(columns={'City': 'Home_City'})
Baseball_Games = pd.merge(Baseball_Games, Baseball_id, how='inner', left_on='Away', 
  right_on='Team_ID').drop('Team_ID', axis=1).rename(columns={'City': 'Away_City'})

Result:

    Home    Away Home_City      Away_City
0   NYY     DET  New York, NY   Detroit, MI
1   PIT     BOS  Pittsburgh, PA Boston, MA

Wasn't sure from your question if any of the columns are an index. If they are, use 'right_index'=True (or 'left_index', whichever dataframe's index you need) instead of 'right_on' or 'left_on'.

Hope this helps

Look Up IDs and Values from Another Dataframe

1 Answers1