2
$\begingroup$

This is admittedly boring but one needs such things occasionally, and I could not find it documented.

I have specific time format like "20160723 170543518" (YearMonthDay HourMinuteSecondMillisecond, it is from forex historical data in ASCII), and I want to transform a csv file with such first column into a, say, time series as efficiently as possible.

What I tried is

DateObject[{StringInsert[First[x], " ", {5, 7, 12, 14, 16}], {"Year", "Month", "Day", "Hour", "Minute", "Second", "Millisecond"}}]

but it is quite slow, and I am pretty sure there must be more efficient way to do it.

So?

$\endgroup$
  • 1
    $\begingroup$ Are month, day. hour, minute, and second always in two-digit format? $\endgroup$ – J. M.'s discontentment Dec 17 '16 at 14:27
  • $\begingroup$ @J.M. Yes, and milliseconds take last three positions, it is exactly like this, constant width strings. $\endgroup$ – მამუკა ჯიბლაძე Dec 17 '16 at 14:28
4
$\begingroup$

Here is a method that may work for you. I'll describe it in two sections.

Concept

We start with one line of string data, split it into pieces with StringTake and convert to integers with FromDigits. Then we use Join to assemble a list of 6 numeric values that can be fed into DateObject or AbsoluteTime.

sdata = "20160723 170543518";
split = StringTake[sdata, {{1, 4}, {5, 6}, {7, 8},
   {10, 11}, {12, 13}, {14, 15}, {16, 18}}]
ndata = FromDigits /@ split
tList = Join[ndata[[1 ;; 5]], {ndata[[6]] + ndata[[7]]/1000}]
DateObject[tList]
DateString[%]

Okay, so that works on one line of data. How about many lines?

Implementation

For many lines we create a function that can be applied to each line. Our function takes the concepts above and puts them into a single function. We then generate a list of data points, to simulate a Forex data file, and test the function on the data points with Timing.

Remove[toDateObj]
toDateObj[s_String] := DateObject[
  Join[#[[1 ;; 5]], {#[[6]] + #[[7]]/1000}] &@
   (FromDigits /@ StringTake[s,
      {{1, 4}, {5, 6}, {7, 8},
       {10, 11}, {12, 13}, {14, 15}, {16, 18}}])
  ]
forex = Table[sdata, {30000}];
Timing[toDateObj /@ forex;]

(* {0.98978, Null} *)

So, 30000 lines of data in less than one second, which, of course, will be different on other machines. How to speed it up? Using AbsoluteTime instead of DateObject runs in about 0.5 seconds. And ignoring the seconds & milliseconds runs in about 0.35 seconds. Here's what that might look like:

Remove[toAbsTime]
toAbsTime[s_String] := AbsoluteTime[
  FromDigits /@ StringTake[s,
    {{1, 4}, {5, 6}, {7, 8},
     {10, 11}, {12, 13}}]
  ]

Timing[toAbsTime /@ forex;]

{0.341537, Null}

Comparison

To make a comparison to your original code,

Timing[DateObject[
     {StringInsert[#, " ", {5, 7, 12, 14, 16}],
      {"Year", "Month", "Day",
       "Hour", "Minute", "Second", "Millisecond"}}] &
   /@ forex;]

(* {10.2784, Null} *)

Looks like about 20-30 times faster.

An earlier version of this answer used ToExpression. A great speed improvement was realized by using FromDigits instead, thanks to the comment by @J.M.

| improve this answer | |
$\endgroup$
  • 1
    $\begingroup$ FromDigits[] is safer than ToExpression[] for converting integer strings. $\endgroup$ – J. M.'s discontentment Dec 17 '16 at 22:03
  • $\begingroup$ Great! Unfortunately I need these milliseconds. Still, it is at least 6x faster! Except frankly speaking I don't really understand why :D $\endgroup$ – მამუკა ჯიბლაძე Dec 17 '16 at 22:16
  • $\begingroup$ Great comment by @J.M . I tried it and got a significant speed improvement, in addition to being safer. So much of a speed improvement that I edited my answer. $\endgroup$ – LouisB Dec 17 '16 at 22:34

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.