DT in Shiny and R: Custom number formatting

Question

I have a shiny-app that displays a datatable using the DT-package. What I want is to be able to format columns in a custom way. For example I want a currency value to be displayed like this: 1,234.50€ instead of the DT-way, which displays it like this $1,234.5 (notice the change in the symbol, the position of the currency-symbol as well as the numbers after the decimal-point).

An MWE looks like this:

library(shiny)
library(DT)

shinyApp(
  # UI
  ui = fluidPage(DT::dataTableOutput('tbl')),

  # SERVER
  server = function(input, output) {
    dat <- data.frame(cur = 1234.5, # supposed to be displayed as: 1,234.50€ | Bad! 
                                         # displayed as $1,234.5
                      perc = 0.123456, # 12.34% | Good!
                      num = 1000) # 1,000 | Bad! displayed as 1000

    # render DT
    output$tbl = DT::renderDataTable(
      datatable(dat) %>%
        formatCurrency(c('cur'), "$") %>%
        formatPercentage('perc', 2) %>%
        formatRound('num', digits = 0)
    )
  }
)

It does a fairly good job, however, when changing the currency-symbol to €, the symbol disappears. When inserting another character like "E", the character is still displayed at the beginning not at the end. Furthermore, the numeric value does not get a "big-mark".

Any ideas?

For numeric you could try: `formatCurrency('num', currency = "", interval = 3, mark = ",", digits = 0)`. For Euro, `formatCurrency(c('cur'), currency = "€", interval = 3, mark = ",", digits = 1)`. I'm not sure how to get it on the right though — Chris, Oct 16 '15 at 13:13
The `formatCurrency`-approach to get 1,000 is good! However, I still do not see a €-sign for the currency number. Same applies if I use `\U20AC` instead of `€` as suggested in `?formatCurrency` — David, Oct 16 '15 at 13:16

score 2 · Accepted Answer · answered Oct 20 '15 at 15:54

2

You can change the position of the currency symbol in the .js file from the datatable package.

Edit the line of the DTWidget.formatCurrency function

 $(thiz.api().cell(row, col).node()).html(currency + markInterval(d, interval, mark));

to simply

 $(thiz.api().cell(row, col).node()).html(markInterval(d, interval, mark) + currency);

in the DT/htmlwidgets/datatables.js file in the directory of your R librarys.

As for the € Symbol,

formatCurrency(c('cur'), currency = "\U20AC", interval = 3, mark = ",", digits = 2)

does work for me, thats what you tried and you don't see any symbol?

answered Oct 20 '15 at 15:54

Sebastian

2,430
4
23
40

Interesting approach. I would have to copy the library direction to other machines if I want to get the same results right? – David Oct 21 '15 at 09:50
1

Yeah you would have to link that library to every machine, run on a server only that library of course. Also after every DT package update you would have to adjust the code. So this is kinda gimmiky, you could suggest this to the development team on https://github.com/rstudio/DT to implemend an option in the r code to arrange the currency symbol, as this is very easy to adjust it might get picked up in the next update. – Sebastian Oct 21 '15 at 10:09
1

With regard to the latter solution: When I type in `shinyApp(ui = fluidPage(DT::dataTableOutput('tbl')), server = function(input, output) {dat <- data.frame(cur = 1234.5) output$tbl = DT::renderDataTable( datatable(dat) %>% formatCurrency(c('cur'), currency = "\U20AC", interval = 3, mark = ",", digits = 2) ) } )` I get an error that `digits` is an unused argument... Without the `digits` argument I still do not see the currency symbol. [See Here](http://puu.sh/kShhA/0398a4e597.png) – David Oct 21 '15 at 14:05
Hm, that is kinda strange, your code runs fine on my windows machine and ubuntu server. My package versions are dplyr_0.4.2 DT_0.1.34 shiny_0.12.2 with R version 3.2.2, maybe you have to update your packages? ( use sessionInfo() to check your loaded versions ) – Sebastian Oct 21 '15 at 15:26
I use R version 3.2.2, DT_0.1 (latest CRAN release), shiny 0.12.2 in RStudio 0.99.482 on Windows 7 SP1 64bit. The system language is German, therefore UTF-8 should be working, especially the € sign – David Oct 21 '15 at 16:05
Hm, maybe try the development version of DT: devtools::install_github('rstudio/DT') . – Sebastian Oct 21 '15 at 16:46
1

Still no € sign. I suspect it has something to do with my machine instead of the R-code... Thank you anyway! – David Oct 22 '15 at 12:40

DT in Shiny and R: Custom number formatting

1 Answers1

Linked