arrange alphanumeric column names alphabetically in R

Question

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!

Answer 1

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