CODEWITHSUNDEEP
pythonpandasnumpy
RaduS
RaduS

Reputation: 2555

Pandas: Forward and Backward rows count given a specific signal value

Is there any way to effectively count the nr of rows before and after a specific signal value in Pandas?

Here is the Pandas Datatable. You can see a column called "Signal" which has values 0 and 1. This is the given data. What i am looking after is the column "forwardBackwardRows". In this column you see that for each signal value 1 the nr of rows forward and backwards of that value. I can fixed that i a loop but will not be efficient as my tables have around 100mil rows and i have around 1000 of such tables. The maximum count value should be 2. No more than 2 rows should be counted before or after the signal

import pandas as pd
data = pd.DataFrame([[1420.49,0],[1421.12,0],[1418.95,0],[1419.04,1],[1419.04,0],[1417.51,0],[1416.97,0],[1413.21,0],[1411.49,1],[1412.57,0],[1408.55,0],[1411.56,0],[1409.16,0],[1413.38,0],[1413.38,1],[1402.35,0],[1413.22,0],[1411.7,0],[1397.8,0],[1398.36,0],[1397.62,0],[1394.58,1],[1399.05,0],[1399.9,0],[1398.96,1],[1398.96,0],[1393.69,0],[1398.13,0],[1398.66,1],[1398.02,0],[1397.97,1],[1396.05,0],[1398.13,1]], columns=["Values", "Signal"])

Here is a visual of the result i am looking after

+----+---------+--------+---------------------+
|    | Values  | Signal | forwardBackwardRows |
+----+---------+--------+---------------------+
|  0 | 1420.49 |      0 |                   0 |
|  1 | 1421.12 |      0 |                  -3 |
|  2 | 1418.95 |      0 |                  -2 |
|  3 | 1419.04 |      1 |                   1 |
|  4 | 1419.04 |      0 |                   2 |
|  5 | 1417.51 |      0 |                   3 |
|  6 | 1416.97 |      0 |                  -3 |
|  7 | 1413.21 |      0 |                  -2 |
|  8 | 1411.49 |      1 |                   1 |
|  9 | 1412.57 |      0 |                   2 |
| 10 | 1408.55 |      0 |                   3 |
| 11 | 1411.56 |      0 |                   0 |
| 12 | 1409.16 |      0 |                  -3 |
| 13 | 1413.38 |      0 |                  -2 |
| 14 | 1413.38 |      1 |                   1 |
| 15 | 1402.35 |      0 |                   2 |
| 16 | 1413.22 |      0 |                   3 |
| 17 |  1411.7 |      0 |                   0 |
| 18 |  1397.8 |      0 |                   0 |
| 19 | 1398.36 |      0 |                  -3 |
| 20 | 1397.62 |      0 |                  -2 |
| 21 | 1394.58 |      1 |                   1 |
| 22 | 1399.05 |      0 |                   2 |
| 23 |  1399.9 |      0 |                  -2 |
| 24 | 1398.96 |      1 |                   1 |
| 25 | 1398.96 |      0 |                   2 |
| 26 | 1393.69 |      0 |                   3 |
| 27 | 1398.13 |      0 |                  -2 |
| 28 | 1398.66 |      1 |                   1 |
| 29 | 1398.02 |      0 |                   2 |
| 30 | 1397.97 |      1 |                   1 |
| 31 | 1396.05 |      0 |                   2 |
| 32 | 1398.13 |      1 |                   1 |
+----+---------+--------+---------------------+

Upvotes: 1

Views: 600

Answers (1)

yatu
yatu

Reputation: 88236

Here's one way:

start = df[df.Signal == 1].iloc[0].name
end = df[df.Signal == 1].iloc[-1].name

For the increasing counter you can do the following:

g = df.Signal.cumsum()
pos = df.loc[start:, 'Signal'].groupby(g).cumcount()+1
pos = pos.reindex(index = df.index).fillna(0)
pos[pos > 3] = 0

And for the decreasing:

g2 = df.Signal[::-1].cumsum()[::-1]
neg = -(df.loc[:end, 'Signal'].groupby(g2).cumcount(ascending=False)+1) 
neg = neg.reindex(index = df.index).fillna(0)
neg[neg < -3] = 0

And you can use DataFrame.combine in order to obtain the expected output:

def f(x,y):
    if x == 0.:
        return y
    if y == 0.:
        return x
    if abs(x) <= abs(y):
        return x
    else:
        return y

df['forwardBackwardRows'] = pos.combine(neg, func = f)

Output: 

     Values     Signal        forwardBackwardRows
0   1420.49       0                  0.0
1   1421.12       0                 -3.0
2   1418.95       0                 -2.0
3   1419.04       1                  1.0
4   1419.04       0                  2.0
5   1417.51       0                  3.0
6   1416.97       0                 -3.0
7   1413.21       0                 -2.0
8   1411.49       1                  1.0
9   1412.57       0                  2.0
10  1408.55       0                  3.0
11  1411.56       0                  0.0
12  1409.16       0                 -3.0
13  1413.38       0                 -2.0
14  1413.38       1                  1.0
15  1402.35       0                  2.0
16  1413.22       0                  3.0
17  1411.70       0                  0.0
18  1397.80       0                  0.0
19  1398.36       0                 -3.0
20  1397.62       0                 -2.0
21  1394.58       1                  1.0
22  1399.05       0                  2.0
23  1399.90       0                 -2.0
24  1398.96       1                  1.0
25  1398.96       0                  2.0
26  1393.69       0                  3.0
27  1398.13       0                 -2.0
28  1398.66       1                  1.0
29  1398.02       0                  2.0
30  1397.97       1                  1.0
31  1396.05       0                  2.0
32  1398.13       1                  1.0

Upvotes: 1

Related Questions