Calculate minutes average values

Hi, everyone.

As attached is my observations data for 24 hours from different transmitter (PRN) . The data here are from 1/1/2014 to 31/1/2014 resulting in thousands of row. From this excel (refer attachment), I need to extract 1-minute and 5-min averages for VTEC, S4 and Sigma for each 31 days.

Therefore, later I can do plotting for the graph but currently stuck here finding shortcut to find averages with thousands of data. Does anyone have any ideas? Thank you in advanced.

From Excel:

Column A - Day Column B- Time Column C- GPS TOW (may ignore)

Column D- PRN

Column E- VTEC Column F- S4 Column G- Sigma

Answer 1

[Cris LaPierre]

I would import the spreadsheet as a table, then convert the day and time into a datetime, convert that to a timetable, and use the retime function to combine the data into 1 minute and 5 minute increments.

data = readtable("Book_Jan.xlsx");

Warning: Column headers from the file were modified to make them valid MATLAB identifiers before creating variable names for the table. The original column headers are saved in the VariableDescriptions property.
Set 'VariableNamingRule' to 'preserve' to use the original column headers as table variable names.

% Convert DAY and TIME into durations
data.DAY = days(data.DAY);
data.TIME = days(data.TIME);
% Create a datetime
data.TimeStamp = datetime(2014,01,01) + data.DAY-1 + data.TIME;
data.TimeStamp.Format = "MM-dd-yy HH:mm";

% Convert the table to a timetable
dataTT = table2timetable(data,"RowTimes","TimeStamp");

% Use retime to average the data into 1 and 5 minute increments.
dataTT1min = retime(dataTT(:,["VTEC" "S4" "Sigma"]),"minutely","mean")

dataTT1min = 44636x3 timetable
      TimeStamp        VTEC        S4        Sigma  
    ______________    ______    ________    ________

    01-01-14 00:04    9.4936    0.051653    0.037328
    01-01-14 00:05    15.034    0.053267    0.044752
    01-01-14 00:06    10.191      0.0471    0.037384
    01-01-14 00:07    12.139    0.071698    0.045127
    01-01-14 00:08       NaN         NaN         NaN
    01-01-14 00:09    15.691    0.054704    0.043895
    01-01-14 00:10    3.8546    0.039522    0.035422
    01-01-14 00:11    13.002    0.068763    0.040811
    01-01-14 00:12    15.675    0.090359    0.045485
    01-01-14 00:13       NaN         NaN         NaN
    01-01-14 00:14    10.517    0.044855    0.038009
    01-01-14 00:15    12.454    0.053156     0.03739
    01-01-14 00:16    10.704    0.052649    0.036479
    01-01-14 00:17    2.0486     0.05072    0.049246
    01-01-14 00:18    8.1786    0.041343    0.036154
    01-01-14 00:19       NaN         NaN         NaN

dataTT5min = retime(dataTT(:,["VTEC" "S4" "Sigma"]),"regular","mean","TimeStep",minutes(5))

dataTT5min = 8928x3 timetable
      TimeStamp        VTEC        S4        Sigma  
    ______________    ______    ________    ________

    01-01-14 00:00    9.4936    0.051653    0.037328
    01-01-14 00:05    12.902    0.058996    0.042836
    01-01-14 00:10     11.94    0.065468     0.04091
    01-01-14 00:15    9.2135     0.04967    0.038829
    01-01-14 00:20     6.063    0.046863    0.036622
    01-01-14 00:25    8.6923    0.084346    0.038214
    01-01-14 00:30    14.037    0.067668    0.036141
    01-01-14 00:35    15.687    0.069287    0.044902
    01-01-14 00:40    16.367     0.06382     0.04569
    01-01-14 00:45    14.388    0.046352    0.037041
    01-01-14 00:50    16.051    0.050894    0.031428
    01-01-14 00:55    15.175    0.048557    0.032849
    01-01-14 01:00    19.772    0.066493     0.03821
    01-01-14 01:05    17.465    0.076747    0.045897
    01-01-14 01:10    19.234    0.062305    0.039331
    01-01-14 01:15    17.724    0.055996    0.039595

[Ann]

Thank you, sir!

It works like a charm and I really appreciate that!