20
$\begingroup$

I have many big CSV files for processing in Mathematica. And each time loading them into Mathematica takes a long time. Meanwhile, it takes much less time using Pandas under Python.

For example, Mathematica takes much longer to import this test file (download) than Pandas:

fn = "/Users/sunt05/Dropbox/Public/test.csv";

(*Mathematica*)
ds = Import[fn, "Dataset", "HeaderLines" -> 1]; // AbsoluteTiming
(*{7.48445, Null}*)

(*Pandas*)
session = StartExternalSession["Python"];
py = ExternalEvaluate[session];
py[StringTemplate["import pandas as pd; res=pd.read_csv('``')"][fn]] // AbsoluteTiming
(*{0.966052, Null}*)

So, how can we build a fast CSV reader in Mathematica?


Edit:

Thanks folks for the interests!

For using Dataset in the comparison, I just wanted to make the comparison more fair from the user perspective with respect to functionality: Dataset seems more analogous to pandas.DataFrame if a user wants to further process the imported data.

$\endgroup$
7
  • 3
    $\begingroup$ Using OpenRead with ReadLine to store the header and then call ReadList yields a 55% speedup, but it is not as fast as pandas unfortunately. $\endgroup$ Jun 8 at 22:04
  • 2
    $\begingroup$ Sadly, Dataset is rather inefficient in my experience, and simply unsuitable for working with large data. $\endgroup$
    – Szabolcs
    Jun 9 at 9:15
  • $\begingroup$ As far as I know there is a new Typesystem in the works which makes Dataset a magnitude faster. It was supposed to be in 12.2 by enabling a switch but unfortunately hasn't made it yet. There is a WTC2020 talk about it. $\endgroup$
    – Philipp
    Jun 9 at 10:45
  • $\begingroup$ @Szabolcs I largely agree with you on the inefficiency of Dataset. However, two things we may note here: 1. Dataset may facilitate further processing once constructed thought with considerable overhead in the construction; 2. performance in Dataset has been noticeably improved in v12.3. So, overall I consider Dataset an ~acceptable structure for data processing now (i.e., since v12.3). PS, I used to be strongly against Dataset but did love its claimed features. $\endgroup$
    – sunt05
    Jun 9 at 19:10
  • 1
    $\begingroup$ @Szabolcs I would not hold my breath here, for changes which would only affect the type system. One of the biggest sources of inefficiency for Dataset is that it is row-oriented, and therefore can't effectively use the type information to both store data efficiently and manipulate it faster. The other is generality: Dataset does not have a well-defined and restricted query language, but instead allows pretty much all WL functions as parts of its query. This gives it a lot of power, but this at the same time severely limits its efficiency. Both these issues are unlikely to change soon. $\endgroup$ Jun 11 at 15:13
11
$\begingroup$

I think you've actually answered the question yourself - which is to say, perhaps just use pandas with ExternalEvaluate?

The answer here and the "Python" External Evaluation System guide suggest that data can be transferred quite efficiently back to Mathematica (at-least in the case of numpy arrays, but the cost of converting should be manageable).

So we can do something like this:

AbsoluteTiming[ds2 =
   ExternalEvaluate["Python",
    "
import pandas as pd
res=pd.read_csv('/home/george/Downloads/test.csv')
res.to_numpy()
"];]
{1.28181, Null}

This is indeed much faster than Import:

AbsoluteTiming[ds = Import["/home/george/Downloads/test.csv", "HeaderLines" -> 1];]
{5.93136, Null}

PS your "Dataset" format specification was part of the timing discrepancy above, so perhaps a slightly unfair comparison.

$\endgroup$
3
  • $\begingroup$ Thanks for the answer! I put Dataset as import type intentionally as pandas would read in a CSV as DataFrame, a counterpart of Dataset in python, so we have a ~fair comparison. $\endgroup$
    – sunt05
    Jun 9 at 8:22
  • $\begingroup$ @sunt05 A Dataset is something quite different. Look at its FullForm. $\endgroup$
    – Alan
    Jun 9 at 15:29
  • $\begingroup$ thanks @Alan. I understand they are structurally different but just wanted to compare them from the user perspective with respect to functionality: Dataset seems more analogous to pandas.DataFrame if a user wants to further process the imported data. $\endgroup$
    – sunt05
    Jun 9 at 19:15
4
$\begingroup$

If we limit the solution scope to Mathematica (e.g., without using python, or other External-like approaches), it seems Read/ReadList is the only way to go.

Below is my attempt to finish this task, which can deal with mixed types of elements (e.g., mixture of string, real, integer, etc.):

Clear[readCSV]
readCSV[fn_String?FileExistsQ]:=
Module[{str,sample,listHeader,listData,pattern,pre,len,res},
str=OpenRead[fn];
sample=Read[str,{Record,Record}];
{listHeader,listData}=ImportString[StringRiffle[sample,"\n"],"CSV"];
pattern=(Head/@listData)/.{String->Word,Integer->Number};
pattern=Riffle[pattern,Character];
Close[str];
str=OpenRead[fn];
Skip[str,Record];
res=ReadList[str,pattern,WordSeparators->{ ","}][[All,1;;;;2]];
Close[str];
res=Prepend[res,listHeader];
res
]

and some simple profiling:

ds3=readCSV[fn]; // AbsoluteTiming
(*{3.016329`,Null}*)

Profiling results of other approaches:

  • Import:
ds = Import[fn, "Data", "HeaderLines" -> 1]; // AbsoluteTiming
(*{7.85502`,Null}*)
  • pandas:
session = StartExternalSession["Python"];
py = ExternalEvaluate[session];
ds2 = py[
    StringTemplate["import pandas as pd; df=pd.read_csv('``')"][
     fn]]; // AbsoluteTiming

(*{1.27804`,Null}*)

We can see the ReadList-based approach is still behind the pandas one but does much better job than Import.

PS: I really hope WRI can improve the CSV efficiency in future versions.

$\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.