1
$\begingroup$

I have a large Dataset with a few columns that contain ambiguous date strings in a variety of formats. I am wanting to reformat these so they are all the same. When it comes to values that are ambiguous, I am willing to take Wolfram's best guess.

Consider the following:

data = Dataset[{<|"Item" -> "a", "Date" -> "June 12, 2017"|>, <|
    "Item" -> "b", "Date" -> "5/12/2014"|>, <|"Item" -> "c", 
    "Date" -> "01 September 1980"|>, <|"Item" -> "d", 
    "Date" -> "2006-03-15"|>, <|"Item" -> "e", "Date" -> "8/9/2008"|>, 
     <|"Item" -> "f", "Date" -> "5-8-99"|>}]

Dataset

I can use the following code to pull the values and get a best guess in cases of an ambiguous date while using Quiet to suppress the errors.

Quiet[DateString[
    DateObject[#], {"Day", "-", "MonthNameShort", "-", "Year"}] & /@ 
  Normal[data[[All, "Date"]]]]

{"12-Jun-2017", "12-May-2014", "01-Sep-1980", "15-Mar-2006", "09-Aug-2008", "08-May-1999"}

However, when I attempt to run that function on the dataset column, the ambiguous data causes a failure and the results aren't computed.

cleandata = 
 data[All, {"Date" -> (Quiet[
      DateString[
        DateObject[#], {"Day", "-", "MonthNameShort", "-", 
         "Year"}] &])}]

Failure Message

Is there a way to force Mathematica to work through this Failure, or am I going to have to extract the data, run the function, then replace the column data?

This particular dataset has about 250K entries with about 10 columns that need this treatment.

For now, I am using the following with the replacement code being run for every column before reassembling but it seems terribly inefficient, especially when having to do this for multiple columns.

data = Normal@data; 
data[[All, "Date"]] = 
  Quiet[DateString[
      DateObject[#], {"Day", "-", "MonthNameShort", "-", "Year"}] & /@
     data[[All, "Date"]]];
data = Dataset@data
$\endgroup$
  • 1
    $\begingroup$ Are you looking for Intepreter["Date"] ? Can't be (more) helpful right now, no Mathematica on this machine. $\endgroup$ – High Performance Mark Jul 1 at 14:53
  • $\begingroup$ @HighPerformanceMark, My first approach was actually to use the Interpreter, but with a couple million entries, it was way too slow to be able to use. $\endgroup$ – kickert Jul 1 at 14:55
4
$\begingroup$

The issue is caused by what is likely a typo: Quiet[…&] does essentially nothing, since it only suppresses message during the evaluation of …&, which is inert. You probably meant to write Quiet[…]&, which does what you want:

data[All, {
  "Date" -> (Quiet[
    DateString[DateObject[#], {"Day", "-", "MonthNameShort", "-", "Year"}]
   ] &)
 }
]

enter image description here

As @HighPerformanceMark pointed out in the comments, you might want to use Interpreter["Date"]:

cleandata = data[All, {"Date" -> Interpreter["Date"]}]

enter image description here

As you've noticed however, Interpreter is quite slow:

AbsoluteTiming@data[All, {
  "Date" -> (Quiet[
    DateString[DateObject[#], {"Day", "-", "MonthNameShort", "-", "Year"}]
   ] &)
 }
]
(* { 0.015855, … } *)

AbsoluteTiming@data[All, {"Date" -> Interpreter["Date"]}]
(* { 3.44572, … } *)

This can be improved significantly by applying the Interpreter function to all entries at once. We can do this by transposing the data, applying the interpreter to the whole column, and transposing the data back:

AbsoluteTiming@data[Transpose][{"Date" -> Interpreter["Date"]}][Transpose]
(* { 1.14179, … } *)

(side-note: I initially wanted to suggest SubsetMap for version 12.0, but it appears it can't handle associations at the moment…)

You'll notice that it's still worse, but a big part of this is just a constant overhead that becomes insignificant for bigger data sets:

timings = Transpose@Table[
   With[
    {d = Join @@ Table[data, 2^n]},
    {
     First@
      AbsoluteTiming@
       d[Transpose][{"Date" -> Interpreter["Date"]}][Transpose],
     First@
      AbsoluteTiming@
       d[All, {"Date" -> (Quiet[
             DateString[
              DateObject[#], {"Day", "-", "MonthNameShort", "-", 
               "Year"}]] &)}]
     }
    ],
   {n, 0, 10}
   ]

enter image description here

It's still not as good as the other solution, but I thought I'd point this out anyway, as Interpreter functions can be very handy sometimes.

$\endgroup$
  • $\begingroup$ Thanks @LukasLang. I often wrap multiple functions and pure functions in a single Quiet function and have never had an issue, so the syntax difference here threw me off. Appreciate the detailed response. $\endgroup$ – kickert Jul 1 at 15:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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