# Import large amount of data with time stamps

I have a large data file (150 000 lines) which I want to import in Mathematica. The first few lines look like this

28/04/2013 20:01:36.18  2.5013E-2   W
28/04/2013 20:01:36.26  2.5013E-2   W
28/04/2013 20:01:36.32  2.5013E-2   W
28/04/2013 20:01:36.35  2.5011E-2   W
28/04/2013 20:01:36.48  2.5011E-2   W
28/04/2013 20:01:36.53  2.5011E-2   W
28/04/2013 20:01:36.60  2.5008E-2   W
28/04/2013 20:01:36.64  2.5008E-2   W
28/04/2013 20:01:36.70  2.5008E-2   W


The first column is the date, second is the time, third is the measured value and the last is the unit of measurements. I want to make ListPlot of the data, where the x axis should be time since the start in proper formatting (e. g. HH:MM) and the y axis the measured value. Of course, the importing has to be done very efficiently, due to the large number of lines. So, I guess I should use ReadList, but I don't know how to Mathematica what I want.

Update in response of the comments below

I used this code

s = OpenRead["filename.dat"];
data = ReadList[s, {Record, Number, Record}, RecordSeparators -> {"\t","\n"}]


which gives me this

{{"28/04/2013 20:01:36.18", 0.025013, "W"},
{"28/04/2013 20:01:36.26", 0.025013, "W"},
{"28/04/2013 20:01:36.32", 0.025013, "W"}....}


Now, the problem is how to plot this the way I want.

-
First, open a stream, eg. s = OpenRead["data.txt"], and read in every line as a string, ReadList[s, Record], yes? – BoLe Apr 29 '13 at 13:14
You can read the first two columns as string and convert to date lists with DateList. With that result you can then use e.g. DateListPlot. As converting with DateList might be relatively slow and your date format is fixed, I would suggest to use a StringReplace + ToExpression approach for improved performance. I think answers to this question will also be relevant, although your file seems to rather be large but not yet huge. – Albert Retey Apr 29 '13 at 13:27
Unless you have a screen 150,000 pixels wide you or Mathematica have to drop quite a few measurements to be able to show them on the screen. You may want to consider how you want to do that. Downsample is a possibility. – Sjoerd C. de Vries Apr 29 '13 at 13:41
@SjoerdC.deVries I will use Joined->True, so that is not a problem, but thanks for the tip – phidelio Apr 29 '13 at 13:51
Joined isn't really a solution. My point was that if you have 150,000 points or line segments, the screen won't be able to show them all, so this kind of input is a bit of overkill. – Sjoerd C. de Vries Apr 29 '13 at 13:56

Since you requested performance I would avoid Import and DateList and use ReadList and AbsoluteTime.

format = {Number, Character, Number, Character, Number, Number,
Character, Number, Character, Number, Number, Word};

data = {AbsoluteTime[{#5, #3, #1, #6, #8, #10}], ##11} & @@@ ReadList["data.txt", format];


"data.txt" is of course your data file.
The Slots are used to reorder expression into the form needed by AbsoluteTime.

You can then convert the timestamps into delta from start like this (in-place modification):

data[[All, 1]] -= data[[1, 1]];


This entire process takes under a second with a 150,000 line file on my machine.

-
This indeed seems to be faster. – phidelio Apr 29 '13 at 19:51
But wait a minite, the second command doesn't give me the difference between any entry and the first, but between two consecutive entries. And also my imported data looks like this {{3.57626821343000000000000*10^9, 9.1454*10^-6, "W"}, {3.57626821346000000000000*10^9, 9.1454*10^-6, "W"},... – phidelio Apr 30 '13 at 12:23
@phidelio Pardon me, I apparently misread the question; for some reason I thought you wanted sequential differences. Corrected. As for the data, the values are absolute time in seconds from the beginning of the Mathematica epoch. After processing they are time in seconds from the start. Is this a problem for you, and if so, how? – Mr.Wizard Apr 30 '13 at 12:35
sorry, I solved this quite easily after subtracting the initial value – phidelio Apr 30 '13 at 19:12

Import is very well able to handle this format. As a demonstration I use its nephew ImportString to deal with the few lines from your example:

data =
ImportString[
"28/04/2013 20:01:36.18  2.5013E-2   W
28/04/2013 20:01:36.26  2.5013E-2   W
28/04/2013 20:01:36.32  2.5013E-2   W
28/04/2013 20:01:36.35  2.5011E-2   W
28/04/2013 20:01:36.48  2.5011E-2   W
28/04/2013 20:01:36.53  2.5011E-2   W
28/04/2013 20:01:36.60  2.5008E-2   W
28/04/2013 20:01:36.64  2.5008E-2   W
28/04/2013 20:01:36.70  2.5008E-2   W", "Data"]


{{"28/04/2013", "20:01:36.18", 0.025013, "W"}, {"28/04/2013", "20:01:36.26", 0.025013, "W"}, {"28/04/2013", "20:01:36.32", 0.025013, "W"}, {"28/04/2013", "20:01:36.35", 0.025011, "W"}, {"28/04/2013", "20:01:36.48", 0.025011, "W"}, {"28/04/2013", "20:01:36.53", 0.025011, "W"}, {"28/04/2013", "20:01:36.60", 0.025008, "W"}, {"28/04/2013", "20:01:36.64", 0.025008, "W"}, {"28/04/2013", "20:01:36.70", 0.025008, "W"}}

Convert dates and times to DateList

times = (DateList[#1 <> " " <> #2]) & @@@ data[[All, {1, 2}]]


{{2013, 4, 28, 20, 1, 36.18}, {2013, 4, 28, 20, 1, 36.26}, {2013, 4, 28, 20, 1, 36.32}, {2013, 4, 28, 20, 1, 36.35}, {2013, 4, 28, 20, 1, 36.48}, {2013, 4, 28, 20, 1, 36.53}, {2013, 4, 28, 20, 1, 36.6}, {2013, 4, 28, 20, 1, 36.64}, {2013, 4, 28, 20, 1, 36.7}}

Timing with respect to the start:

timeDifs = (DateDifference[times[[1]], #, "Second"] & /@ times)[[All, 1]]


{0., 0.08, 0.14, 0.17, 0.3, 0.35, 0.42, 0.46, 0.52}

values = data[[All, 3]]


{0.025013, 0.025013, 0.025013, 0.025011, 0.025011, 0.025011, \ 0.025008, 0.025008, 0.025008}

 ListPlot[Transpose[{timeDifs, values}], Joined -> True]


-
Thanks, this is mostly what I wanted. The import command needed to much time, so I replaced it with data = ReadList[s, {Record, Record, Number,Record}, RecordSeparators -> {"\t", " ", "\n"}]; – phidelio Apr 29 '13 at 16:17