Search code examples
rdata.tablereshape2

dcast a data.table in r

a1 <- data.table(id=c(1,1,1,1,2,2,2,3,3),
                 var=c("6402","1","6302","3","6406","6406","2","1","1"))
b1 <- data.table(var=c("6402","6406","6302"),
                 txt=c("A","B","A"))
mm <- b1[a1,on=.(var)]
dcast(mm,id~txt,function(x) any(!is.na(x)),fill=NA)

desired_output <- data.table(id=c(1,2,3),
                 A=c(T,F,F),
                 B=c(F,T,F))

How can I get the desired_output? Somehow the aggregating function seems to be playing games with me...

Solution

  • Make id factor variable and use dcast with drop = FALSE after dropping NA rows.

    library(data.table)
mm$id <- factor(mm$id)
dcast(na.omit(mm),id~txt,function(x) any(!is.na(x)), drop = FALSE)

#   id     A     B
#1:  1  TRUE FALSE
#2:  2 FALSE  TRUE
#3:  3 FALSE FALSE