Move a column conveniently

There are great questions and answers on how to move a column to the first or last place.

Using dplyr The best answers are respectively analog to :

dplyr

iris2 <- iris %>% head(2) iris2 %>% select( Sepal.Width, everything()) # move Sepal.Width to first # Sepal.Width Sepal.Length Petal.Length Petal.Width Species # 1 3.5 5.1 1.4 0.2 setosa # 2 3.0 4.9 1.4 0.2 setosa iris2 %>% select(-Sepal.Width, Sepal.Width) # move Sepal.Width to last # Sepal.Length Petal.Length Petal.Width Species Sepal.Width # 1 5.1 1.4 0.2 setosa 3.5 # 2 4.9 1.4 0.2 setosa 3.0

However I didn't find any easy way to move a column after or before a given one.

I'm posting a rough solution below but :

tidyverse

I believe using vars we could also move a list of columns, or a group of column showing a pattern in the names etc... But I'm not yet so comfortable with tidyverse style programming.

vars

tidyverse

So I challenge you to do better/smarter or point me to the obvious solution I've missed.

Expected output :

iris2 %>% move_at(Species, Sepal.Width, side = "before") # Sepal.Length Species Sepal.Width Petal.Length Petal.Width # 1 5.1 setosa 3.5 1.4 0.2 # 2 4.9 setosa 3.0 1.4 0.2 iris2 %>% move_at(Species, Sepal.Width, side = "after") # Sepal.Length Sepal.Width Species Petal.Length Petal.Width # 1 5.1 3.5 setosa 1.4 0.2 # 2 4.9 3.0 setosa 1.4 0.2

4 Answers
4

This seems to work, regardless of original column order (thanks for the comment to @Moody_Mudskipper ):

iris %>% select(1:Sepal.Width, -Species, Species, everything()) %>% head(2) #> Sepal.Length Sepal.Width Species Petal.Length Petal.Width #> 1 5.1 3.5 setosa 1.4 0.2 #> 2 4.9 3.0 setosa 1.4 0.2 iris %>% select(1:Sepal.Width, -Sepal.Width, -Species, Species, everything()) %>% head(2) #> Sepal.Length Species Sepal.Width Petal.Length Petal.Width #> 1 5.1 setosa 3.5 1.4 0.2 #> 2 4.9 setosa 3.0 1.4 0.2

I'm choosing this as I made it the core of my own solution, and most users don't want a new function. See my answer below for a function permitting less verbose calls.
– Moody_Mudskipper
Aug 30 at 14:04

@Moody_Mudskipper Thanks for accepting! Sure the function you wrote is much clearer to use in real code. To understand these two lines, you have to know quite a lot about tidyselect already, otherwise it's nonsense. So it's a great idea to hide the call in a function whose name describes what it is doing. I guess reordering columns isn't particularly in the focus of tidyverse, even though it's pretty important when exporting results.
– Zsombor Lehoczky
Aug 30 at 14:18

yes, or when you want to explore the data visually as was the case that inspired this question. This discussion is relevant: github.com/tidyverse/dplyr/issues/3051#issuecomment-324646580 , @hadey says "I don't think this is such a common operation that it needs it's own verb.", speaking about moving a column to the last spot. I've seen similar issues a few times on SO though.
– Moody_Mudskipper
Aug 30 at 14:30

The solution needs a correction, it doesn't work if you switch Species and Sepal.width, the right solutions are iris %>% select(1:Sepal.Width,-Species, Species, everything()) %>% head(2) and iris %>% select(1:Sepal.Width, -Sepal.Width, -Species, Species, everything()) %>% head(2)
– Moody_Mudskipper
Aug 30 at 15:35

Species

Sepal.width

iris %>% select(1:Sepal.Width,-Species, Species, everything()) %>% head(2)

iris %>% select(1:Sepal.Width, -Sepal.Width, -Species, Species, everything()) %>% head(2)

Thanks for the comment, I've edited the answer. However, this code looks even worse now. I'm pretty sure that select was not designed for this purpose, and complex column reorderings should be done using base R syntax, like your example below.
– Zsombor Lehoczky
Aug 31 at 6:50

UPDATE : using rlang::enquo I could make it much better, then using @Zsombor's answer I could make it much shorter and more elegant. old solution (in base R) at the end of answer

rlang::enquo

#' Move column or selection of columns #' #' Column(s) described by codecols are moved before (default) or after the reference column described by coderef #' #' @param data A codedata.frame #' @param cols unquoted column name or numeric or selection of columns using a select helper #' @param ref unquoted column name #' @param side code"before" or code"after" #' #' @return A data.frame with reordered columns #' @export #' #' @examples #' iris2 <- head(iris,2) #' move(iris2, Species, Sepal.Width) #' move(iris2, Species, Sepal.Width, "after") #' move(iris2, 5, 2) #' move(iris2, 4:5, 2) #' move(iris2, one_of("Sepal.Width","Species"), Sepal.Width) #' move(iris2, starts_with("Petal"), Sepal.Width) move <- function(data, cols, ref, side = c("before","after")) if(! requireNamespace("dplyr")) stop("Make sure package 'dplyr' is installed to use function 'move'") side <- match.arg(side) cols <- rlang::enquo(cols) ref <- rlang::enquo(ref) if(side == "before") dplyr::select(data,1:!!ref,-!!ref,-!!cols,!!cols,dplyr::everything()) else dplyr::select(data,1:!!ref,-!!cols,!!cols,dplyr::everything())

examples:

iris2 %>% move(Species, Sepal.Width) # Sepal.Length Species Sepal.Width Petal.Length Petal.Width # 1 5.1 setosa 3.5 1.4 0.2 # 2 4.9 setosa 3.0 1.4 0.2 iris2 %>% move(Species, Sepal.Width, "after") # Sepal.Length Sepal.Width Species Petal.Length Petal.Width # 1 5.1 3.5 setosa 1.4 0.2 # 2 4.9 3.0 setosa 1.4 0.2 iris2 %>% move(5, 2) # Sepal.Length Species Sepal.Width Petal.Length Petal.Width # 1 5.1 setosa 3.5 1.4 0.2 # 2 4.9 setosa 3.0 1.4 0.2 iris2 %>% move(4:5, 2) # Sepal.Length Petal.Width Species Sepal.Width Petal.Length # 1 5.1 0.2 setosa 3.5 1.4 # 2 4.9 0.2 setosa 3.0 1.4 iris2 %>% move(one_of("Sepal.Width","Species"), Sepal.Width) # Sepal.Length Sepal.Width Species Petal.Length Petal.Width # 1 5.1 3.5 setosa 1.4 0.2 # 2 4.9 3.0 setosa 1.4 0.2 iris2 %>% move(starts_with("Petal"), Sepal.Width) # Sepal.Length Petal.Length Petal.Width Sepal.Width Species # 1 5.1 1.4 0.2 3.5 setosa # 2 4.9 1.4 0.2 3.0 setosa

Old solution referenced in question

Here's a simple solution using only base R programming :

move_at <- function(data, col, ref, side = c("before","after")) side = match.arg(side) col_pos <- match(as.character(substitute(col)),names(data)) ref_pos <- match(as.character(substitute(ref)),names(data)) sorted_pos <- c(col_pos,ref_pos) if(side =="after") sorted_pos <- rev(sorted_pos) data[c(setdiff(seq_len(ref_pos-1),col_pos), sorted_pos, setdiff(seq_along(data),c(seq_len(ref_pos),col_pos)))] iris2 %>% move_at(Species, Sepal.Width) # Sepal.Length Species Sepal.Width Petal.Length Petal.Width # 1 5.1 setosa 3.5 1.4 0.2 # 2 4.9 setosa 3.0 1.4 0.2 iris2 %>% move_at(Species, Sepal.Width, "after") # Sepal.Length Sepal.Width Species Petal.Length Petal.Width # 1 5.1 3.5 setosa 1.4 0.2 # 2 4.9 3.0 setosa 1.4 0.2

Awesome. That's the second @Moody_Mudskipper function which I will incorporate in my utilities :) Thanks!
– Tjebo
Sep 3 at 22:13

Honoured :). What's the first if I may ask ?
– Moody_Mudskipper
Sep 4 at 0:08

of course! that’s ‘get_age’ ;) stackoverflow.com/a/47529507/7941188
– Tjebo
Sep 4 at 6:06

Just for the record, another solution would be

library(tidyverse) data(iris) iris %>% select(-Species) %>% add_column(Specis = iris$Species, .before = "Petal.Length") %>% head() #> Sepal.Length Sepal.Width Specis Petal.Length Petal.Width #> 1 5.1 3.5 setosa 1.4 0.2 #> 2 4.9 3.0 setosa 1.4 0.2 #> 3 4.7 3.2 setosa 1.3 0.2 #> 4 4.6 3.1 setosa 1.5 0.2 #> 5 5.0 3.6 setosa 1.4 0.2 #> 6 5.4 3.9 setosa 1.7 0.4

Created on 2018-08-31 by the reprex package (v0.2.0).

That's a neat solution as well, if it could leverage the stuff from ?dplyr::select_helpers and would allow "adding" existing columns I would choose it.
– Moody_Mudskipper
Aug 31 at 7:21

?dplyr::select_helpers

Indeed, select-helpers would be nice. I have written a move_columns() function to my sjmisc package. The function is available in the current GitHub-version, and also allows select-helpers. See examples here: strengejacke.github.io/sjmisc/reference/move_columns.html
– Daniel
Aug 31 at 8:09

move_columns()

cool I'll take a look thanks
– Moody_Mudskipper
Aug 31 at 14:16

You have got some awesome and very useful functions in that package, Daniel!
– Tjebo
Sep 4 at 6:44

Thanks, glad you like it! :-)
– Daniel
Sep 4 at 9:16

I found an interesting function (moveMe, written by @A5C1D2H2I1M1N2O1R2T1) that closely fits the problem:

source('https://raw.githubusercontent.com/mrdwab/SOfun/master/R/moveMe.R') head(iris[ moveMe(names(iris), 'Species before Sepal.Width') ], 2) # Sepal.Length Species Sepal.Width Petal.Length Petal.Width # 1 5.1 setosa 3.5 1.4 0.2 # 2 4.9 setosa 3.0 1.4 0.2 head(iris[ moveMe(names(iris), 'Species after Sepal.Width') ], 2) # Sepal.Length Sepal.Width Species Petal.Length Petal.Width # 1 5.1 3.5 setosa 1.4 0.2 # 2 4.9 3.0 setosa 1.4 0.2

It also allows for more complex instructions:

head(iris[ moveMe(names(iris), 'Species after Sepal.Width; Petal.Width first; Sepal.Length last') ], 2) # Petal.Width Sepal.Width Species Petal.Length Sepal.Length # 1 0.2 3.5 setosa 1.4 5.1 # 2 0.2 3.0 setosa 1.4 4.9

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

搜尋此網誌

Dfyjkt