Expanding and Collapsing Child Rows in Shiny DataTable
I am having problems replicating a data object in Shiny. Currently, I can display what I want when I run the data table portion of the code outside of the Shiny environment. However, when I run all the code, it doesn't display the child table.
library(DT)
library(data.table)
library(Shiny)
shinyApp(
ui = fluidPage(DT::dataTableOutput('tbl')),
server = function(input, output) {
output$tbl = DT::renderDataTable(
datatable({
#Transform dataframe to data.table and turn the dataframe rowname into a data.table column called model
mtcars_dt = data.table(mtcars)
mtcars_dt[["model"]] = rownames(mtcars)
setcolorder(mtcars_dt,c(
which(colnames(mtcars_dt) %in% c("mpg","cyl","model")),
which(!colnames(mtcars_dt) %in% c("mpg","cyl","model"))
))
#Turn data table into a nested data.table by mpg, cyl
mtcars_dt <- mtcars_dt[, list(cars=list(.SD)), by = list(mpg,cyl)]
#configure datatable. Hide row number and cars columns [0,4] and enable details control on plus sign column[1]
#turn rows into child rows and remove from parent
cbind(' ' = '⊕', mtcars_dt)},
escape = -2,
options = list(
columnDefs = list(
list(visible = FALSE, targets = c(0,4)),
list(orderable = FALSE, className = 'details-control', targets = 1)
)
),
callback = JS("
table.column(1).nodes().to$().css({cursor: 'pointer'});
// Format cars object into another table
var format = function(d) {
if(d != null){
var result = ('<table id=\"child_' + d[2] + '_' + d[3] + '\">').replace('.','_') + '<thead><tr>'
for (var col in d[4]){
result += '<th>' + col + '</th>'
}
result += '</tr></thead></table>'
return result
}else{
return '';
}
}
var format_datatable = function(d) {
var dataset = [];
for (i = 0; i < + d[4]['model'].length; i++) {
var datarow = [];
for (var col in d[4]){
datarow.push(d[4][col][i])
}
dataset.push(datarow)
}
var subtable = $(('table#child_' + d[2] + '_' + d[3]).replace('.','_')).DataTable({
'data': dataset,
'autoWidth': true,
'deferRender': true,
'info': false,
'lengthChange': false,
'ordering': true,
'paging': false,
'scrollX': false,
'scrollY': false,
'searching': false
});
};
table.on('click', 'td.details-control', function() {
var td = $(this), row = table.row(td.closest('tr'));
if (row.child.isShown()) {
row.child.hide();
td.html('⊕');
} else {
row.child(format(row.data())).show();
td.html('⊖');
format_datatable(row.data())
}
});")
)
)
}
)
Thank you for your help!
source to share
The key here was the difference between an object and an array. When using shiny, it row.data()
is an array, and its fifth element is also an array (here I clicked on the second row in the main table):
["2", "⊕", 22.8, 4, Array(2)]
Outside of shiny environment row.data()
looks like this:
["2", "⊕", 22.8, 4, Object]
As you can see, the fifth element is an object! Why this is so, I cannot say. I think there might be a difference in the versions of the libraries used behind the scenes.
To get this working, we need to make 2 changes:
1. Change format()
:
var format = function(d) {
if(d != null) {
var result = ('<table id=\"child_' + d[2] + '_' + d[3] + '\">').replace('.','_') + '<thead><tr>'
for (var col in d[4][0]) {
result += '<th>' + col + '</th>'
}
result += '</tr></thead></table>'
return result
} else {
return '';
}
}
Here we just added [0]
to line 4. As shown above, d[4]
this is an array. In the case of the second row of data, it consists of 2 objects. (var col in d[4])
returns 0
and 1
(object indices), whereas (var col in d[4][0])
returns the elements of the first object (so column names).
2. Change format_datatable()
:
var format_datatable = function(d) {
var dataset = [];
for (i = 0; i <= d[4].length-1; i++) {
var datarow = $.map(d[4][i], function(value, index) {
return [value];
});
dataset.push(datarow);
}
// ...
// the rest did not change
}
Here we convert every car model (so every item in d[4]
) that is represented as an object into an array using $.map()
. After that, we'll just add this array to dataset
.
The code that creates the subtable expects these arrays. Data is handled differently with DataTables by type, which can be checked here .
source to share