Creating co-occurrence matrix

Question

I'm trying to solve the problem of having a co-occurence matrix. I have a datafile of transactions and items, and I want to see a matrix of the number of transactions where items appear together.

I'm a newbie in R programming and I'm having some fun finding out all the shortcuts that R has, rather than creating specific loops (I used to use C years ago and only sticking to Excel macros and SPSS now). I have checked the solutions here, but haven't found one that works (the closest is the solution given here: Co-occurrence matrix using SAC? - but it produced an error message when I used projecting_tm, I suspected that the cbind wasn't successful in my case.

Essentially I have a table containing the following:

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, etc.

I want to create something like:

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

What I did was (and you'd probably laugh at my rookie R approach):

library(igraph)
library(tnet)

trx <- read.table("FileName.txt", header=TRUE) 
transID <- t(trx[1])
items <- t(trx[2])

id_item <- cbind(items,transID)
item_item <- projecting_tm(id_item, method="sum")
item_item <- tnet_igraph(item_item,type="weighted one-mode tnet")
item_matrix <-get.adjacency(item_item,attr="weight")
item_matrix

As mentioned above the cbind was probably unsuccessful, so the projecting_tm couldn't give me any result.

Any alternative approach or a correction to my method?

Your help would be much appreciated!

Radiothorium

Related thread here. – Maltese

I'm dealing with similar transaction data now, and I just wanted to thank @Radiothorium (and the related thread posted by hhh is super helpful too) – Nazler

there is a small mistake in the example of what you want to create - the B row and F column says 0. it should say 1. Confused me for sometime. – Copybook

I wonder if there is a way to generalize this problem. Rather than co-occurence of two TrxID , can a matix be generated with co-ocurence of n>2 (3 or more) co-occurrences if TrxIDs. Is there a term for such a matrix ? Or can any of you guide me to a related question. Thanks! Related thread is #51923615 . – Contradictory

Answer 1

I'd use a combination of the reshape2 package and matrix algebra:

#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)

#making the boolean matrix   
library(reshape2)
dat2 <- melt(dat)
w <- dcast(dat2, Items~TrxID)
x <- as.matrix(w[,-1])
x[is.na(x)] <- 0
x <- apply(x, 2,  function(x) as.numeric(x > 0))  #recode as 0/1
v <- x %*% t(x)                                   #the magic matrix 
diag(v) <- 0                                      #repalce diagonal
dimnames(v) <- list(w[, 1], w[,1])                #name the dimensions
v

For the graphing maybe...

g <- graph.adjacency(v, weighted=TRUE, mode ='undirected')
g <- simplify(g)
# set labels and degrees of vertices
V(g)$label <- V(g)$name
V(g)$degree <- degree(g)
plot(g)

Breve

Answer 2

Using "dat" from either of the answers above, try crossprod and table:

V <- crossprod(table(dat[1:2]))
diag(V) <- 0
V
#      Items
# Items A B C D E F
#     A 0 1 1 1 1 0
#     B 1 0 3 1 1 1
#     C 1 3 0 1 0 1
#     D 1 1 1 0 1 1
#     E 1 1 0 1 0 0
#     F 0 1 1 1 0 0

Bufordbug

+1 as this was the most computationally efficient for my current case which had a LOT of what would be called "transactions" in OPs working example – Emanative

wonderful, didn't know that vectorial product of a matrix by itself actually meant this – Dougald

Answer 3

For efficiency reasons, especially on sparse data, I would recommend using a sparse matrix.

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)

library("Matrix")

# factors for indexing matrix entries and naming dimensions
trx.fac <- factor(dat[,1])
itm.fac <- factor(dat[,2])

s <- sparseMatrix(
        as.numeric(trx.fac), 
        as.numeric(itm.fac),
        dimnames = list(
                as.character(levels(trx.fac)), 
                as.character(levels(itm.fac))),
        x = 1)

# calculating co-occurrences
v <- t(s) %*% s

# setting transactions counts of items to zero
diag(v) <- 0
v

I was giving each solution posted in this thread a try. None of them worked with large matrices (I was working with a 1,500 x 2,000,000 matrix).

A little bit off-topic: after calculating a co-occurrence matrix, I usually want to calculate the distance between individual items. The cosine similarity / distance can be calculated efficiently on the co-occurrence matrix like this:

# cross-product of vectors (numerator)
num <- v %*% v

# square root of square sum of each vector (used for denominator)
srss <- sqrt(apply(v^2, 1, sum))

# denominator
den <- srss %*% t(srss)

# cosine similarity
v.cos.sim <- num / den

# cosine distance
v.cos.dist <- 1 - v.cos.sim

Duodenal

Answer 4

I would use xtabs for this:

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)


term_doc <- xtabs(~ TrxID + Items, data=dat, sparse = TRUE)
co_occur <- crossprod(term_doc, term_doc)
diag(co_occur) <- 0
co_occur

I threw in the sparse = TRUE to show that this can work for very large data sets.

Henricks

Answer 5

This is actually very easy and clean if you create a bipartite graph first, where the top nodes are the transactions and the bottom nodes are the items. Then you create a projection to the bottom nodes.

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)

library(igraph)
bip <- graph.data.frame(dat)
V(bip)$type <- V(bip)$name %in% dat[,1]

## sparse=TRUE is a good idea if you have a large matrix here
v <- get.adjacency(bipartite.projection(bip)[[2]], attr="weight", sparse=FALSE)

## Need to reorder if you want it alphabetically
v[order(rownames(v)), order(colnames(v))]

#   A B C D E F
# A 0 1 1 1 1 0
# B 1 0 3 1 1 1
# C 1 3 0 1 0 1
# D 1 1 1 0 1 1
# E 1 1 0 1 0 0
# F 0 1 1 1 0 0

Zoography