Resample and Aggregate Data in Timetable

Open Live Script

This example shows how to resample and aggregate data in a timetable. A timetable is a type of table that associates a time with each row. A timetable can store column-oriented data variables that have different data types and sizes, provided that each variable has the same number of rows. With the retime function, you can resample timetable data, or aggregate timetable data into time bins you specify.

Import Timetable

Load a timetable containing weather measurements taken from November 15, 2015, to November 19, 2015. The timetable contains humidity, temperature, and pressure readings taken over this time period.

load outdoors
outdoors(1:5,:)

ans=5×3 timetable
           Time            Humidity    TemperatureF    PressureHg
    ___________________    ________    ____________    __________

    2015-11-15 00:00:24        49          51.3          29.61   
    2015-11-15 01:30:24      48.9          51.5          29.61   
    2015-11-15 03:00:24      48.9          51.5          29.61   
    2015-11-15 04:30:24      48.8          51.5          29.61   
    2015-11-15 06:00:24      48.7          51.5           29.6

Determine if the timetable is regular. A regular timetable is one in which the differences between all consecutive row times are the same. outdoors is not a regular timetable.

TF = isregular(outdoors)

TF = logical
   0

Find the differences in the time steps. They vary between half a minute and an hour and a half.

dt = unique(diff(outdoors.Time))

dt = 3x1 duration
   00:00:24
   01:29:36
   01:30:00

Resample Timetable with Interpolation

Adjust the data in the timetable with the retime function. Specify an hourly time vector. Interpolate the timetable data to the new row times.

TT = retime(outdoors,"hourly","spline");
TT(1:5,:)

ans=5×3 timetable
           Time            Humidity    TemperatureF    PressureHg
    ___________________    ________    ____________    __________

    2015-11-15 00:00:00     49.001        51.298          29.61  
    2015-11-15 01:00:00     48.909        51.467          29.61  
    2015-11-15 02:00:00     48.902         51.51          29.61  
    2015-11-15 03:00:00       48.9          51.5          29.61  
    2015-11-15 04:00:00     48.844        51.498         29.611

Resample Timetable with Nearest Neighbor Values

Specify an hourly time vector for TT. For each row in TT, copy values from the corresponding row in outdoors whose row time is nearest.

TT = retime(outdoors,"hourly","nearest");
TT(1:5,:)

ans=5×3 timetable
           Time            Humidity    TemperatureF    PressureHg
    ___________________    ________    ____________    __________

    2015-11-15 00:00:00        49          51.3          29.61   
    2015-11-15 01:00:00      48.9          51.5          29.61   
    2015-11-15 02:00:00      48.9          51.5          29.61   
    2015-11-15 03:00:00      48.9          51.5          29.61   
    2015-11-15 04:00:00      48.8          51.5          29.61

Aggregate Timetable Data and Calculate Daily Mean

The retime function provides aggregation methods, such as mean. Calculate the daily means for the data in outdoors.

TT = retime(outdoors,"daily","mean");
TT

TT=4×3 timetable
           Time            Humidity    TemperatureF    PressureHg
    ___________________    ________    ____________    __________

    2015-11-15 00:00:00     48.931        51.394         29.607  
    2015-11-16 00:00:00     47.924        51.571         29.611  
    2015-11-17 00:00:00      48.45        51.238         29.613  
    2015-11-18 00:00:00       49.5          50.8          29.61

Adjust Timetable Data to Regular Times

Calculate the means over six-hour time intervals. Specify a regular time step using the "regular" input argument and the TimeStep name-value argument.

TT = retime(outdoors,"regular","mean",TimeStep=hours(6));
TT(1:5,:)

ans=5×3 timetable
           Time            Humidity    TemperatureF    PressureHg
    ___________________    ________    ____________    __________

    2015-11-15 00:00:00       48.9         51.45          29.61  
    2015-11-15 06:00:00       48.9         51.45           29.6  
    2015-11-15 12:00:00     49.025         51.45          29.61  
    2015-11-15 18:00:00       48.9        51.225         29.607  
    2015-11-16 00:00:00       48.5          51.4          29.61

As an alternative, you can specify a time vector that has the same six-hour time intervals. Specify a format for the time vector to display both date and time when you display the timetable.

tv = datetime(2015,11,15):hours(6):datetime(2015,11,18);
tv.Format = "dd-MMM-yyyy HH:mm:ss";
TT = retime(outdoors,tv,"mean");
TT(1:5,:)

ans=5×3 timetable
            Time            Humidity    TemperatureF    PressureHg
    ____________________    ________    ____________    __________

    15-Nov-2015 00:00:00       48.9         51.45          29.61  
    15-Nov-2015 06:00:00       48.9         51.45           29.6  
    15-Nov-2015 12:00:00     49.025         51.45          29.61  
    15-Nov-2015 18:00:00       48.9        51.225         29.607  
    16-Nov-2015 00:00:00       48.5          51.4          29.61