# duckplyr > A **drop-in replacement** for dplyr, powered by DuckDB for **speed**. [dplyr](https://dplyr.tidyverse.org/) is the grammar of data manipulation in the tidyverse. The duckplyr package will run all of your existing dplyr code with identical results, using [DuckDB](https://duckdb.org/) where possible to compute the results faster. In addition, you can analyze larger-than-memory datasets straight from files on your disk or from the web. If you are new to dplyr, the best place to start is the [data transformation chapter](https://r4ds.hadley.nz/data-transform) in *R for Data Science*. ## Installation Install duckplyr from CRAN with: ``` r install.packages("duckplyr") ``` You can also install the development version of duckplyr from [R-universe](https://tidyverse.r-universe.dev/builds): ``` r install.packages("duckplyr", repos = c("https://tidyverse.r-universe.dev", "https://cloud.r-project.org")) ``` Or from [GitHub](https://github.com/) with: ``` r # install.packages("pak") pak::pak("tidyverse/duckplyr") ``` ## Drop-in replacement for dplyr Calling [`library(duckplyr)`](https://duckplyr.tidyverse.org) overwrites dplyr methods, enabling duckplyr for the entire session. ``` r library(conflicted) library(duckplyr) #> Loading required package: dplyr #> ✔ Overwriting dplyr methods with duckplyr methods. #> ℹ Turn off with `duckplyr::methods_restore()`. ``` ``` r conflict_prefer("filter", "dplyr") #> [conflicted] Will prefer dplyr::filter #> over any other package. ``` The following code aggregates the inflight delay by year and month for the first half of the year. We use a variant of the [`nycflights13::flights`](https://rdrr.io/pkg/nycflights13/man/flights.html) dataset, where the timezone has been set to UTC to work around a current limitation of duckplyr, see [`vignette("limits")`](https://duckplyr.tidyverse.org/articles/limits.md). ``` r flights_df() #> # A tibble: 336,776 × 19 #> year month day dep_time sched_d…¹ dep_d…² arr_t…³ sched…⁴ arr_d…⁵ #> #> 1 2013 1 1 517 515 2 830 819 11 #> 2 2013 1 1 533 529 4 850 830 20 #> 3 2013 1 1 542 540 2 923 850 33 #> 4 2013 1 1 544 545 -1 1004 1022 -18 #> 5 2013 1 1 554 600 -6 812 837 -25 #> 6 2013 1 1 554 558 -4 740 728 12 #> 7 2013 1 1 555 600 -5 913 854 19 #> 8 2013 1 1 557 600 -3 709 723 -14 #> 9 2013 1 1 557 600 -3 838 846 -8 #> 10 2013 1 1 558 600 -2 753 745 8 #> # ℹ 336,766 more rows #> # ℹ abbreviated names: ¹​sched_dep_time, ²​dep_delay, ³​arr_time, #> # ⁴​sched_arr_time, ⁵​arr_delay #> # ℹ 10 more variables: carrier , flight , tailnum , #> # origin , dest , air_time , distance , #> # hour , minute , time_hour out <- flights_df() |> filter(!is.na(arr_delay), !is.na(dep_delay)) |> mutate(inflight_delay = arr_delay - dep_delay) |> summarize( .by = c(year, month), mean_inflight_delay = mean(inflight_delay), median_inflight_delay = median(inflight_delay), ) |> filter(month <= 6) ``` The result is a plain tibble: ``` r class(out) #> [1] "tbl_df" "tbl" "data.frame" ``` Nothing has been computed yet. Querying the number of rows, or a column, starts the computation: ``` r out$month #> [1] 1 2 3 4 5 6 ``` Note that, unlike dplyr, the results are not ordered, see [`?config`](https://duckplyr.tidyverse.org/reference/config.md) for details. However, once materialized, the results are stable: ``` r out #> # A tibble: 6 × 4 #> year month mean_inflight_delay median_inflight_delay #> #> 1 2013 1 -3.86 -5 #> 2 2013 2 -5.15 -6 #> 3 2013 3 -7.36 -9 #> 4 2013 4 -2.67 -5 #> 5 2013 5 -9.37 -10 #> 6 2013 6 -4.24 -7 ``` If a computation is not supported by DuckDB, duckplyr will automatically fall back to dplyr. ``` r flights_df() |> summarize( .by = origin, dest = paste(sort(unique(dest)), collapse = " ") ) #> # A tibble: 3 × 2 #> origin dest #> #> 1 EWR ALB ANC ATL AUS AVL BDL BNA BOS BQN BTV BUF BWI BZN CAE CHS C… #> 2 LGA ATL AVL BGR BHM BNA BOS BTV BUF BWI CAE CAK CHO CHS CLE CLT C… #> 3 JFK ABQ ACK ATL AUS BHM BNA BOS BQN BTV BUF BUR BWI CHS CLE CLT C… ``` Restart R, or call [`duckplyr::methods_restore()`](https://duckplyr.tidyverse.org/reference/methods_overwrite.md) to revert to the default dplyr implementation. ``` r duckplyr::methods_restore() #> ℹ Restoring dplyr methods. ``` ## Analyzing larger-than-memory data An extended variant of the [`nycflights13::flights`](https://rdrr.io/pkg/nycflights13/man/flights.html) dataset is also available for download as Parquet files. ``` r year <- 2022:2024 base_url <- "https://blobs.duckdb.org/flight-data-partitioned/" files <- paste0("Year=", year, "/data_0.parquet") urls <- paste0(base_url, files) tibble(urls) #> # A tibble: 3 × 1 #> urls #> #> 1 https://blobs.duckdb.org/flight-data-partitioned/Year=2022/data_0.pa… #> 2 https://blobs.duckdb.org/flight-data-partitioned/Year=2023/data_0.pa… #> 3 https://blobs.duckdb.org/flight-data-partitioned/Year=2024/data_0.pa… ``` Using the [httpfs DuckDB extension](https://duckdb.org/docs/extensions/httpfs/overview.html), we can query these files directly from R, without even downloading them first. ``` r db_exec("INSTALL httpfs") db_exec("LOAD httpfs") flights <- read_parquet_duckdb(urls) ``` Like with local data frames, queries on the remote data are executed lazily. Unlike with local data frames, the default is to disallow automatic materialization if the result is too large in order to protect memory: the results are not materialized until explicitly requested, with a [`collect()`](https://dplyr.tidyverse.org/reference/compute.html) call for instance. ``` r nrow(flights) #> Error: Materialization would result in more than 9090 rows. Use collect() or as_tibble() to materialize. ``` For printing, only the first few rows of the result are fetched. ``` r flights #> # A duckplyr data frame: 110 variables #> Year Quarter Month DayofMonth DayOfWeek FlightDate Report…¹ DOT_I…² #> #> 1 2022 1 1 14 5 2022-01-14 YX 20452 #> 2 2022 1 1 15 6 2022-01-15 YX 20452 #> 3 2022 1 1 16 7 2022-01-16 YX 20452 #> 4 2022 1 1 17 1 2022-01-17 YX 20452 #> 5 2022 1 1 18 2 2022-01-18 YX 20452 #> 6 2022 1 1 19 3 2022-01-19 YX 20452 #> 7 2022 1 1 20 4 2022-01-20 YX 20452 #> 8 2022 1 1 21 5 2022-01-21 YX 20452 #> 9 2022 1 1 22 6 2022-01-22 YX 20452 #> 10 2022 1 1 23 7 2022-01-23 YX 20452 #> # ℹ more rows #> # ℹ abbreviated names: ¹​Reporting_Airline, ²​DOT_ID_Reporting_Airline #> # ℹ 102 more variables: IATA_CODE_Reporting_Airline , #> # Tail_Number , Flight_Number_Reporting_Airline , #> # OriginAirportID , OriginAirportSeqID , #> # OriginCityMarketID , Origin , OriginCityName , #> # OriginState , OriginStateFips , OriginStateName , #> # OriginWac , DestAirportID , DestAirportSeqID , #> # DestCityMarketID , Dest , DestCityName , #> # DestState , DestStateFips , DestStateName , #> # DestWac , CRSDepTime , DepTime , DepDelay , #> # DepDelayMinutes , DepDel15 , … ``` ``` r flights |> count(Year) #> # A duckplyr data frame: 2 variables #> Year n #> #> 1 2022 6729125 #> 2 2023 6847899 #> 3 2024 3461319 ``` Complex queries can be executed on the remote data. Note how only the relevant columns are fetched and the 2024 data isn’t even touched, as it’s not needed for the result. ``` r out <- flights |> mutate(InFlightDelay = ArrDelay - DepDelay) |> summarize( .by = c(Year, Month), MeanInFlightDelay = mean(InFlightDelay, na.rm = TRUE), MedianInFlightDelay = median(InFlightDelay, na.rm = TRUE), ) |> filter(Year < 2024) out |> explain() #> ┌---------------------------┐ #> │ HASH_GROUP_BY │ #> │ -------------------- │ #> │ Groups: │ #> │ #0 │ #> │ #1 │ #> │ │ #> │ Aggregates: │ #> │ mean(#2) │ #> │ median(#3) │ #> │ │ #> │ ~6729125 Rows │ #> └-------------┬-------------┘ #> ┌-------------┴-------------┐ #> │ PROJECTION │ #> │ -------------------- │ #> │ Year │ #> │ Month │ #> │ InFlightDelay │ #> │ InFlightDelay │ #> │ │ #> │ ~13458250 Rows │ #> └-------------┬-------------┘ #> ┌-------------┴-------------┐ #> │ PROJECTION │ #> │ -------------------- │ #> │ Year │ #> │ Month │ #> │ InFlightDelay │ #> │ │ #> │ ~13458250 Rows │ #> └-------------┬-------------┘ #> ┌-------------┴-------------┐ #> │ READ_PARQUET │ #> │ -------------------- │ #> │ Function: │ #> │ READ_PARQUET │ #> │ │ #> │ Projections: │ #> │ Year │ #> │ Month │ #> │ DepDelay │ #> │ ArrDelay │ #> │ │ #> │ File Filters: │ #> │ (CAST(Year AS DOUBLE) < │ #> │ 2024.0) │ #> │ │ #> │ Scanning Files: 2/3 │ #> │ │ #> │ ~13458250 Rows │ #> └---------------------------┘ out |> print() |> system.time() #> # A duckplyr data frame: 4 variables #> Year Month MeanInFlightDelay MedianInFlightDelay #> #> 1 2022 11 -5.21 -7 #> 2 2023 11 -7.10 -8 #> 3 2022 8 -5.27 -7 #> 4 2023 4 -4.54 -6 #> 5 2022 7 -5.13 -7 #> 6 2022 4 -4.88 -6 #> 7 2023 8 -5.73 -7 #> 8 2023 7 -4.47 -7 #> 9 2022 2 -6.52 -8 #> 10 2023 5 -6.17 -7 #> # ℹ more rows #> user system elapsed #> 1.145 0.455 9.402 ``` Over 10M rows analyzed in about 10 seconds over the internet, that’s not bad. Of course, working with Parquet, CSV, or JSON files downloaded locally is possible as well. For full compatibility, `na.rm = FALSE` by default in the aggregation functions: ``` r flights |> summarize(mean(ArrDelay - DepDelay)) #> # A duckplyr data frame: 1 variable #> `mean(ArrDelay - DepDelay)` #> #> 1 NA ``` ## Further reading - [`vignette("large")`](https://duckplyr.tidyverse.org/articles/large.md): Tools for working with large data - [`vignette("prudence")`](https://duckplyr.tidyverse.org/articles/prudence.md): How duckplyr can help protect memory when working with large data - [`vignette("fallback")`](https://duckplyr.tidyverse.org/articles/fallback.md): How the fallback to dplyr works internally - [`vignette("limits")`](https://duckplyr.tidyverse.org/articles/limits.md): Translation of dplyr employed by duckplyr, and current limitations - [`vignette("duckdb")`](https://duckplyr.tidyverse.org/articles/duckdb.md): Using the full power of DuckDB - [`vignette("developers")`](https://duckplyr.tidyverse.org/articles/developers.md): Using duckplyr for individual data frames and in other packages - [`vignette("telemetry")`](https://duckplyr.tidyverse.org/articles/telemetry.md): Telemetry in duckplyr ## Getting help If you encounter a clear bug, please file an issue with a minimal reproducible example on [GitHub](https://github.com/tidyverse/duckplyr/issues). For questions and other discussion, please use [forum.posit.co](https://forum.posit.co/). ## Code of conduct Please note that this project is released with a [Contributor Code of Conduct](https://duckplyr.tidyverse.org/CODE_OF_CONDUCT). By participating in this project you agree to abide by its terms. # Package index ## Using duckplyr - [`duckdb_tibble()`](https://duckplyr.tidyverse.org/reference/duckdb_tibble.md) [`as_duckdb_tibble()`](https://duckplyr.tidyverse.org/reference/duckdb_tibble.md) [`is_duckdb_tibble()`](https://duckplyr.tidyverse.org/reference/duckdb_tibble.md) : duckplyr data frames - [`read_parquet_duckdb()`](https://duckplyr.tidyverse.org/reference/read_parquet_duckdb.md) : Read Parquet files using DuckDB - [`read_csv_duckdb()`](https://duckplyr.tidyverse.org/reference/read_csv_duckdb.md) : Read CSV files using DuckDB - [`read_json_duckdb()`](https://duckplyr.tidyverse.org/reference/read_json_duckdb.md) : Read JSON files using DuckDB - [`read_file_duckdb()`](https://duckplyr.tidyverse.org/reference/read_file_duckdb.md) : Read files using DuckDB - [`read_tbl_duckdb()`](https://duckplyr.tidyverse.org/reference/read_tbl_duckdb.md) **\[experimental\]** : Read a table from a DuckDB database file - [`read_sql_duckdb()`](https://duckplyr.tidyverse.org/reference/read_sql_duckdb.md) **\[experimental\]** : Return SQL query as duckdb_tibble ## dplyr verbs ### Computing and materializing data - [`compute(`*``*`)`](https://duckplyr.tidyverse.org/reference/compute.duckplyr_df.md) : Compute results - [`compute_parquet()`](https://duckplyr.tidyverse.org/reference/compute_parquet.md) : Compute results to a Parquet file - [`compute_csv()`](https://duckplyr.tidyverse.org/reference/compute_csv.md) : Compute results to a CSV file - [`as_tbl()`](https://duckplyr.tidyverse.org/reference/as_tbl.md) **\[experimental\]** : Convert a duckplyr frame to a dbplyr table - [`collect(`*``*`)`](https://duckplyr.tidyverse.org/reference/collect.duckplyr_df.md) : Force conversion to a data frame - [`pull(`*``*`)`](https://duckplyr.tidyverse.org/reference/pull.duckplyr_df.md) : Extract a single column - [`explain(`*``*`)`](https://duckplyr.tidyverse.org/reference/explain.duckplyr_df.md) : Explain details of a tbl ### Verbs that affect rows - [`arrange(`*``*`)`](https://duckplyr.tidyverse.org/reference/arrange.duckplyr_df.md) : Order rows using column values - [`distinct(`*``*`)`](https://duckplyr.tidyverse.org/reference/distinct.duckplyr_df.md) : Keep distinct/unique rows - [`filter(`*``*`)`](https://duckplyr.tidyverse.org/reference/filter.duckplyr_df.md) [`filter_out(`*``*`)`](https://duckplyr.tidyverse.org/reference/filter.duckplyr_df.md) : Keep rows that match a condition - [`slice_head(`*``*`)`](https://duckplyr.tidyverse.org/reference/slice_head.duckplyr_df.md) : Subset rows using their positions - [`head(`*``*`)`](https://duckplyr.tidyverse.org/reference/head.duckplyr_df.md) : Return the First Parts of an Object ### Verbs that affect columns - [`mutate(`*``*`)`](https://duckplyr.tidyverse.org/reference/mutate.duckplyr_df.md) : Create, modify, and delete columns - [`transmute(`*``*`)`](https://duckplyr.tidyverse.org/reference/transmute.duckplyr_df.md) **\[superseded\]** : Create, modify, and delete columns - [`select(`*``*`)`](https://duckplyr.tidyverse.org/reference/select.duckplyr_df.md) : Keep or drop columns using their names and types - [`rename(`*``*`)`](https://duckplyr.tidyverse.org/reference/rename.duckplyr_df.md) : Rename columns - [`relocate(`*``*`)`](https://duckplyr.tidyverse.org/reference/relocate.duckplyr_df.md) : Change column order ### Grouping and summarising verbs - [`count(`*``*`)`](https://duckplyr.tidyverse.org/reference/count.duckplyr_df.md) : Count the observations in each group - [`summarise(`*``*`)`](https://duckplyr.tidyverse.org/reference/summarise.duckplyr_df.md) : Summarise each group down to one row ### Verbs that work with multiple tables - [`left_join(`*``*`)`](https://duckplyr.tidyverse.org/reference/left_join.duckplyr_df.md) : Left join - [`right_join(`*``*`)`](https://duckplyr.tidyverse.org/reference/right_join.duckplyr_df.md) : Right join - [`inner_join(`*``*`)`](https://duckplyr.tidyverse.org/reference/inner_join.duckplyr_df.md) : Inner join - [`full_join(`*``*`)`](https://duckplyr.tidyverse.org/reference/full_join.duckplyr_df.md) : Full join - [`semi_join(`*``*`)`](https://duckplyr.tidyverse.org/reference/semi_join.duckplyr_df.md) : Semi join - [`anti_join(`*``*`)`](https://duckplyr.tidyverse.org/reference/anti_join.duckplyr_df.md) : Anti join - [`intersect(`*``*`)`](https://duckplyr.tidyverse.org/reference/intersect.duckplyr_df.md) : Intersect - [`union(`*``*`)`](https://duckplyr.tidyverse.org/reference/union.duckplyr_df.md) : Union - [`union_all(`*``*`)`](https://duckplyr.tidyverse.org/reference/union_all.duckplyr_df.md) : Union of all - [`setdiff(`*``*`)`](https://duckplyr.tidyverse.org/reference/setdiff.duckplyr_df.md) : Set difference - [`symdiff(`*``*`)`](https://duckplyr.tidyverse.org/reference/symdiff.duckplyr_df.md) : Symmetric difference ### Unsupported verbs - [`unsupported`](https://duckplyr.tidyverse.org/reference/unsupported.md) : Verbs not implemented in duckplyr ## Using duckplyr for all data frames - [`methods_overwrite()`](https://duckplyr.tidyverse.org/reference/methods_overwrite.md) [`methods_restore()`](https://duckplyr.tidyverse.org/reference/methods_overwrite.md) : Forward all dplyr methods to duckplyr ### Datasets - [`flights_df()`](https://duckplyr.tidyverse.org/reference/flights_df.md) : Flight data ## Configuration, telemetry, and internals - [`config`](https://duckplyr.tidyverse.org/reference/config.md) : Configuration options - [`fallback_sitrep()`](https://duckplyr.tidyverse.org/reference/fallback.md) [`fallback_config()`](https://duckplyr.tidyverse.org/reference/fallback.md) [`fallback_review()`](https://duckplyr.tidyverse.org/reference/fallback.md) [`fallback_upload()`](https://duckplyr.tidyverse.org/reference/fallback.md) [`fallback_purge()`](https://duckplyr.tidyverse.org/reference/fallback.md) : Fallback to dplyr - [`stats_show()`](https://duckplyr.tidyverse.org/reference/stats_show.md) : Show stats - [`last_rel()`](https://duckplyr.tidyverse.org/reference/last_rel.md) : Retrieve details about the most recent computation - [`db_exec()`](https://duckplyr.tidyverse.org/reference/db_exec.md) : Execute a statement for the default connection ## Relational operations and expressions - [`new_relational()`](https://duckplyr.tidyverse.org/reference/new_relational.md) [`rel_to_df()`](https://duckplyr.tidyverse.org/reference/new_relational.md) [`rel_filter()`](https://duckplyr.tidyverse.org/reference/new_relational.md) [`rel_project()`](https://duckplyr.tidyverse.org/reference/new_relational.md) [`rel_aggregate()`](https://duckplyr.tidyverse.org/reference/new_relational.md) [`rel_order()`](https://duckplyr.tidyverse.org/reference/new_relational.md) [`rel_join()`](https://duckplyr.tidyverse.org/reference/new_relational.md) [`rel_limit()`](https://duckplyr.tidyverse.org/reference/new_relational.md) [`rel_distinct()`](https://duckplyr.tidyverse.org/reference/new_relational.md) [`rel_set_intersect()`](https://duckplyr.tidyverse.org/reference/new_relational.md) [`rel_set_diff()`](https://duckplyr.tidyverse.org/reference/new_relational.md) [`rel_set_symdiff()`](https://duckplyr.tidyverse.org/reference/new_relational.md) [`rel_union_all()`](https://duckplyr.tidyverse.org/reference/new_relational.md) [`rel_explain()`](https://duckplyr.tidyverse.org/reference/new_relational.md) [`rel_alias()`](https://duckplyr.tidyverse.org/reference/new_relational.md) [`rel_set_alias()`](https://duckplyr.tidyverse.org/reference/new_relational.md) [`rel_names()`](https://duckplyr.tidyverse.org/reference/new_relational.md) : Relational implementer's interface - [`new_relexpr()`](https://duckplyr.tidyverse.org/reference/new_relexpr.md) [`relexpr_reference()`](https://duckplyr.tidyverse.org/reference/new_relexpr.md) [`relexpr_constant()`](https://duckplyr.tidyverse.org/reference/new_relexpr.md) [`relexpr_function()`](https://duckplyr.tidyverse.org/reference/new_relexpr.md) [`relexpr_comparison()`](https://duckplyr.tidyverse.org/reference/new_relexpr.md) [`relexpr_window()`](https://duckplyr.tidyverse.org/reference/new_relexpr.md) [`relexpr_set_alias()`](https://duckplyr.tidyverse.org/reference/new_relexpr.md) : Relational expressions # Articles ### Articles - [Large data](https://duckplyr.tidyverse.org/articles/large.md): - [Memory protection: controlling automatic materialization](https://duckplyr.tidyverse.org/articles/prudence.md): - [Fallback to dplyr](https://duckplyr.tidyverse.org/articles/fallback.md): - [Translations](https://duckplyr.tidyverse.org/articles/limits.md): - [Interoperability with DuckDB and dbplyr](https://duckplyr.tidyverse.org/articles/duckdb.md): - [Selective use of duckplyr](https://duckplyr.tidyverse.org/articles/developers.md): - [Telemetry](https://duckplyr.tidyverse.org/articles/telemetry.md):