Dataframe cell to be locked and used for a running balance calculation conditional of result on another cell on same row
Next starting value depends on the last value of previous group, so I think it can't be vectorized. It requires some kind of iterative process. I came up with solution doing iteratively on groups of groupby. Reverse df
and assign to df1
. Working on each group of df1
and assign the final list of groups to the original df
df1 = df[::-1]
s = df1.B.isin(['BCLOSE','SCLOSE']).shift(fill_value=False).cumsum()
grps = df1.groupby(s)
init_val= 100
l = []
for _, grp in grps:
s = grp.C * 0.01 * init_val
s.iloc[0] = init_val
s = s.cumsum()
init_val = s.iloc[-1]
l.append(s)
df['D'] = pd.concat(l)
Out[50]:
A B C D
0 1/05/2019 SIT 0.0 158.6
1 2/05/2019 SCLOSE 1.0 158.6
2 3/05/2019 SHODL 10.0 157.3
3 4/05/2019 SHODL 5.0 144.3
4 5/05/2019 SHODL 6.0 137.8
5 6/05/2019 SHODL -6.0 130.0
6 7/05/2019 SHODL 6.0 137.8
7 8/05/2019 SELL 0.0 130.0
8 9/05/2019 SIT 0.0 130.0
9 10/05/2019 SIT 0.0 130.0
10 11/05/2019 BCLOSE -8.0 130.0
11 12/05/2019 BHODL 33.0 138.0
12 13/05/2019 BHODL -15.0 105.0
13 14/05/2019 BHODL 6.0 120.0
14 15/05/2019 BHODL -1.0 114.0
15 16/05/2019 BHODL 5.0 115.0
16 17/05/2019 BHODL 10.0 110.0
17 18/05/2019 BUY 0.0 100.0
18 19/05/2019 SIT 0.0 100.0
19 20/05/2019 SIT 0.0 100.0