I am working on a Shiny app that uses the DT package and some JavaScript to create a nice nested table. In the child table the user is allowed to edit the 2nd column (Mix (%)). Upon editing that column the 6th column (GRPs/TRPs) will also update. When updating the mix (%) I am attempting to store the total GRPs/TRPs (or points) then use that total to update the GRPs/TRPs column. See the equation below. However, the total points does not update correctly when editing the mix %.
The main issue is in CosterTableJS in the footerCallback
totalGRPs/TRPs = sum(GRPs/TRPs column)
GRPs/TRPs = (mix%/100)*totalGRPs/TRPs
The below will also use https://github.com/ejbeaty/CellEdit (MIT License)
# parent table
parent_table <- data.frame(structure(list(Market = "ABILENE-SWEETWATER", `Total GRPs/TRPs` = 500L,
Cost = 82500, Impressions = 877215, Population = 175443), .Names = c("Market",
"Total GRPs/TRPs", "Cost", "Impressions", "Population"), row.names = c(NA,
-1L), class = "data.frame"))
# child table
child_table <- structure(list(Market = c("ABILENE-SWEETWATER", "ABILENE-SWEETWATER",
"ABILENE-SWEETWATER", "ABILENE-SWEETWATER", "ABILENE-SWEETWATER",
"ABILENE-SWEETWATER", "ABILENE-SWEETWATER", "ABILENE-SWEETWATER",
"ABILENE-SWEETWATER"), Daypart = c("Early Morning", "Daytime",
"Early Fringe", "Early News", "Prime Access", "Prime Time", "Late News",
"Late Fringe", "Non-Linear"), `Mix (%)` = c(10, 10, 10, 10, 10,
10, 10, 10, 20), Week = c(1, 1, 1, 1, 1, 1, 1, 1, 1), Weeks = c(5L,
5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L), `Override CPP` = c(16, 18, 17,
24, 27, 55, 43, 22, 58), `GRPs/TRPs` = c(10, 10, 10, 10, 10,
10, 10, 10, 20), Cost = c(160, 180, 170, 240, 270, 550, 430,
220, 1160), Impressions = c(17544.3, 17544.3, 17544.3, 17544.3,
17544.3, 17544.3, 17544.3, 17544.3, 35088.6), Population = c(175443,
175443, 175443, 175443, 175443, 175443, 175443, 175443, 175443
), `Full Flight Cost` = c(800, 900, 850, 1200, 1350, 2750, 2150,
1100, 5800), `Full Flight GRPs/TRPs` = c(50, 50, 50, 50, 50,
50, 50, 50, 100), `Full Flight Impressions` = c(87721.5, 87721.5,
87721.5, 87721.5, 87721.5, 87721.5, 87721.5, 87721.5, 175443)), .Names = c("Market",
"Daypart", "Mix (%)", "Week", "Weeks", "Override CPP", "GRPs/TRPs",
"Cost", "Impressions", "Population", "Full Flight Cost", "Full Flight GRPs/TRPs",
"Full Flight Impressions"), row.names = c(NA, -9L), class = "data.frame")
# Module to create the nested structure of the table
NestedData <- function(dat, children) {
stopifnot(length(children) == nrow(dat))
g <- function(d){
if(is.data.frame(d)){
purrr::transpose(d)
}else{
purrr::transpose(NestedData(d[[1]], children = d$children))
}
}
subdats <- lapply(children, g)
oplus <- sapply(subdats, function(x) if(length(x)) "<img src='https://raw.githubusercontent.com/DataTables/DataTables/master/examples/resources/details_open.png'/>" else "")
cbind(" " = oplus, dat, "_details" = I(subdats), stringsAsFactors = FALSE)
}
table <- NestedData(
dat = parent_table,
children = split(child_table, child_table$Market)
)
# Whether to show row names (set TRUE or FALSE)
rowNames <- FALSE
colIdx <- as.integer(rowNames)
parentRows <- which(table[,1] != "")
# Some JS for the table
coster_callback_js <- CosterTableJS(
parentRows,
colIdx
)
# Table
dtable <- datatable(
table,
callback = coster_callback_js,
rownames = rowNames,
escape = -colIdx-1,
style = "bootstrap4",
extensions = 'Buttons',
options = list(
dom = "Bt",
columnDefs = list(
list(width = '30px', targets = 0),
list(width = '300px', targets = 1),
list(width = '80px', targets = 3),
list(visible = FALSE, targets = c(2,3,4,5)),
list(visible = FALSE, targets = ncol(table)-1+colIdx),
list(orderable = FALSE, className = 'details-control', targets = colIdx),
list(className = "dt-center", targets = "_all")
)
)
)
# Call the html tools deps (js & css files in this directory)
path <- "/Users/timmcwilliams/Desktop" # folder containing the file dataTables.cellEdit.js
dep <- htmltools::htmlDependency(
"CellEdit", "1.0.19",
path, script = "dataTables.cellEdit.js")
dtable$dependencies <- c(dtable$dependencies, list(dep))
dtable
CosterTableJS <- function(parentRows, colIdx) {
return(
JS(
"function onUpdate(updatedCell, updatedRow, oldValue) {",
"}",
"function renderInt(data, type, row) {",
" if(type === 'display') {",
" return parseFloat(data).toFixed(0);",
" } else {",
" return data;",
" }",
"}",
"function renderDollars(data, type, row) {",
" if(type === 'display') {",
" return '$' + parseFloat(data).toFixed(2);",
" } else {",
" return data;",
" }",
"}",
sprintf("var parentRows = [%s];", toString(parentRows-1)),
sprintf("var j0 = %d;", colIdx),
"var nrows = table.rows().count();",
"for(var i=0; i < nrows; ++i){",
" if(parentRows.indexOf(i) > -1){",
" table.cell(i,j0).nodes().to$().css({cursor: 'pointer'});",
" }else{",
" table.cell(i,j0).nodes().to$().removeClass('details-control');",
" }",
"}",
"",
"// make the table header of the nested table",
"var format = function(d, childId){",
" if(d != null){",
" var html = ",
" '<table class="display compact hover" ' + ",
" 'style="padding-left: 30px;" id="' + childId + '"><thead><tr>';",
" for(var key in d[d.length-1][0]){",
" html += '<th>' + key + '</th>';",
" }",
" html += '</tr></thead><tfoot><tr>'",
" for(var key in d[d.length-1][0]){",
" html += '<th></th>';",
" }",
" return html + '</tr></tfoot></table>';",
" } else {",
" return '';",
" }",
"};",
"",
"// row callback to style the rows of the child tables",
"var rowCallback = function(row, dat, displayNum, index){",
" if($(row).hasClass('odd')){",
" $(row).css('background-color', 'white');",
" $(row).hover(function(){",
" $(this).css('background-color', 'lightgreen');",
" }, function() {",
" $(this).css('background-color', 'white');",
" });",
" } else {",
" $(row).css('background-color', 'white');",
" $(row).hover(function(){",
" $(this).css('background-color', 'lightblue');",
" }, function() {",
" $(this).css('background-color', 'white');",
" });",
" }",
"};",
"",
"// header callback to style the header of the child tables",
"var headerCallback = function(thead, data, start, end, display){",
" $('th', thead).css({",
" 'color': 'black',",
" 'background-color': 'white'",
" });",
"};",
"",
"// make the datatable",
"var format_datatable = function(d, childId, rowIdx){",
" // footer callback to display the totals",
" var footerCallback = function(tfoot, data, start, end, display){",
" $('th', tfoot).css('background-color', '#F5F2F2');",
" var api = this.api();",
" var cpp = api.column(5).data();",
" var mix = api.column(2).data();",
" var points = api.column(6).data();",
" var population = api.column(9).data();",
" // HERE",
" // Grab the sum of the points to use later on",
" var pointsSumTotal = 0;",
" for(var i = 0; i < points.length; i++) {",
" pointsSumTotal += (parseFloat(points[i]));",
" }",
" console.log('points', pointsSumTotal)",
" // Update the Point column when the Mix column is edited",
" for (var z = 0; z < mix.length; z++) {",
" api.cell(z,6).data((mix[z]/100)*pointsSumTotal);",
" }",
"",
" // Update the Cost and Impressions with the new Points values",
" var points = api.column(6).data();",
" for (var i = 0; i < points.length; i++) {",
" // The cost",
" api.cell(i,7).data(parseFloat(points[i]).toFixed(0)*parseFloat(cpp[i]));",
" // The Imps",
" api.cell(i,8).data((((parseFloat(points[i]).toFixed(0)/100)*parseFloat(population[i]))));",
" }",
" // Update the full flight cost, points, and imps",
" var weeks = api.column(4).data();",
" var cost = api.column(7).data();",
" var imps = api.column(8).data();",
" for (var i = 0; i < weeks.length; i++) {",
" // The full flight cost",
" api.cell(i,10).data(parseFloat(weeks[i])*parseFloat(cost[i]));",
" // The full flight points",
" api.cell(i,11).data(parseFloat(weeks[i])*parseFloat(points[i]).toFixed(0));",
" // The full flight imps",
" api.cell(i,12).data(parseFloat(weeks[i])*parseFloat(imps[i]).toFixed(0));",
" }",
"// Make the footer sums",
" api.columns().eq(0).each(function(index){",
" if(index == 1) return $(api.column(index).footer()).html('Totals');",
" var coldata = api.column(index).data();",
" var total = coldata",
" .reduce(function(a, b){return parseInt(a) + parseInt(b)}, 0);",
"
