Merging datasets in Python: 1st data set with city name and 2nd with two different columns for city_id for origin and destination cities to match with

Question

I need some help in understanding merging 2 data sets in one in Python.

Their layout is as follows,

First has columns: city_id, city_name
Second has: origincity_id, lat, long, destinationcity_id, lat, long

I wanted to understand as to how I could merge these two data sets using python or pandas to match the city_name in the first data set to create two columns to match the origin city id and destination city id?

Thank you.

score 0 · Accepted Answer · answered Sep 18 '20 at 05:51

Use map.

Used for substituting each value in a Series with another value, that may be derived from a function, a dict or a Series.

Here we provide another Series from our city lookup dataframe.

>>> import pandas as pd
>>> lookup_df = pd.DataFrame([{"id":1,"city":"NY"},{"id":2,"city":"TX"}])
>>> 
>>> lookup_df
  city  id
0   NY   1
1   TX   2
>>> data_df = pd.DataFrame([{"id1":2,"id2":1},{"id1":1,"id2":2}])
>>> data_df
   id1  id2
0    2    1
1    1    2
>>> data_df["id1_city"] = data_df["id1"].map(lookup_df.set_index("id")["city"])
>>> data_df["id2_city"] = data_df["id2"].map(lookup_df.set_index("id")["city"])
>>> 
>>> data_df
   id1  id2 id1_city id2_city
0    2    1       TX       NY
1    1    2       NY       TX

I am losing 10 rows when I do the above mapping function. Is there another way to keep those rows intact? — Mrinmayee Kulkarni, Oct 21 '20 at 17:14
Are the values in the rows not in lookup, in which case you can pass argument `na_action='ignore'` to `map` function to retain the rows set with original values. — รยקคгรђשค, Oct 21 '20 at 17:19

Merging datasets in Python: 1st data set with city name and 2nd with two different columns for city_id for origin and destination cities to match with

1 Answers1