Update a null values in countryside column in a data frame with reference to valid countrycode column from another data frame using python
<p>I have two data frames: Disaster, CountryInfo
Disaster has a column country code which has some null values
for example:
Disaster:</p>

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

<p>CountryInfo:</p>

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

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

<p>You can simply use <code>map</code> with a <code>Series</code>. </p>

<p>With this approach all values are overwritten not only non <code>NaN</code>.</p>

<pre><code># Test data
disaster = pd.DataFrame({'Country': ['India', 'Afghanistan', 'India', 'United States'],
                             'Country_code': [np.nan, 'AFD', 'IND', np.nan]})
country = pd.DataFrame({'Country': ['India', 'Afghanistan', 'United States'],
                         'Country_code': ['IND', 'AFD', 'US']})

# Transforming country into a Series in order to be able to map it directly
country_se = country.set_index('Country').loc[:, 'Country_code']
# Map
disaster['Country_code'] = disaster['Country'].map(country_se)

print(disaster)
#          Country Country_code
# 0          India          IND
# 1    Afghanistan          AFD
# 2          India          IND
# 3  United States           US
</code></pre>

<p>You can filter on <code>NaN</code> if you do not want to overwrite all values.</p>

<pre><code>disaster.loc[pd.isnull(disaster['Country_code']), 
             'Country_code'] = disaster['Country'].map(country_se)
</code></pre>

python python-2.7 pandas