This vignette shows the details of how dtplyr translates dplyr expressions into the equivalent data.table code. If you see places where you think I could generate better data.table code, please let me know!
This document assumes that you’re familiar with the basics of data.table; if you’re not, I recommend starting at
To get started, I’ll create a simple lazy table with
The actual data doesn’t matter here since we’re just looking at the translation.
When you print a lazy frame, it tells you that it’s a local data table with four rows. It also prints the call that dtplyr will evaluate when we execute the lazy table. In this case it’s very simple:
If we just want to see the generated code, you can use
show_query(). I’ll use that a lot in this vignette.
Many dplyr verbs have a straightforward translation to either the
j component of
mutate() also uses the
j component with data.table’s special
Note that dplyr will not copy the input data by default, see below for more details.
mutate() allows to refer to variables that you just created using an “extended
transmute() works similarly:
Other verbs require calls to other functions:
distinct() on a computed column uses an intermediate mutate:
Most joins use
dt2 <- lazy_dt(data.frame(a = 1)) dt %>% right_join(dt2, by = "a") %>% show_query() #> `_DT1`[`_DT2`, on = .(a), allow.cartesian = TRUE] dt %>% inner_join(dt2, by = "a") %>% show_query() #> merge(`_DT1`, `_DT2`, all = FALSE, by.x = "a", by.y = "a", allow.cartesian = TRUE) dt %>% full_join(dt2, by = "a") %>% show_query() #> merge(`_DT1`, `_DT2`, all = TRUE, by.x = "a", by.y = "a", allow.cartesian = TRUE)
left_join() will use the
i position where possible:
Anti-joins are easy to translate because data.table has a specific form for them:
Semi-joins are little more complex:
Just like in dplyr,
group_by() doesn’t do anything by itself, but instead modifies the operation of downstream verbs. This generally just involves using the
(Currently there’s no way to use
by instead of
keyby, but that is planned for the future.)
The primary exception is grouped
filter(), which requires the use of
dtplyr tries to generate generate data.table code as close as possible to what you’d write by hand, as this tends to unlock data.table’s tremendous speed. For example, if you
filter() and then
select(), dtplyr generates a single
And similarly when combining filtering and summarising:
This is particularly nice when joining two tables together because you can select variables after you have joined and data.table will only carry those into the join:
By default dtplyr avoids mutating the input data, automatically creating a
copy() if needed:
You can choose to opt out of this copy, and take advantage of data.table’s reference semantics (see
vignette("datatable-reference-semantics") for more details). Do this by setting
immutable = FALSE on construction:
There are two components to the performance of dtplyr: how long it takes to generate the translation, and how well the translation performs. Given my explorations so far, I’m reasonably confident that we’re generating high-quality data.table code, so most of the cost should be in the translation itself.
The following code briefly explores the performance of a few different translations. A signficant amount of work is done by the dplyr verbs, so we benchmark the whole process.
bench::mark( filter = dt %>% filter(a == b, c == d), mutate = dt %>% mutate(a = a * 2, a4 = a2 * 2, a8 = a4 * 2) %>% show_query(), summarise = dt %>% group_by(a) %>% summarise(b = mean(b)) %>% show_query(), check = FALSE )[1:6] #> # A tibble: 3 x 6 #> expression min median `itr/sec` mem_alloc `gc/sec` #> <bch:expr> <bch:tm> <bch:tm> <dbl> <bch:byt> <dbl> #> 1 filter 379µs 417µs 2371. 280B 20.0 #> 2 mutate 810µs 875µs 1130. 280B 19.5 #> 3 summarise 793µs 859µs 1145. 280B 17.3
These translations all take less than a millisecond, suggesting that the performance overhead of dtplyr should be negligible for realistic data sizes. Note that dtplyr run-time scales with the complexity of the pipeline, not the size of the data, so these timings should apply regardless of the size of the underlying data1.
Unless a copy is performed.↩