R move column to last using dplyr

77

votes

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:

r dplyr

120

votes

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.

72

votes

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?

13

votes

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

5

votes

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

0

votes

df <- df[, c(which(colnames(df) != "YourColumnName"), which(colnames(df) == "YourColumnName"))]

R move column to last using dplyr

5 Answers