Find the last sequence of occurance of a value in a time series data in pandas

user_12374654 0
Viewed 257

I have per minute time series data for 10 days with three columns: time stamp, category, alarm. Given a date, I want to groupby category and find the timestamps for which the alarm was set to 1 consecutively before the given date and create a dataframe from this subset.

   'Category'       'Date'              'Alarm'
0      A        1999-11-10 12:01:00        1
1      A        1999-11-10 12:02:00        1
2      A        1999-11-10 12:03:00        1
3      A        1999-11-10 12:04:00        1
4      A        1999-11-10 12:05:00        0
5      A        1999-11-10 12:06:00        0
.
.
.
1101   A        1999-11-13 10:07:00        1
1102   A        1999-11-13 10:08:00        1
1103   A        1999-11-13 10:09:00        1
1104   B        1999-11-23 13:09:00        0
1105   B        1999-11-23 13:10:00        0
1106   B        1999-11-23 13:11:00        1
1107   B        1999-11-23 13:12:00        1
1108   B        1999-11-23 13:13:00        1
1109   B        1999-11-23 13:14:00        0
.
.
.
. 
2110   B        1999-11-26 13:13:00        0
2110   B        1999-11-26 13:14:00        0
2110   B        1999-11-26 13:15:00        1 
2111   B        1999-11-26 13:16:00        1 
2112   B        1999-11-26 13:17:00        1 
2113   B        1999-11-26 13:18:00        1 
2114   B        1999-11-26 13:19:00        1
2115   B        1999-11-26 13:20:00        0
2116   B        1999-11-26 13:21:00        0
2117   C        1999-12-02 17:11:00        1 
.
.    
.

I would like the output to be a new dataframe which has only these rows: 

     'Category'       'Date'              'Alarm'
 1101   A        1999-11-13 10:07:00        1
 1102   A        1999-11-13 10:08:00        1
 1103   A        1999-11-13 10:09:00        1
 2110   B        1999-11-23 13:15:00        1 
 2111   B        1999-11-23 13:16:00        1 
 2112   B        1999-11-23 13:17:00        1 
 2113   B        1999-11-23 13:18:00        1 
 2114   B        1999-11-23 13:19:00        1

i.e, the rows in each group with the last consecutive sequence of 1s in alarm. What I have tried:

df.groupby('category')['alarm'].apply(lambda x: x==1)

But this gives all the rows which have alarm as 1 and not just the last sequence of consecutive 1. Any directions on how to get this is appreciated. Thanks! :)

Edit 1: I just provided a subset of the dataframe.The the last consecutive sequence of 1s can occur any number of days before (e.g, if the timeseries is from Nov 1- Nov 10, for a category the last consecutive 1s could have occurred on nov 2 or nov 8. And the length of the 1s can also vary.

pandas time-series python
2 Answers

Let's try

# convert to datetime type
df.Date = pd.to_datetime(df.Date)

# blocks of consecutive `1`:
s = (1-df.Alarm).cumsum()*df.Alarm

# last group of `1` within Category and day:
last_groups = s.groupby(df.Category).transform('max')

# output:
df[s==last_groups]

Ooutput:

     Category                Date  Alarm
1101        A 1999-11-13 10:07:00      1
1102        A 1999-11-13 10:08:00      1
1103        A 1999-11-13 10:09:00      1
2110        B 1999-11-26 13:15:00      1
2111        B 1999-11-26 13:16:00      1
2112        B 1999-11-26 13:17:00      1
2113        B 1999-11-26 13:18:00      1
2114        B 1999-11-26 13:19:00      1
2117        C 1999-12-02 17:11:00      1
user_42384080 
# Make date into datetime
df['Date'] = pd.to_datetime(df['Date'])
# Get rid of time part
df['Day'] = df['Date'].dt.strftime('%Y-%m-%d')

# Taking rolling sum of Alarms
k = df.groupby('Day')['Alarm'].rolling(3).sum().reset_index()
# Take last set of consecutive 3's per day
k = k[k['Alarm']==3].groupby('Day').last()
# Capture the index of each row in the original data frame
k = k['level_1'].values
# Drop day column
df.drop(columns=['Day'], inplace=True)
# Join the rows together
df = pd.concat([df.loc[x-2:].head(3) for x in k])

Output

 Category                Date  Alarm
6         A 1999-11-10 12:07:00      1  
7         A 1999-11-10 12:08:00      1  
8         A 1999-11-10 12:09:00      1 
15        B 1999-11-23 13:15:00      1 
16        B 1999-11-23 13:16:00      1  
17        B 1999-11-23 13:17:00      1
user_47183500
Related