Move a column conveniently

Question

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 :

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.

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

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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

#' Move column or selection of columns
#'
#' Column(s) described by \code{cols} are moved before (default) or after the reference 
#'   column described by \code{ref}
#'
#' @param data A \code{data.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

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