dtplyr 1.0.0

Photo by Jay Ruzesky

Hadley Wickham

I’m very excited to announce that dtplyr 1.0.0 is now on CRAN. dtplyr provides a data.table backend for dplyr, allowing you to write dplyr code that is automatically translated to the equivalent data.table code.

dtplyr 1.0.0 gives you the speed of data.table with the syntax of dplyr, unlocking the value of data.table to every user of dplyr. Of course, if you know data.table, you can still write it directly, just as we expect SQL experts to continue to write SQL rather than having dbplyr generate it for them. Understanding these foundational tools is particularly important if you want to eke out every last drop of performance.

This version of dtplyr is a complete rewrite which allows it to generate significantly more performant translations. dtplyr now works like dbplyr, where computation only happens when requested by as.data.table(), as.data.frame() or as_tibble() (this idea can also be seen in the table.express and rqdatatable packages). Unfortunately, this rewrite breaks pretty much all existing uses of dtplyr. But frankly, the prior version of dtplyr was not very good and few people used it, so a major overhaul should break little code in the wild.

In this article, I’ll introduce you to the basic usage of dtplyr, talk about some of the performance implications, and show off some of the translations that I’m most proud of.

Usage

To use dtplyr, you must at least load dtplyr and dplyr. You might also want to load data.table so you can access the other goodies that it provides:

library(data.table)
library(dtplyr)
library(dplyr, warn.conflicts = FALSE)

Then use lazy_dt() to create a “lazy” data table that tracks the operations performed on it:

mtcars2 <- lazy_dt(mtcars)

You can use dplyr verbs with this object as if it was a data frame. But there’s a big difference behind the scenes: instead of immediately performing the operation, dtplyr just records what you’re doing so when needed it can generate a single, efficient, data.table statement. You can preview the transformation (including the generated data.table code) by printing the result:

mtcars2 %>% 
  filter(wt < 5) %>% 
  mutate(l100k = 235.21 / mpg) %>% # liters / 100 km
  group_by(cyl) %>% 
  summarise(l100k = mean(l100k))
#> Source: local data table [?? x 2]
#> Call:   `_DT1`[wt < 5][, `:=`(l100k = 235.21/mpg)][, .(l100k = mean(l100k)), 
#>     keyby = .(cyl)]
#> 
#>     cyl l100k
#>   <dbl> <dbl>
#> 1     4  9.05
#> 2     6 12.0 
#> 3     8 14.9 
#> 
#> # Use as.data.table()/as.data.frame()/as_tibble() to access results

Generally, however, you should reserve this preview for exploration and debugging, and instead use as.data.table(), as.data.frame(), or as_tibble() to indicate that you’re done writing the transformation and want to access the results:

mtcars2 %>% 
  filter(wt < 5) %>% 
  mutate(l100k = 235.21 / mpg) %>% # liters / 100 km
  group_by(cyl) %>% 
  summarise(l100k = mean(l100k)) %>% 
  as_tibble()
#> # A tibble: 3 x 2
#>     cyl l100k
#>   <dbl> <dbl>
#> 1     4  9.05
#> 2     6 12.0 
#> 3     8 14.9

Performance

How fast is dtplyr? data.table is generally faster than dplyr, but dtplyr has to do some work to perform the translation, so it’s reasonable to ask if it’s worth it. Do the benefits of using data.table outweigh the cost of the automated translation? My experimentation suggests that it is: the cost of translation is low, and independent of the size of the data. In this section, I’ll explore the performance trade-off through three lenses: translation cost, copies, and interface mismatch.

Translation cost

Each dplyr verb must do some work to convert dplyr syntax to data.table syntax. We can use the bench package to time the cost of the four-step pipeline that I used above:

bench::mark(
  translate = mtcars2 %>% 
    filter(wt < 5) %>% 
    mutate(l100k = 235.21 / mpg) %>% # liters / 100 km
    group_by(cyl) %>% 
    summarise(l100k = mean(l100k))
)
#> # A tibble: 1 x 6
#>   expression      min   median `itr/sec` mem_alloc `gc/sec`
#>   <bch:expr> <bch:tm> <bch:tm>     <dbl> <bch:byt>    <dbl>
#> 1 translate     787µs    969µs     1028.      280B     26.4

Because this pipeline does not use as.data.table() or print() it only generates the data.table code, it doesn’t run it, so we’re timing the translation cost. The translation cost scales with the complexity of the pipeline, not the size of the data, so these timings will apply regardless of the size of the underlying data.

My intial experiments suggest that the translation cost is typically a few milliseconds. Since the computational cost increases with the size of the data, the translation cost becomes a smaller proportion of the total as the data size grows, suggesting the dtplyr does not impose a significant overhead on top of data.table.

Take the following example, which uses the large nycflights13 dataset. This isn’t really big enough for data.table to really shine, but it’s about as big as you can get in an R package. Here I’m going to compute the average arrival delay by destination. It takes raw dplyr about 40ms to do the work. Again, the dtplyr translation is fast, around 1ms, and then computation using data.table only takes about 20ms, almost twice as fast as dplyr.

library(nycflights13)
flights_dt <- lazy_dt(flights)

delay_by_dest <- function(df) {
  df %>%
    filter(!is.na(arr_delay)) %>% 
    group_by(dest) %>% 
    summarise(n = n(), delay = mean(arr_delay))
}

bench::mark(
  flights %>% delay_by_dest(),
  flights_dt %>% delay_by_dest(),
  flights_dt %>% delay_by_dest() %>% as_tibble(),
  check = FALSE
)
#> # A tibble: 3 x 6
#>   expression                                         min  median `itr/sec`
#>   <bch:expr>                                     <bch:t> <bch:t>     <dbl>
#> 1 flights %>% delay_by_dest()                     35.7ms    36ms      27.8
#> 2 flights_dt %>% delay_by_dest()                 671.9µs 824.6µs    1230. 
#> 3 flights_dt %>% delay_by_dest() %>% as_tibble()  18.7ms  20.2ms      48.0
#> # … with 2 more variables: mem_alloc <bch:byt>, `gc/sec` <dbl>

Copies

There is one place where dtplyr does have to add overhead: when translations would generate data.table code that modifies the input in place, like mutate(). dtplyr matches dplyr semantics (which never modifies in place), so most expressions involving mutate() must make a copy:

mtcars2 %>% 
  mutate(x = x * 2) %>% 
  show_query()
#> copy(`_DT1`)[, `:=`(x = x * 2)]

However, dtplyr never generates more than one copy (no matter how many mutates you use), and it also recognises many situations where data.table creates an implicit copy:

mtcars2 %>% 
  mutate(y = x * 2) %>% 
  mutate(z = y * 2) %>% 
  show_query()
#> copy(`_DT1`)[, `:=`(y = x * 2)][, `:=`(z = y * 2)]

mtcars2 %>% 
  filter(x == 1) %>% 
  mutate(x = x * 2) %>% 
  show_query()
#> `_DT1`[x == 1][, `:=`(x = x * 2)]

However, if you have very datasets, creating a deep copy can be expensive. dtplyr allows you to opt out by setting immutable = FALSE. This ensures that dtplyr never makes a copy:

mtcars3 <- lazy_dt(as.data.table(mtcars), immutable = FALSE)

mtcars3 %>% 
  mutate(x2 = x * 2) %>% 
  show_query()
#> `_DT3`[, `:=`(x2 = x * 2)]

Interface mismatch

The hardest overhead to measure is the cost of interface mismatch, i.e. where data.table has features that dplyr doesn’t. For example, there’s no way to express cross- or rolling-joins with dplyr, so there’s no way to generate efficient data.table code for these cases. It’s hard to estimate this cost, but it’s something that we think about when considering what features to add to dplyr next.

Translation

If you’re familiar with data.table, you might be interested to learn more about how the translation works. Here I’ll show a few things I think are particularly interesting, using show_query().

df <- data.frame(a = 1:5, b = 1:5, c = 1:5, d = 1:5)
dt <- lazy_dt(df)

Most uses of the basic dplyr verbs generate calls to [.data.table:

dt %>% select(-c, -d) %>% show_query()
#> `_DT4`[, .(a, b)]
dt %>% summarise(x = mean(x)) %>% show_query()
#> `_DT4`[, .(x = mean(x))]
dt %>% mutate(x = a + b) %>% show_query()
#> copy(`_DT4`)[, `:=`(x = a + b)]
dt %>% filter(a == 1) %>% show_query()
#> `_DT4`[a == 1]
dt %>% arrange(a, desc(b)) %>% show_query()
#> `_DT4`[order(a, desc(b))]

As do simple left and right joins:

dt2 <- lazy_dt(data.frame(a = 1, y = 1, z = 1))
dt %>% left_join(dt2, by = "a") %>% show_query()
#> `_DT5`[`_DT4`, on = .(a), allow.cartesian = TRUE]
dt %>% right_join(dt2, by = "a") %>% show_query()
#> `_DT4`[`_DT5`, on = .(a), allow.cartesian = TRUE]

Where possible, dtplyr will collapse multiple calls to [:

dt %>% 
  filter(a == 1) %>% 
  select(-a) %>% 
  show_query()
#> `_DT4`[a == 1, .(b, c, d)]

dt %>% 
  left_join(dt2, by = "a") %>% 
  select(a, b, z) %>% 
  show_query()
#> `_DT5`[`_DT4`, .(a, b, z), on = .(a), allow.cartesian = TRUE]

But note that the order is important, as a select() followed by a filter() has to generate two statements:

dt %>% 
  select(a = b) %>% 
  filter(a == 1) %>% 
  show_query()
#> `_DT4`[, .(a = b)][a == 1]

When you mix basic dplyr verbs with group_by(), dtplyr adds the keyby argument:

dt %>% 
  group_by(a) %>% 
  summarise(b = mean(b)) %>% 
  show_query()
#> `_DT4`[, .(b = mean(b)), keyby = .(a)]

And when filtering, this automatically uses .SD:

dt %>% 
  group_by(a) %>% 
  filter(b < mean(b)) %>% 
  show_query()
#> `_DT4`[, .SD[b < mean(b)], keyby = .(a)]

You can learn more in vignette("translation").

There are a couple of limitations that I hope to address in the next version of dtplyr. Currently, you can’t translate the _if variants, and there is weak support for the group_ functions. If you discover other functions that don’t work as you expect, please file an issue!.

Acknowledgements

Big thanks to the data.table community, particularly Michael Chirico, for their help educating me on the best way to translate dplyr code into performant, idiomatic, data.table code.

I’d also like to thank everyone to helped make this release happen through their contributions on GitHub: @AlanFeder, @batpigandme, @benjaminleroy, @clayphan, @ColinFay, @daranzolin, @edgararuiz, @franknarf1, @hadley, @hlynurhallgrims, @hope-data-science, @ianmcook, @jl5000, @jonthegeek, @JoshuaSturm, @lenay12, @MichaelChirico, @nlbjan1, @quantitative-technologies, @richpauloo, @S-UP, @tmastny, @TobiRoby, @tomazweiss, @torema-ed, @Vidaringa, @vlahm, @vspinu, @xiaodaigh, and @yiqinfu.

Upcoming events
Miami, FL
December 16 – 18, 2019
Improve your tool building skills with this small hands-on workshop on a boat. All profits go to support the mission of the field school.
San Francisco, CA
January 27 – 30, 2020
rstudio::conf 2020 covers all things RStudio, including workshops to teach you the tidyverse, and talks to show you the latest and greatest features.