I have a df:
count 0 1 2 3 4
cohort
2020-10 46.0 18.0 10.0 3.0 NaN
2020-11 290.0 113.0 40.0 13.0 NaN
2020-12 370.0 176.0 61.0 6.0 1.0
2021-01 561.0 240.0 66.0 4.0 1.0
2021-02 575.0 280.0 80.0 5.0 NaN
2021-03 671.0 274.0 73.0 3.0 1.0
2021-04 1028.0 438.0 56.0 1.0 NaN
2021-05 1191.0 488.0 10.0 2.0 NaN
2021-06 1320.0 607.0 12.0 1.0 NaN
2021-07 1309.0 495.0 9.0 1.0 NaN
2021-08 921.0 209.0 NaN NaN NaN
2021-09 653.0 7.0 NaN NaN NaN
2021-10 633.0 3.0 NaN NaN NaN
2021-11 708.0 5.0 1.0 NaN NaN
2021-12 390.0 4.0 NaN NaN NaN
I am doing a cohort analysis and I want create two charts. One where each step is divided by the total cohort size, and one where it's divided by the previous column's size.
The first chart I was able to achieve by using
df1 = df.divide(df.iloc[:,0], axis = 0)
Which results:
count 0 1 2 3 4
cohort
2020-10 1.0 0.391304 0.217391 0.065217 NaN
2020-11 1.0 0.389655 0.137931 0.044828 NaN
2020-12 1.0 0.475676 0.164865 0.016216 NaN
2021-01 1.0 0.427807 0.117647 NaN NaN
2021-02 1.0 0.486957 0.139130 NaN NaN
2021-03 1.0 0.408346 0.108793 NaN NaN
2021-04 1.0 0.426070 0.054475 NaN NaN
2021-05 1.0 0.409740 NaN NaN NaN
2021-06 1.0 0.459848 NaN NaN NaN
2021-07 1.0 0.378151 NaN NaN NaN
2021-08 1.0 0.226927 NaN NaN NaN
2021-09 1.0 NaN NaN NaN NaN
2021-10 1.0 NaN NaN NaN NaN
2021-11 1.0 NaN NaN NaN NaN
2021-12 1.0 NaN NaN NaN NaN
However, now I want a similar table which instead of dividing by the 0 index throughout the entire df, I want every column to divide by the size of the previous column
Resulting Df portion (roughly):
count 0 1 2 3 4
cohort
2020-10 1.0 0.391304 0.555555 0.30 NaN
2020-11 1.0 0.389655 0.354 0.32 NaN
Constructor:
dictionary = {0: {Period('2020-10', 'M'): 46.0,
Period('2020-11', 'M'): 290.0,
Period('2020-12', 'M'): 370.0,
Period('2021-01', 'M'): 561.0,
Period('2021-02', 'M'): 575.0,
Period('2021-03', 'M'): 671.0,
Period('2021-04', 'M'): 1028.0,
Period('2021-05', 'M'): 1191.0,
Period('2021-06', 'M'): 1320.0,
Period('2021-07', 'M'): 1309.0,
Period('2021-08', 'M'): 921.0,
Period('2021-09', 'M'): 653.0,
Period('2021-10', 'M'): 633.0,
Period('2021-11', 'M'): 708.0,
Period('2021-12', 'M'): 390.0},
1: {Period('2020-10', 'M'): 18.0,
Period('2020-11', 'M'): 113.0,
Period('2020-12', 'M'): 176.0,
Period('2021-01', 'M'): 240.0,
Period('2021-02', 'M'): 280.0,
Period('2021-03', 'M'): 274.0,
Period('2021-04', 'M'): 438.0,
Period('2021-05', 'M'): 488.0,
Period('2021-06', 'M'): 607.0,
Period('2021-07', 'M'): 495.0,
Period('2021-08', 'M'): 209.0,
Period('2021-09', 'M'): 7.0,
Period('2021-10', 'M'): 3.0,
Period('2021-11', 'M'): 5.0,
Period('2021-12', 'M'): 4.0},
2: {Period('2020-10', 'M'): 10.0,
Period('2020-11', 'M'): 40.0,
Period('2020-12', 'M'): 61.0,
Period('2021-01', 'M'): 66.0,
Period('2021-02', 'M'): 80.0,
Period('2021-03', 'M'): 73.0,
Period('2021-04', 'M'): 56.0,
Period('2021-05', 'M'): 10.0,
Period('2021-06', 'M'): 12.0,
Period('2021-07', 'M'): 9.0,
Period('2021-08', 'M'): nan,
Period('2021-09', 'M'): nan,
Period('2021-10', 'M'): nan,
Period('2021-11', 'M'): 1.0,
Period('2021-12', 'M'): nan},
3: {Period('2020-10', 'M'): 3.0,
Period('2020-11', 'M'): 13.0,
Period('2020-12', 'M'): 6.0,
Period('2021-01', 'M'): 4.0,
Period('2021-02', 'M'): 5.0,
Period('2021-03', 'M'): 3.0,
Period('2021-04', 'M'): 1.0,
Period('2021-05', 'M'): 2.0,
Period('2021-06', 'M'): 1.0,
Period('2021-07', 'M'): 1.0,
Period('2021-08', 'M'): nan,
Period('2021-09', 'M'): nan,
Period('2021-10', 'M'): nan,
Period('2021-11', 'M'): nan,
Period('2021-12', 'M'): nan},
4: {Period('2020-10', 'M'): nan,
Period('2020-11', 'M'): nan,
Period('2020-12', 'M'): 1.0,
Period('2021-01', 'M'): 1.0,
Period('2021-02', 'M'): nan,
Period('2021-03', 'M'): 1.0,
Period('2021-04', 'M'): nan,
Period('2021-05', 'M'): nan,
Period('2021-06', 'M'): nan,
Period('2021-07', 'M'): nan,
Period('2021-08', 'M'): nan,
Period('2021-09', 'M'): nan,
Period('2021-10', 'M'): nan,
Period('2021-11', 'M'): nan,
Period('2021-12', 'M'): nan}}