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

Question

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!

Answer 1

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},
    Internal`WithLocalSettings[Null,
        strm=OpenRead[filePath];
        readData:=(data=StringJoin[Riffle[ReadList[strm, Record, blkSize], "\n"]]);
        Replace[
            Reap[While[readData =!= "",
                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[64]:= data=CSVStreamer["~/Downloads/Book5.csv", "Data", {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[72]:= data=CSVStreamer["~/Downloads/Book5.csv", "Data", 5];
In[73]:= col5Median=Median[data];

In[74]:= data=CSVStreamer["~/Downloads/Book5.csv", "Data", 6];
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},
    Internal`WithLocalSettings[Null,
        strm=OpenRead["~/Downloads/Book5.csv"];
        readData:=(data=StringJoin[Riffle[ReadList[strm, Record, blkSize], "\n"]]);
        Replace[
            Reap[While[readData =!= "",
                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