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

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?

-

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]

-

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];

-
An obvious inefficiency with this code is that it reads the entire file 280 times. A better approach might be to read the file in chunks and create a list of column 8 values which you can then use Position on. –  Simon Woods Apr 7 '13 at 17:34
It takes about 7 minutes per cycle. At least it is constant and not exponential. It appears that the For function was included in MMA to appease the dinosaurs and that the Do function is more appropriate and efficient to use than For in virtually any application. I see the idea of your approach, but since numbers from 20 to 300 may exist in any line of the file and the lines that match the number in the list need picked out and saved to a file, I am not sure how this would save time. Perhaps you would elaborate? It is basically a sort operation. Like sorting columns in a spreadsheet. –  Sinistar Apr 17 '13 at 19:47
It's not clear why my answer above doesn't help you. Also, your question refers to finding the list of row numbers at which the target integer appears, but your code here which you say "does the trick" just counts how many times the target appears. Which is correct? –  Simon Woods Apr 18 '13 at 9:13
I think I had modified my attack from the first time I attempted the problem. I am trying to get subset data from a set space. I was considering plotting position data for visualization using positions the interesting subset appears. The count would be easily derived using the Length symbol at that point. So the above code gets the counts of the column integers and writes that line to a file from the master set, in my completed code, and each line number is serialized so I can determine postions either way. –  Sinistar Apr 18 '13 at 18:58
I have 283 unique integers over 22,000,000+ lines of text data. Each integer appears spread out over some of these lines, but it isn't in any discernible order, so I just don't see how I can avoid reading each line to test for each number since I do not have a way to predict what line it will be in. That's one of the goals. There is a pattern here, and I am trying to break down the set space to bring that into focus. –  Sinistar Apr 18 '13 at 19:01