MATLAB: Comparing date time vectors.

correlationdata analysisdata sortingdatetimedurationsynchronizetimetable

I have two datetime vectors of un equal lengths that I need to compare. One issue is they also have diferent degrees of accuracey, one is to the second and one to the minute, and I have multiple points per minute making using a function like synchronize difficult to use. I would like to align the data such that if the day hour and minute are equal then the sampled points are next to each other in the table, and if one set has fewer points than the other, the missing data is replaced with NaN. For instance If I have a set of data from 1/1/17 from 00:00:00 to 1/1/17 00:30:00 sampling every 20 seconds and another set from 1/1/17 00:00 to 1/1/17 00:30 with two to three points per minute sampled randomly without recording the seconds, is there a way I can put both data sets in one table with teh first and second sets aligned to the minute, and any point missing from the second set replaced with NaN? so it would look something like:
date Value_A Value_B
1/1/17 00:00 5 6
1/1/17 00:00 7 9
1/1/17 00:00 2 NaN
1/1/17 00:01 8 3

Best Answer

There is, it's not hard, but this seems like such a strange thing to want to do that I feel like you must mean something else.
Cook up fake data:
>> rng default
>> t1 = datetime(1,1,2017,0,0,0):seconds(20):datetime(1,1,2017,0,3,40);
>> x1 = rand(size(t1))';
>> tt1 = timetable(x1,'RowTimes',t1)
tt1 =
  12×1 timetable
            Time              x1   
    ____________________    _______
    10-Jul-0006 00:00:00    0.81472
    10-Jul-0006 00:00:20    0.90579
    10-Jul-0006 00:00:40    0.12699
    10-Jul-0006 00:01:00    0.91338
    10-Jul-0006 00:01:20    0.63236
    10-Jul-0006 00:01:40    0.09754
    10-Jul-0006 00:02:00     0.2785
    10-Jul-0006 00:02:20    0.54688
    10-Jul-0006 00:02:40    0.95751
    10-Jul-0006 00:03:00    0.96489
    10-Jul-0006 00:03:20    0.15761
    10-Jul-0006 00:03:40    0.97059
>> t2 = datetime(1,1,2017,0,0,0):minutes(1):datetime(1,1,2017,0,3,0);
>> t2 = repelem(t2,randi([2 3],1,length(t2)));
>> x2 = rand(size(t2))';
>> tt2 = timetable(x2,'RowTimes',t2)
tt2 =
  10×1 timetable
            Time               x2   
    ____________________    ________
    10-Jul-0006 00:00:00     0.42176
    10-Jul-0006 00:00:00     0.91574
    10-Jul-0006 00:00:00     0.79221
    10-Jul-0006 00:01:00     0.95949
    10-Jul-0006 00:01:00     0.65574
    10-Jul-0006 00:02:00    0.035712
    10-Jul-0006 00:02:00     0.84913
    10-Jul-0006 00:02:00     0.93399
    10-Jul-0006 00:03:00     0.67874
    10-Jul-0006 00:03:00     0.75774
Find minutes with only two corresponding rows in tt2
>> t2u = unique(tt2.Time);
>> numRowsAtEachMinute = histcounts(tt2.Time,[unique(t2u); datetime(Inf,Inf,Inf)])
numRowsAtEachMinute =
     3     2     3     2
>> minutesWithOnlyTwoRows = t2u(numRowsAtEachMinute == 2)
minutesWithOnlyTwoRows = 
  2×1 datetime array
   10-Jul-0006 00:01:00
   10-Jul-0006 00:03:00
Add all NaN to tt2 for those times and sort
>> n = length(minutesWithOnlyTwoRows);
>> i = height(tt2) + (1:n);
>> tt2.x2(i) = NaN;
>> tt2.Time(i) = minutesWithOnlyTwoRows;
>> tt2 = sortrows(tt2)
tt2 =
  12×1 timetable
            Time               x2   
    ____________________    ________
    10-Jul-0006 00:00:00     0.42176
    10-Jul-0006 00:00:00     0.91574
    10-Jul-0006 00:00:00     0.79221
    10-Jul-0006 00:01:00     0.95949
    10-Jul-0006 00:01:00     0.65574
    10-Jul-0006 00:01:00         NaN
    10-Jul-0006 00:02:00    0.035712
    10-Jul-0006 00:02:00     0.84913
    10-Jul-0006 00:02:00     0.93399
    10-Jul-0006 00:03:00     0.67874
    10-Jul-0006 00:03:00     0.75774
    10-Jul-0006 00:03:00         NaN
Throw away the seconds for tt1 and combine
>> tt1.Time = dateshift(tt1.Time,'start','minute');
>> tt = [tt1 tt2]
tt =
  12×2 timetable
            Time              x1          x2   
    ____________________    _______    ________
    10-Jul-0006 00:00:00    0.81472     0.42176
    10-Jul-0006 00:00:00    0.90579     0.91574
    10-Jul-0006 00:00:00    0.12699     0.79221
    10-Jul-0006 00:01:00    0.91338     0.95949
    10-Jul-0006 00:01:00    0.63236     0.65574
    10-Jul-0006 00:01:00    0.09754         NaN
    10-Jul-0006 00:02:00     0.2785    0.035712
    10-Jul-0006 00:02:00    0.54688     0.84913
    10-Jul-0006 00:02:00    0.95751     0.93399
    10-Jul-0006 00:03:00    0.96489     0.67874
    10-Jul-0006 00:03:00    0.15761     0.75774
    10-Jul-0006 00:03:00    0.97059         NaN
Related Question