Grouping Set aggregation for data tables
groupingsets.RdCalculate 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, ...)
cube(x, ...)
# S3 method for data.table
cube(x, j, by, .SDcols, id = FALSE, ...)
groupingsets(x, ...)
# S3 method for data.table
groupingsets(x, j, by, sets, .SDcols, id = FALSE, jj, ...)Arguments
- x
data.table.- ...
argument passed to custom user methods. Ignored for
data.tablemethods.- 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
groupingsetsfor flexibility.- .SDcols
columns to be used in
jexpression in.SDobject.- id
logical default
FALSE. IfTRUEit will add leading column with bit mask of grouping sets.- jj
quoted version of
jargument, for convenience. When provided function will ignorejargument.
Details
All three functions rollup, cube, groupingsets are generic methods, data.table methods are provided.
References
https://www.postgresql.org/docs/9.5/static/queries-table-expressions.html#QUERIES-GROUPING-SETS https://www.postgresql.org/docs/9.5/static/functions-aggregate.html#FUNCTIONS-GROUPING-TABLE
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
rollup(DT, j = sum(value), by = c("color","year","status")) # 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 = 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: 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 = 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: 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 = 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: 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
# 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