Sort an array by textual date column in Mathematica

Question

Something easy I think.

I have a two dimensional list (array) of mixed date, text and numeric data sourced from a CSV. I want to be able to sort the rows by the value in a single column, which in this case is a date in text format. For example:

{{1/12/2008, Bob, 123}, {28/06/2007, Alice, 456}, {19/08/2009, Charlie, 789}}

I'd like to sort the rows in the list by the date (so that comes out in the order Alice, Bob, Charlie.)

So far I have thought that I might want to map DateList across my date column and prepend the year, month and day to the list, so it becomes:

{{2008, 12, 1, Bob, 123}, {2007, 6, 28, Alice, 456}}

Then I'm left having to do three sorts instead of one, and needing to break the array up by year. That didn't seem right and now I'm stuck. I know this ought to be simple but I can't for the life of me figure it out. Any pointers appreciated.

Thanks,

Tim

Answer 1

Perhaps this ...

l = {{"1/12/2008", Bob, 123}, {"28/06/2007", Alice, 456}, 
     {"19/08/2009", Charlie, 789}}

SortBy[l, AbsoluteTime[{#[[1]], {"Day", "Month", "Year"}}] &]   

gives

{{"28/06/2007", Alice,   456}, 
 {"1/12/2008",  Bob,     123}, 
 {"19/08/2009", Charlie, 789}}   

HTH

Edit

Note that Sort[ ] compares using OrderedQ[ ], and so it can compare lists. (Greater[ ], for example, can't).

So, the following code also works:

Sort@(Flatten@{DateList[{#[[1]],{"Day","Month","Year"}}], #[[2]], #[[3]]} & /@ l)

or perhaps more elegant:

Sort@(l/.{x_String, y__} :> Flatten@{DateList[{x, {"Day", "Month", "Year"}}], y})