13
$\begingroup$

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?

Improve this question
$\endgroup$

5 Answers 5

Reset to default
13
$\begingroup$

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"}
Improve this answer
$\endgroup$
2
  • $\begingroup$ Simple and fast! Not the fastest code, but very simple to understand. It's the one I'm going to use! Thank you! $\endgroup$
    – Rod
    Jan 11, 2013 at 17:20
  • 1
    $\begingroup$ 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. $\endgroup$
    – Mike Honeychurch
    Jan 11, 2013 at 21:31
12
$\begingroup$

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]]]
Improve this answer
$\endgroup$
3
  • $\begingroup$ Almost unreadable, but very fast. Well done! $\endgroup$
    – VLC
    Jan 11, 2013 at 16:53
  • $\begingroup$ @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 $\endgroup$
    – Rod
    Jan 11, 2013 at 17:24
  • $\begingroup$ @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! $\endgroup$
    – Rod
    Jan 11, 2013 at 18:19
8
$\begingroup$

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]
Improve this answer
$\endgroup$
4
  • $\begingroup$ Simply perfect! Thank you! $\endgroup$
    – Rod
    Jan 11, 2013 at 16:35
  • $\begingroup$ 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! $\endgroup$
    – Rod
    Jan 11, 2013 at 16:43
  • $\begingroup$ You can map absolute times onto DateStrings so further speed improvements are available by skipping the intermediary DateList mapping step. $\endgroup$
    – Mike Honeychurch
    Jan 11, 2013 at 21:32
  • $\begingroup$ @MikeHoneychurch Good catch. $\endgroup$
    – VLC
    Jan 12, 2013 at 19:41
3
$\begingroup$

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}

Improve this answer
$\endgroup$
7
  • $\begingroup$ 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... $\endgroup$
    – Rod
    Jan 11, 2013 at 17:14
  • $\begingroup$ @Rod Sorry I forgot you wanted reformatting as well .. hold on .... $\endgroup$
    – image_doctor
    Jan 11, 2013 at 17:17
  • $\begingroup$ @RodLm It's about twice as slow, but I hope that works for you. $\endgroup$
    – image_doctor
    Jan 11, 2013 at 17:38
  • 1
    $\begingroup$ @RodLm Sorry the versions are changing a bit, I'm getting a flat result with the current version, do you get the same? $\endgroup$
    – image_doctor
    Jan 11, 2013 at 17:54
  • 1
    $\begingroup$ @RodLm Glad you like it :) $\endgroup$
    – image_doctor
    Jan 11, 2013 at 17:55
3
$\begingroup$

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"}]
Improve this answer
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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