Subset using time range following specific time

2 views (last 30 days)
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;
  2 Comments
dpb
dpb on 30 Jun 2021
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
Louise Wilson on 1 Jul 2021
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

Sign in to comment.

Accepted Answer

Seth Furman
Seth Furman on 1 Jul 2021
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
  1 Comment
Louise Wilson
Louise Wilson on 1 Jul 2021
Oh shifting the midpoint makes sense to me, that seems like a good work around which would shorten the process a lot. Thank you!

Sign in to comment.

More Answers (0)

Categories

Find more on Data Type Conversion in Help Center and File Exchange

Products


Release

R2020b

Community Treasure Hunt

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

Start Hunting!