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

Question

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

I tried using an absolute path instead of a relative one. The thing is, when I restart the app, the query runs again that doesn't contain the columns ('R/Y/G', 'R', 'Y', 'G' and 'tcolor') . I created these columns using R and so their values disappear even after saving them to an rds, since the rds changes after the query reruns. data1 <- dbGetQuery(q_r,sql) saveRDS(data1, 'data.rds') so the new columns that are created lose their values. About the color thing, the conditions are if d9$R > 2500000 it is red. If it's between 2 and 2.5 million, it will be yellow, less than 2 mil then green — retrx22, Sep 04 '21 at 08:02
The colors only change when I edit the same cell twice, and by the time I restart the app, the colors go back to green — retrx22, Sep 04 '21 at 08:04
Do you need to display all the columns (those from the query and those created in R) in the DT table? — julien.leroux5, Sep 04 '21 at 08:29
yes since the 'R/Y/G' column tracks the color and R, Y, G are editable columns whose values determine the color for 'R/Y/G' column — retrx22, Sep 04 '21 at 08:35
All those columns you mention are created after the query. But do you also need to display some columns from the data you get from the initial query? if yes the possible answer is a bit more complex — julien.leroux5, Sep 04 '21 at 08:41
yes, that's what I want to do. But the initial values get updated due to the query, and the columns that i create on the fly(doesn't exist in DB table) get created afresh, so they lose their values each time I run the app — retrx22, Sep 04 '21 at 08:50

julien.leroux5 · Accepted Answer · 2021-09-04T10:31:28.563

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).

There are issues with the way you create the empty additional columns, preventing the colors to display right, that should be addressed in a different post. — julien.leroux5, Sep 04 '21 at 10:13
I have added a separate post for the color issue here : https://stackoverflow.com/questions/69055953/color-values-dont-change-when-editing-other-dt-column-values-its-based-on-r-sh — retrx22, Sep 04 '21 at 13:55

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

1 Answers1

Linked