Vasily A Vasily A - 1 month ago 9
R Question

best way to transpose data.table

I often need to transpose a

data.table
, every time it takes several lines of code and I am wondering if there's any better solution than mine.

if we take sample table

library(data.table)
mydata <- data.table(col0=c("row1","row2","row3"),
col1=c(11,21,31),
col2=c(12,22,32),
col3=c(13,23,33))

mydata
# col0 col1 col2 col3
# row1 11 12 13
# row2 21 22 23
# row3 31 32 33


and just transpose it with
t()
, it will be transposed to the matrix with conversion to
character
type, while applying
data.table
to such matrix will lose
row.names
:

t(mydata)
# [,1] [,2] [,3]
# col0 "row1" "row2" "row3"
# col1 "11" "21" "31"
# col2 "12" "22" "32"
# col3 "13" "23" "33"

data.table(t(mydata))
# V1 V2 V3
# row1 row2 row3
# 11 21 31
# 12 22 32
# 13 23 33


so I had to write a function for this:

tdt <- function(inpdt){
transposed <- t(inpdt[,-1,with=F]);
colnames(transposed) <- inpdt[[1]];
transposed <- data.table(transposed, keep.rownames=T);
setnames(transposed, 1, names(inpdt)[1]);
return(transposed);
}

tdt(mydata)
# col0 row1 row2 row3
# col1 11 21 31
# col2 12 22 32
# col3 13 23 33


is there anything I could optimize here or do it in "nicer" way?

Answer

Why not just melt and dcast the data.table?

library(reshape2)
dcast.data.table(melt(mydata, id.vars = "col0"), variable ~ col0)
#    variable row1 row2 row3
# 1:     col1   11   21   31
# 2:     col2   12   22   32
# 3:     col3   13   23   33
Comments