Sort cell array based on 2 columns with date and time (strings)

2 views (last 30 days)
Christos Antonakopoulos on 30 Nov 2015
Hello, i have a cell array 395*8 cell.
Columns 3 and 4 have
'01/20/2014' '19:22:05.0120'
'01/15/2014' '19:22:20.8020'
'01/17/2014' '20:01:26.0630'
'01/16/2014' '20:04:21.1260'
and so on.
How can i sort them based firstly on column 3 and then column 4.
sortrows did not work
sortrows(cellname,[3 4]) because i have string values.
Stephen23 on 30 Nov 2015
Edited: Stephen23 on 30 Nov 2015
Actually the easiest solution is to use ISO 8601 date strings to store your dates, and then you can simply use sort or sortrows, because a lexicographical sort will put them into the correct date order. It really is that easy!
You can use my FEX submissions datestr8601 and |datenum8601 to easily convert between ISO 8601 dates and MATLAB serial date numbers or date vectors.

Andrei Bobrov on 30 Nov 2015
[~,ii] = sort(datenum(strcat(cellname(:,3),{' '},cellname(:,4))));
out = cellname(ii,:);
Mohammad Abouali on 1 Dec 2015
Edited: Mohammad Abouali on 1 Dec 2015
I remove my answer. I like yours more.
Christos Antonakopoulos on 1 Dec 2015
Very good code Andrei thanx

Stephen23 on 30 Nov 2015
Edited: Stephen23 on 30 Nov 2015
This is trivial if ISO 8601 date strings are used, because then a lexicographical sort really does put them into the correct date order:
% Original cell array:
X = {
1, 5, '01/20/2014', '19:22:05.0120'; ...
2, 6, '01/15/2014', '19:22:20.8020'; ...
3, 7, '01/17/2014', '20:01:26.0630'; ...
4, 8, '01/16/2014', '20:04:21.1260'};
% replace date strings with ISO 8601 dates:
X(:,3) = cellstr(datestr(datevec(X(:,3),'mm/dd/yyyy'),29));
% sort:
[~,idx] = sortrows(X(:,3:4));
Y = X(idx,:)
gives us this output:
Y =
[2] [6] '2014-01-15' '19:22:20.8020'
[4] [8] '2014-01-16' '20:04:21.1260'
[3] [7] '2014-01-17' '20:01:26.0630'
[1] [5] '2014-01-20' '19:22:05.0120'
Christos Antonakopoulos on 1 Dec 2015
Thank you i will have this in mind with the iso 8601 dates.