How does one take the transpose of an R data.table? Why doesn't t() work?

Question

I have the following R data.table

library(data.table)
mtcars = as.data.table(mtcars)
dt = colSums(mtcars)

> dt
     mpg      cyl     disp       hp     drat       wt     qsec       vs 
 642.900  198.000 7383.100 4694.000  115.090  102.952  571.160   14.000 
      am     gear     carb 
  13.000  118.000   90.000

I would like to reshape the data.table dt as follows:

> transpose
column1   column2
mpg       642.900
cyl       198.000
disp      7373.100
hp        4694.000
drat      115.090
wt        102.952
qsec      571.160
vs        14.000
am        13.000
gear      118.000
carb      90.000

The function t() doesn't appear to work as expected.

transpose = t(dt)

I suspect there's a quick way to do this with melt() and dcast(), but I'm not sure how one defines each column, i.e. column1 and column2

Ah, I see. Simply casting `dt = as.data.table(colSums(mtcars))` appears to delete the column names though — ShanZhengYang, Jun 11 '17 at 20:35
Like Rich said. If you want to do it using a data.table syntax, you could do `melt(mtcars)[, sum(value), by = variable]`, for instance. — David Arenburg, Jun 11 '17 at 20:35
Try `stack(dt)` if you really want to use `colSums`. It's not at all idiomatic for data.table, though. — Frank, Jun 11 '17 at 21:31

score 3 · Answer 1 · answered Jun 11 '17 at 20:49

3

I found this to work:

df = as.data.table(t(as.matrix(dt)))

And it even preserves the names

answered Jun 11 '17 at 20:49

lebelinoz

4,890
10
33
56

score 1 · Accepted Answer · answered Jun 12 '17 at 01:07

A dplyr solution:

library(dplyr)
library(data.table)
mtcars <- data.table(mtcars) # make data.table
class(mtcars) # check that it is a data.table
mtcars %>% # take the data.table
  summarise_all(funs(sum)) %>% # get the sum of each column
  gather(variable, sum) # gather all columns

gather, spread, and summarise is how I do all of my transposing. "Variable" and "sum" become the new column names:

   variable      sum
1       mpg  642.900
2       cyl  198.000
3      disp 7383.100
4        hp 4694.000
5      drat  115.090
6        wt  102.952
7      qsec  571.160
8        vs   14.000
9        am   13.000
10     gear  118.000
11     carb   90.000

How does one take the transpose of an R data.table? Why doesn't t() work?

2 Answers2