How to calculate the passed time for each group of row values in the n column in table?

Hi Guys.

I have table with 1 timestamp column and 1 columns with data. I want to calculate the passed time for each group of row values in the n column that begins with value 1 and ends with next first value 3. Here is example:

12:07:50      3 
12:07:52      3  
12:07:54      1 
12:07:59      1 
12:08:04      1 
12:08:07      1 
12:08:09      1 
12:08:12      7 
12:08:16      2
12:08:19      3 
12:08:20      3 
12:08:21      3 
12:09:32      10 
12:09:33      1 
12:09:38      1 
12:09:43      1 
12:09:48      1 
12:09:53      1 
12:09:58      1 
12:10:03      1 
12:10:04      1 
12:10:09      7 
12:10:13      2 
12:10:14      2 
12:10:16      3 
12:10:17      3

The first group of rows begins with value 1 at 12:07:54 and ends with next first value 3 at 12:08:19, so the passed time will be 12:08:19 - 12:07:54 = 25 seconds. Next group of rows begins with value 1 at 12:09:33 and ends with next first value 3 at 12:10:16, so the passed time will be 12:10:16 - 12:09:33 = 43 seconds.

The result should be a new table as follows:

Start End Seconds

12:07:54 12:08:18 25

12:09:33 12:10:15 43

I did not find solution, so any help/suggestion will be appreciated. Thank you in advance.

The csv file is attached and here is code for creating table from csv file:

        % Button pushed function: Import
        function ImportButtonPushed(app, event)
        %% Setup the Import Options and import the data
        opts = delimitedTextImportOptions;

        % Specify range and delimiter
        opts.DataLines = [2, Inf];
        opts.Delimiter = ";";

        % Specify column names and types
        opts.VariableNamesLine = 1;

        % Import the data
        NewTable = readtable("C:\17.12.2021.csv", opts);
  
        HeaderNames=NewTable.Properties.VariableNames;
        NewTable.Timestamp=datetime(NewTable.Timestamp,"Format","MM/dd/yyyy hh:mm:ss a.SSS");
        NewTable.Timestamp = timeofday(NewTable.Timestamp);
        
        app.UITable.Data=NewTable;
        app.UITable.ColumnName=HeaderNames;

Answer 1

[Manish]

Hi ,

I understand that to calculate the passed time for each group of row values in the n column that begins with value 1 and ends with next first value 3.

You can achive this by following below steps:

• Convert timestamp strings into datetime format and organize the data into a table with timestamps and corresponding values.
• Iterate through the data to identify sequences starting with a value of 1 and ending with the next occurrence of 3, calculating the duration between these timestamps.
• Store the start time, end time, and duration of each identified group in arrays, and compile these into a result table for display.

Refer the code sample below:

% Define the data as arrays
timestamps = {'12:07:50', '12:07:52', '12:07:54', '12:07:59', '12:08:04', ...
              '12:08:07', '12:08:09', '12:08:12', '12:08:16', '12:08:19', ...
              '12:08:20', '12:08:21', '12:09:32', '12:09:33', '12:09:38', ...
              '12:09:43', '12:09:48', '12:09:53', '12:09:58', '12:10:03', ...
              '12:10:04', '12:10:09', '12:10:13', '12:10:14', '12:10:16', ...
              '12:10:17'};
values = [3, 3, 1, 1, 1, 1, 1, 7, 2, 3, 3, 3, 10, 1, 1, 1, 1, 1, 1, 1, 1, 7, 2, 2, 3, 3];

% Convert the timestamps to datetime format
timestamps = datetime(timestamps, 'Format', 'HH:mm:ss');

% Create a table from the data
data = table(timestamps', values', 'VariableNames', {'Timestamp', 'Value'});

% Initialize variables to store results
startTimes = [];
endTimes = [];
elapsedSeconds = [];
i = 1;
while i <= height(data)
    if data.Value(i) == 1
        % Start of a potential new group
        for j = i+1:height(data)
            if data.Value(j) == 3
                startTime = data.Timestamp(i);
                endTime = data.Timestamp(j)-seconds(1); % Adjust end time by 1 second
                elapsedTime = seconds(endTime - startTime);
                startTimes = [startTimes; startTime];
                endTimes = [endTimes; endTime];
                elapsedSeconds = [elapsedSeconds; elapsedTime];
                i = j + 1;
                break;
            end
        end
    else
        i = i + 1;
    end
end
% Create a table for the results
resultTable = table(startTimes, endTimes, elapsedSeconds, ...
    'VariableNames', {'Start', 'End', 'Seconds'});
disp(resultTable);

You can integrate this code into your ML app to make it work with the application.

Hope this helps!