How to format data table values when using a reactive data frame?-CodePudding

In the below MWE code, I was previously using base Shiny renderTable() and successfully formatting data table outputs using this simple line of code: format1 <- function(x){format(x, nsmall = 2, big.mark=",")}.

But since the below MWE is being deployed in an App that manipulates a much larger data frame, I switched over to the DT package for rendering data tables, given it's obvious benefits.

However, the old code I was using prior to DT, for formatting table outputs, doesn't work with DT! So, using DT package, how do I format the numeric outputs for specified columns (ColA and ColB in this case), so they show 2 decimals, every 3 0's are separated by a ",", and possibly be able to show a currency such as € at the beginning of specified columns? I don't want all columns to be automatically formatted in the same way, because the data table for the full App is more complex than in this MWE and some columns will need different formats.

I researched DT formatting solutions, but I seem to be hitting a brick wall because my data frame is reactive.

MWE code:

library(dplyr)
library(DT)
library(shiny)
library(tidyverse)

ui <-
  fluidPage(
    h3("Data table:"),
    tableOutput("data"),
    h3("Sum the data table columns:"),
    radioButtons(
      inputId = "grouping",
      label = NULL,
      choiceNames = c("By period 1", "By period 2"),
      choiceValues = c("Period_1", "Period_2"),
      selected = "Period_1",
      inline = TRUE
    ),
    DT::dataTableOutput("sums")
  )

server <- function(input, output, session) {
  data <- reactive({
    data.frame(
      Period_1 = c("2020-01", "2020-02", "2020-03", "2020-01", "2020-02", "2020-03"),
      Period_2 = c(1, 2, 3, 3, 1, 2),
      ColA = c(1000.01, 20, 30, 40, 50, 60),
      ColB = c(15.06, 25, 35, 45, 55, 65)
    )
  })
  
  summed_data <- reactive({
    data() %>%
      group_by(!!sym(input$grouping)) %>%
      select("ColA","ColB") %>%
      summarise(across(everything(), sum))
    })
  
  output$data <- renderTable(data())
  
  output$sums <- renderDT(summed_data(),rownames = FALSE)
  
}

shinyApp(ui, server)

CodePudding user response：

The DT package offers several formatter functions to format table columns, e.g. in your case formatCurrency seems appropriate:

library(dplyr)
library(DT)
library(shiny)
library(tidyverse)

ui <-
  fluidPage(
    h3("Data table:"),
    tableOutput("data"),
    h3("Sum the data table columns:"),
    radioButtons(
      inputId = "grouping",
      label = NULL,
      choiceNames = c("By period 1", "By period 2"),
      choiceValues = c("Period_1", "Period_2"),
      selected = "Period_1",
      inline = TRUE
    ),
    DT::dataTableOutput("sums")
  )

server <- function(input, output, session) {
  data <- reactive({
    data.frame(
      Period_1 = c("2020-01", "2020-02", "2020-03", "2020-01", "2020-02", "2020-03"),
      Period_2 = c(1, 2, 3, 3, 1, 2),
      ColA = c(1000.01, 20, 30, 40, 50, 60),
      ColB = c(15.06, 25, 35, 45, 55, 65)
    )
  })
  
  summed_data <- reactive({
    data() %>%
      group_by(!!sym(input$grouping)) %>%
      select("ColA","ColB") %>%
      summarise(across(everything(), sum))
  })
  output$data <- renderTable(data())
  
  output$sums <- renderDT({
    summed_data() %>% 
      datatable(rownames = FALSE) %>% 
      formatCurrency(c("ColA", "ColB"), currency = '\U20AC', digits = 2)
  })
  
}

shinyApp(ui, server)
#> 
#> Listening on http://127.0.0.1:3057
#> Adding missing grouping variables: `Period_1`