Sort column name when they have underline_number in dplyr()?

user_13236293 0
Viewed 61

I tried to sort the column by the name_underline_number - using arrange(). It didn't work.

What's the best way to do this in dplyr()?

df <- structure(list(metric = c("rollmean_11", "rollmean_13", "rollmean_15", 
"rollmean_17", "rollmean_19", "rollmean_3", "rollmean_5", "rollmean_7", 
"rollmean_9"), MSE = c(1.92523924349549, 2.39347151053903, 2.89870886591451, 
3.4854131592187, 4.26101662911107, 0.651614338676069, 1.06260257822741, 
1.26237156422673, 1.55433498103862)), row.names = c(NA, -9L), class = c("tbl_df", 
"tbl", "data.frame"))

  metric        MSE
  <chr>       <dbl>
1 rollmean_11 1.93 
2 rollmean_13 2.39 
3 rollmean_15 2.90 
4 rollmean_17 3.49 
5 rollmean_19 4.26 
6 rollmean_3  0.652
7 rollmean_5  1.06 
8 rollmean_7  1.26 
9 rollmean_9  1.55

Expected output

  metric        MSE
  <chr>       <dbl>
1 rollmean_3  0.652
2 rollmean_5  1.06 
3 rollmean_7  1.26 
4 rollmean_9  1.55 
5 rollmean_11 1.93 
6 rollmean_13 2.39 
7 rollmean_15 2.90 
8 rollmean_17 3.49 
9 rollmean_19 4.26
dplyr r
5 Answers

Does this work:

library(dplyr)
library(stringr)
df %>% arrange(as.numeric(str_extract(metric, '\\d+$')))
# A tibble: 9 x 2
  metric        MSE
  <chr>       <dbl>
1 rollmean_3  0.652
2 rollmean_5  1.06 
3 rollmean_7  1.26 
4 rollmean_9  1.55 
5 rollmean_11 1.93 
6 rollmean_13 2.39 
7 rollmean_15 2.90 
8 rollmean_17 3.49 
9 rollmean_19 4.26
user_107227520

We could use parse_number from readr to extract the numeric part and use that in arrange

library(dplyr)
df %>%
     arrange(readr::parse_number(metric))

-output

# A tibble: 9 x 2
  metric        MSE
  <chr>       <dbl>
1 rollmean_3  0.652
2 rollmean_5  1.06 
3 rollmean_7  1.26 
4 rollmean_9  1.55 
5 rollmean_11 1.93 
6 rollmean_13 2.39 
7 rollmean_15 2.90 
8 rollmean_17 3.49 
9 rollmean_19 4.26
user_37322710

A data.table option

> setDT(df)[order(as.numeric(gsub("\\D", "", metric)))]
        metric       MSE
1:  rollmean_3 0.6516143
2:  rollmean_5 1.0626026
3:  rollmean_7 1.2623716
4:  rollmean_9 1.5543350
5: rollmean_11 1.9252392
6: rollmean_13 2.3934715
7: rollmean_15 2.8987089
8: rollmean_17 3.4854132
9: rollmean_19 4.2610166
user_121587570

It doesn't work because, as a character string, 11 comes before 2.

We can fix this systematically by padding the number:

library(dplyr)
library(stringr)

df %>% 
  mutate(metric = str_replace(metric,
                              "\\d+",
                              \(i) str_pad(i, 2, pad = "0"))) %>%
  arrange(metric)

Returns:

# A tibble: 9 x 2
  metric        MSE
  <chr>       <dbl>
1 rollmean_03 0.652
2 rollmean_05 1.06
3 rollmean_07 1.26
4 rollmean_09 1.55
5 rollmean_11 1.93
6 rollmean_13 2.39
7 rollmean_15 2.90
8 rollmean_17 3.49
9 rollmean_19 4.26
user_70100560

This isn't dplyr but given that there exists a function in gtools that does exactly that:

library(gtools)
df[mixedorder(df$metric), ]

giving:

# A tibble: 9 x 2
  metric        MSE
  <chr>       <dbl>
1 rollmean_3  0.652
2 rollmean_5  1.06 
3 rollmean_7  1.26 
4 rollmean_9  1.55 
5 rollmean_11 1.93 
6 rollmean_13 2.39 
7 rollmean_15 2.90 
8 rollmean_17 3.49 
9 rollmean_19 4.26
user_5165480
Related