Hello,
Suppose I have a table
x = ['a';'b';'a','c'];var_a = [1:4]';var_b = [6:9]';var_c = [13:16]';T = table(x,var_a,var_b,var_c)
I want to create a new column, let's call it y that uses the value in column x to choose from the other columns. For example in row 1,
x(1) = 'a', therefore y(1) = var_a(1)
x(2) = 'b', therefore y(2) = var_b(2)
My final table would look like this:
x var_a var_b var_c Y _ _____ _____ _____ __ a 1 6 13 1 b 2 7 14 7 a 3 8 15 3 c 4 9 16 16
My actual table is many thousands of rows so I have been trying to solve this using rowfun or varfun but an added complication is that I don't know the variable names in advance. The columns will be 'var_' but the 'a','b','c' are user supplied codes.
Using rowfun? The logic is .. for each row, get the value from column x, make a string 'var_?', find which column is called 'var_?', set y = the value in column 'var_?'.
% find the columns called var_
col = cellfun(@isempty, regexp(T.Properties.VariableNames, 'var.')); % for row 1, find which column is the right one for the value in x
col2 = cellfun(@isempty, regexp(T.Properties.VariableNames(~col), strcat('var_', T.x(1)))) % set y to the right value
y = max(~col2.*table2array(T(1,~col)));
This works for each row but I don't know how to built this into a rowfun statement that will work on the table. Or am I overcomplicating things!? Is there an easier way to do this?
Best Answer