How to find the sum of duration if the consecutive row elements are the same?

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?

Answer 1

[Stephan]

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.

Then have a look at groupsummary. This should solve your problem.

[Mehnaz Khan]

I think it would make more sense to explain what I want my output to look like (ignoring duration measures for now):

Sort of like a timetable format of when a room is activated during the day:

The start time would be when the room was first activated and end time would be the time of activation of the next room.

Datetime Start Datetime End Location

'2019-12-08 10:32:26' '2019-12-08 10:33:32' 'Room 1 '

'2019-12-08 10:33:32' '2019-12-08 10:35:09' 'Room 2'

'2019-12-08 10:35:09' '2019-12-08 10:40:17' 'Room 3'

'2019-12-08 10:40:17' '2019-12-08 10:45:28' 'Room 1'

[Stephan]

can you provide an example of your data?

[Stephan]

As a file...

[Mehnaz Khan]

Sorry about that! Please see attached..

The rooms and datetime data are in separate files

[Stephan]

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'}

[Mehnaz Khan]

Ok great thank you!

So, the next step from this would be to find the start and end time of repeated activations in the same room?

i.e. room 1 is activated 3 times consecutively from 00:08:24 to 00:25:00

room 2 is then activated 2 times consecutively from 00:25:00 to 00:30:09