Reorder and Group Multiple Columns by Regex/pattern

Question

I have the following:

a_aa a_ab a_ac b_aa b_ab b_ac
2    3    3    3     1    2
3    4    1    1     3    1

Desired outcome:

a_aa b_aa a_ab b_ab a_ac b_ac
2    3    3    1     3    2
3    1    4    3     1    1

Code with data:

d <- "a_aa a_ab a_ac b_aa   b_ab b_ac
2    3    3    3     1    2
3    4    1    1     3    1"
dd <- read.table(textConnection(object = d), header = T)

My current solution is manual:

    dd %>% select(a_aa, b_aa, a_ab, b_ab, a_ac, b_ac)

however, is onerous when number of columns is large. Any ideas how to do this kind of column ordering with grouping (e.g. sequence a_etc1, b_etc1, a_etc2, b_etc2)? Thank you!

B. Christian Kamgang · Accepted Answer · 2022-07-10 05:02:59Z

6

Here is one way to solve your problem:

dd[order(gsub(".+_", "", names(dd)))]

# or

dd %>%
  select(order(gsub(".+_", "", names(.))))


  a_aa b_aa a_ab b_ab a_ac b_ac
1    2    3    3    1    3    2
2    3    1    4    3    1    1

edited Jul 10, 2022 at 5:02

answered Jul 10, 2022 at 4:57

B. Christian Kamgang

6,5348 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Andrei Maiseyeu Over a year ago

This is very elegant, worked on a large set. Thank you!

AnilGoyal · Accepted Answer · 2022-07-10 04:47:58Z

1

You may do something like this

library(tidyverse)

d <- "a_aa  a_ab    a_ac    b_aa    b_ab    b_ac
2   3   1   3   3   2
3   1   3   4   1   1"
dd <- read.table(textConnection(object = d), header = T)

colnames(dd) %>% 
  str_split("_") %>% 
  map_chr(~.x[2]) %>% 
  unique() -> vars

dd %>% 
  select(ends_with(all_of(vars)))
#>   a_aa b_aa a_ab b_ab a_ac b_ac
#> 1    2    3    3    3    1    2
#> 2    3    4    1    1    3    1

If you don't want to use other tidyverse libraries than dplyr, you can do

library(dplyr)
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union

d <- "a_aa  a_ab    a_ac    b_aa    b_ab    b_ac
2   3   1   3   3   2
3   1   3   4   1   1"
dd <- read.table(textConnection(object = d), header = T)

colnames(dd) %>% 
  strsplit("_") %>% 
  sapply(\(.x) .x[2]) %>% 
  unique() -> vars

dd %>% 
  select(ends_with(all_of(vars)))
#>   a_aa b_aa a_ab b_ab a_ac b_ac
#> 1    2    3    3    3    1    2
#> 2    3    4    1    1    3    1

^{Created on 2022-07-10 by the reprex package (v2.0.1)}

answered Jul 10, 2022 at 4:47

AnilGoyal

26.3k4 gold badges34 silver badges50 bronze badges

1 Comment

TarJae Over a year ago

Good to see you here!

TarJae · Accepted Answer · 2022-07-10 06:41:34Z

1

Here is an alternative way:

We first create a function using two additional packages:

stringi for its str_reverse function (advantage:it is vectorized)
gtools for its mixedsort function

Then we apply it to our dataframe df with rename_with:

library(dplyr)

# the function
my_function <- function(df){
  x <- stringi::stri_reverse(colnames(df))
  y <- gtools::mixedsort(x)
  stringi::stri_reverse(y)
}


df %>% 
  rename_with(., ~my_function(df))

  a_aa b_aa a_ab b_ab a_ac b_ac
1    2    3    3    3    1    2
2    3    4    1    1    3    1

answered Jul 10, 2022 at 6:41

TarJae

80.2k6 gold badges30 silver badges94 bronze badges

Comments

hello_friend · Accepted Answer · 2022-07-10 06:50:04Z

1

Base R solution:

dd[,order(gsub(".*\\_(\\w+$)", "\\1", names(dd)))]

answered Jul 10, 2022 at 6:50

hello_friend

5,8381 gold badge13 silver badges18 bronze badges

1 Comment

Andrei Maiseyeu Over a year ago

Excellent, thank you! What is the purpose of "+$" in your regex? This worked as well: dd[,order(gsub(".*\_(\\w)", "\\1", names(dd)))]. Interestingly, in a large dataset with "+" characters in some column names, the sorting was not accomplished when "+$" was used.

Collectives™ on Stack Overflow

Reorder and Group Multiple Columns by Regex/pattern

4 Answers 4

1 Comment

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related