Find unique groups in table with members both before and after date
1 view (last 30 days)
Show older comments
I have a table with strings in cells and a datetime. I need to find the unique groups of entries that occur before and after a particular date- June 1, 2022 in this case.
Here is my example table:
T=table({'Joe','Joe','Joe','Joe','Steve','Steve','Mary','Mary','Susan','Susan'}',...
{'2022-02-03','2021-12-23','2022-08-01','2022-07-15','2021-02-08',...
'2022-04-03','2022-09-03','2022-02-03','2022-08-13','2022-04-23'}',...
{'Car','Van','Car','Van','Car','Car','Truck','Truck','Car','Van'}',...
{'B213','G344','A342','G6767','N1356','A34','M235','A4567','G2345','A23'}',...
'VariableNames',{'Name' 'Date' 'Type' 'Data'}); % my data comes as cells from a database
T.Date=datetime(T.Date,'InputFormat','yyyy-MM-dd'); % Converting to datetime
T = convertvars(T,@iscell,'categorical'); % Convert to categorical - preferred to cells?
T
Now I want to find the groups of matching Name and Type where at least one occurs after 01-Jun-2022 and at least on occurred before that date. I also need to carry the Data field with me. So the solution would be:
T_ans=T;
T_ans([5:6 9:10],:)=[];
sortrows(T_ans,[1 3])
With Steve elimanted becsue his matching Type dates were all before 01-Jun-2022, and Susan eliminated because she did not have 2 or more matching Types.
I'm not sure how to proceed- I am thinking of looping through all the unique 'Name' and 'Type' groups and seeing if there is one before and one after 01-Jun-2022, then listing all memebers if true.
I can;t figure out what to do after this though:
[a,b,c]=unique([T.Name,T.Type],'rows');
a
Thanks for any help.
0 Comments
Accepted Answer
Chunru
on 20 Sep 2022
Edited: Chunru
on 20 Sep 2022
T=table({'Joe','Joe','Joe','Joe','Steve','Steve','Mary','Mary','Susan','Susan'}',...
{'2022-02-03','2021-12-23','2022-08-01','2022-07-15','2021-02-08',...
'2022-04-03','2022-09-03','2022-02-03','2022-08-13','2022-04-23'}',...
{'Car','Van','Car','Van','Car','Car','Truck','Truck','Car','Van'}',...
{'B213','G344','A342','G6767','N1356','A34','M235','A4567','G2345','A23'}',...
'VariableNames',{'Name' 'Date' 'Type' 'Data'}); % my data comes as cells from a database
T.Date=datetime(T.Date,'InputFormat','yyyy-MM-dd'); % Converting to datetime
T = convertvars(T,@iscell,'categorical'); % Convert to categorical - preferred to cells?
T
uName = unique(T.Name);
uType = unique(T.Type);
idx = [];
for i=1:length(uName)
for j=1:length(uType)
T1 = T(T.Name == uName(i) & T.Type == uType(j), :);
if (sum(T1.Date > datetime("01-Jun-2022")) >= 1) && (sum(T1.Date < datetime("01-Jun-2022")) >=1)
idx =[idx; [i j]];
end
end
end
idx
Tout = [];
for i=1:size(idx, 1)
Tout = [Tout;
T((T.Name==uName(idx(i,1))) & (T.Type==uType(idx(i,2))), :)];
end
Tout
3 Comments
More Answers (0)
See Also
Categories
Find more on Data Type Conversion in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!