Sorting rows only with name of column

Question

gcicceri 2020-6-11

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/546879-sorting-rows-only-with-name-of-column

评论： dpb 2020-6-19

采纳的回答： Image Analyst

在 MATLAB Online 中打开

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.

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Image Analyst 2020-6-12

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/546879-sorting-rows-only-with-name-of-column#answer_450113

在 MATLAB Online 中打开

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 个评论
显示 9更早的评论隐藏 9更早的评论

gcicceri 2020-6-16

在 MATLAB Online 中打开

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

dpb 2020-6-19

编辑：dpb 2020-6-19

在 MATLAB Online 中打开

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 2020-6-19

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

dpb 2020-6-19

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 2020-6-12

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/546879-sorting-rows-only-with-name-of-column#answer_449912

编辑：dpb 2020-6-12

在 MATLAB Online 中打开

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 个评论
显示 4更早的评论隐藏 4更早的评论

gcicceri 2020-6-16

编辑：dpb 2020-6-16

在 MATLAB Online 中打开

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 2020-6-16

编辑：dpb 2020-6-16

在 MATLAB Online 中打开

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 2020-6-17

编辑：gcicceri 2020-6-17

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 2020-6-17

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 2020-6-12

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/546879-sorting-rows-only-with-name-of-column#answer_449913

在 MATLAB Online 中打开

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