How to do Group By Rollup in R? (Like SQL)

问题:

I have a dataset and I want to perform something like Group By Rollup like we have in SQL for aggregate values.

Below is a reproducible example. I know aggregate works really well as explained here but not a satisfactory fit for my case.

year<- c('2016','2016','2016','2016','2017','2017','2017','2017')
month<- c('1','1','1','1','2','2','2','2')
region<- c('east','west','east','west','east','west','east','west')
sales<- c(100,200,300,400,200,400,600,800)
df<- data.frame(year,month,region,sales)
df


year month region sales
1 2016     1   east   100
2 2016     1   west   200
3 2016     1   east   300
4 2016     1   west   400
5 2017     2   east   200
6 2017     2   west   400
7 2017     2   east   600
8 2017     2   west   800

now what I want to do is aggregation (sum- by year-month-region) and add the new aggregate row in the existing dataframe e.g. there should be two additional rows like below with a new name for region as 'USA' for the aggreagted rows

year month region sales
1 2016     1   east   400
2 2016     1   west   600
3 2016     1    USA  1000
4 2017     2   east   800
5 2017     2   west  1200
6 2017     2    USA  2000

I have figured out a way (below) but I am very sure that there exists an optimum solution for this OR a better workaround than mine

df1<- setNames(aggregate(df$sales, by=list(df$year,df$month, df$region), FUN=sum),
    c('year','month','region', 'sales'))


df2<- setNames(aggregate(df$sales, by=list(df$year,df$month), FUN=sum),
               c('year','month', 'sales'))

df2$region<- 'USA'                  ## added a new column- region- for total USA
df2<- df2[,  c('year','month','region', 'sales')]  ## reordering the columns of df2

df3<- rbind(df1,df2)

df3<- df3[order(df3$year,df3$month,df3$region),]  ## order by
rownames(df3)<- NULL  ## renumbered the rows after order by

df3

Thanks for the support!

回答1:

melt/dcast in the reshape2 package can do subtotalling. After running dcast we replace "(all)" in the month column with the month using na.locf from the zoo package:

library(reshape2)
library(zoo)

m <- melt(df, measure.vars = "sales")
dout <- dcast(m, year + month + region ~ variable, fun.aggregate = sum, margins = "month")

dout$month <- na.locf(replace(dout$month, dout$month  == "(all)", NA))

giving:

> dout
  year month region sales
1 2016     1   east   400
2 2016     1   west   600
3 2016     1  (all)  1000
4 2017     2   east   800
5 2017     2   west  1200
6 2017     2  (all)  2000

回答2:

In recent devel data.table 1.10.5 you can use new feature called "grouping sets" to produce sub totals:

library(data.table)
setDT(df)
res = groupingsets(df, .(sales=sum(sales)), sets=list(c("year","month"), c("year","month","region")), by=c("year","month","region"))
setorder(res, na.last=TRUE)
res
#   year month region sales
#1: 2016     1   east   400
#2: 2016     1   west   600
#3: 2016     1     NA  1000
#4: 2017     2   east   800
#5: 2017     2   west  1200
#6: 2017     2     NA  2000

You can substitute NA to USA using res[is.na(region), region := "USA"].

回答3:

plyr::ddply(df, c("year", "month", "region"), plyr::summarise, sales = sum(sales))