Convert co-occurrence dataframe to square matrix

Question

Say I have this dataframe:

    d <- structure(list(A = c(1L, 1L, 1L, 0L, 1L, 1L, 1L), B = c(1L, 1L, 
1L, 1L, 1L, 1L, 1L), C = c(0L, 0L, 1L, 0L, 0L, 0L, 0L), D = c(0L, 
0L, 0L, 0L, 0L, 0L, 0L), E = c(0L, 0L, 0L, 0L, 0L, 0L, 0L), F = c(0L, 
0L, 0L, 0L, 0L, 0L, 0L), G = c(0L, 0L, 0L, 0L, 0L, 0L, 0L), H = c(0L, 
1L, 0L, 0L, 1L, 1L, 0L)), row.names = c(NA, -7L), class = c("tbl_df", 
"tbl", "data.frame"))



      A     B     C     D     E     F     G     H
  <int> <int> <int> <int> <int> <int> <int> <int>
1     1     1     0     0     0     0     0     0
2     1     1     0     0     0     0     0     1
3     1     1     1     0     0     0     0     0
4     0     1     0     0     0     0     0     0
5     1     1     0     0     0     0     0     1
6     1     1     0     0     0     0     0     1
7     1     1     0     0     0     0     0     0

I wish to turn this into a square matrix of 8x8 (A:H x A:H). Each cell equal the total times that each two letters appear together. For example, The cell [A,B] should have a value of 6 as the columns A and B both have a 1 in 6 out of the 7 rows. The cell [B,A] has the same value. The output should be:

I can do this in a loop like this:

mat <- matrix(NA,8,8)
for(i in 1:8) { 
  for(j in 1:8) {
    mat[i,j] <- sum(mat1[,i]==1 & mat1[,j]==1)
    mat[j,i] <- sum(mat1[,j]==1 & mat1[,i]==1)
  }}
diag(mat)<-0
rownames(mat)<-colnames(mat)<-LETTERS[1:8]
mat

Desired output:

  A B C D E F G H
A 0 6 1 0 0 0 0 3
B 6 0 1 0 0 0 0 3
C 1 1 0 0 0 0 0 0
D 0 0 0 0 0 0 0 0
E 0 0 0 0 0 0 0 0
F 0 0 0 0 0 0 0 0
G 0 0 0 0 0 0 0 0
H 3 3 0 0 0 0 0 0

But this is slow when doing very large matrices. Is there a way of doing this quicker ?

`m <- crossprod(as.matrix(d))`; `diag(m) <- 0` – Henrik Nov 14 '18 at 21:50 — Henrik, Nov 14 '18 at 21:50

IRTFM · Accepted Answer · 2018-11-15T00:04:39.663

2

What you described in words sounded like ordinary matrix multiplication forllowed by setting the diag to 0:

temp <- t(as.matrix(d)) %*% as.matrix(d)
diag(temp) <- 0


> temp
  A B C D E F G H
A 0 6 1 0 0 0 0 3
B 6 0 1 0 0 0 0 3
C 0 1 0 0 0 0 0 0
D 0 0 0 0 0 0 0 0
E 0 0 0 0 0 0 0 0
F 0 0 0 0 0 0 0 0
G 0 0 0 0 0 0 0 0
H 3 3 0 0 0 0 0 0

The tcrossprod function is probably even faster, but either of these methods will surely out-perform your nested loop.

edited Nov 15 '18 at 00:04

answered Nov 14 '18 at 22:00

IRTFM

258,963
21
364
487

Did you mean the `crossprod` function? – Manos Papadakis Nov 16 '18 at 19:38

Convert co-occurrence dataframe to square matrix

1 Answers1