我在 DataFrame 中存储了国家/地区的年度信息 (COUNT)。 但是,有些国家在某些年份缺失。
如果我有一个完整的国家列表,将它们添加到相应年份下并用 0 填充 COUNT 的缺失值的最佳方法是什么?
DATE COUNTRY COUNTRY_ID COUNT
0 1980 United States 840 42
42 1980 Czech Republic 203 2
95 1980 Hungary 348 1
96 1980 Great Britain 826 1
97 1980 South Africa 710 1
98 1982 United States 840 42
140 1982 Paraguay 600 2
.
.
请您参考如下方法:
一种方法是组合所有 DATE、COUNTRY 组合,然后重新索引 DataFrame,最后填充缺失值。
# Assume that we want all years not just the ones seen
years = range(df['DATE'].min(), df['DATE'].max()+1)
# get all combinations
idx = pd.MultiIndex.from_product([years, df['COUNTRY'].unique()], names=['DATE', 'COUNTRY'])
# reindex by first putting DATE and COUNTRY into the index
df1 = df.set_index(['DATE', 'COUNTRY']).reindex(idx).reset_index()
# Fill back in missing IDs
country_map = df.set_index('COUNTRY')['COUNTRY_ID'].drop_duplicates()
df1['COUNTRY_ID'] = df1.COUNTRY.map(country_map)
# fill in 0 for COUNT and convert back to int
df1['COUNT'] = df1['COUNT'].fillna(0).astype(int)
DATE COUNTRY COUNTRY_ID COUNT
0 1980 United States 840 42
1 1980 Czech Republic 203 2
2 1980 Hungary 348 1
3 1980 Great Britain 826 1
4 1980 South Africa 710 1
5 1980 Paraguay 600 0
6 1981 United States 840 0
7 1981 Czech Republic 203 0
8 1981 Hungary 348 0
9 1981 Great Britain 826 0
10 1981 South Africa 710 0
11 1981 Paraguay 600 0
12 1982 United States 840 42
13 1982 Czech Republic 203 0
14 1982 Hungary 348 0
15 1982 Great Britain 826 0
16 1982 South Africa 710 0
17 1982 Paraguay 600 2
