Control number formatting in Shiny's implementation of DataTable

Question

Aim

Render large numbers as with thousands separators in a shiny DataTable by default.

Problem

The documentation reads (to me) like it should already be showing thousand separators. Am I suffering compatibility issues or do I need to add more to my code, and if so what and how?

[UPDATE] There is another SO question that attempts to resolve sorting of pre-formatted data in shiny. The question is unresolved but suggests another route - formatting beforehand. Is this an advisable route and if so, how to resolve correctly the other OP's issues with sorting?

Background

I am trying to improve the output of a table in shiny to improve readability. I have successfully used another SO question to freeze the headers but am struggling to get numbers formatted correctly.

MWE

library(shiny)
runApp(
  list(ui = fluidPage(
    tagList(
      singleton(tags$head(tags$script(src='//cdn.datatables.net/fixedheader/2.1.2/js/dataTables.fixedHeader.min.js',type='text/javascript'))),
      singleton(tags$head(tags$link(href='//cdn.datatables.net/fixedheader/2.1.2/css/dataTables.fixedHeader.css',rel='stylesheet',type='text/css')))
    ), 

    dataTableOutput("mytable")
  )
  , server = function(input, output, session){
    output$mytable <- renderDataTable(iris*1000,
                                      options = list(
                                        pageLength = 50,
                                        language.thousands=",",
                                        initComplete = I("function(settings, json){
                                          new $.fn.dataTable.FixedHeader(this, {
                                            left:   true
                                          } );
                                        }")
                                      )
    )
  })
)

Documentation

Looking at the DataTable documentation it appears like setting the language.thousands should be sufficient:

DataTables' built in number formatter (formatNumberDT) is used to format large numbers that are used in the table information.

language.thousands

By default DataTables will use the character specified in language.thousandsDT (in turn, that, by default, is a comma) as the thousands separator.

formatNumber

In formatNumber it has a function that could be added, however, this is very new to me so I'm unsure exactly how this function would be utilised in the context of the existing code.

$('#example').dataTable( {
  "formatNumber": function ( toFormat ) {
    return toFormat.toString().replace(
      /\B(?=(\d{3})+(?!\d))/g, "'"
    );
  };
} );

Browser details

Chrome: Version 39.0.2171.71 m

Answer 1

I know this is quite an old post, however was struggling with this today too so thought that someone else may get value from this solution.

Since the question was asked the DT package was released by RStudio (Github or CRAN). At first glance it isn't too obvious how to format numbers as their isn't a corresponding formatNumber function like their is formatCurrency. The way around it is to use the formatCurrency and just set the currency parameter to nothing. Example below.

library(shiny)
library(DT)
runApp(
  list(ui = fluidPage(
    DT::dataTableOutput("mytable")
  ),
  server = function(input, output, session) {
    output$mytable <- DT::renderDataTable(
      DT::datatable(iris*1000, 
                    options = list(pageLength = 50, 
                                   columnDefs = list(list(className = 'dt-left', 
                                                          targets = 0:4)))) %>% 
        formatCurrency(1:4, '')
    )
  }
  )
)

Answer 2

Problem with format an prettyNum is the fact that these functions produce characters instead of numerics. Thus, it is then impossible to style your rendered table. A solution may be the following with this example :

  output$tab <- renderDataTable({ 
  datatable(mytable) %>%
      formatCurrency(columns = ..., currency = "", interval = 3, mark = ",") %>%
      formatStyle(
      columns = ...,
      color = styleInterval(myThreshold, c("black", "red"))) }) 

Answer 3

Try formatting the df you pass to renderDatatable() with thousands formatting

df$x <- format(df$x, big.mark=',', scientific=FALSE) 

Or

df$x<-prettyNum(df$x, big.mark = ",")