Using retime command for daily minimum when there are two variables

I'm using the 'retime' command to return the daily minimum for a series of daily readings. Each reading of the multiple daily readings has t[w]o variables which relate to them, Height and Tide. I am trying to find the daily minimum height for each day and its corresponding tide value. So far my code returns the min Height and Tide for each day, has anyone encountered this problem before and have a solution?

filename_LT = 'tidal_data_jtide.txt';
TD = readtable(filename_LT);  
DT1 = datetime([TD.Var1]);

DT2 = cell2mat(TD.Var2);
DT2_1 = DT2(:,1);
DT2_2 = DT2(:,2);
DT2_3 = DT2(:,4);
DT2_4 = DT2(:,5);
DT2_5 = DT2(:,7);
DT2_6 = DT2(:,8);
DT3 = strcat(DT2_1,DT2_2,DT2_3,DT2_4,DT2_5,DT2_6);
DT4 = str2num(DT3);
TD=table(DT1,TD.Var5,Tide_date_time, ...          
    'VariableNames',{'Time','Height','Tide'});

TD_timetable = table2timetable(TD);
LT_data = retime(TD_timetable,'daily','min');

[dpb]

I thought we had already solved this in a previous Q?, hadn't we?

There is no "problem", retime is doing precisely what it is documented to do; return the minimum of the columns for the grouping selection -- the min height on 10/24 is 0.98 and the "minimum" time is the first observation of the day 01:17:38; those are the reported values.

I don't have a link handy to the previous, but iirc I showed how to use findgroups and splitapply there or you could redefine the timetable here to incorporate date/time as a single variable instead of two; then retime would have only the height over which to operate.

ADDENDUM/ERRATUM

I hadn't actually tried retime at the time (so to speak :) ) I wrote the above; it has the problem that it still only reports the grouped time rather than the specific record/time associated with the function result so the above implication that compounding the date/time would solve the problem was an unwarranted presumption on my part.

Answer 1

[Gwyn Hennessey]

    filename_LT = 'tidal_data_jtide.txt';
    TD = readtable(filename_LT);  

    DT1=datetime(join([string(TD.Var1) string(TD.Var2)])); 

    Tide_date_time = datenum(DT1);

    TD=table(DT1,TD.Var5,Tide_date_time, ...          % and then make the final 
    table
         'VariableNames',{'Time','Height','Tide'});

    TD_timetable = table2timetable(TD);

    %Groups the days in the timetable - Noting there are an inconsitent number
    %of samples per day
    [group_days, days] = discretize(TD_timetable.Time, 'day');

    %Finds the minimum low tide height for each day, and the index within the 
    day group
    [Daily_LT_Height, groupRow] = splitapply(@min, TD_timetable.Height, 
    group_days);

    %Find the offset to get the correct index within the full timetable
    DaysPerGroup = hist(group_days, max(group_days));
    IdxOffset = cumsum([0,DaysPerGroup]);

    %Find the index with respect to minimum tide heights for the full time table
    Daily_LT_Idx = groupRow + IdxOffset(1:end-1)';

    %Find the corresponding times to minimum tide heights
    LT_times = TD_timetable.Time(Daily_LT_Idx);

    %Find the corresponding tide values to the minimum tide heights
    LT_tides = TD_timetable.Tide(Daily_LT_Idx);

[dpb]

This was the Q? that I answered that was later apparently removed.

I've played with it some more and it seems an oversight there isn't a way for either retime nor splitapply to return the specific record associated with the selected observation.

I think it worthy of an enhancement request; I've been mulling over how best to write one as far as how it should work...

[Sean de Wolski]

@dpb, I didn't see the original but I think I know what you're after. It's straightforward if you pass (1:n)' in as the value and use it as an index. My apologies for the terrible variable names.

T = readtable('patients.dat');
T.Gender = categorical(T.Gender);
[group, uval] = findgroups(T.Gender);
splitapply(@(gidx)minAndIndex(T.Age(gidx),gidx), (1:height(T)).', group)

function mintab = minAndIndex(val, validx)
  [minval, midx] = min(val);    
  mintab = table(minval, validx(midx), 'VariableNames', {'MinVal', 'MinIdx'});
end

[dpb]

Sean, that came to me later as a possibility one could possibly make work but I hadn't actually tried to do so as yet...I'll try to apply it to Gwyn's problem here later on...I do still have the data hanging around.

Answer 2

[dpb]

With appropriate acknowledgement to Sean, a slight rearrangement is a little less verbose in use...with the previous TD table of Date, Time, Height as the three variables for start point

>> [g,grp]=findgroups(TD.Date);                 % 
>> [mn,mix]=splitapply(@(idx) minbygroup(TD.Height(idx),idx),[1:height(TD)].',g);
>> TDminHT=table(grp, TD.Time(mix), mn,'VariableNames',{'Date','Time','MinHeight'});
>> TDminHT(1:10,:)
ans =
  10×3 table
       Date         Time      MinHeight
    __________    ________    _________
    2017-10-24    18:12:08    0.98     
    2017-10-25    18:46:26     1.1     
    2017-10-26    19:24:46    1.21     
    2017-10-27    20:11:53    1.31     
    2017-10-28    21:23:26    1.38     
    2017-10-29    23:12:50    1.34     
    2017-10-30    11:59:34    1.48     
    2017-10-31    00:14:11    1.21     
    2017-11-01    01:00:08    1.04     
    2017-11-02    01:42:08    0.84     
>> 

function [minval,midx] = minbygroup(val, validx)
  % return min value of val, index in input list vector (typically 1:length(val))
  [minval, midx] = min(val);    
  midx = validx(midx);
end

This uses the facility of splitapply to return multiple outputs so have the two variables directly rather than then referencing inside a table to retrieve to build the final desired table.

I still think the enhancement to splitapply to be able to do this automagically on user request is worth serious investigation; it's too common a type of analysis to require the user to be so creative in thinking of how to get the obvious result.

At a bare minimum it deserves to be an example in the documentation of use for the type of problem.

As a side note, I find the inconsistent formatting for the height data disconcerting...just irritating to look at; reminds me of the same problem with a default axes where the tick labels are inconsistent at origin; it's just tacky and takes unnecessary time and effort to clean up for presentation and it's embarrassing to show in public as is...

[Sean de Wolski]

I put in an enhancement request citing this thread.

[dpb]

Thank you for taking the initiative...just for the record the initial thread including a follow-up discussion on this subject is at Answers411542