Q:

Mapping a new column to a DataFrame by rows from another DataFrame

I have a Pandas DataFrame stations with index as id:
id    station     lat     lng
1     Boston      45.343  -45.333
2     New York    56.444  -35.690

I have another DataFrame df1 that has the following:
duration   date       station   gender
NaN        20181118   NaN       M
9          20181009   2.0       F
8          20170605   1.0       F

I want to add to df1 so that it looks like the following DataFrame:
duration   date       station   gender  lat     lng 
NaN        20181118   NaN       M       nan     nan
9          20181009   New York  F       56.444  -35.690
8          20170605   Boston    F       45.343  -45.333

I tried doing this iteratively by referring to the station.iloc[] as shown in the following example but I have about 2 mil rows and it ended up taking a lot of time. 
stat_list = []    
lng_list []
lat_list = []
for stat in df1:
  if not np.isnan(stat):
        ref = station.iloc[stat]
        stat_list.append(ref.station)
        lng_list.append(ref.lng)
        lat_list.append(ref.lat)
  else:
        stat_list.append(np.nan)
        lng_list.append(np.nan)
        lat_list.append(np.nan)

Is there a faster way to do this? 

A:

Looks like this would be best solved with a merge which should significantly boost performance:
df1.merge(stations, left_on="station", right_index=True, how="left")

This will leave you with two columns station_x and station_y if you only want the station column with the string names in you can do:
df_merged = df1.merge(stations, left_on="station", right_index=True, how="left", suffixes=("_x", ""))
df_final = df_merged[df_merged.columns.difference(["station_x"])]

(or just rename one of them before you merge)

