Skip to contents

Calculate aggregates at various levels of groupings producing multiple (sub-)totals. Reflects SQLs GROUPING SETS operations.

Usage

rollup(x, ...)
# S3 method for data.table
rollup(x, j, by, .SDcols, id = FALSE, label = NULL, ...)
cube(x, ...)
# S3 method for data.table
cube(x, j, by, .SDcols, id = FALSE, label = NULL, ...)
groupingsets(x, ...)
# S3 method for data.table
groupingsets(x, j, by, sets, .SDcols, id = FALSE, jj, label = NULL, ...)

Arguments

x

data.table.

...

argument passed to custom user methods. Ignored for data.table methods.

j

expression passed to data.table j.

by

character column names by which we are grouping.

sets

list of character vector reflecting grouping sets, used in groupingsets for flexibility.

.SDcols

columns to be used in j expression in .SD object.

id

logical default FALSE. If TRUE it will add leading column with bit mask of grouping sets.

jj

quoted version of j argument, for convenience. When provided function will ignore j argument.

label

label(s) to be used in the 'total' rows in the grouping variable columns of the output, that is, in rows where the grouping variable has been aggregated. Can be a named list of scalars, or a scalar, or NULL. Defaults to NULL, which results in the grouping variables having NA in their 'total' rows. See Details.

Details

All three functions rollup, cube, groupingsets are generic methods, data.table methods are provided.

The label argument can be a named list of scalars, or a scalar, or NULL. When label is a list, each element name must be (1) a variable name in by, or (2) the first element of the class in the data.table x of a variable in by, or (3) one of 'character', 'integer', 'numeric', 'factor', 'Date', 'IDate'. The order of the list elements is not important. A label specified by variable name will apply only to that variable, while a label specified by first element of a class will apply to all variables in by for which the first element of the class of the variable in x matches the label element name, except for variables that have a label specified by variable name (that is, specification by variable name takes precedence over specification by class). For label elements with name in by, the class of the label value must be the same as the class of the variable in x. For label elements with name not in by, the first element of the class of the label value must be the same as the label element name. For example, label = list(integer = 999, IDate = as.Date("3000-01-01")) would produce an error because class(999)[1] is not "integer" and class(as.Date("3000-01-01"))[1] is not "IDate". A corrected specification would be label = list(integer = 999L, IDate = as.IDate("3000-01-01")).

The label = <scalar> option provides a shorter alternative in the case where only one class of grouping variable requires a label. For example, label = list(character = "Total") can be shortened to label = "Total". When this option is used, the label will be applied to all variables in by for which the first element of the class of the variable in x matches the first element of the class of the scalar.

Value

A data.table with various aggregates.

See also

Examples

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
by_vars = c("color", "year", "status")
rollup(DT, j=sum(value), by=by_vars) # default id=FALSE
#>      color       year   status    V1
#>     <char>     <Date>   <fctr> <num>
#>  1:    red 2015-01-01   active   3.5
#>  2:  green 2015-01-01 inactive   5.5
#>  3:  green 2014-01-01 archived   3.5
#>  4:  green 2015-01-01 archived   2.0
#>  5: yellow 2014-01-01   active   4.5
#>  6:    red 2013-01-01 inactive   2.0
#>  7:  green 2011-01-01   active   6.0
#>  8:    red 2014-01-01 inactive   2.5
#>  9:  green 2011-01-01 archived   2.5
#> 10: yellow 2015-01-01   active   2.0
#> 11:    red 2012-01-01 archived   2.0
#> 12:    red 2011-01-01  removed   3.5
#> 13:  green 2014-01-01 inactive   8.0
#> 14:  green 2011-01-01  removed   2.0
#> 15: yellow 2012-01-01 archived   2.5
#> 16:    red 2013-01-01  removed   3.5
#> 17:  green 2013-01-01   active   3.0
#> 18:  green 2014-01-01  removed   2.5
#> 19:    red 2011-01-01 archived   3.0
#> 20:    red 2015-01-01     <NA>   3.5
#> 21:  green 2015-01-01     <NA>   7.5
#> 22:  green 2014-01-01     <NA>  14.0
#> 23: yellow 2014-01-01     <NA>   4.5
#> 24:    red 2013-01-01     <NA>   5.5
#> 25:  green 2011-01-01     <NA>  10.5
#> 26:    red 2014-01-01     <NA>   2.5
#> 27: yellow 2015-01-01     <NA>   2.0
#> 28:    red 2012-01-01     <NA>   2.0
#> 29:    red 2011-01-01     <NA>   6.5
#> 30: yellow 2012-01-01     <NA>   2.5
#> 31:  green 2013-01-01     <NA>   3.0
#> 32:    red       <NA>     <NA>  20.0
#> 33:  green       <NA>     <NA>  35.0
#> 34: yellow       <NA>     <NA>   9.0
#> 35:   <NA>       <NA>     <NA>  64.0
#>      color       year   status    V1
rollup(DT, j=sum(value), by=by_vars, id=TRUE)
#>     grouping  color       year   status    V1
#>        <int> <char>     <Date>   <fctr> <num>
#>  1:        0    red 2015-01-01   active   3.5
#>  2:        0  green 2015-01-01 inactive   5.5
#>  3:        0  green 2014-01-01 archived   3.5
#>  4:        0  green 2015-01-01 archived   2.0
#>  5:        0 yellow 2014-01-01   active   4.5
#>  6:        0    red 2013-01-01 inactive   2.0
#>  7:        0  green 2011-01-01   active   6.0
#>  8:        0    red 2014-01-01 inactive   2.5
#>  9:        0  green 2011-01-01 archived   2.5
#> 10:        0 yellow 2015-01-01   active   2.0
#> 11:        0    red 2012-01-01 archived   2.0
#> 12:        0    red 2011-01-01  removed   3.5
#> 13:        0  green 2014-01-01 inactive   8.0
#> 14:        0  green 2011-01-01  removed   2.0
#> 15:        0 yellow 2012-01-01 archived   2.5
#> 16:        0    red 2013-01-01  removed   3.5
#> 17:        0  green 2013-01-01   active   3.0
#> 18:        0  green 2014-01-01  removed   2.5
#> 19:        0    red 2011-01-01 archived   3.0
#> 20:        1    red 2015-01-01     <NA>   3.5
#> 21:        1  green 2015-01-01     <NA>   7.5
#> 22:        1  green 2014-01-01     <NA>  14.0
#> 23:        1 yellow 2014-01-01     <NA>   4.5
#> 24:        1    red 2013-01-01     <NA>   5.5
#> 25:        1  green 2011-01-01     <NA>  10.5
#> 26:        1    red 2014-01-01     <NA>   2.5
#> 27:        1 yellow 2015-01-01     <NA>   2.0
#> 28:        1    red 2012-01-01     <NA>   2.0
#> 29:        1    red 2011-01-01     <NA>   6.5
#> 30:        1 yellow 2012-01-01     <NA>   2.5
#> 31:        1  green 2013-01-01     <NA>   3.0
#> 32:        3    red       <NA>     <NA>  20.0
#> 33:        3  green       <NA>     <NA>  35.0
#> 34:        3 yellow       <NA>     <NA>   9.0
#> 35:        7   <NA>       <NA>     <NA>  64.0
#>     grouping  color       year   status    V1
rollup(DT, j=lapply(.SD, sum), by=by_vars, id=TRUE, .SDcols="value")
#>     grouping  color       year   status value
#>        <int> <char>     <Date>   <fctr> <num>
#>  1:        0    red 2015-01-01   active   3.5
#>  2:        0  green 2015-01-01 inactive   5.5
#>  3:        0  green 2014-01-01 archived   3.5
#>  4:        0  green 2015-01-01 archived   2.0
#>  5:        0 yellow 2014-01-01   active   4.5
#>  6:        0    red 2013-01-01 inactive   2.0
#>  7:        0  green 2011-01-01   active   6.0
#>  8:        0    red 2014-01-01 inactive   2.5
#>  9:        0  green 2011-01-01 archived   2.5
#> 10:        0 yellow 2015-01-01   active   2.0
#> 11:        0    red 2012-01-01 archived   2.0
#> 12:        0    red 2011-01-01  removed   3.5
#> 13:        0  green 2014-01-01 inactive   8.0
#> 14:        0  green 2011-01-01  removed   2.0
#> 15:        0 yellow 2012-01-01 archived   2.5
#> 16:        0    red 2013-01-01  removed   3.5
#> 17:        0  green 2013-01-01   active   3.0
#> 18:        0  green 2014-01-01  removed   2.5
#> 19:        0    red 2011-01-01 archived   3.0
#> 20:        1    red 2015-01-01     <NA>   3.5
#> 21:        1  green 2015-01-01     <NA>   7.5
#> 22:        1  green 2014-01-01     <NA>  14.0
#> 23:        1 yellow 2014-01-01     <NA>   4.5
#> 24:        1    red 2013-01-01     <NA>   5.5
#> 25:        1  green 2011-01-01     <NA>  10.5
#> 26:        1    red 2014-01-01     <NA>   2.5
#> 27:        1 yellow 2015-01-01     <NA>   2.0
#> 28:        1    red 2012-01-01     <NA>   2.0
#> 29:        1    red 2011-01-01     <NA>   6.5
#> 30:        1 yellow 2012-01-01     <NA>   2.5
#> 31:        1  green 2013-01-01     <NA>   3.0
#> 32:        3    red       <NA>     <NA>  20.0
#> 33:        3  green       <NA>     <NA>  35.0
#> 34:        3 yellow       <NA>     <NA>   9.0
#> 35:        7   <NA>       <NA>     <NA>  64.0
#>     grouping  color       year   status value
rollup(DT, j=c(list(count=.N), lapply(.SD, sum)), by=by_vars, id=TRUE)
#>     grouping  color       year   status count amount value
#>        <int> <char>     <Date>   <fctr> <int>  <int> <num>
#>  1:        0    red 2015-01-01   active     1      4   3.5
#>  2:        0  green 2015-01-01 inactive     2      5   5.5
#>  3:        0  green 2014-01-01 archived     1      3   3.5
#>  4:        0  green 2015-01-01 archived     1      4   2.0
#>  5:        0 yellow 2014-01-01   active     2      5   4.5
#>  6:        0    red 2013-01-01 inactive     1      1   2.0
#>  7:        0  green 2011-01-01   active     2      9   6.0
#>  8:        0    red 2014-01-01 inactive     1      5   2.5
#>  9:        0  green 2011-01-01 archived     1      4   2.5
#> 10:        0 yellow 2015-01-01   active     1      4   2.0
#> 11:        0    red 2012-01-01 archived     1      4   2.0
#> 12:        0    red 2011-01-01  removed     1      1   3.5
#> 13:        0  green 2014-01-01 inactive     3      7   8.0
#> 14:        0  green 2011-01-01  removed     1      4   2.0
#> 15:        0 yellow 2012-01-01 archived     1      1   2.5
#> 16:        0    red 2013-01-01  removed     1      3   3.5
#> 17:        0  green 2013-01-01   active     1      2   3.0
#> 18:        0  green 2014-01-01  removed     1      5   2.5
#> 19:        0    red 2011-01-01 archived     1      1   3.0
#> 20:        1    red 2015-01-01     <NA>     1      4   3.5
#> 21:        1  green 2015-01-01     <NA>     3      9   7.5
#> 22:        1  green 2014-01-01     <NA>     5     15  14.0
#> 23:        1 yellow 2014-01-01     <NA>     2      5   4.5
#> 24:        1    red 2013-01-01     <NA>     2      4   5.5
#> 25:        1  green 2011-01-01     <NA>     4     17  10.5
#> 26:        1    red 2014-01-01     <NA>     1      5   2.5
#> 27:        1 yellow 2015-01-01     <NA>     1      4   2.0
#> 28:        1    red 2012-01-01     <NA>     1      4   2.0
#> 29:        1    red 2011-01-01     <NA>     2      2   6.5
#> 30:        1 yellow 2012-01-01     <NA>     1      1   2.5
#> 31:        1  green 2013-01-01     <NA>     1      2   3.0
#> 32:        3    red       <NA>     <NA>     7     19  20.0
#> 33:        3  green       <NA>     <NA>    13     43  35.0
#> 34:        3 yellow       <NA>     <NA>     4     10   9.0
#> 35:        7   <NA>       <NA>     <NA>    24     72  64.0
#>     grouping  color       year   status count amount value
rollup(DT, j=sum(value), by=by_vars,
       # specify label by variable name
       label=list(color="total", year=as.Date("3000-01-01"), status=factor("total")))
#>      color       year   status    V1
#>     <char>     <Date>   <fctr> <num>
#>  1:    red 2015-01-01   active   3.5
#>  2:  green 2015-01-01 inactive   5.5
#>  3:  green 2014-01-01 archived   3.5
#>  4:  green 2015-01-01 archived   2.0
#>  5: yellow 2014-01-01   active   4.5
#>  6:    red 2013-01-01 inactive   2.0
#>  7:  green 2011-01-01   active   6.0
#>  8:    red 2014-01-01 inactive   2.5
#>  9:  green 2011-01-01 archived   2.5
#> 10: yellow 2015-01-01   active   2.0
#> 11:    red 2012-01-01 archived   2.0
#> 12:    red 2011-01-01  removed   3.5
#> 13:  green 2014-01-01 inactive   8.0
#> 14:  green 2011-01-01  removed   2.0
#> 15: yellow 2012-01-01 archived   2.5
#> 16:    red 2013-01-01  removed   3.5
#> 17:  green 2013-01-01   active   3.0
#> 18:  green 2014-01-01  removed   2.5
#> 19:    red 2011-01-01 archived   3.0
#> 20:    red 2015-01-01    total   3.5
#> 21:  green 2015-01-01    total   7.5
#> 22:  green 2014-01-01    total  14.0
#> 23: yellow 2014-01-01    total   4.5
#> 24:    red 2013-01-01    total   5.5
#> 25:  green 2011-01-01    total  10.5
#> 26:    red 2014-01-01    total   2.5
#> 27: yellow 2015-01-01    total   2.0
#> 28:    red 2012-01-01    total   2.0
#> 29:    red 2011-01-01    total   6.5
#> 30: yellow 2012-01-01    total   2.5
#> 31:  green 2013-01-01    total   3.0
#> 32:    red 3000-01-01    total  20.0
#> 33:  green 3000-01-01    total  35.0
#> 34: yellow 3000-01-01    total   9.0
#> 35:  total 3000-01-01    total  64.0
#>      color       year   status    V1
rollup(DT, j=sum(value), by=by_vars,
       # specify label by variable name and first element of class
       label=list(color="total", Date=as.Date("3000-01-01"), factor=factor("total")))
#>      color       year   status    V1
#>     <char>     <Date>   <fctr> <num>
#>  1:    red 2015-01-01   active   3.5
#>  2:  green 2015-01-01 inactive   5.5
#>  3:  green 2014-01-01 archived   3.5
#>  4:  green 2015-01-01 archived   2.0
#>  5: yellow 2014-01-01   active   4.5
#>  6:    red 2013-01-01 inactive   2.0
#>  7:  green 2011-01-01   active   6.0
#>  8:    red 2014-01-01 inactive   2.5
#>  9:  green 2011-01-01 archived   2.5
#> 10: yellow 2015-01-01   active   2.0
#> 11:    red 2012-01-01 archived   2.0
#> 12:    red 2011-01-01  removed   3.5
#> 13:  green 2014-01-01 inactive   8.0
#> 14:  green 2011-01-01  removed   2.0
#> 15: yellow 2012-01-01 archived   2.5
#> 16:    red 2013-01-01  removed   3.5
#> 17:  green 2013-01-01   active   3.0
#> 18:  green 2014-01-01  removed   2.5
#> 19:    red 2011-01-01 archived   3.0
#> 20:    red 2015-01-01    total   3.5
#> 21:  green 2015-01-01    total   7.5
#> 22:  green 2014-01-01    total  14.0
#> 23: yellow 2014-01-01    total   4.5
#> 24:    red 2013-01-01    total   5.5
#> 25:  green 2011-01-01    total  10.5
#> 26:    red 2014-01-01    total   2.5
#> 27: yellow 2015-01-01    total   2.0
#> 28:    red 2012-01-01    total   2.0
#> 29:    red 2011-01-01    total   6.5
#> 30: yellow 2012-01-01    total   2.5
#> 31:  green 2013-01-01    total   3.0
#> 32:    red 3000-01-01    total  20.0
#> 33:  green 3000-01-01    total  35.0
#> 34: yellow 3000-01-01    total   9.0
#> 35:  total 3000-01-01    total  64.0
#>      color       year   status    V1
# label is character scalar so applies to color only
rollup(DT, j=sum(value), by=by_vars, label="total")
#>      color       year   status    V1
#>     <char>     <Date>   <fctr> <num>
#>  1:    red 2015-01-01   active   3.5
#>  2:  green 2015-01-01 inactive   5.5
#>  3:  green 2014-01-01 archived   3.5
#>  4:  green 2015-01-01 archived   2.0
#>  5: yellow 2014-01-01   active   4.5
#>  6:    red 2013-01-01 inactive   2.0
#>  7:  green 2011-01-01   active   6.0
#>  8:    red 2014-01-01 inactive   2.5
#>  9:  green 2011-01-01 archived   2.5
#> 10: yellow 2015-01-01   active   2.0
#> 11:    red 2012-01-01 archived   2.0
#> 12:    red 2011-01-01  removed   3.5
#> 13:  green 2014-01-01 inactive   8.0
#> 14:  green 2011-01-01  removed   2.0
#> 15: yellow 2012-01-01 archived   2.5
#> 16:    red 2013-01-01  removed   3.5
#> 17:  green 2013-01-01   active   3.0
#> 18:  green 2014-01-01  removed   2.5
#> 19:    red 2011-01-01 archived   3.0
#> 20:    red 2015-01-01     <NA>   3.5
#> 21:  green 2015-01-01     <NA>   7.5
#> 22:  green 2014-01-01     <NA>  14.0
#> 23: yellow 2014-01-01     <NA>   4.5
#> 24:    red 2013-01-01     <NA>   5.5
#> 25:  green 2011-01-01     <NA>  10.5
#> 26:    red 2014-01-01     <NA>   2.5
#> 27: yellow 2015-01-01     <NA>   2.0
#> 28:    red 2012-01-01     <NA>   2.0
#> 29:    red 2011-01-01     <NA>   6.5
#> 30: yellow 2012-01-01     <NA>   2.5
#> 31:  green 2013-01-01     <NA>   3.0
#> 32:    red       <NA>     <NA>  20.0
#> 33:  green       <NA>     <NA>  35.0
#> 34: yellow       <NA>     <NA>   9.0
#> 35:  total       <NA>     <NA>  64.0
#>      color       year   status    V1
rollup(DT, j=.N, by=c("color", "year", "status", "value"),
       # label can be explicitly specified as NA or NaN
       label = list(color=NA_character_, year=as.Date(NA), status=factor(NA), value=NaN))
#>      color       year   status value     N
#>     <char>     <Date>   <fctr> <num> <int>
#>  1:    red 2015-01-01   active   3.5     1
#>  2:  green 2015-01-01 inactive   3.5     1
#>  3:  green 2014-01-01 archived   3.5     1
#>  4:  green 2015-01-01 archived   2.0     1
#>  5:  green 2015-01-01 inactive   2.0     1
#>  6: yellow 2014-01-01   active   2.5     1
#>  7:    red 2013-01-01 inactive   2.0     1
#>  8: yellow 2014-01-01   active   2.0     1
#>  9:  green 2011-01-01   active   3.5     1
#> 10:    red 2014-01-01 inactive   2.5     1
#> 11:  green 2011-01-01 archived   2.5     1
#> 12: yellow 2015-01-01   active   2.0     1
#> 13:    red 2012-01-01 archived   2.0     1
#> 14:    red 2011-01-01  removed   3.5     1
#> 15:  green 2014-01-01 inactive   3.0     2
#> 16:  green 2011-01-01  removed   2.0     1
#> 17: yellow 2012-01-01 archived   2.5     1
#> 18:  green 2011-01-01   active   2.5     1
#> 19:    red 2013-01-01  removed   3.5     1
#> 20:  green 2014-01-01 inactive   2.0     1
#> 21:  green 2013-01-01   active   3.0     1
#> 22:  green 2014-01-01  removed   2.5     1
#> 23:    red 2011-01-01 archived   3.0     1
#> 24:    red 2015-01-01   active   NaN     1
#> 25:  green 2015-01-01 inactive   NaN     2
#> 26:  green 2014-01-01 archived   NaN     1
#> 27:  green 2015-01-01 archived   NaN     1
#> 28: yellow 2014-01-01   active   NaN     2
#> 29:    red 2013-01-01 inactive   NaN     1
#> 30:  green 2011-01-01   active   NaN     2
#> 31:    red 2014-01-01 inactive   NaN     1
#> 32:  green 2011-01-01 archived   NaN     1
#> 33: yellow 2015-01-01   active   NaN     1
#> 34:    red 2012-01-01 archived   NaN     1
#> 35:    red 2011-01-01  removed   NaN     1
#> 36:  green 2014-01-01 inactive   NaN     3
#> 37:  green 2011-01-01  removed   NaN     1
#> 38: yellow 2012-01-01 archived   NaN     1
#> 39:    red 2013-01-01  removed   NaN     1
#> 40:  green 2013-01-01   active   NaN     1
#> 41:  green 2014-01-01  removed   NaN     1
#> 42:    red 2011-01-01 archived   NaN     1
#> 43:    red 2015-01-01     <NA>   NaN     1
#> 44:  green 2015-01-01     <NA>   NaN     3
#> 45:  green 2014-01-01     <NA>   NaN     5
#> 46: yellow 2014-01-01     <NA>   NaN     2
#> 47:    red 2013-01-01     <NA>   NaN     2
#> 48:  green 2011-01-01     <NA>   NaN     4
#> 49:    red 2014-01-01     <NA>   NaN     1
#> 50: yellow 2015-01-01     <NA>   NaN     1
#> 51:    red 2012-01-01     <NA>   NaN     1
#> 52:    red 2011-01-01     <NA>   NaN     2
#> 53: yellow 2012-01-01     <NA>   NaN     1
#> 54:  green 2013-01-01     <NA>   NaN     1
#> 55:    red       <NA>     <NA>   NaN     7
#> 56:  green       <NA>     <NA>   NaN    13
#> 57: yellow       <NA>     <NA>   NaN     4
#> 58:   <NA>       <NA>     <NA>   NaN    24
#>      color       year   status value     N

# cube
cube(DT, j = sum(value), by = c("color","year","status"), id=TRUE)
#>     grouping  color       year   status    V1
#>        <int> <char>     <Date>   <fctr> <num>
#>  1:        0    red 2015-01-01   active   3.5
#>  2:        0  green 2015-01-01 inactive   5.5
#>  3:        0  green 2014-01-01 archived   3.5
#>  4:        0  green 2015-01-01 archived   2.0
#>  5:        0 yellow 2014-01-01   active   4.5
#>  6:        0    red 2013-01-01 inactive   2.0
#>  7:        0  green 2011-01-01   active   6.0
#>  8:        0    red 2014-01-01 inactive   2.5
#>  9:        0  green 2011-01-01 archived   2.5
#> 10:        0 yellow 2015-01-01   active   2.0
#> 11:        0    red 2012-01-01 archived   2.0
#> 12:        0    red 2011-01-01  removed   3.5
#> 13:        0  green 2014-01-01 inactive   8.0
#> 14:        0  green 2011-01-01  removed   2.0
#> 15:        0 yellow 2012-01-01 archived   2.5
#> 16:        0    red 2013-01-01  removed   3.5
#> 17:        0  green 2013-01-01   active   3.0
#> 18:        0  green 2014-01-01  removed   2.5
#> 19:        0    red 2011-01-01 archived   3.0
#> 20:        1    red 2015-01-01     <NA>   3.5
#> 21:        1  green 2015-01-01     <NA>   7.5
#> 22:        1  green 2014-01-01     <NA>  14.0
#> 23:        1 yellow 2014-01-01     <NA>   4.5
#> 24:        1    red 2013-01-01     <NA>   5.5
#> 25:        1  green 2011-01-01     <NA>  10.5
#> 26:        1    red 2014-01-01     <NA>   2.5
#> 27:        1 yellow 2015-01-01     <NA>   2.0
#> 28:        1    red 2012-01-01     <NA>   2.0
#> 29:        1    red 2011-01-01     <NA>   6.5
#> 30:        1 yellow 2012-01-01     <NA>   2.5
#> 31:        1  green 2013-01-01     <NA>   3.0
#> 32:        2    red       <NA>   active   3.5
#> 33:        2  green       <NA> inactive  13.5
#> 34:        2  green       <NA> archived   8.0
#> 35:        2 yellow       <NA>   active   6.5
#> 36:        2    red       <NA> inactive   4.5
#> 37:        2  green       <NA>   active   9.0
#> 38:        2    red       <NA> archived   5.0
#> 39:        2    red       <NA>  removed   7.0
#> 40:        2  green       <NA>  removed   4.5
#> 41:        2 yellow       <NA> archived   2.5
#> 42:        3    red       <NA>     <NA>  20.0
#> 43:        3  green       <NA>     <NA>  35.0
#> 44:        3 yellow       <NA>     <NA>   9.0
#> 45:        4   <NA> 2015-01-01   active   5.5
#> 46:        4   <NA> 2015-01-01 inactive   5.5
#> 47:        4   <NA> 2014-01-01 archived   3.5
#> 48:        4   <NA> 2015-01-01 archived   2.0
#> 49:        4   <NA> 2014-01-01   active   4.5
#> 50:        4   <NA> 2013-01-01 inactive   2.0
#> 51:        4   <NA> 2011-01-01   active   6.0
#> 52:        4   <NA> 2014-01-01 inactive  10.5
#> 53:        4   <NA> 2011-01-01 archived   5.5
#> 54:        4   <NA> 2012-01-01 archived   4.5
#> 55:        4   <NA> 2011-01-01  removed   5.5
#> 56:        4   <NA> 2013-01-01  removed   3.5
#> 57:        4   <NA> 2013-01-01   active   3.0
#> 58:        4   <NA> 2014-01-01  removed   2.5
#> 59:        5   <NA> 2015-01-01     <NA>  13.0
#> 60:        5   <NA> 2014-01-01     <NA>  21.0
#> 61:        5   <NA> 2013-01-01     <NA>   8.5
#> 62:        5   <NA> 2011-01-01     <NA>  17.0
#> 63:        5   <NA> 2012-01-01     <NA>   4.5
#> 64:        6   <NA>       <NA>   active  19.0
#> 65:        6   <NA>       <NA> inactive  18.0
#> 66:        6   <NA>       <NA> archived  15.5
#> 67:        6   <NA>       <NA>  removed  11.5
#> 68:        7   <NA>       <NA>     <NA>  64.0
#>     grouping  color       year   status    V1
cube(DT, j = lapply(.SD, sum), by = c("color","year","status"), id=TRUE, .SDcols="value")
#>     grouping  color       year   status value
#>        <int> <char>     <Date>   <fctr> <num>
#>  1:        0    red 2015-01-01   active   3.5
#>  2:        0  green 2015-01-01 inactive   5.5
#>  3:        0  green 2014-01-01 archived   3.5
#>  4:        0  green 2015-01-01 archived   2.0
#>  5:        0 yellow 2014-01-01   active   4.5
#>  6:        0    red 2013-01-01 inactive   2.0
#>  7:        0  green 2011-01-01   active   6.0
#>  8:        0    red 2014-01-01 inactive   2.5
#>  9:        0  green 2011-01-01 archived   2.5
#> 10:        0 yellow 2015-01-01   active   2.0
#> 11:        0    red 2012-01-01 archived   2.0
#> 12:        0    red 2011-01-01  removed   3.5
#> 13:        0  green 2014-01-01 inactive   8.0
#> 14:        0  green 2011-01-01  removed   2.0
#> 15:        0 yellow 2012-01-01 archived   2.5
#> 16:        0    red 2013-01-01  removed   3.5
#> 17:        0  green 2013-01-01   active   3.0
#> 18:        0  green 2014-01-01  removed   2.5
#> 19:        0    red 2011-01-01 archived   3.0
#> 20:        1    red 2015-01-01     <NA>   3.5
#> 21:        1  green 2015-01-01     <NA>   7.5
#> 22:        1  green 2014-01-01     <NA>  14.0
#> 23:        1 yellow 2014-01-01     <NA>   4.5
#> 24:        1    red 2013-01-01     <NA>   5.5
#> 25:        1  green 2011-01-01     <NA>  10.5
#> 26:        1    red 2014-01-01     <NA>   2.5
#> 27:        1 yellow 2015-01-01     <NA>   2.0
#> 28:        1    red 2012-01-01     <NA>   2.0
#> 29:        1    red 2011-01-01     <NA>   6.5
#> 30:        1 yellow 2012-01-01     <NA>   2.5
#> 31:        1  green 2013-01-01     <NA>   3.0
#> 32:        2    red       <NA>   active   3.5
#> 33:        2  green       <NA> inactive  13.5
#> 34:        2  green       <NA> archived   8.0
#> 35:        2 yellow       <NA>   active   6.5
#> 36:        2    red       <NA> inactive   4.5
#> 37:        2  green       <NA>   active   9.0
#> 38:        2    red       <NA> archived   5.0
#> 39:        2    red       <NA>  removed   7.0
#> 40:        2  green       <NA>  removed   4.5
#> 41:        2 yellow       <NA> archived   2.5
#> 42:        3    red       <NA>     <NA>  20.0
#> 43:        3  green       <NA>     <NA>  35.0
#> 44:        3 yellow       <NA>     <NA>   9.0
#> 45:        4   <NA> 2015-01-01   active   5.5
#> 46:        4   <NA> 2015-01-01 inactive   5.5
#> 47:        4   <NA> 2014-01-01 archived   3.5
#> 48:        4   <NA> 2015-01-01 archived   2.0
#> 49:        4   <NA> 2014-01-01   active   4.5
#> 50:        4   <NA> 2013-01-01 inactive   2.0
#> 51:        4   <NA> 2011-01-01   active   6.0
#> 52:        4   <NA> 2014-01-01 inactive  10.5
#> 53:        4   <NA> 2011-01-01 archived   5.5
#> 54:        4   <NA> 2012-01-01 archived   4.5
#> 55:        4   <NA> 2011-01-01  removed   5.5
#> 56:        4   <NA> 2013-01-01  removed   3.5
#> 57:        4   <NA> 2013-01-01   active   3.0
#> 58:        4   <NA> 2014-01-01  removed   2.5
#> 59:        5   <NA> 2015-01-01     <NA>  13.0
#> 60:        5   <NA> 2014-01-01     <NA>  21.0
#> 61:        5   <NA> 2013-01-01     <NA>   8.5
#> 62:        5   <NA> 2011-01-01     <NA>  17.0
#> 63:        5   <NA> 2012-01-01     <NA>   4.5
#> 64:        6   <NA>       <NA>   active  19.0
#> 65:        6   <NA>       <NA> inactive  18.0
#> 66:        6   <NA>       <NA> archived  15.5
#> 67:        6   <NA>       <NA>  removed  11.5
#> 68:        7   <NA>       <NA>     <NA>  64.0
#>     grouping  color       year   status value
cube(DT, j = c(list(count=.N), lapply(.SD, sum)), by = c("color","year","status"), id=TRUE)
#>     grouping  color       year   status count amount value
#>        <int> <char>     <Date>   <fctr> <int>  <int> <num>
#>  1:        0    red 2015-01-01   active     1      4   3.5
#>  2:        0  green 2015-01-01 inactive     2      5   5.5
#>  3:        0  green 2014-01-01 archived     1      3   3.5
#>  4:        0  green 2015-01-01 archived     1      4   2.0
#>  5:        0 yellow 2014-01-01   active     2      5   4.5
#>  6:        0    red 2013-01-01 inactive     1      1   2.0
#>  7:        0  green 2011-01-01   active     2      9   6.0
#>  8:        0    red 2014-01-01 inactive     1      5   2.5
#>  9:        0  green 2011-01-01 archived     1      4   2.5
#> 10:        0 yellow 2015-01-01   active     1      4   2.0
#> 11:        0    red 2012-01-01 archived     1      4   2.0
#> 12:        0    red 2011-01-01  removed     1      1   3.5
#> 13:        0  green 2014-01-01 inactive     3      7   8.0
#> 14:        0  green 2011-01-01  removed     1      4   2.0
#> 15:        0 yellow 2012-01-01 archived     1      1   2.5
#> 16:        0    red 2013-01-01  removed     1      3   3.5
#> 17:        0  green 2013-01-01   active     1      2   3.0
#> 18:        0  green 2014-01-01  removed     1      5   2.5
#> 19:        0    red 2011-01-01 archived     1      1   3.0
#> 20:        1    red 2015-01-01     <NA>     1      4   3.5
#> 21:        1  green 2015-01-01     <NA>     3      9   7.5
#> 22:        1  green 2014-01-01     <NA>     5     15  14.0
#> 23:        1 yellow 2014-01-01     <NA>     2      5   4.5
#> 24:        1    red 2013-01-01     <NA>     2      4   5.5
#> 25:        1  green 2011-01-01     <NA>     4     17  10.5
#> 26:        1    red 2014-01-01     <NA>     1      5   2.5
#> 27:        1 yellow 2015-01-01     <NA>     1      4   2.0
#> 28:        1    red 2012-01-01     <NA>     1      4   2.0
#> 29:        1    red 2011-01-01     <NA>     2      2   6.5
#> 30:        1 yellow 2012-01-01     <NA>     1      1   2.5
#> 31:        1  green 2013-01-01     <NA>     1      2   3.0
#> 32:        2    red       <NA>   active     1      4   3.5
#> 33:        2  green       <NA> inactive     5     12  13.5
#> 34:        2  green       <NA> archived     3     11   8.0
#> 35:        2 yellow       <NA>   active     3      9   6.5
#> 36:        2    red       <NA> inactive     2      6   4.5
#> 37:        2  green       <NA>   active     3     11   9.0
#> 38:        2    red       <NA> archived     2      5   5.0
#> 39:        2    red       <NA>  removed     2      4   7.0
#> 40:        2  green       <NA>  removed     2      9   4.5
#> 41:        2 yellow       <NA> archived     1      1   2.5
#> 42:        3    red       <NA>     <NA>     7     19  20.0
#> 43:        3  green       <NA>     <NA>    13     43  35.0
#> 44:        3 yellow       <NA>     <NA>     4     10   9.0
#> 45:        4   <NA> 2015-01-01   active     2      8   5.5
#> 46:        4   <NA> 2015-01-01 inactive     2      5   5.5
#> 47:        4   <NA> 2014-01-01 archived     1      3   3.5
#> 48:        4   <NA> 2015-01-01 archived     1      4   2.0
#> 49:        4   <NA> 2014-01-01   active     2      5   4.5
#> 50:        4   <NA> 2013-01-01 inactive     1      1   2.0
#> 51:        4   <NA> 2011-01-01   active     2      9   6.0
#> 52:        4   <NA> 2014-01-01 inactive     4     12  10.5
#> 53:        4   <NA> 2011-01-01 archived     2      5   5.5
#> 54:        4   <NA> 2012-01-01 archived     2      5   4.5
#> 55:        4   <NA> 2011-01-01  removed     2      5   5.5
#> 56:        4   <NA> 2013-01-01  removed     1      3   3.5
#> 57:        4   <NA> 2013-01-01   active     1      2   3.0
#> 58:        4   <NA> 2014-01-01  removed     1      5   2.5
#> 59:        5   <NA> 2015-01-01     <NA>     5     17  13.0
#> 60:        5   <NA> 2014-01-01     <NA>     8     25  21.0
#> 61:        5   <NA> 2013-01-01     <NA>     3      6   8.5
#> 62:        5   <NA> 2011-01-01     <NA>     6     19  17.0
#> 63:        5   <NA> 2012-01-01     <NA>     2      5   4.5
#> 64:        6   <NA>       <NA>   active     7     24  19.0
#> 65:        6   <NA>       <NA> inactive     7     18  18.0
#> 66:        6   <NA>       <NA> archived     6     17  15.5
#> 67:        6   <NA>       <NA>  removed     4     13  11.5
#> 68:        7   <NA>       <NA>     <NA>    24     72  64.0
#>     grouping  color       year   status count amount value

# 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)
#>     grouping  color       year   status count amount value
#>        <int> <char>     <Date>   <fctr> <int>  <int> <num>
#>  1:        3    red       <NA>     <NA>     7     19  20.0
#>  2:        3  green       <NA>     <NA>    13     43  35.0
#>  3:        3 yellow       <NA>     <NA>     4     10   9.0
#>  4:        4   <NA> 2015-01-01   active     2      8   5.5
#>  5:        4   <NA> 2015-01-01 inactive     2      5   5.5
#>  6:        4   <NA> 2014-01-01 archived     1      3   3.5
#>  7:        4   <NA> 2015-01-01 archived     1      4   2.0
#>  8:        4   <NA> 2014-01-01   active     2      5   4.5
#>  9:        4   <NA> 2013-01-01 inactive     1      1   2.0
#> 10:        4   <NA> 2011-01-01   active     2      9   6.0
#> 11:        4   <NA> 2014-01-01 inactive     4     12  10.5
#> 12:        4   <NA> 2011-01-01 archived     2      5   5.5
#> 13:        4   <NA> 2012-01-01 archived     2      5   4.5
#> 14:        4   <NA> 2011-01-01  removed     2      5   5.5
#> 15:        4   <NA> 2013-01-01  removed     1      3   3.5
#> 16:        4   <NA> 2013-01-01   active     1      2   3.0
#> 17:        4   <NA> 2014-01-01  removed     1      5   2.5
#> 18:        7   <NA>       <NA>     <NA>    24     72  64.0