Learn R Programming

⚠️There's a newer version (1.0.10) of this package.Take me there.

dplyr

dplyr is the next iteration of plyr, focussed on tools for working with data frames (hence the d in the name). It has three main goals:

  • Identify the most important data manipulation tools needed for data analysis and make them easy to use from R.

  • Provide blazing fast performance for in-memory data by writing key pieces in C++.

  • Use the same interface to work with data no matter where it's stored, whether in a data frame, a data table or database.

You can install:

  • the latest released version from CRAN with

    install.packages("dplyr")
  • the latest development version from github with

    if (packageVersion("devtools") < 1.6) {
      install.packages("devtools")
    }
    devtools::install_github("hadley/lazyeval")
    devtools::install_github("hadley/dplyr")

You'll probably also want to install the data packages used in most examples: install.packages(c("nycflights13", "Lahman")).

If you encounter a clear bug, please file a minimal reproducible example on github. For questions and other discussion, please use the manipulatr mailing list.

Learning dplyr

To get started, read the notes below, then read the intro vignette: vignette("introduction", package = "dplyr"). To make the most of dplyr, I also recommend that you familiarise yourself with the principles of tidy data: this will help you get your data into a form that works well with dplyr, ggplot2 and R's many modelling functions.

If you need more, help I recommend the following (paid) resources:

  • dplyr on datacamp, by Garrett Grolemund. Learn the basics of dplyr at your own pace in this interactive online course.

  • Introduction to Data Science with R: How to Manipulate, Visualize, and Model Data with the R Language, by Garrett Grolemund. This O'Reilly video series will teach you the basics oto be an effective analyst in R.

Key data structures

The key object in dplyr is a tbl, a representation of a tabular data structure. Currently dplyr supports:

You can create them as follows:

library(dplyr) # for functions
library(nycflights13) # for data
flights

# Caches data in local SQLite db
flights_db1 <- tbl(nycflights13_sqlite(), "flights")

# Caches data in local postgres db
flights_db2 <- tbl(nycflights13_postgres(), "flights")

Each tbl also comes in a grouped variant which allows you to easily perform operations "by group":

carriers_df  <- group_by(flights, carrier)
carriers_db1 <- group_by(flights_db1, carrier)
carriers_db2 <- group_by(flights_db2, carrier)

Single table verbs

dplyr implements the following verbs useful for data manipulation:

  • select(): focus on a subset of variables
  • filter(): focus on a subset of rows
  • mutate(): add new columns
  • summarise(): reduce each group to a smaller number of summary statistics
  • arrange(): re-order the rows

They all work as similarly as possible across the range of data sources. The main difference is performance:

system.time(summarise(carriers_df, delay = mean(ArrDelay, na.rm = TRUE)))
#   user  system elapsed
#  0.010   0.002   0.012
system.time(summarise(collect(carriers_db1, delay = mean(ArrDelay))))
#   user  system elapsed
#  0.402   0.058   0.465
system.time(summarise(collect(carriers_db2, delay = mean(ArrDelay))))
#   user  system elapsed
#  0.386   0.097   0.718

The data frame methods are all at least an order of magnitude faster than the plyr equivalent. The database methods are slower, but can work with data that don't fit in memory.

library(plyr)
system.time(ddply(hflights, "UniqueCarrier", summarise,
  delay = mean(ArrDelay, na.rm = TRUE)))
#   user  system elapsed
#  0.527   0.078   0.604

do()

As well as the specialised operations described above, dplyr also provides the generic do() function which applies any R function to each group of the data.

Let's take the batting database from the built-in Lahman database. We'll group it by year, and then fit a model to explore the relationship between their number of at bats and runs:

batting_db <- tbl(lahman_sqlite(), "Batting")
batting_df <- collect(batting_db)

years_db <- group_by(batting_db, yearID)
years_df <- group_by(batting_df, yearID)

system.time(do(years_db, failwith(NULL, lm), formula = R ~ AB))
system.time(do(years_df, failwith(NULL, lm), formula = R ~ AB))

Note that if you are fitting lots of linear models, it's a good idea to use biglm because it creates model objects that are considerably smaller:

library(biglm)
mod1 <- do(years_df, lm, formula = R ~ AB)
mod2 <- do(years_df, biglm, formula = R ~ AB)
print(object.size(mod1), unit = "MB")
print(object.size(mod2), unit = "MB")

Multiple table verbs

As well as verbs that work on a single tbl, there are also a set of useful verbs that work with two tbls at a time: joins and set operations.

dplyr implements the four most useful joins from SQL:

  • inner_join(x, y): matching x + y
  • left_join(x, y): all x + matching y
  • semi_join(x, y): all x with match in y
  • anti_join(x, y): all x without match in y

And provides methods for:

  • intersect(x, y): all rows in both x and y
  • union(x, y): rows in either x or y
  • setdiff(x, y): rows in x, but not y

Plyr compatibility

You'll need to be a little careful if you load both plyr and dplyr at the same time. I'd recommend loading plyr first, then dplyr, so that the faster dplyr functions come first in the search path. By and large, any function provided by both dplyr and plyr works in a similar way, although dplyr functions tend to be faster and more general.

Related approaches

Copy Link

Version

Install

install.packages('dplyr')

Monthly Downloads

1,307,121

Version

0.4.0.9000

License

MIT + file LICENSE

Issues

Pull Requests

Stars

Forks

Maintainer

Hadley Wickham

Last Published

January 30th, 2023

Functions in dplyr (0.4.0.9000)

backend_sql

SQL generation.
backend_src

Source generics.
add_rownames

Convert row names to an explicit variable.
all.equal.tbl_df

Provide a useful implementation of all.equal for data.frames.
bench_compare

Evaluate, compare, benchmark operations of a set of srcs.
between

Do values in a numeric vector fall in specified range?
arrange

Arrange rows by variables.
as.tbl_cube

Coerce an existing data structure into a
as_data_frame

Coerce a list to a data frame.
backend_db

Database generics.
chain

Chain together multiple operations.
compute

Compute a lazy tbl.
rbind_all

Efficiently bind multiple data frames by row and column.
build_sql

Build a SQL string.
do

Do arbitrary operations on a tbl.
group_indices

Group id.
group_size

Calculate group sizes.
nasa

NASA spatio-temporal data
nth

Extract the first, last or nth value from a vector.
rowwise

Group input by rows
same_src

Figure out if two sources are the same (or two tbl have the same source)
sql_quote

Helper function for quoting sql elements.
base_scalar

Create an sql translator
cumall

Cumulativate versions of any, all, and mean
data_frame

Build a data frame.
explain

Explain details of an tbl.
tbl

Create a table from a data source
tbl_cube

A data cube tbl.
temp_srcs

Connect to temporary data sources.
top_n

Select top n rows (by value).
print.tbl_df

Tools for describing matrices
dplyr

dplyr: a grammar of data manipulation
groups

Get/set the grouping variables for tbl.
id

Compute a unique numeric id for each unique row in a data frame.
dplyr-cluster

Cluster management.
join

Join two tbls together.
join.tbl_df

Join data frame tbls.
make_tbl

Create a "tbl" object
copy_to

Copy a local data frame to a remote src.
copy_to.src_sql

Copy a local data frame to a sqlite src.
glimpse

Get a glimpse of your data.
group_by

Group a tbl by one or more variables.
failwith

Fail with specified value.
sample

Sample n rows from a table.
select

Select/rename variables by name.
src

Create a "src" object
src_local

A local source.
tbl_sql

Create an SQL tbl (abstract)
tbl_vars

List variables provided by a tbl.
mutate

Add new variables.
select_vars

Select variables.
setops

Set operations.
desc

Descending order.
distinct

Select distinct/unique rows.
filter

Return rows with matching conditions.
funs

Create a list of functions calls.
join.tbl_dt

Join data table tbls.
join.tbl_sql

Join sql tbls.
lead-lag

Lead and lag.
location

Print the location in memory of a data frame
summarise_each

Summarise and mutate multiple columns.
grouped_df

A grouped data frame.
grouped_dt

A grouped data table.
knit_print.trunc_mat

knit_print method for trunc mat
lahman

Cache and retrieve an
nycflights13

Database versions of the nycflights13 data
order_by

A helper function for ordering window function output.
src_sql

Create a "sql src" object
src_sqlite

Connect to a sqlite database.
n

The number of observations in the current group.
n_distinct

Efficiently count the number of unique values in a vector.
query

Create a mutable query object.
ranking

Windowed rank functions.
partial_eval

Partially evaluate an expression.
progress_estimated

Progress bar with estimated time.
src_mysql

Connect to mysql/mariadb.
src_postgres

Connect to postgresql.
tbl_df

Create a data frame tbl.
tbl_dt

Create a data table tbl.
translate_sql

Translate an expression to sql.
type_sum

Provide a succint summary of a type
tally

Counts/tally observations by group.
slice

Select rows by position.
sql

SQL escaping.
src_tbls

List all tbls provided by a source.
summarise

Summarise multiple values to a single value.
with_order

Run a function with one order, translating result back to original order