This is a sample data.
library(data.table);
DTmtcars<-as.data.table(mtcars,keep.rownames = TRUE);
I need this summary table, however I also need a total row.
DTmtcars[, .(
QSEC = mean(qsec) ,
DISP = sum(disp)/.N ,
VS = sum(vs !=0)),
by = gear]
total row should have these values:
DTmtcars[, .(
QSEC = mean(qsec) ,
DISP = sum(disp)/.N ,
VS = sum(vs !=0))]
Recognized by R Language Collective
5
cube(DTmtcars,
.(QSEC = mean(qsec) ,
DISP = sum(disp)/.N ,
VS = sum(vs !=0)),
by = "gear")
Output
gear QSEC DISP VS
<num> <num> <num> <int>
1: 4 18.96500 123.0167 10
2: 3 17.69200 326.3000 3
3: 5 15.64000 202.4800 1
4: NA 17.84875 230.7219 14
required output:
> DTmtcars[, .(
+ QSEC = mean(qsec) ,
+ DISP = sum(disp)/.N ,
+ VS = sum(vs !=0))]
QSEC DISP VS
<num> <num> <int>
1: 17.84875 230.7219 14
Use data.table::rbind with fill:
rbind(
DTmtcars[, .(
QSEC = mean(qsec) ,
DISP = sum(disp)/.N ,
VS = sum(vs !=0)),
by = gear],
DTmtcars[, .(
QSEC = mean(qsec) ,
DISP = sum(disp)/.N ,
VS = sum(vs !=0))],
fill = TRUE)
Or loop with ifelse and use rbindlist:
rbindlist(
lapply(1:2, function(i){
if(i == 1){
DTmtcars[, .(
QSEC = mean(qsec) ,
DISP = sum(disp)/.N ,
VS = sum(vs !=0)),
by = gear]
} else {
DTmtcars[, .(
QSEC = mean(qsec) ,
DISP = sum(disp)/.N ,
VS = sum(vs !=0))]
}
}), fill = TRUE)
# gear QSEC DISP VS
# <num> <num> <num> <int>
#1: 4 18.96500 123.0167 10
#2: 3 17.69200 326.3000 3
#3: 5 15.64000 202.4800 1
#4: NA 17.84875 230.7219 14
Recognized by R Language Collective