Best Way to Transpose Data.Table

best way to transpose data.table

Why not just melt and dcast the data.table?

require(data.table)

dcast(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

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

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

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

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

Why is my effort in transposing a data table not working?

instead of t() which is good for matrices() use transpose from data.table.
Making changes only where required, its works for me now without any warning error.
with t() now commented our and next row with the only change in code :

data = if(input$transposeDT=='Rows'){
        #as.data.frame(t(results()))
        results()%>%transpose(make.names = 'to_state',keep.names = 'to_state')
        } else {results()},

You should see the transposition working by toggling the radioButton.
From this Default :

To this transposed. [I could not understand your logic but transposing is a reali life problem.

What is the best way to transpose a data.frame in R and to set one of the columns to be the header for the new transposed table?

Well you could do it in 2 steps by using

# Transpose table YOU WANT
fooData.T <- t(fooData[,2:ncol(fooData)])

# Set the column headings from the first column in the original table
colnames(fooData.T) <- fooData[,1] 

The result being a matrix which you're probably aware of, that's due to class issues when transposing. I don't think there will be a single line way to do this given the lack of naming abilities in the transpose step.

How to make data table transposition that previously worked, work again after a computer restart?

YBS comment (try data.table::transpose(...)) solved the problem. See post What are the double colons (::) in R?, which explains that there may be functions with the same name in multiple packages (which seems to be the case of my use of transpose()). The double colon operator :: allows you to specify the specific function from the specific package to use in a particular situation.

Transpose a datatable

.NET does not include a method to transpose data tables. You have to make your own. This website has a tutorial on an example transpose method. I will copy and paste the code snippet below:

private DataTable Transpose(DataTable dt)
{
    DataTable dtNew = new DataTable();

    //adding columns    
    for(int i=0; i<=dt.Rows.Count; i++)
    {
       dtNew.Columns.Add(i.ToString());
    }



    //Changing Column Captions: 
    dtNew.Columns[0].ColumnName = " ";

     for(int i=0; i<dt.Rows.Count; i++) 
     {
      //For dateTime columns use like below
       dtNew.Columns[i+1].ColumnName = Convert.ToDateTime(dt.Rows[i].ItemArray[0].ToString()).ToString("MM/dd/yyyy");
      //Else just assign the ItermArry[0] to the columnName prooperty
     }

    //Adding Row Data
    for(int k=1; k<dt.Columns.Count; k++)
    {
        DataRow r = dtNew.NewRow();
        r[0] = dt.Columns[k].ToString(); 
        for(int j=1; j<=dt.Rows.Count; j++)
        r[j] = dt.Rows[j-1][k];  
        dtNew.Rows.Add(r);
    }

 return dtNew;
}

---

Related Topics