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

Question

Mehnaz Khan 2021-4-17

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/804881-how-to-find-the-sum-of-duration-if-the-consecutive-row-elements-are-the-same

评论： Mehnaz Khan 2021-4-19

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 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Stephan 2021-4-17

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/804881-how-to-find-the-sum-of-duration-if-the-consecutive-row-elements-are-the-same#answer_677656

编辑：Stephan 2021-4-17

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.

6 个评论
显示 4更早的评论隐藏 4更早的评论

Stephan 2021-4-19

在 MATLAB Online 中打开

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