How do I find a daily minimum value using retime when I have categorial data in the table?

I have a table with three columns as shown in the below snapshot where column 3 (Material) is categorical. I would like to obtain the daily minimum anomaly time-series and corresponding name of the category from the Material column using the retime function. I can apply retime to estimate minimum but I am not sure how to keep the corresponding row for the 'Material' category. The following code worked but I would like to know how to keep the corrresponding categorical information from column 3.

TT=timetable(time,Anomaly,Material);
subset=TT(:,{'Anomaly'});
TT1=retime(subset,'daily','max');

Answer 1

[Star Strider]

My impression is that ‘TT1’ should have all the other information as well.

If it does not, post the data (or a representative subset) as an attachment here (use the paperclip icon) so we can work with it.

.

[Khandker Ishtiaq]

Thanks much for your comment. TT1 worked but it does not include the 'Material' column. I would like to know the minimum value and corresponing name of the material from Column 3.

I attached the sample data.

[Star Strider]

I am not certain what the problem is, however this:

subset=TT(:,{'Anomaly'});
TTd = retime(TT,'daily');
TT1 = retime(subset, 'daily','max');
Lv = isfinite(TT1.Anomaly);
TTr = [TT1(Lv,:) TTd(Lv,2)];

produces:

TTr =

  27×2 timetable

        DT         Anomaly     Material  
    ___________    _______    ___________

    07-Dec-2019         0     <undefined>
    09-Dec-2019    -0.525     <undefined>
    17-Dec-2019    -0.175     <undefined>
    19-Dec-2019     -7.85     Plasctic   
    21-Dec-2019     3.025     <undefined>
    22-Dec-2019     -0.35     <undefined>
    25-Dec-2019    -1.625     <undefined>
    01-Jan-2020      1.45     <undefined>
    03-Jan-2020      0.75     <undefined>
    05-Jan-2020       6.9     <undefined>
    11-Jan-2020    -0.475     <undefined>
    12-Jan-2020       1.5     <undefined>
    15-Jan-2020       1.8     <undefined>
    16-Jan-2020      -2.7     <undefined>
    18-Jan-2020     0.125     <undefined>
    25-Jan-2020      -0.1     <undefined>
    28-Jan-2020     2.275     <undefined>
    02-Feb-2020     2.575     <undefined>
    06-Feb-2020      1.45     <undefined>
    08-Feb-2020     1.325     <undefined>
    14-Feb-2020    -2.725     <undefined>
    16-Feb-2020     3.925     <undefined>
    18-Feb-2020     -2.95     <undefined>
    20-Feb-2020    -4.025     <undefined>
    21-Feb-2020       2.1     <undefined>
    25-Feb-2020      1.95     <undefined>
    29-Feb-2020     0.825     <undefined>

I had hoped that ‘TTd’ (the daily summary with all the other information) would also return the material (and it did in one instance), howeever it dod not in any of the others. In any event, creating the logical vector ‘Lv’ permitted the isolation of days where ‘Anomaly’ had a maximum. (There are no values for som days.) It woiuld seem that every maximun value would have a ‘Material’ associated with it, however that does not appear to be the situaiton.

So, I went for:

Lv1 = ismember([fix(datenum(TT.DT)) TT.Anomaly], [fix(datenum(TT1.DT)) TT1.Anomaly], 'rows');

and:

TTr = sortrows(TT(Lv1,:));

produced:

TTr =

  27×2 timetable

             DT             Anomaly    Material
    ____________________    _______    ________

    07-Dec-2019 17:00:00         0     Plasctic
    09-Dec-2019 16:00:00    -0.525     Plasctic
    17-Dec-2019 17:00:00    -0.175     Plasctic
    19-Dec-2019 00:00:00     -7.85     Plasctic
    21-Dec-2019 14:00:00     3.025     Metal   
    22-Dec-2019 20:00:00     -0.35     Metal   
    25-Dec-2019 08:00:00    -1.625     Metal   
    01-Jan-2020 17:00:00      1.45     Plasctic
    03-Jan-2020 15:00:00      0.75     Plasctic
    05-Jan-2020 16:00:00       6.9     Plasctic
    11-Jan-2020 05:00:00    -0.475     Metal   
    12-Jan-2020 16:00:00       1.5     Plasctic
    15-Jan-2020 09:00:00       1.8     Metal   
    16-Jan-2020 18:00:00      -2.7     Metal   
    18-Jan-2020 12:00:00     0.125     Plasctic
    25-Jan-2020 16:00:00      -0.1     Metal   
    28-Jan-2020 14:00:00     2.275     Plasctic
    02-Feb-2020 17:00:00     2.575     Metal   
    06-Feb-2020 19:00:00      1.45     Metal   
    08-Feb-2020 14:00:00     1.325     Metal   
    14-Feb-2020 18:00:00    -2.725     Plasctic
    16-Feb-2020 11:00:00     3.925     Plasctic
    18-Feb-2020 07:00:00     -2.95     Plasctic
    20-Feb-2020 08:00:00    -4.025     Metal   
    21-Feb-2020 14:00:00       2.1     Plasctic
    25-Feb-2020 10:00:00      1.95     Plasctic
    29-Feb-2020 16:00:00     0.825     Metal   

This seems to be the desired result.

My apologies for the delay — it took me a few minutes to figure out how to solve this. (The solution is obvious in retrospect, however was not initially.)

.

[Khandker Ishtiaq]

Thanks a lot.

[Star Strider]

As always, my pleasure!

That is an interesting problem!

.

Answer 2

[Peter Perkins]

SS's answer seems to have worked, and I'm late to the party, but I always like to provide an alternative to using datenum, and rowfun is lonely. So here's another way:

>> load sample.mat
>> sampledata = sortrows(table2timetable(sampledata));

Ordinarily, at this point you might turn to retime, but that works on each var separately. So the thing to use is rowfun, which can work on multiple vars, and allows grouping. (SS used max, I've used min).

>> sampledata.Day = dateshift(sampledata.DT,"start","day")
sampledata =
  54×3 timetable
             DT                   Anomaly          Material        Day    
    ____________________    ___________________    ________    ___________
    07-Dec-2019 17:00:00                      0    Plasctic    07-Dec-2019
    09-Dec-2019 16:00:00     -0.525000000000006    Plasctic    09-Dec-2019
    09-Dec-2019 21:00:00      -4.27500000000001    Plasctic    09-Dec-2019
[snip]
    25-Feb-2020 10:00:00                   1.95    Plasctic    25-Feb-2020
    29-Feb-2020 16:00:00      0.825000000000003    Metal       29-Feb-2020
    29-Feb-2020 20:00:00                  -2.75    Metal       29-Feb-2020
>> rowfun(@myFun,sampledata,"GroupingVariable","Day","OutputVariableNames",["Anomaly" "Material"])
ans =
  27×4 timetable
             DT                 Day        GroupCount          Anomaly          Material
    ____________________    ___________    __________    ___________________    ________
    07-Dec-2019 17:00:00    07-Dec-2019        1                           0    Plasctic
    09-Dec-2019 21:00:00    09-Dec-2019        2           -4.27500000000001    Plasctic
    17-Dec-2019 17:00:00    17-Dec-2019        1          -0.174999999999997    Plasctic
[snip]
    21-Feb-2020 11:00:00    21-Feb-2020        3                         1.5    Metal   
    25-Feb-2020 03:00:00    25-Feb-2020        2                      -5.575    Plasctic
    29-Feb-2020 20:00:00    29-Feb-2020        2                       -2.75    Metal   

where

    function [aMin,mMin] = myFun(a,m)
    [aMin,iMin] = min(a);
    mMin = m(iMin);
    end