Grouping Set aggregation for data tables
Calculate aggregates at various levels of groupings producing multiple (sub-)totals. Reflects SQLs GROUPING SETS operations.
rollup(x, ...) ## S3 method for class 'data.table' rollup(x, j, by, .SDcols, id = FALSE, ...) cube(x, ...) ## S3 method for class 'data.table' cube(x, j, by, .SDcols, id = FALSE, ...) groupingsets(x, ...) ## S3 method for class 'data.table' groupingsets(x, j, by, sets, .SDcols, id = FALSE, jj, ...)
x |
|
... |
argument passed to custom user methods. Ignored for |
j |
expression passed to data.table |
by |
character column names by which we are grouping. |
sets |
list of character vector reflecting grouping sets, used in |
.SDcols |
columns to be used in |
id |
logical default |
jj |
quoted version of |
All three functions rollup, cube, groupingsets are generic methods, data.table methods are provided.
A data.table with various aggregates.
n = 24L
set.seed(25)
DT <- data.table(
color = sample(c("green","yellow","red"), n, TRUE),
year = as.Date(sample(paste0(2011:2015,"-01-01"), n, TRUE)),
status = as.factor(sample(c("removed","active","inactive","archived"), n, TRUE)),
amount = sample(1:5, n, TRUE),
value = sample(c(3, 3.5, 2.5, 2), n, TRUE)
)
# rollup
rollup(DT, j = sum(value), by = c("color","year","status")) # default id=FALSE
rollup(DT, j = sum(value), by = c("color","year","status"), id=TRUE)
rollup(DT, j = lapply(.SD, sum), by = c("color","year","status"), id=TRUE, .SDcols="value")
rollup(DT, j = c(list(count=.N), lapply(.SD, sum)), by = c("color","year","status"), id=TRUE)
# cube
cube(DT, j = sum(value), by = c("color","year","status"), id=TRUE)
cube(DT, j = lapply(.SD, sum), by = c("color","year","status"), id=TRUE, .SDcols="value")
cube(DT, j = c(list(count=.N), lapply(.SD, sum)), by = c("color","year","status"), id=TRUE)
# groupingsets
groupingsets(DT, j = c(list(count=.N), lapply(.SD, sum)), by = c("color","year","status"),
sets = list("color", c("year","status"), character()), id=TRUE)Please choose more modern alternatives, such as Google Chrome or Mozilla Firefox.