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

2 views (last 30 days)
greenyellow22
greenyellow22 on 18 May 2022
Answered: Steven Lord on 18 May 2022
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 :)

Accepted Answer

KSSV
KSSV on 18 May 2022
Edited: KSSV on 18 May 2022
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) ;
  3 Comments

Sign in to comment.

More Answers (1)

Steven Lord
Steven Lord on 18 May 2022
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

Products


Release

R2022a

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by