36
$\begingroup$

I have two CSV files, each one is around 1 GB of data. When I use Import["file.csv"], it takes a very, very, long time to import the data. So how can I accelerate the import procedure?

The file contains around 2000 columns of different type of data, like numbers, category data and string. And contains around 140000 lines. And there are a lot of missing values in the data. So there is no assumption about the data set like the post as following:

Speeding up Importing and Exporting CSV format

In addition, since the column is encrypted like "VAR_0001", "VAR_0002", so we can not judge whether the column contains number or category or string data.

The original data file can be downloaded from the following link(around 1GB large):

data file

The first 7000 rows of the dataset, around 45M of size:

first 7000 row of the dataset

$\endgroup$
  • 2
    $\begingroup$ Mathematica will fail on large things like this but pandas in python handles these sized imports with ease. Switch to another system for this sort of thing. $\endgroup$ – M.R. Sep 16 '15 at 2:53
  • $\begingroup$ See the answer to my question reading periodic elements from a large file. I'm on my phone so can't easily post the link but you should be able to find it if you search. $\endgroup$ – s0rce Sep 16 '15 at 2:57
  • 1
    $\begingroup$ @SjoerdC.deVries Yes, I have enough RAM, around 96G, I just want to speed up the import procedure. It take a long long time to do the import. $\endgroup$ – m00nlight Sep 16 '15 at 6:39
  • 1
    $\begingroup$ Strongly related (dupe?): "Speeding up Import and Export in CSV format." $\endgroup$ – Alexey Popkov Sep 16 '15 at 6:56
  • 1
    $\begingroup$ That's better. Still more details are welcome. For instance, do you have the format specification for each of your 2000 columns or does the import have to guess the format, or do all the coulmns have to be read in as strings? Come to think of it: Do all the elements in a given column have the same format? $\endgroup$ – Sjoerd C. de Vries Sep 16 '15 at 7:28
23
$\begingroup$

Analysis

Reading the data is not the issue. I can read the data as strings quite fast.

str = OpenRead["train-7000.csv"];
(data = ReadList[str, String]); // AbsoluteTiming//First
(* 0.453251 *)

Memory use is modest too:

data // ByteCount
(* 48550344 *)

It's only slightly larger than the file on disk:

FileByteCount["train-7000.csv"]
(* 46483707 *)

The main issue is that your columns contain mixed data. For instance, column 405 contains both "CHIEF EXECUTIVE OFFICER" and -1. Missing numerical data is marked by the string "NA". It is impossible to specify a general format mask as needed by ReadList. Hence, tesseract's solution won't work for you. Each and every field has to be tested and converted separately. Plain importing as CSV does that and that's why it takes that long:

(data2 = Import["train-7000.csv"];) //AbsoluteTiming//First
(* 55.3151 *)

I guess your full, 1GB file will take 20 minutes or so to load.

Memory usage is much larger now:

data2 // ByteCount
(* 341779872 *)

and Import also needed about 1.2 GB during this process.

Of course, there's SemanticImport that has more sophisticated methods to determine field types, but in practice it is hardly usable:

(data3 = SemanticImport["train-7000.csv"];) //AbsoluteTiming//First
(* 69.1186 *)

data3 // ByteCount
(* 2044683080 *)

Memory usage is now a whopping 2 GB because it returns a Dataset to represent the input, which is very inefficient for flat data. While working, SemanticImport also needed about twice the space that Import needed.

Trying to beat Import

It is possible to do your own custom conversions after reading in the raw strings in data:

(data4 = (StringSplit[#, ","] & /@ (StringReplace[#, "\"" -> ""] & /@ data)) /.
  {
   a_String?(StringMatchQ[#, DigitCharacter ..] &) :> FromDigits[a],
   a_String?(StringMatchQ[#, "-" ~~ DigitCharacter ..] &) :> -FromDigits[StringDrop[a, 1]],
   "NA" | "" | "N/A" -> Null,
   a_String?(StringMatchQ[#, 
        RegularExpression["(?i)^-?\\d+\\.\\d+|^-?\\d+\\.?\\d*e-?\\d+"]] &) 
           :> Internal`StringToDouble[a]
   };
) // AbsoluteTiming // First

(* 44.816 *)

This is about a 20% speed increase.

What it does first is removing all the quotes in your data (you don't need to do this if you don't want it, but I don't think you need quotes in the strings).

Next, the code splits the lines into separate fields at the commas. These two operations are actually pretty fast and take about 1 sec.

We then perform some simple conversions (integers, empty fields, and general C-language numbers). If necessary you can write additional converters for the date fields, map "false" to False etc.

ToExpression is the general Mathematica string-to-expression converter. It is not particularly slow:

ToExpression["1234567890"] // RepeatedTiming
(* {3.7*10^-6, 1234567890} *)

but with the 13 million elements in the 45 MB file it would take 50 secs to convert them all (assuming for the moment hey are all integers). It looks like this conversion is the main bottleneck. FromDigits is about 10 times faster:

FromDigits["1234567890"] // RepeatedTiming
(* {3.2*10^-7, 1234567890} *)

Same for floating point numbers:

Internal`StringToDouble["12345.67890"] // RepeatedTiming
(* {2.8*10^-7, 12345.7} *)

ToExpression["12345.67890"] // RepeatedTiming
(* {3.77*10^-6, 12345.7} *)

I guess the pattern matching part takes considerable time, and so the result of the faster replacements is disappointingly modest.

It should be possible to do this faster. Python with Pandas (written in C) does the conversion in a couple of seconds.

Another alternative, the parser built-in in .NET is convenient (can deal with quoted embedded characters like comma's) and can be used in Mathematica with ease, but is unfortunately just as slow as Import:

Needs["NETLink`"]
InstallNET[];
LoadNETAssembly["Microsoft.VisualBasic"];
parser = NETNew["Microsoft.VisualBasic.FileIO.TextFieldParser", "train-7000.csv"];
parser[SetDelimiters[{","}]];
parser[HasFieldsEnclosedInQuotes] = True;

(data = Reap[While [! parser[EndOfData], Sow[parser[ReadFields[]]]]][[2, 1]];) 
  //First// AbsoluteTiming
(* 38.482 *)

Note that at the end data contains correctly split strings (e.g., "NURSE, LICENSED PRACTICAL" is read as a whole and not cut into two pieces). However, they still need type conversion at this point.

$\endgroup$
  • $\begingroup$ I have been doing quite some editing in the answer box for readability, which may have caused some problems. I'll look into that. Btw I have not forgotten about file closure, I just did not copy it from my notebook. I'll delete the answer for now until I have time to work on this. $\endgroup$ – Sjoerd C. de Vries Sep 16 '15 at 20:27
  • 1
    $\begingroup$ Nice answer, I think you cover all the best ingredients. However, I have some notes. First of all, using StringSplit["", ","]& fails here, because there are a few instances where there is a comma inside a field. That's why there are quotes surrounding the fields, the comma's are supposed to be shielded by the quotes. I suppose the same thing may happen if there are returns in fields (which this standard) allows, so that ReadList may fail, but that is more pathological. The csv file also contains an entry "PSYCHOLOGIST\CLINICAL PSYCHOLOGIST"... [cont] $\endgroup$ – Jacob Akkerboom Sep 21 '15 at 11:41
  • $\begingroup$ Which unfortunately trips up ToExpression. Otherwise I think ToExpression should not be too slow, when we feed it entire rows. Using ToExpression on the first 5000 rows (out of 7000) takes 5.49675 seconds for me. $\endgroup$ – Jacob Akkerboom Sep 21 '15 at 11:42
  • $\begingroup$ ToExpression did throw errors (not unexpectedly) when it encountered the date fields. Not in your case then? Did you feed it full unsplitted lines? $\endgroup$ – Sjoerd C. de Vries Sep 21 '15 at 17:40
  • $\begingroup$ Yeah I fed it full unsplitted lines/rows, see also my answer. It only gave errors after row 5000. Kind of unexpected that the quoted fields would give the trouble, but then again, it makes sense. By the way I wonder how ToExpression is programmed. "The Wolfram language" with all its infix notation may require a bloaty parser. I like your comparison with FromDigits and StringToDouble. $\endgroup$ – Jacob Akkerboom Sep 22 '15 at 14:08
19
$\begingroup$

This is a complementary answer, which shows mostly how to reduce memory use rather than speed (although later I might update it to address the speed issue as well). This answer is based on an undocumented functionality, so the usual warning applies: there is no guarantee that the method suggested below will work in future versions.

Using undocumented Streaming` module to load the file as LazyList object

I will show one way, based on a low-level part of the undocumented Streaming` functionality, available since version 10.1. It will not solve the speed issue in a straight-forward fashion, but, assuming that you want to work with your file later, it will improve the speed of loading all subsequent times, while keeping memory use pretty low.

Needs["Streaming`"]

We now import your file as a LazyList object, with a chunk size of 1000 rows:

(imported = 
   Streaming`LazyListImport["/Users/apple/Downloads/train-7000.csv", "CSV",1000]
)//AbsoluteTiming



(* 
 {134.237,« LazyList[{ID,VAR_0001,VAR_0002,VAR_0003,<<1926>>,VAR_1932,VAR_1933,VAR_1934,target},
{2,H,224,0,4300,C,0,0,false,<<1916>>,98,998,999999998,998,998,9998,9998,IAPS,0},...]»
 }
*)

It admittedly takes time, but let's look at the memory usage:

MaxMemoryUsed[]

(* 68890440 *)

This is only 68Mb, compared to an almost 1Gb needed by Import - as shown in other answers. Moreover, the memory use won't dramatically increase for streaming import, even for a much larger file size, like your original file for example.

Working with LazyList objects

What can you do with LazyList? First of all, you can easily convert it to a normal list, using Normal:

Normal@imported//ByteCount//AbsoluteTiming

(* {0.887823,341779872} *)

which takes less than a second for your list. But, many core List operations are supported for LazyList. For example, you may try:

Take[imported, {1000, 2000}]

Drop[imported, 1000]

Select[imported, #[[4]] == 1 &]

imported[[10]]

imported[[{1,2,3,4}]]

Length[imported]

All of these (except single part extraction and Length) will return LazyList as a result. You can always convert it to a usual List with Normal, or continue working with LazyList objects, at any stage. Most operations which have special implementations for LazyList, are performed in the out-of-core fashion, so they don't require a list to be loaded into memory in full.

Some operations such as Take, Drop, Select are lazy by default, which means that they will only do real work when some elements are extracted from the list. You can force the eager execution for most of them, by wrapping them in Strict wrapper. For example:

Strict[Select[imported, #[[4]] == 1 &]] // AbsoluteTiming

(* {0.766582, Streaming`LazyList[...]} *)

Eager operations are still done out-of-core (so the memory use stays low), but they are performed immediately, on entire list.

Persistence and faster loads for subsequent work with the data

You can persist any given LazyList object into a directory, which you must first create:

CreateDirectory[mydir = FileNameJoin[{$TemporaryDirectory, "train-7000"}]] 

(* "/var/folders/8r/lhqmmmj93hjgxbsx08g_5nhw0000gn/T/train-7000" *)

Now, here is how to persist LazyList:

Streaming`LazyListPut[imported, mydir] // AbsoluteTiming

(* {11.8504, "/var/folders/8r/lhqmmmj93hjgxbsx08g_5nhw0000gn/T/train-7000"} *)

It still takes time, although is an order of magnitude faster than importing the file. But the best part is that you can load the persisted LazyList back to memory in future sessions pretty fast:

Streaming`LazyListGet[mydir]//AbsoluteTiming

(*
  {1.31455, « LazyList[{ID,VAR_0001,VAR_0002,VAR_0003,<<1926>>,VAR_1932,VAR_1933,VAR_1934,target},
  {2,H,224,0,4300,C,0,0,false,<<1916>>,98,998,999999998,998,998,9998,9998,IAPS,0},...]»}
*)

Summary

The speed of import for the method I desrcibed is in fact worse than using plain Import, at least for smaller files (for larger files it may be different, because Import uses a lot of RAM, which makes it slow). However, the memory savings can be very substantial, and the described method should scale well for larger file sizes. One can also persist the resulting LazyList object on disk, and it will load much faster in all future work sessions.

The above method is based in undocumented functionality, so there is no guarantee that it will work in future versions.

Any feedback is more than welcome!

$\endgroup$
  • 4
    $\begingroup$ Lazy lists are great of course, but importing with LazyListImport or Import just takes too long at this moment. Nobody really wants to plan his lunch break so that it coincides with his data imports. Other systems can do this much faster and so should Mathematica if it wants to compete in the Big Data area. $\endgroup$ – Sjoerd C. de Vries Sep 16 '15 at 12:36
  • $\begingroup$ @SjoerdC.deVries I agree, of course. I may update this post later with a much faster importer, if I have the time. LazyList has a mini-framework which makes it pretty easy to plug in custom importers. For example, I can construct one from the code in your answer - which is perhaps one thing I will do and post here, when I have the time. $\endgroup$ – Leonid Shifrin Sep 16 '15 at 12:51
  • $\begingroup$ @LeonidShifrin, This is great. But will LazyListImport support Part, Span, All , eg stream CSV 1st and 4th columns only or the last 1000 rows for example? More generally for trees you see where that's going. $\endgroup$ – alancalvitti Sep 18 '15 at 17:12
  • $\begingroup$ @LeonidShifrin, Re: "slow speed ... the memory savings" - speed is more important than memory. The economy is moving to the real time limit, while the cost of storage is tumbling, eg FlashArray. Google gives Photos storage free just to mine the images >> Exa-scale like Horizon2020. $\endgroup$ – alancalvitti Sep 18 '15 at 17:18
  • $\begingroup$ @alancalvitti LazyList already partially supports Part, Span, All etc specs. But, to make it efficient during the import stage, one has to write an optimized importer. That's surely possible, but has not been done yet. Right now, you have to first import the file into LazyList entirely, and then work with LazyList. There is an option to import lazily too (so that it will only read as much of a file as needed to get the elements you want) - but not random access (like, last 1000 rows without importing the previous). So, indeed, a lot of room for optimization here. $\endgroup$ – Leonid Shifrin Sep 18 '15 at 17:41
17
$\begingroup$

A while ago I had to do some work on large amounts of timestamped magnetometer data. Although my work only recently reached the level of 500MB, you might be able to use some of these techniques on your files.

Here's a sample of my CSV file with timestamp,x,y,z values

2015-06-03T22:21:30.827Z,10.5767,2.2233,-51.9933
2015-06-03T22:21:30.313Z,10.5833,2.2233,-51.9933
2015-06-03T22:21:29.799Z,10.5967,2.2300,-51.9867
2015-06-03T22:21:29.287Z,10.5967,2.2300,-51.9867
2015-06-03T22:21:28.767Z,10.5833,2.2233,-51.9867

I don't know if your work requires date interpretation, but mine did, and that was the primary load on my system. Another problem, was that Mathematica's Compile function doesn't support string objects, so the most I could do to increase speed is Parallelize and simplify. If you could figure out a way to Compile my code you might be able to gain a modest speed increase.

Date interpretation with the Import command: note the actual Import command only takes 20 seconds, but even with parallelizing, the date interpretation brings the total time up to 305.032 seconds. And sometimes the Import command does date interpretation automatically Beware!

{dateCalc[{time_String, x_, y_, z_}] := {AbsoluteTime[time], x, y, z};

dataimport = 
   Import["data.csv"];

data = ParallelMap[dateCalc, dataimport]} // AbsoluteTiming

and the output

{305.032, {{{3.64277854564300000000000*10^9, 10.83, 2.4433, -52.66}, 
 ...771897..., {3.64235839077600000000000*10^9, 10.5767, 2.2167, -51.94}}}}

Now with new improved functions: Note OpenRead , and format both run almost instantly. Also you will have to cater the format to your own needs

{data = OpenRead["data.csv"];

format = {"Number", "Character", "Number", "Character", "Number", 
"Character", "Number", "Character", "Number", "Character", 
"Number", "Character", "Character", "Number", "Character", 
"Number", "Character", "Number"};

data = Parallelize[{AbsoluteTime[{#1, #3, #5, #7, #9, #11}], #14, #16, #18} & @@@
ReadList[data, format]]} // AbsoluteTiming

and the Output

{44.8105, {{{3.64277854564300000000000*10^9, 10.83, 2.4433, -52.66}, 
...771897..., {3.64235839077600000000000*10^9, 10.5767, 2.2167, -51.94}}}}

At only 44.8105 seconds that's a 681.3581 % improvement! but your milage may vary

$\endgroup$
  • 1
    $\begingroup$ But I think for this scenario, this may not so help. Since the data has a lot of columns and I can not know the format of column from the header of csv file. Any other suggestion? $\endgroup$ – m00nlight Sep 16 '15 at 7:50
9
$\begingroup$

Assumptions

  • No fields contain a \n or \r (true for first 7000 rows)

  • everything is encoded in ASCII (true for first 7000 rows)

Observations

  • Some of your fields contain a , (not a problem)

  • Some of your fields contain a \ (big problem)


Your data contains the following field (in FullForm, i.e. the \ is not escaped)

"PSYCHOLOGIST\CLINICAL PSYCHOLOGIST"

In row 5268 between positions 1000 and 1400 in the string corresponding to that row. Unfortunately backslashes are not allowed in Mathematica strings. Apart from that, the following approach is pretty fast

(strData = 
     FromCharacterCode@BinaryReadList["train-7000.csv", "Byte"];) // 
  Timing // First
(splitStrData = StringSplit[strData, "\n" | "\r"]; ) // Timing // First
(exprs5000 = 
    ToExpression["{" ~~ # ~~ "}"] & /@ splitStrData[[;; 5000]]) // 
  Timing // First
0.978296 (*cheats, this was loaded from an SSD drive*)
1.36166
5.68143

Notes

I figured reading as "Byte" and converting to a string was faster than StringJoin on a list of strings. In case we want to handle \n characters in a special way this first line of code is relevant (see further below), otherwise we can also simply use Sjoerd's methods using ReadList.

It turns out ToExpression can perform ok if we feed it a row at a time.

It seems to me that we will have to do some relatively simple (compared to a full parser) parsing if we want to account problematic characters inside fields. One way to do this is to use a CompiledFunction on a list of integers representing the characters.

$\endgroup$
  • $\begingroup$ +1. This looks promising. I didn't realize that ToExpression applied to a string that looks like a list would not complain about date elements, whereas it does complain when given those separately. $\endgroup$ – Sjoerd C. de Vries Sep 22 '15 at 17:32
  • 1
    $\begingroup$ Nice! I've tested your code and for me it is 7x faster than Import (and even 8.5x faster if I use ReadList[file,String]), and 4.5x faster than Sjoerd's. I like the fact that it preserves the entries shielded with commas (so no problem with commas inside an entry). Actually to get the exactly the same result than Import i had to add this replacement at the end: {NA -> "NA", "-1" -> -1} (my benchmark above takes that into account). However ToExpression might be dangerous to execute if there are entries looking Mathematica Code (in comparison Import handles this well: try to import a $\endgroup$ – SquareOne Sep 22 '15 at 19:29
  • $\begingroup$ file containing for example this single line: 1,"Hi",Print["Hello"],12 ... your code will print the message whereas mma will convert it into a string). $\endgroup$ – SquareOne Sep 22 '15 at 19:33
  • $\begingroup$ @SjoerdC.deVries Actually what happens in Jacob's code is that (contrary to your code) it does not remove the inner quotes so an entry which looks like 1,\" 29JAN14:21:16:00\",3 in the file is imported like as a whole string "1,\"29JAN14:21:16:00\",3" then ToExpression["{" ~~ "1,\"29JAN14:21:16:00\",3" ~~ "}"] returns {1, "29JAN14:21:16:00", 3}. The date was shielded with inner quotes wheras in your code you remove these quotes (and have then also problems with entries containing commas ... like for example "ASSISTANT, ADMIN AND LAW" on line 238). $\endgroup$ – SquareOne Sep 22 '15 at 19:47
  • $\begingroup$ @SquareOne you're right. In the mean time I've been looking at .NET functionality to parse CSV more properly now and found a way to get correctly split substrings in Mathematic using NETLink. That works great but unfortunately it took 36 secs with the type conversion still to be done. Perhaps Pythonica is the way to go (to get access to Pandas) but I didn't get it to work yet. $\endgroup$ – Sjoerd C. de Vries Sep 22 '15 at 20:00
2
$\begingroup$

(Long comment, and will be deleted if it is not proven useful.)

News from 2018. Wolfram said (see the link) in the latest version of MMA 11.3 (released today!), there has been an improvement in the efficiency of importing CSV files, etc. Maybe the problem can be solved easily now.

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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