Re-order dataframe columns in R

February 3, 2022

I need to re-ordering the columns’ position in a dataframe with 500 columns. In fact, I only want the last column to be moved between the third and the fourth columns.

Here is what I tried:

 df[ ,c(1, 2, 3, ncol(df), 4:ncol(df)-1)]

But it gives me a vector of values which are the columns’ number. Would you someone tell me what I expect wrong from this code?

>Solution :

The issue maybe related to the operator precedence – wrap the (ncol(df)-1) within bracket (assuming the original object is a data.frame)

library(data.table)
df <-  df[ ,c(1, 2, 3, ncol(df), 4:(ncol(df)-1)), with = FALSE]

Or use setcolorder to update the original object

setcolorder(df, c(1, 2, 3, ncol(df), 4:(ncol(df)-1)))

NOTE: with = FALSE was added after the OP confirmed it is a data.table object

Or another option is select

library(dplyr)
df <- df %>% 
    select(1:3, last_col(), everything())

Or with relocate

df <- df %>% 
     relocate(last_col(), .before = 4)

-reproducible example testing

> data(mtcars)
> head(mtcars)[, c(1, 2, 3, ncol(mtcars), 4:(ncol(mtcars)-1))]
                   mpg cyl disp carb  hp drat    wt  qsec vs am gear
Mazda RX4         21.0   6  160    4 110 3.90 2.620 16.46  0  1    4
Mazda RX4 Wag     21.0   6  160    4 110 3.90 2.875 17.02  0  1    4
Datsun 710        22.8   4  108    1  93 3.85 2.320 18.61  1  1    4
Hornet 4 Drive    21.4   6  258    1 110 3.08 3.215 19.44  1  0    3
Hornet Sportabout 18.7   8  360    2 175 3.15 3.440 17.02  0  0    3
Valiant           18.1   6  225    1 105 2.76 3.460 20.22  1  0    3

             
> head(mtcars) %>% select(1:3, last_col(), everything())
                   mpg cyl disp carb  hp drat    wt  qsec vs am gear
Mazda RX4         21.0   6  160    4 110 3.90 2.620 16.46  0  1    4
Mazda RX4 Wag     21.0   6  160    4 110 3.90 2.875 17.02  0  1    4
Datsun 710        22.8   4  108    1  93 3.85 2.320 18.61  1  1    4
Hornet 4 Drive    21.4   6  258    1 110 3.08 3.215 19.44  1  0    3
Hornet Sportabout 18.7   8  360    2 175 3.15 3.440 17.02  0  0    3
Valiant           18.1   6  225    1 105 2.76 3.460 20.22  1  0    3
> ?relocate
> head(mtcars) %>% relocate(last_col(), .before = 4)
                   mpg cyl disp carb  hp drat    wt  qsec vs am gear
Mazda RX4         21.0   6  160    4 110 3.90 2.620 16.46  0  1    4
Mazda RX4 Wag     21.0   6  160    4 110 3.90 2.875 17.02  0  1    4
Datsun 710        22.8   4  108    1  93 3.85 2.320 18.61  1  1    4
Hornet 4 Drive    21.4   6  258    1 110 3.08 3.215 19.44  1  0    3
Hornet Sportabout 18.7   8  360    2 175 3.15 3.440 17.02  0  0    3
Valiant           18.1   6  225    1 105 2.76 3.460 20.22  1  0    3