对于如下数据框,我想在每组 city 和 district 中填充缺失的年份(从 2015 年到 2017 年);然后通过按列分组计算pct:city,district和year,最后一步,然后显示value 和 pct 水平列?
city district value year
0 sh a 2 2015
1 sh a 3 2016
2 sh b 5 2015
3 sh b 3 2016
4 bj c 4 2015
5 bj c 3 2017
到目前为止我做了什么:
<强> 1。填补缺失的年份,但还没有工作: 强>
rng = pd.date_range('2015', '2017', freq='YS').dt.year
df = df.apply(lambda x: x.reindex(rng, fill_value = 0))
<强>
2。通过按
city 和
district 分组计算
pct:
强>
df['pct'] = df.sort_values('year').groupby(['city', 'district']).value.pct_change()
<强>
3。水平显示
value 和
pct 列,但顺序不是我想要的:
强>
df.pivot_table(columns='year', index=['city','district'], values=['value', 'pct'], fill_value='NaN').reset_index()
到目前为止我得到的输出:
city district pct value
year 2015 2016 2017 2015 2016 2017
0 bj c NaN NaN -0.25 4.0 NaN 3
1 sh a NaN 0.5 NaN 2.0 3 NaN
2 sh b NaN -0.4 NaN 5.0 3 NaN
我怎么可能得到这样的预期结果?
city district 2015 2016 2017
value pct value pct value pct
bj c 4 3
sh a 2 3 0.5
sh b 5 3 -0.4
谢谢。
请您参考如下方法:
使用DataFrame.swaplevel与 DataFrame.sort_index ,还为 reindex 添加了另一个解决方案:
rng = pd.date_range('2015', '2017', freq='YS').year
c = df['city'].unique()
d = df['district'].unique()
mux = pd.MultiIndex.from_product([c, d, rng], names=['city','district','year'])
df = df.set_index(['city','district','year']).reindex(mux)
df['pct'] = df.sort_values('year').groupby(['city', 'district']).value.pct_change()
df = df.pivot_table(columns='year',
index=['city','district'],
values=['value', 'pct'],
fill_value='NaN')
df = df.swaplevel(0,1, axis=1).sort_index(axis=1, level=0)
print (df)
year 2015 2016 2017
pct value pct value pct value
city district
bj c NaN 4.0 0.0 NaN -0.25 3
sh a NaN 2.0 0.5 3 0.00 NaN
b NaN 5.0 -0.4 3 0.00 NaN
编辑:错误:
ValueError: cannot handle a non-unique multi-index!
表示传递给 groupby 的每列都有重复项,因此此处为 ['city','district','year']。解决方案是创造独特的值(value)——例如按总平均值:
print (df)
# city district value year
#0 sh a 2 2015
#0 sh a 20 2015
#1 sh a 3 2016
#2 sh b 5 2015
#3 sh b 3 2016
#4 bj c 4 2015
#5 bj c 3 2017
rng = pd.date_range('2015', '2017', freq='YS').year
c = df['city'].unique()
d = df['district'].unique()
mux = pd.MultiIndex.from_product([c, d, rng], names=['city','district','year'])
print (df.groupby(['city','district','year'])['value'].mean())
city district year
bj c 2015 4
2017 3
sh a 2015 11
2016 3
b 2015 5
2016 3
Name: value, dtype: int64
df = df.groupby(['city','district','year'])['value'].mean().reindex(mux)
print (df)
#city district year
#sh a 2015 11.0
# 2016 3.0
# 2017 NaN
# b 2015 5.0
# 2016 3.0
# 2017 NaN
# c 2015 NaN
# 2016 NaN
# 2017 NaN
#bj a 2015 NaN
# 2016 NaN
# 2017 NaN
# b 2015 NaN
# 2016 NaN
# 2017 NaN
# c 2015 4.0
# 2016 NaN
# 2017 3.0
#Name: value, dtype: float64
