Transpose a data.table (columns names -> first column of output)

Question

What is the most efficient way to traspose

> dt <- data.table( x = c(1, 1, 3, 1, 3, 1, 1), y = c(1, 2, 1, 2, 2, 1, 1) )
> dt
   x y
1: 1 1
2: 1 2
3: 3 1
4: 1 2
5: 3 2
6: 1 1
7: 1 1

into:

> output
    cn   v1   v2   v3   v4   v5   v6   v7
1:   x    1    1    3    1    3    1    1
2:   y    1    2    1    2    2    1    1

dcast.data.table is supposed to be efficient, but I can't figure out how exactly it has to be done

Sukkah

Answer 1

How about data.table::transpose:

data.table(cn = names(dt), transpose(dt))
#   cn V1 V2 V3 V4 V5 V6 V7
#1:  x  1  1  3  1  3  1  1
#2:  y  1  2  1  2  2  1  1

---

If you are really concerned about efficiency, this may be better:

tdt <- transpose(dt)[, cn :=  names(dt)]
setcolorder(tdt, c(ncol(tdt), 1:(ncol(tdt) - 1)))
tdt
#   cn V1 V2 V3 V4 V5 V6 V7
#1:  x  1  1  3  1  3  1  1
#2:  y  1  2  1  2  2  1  1 

---

transpose seems to be a little faster than t (which calls do_transpose), but not by a large margin. I would guess that both of these implementations are roughly near the upper bound of efficiency for non in-place transposition algorithms.

Dt <- data.table( 
    x = rep(c(1, 1, 3, 1, 3, 1, 1), 10e2), 
    y = rep(c(1, 2, 1, 2, 2, 1, 1), 10e2))

all.equal(data.table(t(Dt)), data.table(transpose(Dt)))
#[1] TRUE

microbenchmark::microbenchmark(
    "base::t" = data.table(t(Dt)),
    "data.table::transpose" = data.table(transpose(Dt))
)
#Unit: milliseconds
#                 expr      min       lq     mean   median       uq      max neval
#base::t               415.4200 434.5308 481.4373 458.1619 507.9556 747.2022   100
#data.table::transpose 409.5624 425.8409 474.9709 444.5194 510.3750 685.0543   100

Landre

Wonder how would it be different from just data.table(cn = names(dt), t(dt)) – Merengue

@David Arenburg Good question; it looks like all the real work is done in C. Ill put together a benchmark against t and add it to my answer. – Landre

@DavidArenburg, like other native data.table:: function, I guess it is as efficient as it gets. This is what is also claimed in cran.r-project.org/web/packages/data.table/data.table.pdf I am a bit embarrassed on how easy this question actually was. I should have Ctrl+F on 'transpose' in data.table documentation rather than googling it. – Sukkah

t is also fully vectorized. The only trade off I ca think of is conversion to a matrix I guess. – Merengue

Answer 2

Code to identify fields for object [temp_table] and report this via the object [temp_table_schema]

  temp_table
  temp_table_data_types <- sapply (temp_table, class)
  temp_table_schema <- NULL
  for (x in 1:(length(temp_table_data_types))) {
    temp_table_schema <- base::rbind(temp_table_schema, data.table(ROWID = (x)
                                                                   , COLUMN_NAME = names(temp_table_data_types[x])
                                                                   , DATA_TYPE   = temp_table_data_types[[x]][[1]]
                                                                   , DETAILS     = if(length(as.list(temp_table_data_types[[x]]))> 1) {as.list(temp_table_data_types[[x]])[[2]]} else {""}
                                                                   ))
    }
  temp_table_schema
  rm(list = c("temp_table_data_types"))

Schiff