Home > Back-end >  dataframe calculate pct_change() in slices between two specific values
dataframe calculate pct_change() in slices between two specific values

Time:05-04

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
  • Related