CODEWITHSUNDEEP
rshinydt
Omar Gonzales
Omar Gonzales

Reputation: 4008

R DataTable: How to format a column to show numbers with comma for thousends?

Inside Shiny, I'm rendering a data table.

I was reading the options for the DT package, and I'm founding that to do this I need to know Javascript.

https://rstudio.github.io/DT/options.html

m = as.data.frame(matrix(round(rnorm(100, 1e5, 1e6)), 20))
datatable(m, options = list(
  rowCallback = JS(
    "function(row, data) {",
    "var num = '$' + data[3].toString().replace(/\\B(?=(\\d{3})+(?!\\d))/g, ',');",
    "$('td:eq(3)', row).html(num);",
    "}")
), callback = JS("table.order([3, 'asc']).draw();"))

I don't know a lot about JS, so I'm asking this question. My biggest number right now is: 22381, and lowest 0.

I would like to display: 22381 as 22,381. If posible: 22381 as S/.22,381.

This is how my DataTable is rendering rightnow, I'm using the options to order (desc) the revenue.

output$products <- renderDataTable(products_ss(),
                                           options = list(
                                             order = list(list(2, 'desc'))
                                           ))

UPDATE 1: How to apply formatCurrency?

Applying it like this gives error:

You specified the columns: itemRevenue, but the column names of the data are

output$productos_sams <- renderDataTable(productos_samsung()  %>% 
                                         formatCurrency(c('itemRevenue'), currency = ' S/.',
                                                        interval = 3, mark = ',', before = FALSE),
                                       options = list(
                                         order = list(list(2, 'desc'))
                                       ))

I've changed your answer to match my column name.

Upvotes: 2

Views: 6663

Answers (1)

A. Suliman
A. Suliman

Reputation: 13135

You are looking for DT::formatCurrency

library(DT)
datatable(m) %>% 
             formatCurrency(c('V1', 'V2', 'V3', 'V4', 'V5'), currency = ' S/.',
                            interval = 3, mark = ',', before = FALSE)

Update1:

library(shiny)
library(DT)
#Use DT::dataTableOutput and DT::renderDataTable as shiny also has these functions name
shinyApp(
  ui = fluidPage(fluidRow(column(12, DT::dataTableOutput('tbl')))),
  server = function(input, output) {
    m <- reactive({m <- as.data.frame(matrix(round(rnorm(100, 1e5, 1e6)), 20))})
    output$tbl = DT::renderDataTable(
      datatable(m()) %>% formatCurrency(c('V1', 'V2', 'V3', 'V4', 'V5'), currency = ' S/.',
                                           interval = 3, mark = ',', before = FALSE) 
    )
  }
)

As in https://rstudio.github.io/DT/shiny.html The first argument of DT::renderDT() can be either a data object or a table widget returned by datatable(). The latter form can be useful when you want to manipulate the widget before rendering it in Shiny, e.g. you may apply a formatting function to a table widget:. So you need to datatable(m()) before passing it to another step.

Upvotes: 6

Related Questions