How to find the sum of duration if the consecutive row elements are the same?
1 view (last 30 days)
Show older comments
I have a table of datetime Activated and Location
e.g.
'2019-12-08 10:32:26' 'Room 1 '
'2019-12-08 10:33:32' 'Room 2'
'2019-12-08 10:34:35' 'Room 2'
'2019-12-08 10:35:09' ''Room 3'
'2019-12-08 10:36:56' 'Room 3'
'2019-12-08 10:38:24' 'Room 3'
'2019-12-08 10:40:17' 'Room 1'
'2019-12-08 10:43:06' 'Room 1'
'2019-12-08 10:44:28' 'Room 1'
'2019-12-08 10:45:28' 'Room 2'
and I have used diff(table.datetime,1,1) to return a separate duration file of duration spent in each room before the next room is activated
e.g.
'00:01:06'
'00:01:03'
'00:00:34'
'00:01:47'
'00:01:28'
'00:01:53'
'00:02:49'
'00:01:22'
'00:04:02'
'00:07:18'
Now I want to find the duration of time spent in each room, i.e. the start and end time of a repeated train of activations of a single room. E.g. time spent in room 2 would be the duration from 10:33:32 to 10:35:09 (i.e. sum of duration values of same rows in the duration file)
Is there a way to loop through the table and calculate time spent in each room?
0 Comments
Answers (1)
Stephan
on 17 Apr 2021
Edited: Stephan
on 17 Apr 2021
Save the durations you calculated using diff in an additional column so that the durations are correct for every row in your table. I guess the last entry would be NaN or zero or something like that.
6 Comments
Stephan
on 19 Apr 2021
load('activations.mat')
load('rooms.mat')
endTime = [diff(activations); 0] + activations;
T = table(activations, endTime, room)
results in:
T =
30×3 table
activations endTime room
____________________ ____________________ __________
08-Dec-2018 00:08:24 08-Dec-2018 00:10:05 {'Room 1'}
08-Dec-2018 00:10:05 08-Dec-2018 00:13:53 {'Room 1'}
08-Dec-2018 00:13:53 08-Dec-2018 00:15:00 {'Room 1'}
08-Dec-2018 00:15:00 08-Dec-2018 00:25:00 {'Room 1'}
08-Dec-2018 00:25:00 08-Dec-2018 00:29:51 {'Room 2'}
08-Dec-2018 00:29:51 08-Dec-2018 00:30:09 {'Room 2'}
08-Dec-2018 00:30:09 08-Dec-2018 03:03:32 {'Room 1'}
08-Dec-2018 03:03:32 08-Dec-2018 03:06:56 {'Room 1'}
08-Dec-2018 03:06:56 08-Dec-2018 03:08:45 {'Room 1'}
08-Dec-2018 03:08:45 08-Dec-2018 07:21:00 {'Room 1'}
08-Dec-2018 07:21:00 08-Dec-2018 07:23:51 {'Room 1'}
08-Dec-2018 07:23:51 08-Dec-2018 07:26:40 {'Room 1'}
08-Dec-2018 07:26:40 08-Dec-2018 07:28:03 {'Room 2'}
08-Dec-2018 07:28:03 08-Dec-2018 07:28:07 {'Room 2'}
08-Dec-2018 07:28:07 08-Dec-2018 07:29:19 {'Room 3'}
08-Dec-2018 07:29:19 08-Dec-2018 07:30:06 {'Room 3'}
08-Dec-2018 07:30:06 08-Dec-2018 07:30:09 {'Room 3'}
08-Dec-2018 07:30:09 08-Dec-2018 07:32:26 {'Room 2'}
08-Dec-2018 07:32:26 08-Dec-2018 07:33:32 {'Room 4'}
08-Dec-2018 07:33:32 08-Dec-2018 08:20:55 {'Room 4'}
08-Dec-2018 08:20:55 08-Dec-2018 08:21:18 {'Room 1'}
08-Dec-2018 08:21:18 08-Dec-2018 08:23:26 {'Room 4'}
08-Dec-2018 08:23:26 08-Dec-2018 08:23:40 {'Room 4'}
08-Dec-2018 08:23:40 08-Dec-2018 08:24:45 {'Room 1'}
08-Dec-2018 08:24:45 08-Dec-2018 08:25:47 {'Room 1'}
08-Dec-2018 08:25:47 08-Dec-2018 08:26:39 {'Room 1'}
08-Dec-2018 08:26:39 08-Dec-2018 08:29:22 {'Room 4'}
08-Dec-2018 08:29:22 08-Dec-2018 08:32:21 {'Room 4'}
08-Dec-2018 08:32:21 08-Dec-2018 08:32:46 {'Room 4'}
08-Dec-2018 08:32:46 08-Dec-2018 08:32:46 {'Room 1'}
See Also
Categories
Find more on Time Series Events in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!