How to order variables in a table

17 views (last 30 days)
012786534 on 25 Oct 2019
Commented: 012786534 on 28 Oct 2019
Hello,
I find myself with a curious problem: I need to order the variables of a table without knowing in advance if all the variables will be present. I mean, I know what all the possible variables might be (possible_vars), and I know in what order I want them (desired_order). But I dont know if all the variables will be present (at least some of them will be but I dont know which ones). And if a variable is missing then it is missing, thats fine. The desired order should be kept just without that variable.
So how could I order table t below based on the desired order but without knowing in advance what all the variables will be ?
posible_vars = {'x', 'p', 'o', 'v', 's', 'a'}
desired_order = {'a', 'v', 'o', 'v', 's', 'x'}
% data
x = [1:3]';
a = {'A';'B';'C'};
o = {'A';'B';'C'};
t = table(x, a, o)

Adam Danz on 25 Oct 2019
Edited: Adam Danz on 26 Oct 2019
The solution to sorting table columns according to a desired order is below. However, your current method requires that the variable has a certain name and this is not recommended. A variable name should never be used in code. Variable names are merely a way for humans to organize their data and quickly understand what a variable is. The code should never rely on the name of a variable. I've therefore changed the way you are forming the table so that your variable names are not used by the code.
p = [1:3]';
q = {'A';'B';'C'};
r = {'A';'B';'C'};
t = table(p,q,r,'VariableNames',{'x', 'a', 'o'});
% even if your variable names match the "VariableNames".
desired_order = {'a', 'v', 'o', 'v', 's', 'x'};
[~, varOrder] = ismember(t.Properties.VariableNames, desired_order);
[~, resortOrder] = sort(varOrder);
t = t(:,resortOrder)
% ans =
% 3×3 table
% a o x
% _____ _____ _
% {'A'} {'A'} 1
% {'B'} {'B'} 2
% {'C'} {'C'} 3
In the above solution, case matters. If a column header is named 'X' (upper case) it will not be sorted based on the index of 'x' (lower case). If you'd like to ignore case, you can use lower() to make sure the matching is all done in lower case no matter what case your headers are in.
Example:
[~, varOrder] = ismember(lower(t.Properties.VariableNames), desired_order);
% ^^^^^

1 Comment

012786534 on 28 Oct 2019