2

I have a dataframe with 3 columns, signal is either 0 or 1. I need to calculate number of cells after the signal was generated. also need to start the calculation with 0 if there was no signal from the start. sample data as follows -

Time    symbol  signal
09:15   abc     0
09:16   abc     0
09:17   abc     0
09:18   abc     1
09:19   abc     0
09:20   abc     0
09:21   abc     0
09:22   abc     1
09:23   abc     0
09:24   abc     1
09:25   abc     1
09:26   abc     0
09:15   xyz     0
09:16   xyz     0
09:17   xyz     1
09:18   xyz     0
09:19   xyz     0
09:20   xyz     0
09:21   xyz     1
09:22   xyz     0
09:23   xyz     0
09:24   xyz     0
09:25   xyz     0
09:26   xyz     0

Expected output -

Time    symbol  signal  MinsSinceSignal
09:15   abc     0       0
09:16   abc     0       0
09:17   abc     0       0
09:18   abc     1       1
09:19   abc     0       2
09:20   abc     0       3
09:21   abc     0       4
09:22   abc     1       1
09:23   abc     0       2
09:24   abc     1       1
09:25   abc     1       1
09:26   abc     0       2
09:15   xyz     0       0
09:16   xyz     0       0
09:17   xyz     1       1
09:18   xyz     0       2
09:19   xyz     0       3
09:20   xyz     0       4
09:21   xyz     1       1
09:22   xyz     0       2
09:23   xyz     0       3
09:24   xyz     0       4
09:25   xyz     0       5
09:26   xyz     0       6

I have tried solution from Cumsum within group and reset on condition in pandas but its not working as expected.

df['G']=df.groupby('symbol').signal.apply(lambda x :(x.diff().ne(0)&x==1)|x==1)
df['MinsSinceSignal']= df.groupby([df.symbol,df.G.cumsum()]).G.apply(lambda x : (~x).cumsum())

There are couple of issues with above code.

  1. It doesn't start with 0.
  2. when signal is 1. calculation starts from next row.

Please help!

Rakesh Nikam
  • 23
  • 3
  • 2
    Hi, welcome to SO! I have a question: Is MinsSinceSignal at 09:15 and 09:16 correct? It's 0, but from my understanding they should be 3 and 4, respectively? – fsimonjetz Jul 29 '21 at 16:33
  • 1
    @fsimonjetz thanks for your comment. I need to group this calculation by symbol. so when a new symbol starts and there is no signal i.e. 0 then MinsSinceSignal should be 0. – Rakesh Nikam Jul 29 '21 at 16:40
  • 1
    @RakeshNikam there you go for your rep points. keep up the post standard! – pyeR_biz Jul 29 '21 at 17:47

1 Answers1

1

Here's a solution. First, group them by symbol and add a cumulative sum:

>>> df['groups'] = df.groupby('symbol').signal.transform(lambda g:g.cumsum())
>>> print(df.head(15))
   symbol  signal  groups
0     abc       0       0
1     abc       0       0
2     abc       0       0
3     abc       1       1
4     abc       0       1
5     abc       0       1
6     abc       0       1
7     abc       1       2
8     abc       0       2
9     abc       1       3
10    abc       1       4
11    abc       0       4
12    xyz       0       0
13    xyz       0       0
14    xyz       1       1

Next, fill a new column, depending on what's in the first row of the group – if the first value is 0, add 0s, else 1s. That's how much we need to add up in the final step.

>>> df['MinsSinceSignal'] = df.groupby(['symbol', 'groups'])['groups'].transform(lambda g: min((g.values[0], 1)))
>>> print(df.head(15))
   symbol  signal  groups  MinsSinceSignal
0     abc       0       0                0
1     abc       0       0                0
2     abc       0       0                0
3     abc       1       1                1
4     abc       0       1                1
5     abc       0       1                1
6     abc       0       1                1
7     abc       1       2                1
8     abc       0       2                1
9     abc       1       3                1
10    abc       1       4                1
11    abc       0       4                1
12    xyz       0       0                0
13    xyz       0       0                0
14    xyz       1       1                1

Finally, another cumsum will generate the desired result:

>>> df['MinsSinceSignal'] = df.groupby(['symbol', 'groups'])['MinsSinceSignal'].cumsum()
>>> df = df.drop('groups', axis=1)
>>> print(df)
   symbol  signal  MinsSinceSignal
0     abc       0                0
1     abc       0                0
2     abc       0                0
3     abc       1                1
4     abc       0                2
5     abc       0                3
6     abc       0                4
7     abc       1                1
8     abc       0                2
9     abc       1                1
10    abc       1                1
11    abc       0                2
12    xyz       0                0
13    xyz       0                0
14    xyz       1                1
15    xyz       0                2
16    xyz       0                3
17    xyz       0                4
18    xyz       1                1
19    xyz       0                2
20    xyz       0                3
21    xyz       0                4
22    xyz       0                5
23    xyz       0                6
fsimonjetz
  • 5,644
  • 3
  • 5
  • 21
  • 1
    Many thanks @fsimonjetz.. your solution works like a charm. I am new here so I am not able to upvote your solution but that is first thing I will do once I get my 15 rep points :) – Rakesh Nikam Jul 29 '21 at 17:28