Sorting rows only with name of column
Show older comments
Hi guys, my question is:
I have a table with 5 rows and 10 columns like this:
Datetime Var1 Var2 Var3 Var4 Var5
________________________________________________________
01-01-90 0.025001 0 0.058841 0 0.059898
01-02-90 0.025318 0 0.068993 0 0.067252
01-03-90 0.025318 0 0.074108 0 0.074613
01-04-90 0.025318 0.019803 0.10536 0 0.10008
01-05-90 0.03774 0.020203 0.1372 0 0.16455
......... etc
My question is: How do I sort each row by column name and get a result like this?
Datetime 1 2 3 4 5
___________________________________
01-01-90 Var5 Var3 Var1 Var2 Var4
01-02-90 Var3 Var5 Var1 Var2 Var4
01-03-90 Var5 Var3 Var1 Var2 Var4
01-04-90 Var3 Var5 Var1 Var2 Var4
01-05-90 Var5 Var3 Var1 Var2 Var4
.......... etc
thanks a lot.
Accepted Answer
Image Analyst
on 12 Jun 2020
Try this:
% Setup table.
m = randi(9, 10, 5); % Random integers
dateTimes = repmat(datestr(now), 10, 1)
t1 = table(dateTimes, m(:, 1), m(:, 2), m(:, 3), m(:, 4), m(:, 5), 'VariableNames', {'Datetime', 'Var1', 'Var2', 'Var3', 'Var4', 'Var5'})
% Now we have our input table and we can begin
% Get the variable names from table t1.
t1VariableNames = t1.Properties.VariableNames
% Make an output table called t2.
t2VariableNames = {'Datetime', 'Pos1', 'Pos2', 'Pos3', 'Pos4', 'Pos5'}
t2 = table("Datetime", "Pos1", "Pos2", "Pos3", "Pos4", "Pos5", 'VariableNames', t2VariableNames)
% Now sort each row by columns:
for row = 1 : size(t1, 1)
[~, sortOrder] = sort(t1{row, 2:end}, 'descend');
t2(row, 2:end) = t1VariableNames(sortOrder + 1);
t2(row, 1) = t1(row, 1); % Transfer datetime column.
end
% Display result in command window:
t2
You get:
t2 =
10×6 table
Datetime Pos1 Pos2 Pos3 Pos4 Pos5
______________________ ______ ______ ______ ______ ______
"12-Jun-2020 08:32:30" "Var4" "Var1" "Var2" "Var3" "Var5"
"12-Jun-2020 08:32:30" "Var2" "Var1" "Var3" "Var5" "Var4"
"12-Jun-2020 08:32:30" "Var5" "Var4" "Var1" "Var3" "Var2"
"12-Jun-2020 08:32:30" "Var1" "Var2" "Var4" "Var5" "Var3"
"12-Jun-2020 08:32:30" "Var2" "Var5" "Var3" "Var4" "Var1"
"12-Jun-2020 08:32:30" "Var3" "Var1" "Var2" "Var4" "Var5"
"12-Jun-2020 08:32:30" "Var2" "Var3" "Var1" "Var4" "Var5"
"12-Jun-2020 08:32:30" "Var1" "Var4" "Var2" "Var3" "Var5"
"12-Jun-2020 08:32:30" "Var2" "Var3" "Var1" "Var4" "Var5"
"12-Jun-2020 08:32:30" "Var3" "Var4" "Var1" "Var2" "Var5"
11 Comments
gcicceri
on 16 Jun 2020
Image Analyst
on 16 Jun 2020
Maybe use sortrows() instead of sort(). Why are you splitting the table into two parts anyway?
gcicceri
on 18 Jun 2020
dpb
on 18 Jun 2020
Again, you don't need a loop -- compose will do it (or so will sprintf or num2str or various other ways).
The problem wasn't with writing the string; it was in trying to use just a numeric value string as the variable name for the column in the table with a MATLAB Release prior to R2019 that first introduced the capacity.
nVarNames = 3; % set the number of variables
t2VariableNames=[{'Datetime'},compose('Pos%d',1:nVNames)]
t2VariableNames =
1×4 cell array
{'Datetime'} {'Pos1'} {'Pos2'} {'Pos3'}
>>
gcicceri
on 18 Jun 2020
Image Analyst
on 18 Jun 2020
My code shows you how to create 2 tables: t1 and t2. It shows you what to pass. What you pass it determines the number and names of the rows and columns in the table. Plus the documentation also give you instructions and examples.
What you pass is all dependent upon what you have and what you want -- IA and I have both shown alternative ways to build second tables based on your example as a starting point.
How to proceed from there to something more general in nature as your follow-up suggests all depends on just what you're starting with and where you're trying to go in the end -- and we know neither of those other than for the original question.
t2 = table('VariableNames', t2VariableNames);
Error:
"The VariableNames property must contain one name for each variable in the table."
What you're doing wrong-- there are no variables in the argument list, only a list of names...
The second example also doesn't have variables, it has a list of strings that could be variable names except there's a real problem -- do NOT create multiple variables of the same base name with sequential subscripts like you show -- use an array instead. Then you do not need such lists of variables that have to try to change programmatically.
Again, the code examples both IA and I provided do work; go back to those and study the syntax to understand what is being done there.
For the above problem, revisit the way in which you produced the data that needs twenty variables and rewrite that portion of the script before you get to this part to eliminate the list in favor of an array.
Again, we've only seen what you posted for the original question so all we can do is respond to it; not what else you may be trying to do.
As the first Answers provided show, given the starting table you showed, you can generate the names for the subsequent sorted table programmatically and automagically from the information in the table you start with that is completely general for the size of the input table without any explicit variable names at all.
I'll repeat that code here:
% sort the input table variables exclusive of time; return the sort order array, ix
% sort descending by row, not column (the "2" second dimension second argument)
% only consider variables besides the first date/time column, all rows and return
% the table content of that section as an array (the "{}" to dereference the table)
[~,ix]=sort(ttmp{:,2:end},2,'descend');
% build new sorted table; show which variable was in which position instead of values
% Build a variable name for the columns; using older version than R2019 the
% column variable names must be valid MATLAB variable names so can't just use number
% The output table appends the index array values to the first column date of original
% and uses array2table() to convert the IX array to a table to catenate the two pieces.
% I used the categorical class for the variable names because is displayed at command
% window without any other markings denoting content as string "" or cell {''} so is
% somewhat mmore aesthetically pleasing to look at...other examples show either of those
% as alternatives; your choice.
% The data input to the table are the variable names from the original table
% retrieved from the .Properties.VariableNames array of names based on the
% sorted index array just obtained. The "+1" accounts for the variables that were
% sorted being columns 2:end in original table but that array is dimensions 1:nCols
% so the index to column position returned by sort() is one less than for the table.
tSortV=[ttmp(:,1) ...
array2table(categorical(ttmp.Properties.VariableNames(ix+1)), ...
'VariableNames',compose('Pos%d',1:size(ix,2)))]
tSortV =
5×6 table
DateTime Pos1 Pos2 Pos3 Pos4 Pos5
___________ ____ ____ ____ ____ ____
01-Jan-1990 Var5 Var3 Var1 Var2 Var4
02-Jan-1990 Var3 Var5 Var1 Var2 Var4
03-Jan-1990 Var5 Var3 Var1 Var2 Var4
04-Jan-1990 Var3 Var5 Var1 Var2 Var4
05-Jan-1990 Var5 Var3 Var1 Var2 Var4
>>
Observe the variable names are created based on the size of the returned index array of the sorted values -- that gets around the need to have any specific number of separate variables. Also note the indices of the sorted order are returned in an array the size of the input -- again that is generic only relying on there being a date column as the leftmost column in the input table -- there could be as many variables as wanted in the rest of the table with no changes at all in the code.
ARRAY2TABLE() is the MATLAB-supplied function that takes an array and puts it into a table by column that removes the need to address every column as a separate variable. Those are the tools TMW supplies; to use ML effectively need to use the array syntax and not try to address what is naturally an array by multiple names but by indices including the magic colon and end operators that are dynamically sized.
gcicceri
on 19 Jun 2020
dpb
on 19 Jun 2020
You'll find the time invested will be paid back amply to learn to think of using the vectorized array/matrix expressions and thereby avoid the pitfall of the multiple variables with similar names.
Don't feel badly, though; it's the normal first reaction for everybody when begin--but "the MATLAB way" will soon begin to feel natural and reverting to procedural languages and explicit variables and looping will seem so pedestrian! :)
More Answers (2)
ttmp=[table(datetime(string(Datetime),'InputFormat','MM-dd-yy','PivotYear',1900)) array2table(Var)];
>> [~,ix]=sort(ttmp{:,2:end},2,'descend')
ix =
5 3 1 2 4
3 5 1 2 4
5 3 1 2 4
3 5 1 2 4
5 3 1 2 4
>>
If need the text string, use compose.
ADDENDUM: Specifically...
>> tSortV=[ttmp(:,1) cell2table(ttmp.Properties.VariableNames(ix+1), ...
'VariableNames',compose('%d',1:size(tmp,2)))]
tSortV =
5×6 table
DateTime 1 2 3 4 5
___________ ________ ________ ________ ________ ________
01-Jan-1990 {'Var5'} {'Var3'} {'Var1'} {'Var2'} {'Var4'}
02-Jan-1990 {'Var3'} {'Var5'} {'Var1'} {'Var2'} {'Var4'}
03-Jan-1990 {'Var5'} {'Var3'} {'Var1'} {'Var2'} {'Var4'}
04-Jan-1990 {'Var3'} {'Var5'} {'Var1'} {'Var2'} {'Var4'}
05-Jan-1990 {'Var5'} {'Var3'} {'Var1'} {'Var2'} {'Var4'}
>>
or as strings instead of cellstr()
>> tSortV=[ttmp(:,1) array2table(string(ttmp.Properties.VariableNames(ix+1)),'VariableNames',compose('%d',1:size(tmp,2)))]
tSortV =
5×6 table
DateTime 1 2 3 4 5
___________ ______ ______ ______ ______ ______
01-Jan-1990 "Var5" "Var3" "Var1" "Var2" "Var4"
02-Jan-1990 "Var3" "Var5" "Var1" "Var2" "Var4"
03-Jan-1990 "Var5" "Var3" "Var1" "Var2" "Var4"
04-Jan-1990 "Var3" "Var5" "Var1" "Var2" "Var4"
05-Jan-1990 "Var5" "Var3" "Var1" "Var2" "Var4"
K>>
6 Comments
Well, you've already got them available...
>> ttmp.Properties.VariableNames(ix+1)
ans =
5×5 cell array
{'tmp5'} {'tmp3'} {'tmp1'} {'tmp2'} {'tmp4'}
{'tmp3'} {'tmp5'} {'tmp1'} {'tmp2'} {'tmp4'}
{'tmp5'} {'tmp3'} {'tmp1'} {'tmp2'} {'tmp4'}
{'tmp3'} {'tmp5'} {'tmp1'} {'tmp2'} {'tmp4'}
{'tmp5'} {'tmp3'} {'tmp1'} {'tmp2'} {'tmp4'}
>>
There's no need for any loops at all -- and I showed the form (actually two forms) to build the output table containing the datetime as well in the ADDENDUM to the Answer above -- your choice whether you want cellstr() or string() for the result:
>> [~,ix]=sort(ttmp{:,2:end},2,'descend');
>> tSortV=[ttmp(:,1) cell2table(ttmp.Properties.VariableNames(ix+1), ...
'VariableNames',compose('%d',1:size(tmp,2)))]
tSortV =
5×6 table
DateTime 1 2 3 4 5
___________ ________ ________ ________ ________ ________
01-Jan-1990 {'Var5'} {'Var3'} {'Var1'} {'Var2'} {'Var4'}
02-Jan-1990 {'Var3'} {'Var5'} {'Var1'} {'Var2'} {'Var4'}
03-Jan-1990 {'Var5'} {'Var3'} {'Var1'} {'Var2'} {'Var4'}
04-Jan-1990 {'Var3'} {'Var5'} {'Var1'} {'Var2'} {'Var4'}
05-Jan-1990 {'Var5'} {'Var3'} {'Var1'} {'Var2'} {'Var4'}
>>
is the cellstr version. The string version is:
>> [~,ix]=sort(ttmp{:,2:end},2,'descend');
>> tSortV=[ttmp(:,1) array2table(string(ttmp.Properties.VariableNames(ix+1)),'VariableNames',compose('%d',1:size(tmp,2)))]
tSortV =
5×6 table
DateTime 1 2 3 4 5
___________ ______ ______ ______ ______ ______
01-Jan-1990 "Var5" "Var3" "Var1" "Var2" "Var4"
02-Jan-1990 "Var3" "Var5" "Var1" "Var2" "Var4"
03-Jan-1990 "Var5" "Var3" "Var1" "Var2" "Var4"
04-Jan-1990 "Var3" "Var5" "Var1" "Var2" "Var4"
05-Jan-1990 "Var5" "Var3" "Var1" "Var2" "Var4"
>>
Use your table variable name in place of ttmp that was the local copy here, obviously.
ADDENDUM:
Actually, a third option is to make the variable names a categorical() variable -- if prettyness is the objective in the displayed table, this may be the weiner...
>> [~,ix]=sort(ttmp{:,2:end},2,'descend');
>> tSortV=[ttmp(:,1) array2table(categorical(ttmp.Properties.VariableNames(ix+1)),'VariableNames',compose('%d',1:size(tmp,2)))]
tSortV =
5×6 table
DateTime 1 2 3 4 5
___________ ____ ____ ____ ____ ____
01-Jan-1990 Var5 Var3 Var1 Var2 Var4
02-Jan-1990 Var3 Var5 Var1 Var2 Var4
03-Jan-1990 Var5 Var3 Var1 Var2 Var4
04-Jan-1990 Var3 Var5 Var1 Var2 Var4
05-Jan-1990 Var5 Var3 Var1 Var2 Var4
>>
dpb
on 17 Jun 2020
compose was introduced R2016 but the table variable (and/or row) names being anything other than valid MATLAB variable names wasn't introduced until R2019(not sure of a or b; I saw a note on that once but can't find it at the moment)...
You'll have to have use a variable name beginning with a letter (or upgrade)
Image Analyst
on 12 Jun 2020
Here's one way, perhaps a little easier to understand than dpb's method, though not as compact:
% Setup table.
m = randi(9, 10, 5);
t = array2table(m)
% Now sort each row by columns in descending order:
for row = 1 : size(t, 1)
t{row, :} = sort(t{row, :}, 'descend');
end
% Display result in command window:
t
Categories
Find more on Tables 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!
