Working with reactiveValues and ggplot in shiny

Question

Im using R and shiny to query an SQL database. The user can search and add to a reactive data frame, the output of which is plotted in ggplot. However, I need to change the columns of the reactive data frames to factors for plotting. I can do this directly with ggplot (aes(factor(...), ). However, if I add the option of changing the plotted variable using a reactive input, I must use aes_string. If I use aes_string it does not like aes(factor(...),. Here is a working example:

Server:

# Create example data

set.seed(10)
MeasurementA <- rnorm(1000, 5, 2)
MeasurementB <- rnorm(1000, 5, 2)

Wafer <- rep(c(1:100), each=10)
ID <- rep(c(101:200), each=10)
Batch <- rep(c(1:10), each=100)

dd <- data.frame(Batch, Wafer, ID, MeasurementA, MeasurementB)

# Create local connection (in reality this will be a connection to a host site)

con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbWriteTable(con, "dd", dd)
query <-  function(...) dbGetQuery(con, ...)

# Create empty data frames to populate

wq = data.frame()
sq = data.frame()

shinyServer(function(input, output){

  # create data frame to store reactive data set from query
  values <- reactiveValues()
  values$df <- data.frame()

  # Action button for first query
  d <- eventReactive(input$do, { input$wafer })

  # First stage of reactive query
  a <- reactive({ paste("Select ID from dd where Wafer=",d(), sep="") })

  wq <- reactive({  query( a() ) })

  # Output to confirm query is correct
  output$que <- renderPrint({ a() }) 
  output$pos <- renderPrint( wq()[1,1] )  

  # Action button to add results from query to a data frame
  e <- eventReactive(input$do2, { wq()[1,1] })

  b <- reactive({ paste("select Wafer, Batch, MeasurementA, MeasurementB from dd where ID=",e()," Order by  ID asc ;", sep="") })

  # observe e() so that data is not added until user presses action button  
  observe({
    if (!is.null(e())) {
      sq <- reactive({  query( b() ) })

      # add query to reactive data frame
      values$df <- rbind(isolate(values$df), sq())
    }
  })


# output of results  

  # Without mesurement choice (works)
  output$boxV <- renderPlot({
  ggplot(values$df, aes(factor(Wafer), MeasurementA, fill=factor(Batch))) + geom_boxplot() 
  })
  # With measurement choice (doesnt work)
  #output$boxV <- renderPlot({
    #ggplot(values$df, aes_string(factor('Wafer'), input$char, fill=factor('Batch'))) + geom_boxplot() 
  #})

  })

UI:

library(markdown)

shinyUI(fluidPage(
  titlePanel("Generic grapher"),
  sidebarLayout(
    sidebarPanel(

      numericInput("wafer", label = h3("Input wafer ID:"), value = NULL),

      actionButton("do", "Search wafer"),
      actionButton("do2", "Add to data frame"),
      selectInput("char", label="Boxplot choice:",
                  choices = list("A"="MeasurementA", "B"="MeasurementB"),                            
                  selected="Von.fwd")

      ),

      mainPanel(
        verbatimTextOutput("que"), 
        verbatimTextOutput("pos"),
        plotOutput("boxV")
      )
    )
  )
)

Ive added output plot code for both working and non-working (non-working is commented out).

Now, ive read this (Formatting reactive data.frames in Shiny) and this (R shiny modify reactive data frame) but im confused. Because im using reactiveValues to store data, I use the code values$df to access the data...but what if i I want to turn a column to a factor for purpose of above? this doesnt seem to work:

new <- reactive(as.factor(values$df$Wafer))

Perhaps I am barking up the wrong tree with this?

Answer 1

Ok, I solved the problem by changing the data type within the query itself:

b <- reactive({ paste("select cast(Wafer as varchar) as Wafer, cast(Batch as varchar) as Batch, MeasurementA, MeasurementB from dd where ID=",e()," Order by  ID asc ;", sep="") })

That way I didnt have to mess about afterwards. It works for me but if anyone reading this wants to tell me that its a bad idea, please do. Im new to SQL and R so please correct me so I can learn. Thanks