MATLAB: Get daily max values from timetable keeping the other variables from ‘maxed value’ row.

retimetimetable

I am working with a timetable with values of flow [m³/h] obtained approximately every 10 minutes over a period of a year. I would like to get the maximum value of every day for that flow variable mantaining the other variables assigned to that value. I have tried to use retime, but it only returned me the daily maximum value of the flow variable, without adding the remaining others.
Please note that grouping does not seem to work as my values might be missing days (I tried and got this error: ERROR: For N groups, every integer between 1 and N must occur at least once in the vector of group numbers.)
Here I print some rows of the table
>> B3_bruto(1:50:600,3:7)
ans =
  12×5 timetable
           Time            PRI_IN    PRI_OUT    SEC_IN    SEC_OUT    CAUDAL_XARXA
    ___________________    ______    _______    ______    _______    ____________
    2018-12-14 15:10:20     93.12     70.69      58.48     67.81         7.884   
    2018-12-14 18:00:20     92.95     71.57      57.44     68.16        19.458   
    2018-12-17 07:44:23    93.149    74.216     56.863    68.169          37.8   
    2018-12-17 10:30:20     92.83     71.61      60.22     68.25        12.942   
    2018-12-17 13:20:42    93.112    70.481     57.175     67.75          15.3   
    2018-12-17 16:10:20     92.91    71.577     56.518     68.04        21.468   
    2018-12-17 18:55:31     92.72    72.137     56.368    67.874          26.1   
    2018-12-18 08:40:31    88.718    72.852     55.649    68.029          46.8   
    2018-12-18 11:30:20     88.08     71.16      55.84     67.77          33.3   
    2018-12-18 14:15:31    86.547    70.107     57.153    67.842          21.6   
    2018-12-18 17:00:31     87.51    70.328     56.403    68.152          23.4   
    2018-12-18 19:50:20     92.82     71.45      55.75     68.03        21.567
I would like to obtain the result like this, maximizing the 'CAUDAL_XARXA' variable:
    ans =
  12×5 timetable
           Time            PRI_IN    PRI_OUT    SEC_IN    SEC_OUT    CAUDAL_XARXA
    ___________________    ______    _______    ______    _______    ____________
  
    2018-12-14 18:00:20     92.95     71.57      57.44     68.16        19.458   
    2018-12-17 18:55:31     92.72    72.137     56.368    67.874          26.1   
    2018-12-18 08:40:31    88.718    72.852     55.649    68.029          46.8
——————————————————————————————————————————————————————-
So far, the code I used to calculate was this:
%% First Method
    daily_test = retime(test(:,{'CAUDAL_XARXA'}),'daily','max');
    % Just the values of CAUDAL_XARXA, not the other values
    % it also returns the time as 00:00:00, I would like the exxact time of the max value
    daily_test.Time.Format = 'yyyy-MM-dd';
    
    daily_test =
  21×1 timetable
       Time       CAUDAL_XARXA
    __________    ____________
    2018-12-14       19.458   
    2018-12-15          NaN   
    2018-12-16          NaN   
    2018-12-17         37.8   
    2018-12-18         46.8   
    2018-12-19         21.6   
    2018-12-20         83.7   
    2018-12-21         31.5   
    2018-12-22          NaN   
    2018-12-23          NaN   
    2018-12-24          NaN   
    2018-12-25          NaN   
    2018-12-26          1.8   
    2018-12-27          NaN   
    2018-12-28          NaN   
    2018-12-29          NaN   
    2018-12-30          NaN   
    2018-12-31          NaN   
    2019-01-01          NaN   
    2019-01-02       2.0488   
    2019-01-03        3.015   
%% Second Method
    [bin, bindates] = discretize(test.Time, 'day');      
    [daterow, ~, group] = unique(bin);
    dates = bindates(daterow);
    [maxconc, row] = splitapply(@max, test.CAUDAL_XARXA, group);
    dailymax = timetable(maxconc, test.Time(row),'RowTimes', dates, 'VariableName', {'CAUDAL_XARXA', 'ActualTime'});
    % row does counts the index starting from 0 for each group instead of the
    % global timetable
    dailymax.Time.Format = 'dd/MM/yyyy';
    dailymax.ActualTime.Format = 'HH:mm';
    
    dailymax =
  9×2 timetable
       Time       CAUDAL_XARXA    ActualTime
    __________    ____________    __________
    14/12/2018       19.458         18:00   
    17/12/2018         37.8         15:10   
    18/12/2018         46.8         15:10   
    19/12/2018         21.6         15:10   
    20/12/2018         83.7         15:10   
    21/12/2018         31.5         15:10   
    26/12/2018          1.8         15:10   
    02/01/2019       2.0488         18:00   
    03/01/2019        3.015         15:10   
%% Third method (ERROR: For N groups, every integer between 1 and N must occur at least once in the vector of group numbers.)
    daygroup = discretize(test.Time, 'day');                        %find which group each row belongs to
    [~, grouprow] = splitapply(@max, test.CAUDAL_XARXA, daygroup);  % find location of max within the group
The file I am working with is attached as 'test.mat'.
I am new to Matlab programming as I am using it for my thesis.
Thank you in advance!

Best Answer

OK, just use the "deadahead" solution...
ttMAX=varfun(imax,ttbruto,'InputVariables','CAUDAL_XARXA','GroupingVariables','DOY'); % get index to max row in group
ttDailyMaxFlow=[];                                                                     % empty accumulator for results
for i=1:height(ttMAX)                                       % for each group (day) in ttMAX
  ttTMP=ttbruto(ttbruto.DOY==ttMAX.DOY(i),1:6);             % select the subset table so indexing from 1 works
  ttDailyMaxFlow=[ttDailyMaxFlow;ttTMP(ttMAX.imax_CAUDAL_XARXA(i),1:5)]; % accumulate rows in the output ttable
end
results in for your small initial dataset
>> ttDailyMaxFlow
ttDailyMaxFlow =
  3×5 timetable
            Time            PRI_IN    PRI_OUT    SEC_IN    SEC_OUT    CAUDAL_XARXA
    ____________________    ______    _______    ______    _______    ____________
    14-Dec-2018 18:00:20     92.95     71.57      57.44     68.16        19.458   
    17-Dec-2018 07:44:23    93.149    74.216     56.863    68.169          37.8   
    18-Dec-2018 08:40:31    88.718    72.852     55.649    68.029          46.8   
>>
One could compute the index in the overall table by keeping track of the number of elements in each group and doing the arithmetic to add 'em all up and subtract the offset as a more elegant solution.
I'll ponder the alternatives; maybe somebody else won't have the mental block I've got at the moment...
This will only work within a single year; you'll need grouping by year and day of year for multiple years.
Related Question