I am calculating the shares across two dimensions (lets say product type and region) for individual years:
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)
I am getting something like this (shares per year):
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
However, now I want to calculate the share differences per year and get the average difference for different time periods (e.g. year1-5, vs. year6-10).
I would know how to do it in a 1d form, but for that I would have to create a single column for every row/column combination. However, the final output I need again as a 4x5 dataframe.
IIUC, per your approach, you can store all the annual data in an array and work on that.
But better yet, create a double-index dataframe:
# 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')
Output:
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
With this output, the last 5 years average ending in 2019 is:
new_df.loc[2019]
which gives
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