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>In order to deal with the inconsistent structure of your strings, a good solution is to use regular expressions. I mocked up some data based on your description and created a function to capture the city from the strings. </p>

<p>In my solution I used numpy to output NaN values when there wasn't a match, but you could easily just make it a blank string. I also included a test case where the input was blank in order to display the NaN result.</p>

<pre><code>import re
import numpy as np

data = ["Inst Huizhou, Huihzhou 516001, Guangdong, People's Republic of China",
        "Guangxi Acad Sci, Nanning 530004, Guangxi, People's Republic of China",
        "Shenzhen VisuCA Key Lab SIAT, Shenzhen, People's Republic of China",
        "New York University, New York, New York 10012, United States of America",
        ""]
df = pd.DataFrame(data, columns = ['university'])

def extract_city(row):
    match = re.match('^[^,]*,([^,]*),', row)
    if match:
        city = re.sub('\d+', '', match.group(1)).strip()
    else:
        city = np.nan
    return city


df.university.apply(extract_city)
</code></pre>

<p>Here's the output: </p>

<pre><code>0    Huihzhou
1     Nanning
2    Shenzhen
3    New York
4         NaN
Name: university, dtype: object
</code></pre>

python pandas dataframe match