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("hflights", "Lahman")).

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 encounter a clear bug, please file a minimal reproducible example on github. For questions and other discussion, please use the manipulatr mailing list.

tbls

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(hflights) # for data
head(hflights)

# Coerce to data table
hflights_dt <- tbl_dt(hflights)

# Caches data in local SQLite db
hflights_db1 <- tbl(hflights_sqlite(), "hflights")

# Caches data in local postgres db
hflights_db2 <- tbl(hflights_postgres(), "hflights")

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

carriers_df  <- group_by(hflights, UniqueCarrier)
carriers_dt  <- group_by(hflights_dt, UniqueCarrier)
carriers_db1 <- group_by(hflights_db1, UniqueCarrier)
carriers_db2 <- group_by(hflights_db2, UniqueCarrier)

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

See ?manip for more details.

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(carriers_dt, delay = mean(ArrDelay, na.rm = TRUE)))
#   user  system elapsed
#  0.007   0.000   0.008
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 and data table methods are order of magnitude faster than plyr. 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)
batting_dt <- tbl_dt(batting_df)

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

system.time(do(years_db, failwith(NULL, lm), formula = R ~ AB))
system.time(do(years_df, failwith(NULL, lm), formula = R ~ AB))
system.time(do(years_dt, 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")

Binary 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. 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

Currently join variables must be the same in both the left-hand and right-hand sides.

Other operations

All tbls also provide head() and print() methods. The default print method gives information about the data source and shows the first 10 rows and all the columns that will fit on one screen.

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,355,051

Version

0.3.0.1

License

MIT + file LICENSE

Issues

Pull Requests

Stars

Forks

Maintainer

Hadley Wickham

Last Published

January 30th, 2023

Functions in dplyr (0.3.0.1)

explain

Explain details of an tbl.
nasa

NASA spatio-temporal data
arrange

Arrange rows by variables.
backend_db

Database generics.
distinct

Select distinct/unique rows.
join.tbl_dt

Join data table tbls.
select_vars

Select variables.
translate_sql

Translate an expression to sql.
src_tbls

List all tbls provided by a source.
id

Compute a unique numeric id for each unique row in a data frame.
top_n

Select top n rows (by value).
nycflights13

Database versions of the nycflights13 data
base_scalar

Create an sql translator
tbl

Create a table from a data source
sql

SQL escaping.
grouped_df

A grouped data frame.
src_mysql

Connect to mysql/mariadb.
print.tbl_df

Tools for describing matrices
chain

Chain together multiple operations.
nth

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

Build a data frame.
backend_src

Source generics.
cumall

Cumulativate versions of any, all, and mean
compute

Compute a lazy tbl.
tbl_df

Create a data frame tbl.
mutate

Add new variables.
join.tbl_sql

Join sql tbls.
dplyr

The dplyr package.
grouped_dt

A grouped data table.
make_tbl

Create a "tbl" object
glimpse

Get a glimpse of your data.
funs

Create a list of functions calls.
type_sum

Provide a succint summary of a type
group_size

Calculate group sizes.
summarise

Summarise multiple values to a single value.
sample

Sample n rows from a table.
summarise_each

Summarise and mutate multiple columns.
partial_eval

Partially evaluate an expression.
desc

Descending order.
all.equal.tbl_df

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

The number of observations in the current group.
query

Create a mutable query object.
group_by

Group a tbl by one or more variables.
bench_compare

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

Create a "src" object
join.tbl_df

Join data frame tbls.
copy_to.src_sql

Copy a local data fram to a sqlite src.
src_sqlite

Connect to a sqlite database.
groups

Get/set the grouping variables for tbl.
src_sql

Create a "sql src" object
rowwise

Group input by rows
tbl_cube

A data cube tbl.
select

Select/rename variables by name.
tbl_sql

Create an SQL tbl (abstract)
slice

Select rows by position.
do

Do arbitrary operations on a tbl.
build_sql

Build a SQL string.
progress_estimated

Progress bar with estimated time.
sql_quote

Helper function for quoting sql elements.
setops

Set operations.
same_src

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

A helper function for ordering window function output.
between

Do values in a numeric vector fail in specified range?
src_postgres

Connect to postgresql.
tbl_vars

List variables provided by a tbl.
lead-lag

Lead and lag.
backend_sql

SQL generation.
tbl_dt

Create a data table tbl.
n_distinct

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

A local source.
copy_to

Copy a local data frame to a remote src.
temp_srcs

Connect to temporary data sources.
dplyr-cluster

Cluster management.
rbind_list

Efficiently rbind multiple data frames.
location

Print the location in memory of a data frame
join

Join two tbls together.
failwith

Fail with specified value.
ranking

Windowed rank functions.
filter

Return rows with matching conditions.
as.tbl_cube

Coerce an existing data structure into a
with_order

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

Cache and retrieve an
tally

Counts/tally observations by group.