Sort all columns in a cell array with labels

Hello everyone

I have the following cell array with text and numeric data:

>> M
M =
  5×4 cell array
    ''     'Ana'       'Delta'     'Oscar'   
    'B'    [0.9058]    [0.9058]    [0.9058]
    'C'    [0.1270]    [0.1270]    [0.1270]
    'D'    [0.9134]    [     4]    [     2]
    'E'    [0.6324]    [0.6324]    [0.6324]

I would like to sort the data in columns 2, 3 and 4 in descending order (largest to smallest) but also with labels (column 1) and also with labels Ana, Delta and Oscar. I am aware that sortrows can be used for sorting data using a particular column, but I am not sure of the indexing for sortrows or an efficient way of sorting data in columns 2-4 in my cell array M and at the same time having labels next to them.

Thank you for your help.

Suha.

[Stephen23]

@Suha: you should really consider putting your data into a table, which natively store data with headers. Then you could apply lots of other functions to the data without having to worry about the first row and column being special cases, e.g. sorting the rows without requiring any indexing at all. It also stores the columns as arrays of an appropriate class, which makes working with the data much easier (putting lots of scalar numeric data into cells of a cell array just makes working with numeric data much more complex than it needs to be).

[Suha]

I turned the cell array into a table using cell2table, but then got stuck trying to sort the data...

Answer 1

[Stephen23]

If you do not want to use a table then you would be better off keeping your numeric and cell-of-character data separately, e.g.:

hdr = {'Ana','Delta','Oscar'}
typ = {'B','C','D','E'};
mat = [0.9058,0.9058,0.9058;...
       0.1270,0.1270,0.1270;...
       0.9134,     4,     2;...
       0.6324,0.6324,0.6324];

Then you can trivially sort by rows as you choose:

>> [mat,idx] = sortrows(mat)
mat =
   0.12700   0.12700   0.12700
   0.63240   0.63240   0.63240
   0.90580   0.90580   0.90580
   0.91340   4.00000   2.00000
idx =
   1
   2
   3
   4
>> typ = typ(idx);
>> typ{:}
ans = B
ans = C
ans = D
ans = E

[Suha]

Thank you this works. Could you please tell me if there is a way of modifying the command below so that idx comes out as a matrix for all sorted columns in mat, and then I can put the labels back in, as you have done in the next step:

[mat,idx] = sortrows(mat)

Thank you !!

[Stephen23]

"Could you please tell me if there is a way of modifying the command below so that idx comes out as a matrix for all sorted columns in mat"

It is not clear what you mean. Do you want to sort each column independently?

[Suha]

Yes, I would like to sort each column independently and then for each column I would like to have the labels (e.g. B, C, D, E).

Thank you

[Stephen23]

@Suha: each column might have a different order: is that what you want?

>> [mat,idx] = sort(mat,1)
mat =
   0.12700   0.12700   0.12700
   0.63240   0.63240   0.63240
   0.90580   0.90580   0.90580
   0.91340   4.00000   2.00000
idx =
   2   2   2
   4   4   4
   1   1   1
   3   3   3
>> out = typ(idx)
'C'  'C'  'C'
'E'  'E'  'E'
'B'  'B'  'B'
'D'  'D'  'D'

[Suha]

@Suha: each column might have a different order: is that what you want?

Yes, that's exactly what I want ! Thank you. It works :)

Answer 2

[Birdman]

Something like this?

[~,idx]=sort(cell2mat(M(2:end,2:end)),1,'descend');
M(sort(idx)+1,:)=M(idx+1,:)

or

[~,idx]=sortrows(M(2:end,2:end),'descend');
M(sort(idx)+1,:)=M(idx+1,:)

[Suha]

Thank you. This worked only for the first column:

>> M

M =

5×4 cell array

    ''     'Oscar'    'Charlie'    'Maggie'
    'A'    [    5]    [      4]    [    54]
    'B'    [    7]    [     44]    [     2]
    'C'    [   71]    [      4]    [    21]
    'D'    [   41]    [      7]    [    10]

>> [~,idx]=sortrows(M(2:end,2:end),'descend'); M(sort(idx)+1,:)=M(idx+1,:)

M =

5×4 cell array

    ''     'Oscar'    'Charlie'    'Maggie'
    'C'    [   71]    [      4]    [    21]
    'D'    [   41]    [      7]    [    10]
    'B'    [    7]    [     44]    [     2]
    'A'    [    5]    [      4]    [    54]

>> idx

idx =

     3
     4
     2
     1

[Birdman]

What did you expect?

[Suha]

I would like to have all columns sorted, second and third column too, and their index shown in a 4 x 3 matrix idx. Is this possible? Thank you !!

[Birdman]

Use this one,

[~,idx]=sort(cell2mat(M(2:end,2:end)),1,'descend');
M(sort(idx)+1,:)=M(idx+1,:)

[Suha]

I have an idx matrix now but not all columns in M get sorted from largest to smallest:

>> [~,idx]=sort(cell2mat(M(2:end,2:end)),1,'descend'); M(sort(idx)+1,:)=M(idx+1,:)

M =

5×4 cell array

    ''     'Oscar'    'Charlie'    'Maggie'
    'A'    [    5]    [      4]    [    54]
    'C'    [   71]    [      4]    [    21]
    'D'    [   41]    [      7]    [    10]
    'B'    [    7]    [     44]    [     2]

Sorry for so many follow up questions!

[Birdman]

It worked perfectly in my case, where each column was sorted independently. Make sure you enter the code correctly, otherwise it should work. I can not attach a screenshot of it at the moment.

[Suha]

Thank you for your help ! :)