31
$\begingroup$

Here is the GitHub link to the most recent data.table benchmark.

The data.table benchmarks has not been updated since 2014. I heard somewhere that Pandas is now faster than data.table. Is this true? Has anyone done any benchmarks? I have never used Python before but would consider switching if pandas can beat data.table?

Improve this question
$\endgroup$
8
  • 9
    $\begingroup$ That's a really bad reason to switch to python. $\endgroup$
    – Matthew Drury
    Oct 25, 2017 at 3:47
  • 4
    $\begingroup$ @MatthewDrury how so? Data and the manipulation of it is 80% of my job. Only 20% is to fitting models and presentation. Why shouldn't I choose the one that gives me the results the quickest? $\endgroup$
    – xiaodai
    Oct 25, 2017 at 4:31
  • 3
    $\begingroup$ Both python and R are established languages with huge ecosystems and communities. To reduce the choice to a single library is worshiping a single tree in a vast forest. Even so, efficiency is just a single concern among many even for a single library (how expressive is the interface, how does it connect to other library, how extensible is the codebase, how open are its developers). I would argue that the choice itself is a false dichotomy; both communities have a different focus, which lends the languages different strengths. $\endgroup$
    – Matthew Drury
    Oct 25, 2017 at 4:52
  • 6
    $\begingroup$ you have a huge forest that is good for 20% of the work? so don't make a choice thst affecta 80% of your work? nothing stopping me from using panda to do data prep and then model in R python or Julia. i think my thinking is sound. if panda is faster than i should choose it as my main tool. $\endgroup$
    – xiaodai
    Oct 25, 2017 at 6:46
  • 2
    $\begingroup$ You might find the reticulate package in R of interest/use. Also, increasingly a lot of effort has been put into getting R to work/play with databases (see efforts such as dbplyr). $\endgroup$
    – slackline
    Apr 25, 2018 at 13:04

4 Answers 4

Reset to default
31
$\begingroup$

Has anyone done any benchmarks?

Yes, the 2014's benchmark in question has turned into foundation for db-benchmark project. Initial step was to reproduce 2014's benchmark on recent version of software, then to make it a continuous benchmark, so it runs routinely and automatically upgrades software before each run. Over time many things have been added. Below is high level diff of the 2014's benchmark comparing to db-benchmark project.

New:

  • continuous benchmark: runs routinely, upgrades software, re-run benchmarking script
  • more software solutions: spark, python datatable, cuda dataframes, julia dataframes, clickhouse, dask
  • more data cases
    • two new smaller data sizes: 0.5GB (1e7 rows) and 5GB (1e8 rows)
    • two more cardinality factors: unbalanced, heavily unbalanced
    • sortedness
    • data having NA
  • advanced groupby questions
    • median, sd
    • range v1-v2: max(v1)-min(v2)
    • top 2 rows: order(.); head(.,2)
    • regression: cor(v1, v2)^2
    • count and grouping by 6 columns
  • benchmark task: join

Changes (see groupby2014 task for 2014 fully compliant benchmark script):

We are planning to add even more software solutions and benchmark tasks in future. Feedback is very welcome, feel invited to our issue tracker at https://github.com/h2oai/db-benchmark/issues.

Is pandas now faster than data.table?

According the our results pandas is not faster than data.table.

I am pasting medium size data 5GB (1e8 rows) groupby benchmark plot taken from the report at h2oai.github.io/db-benchmark as of 20210312. Consult the h2oai.github.io/db-benchmark#explore-more-data-cases for other data sizes (1e7, 1e9), data cases (cardinality, NAs, sorted), questions groups (advanced), or tasks (join).

For up-to-date timings please visit https://h2oai.github.io/db-benchmark.

groupby_1e8_1e2_0_0_basic

Improve this answer
$\endgroup$
9
  • 1
    $\begingroup$ What about JuliaDB? $\endgroup$
    – skan
    Dec 16, 2018 at 0:09
  • 2
    $\begingroup$ @skan you can track status of that in github.com/h2oai/db-benchmark/issues/63 $\endgroup$
    – jangorecki
    Dec 17, 2018 at 5:17
  • 3
    $\begingroup$ Good answer -- AFAICT the benchmarks you link were all run on the same VM? That is, under the same conditions, pandas and dask need over 128GB RAM for the 50GB table, while the others can perform under this constraint? If so it also reflects my experiences with pandas being very RAM inefficient for a lot of normal everyday stuff on moderate (~10GB) tables, and it's a much bigger issue most of the time than execution speed. (which is much closer and trades-off back and forth in any event depending on the specific workload.) $\endgroup$
    – jkf
    Apr 13, 2019 at 22:20
  • $\begingroup$ @jkf yes, exactly. Machine is 128 GB memory because we are planning to test out of mem processing on 500 GB dataset (10e9 rows). Currently only spark and pydatatable will support that, also soon to be added clickhouse. $\endgroup$
    – jangorecki
    Apr 20, 2019 at 8:44
  • 1
    $\begingroup$ @Arun timings here are not up to date anymore, DT is now 1.48s vs PD 5.18s for 1e8 rows $\endgroup$
    – jangorecki
    Oct 4, 2020 at 15:22
17
$\begingroup$

A colleague and I have conducted some preliminary studies on the performance differences between pandas and data.table. You can find the study (which was split into two parts) on our Blog (You can find part two here).

We figured that there are some tasks where pandas clearly outperforms data.table, but also cases in which data.table is much faster. You can check it out yourself and let us know what you think of the results.

EDIT:
If you don't want to read the blogs in detail, here is a short summary of our setup and our findings:

Setup

We compared pandas and data.table on 12 different simulated data sets on the following operations (so far), which we called scenarios.

  • Data retrieval with a select-like operation
  • Data filtering with a conditional select operation
  • Data sort operations
  • Data aggregation operations

The computations were performed on a machine with an Intel i7 2.2GHz with 4 physical cores, 16GB RAM and a SSD hard drive. Software Versions were OS X 10.13.3, Python 3.6.4 and R 3.4.2. The respective library versions used were 0.22 for pandas and 1.10.4-3 for data.table

Results in a nutshell

  • data.tableseems to be faster when selecting columns (pandason average takes 50% more time)
  • pandas is faster at filtering rows (roughly 50% on average)
  • data.table seems to be considerably faster at sorting (pandas was sometimes 100 times slower)
  • adding a new column appears faster with pandas
  • aggregating results are completely mixed

Please note that I tried to simplify the results as much as possible to not bore you to death. For a more complete visualization read the studies. If you cannot access our webpage, please send me a message and I will forward you our content. You can find the code for the complete study on GitHub. If you have ideas how to improve our study, please shoot us an e-mail. You can find our contacts on GitHub.

Improve this answer
$\endgroup$
5
  • 1
    $\begingroup$ As you may have read from my answer, I already say that the results are mixed. Please clarify if I shall be more specific in my answer, potentially elaborating on some numbers. $\endgroup$
    – Tobias Krabel
    Apr 25, 2018 at 18:23
  • 1
    $\begingroup$ "Your access to this site has been limited." I can't seem to access the site on my phone nor on my work computer. $\endgroup$
    – xiaodai
    Apr 25, 2018 at 22:18
  • 1
    $\begingroup$ I am sorry to read that. I have checked it myself on my phone and had no issues. Could have something to do with the country you try to connect from? $\endgroup$
    – Tobias Krabel
    Apr 26, 2018 at 7:29
  • 1
    $\begingroup$ "4 physical cores" = 8 logical cores. Also it helps to say which specific Intel i7 2.2GHz (which generation? which variant? -HQ?) and what cache size. And for the SSD, what read and write speeds? $\endgroup$
    – smci
    Aug 2, 2018 at 18:15
  • $\begingroup$ How do they compare to Julia dataframes and JuliaDB? $\endgroup$
    – skan
    Dec 16, 2018 at 0:07
4
$\begingroup$

Nope, In fact if dataset size is sooooooo large that pandas crashes, you are basically stuck with dask, which sucks and you can't even do a simple groupby-sum. dplyr may not be fast, but it doesn't mess up.

I'm currently working on some little 2G dataset and a simple print(df.groupby(['INCLEVEL1'])["r"].sum())crashes the dask.

Didn't experience this error with dplyr.

So, if pandas can handle the dataset, I use pandas, if not, stick to R data table.

And yes, you can convert dask back to pandas dataframe with a simple df.compute() But it takes a fairly long time, so you might as well just wait patiently for pandas to load or datatable to read.

Improve this answer
$\endgroup$
1
1
$\begingroup$

I know this is an older post, but figured it may be worth mentioning - using feather (in R and in Python) allows operating on data frames / data tables and sharing those results through feather.

See feather's github page

Improve this answer
$\endgroup$
1
  • $\begingroup$ Segfaults for medium and big datasets $\endgroup$
    – jangorecki
    Apr 16, 2019 at 7:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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