# NaNs in tabular data and import performance

I'm working with some data files that are just too large for Import.

I tried using ReadList, specifying either Number or Real for all the columns, but this fails, because a few of the values in the data are NaNs, and are thus not considered numerical by Mathematica1.

I can get ReadList to work if I use Record as the types for all the columns, and the performance is not terrible (at least with the relatively small input files I'm using to prototype this import code), but the subsequent call to ToExpression is a performance killer.

I give a detailed example below, including timing data.

Any suggestions on how to get around this problem?

One promising possibility I discovered shortly after posting this question would be to define some input stream method skipLinesWithNaNs that (somehow) filters out the lines that contain NaNs. Then I could just call

ReadList[OpenRead[pathToTSV, listOfNumericTypes, ...],
Method -> "skipLinesWithNaNs"];


...but I have not been able to figure out how to do this. I'm sure it's not difficult, but I'm too sleep-deprived at the moment to see how to do it. I guess my question may boil down to how to implement the skipLinesWithNaNs input stream method. The difficulty, as I see it, is that input stream methods are byte-oriented (AFAICT), whereas the filtering I need done is best described, and easiest to implement, at the line level. It would be great if somewhere in the ocean of Mathematica there was an adapter to convert a byte-oriented stream into a line-oriented one, but I have not yet found such...

EDIT

In response to episanty's request:

makeRow[ncols_, withNaNs_] :=
If[withNaNs,
RandomSample[Prepend[makeRow[ncols - 1, False], "nan"]],
RandomReal[{0, 1}, ncols]];

makeSampleInput[path_, nrows_, ncols_, nnans_] :=
Export[path,
RandomSample[Table[makeRow[ncols, i <= nnans], {i, nrows}]], "TSV"];


Given these helper functions, one can use

makeSampleInput[pathToTSV, 40000, 400, 10];


to create a file at pathToTSV that is comparable to the input file I used for the EXAMPLE below. Also, the following definition of the types parameter (which the EXAMPLE refers to) is consistent with the sample input file.

types = Table["Real", {400}];


EXAMPLE

First some setup:

fmt = StringTemplate["1 s\t2"];
printTime[expr_] := Module[{timing, value},
{timing, value} = AbsoluteTiming[expr];
Print[fmt[timing, ToString[HoldForm[expr]]]];
value
];
SetAttributes[printTime, HoldFirst];
tab = "\t";
nl = "\n";


Now, a data import sequence, as described at the beginning of this post:

(* read in rows of "Records" (in this case, simple strings) from TSV file *)
strings =
RecordSeparators -> {tab, nl}] // printTime;

(* determine the indices of the "NaN-free" rows *)
keep = Flatten[
Position[strings,
Except[{___, "nan", ___}], 1, Heads -> False]] // printTime;

(* extract the "NaN-free" rows from table of strings *)
keptStrings = strings[[keep, ;;]] // printTime;

(* convert remaining string values to numbers *)
rows = ToExpression[keptStrings] // printTime;


The output produced by the calls to printTime are shown below; the first column shows the timing results for the expression in the second column:

2.796349 s  ReadList[pathToTSV, Table[Record, {types}], RecordSeparators -> {tab, nl}]

0.37655 s   Flatten[Position[strings, Except[{___, nan, ___}], 1, Heads -> False]]

0.005952 s  strings[[keep,1 ;; All]]

42.976491 s ToExpression[keptStrings]


As you can see, the call to ToExpression just blows away the performance.

BTW, the size of the file used for this experiment is about one meg:

NumberForm[ByteCount[strings], DigitBlock -> 3]
==> 770,461,048


1Producing pre-processed versions of the input files to remove the NaNs would pose a whole host of complications with the rest of our pipeline, so it is out of the question.

• Can you post samples of the files you're trying to import? What do you want Mathematica to do with the NaN entries? – Emilio Pisanty May 12 '15 at 12:40
• @episanty: as the example I posted shows, I am discarding all the lines that contain NaNs. – kjo May 12 '15 at 12:44
• @episanty: see the new EDIT section in my post. – kjo May 12 '15 at 13:05
• Just to be clear, you want to output the rows with no NaNs as numbers inside nested Lists? – Emilio Pisanty May 12 '15 at 13:38
• You mentioned line-oriented input: would ReadLine be of help in this case? It returns lines of input as strings; you would then be left with checking for NaN, then discarding or processing the vakd strings (maybe with ImportString?) – MarcoB May 12 '15 at 13:44

I would do this line by line, using the ability of ReadList to read a single record via its third argument. You can then check whether you want to keep that record, and Sow it if you do.

Thus, I would use something like this:

importFunction[path_, columns_, maxRows_:∞] := Block[
{inputstream, record, i = 1},
Reap[
While[
i < maxRows,
record = ReadList[inputstream, Table[Word, {columns}], 1];
If[record === {}, Break[]];
i++;
If[
FreeQ[record, "nan"],
Sow[InternalStringToDouble /@ First[record]]
]
];
Close[inputstream]
][[2, 1]]
]


What this does:

• Opens the file as an InputStream object.
• Enters a While loop which will run over each line of the file.
• Reads columns words from the file at a time. This assumes the file is made up of columns numbers per line, separated with spaces. This record is stored in record as a list of strings.
• It then checks whether record is FreeQ of "nan". If it is,
• it converts it to numbers via InternalStringToDouble, as per this answer. This is mostly equivalent to ToExpression when the input is a number. Pro: it handles the notation 7.556e-4 correctly, which ToExpression does not. Con: it doesn't notice if it is not given a number, so InternalStringToDouble["nan"] == 0.
• If the record is empty, it Breaks from the loop.

Given the input

makeSampleInput["~/Desktop/temp.tsv", 40000, 400, 10]


on my machine the timing is

AbsoluteTiming[
Dimensions[data = importFunction["~/Desktop/temp.tsv", 400]]
]

(* {14.3875, {39990, 400}} *)


I'm not sure how acceptable this is for your larger files. I'm afraid I can't compare it with your code as it returns the error Table::iterb: "Iterator {types} does not have appropriate bounds. ".

• Thanks, I found reading your code instructive. I did find a pretty good solution, though. Both fast and simple. I just posted it. I think it's the way to go, at least when running under a "Unixy" OS. (There's got to be an equivalent move for Windows, but I know very little about that OS.) – kjo May 12 '15 at 23:38
• You might find tip 7 here interesting. I was under the impression that it also discouraged use of Part (which you use in your question to sort out the keepers from the nots), but it may not be that inefficient. – Emilio Pisanty May 13 '15 at 0:01
• On the Mathematica side, you could simply try mapping InternalStringToDouble over the appropriate level of the list in the code in the question. – Emilio Pisanty May 13 '15 at 0:03

OK, I found a simple solution, at least if grep or the like is available. Here are the timing results when I apply to the file I used in my original post:

rows =
ReadList["!grep -iv nan " <> pathToTSV, types,
RecordSeparators -> {tab, nl}] // printTime;

3.72033 s   ReadList[!grep -v nan <> pathToTSV, types, RecordSeparators -> {tab, nl}]


One could generalize this idea to any type of pre-processing pipe (e.g. to weed out any line that contains anything other than numbers, not just NaNs), though, of course, all pre-processing incurs a performance cost.

• This can work as well - it depends on where you want to put the data processing. If it's on the OS side you might as well do a pure preprocessing step, but there's probably something to be said for grepping the data as it flows into Mathematica. – Emilio Pisanty May 12 '15 at 23:53