Re-order columns: split the second half of columns up so that they come as every second

user_13591191 0
Viewed 41

I want to rearrange a dataframe so that 1) the first column always stays first, and 2) that the second half of the remaining columns are split up to come every second instead.

Note that in the example below

Please see example data:

# Example data
N <- "AB"
l_x_1 <- 1
l_x_2 <- 2
l_x_3 <- 3
# ... not it should be able to handle different number of columns  

s_x_1 <- 1
s_x_2 <- 2
s_x_3 <- 3
# ... not it should be able to handle different number of columns (although always equal N of s_ and l_. 

# Current state
df <- tibble(N, l_x_1, l_x_2, l_x_3, s_x_1, s_x_2, s_x_3)
df


# What I want (but potential to handle different number of As and Bs)
df <- tibble(N, l_x_1, s_x_1, l_x_2, s_x_2,  l_x_3,  s_x_3)
df
tidyverse dataframe r
3 Answers
df[c(1, matrix(seq_along(df)[-1], 2, byrow = TRUE))]

# # A tibble: 1 × 7
#   N     l_x_1 s_x_1 l_x_2 s_x_2 l_x_3 s_x_3
#   <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
# 1 AB        1     1     2     2     3     3

The idea is to reorder column indices with matrix(). E.g.

c(matrix(1:6, 2, byrow = TRUE))
# [1] 1 4 2 5 3 6
user_100689850

A solution based on stringi::stri_reverse:

cbind(df[1], df[-1][order(stringi::stri_reverse(colnames(df[-1])))])

   N l_x_1 s_x_1 l_x_2 s_x_2 l_x_3 s_x_3
1 AB     1     1     2     2     3     3

This relies on the fact that if you reverse every names, you recover the desired order:

> sort(stringi::stri_reverse(colnames(df[-1])))
[1] "1_x_l" "1_x_s" "2_x_l" "2_x_s" "3_x_l" "3_x_s"
user_134606020 

library(tidyverse)
(preferred_order <- str_split(names(df)[-1],"_",
                              n = 3, # because  2 underscores 
                              simplify = TRUE) |>
    data.frame() |> 
    arrange(X3,X1) |>  # also because  2 underscores 
    rowwise() |>
    mutate(nm=paste0(c_across(),
                     collapse = "_")) |> pull(nm))

relocate(df,
         preferred_order,.after = "N")
user_117264360
Related