Use datatime data of one table to find (and write) the nearest datetime and corresponding value data in another table

So I have two datetime datasets written in table form, column 1 is dates (in datetime format) and column 2 is the value. The datatimes of table 1 (runs from 1 to 160) dont exactly match with the datetimes of table 2 (runs from 1 to 1065). What I want to do is to take each datetime from table 1 and find the nearest datetime in table 2 and also the corresponding value and write them to another table or a txt file. For example, the first datetime of table 1 will match with row 9 of table 2 and I would like to write the that datetime and the corresponding value of X in a new table or a txt file.

Table 1

.

Table 2

I saw the answer to simliar question where the two data sets are written in matrix form and the variables were double array. But I am having problem using the same method for tables and datetime array. Any help would be much appreciated. I have already tried (unsuccessfully) using datenum to convert the datetime to double array, and table to array etc. Thanks in advance.

Answer 1

[Campion Loong]

Use timetable instead of table for timestamped data. You may use table2timetable to convert table to timetable; or use readtimetable (introduced in R2019b) to import directly into a timetable.

With a timetable, retime lets your accomplish your goal in one line:

% tt3 has same time as tt1 and draws 'nearest' data from tt2
tt3 = retime(tt2, tt1.Time, 'nearest');

For example, mimicking your data (but using timetable instead):

% 'tt1' corresponds to your 'Table 1'
>> size(tt1)
ans =
   160     1

>> head(tt1,10)
ans =
  10×1 timetable
            Time              X   
    ____________________    ______
    23-Jan-2018 15:08:39    1.6151
    23-Jan-2018 15:11:50     1.474
    23-Jan-2018 15:14:55    1.6389
    23-Jan-2018 15:17:50    1.5455
    23-Jan-2018 15:20:55     1.592
    23-Jan-2018 15:23:49    1.7928
    23-Jan-2018 15:26:40    1.5827
    23-Jan-2018 15:29:56    1.6156
    23-Jan-2018 15:33:03    1.6353
    23-Jan-2018 15:36:21     1.471

% 'tt2' corresponds to your 'Table 2'
>> size(tt2)
ans =
        1065           1
    
>> head(tt2,10)
ans =
  10×1 timetable
            Time              X   
    ____________________    ______
    23-Jan-2018 15:04:30    1.6899
    23-Jan-2018 15:05:00    1.7574
    23-Jan-2018 15:05:30      1.47
    23-Jan-2018 15:06:00     1.646
    23-Jan-2018 15:06:30    1.7471
    23-Jan-2018 15:07:00    1.7987
    23-Jan-2018 15:07:30    1.4625
    23-Jan-2018 15:08:00    1.7743
    23-Jan-2018 15:08:30    1.7089
    23-Jan-2018 15:09:00    1.7201

The above call to retime gives you this result:

% Note tt3 has the same Time as tt1 and values of X from row 9 of tt2, as described in your example.
>> tt3 = retime(tt2, tt1.Time, 'nearest');

>> isequal(tt1.Time, tt3.Time)
ans =
  logical
   1

>> size(tt3)
ans =
   160     1
   
>> head(tt3,10)
ans =
  10×1 timetable
            Time              X   
    ____________________    ______
    23-Jan-2018 15:08:39    1.7089
    23-Jan-2018 15:11:50     1.424
    23-Jan-2018 15:14:55    1.5667
    23-Jan-2018 15:17:50    1.6594
    23-Jan-2018 15:20:55    1.7137
    23-Jan-2018 15:23:49     1.704
    23-Jan-2018 15:26:40      1.61
    23-Jan-2018 15:29:56    1.7736
    23-Jan-2018 15:33:03     1.767
    23-Jan-2018 15:36:21    1.4465

[Subhasish Sutradhar]

Hello Campion,

I checked it (after convertng the table to timetable) and its giving me following error (I am using R2018b).

.......................................................................................................................................................

tt3 = retime(tt2, tt1.Time, 'nearest');

Error using timetable/retime (line 140)

Input timetables must contain sorted (increasing or decreasing) row times when synchronizing using 'nearest'.

.......................................................................................................................................................