9

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)
davechilders
  • 8,693
  • 2
  • 18
  • 18

3 Answers3

7

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.

imesner
  • 86
  • 1
  • 4
1

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.

IRTFM
  • 258,963
  • 21
  • 364
  • 487
jdharrison
  • 30,085
  • 4
  • 77
  • 89
  • This option to `renderDataTable` is the type of thing I was looking for. Can't figure out why this doesn't work. Perhaps this can be accomplished through literal JavaScript? – davechilders Oct 27 '14 at 13:16
0

The mixedsort and mixedorder functions in package gtools can do that:

x <- seq(8000, 12000, by = 1000)
x <- scales::dollar(x)
d <- data.frame(x)

 mixedsort(d$x) # not much of a test but also give same results with mixedsort(rev(d$x))

[1] $8,000  $9,000  $10,000 $11,000 $12,000
Levels: $10,000 $11,000 $12,000 $8,000 $9,000

Notice that your data.frame call created factors. You might not want that and if not should include stringsAsFactors=FALSE. I do not see any mention in the help page about commas and so you might want to apply a gsub("[,]", "", d$x) before the mixedsort.

IRTFM
  • 258,963
  • 21
  • 364
  • 487