Search code examples
javascriptrshinydtnatural-sort

Replace numeric with string and sort as being less than numeric values

In a Shiny app I have a column of numbers in a datatable where for security reasons some values have been suppressed and we want to replace those with a specific string, which here I will call "my_string". When sorting on this column these suppressed values need to sort as though they are less than all actual numbers. In this column all values are positive, except for the suppressed values which have been coded as -1.

I've tried recoding the -1 as "my_string" (which coerces the column to character) and using the natural plug-in to sort the character-coded numerics correctly, but "my_string" is being sorted as though it is greater than all of the numeric values.

Another possible way to handle this might be to use a JavaScript callback to replace the -1 with the string, but I don't know how to write that script and properly add it to the datatable.

Here is my attempt using the natural plug-in. If it was working like I want, the line with "my_string" would be at the bottom of the list instead of the top.

# Example data, representing how the data comes to me
my_mtcars <- mtcars[1:6, 1:4]
my_mtcars[1, 4] <- -1

# Here I am recoding the -1
my_mtcars[my_mtcars == -1] <- 'my_string'

# This is our demo app.R
library(shiny)
library(DT)

ui <- fluidPage(
  dataTableOutput('example')
)

server <- function(input, output) {
  output$example <- renderDataTable(
    my_mtcars,
    server = FALSE,
    plugins = 'natural',
    options = list(columnDefs = list(list(type = 'natural', targets = '_all')))
  )
}

shinyApp(ui = ui, server = server)

enter image description here

Solution

  • This is probably easier with a custom formatter / column render function.

    See Column Rendering in the DT docs: https://rstudio.github.io/DT/options.html

    And the DataTables docs: https://datatables.net/reference/option/columns.render

    my_mtcars <- mtcars[1:6, 1:4]
my_mtcars[1, 4] <- -1

formatSuppressedValues <- JS("
  function(data, type) {
    if (type !== 'display') return data;
    if (data !== -1) return data;
    return 'my_string';
  }
")

library(shiny)
library(DT)

ui <- fluidPage(
  DT::dataTableOutput('example')
)

server <- function(input, output) {
  output$example <- DT::renderDataTable(
    my_mtcars,
    server = FALSE,
    options = list(
      columnDefs = list(list(
        targets = '_all',
        render = formatSuppressedValues
      ))
    )
  )
}

shinyApp(ui = ui, server = server)