I wish to sum pairs of columns by group. In the example below I wish to sum pairs (v1 and v2), (v3 and v4), and (v5 and v6), each by r1, r2 and r3.
I can do this using the sapply statement below and I get the correct answer. However, the required code is complex. Could someone show me how to do the same operation perhaps in package data.table or with rollapply and/or other options? I have not yet explored those options.
Sorry if this is a duplicate.
my.data <- read.table(text= "
r1 r2 r3 t1 t2 t3 v1 v2 v3 v4 v5 v6
1 0 0 10 20 30 1 0 0 0 0 0
1 0 0 10 20 30 1 1 0 0 0 0
1 0 0 10 20 30 1 0 1 0 0 0
1 0 0 10 20 30 1 0 1 1 0 0
1 0 0 10 20 30 0 0 0 0 0 0
0 1 0 10 20 30 0 1 1 1 1 1
0 1 0 10 20 30 0 0 1 1 1 1
0 1 0 10 20 30 0 0 0 1 1 1
0 1 0 10 20 30 0 0 0 0 1 1
0 1 0 10 20 30 0 0 0 0 0 1
0 0 1 10 20 30 1 1 1 1 1 1
0 0 1 10 20 30 1 0 1 1 1 1
0 0 1 10 20 30 1 0 0 1 1 1
0 0 1 10 20 30 1 0 0 0 1 1
0 0 1 10 20 30 1 0 0 0 0 1
", header=TRUE, na.strings=NA)
my.data$my.group <- which(my.data[,1:3]==1, arr.ind=TRUE)[,2]
my.data
my.sums <- t(sapply(split(my.data[,7:(ncol(my.data)-1)], my.data$my.group), function(i) sapply(seq(2, ncol(i), 2), function(j) sum(i[,c((j-1),j)], na.rm=TRUE))))
my.sums
# [,1] [,2] [,3]
# 1 5 3 0
# 2 1 5 9
# 3 6 5 9
Here's a pretty general expression that you can probably simplify if you want it to match your specific data dimensions/column names/etc:
library(data.table)
dt = data.table(my.data)
dt[, lapply(1:(ncol(.SD)/2), function(x) sum(.SD[[2*x-1]], .SD[[2*x]])),
by = eval(grep('^r', names(dt), value = TRUE)),
.SDcols = grep('^v', names(dt), value = TRUE)]
# r1 r2 r3 V1 V2 V3
#1: 1 0 0 5 3 0
#2: 0 1 0 1 5 9
#3: 0 0 1 6 5 9