How to extract column index based on value in another column?

Hi everyone,

I have a table with 5 columns:

I want to create a new column (6th) where each row value corresponds to the value of the first 4 columns (1:4) where the number from column 5 is in.

So for example row 1:

value of column 5 is 2, and present in column 1, so value of column 6 should be 1, and so forth (2,4,1,3,2,1).

I apologize if my question is confusing and very much appreciate your time and help!

Answer 1

[Star Strider]

Try something like this —

A = [2 4 1 3 2; 3 1 4 2 3; 2 4 1 3 4; 4 2 3 1 4; 1 3 2 4 4];
T1 = array2table(A, 'VariableNames',{'tar1','tar2','tar3','tar4','probe'})

T1 = 5×5 table
    tar1    tar2    tar3    tar4    probe
    ____    ____    ____    ____    _____

     2       4       1       3        2  
     3       1       4       2        3  
     2       4       1       3        4  
     4       2       3       1        4  
     1       3       2       4        4  

Col6 = arrayfun(@(k)find(T1{k,1:4}==T1{k,5}), 1:size(T1,1)).';
T1 = addvars(T1,Col6,'After',5)

T1 = 5×6 table
    tar1    tar2    tar3    tar4    probe    Col6
    ____    ____    ____    ____    _____    ____

     2       4       1       3        2       1  
     3       1       4       2        3       1  
     2       4       1       3        4       2  
     4       2       3       1        4       1  
     1       3       2       4        4       4  

.

[Anastasiia Khibovska]

Thank you very much! I realized I had text instead of numbers, so I had to convert my table first for your code to work!

[Star Strider]

As always, my pleasure!

Answer 2

[Voss]

% first I create a table of random data
n_rows = 15;
data = zeros(n_rows,4);
for ii = 1:n_rows
    data(ii,:) = randperm(4);
end
t = array2table([data randi(4,n_rows,1)], ...
    'VariableNames',{'tar1','tar2','tar3','tar4','probe'})

t = 15×5 table
    tar1    tar2    tar3    tar4    probe
    ____    ____    ____    ____    _____

     4       1       2       3        3  
     1       4       2       3        4  
     1       3       2       4        2  
     3       2       4       1        1  
     3       4       2       1        4  
     1       2       4       3        4  
     3       2       4       1        1  
     3       4       1       2        1  
     1       3       4       2        1  
     2       1       4       3        3  
     1       2       4       3        3  
     2       3       1       4        1  
     4       3       2       1        4  
     1       2       4       3        3  
     3       1       2       4        2  

% now construct the new column
n_rows = size(t,1);
new_col = zeros(n_rows,1);
for ii = 1:n_rows
    [~,new_col(ii)] = ismember(t{ii,5},t{ii,1:4});
end
t.new_column = new_col

t = 15×6 table
    tar1    tar2    tar3    tar4    probe    new_column
    ____    ____    ____    ____    _____    __________

     4       1       2       3        3          4     
     1       4       2       3        4          2     
     1       3       2       4        2          3     
     3       2       4       1        1          4     
     3       4       2       1        4          2     
     1       2       4       3        4          3     
     3       2       4       1        1          4     
     3       4       1       2        1          3     
     1       3       4       2        1          1     
     2       1       4       3        3          4     
     1       2       4       3        3          4     
     2       3       1       4        1          3     
     4       3       2       1        4          1     
     1       2       4       3        3          4     
     3       1       2       4        2          3