Pandas: Replace index values(int) in column with column values (string) of another dataframe matching the index values

df1

                    STATION NAME     LAT     LON   LAT_rad   LON_rad
                       BEAUFORT  32.400 -80.633  0.565487 -1.407311
    CONWAY HORRY COUNTY AIRPORT  33.828 -79.122  0.590410 -1.380939
               HUSTON/EXECUTIVE  29.800 -95.900  0.520108 -1.673771
              ELIZABETHTON MUNI  36.371 -82.173  0.634794 -1.434189
           JACK BARSTOW AIRPORT  43.663 -84.261  0.762063 -1.470632
  MARLBORO CO JETPORT H E AVENT  34.622 -79.734  0.604268 -1.391621
            SUMMERVILLE AIRPORT  33.063 -80.279  0.577058 -1.401133

df2

                       STATION NAME     LAT  ...  distance_nearest  id_nearest
  BOOMVANG HELIPORT / OIL PLATFORM  27.350  ...          0.046963           2
                LEE COUNTY AIRPORT  36.654  ...          0.015469           3
                         ELLINGTON  35.507  ...          0.067148           3
      LAWRENCEVILLE BRUNSWICK MUNI  36.773  ...          0.046532           5
                         PUTNAM CO  39.630  ...          0.077857           4

I want to replace "id_nearest" column which holds indices to df1 column "STATION NAME" with the actual string. This is what I tried

for index, row in df2.itertuples():
    (_id) = (row.id_nearest)

    df2["id_name"] = df1.loc[_id, 'STATION NAME']

and this was the output

                       STATION NAME     LAT  ...  id_nearest               id_name
  BOOMVANG HELIPORT / OIL PLATFORM  27.350  ...           2  JACK BARSTOW AIRPORT
                LEE COUNTY AIRPORT  36.654  ...           3  JACK BARSTOW AIRPORT
                         ELLINGTON  35.507  ...           3  JACK BARSTOW AIRPORT
      LAWRENCEVILLE BRUNSWICK MUNI  36.773  ...           5  JACK BARSTOW AIRPORT
                         PUTNAM CO  39.630  ...           4  JACK BARSTOW AIRPORT

As you can see this is clearly wrong. The Expected output is

                           STATION NAME     LAT  ...  id_nearest       id_name
  BOOMVANG HELIPORT / OIL PLATFORM  27.350  ...           2   HUSTON/EXECUTIVE
                LEE COUNTY AIRPORT  36.654  ...           3   ELIZABETHTON MUNI
                         ELLINGTON  35.507  ...           3   ELIZABETHTON MUNI
      LAWRENCEVILLE BRUNSWICK MUNI  36.773  ...           5   MARLBORO CO JETPORT H E AVENT
                         PUTNAM CO  39.630  ...           4   JACK BARSTOW AIRPORT

Answer

You can use .map() to map id_nearest to the index of df1 and get the df1 column STATION NAME values, as follows:

df2['id_name'] = df2['id_nearest'].map(df1['STATION NAME'])

Data Input

df1

                    STATION NAME     LAT     LON   LAT_rad   LON_rad
0                       BEAUFORT  32.400 -80.633  0.565487 -1.407311
1    CONWAY HORRY COUNTY AIRPORT  33.828 -79.122  0.590410 -1.380939
2               HUSTON/EXECUTIVE  29.800 -95.900  0.520108 -1.673771
3              ELIZABETHTON MUNI  36.371 -82.173  0.634794 -1.434189
4           JACK BARSTOW AIRPORT  43.663 -84.261  0.762063 -1.470632
5  MARLBORO CO JETPORT H E AVENT  34.622 -79.734  0.604268 -1.391621
6            SUMMERVILLE AIRPORT  33.063 -80.279  0.577058 -1.401133


df2

                       STATION NAME     LAT  distance_nearest  id_nearest
0  BOOMVANG HELIPORT / OIL PLATFORM  27.350          0.046963           2
1                LEE COUNTY AIRPORT  36.654          0.015469           3
2                         ELLINGTON  35.507          0.067148           3
3      LAWRENCEVILLE BRUNSWICK MUNI  36.773          0.046532           5
4                         PUTNAM CO  39.630          0.077857           4

Output:

                       STATION NAME     LAT  distance_nearest  id_nearest                        id_name
0  BOOMVANG HELIPORT / OIL PLATFORM  27.350          0.046963           2               HUSTON/EXECUTIVE
1                LEE COUNTY AIRPORT  36.654          0.015469           3              ELIZABETHTON MUNI
2                         ELLINGTON  35.507          0.067148           3              ELIZABETHTON MUNI
3      LAWRENCEVILLE BRUNSWICK MUNI  36.773          0.046532           5  MARLBORO CO JETPORT H E AVENT
4                         PUTNAM CO  39.630          0.077857           4           JACK BARSTOW AIRPORT