How to get daily max values of timetable keeping hour, using retime?

Hi,

I have a hourly dataset in a timetable (data, attached as 'data.mat') and I need to take daily maximum values of one variable (TempC). Using retime I get

>> retime(data,'daily','max')

ans =

  458×11 timetable

        Data_CET        DT_100m      q          R         R2        RMSE       SSE      DFE    TempC     WindSpeed_kmh    RainRate_mmh    Rain_mm 
    ________________    _______    ______    _______    _______    _______    ______    ___    ______    _____________    ____________    ________

    01/06/2019 00:00    3.5271     24.942    0.71046    0.50476     1.2498    24.991     16    25.779       7.2908                0       0.011111
    02/06/2019 00:00    3.0652     25.567    0.78203    0.61157     1.3609    29.634     16    25.949       10.935                0              0
    03/06/2019 00:00    3.5115      24.56    0.78381    0.61435     1.1878    22.575     16    25.174       11.117                0              0

and so on. As you can see, time has days with the hour always 00:00. However, I need the hours to be kept, because I don't need only the maximum values, but else the hours at which they occurred. How can I achieve this? I thought to use find function to find the indexes of the values, but unfortunately there are some maximum values that occurred more than one time and so this method can't be applied.

Do you have any other ideas?

Thank you.

Answer 1

[Arun]

Hi Andrea,

I understand that you wish to get daily maximum for a variable value from a ‘timetable’ datatype. The requirement is to have hours value to be kept and “retime” function removes the time at which the maximum value occurs.

These are the steps that can create a table which contains daily maximum values with all the required fields:

1. As the data is in “timetable” datatype and is continues. with no missing value, 24 entries in the table can be considered as a day.
2. Loop through each day.
3. Find the occurrences of maximum “TempC” value for that day.
4. Store the values in a table.
5. Output the desired table.

Here is a sample code to implement the above-mentioned steps:

%convert the timetable into table.
load("data.mat")
datatable = timetable2table(data);
outputTable = table();
%loop through the day to find the time for maximum value of TempC
for i = 1:24:4416
    extractValues = datatable(i:i+23,:);
    maxValue = max(extractValues.TempC);
    rowWithMaxTempC = extractValues.TempC == maxValue;
    inOutputTable = extractValues(rowWithMaxTempC,:);
    %store the value in the table 
    outputTable(end+1,:) = inOutputTable;

end
outputTable

outputTable = 184x12 table
        Data_CET        DT_100m      q          R          R2        RMSE       SSE      DFE    TempC     WindSpeed_kmh    RainRate_mmh    Rain_mm
    ________________    _______    ______    _______    ________    _______    ______    ___    ______    _____________    ____________    _______

    01/06/2019 16:00     1.5607    24.656    0.53374     0.28488    0.53464    4.5735    16     25.779       5.9029             0             0   
    02/06/2019 12:00    0.53103    25.567    0.11345    0.012871     1.0055    16.176    16     25.949       5.9306             0             0   
    03/06/2019 12:00    0.89864    24.527    0.26066    0.067946    0.71961    8.2855    16     25.174       6.9634             0             0   
    04/06/2019 14:00     1.4135    24.567     0.3981     0.15849    0.70424    7.9352    16     25.584       8.2016             0             0   
    05/06/2019 13:00    0.90275    25.558    0.22956    0.052699    0.82753    10.957    16     26.207       6.0951             0             0   
    06/06/2019 13:00    0.52069    25.777     0.2302     0.05299    0.47592     3.624    16     26.152       11.404             0             0   
    07/06/2019 17:00     1.7306     29.24    0.63894     0.40825    0.45047    3.2468    16     30.485       8.7082             0             0   
    08/06/2019 15:00     1.4198    29.647    0.37176     0.13821    0.76655    9.4015    16     30.668       4.6056             0             0   
    09/06/2019 15:00     1.8001    31.597    0.53408     0.28524    0.61609    6.0731    16     32.892       6.6548             0             0   
    10/06/2019 14:00     1.9378    32.412    0.58004     0.33645    0.58837    5.5389    16     33.805       10.561             0             0   
    11/06/2019 17:00      2.342     28.84    0.59329     0.35199    0.68706    7.5527    16     30.524       6.4288             0             0   
    12/06/2019 14:00    0.93115    28.116    0.33107      0.1096    0.57381    5.2682    16     28.785       10.813             0             0   
    13/06/2019 14:00     0.5424    31.926    0.13375    0.017889    0.86894    12.081    16     32.316       4.4081             0             0   
    14/06/2019 14:00     1.2322    34.172    0.28403    0.080675    0.89936    12.942    16     35.059       7.8901             0             0   
    15/06/2019 12:00     1.2628    28.425    0.23241    0.054014     1.1426    20.887    16     29.333       7.7302             0             0   
    16/06/2019 14:00    0.87011    28.732    0.17737     0.03146     1.0438    17.433    16     29.358       8.4592             0             0   

This provides the required data, for daily maximum “TempC” with the time value as well.

For more information related to tables please refer the shared documentation link: https://www.mathworks.com/help/matlab/tables.html

I hope this helps you in resolving the issue.