Main Content

Select Times in Timetable

A timetable is a type of table that associates a time with each row. You can select time-based subsets of its data in several ways:

  • Find times within a certain range using the timerange or withtol functions.

  • Match recurring units of time, such as days or months, using the components of datetime arrays.

  • Resample or group data with the retime function.

For example, read a sample file outages.csv, containing data representing electric utility outages in the United States from 2002–2014. The vector of row times, OutageTime, indicates when the outages occurred. The readtimetable function imports it as a datetime array. Display the first five rows.

TT = readtimetable('outages.csv');
head(TT,5)
         OutageTime            Region         Loss     Customers       RestorationTime              Cause       
    ____________________    _____________    ______    __________    ____________________    ___________________

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

Before R2019a, read tabular data with readtable and convert it to a timetable using table2timetable.

Select Time Range

To find data in a specific range, you can use the timerange function, which defines time-based subscripts for indexing. For instance, define a range for the summer of 2008, which started on June 20 and ended on September 21. By default, timerange defines a half-open interval that is closed on the left and open on the right, so specify the end date as September 22.

TR = timerange("2008-06-20","2008-09-22")
TR = 
	timetable timerange subscript:

		Select timetable rows with times in the half-open interval:
		[20-Jun-2008 00:00:00, 22-Sep-2008 00:00:00)

Find the outages that occurred in that range, and then plot the number of customers affected over time.

summer08 = TT(TR,:);
stem(summer08.OutageTime,summer08.Customers)
ylabel("Customers")

Figure contains an axes object. The axes object with ylabel Customers contains an object of type stem.

Several outages during that time range had high customer impact. Expand the range to a time period that spans the entire year of 2008 and look for similarly high numbers.

TR = timerange("2008","years");
all08 = TT(TR,:);
high08 = all08(all08.Customers > 500000,:);

stem(high08.OutageTime,high08.Customers)
ylabel('Customers')

Figure contains an axes object. The axes object with ylabel Customers contains an object of type stem.

The timerange function is also helpful for selecting specific dates. Selecting times by comparing datetime values can give misleading results because all datetime values include both date and time components. However, when you specify only the date component of a datetime value, the time component is set to midnight. Therefore, although there is data from June 26, a comparison like this one returns no results.

any(summer08.OutageTime == datetime("2008-06-26"))
ans = logical
   0

Instead, you can use timerange.

TR = timerange("2008-06-26","days");
june26 = summer08(TR,:)
june26=1×5 timetable
         OutageTime            Region         Loss     Customers      RestorationTime             Cause      
    ____________________    _____________    ______    _________    ____________________    _________________

    26-Jun-2008 22:36:00    {'NorthEast'}    425.21      93612      27-Jun-2008 06:53:00    {'thunder storm'}

Another way to define a range is to specify a tolerance around a time using withtol. For example, find rows from the summer of 2008 where OutageTime is within three days of Labor Day, September 1.

WT = withtol("2008-09-01",days(3));
nearSep1 = summer08(WT,:)
nearSep1=4×5 timetable
         OutageTime            Region         Loss     Customers      RestorationTime              Cause       
    ____________________    _____________    ______    _________    ____________________    ___________________

    01-Sep-2008 23:35:00    {'SouthEast'}    206.27     2.27e+05                     NaT    {'equipment fault'}
    01-Sep-2008 00:18:00    {'MidWest'  }    510.05        74213    01-Sep-2008 14:07:00    {'thunder storm'  }
    02-Sep-2008 19:01:00    {'MidWest'  }       NaN    2.215e+05    03-Sep-2008 02:58:00    {'severe storm'   }
    29-Aug-2008 20:25:00    {'West'     }       NaN        31624    01-Sep-2008 01:51:00    {'wind'           }

Match Units of Time

You also can use units of datetime values, such as hours or days, to identify rows for logical indexing. This method can be useful for specifying periodic intervals.

For example, find the values of OutageTime whose month components have values of 3 or less, corresponding to January, February, and March of each year. Use the resulting logical array to index into TT.

TR = (month(TT.OutageTime) <= 3);
winterTT = TT(TR,:);

head(winterTT,5)
         OutageTime            Region         Loss     Customers       RestorationTime            Cause      
    ____________________    _____________    ______    __________    ____________________    ________________

    01-Feb-2002 12:18:00    {'SouthWest'}    458.98    1.8202e+06    07-Feb-2002 16:50:00    {'winter storm'}
    23-Jan-2003 00:49:00    {'SouthEast'}    530.14    2.1204e+05                     NaT    {'winter storm'}
    07-Feb-2003 21:15:00    {'SouthEast'}     289.4    1.4294e+05    17-Feb-2003 08:14:00    {'winter storm'}
    16-Mar-2002 06:18:00    {'MidWest'  }    186.44    2.1275e+05    18-Mar-2002 23:23:00    {'severe storm'}
    04-Feb-2005 08:18:00    {'MidWest'  }       NaN           NaN    04-Feb-2005 19:51:00    {'attack'      }

Create a pie chart of the wintertime causes. The pie function accepts only numeric or categorical inputs, so first convert Cause to categorical.

winterTT.Cause = categorical(winterTT.Cause);
pie(winterTT.Cause)
title("Causes of Outages, January to March");

Group by Time Period

The retime function adjusts row times to create specified intervals, either by resampling or grouping values. Its pre-defined intervals range from seconds to years, and you can specify how to handle missing or multiple values for the intervals. For instance, you can select the first observation from each week, or count observations in a quarter.

For the outage data, you can use retime to find totals for each year. First, create a timetable with only numeric variables. Then, call retime and specify a yearly interval, combining multiple values using a sum. The output has one row for each year, containing the total losses and total customers affected during that year.

numTT = TT(:,vartype("numeric"));
numTT = retime(numTT,"yearly","sum");
head(numTT,5)
    OutageTime     Loss     Customers 
    ___________    _____    __________

    01-Jan-2002    81335    1.3052e+07
    01-Jan-2003    58036     1.396e+07
    01-Jan-2004    51014    1.5523e+07
    01-Jan-2005    33980    8.7334e+06
    01-Jan-2006    35129    2.5729e+07

Create a bar chart of the number of customers affected each year.

bar(numTT.OutageTime,numTT.Customers)
xlabel("Year")
ylabel("Customers")

Figure contains an axes object. The axes object with xlabel Year, ylabel Customers contains an object of type bar.

Calculate Durations Using Row Times

You can use the row times of a timetable with other datetime or duration values to perform calculations. For example, calculate the durations of the power outages listed in the outage data. Then calculate the monthly medians of the outage durations and plot them.

First add the outage durations to TT by subtracting the row times (which are the starts of power outages) from RestorationTime (which are the ends of the power outages). Change the format of OutageDuration to display the durations of the outages in days. Display the first five rows of TT.

TT.OutageDuration = TT.RestorationTime - TT.OutageTime;
TT.OutageDuration.Format = 'd';
head(TT,5)
         OutageTime            Region         Loss     Customers       RestorationTime              Cause           OutageDuration
    ____________________    _____________    ______    __________    ____________________    ___________________    ______________

    01-Feb-2002 12:18:00    {'SouthWest'}    458.98    1.8202e+06    07-Feb-2002 16:50:00    {'winter storm'   }      6.1889 days 
    23-Jan-2003 00:49:00    {'SouthEast'}    530.14    2.1204e+05                     NaT    {'winter storm'   }         NaN days 
    07-Feb-2003 21:15:00    {'SouthEast'}     289.4    1.4294e+05    17-Feb-2003 08:14:00    {'winter storm'   }      9.4576 days 
    06-Apr-2004 05:44:00    {'West'     }    434.81    3.4037e+05    06-Apr-2004 06:10:00    {'equipment fault'}    0.018056 days 
    16-Mar-2002 06:18:00    {'MidWest'  }    186.44    2.1275e+05    18-Mar-2002 23:23:00    {'severe storm'   }      2.7118 days 

Create a timetable that has only the outage durations. Some rows of TT have missing values, NaT, for the restoration times, leading to NaN values in OutageDuration. To remove the NaN values from medianTT, use the rmmissing function. Then use retime to calculate the monthly median outage duration. Display the first five rows of medianTT.

medianTT = TT(:,"OutageDuration");
medianTT = rmmissing(medianTT); 
medianTT = retime(medianTT,'monthly',@median);
head(medianTT,5)
    OutageTime     OutageDuration
    ___________    ______________

    01-Feb-2002      6.1889 days 
    01-Mar-2002      2.7472 days 
    01-Apr-2002         NaN days 
    01-May-2002     0.72917 days 
    01-Jun-2002     0.22431 days 

Create a stairstep chart of the monthly median outage durations.

stairs(medianTT.OutageTime,medianTT.OutageDuration)
xlabel("Year")
ylabel("Median Duration (days)")

Figure contains an axes object. The axes object with xlabel Year, ylabel Median Duration (days) contains an object of type stair.

See Also

| | | | | | | | | | |

Related Topics