How to sort alphanumeric column by natural numbers in a datatable?

shiny
1

I have a data table with a column of alphanumeric values (for example, 'ab1', 'ab2'... 'ab11', 'ac1', 'acr11', 'ac2'). However, when I click on the header in order to sort the column, the data does not appear as "ab1, ab2, ab11, ac1, ac2, acr11" but instead as "ab1, ab11, ab2, ac1, ac2, acr11".

Ideally I would like to sort first by the letters (two or three letters) and then by the numbers sequentially (from 1 to 1000+). It seems that the datatable currently sorts for the letters - which is good - but it does not sort for natural numbers (or perhaps I should say counting numbers).

I have tried using the "natural" sort plugin, to no avail. My own testing of this plugin indicates that it will only sort numbers then letters, not letters than numbers.

Below is working example. Any help or suggestions would be much appreciated!

library(shiny)
library(DT)

d = cbind(
  head(iris),
  Mixed = c('ab1', 'ab2', 'ab11', 'ac1', 'acr11', 'ac2')
)

shinyApp(
  ui = fluidPage(DT::dataTableOutput('tbl')),
  server = function(input, output) {
    output$tbl = DT::renderDataTable(
      d, plugins = 'natural', options = list(
        dom = 't',
        columnDefs = list(list(type = 'natural', targets = 6))
      )
    )
  }
)
2

natural is a client-side plugin (see https://datatables.net/plug-ins/sorting/) so you'll have to use client-side processing in DT

output$tbl = DT::renderDataTable(
  d, server = FALSE, plugins = 'natural', options = list(
    dom = 't',
    columnDefs = list(list(type = 'natural', targets = 6))
  )
)
1 Like
3

I recently just faced this problem in a project I was working with. I had a column of numbered categories (1.1, 1.2, 1.3, 2.1, 2.2...). I had 20 categories which isn't too many, so I used the following code to get them in the right order on my plot.

dat3$Milestone <- factor(dat3$Milestone, levels = c(1.1, 1.2, 1.3, 2.1, 2.2, 
                                                    3.1, 4.1, 4.2, 5.1, 5.2, 
                                                    6.1, 6.2, 7.1, 7.2, 7.3, 
                                                    8.1, 8.2, 9.1, 9.2, 10.1))
4

You probably are stuck with the data you have but the underlying problem here is that the Mixed column has compound data in it... that usually ends up causing computational problems. The alpha value and a numeric value should ideally these would be in separate columns.

Here are a couple of ways you can tease these values apart so you can get the sort order you want:

You can just sort it using dplyr::arrange after teasing the values apart.


suppressPackageStartupMessages(library(tidyverse))
tb <- tibble::tribble(
    ~aval,
    "ab1",
    "ab2",
    "ab3",
    "ab10",
    "ab11",
    "ac1",
    "ac2",
    "ac10",
    "ac12"
)


dplyr::arrange(tb, stringr::str_extract(aval, "^\\D+"), stringr::str_extract(aval, "\\d+$"))
#> # A tibble: 9 x 1
#>    aval
#>   <chr>
#> 1   ab1
#> 2  ab10
#> 3  ab11
#> 4   ab2
#> 5   ab3
#> 6   ac1
#> 7  ac10
#> 8  ac12
#> 9   ac2

Or you can break up the Mixed into two appropriate columns


suppressPackageStartupMessages(library(tidyverse))
suppressPackageStartupMessages(library(dataset))
#> Error in library(dataset): there is no package called 'dataset'

Mixed <- c('ab1', 'ab2', 'ab11', 'ac1', 'acr11', 'ac2')

d <- cbind(head(datasets::iris), 
                     alpha = stringr::str_extract(Mixed, "^\\D+"),
                        dec = stringr::str_extract(Mixed, "\\d+$"))

dplyr::arrange(d, alpha, dec)
#>   Sepal.Length Sepal.Width Petal.Length Petal.Width Species alpha dec
#> 1          5.1         3.5          1.4         0.2  setosa    ab   1
#> 2          4.7         3.2          1.3         0.2  setosa    ab  11
#> 3          4.9         3.0          1.4         0.2  setosa    ab   2
#> 4          4.6         3.1          1.5         0.2  setosa    ac   1
#> 5          5.4         3.9          1.7         0.4  setosa    ac   2
#> 6          5.0         3.6          1.4         0.2  setosa   acr  11
1 Like
5

I just realized I had a bug in the code, it was still doing a lexical sort on the dec column. This fixes it.

suppressPackageStartupMessages(library(tidyverse))
tb <- tibble::tribble(
    ~aval,
    "ab1",
    "ab2",
    "ab3",
    "ab10",
    "ab11",
    "ac1",
    "ac2",
    "ac10",
    "ac12"
)


dplyr::arrange(tb, stringr::str_extract(aval, "^\\D+"), 
                             as.integer(stringr::str_extract(aval, "\\d+$")))
#> # A tibble: 9 x 1
#>    aval
#>   <chr>
#> 1   ab1
#> 2   ab2
#> 3   ab3
#> 4  ab10
#> 5  ab11
#> 6   ac1
#> 7   ac2
#> 8  ac10
#> 9  ac12

and

suppressPackageStartupMessages(library(tidyverse))
suppressPackageStartupMessages(library(dataset))
#> Error in library(dataset): there is no package called 'dataset'

Mixed <- c('ab1', 'ab2', 'ab11', 'ac1', 'acr11', 'ac2')

d <- cbind(head(datasets::iris), 
                     alpha = stringr::str_extract(Mixed, "^\\D+"),
                        dec = as.integer(stringr::str_extract(Mixed, "\\d+$")))

dplyr::arrange(d, alpha, dec)
#>   Sepal.Length Sepal.Width Petal.Length Petal.Width Species alpha dec
#> 1          5.1         3.5          1.4         0.2  setosa    ab   1
#> 2          4.9         3.0          1.4         0.2  setosa    ab   2
#> 3          4.7         3.2          1.3         0.2  setosa    ab  11
#> 4          4.6         3.1          1.5         0.2  setosa    ac   1
#> 5          5.4         3.9          1.7         0.4  setosa    ac   2
#> 6          5.0         3.6          1.4         0.2  setosa   acr  11
6

Thank you danr and greg for taking the time to respond to my inquiry. So in my original post, I tried to simplify the code, and I know realize that I accidentally oversimplified and thus masked the problem.

In essence, I would like to add a website link to the column subject to natural sorting. In order to do so requires adding an "escape" to that column. However, when I use both "escape" and "server = FALSE" then I get the following error, "Error: attempt to select less than one element in integerOneIndex"

Below is a working example of the problem.


library(shiny)
library(DT)

number <- c('ab1', 'ab2', 'ab11', 'ac1', 'acr11', 'ac2')
link <- paste0('<a style="color:#772953" href="https://fakewebsiteinfo target="_blank">', number, '</a>')


d = cbind(
  head(iris),
  link
)

shinyApp(
  ui = fluidPage(DT::dataTableOutput('tbl'),
                 radioButtons("test", 
                              label = NULL,
                              choices = c("A", "B", "C"),
                              selected = FALSE
                              )
                 ),
  
  server = function(input, output) {
    output$tbl = DT::renderDataTable({
      
      req(input$test)
      
      d}, 
      server = FALSE,
      escape = 0,
      plugins = 'natural', options = list(
        dom = 't',
        columnDefs = list(list(type = 'natural', targets = 6))
      )
    )
  }
)

Any suggestions?

7

Shouldn't escape be FALSE and not 0?

8

Hi Greg - thanks for the response! Alas, setting "escape = FALSE" does not
change the outcome. The number still do not sort as desired.

Any other thoughts?