Importing large datasets using streams

Question

I work with large data sets with 22 columns and up to a million rows. Each row contains one observation with several variables. All variables are numbers.

Simplyfied example:
1, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
2, 10, 9, 8, 7, 6, 5, 4, 3, 2, 1
3, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
4, 10, 9, 8, 7, 6, 5, 4, 3, 2, 1
(...)

First of all I need to import one row at a time, in the format {1,1,2,3,4,5,6,7,8,9,10}.

I have trouble getting Mathematica to ignore the blank spaces between the numbers unless I use the Import function. That is not a reasonable solution with very large data sets.

Can I use Mathematica's stream environment to solve this? If so, how?

Second, I eventually need to be able to import specific rows. For example all the rows where the second value is 2: {{1,1,2,3,4,5,6,7,8,9,10},{3,1,2,3,4,5,6,7,8,9,10}}.

Can that be done without having to import the entire dataset first?

Answer 1

It might well be possible to use the stream methods functionality for that, but I think it is not exactly what they were made for. You probably can achieve what you want as easy as this (there might be more efficient ways to turn the string that Read returns into a list of integers/numbers):

stream = StringToStream["1, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
  2, 10, 9, 8, 7, 6, 5, 4, 3, 2, 1
  3, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
  4, 10, 9, 8, 7, 6, 5, 4, 3, 2, 1"];
While[(line = Read[stream, String]) =!= EndOfFile,
  Print@ToExpression[StringSplit[line, ","]]
];
Close[stream];

If that is still too slow I'd try to read the file in chunks of e.g. 100 or 1000 lines with ReadList and work with those in memory, then read the next chunk etc. I've worked with huge files that way and with that approach neither memory consumption nor reading speed ever turned out to be the limiting factor, but your milage may vary.

Only if that is still too slow I'd go for a more involved solution, which I think could be along the lines of what is shown in the answers to this question.

For your second question: I don't see a way to make such a selection that without reading at least the beginning of each line of the whole file when the data is in a textfile. Of course you can do that line by line or in chunks instead of importing all the data into memory. If you not only need to do this once but several times, then it might be worth to store the data in a format which is better suited for such selections, e.g. a database format or something like shown here.

If you are willing to learn something new and use still undocumented features I could imagine that you could take advantage of the Streaming` package for efficient handling of your data, maybe you are in luck and the author will find your question interesting and shows us how that can be done...