Best way to sort & save num data+indexof1st column ?

Dear community,

I have a table / cell array as:

 NAME       X1               X2                X3       X4
A  0.116299086  -0.413185158  -0.172190135  -0.040412197
B  0.108237212  -0.17513233  -0.050167011  0.152124077
C  1.60E-05  -0.286240706  -0.143676596  0.037703041
D  0.054848362  -0.100261385  0.014409355  0.233691507
E  -0.012845018  -0.336929674  -0.099843805  0.05228656
F  0.005128934  -0.344842269  -0.152310505  0.022771614
G  0.181237905  -0.506421902  -0.232372741  -0.127148492

numerical values in each cell are an example. I want to sort the whole table by values in X1 (in descending order), and save both X1 and NAME values when sorted. And I want the same repeated for all X2, X3, X4, etc. my first attemp is as follow:

[nbrow,nbcolumn]=size(R);% specifiy array size of the R matrix 
StorageVALUE = cell(nbrow,nbcolumn);  %specifiy storage size to save sorted values
StorageNAME = cell(nbrow,nbcolumn);  %specifiy storage size to save sorted names
for i = 2 : nbcolumn      
   sortedcells = sortrows(R, i, 'descent');
   StorageVALUE{i} = sortedcells; 
   StorageNAME{i} = (sortedcells,1)
end

Then I want to repeat the whole process for Names(row): sort values in the "A" row and record all values in descending order, then B, C, D, ... as above. (so I was thinking to use a 2nd loop similar as above)

Considering I have many rows & column in true dataset (several thousands for both rows & columns), sorting the whole table might not be the best approach for computing time (for example, to keep sorted info of the 2 columns of interest would be smarter, if it doesn't change the structure of data during the loop).

what would be the best option in terms of dataformat (table, cellarray,matrix) and functions for sorting/storaging?

Thank you very much for your help and time,

Answer 1

[Arnab Sen]

Hello Taro,

I understand that you would like sort all the columns of a matrix based on the a single column. You can consider the following script to sort all the columns of a matrix named 'data' based on the sorting of 2nd column.

[B,I]=sort(data(:,2),'descend');

for i=1:numel(data(1,:))
   x=data(:,i);
    data(:,i)=x(I);
end

Here, at the same time of sorting we store the rearrangement of the indices in vector 'I' and in the next for loop we rearrange other columns as well based on the rearranged index I.

[Taro Ichimura]

Thanks for your example about matrix. From your answer I could figure out how to proceed: what I wanted was like this (for a R matrix). Note that I had to import the "NAME" column from the table given in my original question (A,B,C,D,...) then I used the "I" variable to get back the NAME.

%%get size of R matrix
[nbrow,nbcolumn] = size(R);
%%sort data
for k = 1 : nbcolumn
  [B,I]=sort(R(:,k),'descend');
  StorageNAME(:,k) = B;
  StorageVALUE(:,k) = NAME(I,1);
end

However, the sorting process, when applied to all rows and all columns on large dataset, so I would like to know, as asked in the first question, if there are faster/more efficient way in terms of computational time, to do that, such as using Table-based functions instead of matrix?

Best regards,

Answer 2

[Sean de Wolski]

What about sortrows? Pass in the rows you want in the order you want?