Timeline for How can I import a huge CSV file quickly?
Current License: CC BY-SA 3.0
9 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Sep 24, 2015 at 19:40 | comment | added | Sjoerd C. de Vries | @JacobAkkerboom True. BTW I added the .NET piece at the bottom of my post. | |
| Sep 24, 2015 at 10:25 | comment | added | Jacob Akkerboom |
@SjoerdC.deVries that sounds very interesting! This raises another question about the structure of the data: What is the structure of numerical values? Are they specific to Mathematica, or is there some way of representing numbers shared by languages that the data satisfies? E.g. I suppose forms like 10`1000 and 1*^7 are not present in the data.
|
|
| Sep 24, 2015 at 10:18 | comment | added | Jacob Akkerboom |
@SquareOne thanks for the detailed observations and positive feedback :). I agree that using ToExpression seems a bit insecure. However note that Import is generally not a safe function. Maybe it is safe for csvs, but it is unsafe for example in Import[test.nb, "Elements"], which was quite unexpected to me. By the way feel free to edit my answer if you wish. It is interesting that NA and "-1" are in some sense the only exceptions to yet another assumption about the data.
|
|
| Sep 22, 2015 at 20:00 | comment | added | Sjoerd C. de Vries | @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. | |
| Sep 22, 2015 at 19:47 | comment | added | SquareOne |
@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).
|
|
| Sep 22, 2015 at 19:33 | comment | added | SquareOne |
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).
|
|
| Sep 22, 2015 at 19:29 | comment | added | SquareOne |
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
|
|
| Sep 22, 2015 at 17:32 | comment | added | Sjoerd C. de Vries | +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. | |
| Sep 21, 2015 at 12:12 | history | answered | Jacob Akkerboom | CC BY-SA 3.0 |