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

Question

greenyellow22 2022-5-18

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1721925-performing-calculations-for-specific-values-in-a-table-defined-by-certain-values-from-another-column

回答： 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 :)

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

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

Answer 1

KSSV 2022-5-18

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1721925-performing-calculations-for-specific-values-in-a-table-defined-by-certain-values-from-another-column#answer_966205

编辑：KSSV 2022-5-18

在 MATLAB Online 中打开

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更早的评论

KSSV 2022-5-18

Edited the code.

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

greenyellow22 2022-5-18

thanks!:)

请先登录，再进行评论。

Answer 2

Steven Lord 2022-5-18

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1721925-performing-calculations-for-specific-values-in-a-table-defined-by-certain-values-from-another-column#answer_966410

在 MATLAB Online 中打开

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