34
$\begingroup$

I have a tab separated value file with 10 million rows each of which has three tab separated values. The first value is a string, the second an integer, and the third another string. How to read efficiently (in terms of timing and memory footprint) the $n^{th}$ to $(n+100)^{th}$ rows of the file into Mathematica as

{
    {_String, _Integer, _String},
    ...
}

?

Improve this question
$\endgroup$
1
  • $\begingroup$ For a more generic type of answer on this problem read my solution here $\endgroup$
    – Athanassios
    Feb 19, 2016 at 10:37

3 Answers 3

Reset to default
34
$\begingroup$

For a one-off read you can Skip a number of records:

str = OpenRead["test.tsv"];
Skip[str, Record, n - 1];
data = ReadList[str, {Record, Number, Record}, 100, RecordSeparators -> {"\t", "\n"}];
Close[str];

If you will be reading from the same file many times, it may be worth building an index you can use with SetStreamPosition

str = OpenRead["test.tsv"];
index = Table[pos = StreamPosition[str]; Skip[str, Record]; pos, {100000}];

readlines[n_, m_] := Block[{},
SetStreamPosition[str, index[[n]]];
ReadList[str, {Record, Number, Record}, m, RecordSeparators -> {"\t", "\n"}]]

data = readlines[50000,100]

On my PC building the index took about half a second for 10^5 rows in the file, assuming it scales linearly this would be about a minute for 10^7 rows. So this is only worth doing if you are going to be doing a lot of reads.

Improve this answer
$\endgroup$
9
  • 5
    $\begingroup$ Since this is going to be used on a very large file, it is very likely that at some point Abort will be called while the file is being Read. So, it needs to be wrapped in a CheckAbort so that you can ensure that you close the stream if an Abort occurs. Here's a cautionary tale of what happens if too many Streams are left open. Also, an error is thrown if you attempt to open a file with an existing Stream. $\endgroup$
    – rcollyer
    May 5, 2012 at 16:14
  • 5
    $\begingroup$ +1 - Nice use of streams. You can build index about 5-6 times faster (according to my benchmarks) with this function: buildIndex[file_] := With[{bytes = BinaryReadList[file]}, Prepend[#, 0] &[ Most@Flatten[(SparseArray[#, Length[#], 1] & @Unitize[bytes - 10])["NonzeroPositions"]] - 1]], when there is enough memory to hold all file's contents in RAM at once. A speed - memory tradeoff. $\endgroup$
    – Leonid Shifrin
    May 5, 2012 at 18:48
  • $\begingroup$ What if the rows are of form _String _Integer _String _Integer? $\endgroup$
    – qazwsx
    May 6, 2012 at 1:57
  • 3
    $\begingroup$ @Leonid: I'm sure you are aware of it but probably not every other reader: you could build the index with BinaryReadList as you suggested even if the file content doesn't fit in RAM by handling the content in chunks. That would let you take control over speed vs. memory in even more detail... $\endgroup$
    – Albert Retey
    May 6, 2012 at 9:35
  • $\begingroup$ @AlbertRetey Thanks, a very good point! $\endgroup$
    – Leonid Shifrin
    May 6, 2012 at 9:51
10
$\begingroup$

Here is a solution for Linux/( ? Mac ? ) uses, or Windows users with a Linux toolkit.

To read line 23681 to 23781, use the following variants of head and tail.

data=Import["!head -23781 /tmp/data.txt | tail -n 100", "Text"];
Improve this answer
$\endgroup$
4
  • $\begingroup$ Any idea how to make this also work on Windows? $\endgroup$
    – qazwsx
    Nov 16, 2012 at 0:41
  • $\begingroup$ @Problemania Reports say that this sourceforge.net/projects/unxutils or msysgit will give you unix command line utils in the windows environment. $\endgroup$
    – image_doctor
    Nov 16, 2012 at 1:22
  • $\begingroup$ I confirm that this works on Mac too. $\endgroup$
    – Taiki
    May 16, 2013 at 20:51
  • $\begingroup$ @image_doctor can you also use grep? I tried replacing !head with grep , it doesn't work, any idea? $\endgroup$
    – Gvxfjørt
    Sep 11, 2017 at 2:16
4
$\begingroup$

You can use set stream position which sets the current point in an open stream. Then you can use Read which reads one expression from an input stream, and returns the expression with byte, character, expression, Number, Real, String or Word options. Something like:

Edit 1 with ReadList

n = 4;
str = OpenRead["data.txt"];
Skip[str, Record, n - 1]
ReadList[str, {Word, Number, Word}, 100]
Close[str];

Edit 2 with Read and Loop no abort check

data = {};
n = 4;
str = OpenRead["data.txt"];
Skip[str, Record, n - 1]
For[i = 0, i < 100, i++,
 {a, b, c} = Read[str, {Word, Number, Word}];
 data = Append[data, {a, b, c}];
 ]
Close[str];
data

Edit 2 with Read and Loop with abort check

data = {};
n = 4;
str = OpenRead["data.txt"];
Skip[str, Record, n - 1]
For[i = 0, i < 100, i++, CheckAbort[Module[{a, b, c},
   {a, b, c} = Read[str, {Word, Number, Word}];
   data = Append[data, {a, b, c}];
   ], Close[str]]]
Close[str];
data
Improve this answer
$\endgroup$
5
  • $\begingroup$ That gives a Read::readn: Invalid real number found when reading from ... error. $\endgroup$
    – qazwsx
    May 5, 2012 at 15:46
  • 1
    $\begingroup$ SetStreamPosition[str, n] doesn't seem to be correct. It shouldn't be n. n is the row index. But SetStreamPosition doesn't seem to operate on rows. $\endgroup$
    – qazwsx
    May 5, 2012 at 15:52
  • $\begingroup$ For won't work as written, see this question and my answer. Also, String captures the entire line, Word should be used instead. Lastly, as this is a large file, the For loop should be wrapped in a CheckAbort to ensure that the Stream is closed properly. I'd use the OpenAndRead function I define here. $\endgroup$
    – rcollyer
    May 5, 2012 at 16:09
  • $\begingroup$ @ rcollyer Thanks for the links. But, some how they don't point to the right page. I found via search and it was useful. $\endgroup$
    – s.s.o
    May 6, 2012 at 0:31
  • $\begingroup$ @ MaThEmAtika, I wrote them lack of time in order to give you some possible paths and functions. $\endgroup$
    – s.s.o
    May 6, 2012 at 0:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.