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

3 次查看（过去 30 天）
greenyellow22
greenyellow22 2022-5-18
回答： Steven Lord 2022-5-18
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 :)

请先登录，再回答此问题。

采纳的回答

KSSV
KSSV 2022-5-18
编辑：KSSV 2022-5-18
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 个评论
显示 1更早的评论

请先登录，再进行评论。

更多回答（1 个）

Steven Lord
Steven Lord 2022-5-18
Ran in:
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

类别

在 Help Center 和 File Exchange 中查找有关 Dates and Time 的更多信息

标签

产品


版本

R2022a

Community Treasure Hunt

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

Start Hunting!

Translated by