I based the code below on Stephane Laurent's solution to the following question on Stack Overflow:
Edit datatable in Shiny with dropdown selection for factor variables
I added in code to use editData to update the table and to be able to save/export the updates.
The following works with DT v0.18 but with DT v0.19 I found the id_cell_edit seems to not be triggering. I am unsure if it has to do with the callback or possibly jquery.contextMenu given DT v0.19 upgraded to jquery 3.0. Would appreciate any insight people may have on how to work through this.
Here is a description of the behavior I observe when using v0.18. When I select the usage column and update the value for the first row from the default “sel” to “id” the value changes in the DT table. I also see it updates the view of the tibble and thus the data in the download csv file is also updated. If I progress to the Next page to see the 11th item, and then return back to the first page, I can see the record I updated still says “id”.
Here is a description of the behavior I observe when using v0.19. When I select the usage column and update the value for the first row from the default “sel” to “id” the value changes in the DT table. It does not update the view of the tibble and thus the data in the download csv file does not get updated. If I progress to the Next page to see the 11th item, and then return back to the first page, the update I had made gets cleared.
Note that I also ran reactive graphs using reactlog. I followed the same steps to update the usage column of the first row to "id". The first difference I note is that reactiveValues###$dt at Step 5 gives me a list of 7 when I use version v0.18 and a list of 8 when I use version v0.19. At Step 16, for v0.18 input$dt_cell_edit invalidates, then Data invalidates and output$table invalidates. At Step 16 when using v0.19, however, output$dt invalidates then output$table invalidates. In other words, when using v0.19 input$dt_cell_edit and Data are not invalidating.
library(shiny)
library(DT)
library(dplyr)
cars_df <- mtcars
cars_meta <- dplyr::tibble(variables = names(cars_df), data_class = sapply(cars_df, class), usage = "sel")
cars_meta$data_class <- factor(cars_meta$data_class, c("numeric", "character", "factor", "logical"))
cars_meta$usage <- factor(cars_meta$usage, c("id", "meta", "demo", "sel", "text"))
callback <- c(
"var id = $(table.table().node()).closest('.datatables').attr('id');",
"$.contextMenu({",
" selector: '#' + id + ' td.factor input[type=text]',",
" trigger: 'hover',",
" build: function($trigger, e){",
" var levels = $trigger.parent().data('levels');",
" if(levels === undefined){",
" var colindex = table.cell($trigger.parent()[0]).index().column;",
" levels = table.column(colindex).data().unique();",
" }",
" var options = levels.reduce(function(result, item, index, array){",
" result[index] = item;",
" return result;",
" }, {});",
" return {",
" autoHide: true,",
" items: {",
" dropdown: {",
" name: 'Edit',",
" type: 'select',",
" options: options,",
" selected: 0",
" }",
" },",
" events: {",
" show: function(opts){",
" opts.$trigger.off('blur');",
" },",
" hide: function(opts){",
" var $this = this;",
" var data = $.contextMenu.getInputValues(opts, $this.data());",
" var $input = opts.$trigger;",
" $input.val(options[data.dropdown]);",
" $input.trigger('change');",
" }",
" }",
" };",
" }",
"});"
)
createdCell <- function(levels){
if(missing(levels)){
return("function(td, cellData, rowData, rowIndex, colIndex){}")
}
quotedLevels <- toString(sprintf("\"%s\"", levels))
c(
"function(td, cellData, rowData, rowIndex, colIndex){",
sprintf(" $(td).attr('data-levels', '[%s]');", quotedLevels),
"}"
)
}
ui <- fluidPage(
tags$head(
tags$link(
rel = "stylesheet",
href = "https://cdnjs.cloudflare.com/ajax/libs/jquery-contextmenu/2.8.0/jquery.contextMenu.min.css"
),
tags$script(
src = "https://cdnjs.cloudflare.com/ajax/libs/jquery-contextmenu/2.8.0/jquery.contextMenu.min.js"
)
),
DTOutput("dt"),
br(),
verbatimTextOutput("table"),
br(),
downloadButton('download',"Download the data")
)
server <- function(input, output){
dat <- cars_meta
value <- reactiveValues()
value$dt<-
datatable(
dat, editable = "cell", callback = JS(callback),
options = list(
columnDefs = list(
list(
targets = 2,
className = "factor",
createdCell = JS(createdCell(c(levels(cars_meta$data_class), "another level")))
),
list(
targets = 3,
className = "factor",
createdCell = JS(createdCell(c(levels(cars_meta$usage), "another level")))
)
)
)
)
output[["dt"]] <- renderDT({
value$dt
},
server = TRUE)
Data <- reactive({
info <- input[["dt_cell_edit"]]
if(!is.null(info)){
info <- unique(info)
info$value[info$value==""] <- NA
dat <- editData(dat, info, proxy = "dt")
}
dat
})
#output table to be able to confirm the table updates
output[["table"]] <- renderPrint({Data()})
output$download <- downloadHandler(
filename = function(){"Data.csv"},
content = function(fname){
write.csv(Data(), fname)
}
)
}
shinyApp(ui, server)
Below I've leveraged ismirsehregal's solution into my use case. I also added in the renderPrint/verbatimTextOutput to illustrate what I am trying to do with the underlying data. I'd like to be able to capture the values not the input containers. Essentially with the code I am trying to give the User a dataset, allow them to change some values but restrict the choices with dropdowns, and then use the updated dataset for further processing. At this point in the solution I don't know how to get to the updated dataset so that I can use it, for example, to export to a csv file.
library(DT)
library(shiny)
library(dplyr)
cars_df <- mtcars
selectInputIDa <- paste0("sela", 1:length(cars_df))
selectInputIDb <- paste0("selb", 1:length(cars_df))
initMeta <- dplyr::tibble(
variables = names(cars_df),
data_class = sapply(selectInputIDa, function(x){as.character(selectInput(inputId = x, label = "", choices = c("character","numeric", "factor", "logical"), selected = sapply(cars_df, class)))}),
usage = sapply(selectInputIDb, function(x){as.character(selectInput(inputId = x, label = "", choices = c("id", "meta", "demo", "sel", "text"), selected = "sel"))})
)
ui <- fluidPage(
DT::dataTableOutput(outputId = 'my_table'),
br(),
verbatimTextOutput("table")
)
server <- function(input, output, session) {
displayTbl <- reactive({
dplyr::tibble(
variables = names(cars_df),
data_class = sapply(selectInputIDa, function(x){as.character(selectInput(inputId = x, label = "", choices = c("numeric", "character", "factor", "logical"), selected = input[[x]]))}),
usage = sapply(selectInputIDb, function(x){as.character(selectInput(inputId = x, label = "", choices = c("id", "meta", "demo", "sel", "text"), selected = input[[x]]))})
)
})
output$my_table = DT::renderDataTable({
DT::datatable(
initMeta, escape = FALSE, selection = 'none', rownames = FALSE,
options = list(paging = FALSE, ordering = FALSE, scrollx = TRUE, dom = "t",
preDrawCallback = JS('function() { Shiny.unbindAll(this.api().table().node()); }'),
drawCallback = JS('function() { Shiny.bindAll(this.api().table().node()); } ')
)
)
}, server = TRUE)
my_table_proxy <- dataTableProxy(outputId = "my_table", session = session)
observeEvent({sapply(selectInputIDa, function(x){input[[x]]})}, {
replaceData(proxy = my_table_proxy, data = displayTbl(), rownames = FALSE) # must repeat rownames = FALSE see ?replaceData and ?dataTableAjax
}, ignoreInit = TRUE)
observeEvent({sapply(selectInputIDb, function(x){input[[x]]})}, {
replaceData(proxy = my_table_proxy, data = displayTbl(), rownames = FALSE) # must repeat rownames = FALSE see ?replaceData and ?dataTableAjax
}, ignoreInit = TRUE)
output$table <- renderPrint({displayTbl()})
}
shinyApp(ui = ui, server = server)
