CODEWITHSUNDEEP
rshiny
Vaibhav Thapliyal
Vaibhav Thapliyal

Reputation: 15

How to get data out of reactive function in Shiny to create new columns on the same DF

In my script below, Shiny is fetching data from PostgresSQL as a reactive function and I want to manipulate the output of that reactive function (just like we do in a data frame), so that I can then pass it to the renderPlot function to get the output I desire.

I will really appreciate any help in letting me know how to get this working. I have not included error message because I believe error is happening at the handling of the reactive function.

Thanks for the help! Highly appreciated. 

#server side operation

library(shiny)
library(dplyr)
library(ggplot2)
library(DBI)
library(RPostgreSQL)

drv <- dbDriver("PostgreSQL")
con <-dbConnect(drv,dbname = "", host = "valid credentials", port = 5439,
           user = "USER", password = "password")

dates <- seq(as.Date(as.character(Sys.Date() - 33)), as.Date(as.character(Sys.Date() - 1)), by = 1)


shinyServer(function(input, output, session)  {

generate <- function(dates){

listofdfs <- list() #Create a list in which you intend to save your df's. 

for (i in 1:length(dates)){



data <- reactive({dbGetQuery(con, sprintf("select Column A, CAST (date AS date), id from My_Table 
                                       where id =", input$user_id," and
                                       date <= '%s' and date >= '%s'- INTERVAL '7 days'", dates[i], dates[i]))


data$Column_A_mean <- mean(data[,1]) #creating a new column like we do in a data frame (DF)
  })

 listofdfs[[i]] <- data() # save your dataframes into the list
 }

return(listofdfs) #Return the list of dataframes.
}


df <- do.call("rbind", generate(dates))

output$Coolplot <- renderPlot({
ggplot(data = df, aes(date)) +
  geom_line(aes(y = Column A, colour = "Column A"))+
  geom_line(aes(y = Column_A_mean, colour = "Column_A_mean"))
     })

  })

Upvotes: 0

Views: 839

Answers (1)

HubertL
HubertL

Reputation: 19544

You have to put the reactive out of the function, for example you can call the function from the reactive:

shinyServer(function(input, output, session)  {

  generate <- function(dates){
    listofdfs <- list() #Create a list in which you intend to save your df's. 
    for (i in 1:length(dates)){
      data <- dbGetQuery(con, sprintf("select Column A, CAST (date AS date), id from My_Table 
                                                where id =", input$user_id," and
                                                date <= '%s' and date >= '%s'- INTERVAL '7 days'", dates[i], dates[i]))
      data$Column_A_mean <- mean(data[,1]) #creating a new column like we do in a data frame (DF)
      listofdfs[[i]] <- data() # save your dataframes into the list
    }
    return(listofdfs) #Return the list of dataframes.
  }

  df <- reactive({do.call("rbind", generate(dates))})

  output$Coolplot <- renderPlot({
    ggplot(data = df(), aes(date)) +
      geom_line(aes(y = Column A, colour = "Column A"))+
      geom_line(aes(y = Column_A_mean, colour = "Column_A_mean"))
  })

})

Upvotes: 1

Related Questions