1

How do I sort a column based on the values in another column in MATLAB?

Column A shows position data (it is neither ascending or descending in order) Column B contains another column of position data. Finally column C contains numerical values. Is it possible to link the first position value in B with its numerical value in the first cell of C? Then after this I want to sort B such that it is in the same order as column A with the C values following their B counterparts?The length of my columns would be 1558 values.

Before case;

A     B     C
1      4      10
4      1      20
3      5      30
5      2      40
2      3      50

After Case;

A     B     C
1      1      20
4      4      10
3      3      50
5      5      30
2      2      40

Basically A and B became the same and Column C followed B.

Adriaan
  • 17,741
  • 7
  • 42
  • 75
Jerry
  • 107
  • 3
  • 10

4 Answers4

6

Since you don't want things necessarily in ascending or descending order, I don't think any built-in sorting functions like sortrows() will help here. Instead you are matching elements in one column with elements in another column.

Using [~,idx]=ismember(A,B) will tell you where each element of B is in A. You can use that to sort the desired columns. 

M=[1      4      10
   4      1      20
   3      5      30
   5      2      40
   2      3      50];
A=M(:,1); B=M(:,2); C=M(:,3);
[~,idx]=ismember(A,B); 

sorted_matrix = [A B(idx) C(idx)]
Geoff
  • 1,202
  • 10
  • 18
  • Nice use of `ismember` I forgot that it also returns locations. Good solution because of all the error handling that it does. – Matt Sep 16 '15 at 15:26
  • How do I implement this if some of the values in column A are negative – Jerry Sep 16 '15 at 18:03
  • @Jerry Your comment helped me catch an error in the original solution. It's fixed now and should work even with negative entries in A (assuming there are corresponding negative entries in B). – Geoff Sep 16 '15 at 18:11
  • @Geoff I'm still getting the same error when I use ``%Create matrix% Matrix = [Control, LES2_54_pos, LES2_54_U]; %Sort Matrix% A=Matrix(:,1); B=Matrix(:,2); C=Matrix(:,3); [~,idx]=ismember(A,B); sorted_matrix = [A B(idx) C(idx)]`` I'm not sure if its relevant but the negative values in A dont match to negative values in B before the sorting. Hence why the sorting is required. – Jerry Sep 16 '15 at 18:25
  • @Geoff ``Subscript indices must either be real positive integers or logicals. Error in Sort_data (line 17) sorted_matrix = [A B(idx) C(idx)]`` – Jerry Sep 16 '15 at 18:28
  • If the matrix is layed out like the following ``>> M=[-1 4 10 4 1 20 -3 -5 30 5 -2 40 2 3 50]; A=M(:,1); B=M(:,2); C=M(:,3); [~,idx]=ismember(A,B); sorted_matrix = [A B(idx) C(idx)] Subscript indices must either be real positive integers or logicals.`` the error can be reproduced. The negatives do no match up in both columns – Jerry Sep 16 '15 at 18:29
  • Ok, it wasn't clear by your previous comment that the negatives may appear in one column but not the other. This is crucial since `idx=0` whenever there isn't an exact match. To alleviate this try `[~,idx]=ismember(abs(A),abs(B))` . For more general examples this could be dangerous if you have, e.g., -1 and 1 both show up in the same column. – Geoff Sep 16 '15 at 18:36
  • 1
    @Jerry So, what must be the output for `M=[-1 4 10 4 1 20 -3 -5 30 5 -2 40 2 3 50]`? Edit your question with such a case? – Divakar Sep 16 '15 at 18:39
  • Part of both columns A and B do have an overlap i.e. 70 and -70 etc Is there any other way around this? – Jerry Sep 16 '15 at 18:40
  • @Jerry Please edit the original post with a new matrix `M` and the desired corresponding result so that it is clear what you are looking for. – Geoff Sep 16 '15 at 19:12
  • @Jerry I'm confused as well. I thought `A` and `B` had identical elements, just in different orders. This isn't the case for this new matrix. Please clarify. – beaker Sep 16 '15 at 20:23
  • @Geoff I think I figured it out. Try `[~,idx]=ismember(abs(A),abs(B))` on the new matrix. – beaker Sep 16 '15 at 20:38
  • @beaker nice! but I guess you missed my second to last comment :) – Geoff Sep 16 '15 at 20:40
  • Ha! I did... got caught up in trying to reconcile the numbers ;) – beaker Sep 16 '15 at 20:51
1

Given M = [A B C]:

M =

    1    4   10
    4    1   20
    3    5   30
    5    2   40
    2    3   50

You need to sort the rows of the matrix excluding the first column:

s = sortrows(M(:,2:3));

s =

    1   20
    2   40
    3   50
    4   10
    5   30

Then use the first column as the indices to reorder the resulting submatrix:

s(M(:,1),:);

ans =

    1   20
    4   10
    3   50
    5   30
    2   40

This would be used to build the output matrix:

N = [M(:,1) s(M(:,1),:)];

N =

    1    1   20
    4    4   10
    3    3   50
    5    5   30
    2    2   40

The previous technique will obviously only work if A and B are permutations of the values (1..m). If this is not the case, then we need to find the ranking of each value in the array. Let's start with new values for our arrays:

    A    B   C
    1    5   60
    6    1   80
    9    6   60
   -4    9   40
    5   -4   30

We construct s as before:

s = sortrows([B C]);

s =

   -4   30
    1   80
    5   60
    6   60
    9   40

We can generate the rankings one of two ways. If the elements of A (and B) are unique, we can use the third output of unique as in this answer:

[~, ~, r] = unique(A);

r =

   2
   4
   5
   1
   3

If the values of A are not unique, we can use the second return value of sort, the indices in the original array of the elements in sorted order, to generate the rank of each element:

[~, r] = sort(A);

r =

   4
   1
   5
   2
   3

[~, r] = sort(r);

r =

   2
   4
   5
   1
   3

As you can see, the resulting r is the same, it just takes 2 calls to sort rather than 1 to unique. We then use r as the list of indices for s above:

M = [A s(r, :)];

M =

    1    1   80
    6    6   60
    9    9   40
   -4   -4   30
    5    5   60
Community
  • 1
  • 1
beaker
  • 16,331
  • 3
  • 32
  • 49
1

Powerful combo of bsxfun and matrix-multiplication solves it and good for code-golfing too! Here's the implementation, assuming M as the input matrix -

[M(:,1) bsxfun(@eq,M(:,1),M(:,2).')*M(:,2:3)]

Sample run -

>> M
M =
     1     4    10
     4     1    20
     3     5    30
     5     2    40
     2     3    50
>> [M(:,1) bsxfun(@eq,M(:,1),M(:,2).')*M(:,2:3)]
ans =
     1     1    20
     4     4    10
     3     3    50
     5     5    30
     2     2    40
Community
  • 1
  • 1
Divakar
  • 218,885
  • 19
  • 262
  • 358
  • why have you included ``*M(:,2:3)`` when I use this code it orders the matrix but my original values are multiplied which now gives me incorrect values – Jerry Sep 16 '15 at 18:19
0

If you must retain the order of A then use something like this

matrix = [1 4 10; 4 1 20; 3 5 30; 5 2 40; 2 3 50];
idx = arrayfun(@(x) find(matrix(:,2) == x), matrix(:,1));
sorted = [matrix(:,1), matrix(idx,2:3)];
Matt
  • 2,554
  • 2
  • 24
  • 45
  • Would you be able to explain why I am getting the following error Error using arrayfun Non-scalar in Uniform output, at index 1, output 1. Set 'UniformOutput' to false. Error in Sort_data (line 14) idx = arrayfun(@(x) find(Matrix(:,2) == x), Matrix(:,1)); – Jerry Sep 16 '15 at 17:55
  • I would guess that happens because you have repeated values in column 2. If that is true you need to determine which one to keep. If the first, then change to `find(matrix(:,2) == x, 1, 'first')`. – Matt Sep 17 '15 at 02:51
  • @Jerry if you have repeated values for either column `A` or `B` you will have an unbalanced answer. If there are repeated values in `B` this means you have at least 1 vacant `A` entry and the 3rd line will error. Also you have at least 2 rows of `B` that will match to `A`. Sounds like you need cleanup your numbers beforehand. You can use `unique` with the `rows` option if all 3 columns are the same. If they are not you need to use some selection criteria like max/min `C` – Matt Sep 17 '15 at 13:27