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

13 views (last 30 days)
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.

Campion Loong on 15 Nov 2019
Edited: Campion Loong on 15 Nov 2019
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');
% 'tt1' corresponds to your 'Table 1'
>> size(tt1)
ans =
160 1
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
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
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 on 16 Nov 2019
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'.
.......................................................................................................................................................

R2019b

### Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by