# Reading specific entries from a CSV file

This question is related to the following:

Speeding up Import and Export in CSV format

Stream CSV files

Reading periodic elements from a large file

I have a set of very large CSV files (a few hundred MB), a sample file is available here. RAM limitations make it quite cumbersome to use Import.

I am only interested in a subset of data from the file:

• I want to read data from just a few columns
• I want to read data from every 256th row starting with the 3rd row

 columns = {3, 4, 5, 8, 12}; first = 3; step = 256;

I am interested in methods that minimize memory and speed up import.
-
Have you tried some of the solutions in the linked questions? At first glance your research seems spot-on. – Yves Klett Jun 17 '14 at 20:54
I did try a few things. I can’t figure out how to skip rows. Adding a Take function to the readRows functions in the Stream CSV file example is slowing things down… – Pam Jun 17 '14 at 22:38
There is, in fact, a function exactly for skipping records in a stream: its name is... Skip! – Oleksandr R. Jun 18 '14 at 1:09
For a generic type of answer on this problem you may read my solution here. – Athanassios Feb 19 at 10:44

Here is a more general approach but somewhat slower on my machine:

readSkipCSVGen[file_String?FileExistsQ, first_Integer, step_Integer, cols_: {3, 4, 5, 8, 12}] :=
Module[{str = OpenRead[file], temp, data, ta},
Skip[str, String, first - 1];
data = Reap[
While[
Or[ta =!= EndOfFile, Not@ValueQ[ta]],
Skip[str, String, step - 2];
]
][[2, 1]];
Close[str];
ToExpression[StringCases[data, NumberString]][[All, cols]]
]


Then:

lis = readSkipCSVGen["filename.csv", 3, 256]


For a different set of columns e.g {3, 5, 7, 9, 2, 6, 13}. Note that the columns do not have to be in order.

lis2 = readSkipCSVGen["filename.csv", 3, 256, {3, 5, 7, 9, 2, 6, 13}];

lis2[[1 ;; 5]]

{{-8.44531, 0.32031, -0.080078, 22, 32, 0.007568,  0.19870377},
{-8.47656, 0.26562, -0.080078, 22, 32, 0.007568,  0.19870377},
{-8.48438, 0.29688, -0.080078, 22, 32, 0.007324,  0.19870377},
{-8.46094, 0.3125, -0.080322, 22, 32, 0.007568,   0.19870377},
{-8.45312, 0.28906, -0.080322, 22, 32, 0.007324,  0.19870377}}


Here is an answer tailored for your problem. I make no attempt to generalize this. I may add that in the future but for now this does what you want and is plenty fast.

readSkipCSV[file_String?FileExistsQ, first_Integer, step_Integer] :=
Module[{str = OpenRead[file], data = {}, temp, ta},
Skip[str, String, first - 2];
While[ta =!= EndOfFile,
Skip[str, Word, 2, WordSeparators -> {","}];
temp = Read[str, Table[Word, {3}], WordSeparators -> {","}];
Skip[str, Word, 2, WordSeparators -> {","}];
temp = {temp, Read[str, Word, WordSeparators -> {","}]};
Skip[str, Word, 3, WordSeparators -> {","}];
temp = {temp, Read[str, Word, WordSeparators -> {","}]};
Skip[str, String];
data = {data, temp};
Skip[str, String, step - 2];
]
Close[str]; Partition[ToExpression[Flatten @ data], 5]
]


Usage

lis = readSkipCSV["filename.csv", 3, 256]; // AbsoluteTiming


{2.162101, Null}

lis[[1 ;; 10]]

{{-8.44531, -3.0625, 0.32031, 0.929932, 0.421932219},
{-8.47656, -3.03906, 0.26562, 0.929688, 0.421932219},
{-8.48438, -3.01562, 0.29688, 0.929688, 0.421932219},
{-8.46094, -3.04688, 0.3125, 0.930176, 0.421932219},
{-8.45312, -3.03125, 0.28906, 0.929932, 0.421932219},
{-8.42969, -3.0625, 0.30469, 0.929688, 0.421932219},
{-8.42188, -3.02344, 0.27344, 0.929932, 0.421932219},
{-8.44531, -3.07031, 0.29688, 0.929443, 0.421932219},
{-8.42969, -3.03906, 0.27344, 0.929932, 0.421937941},
{-8.42188, -3.0625, 0.29688, 0.929932, 0.421937941}}


Length @ lis


4096

Edit

You can also use Reap and Sow in addition to AppendTo which avoids Flattening the data and Partitioning it afterwards.

readSkipCSVSow[file_String?FileExistsQ, first_Integer, step_Integer] :=
Module[{str = OpenRead[file], temp, data, ta},
Skip[str, String, first - 1];
data = Reap[
While[Or[ta =!= EndOfFile, Not@ValueQ[ta]],
Skip[str, Word, 2, WordSeparators -> {","}];
temp = Read[str, Table[Word, {3}], WordSeparators -> {","}];
Skip[str, Word, 2, WordSeparators -> {","}];
temp = AppendTo[temp, Read[str, Word, WordSeparators -> {","}]];
Skip[str, Word, 3, WordSeparators -> {","}];
temp = AppendTo[temp, Read[str, Word, WordSeparators -> {","}]];
Skip[str, String];
Sow[temp];
Skip[str, String, step - 2];
]
][[2, 1]];
Close[str]; ToExpression[data]
]


Usage is the same as before.

-
2runnyKine: thanks. I have been working on a solution. Will post it for completeness, not as fast as yours… – Pam Jun 18 '14 at 10:06
@Pam. Glad I could help. – RunnyKine Jun 18 '14 at 16:39
If performance is an issue it might be faster to make all of the Reading and Skiping whole lines (String), then extract the columns you need from the strings. – george2079 Jun 19 '14 at 16:21
@george2079, I actually did that but didn't post that version. Because of the hassle involved in dealing with the String returned when reading .csv files, it turned out to be slightly slower but more general than the answer I posted. I'll probably just put that up for completeness. – RunnyKine Jun 19 '14 at 17:34
Sorry I cannot understand what you are doing there: I am a bit lost why the solutions get so complicated. Perhaps you understand this puzzle here better than me? – hhh Nov 11 '14 at 4:13