Any way to use data.table or dplyr to solve the below?
library(data.table)
(DT = data.table(a = LETTERS[c(1, 1:3, 8)], b = c(2, 4:7),
c = as.factor(c("bob", "mary", "bob", "george", "alice")), key="a"))
Returns:
# a b c
# 1: A 2 bob
# 2: A 4 mary
# 3: B 5 bob
# 4: C 6 george
# 5: H 7 alice
Would like to get this:
# alice bob george mary
# 1: A NA 2 NA NA
# 2: A NA NA NA 4
# 3: B NA 5 NA NA
# 4: C NA NA 6 NA
# 5: H 7 NA NA NA
This is similar to creating dummy variables.
uc <- sort(unique(as.character(DT$c)))
DT[,(uc):=lapply(uc,function(x)ifelse(c==x,b,NA))][,c('b','c'):=NULL]
I've heard bad things about ifelse
, so a speedier route may be
uc <- sort(unique(as.character(DT$c)))
is <- 1:nrow(DT)
js <- as.character(DT$c)
vs <- DT$b
DT[,(uc):=NA_integer_]
for (i in is) set(DT,i=is[i],j=js[i],value=vs[i])
DT[,c('b','c'):=NULL]
Just using the idea of dummy variables from Frank:
df1 <- cbind( a = DT$a, as.data.frame( model.matrix(a ~ c - 1, data = DT ) * DT$b ))
df1[df1==0] <- NA
names(df1) <- c("a", levels(DT$c))
# a alice bob george mary
# 1 A NA 2 NA NA
# 2 A NA NA NA 4
# 3 B NA 5 NA NA
# 4 C NA NA 6 NA
# 5 H 7 NA NA NA
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With