Obtaining contents of a column in a table corresponding to specific contents of another column in the same table

Hello,

I have a table similar to the table provided below:

col1 = ['n1'; 'n2'; 'n3'; 'n4';'n5'; 'n6'; 'n7'; 'n8'; 'n9'];
col2 = [1.1; 1.2; 1.3; 2.1; 2.2; 2.3; 1.1; 2.2; 3.3];
col3 = [1;1;1;2;2;2;3;3;3];
T = table(col1, col2, col3);

I have two other matrices similar to the following

X = [1,2,3];
a = [1.1, 2.3, 2.2];

I would like to obtain only those contents of col1 of table T, that correspond to values in col2 which are equal to the values in 'a' and when the values of col3 match a value in matrix X

Meaning, I would like to obtain a folder, say 'aFile', that has the following content

cola = ['n1'; 'n6'; 'n8'];
colb = a';
aFile = table(cola,colb);

I use a for loop of the following

num = numel(X);
aFile = cell(num,3);
for n = 1:num & T.col3 == 1:num
       r = find(T.col2 == a(n));
       aFile{n,1} = T(r,1);
       aFile{n,2} = T(r,2);
end

But I don't recieve anything in aFile folder. Furthermore, I recieve an error

Row index exceeds table dimensions. at line aFile{n,1} = T(r,1);

How should I modify my code?

Answer 1

[Guillaume]

You shouldn't use a loop for this sort of things. Operate on the whole columns at once:

col1 = ['n1'; 'n2'; 'n3'; 'n4';'n5'; 'n6'; 'n7'; 'n8'; 'n9'];
col2 = [1.1; 1.2; 1.3; 2.1; 2.2; 2.3; 1.1; 2.2; 3.3];
col3 = [1;1;1;2;2;2;3;3;3];
T = table(col1, col2, col3);

X = [1,2,3];
a = [1.1, 2.3, 2.2];

%Note that the following uses implicit expansion, so X and a MUST be row vectors (or compatible),
%see https://www.mathworks.com/help/matlab/matlab_prog/compatible-array-sizes-for-basic-operations.html
aFile = T(any(T.col2 == a & T.col3 == X, 2), [1 2])

I've not looked at your loop to see what is wrong with it.

[Janhavi Srirangaraj]

Dear Guillaume,

Thank you for your answer. Your answer was useful.

However, Expanding on my question, I have data table which are split into n groups using 'findgroups' and the indices of the groups are stored similar to col3 of T. I needed to find the smallest value of col2 in each group for my data. I have done the same using 'splitapply' and stored it in a seperate matrix representative as 'a' (the values in 'a' however are arbitrarily assigned and not the smallest ones for each group). As asked primarily, I wished to obtain values of col1 corresponding to min values of col2, for each group (indexed in col3). I thought that the best way to select specific contents of col1 in this case is to match values in col2 and col3 with a and X respectively. Would you think it is a good approach, especially when my data has 1000s of values per column and 20+ groups?

Answer 2

[Peter Perkins]

The original question you've asked only vaguely matches your second description of, "I wished to obtain values of col1 corresponding to min values of col2, for each group (indexed in col3)."

This second thing is easy. It could be done with rowfun, splitapply, or (in more recent versions of MATLBA) with groupsummary. You mentioned splitapply, let's go with that. Given this function

function [c1Min,c2Min] = myfun(c1,c2)
[c2Min,iMin] = min(c2);
c1Min = c1(iMin);

and your table T, this

>> group = unique(col3);
>> Tmin = table(group);
>> [Tmin.c1Min,Tmin.c2Min] = splitapply(@myfun,T(:,1:2),T.col3)
Tmin =
  3×3 table
    group    c1Min    c2Min
    _____    _____    _____
      1      "n1"      1.1 
      2      "n4"      2.1 
      3      "n7"      1.1

gets you what I think you asked for. Notice that I have converted your col1 to string, I strongly suggest NOT using char matrices. categorical would be another possible choice, although col1 seems to have no repetitions.

You'll need to clarify your original question if it is not the above.

[Janhavi Srirangaraj]

Thank you for your answer, this works as well. The previous one suited my script better though.