I have a data frame that looks like this:
Ret %
0 0.02
1 0.01
2 0.04
3 -0.02
4 -0.01
5 0.04
6 0.02
7 -0.01
8 0.04
9 -0.02
10 0.01
11 0.04
I need to create a column named 'Equity' that shows how equity changes from a starting amount every time the percentage change (return %) of the first column is applied to this amount. The result should look like this assuming that the starting amount is 100:
Ret % Equity
0 0.02 102.00
1 0.01 103.02
2 0.04 107.14
3 -0.02 105.00
4 -0.01 103.95
5 0.04 108.11
6 0.02 110.27
7 -0.01 109.17
8 0.04 113.53
9 -0.02 111.26
10 0.01 112.37
11 0.04 116.87
I found a solution using a "for" loop however I need to increase the performance so I'm looking for a vectorized solution with a panda/numpy method. Is there a way to do this?
Thank you in advance
Try using df.cumprod:
base = 100
df['Equity'] = (1 + df['Ret %']).cumprod()*base
print(df)
Ret % Equity
0 0.02 102.000000
1 0.01 103.020000
2 0.04 107.140800
3 -0.02 104.997984
4 -0.01 103.948004
5 0.04 108.105924
6 0.02 110.268043
7 -0.01 109.165362
8 0.04 113.531977
9 -0.02 111.261337
10 0.01 112.373951
11 0.04 116.868909
Or np.cumprod if you prefer:
import numpy as np
df['Equity'] = np.cumprod(1+df['Ret %'])*base
You could round these values of course to end up with your expected output:
df['Equity'] = df['Equity'].round(2)