Printing like a character but sorting like numeric in Shiny and DataTable

Question

I would like to sort a DataTable column that is formatted with dollars (and thus is a character). I have used scales::dollar() for formatting. This converts the field to a character which causes sorting problems (for instance, "$8" > "$10").

How can I sort the field as if it were numeric? Alternatively, can I keep the field as numeric and just print with dollar formatting?

app.R (requires Shiny 0.10.2)

server <- function(input, output) {
  output$foo_table <- renderDataTable({
    x <- seq(8000, 12000, by = 1000)
    x <- scales::dollar(x)
    d <- data.frame(x, stringsAsFactors = FALSE)
    d
  })
}

ui <- shinyUI(fluidPage(
    mainPanel(dataTableOutput("foo_table"))
  )
)

shinyApp(ui = ui, server = server)

Answer 1

A bit late, but the DT Package now has format functions, including formatCurrency:

# format the columns A and C as currency, and D as percentages
datatable(m) %>% formatCurrency(c('A', 'C')) %>% formatPercentage('D', 2)

From the Functions page:

Under the hood, these formatting functions are just wrappers for the rowCallback option to generate the appropriate JavaScript code. Similarly, there is a formatDate() function that can be used to format date/time columns. It has a method argument that takes values from a list of possible conversion methods: toDateString, toISOString, toLocaleDateString, toLocaleString, toLocaleTimeString, toString, toTimeString, toUTCString.

Answer 2

As of DataTables 1.10 you should be able to sort with currency http://datatables.net/reference/option/columns.type. In options it should suffice to give a type = 'num-fmt' to column index zero. This would correspond to columnDefs = list(list(targets = c(0), type = "num-fmt")) in `options. The following should work but does not for me:

library(shiny)
server <- function(input, output) {
  output$foo_table <- renderDataTable({
    x <- seq(8000, 12000, by = 1000)
    x <- scales::dollar(x)
    d <- data.frame(x)
    d
  }
  , options = list(
    columnDefs = list(list(targets = c(0), type = "num-fmt"))
  )
  )
}

ui <- shinyUI(fluidPage(
  mainPanel(dataTableOutput("foo_table"))
)
)

shinyApp(ui = ui, server = server)

Maybe @yihui can shed some light on the issue.