Effacer les filtres
Effacer les filtres

Subset using time range following specific time

2 vues (au cours des 30 derniers jours)
Louise Wilson
Louise Wilson le 29 Juin 2021
Commenté : Louise Wilson le 1 Juil 2021
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 commentaires
dpb
dpb le 30 Juin 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 le 1 Juil 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

Connectez-vous pour commenter.

Réponse acceptée

Seth Furman
Seth Furman le 1 Juil 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 commentaire
Louise Wilson
Louise Wilson le 1 Juil 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!

Connectez-vous pour commenter.

Plus de réponses (0)

Catégories

En savoir plus sur Data Type Conversion dans Help Center et File Exchange

Produits


Version

R2020b

Community Treasure Hunt

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

Start Hunting!

Translated by