Cross tabs - need to aggregate several columns based on certain values in other columns

Question

My data looks like this, all columns with binary presence/absence data:

POP1   POP2   POP3    T1    T2    T3    T4    T5    T6    T7    T8    T9
 1      1      0       1     1     1     1     0     1     0     0     1
 1      0      1       0     1     1     0     1     1     0     1     1
 1      1      0       1     1     1     1     0     0     1     0     1
 0      0      0       0     1     1     0     1     0     1     1     0
 1      0      1       0     0     1     1     1     0     1     1     0
 0      1      0       0     1     1     1     0     0     0     0     1
 0      1      0       1     1     0     1     0     0     0     0     0
 1      1      1       0     1     0     0     0     1     0     0     0
 0      0      0       0     1     1     1     1     1     0     0     1
 1      0      0       1     0     1     0     1     0     1     1     1
 1      1      0       0     1     0     1     0     0     1     0     0 
 1      0      1       0     1     1     1     0     1     0     1     0
 0      1      0       1     1     1     1     0     0     0     0     0
 1      0      0       0     1     1     0     0     0     0     1     1

The POP1:POP3 are populations, and I need counts of all 1's for all T1:T9 for all POP1=1, POP2=1 and POP3=1. I need a table that crosstabulates my data like this:

         T1    T2    T3    T4    T5    T6    T7    T8    T9
POP1=1    3     9     7     5     3     4     4     5     5
POP2=1    4     7     8     6     2     3     2     0     3
POP3=1    0     3     4     2     2     2     1     3     1

Don't bother checking the aggregated counts, they're not necessarily correct. I've have tried lots of synthaxes without getting what I want. Thankful for some guidance.

Answer 1

You need the matrix multiplication %*% here:

t(df[1:3]) %*% as.matrix(df[4:12]) 

     T1 T2 T3 T4 T5 T6 T7 T8 T9
POP1  3  7  7  5  3  4  4  5  5
POP2  4  7  4  6  0  2  2  0  3
POP3  0  3  3  2  2  3  1  3  1

Answer 2

df = structure(list(POP1 = c(1L, 1L, 1L, 0L, 1L, 0L, 0L, 1L, 0L, 1L, 
1L, 1L, 0L, 1L), POP2 = c(1L, 0L, 1L, 0L, 0L, 1L, 1L, 1L, 0L, 
0L, 1L, 0L, 1L, 0L), POP3 = c(0L, 1L, 0L, 0L, 1L, 0L, 0L, 1L, 
0L, 0L, 0L, 1L, 0L, 0L), T1 = c(1L, 0L, 1L, 0L, 0L, 0L, 1L, 0L, 
0L, 1L, 0L, 0L, 1L, 0L), T2 = c(1L, 1L, 1L, 1L, 0L, 1L, 1L, 1L, 
1L, 0L, 1L, 1L, 1L, 1L), T3 = c(1L, 1L, 1L, 1L, 1L, 1L, 0L, 0L, 
1L, 1L, 0L, 1L, 1L, 1L), T4 = c(1L, 0L, 1L, 0L, 1L, 1L, 1L, 0L, 
1L, 0L, 1L, 1L, 1L, 0L), T5 = c(0L, 1L, 0L, 1L, 1L, 0L, 0L, 0L, 
1L, 1L, 0L, 0L, 0L, 0L), T6 = c(1L, 1L, 0L, 0L, 0L, 0L, 0L, 1L, 
1L, 0L, 0L, 1L, 0L, 0L), T7 = c(0L, 0L, 1L, 1L, 1L, 0L, 0L, 0L, 
0L, 1L, 1L, 0L, 0L, 0L), T8 = c(0L, 1L, 0L, 1L, 1L, 0L, 0L, 0L, 
0L, 1L, 0L, 1L, 0L, 1L), T9 = c(1L, 1L, 1L, 0L, 0L, 1L, 0L, 0L, 
1L, 1L, 0L, 0L, 0L, 1L)), .Names = c("POP1", "POP2", "POP3", 
"T1", "T2", "T3", "T4", "T5", "T6", "T7", "T8", "T9"), class = "data.frame", 
row.names = c(NA, -14L))

library(reshape2)
df = melt(df, id.vars = colnames(df)[-(1:3)] )

do.call(rbind, lapply(split(df, df$variable), function(x)
                    apply(x[x$value == 1,1:9], 2, function(y) sum(y))))

#     T1 T2 T3 T4 T5 T6 T7 T8 T9
#POP1  3  7  7  5  3  4  4  5  5
#POP2  4  7  4  6  0  2  2  0  3
#POP3  0  3  3  2  2  3  1  3  1