我正在计算各个年份的两个维度(假设产品类型和地区(的份额:
for year in years:
subset = df[df["year"] == year]
total_value = subset["Sales"].sum()
test = pd.crosstab(subset["region"], subset["type"], values= subset["Sales"], aggfunc='sum')
test = test.div(total_value)
test = test.mul(100)
test = test.fillna(0).applymap('{:,.2f}'.format)
test = test[test.columns].astype(float)
我得到这样的东西(每年的份额(:
P1 P2 P3 P4 P5
East 7.87 0.19 3.62 18.03 4.21
North 2.61 0.00 1.43 2.72 1.58
South 4.86 0.00 3.28 4.36 5.02
West 8.56 0.00 7.30 14.34 10.01
但是,现在我想计算每年的份额差异,并获得不同时间段(例如第 1-5 年与第 6-10 年(的平均差异。
我会知道如何以 1d 形式做到这一点,但为此我必须为每个行/列组合创建一个列。但是,最终输出我再次需要作为 4x5 数据帧。
IIUC,根据您的方法,您可以将所有年度数据存储在一个数组中并进行处理。
但更好的是,创建一个双索引数据帧:
# toy data
np.random.seed(1)
df = pd.DataFrame({'year': np.random.randint(2010,2020, 1000),
'region':np.random.choice(['E','N','S','W'], 1000),
'type': np.random.choice(range(5), 1000),
'Sales': np.random.randint(0,100, 1000)})
# annual sale by number
new_df = df.groupby(['year','region','type']).Sales.sum().unstack('type')
# annual sale percentage
# unstack is for difference and rolling
new_df = new_df.div(new_df.sum(1), axis='rows').mul(100).unstack('region')
# now we take difference Y-o-Y and sum over rolling 5 years
new_df = new_df.diff().abs().rolling(5).sum().stack('region')
输出:
type 0 1 2 3 4
year region
2015 E 44.474332 64.931846 61.957656 30.060912 45.492996
N 36.204057 52.299241 45.474781 NaN 109.632937
S 39.698786 83.768715 27.301780 40.782696 36.904007
W 49.670535 66.442188 72.853962 64.791541 41.014700
2016 E 38.388212 65.782743 50.332091 29.604978 59.610948
N 29.523157 39.702785 46.555568 NaN 74.166048
S 31.292163 91.905342 22.590774 48.125503 40.766833
W 43.356486 49.935648 61.237368 61.780280 48.403081
2017 E 29.999764 50.469091 53.820935 21.917220 63.225173
N 23.144194 44.182024 56.224184 73.611386 47.923053
S 39.958449 97.206148 36.318395 38.854843 48.255563
W 39.394688 44.748035 61.690934 40.369818 52.724580
2018 E 44.147129 60.643527 52.280244 35.161092 79.539544
N 30.314490 30.613567 38.863245 88.982652 39.505871
S 43.003287 78.883680 62.720196 46.120358 47.269314
W 53.430137 53.121051 59.104072 34.959932 56.230274
2019 E 39.953920 69.182441 30.876777 51.356302 94.883691
N 56.479921 30.338623 49.644488 83.042179 25.614797
S 55.892248 47.252970 65.340297 44.674311 32.825135
W 61.341875 43.624507 50.857851 26.915145 83.036502
有了这个产出,截至2019年的过去5年平均值为:
new_df.loc[2019]
这给了
type 0 1 2 3 4
region
E 39.953920 69.182441 30.876777 51.356302 94.883691
N 56.479921 30.338623 49.644488 83.042179 25.614797
S 55.892248 47.252970 65.340297 44.674311 32.825135
W 61.341875 43.624507 50.857851 26.915145 83.036502
这太好了!但是,有一个小的修正。份额不应按行(按区域(求和,而应按年份求和(整个 df 总和为 1(。由于某些原因,.unstack()
在链中对我不起作用。因此,我不得不将第二行更改为:
new_df = new_df.unstack('region')
new_df = new_df.div(new_df.sum(1), axis='rows').mul(100)