Select Timetable Data by Row Time and Variable Type

A timetable is a type of table that associates a time with each row. You can subscript into a timetable to select subsets of its data in a number of different ways. To select timetable rows with row times that fall within a given time range, specify the time range using the timerange function. Since a timetable is a table, you can index on rows and variables using either smooth parentheses or curly braces. You can index on specific row times, or select rows with row times that match specified times within a tolerance you set using the withtol function. You can also subscript into a table or timetable to select all the variables that match a type you specify with the vartype function. Finally, extract data from a timetable into a matrix using the Variables property.

Create Timetable from File

Create a timetable from the sample file outages.csv, containing data representing electric utility outages in the United States. Read the table from the file with the readtable function. Convert T.Cause and T.Region into categorical arrays. Then convert the table to a timetable using the table2timetable function. Display the first five rows of the timetable. TT is a timetable containing outage data from February 2002 to January 2014.

T = readtable('outages.csv');
T.Cause = categorical(T.Cause);
T.Region = categorical(T.Region);
TT = table2timetable(T);
TT(1:5,:)
ans=5×6 timetable
       OutageTime        Region       Loss     Customers     RestorationTime          Cause     
    ________________    _________    ______    __________    ________________    _______________

    2002-02-01 12:18    SouthWest    458.98    1.8202e+06    2002-02-07 16:50    winter storm   
    2003-01-23 00:49    SouthEast    530.14    2.1204e+05                 NaT    winter storm   
    2003-02-07 21:15    SouthEast     289.4    1.4294e+05    2003-02-17 08:14    winter storm   
    2004-04-06 05:44    West         434.81    3.4037e+05    2004-04-06 06:10    equipment fault
    2002-03-16 06:18    MidWest      186.44    2.1275e+05    2002-03-18 23:23    severe storm   

Summarize Timetable and Access Row Times

Display a summary of TT. It is a timetable that contains 1468 rows and five variables.

summary(TT)
RowTimes:

    OutageTime: 1468x1 datetime
        Values:
            Min         2002-02-01 12:18 
            Median      2010-03-18 21:05 
            Max         2014-01-15 02:41 

Variables:

    Region: 1468x1 categorical

        Values:

            MidWest        142   
            NorthEast      557   
            SouthEast      389   
            SouthWest       26   
            West           354   

    Loss: 1468x1 double

        Values:

            Min             0       
            Median          180.26  
            Max             23418   
            NumMissing      604     

    Customers: 1468x1 double

        Values:

            Min             0           
            Median          75765       
            Max             5.9689e+06  
            NumMissing      328         

    RestorationTime: 1468x1 datetime

        Values:

            Min             2002-02-07 16:50  
            Median          2010-03-31 10:54  
            Max             2042-09-18 23:31  
            NumMissing      29                

    Cause: 1468x1 categorical

        Values:

            attack                294  
            earthquake              2  
            energy emergency      188  
            equipment fault       156  
            fire                   25  
            severe storm          338  
            thunder storm         201  
            unknown                24  
            wind                   95  
            winter storm          145  

Access the row times. The row times are not in a variable. Instead, the vector of row times is a property of the timetable. However, you can access the row times with dot syntax. TT.OutageTime is a 1468-by-1 vector of datetime values. Display the first five rows of TT.OutageTime.

TT.OutageTime(1:5)
ans = 5x1 datetime array
   2002-02-01 12:18
   2003-01-23 00:49
   2003-02-07 21:15
   2004-04-06 05:44
   2002-03-16 06:18

Subscript on Time Range

To select all timetable rows that fall in a time range, create a subscript as a helper, using the timerange function. The start and end times you specify do not have to match any of the row times in the timetable.

Select all rows with outages that occurred between January 2002 and December 2003. Display the first five rows of TT2.

TR = timerange('2002-01-01','2003-12-31');
TT2 = TT(TR,:);
TT2(1:5,:)
ans=5×6 timetable
       OutageTime        Region       Loss     Customers     RestorationTime        Cause    
    ________________    _________    ______    __________    ________________    ____________

    2002-02-01 12:18    SouthWest    458.98    1.8202e+06    2002-02-07 16:50    winter storm
    2003-01-23 00:49    SouthEast    530.14    2.1204e+05                 NaT    winter storm
    2003-02-07 21:15    SouthEast     289.4    1.4294e+05    2003-02-17 08:14    winter storm
    2002-03-16 06:18    MidWest      186.44    2.1275e+05    2002-03-18 23:23    severe storm
    2003-06-18 02:49    West              0             0    2003-06-18 10:54    attack      

Display the last five rows of TT2.

TT2(end-4:end,:)
ans=5×6 timetable
       OutageTime        Region       Loss     Customers     RestorationTime          Cause      
    ________________    _________    ______    __________    ________________    ________________

    2003-09-02 19:46    SouthEast         0             0    2003-09-16 22:25    severe storm    
    2003-09-15 14:56    MidWest       418.7         61045    2003-09-22 04:21    thunder storm   
    2003-09-24 22:43    SouthWest    2576.9    9.4873e+05    2003-09-25 14:46    severe storm    
    2003-09-18 10:40    SouthWest     301.8    2.3973e+05    2003-09-27 08:17    severe storm    
    2003-10-11 19:36    SouthEast     309.8         93582    2003-10-11 19:49    energy emergency

TT2 is a timetable with only 98 rows, containing outage data only from 2002 and 2003.

Index on Specified Times

You can index into TT with datetime values or character vectors representing specific times in TT.OutageTime. But when you do so, the times you specify must have exact matches in the time vector, and only those times are selected. Index into TT on times for the first and third rows of TT.

TT({'2002-02-01 12:18:00','2003-02-07 21:15:00'},:)
ans=2×6 timetable
       OutageTime        Region       Loss     Customers     RestorationTime        Cause    
    ________________    _________    ______    __________    ________________    ____________

    2002-02-01 12:18    SouthWest    458.98    1.8202e+06    2002-02-07 16:50    winter storm
    2003-02-07 21:15    SouthEast     289.4    1.4294e+05    2003-02-17 08:14    winter storm

Index on Specified Times with Tolerances

Specify a tolerance when indexing on times. You can use the withtol function to create a subscript as a helper. With the output of withtol, you can select row times that match within the specified tolerance.

Index into TT on specified dates. Specify a tolerance of one day to return rows with row times that are within one day of the specified dates. The times must be in a datetime or duration vector, or in a cell array of character vectors that can be converted to datetime or duration values. The tolerance must be specified as a duration, using a function such as seconds, minutes, hours, or days.

rowTimes = {'2002-02-01','2003-02-07'};
S = withtol(rowTimes,days(1));
TT(S,:)
ans=2×6 timetable
       OutageTime        Region       Loss     Customers     RestorationTime        Cause    
    ________________    _________    ______    __________    ________________    ____________

    2002-02-01 12:18    SouthWest    458.98    1.8202e+06    2002-02-07 16:50    winter storm
    2003-02-07 21:15    SouthEast     289.4    1.4294e+05    2003-02-17 08:14    winter storm

Subscript by Variable Type

To select all timetable variables that are of a given type, create a subscript as a helper, using the vartype function. You can specify variable types without having to specify their names or positions in the timetable.

Select all variables that contain numeric data. TT2 contains only the variables Loss and Customers. The other three variables of TT are either categorical or datetime variables. Display the first five rows of TT2.

S = vartype('numeric');
TT2 = TT(:,S);
TT2(1:5,:)
ans=5×3 timetable
       OutageTime        Loss     Customers 
    ________________    ______    __________

    2002-02-01 12:18    458.98    1.8202e+06
    2003-01-23 00:49    530.14    2.1204e+05
    2003-02-07 21:15     289.4    1.4294e+05
    2004-04-06 05:44    434.81    3.4037e+05
    2002-03-16 06:18    186.44    2.1275e+05

Subscript both on a time range and by variable type.

TR = timerange('2002-01-01','2003-12-31');
TT2 = TT(TR,S);
TT2(1:5,:)
ans=5×3 timetable
       OutageTime        Loss     Customers 
    ________________    ______    __________

    2002-02-01 12:18    458.98    1.8202e+06
    2003-01-23 00:49    530.14    2.1204e+05
    2003-02-07 21:15     289.4    1.4294e+05
    2002-03-16 06:18    186.44    2.1275e+05
    2003-06-18 02:49         0             0

Extract Data with Variables Property

Tables and timetables have a property, Variables, that you can use to extract data from variables into a matrix, as long as the variables can be concatenated together.

Extract the numeric data from TT2 using the Variables property. A is a 1468-by-2 matrix of doubles. When you extract data from a timetable into an array, the row times are not included.

A = TT2.Variables;
A(1:5,:)
ans = 5×2
106 ×

    0.0005    1.8202
    0.0005    0.2120
    0.0003    0.1429
    0.0002    0.2128
         0         0

The result of TT2.Variables is the same as the result of using curly braces to extract data, using the TT2{:,:} syntax.

You can concatenate the variables in TT2 into an array of doubles. However, TT contains numeric, categorical, and datetime variables that cannot be concatenated. The Variables property returns an error when variables cannot be concatenated. To avoid such an error, you can subscript by variable type before using the Variables property.

Subscript into TT to select numeric variables and extract them into a matrix.

A = TT(:,vartype('numeric')).Variables;
A(1:5,:)
ans = 5×2
106 ×

    0.0005    1.8202
    0.0005    0.2120
    0.0003    0.1429
    0.0004    0.3404
    0.0002    0.2128

See Also

| | | | | |

Related Topics