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

18 次查看（过去 30 天）
Subhasish Sutradhar
评论： Subhasish Sutradhar 2019-11-16
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.

请先登录，再回答此问题。

回答（1 个）

Campion Loong
Campion Loong 2019-11-15
编辑：Campion Loong 2019-11-15
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 个评论
Subhasish Sutradhar
Subhasish Sutradhar 2019-11-16
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'.
.......................................................................................................................................................

请先登录，再进行评论。

类别

在 Help Center 和 File Exchange 中查找有关 Timetables 的更多信息

标签

产品


版本

R2019b

Community Treasure Hunt

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

Start Hunting!

Translated by