Applying Function to Ambiguous Dates in a Dataset

Question

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"|>}]

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"}] &])}]

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

Answer 1

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"}]
   ] &)
 }
]

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

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

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}
   ]

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.