# Working with a CSV data file with 100 millions rows (and 30 columns)

I am new to working with large datasets.. Currently I am struggling to upload a "big data" file into Mathematica. The file is in the CSV format and has 14GB. There are 100 millions of rows and 30 columns which contain integers, bytes, long, doubles.

I have tried through both "Import" and "ReadList" but the kernel just stops after some time without even giving an error message.

My question is if it is feasible to work with such files in Mathematica at all and if so how to upload this amount of data? (In fact at the end I would need a 10% subsample of all data, selected according to a certain rule..)

Thank you for all hints!

• How much RAM do you have? – Marius Ladegård Meyer Oct 11 at 12:14
• Hm. Files of this size have to be processes in batches. And indeed ReadList, ReadLine etc. should be the tools of choice. See here for a solution based on ReadList. It can certainly be adapted to load only chunks of the whole data set. See also the three-argument version of ReadList. – Henrik Schumacher Oct 11 at 12:14
• I have 8GB RAM... – Kass Oct 11 at 12:18
• can you give a minimal working example of the kind of data in each filed (only a few lines) and the intended operations? What is the code you have tried so far? – rhermans Oct 11 at 13:46
• Recall that there is noticable overhead for importing data into Mathematica as well. A file that is x bytes might take 3x bytes of memory to import (the specifics here vary by file format). – user6014 Oct 11 at 22:55

Import isn't going to be able to handle all of that data all at once. I made a file with ~111 million rows by just copying the few rows you provided enough times. We can stream some set number of lines to Import at a time, only extracting the value from the particular columns of interest. With 111 million rows, you may be able to get all the columns you need in one import, then work with the data from there (perhaps you only need columns 1, 5 and 6 but what you want is the value from column 1 if the values of col 5 and col 6 for that row are less than the median of the entire column). If this is too much data to handle with the ram you have, you may need to import one column, take the median, then stream through the data again to take column 1 but only after checking the criteria for 5 and 6. This way we only hold 10000 rows in memory at one time to do the check, and still end up accumulating one data point at a time. If you need the entire row for well over 10 million rows, it just might not be possible to pull that much data in all at once on your machine. I'm going to make a much smaller example (only 100,000 rows) to show some simple results that we can get using this approach. On my machine it took about 30 minutes to import one column with 111 million rows. This is meant to be code that can be modified to suit your needs but you'll need to play with it. I would start by trying these techniques on a smaller set of data like I did (100k or so rows) and once you're confident it's doing what you want move on to the whole file.

This code makes a few assumptions though. There can not be a newline in any of the cells of data for this to work. We are assuming each newline is a row delimiter. We are also assuming this doesn't have any non ISOLatin1 characters (encoding could be changed if necessary but this is usually likely to be fastest). This can also only control the column spec (a specific int column, a list of desired columns in any order, a span of columns or All). But this code will generically get a column of data from a large CSV file with a low memory footprint, which I've tested up to 111 million rows (granted with 16gb of ram). Writing code which combines some column spec and a condition to grab data generically was also fairly tough, it's easier to write very specific code which isn't abstracted to a simple streamer using data we were able to more easily acquire from the streamer if this is needed.

Please let me know how this works or if you have any more questions. If you give me more details about exactly what you need for a final result, maybe I can offer more suggestions.

In[63]:= CSVStreamer[filePath_String, element_String:"Data", col_:All]:=
Block[{strm, readData, data, res, blkSize = 10000},
InternalWithLocalSettings[Null,
Replace[
data = ImportString[data,
{"CSV", element, All, col},
CharacterEncoding->"ISOLatin1"
];
If[MatchQ[data, {} | {{}}]|| FailureQ[data],
Break[];
];
Scan[Sow,data];

]]
,
{
{_, {x_}} :> x,
_ :> {{}}
}
]
,
Close[strm];
]
]

(* Get columns 1, 5, 6: *)

In[65]:= data//Length
Out[65]= 100000

In[66]:= Short[data]
Out[66]//Short= {{90085565,9269201,7217073},{60403635,3251388,7217073},
<<99997>>,{97705949,7627282,8961033}}

(* Now the index of the 5th column in data is 2: *)

In[67]:= col5Median=Median[data[[All,2]]]
Out[67]= 7606964

In[68]:= col6Median=Median[data[[All,3]]]
Out[68]= 7850103

(* Now we can select column 1 from all rows which meet some condition
(one can adjust the pattern with cases.. If at this point you succeeded,
remove the :> x piece and it will take the all values from selected columns
which matches the pattern):  *)

In[69]:= newData=Cases[data, {x_, _?(# < col5Median&), _?(# < col6Median&)} :> x];

In[70]:= newData//Length
Out[70]= 23529

In[71]:= newData[[1]]
Out[71]= 60403635

(* If we weren't able to import all the columns we needed the first time,
start by getting the medians from individual columns:  *)

In[73]:= col5Median=Median[data];

In[75]:= col6Median=Median[data];

(* Now lets manually modify the streaming code to get only column 1
while checking a condition:  *)

In[76]:=
newData2=
Block[{strm, readData, res, blkSize = 10000},
InternalWithLocalSettings[Null,
Replace[
data = ImportString[data,
(* get only rows 1 5 and 6*)
{"CSV", "Data", All, {1, 5, 6}},
CharacterEncoding->"ISOLatin1"
];
If[MatchQ[data, {} | {{}}]|| FailureQ[data],
Break[];
];
Scan[
(* 1, 2, 3 correspond to cols 1, 5, 6 *)
If[#[[2]] < col5Median && #[[3]] < col6Median,
Sow[#[[1]]]
] &
,
data
];
]]
,
{
{_, {x_}} :> x,
_ :> {{}}
}
]
,
Close[strm];
]
];

In[77]:= newData2//Length
Out[77]= 23529

(* Verify our result *)

In[78]:= newData===newData2
Out[78]= True

• There are other older questions about importing large data in M. Perhaps those threads could also point to here to try this code as a possible solution. I'm still new to StackExchange, I'm not sure how to go about doing that.. Can anybody help? Is this useful? – GenericAccountName Oct 12 at 2:48
• Hi GenericAccountName, first -- thank you very much, the code works perfectly and its first part has really solved my problem. But may I ask one question on in the second part (where we first getting separate columns and then select the other one according to a condition). In the part $If[#[[2]] < col5Median && #[[3]] < col6Median, Sow[#[[1]]]$, if I want to get say 6 columns, how should I deal with the Sow(.) part to account for multiple columns efficiently? – Kass Oct 23 at 11:04
• PS: To my shame I have not found a better way than to use If["condition", Sow[#[[1]]]&& Sow[#[[2]]]&&....&& Sow[#[[6]]]] and then to run Partition[newData2,6]. But it works. Any thoughts on improving this are appreciated! – Kass Oct 24 at 15:31
• @Kass sorry for the delay, I'm glad the code is working out. If you want all of the rows that were queried, like {1,5,6} in the second example above, If[condition, Sow[#]]& should have worked, it would have Sow'd the entire list as one entry, and what you get at the end is the already parttioned table.. If you wanted just rows 5/6 (which map to 2/3 in the example since we already have a row of length 3), then just If[condition, Sow[#[[2;;3]]]]& – GenericAccountName Nov 7 at 4:47