How to update an editable data table when a column is edited and use it to perform successive calculations in Shiny

Question

I'm trying to make a calculator in shiny where the user can edit one column and the data table will update the calculations in the other columns that uses the user input within the DT, the user input in the side panel, and/or the other calculated values in the other columns.

Here is what I have so far. I want the user to be able to edit the "ppl" column and then use it to update the other columns. I just populated "ppl" with 72 to have something there. I've been stuck on this for a while since I'm new to R Shiny. I've seen people use observeEvent, but I'm still confused on how to implement it. Any help or guidance is appreciated.

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

ui <- 
  sidebarLayout(
    sidebarPanel(
      numericInput("price", label = "Price ($)", value = 4.31, min = 0),
      numericInput("sqft", label = "Average Square Footage", value = 4400, min = 0),
      numericInput("delivery", label = "Delivery Cost", value = 2.60, min = 0),
      numericInput("avg_use", label = "Average Annual Use", value = 88000, min = 0),
      numericInput("turf", label = "Percentage (%)", value = 0, min = 0)
    ),
    mainPanel(
      DTOutput("table1")
    )
  )


server <- function(input, output, session) {
  
  df <- reactive({
    
    data.frame(
      behavior = c("A",
                   "B",
                   "C",
                   "D",
                   "E",
                   "F",
                   "G",
                   "H",
                   "I",
                   "J",
                   "K"),
      average = c(2541, 11913, 12707, 16995, 23668, 2859, 2224, 10483, 22555, 8259, 5718),
      ppl = c(72, 72, 72, 72, 72, 72, 72, 72, 72, 72, 72)
      
    ) |>
      # If there is an input for 'Percentage', modify E's total_land value by using the input
      mutate(total_land = ifelse(str_detect(behavior, "E") & input$turf > 0,
                                 ppl * input$sqft * input$turf,
                                 ppl * input$sqft),
             all_w = average * (total_land / 1000),
             all_ut = input$price * (all_w / 1000),
             annual_w = all_w / ppl,
             mon_w = annual_w / 12,
             annual_ut = all_ut / ppl,
             mon_ut = annual_ut / 12,
             deliv = (all_w / 1000) * input$delivery,
             supply = all_w / input$avg_use
      )
  })
  
    output$table1 <- renderDT(
      df(),
      selection = 'none',
      editable = list(target = 'column', disable = list(columns = c(0:1, 3:12))),
      server = T,
      rownames = F
    )
}

shinyApp(ui, server)

Answer 1

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

ui <- 
  sidebarLayout(
    sidebarPanel(
      numericInput("price", label = "Price ($)", value = 4.31, min = 0),
      numericInput("sqft", label = "Average Square Footage", value = 4400, min = 0),
      numericInput("delivery", label = "Delivery Cost", value = 2.60, min = 0),
      numericInput("avg_use", label = "Average Annual Use", value = 88000, min = 0),
      numericInput("turf", label = "Percentage (%)", value = 0, min = 0)
    ),
    mainPanel(
      DTOutput("table1")
    )
  )

dat00 <- data.frame(
  behavior = c("A",
               "B",
               "C",
               "D",
               "E",
               "F",
               "G",
               "H",
               "I",
               "J",
               "K"),
  average = c(2541, 11913, 12707, 16995, 23668, 2859, 2224, 10483, 22555, 8259, 5718),
  ppl = c(72, 72, 72, 72, 72, 72, 72, 72, 72, 72, 72)
)

makeData <- function(dat0, turf, sqft, price, delivery, avg_use) {
  dat0 |>
    # If there is an input for 'Percentage', modify E's total_land value by using the input
    mutate(total_land = ifelse(str_detect(behavior, "E") & turf > 0,
                               ppl * sqft * turf,
                               ppl * sqft),
           all_w = average * (total_land / 1000),
           all_ut = price * (all_w / 1000),
           annual_w = all_w / ppl,
           mon_w = annual_w / 12,
           annual_ut = all_ut / ppl,
           mon_ut = annual_ut / 12,
           deliv = (all_w / 1000) * delivery,
           supply = all_w / avg_use
    )
}

server <- function(input, output, session) {
  
  Dat0 <- reactiveVal(dat00)

  output$table1 <- renderDT({
    dat <- makeData(
      isolate(Dat0()), 
      input$turf, input$sqft, input$price, input$delivery, input$avg_use
    )
    datatable(
      dat,
      rownames = FALSE,
      selection = "none",
      editable = list(target = "cell", disable = list(columns = c(0:1, 3:12)))
    )
  }, server = TRUE)
  
  proxy <- dataTableProxy("table1")
  
  observeEvent(input[["table1_cell_edit"]], {
    info <- input[["table1_cell_edit"]] 
    # update Dat0
    dat0 <- editData(Dat0(), info, rownames = FALSE) 
    Dat0(dat0) 
    # update the data in the table
    dat <- makeData(
      dat0, 
      input$turf, input$sqft, input$price, input$delivery, input$avg_use
    )
    replaceData(proxy, dat, resetPaging = FALSE, rownames = FALSE)
  })
  
}

shinyApp(ui, server)