Subset using time range following specific time

I have two tables (see attached screenshot.) Both tables have a list of datetimes. I want to select the value from counts_subset.BoatCount if the counts_subset.DateTime falls within any of dBtable.DateTime+2 minutes, and then I went to append that count to the same row of the matching datetime. I can do it with withtol, but withtol selects 2 minutes before and after, whereas I only want to select two minutes after. Is this possible?

    tmatch_tt1=counts_subset(withtol(dBtable.DateTime,tol),:).DateTime; %using withtol

    

Update: I figured out by subsetting with withol(2 minutes) and then selecting only those datetimes which occur after 2 minutes. It would be good to know if there is a quicker/more efficient way though.

    %select the times in table that match countd times with tolerance
    tmatch_tt1=counts_subset(withtol(dBtable.DateTime,tol),:).DateTime;

    %extract rows in table that are within 2 min 
    [Lia,Loc]=ismember(tmatch_tt1,counts_subset.DateTime)
    counts_subset_new=counts_subset(Loc,:);
    
    dBtable=table2timetable(dBtable);
    tt2_matched=retime(dBtable,tmatch_tt1,'nearest');

    %use synchronize to join the matched rows
    new_table=synchronize(tt2_matched,counts_subset_new);

    new_table.DateTime=datetime(new_table.DateNum,'ConvertFrom','datenum');
    
    %find datetimes which occur AFTER 2 mins
    for ii=1:height(new_table)
        
        %get wav datetime from .wav filename
        split_name=strsplit(new_table.name{ii},{'.','_'});
        wav_dt=split_name(2);
        new_table.wavDateTime(ii)=datetime(wav_dt,'InputFormat','yyMMddHHmmss');
        
        %get image datetime from img filename
        split_imgname=strsplit(new_table.Filename_boatCounts{ii},{'-'});
        img_dt=strcat(split_imgname(2),split_imgname(3));
        new_table.imgDateTime(ii)=datetime(img_dt,'InputFormat','yyyyMMddHHmm');
    end
    
    %only select rows where img dt is after wav dt
    S=new_table.imgDateTime>=new_table.wavDateTime; 
    new_table=new_table(S,:);    
    
    dBcalcs_CameraCounts.(sites{a})=new_table;
    

[dpb]

Be much more helpful to attach the data tables as .mat files instead of just images...nobody can experiment with an image.

I'd think maybe rowfun() with diff() might be just as quick, but haven't tried to build a test dataset to compare.

I've not had opporunity to use withtol to date but looks like an optional two-vector of tolerance values of plus/minus tolerance values would be a good enhancement request, maybe...

[Louise Wilson]

Thanks! I haven't looked at those functions but I will take a look thank you. And you are right I will attach a subset of the data. Thank you

Answer 1

[Seth Furman]

Two alternative workflows are

1. Shift the midpoint and tolerance passed into withtol to achieve the desired range.
2. Use logical subscripting to only include those datetimes in the desired range.

For example

rng default
dt = datetime(2000,1,1)+minutes(0:5:50)'

dt = 11×1 datetime array
   01-Jan-2000 00:00:00
   01-Jan-2000 00:05:00
   01-Jan-2000 00:10:00
   01-Jan-2000 00:15:00
   01-Jan-2000 00:20:00
   01-Jan-2000 00:25:00
   01-Jan-2000 00:30:00
   01-Jan-2000 00:35:00
   01-Jan-2000 00:40:00
   01-Jan-2000 00:45:00
   01-Jan-2000 00:50:00

posNeg = [-1 1];
posNeg = posNeg(randi(2,11,1))';
dtNoisy = dt+minutes(2.5).*rand(11,1).*posNeg;
tt = timetable(dtNoisy,(1:11)')

tt = 11×1 timetable
          dtNoisy           Var1
    ____________________    ____

    01-Jan-2000 00:02:25      1 
    01-Jan-2000 00:07:23      2 
    01-Jan-2000 00:08:47      3 
    01-Jan-2000 00:17:00      4 
    01-Jan-2000 00:20:21      5 
    01-Jan-2000 00:23:56      6 
    01-Jan-2000 00:27:42      7 
    01-Jan-2000 00:36:58      8 
    01-Jan-2000 00:42:23      9 
    01-Jan-2000 00:46:38     10 
    01-Jan-2000 00:49:54     11 

Shift the midpoint and tolerance passed into withtol to achieve the desired range

tt(withtol(dt+minutes(1),minutes(1)),:)

ans = 3×1 timetable
          dtNoisy           Var1
    ____________________    ____

    01-Jan-2000 00:20:21      5 
    01-Jan-2000 00:36:58      8 
    01-Jan-2000 00:46:38     10 

Use logical subscripting to only include those datetimes in the desired range

tt(minutes(0) <= tt.dtNoisy-dt & tt.dtNoisy-dt <= minutes(2),:)

ans = 3×1 timetable
          dtNoisy           Var1
    ____________________    ____

    01-Jan-2000 00:20:21      5 
    01-Jan-2000 00:36:58      8 
    01-Jan-2000 00:46:38     10 

[Louise Wilson]

Oh shifting the midpoint makes sense to me, that seems like a good work around which would shorten the process a lot. Thank you!