R split data to frequency

Question

I have a data set like this, with a variable ("item") that contains comma-separated codes:

id  item
1    102, 103,401,
2    108,102,301
3    103, 108 , 405, 505, 708

For each id, I would like to get the frequencies of each separate item, like this:

id  102  103   104   108  301 401 ...
1    1    1                    1
2    1                 1    1
3         1            1

How can I do that?

Answer 1

We can do this with mtabulate from qdapTools

library(qdapTools)
cbind(dat['id'], mtabulate(strsplit(dat$item, '\\s*,\\s*')))
#  id 102 103 108 301 401 405 505 708
#1  1   1   1   0   0   1   0   0   0
#2  2   1   0   1   1   0   0   0   0
#3  3   0   1   1   0   0   1   1   1

NOTE: Data taken from @thelatemail's post.

---

Or another option (if we need a sparseMatrix)

library(Matrix)
#split the 'item' column to `list`
lst <- strsplit(dat$item, '\\s*,\\s*')
#get the `unique` elements after `unlist`ing.
Un1 <- sort(unique(unlist(lst)))
#create a `sparseMatrix` by specifying the row
#column index along with dim names (if needed)
sM <-  sparseMatrix(rep(dat$id, lengths(lst)), 
            match(unlist(lst), Un1), x= 1, 
             dimnames=list(dat$id, Un1))
sM
#    3 x 8 sparse Matrix of class "dgCMatrix"
#   102 103 108 301 401 405 505 708
#1   1   1   .   .   1   .   .   .
#2   1   .   1   1   .   .   .   .
#3   .   1   1   .   .   1   1   1

It can be converted to matrix by wrapping with as.matrix

as.matrix(sM)
#   102 103 108 301 401 405 505 708
#1   1   1   0   0   1   0   0   0
#2   1   0   1   1   0   0   0   0
#3   0   1   1   0   0   1   1   1

Answer 2

Use strsplit and then take advantage of a factor to make sure all the column counts are included.

spl <- strsplit(dat$item,"\\s*,\\s*")
ulevs <- sort(unique(unlist(spl)))
tab <- t(vapply(
  spl, 
  function(x) table(factor(x,levels=ulevs)),
  FUN.VALUE=numeric(length(ulevs))
))
cbind(dat["id"],tab)

#  id 102 103 108 301 401 405 505 708
#1  1   1   1   0   0   1   0   0   0
#2  2   1   0   1   1   0   0   0   0
#3  3   0   1   1   0   0   1   1   1

Data used:

dat <- read.table(text="id;item
1;102, 103,401,
2;108,102,301
3;103, 108 , 405, 505, 708",header=TRUE,sep=";",stringsAsFactors=FALSE)

Answer 3

You can do this using strsplit function. Below is my solution

library(data.table)
id <- c(1:3)
item <- c("102, 103,401",  "108,102,301", "103, 108 , 405, 505, 708")
dataT <- data.table(id, item)

reqCol <- unlist(strsplit(dataT$item, split=","))
reqCol <- gsub(" ", "", reqCol)
reqCol <-  unique(reqCol)
reqColNames <- paste0("Col_", reqCol)

for(i in 1:length(reqCol)){
    expr1 <- parse(text = paste0(reqColNames[i], ":=0"))
    expr2 <- parse(text = paste0(reqColNames[i], ":=1"))
    dataT[, eval(expr1)]
    rowIndex <- grep(reqCol[i], dataT$item)
    dataT[rowIndex, eval(expr2)] 
}

I have used data.table instead of data.frame, because data.table is very fast as compared to data.frame.