arrange alphanumeric column names alphabetically in R

user_14819997 0
Viewed 385

i am working on rstudio. i have a dataframe with column names as follows: USA10 USA2 USA31 UK10 UK2 UK48 UK31 FRA31 FRA2 and so on. how can i arrange column names so they start with according to reverse alphabetical but increasing numeric order that is USA1 USA2 ... UK1 UK2 ... FRA1 FRA2 and so on. I tried

select(order(colnames(data),decreasing = TRUE))

however this ignores the numeric part of the column names. i would like the column names to be sorted by alphabets and numbers then. thanks!

dplyr r
3 Answers

The basic logic is that you need to split out the text and numeric parts separately, and then call an ordering function. I'll make an example dataset using the columns you describe:

x <- scan(text="USA10 USA2 USA31 UK10 UK2 UK48 UK31 FRA31 FRA2", what="")
df <- as.data.frame(as.list(x), col.names=x)

In base R:

ords <- strcapture("([A-Z]*)([0-9]*)", colnames(df), proto=list(char="",num=1L))
df[order(-xtfrm(ords$char), ords$num)]
#  USA2 USA10 USA31 UK2 UK10 UK31 UK48 FRA2 FRA31
#1 USA2 USA10 USA31 UK2 UK10 UK31 UK48 FRA2 FRA31

Or tidyverse:

df %>% select(
  data.frame(x=colnames(df)) %>%
    separate(x, sep="(?<=[A-Z])(?=[0-9])", into=c("char","num"),
             remove=FALSE, convert=TRUE) %>%
    arrange(desc(char), num) %>% pull(x)
)
#  USA2 USA10 USA31 UK2 UK10 UK31 UK48 FRA2 FRA31
#1 USA2 USA10 USA31 UK2 UK10 UK31 UK48 FRA2 FRA31
user_4968030

The problem is one of padding zeros in front of the numbers. The following should work:

## Example data and load stringr
library(stringr)
dat <- tibble(USA10 = rnorm(5),
       USA2 = rnorm(5),
       USA31 = rnorm(5),
       UK10 = rnorm(5),
       UK2 = rnorm(5),
       UK48 = rnorm(5),
       UK31 = rnorm(5),
       FRA31 = rnorm(5),
       FRA2 = rnorm(5))

## Extract country names and numbers
countries <- names(dat) %>% str_extract("([^0-9]+)")
## Pad numbers with 0s
numbers <- names(dat) %>% str_extract("(\\d+)") %>% str_pad(3,"left","0")
## rename colnames
names(dat) <- paste0(countries,numbers)

dat %>% select(order(names(dat),decreasing = TRUE))
user_153697220

For sorting you can try append 0 to your column names

library(stringr)

append_zero <- function(names_vector, digits = 2) {
  # extract alphabe & number from the original vectors
  alpha_part <- str_extract(names_vector, "^[A-Z]+")
  extract_number <- str_extract(names_vector, "\\d+$")
  # add dummy 0 into the extract number
  extract_number <- paste0(paste0(rep("0", digits - 1), collapse = ""), extract_number)
  
  # only take x number of digits from the right
  extract_number <- substr(extract_number, nchar(extract_number) - digits + 1,
    nchar(extract_number))
  
  # combine the alphabe & number back again
  names_vector <- paste0(alpha_part, extract_number)
  names_vector
}

sample <- c("USA10", "USA2", "USA31", "USA1")
sort(sample)
#> [1] "USA1"  "USA10" "USA2"  "USA31"

sort(append_zero(sample))
#> [1] "USA01" "USA02" "USA10" "USA31"
user_42096570
Related