Match datetime within 3 seconds from two tables of different sizes

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.

[Walter Roberson]

d = abs(table_1{:,15} - table_2{:,2});

[Sean de Wolski]

It sounds like you want synchronize but with a tolerance.

[Tricia]

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.

Answer 1

[Campion Loong]

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 

[Tricia]

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');

[Campion Loong]

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');

[Louise Wilson]

How would you do this but only select the tolerance after the datetime of interest? Right now, we are selecting + & - 3 secs, but what if we wanted only + or only - ?

[Harr]

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:

https://se.mathworks.com/matlabcentral/answers/795992-how-to-merge-different-data-collected-at-different-times

Answer 2

[Steven Lord]

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)), :)