# Extracting columns of text from a rectangular string

I have very large rectangular strings (on the order of 30-100 MB in size), which I import into Mathematica from text files using a command like text = Import["test.txt", "String"];. A small representative example string str is below. Lines of text in the rectangular strings are delimited by the "\n" character. I would like to write an efficient function that extracts columns of text from the string. It must be efficient because my actual strings are so large.

It seems that splitting the string into individual characters, using something like Map[Characters, StringSplit[str, "\n"]], is not an option, because it seems to take too much memory. So, in takeFun below, I have instead located the positions of characters in each column. Can you think of a more efficient way to write takeFun? By efficient, I am referring to memory conservation, not speed. Thank you!

str =
"ABCDE
abcde
12345";

pos = Prepend[StringPosition[str, "\n"][[All, 1]], 0];

(* Define a new string take function *)
takeFun[s_, p_, index_] := StringTake[s, Partition[p + index, 1]]

(* Take first column *)
takeFun[str, pos, 1]

(* Take second column *)
takeFun[str, pos, 2]

(* Take fifth column *)
takeFun[str, pos, 5]


which gives this output:

{"A", "a", "1"}

{"B", "b", "2"}

{"E", "e", "5"}

-
Have you considered using "Table" instead of "String", or even ReadList? –  rcollyer Aug 16 '12 at 4:09
Andrew, I updated my answer to describe a methodology that I believe will be of value to you. –  Mr.Wizard Aug 18 '12 at 3:56
Thanks for the bounty! :D –  Mr.Wizard Aug 20 '12 at 21:30

## General method

Since the emphasis of this question is on performance I propose something like this:

extractColumn[file_, col_] :=
Module[{bin, len},
len = 1 + LengthWhile[bin, # =!= 10 &];
bin[[col ;;;; len]] // FromCharacterCode
]


Or, if you prefer, condensed:

extractColumn[file_, col_] :=
FromCharacterCode @
#[[col ;;;; 1 + LengthWhile[#, # =!= 10 &]]] & @


This directly reads in the text file as 8-bit integers; it would be possible to use 16-bit integers if the file uses. 16-bit encoding. It then finds a line return (10) to determine line length, and extracts the column with Part and Span; finally the output string is reconstructed with FromCharacterCode.

To test it I make a 44MB rectangular text file "textblock.txt":

Export["textblock.txt",
Riffle[
StringJoin /@ RandomChoice["a"~CharacterRange~"z", {500000, 90}],
"\n"
] // StringJoin
]


Then call:

extractColumn["textblock.txt", 17] // StringLength // Timing

{0.406, 500000}


## Methodology for binary data

In a comment you state:

My text files are files containing only " ", "o", and return characters ("\r" and/or "\n"). The " " and "o" characters comprise an "existence matrix," where " " represents False and "o" represents True. The row number and the column number each have meaning (the row number represents "type" while the column number represents "time"). I would like to sequentially read in each column and perform analysis on each column.

So it would be nice if I could read in multiple columns at once, since I think BinaryReadList@file may be relatively expensive (my text files have 200,001 columns each), so calling extractColumn 200,001 times (each call taking on the order of 1 second) can be quite time consuming.

Okay, I'll create a new 98MB sample file as follows:

Export["TFblock.txt",
Riffle[StringJoin /@ RandomChoice[{" ", "o"}, {500, 200000}], "\n"] // StringJoin
]


On a modern machine you should be able to read this entire file at once, as strings (using about the same memory):

strings = ReadList["TFblock.txt", Record];

ByteCount[strings]

MaxMemoryUsed[]


100024032

115317104

If for some reason this is not possible, e.g. you start working with huge files, you could use Read[stream, Record] to do this (the following) one row at a time.

The first rule of working efficiently with binary data is: store it in a binary format.

Since Mathematica is equipped to handle very large integers we can store each row of this data as a single Integer. As strings, each data point takes one byte, but packed in an integer each takes only one bit:

strings[[1]] // ByteCount


200040

stringToInt = # ~StringReplace~ {" " -> "0", "o" -> "1"} ~FromDigits~ 2 &;

stringToInt @ strings[[1]] // ByteCount


25040

This operation is a bit slow but with it the entire data is stored in a mere 12.5MB:

Timing[ints = stringToInt /@ StringReverse @ strings;]

ByteCount[ints]


{8.627, Null}

12524032

We can use BitGet to pull data from the Integers, indexed from zero at the least significant bit. (This is the reason for StringReverse above.) This returns the first column: BitGet[ints, 0]. This the last: BitGet[ints, 199999].

The operation is reasonably fast:

Do[BitGet[ints, i], {i, 0, 4999}]; // Timing


{0.421, Null}

Confirmation of result:

StringJoin[BitGet[ints, 16] /. {0 -> " ", 1 -> "o"}] ===
extractColumn["TFblock.txt", 17]


True

Of course if the data is always accessed by column it would be better to have the data in that format to begin with. You could make a one-time pass through the columns using BitGet and FromDigits to transpose the data:

Timing[
ints2 = Table[ints ~BitGet~ i ~FromDigits~ 2, {i, 0, 199999}];
]


{19.391, Null}

A column is then read with: IntegerDigits[ints2[[ (*column*) ]], 2, (* row length *)]. Row length must be specified so as not to lose leading zeros. Confirmation:

IntegerDigits[ints2[[17]], 2, 500] === BitGet[ints, 16]


True

Data extraction is now very fast:

Do[IntegerDigits[ints2[[i]], 2, 500], {i, 1, 5000}] // Timing


{0.016, Null}

-
Thanks for your time. I am having some difficulty using extractColumn, though. Suppose I have a file called test.txt containing the text ABCDE\nabcde\n12345. If I run extractColumn["test.txt", 1], I obtain the output a1. If I run extractColumn["test.txt", 3], I obtain the output a1 also. It seems that while extractColumn has the header extractColumn[file_, col_], col_ is not referred to in the function definition. Do you have any suggestions? –  Andrew Aug 16 '12 at 17:47
@Andrew sorry, I clearly made a mess of it. Let me try to fix that... –  Mr.Wizard Aug 17 '12 at 0:54
Thanks so much. It works great! –  Andrew Aug 17 '12 at 14:04
@Andrew you're welcome. Sorry I wasn't more careful with the copy&paste the first time. I don't know if you typically need to pull a single column from a file, which is what this is written for, or if you intend to read the file and then pull multiple columns and do other manipulations. –  Mr.Wizard Aug 17 '12 at 14:27
Thanks. I would like to read the file and then pull multiple columns, but in my (novice, limited) experience, I run out of memory on my PC to read the entire file and do manipulations on each column. –  Andrew Aug 17 '12 at 15:41

stringColumn[str_String, col_Integer] := Module[{n = StringPosition[str, "\n", 1][[1, 1]]},
StringTake[str, Table[{k n + col}, {k, 0, (StringLength[str] + 1)/n - 1}]]]


or

stringColumn[str_String, col_Integer] := Module[{n = StringPosition[str, "\n", 1][[1, 1]]},
StringTake[str, {col, StringLength[str] - n + col + 1, n}]]


The first one returns a List[], while the second one returns a String[]. Take your pick.

-

An alternative ....

My experience with large files is that Read and its ilk work quicker than Import, but YMMV. I'll use the example text "OriginOfSpecies.txt", and I'll assume that it is in the current directory.

text = ReadList["OriginOfSpecies.txt", Record] (* automagically break on new line *);


Length@text shows we have 758 records.

We can now use StringTake. For example,

char01 = StringTake[text, {1, 1}] (* first character from all strings *);
char02 = StringTake[text, {2, 2}];


etc,.

-
I believe you can shorten this code by using ReadList in place of OpenRead, Read, and Close. +1 –  Mr.Wizard Aug 16 '12 at 12:43
@Mr.Wizard Thanks, Mr. Wizard and dwa. I am a little confused, because it seems that Read[inpu, Record] reads only the first record, not all the records. Is this true? –  Andrew Aug 16 '12 at 17:36
@Andrew: Your're correct. ReadList will read All The Things. –  dwa Aug 16 '12 at 22:48

If you would like to create all the columns in one pass, the following using Transpose should work.

FromCharacterCode /@ Transpose@(ToCharacterCode /@ StringSplit[str, "\n"])


{"Aa1", "Bb2", "Cc3", "Dd4", "Ee5"}

As per Mr Wizard's suggestion, this can be made more elegant using the listable property of To/FromCharacterCode

FromCharacterCode @ Transpose @ (ToCharacterCode @ StringSplit[str, "\n"])

-
I believe that you will find that ToCharacterCode and FromCharacterCode are Listable, and usually perform better this way: FromCharacterCode @ Transpose @ (ToCharacterCode @ StringSplit[str, "\n"]). +1 –  Mr.Wizard Aug 16 '12 at 12:47
@Mr.Wizard Thanks for that :) –  image_doctor Aug 16 '12 at 23:10
ClearAll[stringColumn2];
stringColumn2[strng_String, column : {__Integer}] :=
If[StringLength[#] < Max@Abs@column, "n/A",
StringTake[#, column]] & /@ StringSplit[strng, "\n"]


Usage examples:

str2 = "ABCDE
abcdefgh
12345";

stringColumn2[str2, {3}]
(* {"C","c","3"} *)
stringColumn2[str2, {2, 3}]
(* {"BC","bc","23"} *)
stringColumn2[str2, {3, 5}]
(* {"CDE","cde","345"} *)
stringColumn2[str2, {1, 5, 2}]
(* {"ACE","ace","135"}  *)
stringColumn2[str2, {-1}]
(* {" ", "h", "5"} *)
stringColumn2[str2, {-1, -3, -1}]
(* {" ED","hgf","543"} *)
stringColumn2[str2, {-1, 1, -1}]
(* {" EDCBA","hgfedcba","54321"} *)
stringColumn2[str2, {-8, -1, 1}]
(* {"n/A","abcdefgh","n/A"} *)


EDIT: For "rectangular" strings (strings with equal size substrings seperated by "\n"):

 ClearAll[stringColumn3];
stringColumn3[strng_String, column_] :=
StringTake[StringSplit[strng, "\n"], column]


Usage examples:

str3 = "ABCDEF
abcdef
1234567";

stringColumn3[str3, {2}]
(* {"B","b","2"} *)
stringColumn3[str3, 2]
(* {"AB","ab","12"} *)
stringColumn3[str3, -3]
(* {"EF ","ef ","567"} *)
stringColumn3[str3, {-3}]
(* {"E","e","5"} *)
stringColumn3[str3, {2, 5}]
(* {"BCDE","bcde","2345"} *)
stringColumn3[str3, {2, 6, 2}]
(* {"BDF","bdf","246"} *)
stringColumn3[str3, {-1, -5, -2}]
(*  {" EC"," ec","753"} *)
stringColumn3[str3, {-1, 1, -1}]
(* {" FEDCBA"," fedcba","7654321"} *)

-