Reading specific entries from a CSV file

Question

This question is related to the following:

Speeding up Import and Export in CSV format

Stream CSV or TSV 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.

Answer 1

Previous Answers Below

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]],
      Sow[temp = Read[str, String]];
      Skip[str, String, step - 2];
      ta = Read[str, String];
      ]
     ][[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}}

Original Answer

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];
  ta = Read[str, String];
  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];
    ta = Read[str, String];
    ]
   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}}

Number of lines read:

Length @ lis

4096

This is from reading your ~255MB file

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];
                 ta = Read[str, String];
                 ]
              ][[2, 1]];
      Close[str]; ToExpression[data]
     ]

Usage is the same as before.