Iterating Reads From a Text File and Writing the Results of Operation

Question

I have a very large text (TSV) file. I am able to now read single line, thanks to StackExchange. I am able to read a part of that line using the part operator. I want to iterate through the whole file, each line, test a part of that line if it is equal to one of 100 possible integers, and write the line number to a text file.

So in essence I have a very large array, say 9 x 20000000. For each of the 20000000 rows, I want to test column 8 for one of 100 possible integer values, and write all the positions a distinct integer appears into another data file.

For example, 22 may appear in column {8} 22222 times, but at various non-adjacent rows in the array. I want an ascending list of all rows that each possible number appears, by moving on to 23 and make an ascending list of all the places it appears up until I iterate all the possible integers in column {8}, say to 122.

I tried a nested For loop to read through all the possible rows, and then try to test column 8 of each row by looping through all the possible values. Wash, rinse, repeat. It doesn't quite work and I'm not sure why, but MMA seems to discourage this otherwise common technique in other programming environments. I have looked a while now and do not find a way to this in MMA using any of its purpose built functions.

Testing the file would be easy enough to do if I could import the file, but MMA chokes on it. For some reason importing a 1GB TSV file requires 20GB of Virtual Memory + all the remaining free memory of my 16GB DRAM machine and 30 minutes. If it is able to complete this without crashing, in apparently a new 'feature' for version 9, this may suddenly be inexplicably dumped and all the variables reset without telling MMA to do so. I've long been perplexed by MMA not being able to read and store a large text file when other programs like UltraEdit do this with ease. It is what it is I guess.

On another note, if I do not know how many lines (delineated by a new line or line feed) are in a file, how do I find that value without manually opening it and checking?

Answer 1

You could read the file in chunks and create a list of column 8 values. In this example the file is read in chunks of 100000 lines, and there are a total of 200 chunks (ie. 20000000 lines).

str = OpenRead["test.tsv"];
col8 = Flatten @ Table[ReadList[str, Number, 100000*9][[8 ;; ;; 9]], {200}];
Close[str];

Then the row numbers containing e.g. 22 in column 8 are just:

Flatten @ Position[col8, 22]

Answer 2

I managed to work the kinks out of the nested loop. This does the trick, but the more lines you read as one might expect the time to complete the nested loops goes up exponentially. Anyone have a more efficient way to accomplish this?

Note: readlines is a function I found here in StackExchange MMA section. I found that after being confounded trying to read a text file one line at a time using MMA functions. This code builds an index and allows selection of 1 to n lines of a text file. I am puzzled why such an operation should require 12 lines of code instead of a single function like ReadFile[1,20]?

fname = "D:/Sum.txt";
fsum = OpenWrite[fname]; k = 0;
For[i = 20, i <= 300, i++,
 For[j = 1, j <= 22000000, j++, line = Flatten[readlines[j, 1]]; 
  If[line[[8]] == i, k = k + 1, k = k]];
 Write[fsum, {i, k}]; k = 0;]
Close[fsum];