Asked by Mate 2u
on 12 Apr 2013

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.

*No products are associated with this question.*

Answer by per isakson
on 13 Apr 2013

Edited by per isakson
on 13 Apr 2013

Accepted 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

ans = 484 484 484 484 484 484 ans = 713500 713500 713500 713500 713500 713500 ans = 2 2 3 1 1 1 >>

Show 8 older comments

per isakson
on 13 Apr 2013

*" into a Matrix in MATLAB "* to save on memory I have chosen different classes for the different variables. Thus, all three in **one** matrix is not possible.

One step at a time

- make a copy of my six line text file (it's in my answer)
- try to reproduce the result displayed in my answer

Yes, all data will fit in memory. Did you say how much RAM and which version of Matlab you have? I'm not sure you need the loop. However, the my code is just a start.

Mate 2u
on 13 Apr 2013

Hi Per Isakson.....your example works.....but here let me demonstrate some examples where it doesent work:

**Input as shown from CTTEST20.txt:**

01/03/2007,15:30:06.000,55.90,CTH07,0

01/03/2007,15:30:30.000,55.75,CTH07,0

01/03/2007,15:30:42.000,55.80,CTH07,0

01/03/2007,15:30:53.000,55.85,CTH07,0

01/03/2007,15:30:57.000,55.75,CTH07,0

01/03/2007,15:31:17.000,55.70,CTH07,0

01/03/2007,15:31:23.000,55.65,CTH07,0

01/03/2007,15:31:36.000,55.55,CTH07,0

01/03/2007,15:31:38.000,55.60,CTH07,0

01/03/2007,15:31:43.000,55.55,CTH07,0

01/03/2007,15:31:44.000,55.60,CTH07,0

01/03/2007,15:31:50.000,55.70,CTH07,0

01/03/2007,15:32:07.000,55.55,CTH07,0

01/03/2007,15:32:07.000,55.90,CTH07,0

01/03/2007,15:40:41.000,55.30,CTH07,0

01/03/2007,15:40:43.000,55.40,CTH07,0

01/03/2007,15:40:52.000,55.30,CTH07,0

01/03/2007,15:40:54.000,55.50,CTH07,0

01/03/2007,15:41:33.000,55.15,CTH07,0

01/03/2007,15:41:34.000,55.20,CTH07,0

**Output in cac:**

'01/03/2007' '01/03/2007' '01/03/2007' '01/03/2007' '01/03/2007' '01/03/2007' '01/03/2007' '01/03/2007' '01/03/2007' '01/03/2007'

55.599998 55.700001 55.549999 55.900002 55.299999 55.400002 55.299999 55.500000 55.150002 55.200001

0 0 0 0 0 0 0 0 0 0

As we can see we are missing 10 entries.....in our larger txt/csv files we get many more missing entries. Additionally look at the output prices...I am not sure why they are varying to the input prices (even if it is marginal)

per isakson
on 13 Apr 2013

Firstly, make some experiments with the [{}Code] button.

Secondly:

- convert the script to a function (I've done it in my answer)
- step through my code with the debugger and analyze what it does
- notice that the twenty lines are indeed printed in the command window - two chunks of ten entries each
- the prices are hurt by the single precision "%f32" - you could change f32 to f64

Answer by Image Analyst
on 12 Apr 2013

What are the classes of each column? Are they all 8 byte (64 bit) doubles? For example, the number of trades might be able to be a 4 byte integer, and most of the floating point numbers could probably be single instead of double. By retrieving it a line at a time and using sscanf() you can place each value into the *smallest type of variable* that is appropriate for that number. For example, assuming no stock price is over $655.35 you could read in the number and multiply by 100 so that all stock prices are in **cents rather than dollars**. That way you can use 16 bit unsigned integer instead of a 32 bit single.

I don't have the toolboxes, but perhaps the Financial Toolbox or the Fixed Point Designer may have efficient ways of handling numbers like prices of stocks.

Like Matt said, perhaps you don't need *all* 60 million rows in memory *at once* - hopefully you can process it in chunks.

Show 1 older comment

Image Analyst
on 13 Apr 2013

Image Analyst
on 13 Apr 2013

Opportunities for recent engineering grads.

## 13 Comments

## Matt Kindig (view profile)

Direct link to this comment:http://www.mathworks.com/matlabcentral/answers/71837#comment_142969

If memory is an issue, you might want to parse the file line-by-line (using fgetl() or similar). That way, you don't need to read the entire file into memory at once. Also, by retrieving one line at a time, you can easily implement your year=2009 inclusion criterion.

## per isakson (view profile)

Direct link to this comment:http://www.mathworks.com/matlabcentral/answers/71837#comment_142970

Please provide a sample. Type a few lines in the Matlab editor.

## Mate 2u (view profile)

Direct link to this comment:http://www.mathworks.com/matlabcentral/answers/71837#comment_142971

Now.... for 1) the modifcation we would need is we can also ignore column 2, but also.....is there a way where we can save memory in matlab (ie assign each matrix cell less bytes?). I know for a larger CSV file than I am using I will have trouble....

## per isakson (view profile)

Direct link to this comment:http://www.mathworks.com/matlabcentral/answers/71837#comment_142974

I cannot see any reason why I should guess and create a sample cvs-file!

## Mate 2u (view profile)

Direct link to this comment:http://www.mathworks.com/matlabcentral/answers/71837#comment_142975

If you want sample of CSV file:

Column 1: 04/29/2008 04/29/2008 04/29/2008 04/29/2008 04/29/2008 04/29/2008

Column 2: 38:52.0 38:53.0 38:56.0 38:56.0 38:56.0 38:57.0

Column 3: 71.35 71.35 71.35 71.35 71.35 71.35

Column 4: CTN08 CTN08 CTN08 CTN08 CTN08 CTN08

Column 5: 2 2 3 1 1 1

Please bear in mind we only need column 1,3,5 in our matrix in Matlab

## per isakson (view profile)

Direct link to this comment:http://www.mathworks.com/matlabcentral/answers/71837#comment_142976

Does the file look like this?

## Mate 2u (view profile)

Direct link to this comment:http://www.mathworks.com/matlabcentral/answers/71837#comment_142977

That is correct.

## per isakson (view profile)

Direct link to this comment:http://www.mathworks.com/matlabcentral/answers/71837#comment_142978

What are the maximum and minimum values of column 3 and 5, respectively?

## Mate 2u (view profile)

Direct link to this comment:http://www.mathworks.com/matlabcentral/answers/71837#comment_142980

minimum values for both 3 and 5 is 0 as 3 represent Price (cant be negative) and 5 represents volume (which also cant be negative). The maximum varies. As I need to program to work on many CSV files, and I cant access many of the CSV files (as the max rows allowed in excel is 1 million), therefore I cant tell you maximum for both 3 and 5... Do you need to define a maximum?

## per isakson (view profile)

Direct link to this comment:http://www.mathworks.com/matlabcentral/answers/71837#comment_142981

See the answer of ImageAnalyst. It is wasteful to store the numbers in double float (8 byte).

Maybe, volume can be stored in uint8.

## Image Analyst (view profile)

Direct link to this comment:http://www.mathworks.com/matlabcentral/answers/71837#comment_142982

The maximum determines the smallest data class you can use. See code in my answer.

## Mate 2u (view profile)

Direct link to this comment:http://www.mathworks.com/matlabcentral/answers/71837#comment_142984

In this case volume (column 5) the maximum would never exceed 2500 (5000 to be sure)

Column 3 the maximum would never exceed 250-350

## per isakson (view profile)

Direct link to this comment:http://www.mathworks.com/matlabcentral/answers/71837#comment_142987

And price will never exceed

cents ????