Take the 2-minute tour ×
Mathematica Stack Exchange is a question and answer site for users of Mathematica. It's 100% free, no registration required.

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.
share|improve this question
    
Have you tried some of the solutions in the linked questions? At first glance your research seems spot-on. –  Yves Klett Jun 17 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 at 22:38
1  
There is, in fact, a function exactly for skipping records in a stream: its name is... Skip! –  Oleksandr R. Jun 18 at 1:09

1 Answer 1

up vote 5 down vote accepted

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.

share|improve this answer
    
2runnyKine: thanks. I have been working on a solution. Will post it for completeness, not as fast as yours… –  Pam Jun 18 at 10:06
    
@Pam. Glad I could help. –  RunnyKine Jun 18 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 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 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 at 4:13

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.