146

I'd like to reorder columns in my data.table x, given a character vector of column names, neworder:

library(data.table)
x <- data.table(a = 1:3, b = 3:1, c = runif(3))
neworder <- c("c", "b", "a")

Obviously I could do:

x[ , neworder, with = FALSE]
# or
x[ , ..neworder]
#            c b a
# 1: 0.8476623 3 1
# 2: 0.4787768 2 2
# 3: 0.3570803 1 3

but that would require copying the entire dataset again. Is there another way to do this?

Henrik
  • 65,555
  • 14
  • 143
  • 159
Michael
  • 5,808
  • 4
  • 30
  • 39

2 Answers2

213

Use setcolorder():

library(data.table)
x <- data.table(a = 1:3, b = 3:1, c = runif(3))
x
#      a b         c
# [1,] 1 3 0.2880365
# [2,] 2 2 0.7785115
# [3,] 3 1 0.3297416
setcolorder(x, c("c", "b", "a"))
x
#              c b a
# [1,] 0.2880365 3 1
# [2,] 0.7785115 2 2
# [3,] 0.3297416 1 3

From ?setcolorder:

In data.table parlance, all set* functions change their input by reference. That is, no copy is made at all, other than temporary working memory, which is as large as one column.

so should be pretty efficient. See ?setcolorder for details.

Henrik
  • 65,555
  • 14
  • 143
  • 159
Chase
  • 67,710
  • 18
  • 144
  • 161
  • 29
    Small clarification: `setcolorder` moves the column pointers around without using any working memory at all. That sentence about using working memory as large as one column is just about `setkey` really. – Matt Dowle Sep 02 '12 at 08:43
  • 2
    @MatthewDowle - thanks for the clarification. I sort of thought that may have been the case, but wasn't 100% sure. – Chase Sep 02 '12 at 14:45
  • 4
    can I do that for a subset of columns? For example when I just want to move to columns to the front? – Jakob Apr 20 '16 at 15:41
  • 7
    `setcolorder(df, c("someCol",colnames(dt)[!(colnames(dt) %in% c("someCol"))]))` – hedgedandlevered Aug 17 '16 at 16:18
  • 12
    @PeterPan See also [NEWS about the devel version 1.10.5](https://github.com/Rdatatable/data.table/blob/master/NEWS.md): "`setcolorder()` now accepts less than `ncol(DT)` columns to be moved to the front" – Henrik Oct 22 '17 at 12:21
  • @Henrik Timely! I was looking for a way to move the key column to the front, that will fit the bill nicely when it's released. – Ken Williams Nov 27 '17 at 03:12
  • Is it possible to move a few columns (selected by name) to the back at all? – Atakan May 01 '20 at 06:24
19

One may find it easier to use the above solution, but instead sort by column number. For example: library(data.table)

    > x <- data.table(a = 1:3, b = 3:1, c = runif(3))
    > x
         a b         c
    [1,] 1 3 0.2880365
    [2,] 2 2 0.7785115
    [3,] 3 1 0.3297416
    > setcolorder(x, c(3,2,1))
    > x
         c         b a
    [1,] 0.2880365 3 1
    [2,] 0.7785115 2 2
    [3,] 0.3297416 1 3
Stephen
  • 473
  • 4
  • 11
  • 19
    It is generally discouraged to refer to columns by number, in data.table and elsewhere. The data.table faq makes the argument for this in the first item here: http://datatable.r-forge.r-project.org/datatable-faq.pdf – Frank Dec 02 '15 at 21:51