How to smartly match two data frames using Python (using pandas or other means)?
<p>I have one pandas dataframe composed of the names of the world's cities as well as countries, to which cities belong,</p>

<pre><code>city.head(3)

    city    country
0   Qal eh-ye Now   Afghanistan
1   Chaghcharan Afghanistan
2   Lashkar Gah Afghanistan
</code></pre>

<p>and another data frame consisting of addresses of the world's universities, which is shown below:</p>

<pre><code>df.head(3)
    university
0   Inst Huizhou, Huihzhou 516001, Guangdong, Peop...
1   Guangxi Acad Sci, Nanning 530004, Guangxi, Peo...
2   Shenzhen VisuCA Key Lab SIAT, Shenzhen, People...
</code></pre>

<p>The locations of cities' names are irregularly distributed across rows. I would like to match the city names with the addresses of world's universities. That is, I would like to know which city each university is located in. Hopefully, the city name matched is shown in the same row as the address of each university.</p>

<p>I've tried the following, and it doesn't work because the locations of cities are irregular across the rows.</p>

<pre><code>df['university'].str.split(',').str[0]
</code></pre>

<p>Partial match is prevented in the below function. Information of countries also considered while matching cities. To use this function university dataframe need to be split into list data type, such that every piece of address split into list of strings.</p>

<pre><code>In [22]: def get_city(univ_name_split):
   ....:     # find country from university address
   ....:     for name in univ_name_split:
   ....:         if name in city['country'].values:
   ....:             country = name
   ....:     else:
   ....:         country = None

   ....:     if country:
   ....:         cities = city[city.country == country].city.values
   ....:     else:
   ....:         cities = city['city'].values

   ....:     # find city from university address
   ....:     for name in univ_name_split:
   ....:         if name in cities:
   ....:             return name
   ....:     else:
   ....:         return None
   ....:     


In [1]: import pandas as pd

In [2]: city = pd.read_csv('city.csv')

In [3]: df = pd.read_csv('university.csv')

In [4]: # splitting university name and address

In [5]: df_split = df['university'].str.split(',')

In [6]: df_split = df_split.apply(lambda x:[i.strip() for i in x])

In [10]: df
Out[10]: 
                                          university
0  Kongu Engineering College, Perundurai, Erode, ...
1           Anna University - Guindy, Chennai, India
2  Birla Institute of Technology and Science, Pil...

In [11]: df_split
Out[11]: 
0    [Kongu Engineering College, Perundurai, Erode,...
1           [Anna University - Guindy, Chennai, India]
2    [Birla Institute of Technology and Science, Pi...
Name: university, dtype: object

In [12]: city
Out[12]: 
         city country
0   Bangalore   India
1     Chennai   India
2  Coimbatore   India
3       Delhi   India
4       Erode   India


#This function is shorter version of above function
In [14]: def get_city(univ_name_split):
   ....:     for name in univ_name_split:
   ....:         if name in city['city'].values:
   ....:             return name
   ....:     else:
   ....:         return None
   ....:     

In [15]: df['city'] = df_split.apply(get_city)

In [16]: df
Out[16]: 
                                          university     city
0  Kongu Engineering College, Perundurai, Erode, ...    Erode
1           Anna University - Guindy, Chennai, India  Chennai
2  Birla Institute of Technology and Science, Pil...     None
</code></pre>

python pandas dataframe match