Build a sparse matrix with items coexistence frequency (to analyze cross-selling of products)

Question

I stuck with creating a sparse matrix, in which I can count cross-selling frequency of products based on the cart and product ids.

Sample data frame:

x = data.frame(
      cart_id = c("1","1","1","2","2","3","4","5","5","6"),
      product_id = c("A","B","C","D","A","F","G","A","C","F")
)

The most desirable output: a sparse matrix with the number of times that two of products appeared in the same cart.

Any hints?

EDIT:

Both answers solve the problem.

Answer 1

This is a very interesting problem/application!!!

Your two-column data.frame x shows what product is in what cart, but you are interested in an event that products i and j fall into the same cart. You don't care what particular cart it is; instead, you want to count how many times such an event happens.

Certainly, your expected output is a contingency table (a square matrix with counts). However, the counts must be first computed, which is not a trivial task. The following well-commented function does this.

Contingency <- function (product_id, cart_id) {
  ## unique product ID
  ProductID <- unique(product_id)
  ## let's use a consecutive numeric ID for product
  ProductIDnum <- match(product_id, ProductID)
  ## split products by cart
  CartItems <- unname(split(ProductIDnum, cart_id))
  ## number of products in each cart
  nItemsPerCart <- lengths(CartItems)
  ## we are only interested in carts with 2+ different products
  CartItems <- CartItems[nItemsPerCart >= 2]
  CartItems <- lapply(CartItems, sort)
  ## an event: a pair of products (i, j) fall into one same cart
  ## (note that we don't care which particular cart it is)
  ## here, `Events` is a 2-column matrix where each row is an event
  ## this matrix will have duplicated rows so that we can `aggregate`
  Events <- t(do.call("cbind", lapply(CartItems, combn, m = 2)))
  ## aggregate: how many times does each event happen?
  Freq <- aggregate(rep(1, nrow(Events)), data.frame(Events), sum)
  ## (i, j, x) triplet for a "TsparseMatrix"
  i <- Freq[[1]]
  j <- Freq[[2]]
  x <- Freq[[3]]
  ## the dimension of the square matrix
  n <- length(ProductID)
  Matrix::sparseMatrix(i = i, j = j, x = x, symmetric = TRUE, dims = c(n, n),
                       dimnames = list(ProductID, ProductID))
}

Now we can apply it to your dataset x.

mat <- Contingency(x$product_id, x$cart_id)
#6 x 6 sparse Matrix of class "dsCMatrix"
#  A B C D F G
#A . 1 2 1 . .
#B 1 . 1 . . .
#C 2 1 . . . .
#D 1 . . . . .
#F . . . . . .
#G . . . . . .

## dense form (not recommended if there are lots of products)
as.matrix(mat)
#  A B C D F G
#A 0 1 2 1 0 0
#B 1 0 1 0 0 0
#C 2 1 0 0 0 0
#D 1 0 0 0 0 0
#F 0 0 0 0 0 0
#G 0 0 0 0 0 0

---

You may also use xtabs and crossprod:

mat <- Matrix::crossprod(xtabs(~ ., data = x, sparse = TRUE))
#6 x 6 sparse Matrix of class "dsCMatrix"
#  A B C D F G
#A 3 1 2 1 . .
#B 1 1 1 . . .
#C 2 1 2 . . .
#D 1 . . 1 . .
#F . . . . 2 .
#G . . . . . 1

The only thing left is to set diagonal entries to zeros:

diag(mat) <- 0
mat
#  A B C D F G
#A 0 1 2 1 . .
#B 1 0 1 . . .
#C 2 1 0 . . .
#D 1 . . 0 . .
#F . . . . 0 .
#G . . . . . 0

But note that "diag<-" is not doing a very neat job here, as the replacement 0 is not treated as zero, in the storage sense.

---

Damm!!! I just found a dupe for this: Creating co-occurrence matrix.