Before and after state transitions

Question

I have a df

   uid       timestamp               event
   34900     2015-01-01 00:00:10     5
   90100     2015-01-07 00:00:00     4
   90100     2015-03-02 00:00:00     9
   34900     2015-01-03 00:00:00     5
   34900     2015-01-01 00:40:00     6
   34900     2015-01-01 00:00:01     2
   90100     2015-03-01 00:07:00     5
   34900     2015-01-04 00:00:09     8
   34900     2015-01-07 00:00:10     2
   90100     2015-01-01 20:00:00     1

It has events (1-9) occurring on some unique ids (34900,90100,..) timestamped as datetime.

I am trying to find what happens before and after an event (event=5) occurs on each unique id. My thinking is to subset the df into each unique and retain the adjacent event for each 5 event that happens.

So I will like to know how to retrieve the following dataframes below from the df above

uid=34900

   timestamp               before      after
   2015-01-01 00:00:10     2           6
   2015-01-03 00:00:00     6           8

uid=90100

   timestamp               before      after
   2015-03-01 00:07:00     4           9

score 0 · Answer 1 · answered Mar 27 '18 at 21:14

0

I just asked the same question yesterday, but for SQL. I was informed about the lead/lag functions. You can find my question here.

Once I knew about lead and lag, it was easy to further my research on the matter. It looks like this question contains the solution you are looking for in Python.

answered Mar 27 '18 at 21:14

Sparrower

25
6

Thanks, I posted the code I used based on your suggestion above. – Ffiirree Mar 30 '18 at 05:04

score 0 · Accepted Answer · answered Mar 30 '18 at 05:03

0

Based on Sparrower suggestion, I sorted the data and used the following to obtain the desired outputs.

df['before'] = df.groupby(['uid'])['event'].shift(1)
df['after']  = df.groupby(['uid'])['event'].shift(-1)
df1 = df[df['event']==5]

answered Mar 30 '18 at 05:03

Ffiirree

9
3

Before and after state transitions

2 Answers2