Combine Timetables and Synchronize Their Data

Open Live Script

You can combine timetables and synchronize their data in a variety of ways. You can concatenate timetables vertically or horizontally, but only when they contain the same row times or timetable variables. Use the synchronize function to combine timetables with different row times and timetable variables. synchronize creates a timetable that contains all variables from all input timetables. It then synchronizes the data from the input timetables to the row times of the output timetable. synchronize can fill in missing elements of the output timetable with missing data indicators, with values copied from their nearest neighbors, or with interpolated values. synchronize also can aggregate timetable data over time bins you specify.

Concatenate Timetables Vertically

Load timetables from openPricesSmall and concatenate them vertically. The timetables are opWeek1 and opWeek2. They contain opening prices for some stocks during the first and second weeks of January 2016.

load openPricesSmall

Display the two timetables.

opWeek1

opWeek1=5×2 timetable
            Time             AAPL       FB  
    ____________________    ______    ______

    08-Jan-2016 09:00:00     98.55     99.88
    07-Jan-2016 09:00:00     98.68     100.5
    06-Jan-2016 09:00:00    100.56    101.13
    05-Jan-2016 09:00:00    105.75    102.89
    04-Jan-2016 09:00:00    102.61    101.95

opWeek2

opWeek2=5×2 timetable
            Time             AAPL       FB  
    ____________________    ______    ______

    14-Jan-2016 09:00:00     97.96     95.85
    13-Jan-2016 09:00:00    100.32    100.58
    12-Jan-2016 09:00:00    100.55        99
    11-Jan-2016 09:00:00     98.97     97.91
    08-Jan-2016 09:00:00     98.55     99.88

Concatenate the timetables. You can concatenate timetables vertically when they have the same variables. The row times label the rows and are not contained in a timetable variable. Note that the row times of a timetable can be out of order and do not need to be regularly spaced. For example, op does not include days that fall on weekends. A timetable also can contain duplicate times. op contains two rows for 08-Jan-2016 09:00:00.

op = [opWeek2;opWeek1]

op=10×2 timetable
            Time             AAPL       FB  
    ____________________    ______    ______

    14-Jan-2016 09:00:00     97.96     95.85
    13-Jan-2016 09:00:00    100.32    100.58
    12-Jan-2016 09:00:00    100.55        99
    11-Jan-2016 09:00:00     98.97     97.91
    08-Jan-2016 09:00:00     98.55     99.88
    08-Jan-2016 09:00:00     98.55     99.88
    07-Jan-2016 09:00:00     98.68     100.5
    06-Jan-2016 09:00:00    100.56    101.13
    05-Jan-2016 09:00:00    105.75    102.89
    04-Jan-2016 09:00:00    102.61    101.95

Concatenate Timetables Horizontally

You also can concatenate timetables horizontally. The timetables must have the same row times and different variables.

Display the timetable opOtherStocks. The timetable has the same row times as opWeek1, but variables for different stocks.

opOtherStocks

opOtherStocks=5×2 timetable
            Time            MSFT     TWTR 
    ____________________    _____    _____

    08-Jan-2016 09:00:00    52.37    20.51
    07-Jan-2016 09:00:00     52.7       21
    06-Jan-2016 09:00:00    54.32    21.62
    05-Jan-2016 09:00:00    54.93    22.79
    04-Jan-2016 09:00:00    54.32    22.64

Concatenate opWeek1 and opOtherStock. The output timetable has one set of row times and the variables from both timetables.

op = [opWeek1 opOtherStocks]

op=5×4 timetable
            Time             AAPL       FB      MSFT     TWTR 
    ____________________    ______    ______    _____    _____

    08-Jan-2016 09:00:00     98.55     99.88    52.37    20.51
    07-Jan-2016 09:00:00     98.68     100.5     52.7       21
    06-Jan-2016 09:00:00    100.56    101.13    54.32    21.62
    05-Jan-2016 09:00:00    105.75    102.89    54.93    22.79
    04-Jan-2016 09:00:00    102.61    101.95    54.32    22.64

Synchronize Timetables and Indicate Missing Data

Load air quality data and weather measurements from two different timetables and synchronize them. The dates of the measurements range from November 15, 2015, to November 19, 2015. The air quality data come from a sensor inside a building, while the weather measurements come from sensors outside.

load indoors
load outdoors

Display the first five lines of each timetable. They contain measurements of different quantities taken at different times.

indoors(1:5,:)

ans=5×2 timetable
           Time            Humidity    AirQuality
    ___________________    ________    __________

    2015-11-15 00:00:24       36           80    
    2015-11-15 01:13:35       36           80    
    2015-11-15 02:26:47       37           79    
    2015-11-15 03:39:59       37           82    
    2015-11-15 04:53:11       36           80

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

Synchronize the timetables. The output timetable tt contains all the times from both timetables. synchronize puts a missing data indicator where there are no data values to place in tt. When both input timetables have a variable with the same name, such as Humidity, synchronize renames both variables and adds both to the output timetable.

tt = synchronize(indoors,outdoors);
tt(1:5,:)

ans=5×5 timetable
           Time            Humidity_indoors    AirQuality    Humidity_outdoors    TemperatureF    PressureHg
    ___________________    ________________    __________    _________________    ____________    __________

    2015-11-15 00:00:24           36               80                49               51.3          29.61   
    2015-11-15 01:13:35           36               80               NaN                NaN            NaN   
    2015-11-15 01:30:24          NaN              NaN              48.9               51.5          29.61   
    2015-11-15 02:26:47           37               79               NaN                NaN            NaN   
    2015-11-15 03:00:24          NaN              NaN              48.9               51.5          29.61

Synchronize and Interpolate Data Values

Synchronize the timetables, and fill in missing timetable elements with linear interpolation. To synchronize on a time vector that includes all times from both timetables, specify "union" for the output times.

ttLinear = synchronize(indoors,outdoors,"union","linear");
ttLinear(1:5,:)

ans=5×5 timetable
           Time            Humidity_indoors    AirQuality    Humidity_outdoors    TemperatureF    PressureHg
    ___________________    ________________    __________    _________________    ____________    __________

    2015-11-15 00:00:24            36                80               49               51.3         29.61   
    2015-11-15 01:13:35            36                80           48.919             51.463         29.61   
    2015-11-15 01:30:24         36.23             79.77             48.9               51.5         29.61   
    2015-11-15 02:26:47            37                79             48.9               51.5         29.61   
    2015-11-15 03:00:24            37            80.378             48.9               51.5         29.61

Synchronize to Regular Times

Synchronize the timetables to an hourly time vector. The input timetables had irregular row times. The output timetable has regular row times with one hour as the time step.

ttHourly = synchronize(indoors,outdoors,"hourly","linear");
ttHourly(1:5,:)

ans=5×5 timetable
           Time            Humidity_indoors    AirQuality    Humidity_outdoors    TemperatureF    PressureHg
    ___________________    ________________    __________    _________________    ____________    __________

    2015-11-15 00:00:00             36               80               49             51.299         29.61   
    2015-11-15 01:00:00             36               80           48.934             51.432         29.61   
    2015-11-15 02:00:00         36.634           79.366             48.9               51.5         29.61   
    2015-11-15 03:00:00             37           80.361             48.9               51.5         29.61   
    2015-11-15 04:00:00         36.727           81.453           48.834               51.5         29.61

Synchronize the timetables to a 30-minute time step. Specify a regular time step using the "regular" input argument and the TimeStep name-value argument.

ttHalfHour = synchronize(indoors,outdoors,"regular","linear",TimeStep=minutes(30));
ttHalfHour(1:5,:)

ans=5×5 timetable
           Time            Humidity_indoors    AirQuality    Humidity_outdoors    TemperatureF    PressureHg
    ___________________    ________________    __________    _________________    ____________    __________

    2015-11-15 00:00:00             36               80               49             51.299         29.61   
    2015-11-15 00:30:00             36               80           48.967             51.366         29.61   
    2015-11-15 01:00:00             36               80           48.934             51.432         29.61   
    2015-11-15 01:30:00         36.224           79.776             48.9             51.499         29.61   
    2015-11-15 02:00:00         36.634           79.366             48.9               51.5         29.61

As an alternative, you can synchronize the timetables to a time vector that specifies half-hour intervals.

tv = [datetime(2015,11,15):minutes(30):datetime(2015,11,18)];
tv.Format = indoors.Time.Format;
ttHalfHour = synchronize(indoors,outdoors,tv,"linear");
ttHalfHour(1:5,:)

ans=5×5 timetable
           Time            Humidity_indoors    AirQuality    Humidity_outdoors    TemperatureF    PressureHg
    ___________________    ________________    __________    _________________    ____________    __________

    2015-11-15 00:00:00             36               80               49             51.299         29.61   
    2015-11-15 00:30:00             36               80           48.967             51.366         29.61   
    2015-11-15 01:00:00             36               80           48.934             51.432         29.61   
    2015-11-15 01:30:00         36.224           79.776             48.9             51.499         29.61   
    2015-11-15 02:00:00         36.634           79.366             48.9               51.5         29.61

Synchronize and Aggregate Data Values

Synchronize the timetables and calculate the daily means for all variables in the output timetable.

ttDaily = synchronize(indoors,outdoors,"daily","mean");
ttDaily

ttDaily=4×5 timetable
           Time            Humidity_indoors    AirQuality    Humidity_outdoors    TemperatureF    PressureHg
    ___________________    ________________    __________    _________________    ____________    __________

    2015-11-15 00:00:00          36.5            80.05            48.931             51.394         29.607  
    2015-11-16 00:00:00         36.85            80.35            47.924             51.571         29.611  
    2015-11-17 00:00:00         36.85            79.45             48.45             51.238         29.613  
    2015-11-18 00:00:00           NaN              NaN              49.5               50.8          29.61

Synchronize the timetables to six-hour time intervals and calculate a mean for each interval.

tt6Hours = synchronize(indoors,outdoors,"regular","mean",TimeStep=hours(6));
tt6Hours(1:5,:)

ans=5×5 timetable
           Time            Humidity_indoors    AirQuality    Humidity_outdoors    TemperatureF    PressureHg
    ___________________    ________________    __________    _________________    ____________    __________

    2015-11-15 00:00:00          36.4             80.2              48.9              51.45          29.61  
    2015-11-15 06:00:00          36.4             79.8              48.9              51.45           29.6  
    2015-11-15 12:00:00          36.6             80.4            49.025              51.45          29.61  
    2015-11-15 18:00:00          36.6             79.8              48.9             51.225         29.607  
    2015-11-16 00:00:00          36.6             80.2              48.5               51.4          29.61

As an alternative, specify a time vector that has the same six-hour time intervals.

tv = [datetime(2015,11,15):hours(6):datetime(2015,11,18)];
tv.Format = indoors.Time.Format;
tt6Hours = synchronize(indoors,outdoors,tv,"mean");
tt6Hours(1:5,:)

ans=5×5 timetable
           Time            Humidity_indoors    AirQuality    Humidity_outdoors    TemperatureF    PressureHg
    ___________________    ________________    __________    _________________    ____________    __________

    2015-11-15 00:00:00          36.4             80.2              48.9              51.45          29.61  
    2015-11-15 06:00:00          36.4             79.8              48.9              51.45           29.6  
    2015-11-15 12:00:00          36.6             80.4            49.025              51.45          29.61  
    2015-11-15 18:00:00          36.6             79.8              48.9             51.225         29.607  
    2015-11-16 00:00:00          36.6             80.2              48.5               51.4          29.61