MATLAB: Way of conserving memory when extracting data from CSV

csvextractmemory

Hi everybody I have few questions. I have some HUGE CSV files which I need in Matlab for analysis. The CSV it self has 5 columns. The columns of relevance are:

Column 1 is our date starting from early 2007 all the way till till mid 2011 in the form of mm/dd/yyyy.

Column 3 is our respective prices

Column 5 is the number of trades.

The questions I have are these:

1) How can I extract these 3 columns into a Matrix in MATLAB without taking too much memory (bear in mind that some of these CSV files have around 60 million rows)? Is there a way to decrease the memory of each cell Matlab allocates for the matrix? Please help with code.

2) How can I extract all the information into a non-string matrix (for analysis) for a specific year….ie only for 2009. So I would require to store in Matrix all information for 2009 (bearing in mind the memory limitations in 1).

Thanks so much.

Best Answer

Something like this will do it

    function mate2u
      day_number = zeros( 60*1e6, 1, 'uint16' );  % day_number = 1 for 1/1/2007
      price      = zeros( 60*1e6, 1, 'uint32' );  % 1/100 of cents 
      volume     = zeros( 60*1e6, 1, 'uint16' );  % volume
      pivot_day   = datenum( '1/1/2007', 'mm/dd/yyyy' );
      chunk_size  = 10;  % choose 5*1e6
      fid = fopen( 'mate2u.txt' );
      while not( feof( fid ) )
          cac = textscan( fid, '%s%*s%f32%*s%u16', chunk_size, 'Delimiter', ',' );
          uint16( datenum( cac{1}, 'mm/dd/yyyy' ) - pivot_day )
          uint32( cac{2}*10000 )
          cac{3}
      end
      fclose( fid );
    end

where mate2u.txt is

    04/29/2008,38:52.0,71.35,CTN08,2
    04/29/2008,38:53.0,71.35,CTN08,2
    04/29/2008,38:56.0,71.35,CTN08,3
    04/29/2008,38:56.0,71.35,CTN08,1
    04/29/2008,38:56.0,71.35,CTN08,1
    04/29/2008,38:57.0,71.35,CTN08,1

prints to command window

Related Solutions

MATLAB: Delete the header from a scientific data

Hello Aliaa,

It's good that your files are plain text, you can open them in MATLAB easily.

You need to write a program, consisted of a for loop which has the same number as the number of your files 365.

It's also good that you know all the headers are from line 1 to 21, as you can simply get rid of them.

Hopefully, your files have some sort of order in their names, like cnb1,cnb2,...,cnb365 or any other order.

lets say cnb1 is the first file we want to edit.

use fopen to open cnb1

use fopen to open another file in a different folder with the same name cnb1 (this is going to be the new file without the header)

use getl to read one line at a time

create a while loop to read and write line by line to the files, while should stop when the result of fgetl is empty

make a counter for lines in the while loop

make an if so when the number of counter is smaller than 21 it won't write to the new cnb1 file.

that's all!

finally outside the while loop close both files using fclose

see these in MATLAB documentation

doc fopen
doc fclose
doc fgetl
doc while
doc for

Oh! in the main for loop before you open cnb1 cnb2 and ... you need to create their names lets assume

for i = 1 :10
 filename = ['C:\myFolder\cnb',num2str(i),'.txt'];

Although your files are not txt it's just an assumption.

Give it a try, and let us know if you have prob.

I'll attach a similar file where I edited a txt file, just to see how you can open files, read them and ...

Good Luck!

MATLAB: How to import some large data please

fread is for stream unformatted files; you have formatted delimited file--

doc textscan % and friends

If you really only want/need the two columns sotoo (air-code, untested)

[p,s]=textread('K:\test\test\DJ.csv','%*s%*s,%f%*f%f','delimiter',',');

ought to do unless the third column is indeed a comma-for-a-decimal point as well as a comma-delimited file. In that case you've got a problem. You'll have to read three values instead of just two or preprocess the file or otherwise handle the decimal separator as Matlab can't (and you can't expect it to) know the difference between comma-delimiters and decimal places.

Best Answer

Related Solutions

MATLAB: Delete the header from a scientific data

MATLAB: How to import some large data please

Related Question