使用data.table汇总子总计和总计

Question

使用data.table汇总子总计和总计

14

我在R中有一个data.table：

library(data.table)
set.seed(1)
DT = data.table(
  group=sample(letters[1:2],100,replace=TRUE), 
  year=sample(2010:2012,100,replace=TRUE),
  v=runif(100))

按照组和年份将这些数据汇总到摘要表中非常简单和优雅：

table <- DT[,mean(v),by='group, year']

然而，将这些数据聚合到汇总表中，包括小计和总计，会更加困难，而且不够优雅：

library(plyr)
yearTot <- DT[,list(mean(v),year='Total'),by='group']
groupTot <- DT[,list(mean(v),group='Total'),by='year']
Tot <- DT[,list(mean(v), year='Total', group='Total')]
table <- rbind.fill(table,yearTot,groupTot,Tot)
table$group[table$group==1] <- 'Total'
table$year[table$year==1] <- 'Total'

这将产生：

table[order(table$group, table$year), ]

在data.table中是否有类似于plyr中的margins=TRUE命令的简单方法来指定小计和总计？我希望在我的数据集上使用data.table而不是plyr，因为这是一个非常大的数据集，并且已经以data.table格式存在。

- Zach

5个回答

11

我不知道有简单的方法。这是一种初步实现方案。我不知道plyr中的margins=TRUE是什么意思？

crossby = function(DT, j, by) {
    j = substitute(j)
    ans = rbind(
        DT[,eval(j),by],
        DT[,list("Total",eval(j)),by=by[1]],
        cbind("Total",DT[,eval(j),by=by[2]]),
        list("Total","Total",DT[,eval(j)]),
        use.names=FALSE
        # 'use.names' argument added in data.table v1.8.0
    )
    setkeyv(ans,by)
    ans
}

crossby(DT, mean(v), c("group","year"))

      group  year        V1
 [1,]     a  2010 0.2926945
 [2,]     a  2011 0.4176346
 [3,]     a  2012 0.4227796
 [4,]     a Total 0.3901875
 [5,]     b  2010 0.5231845
 [6,]     b  2011 0.4997119
 [7,]     b  2012 0.4306871
 [8,]     b Total 0.4835788
 [9,] Total  2010 0.4278093
[10,] Total  2011 0.4463616
[11,] Total  2012 0.4271160
[12,] Total Total 0.4350153

- Matt Dowle

2

tables包似乎可以与DT一起使用，尽管不清楚它是否实际上在底层使用了data.table操作 -- library(tables); tabular(group + 1 ~ (factor(year) + 1) * v * mean, data = DT) - G. Grothendieck

3

@Michael。好的，我现在已经提交了#2695以添加那个功能（或类似的功能）。谢谢。 - Matt Dowle

3

我不知道是否因为上面的代码是针对较旧版本的“data.table”，但我不得不进行一些更改才能使上述代码正常工作-我必须将by [1]和 by [2]用 c()包装起来，我必须在第二个eval中添加.SD，并且我必须确保DT中的year是一个字符列（否则rbind会将Total转换为NA）。 - eddi

说到 eval 和 FR，我们是否可以在复杂表达式中让 eval 自动默认为 .SD？ - eddi

@eddi 那是个好主意。请提交吧。抱歉，不确定为什么需要 c()。可能是自从我回答这里以来，data.table 已经发生了变化，但我希望有些东西会在新闻中记录这种变化。 - Matt Dowle

显示剩余4条评论

5

根据当前的回答，我已经添加了多个度量和聚合函数的支持，并可以添加聚合级别指示器。

#' @title SQL's ROLLUP function
#' @description Returns data.table of aggregates value for each level of hierarchy provided in `by`.
#' @param x data.table input data.
#' @param j expression to evaluate in `j`, support multiple measures.
#' @param by character a hierarchy level for aggregations.
#' @param level logical, use `TRUE` to add `level` column of sub-aggregation.
#' @seealso [postgres: GROUPING SETS, CUBE, and ROLLUP](http://www.postgresql.org/docs/9.5/static/queries-table-expressions.html#QUERIES-GROUPING-SETS), [SO: Aggregating sub totals and grand totals with data.table](https://dev59.com/jWox5IYBdhLWcg3wSCRW#24828162)
#' @return data.table
#' @examples 
#' set.seed(1)
#' x = data.table(group=sample(letters[1:2],100,replace=TRUE),
#'                year=sample(2010:2012,100,replace=TRUE),
#'                v=runif(100))
#' rollup(x, .(vmean=mean(v), vsum=sum(v)), by = c("group","year"))
library(data.table)
rollup = function(x, j, by, level=FALSE){
    stopifnot(is.data.table(x), is.character(by), length(by) >= 2L, is.logical(level))
    j = substitute(j)
    aggrs = rbindlist(c(
        lapply(1:(length(by)-1L), function(i) x[, eval(j), c(by[1:i])][, (by[-(1:i)]) := NA]), # subtotals
        list(x[, eval(j), c(by)]), # leafs aggregations
        list(x[, eval(j)][, c(by) := NA]) # grand total
    ), use.names = TRUE, fill = FALSE)
    if(level) aggrs[, c("level") := sum(sapply(.SD, is.na)), 1:nrow(aggrs), .SDcols = by]
    setcolorder(aggrs, neworder = c(by, names(aggrs)[!names(aggrs) %in% by]))
    setorderv(aggrs, cols = by, order=1L, na.last=TRUE)
    return(aggrs[])
}
set.seed(1)
x = data.table(group=sample(letters[1:2],100,replace=TRUE),
               year=sample(2010:2012,100,replace=TRUE),
               month=sample(1:12,100,replace=TRUE),
               v=runif(100))
rollup(x, .(vmean=mean(v), vsum=sum(v)), by = c("group","year","month"), level=TRUE)

- jangorecki

这是您的一个套餐的一部分吗？ - David Arenburg

不，如果data.table中有这样的东西会很好，我已经添加了FR以查看是否有机会在C中加速此类操作，如果不可能，则可以在PR中提出这样的包装器。 - jangorecki

@DavidArenburg，从现在开始是这样的。它更加精确，因为它允许计算跳过其余部分只选择的聚合级别。levels参数采用整数向量，指示不同的聚合级别。您可以在此处找到该函数rollup.R#L11。 - jangorecki

5

请看下面的解决方案——与@MattDowle上面的类似——可以处理任意数量的组。

crossby2 <- function(data, j, by, grand.total = T, total.label = "(all)", value.label = "value") {
  j = substitute(j)

  # Calculate by each group
  lst <- lapply(1:length(by), function(i) {
    x <- data[, list(..VALUE.. = 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[, list(..VALUE.. = eval(j))][, (by) := total.label]))

  # Combine all tables
  res <- rbindlist(lst, use.names = T, fill = F)

  # Change value column name
  setnames(res, "..VALUE..", value.label)

  # Set proper column order
  setcolorder(res, c(by, value.label))

  # Sort values
  setkeyv(res, by)

  return(res)
}

- reinholdsson

1

借鉴自这个答案 (https://dev59.com/hXLYa4cB1Zd3GeqParT5#39536828)，下面提供了一个全子集汇总 (与crossby2和rollup不同，它们似乎错过了OP所需的第9到11行)。虽然在当前状态下只允许一种聚合函数，但该函数可扩展到任意数量的按或聚合变量。非常适合通过组交互计算行小计（我用它来做这个）。

add_col_sums.data.table <- function(data, aggvars, byvars, FUN = sum, level = "level") {

  # Find all possible subsets of your data
  subsets <- lapply(0:length(byvars), combn, x = byvars, simplify = FALSE)
  subsets <- do.call(c, subsets)

  # Calculate summary value by each subset
  agg_values <- lapply(subsets, function(x) 
    data[,lapply(.SD, FUN), by = x, .SDcols = aggvars])

  # Pull them all into one dataframe
  dat_out <- rbindlist(agg_values, fill = TRUE)

  # Order columns and rows
  setorderv(dat_out, byvars, na.last = TRUE)
  setcolorder(dat_out, c(byvars, aggvars))

  # Add level indication
  dat_out[, c(level) := Reduce("+", lapply(.SD, is.na))]

  # Return data.table
  dat_out[]

}

add_col_sums.data.table(DT, "v", c("group", "year"), FUN = mean)

- JWilliman

非常好的解决方案。第一次就成功了，而其他的在多列集上都无法奏效。 - Chris

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- jangorecki · Accepted Answer

在最近开发的data.table中，你可以使用称为“分组集”（grouping sets）的新特性来生成小计：

library(data.table)
set.seed(1)
DT = data.table(
    group=sample(letters[1:2],100,replace=TRUE), 
    year=sample(2010:2012,100,replace=TRUE),
    v=runif(100))

cube(DT, mean(v), by=c("group","year"))
#    group year        V1
# 1:     a 2011 0.4176346
# 2:     b 2010 0.5231845
# 3:     b 2012 0.4306871
# 4:     b 2011 0.4997119
# 5:     a 2012 0.4227796
# 6:     a 2010 0.2926945
# 7:    NA 2011 0.4463616
# 8:    NA 2010 0.4278093
# 9:    NA 2012 0.4271160
#10:     a   NA 0.3901875
#11:     b   NA 0.4835788
#12:    NA   NA 0.4350153
cube(DT, mean(v), by=c("group","year"), id=TRUE)
#    grouping group year        V1
# 1:        0     a 2011 0.4176346
# 2:        0     b 2010 0.5231845
# 3:        0     b 2012 0.4306871
# 4:        0     b 2011 0.4997119
# 5:        0     a 2012 0.4227796
# 6:        0     a 2010 0.2926945
# 7:        2    NA 2011 0.4463616
# 8:        2    NA 2010 0.4278093
# 9:        2    NA 2012 0.4271160
#10:        1     a   NA 0.3901875
#11:        1     b   NA 0.4835788
#12:        3    NA   NA 0.4350153