Duration of multiple events from a datetime column in Python

Question

I have the below sample data (multiple_sensors.csv) from multiple motion sensors:

sensorid,date_time,value
303,2012-06-25 11:15:35,0
404,2012-06-25 11:15:35,0
101,2012-06-25 11:15:35,0
202,2012-06-25 11:15:35,0
303,2012-06-25 11:15:36,0
404,2012-06-25 11:15:36,0
101,2012-06-25 11:15:36,0
202,2012-06-25 11:15:36,1
303,2012-06-25 11:15:37,0
404,2012-06-25 11:15:37,0
101,2012-06-25 11:15:37,0
202,2012-06-25 11:15:37,1
303,2012-06-25 11:15:38,0
404,2012-06-25 11:15:38,0
101,2012-06-25 11:15:38,0
202,2012-06-25 11:15:38,0
303,2012-06-25 11:15:39,0
404,2012-06-25 11:15:39,1
101,2012-06-25 11:15:39,0
202,2012-06-25 11:15:39,0
303,2012-06-25 11:15:40,0
404,2012-06-25 11:15:40,1
101,2012-06-25 11:15:40,0
202,2012-06-25 11:15:40,0
303,2012-06-25 11:15:41,1
404,2012-06-25 11:15:41,0
101,2012-06-25 11:15:41,0
202,2012-06-25 11:15:41,0
303,2012-06-25 11:15:42,1
404,2012-06-25 11:15:42,0
101,2012-06-25 11:15:42,0
202,2012-06-25 11:15:42,0
303,2012-06-25 11:15:43,1
404,2012-06-25 11:15:43,0
101,2012-06-25 11:15:43,0
202,2012-06-25 11:15:43,0
303,2012-06-25 11:15:44,0

I need to return id and duration of each motion sensor event in order of occurrence (see expected_output.png). The value column determines whether a motion is triggered or not (1 - means motion triggered, 0 - means no motion) and date_time column indicates when the motion started or ended.

For now I managed to extract the id and duration using a single motion sensor (single_sensor.csv) below (see single_sensor_output.png).

sensorid,date_time,value
202,2012-06-25 00:01:07,0
202,2012-06-25 00:01:08,1
202,2012-06-25 00:01:09,1
202,2012-06-25 00:01:10,0
202,2012-06-25 00:02:12,0
202,2012-06-25 00:02:13,1
202,2012-06-25 00:02:14,1
202,2012-06-25 00:02:15,1
202,2012-06-25 00:02:16,0
202,2012-06-25 00:03:40,0
202,2012-06-25 00:03:41,1
202,2012-06-25 00:03:42,1
202,2012-06-25 00:03:43,1
202,2012-06-25 00:03:44,0
202,2012-06-25 00:05:11,0
202,2012-06-25 00:05:12,1
202,2012-06-25 00:05:13,1
202,2012-06-25 00:05:14,0
202,2012-06-25 00:06:19,0
202,2012-06-25 00:06:20,1
202,2012-06-25 00:06:21,1
202,2012-06-25 00:06:22,0

For my code involving the single sensor I followed the example here (Calculate duration between events with pandas)

import pandas as pd
import numpy as np
from pandas import read_csv
from datetime import datetime
from datetime import timedelta

data_time_format = '%Y-%m-%d %H:%M:%S'

df = read_csv('single_sensor.csv')
df['date_time'] = pd.to_datetime(df['date_time'], format=data_time_format)

a = (df['value'] != 1).cumsum().mask(df['value'] == 1)
df['value group'] = a.bfill()

df_final = df.groupby('value group').filter(lambda x: set(x['value']) == set([1,0]))\
           .groupby('value group')['date_time'].agg(['first','last'])\
           .rename(columns={'first':'start','last':'end'})\
           .reset_index()

df_final['id'] = df['sensorid']
df_final['duration'] = df_final['end'].values - df_final['start']
df_final['duration'] = df_final['duration'].dt.total_seconds().astype(int)
print(df_final)

How can I extend this to achieve my expected output using the multiple_sensors.csv

user_13813365 · Answer

For a single sensor:

import pandas as pd
df = pd.read_csv('single_censor.csv')
df['date_time'] = pd.to_datetime(df['date_time'])

# Assume that your data format first value=0 ignore, start value=1 end value=0
selected_rows = df['value'] != df['value'].shift(1)
selected_rows[0] = False

df2 = df[selected_rows].copy()

df2['start'] = df2['date_time']
df2['end'] = df2['date_time'].shift(-1)
df2.drop(['date_time'], axis=1, inplace=True)

df3 = df2[df2['value'] == 1].copy()

df3['duration'] = df3['end'] - df3['start']
df3.drop('value', axis=1, inplace=True)

Output

    sensorid    start   end duration
1   202 2012-06-25 00:01:08 2012-06-25 00:01:10 00:00:02
5   202 2012-06-25 00:02:13 2012-06-25 00:02:16 00:00:03
10  202 2012-06-25 00:03:41 2012-06-25 00:03:44 00:00:03
15  202 2012-06-25 00:05:12 2012-06-25 00:05:14 00:00:02
19  202 2012-06-25 00:06:20 2012-06-25 00:06:22 00:00:02

Multiple Sensors:

import pandas as pd
df = pd.read_csv('multiple_sensors.csv')
df['date_time'] = pd.to_datetime(df['date_time'])
df2 = df.sort_values(['sensorid', 'date_time'])

selected_rows = df2['value'] != df2['value'].shift(1)
selected_rows[0] = False

df3 = df2[selected_rows].copy()
df3['start'] = df3['date_time']
df3['end'] = df3['date_time'].shift(-1)
df3.drop(['date_time'], axis=1, inplace=True)

df4 = df3[df3['value'] == 1].copy()
df4['duration'] = df4['end'] - df4['start']
df4.drop('value', axis=1, inplace=True)
df4.sort_values('start')

Output

    sensorid               start                 end duration
7        202 2012-06-25 11:15:36 2012-06-25 11:15:38 00:00:02
17       404 2012-06-25 11:15:39 2012-06-25 11:15:41 00:00:02
24       303 2012-06-25 11:15:41 2012-06-25 11:15:44 00:00:03

Removing Overlapping Time:

data = [
    (202, pd.to_datetime('2012-06-25 00:11:47'),
     pd.to_datetime('2012-06-25 00:11:49'), 2),
    (404, pd.to_datetime('2012-06-25 00:11:48'),
     pd.to_datetime('2012-06-25 00:11:50'), 2)
]
df = pd.DataFrame(data, columns=['sensor_id', 'start', 'end', 'duration'])

df['end_shift'] = df['end'].shift().fillna(pd.to_datetime('1971-01-01'))
df.loc[0, 'end_shift'] = pd.to_datetime('1971-01-01')
df[df['start'] >= df['end_shift']].drop('end_shift', axis=1)

Output

   sensor_id               start                 end  duration
0        202 2012-06-25 00:11:47 2012-06-25 00:11:49         2

Group durations:

data = [
(202, pd.to_datetime('2020-06-25 00:11:43'), pd.to_datetime('2020-06-25 00:11:45'),2), 
(202, pd.to_datetime('2020-06-25 00:11:47'), pd.to_datetime('2020-06-25 00:11:49'),2),
(404, pd.to_datetime('2020-06-25 00:11:51'), pd.to_datetime('2020-06-25 00:11:54'),3),
(404, pd.to_datetime('2020-06-25 00:11:55'), pd.to_datetime('2020-06-25 00:11:57'),2),
(202, pd.to_datetime('2020-06-25 00:11:58'), pd.to_datetime('2020-06-25 00:12:01'),3),
(202, pd.to_datetime('2020-06-25 00:12:18'), pd.to_datetime('2020-06-25 00:12:21'),3),
(101, pd.to_datetime('2020-06-25 00:12:21'), pd.to_datetime('2020-06-25 00:12:23'),2),
(101, pd.to_datetime('2020-06-25 00:12:32'), pd.to_datetime('2020-06-25 00:12:34'),2),
]
df=pd.DataFrame(data, columns=['sensor_id', 'start', 'end', 'duration'])

df['id'] = df['sensor_id'].shift(-1)
df['cumsum'] = df['duration'].cumsum()
df2 = df[df['id'] != df['sensor_id']].copy()
df2['duration2'] = df2['cumsum'] - df2['cumsum'].shift().fillna(0)
df2[['sensor_id', 'duration2']]

Output

   sensor_id  duration2
1        202        4.0
3        404        5.0
5        202        6.0
7        101        4.0

The requirements is not clear from the beginning. All original calculated durations are thrown away and the new durations are recalculated. It would be better if the requirements were clear. The solution would be shorted.

data = [
(202, pd.to_datetime('2020-06-25 00:11:43'), pd.to_datetime('2020-06-25 00:11:45'),2), 
(202, pd.to_datetime('2020-06-25 00:11:47'), pd.to_datetime('2020-06-25 00:11:49'),2),
(404, pd.to_datetime('2020-06-25 00:11:51'), pd.to_datetime('2020-06-25 00:11:54'),3),
(404, pd.to_datetime('2020-06-25 00:11:55'), pd.to_datetime('2020-06-25 00:11:57'),2),
(202, pd.to_datetime('2020-06-25 00:11:58'), pd.to_datetime('2020-06-25 00:12:01'),3),
(202, pd.to_datetime('2020-06-25 00:12:18'), pd.to_datetime('2020-06-25 00:12:21'),3),
(101, pd.to_datetime('2020-06-25 00:12:21'), pd.to_datetime('2020-06-25 00:12:23'),2),
(101, pd.to_datetime('2020-06-25 00:12:32'), pd.to_datetime('2020-06-25 00:12:34'),2),
]
df=pd.DataFrame(data, columns=['sensor_id', 'start', 'end', 'duration'])

df['id1'] = df['sensor_id'].shift(-1)
df['id2'] = df['sensor_id'].shift(1)

df2 = df[df['id1'] != df['sensor_id']].copy().reset_index()
df2['start'] = df[df['id2'] != df['sensor_id']].reset_index()['start']

df2['duration'] = df2['end'] - df2['start']
df2.drop(['id1', 'id2'], axis=1, inplace=True) 
df2

Output

   index  sensor_id               start                 end duration
0      1        202 2020-06-25 00:11:43 2020-06-25 00:11:49 00:00:06
1      3        404 2020-06-25 00:11:51 2020-06-25 00:11:57 00:00:06
2      5        202 2020-06-25 00:11:58 2020-06-25 00:12:21 00:00:23
3      7        101 2020-06-25 00:12:21 2020-06-25 00:12:34 00:00:13

user_6361531 · Answer

IIUC,

Let's try this:

def f(df):
    a = (df['value'] != 1).cumsum().mask(df['value'] == 1)
    df['value group'] = a.bfill()

    df_final = df.groupby('value group').filter(lambda x: set(x['value']) == set([1,0]))\
           .groupby('value group')['date_time'].agg(['first','last'])\
           .rename(columns={'first':'start','last':'end'})\
           .reset_index()
    if df_final.shape[0] == 0:
        return
    df_final['id'] = df['sensorid']
    df_final['duration'] = df_final['end'].values - df_final['start']
    df_final['duration'] = df_final['duration'].dt.total_seconds().astype(int)
    return df_final

df_out = df.groupby('sensorid').apply(f).reset_index().drop(['level_1', 'value group', 'id'], axis=1)
df_out = df_out.sort_values('start')
df_out

Output:

   sensorid               start                 end  duration
0       202 2012-06-25 11:15:36 2012-06-25 11:15:38         2
1       303 2012-06-25 11:15:41 2012-06-25 11:15:44         3
2       404 2012-06-25 11:15:39 2012-06-25 11:15:41         2

Note: This may need a more robust test case. But, using the previous logic in a custom function called by groupby 'sensorid'.

Duration of multiple events from a datetime column in Python

2 Answers