Update a null values in countrycode column in a data frame by matching substring of country name using python
<p>I have two data frames: Disaster, CountryInfo Disaster has a column country code which has some null values for example: </p>

<p>Disaster:</p>

<pre><code> 1.**Country**              - **Country_code** 
 2.India                    - Null         
 3.Afghanistan (the)        - AFD
 4.India                    - IND
 5.United States of America - Null
</code></pre>

<p>CountryInfo:</p>

<pre><code>0.**CountryName**   - **ISO** 
1.India             - IND
2.Afganistan        - AFD
3.United States     - US
</code></pre>

<p>Expected Result</p>

<pre><code>          Country Country_code
 0          India          IND
 1    Afghanistan          AFD
 2          India          IND
 3  United States           US
</code></pre>

<p>I need to fill the country code with reference to the substring of the country name.Can anyone suggest a solution for this?</p>

<p>This should do it. You need to change the column names with <code>rename</code> so that both <code>dataframes</code> have the same column names. Then, the <code>difflib</code> module and its <code>get_close_matches</code> method can be used to do a fuzzy match and replace of <code>Country</code> names. Then it is a simple matter of merging the <code>dataframes</code></p>

<pre><code>import pandas as pd
import numpy as np
import difflib

df1 = pd.DataFrame({'Country' : ['India', 'Afghanistan', 'India', 'United States of America'],
                        'Country_code' : ['Null', 'AFD', 'IND', 'Null']})
df1
                    Country Country_code
0                     India         Null
1               Afghanistan          AFD
2                     India          IND
3  United States of America         Null

df2 = pd.DataFrame({'Country' : ['India', 'Afghanistan', 'India', 'United States'],
                    'ISO' : ['IND', 'AFD', 'IND', 'USA']})
df2
          Country ISO
0          India  IND
1    Afghanistan  AFD
2          India  IND
3  United States  USA

df2.rename(columns={'ISO' : 'Country_code'}, inplace=True)
df2
         Country Country_code
0          India          IND
1    Afghanistan          AFD
2          India          IND
3  United States          USA
</code></pre>

<p>The following code will change the <code>Country</code> column in <code>df2</code> with the names in the <code>Country</code> column in <code>df1</code> that provide the closest match. This is a way of performing a kind of "fuzzy join" on the substrings.</p>

<pre><code>df1['Country'] = df1.Country.map(lambda x: difflib.get_close_matches(x, df2.Country)[0])
df1
         Country Country_code
0          India         Null
1    Afghanistan          AFD
2          India          IND
3  United States         Null
</code></pre>

<p>Now you can simply <code>merge</code> the <code>dataframes</code>, which will update missing <code>Country_code</code> rows in <code>df1</code>.</p>

<pre><code>df1.merge(df2, how='right', on=['Country', 'Country_code'])

         Country Country_code
0    Afghanistan          AFD
1          India          IND
2          India          IND
3  United States          USA
</code></pre>

python python-2.7 pandas