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>I think one simple idea would be to create a mapping from any word or sequence of word of any address to the full address the word is part of, with the assumption that one of those address words is the cites. In a second step we match this with the set of known cities that you have, and anything that is not a known city gets discarded. </p>

<p>A mapping from each single word to address is as simple as: </p>

<pre><code>def address_to_dict(address):
    return {word: address for word in address.split(",")}
</code></pre>

<p>And we can easily extend this to include the set of bi-grams, tri-gram,... so that universities encoded in several words are also collected. See a discussion here: <a href="http://locallyoptimal.com/blog/2013/01/20/elegant-n-gram-generation-in-python/" rel="nofollow">Elegant N-gram Generation in Python</a></p>

<p>We can then apply this to every address we have to obtain one grand mapping from any word to the full address: </p>

<pre><code>word_to_address_mapping = pd.DataFrame(df.university.apply(address_to_dict ).tolist()).stack()

word_to_address_mapping = pd.DataFrame(word_to_address_mapping, 
                                       columns=["address"])
word_to_address_mapping.index = word_to_address_mapping.index.droplevel(level=0)
word_to_address_mapping
</code></pre>

<p>This yields something like this: </p>

<p><a href="http://i.stack.imgur.com/gaQEA.png" rel="nofollow"><img src="http://i.stack.imgur.com/gaQEA.png" alt="enter image description here"></a></p>

<p>All you have to do then is join this with the actual city list you have: this will automatically discard any entry in <code>word_to_address_mapping</code> which is not a known city, and provide a mapping between university address and their city. </p>

<pre><code># the outer join here should ensure that several university in the 
# same city do not overwrite each other
pd.merge(left=word_to_address_mapping, right=city,
         left_index=True, right_on="city", 
         how="outer)
</code></pre>

python pandas dataframe match