NaNs in tabular data and import performance

Question

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 Mathematica¹.

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?

---

ADDENDUM

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\t`2`"];
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 = 
  ReadList[pathToTSV, Table[Record, {types}], 
    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

---

_{¹Producing 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.}

Answer 1

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},
  inputstream = OpenRead[path];
  Reap[
    While[
     i < maxRows,
     record = ReadList[inputstream, Table[Word, {columns}], 1];
     If[record === {}, Break[]];
     i++;
     If[
      FreeQ[record, "nan"],
      Sow[Internal`StringToDouble /@ 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 Internal`StringToDouble, 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 Internal`StringToDouble["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. ".

Answer 2

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.