Pfthb

I wish to implement formatCurrency() and formatPercentage() (both from DT package) across multiple columns simultaneously in a shiny dashboard. I am using shinymaterial for the given example.

I am currently doing the following:

# The packages to load.
required_packages <- c("shiny", "shinymaterial", "DT", "tidyverse")

# This function will load in all the packages needed.
lapply(required_packages, require, character.only = TRUE)

# A table example.
ui <- material_page(
 title = "Example table",
 tags$h1("Table example"),
 material_card(
 title = "Table",
 material_row(
 DT::dataTableOutput("data_table_example")
 ),
 depth = 1
 )
)

server <- function(input, output) 

 data_table_example_data = tibble(
 Person = paste0("Person ", c(1:100)),
 `Price $` = rnorm(100, 50000, 500),
 `Cost $` = rnorm(100, 30000, 300),
 `Probability %` = rnorm(100, 0.6, 0.1),
 `Win %` = rnorm(100, 0.5, 0.2)
 )

 # This will create an output summary table
 output$data_table_example = renderDataTable(
 result = datatable(data_table_example_data, options = list(pageLength = 100, scrollX = TRUE), 
 class = 'cell-border stripe compact', rownames = FALSE) %>%
 formatCurrency("Price $") %>%
 formatCurrency("Cost $") %>%
 formatPercentage("Probability %", digits = 1) %>%
 formatPercentage("Win %", digits = 1)
 )


shinyApp(ui = ui, server = server)

However, what I wish to do is, within the renderDataTable() function, to simplify the format functions into fewer lines. For example, implement formatCurrency() in any column with a "$" and formatPercentage() in any column with a "%".

I have done a fair bit of searching for an appropriate but could not find a solution, but I assume I am just missing a fairly simple solution.

Something like:

# This will create an output summary table
 output$data_table_example = renderDataTable(
 result = datatable(data_table_example_data, options = list(pageLength = 100, scrollX = TRUE), 
 class = 'cell-border stripe compact', rownames = FALSE) %>%
 formatCurrency(grepl("$", colnames()) %>%
 formatPercentage(grepl("%", colnames()), digits = 1)
 )

A few additional points:

• The tibble will actually be a reactive


• This example is a very trivial version of a rather more complex table and set of reactives


• I do not want to implement the formatting in the reactive part since I find this then messes with the DT sorting function, since it assumes the column is a character string

Any help will be greatly appreciated

Try:

# This will create an output summary table
 output$data_table_example = renderDataTable(
 result = datatable(data_table_example_data, options = list(pageLength = 100, scrollX = TRUE), 
 class = 'cell-border stripe compact', rownames = FALSE) %>%
 formatCurrency(grepl("$", colnames(data_table_example_data)) %>%
 formatPercentage(grepl("%", colnames(data_table_example_data)), digits = 1)
 )

It seems you need to be explicit with the data so colnames() doesn't work - you need colnames(data_table_example_data).

I noticed during testing if you use grepl with rownames = TRUE that rownames becomes the first column name which means all the formatting is out by one. grep seems to not have this issue.