R data.table: Aggregated functions with totals

README.md

See http://stackoverflow.com/questions/9315258/aggregating-sub-totals-and-grand-totals-with-data-table.

code.r

aggr_by <- function(data, j, ..., grand.total = T, total.label = "(all)", by = names(dplyr:::named_dots(...))) {
    j = substitute(j)
    
    # Calculate by each combination
    lst <- lapply(1:length(by), function(i) {
      x <- data[, eval(j), by = eval(by[1:i])]
      if (i != length(by)) x[, (by[-(1:i)]) := total.label]
      return(x)
    })
    
    # Grand total
    if (grand.total) lst <- c(lst, list(data[, eval(j)][, (by) := total.label]))
    
    # Combine all tables
    res <- rbindlist(lst, use.names = T, fill = F)
  
    # Set proper column order
    setcolorder(res, c(by, colnames(res)[!colnames(res) %in% by]))
    
  # Sort values
  setkeyv(res, by)
  
  return(res)
}

## Example
# dt <- data.table::data.table(mtcars)
# aggr_by(dt, list(mean(disp), max(mpg)), vs, carb, gear)
# aggr_by(dt, list(A = mean(disp), B = any(hp > 120), C = .N), vs, carb, gear, total.label = "`")