Wen Qin
Wen Qin

Reputation: 11

Format number into K(thousand), M(million) in Shiny DataTables

I'm looking for a straight forward way to change the formatting of numbers into K,M in shiny dataTables. Preferably with something like formatCurrency. I don't want to write k, m functions to convert number into string in order to do the formatting as it makes it difficult to sort rows by value.

Upvotes: 1

Views: 2964

Answers (3)

Binwin Viju
Binwin Viju

Reputation: 334

If you are using DataTables, to get the data as Unit format i.e

10000 -> 10K

we can use render function

"render": function ( data ) {
                                    if(data > 999 && data < 1000000) {
                                      return data/1000+' K' 
                                    }
                                    else if(data > 1000000){
                                        return data/1000000+' M'
                                    }
                                    else{
                                        return data
                                    }    
                                  }
                            }

Upvotes: 0

R.Sav
R.Sav

Reputation: 73

Alternatively, if you want a non-JavaScript method, you could use the colFormat function used with the reactable package. Unfortunately, there is no automatic millions option but it's pretty easy to replicate if you divide the original data and add the labels on with colFormat.

Product <- c('Apples','Oranges','Pears')
Revenue <- c(212384903, 23438872, 26443879)
df <- data.frame(Product,Revenue)

df$Revenue_millions <- dfeg$Revenue/1000000

reactable(df,
  showSortable = TRUE,
  columns = list(
  Revenue_millions = colDef(format = colFormat(prefix = "£", separators = TRUE,digits=1,suffix = "m"))))

The data should now sort correctly

Upvotes: 0

greg L
greg L

Reputation: 4134

There's no built-in way to do this, but it's not too bad to write your own format function in JavaScript that doesn't break row sorting.

See Column Rendering in the DT docs for how to do this: https://rstudio.github.io/DT/options.html

And this will also help: https://datatables.net/reference/option/columns.render

Here's an example of a custom thousands formatter that rounds to 1 decimal place:

library(DT)

formatThousands <- JS(
  "function(data) {",
  "return (data / 1000).toFixed(1) + 'K'",
  "}")

datatable(datasets::rock, rownames = FALSE, options = list(
  columnDefs = list(list(
    targets = 0:1, render = formatThousands
  ))
))

Upvotes: 3

Related Questions