Preparing large data sets for use with DateListPlot is extremely slow

Question

Disclaimer: This is my first foray into Mathematica at the recommendation of a colleague. Any comments or recommendations in reference to idiomatic use, correct practices, etc. would be appreciated. Also, please assume I know nothing about how things work when explaining.

I have multiple large datasets (anywhere from 16 megabytes to 16 gigabytes, potentially even larger) that I need to use to plot a DateListPlot. Each entry of the data looks like this:

//set identifier, timestamp, value
1667,2017-04-19 16:03:84.22,54

Full (contrived) test data can be found here (14 megabytes): https://www.dropbox.com/s/pln55p0pzgcao2e/data.csv?dl=0

data = Import["https://www.dropbox.com/s/pln55p0pzgcao2e/data.csv?dl=1", "CSV"];

Each data file contains several (up to thousands) of separate sets that each need their own line on the graph, distinguished by the first column of the csv file. In order to read the data into Mathematica, I use the following functions:

GetXY[data_] := {data[[All, 2]], data[[All, 3]]}\[Transpose];
LoadData[id_] := GetXY /@ GatherBy[Import[id, "CSV"], First];

All is well up until this point. I can run analysis functions like Mean and StandardDeviation on the flattened list in under a second using the function PowerStats[data_, func_] := func[Flatten[data, 1][[All, 2]]] // N;, but once I try to format the data in a way that DateListPlot can understand, I run into a performance bottleneck. My first attempt involved a different version of the GetXY function that immediately converted the timestamp into a DateList:

GetDateXY[data_] := {DateList /@ data[[All, 1]], data[[All, 2]]}

According to AbsoluteTiming, running each version on 16 megabytes of data took 5.54 seconds for the GetXY function, but 344.02 seconds for the GetDateXY function. Further tests on different sets of data show that these times scale basically linearly. Considering the solution needs to be scaled into at least the realm of 16 gigabytes, this doesn't particularly work for me.

Further attempts included first loading the plain data then parallelizing the transformation of the timestamps, but with only two cores available this only led to an improvement on the 16 megabyte dataset of around 90 seconds. Converting the data into TemporalData had a similar time requirement of ~330 seconds.

I am looking for any substantial improvement over this time requirement. It is important to note that: I only need a plot that looks like a DateTimePlot, it does not necessarily need to be one as long as it plots the data points in the correct order with the correct spacing (one data point every second). I am also open to solutions that summarize the data, such as a moving 10 second average or something along those lines. These graphs will be used for further (human) analysis outside of Mathematica, so the graphs just need to be representative of the data.

Answer 1

The string syntax of DateObject and the "DateStringFormat" option of "CSV" Import is a bit slow for this solution. However, you can parse the date string for its components using StringSplit with is considerably faster that the OP method.

The OP method is similar to

AbsoluteTiming[data1 = MapAt[DateList, {All, All, 1}]@GroupBy[data, First -> Rest];]

{327.317, Null}

But with StringSplit

AbsoluteTiming[
 data2 =
   MapAt[DateObject, {All, All, 1}]@
    MapAt[Internal`StringToDouble, {All, All, 1, -1}]@
     MapAt[FromDigits, {All, All, 1, ;; -2}]@
      MapAt[StringSplit[#, "-" | " " | ":"] &, {All, All, 1}]@
       GroupBy[data, First -> Rest];
 ]

{7.87115, Null}

There is the added benefit of have the result in an Association from GroupByas Query comes in handy for calculating the set metrics.

Query[All, N@*Mean, 2]@data2 // Short

<|1000->149.669,1001->153.896,<<496>>,1498->147.296,1499->149.192|>

or

Query[All, N@Through@{Mean, StandardDeviation}@# &, 2]@data2 // Short

<|1000->{149.669,57.5243},<<498>>,1499->{149.192,58.4228}|>

It is also useful for plotting single sets

Query[Key[1000] /* DateListPlot]@data2

or multiple sets on one chart

Query[(Key /@ {1000, 1001}) /* Values /* DateListPlot]@data2

or to plot and hold all plots

plots = Query[All, DateListPlot]@data2;
plots[1000]

Hope this helps.

Answer 2

To load your data (Notice that dl=1 does the trick for Dropbox to serve the file instead of a webpage).

Import["https://www.dropbox.com/s/pln55p0pzgcao2e/data.csv?dl=1"
, {"CSV", "Data"}
]

It seems to me you are doing things you do don't need to do, the task by GetXY is done at load time using Part ([[]])

{group, data} = {
     #[[All, 1, 1]],
     #[[All, All, {2, 3}]]
     } &@GatherBy[
    Import["https://www.dropbox.com/s/pln55p0pzgcao2e/data.csv?dl=1"
     , {"CSV", "Data"}
     ]
    , First
    ];

DateListPlot will take strings for the time, so there is no need to change the data, so no need for GetDateXY.

Manipulate[
 Module[{timetoload, plot},
  {timetoload, plot} = AbsoluteTiming[
    DateListPlot[
     data[[k]]
     , PlotLabel -> 
      Row[{group[[k]], " loaded in ", timetoload, "seconds"}]
      ]
    ];
  plot
  ]
 , {k, 1, Length[group], 1, Appearance -> "Open"}
 ]

Each plot loads in less that a second.