1\name{groupingsets} 2\alias{rollup} 3\alias{cube} 4\alias{groupingsets} 5\alias{rollup.data.table} 6\alias{cube.data.table} 7\alias{groupingsets.data.table} 8\title{ Grouping Set aggregation for data tables } 9\description{ 10 Calculate aggregates at various levels of groupings producing multiple (sub-)totals. Reflects SQLs \emph{GROUPING SETS} operations. 11} 12\usage{ 13rollup(x, \dots) 14\method{rollup}{data.table}(x, j, by, .SDcols, id = FALSE, \dots) 15cube(x, \dots) 16\method{cube}{data.table}(x, j, by, .SDcols, id = FALSE, \dots) 17groupingsets(x, \dots) 18\method{groupingsets}{data.table}(x, j, by, sets, .SDcols, id = FALSE, jj, \dots) 19} 20\arguments{ 21 \item{x}{\code{data.table}.} 22 \item{\dots}{argument passed to custom user methods. Ignored for \code{data.table} methods.} 23 \item{j}{expression passed to data.table \code{j}.} 24 \item{by}{character column names by which we are grouping.} 25 \item{sets}{list of character vector reflecting grouping sets, used in \code{groupingsets} for flexibility.} 26 \item{.SDcols}{columns to be used in \code{j} expression in \code{.SD} object.} 27 \item{id}{logical default \code{FALSE}. If \code{TRUE} it will add leading column with bit mask of grouping sets.} 28 \item{jj}{quoted version of \code{j} argument, for convenience. When provided function will ignore \code{j} argument.} 29} 30\details{ 31 All three functions \code{rollup, cube, groupingsets} are generic methods, \code{data.table} methods are provided. 32} 33\value{ 34 A data.table with various aggregates. 35} 36\seealso{ \code{\link{data.table}}, \code{\link{rbindlist}} 37} 38\references{ 39\url{https://www.postgresql.org/docs/9.5/static/queries-table-expressions.html#QUERIES-GROUPING-SETS} 40\url{https://www.postgresql.org/docs/9.5/static/functions-aggregate.html#FUNCTIONS-GROUPING-TABLE} 41} 42\examples{ 43n = 24L 44set.seed(25) 45DT <- data.table( 46 color = sample(c("green","yellow","red"), n, TRUE), 47 year = as.Date(sample(paste0(2011:2015,"-01-01"), n, TRUE)), 48 status = as.factor(sample(c("removed","active","inactive","archived"), n, TRUE)), 49 amount = sample(1:5, n, TRUE), 50 value = sample(c(3, 3.5, 2.5, 2), n, TRUE) 51) 52 53# rollup 54rollup(DT, j = sum(value), by = c("color","year","status")) # default id=FALSE 55rollup(DT, j = sum(value), by = c("color","year","status"), id=TRUE) 56rollup(DT, j = lapply(.SD, sum), by = c("color","year","status"), id=TRUE, .SDcols="value") 57rollup(DT, j = c(list(count=.N), lapply(.SD, sum)), by = c("color","year","status"), id=TRUE) 58 59# cube 60cube(DT, j = sum(value), by = c("color","year","status"), id=TRUE) 61cube(DT, j = lapply(.SD, sum), by = c("color","year","status"), id=TRUE, .SDcols="value") 62cube(DT, j = c(list(count=.N), lapply(.SD, sum)), by = c("color","year","status"), id=TRUE) 63 64# groupingsets 65groupingsets(DT, j = c(list(count=.N), lapply(.SD, sum)), by = c("color","year","status"), 66 sets = list("color", c("year","status"), character()), id=TRUE) 67} 68\keyword{ data } 69