# dtplyr ## Overview [![data.table seal of approval](reference/figures/dt-seal.png)](https://rdatatable-community.github.io/The-Raft/posts/2024-08-01-seal_of_approval-dtplyr/)dtplyr provides a [data.table](https://r-datatable.com/) backend for dplyr. The goal of dtplyr is to allow you to write dplyr code that is automatically translated to the equivalent, but usually much faster, data.table code. See [`vignette("translation")`](https://dtplyr.tidyverse.org/articles/translation.md) for details of the current translations, and [table.express](https://github.com/asardaes/table.express) and [rqdatatable](https://github.com/WinVector/rqdatatable/) for related work. ## Installation You can install from CRAN with: ``` r install.packages("dtplyr") ``` Or try the development version from GitHub with: ``` r # install.packages("pak") pak::pak("tidyverse/dtplyr") ``` ## Usage To use dtplyr, you must at least load dtplyr and dplyr. You may also want to load [data.table](https://r-datatable.com/) so you can access the other goodies that it provides: ``` r library(data.table) library(dtplyr) library(dplyr, warn.conflicts = FALSE) ``` Then use [`lazy_dt()`](https://dtplyr.tidyverse.org/reference/lazy_dt.md) to create a “lazy” data table that tracks the operations performed on it. ``` r mtcars2 <- lazy_dt(mtcars) ``` You can preview the transformation (including the generated data.table code) by printing the result: ``` r mtcars2 %>% filter(wt < 5) %>% mutate(l100k = 235.21 / mpg) %>% # liters / 100 km group_by(cyl) %>% summarise(l100k = mean(l100k)) #> Source: local data table [3 x 2] #> Call: `_DT1`[wt < 5][, `:=`(l100k = 235.21/mpg)][, .(l100k = mean(l100k)), #> keyby = .(cyl)] #> #> cyl l100k #> #> 1 4 9.05 #> 2 6 12.0 #> 3 8 14.9 #> #> # Use as.data.table()/as.data.frame()/as_tibble() to access results ``` But generally you should reserve this only for debugging, and use [`as.data.table()`](https://rdrr.io/pkg/data.table/man/as.data.table.html), [`as.data.frame()`](https://rdrr.io/r/base/as.data.frame.html), or [`as_tibble()`](https://tibble.tidyverse.org/reference/as_tibble.html) to indicate that you’re done with the transformation and want to access the results: ``` r mtcars2 %>% filter(wt < 5) %>% mutate(l100k = 235.21 / mpg) %>% # liters / 100 km group_by(cyl) %>% summarise(l100k = mean(l100k)) %>% as_tibble() #> # A tibble: 3 × 2 #> cyl l100k #> #> 1 4 9.05 #> 2 6 12.0 #> 3 8 14.9 ``` ## Why is dtplyr slower than data.table? There are two primary reasons that dtplyr will always be somewhat slower than data.table: - Each dplyr verb must do some work to convert dplyr syntax to data.table syntax. This takes time proportional to the complexity of the input code, not the input *data*, so should be a negligible overhead for large datasets. [Initial benchmarks](https://dtplyr.tidyverse.org/articles/translation.html#performance) suggest that the overhead should be under 1ms per dplyr call. - To match dplyr semantics, [`mutate()`](https://dplyr.tidyverse.org/reference/mutate.html) does not modify in place by default. This means that most expressions involving [`mutate()`](https://dplyr.tidyverse.org/reference/mutate.html) must make a copy that would not be necessary if you were using data.table directly. (You can opt out of this behaviour in [`lazy_dt()`](https://dtplyr.tidyverse.org/reference/lazy_dt.md) with `immutable = FALSE`). ## Code of Conduct Please note that the dtplyr project is released with a [Contributor Code of Conduct](https://dtplyr.tidyverse.org/CODE_OF_CONDUCT.html). By contributing to this project, you agree to abide by its terms. # Package index ## Getting data in and out - [`lazy_dt()`](https://dtplyr.tidyverse.org/reference/lazy_dt.md) : Create a "lazy" data.table for use with dplyr verbs - [`collect(`*``*`)`](https://dtplyr.tidyverse.org/reference/collect.dtplyr_step.md) [`compute(`*``*`)`](https://dtplyr.tidyverse.org/reference/collect.dtplyr_step.md) [`as.data.table(`*``*`)`](https://dtplyr.tidyverse.org/reference/collect.dtplyr_step.md) [`as.data.frame(`*``*`)`](https://dtplyr.tidyverse.org/reference/collect.dtplyr_step.md) [`as_tibble(`*``*`)`](https://dtplyr.tidyverse.org/reference/collect.dtplyr_step.md) : Force computation of a lazy data.table ## Single table verbs - [`arrange(`*``*`)`](https://dtplyr.tidyverse.org/reference/arrange.dtplyr_step.md) : Arrange rows by column values - [`count(`*``*`)`](https://dtplyr.tidyverse.org/reference/count.dtplyr_step.md) : Count observations by group - [`distinct(`*``*`)`](https://dtplyr.tidyverse.org/reference/distinct.dtplyr_step.md) : Subset distinct/unique rows - [`filter(`*``*`)`](https://dtplyr.tidyverse.org/reference/filter.dtplyr_step.md) : Subset rows using column values - [`group_by(`*``*`)`](https://dtplyr.tidyverse.org/reference/group_by.dtplyr_step.md) [`ungroup(`*``*`)`](https://dtplyr.tidyverse.org/reference/group_by.dtplyr_step.md) : Group and ungroup - [`group_modify(`*``*`)`](https://dtplyr.tidyverse.org/reference/group_modify.dtplyr_step.md) [`group_map(`*``*`)`](https://dtplyr.tidyverse.org/reference/group_modify.dtplyr_step.md) : Apply a function to each group - [`head(`*``*`)`](https://dtplyr.tidyverse.org/reference/head.dtplyr_step.md) [`tail(`*``*`)`](https://dtplyr.tidyverse.org/reference/head.dtplyr_step.md) : Subset first or last rows - [`mutate(`*``*`)`](https://dtplyr.tidyverse.org/reference/mutate.dtplyr_step.md) : Create and modify columns - [`transmute(`*``*`)`](https://dtplyr.tidyverse.org/reference/transmute.dtplyr_step.md) : Create new columns, dropping old - [`relocate(`*``*`)`](https://dtplyr.tidyverse.org/reference/relocate.dtplyr_step.md) : Relocate variables using their names - [`rename(`*``*`)`](https://dtplyr.tidyverse.org/reference/rename.dtplyr_step.md) [`rename_with(`*``*`)`](https://dtplyr.tidyverse.org/reference/rename.dtplyr_step.md) : Rename columns using their names - [`reframe(`*``*`)`](https://dtplyr.tidyverse.org/reference/reframe.dtplyr_step.md) : Summarise each group to one row - [`select(`*``*`)`](https://dtplyr.tidyverse.org/reference/select.dtplyr_step.md) : Subset columns using their names - [`slice(`*``*`)`](https://dtplyr.tidyverse.org/reference/slice.dtplyr_step.md) [`slice_head(`*``*`)`](https://dtplyr.tidyverse.org/reference/slice.dtplyr_step.md) [`slice_tail(`*``*`)`](https://dtplyr.tidyverse.org/reference/slice.dtplyr_step.md) [`slice_min(`*``*`)`](https://dtplyr.tidyverse.org/reference/slice.dtplyr_step.md) [`slice_max(`*``*`)`](https://dtplyr.tidyverse.org/reference/slice.dtplyr_step.md) : Subset rows using their positions - [`summarise(`*``*`)`](https://dtplyr.tidyverse.org/reference/summarise.dtplyr_step.md) : Summarise each group to one row ## Two table verbs - [`left_join(`*``*`)`](https://dtplyr.tidyverse.org/reference/left_join.dtplyr_step.md) : Join data tables - [`intersect(`*``*`)`](https://dtplyr.tidyverse.org/reference/intersect.dtplyr_step.md) [`union(`*``*`)`](https://dtplyr.tidyverse.org/reference/intersect.dtplyr_step.md) [`union_all(`*``*`)`](https://dtplyr.tidyverse.org/reference/intersect.dtplyr_step.md) [`setdiff(`*``*`)`](https://dtplyr.tidyverse.org/reference/intersect.dtplyr_step.md) : Set operations ## tidyr verbs - [`complete(`*``*`)`](https://dtplyr.tidyverse.org/reference/complete.dtplyr_step.md) : Complete a data frame with missing combinations of data - [`drop_na(`*``*`)`](https://dtplyr.tidyverse.org/reference/drop_na.dtplyr_step.md) : Drop rows containing missing values - [`expand(`*``*`)`](https://dtplyr.tidyverse.org/reference/expand.dtplyr_step.md) : Expand data frame to include all possible combinations of values. - [`fill(`*``*`)`](https://dtplyr.tidyverse.org/reference/fill.dtplyr_step.md) : Fill in missing values with previous or next value - [`nest(`*``*`)`](https://dtplyr.tidyverse.org/reference/nest.dtplyr_step.md) : Nest - [`pivot_wider(`*``*`)`](https://dtplyr.tidyverse.org/reference/pivot_wider.dtplyr_step.md) : Pivot data from long to wide - [`pivot_longer(`*``*`)`](https://dtplyr.tidyverse.org/reference/pivot_longer.dtplyr_step.md) : Pivot data from wide to long - [`replace_na(`*``*`)`](https://dtplyr.tidyverse.org/reference/replace_na.dtplyr_step.md) : Replace NAs with specified values - [`separate(`*``*`)`](https://dtplyr.tidyverse.org/reference/separate.dtplyr_step.md) : Separate a character column into multiple columns with a regular expression or numeric locations - [`unite(`*``*`)`](https://dtplyr.tidyverse.org/reference/unite.dtplyr_step.md) : Unite multiple columns into one by pasting strings together. # Articles ### All vignettes - [Translation](https://dtplyr.tidyverse.org/articles/translation.md):