Take the 2-minute tour ×
Mathematica Stack Exchange is a question and answer site for users of Mathematica. It's 100% free, no registration required.

I have a list of dates (ranging from 2007 to 2012) in the DD/MM/YYYY format and I need to sort this list. My problem is, when I simply use Sort[] I get:

{"01/02/2007", "01/02/2008", "01/02/2009", "01/02/2010", "01/02/2011",...}

However, I need to sort this list to get something like:

{"02/01/2007", "03/01/2007", "06/01/2007", "08/01/2007", "10/01/2007",...}

After reading the StackExchange Mathematica forum I got the following code:

Flatten[SortBy[
  Partition[
   Table[DateString[
     datelist[[v]], {"Day", "/", "Month", "/", "Year"}], {v, 1, 
     Dimensions[datelist][[1]]}], 1], 
  AbsoluteTime[{#[[1]], {"Day", "Month", "Year"}}] &]]

It works... However, it takes almost 2 seconds to compute, and, worst, I get 4 error messages:

DateString::ambig: Warning: the interpretation of the string 02/01/2007 as a date is ambiguous. >>
DateString::ambig: Warning: the interpretation of the string 10/04/2012 as a date is ambiguous. >>
DateString::ambig: Warning: the interpretation of the string 09/04/2012 as a date is ambiguous. >>
General::stop: Further output of DateString::ambig will be suppressed during this calculation. >>

Is there any way to sort this list faster without getting error messages?

share|improve this question

5 Answers 5

up vote 12 down vote accepted

Using Simon's data:

In[6]:= datelist = {"29/02/2008", "15/12/2007", "06/09/2007", 
   "06/10/2008", "05/03/2007", "24/01/2010", "19/06/2009", 
   "03/11/2009", "02/02/2010", "25/12/2009"};

We can just sort the data by the absolute time:

In[7]:= SortBy[datelist, AbsoluteTime[{#, {"Day", "Month", "Year"}}] &]

Out[7]= {"05/03/2007", "06/09/2007", "15/12/2007", "29/02/2008", "06/10/2008",
         "19/06/2009", "03/11/2009", "25/12/2009", "24/01/2010", "02/02/2010"}
share|improve this answer
    
Simple and fast! Not the fastest code, but very simple to understand. It's the one I'm going to use! Thank you! –  Rod Jan 11 '13 at 17:20
1  
Date and time math still very slow in Mma. This is faster by converting the entire list to AbsoluteTime, sorting then mapping back to DateString ...like @VLC but without the intermediary step via DateList. –  Mike Honeychurch Jan 11 '13 at 21:31

A simple string-based approach is to swap the order of day/month/year, do the Sort and then swap back again:

(*  Example data  *)
datelist = DateString[# + AbsoluteTime[{2007, 01, 01}],
 {"Day", "/", "Month", "/", "Year"}] & /@ RandomInteger[10^8, 10]

{"29/02/2008", "15/12/2007", "06/09/2007", "06/10/2008", "05/03/2007", "24/01/2010", "19/06/2009", "03/11/2009", "02/02/2010", "25/12/2009"}

With[{rule = a__ ~~ "/" ~~ b__ ~~ "/" ~~ c__ :> c <> "/" <> b <> "/" <> a},
 StringReplace[Sort@StringReplace[datelist, rule], rule]]

{"05/03/2007", "06/09/2007", "15/12/2007", "29/02/2008", "06/10/2008", "19/06/2009", "03/11/2009", "25/12/2009", "24/01/2010", "02/02/2010"}

Update

A faster approach using the same string replacement and Ordering:

datelist[[Ordering@
   StringReplace[datelist, 
    a__ ~~ "/" ~~ b__ ~~ "/" ~~ c__ :> c <> "/" <> b <> "/" <> a]]]
share|improve this answer
    
Almost unreadable, but very fast. Well done! –  VLC Jan 11 '13 at 16:53
    
@Simon Woods, I didn't try your code before... but now that I've tried it I'm impressed... 0.010001 seconds to sort my list... Fastest way for sure! TY –  Rod Jan 11 '13 at 17:24
    
@Simon, it's faster! I though it couldn't be any faster than your previous code, but it is! It took only 0.006001 seconds to sort my list... Impressive! Thank you! –  Rod Jan 11 '13 at 18:19

You can avoid the error messages by specifying how your dates are defined to correctly interpret ambiguous strings such as "01/02/2007":

Flatten[SortBy[
  Partition[
   Table[DateString[{datelist[[v]], {"Day", "Month", "Year"}}, {"Day",
       "/", "Month", "/", "Year"}], {v, 1, 
     Dimensions[datelist][[1]]}], 1], 
  AbsoluteTime[{#[[1]], {"Day", "Month", "Year"}}] &]]

For the speed issue try this and let me know if it is actually faster:

DateString[#, {"Day", "/", "Month", "/", "Year"}] & /@ (DateList /@ 
   Sort[AbsoluteTime[{#, {"Day", "Month", "Year"}}] & /@ datelist])

As Mike suggested this piece of code can be actually reduced to:

DateString[#, {"Day", "/", "Month", "/", "Year"}] & /@ 
   Sort[AbsoluteTime[{#, {"Day", "Month", "Year"}}] & /@ datelist]
share|improve this answer
    
Simply perfect! Thank you! –  Rod Jan 11 '13 at 16:35
    
The second option is even better! It took 0.904114 seconds to sort the list using the first code, and only 0.549069 seconds using the second option... Thank you! –  Rod Jan 11 '13 at 16:43
    
You can map absolute times onto DateStrings so further speed improvements are available by skipping the intermediary DateList mapping step. –  Mike Honeychurch Jan 11 '13 at 21:32
    
@MikeHoneychurch Good catch. –  VLC Jan 12 '13 at 19:41

And yet another way based on converting to integer lists and then finding their ordering:

sdl=datelist[[ToExpression /@ StringSplit[#, "/"][[{3, 2, 1}]] & /@ datelist// Ordering]];

Timing on 100,000 dates:

datelist = 
  DateString[# + AbsoluteTime[{2007, 01, 01}], {"Day", "/", "Month", 
      "/", "Year"}] & /@ RandomInteger[10^8, 10^5];

datelist[[ToExpression /@ StringSplit[#, "/"][[{3, 2, 1}]] & /@ datelist// Ordering]];   
// AbsoluteTiming

{0.658967, Null}

share|improve this answer
    
Hello image_doctor, I think there is a problem with your code... Although it was really fast (it took only 0.031004 seconds to sort my list), my final result was ordered in the MM/DD/YYY format... –  Rod Jan 11 '13 at 17:14
    
@Rod Sorry I forgot you wanted reformatting as well .. hold on .... –  image_doctor Jan 11 '13 at 17:17
    
@RodLm It's about twice as slow, but I hope that works for you. –  image_doctor Jan 11 '13 at 17:38
1  
@RodLm Sorry the versions are changing a bit, I'm getting a flat result with the current version, do you get the same? –  image_doctor Jan 11 '13 at 17:54
1  
@RodLm Glad you like it :) –  image_doctor Jan 11 '13 at 17:55

Here's yet another way to sort and reformat your dates. It could be rewritten as a pure function, but some people find named parameters are easier to read.

f[dates_]:= DateString[#, {"Month", "/", "Day", "/", "Year"}] & /@ 
    Sort[DateList[{#, {"Day", "Month", "Year"}}] & /@ dates]

Usage

myDates = {"01/02/2007", "12/02/2007", "01/02/2008", "01/02/2009", "01/02/2010", 
  "06/01/2007", "01/02/2011"}

f[mydates]

{"01/06/2007", "02/01/2007", "02/12/2007", "02/01/2008", \ "02/01/2009", "02/01/2010", "02/01/2011"}

How it works

First, Sort the list of dates, presently in {Day, Mon, Year} format , into a more convenient format (convenient for Mathematica to sort)

    Sort[DateList[{#, {"Day", "Month", "Year"}}] & /@ myDates

{{2007, 1, 6, 0, 0, 0.}, {2007, 2, 1, 0, 0, 0.}, {2007, 2, 12, 0, 0, 0.}, {2008, 2, 1, 0, 0, 0.},
{2009, 2, 1, 0, 0, 0.}, {2010, 2, 1, 0,0, 0.}, {2011, 2, 1, 0, 0, 0.}}

Then, re-format the results to {Mon, Day, Year}.

DateString[%, {"Month", "/", "Day", "/", "Year"}]
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.