Group data based on contiguous blocks of time

Consider data set as follows:

Time Col1 Col2 Col3

09:00:00 A B C

09:00:01 A B C

09:00:02 A B C

09:00:03 R B C

16:00:00 A B C

16:00:01 A B C

20:00:00 A B C

Data between 09:00:00 to 09:00:02 is considered as one event since time increment in 1 sec and Col 1 through Col 3 are same. I am trying to find start and end time for each event in a given set of data.

Desired output:

StartTime End Time Col1 Col2 Col3

09:00:00 09:00:02 A B C

09:00:03 09:00:03 R B C

16:00:00 16:00:01 A B C

20:00:00 20:00:00 A B C

How can I implement this (without using Statistics toolbox)?

Answer 1

[Adam Danz]

This transforms input table T to the start-end table Tnew.

% Create input table
T = table(duration(9,0,0)+seconds([0:3,25200,25201,39600]'), ...
    {'A' 'A' 'A' 'R' 'A' 'A' 'A'}', {'B' 'B' 'B' 'B' 'B' 'B' 'B'}',...
    {'C' 'C' 'C' 'C' 'C' 'C' 'C' }','VariableNames', {'Time','Col1','Col2','Col3'}); 
% T = table2timetable(T); % If you're using a timetable

% Identify groups of identical rows of Col data
% Every 'false' is a start of a new group of identical rows.
[~,~,rowNum] = unique(cell2mat([T.Col1, T.Col2, T.Col3]),'rows'); 
colGroups = [false; diff(rowNum)==0]; 

% Identify groups of time separated by exactly 1 second.
% Every 'false' is the start of a new temporal group 
timeGroups = [false; diff(T.Time) == seconds(1)]; 

% Combine the groups to identify rows that are separated by exactly 
% 1 second and have identical Col rows of data.
finalGroups = timeGroups & colGroups; 
groupID = cumsum(~finalGroups); 

% Compute the new start and stop times & isolate the unique col rows.
startTimes = arrayfun(@(i)min(T.Time(groupID == i)),unique(groupID));
endTimes = arrayfun(@(i)max(T.Time(groupID == i)),unique(groupID)); 
colData = arrayfun(@(j)[T.Col1(j),T.Col2(j),T.Col3(j)],find(~finalGroups),'UniformOutput',false); 

% Produce new table
unqRows = find(~finalGroups); 
Tnew = table(startTimes, endTimes, T.Col1(unqRows), T.Col2(unqRows), T.Col3(unqRows), ...
    'VariableNames', {'StartTime', 'EndTime', 'Col1', 'Col2','Col3'});

View results

Tnew =
  4×5 table
    StartTime    EndTime     Col1     Col2     Col3 
    _________    ________    _____    _____    _____
    09:00:00     09:00:02    {'A'}    {'B'}    {'C'}
    09:00:03     09:00:03    {'R'}    {'B'}    {'C'}
    16:00:00     16:00:01    {'A'}    {'B'}    {'C'}
    20:00:00     20:00:00    {'A'}    {'B'}    {'C'}

[Anila Sasidharan]

Thank you! Works brilliantly

[Adam Danz]

Glad I could help!