Total of a column in DT dataTables in shiny

Question

I am trying to calculate the total of a column in my shiny app using DT::datatable. By total I mean the sum of all elements in a table not just what is showing in the current pagination. Following this example the following code should work (but it doesn't):

jsCode <- "function(row, data, start, end, display) {

                  var api = this.api(), data;

                  total = api.column(1, {page: 'all'}).data().reduce( function(a, b) { return a + b}, 0); 

                  $( api.column(1).footer() ).html('Total: ' + total);
                  }"

All I am getting from this is the sum of the elements in the current pagination. Full code below:

library(shiny)
library(DT)

set.seed(2282018)
company <- data.frame(Company = letters[1:30], Units = round(runif(30, 
                                                             1000, 10e6), 0), 
                      Price = scales::dollar(runif(30, 200, 1230)), stringsAsFactors = F)

jsCode <- "function(row, data, start, end, display) {

                  var api = this.api(), data;

                  total = api.column(1, {page: 'all'}).data().reduce( function(a, b) { return a + b}, 0); 

                  $( api.column(1).footer() ).html('Total: ' + total);
                  }"

# UI ---- 
ui <- function(){

  fluidPage(

    sidebarLayout(

      sidebarPanel(numericInput("nums", label = "Num Input", value = 1, min = 1, max = 10)),

      mainPanel(dataTableOutput("mytable"))

    )

  )

}

# server ----
server <- function(input, output, session){

  cont <- htmltools::withTags(table(
    tableHeader(names(company)),tableFooter(names(company))
  ))

  output$mytable <- DT::renderDataTable(  {

    DT::datatable(company,
                  container = cont,
                  caption = tags$caption("Example"), 
                  filter = "none", 
                  rownames = F,
                  options = list(autoWidth = T, 
                                 pageLength = 10, 
                                 scrollCollapse = T,
                                 dom = 'lftp', 
                                 footerCallback = JS(jsCode))
                  )
  }
  )
}

runApp(list(ui = ui, server = server))

Thank you

Monosyllable

@PorkChop no it isn't. That thread helped me to get this far but it only gives me the subtotal (or the total by pagination). I need the total of all elements in the column. Thanks for answering the other thread btw... – Monosyllable

I marked as duplicated because the questions are the same, you should maybe bump the other one – Cordovan

Ok, so how do I get my question answered there? I agree that the nature of the question is similar but the solution in the first question does not work in this particular scenario. – Monosyllable

That works outside Shiny, or inside Shiny but at condition to set the option server = FALSE. As said here, When using DataTables in server-side processing mode [...] the selector can only select those rows which are on the current page.. – Chenault

Thanks for chiming in. It makes more sense now – Monosyllable

Answer 1

Maybe you can write a workaround: such as below:

library(shiny)
library(DT)

set.seed(2282018)
company <- data.frame(Company = letters[1:30], Units = round(runif(30,  1000, 10e6), 0), Price = scales::dollar(runif(30, 200, 1230)), stringsAsFactors = F)
jsCode <- "function(row, data, start, end, display) {var api = this.api(), data;$( api.column(1).footer() ).html('Total: ' + MYTOTAL);}"

# Workaround
getTotal <- function(data,index){

  if(index < 1 || index > ncol(data)){
    return("")
  }
  col <- data[,index]
  col <- gsub("[$]","",col)
  col <- gsub("[£]","",col)
  col <- gsub("[,]","",col)
  col <- suppressWarnings(as.numeric(col))
  if(all(is.na(col))){
    return("")
  }
  sum(col)
}


ui <- function(){
  fluidPage(
    sidebarLayout(
      sidebarPanel(numericInput("nums", label = "Num Input", value = 1, min = 1, max = 10)),
      mainPanel(dataTableOutput("mytable"))
    )
  )
}

server <- function(input, output, session){

  Total <- reactive({
    getTotal(company,2)
  })

  cont <- htmltools::withTags(table(
    tableHeader(names(company)),tableFooter(names(company))
  ))

  output$mytable <- DT::renderDataTable(  {
    jsCode <- sub("MYTOTAL",Total(),jsCode)
    DT::datatable(company,
                  container = cont,
                  caption = tags$caption("Example"), 
                  filter = "none", 
                  rownames = F,
                  options = list(autoWidth = T, 
                                 pageLength = 10, 
                                 scrollCollapse = T,
                                 dom = 'lftp', 
                                 footerCallback = JS(jsCode))
    )
  }
  )
}

runApp(list(ui = ui, server = server))

Cordovan

Thanks. Do you know why the `.colum(x, {page: 'all'}' does not work? – Monosyllable

I did try this line by line with the one provided by the footer callback but it doesnt work. datatables.net/examples/advanced_init/footer_callback.html its very strange. I suspect there is some dependency missing. I did "hacks" like displaying the whole table without paging and calculating it and only show 500px, but its not a good solution – Cordovan

btw I was trying to get .column(x).data() to work with no success – Cordovan

PorkChop, see my comment below the OP. That explains why colum(x, {page: 'all'}) does not work. This is due to the server mode processing. – Chenault