用python中另一个数据帧的值替换一个数据帧中的NAs

Question

我是编码新手，我有2个数据帧，我将发布如下：

原始数据：

country_code      homicides_per_100k
     ABC               2.6
     ABB               nan
     ACC               nan

霍米尤集：

Country Code          year
     ABC               2.6
     ACC               11
     ABB               3.1
     ADD               0.5

两个数据帧的顺序和形状不同

如何使用homi_集合中的数据替换原始_数据中的nan

我的代码看起来像这样。它不起作用：

for row, homicide in enumerate(raw_data['homicides_per_100k']):
    if homicide == "":
       country_code = raw_data.loc[row, 'country_code']
       homi_set_index = homi_set.index[homi_set['Country Code'] == country_code]
       homi_value = homi_set.loc[homi_set_index, '2014']
       raw_data.loc[row, 'homicides_per_100k'] = homi_value

Answer 1

set_index+combine_first。设置索引使其基于country_code更新值。如果raw_data中有不同的值，则Combine first会优先考虑raw_data中的非空值

raw_data = raw_data.set_index('country_code')
raw_data.combine_first(homi_set.set_index('Country Code')
                               .rename(columns={'year': 'homicides_per_100k'}))

print(raw_data)
              homicides_per_100k
country_code                    
ABC                          2.6
ABB                          3.1
ACC                         11.0

Answer 2

import pandas as pd
import numpy as np
# Just Creating your dataframes
raw_data = pd.DataFrame([('ABC', 2.6), ('ABB', np.nan), ('ACC', np.nan)], columns=['Country_code', 'homicides_per_100k'] )
homi_set = pd.DataFrame([('ABC', 2.6), ('ACC', 11), ('ABB', 3.1), ('ADD', 0.5)], columns=['Country_code', 'year'] )
# Left Join
new_set = pd.merge(raw_data, homi_set, on='Country_code', how='left')
# condition on the column
new_set['homicides_per_100k'] = np.where(new_set['homicides_per_100k'].isnull(), new_set['year'], new_set['homicides_per_100k'] )
del new_set['year']
new_set