Match datetime within 3 seconds from two tables of different sizes

51 views (last 30 days)
Tricia
Tricia on 15 Nov 2019
Commented: Harr on 8 Apr 2021
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
Tricia
Tricia on 18 Nov 2019
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.

Accepted Answer

Campion Loong
Campion Loong on 18 Nov 2019
Edited: Campion Loong on 18 Nov 2019
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
  4 Comments
Harr
Harr on 8 Apr 2021
Dear Campion, My question is quite close to this! Are you still around to help? I have 3 text files instead and i am using R2018b.
Link to the question:

Sign in to comment.

More Answers (1)

Steven Lord
Steven Lord on 18 Nov 2019
Edited: Steven Lord on 18 Nov 2019
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)), :)

Community Treasure Hunt

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

Start Hunting!

Translated by