The very first task in any data analysis workflow is simply reading the data, and this absolutely must be done quickly and efficiently so the more interesting work can begin. Across many industries and domains, the CSV file format is king for storing and sharing tabular data. Loading CSVs fast and robustly is crucial, and it must scale well across a wide variety of file sizes, data types, and shapes. This post compares the performance for reading 8 different real-world datasets across three different CSV parsers: R’s fread, Pandas’ read_csv, and Julia’s CSV.jl. Each of these was chosen as the “best in class” CSV parser in each R, Python and Julia, respectively.
All three tools have robust support for loading a wide variety of data types with potentially missing values, but only fread (R) and CSV.jl (Julia) support multithreading—Pandas only supports single threaded CSV loading. Julia’s CSV.jl is further unique in that it is the only tool that is fully implemented in its higher-level language rather than being implemented in C and wrapped from R / Python. (Pandas does have a slightly more capable Python-native parser, it is significantly slower and nearly all uses of read_csv default to the C engine.) As such, the CSV.jl benchmarks here not only represent the speed of loading data in Julia, but are also indicative of the sorts of performance that’s possible in the subsequent Julia code used in the analysis.
The following benchmarks show that Julia’s CSV.jl is 1.5 to 5 times faster than Pandas even on a single core; with multithreading enabled, it is as fast or faster than R’s read_csv. The tools used for benchmarking were BenchmarkTools.jl for Julia, microbenchmark for R, and timeit for Python.
Homogenous data
Let’s start with some homogeneous datasets i.e. datasets which have the same kind of data in all columns. The datasets in this section, apart from stock price dataset, are derived from this benchmark site. The performance metric is the time taken to load a dataset as the number of threads is increased from 1 to 20. Since Pandas does not support multi-threading, single threaded speed is reported across the board for all core counts.
Performance on Homogenous Datasets:
Uniform Float dataset: The first dataset contains float values arranged in 1 Million rows and 20 columns. Pandas takes 232 milliseconds to load this file. Single threaded data.table is 1.6 times faster than CSV.jl. With Multithreading, CSV.jl is at its best, more than double the speed of data.table. CSV.jl is 1.5 times faster than Pandas without multithreading, and about 11 times faster with.
Uniform String dataset(I): This dataset contains string values in all columns and has 1 Million rows and 20 columns. Pandas takes 546 milliseconds to load the file. With R, adding threads doesn’t seem to lead to any performance gain. Single threaded CSV.jl is 2.5 times faster than data.table. At 10 threads, it is about 14 times faster than data.table.
Uniform String dataset(II): The dimensions of this dataset are the same as that of the one above. However, every column has missing values as well. Pandas takes 300 milliseconds. Without threading, CSV.jl is 1.2 times faster than R, and with, it is about 5 times faster.
Apple stock prices:
This dataset contains 50 million rows and 5 columns, and is 2.5GB. The rows are open, high, low, and close prices for AAPL stock. The four columns with prices are float values, and there is a date column.
The single threaded CSV.jl is about 1.5 times faster than R’s fread from data.table. With multithreading CSV.jl is about 22 times faster! Pandas’ read_csv takes 34s to read, this is slower than both R and Julia.
Performance on Heterogeneous Datasets
Mixed dataset: This dataset has 10k rows and 200 columns. The columns contain, String, Float, DateTime, and missing values. Pandas takes about 400 milliseconds to load this dataset. Without threading, CSV.jl is 2 times faster than R, and is about 10 times faster with 10 threads.
Mortgage risk dataset
Now, let’s look at a wider dataset. This mortgage risk dataset from Kaggle is a mixed type dataset, with 356k rows and 2190 columns. The columns are heterogeneous and have values of types String, Int, Float, Missing. Pandas takes 119s to read in this dataset. Single threaded fread is about twice faster than CSV.jl. However, with more threads Julia is either as fast or slightly faster than R.
Wide dataset: This is a considerably wider dataset with 1000 rows and 20,000 columns. The dataset contains string and Int values. Pandas takes 7.3 seconds to read the dataset. In this case, single threaded data.table is about 5 times faster than CSV.jl. With more threads, CSV.jl is competitive with data.table. Increasing the number of threads doesn’t seem to result in any performance gain in case of data.table.
Fannie Mae Acquisition dataset: This dataset can be downloaded from Fannie Mae site here. The dataset has 4 Million rows and 25 columns and values of types Int, String, Float, Missing.
Single threaded data.table is 1.25 times faster than CSV.jl. But, the performance of CSV.jl keeps increasing with more threads. CSV.jl gets about 4 times faster with multi-threading.
Summary Charts:
Across all eight datasets, Julia’s CSV.jl is always faster than Pandas, and with multi-threading it is competitive with R’s data.table.
System Info: The specs of the system on which the benchmarking was performed are as below
$ lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description: Ubuntu 18.04.4 LTS
Release: 18.04
Codename: bionic$ uname -a
Linux antarctic 5.6.0-custom+ #1 SMP Mon Apr 6 00:47:33 EDT 2020 x86_64 x86_64 x86_64 GNU/Linux$ lscpu
Architecture: x86_64
CPU op-mode(s): 32-bit, 64-bit
Byte Order: Little Endian
CPU(s): 40
On-line CPU(s) list: 0-39
Thread(s) per core: 2
Core(s) per socket: 10
Socket(s): 2
NUMA node(s): 2
Vendor ID: GenuineIntel
CPU family: 6
Model: 85
Model name: Intel(R) Xeon(R) Silver 4114 CPU @ 2.20GHz
Stepping: 4
CPU MHz: 800.225
CPU max MHz: 3000.0000
CPU min MHz: 800.0000
BogoMIPS: 4400.00
Virtualization: VT-x
L1d cache: 32K
L1i cache: 32K
L2 cache: 1024K
L3 cache: 14080K
NUMA node0 CPU(s): 0-9,20-29
NUMA node1 CPU(s): 10-19,30-39$ free -h
total used free shared buff/cache available
Mem: 62G 3.3G 6.3G 352K 52G 58G
Swap: 59G 3.2G 56G