Reading non-tabular data with NaN-s from file

Question

A NaN stands for an entry that is not a number/undefined/missing/infinite/etc. On my machine the C code printf("%f", 0/0.) produces -1.#IND00 which is the standard Windows representation of an indeterminate float. I have to read in a data file containing:

1. either a fixed number of floating-point reals per line with possible NaN-s OR
2. a variable number of floating-point reals per line, as NaN-s are omitted during file-generation.

Unfortunately, Mathematica is unable to read and convert such NaN-s silently to Indeterminate or Missing[] with e.g. ReadList[..., Table[Number, {10}]]. Also, Mathematica is unable to read a variable number of Number-s per line (until a \n is encountered). Seems like one cannot avoid the rather slow string-based import:

StringSplit /@ ReadList[file, String] /. "-1.#IND00" -> Indeterminate // ToExpression

Is there a FASTER way to read in such irregular data?

Aassume test.dat contains the followings (the real case has 100K lines and ~1000 entries per line.):

1.0        0.0        3.0
4.0        -1.#IND00  -1.#IND00
5.0        6.0        -1.#IND00

(* ==> {{1., 0., 3.}, {4., Indeterminate, Indeterminate}, {5., 6., Indeterminate}} *)

Answer 1

ReadList["test.dat", Table[Record, {3}], RecordSeparators -> {" ", "\n"}] /. 
  "-1.#IND00" -> Indeterminate // ToExpression

One can also use the RecordLists -> True option for irregular data.

You can also use -1.#IND00 as RecordSeperators and they'll be skipped during the reading of the data. e.g.

ReadList["test.dat", Record, RecordSeparators -> {" ", "\n", "-1.#IND00"}]

It seems the best way is to use Word and related options. The following takes care of irregular data.

ReadList["test.dat", Word, WordSeparators -> {" ", "-1.#IND00"}, RecordLists -> True] //  
ToExpression

{{1., 0., 3.}, {4.}, {5., 6.}}

I have omitted the Indeterminate data. to include it just do:

ReadList["test.dat", Word, WordSeparators -> {" "}, RecordLists -> True] /. 
  "-1.#IND00" -> Indeterminate // ToExpression

{{1., 0., 3.}, {4., Indeterminate, Indeterminate}, {5., 6., Indeterminate}}

Answer 2

I did a few tests and arrived to the conclusion, that fastest and safest is to produce regular data that can be quickly read in. So this answer is more like a memo to remember which is the fastest solution. Accordingly, data should consist of:

• identically typed entries (e.g. only integers or reals)
• NaN-s are represented as out-of-scope values of same type (e.g. negatives, if valid data is always nonnegative)
• same amount of entries per row (data is rectangular matrix)
• and a header (prepended to data) containing information about out-of-scope values and their replacements, that can be omitted.

First, generate some large rectangular data sets (10 000 x 100, this might take a few seconds). The first one with 10% NAN entries:

dataNan = Table[If[RandomReal[] < .1, "-1.#IND00", RandomReal[]], {10000}, {100}];
Export["testNan.dat", dataNan, "Table"];

and one with the same dimensionality, only numerical data without NAN-s, but with a header line that holds information about what numerical values should be omitted.

dataHeader = Table[If[RandomReal[] < .1, -99., RandomReal[]], {10000}, {100}];
Export["testHeader.dat", Prepend[dataHeader, {"{-99.0 -> Missing[]}"}], "Table"];

Since my original data only contains positive floats, I designate -99. to be a dummy placeholder.

Code

To read headered data effectively, I provide readWithHeader. It takes the first line of the file as the header, extracts the conversion rule, then reads the first line of data to get the actual row length (number of entries per row), and then reads in a fixed n number of entries of the same type (e.g. Real) per line.

readWithHeader[file_String, type_: Real] := 
  Module[{stream = OpenRead@file, n, conv, pos, data},
   conv = ToExpression@Read[stream, String];
   pos = StreamPosition@stream;
   n = Length@StringSplit@Read[stream, String];
   SetStreamPosition[stream, pos];
   data = ReadList[stream, Table[type, {n}]] /. conv;
   Close@stream;
   data];

Of course the process can be further sped up if the column number is also saved in the header.

Timings

My original attempt. Slow.

Dimensions[
  StringSplit /@ ReadList[fileNan, String] /. "-1.#IND00" -> Indeterminate // 
   ToExpression] // AbsoluteTiming

{8.350012, {10000, 100}}

RunnyKine's solution. Faster, but not fast enough.

Dimensions[
  ReadList[fileNan, Word, WordSeparators -> {"\t"}, RecordLists -> True] /. 
    "-1.#IND00" -> "Missing[]" // ToExpression] // AbsoluteTiming

{6.630009, {10000, 100}}

Processing headered data. ~7x faster than original:

Dimensions@readWithHeader@fileHeader // AbsoluteTiming

{1.160002, {10000, 100}}