MATLAB: Match datetime within 3 seconds from two tables of different sizes

datetimedifferent size tablesmatch

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 2464×15 and the other 236×15. 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.

Best 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
Related Question