Clever Geek Handbook

R special reformatting of data tables

I have a data table with a key id and a text value column. Key ID is not unique (some lines have the same ID, there can be from 1 to 8 lines with the same ID). I need a data table with a unique key id and 8 columns.

For example, what I have:

require(data.table)
set.seed(1)
out<-data.table(id=c(rep("id1",3),rep("id2",2),"id3"),value=paste("value",round(rnorm(6,0,100))))
out
#     id     value
# 1: id1  value 49
# 2: id1  value 74
# 3: id1  value 58
# 4: id2 value -31
# 5: id2 value 151
# 6: id3  value 39

Desired output:

data.table(id=c("id1","id2","id3"),value1=c("value -63","value 160","value -82"),value2=c("value 18","value 33",NA),value3=c("value -84",NA,NA))
#    id    value1   value2    value3
#1: id1 value -63 value 18 value -84
#2: id2 value 160 value 33        NA
#3: id3 value -82       NA        NA
+3


source to share


2 answers


You can try: ( setnames

part contributed by @David Arenburg)

 res <- setnames(dcast.data.table(out[, N:=1:.N,by=id], id~N, value.var="value"),
                                                     2:4, paste0("value", 1:3))
 res   
 #     id   value1    value2        value3
 # 1: id1 value -63 value 18 value -84
 # 2: id2 value 160 value 33        NA
 # 3: id3 value -82       NA        NA


Or the compact version as suggested by @David Arenburg

 dcast.data.table(out[, N := paste0('value', 1:.N), by = id],
                                    id ~ N, value.var = "value")
+4


source


You would consider aggregate

:

aggregate(value~id,data=out,FUN=paste,collapse=" ")

id                        value
1 id1 value -63 value 18 value -84
2 id2           value 160 value 33
3 id3                    value -82


It is not identical to your desired output, but it is still a trick.

0


source






More articles:


All Articles