Weekly retime grouping by ID on large dataset

Hello everyone,

I'm going to calculate the weekly average of daily temperatures and pressure grouping by sensor using retime function. At the moment I'm trying to select each sensor with a loop and then apply the retime function, but I have to calculate nine million rows so I would like to avoid a loop to speed up the calculation

I give an example of input table:

MeasurementTime = datetime({'2015-11-11';'2015-11-12';'2015-11-23';'2015-11-23';'2015-12-04';'2015-12-08';'2015-12-10';'2015-12-11';'2015-12-12'});
Temp = [36.3;38.1;39.3;37.3;39.1;42.3;36.3;38.1;39.3];
Pressure = [29.9;29.1;29.3;30.4;30.3;29.9;30.1;30.6;29.6;];
Sensor = [121;121;143;143;121;143;121;143;143];
SensorState = ["T"; "T"; "T"; "T"; "W"; "T"; "T"; "W"; "W"];

TT = timetable(MeasurementTime,Sensor,SensorState,Temp,Pressure);

I give an example of output table:

    MeasurementTime    Temp    Pressure    Sensor    SensorState
    _______________    ____    ________    ______    ___________

    08-Nov-2015        37.2      29.5      "121"         "T"    
    15-Nov-2015         NaN       NaN      "121"         "T"    
    22-Nov-2015         NaN       NaN      "121"         "T"    
    22-Nov-2015        38.3     29.85      "143"         "T"    
    29-Nov-2015         NaN       NaN      "121"         "T"    
    29-Nov-2015         NaN       NaN      "143"         "T"    
    29-Nov-2015        39.1      30.3      "121"         "W"    
    06-Dec-2015        36.3      30.1      "121"         "T"    
    06-Dec-2015        42.3      29.9      "143"         "T"    
    06-Dec-2015        38.7      30.1      "143"         "W"    

Thanks in advance!

Answer 1

[Cris LaPierre]

Look into groupsummary. You can group by multiple variables.

% groupsummary
MeasurementTime = datetime({'2015-11-11';'2015-11-12';'2015-11-23';'2015-11-23';'2015-12-04';'2015-12-08';'2015-12-10';'2015-12-11';'2015-12-12'});
Temp = [36.3;38.1;39.3;37.3;39.1;42.3;36.3;38.1;39.3];
Pressure = [29.9;29.1;29.3;30.4;30.3;29.9;30.1;30.6;29.6;];
Sensor = [121;121;143;143;121;143;121;143;143];
SensorState = ["T"; "T"; "T"; "T"; "W"; "T"; "T"; "W"; "W"];

TT = timetable(MeasurementTime,Sensor,SensorState,Temp,Pressure);

wklyAvg = groupsummary(TT,["Sensor","MeasurementTime"],["none","week"],'mean',["Temp","Pressure"], 'IncludeMissingGroups',true,'IncludeEmptyGroups',true)

wklyAvg = 10×5 table
    Sensor       week_MeasurementTime       GroupCount    mean_Temp    mean_Pressure
    ______    __________________________    __________    _________    _____________

     121      [08-Nov-2015, 15-Nov-2015)        2           37.2            29.5    
     121      [15-Nov-2015, 22-Nov-2015)        0            NaN             NaN    
     121      [22-Nov-2015, 29-Nov-2015)        0            NaN             NaN    
     121      [29-Nov-2015, 06-Dec-2015)        1           39.1            30.3    
     121      [06-Dec-2015, 13-Dec-2015)        1           36.3            30.1    
     143      [08-Nov-2015, 15-Nov-2015)        0            NaN             NaN    
     143      [15-Nov-2015, 22-Nov-2015)        0            NaN             NaN    
     143      [22-Nov-2015, 29-Nov-2015)        2           38.3           29.85    
     143      [29-Nov-2015, 06-Dec-2015)        0            NaN             NaN    
     143      [06-Dec-2015, 13-Dec-2015)        3           39.9          30.033    

[Gian23]

Hi Cris, sorry for the delay in my answer. I tried your solution and run very fast. Thank you.