Synchronize timetables with tolerance of 5 minutes

I have two tables with common information and I would like to synchronize them, creating an additional column for data from one column of the second table, using a tolerance of 5 minutes. The two tables do not have exactly the same times in the common DateTime variable, but I would like to add those which match within 5 minutes to the same row.

To detections_t, I would like to add a second column 'BoatCount' from the second table. Since the times don't match exactly I am keen to match within 5 minutes. For example, row 12 of detections_t would have the value of row 2 from boat_counts.

I was thinking I could copy the datetimes in detections_t, move them to nearest ten minute interval, and then match the values in boat_counts that way, but I can't figure out how to change the times in this way. Rather than changing them, I would prefer to create a second datetime variable with the adjusted times.

%read data
detections_t=readtable('H:\SoundTrap\Boats\Manual Vessel Detections\boatPresenceAbsence_table.csv');
boat_counts=readtable('H:\Cameras\Virtual Machine\Processed Images\Boat Counts\boatcounts_final_10mins_concat.csv');

%To detections_t, we want to add the boat count for the closest time to
%each row. This will restrict the data to 8-5pm. 
tol=seconds(300);
detections_t=table2timetable(detections_t); %convert to tt

%select the times in boat_counts that match detections times with tolerance
tmatch_tt1=detections_t(withtol(boat_counts.DateTime,tol),:).DateTime;

Error using withtol (line 78)
Tolerance exceeds half the smallest interval between subscript times, and might result in
selecting duplicate rows. Tolerance must be less than 30 sec.

[Mathieu NOE]

hello

have you tried to synchronize the time tables with synchronize

synchronize Synchronize timetables.

synchronize provides a way to, in effect, horizontally concatenate two or

more timetables even when their row times are different. You can retain

all the times from the input timetables in the output, or synchonize one

timetable to another, or specify a completely new vector of row times for the

output. synchronize also provides several ways to adjust each timetable's

data to account for aligning to a different vector of row times.

[Louise Wilson]

Thanks. Synchronize concatenates the two timetables, to create a larger timetable, it doesn't know to add data/columns to existing rows within a certain time tolerance. Unless there is a way to tell it to do this?

boat_counts=table2timetable(boat_counts);
tt=synchronize(detections_t,boat_counts);

I tried this:

tol=minutes(5);
detections_t=table2timetable(detections_t); %convert both datasheets to tt

%select the times in detections_table that match PSD_dB1 times with tolerance
tmatch_tt1=detections_t(withtol(boat_counts.DateTime,tol),:).DateTime;

%{
%To do this...
%extract rows 
%tt2_matched=boat_counts(withtol(tmatch_tt1,tol),:);

%adjust tt2_matched times to matched tt1 times
tt2_matched=retime(tt2_matched,tmatch_tt1,'nearest');

%use synchronize to join the matched rows
table=synchronize(detections_t,tt2_matched);
%}

but it doesn't work I think because the time difference between the rows in detections_t is variable and some rows are closer together in time than 10 minutes, and could be as small as a few seconds.

I think I would be able to solve this if I can figure out how to round the times to the nearest 10 minutes i.e. 0, 10, 20, 30, 40, 50 minutes.

[Mathieu NOE]

yes, you should probably first round the two time tables (to the nearest 10 min) and then synchronize

% round time to nearest 10 min
tt = datetime('now','Format','yyyy-MM-dd HH:mm')
tt.Minute = 10 * floor(tt.Minute/10);
tt.Second = 0;
tt