How to make Mathematica read in CSV as fast as Python's Pandas

Question

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.

Answer 1

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.

Answer 2

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.