How to edit an uploaded data in rshiny

Question

Here are the requirements.

1)I need to browse and upload an excel file (with package readxl), which is used to arrive at some calculations which I need to display in shiny window as a different table output

2) Manually edit some data in the uploaded file and it should automatically reflect in the results displayed

3) We should be able to download the edited file.

I have written so far. I have columns ID, exposure and frequency in input data. For each ID I need to calculate a variable using corresponding exposure and frequency which would be displayed. I need to manually edit frequency and Exposure using ID ( which is unique ). I have added an "update" button. But change is not permanent. It goes back as soon as I click update button once more

library(shiny)
ui = fluidPage( 

     titlePanel("HEllo world"),
     sidebarLayout(
          sidebarPanel(

               fileInput('file1', 'Choose xlsx file',
                         accept = c(".xlsx")),
               actionButton("go", "update"),
               numericInput('NewVal', 'Enter new Frequency',NULL),
               numericInput('NewExp', 'Enter new Exposure',NULL)),


          mainPanel(
               textInput('ID', 'Enter ID'),
               dataTableOutput('contents')

          )))

server = function(input,output){
     ef <- eventReactive(input$go, {
          infile <- input$file1 
          if(is.null(infile))
               return(NULL)
          file.rename(infile$datapath,paste(infile$datapath, ".xlsx", sep=""))
          data<-read_excel(paste(infile$datapath, ".xlsx", sep=""), 1)

          if(input$ID!="" && input$go>0){
               for( i in 1:nrow(data)){

                    if( input$ID == data$'ID'[i]){

                         if(!is.na(input$NewVal)){
                              data$' Frequency'[i] <- input$NewVal
                         }

                         if(!is.na(input$NewExp)){
                              data$'Exposure'[i] <- input$NewExp
                         }
                    }}}

          data
     }, ignoreNULL =  FALSE)  


     output$contents <- renderDataTable({ef()})}
shinyApp(ui,server)

UPDATE!:As per one answer, I have made some changes to my code. The new code seems to be working fine. Here is the working code, for anyone who might need help with the same issue.

ui = fluidPage( 

 titlePanel("HEllo world"),
  sidebarLayout(
  sidebarPanel(

  fileInput('file1', 'Choose xlsx file',
            accept = c(".xlsx")),
  actionButton("go", "update"),
  numericInput('NewVal', 'Enter new Frequency',NULL),
  numericInput('NewExp', 'Enter new Exposure',NULL)),


mainPanel(
  textInput('ID', 'Enter ID'),
  tableOutput('contents')

)))

  server = function(input,output){
  # Reactive value to save input data frame for use elsewhere
   original <- reactiveValues()

  observeEvent(input$file1, {
  theFile <- input$file1
   if(is.null(theFile)) {
    return(NULL)}
    **file.rename(theFile$datapath,paste(theFile$datapath, ".xlsx", sep=""))**
     original$oldData <- read_excel(paste(theFile$datapath, ".xlsx", sep = ""), 1)    
    })

   observeEvent(input$go, {

   original$newData <- original$oldData
   if(input$ID !="") {
    for( i in 1:nrow(original$oldData)){

     if( input$ID == original$oldData$'ID'[i]){

      if(!is.na(input$NewVal)){
        original$newData$'Frequency'[i] <- input$NewVal
      }

      if(!is.na(input$NewExp)){
        original$newData$'Exposure'[i] <- input$NewExp
      }
     }
    }
    **original$oldData<-original$newData**  }
  })

output$contents <- renderTable({
  if(!is.null(original$newData)) {
  original$newData}
else {
  original$oldData}
  })
  }
  shinyApp(ui = ui, server = server)

where does the object `dat` come from in your code? What's the difference with "data"? — agenis, Nov 30 '17 at 09:02
I guess the problem is because in one function you are reading the file and updating it. Make 2 functions, in 1st you read the file and store it. Pass that function into your second function, where you will do updates and then show the output. — Rahul Agarwal, Nov 30 '17 at 09:07
@agenis Sorry its data not dat. Its the same. data contains "ID", "Exposure" and "frequency" — Dom Jo, Nov 30 '17 at 09:32
@RahulAgarwal Can you show me. Im getting error when I do it. You can do with your own data. I just want the logic — Dom Jo, Nov 30 '17 at 09:35
I get this error with a data.frame with your 3 columns "Error in if: missing value where TRUE/FALSE needed" — agenis, Nov 30 '17 at 09:39
@agenis I just want the logic. Its okay if you do a similar thing with a data of your choice. Don't use my code if it is confusing — Dom Jo, Nov 30 '17 at 10:41
well several remarks: first you don't need an action button but a built-in `submitButton`, which has exactly this purpose. Then I guess you could get rid of your for loop if it's just there to find the row to be changed... and you should work with a structure using `reactiveValues` . There are some answered questions about this like this one: https://stackoverflow.com/a/30502250/3871924 — agenis, Nov 30 '17 at 10:48
@agenis thanks for the suggestions. Can you show me a similar code with a sample data? Just like 2 columns with 4 or 5 entries? It would be helpful if i could visualise it — Dom Jo, Dec 01 '17 at 06:32
@agenis I have added a new code which is working. I have added it as an update to the question. Could you give an insight into how to make changes to this data reflect in a calculated column? — Dom Jo, Dec 01 '17 at 10:30
Apologies if I'm barking at the wrong tree, but have you considered to use `rhandsontable`? It offers `excel-like` GUI and (if set) allows cell-level editing. It may not make the code any simpler (you need to know how to handle the changed cell, of course), but at least gives a better experience to the user. — Enzo, Dec 03 '17 at 18:46

creutzml · Accepted Answer · 2017-12-07T13:52:21.597

Some of the comments seem to be on the right track of what's going on here. There's several solutions that could be used, but I'll just share what's most intuitive to me. Also, I'll only be changing the server function.

server = function(input,output){
 # Reactive value to save input data frame for use elsewhere
 original <- reactiveValues()

 observeEvent(input$file1, {
   theFile <- input$file1
   if(is.null(theFile)) {return(NULL)}
   original$oldData <- read_excel(paste(theFile$datapath, ".xlsx", sep = ""), 1)    
 })

 observeEvent(input$goButton2, {
   original$newData <- original$oldData
   if(input$ID !="") {
          for( i in 1:nrow(data)){
                if( input$ID == dat$'ID'[i]){

                     if(!is.na(input$NewVal)){
                          original$newData$' Frequency'[i] <- input$NewVal
                     }

                     if(!is.na(input$NewExp)){
                          original$newData$'Exposure'[i] <- input$NewExp
                     }
                }
          }
   }
 })

 output$contents <- renderDataTable({
   if(!is.null(original$newData)) {original$newData}
   else {original$oldData}
 })
}

This won't change the table output until the go button is clicked. I haven't tested it fully, since I don't have your data, but I believe this should set you on the right track at the bare minimum... I like observe statements, because they cause side effects and seem more open ended than eventReactives or functions.

This only helps with the initial issues of having the correct changes made and continued showing in the output. If this works, it should be fairly easy to add a download function, which saves the file whenever it's updated.

Update 1

The code below should do what you would want it to do. I've added two different capabilities for saving the new data frame. The commented out code saves the data automatically whenever the update button is pressed. The code that's there without comments around it creates a download button for downloading the data. I've also added a line that calculates a new value based off frequency and exposure. Named this column Value in data set. Hope this helps!

#### Example app for Exchange answer
library(shiny)
library(readxl)

ui = fluidPage( 

  titlePanel("HEllo world"),
  sidebarLayout(
    sidebarPanel(

      fileInput('file1', 'Choose xlsx file',
                accept = c(".xlsx")),
      actionButton("go", "update"),
      numericInput('NewVal', 'Enter new Frequency',NULL),
      numericInput('NewExp', 'Enter new Exposure',NULL),

      # Download button (goes with download handler below)
      # Use if desire is to save at press of button
      downloadButton("save", "Download")
    ),

    mainPanel(
      textInput('ID', 'Enter ID'),
      dataTableOutput('contents')
    )
  )
)

server = function(input,output){
  # Reactive value to save input data frame for use elsewhere
  original <- reactiveValues()

  observeEvent(input$file1, {
    theFile <- input$file1
    if(is.null(theFile)) {
      original$oldData <- NULL
    } else {
      original$oldData <- read_excel(theFile$datapath, 1)      
    }
  })

  observeEvent(input$go, {

    original$newData <- original$oldData
    if(input$ID !="") {
      for(i in 1:nrow(original$oldData)){

        if(input$ID == original$oldData$'ID'[i]){

          if(!is.na(input$NewVal)){
            original$newData$'Frequency'[i] <- input$NewVal
          }

          if(!is.na(input$NewExp)){
            original$newData$'Exposure'[i] <- input$NewExp
          }
          ### Make sure a column in your data set is named Value for this
          # Calculate a new column
          original$newData$'Value'[i] <- (original$newData$'Exposure'[i]*
                                            original$newData$'Frequency'[i])
        }
      }

      original$oldData<-original$newData  
    }

    ### Use this to automatically save table when update is clicked
    # write.csv(original$newData, 
    #           file = #Desired Pathname, 
    #           row.names = FALSE)
  })

  output$contents <- renderDataTable({
     if(!is.null(original$newData)) {
      original$newData}
    else {
      original$oldData
    }
  })

  ### Use this code below if desired saving is through download button
  # Server code for download button
  output$save <- downloadHandler(
    filename = function() {
      paste0("newData - ", Sys.Date(), ".csv")
    },

    content = function(con) {
      if (!is.null(original$newData)) {
        dataSave <- original$newData
      } else {
        dataSave <- original$oldData
      }
      con <- ## Desired save location... could just use `getwd()` to
        # save to working directory
      write.csv(dataSave, con)
    }
  )

}


shinyApp(ui = ui, server = server)

Thank you so much. you can use a random data frame if you want. I just want the logic. — Dom Jo, Dec 01 '17 at 06:36
It would be helpful if you can try it with your own data file. — Dom Jo, Dec 01 '17 at 06:37
Im getting error bro. The shiny window is popping up but once I load a data, it crashes. I have changed the question. View the 2nd part of the question — Dom Jo, Dec 01 '17 at 07:01
Okay I got it. I made a couple of changes and now its working. Please find the new code below the question. Thanks a lot. — Dom Jo, Dec 01 '17 at 10:24
Also, it would be helpful if you could give an insight into 1) how to make the changes reflect in the calculated column. assume that we multiply the columns "exposure" and "frequency". Assume that the calculated column is displayed in a separate tab (tab2. 2) How to download the changed data (in tab1) — Dom Jo, Dec 01 '17 at 10:24
Hey, sorry for the delayed response! Just saw these comments. If you want to make a new column, it should be as easy as adding a `exposure*frequency` step within the for loop, and then c-bind that result with the data frame.. Also, I was trying to avoid overwriting the original data, so that weird reactive dependencies didn't occur. For downloading the data, there's a couple options: 1. Add a download button and download handler 2. Just add a statement in the observeEvent for goButton at the end that automatically writes a csv file to the working directory, whenever the data changes — creutzml, Dec 01 '17 at 23:08
'original <- reactiveValues()' . DO we always need this piece of code if we are using observeEvent inside server function. How does this work? — Dom Jo, Dec 02 '17 at 05:09
I almost tend to thing of reactiveValues as global variables that can be changed and interact with other things. By specifying the original dataset as a reactive value outside of other functions, it will allow us to use it within multiple observe events and reactives... now we just need to be careful about the dependency structure being created by doing so. (See Shiny's tutorial on Dependency program if you are unfamiliar with how it actually works. Their guide helps tremendously when trying to solve problems like these.)... Another way to solve this problem, would be to read the original data — creutzml, Dec 03 '17 at 18:06
into a variable outside of the server function. Then, the data file could be manipulated in different code within the server function like normal... That may be an easier way to solve your problem, actually, if you don't care about overwriting the original data. — creutzml, Dec 03 '17 at 18:07
Can you show me how it's done? to read the original data into a variable outside of the server function. I'm very amateur at coding with rshiny. — Dom Jo, Dec 04 '17 at 06:11
Im finding it difficult to download the edited data. Mind showing me how its done — Dom Jo, Dec 05 '17 at 09:08
Sorry Dom, just saw these messages. I'll try writing an example comparable to your code that runs on it's own, and will update my answer above. — creutzml, Dec 05 '17 at 15:26
Answer has been edited... As your table becomes more complicated, you may want to look into RHandsontable (as one user above commented). — creutzml, Dec 05 '17 at 16:04

How to edit an uploaded data in rshiny

1 Answers1