95

For a data.frame with n columns, I would like to be able to move a column from any of 1-(n-1) positions, to be the nth column (i.e. a non-last column to be the last column). I would also like to do it using dplyr. I would like to do so without simply typing out the names of all the columns.

For example:

data<-data.frame(a=1:5, b=6:10, c=11:15)

This works, but isn't the dplyr way: 

data[,c(colnames(data)[colnames(data)!='b'],'b')]

This is the dplyr way to make column b first: 

data%>%select(b, everything())

But this doesn't work to make column b last: 

data%>%select(everything(), b)

This works, but requires me to type out all the columns: 

data%>%select(a,c,b)

So is there an elegant dplyr way to do this?

Related questions:

Community
  • 1
  • 1
dule arnaux
  • 3,500
  • 2
  • 14
  • 21

5 Answers5

158

After some tinkering, the following works and requires very little typing.

data %>% select(-b,b)


UPDATE: dplyr 1.0.0

dplyr 1.0.0 introduces the relocate verb:

data %>% relocate(b, .after = last_col())

I still prefer the old "hacky" way.

dule arnaux
  • 3,500
  • 2
  • 14
  • 21
  • Thanks a lot for this easy and simple way. Appreciated Dule. – HassanSh__3571619 Jul 07 '17 at 18:15
  • 1
    Dule, you could change the accepted answer to either this or Arthur Yip's, as they're decidedly cleaner and more 'elegant' than Arkun's (although it works fine.) – Scransom Jul 18 '17 at 07:25
  • 1
    The other answers teach me more about dplyr, but this answer is the shortest of them all! So I'd consider it a toss-up. – octern Dec 19 '18 at 15:55
83

Update:

dplyr::relocate, a new verb introduced in dplyr 1.0.0, is now my preferred solution, since it is explicit about what you are doing, you can continue to pick variables using tidyselect helpers, and you can specify exactly where to put the columns with .before or .after

data %>% relocate(b, .after = last_col()) (same as dule arnaux's update)

Original answer

data%>%select(-b,everything())

will move variable b to the end.

This is because a negative variable in the first position of select elicits a special behavior from select(), which is to insert all the variables. Then it removes b, and then it gets added back with the everything() part.

Explained by Hadley himself: https://github.com/tidyverse/dplyr/issues/2838

Also see this other answer for other examples of how to move some columns to the end and other columns to the beginning: How does dplyr's select helper function everything() differ from copying?

derelict
  • 3,657
  • 3
  • 24
  • 29
Arthur Yip
  • 5,810
  • 2
  • 31
  • 50
  • 4
    This is cleaner than the answer from dule arnaux if you are moving several columns to the back. – Dannid Jan 08 '19 at 23:59
  • 1
    Note that this answer does not respect the order of variables, in case you pass multiple names, like `-c(a,b,c)` instead of just `-b`. With this solution, the order of variables will reflect the order in which they were already in the data frame. So if the column order was `a, c, b, d, e, f`, this answer will return `d, e, f, a, c, b`. Dule arnaux's answer will return `d, e, f, a, b, c` – rvrvrv Apr 02 '20 at 09:43
14

We can either use

data %>%
    select(-one_of('b'), one_of('b'))
#  a  c  b
#1 1 11  6
#2 2 12  7
#3 3 13  8
#4 4 14  9
#5 5 15 10

Or

data %>%
    select(matches("[^b]"), matches("b"))

or with the select_

data %>% 
    select_(.dots = c(setdiff(names(.), 'b'), 'b'))
#  a  c  b
#1 1 11  6
#2 2 12  7
#3 3 13  8
#4 4 14  9
#5 5 15 10
akrun
  • 874,273
  • 37
  • 540
  • 662
  • 1
    Great answer always, What does one_of do? , does it actually picks the name in quotes, unlike other options ? Thanks – PKumar May 10 '17 at 17:11
  • 1
    @Bankelal Thanks. You can have a vector of string names in `one_of` to match and pick it up – akrun May 10 '17 at 17:13
  • 2
    +1 for using `one_of` as protection for missing columns. Combine with Arthur Yip's answer for `data %>% select(-one_of('b'), everything())`, which puts the removed column back at the end with the everything() call. – Dannid Jan 08 '19 at 23:58
  • 1
    I think `matches("[^b]"), matches("b")` is very clever and could be really useful in other situations. – abalter Dec 20 '20 at 03:34
5

Since there's no ready-made solution to this in dplyr you could define your own little function to do it for you:

move_last <- function(DF, last_col) {
    match(c(setdiff(names(DF), last_col), last_col), names(DF))
}

You can then use it easily in a normal select call:

mtcars %>% select(move_last(., "mpg")) %>% head()

You can also move multiple columns to the end:

mtcars %>% select(move_last(., c("mpg", "cyl"))) %>% head()

And you can still supply other arguments to select, for example to remove a column:

mtcars %>% select(move_last(., "mpg"), -carb) %>% head()
talat
  • 68,970
  • 21
  • 126
  • 157
  • 1
    Why do you say there is no ready made solution in dplyr? Akrun's solution example apears to be one. – dule arnaux May 10 '17 at 20:22
  • True, dplyr does allow for this, but Hadley notes that moving/reordering variables is "not usually that important, so you'll need to muddy along with select() for now." https://github.com/tidyverse/dplyr/issues/2838 – Arthur Yip Jun 05 '17 at 04:01
0
df <- df[, c(which(colnames(df) != "YourColumnName"), which(colnames(df) == "YourColumnName"))]
sm925
  • 2,648
  • 1
  • 16
  • 28
f2003596
  • 179
  • 1
  • 5