How to sort a concatenated string in a column in R?

user_10056318 0
Viewed 41

Have a data frame with a concatenated column that I want to order numerically with the number after -

df <- data.frame(Order = c("A23_2-A27_3-A40_4-A10_1", "A25_2-A21_3-A11_1", "A9_1", "A33_2-A8_1"))

and want to have a result like this:

df <- data.frame(Order = c("A10A23A27A40", "A11A25A21", "A9", "A8A33"))

tried couple of things with tidyverse but couldn't get a clean result.

dplyr tidyverse r
3 Answers
df %>%
  rowid_to_column() %>%
  separate_rows(Order, sep='-') %>%
  separate(Order, c('Order', 'v'), convert = TRUE) %>%
  arrange(v)%>%
  group_by(rowid) %>%
  summarise(Order = str_c(Order, collapse = ''))
  
# A tibble: 4 x 2
  rowid Order       
  <int> <chr>       
1     1 A10A23A27A40
2     2 A11A25A21   
3     3 A9          
4     4 A8A33
user_83802720

Here is a base R option:

df$Order <-
  sapply(strsplit(df$Order, "-"), function(x)
       paste0(gsub("\\_.*", "", x[order(as.numeric(sub("^[^_]*_", "", x)))]), collapse = ""))

Output

         Order
1 A10A23A27A40
2    A11A25A21
3           A9
4        A8A33

Or a tidyverse option:

library(tidyverse)

df %>%
  mutate(Order = map(str_split(Order, "-"), ~
                       str_c(
                         str_replace_all(.x[order(as.numeric(str_replace_all(.x, "^[^_]*_", "")))], "\\_.*", ""), collapse = ""
                       )))
user_152931910

Another base R approach:

df$Order <- sapply(strsplit(df$Order, '-'), function(x) {
  spl <- strsplit(x, '_')               # split by '_'
  spl <- do.call(rbind, spl)            # create a 2-column matrix
  ord <- order(as.numeric(spl[, 2]))    # order of numeric parts
  paste(spl[ord, 1], collapse='')       # concatenate in correct order
})
user_20948930
Related