# Reading non-tabular data with NaN-s from file

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}} *)

-

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

-
For comparison, the speed improvement is around ~20%. I used a test file of 10K lines, 300 entries per line, with 20% of entries being nan (takes ~13 sec). Would be nice to have a native solution that spares us the string-to-expression conversion part... –  István Zachar Nov 19 '13 at 17:15
@IstvánZachar. Yeah, it would be faster if we didn't have to do that extra conversion. But a look at the documentation doesn't give me hope that a native solution is coming soon. ReadList was last modified in v2. –  RunnyKine Nov 19 '13 at 18:35

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


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},
pos = StreamPosition@stream;
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}}

-