How to save an edited dataframe and load it back when restarting an R Shiny app or query?

I am using a query to build a report in R shiny and then storing that result in a data frame.

data1 <- dbGetQuery(q_r,sql)

Then I use this dataframe to create new columns on the fly (these columns do not exist in the table I'm querying from since I only have read access and cannot write back to the database)

data1['R/Y/G'] <- " "

data1['R'] <- " "

data1['Y'] <- " "

data1['G'] <- " "

data1['tcolor'] <- " "

Then I save it to a local rds file

saveRDS(data1, 'data.rds')

Then I create an editable Datatable (https://yihui.shinyapps.io/DT-edit/) and save it back to the same rds file after it has been edited.

The thing is, whenever the query reruns, the values of the columns created using R ('R/Y/G', 'R', 'Y', 'G' and 'tcolor') lose all their values. How do I make sure that even after the query reruns, the columns created using R('R/Y/G', 'R', 'Y', 'G' and 'tcolor') retain their values? Do I use multiple files?

Here's the rest of the code :

dt_output = function(title, id) {
  fluidRow(column(
    12, h1(paste0(title)),
    hr(), DTOutput(id)
  ))
}

render_dt = function(data, editable = 'cell', server = TRUE, ...) {
  renderDT(data,selection = 'none', server = server, editable = editable, ...)
}

ui = fluidPage(
  downloadButton("mcp_csv", "Download as CSV", class="but"),
  
  dt_output('Report', 'x9')
)

server = function(input, output, session) {
  d1 = readRDS('data.rds')
  d9 = d1
  
  rv <- reactiveValues()
  observe({
    rv$d9 <- d9
  })
  
  dt_d9=datatable(isolate(d9), editable = 'cell', rownames = FALSE, extensions = 'Buttons', options = list(dom = 'Bfrtip', buttons = I('colvis'))) %>% formatStyle(
    'R/Y/G', 'tcolor',
    backgroundColor = styleEqual(c(0,1,2), c('green', 'yellow', 'red')),fontWeight = 'bold'
  )
  
  output$x9 = render_dt(dt_d9)
  
  proxy = dataTableProxy('x9')
  observe({
    DT::replaceData(proxy, rv$d9, rownames = FALSE, resetPaging = FALSE)
  })
  
  observeEvent(input$x9_cell_edit, {
    rv$d9 <<- editData(rv$d9, input$x9_cell_edit, 'x9', rownames = FALSE)
    d9 <- rv$d9
    d9$tcolor <- ifelse(d9$R > 2500000, 2,
                        ifelse(d9$Y > 2000000 & d9$Y <= 2500000, 1,
                               ifelse(d9$G <= 2000000, 0)))
    rv$d9 <<- d9
    saveRDS(d9, 'data.rds')
    
  })

Thanks

Solution

This is because you first save the data1 table with empty columns, then overwrite it after editing the table. When restarting the app, you again save the data1 with empty columns, overwriting the previously saved table (with edited columns).

If you need to display columns that you get from the sql query and the new columns created after the query, you need to save two dataframes and combine them to display them in the rendered DT.

First, save the results of the sql query right after the query.

data1 <- dbGetQuery(q_r,sql)
saveRDS(data1, 'data_query.rds')

Then you must create your combined dataframe with the added empty columns in the server. We can also retrieve data if the dataframe has already been edited and saved, by checking if a saved file exists in the app folder: replace this:

d1 = readRDS('data.rds') 
d9 = d1

by this:

  if(!file.exists("data.rds")){
    d9 = data1
    d9['R/Y/G'] <- NA
    d9['R'] <- NA
    d9['Y'] <- NA
    d9['G'] <- NA
    d9['tcolor'] <- NA
  }
  else{
    cmp <- readRDS("data.rds")
    d9 = cbind(data1, cmp[,(ncol(cmp)-4):ncol(cmp)])
  }

̀d9 is now your combined dataframe that you display with renderDT() and can edit. Save this dataframe after edition as already described in your code (saveRDS(d9, 'data.rds')).

When starting the app again, if a data.rds file already exists in the folder, it contains all columns from data1 (from a previous query) and the additional columns (R, Y, G...). So we must cbind the new data1 created with the new query and the last 5th columns of the saved data.rds file (i.e. only the additional columns).