Synchronize timetables with tolerance of 5 minutes

11 visualizzazioni (ultimi 30 giorni)
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.
  3 Commenti
Louise Wilson
Louise Wilson il 10 Dic 2020
Modificato: Louise Wilson il 10 Dic 2020
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
Mathieu NOE il 11 Dic 2020
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

Accedi per commentare.

Risposte (0)

Categorie

Scopri di più su Data Type Conversion in Help Center e File Exchange

Prodotti


Release

R2019b

Community Treasure Hunt

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

Start Hunting!

Translated by