create a scoring matrix from two dataframes

Question

I am trying to compare sets of variables(X) that are stored in two dataframes (foo, bar). Each X is a unique independent variable that has up to 10 values of Y associated with it. I would like to compare every foo.X with every bar.X by comparing the number of Y values they have in common - so the output could be a matrix with axes of foo.x by bar.x in length.

this simple example of foo and bar would want to return a 2x2 matrix comparing a,b with c,d:

foo <- data.frame(x= c('a', 'a', 'a', 'b', 'b', 'b'), y=c('ab', 'ac', 'ad', 'ae', 'fx', 'fy'))
bar <- data.frame(x= c('c', 'c', 'c', 'd', 'd', 'd'), y=c('ab', 'xy', 'xz', 'xy', 'fx', 'xz'))

EDIT:

I've left the following code for other newbies to learn from (for loops are effectvie but probably very suboptimal), but the two solutions below are effective. In particular Ramnath's use of data.table is very effective when dealing with very large dataframes.

store the dataframes as lists where the values of y are stored using the stack function

foo.list <- dlply(foo, .(x), function(x) stack(x, select = y))
bar.list <- dlply(bar, .(x),function(x)  stack(x, select = y))

write a function for comparing membership in the two stacked lists

comparelists <- function(list1, list2) {
  for (i in list1){ 
    for (j in list2){
      count <- 0
      if (i[[1]] %in% j[[1]]) count <- count + 1
    }
  }
  return count
  }

write an output matrix

output.matrix <- matrix(1:length(foo.list), 1:length(bar.list))
for (i in foo.list){
  for (j in bar.list){
    output.matrix[i,j] <- comparelists(i,j)

    }

}

Josh O'Brien Josh O'Brien · Accepted Answer · 2012-01-05T21:12:45

There must be a hundred ways to do this; here is one that feels relatively straightforward to me:

library(reshape2)
foo <- data.frame(x = c('a', 'a', 'a', 'b', 'b', 'b'), 
                  y = c('ab', 'ac', 'ad', 'ae', 'fx', 'fy'))
bar <- data.frame(x = c('c', 'c', 'c', 'd', 'd', 'd'), 
                  y = c('ab', 'xy', 'xz', 'xy', 'fx', 'xz'))

# Create a function that counts the number of common elements in two groups
nShared <- function(A, B) {
    length(intersect(with(foo, y[x==A]), with(bar, y[x==B])))
}

# Enumerate all combinations of groups in foo and bar
(combos <- expand.grid(foo.x=unique(foo$x), bar.x=unique(bar$x)))
#   foo.x bar.x
# 1     a     c
# 2     b     c
# 3     a     d
# 4     b     d

# Find number of elements in common among all pairs of groups
combos$n <- mapply(nShared, A=combos$foo.x, B=combos$bar.x)

# Reshape results into matrix form
dcast(combos, foo.x ~ bar.x)
#   foo.x c d
# 1     a 1 0
# 2     b 0 1

create a scoring matrix from two dataframes

2 Answers