Rounding currency formatted numbers in DT

Question

I'm trying to get the numbers in a currency format and then round them but I get unexpected behaviour from DT (v 0.1).

I want the values looking like 808084.227872401 into £808,084.2

Here's the code:

library(DT)

m <- structure(list(A = c(808084.227872401, 1968554.9592654, 751271.053745238, 
-248530.769710688, 1022891.09543523, -407303.626363765), B = c(143073.342325492, 
-1440469.87343229, -590080.736184761, -608299.78907882, 1167155.65688074, 
803870.898483576), C = c(-447086.9382469, 606572.488852836, 89371.3745637198, 
-1496047.6143101, -410103.544644035, 1106358.3287006), D = c(0.754009573487565, 
0.364774209912866, 0.525769896339625, 0.44853704655543, 0.909551323624328, 
0.439131782157347), E = c(98.8604132297185, 98.9055931760521, 
99.3795062166865, 98.5895350315005, 101.194549174315, 102.325111315431
)), .Names = c("A", "B", "C", "D", "E"), row.names = c(NA, -6L
), class = "data.frame")

According to documentation this should work:

datatable(m) %>% formatCurrency("A", "£", digits = 1)

But I get the following error:

Error in formatCurrency(., "A", "£", digits = 1) : unused argument (digits = 1)

Then I tried another command:

datatable(m) %>% formatCurrency("A", "£") %>% formatRound("A", 1)

But it only formatted the currency without rounding it.

Any ideas?

PS. I'm aware of this answer, but I don't want to display strings because I want to sort the numbers when displayed with datatable.

Tinned

Why not use regular round first on that column and then feed to datatable? – Opinion

I don't want to change the underlying data but only the way it's displayed. Plus I'd like to know what's going on when I use DT. – Tinned

Answer 1

My conclusion is that you cannot add 2 formatters to the same column with DT, although of course I could be wrong.

Note that, even this is not explicitly stated in the documentation of DT, only one formatter can be added per column table. Note also that on the examples in the link you provide, or when you type ?formatCurrency, when they include two pipes %>%, they always affect to two different columns.

In your example, when you do

datatable(m) %>% formatRound("A", digits=1) %>% formatCurrency("A", currency="£")

the result is round for 1 digit with no currency, and if you do

datatable(m) %>% formatCurrency("A", currency="£") %>% formatRound("A", digits=1)

the result is the currency added with no rounding.

My knowledge on how R integrates with js is very limited, but looking at the R sources for the package in cran, it looks like every format command in the pipe appends a formatter, but for some reason only one formatter comes into play:

formatCurrency = function(table, columns, currency = '$', interval = 3, mark = ',') {
  formatColumns(table, columns, tplCurrency, currency, interval, mark)
}

formatRound = function(table, columns, digits = 2) {
  formatColumns(table, columns, tplRound, digits)
}

formatColumns = function(table, columns, template, ...) {
  ...
  x$options$rowCallback = appendFormatter(
    x$options$rowCallback, columns, colnames, rownames, template, ...
  )
  ...
}

appendFormatter = function(js, name, names, rownames = TRUE, template, ...) {
  ...
  JS(append(
    js, after = 1,
    template(i, ...)
  ))
}

where every formatter ends up calling formatColumns with a different template, and i resolves an id for the column. As I said, I have no idea if this is because the append operations overwrites the formatter, or if it has to do with the execution.

---

EDIT: Sorry I accidentally pressed the post button and got interruped. I actually implemented a formatter that takes more arguments. The solution is a bit convoluted but it works. This is a formatter that accepts both currency and digits:

tplRound2 = function(cols, currency, digits) {
  sprintf(
    "var d = parseFloat(data[%d]); $(this.api().cell(row, %s).node()).html(isNaN(d) ? '' : '%s' + d.toFixed(%d).toString());",
    cols, cols, currency, digits
  )
}

You need to add all these functions to your session:

formatRound2 = function(table, columns, currency, digits = 2) {
  formatColumns2(table, columns, tplRound2, currency, digits)
}

formatColumns2 = function(table, columns, template, ...) {
  if (inherits(columns, 'formula')) columns = all.vars(columns)
  x = table$x
  colnames = base::attr(x, 'colnames', exact = TRUE)
  rownames = base::attr(x, 'rownames', exact = TRUE)
  x$options$rowCallback = appendFormatter2(
    x$options$rowCallback, columns, colnames, rownames, template, ...
  )
  table$x = x
  table
}

name2int = function(name, names) {
  if (is.numeric(name)) {
    return(if (all(name > 0)) name else seq_along(names)[name])
  }
  names = setNames(seq_along(names), names)
  unname(names[name])
}

appendFormatter2 = function(js, name, names, rownames = TRUE, template, ...) {
  js = if (length(js) == 0) c('function(row, data) {', '}') else {
    unlist(strsplit(as.character(js), '\n'))
  }
  i = name2int(name, names)
  if (is.character(name) || (is.numeric(name) && !rownames)) i = i - 1
  if (any(is.na(i))) stop(
    'You specified the columns: ', paste(name, collapse = ', '), ', ',
    'but the column names of the data are ', paste(names, collapse = ', ')
  )
  JS(append(
    js, after = 1,
    template(i, ...)
  ))
}

And then you can run with the new formatter to get the desired result:

datatable(m) %>% formatRound2("A", "£", digits=1)

(However this does not add the , every 3 digits, if you really need it I could add it to the formatter...)

---

EDIT2 after comments:

This would be the formatter function for using both currency and number of digits, plus the ',' marks:

tplRound3 = function(cols, currency, digits, interval, mark) {
  sprintf(
    "var d = parseFloat(data[%d]); $(this.api().cell(row, %s).node()).html(isNaN(d) ? '' : '%s' + d.toFixed(%d).toString().replace(/\\B(?=(\\d{%d})+(?!\\d))/g, '%s'));",
    cols, cols, currency, digits, interval, mark
  )
}


formatRound3 = function(table, columns, currency, digits = 2, interval=3, mark=',') {
  formatColumns2(table, columns, tplRound3, currency, digits, interval, mark)
}

In order to use it just type

datatable(m) %>% formatRound3("A", "£", digits=1)

Horsemanship

Sorry @epo3, accidentally pressed the "post" button, edited the post with a kind of complex solution. – Horsemanship

Great answer. It would be great if you could add a comma every 3 digits. That would make the function complete. I guess you could suggest an improvement to DT. It would be a very useful function to add to the package. – Tinned

You're more than welcome. I've updated the answer with the formatter including comma every 3 digits. Yes maybe I'll do a PR to DT. Thanks. – Horsemanship

@Horsemanship nice answer! Any thoughts on how to display the a shortened abbreviation of the scale as "£13.4 k" or "-£98.5 mil" or "£53.5 bil" or "-£42.2 mil" or simply "-£7.43" whilst still allowing numerical sorting in DT? – Castellanos

Just... great answer. – Westley

Answer 2

After some research i figured that there is way to accomplish this within formatCurrency()

Changing the code to below will solve the issue:

datatable(m) %>% formatCurrency("A", '\U20AC', digits = 1)

Mahaliamahan