How to count the number of times a sequence in a panda (time) series is less than a specific duration

Question

Given a panda series where a value is either {0, +1, -1} and the sequence always starts with a +1 and end with a -1.

For example,

2019-01-02 15:19:00    0.0
2019-01-02 15:20:00    0.0
2019-01-02 15:21:00    1.0       # sequence start
2019-01-02 15:22:00    0.0       # sequence continue, it's only a 0
2019-01-02 15:23:00   -1.0       # sequence finish, let's look for the next +1 value 
2019-01-02 15:24:00    0.0
2019-01-02 15:25:00    1.0       # sequence start
2019-01-02 15:26:00   -1.0       # sequence finish 
2019-01-02 15:27:00    0.0
2019-01-02 15:28:00    0.0
2019-01-02 15:29:00    1.0       # sequence start
2019-01-02 15:30:00   -1.0       # sequence finish 

How do you calculate the total number of sequences where the interval from the "sequence start" and "sequence finish" is less than 5 minutes.

For example,

2019-01-02 15:19:00    0.0
2019-01-02 15:20:00    0.0
2019-01-02 15:21:00    1.0       # sequence start
2019-01-02 15:22:00    0.0
2019-01-02 15:23:00    0.0
2019-01-02 15:24:00    0.0
2019-01-02 15:25:00   -1.0       # sequence finish (sequence start to sequence finish is less than 5 minutes, so increment counter) 
2019-01-02 15:26:00    1.0       # sequence start
2019-01-02 15:27:00    0.0
2019-01-02 15:28:00    0.0
2019-01-02 15:29:00    0.0
2019-01-02 15:30:00    0.0
2019-01-02 15:31:00    0.0
2019-01-02 15:32:00    0.0
2019-01-02 15:33:00    0.0
2019-01-02 15:34:00    0.0
2019-01-02 15:35:00    0.0
2019-01-02 15:36:00    0.0
2019-01-02 15:37:00    0.0
2019-01-02 15:38:00    0.0
2019-01-02 15:39:00    0.0
2019-01-02 15:40:00   -1.0       # sequence finish (longer than 5 minutes, don't increment) 

From the above example, we get a total count:

counter: 1

Answer 1

Here's my idea:

# dataframe example
b = [1,0,0,0,0,0,0-1,1,0,0,-1,0,0,0,0,0,0,1,0,0,-1]
minutes = ['2019-01-02 15:' + str(x) + ':00' for x in range(len(b))]
df = pd.DataFrame({'a': minutes, 'b': b})

# keep only rows which are equal to 1 or -1
df_tmp = df[df['b'] != 0]

# convert date column to datetime if needed
df_tmp['a'] = pd.to_datetime(df['a'])

# create a new column which is the time difference between each row
df_tmp['diff'] = df_tmp['a'].diff().astype('timedelta64[m]')

# keep only the rows equal to -1
df_tmp = df_tmp[df_tmp['b'] == -1]

# count the values which are smaller or equal to 5
(df_tmp['diff'] <= 5).sum()

Answer 2

Using pandas merge_asof can work here:

my_df[['date_hour', 'ind']].head(5)
    date_hour   ind
0   2019-01-02 15:19:00 0
1   2019-01-02 15:20:00 0
2   2019-01-02 15:21:00 1
3   2019-01-02 15:22:00 0
4   2019-01-02 15:23:00 0

starts = my_df[my_df['ind'] == 1][['date_hour', 'ind']]
ends = my_df[my_df['ind'] == -1][['date_hour', 'ind']]
ends['end_date_hour'] = ends['date_hour']
##merging ends to starts by closest date hour
merged_df = pd.merge_asof(starts, ends,on='date_hour', direction='forward',
         suffixes=('_start', '_end'), tolerance=pd.Timedelta(10000, 's'))
##calculating time diff for matching starts ends
merged_df['time_diff'] = merged_df['end_date_hour'] - merged_df['date_hour']
##get total of segments that fit the 5 minutes condition
print(sum((merged_df['time_diff'].dt.seconds / 60) < 5))

Note that the 10000 set for the tolerance param just needs to be big enough.