How can I change the data.table (long in width) without executing a function like sum or value?
How can I change the shape data.table
(long in width) without doing a function like sum
or mean
? I was looking at dcast / melt / reshape / etc. But I am not getting the results I want.
This is my data:
DT <- data.table(id = c("1","1","2","3"), score = c("5", "4", "5", "6"))
Original format:
> DT
id score
1 5
1 4
2 5
3 6
Desired format:
id score1 score2
1 5 4
2 5 NA
3 6 NA
Now I am doing the trick with:
DT <- DT[, list(list(score)), by=id]
But then the content of the first cell looks like this:
c("5", "4")
And I need to split it (I am using a package splitstackshape
):
DT <- cSplit(DT, "V1", ",")
This is probably not the most efficient method ... Which is better?
source to share
You can use getanID
to create a unique .id
grouping for a variable id
. Then try dcast.data.table
(or just dcast
from versions 1.9.5 and up) and change the column names as needed usingsetnames
library(splitstackshape)
res <- dcast(getanID(DT, 'id'), id~.id,value.var='score')
setnames(res, 2:3, paste0('score', 1:2))[]
# id score1 score2
#1: 1 5 4
#2: 2 5 NA
#3: 3 6 NA
Or using only data.table
dcast(DT[, .id:=paste0('score', 1:.N), by=id],
id~.id, value.var='score')
# id score1 score2
#1: 1 5 4
#2: 2 5 NA
#3: 3 6 NA
Or from the code you used (fewer characters)
cSplit(DT[, toString(score), by=id], 'V1', ',')
# id V1_1 V1_2
#1: 1 5 4
#2: 2 5 NA
#3: 3 6 NA
source to share