dplyr v0.1


Monthly downloads



by Hadley Wickham

dplyr: a grammar of data manipulation

A fast, consistent tool for working with data frame like objects, both in memory and out of memory.



Build Status

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

  • the latest development version from github with


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.


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

You can create them as follows:

# Built in data frame

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

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


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(), "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:

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 are 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

Currently, it's not a good idea to have both dplyr and plyr loaded. This is just a short-term problem: in the long-term, I'll move the matching functions from plyr into dplyr, and add a dplyr dependency to plyr.

Functions in dplyr

Name Description
group_size Calculate the size of each group
grouped_df A grouped data frame.
bench_compare Evaluate, compare, benchmark operations of a set of srcs.
build_sql Build a SQL string.
failwith Fail with specified value.
hflights_df Database versions of the hflights data
id Compute a unique numeric id for each unique row in a data frame.
src_bigquery A bigquery data source.
src_local A local source.
all.equal.data.frame Provide a useful implementation of all.equal for data.frames.
group_by Group a tbl by one or more variables.
join.tbl_dt Join data table tbls.
join.tbl_sql Join sql tbls.
manip_df Data manipulation for data frames.
cumall Cumulativate versions of any, all, and mean
desc Descending order.
as.tbl_cube Coerce an existing data structure into a
do Apply a function to a tbl
dplyr-formatting Tools for describing matrices
copy_to Copy a local data frame to a remote src.
copy_to.src_sql Copy a local data fram to a sqlite src.
dplyr The dplyr package.
explain_sql Show sql and query plans.
manip_dt Data manipulation for data tables.
setops Set operations.
sql SQL escaping.
src_mysql Connect to mysql/mariadb.
make_tbl Create a "tbl" object
manip Data manipulation functions.
n_distinct Efficiently count the number of unique values in a vector.
src_postgres Connect to postgresql.
translate_sql Translate an expression to sql.
type_sum Provide a succint summary of a type
nasa NASA spatio-temporal data
ranking Windowed rank functions.
rbind_all Efficiently rbind multiple data frames.
lahman Cache and retrieve an
lead-lag Lead and lag.
manip_grouped_dt Data manipulation for grouped data tables.
n The number of observations in the current group.
chain Chain together multiple operations.
compute Compute a lazy tbl.
src_tbls List all tbls provided by a source.
tally Tally observations by group.
var_eval Evaluate variable names in the context of a tbl.
with_order Run a function with one order, translating result back to original order
tbl_df Create a data frame tble.
tbl_dt Create a data table tbl.
tbl_sql Create an SQL tbl (abstract)
tbl_vars List variables provided by a tbl.
grouped_dt A grouped data table.
groups Get/set the grouping variables for tbl.
join Join two tbls together.
same_src Figure out if two sources are the same (or two tbl have the same source)
setops-data.frame Set operations for data frames.
join.tbl_df Join data table tbls.
partial_eval Partially evaluate an expression.
query Create a mutable query object.
base_scalar Create an sql translator
nth Extract the first, last or nth value from a vector.
order_by A helper function for ordering window function output.
tbl Create a table from a data source
tbl_cube A data cube tbl.
src_sql Create a "sql src" object
src_sqlite Connect to a sqlite database.
temp_srcs Connect to temporary data sources.
top_n Select top n rows (by value).
src Create a "src" object
No Results!

Last month downloads


Type Package
VignetteBuilder knitr
LazyData yes
LinkingTo Rcpp (>= 0.10.6), BH (>= 1.51.0-2)
License MIT + file LICENSE
Collate 'RcppExports.R' 'all-equal.r' 'bench-compare.r' 'chain.r' 'compute-collect.r' 'copy-to.r' 'data-hflights.r' 'data-lahman.r' 'data-nasa.r' 'data-temp.r' 'data.r' 'dbi-s3.r' 'desc.r' 'do.r' 'dplyr.r' 'explain.r' 'failwith.r' 'group-by.r' 'group-size.r' 'grouped-df.r' 'grouped-dt.r' 'id.r' 'join-df.r' 'join-dt.r' 'join-sql.r' 'join.r' 'lead-lag.R' 'manip-cube.r' 'manip-df.r' 'manip-dt.r' 'manip-grouped-dt.r' 'manip-sql.r' 'manip.r' 'nth-value.R' 'order-by.R' 'over.R' 'partial-eval.r' 'query.r' 'query-bq.r' 'rank.R' 'rbind.r' 'sets.r' 'sql-escape.r' 'sql-star.r' 'src-bigquery.r' 'src-local.r' 'src-mysql.r' 'src-postgres.r' 'src-sql.r' 'src-sqlite.r' 'src.r' 'tally.R' 'tbl-cube.r' 'tbl-data-frame.R' 'tbl-df.r' 'tbl-dt.r' 'tbl-sql.r' 'tbl.r' 'top-n.R' 'translate-sql-helpers.r' 'translate-sql-base.r' 'translate-sql-window.r' 'translate-sql.r' 'type-sum.r' 'utils-format.r' 'utils.r' 'view.r' 'zzz.r'

Include our badge in your README