Shiny DataTables Column Summ Callback
I am working on implementing the function callback
for DataTables
in an application shiny
similar to this example from the DataTables
forum. My thought so far not to read the documentation DT
(section 4.4) was that it would be possible to apply the same class sum
via an argument columnDefs
options
like below, but that also makes sense if I just know where to put the JS argument to do classes manually, as in the link.
You can remove all arguments columnDefs
and callback
to see an example of the starting point.
app.R
library(shiny)
library(DT)
ui <- fluidPage(
title = 'Select Table Rows',
hr(),
h1('A Server-side Table'),
fluidRow(
column(9, DT::dataTableOutput('x3'))
)
)
server <- function(input, output, session) {
# server-side processing
mtcars2 = mtcars[, 1:8]
output$x3 = DT::renderDataTable(DT::datatable(mtcars2,
extensions = 'Buttons',
options = list(
scrollX = TRUE,
scrollY = TRUE,
pageLength = 10,
order = list(list(1, 'asc')),
fixedHeader = TRUE,
dom = 'Blrtip',
buttons = c('copy', 'csv', 'excel', 'pdf', 'print')
# columnDefs = JS("[
# { className: 'sum', 'targets': [ 1,2 ] }
# ]")
# ),
#callback = JS(
# " function(row, data, start, end, display) {
# var api = this.api();
#
# api.columns('.sum', { page: 'current' }).every(function () {
# var sum = api
# .cells( null, this.index(), { page: 'current'} )
# .render('display')
# .reduce(function (a, b) {
# var x = parseFloat(a) || 0;
# var y = parseFloat(b) || 0;
# return x + y;
# }, 0);
# console.log(this.index() +' '+ sum); //alert(sum);
# $(this.footer()).html(sum);
# });
#}"
)
)
)
}
shinyApp(ui = ui, server = server)
Final solution:
library(shiny)
library(DT)
ui <- fluidPage(
title = 'Select Table Rows',
hr(),
h1('A Server-side Table'),
fluidRow(
column(9, DT::dataTableOutput('x3'))
)
)
server <- function(input, output, session) {
# server-side processing
mtcars2 = mtcars[, 1:8]
sketch <- htmltools::withTags(table(
class = "display",
style = "bootstrap",
tableHeader(colnames(mtcars2)),
tableFooter(colnames(mtcars2))
))
output$x3 = DT::renderDataTable(DT::datatable(mtcars2,
container = sketch,
extensions = 'Buttons',
options = list(
scrollX = TRUE,
scrollY = TRUE,
pageLength = 10,
order = list(list(1, 'asc')),
dom = 'Blrtip',
buttons = c('copy', 'csv', 'excel', 'pdf', 'print'),
footerCallback = JS(
"function( tfoot, data, start, end, display ) {",
"var api = this.api(), data;",
"total = api.column( 1, { page: 'current'} ).data().reduce( function ( a, b ) {return a + b;} )",
"total1 = api.column( 2, { page: 'current'} ).data().reduce( function ( a, b ) {return a + b;} )",
"total2 = api.column( 3, { page: 'current'} ).data().reduce( function ( a, b ) {return a + b;} )",
"total3 = api.column( 4, { page: 'current'} ).data().reduce( function ( a, b ) {return a + b;} )",
"total4 = api.column( 5, { page: 'current'} ).data().reduce( function ( a, b ) {return a + b;} )",
"total5 = api.column( 6, { page: 'current'} ).data().reduce( function ( a, b ) {return a + b;} )",
"total6 = api.column( 7, { page: 'current'} ).data().reduce( function ( a, b ) {return a + b;} )",
"total7 = api.column( 8, { page: 'current'} ).data().reduce( function ( a, b ) {return a + b;} )",
"$( api.column( 1 ).footer() ).html(total.toFixed(2));
$( api.column( 2 ).footer() ).html(total1.toFixed(2));
$( api.column( 3 ).footer() ).html(total2.toFixed(2));
$( api.column( 4 ).footer() ).html(total3.toFixed(2));
$( api.column( 5 ).footer() ).html(total4.toFixed(2));
$( api.column( 6 ).footer() ).html(total5.toFixed(2));
$( api.column( 7 ).footer() ).html(total6.toFixed(2));
$( api.column( 8 ).footer() ).html(total7.toFixed(2));",
"}"
))
))
}
shinyApp(ui = ui, server = server)
I realize this is probably bad form for JS, however it works best in my case, so I can apply different options for each (some currency symbols, some averages, different decimal prefixes, etc.).
source to share
To show the sum / total in the footer, you have to add a container to the table as shown below. I also changed the JS code: the below version should work. Unfortunately I can't tell what was wrong with your JS code as I'm not a javascript guy. You can play with the HTML part (...) to change the presentation of your sums.
server <- function(input, output, session) {
# server-side processing
mtcars2 = mtcars[, 1:8]
sketch = htmltools::withTags(table(tableFooter(c("",0,0,0,0,0,0,0,0))))
output$x3 = DT::renderDataTable(DT::datatable(mtcars2, container = sketch,
extensions = 'Buttons',
options = list(
scrollX = TRUE,
scrollY = TRUE,
pageLength = 10,
order = list(list(1, 'asc')),
fixedHeader = TRUE,
dom = 'Blrtip',
buttons = c('copy', 'csv', 'excel', 'pdf', 'print')
footerCallback = JS(
"function( tfoot, data, start, end, display ) {",
"var api = this.api();",
"$( api.column( 1 ).footer() ).html(",
"api.column( 1).data().reduce( function ( a, b ) {",
"return a + b;",
"} )",
");",
"$( api.column( 2 ).footer() ).html(",
"api.column( 2 ).data().reduce( function ( a, b ) {",
"return a + b;",
"} )",
");",
"$( api.column( 3 ).footer() ).html(",
"api.column( 3 ).data().reduce( function ( a, b ) {",
"return a + b;",
"} )",
");",
"$( api.column( 4 ).footer() ).html(",
"api.column( 4 ).data().reduce( function ( a, b ) {",
"return a + b;",
"} )",
");",
"$( api.column( 5 ).footer() ).html(",
"api.column( 5 ).data().reduce( function ( a, b ) {",
"return a + b;",
"} )",
");",
"$( api.column( 6 ).footer() ).html(",
"api.column( 6 ).data().reduce( function ( a, b ) {",
"return a + b;",
"} )",
");",
"$( api.column( 7 ).footer() ).html(",
"api.column( 7 ).data().reduce( function ( a, b ) {",
"return a + b;",
"} )",
");",
"$( api.column( 8 ).footer() ).html(",
"api.column( 8 ).data().reduce( function ( a, b ) {",
"return a + b;",
"} )",
");",
"}")
)
)
)
}
source to share
I developed this function as a possible solution for totals and formats. If you need to perform a complex operation, just run the code before and then insert the values as operation = "custom"
. Hope this helps.
library(DT)
dat <- iris[1:4]
sketch <- htmltools::tags$table(
tableHeader(names(dat)),
tableFooter(rep("", ncol(dat))))
js_op <- function(column, operation, name = "", signif = 3) {
# Auxiliar function for mean
aux <- ifelse(
operation == "mean",
paste0("map(function(num) { return num/data.length; })."), "")
# Decimals to consider
signif <- 10^signif
# Operation
if (operation %in% c("sum", "mean"))
operation <- paste0("Math.round((a+b)*",signif,")/",signif)
if (operation == "count")
operation <- "data.length"
if (operation == "custom")
return(paste0("$(api.column(", column, ").footer()).html('", name, "')"))
# Result
res <- paste0(
"$(api.column(", column, ").footer()).html('", name, "'+",
"api.column(", column, ").data().", aux, "reduce( function ( a, b ) {",
"return ", operation, ";",
"} ));")
return(res)
}
javascript <- JS(
"function(tfoot, data, start, end, display ) {",
"var api = this.api(), data;",
js_op(0, operation = "count", name = "Counter: "),
js_op(1, operation = "sum", name = "Sum: "),
js_op(2, operation = "mean", name = "Mean: "),
js_op(3, operation = "custom", name = "Something"),
";}")
datatable(iris[,1:4], rownames = FALSE, container = sketch,
options = list(footerCallback = javascript))
source to share