0

I have a long dataframe in R right now. I am working from another similar post (Creating co-occurrence matrix). The example dataset is this:

#read in your data
dat <- read.table(text="TrxID Items Quant
Trx1 A 3
Trx1 B 1
Trx1 C 1
Trx2 E 3
Trx2 B 1
Trx3 B 1
Trx3 C 4
Trx4 D 1
Trx4 E 1
Trx4 A 1
Trx5 F 5
Trx5 B 3
Trx5 C 2
Trx5 D 1", header=T)

They counted the number of co-occurances and built a final matrix that looked like this:

   A B C D E F
A  2 1 1 0 1 1
B  1 4 3 1 1 0
C  1 3 3 1 0 0
D  1 1 1 2 1 1
E  1 1 0 1 2 0
F  0 1 1 1 0 1

using this code:

crossprod(table(dat[1:2]))

Note - in the previous example they wanted the diagonal to be 0, but I do not here, so I have changed the example so the diagonal is not set to 0 here.

I want to do essentially the same thing as above, but instead of filling the cells of the matrix with the co-occurrence counts, I want the values to be the sum of the variable Quant across co-occurring records. In this case, the final matrix should look like:

   A B  C  D E F
A  4 4  4  2 2 0
B  4 6  12 4 4 8
C  4 12 7  3 0 7
D  2 4  3  2 2 6
E  2 4  0  2 4 0
F  0 8  7  6 0 5

Thanks in advance for suggestions. I generally use tidyverse personally.

rt11
  • 45
  • 4
  • how do you get A-B to be 4 for example? – Onyambu Nov 20 '20 at 02:56
  • Because A and B only co-occur in/share one treatment - Trx1, where A has a Quant value of 3 and B has a Quant value of 1, so 1+3 = 4. B-C is 12 because the co-occurrences are Trx1, Trx13, and Trx5, so adding up the Quant values for these records gives 1+1+3+1+4+2 = 12. – rt11 Nov 20 '20 at 15:34

1 Answers1

1

You could do:

e <- crossprod(s<-xtabs(Quant~., dat), s>0)
e + t(e) - diag(diag(e))

   Items
Items A  B  C D E F
    A 4  4  4 2 2 0
    B 4  6 12 4 4 8
    C 4 12  7 3 0 7
    D 2  4  3 2 2 6
    E 2  4  0 2 4 0
    F 0  8  7 6 0 5

Note that diag(e) is the same as xtabs(Quant~Items, dat)

Onyambu
  • 67,392
  • 3
  • 24
  • 53
  • 1
    @rt11 No. you should probably break it down into two lines of code `s<-xtabs(Quant~., dat); e <- crossprod(s, s>0)` – Onyambu Nov 20 '20 at 16:07