Navigate
Courses Tutorials Practice Pricing Tools Upgrade
Lesson 4 of 4

dtplyr: dplyr at data.table Speed

Across this course Maya learned data.table on her bakery chain's sales: the DT[i, j, by] bracket and keys in Lesson 1, data.table beating dplyr on speed in Lesson 2, and querying data too big for memory in Lesson 3. One friction stuck around. data.table is fast, but its compact bracket is a second language to learn and to read, while the dplyr she already writes is slower on big tables.

dtplyr removes the trade-off. You keep writing ordinary dplyr, and dtplyr quietly translates it into data.table and lets data.table's engine do the work. Readable code on top, fast engine underneath.

By the end of this lesson you will be able to:

  • Wrap a table with lazy_dt() and run the dplyr verbs you already know on it
  • Reveal the data.table code dtplyr wrote with show_query(), and read it back as DT[i, j, by]
  • Materialize a result with collect(), and judge when the bridge is worth it and where it leaks

Prerequisites: you can run R; you know the dplyr verbs and the pipe and the [data.table DT[i, j, by] bracket and :=](data-table-Syntax-and-Keys.html). We translate between the two, so both halves stay familiar.

The wrapper

lazy_dt(): wrap once, then write the dplyr you know

The whole bridge starts with one function. lazy_dt() takes a data frame (or a data.table) and wraps it into a lazy data.table: an object that looks like your table but, instead of running dplyr verbs the moment you type them, records them as a plan to hand to data.table later. "Lazy" just means "does not do the work yet."

Each lesson runs in a fresh R session, so we build Maya's sales right here, then wrap them once.

RInteractive R
library(dplyr) library(data.table) library(dtplyr) setDTthreads(1) # the in-browser R runs on one core # Maya's bakery chain: a week of sales across three shops, one row per sale sales <- data.table( shop = c("Austin","Austin","Austin","Denver","Denver","Denver", "Seattle","Seattle","Seattle","Austin","Denver","Seattle"), item = c("Sourdough","Bagel","Croissant","Sourdough","Bagel","Croissant", "Sourdough","Bagel","Croissant","Sourdough","Sourdough","Bagel"), units = c(18, 40, 27, 22, 30, 15, 12, 33, 19, 20, 24, 28), revenue = c(81, 60, 108, 99, 45, 60, 54, 49, 76, 90, 108, 42) ) sales_dt <- lazy_dt(sales) # wrap once into a lazy data.table sales_dt #> Source: local data table [12 x 4] #> Call: `_DT1` #> #> shop item units revenue #> <chr> <chr> <dbl> <dbl> #> 1 Austin Sourdough 18 81 #> 2 Austin Bagel 40 60 #> 3 Austin Croissant 27 108 #> # 9 more rows #> # Use as.data.table()/as.data.frame()/as_tibble() to access results

  

  





Now write dplyr against sales_dt exactly as you always would. Nothing about the verbs changes:




  

    
RInteractive R

    
sales_dt |>
  filter(shop == "Austin")
#> Source: local data table [4 x 4]
#> Call:   `_DT1`[shop == "Austin"]
#>
#>   shop   item      units revenue
#>   <chr>  <chr>     <dbl>   <dbl>
#> 1 Austin Sourdough    18      81
#> 2 Austin Bagel        40      60
#> 3 Austin Croissant    27     108
#> 4 Austin Sourdough    20      90

    

      
      
    

    

  

  





Same filter() you already know, now carried out by data.table under the hood. The widget shows that step: keep the Austin rows, the others fall away.