MATLAB Answers

Tricia
1

Match datetime within 3 seconds from two tables of different sizes

Asked by Tricia
on 15 Nov 2019 at 20:52
Latest activity Commented on by Campion Loong on 18 Nov 2019 at 18:34
Hello all,
I am trying to match datetimes to an accuracy of within 3 seconds of two columns of different sizes within two different tables of different sizes and output certain information from those that match.
I have two tables, one 2464x15 and the other 236x15. Both tables have a datetime column with the format 'MM/dd/yyyy HH:mm:ss.SSS' and I would like to see if any of these datetimes match each other within 3 seconds. None of them will be exactly accurate and none should have multiple matches. Then for those that have a matching datetime, I would like variables to be created from the other columns of data within each table, such as latititude, longitude, and depth. In the end what I want are 6 variables that are lat, lon, and depth from table_1 and lat, lon, depth from table_2 where the values of row 1 for all of them are from the first matching datetime and so on.
I really have no idea where to start with doing this. I'm assuming I need a for loop and may use ismember, but that's about all the ideas I have. This is what I've found so far from searching online about matching datetimes within 3 seconds but I don't know where to go from here.
d = abs(table_1(:,15) - table_2(:,2));
d.Format = 'MM/dd/yyyy HH:mm:ss.SSS';
d < seconds(3)
The error the first line returns is Undefined operator '-' for input arguments of type 'table'.
Any help would be appreciated.

  3 Comments

d = abs(table_1{:,15} - table_2{:,2});
It sounds like you want synchronize but with a tolerance.
Is there a way to set a tolerance with synchronize? The documentation doesn't indicate that it has anything like that available, except maybe the nearest neighbor method.

Sign in to comment.

2 Answers

Answer by Campion Loong on 18 Nov 2019 at 17:24
Edited by Campion Loong on 18 Nov 2019 at 18:29
 Accepted Answer

This is a good question! Basically, if I understand correctly, you want to extract rows from table_2 whose timestamp matches that of table_1 within a tolerance; then you want those rows added to table_1 at corresponding, tolerance-matched timestamps.
At the moment, you cannot do this with one single function. But here's a workflow that could help you get there:
First of all, timetable is superior to table when dealing with timestamped data. You may use readtimetable (since R2019b) to directly import as a timetable, or use table2timetable to convert an existing table:
tt1 = table2timetable(table_1);
tt2 = table2timetable(table_2);
Now that your data are in timetable format, you can combine withtol, retime and synchronize to achieve what you need:
% Define your tolerance
tol = seconds(3);
% Extract the rows, using withtol, and corresponding time vector
% in tt1 that matches tt2's Time within the tolerance.
% This assumes the 'time' variable in your initial table is named 'Time'.
tMatch_tt1 = tt1(withtol(tt2.Time,tol), :).Time;
% Extract rows in tt2 that are within tolerance from tMatch_tt1
tt2_matched = tt2(withtol(tMatch_tt1, tol),:);
% Adjust tt2_matched's time to the matched tt1 time (i.e. tMatch_tt1).
% % 'nearest' works especially there is no ambiguous match in your
% according to your description.
%
% Alternatively, you can also directly assign the time here if you
% are sure both are already sorted by time
% tt2_matched.Time = tMatch_tt1;
tt2_matched = retime(tt2_matched, tMatch_tt1, 'nearest');
% Finally, use synchronize to join the matched rows in both timetables
tt_result = synchronize(tt1, tt2_matched);
As an example, your result will look like this with the following tt1 and tt2:
>> tt1
tt1 =
11×1 timetable
Time Var1
____________________ ____
18-Nov-2019 09:00:00 0
18-Nov-2019 09:30:00 0.5
18-Nov-2019 10:00:00 1
18-Nov-2019 10:30:00 1.5
18-Nov-2019 11:00:00 2
18-Nov-2019 11:30:00 2.5
18-Nov-2019 12:00:00 3
18-Nov-2019 12:30:00 3.5
18-Nov-2019 13:00:00 4
18-Nov-2019 13:30:00 4.5
18-Nov-2019 14:00:00 5
>> tt2
tt2 =
11×1 timetable
Time Var1
____________________ ____
18-Nov-2019 09:00:05 10
18-Nov-2019 09:30:03 11
18-Nov-2019 10:00:03 12
18-Nov-2019 10:30:02 13
18-Nov-2019 11:00:05 14
18-Nov-2019 11:30:02 15
18-Nov-2019 12:00:01 16
18-Nov-2019 12:30:04 17
18-Nov-2019 13:00:02 18
18-Nov-2019 13:30:02 19
18-Nov-2019 14:00:03 20
>> tt_result =
11×2 timetable
Time Var1_tt1 Var1_tt2_matched
____________________ ________ ________________
18-Nov-2019 09:00:00 0 NaN
18-Nov-2019 09:30:00 0.5 11
18-Nov-2019 10:00:00 1 12
18-Nov-2019 10:30:00 1.5 13
18-Nov-2019 11:00:00 2 NaN
18-Nov-2019 11:30:00 2.5 15
18-Nov-2019 12:00:00 3 16
18-Nov-2019 12:30:00 3.5 NaN
18-Nov-2019 13:00:00 4 18
18-Nov-2019 13:30:00 4.5 19
18-Nov-2019 14:00:00 5 20

  2 Comments

Thank you so much for the very clear instructions and it worked perfectly! The only change that I made was using 'intersection' in the synchronize command becasue I only needed the data that matched.
tt_result = synchronize(tt1, tt2_matched,'intersection');
In that case, you can also do it this way:
tol = seconds(3);
% Rows in tt1 that match with tt2's Time within tolerance
tt1_matched = tt1(withtol(tt2.Time,tol), :);
% Rows in tt2 that match with tt1's Time within tolerance
tt2_matched = tt2(withtol(tt1.Time,tol), :);
% Join the two timetables, using tt1's time vector (i.e. 'first')
% and resample with 'nearest' method
tt_result = synchronize(tt1_matched, tt2_matched, 'first', 'nearest');

Sign in to comment.


Answer by Steven Lord
on 18 Nov 2019 at 17:26
Edited by Steven Lord
on 18 Nov 2019 at 17:27

Use withtol. Let's use a sample timetable generated using the example from the help:
MeasurementTime = datetime({'2015-12-18 08:03:05';...
'2015-12-18 10:03:17';...
'2015-12-18 12:03:13'});
Temp = [37.3;39.1;42.3];
Pressure = [30.1;30.03;29.9];
WindSpeed = [13.4;6.5;7.3];
WindDirection = categorical({'NW';'N';'NW'});
TT = timetable(MeasurementTime,Temp,Pressure,WindSpeed,WindDirection)
Let's create a datetime array representing three minutes past the hour for the hours between 8 AM and 1 PM?
threePastHour = datetime(2015, 12, 18, 8:13, 3, 0)
What rows of TT were measured within 10 seconds of the times in threePastHour?
TT(withtol(threePastHour, seconds(10)), :)

  0 Comments

Sign in to comment.