Subset using time range following specific time

3 次查看（过去 30 天）
Louise Wilson
Louise Wilson 2021-6-29
评论： Louise Wilson 2021-7-1
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 个评论
dpb
dpb 2021-6-30
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 2021-7-1
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

请先登录，再进行评论。

请先登录，再回答此问题。

采纳的回答

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

请先登录，再进行评论。

更多回答（0 个）

类别

在 Help Center 和 File Exchange 中查找有关 Timetables 的更多信息

标签

产品


版本

R2020b

Community Treasure Hunt

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

Start Hunting!

Translated by