Create a two-mode frequency matrix in R

Question

I have a data frame that looks something like this:

CASENO Var1 Var2 Resp1 Resp2 1 1 0 1 1 2 0 0 0 0 3 1 1 1 1 4 1 1 0 1 5 1 0 1 0 

The data set contains more than 400 variables. This is just an example. I need to create a simple frequency matrix in R (excluding case numbers), but the table function does not work. In particular, I am looking to cross-tabulate part of the columns to create a two-mode frequency matrix. The table should look like this:

  Var1 Var2 Resp1 3 1 Resp2 3 2 

In the Stata command:

 gen var = 1 if Var1==1 replace var= 2 if Var2==1 gen resp = 1 if Resp1==1 replace resp = 2 if Resp2==1 tab var resp 

Answer 1

This should work for any number of Var and Resps:

 d <- structure(list(CASENO = 1:5, Var1 = c(1L, 0L, 1L, 1L, 1L), Var2 = c(0L, 0L, 1L, 1L, 0L), Resp1 = c(1L, 0L, 1L, 0L, 1L), Resp2 = c(1L, 0L, 1L, 1L, 0L)), .Names = c("CASENO", "Var1", "Var2", "Resp1", "Resp2"), class = "data.frame", row.names = c(NA, -5L)) m <- as.matrix(d[,-1]) m2 <- t(m) %*% m rnames <- grepl('Resp',rownames((m2))) cnames <- grepl('Var',colnames((m2))) m2[rnames,cnames] 

[UPDATE] A more elegant version given in a comment by G.Grothendieck:

 m <- as.matrix(d[,-1]) cn <- colnames(m); crossprod(m[, grep("Resp", cn)], m[, grep("Var", cn)]) 

Answer 2

I am sure there is another way, but you could do this:

 library(reshape2) library(plyr) df1 <- melt(df[,-1],id=1:2) ddply(df1,.(variable),summarize, Var1 = sum(value==1&Var1==1), Var2 = sum(value==1&Var2==1)) # variable Var1 Var2 # 1 Resp1 3 1 # 2 Resp2 3 2 

Answer 3

Here is an approach using xtabs .

 # get names of non "variables" not_vars <- c("Resp1", "Resp2", "CASENO") # get names of "variables" vars <- as.matrix(d[,!names(d) %in% not_vars]) # if you have many more than 2 response variables, this could get unwieldy result <- rbind( xtabs( vars ~ Resp1, data=d, exclude=0), xtabs( vars ~ Resp2, data=d, exclude=0)) # give resulting table appropriate row names. rownames(result) <- c("Resp1", "Resp2") # Var1 Var2 #Resp1 3 1 #Resp2 3 2 

sample data:

 d <- read.table(text=" CASENO Var1 Var2 Resp1 Resp2 1 1 0 1 1 2 0 0 0 0 3 1 1 1 1 4 1 1 0 1 5 1 0 1 0", header=TRUE)