I have a dataframe as below. I want to calculate df['Close'].pct_change()
in the slices between posizione==1
and posizione==-1
. So in this example I have to calculate the pct_change in the slices:
between (2022-04-06 09:00:00,2022-04-06 10:15:00);<BR/>
between (2022-04-06 12:30:00,2022-04-06 14:00:00);<BR/>
between (2022-04-06 15:15:00,2022-04-06 16:00:00);<BR/>
so a formula like
df['between']=np.where(df['posizione'].between(1,-1),df['posizione'].pct_change(),0)
outside 1 and -1 the pct_change() will be zero. Is it possible?. Thanks in advance
Time Close buy_80_150 posizione
0 2022-04-06 07:30:00 1.053 0.0 0.0
1 2022-04-06 07:45:00 1.049 0.0 0.0
2 2022-04-06 08:00:00 1.046 0.0 0.0
3 2022-04-06 08:15:00 1.049 0.0 0.0
4 2022-04-06 08:30:00 1.048 0.0 0.0
5 2022-04-06 08:45:00 1.049 0.0 0.0
6 2022-04-06 09:00:00 1.047 1.0 1.0
7 2022-04-06 09:15:00 1.044 1.0 0.0
8 2022-04-06 09:30:00 1.044 1.0 0.0
9 2022-04-06 09:45:00 1.050 1.0 0.0
10 2022-04-06 10:00:00 1.041 1.0 0.0
11 2022-04-06 10:15:00 1.048 0.0 -1.0
12 2022-04-06 10:30:00 1.040 0.0 0.0
13 2022-04-06 10:45:00 1.032 0.0 0.0
14 2022-04-06 11:00:00 1.017 0.0 0.0
15 2022-04-06 11:15:00 1.018 0.0 0.0
16 2022-04-06 11:30:00 1.021 0.0 0.0
17 2022-04-06 11:45:00 1.023 0.0 0.0
18 2022-04-06 12:00:00 1.021 0.0 0.0
19 2022-04-06 12:15:00 1.024 0.0 0.0
20 2022-04-06 12:30:00 1.021 1.0 1.0
21 2022-04-06 12:45:00 1.014 1.0 0.0
22 2022-04-06 13:00:00 1.018 1.0 0.0
23 2022-04-06 13:15:00 1.024 1.0 0.0
24 2022-04-06 13:30:00 1.014 1.0 0.0
25 2022-04-06 13:45:00 1.011 1.0 0.0
26 2022-04-06 14:00:00 1.014 0.0 -1.0
27 2022-04-06 14:15:00 1.017 0.0 0.0
28 2022-04-06 14:30:00 1.019 0.0 0.0
29 2022-04-06 14:45:00 1.015 0.0 0.0
30 2022-04-06 15:00:00 1.009 0.0 0.0
31 2022-04-06 15:15:00 1.003 1.0 1.0
32 2022-04-06 15:30:00 1.007 1.0 0.0
33 2022-04-06 15:45:00 1.007 1.0 0.0
34 2022-04-06 16:00:00 1.002 0.0 -1.0
35 2022-04-06 16:15:00 0.994 0.0 0.0
36 2022-04-06 16:30:00 0.993 0.0 0.0
37 2022-04-06 16:45:00 0.992 0.0 0.0
38 2022-04-06 17:00:00 0.980 0.0 0.0
CodePudding user response:
IIUC, you can compute a mask and use it on your data before pct_change
:
m1 = df['posizione'].replace(0, float('nan')).ffill().eq(1)
m2 = df['posizione'].eq(-1)
df['pct_change'] = df['Close'].where(m1|m2).pct_change().fillna(0)
output:
Time Close buy_80_150 posizione pct_change
0 2022-04-06 07:30:00 1.053 0.0 0.0 0.000000
1 2022-04-06 07:45:00 1.049 0.0 0.0 0.000000
2 2022-04-06 08:00:00 1.046 0.0 0.0 0.000000
3 2022-04-06 08:15:00 1.049 0.0 0.0 0.000000
4 2022-04-06 08:30:00 1.048 0.0 0.0 0.000000
5 2022-04-06 08:45:00 1.049 0.0 0.0 0.000000
6 2022-04-06 09:00:00 1.047 1.0 1.0 0.000000
7 2022-04-06 09:15:00 1.044 1.0 0.0 -0.002865
8 2022-04-06 09:30:00 1.044 1.0 0.0 0.000000
9 2022-04-06 09:45:00 1.050 1.0 0.0 0.005747
10 2022-04-06 10:00:00 1.041 1.0 0.0 -0.008571
11 2022-04-06 10:15:00 1.048 0.0 -1.0 0.006724
12 2022-04-06 10:30:00 1.040 0.0 0.0 0.000000
13 2022-04-06 10:45:00 1.032 0.0 0.0 0.000000
14 2022-04-06 11:00:00 1.017 0.0 0.0 0.000000
15 2022-04-06 11:15:00 1.018 0.0 0.0 0.000000
16 2022-04-06 11:30:00 1.021 0.0 0.0 0.000000
17 2022-04-06 11:45:00 1.023 0.0 0.0 0.000000
18 2022-04-06 12:00:00 1.021 0.0 0.0 0.000000
19 2022-04-06 12:15:00 1.024 0.0 0.0 0.000000
20 2022-04-06 12:30:00 1.021 1.0 1.0 -0.025763
21 2022-04-06 12:45:00 1.014 1.0 0.0 -0.006856
22 2022-04-06 13:00:00 1.018 1.0 0.0 0.003945
23 2022-04-06 13:15:00 1.024 1.0 0.0 0.005894
24 2022-04-06 13:30:00 1.014 1.0 0.0 -0.009766
25 2022-04-06 13:45:00 1.011 1.0 0.0 -0.002959
26 2022-04-06 14:00:00 1.014 0.0 -1.0 0.002967
27 2022-04-06 14:15:00 1.017 0.0 0.0 0.000000
28 2022-04-06 14:30:00 1.019 0.0 0.0 0.000000
29 2022-04-06 14:45:00 1.015 0.0 0.0 0.000000
30 2022-04-06 15:00:00 1.009 0.0 0.0 0.000000
31 2022-04-06 15:15:00 1.003 1.0 1.0 -0.010848
32 2022-04-06 15:30:00 1.007 1.0 0.0 0.003988
33 2022-04-06 15:45:00 1.007 1.0 0.0 0.000000
34 2022-04-06 16:00:00 1.002 0.0 -1.0 -0.004965
35 2022-04-06 16:15:00 0.994 0.0 0.0 0.000000
36 2022-04-06 16:30:00 0.993 0.0 0.0 0.000000
37 2022-04-06 16:45:00 0.992 0.0 0.0 0.000000
38 2022-04-06 17:00:00 0.980 0.0 0.0 0.000000