DT数据中一列的总和

问题描述

我正在尝试使用DT :: datatable计算我闪亮的应用程序中列的总计。总的来说，我指的是表中所有元素的总和，而不仅仅是当前分页中显示的内容。遵循此

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

解决方案

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))

这篇关于DT数据中一列的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！