How to sort a matrix based on two columns

Question

I am trying to sort a cell matrix based on two columns using MATLAB:

ticker date price
msft 1/9/11 19.8
msft 1/8/11 18.7
csco 1/8/11 9.8
csco 1/9/11 10.0

I want to sort the matrix first by date then by ticker, so the result I want is:

ticker date price
csco 1/8/11 9.8
msft 1/8/11 18.7
csco 1/9/11 10.0
msft 1/9/11 19.8

Anyone knows how I can do that? THanks.

Welcome to StackOverflow! Please read the [FAQ](http://stackoverflow.com/faq) to understand how it works. Also, if you can decide on the best answer, accept it by clicking on the check mark next to it. — Jacob, Jul 13 '11 at 17:01

Jacob · Answer 1 · 2011-07-13T16:19:35.980

I would convert the date column into the corresponding serial date number with datenum and use sortrows with the required ordering to sort the data.

Here's some code ; assume d is your 4x3 cell array from the example you gave:

d = 

'msft'    '1/9/11'    [19.8000]
'msft'    '1/8/11'    [18.7000]
'csco'    '1/8/11'    [ 9.8000]
'csco'    '1/9/11'    [     10]

Convert the date column to numbers with arrayfun
```
d(:,2) = arrayfun(@(x){datenum(x)},d(:,2));
```
Sort the matrix with sortrows (date first then ticker symbol)
```
d = sortrows(d,[2 1]);
```

Replace the date with a string with datestr.

d(:,2) = arrayfun(@(x){datestr(x{1},'mm/dd/yy')},d(:,2));

elegant use of functional style. – BlessedKey Jul 13 '11 at 16:32 — BlessedKey, Jul 13 '11 at 16:32

score 1 · Answer 2 · answered Jul 13 '11 at 16:10

concatenate the two cell arrays into a single cell array of strings. Sort the resultant cell array of strings and get the order of the indicies. Use those indicies to sort the original cell arrays

a = {'1', '2', '2', '3'}
b = {'a', 'b', 'a', 'a'}
for i = 1:length(a)
   ab{i} = [a{i},b{i}]
end
[s,si] = sort(ab);
sorted_a = a(si);
sorted_b = b(si);

following Jacob's suggestion, if the date format you are using is not sortable alphabetically you can replace

   ab{i} = [a{i},b{i}]

with

   ab{i} = [num2str(datenum(a{i})),b{i}]

score 0 · Answer 3 · edited Jul 01 '17 at 10:16

0

[tblB,index] = sortrows(tblA,{'Height','Weight'},{'ascend','descend'})

for your specific case:

ticker date price
msft 1/9/11 19.8
msft 1/8/11 18.7
csco 1/8/11 9.8
csco 1/9/11 10.0

[tblB,index] = sortrows(tblA,{'date','price'},{'ascend','descend'})

edited Jul 01 '17 at 10:16

CraftedCart

651
7
16

answered Jun 30 '17 at 21:39

Donny Sun

1

score 0 · Answer 4 · edited May 23 '17 at 12:24

Using a similar idea to @Jacob's, here is a slightly easier solution:

%# your cell array
d = {
    'msft' '1/9/11' 19.8
    'msft' '1/8/11' 18.7
    'csco' '1/8/11'  9.8
    'csco' '1/9/11' 10.0
};

%# extract 2 columns, convert to serial date, sort, apply order to original data
[~,idx] = sortrows([datenum(d(:,2),'mm/dd/yy') cell2mat(d(:,3))], [1 2]);
d = d(idx,:)

The before:

d = 
    'msft'    '1/9/11'    [19.8]
    'msft'    '1/8/11'    [18.7]
    'csco'    '1/8/11'    [ 9.8]
    'csco'    '1/9/11'    [  10]

The after:

d = 
    'csco'    '1/8/11'    [ 9.8]
    'msft'    '1/8/11'    [18.7]
    'csco'    '1/9/11'    [  10]
    'msft'    '1/9/11'    [19.8]

How to sort a matrix based on two columns

4 Answers4