Editable calculation with DT table in Shiny

Question

I've been at this for awhile and have read a bunch but I still can't wrap my head around how to make this work. Is there a simple solution?

I want to edit a DT table in my shiny app and, upon editing, I'd like there to be a change in a column that aggregates two values.

Here is an example:

library(tidyverse)
library(shiny)
library(DT)

mt <- mtcars %>%
    select(mpg, cyl) %>%
    head()

ui <- fluidPage(
  
  DTOutput(outputId = "final_tbl")
)

server <- function(input, output){

  dat <- reactive({
    d <- mt %>%
      mutate(total = mpg + cyl)
    d
  })
    
  output$final_tbl <- renderDT({
    
    dat() %>%
      datatable(editable = TRUE)
    
  })
}

shinyApp(ui, server)

This produces a simple editable table with a total column that adds up mpg and cyl. What I'd like to be able to do is edit the cyl value and have the change reflected in the summed total column. Is there an easy solution to this?

Answer 1

You need to use _cell_edit as shown below in a ObserveEvent.

mt <- mtcars %>%
  select(mpg, cyl) %>%
  head()

ui <- fluidPage(
  
  DTOutput(outputId = "final_tbl")
)

server <- function(input, output){
  df1 <- reactiveValues(data=NULL)
  dat <- reactive({
    d <- mt %>%
      mutate(total = mpg + cyl)
    d
  })
  
  observe({
    df1$data <- dat()
  })
  
  output$final_tbl <- renderDT({
    
    df1$data %>%
      datatable(editable = TRUE)
    
  })
  
  observeEvent(input$final_tbl_cell_edit, {
    info = input$final_tbl_cell_edit
    str(info)
    i = info$row
    j = info$col
    v = info$value
    
    # Without this line the table does not change but with it it jumps to row 1 after an edit.
    df1$data[i, j] <<- (DT::coerceValue(v, df1$data[i, j]))
    df1$data[,"total"] <<- df1$data[,"mpg"] + df1$data[,"cyl"]  ## update the total column
  })

}

shinyApp(ui, server)