Calculate actual time used over a row of time ranges

Hi, i need help deleting parallel time in my table. I have place a picture of a data set which looks like mine in structure. Obviously mine is alot longer

My data is grouped by the variable "CaseID", which sections rows of data into groups. the rows of time overlap sometimes in the groups as seen i group caseid = 1 row 5 & 6. I wish to find the time of not overlapping time. If i just summed up the variable seconds then some of my time would be in parallel to some of the other rows. I wish to remove all parallel time from my calculations. So all the time used by each caseid is serial to each row.

example of how its should be calculated in pseudocode

time used CaseID = if date/time is overlapping, take earlist time entry in "StartDateTime" and latest time entry in EndDateTime.

Hopefully my explanation for the problem is sufficent. if not let me know

Hope you are able to help, thanks.

[Hannes Morgenroth]

Just to make sure, what do you want to do with the overlapping sections? (Assuming you want to "fix" the table, before going further and calculating anything else)

1) Combine them, so row 5&6 would result in Start: 20.00 and End: 23.30 (put that in row 5 and then delete row 6?)

2) Remove the overlap in the first row 5:(20.00-20.30) 6:(20.30-23.30)

3) Remove the overlap in the second row 5:(20.00-23.00) 6:(23.00-23.30)

[Magnus Rasmussen]

option number 1 would be sufficient

Answer 1

[Nihal]

Hi Magnus,

I understand that you want to remove the overlapping time intervals from your sheet. You can utilize the following code as a starting point and customize it based on your requirements:

data = readtable("data.xlsx");
data = table2struct(data);
final = [];
final = [final,data(1)];
for i=2:size(data,1)
    cstartdt = getDateTime(final(end).StartDateTime);
    cenddt   = getDateTime(final(end).EndDateTime);
    startdt = getDateTime(data(i).StartDateTime);
    enddt   = getDateTime(data(i).EndDateTime);
    mylogic = cstartdt<startdt;

    if(startdt>=cenddt || data(i).Caseld ~= final(end).Caseld)
        final = [final,data(i)];

    elseif(startdt>=cstartdt && enddt <= cenddt)
        continue
   
    elseif (startdt<cenddt && cenddt<enddt)
        final(end).EndDateTime = data(i).EndDateTime;
        final(end).Seconds = seconds(enddt-cstartdt);
    end
    
end
final %% will contain the output which you need
function myDatetime = getDateTime(data)
    myDatetime = datetime(data,'InputFormat','dd/MM/yyyy HH.mm');
end

The code reads data from an Excel file and converts it into a struct array. It then iterates through the data, comparing the dates and conditions for each element. If a condition is met, it either adds the element to the final array, extends the end date of the previous element, or ignores the current element. The final array is displayed at the end. The getDateTime function converts a date string to a datetime object.

Hope this helps.

[Peter Perkins]

There are probably better ways to do this.

First, read the timestamps from the xlsx as datetimes and avoind one-by-one conversion to datetime. Not sure why that is not "just happening" but I don't have the spreadsheet and I don't know what version of MATLAB you are on.

Second, there's no need to convert to a struct array. In fact, if you change things like data(i).Caseld to data.Caseld(i), the code willl look very similar. If the issue is performace, there are better ways.

Next, this is likely best done with a grouped calculation using rowfun.

Finally, within each group, it should be possible to compare StartTimes(1:end-1) to EndTimes(2:end) to find overlaps, and merge rows. There's probably no need to loop over every row of the table.