Sort Cell array by data

Hi all,

I have a cell array named 'Final' which contains dates in DD/MM/YYYY HH:MM:SS format and also a column relating to wind speed and wind direction. The array looks something like this:

'08/08/2006 18:00:00  0.57368      15.2205'
'15/12/1999 12:00:00  1.94849      15.3231'
'23/11/2013 09:00:00  1.33748      15.3277'
'24/01/2007 12:00:00  1.59253      15.4576'
'15/02/2005 12:00:00  1.16488      15.4618'
'21/05/2012 00:00:00  0.55439      15.5536'
'25/11/2010 06:00:00  0.78733      15.5557'
'21/03/2007 06:00:00  1.24811      15.5585'
'25/11/2008 18:00:00  1.20435      15.5773'
'19/05/1999 03:00:00  0.63412      15.6291'

I have tried using sort and sortrows, however neither of these function appear to be working.

I must also say that this array 'Final' has been created by another piece of matlab which splits a 17yr wind database into 30 degree sectors before writing this data to a series of .TXT files before being used by another program. However, the other program needs the dates to be in descending order.

Any help would be greatly appreciated.

Regards, Kristopher

Answer 1

[Star Strider]

See if this does what you want:

Final = {'08/08/2006 18:00:00  0.57368      15.2205'
         '15/12/1999 12:00:00  1.94849      15.3231'
         '23/11/2013 09:00:00  1.33748      15.3277'
         '24/01/2007 12:00:00  1.59253      15.4576'
         '15/02/2005 12:00:00  1.16488      15.4618'
         '21/05/2012 00:00:00  0.55439      15.5536'
         '25/11/2010 06:00:00  0.78733      15.5557'
         '21/03/2007 06:00:00  1.24811      15.5585'
         '25/11/2008 18:00:00  1.20435      15.5773'
         '19/05/1999 03:00:00  0.63412      15.6291'};

dn = cellfun(@(x) datenum(x(1:19), 'dd/mm/yyyy HH:MM'), Final);
[dns,ix] = sort(dn);

FinalSorted = Final(ix);

FinalSorted = 

      '19/05/1999 03:00:00  0.63412      15.6291'
      '15/12/1999 12:00:00  1.94849      15.3231'
      '15/02/2005 12:00:00  1.16488      15.4618'
      '08/08/2006 18:00:00  0.57368      15.2205'
      '24/01/2007 12:00:00  1.59253      15.4576'
      '21/03/2007 06:00:00  1.24811      15.5585'
      '25/11/2008 18:00:00  1.20435      15.5773'
      '25/11/2010 06:00:00  0.78733      15.5557'
      '21/05/2012 00:00:00  0.55439      15.5536'
      '23/11/2013 09:00:00  1.33748      15.3277'

[Kris C]

Great piece of code Star Strider, did exactly what I was looking for.

Many thanks.

[Star Strider]

Thank you.

As always, my pleasure!

Answer 2

[Stephen23]

The whole problem could be avoided by using any ISO 8601 date format in the strings. Performing a lexicographical sort on these date strings will then result in the correct date order as well:

>> X = {
'2006/08/08 18:00:00  0.57368      15.2205'
'1999/12/15 12:00:00  1.94849      15.3231'
'2013/11/23 09:00:00  1.33748      15.3277'
'2007/01/24 12:00:00  1.59253      15.4576'
'2005/02/15 12:00:00  1.16488      15.4618'
'2012/05/21 00:00:00  0.55439      15.5536'
'2010/11/25 06:00:00  0.78733      15.5557'
'2007/03/21 06:00:00  1.24811      15.5585'
'2008/11/25 18:00:00  1.20435      15.5773'
'1999/05/19 03:00:00  0.63412      15.6291'}
>> sort(X)
ans =
1999/05/19 03:00:00  0.63412      15.6291
1999/12/15 12:00:00  1.94849      15.3231
2005/02/15 12:00:00  1.16488      15.4618
2006/08/08 18:00:00  0.57368      15.2205
2007/01/24 12:00:00  1.59253      15.4576
2007/03/21 06:00:00  1.24811      15.5585
2008/11/25 18:00:00  1.20435      15.5773
2010/11/25 06:00:00  0.78733      15.5557
2012/05/21 00:00:00  0.55439      15.5536
2013/11/23 09:00:00  1.33748      15.3277

For handling ISO 8601 dates my FEX submissions can be useful:

http://de.mathworks.com/matlabcentral/fileexchange/34095-date-vector-number-to-iso-8601-date-string

http://de.mathworks.com/matlabcentral/fileexchange/39389-iso-8601-date-string-to-serial-date-number