Do date ranges from two different timetables intersect?
Is a specific datetime value within the range of a timetable?
Is the range of row times in a timetable within the limits of a datetime array?
Three new functions in r2020a will help to answer these questions.
- [tf, whichRows] = containsrange(TT,__)
- [tf, whichRows] = overlapsrange(TT,__)
- [tf, whichRows] = withinrange(TT,__)
In these function inputs, TT is a timetable and input #2 is one of the following:
- another timetable
- a time range object produced by timerange(startTime,endTime)
- a datetime scalar produced by datetime()
- a duration
The tf output is a logical scalar indicating pass|fail and the whichRows output is a logical vector identifying the rows of TT that are within the specified time range.
How do these functions differ?
Let's test all 3 functions with different time ranges and a timetable of electric utility outages in the United States, provided by Matlab. The first few rows of outages.csv are shown below in a timetable. You can see that the row times are not sorted which won't affect the behavior of these functions.
8×5 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' } 2003-06-18 02:49 {'West' } 0 0 2003-06-18 10:54 {'attack' } 2004-06-20 14:39 {'West' } 231.29 NaN 2004-06-20 19:16 {'equipment fault'} 2002-06-06 19:28 {'West' } 311.86 NaN 2002-06-07 00:51 {'equipment fault'}
The range of times in utility.csv is depicted by the gray timeline bar in the plot below labeled "Timetable row times". The timeline bars above it are various time ranges or scalar datetime values used to test the three new functions.
The three columns of checkboxes on the right of the plot show the results of the first output of each function, tf, for each time range.
The time ranges were created by the timerange function using the syntax timerange(startTime,endTime). The default intervalType in timerange() is 'openright' which means that datetimes are matched when they are equal to or greater than startTime and less than but not equal to endTime. The scalar datetime values were created with datetime().
The colorbar along with the colored points at the bottom of each timeline bar show the row numbers of timetable TT that were selected by the whichRows output of the three functions.
The containsrange() function returns true when all of the time range values are within the timetable including the timetable's minimum and maximum datetime.
The overlapsrange() function returns true when any of the time range values are with the timetable's range.
The withinrange() function returns true only when all of the timetable's datetime values are within the time range values. A careful observer may see that comparison number 1 is false even though that time range is exactly equal to the timetable's row time range. This is because the default value for intervalType in the timerange() function is 'openright' which does not match the end values if they are equal. If you change the intervalType to 'closed' the withinrange result for comparison 1 would be true.
The scalar datetime values for comparisons 8, 9 and 10 are all exact matches of datetimes within the timetable and result in a single match in the whichRows output. The datetime values for comparisons 7 and 11 do not match any of the timetable row times and the values in whichRows are all false even though comparison 7 is within the timetable range. For all three tests, the whichRows outputs are identical.
---------------------------------------------------------------
Here is the code used to generate this data, test the functions, and produce the plot.
% Read in the outage data as a table and convert it to a timetable TT. T = readtable('outages.csv'); TT = table2timetable(T);
% Look at the first few rows. head(TT) % Show that row time vector is not sorted. issorted(TT)
% Get the earliest and latest row time. outageTimeLims = [min(TT.OutageTime), max(TT.OutageTime)];
% Define time ranges to test [start,end] or scalar times [datetime, NaT] % The scalar times must be listed after time ranges. dateRanges = [ % start, end outageTimeLims; % original data outageTimeLims; % equal time range datetime(2005,2,1), datetime(2011,2,1); % all within datetime(1998,3,16), datetime(2018,4,11); % starts before, ends after datetime(2000,1,1), datetime(2010,4,11); % starts before, ends within datetime(2009,1,15), datetime(2019, 4,7); % starts within, ends after datetime(2015,6,15), datetime(2019,12,31); % all outside datetime(2008,6,6), NaT; % 1-value, inside, not a member [TT.OutageTime(find(year(TT.OutageTime)==2010,1)), NaT] % 1 value, inside, is a member outageTimeLims(1), NaT; % 1-value, on left edge outageTimeLims(2), NaT; % 1-value, on right edge datetime(2000,6,6), NaT; % 1-value, outside ]; nRanges = size(dateRanges,1); dateRangeLims = [min(dateRanges,[],'all'), max(dateRanges,[],'all')];
% Set up the figure and axes uifig = uifigure('Name', 'Timetable_intersection_demo', 'Resize', 'off'); uifig.Position(3:4) = [874,580]; movegui(uifig,'center') uiax = uiaxes(uifig, 'Position', [0,0,uifig.Position(3:4)], 'box', 'on', 'YAxisLocation', 'right',... 'ytick', -.5:1:nRanges, 'YTickLabel', [], 'ylim', [-3.5, nRanges], 'FontSize', 18); hold(uiax, 'on') grid(uiax, 'on') uiax.Toolbar.Visible = 'off'; % Add axes labels & title title(uiax, strrep(uifig.Name,'_',' ')) xlabel(uiax, 'Timeline') ylab = ylabel(uiax, 'Comparison number'); set(ylab, 'Rotation', -90, 'VerticalAlignment', 'Bottom')
% Add the timetable frame fill(uiax, outageTimeLims([1,2,2,1]), [-.7,-.7,nRanges-.3,nRanges-.3] , 0.85938*[1,1,1], ... %gainsboro 'EdgeColor', 0.41016*[1,1,1], 'LineStyle', '--', 'LineWidth', 1.5, 'FaceAlpha', .25) %dimgray
% Set xtick & xlim after x-axis is converted to datetime range = @(x)max(x)-min(x); uiax.XLim = dateRangeLims + range(dateRangeLims).*[-.01, .40]; uiax.XTick = dateshift(dateRangeLims(1),'start','Year') : calyears(2) : dateshift(dateRangeLims(2),'start','Year','next'); xtickformat(uiax,'yyyy')
% Set up timeline plot lineColors = [0.41016*[1,1,1]; lines(nRanges-1)]; %dimGray uiax.Colormap = parula(size(TT,1)); tfUniCodes = {char(09745),char(09746)}; %{true, false} checkbox characters barHeight = 0.8; rightMargin = [max(dateRangeLims),max(uiax.XLim)]; tfCenters = linspace(rightMargin(1),rightMargin(2),5); tfCenters([1,end]) = []; intervalType = 'openright'; % open, closed, openleft openright(def); see https://www.mathworks.com/help/matlab/ref/timerange.html#bvdk6vh-intervalType
% Loop through each row of dateRanges for i = 0:nRanges-1 % Plot timeline bar pObj = fill(uiax, dateRanges(i+1,[1,2,2,1]), i+[-barHeight,-barHeight,barHeight,barHeight]/2, lineColors(i+1,:), 'FaceAlpha', .4);
% Evaluate date ranges and single values differently if any(isnat(dateRanges(i+1,:))) % Test single datetime tr = dateRanges(i+1,~isnat(dateRanges(i+1,:))); set(pObj, 'LineWidth', 3, 'EdgeAlpha', .6, 'EdgeColor', lineColors(i+1,:)) else % Test date range tr = timerange(dateRanges(i+1,1), dateRanges(i+1,2),intervalType); end
% Create timerange obj and test for intersections [tf(1), whichRows{1}] = containsrange(TT,tr); [tf(2), whichRows{2}] = overlapsrange(TT,tr); [tf(3), whichRows{3}] = withinrange(TT,tr); % Confirm that all 'whichRows' are equal assert(isequal(whichRows{1},whichRows{2},whichRows{3}), 'Unequal whichRows outputs.')
if i>0 % Add pass/fail checkboxes text(uiax, tfCenters(tf), repmat(i,1,sum(tf)), repmat(tfUniCodes(1),1,sum(tf)), ... 'HorizontalAlignment', 'Center', 'Color', [0 .5 0], 'FontSize', 36, 'FontWeight', 'Bold') % Fail checkboxes text(uiax, tfCenters(~tf), repmat(i,1,sum(~tf)), repmat(tfUniCodes(2),1,sum(~tf)), ... 'HorizontalAlignment', 'Center', 'Color', [1 0 0], 'FontSize', 36, 'FontWeight', 'Bold')
% Plot the TT row number matches scatter(uiax, TT.OutageTime(whichRows{1}), repmat(i-barHeight/2+.1,1,sum(whichRows{1})), ... 10, uiax.Colormap(whichRows{1},:), 'filled', 'MarkerFaceAlpha', 0.2) else % add stripes to reference bar xHatch = linspace(dateRanges(i+1,1)-days(2), dateRanges(i+1,2)+days(2), 20); yHatch = repmat(unique(pObj.YData), 1, 19); plot(uiax, [xHatch(1:end-1);xHatch(2:end)], yHatch, '-', 'Color', [1 1 1 .5], 'LineWidth', 4) pObj.FaceAlpha = .9; text(uiax, mean(outageTimeLims), 0, 'Timetable row times', 'FontSize', 20, ... 'HorizontalAlignment', 'Center', 'VerticalAlignment', 'middle', 'FontWeight', 'Bold') end end
% Draw frame around checkboxs for duration comparisons and label intervalType rectEdges = linspace(rightMargin(1),rightMargin(2),33); nDurations = sum(~isnat(dateRanges(:,2)))-1; fill(uiax, rectEdges([6,end-5,end-5,6]), [.4 .4 nDurations+[.4,.4]], 'w', ... 'FaceAlpha', 0, 'LineWidth', 1.5, 'EdgeColor', 0.41016*[1,1,1]) % dimgray text(uiax, rectEdges(6), nDurations/2+.5, sprintf('intervalType: %s', intervalType), 'FontSize', 16, ... 'HorizontalAlignment', 'Center', 'VerticalAlignment', 'Bottom', 'Rotation', 90)
% Add text labels for checkboxes and comparison number text(uiax, tfCenters, [.5 .5 .5], {'containsrange ', 'overlapsrange ', 'withinrange '}, 'Fontsize', 22, 'Rotation', 90, ... 'HorizontalAlignment', 'right', 'FontWeight', 'b') text(uiax, repmat(rightMargin(2)-range(xlim(uiax))*.02,1,nRanges-1), 1:nRanges-1, cellstr(num2str((1:nRanges-1)')), ... 'HorizontalAlignment', 'Right', 'FontSize', 16)
% Add color bar; position it under the timetable bar (must be done after all other axes properties are set) % requires coordinate tranformation from data units to fig position units. cb = colorbar(uiax, 'Location', 'South', 'TickDirection', 'Out', 'YAxisLocation', 'Bottom', 'FontSize', 11); caxis(uiax, [1,size(TT,1)]) cb.Position(3) = range(outageTimeLims)/range(xlim(uiax)) * (uiax.InnerPosition(3)/uifig.Position(3)); cb.Position(1) = ((outageTimeLims(1)-min(xlim(uiax)))/range(xlim(uiax)) * uiax.InnerPosition(3) + uiax.InnerPosition(1)) / uifig.Position(3); cb.Position(4) = 0.008; cb.Position(2) = ((-barHeight-min(ylim(uiax))-.5)/range(ylim(uiax)) * uiax.InnerPosition(4) + uiax.InnerPosition(2)) / uifig.Position(4); ylabel(cb, 'Timetable row number')