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

38 views (last 30 days)
Subhasish Sutradhar
Subhasish Sutradhar on 9 Nov 2019
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.

Answers (1)

Campion Loong
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');
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
  1 Comment
Subhasish Sutradhar
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'.
.......................................................................................................................................................

Sign in to comment.

Community Treasure Hunt

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

Start Hunting!

Translated by