Having an initial dataframe like this, defined with a items columns and a percentage column:
How can I calculate the new items column as shown in the following picture?
In theory this seems an easy task in my mind (and in Excel/Google Sheet), but trying to do this with a pandas dataframe is getting me mad!
Thank you for the help!
>Solution :
You need to compute the cumulated percentage by dividing by 100, adding 1 and computing the cumprod, then multiply by "items":
df = pd.DataFrame({'label': list('ABCDE'),
'items': 5,
'perc': [float('nan'), 0, 24.32, -19.57, -91.80]})
df['new_items'] = (df['perc']
.fillna(0).div(100).add(1)
.cumprod().mul(df['items'])
)
Output:
label items perc new_items
0 A 5 NaN 5.000000
1 B 5 0.00 5.000000
2 C 5 24.32 6.216000
3 D 5 -19.57 4.999529
4 E 5 -91.80 0.409961
Intermediates:
label items perc new_items percent cumprod
0 A 5 NaN 5.000000 1.0000 1.000000
1 B 5 0.00 5.000000 1.0000 1.000000
2 C 5 24.32 6.216000 1.2432 1.243200
3 D 5 -19.57 4.999529 0.8043 0.999906
4 E 5 -91.80 0.409961 0.0820 0.081992