Select rows in a given table according to 3 criteria

5 views (last 30 days)
I have a table data like this
%% Data of Table
Name = {'A';'A';'A';'B';'B';'C';'D'};
index = [1;9;14;16;19;38;55];
Var_1 = [1;0;0;0;0;1;1];
Var_2 = [0;1;0;1;0;0;1];
Var_3 = [0;0;1;0;0;0;0];
Var_4 = [0;0;1;1;1;0;0];
Var_5 = [1;1;0;1;0;0;0];
Var_6 = [1;1;1;0;0;1;1];
T = table(Name,index,Var_1,Var_2,Var_3,Var_4,Var_5,Var_6);
V = {[1,2],[2,6],[1,3,4],[4,8,9],[1,9,32,40],[1,2,3,45,53]};
F = @(n)sprintf("{%s}",join(string(n),","));
T.Properties.VariableNames(3:8) = cellfun(F,V);
I have two groups in the above table
group_1 = [3;4;5];
group_2 = [6;7;8];
T_group_1= T(:,group_1);
T_group_2= T(:,group_2);
I want to choose three rows of the table according to this criteria
1) The rows should be belong to 'A' and 'B'.
2) Sum of the any column of chosen row should be smaller or equal 2 for T_group_1
3) Sum of the any column of chosen row should be greater than 3 for T_group_2
I have came up with the following code
%% first criteria
T_new = T((strcmp(T.Name, 'A') | strcmp(T.Name, 'B')),:);
group_1_new = [3;4;5]-2;
group_2_new = [6;7;8]-2;
%% choose row index
chosen_index_candidate = cell([],1);
i = 1;
m = 0;
while 1
chosen_index = randperm(size(T_new{:,3:end},1),3);
sum_of_each_col = sum(T_new{chosen_index,3:end},1);
m = m+1;
if m==40 % I want to find some number to break the loop
if any(sum_of_each_col(:,group_1_new)<=2) && any(sum_of_each_col(:,group_2_new)>=3) %% second and third criteria
if i==1
chosen_index_candidate{i} = chosen_index;
i = i+1;
if any(cell2mat(cellfun(@(x)all(ismember(sort(x),sort(chosen_index))),chosen_index_candidate,'uni',0)))==0
chosen_index_candidate{i} = chosen_index;
i = i+1;
I think the code is not written in proper way especially break from while loop

Accepted Answer

J. Alex Lee
J. Alex Lee on 5 Jun 2021
This is small enough you could generate the full list of combinations
% generate all combinations
alltriplets = nchoosek(1:7,3)
% randomize
iterlist = randperm(size(alltriplets,1))
% replace your while loop with a for loop over all possible triplets
for i = iterlist
J. Alex Lee
J. Alex Lee on 5 Jun 2021
I guess that should work, but I personally don't like the counter approach. You can create a true/false mask that can be applied to your randomly permuted list of triplets
alltriplets = nchoosek(1:size(T_new,1),3); % generate all combinations
iterlist = randperm(size(alltriplets,1)); % randomize
meetsCriteria = false(size(alltriplets,1),1);
for i = iterlist
chosen_index = alltriplets(i,:);
sum_of_each_col = sum(T_new{chosen_index,3:end},1);
if any(sum_of_each_col(:,group_1_new)<=2) && any(sum_of_each_col(:,group_2_new)>=3)
meetsCriteria(i) = true;
% then you can extract the rows of alltriplets that satisfies your
% condition as an array, rather than a cell
chosen_index_candidate = alltriplets(meetsCriteria,:)

Sign in to comment.

More Answers (0)


Find more on Matrices and Arrays 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!