DT::datatable with editable cells, recalculate data frame based on user inputs

[dougfir]

The small app.R script below generates a Shiny app that renders a DT::datatable with editable cells in the 2nd column. Since the columns to the right of column 2 are calculated based in colum 2's values, I wanted to know if there's a way to have the dataframe behind the datatable update after the user has entered new values?

How best to achieve this?

# Setup ----
pacman::p_load(shiny, tidyverse, shinydashboard, lubridate, scales)

# UI ----
header <- dashboardHeader(title = 'Spend & Return Calculator Example')

sidebar <- dashboardSidebar(
    menuItem("dh", tabName = "dh", icon = icon("dashboard")),
    menuItem("cf", tabName = "cf", icon = icon("th"))
)

body <- dashboardBody(
    tabItems(
        tabItem(tabName = "dh",
                h2("dh Estimator"),
                
                DT::DTOutput('example_ui')
                
        ),
        
        tabItem(tabName = "cf",
                h2("cf Estimator")
        )
    )
)


ui <- dashboardPage(header, sidebar, body)


# Server ----
server <- function(input, output) {
    
    # eventually set to come from dropdowns or user input
    budgets <- list(
        '2020.4' = 1000000,
        '2021.1' = 2000000,
        '2021.2' = 2000000,
        '2021.3' = 1500000,
        '2021.4' = 1500000
    )
    
    # just to demo UI
    sample_df <- data.frame(
        cohort = seq('2020-10-01' %>% ymd, '2021-12-31' %>% ymd, by = '1 days')) %>% 
        mutate(Quarter = quarter(cohort, with_year = T)) %>% 
        add_count(Quarter) %>% 
        mutate(DailyBudget = budgets[Quarter %>% as.character] %>% unlist / n) %>% 
        mutate(Revenue = DailyBudget + rnorm(nrow(.), mean = 0, sd = sd(DailyBudget))) %>% 
        group_by(Quarter) %>% 
        summarise(Spend = sum(DailyBudget),
                  Revenue = sum(Revenue),
                  .groups = 'drop') %>% 
        mutate(Profit = dollar(Revenue - Spend),
               Payback = percent(Revenue / Spend),
               Spend = dollar(Spend),
               Revenue = dollar(Revenue)) %>% 
        mutate(Quarter = as.character(Quarter)) # do this last keep ordering of quarters
    
    # from https://yihui.shinyapps.io/DT-edit/
    render_dt = function(data, editable = 'cell', server = TRUE, ...) {
        DT::renderDT(data, selection = 'none', server = server, editable = editable, ...)
    }
    
    output$example_ui <-  render_dt(sample_df, list(target = 'column', disable = list(columns = c(1,3,4,5))))
}
shinyApp(ui, server)

When I run the app it looks like this:

A user can enter new numbers for the Spend column and then I use this to calculate fictitious revenue and profit with mutate(Revenue = DailyBudget + rnorm(nrow(.), mean = 0, sd = sd(DailyBudget))) .

Is there a way to repopulate the data based on the users input in this way?

[nirgrahamuk]

I found you some examples here
https://yihui.shinyapps.io/DT-edit/