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!

  • 2
    How much RAM do you have? – Marius Ladegård Meyer Oct 11 at 12:14
  • 1
    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
  • 1
    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
  • 1
    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
up vote 3 down vote accepted

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
  • 3
    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

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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