Reordering columns in a large dataframe

Question

Using the following example dataframe:

a <-  c(1:5)
b <- c("Cat", "Dog", "Rabbit", "Cat", "Dog")
c <- c("Dog", "Rabbit", "Cat", "Dog", "Dog")
d <- c("Rabbit", "Cat", "Dog", "Dog", "Rabbit")
e <- c("Cat", "Dog", "Dog", "Rabbit", "Cat")
f <- c("Cat", "Dog", "Dog", "Rabbit", "Cat")

df <- data.frame(a,b,c,d,e,f)

I want to investigate how to reorder the columns WITHOUT having to type in all the column names, i.e., df[,c("a","d","e","f","b","c")]

How would I just say I want columns b and c AFTER column f? (only referencing the columns or range of columns that I want to move?).

Many thanks in advance for your help.

score 49 · Answer 1 · answered May 27 '15 at 02:37

49

To move specific columns to the beginning or end of a data.frame, use select from the dplyr package and its everything() function. In this example we are sending to the end:

library(dplyr)
df %>%
  select(-b, -c, everything())

  a      d      e      f      b      c
1 1 Rabbit    Cat    Cat    Cat    Dog
2 2    Cat    Dog    Dog    Dog Rabbit
3 3    Dog    Dog    Dog Rabbit    Cat
4 4    Dog Rabbit Rabbit    Cat    Dog
5 5 Rabbit    Cat    Cat    Dog    Dog

Without the negation, the columns would be sent to the front.

answered May 27 '15 at 02:37

Sam Firke

21,571
9
87
105

This is a neat trick, but it breaks down if you need to reorder some columns to the beginning and others to the end. Also confusing (drop and then re-add with everything? unclear how to interpret) – Arthur Yip Jun 04 '17 at 10:30
1

Yes, if you want to reorder some columns to the beginning *and* others to the end, that requires two different `select()` lines. – Sam Firke Jun 04 '17 at 13:57
3

Clarified with Hadley - the negative operator only works in the first position of select() https://www.github.com/tidyverse/dplyr/issues/2838 In this special behaviour, select() will call all the variables, remove those with negatives, and finally re-add the cancelled ones with everything(). Having negatives and then positives won't work because they will have already been called in the special behaviour. – Arthur Yip Jun 06 '17 at 03:05

A5C1D2H2I1M1N2O1R2T1 · Accepted Answer · 2013-08-20T16:29:16.967

If you're just moving certain columns to the end, you can create a little helper-function like the following:

movetolast <- function(data, move) {
  data[c(setdiff(names(data), move), move)]
}

movetolast(df, c("b", "c"))
#   a      d      e      f      b      c
# 1 1 Rabbit    Cat    Cat    Cat    Dog
# 2 2    Cat    Dog    Dog    Dog Rabbit
# 3 3    Dog    Dog    Dog Rabbit    Cat
# 4 4    Dog Rabbit Rabbit    Cat    Dog
# 5 5 Rabbit    Cat    Cat    Dog    Dog

I would not recommend getting too into the habit of using column positions, especially not from a programmatic standpoint, since those positions might change.

"For fun" update

Here's an extended interpretation of the above function. It allows you to move columns to either the first or last position, or to be before or after another column.

moveMe <- function(data, tomove, where = "last", ba = NULL) {
  temp <- setdiff(names(data), tomove)
  x <- switch(
    where,
    first = data[c(tomove, temp)],
    last = data[c(temp, tomove)],
    before = {
      if (is.null(ba)) stop("must specify ba column")
      if (length(ba) > 1) stop("ba must be a single character string")
      data[append(temp, values = tomove, after = (match(ba, temp)-1))]
    },
    after = {
      if (is.null(ba)) stop("must specify ba column")
      if (length(ba) > 1) stop("ba must be a single character string")
      data[append(temp, values = tomove, after = (match(ba, temp)))]
    })
  x
}

Try it with the following.

moveMe(df, c("b", "c"))
moveMe(df, c("b", "c"), "first")
moveMe(df, c("b", "c"), "before", "e")
moveMe(df, c("b", "c"), "after", "e")

You'll need to adapt it to have some error checking--for instance, if you try to move columns "b" and "c" to "before c", you'll (obviously) get an error.

That's great @Ananda Mahto - it's exactly what I wanted. Thank you. — KT_1, Aug 20 '13 at 15:59
@KT_1, I found [this related question](http://stackoverflow.com/q/12544888/1270695) and posted [this related answer](http://stackoverflow.com/a/18420673/1270695). It's a lot more than what you're looking for, but I had fun writing the function, so I thought I'd share it! — A5C1D2H2I1M1N2O1R2T1, Aug 24 '13 at 16:38
nice, it looks like dplyr relocate could have been inspired by this :) — Arthur Yip, Jan 30 '21 at 18:04

score 16 · Answer 3 · edited Aug 20 '13 at 15:55

16

You can refer to columns by position. e.g.

df <- df[ ,c(1,4:6,2:3)]
> df
  a      d      e      f      b      c
1 1 Rabbit    Cat    Cat    Cat    Dog
2 2    Cat    Dog    Dog    Dog Rabbit
3 3    Dog    Dog    Dog Rabbit    Cat
4 4    Dog Rabbit Rabbit    Cat    Dog
5 5 Rabbit    Cat    Cat    Dog    Dog

edited Aug 20 '13 at 15:55

Metrics

15,172
7
54
83

answered Aug 20 '13 at 15:46

r.bot

5,309
1
34
45

1

This will give `a,f,b,c,d,e`. – zx8754 Aug 20 '13 at 15:51

Arthur Yip · Answer 4 · 2021-01-30T18:02:33.527

8

The package dplyr and the function dplyr::relocate, a new verb introduced in dplyr 1.0.0, does exactly what you are looking for with highly readable syntax.

df %>% dplyr::relocate(b, c, .after = f)

edited Jan 30 '21 at 18:02

answered Apr 20 '20 at 07:02

Arthur Yip

5,810
2
31
50

score 4 · Answer 5 · edited May 20 '17 at 21:31

4

To generalize the reshuffling of columns in any order using dplyr, for example, to reshuffle:

df <- data.frame(a,b,c,d,e,f)

to

df[,c("a","d","e","f","b","c")]

df %>% select(a, d:f, b:c)

edited May 20 '17 at 21:31

Dan Lowe

51,713
20
123
112

answered May 20 '17 at 20:51

PeteRlearner

154
2
7

score 4 · Answer 6 · answered Nov 27 '19 at 08:15

Use the subset function:

> df <- data.frame(a,b,c,d,e,f)
> df <- subset(df, select = c(a, d:f, b:c))
> df
  a      d      e      f      b      c
1 1 Rabbit    Cat    Cat    Cat    Dog
2 2    Cat    Dog    Dog    Dog Rabbit
3 3    Dog    Dog    Dog Rabbit    Cat
4 4    Dog Rabbit Rabbit    Cat    Dog
5 5 Rabbit    Cat    Cat    Dog    Dog

score 2 · Answer 7 · answered Feb 24 '16 at 11:03

I changed the previous function to use it for data.table usinf the function setcolorder of the package data.table.

moveMeDataTable <-function(data, tomove, where = "last", ba = NULL) {
  temp <- setdiff(names(data), tomove)
  x <- switch(
    where,
    first = setcolorder(data,c(tomove, temp)),
    last = setcolorder(data,c(temp, tomove)),
    before = {
      if (is.null(ba)) stop("must specify ba column")
      if (length(ba) > 1) stop("ba must be a single character string")
      order = append(temp, values = tomove, after = (match(ba, temp)-1))
      setcolorder(data,order)

    },
    after = {
      if (is.null(ba)) stop("must specify ba column")
      if (length(ba) > 1) stop("ba must be a single character string")
      order = append(temp, values = tomove, after = (match(ba, temp)))
      setcolorder(data,order)
    })
  x
}

DT <- data.table(A=sample(3, 10, TRUE),
                B=sample(letters[1:3], 10, TRUE), C=sample(10))
DT <- moveMeDataTable(DT, "C", "after", "A")

score 0 · Answer 8 · answered Aug 20 '13 at 16:25

0

Here is another option:

df <- cbind( df[, -(2:3)], df[, 2:3] )

answered Aug 20 '13 at 16:25

Greg Snow

48,497
6
83
110

Reordering columns in a large dataframe

8 Answers8

"For fun" update

Linked