dplyr
is the next iteration of plyr with the following goals:
- Improved performance
- A more consistent interface focussed on tabular data (e.g. ddply, ldply and dlply)
- Support for alternative data stores (data.table, sql, hive, ...)
One of the key ideas of dplyr
is that it shouldn't matter how your data is stored. Regardless of whether your data in an SQL database, a data frame or a data table, you should interact with it in the exactly the same way. (That said, dplyr
works with tidy data so it can assume varaibles are always described in a consistent way.)
dplyr
is not currently available on CRAN, but you can install it from github with:
devtools::install_github("assertthat")
devtools::install_github("dplyr")
The key object in dplyr is a tbl, a representation of a tabular data structure.
Currently dplyr
supports data frames, data tables and SQLite databases. You can create them as follows:
library(dplyr)
# Built in data frame
head(hflights)
# Coerce to data table
hflights_dt <- tbl_dt(hflights)
# Create SQLite database and copy into
db <- src_sqlite(tempfile(), create = TRUE)
hflights_db <- copy_to(db, hflights, indexes = list("UniqueCarrier"))
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_db <- group_by(hflights_db, UniqueCarrier)
# This database has an index on the player id, which is a recommended
# minimum whenever you're doing group by queries
dplyr
implements the following verbs useful for data manipulation:
select()
: focus on a subset of variablesfilter()
: focus on a subset of rowsmutate()
: add new columnssummarise()
: reduce each group to a smaller number of summary statisticsarrange()
: 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, 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(carriers_db, delay = mean(ArrDelay)))
# user system elapsed
# 0.039 0.000 0.040
# Substantially faster on "warm" database
# user system elapsed
# 0.005 0.000 0.005
All methods are substantially faster than plyr:
library(plyr)
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:
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")
You can also join data sources: this is currently only supported for SQL, but data frame and data table wrappers will be added in the near future.
- inner join
- left join
- semi join
- anti join
Currently joins variables must be the same in both the left-hand and right-hand sides.
Sqlite tbls also support the compound operator, which allows you to union all tbls together.
All tbls also provide head()
, tail()
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.
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.