3
$\begingroup$

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
$\endgroup$
  • $\begingroup$ it's unclear what you are asking, please clarify with a better example $\endgroup$ – pcko1 Jul 27 '19 at 19:40
  • $\begingroup$ @pcko1 I tried to clarify. Let me know if it's more clear, or which parts are unclear. Thanks! $\endgroup$ – trienism Jul 28 '19 at 2:54
2
$\begingroup$

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()
$\endgroup$
2
$\begingroup$

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.

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.