Create a two-mode frequency matrix in R

Question

I have a data frame, which 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

There are over 400 variables in the dataset. This is just an example. I need to create a simple frequency matrix in R (excluding the case numbers), but the table function doesn't work. Specifically, I'm looking to cross-tabulate a portion of the columns to create a two-mode matrix of frequencies. The table should look like this:

       Var1    Var2
Resp1    3       1
Resp2    3       2

In Stata, the command is:

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

Mcmichael

Answer 1

This one should work for any number of Var & 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, provided in the comment by G.Grothendieck:

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

Cutler

One further simplification would be m <- as.matrix(d) since the greps will never match the first column anyways. – Germin

Thank you! This is so helpful. How would I reference column numbers, rather than column names, using the crossprod command? – Mcmichael

@jj987246, just use vectors containing column numbers, e.g. crossprod(m[,1:4],m[,5:8]) – Cutler

Answer 2

I'm sure there's 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

Morette

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)

Autocorrelation