dplyr 0.5.0

by Hadley Wickham
Other versions:

Monthly downloads




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 CRAN\_Status\_Badge Coverage 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

    if (packageVersion("devtools") < 1.6) {

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 needed to 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
#> Source: local data frame [336,776 x 16]
#>     year month   day dep_time dep_delay arr_time arr_delay carrier tailnum
#>    (int) (int) (int)    (int)     (dbl)    (int)     (dbl)   (chr)   (chr)
#> 1   2013     1     1      517         2      830        11      UA  N14228
#> 2   2013     1     1      533         4      850        20      UA  N24211
#> 3   2013     1     1      542         2      923        33      AA  N619AA
#> 4   2013     1     1      544        -1     1004       -18      B6  N804JB
#> 5   2013     1     1      554        -6      812       -25      DL  N668DN
#> 6   2013     1     1      554        -4      740        12      UA  N39463
#> 7   2013     1     1      555        -5      913        19      B6  N516JB
#> 8   2013     1     1      557        -3      709       -14      EV  N829AS
#> 9   2013     1     1      557        -3      838        -8      B6  N593JB
#> 10  2013     1     1      558        -2      753         8      AA  N3ALAA
#> ..   ...   ...   ...      ...       ...      ...       ...     ...     ...
#> Variables not shown: flight (int), origin (chr), dest (chr), air_time
#>   (dbl), distance (dbl), hour (dbl), minute (dbl).

# 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  <- flights %>% group_by(carrier)
carriers_db1 <- flights_db1 %>% group_by(carrier)
carriers_db2 <- flights_db2 %>% group_by(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(carriers_df %>% summarise(delay = mean(arr_delay)))
#>    user  system elapsed 
#>   0.040   0.001   0.043
system.time(carriers_db1 %>% summarise(delay = mean(arr_delay)) %>% collect())
#>    user  system elapsed 
#>   0.348   0.302   1.280
system.time(carriers_db2 %>% summarise(delay = mean(arr_delay)) %>% collect())
#>    user  system elapsed 
#>   0.015   0.000   0.142

Data frame methods are much much faster than the plyr equivalent. The database methods are slower, but can work with data that don't fit in memory.

system.time(plyr::ddply(flights, "carrier", plyr::summarise,
  delay = mean(arr_delay, na.rm = TRUE)))
#>    user  system elapsed 
#>   0.104   0.029   0.134


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:

by_year <- lahman_df() %>% 
  tbl("Batting") %>%
by_year %>% 
  do(mod = lm(R ~ AB, data = .))
#> Source: local data frame [144 x 2]
#> Groups: <by row>
#>    yearID     mod
#>     (int)  (list)
#> 1    1871 <S3:lm>
#> 2    1872 <S3:lm>
#> 3    1873 <S3:lm>
#> 4    1874 <S3:lm>
#> 5    1875 <S3:lm>
#> 6    1876 <S3:lm>
#> 7    1877 <S3:lm>
#> 8    1878 <S3:lm>
#> 9    1879 <S3:lm>
#> 10   1880 <S3:lm>
#> ..    ...     ...

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:

by_year %>% 
  do(mod = lm(R ~ AB, data = .)) %>%
  object.size() %>%
  print(unit = "MB")
#> 22.7 Mb

by_year %>% 
  do(mod = biglm::biglm(R ~ AB, data = .)) %>%
  object.size() %>%
  print(unit = "MB")
#> 0.8 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.

Functions in dplyr

Name Description
backend_sql SQL generation.
backend_src Source generics.
as.tbl_cube Coerce an existing data structure into a tbl_cube
arrange Arrange rows by variables.
auto_copy Copy tables to same source, if necessary.
all_equal Flexible equality comparison for data frames.
bench_compare Evaluate, compare, benchmark operations of a set of srcs.
as.table.tbl_cube Coerce a tbl_cube to other data structures
add_rownames Convert row names to an explicit variable.
backend_db Database generics.
compute Compute a lazy tbl.
copy_to Copy a local data frame to a remote src.
coalesce Find first non-missing element
case_when A general vectorised if.
build_sql Build a SQL string.
bind Efficiently bind multiple data frames by row and column.
common_by Extract out common by variables
between Do values in a numeric vector fall in specified range?
cumall Cumulativate versions of any, all, and mean
copy_to.src_sql Copy a local data frame to a sqlite src.
dplyr dplyr: a grammar of data manipulation
funs Create a list of functions calls.
dim_desc Describing dimensions
explain Explain details of a tbl.
desc Descending order.
do Do arbitrary operations on a tbl.
distinct Select distinct/unique rows.
failwith Fail with specified value.
group_by_prepare Prepare for grouping.
filter Return rows with matching conditions.
id Compute a unique numeric id for each unique row in a data frame.
groups Get/set the grouping variables for tbl.
group_indices Group id.
if_else Vectorised if.
join Join two tbls together.
join.tbl_df Join data frame tbls.
join.tbl_sql Join sql tbls.
group_size Calculate group sizes.
grouped_df Convert to a data frame
group_by Group a tbl by one or more variables.
lead-lag Lead and lag.
make_tbl Create a "tbl" object
mutate Add new variables.
n The number of observations in the current group.
n_distinct Efficiently count the number of unique values in a set of vector
lazy_ops Lazy operations
lahman Cache and retrieve an src_sqlite of the Lahman baseball database.
na_if Convert values to NA.
location Print the location in memory of a data frame
named_commas Provides comma-separated string out ot the parameters
nth Extract the first, last or nth value from a vector.
nycflights13 Database versions of the nycflights13 data
ranking Windowed rank functions.
partial_eval Partially evaluate an expression.
recode Recode values
progress_estimated Progress bar with estimated time.
query Create a mutable query object.
nasa NASA spatio-temporal data
order_by A helper function for ordering window function output.
near Compare two numeric vectors.
select_helpers Select helpers
slice Select rows by position.
rowwise Group input by rows
same_src Figure out if two sources are the same (or two tbl have the same source)
select Select/rename variables by name.
select_vars Select variables.
reexports Objects exported from other packages
sample Sample n rows from a table.
setops Set operations.
select_if Select columns using a predicate
sql SQL escaping.
sql_quote Helper function for quoting sql elements.
src_sqlite Connect to a sqlite database.
src_local A local source.
src_postgres Connect to postgresql.
sql_variant Create an sql translator
src_memdb Per-session in-memory SQLite databases.
sql_build Build and render SQL from a sequence of lazy operations
src_mysql Connect to mysql/mariadb.
src_sql Create a "sql src" object
tbl_df Create a data frame tbl.
src Create a "src" object
summarise_each Summarise and mutate multiple columns.
src-test A set of DBI methods to ease unit testing dplyr with DBI
tally Counts/tally observations by group.
summarise_all Summarise and mutate multiple columns.
src_tbls List all tbls provided by a source.
tbl_cube A data cube tbl.
summarise Summarise multiple values to a single value.
tbl_sql Create an SQL tbl (abstract)
tbl_vars List variables provided by a tbl.
with_order Run a function with one order, translating result back to original order
translate_sql Translate an expression to sql.
tbl Create a table from a data source
testing Infrastructure for testing dplyr
vars Select columns
top_n Select top (or bottom) n rows (by value).
No Results!

Last month downloads


Type Package
URL https://github.com/hadley/dplyr
BugReports https://github.com/hadley/dplyr/issues
VignetteBuilder knitr
LinkingTo Rcpp (>= 0.12.0), BH (>= 1.58.0-1)
LazyData yes
License MIT + file LICENSE
Collate 'RcppExports.R' 'all-equal.r' 'bench-compare.r' 'bind.r' 'case_when.R' 'coalesce.R' 'colwise.R' 'compute-collect.r' 'copy-to.r' 'data-lahman.r' 'data-nasa.r' 'data-nycflights13.r' 'data-temp.r' 'data.r' 'dataframe.R' 'dbi-s3.r' 'desc.r' 'distinct.R' 'do.r' 'dplyr.r' 'explain.r' 'failwith.r' 'funs.R' 'group-by.r' 'group-indices.R' 'group-size.r' 'grouped-df.r' 'id.r' 'if_else.R' 'inline.r' 'join.r' 'lazy-ops.R' 'lead-lag.R' 'location.R' 'manip.r' 'na_if.R' 'near.R' 'nth-value.R' 'order-by.R' 'over.R' 'partial-eval.r' 'progress.R' 'query.r' 'rank.R' 'recode.R' 'rowwise.r' 'sample.R' 'select-utils.R' 'select-vars.R' 'sets.r' 'sql-build.R' 'sql-escape.r' 'sql-generic.R' 'sql-query.R' 'sql-render.R' 'sql-star.r' 'src-local.r' 'src-mysql.r' 'src-postgres.r' 'src-sql.r' 'src-sqlite.r' 'src-test.r' 'src.r' 'tally.R' 'tbl-cube.r' 'tbl-df.r' 'tbl-lazy.R' 'tbl-sql.r' 'tbl.r' 'tibble-reexport.r' 'top-n.R' 'translate-sql-helpers.r' 'translate-sql-base.r' 'translate-sql-window.r' 'translate-sql.r' 'utils-format.r' 'utils-replace-with.R' 'utils.r' 'view.r' 'zzz.r'
RoxygenNote 5.0.1
NeedsCompilation yes
Packaged 2016-06-23 21:54:41 UTC; hadley
Repository CRAN
Date/Publication 2016-06-24 15:37:11

Include our badge in your README