Sorting rows only with name of column

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.

Answer 1

[Image Analyst]

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"

[gcicceri]

Your script works but I have a problem in a for loop by following your approach to join the first column of my table (datetime) with the rest of the columns...

Take a look....

my_table(:,1):
Datetime
'01-01-90'
'01-02-90'
'01-03-90'
'01-04-90'
'01-05-90'
my_table_sort:
'Var_Name'	'Var_Name'	'Var_Name'	.......
'Var_Name'	'Var_Name'	'Var_Name'	.......
'Var_Name'	'Var_Name'	'Var_Name'	.......
'Var_Name'	'Var_Name'	'Var_Name'	.......
'Var_Name'	'Var_Name'	'Var_Name'      .......
My_code:
my_table_sort = table();
for i = 1:size(my_table, 1)
    [~,idx]=sort(my_table{i,2:end}, 2,'descend');
    my_table_sort(i,:) = my_table.Properties.VariableNames(idx+1)
    tSortV=[my_table(:,1) array2table(string(my_table_sort.Properties.VariableNames(ix+1)),'VariableNames',compose('%d',1:size(my_table,2)))]
end

The mistake says:

Error using table.init (line 380)
'1' is not a valid variable name.
Error in array2table (line 64)
    t = table.init(vars,nrows,rownames,nvars,varnames);
Error in script_2 (line 100)
    tSortV=[my_table(:,1)
    array2table(string(my_table.Properties.VariableNames(index+1)),'VariableNames',compose('%d',1:size(my_table,2)))] 

The loop works but I only collect columns without dates.

I would like a single table with dates and rows sorted as in your example above:

     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"
.................

thanks a lot..

[Image Analyst]

Maybe use sortrows() instead of sort(). Why are you splitting the table into two parts anyway?

[gcicceri]

In your script, you writed this code for create a table t2:

% Make an output table called t2.

t2VariableNames = {'Datetime', 'Pos1', 'Pos2', 'Pos3', 'Pos4', 'Pos5'}

t2 = table("Datetime", "Pos1", "Pos2", "Pos3", "Pos4", "Pos5", 'VariableNames', t2VariableNames)

My question is:

is there a way to automatically (with one loop) to create a row with Pos from 1 to 100 or from 1 to 1000?

[dpb]

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]

and for the creation of the table what do I pass?

[Image Analyst]

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.

[dpb]

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.

[gcicceri]

nVarNames = 20; 
t2VariableNames=[{"Datetime"},compose("Pos%d",1:nVarNames)];

When I create t2 and pass the variable names it gives me an error..

t2 = table('VariableNames', t2VariableNames);

Error: 
"The VariableNames property must contain one name for each variable in the table."

Am I doing something wrong?

My personal goal is to create a table and to pass the name for each variable with a range(1:20) or loop and not the single name for each variable as like:

t2 = table("Datetime", "P1", "P2", "P3", "P4", "P5", "P6", "P7", "P8", "P9", "P10", ...
    "P11", "P12", "P13", "P14", "P15", "P16", "P17", "P18", "P19", "P20", ...
    'VariableNames', t2VariableNames);

[dpb]

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]

Thank you so much! I'll take all your examples as my starting point.

[dpb]

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! :)

Answer 2

[dpb]

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>> 

[gcicceri]

Ok ix but I need string format of each column ordered for position.

Find in someway to associate index with name of column and sort.

For instance:

Date pos1 pos2 pos3 pos4 pos 5

01-01-90 Var_Name Var_Name Var_Name Var_Name Var_Name

01-02-90 Var_Name Var_Name Var_Name Var_Name Var_Name

01-03-90 Var_Name Var_Name Var_Name Var_Name Var_Name

01-04-90 Var_Name Var_Name Var_Name Var_Name Var_Name

01-05-90 Var_Name Var_Name Var_Name Var_Name Var_Name

[dpb]

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'}
>> 

[gcicceri]

I have a problem in a for loop by following your approach to join the first column of my table (datetime) with the rest of the columns...

Take a look....

my_table(:,1):
Datetime
'01-01-90'
'01-02-90'
'01-03-90'
'01-04-90'
'01-05-90'
my_table_sort:
'Var_Name'	'Var_Name'	'Var_Name'	.......
'Var_Name'	'Var_Name'	'Var_Name'	.......
'Var_Name'	'Var_Name'	'Var_Name'	.......
'Var_Name'	'Var_Name'	'Var_Name'	.......
'Var_Name'	'Var_Name'	'Var_Name'      .......

My_code:

my_table_sort = table();
for i = 1:size(my_table, 1)
    [~,idx]=sort(my_table{i,2:end}, 2,'descend');
    my_table_sort(i,:) = my_table.Properties.VariableNames(idx+1)
    tSortV=[my_table(:,1) array2table(string(my_table_sort.Properties.VariableNames(ix+1)),'VariableNames',compose('%d',1:size(my_table,2)))]
end

The mistake says: 
Error using table.init (line 380)
'1' is not a valid variable name.

Error in array2table (line 64)
    t = table.init(vars,nrows,rownames,nvars,varnames);

Error in script_2 (line 100)
    tSortV=[my_table(:,1)
    array2table(string(my_table.Properties.VariableNames(index+1)),'VariableNames',compose('%d',1:size(my_table,2)))] 

The loop works but I only collect columns without dates.

I would like a single table with dates and rows sorted as in your example above:

     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"
.................

thanks a lot..

[dpb]

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
>> 

[gcicceri]

I have same error....

that is: " '1' is not a valid variable name. ".

Is it possible that problem is relative to my Matlab's version? (I have version 2018a). So, the ‘compose’ function doesn’t works?

Could it be that my variables, starting with an 'x10292', are not recognized as strings?

[dpb]

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)

Answer 3

[Image Analyst]

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