Performing calculations for specific values in a table defined by certain values from another column (Date)

I have a table with about 100 rows and 4 columns. It looks like this:

T = 1000x4   

    RandomNr          Date               Time                 Status        
    __________    ______________    ________________    _____________________

    8.6947e+11     10-Nov-2021        17:57:55:890        Saving           
    5.6831e+11     10-Nov-2021        17:57:55:890        Saving Successful 
    ...
    4.5643e+11     01-Dec-2021        05:45:34:760        Loading Successful 

For each day, I want to calculate the total online time (duration) by taking the difference between the maximum time and the minimum time.

I started with calculating it for one specific day (e.g., 10-Nov-2021). First, I extracted the data for that specific day and stored it in a new table called 'ExtractedData':

   ExtractedData = T(find(T.Date =='10-Nov-2021'),1:4); 

Then I simply calculated the difference score between the max. and min. Time values:

   duration=max(ExtractedData.Time) - Min(ExtractedData.Time)
   
   duration = 

        duration

         00:09:49

Now, I want to calculate the difference time for each Date in the Table and to store it in a new table. I tried different combinations but nothing worked (e.g. loops, varfun,...). So how can I anonymize the calculations so they are performed automatically for each Date and be stored in another table?

I'm looking forward to your responses! Thanks in advance :)

Answer 1

[KSSV]

Let T be your table.

[c,ia,ib] = unique(T.Date) ; 
N = length(c) ; 
theduration = duration(nan(N,3)) ; 
for i = 1:N
    ExtractedData = T.Time(ib==i);    
    theduration(i)=max(ExtractedData) - Min(ExtractedData) ;
end
thedates = c ;
iwant = table(thedates,theduration) ;

[greenyellow22]

thank you so much, it worked out perfectly! I was wondering whether I can create a table (iwant) which not only includes the calculated duration but also the associated Dates as a column?

[KSSV]

Edited the code.

Thanks is accepting and/or voting the asnwer. :)

[greenyellow22]

thanks!:)

Answer 2

[Steven Lord]

Since you have time-based data I'd store it in a timetable array instead of a table array. If you do, you can use retime to aggregate the data daily.

D = datetime(2021, [11; 11; 11], [10; 10; 14])

D = 3×1 datetime array
   10-Nov-2021
   10-Nov-2021
   14-Nov-2021

I had to change the format of your time strings slightly, replacing the last colon with a period.

T = duration(["17:57:55.890"; "17:57:55.890"; "05:45:34.760"], ...
    'Format', 'hh:mm:ss.SSS')

T = 3×1 duration array
   17:57:55.890
   17:57:55.890
   05:45:34.760

N = [1; 5; 42]

N = 3×1

1 5 42

dateAndTime = D + T

dateAndTime = 3×1 datetime array
   10-Nov-2021 17:57:55
   10-Nov-2021 17:57:55
   14-Nov-2021 05:45:34

TT = timetable(dateAndTime, N)

TT = 3×1 timetable
        dateAndTime         N 
    ____________________    __

    10-Nov-2021 17:57:55     1
    10-Nov-2021 17:57:55     5
    14-Nov-2021 05:45:34    42

TT2 = retime(TT, 'daily', @computeDifferenceWithEmptyGiving0)

TT2 = 5×1 timetable
    dateAndTime    N
    ___________    _

    10-Nov-2021    4
    11-Nov-2021    0
    12-Nov-2021    0
    13-Nov-2021    0
    14-Nov-2021    0

function y = computeDifferenceWithEmptyGiving0(x)
% I can't just use max-min because empty input needs to return a row vector
if isempty(x)
    y = 0;
else
    y = max(x)-min(x);
end
end