Filtering not updating tables and graphs

smruthi · November 18, 2022, 7:22pm

Hi,
I am trying to make real time visualization using Shiny. The data is stored in MySQL, i am connecting it to Shiny using pool package. Its almost complete, I have tables and plots working fine but the filters doesn't not work, it does not update the graphs.

Please find the code below, any help is appreciated.

Load RShiny libraries

library(shiny)
library(shinythemes)
library(DBI)
library(pool)
library(RMySQL)
library(dplyr)
library(tidyverse)
library(DT)
library(plotly)
library(ggplot2)
library(dbplyr)
library(tibble)

Connecting to the database

pool <- pool::dbPool(RMySQL::MySQL(),

)

onStop(function(){
poolClose(pool)
})

Define the input

ui <- fluidPage(theme = shinytheme("slate"),
ui <- navbarPage(
title = strong('BFU+/CED - Recruiting'),
position = 'static-top',
collapsible = FALSE,
fluid = TRUE,
#theme = shinytheme('slate'),

              # titlePanel("BFU+/CED - Recruiting"),
              # br(),
              
              
              sidebarPanel(width = 2,
                           br(),#to introduce extra vertical spacing
                           
                           
                           
                           
                           
                           selectInput(inputId = "filter1",
                                       label = "Diagnosis",
                                       choices = "Names"),
                           
                           
                           br(),
                           
                           
                           selectInput("filter2",
                                       label="Drugs used",
                                       choices = list("",
                                                      "Infliximab",
                                                      "Adalimumab",
                                                      "Certolizumab",
                                                      "Golimumab",
                                                      "Vedolizumab",
                                                      "Ustekinumab",
                                                      "Tofacitinib",
                                                      "Filgotinib",
                                                      "Ozanimod",
                                                      "Other"),
                                       
                                       selected = NULL),
                           
                           
                           br(),
                           
                           
                           
                           selectInput("filter3",
                                       label="Response",
                                       choices = list("",
                                                      "Super-response",
                                                      "Response",
                                                      "Partial response",
                                                      "Non-response",
                                                      "Allergic reaction",
                                                      "Low disease activity"),
                                       selected = NULL),
                           
                           
                           br(), #to introduce extra vertical spacing
                           
                           
                           
                           
                           selectInput("filter4",
                                       label="Events",
                                       choices = list("",
                                                      "Week 0",
                                                      "Week 2",
                                                      "Week 6",
                                                      "Week 14",
                                                      "Week 26",
                                                      "Week 52",
                                                      "Week 78",
                                                      "Week 104"),
                                       selected = NULL)
                           
                           
              ),
              

              mainPanel( width = 10,
                         
                         tabsetPanel(
                           
                           tabPanel("Consents and Demographics", tableOutput("consents"), br(),
                                    splitLayout(cellWidths = c(250,250,250,250,250),
                                                plotOutput("plot",width="250px",height="250px"),
                                                plotOutput("plot2",width="250px",height="250px"),
                                                plotOutput("plot3",width="250px",height="250px"),
                                                plotOutput("plot4",width="250px",height="250px"),
                                                plotOutput("plot5",width="250px",height="250px")),
                                    br(),
                                    splitLayout(cellWidths = c(250,250,250,250,250),         
                                                plotOutput("plot6",width="250px",height="250px"),
                                                plotOutput("plot7",width="250px",height="250px"),
                                                plotOutput("plot8",width="250px",height="250px"),
                                                plotOutput("plot9",width="250px",height="250px"),
                                                plotOutput("plot10",width="250px",height="250px")
                                                
                                    )    
                           ),
                           tabPanel("Anamnesis", tableOutput("anamnesis"), br(),
                                    splitLayout(cellWidths = c(250,250,250,250,250),
                                                plotOutput("plota1",width="250px",height="250px"),
                                                plotOutput("plota2",width="250px",height="250px"),
                                                plotOutput("plota3",width="250px",height="250px"),
                                                plotOutput("plota4",width="250px",height="250px"),
                                                plotOutput("plota5",width="250px",height="250px")
                                    )),
                           tabPanel("Clinical Visit", tableOutput("clinical"), br(),
                                    splitLayout(cellWidths = c(250,250,250,250,250),
                                                plotOutput("plotcv1",width="300px",height="300px"),
                                                plotOutput("plotcv2",width="250px",height="250px"),
                                                plotOutput("plotcv3",width="250px",height="250px"),
                                                plotOutput("plotcv4",width="250px",height="250px"),
                                                plotOutput("plotcv5",width="250px",height="250px")),
                                    br(),
                                    splitLayout(cellWidths = c(250,250,250,250,250),         
                                                plotOutput("plotcv6",width="250px",height="250px"),
                                                plotOutput("plotcv7",width="250px",height="250px"),
                                                plotOutput("plotcv8",width="250px",height="250px"),
                                                plotOutput("plotcv9",width="250px",height="250px"),
                                                plotOutput("plotcv10",width="250px",height="250px")),
                                    
                                    
                                    br(),
                                    splitLayout(cellWidths = c(250,250,250,250,250),         
                                                plotOutput("plotcv11",width="250px",height="250px"),
                                                plotOutput("plotcv12",width="250px",height="250px"),
                                                
                                                
                                                
                                    )    
                           ),
                           
                           tabPanel("Routine lab values", tableOutput("routine"), br(),
                                    splitLayout(cellWidths = c(250,250,250,250,250),
                                                
                                                plotOutput("plotr2",width="250px",height="250px"),
                                                plotOutput("plotr3",width="250px",height="250px"),
                                                plotOutput("plotr4",width="250px",height="250px"),
                                                plotOutput("plotr5",width="250px",height="250px")),
                                    br(),
                                    splitLayout(cellWidths = c(250,250,250,250,250),         
                                                plotOutput("plotr6",width="250px",height="250px"),
                                                plotOutput("plotr7",width="250px",height="250px"),
                                                plotOutput("plotr8",width="250px",height="250px"),
                                                plotOutput("plotr9",width="250px",height="250px"),
                                                
                                    )    
                           ),
                           
                           tabPanel("Endoscopy", tableOutput("endo"), br(),
                                    splitLayout(cellWidths = c(250,250,250,250,250),
                                                plotOutput("plote1",width="250px",height="250px"),
                                                plotOutput("plote2",width="250px",height="250px"),
                                                plotOutput("plote3",width="250px",height="250px"),
                                                plotOutput("plote4",width="250px",height="250px"),
                                                plotOutput("plote5",width="250px",height="250px")),
                                    br(),
                                    splitLayout(cellWidths = c(250,250,250,250,250),         
                                                plotOutput("plote6",width="250px",height="250px"),
                                                plotOutput("plote7",width="250px",height="250px"),
                                                plotOutput("plote8",width="250px",height="250px"),
                                                plotOutput("plote9",width="250px",height="250px"),
                                                plotOutput("plote10",width="250px",height="250px"),
                                                
                                    )    
                           ),
                           
                         )
                         
                         
                         
              )
              
            )

)

Define the output

server <- function(input, output, session){

#Creating dataframes

condata <- reactive({tbl(pool, "consents_and_demographics") %>%
select(consent,consent_guide_ibd,diagnosis,diagnosis_year,used_drug,other_study_drug,sex,age,age_first_diagnosis,ethnicity)
})

#updated_consent <- tbl(pool, "consents_and_demographics") %>% select(consent,consent_guide_ibd,diagnosis,diagnosis_year,used_drug,other_study_drug,sex,age,age_first_diagnosis,ethnicity)

anamnesis_db <- tbl(pool,"anamnesis") %>% select(diet,smoking,alcohol,prednisolone_status,aza_status)

clinical_db <- tbl(pool,"clinical_visit") %>% select(bmi,cd_pro2,cd_pro3,cdai,uc_pro2,partial_mayo,complete_mayo,current_prednisolon_dose,current_dose,cumulative_dose,response,low_activity,remission)

endoscopy_db <- tbl(pool,"endoscopy") %>% select(sescd,cd_location,cd_disease_behaviour,cd_perianal,extent_uc,endo_mayo,nancy_index,nancy_index_ulceration,nancy_index_acute,nancy_index_chronic)

routine_db <- tbl(pool,"routine_lab_values") %>% select(leucocytes,hemoglobin,hematokrit,thrombocytes,eosinophils,crp,il6,tryptophan,calprotectin)

observe({
updateSelectInput(session, "filter1", choices = condata()$diagnosis)

})

output$plot <- renderPlot({ggplot(pool %>% tbl("consents_and_demographics"), aes(x=consent)) + # Create object called output$plot with a ggplot inside it
geom_bar(width=0.3)

# Outline the bins in black

})

output$plot2 <- renderPlot({ggplot(condata(), aes(x=consent_guide_ibd)) + # Create object called output$plot with a ggplot inside it
geom_bar(width=0.3) # Outline the bins in black
})

output$plot3 <- renderPlot({ggplot(pool %>% tbl("consents_and_demographics"), aes(x=diagnosis)) + # Create object called output$plot with a ggplot inside it
geom_bar(width=0.3) # Outline the bins in black
})

output$plot4 <- renderPlot({ggplot(pool %>% tbl("consents_and_demographics"), aes(x=diagnosis, y=diagnosis_year)) + # Create object called output$plot with a ggplot inside it
geom_point(width=0.3) # Outline the bins in black
})

output$plot5 <- renderPlot({ggplot(pool %>% tbl("consents_and_demographics"), aes(x=used_drug)) + # Create object called output$plot with a ggplot inside it
geom_bar(width=0.3) # Outline the bins in black
})

output$plot6 <- renderPlot({ggplot(pool %>% tbl("consents_and_demographics"), aes(x=other_study_drug)) + # Create object called output$plot with a ggplot inside it
geom_bar(width=0.3) # Outline the bins in black
})

output$plot7 <- renderPlot({ggplot(pool %>% tbl("consents_and_demographics"), aes(x=sex)) + # Create object called output$plot with a ggplot inside it
geom_bar(width=0.3) # Outline the bins in black
})

output$plot8 <- renderPlot({ggplot(pool %>% tbl("consents_and_demographics"), aes(x=age)) + # Create object called output$plot with a ggplot inside it
geom_bar(width=0.3) # Outline the bins in black
})

output$plot9 <- renderPlot({ggplot(pool %>% tbl("consents_and_demographics"), aes(x=age_first_diagnosis)) + # Create object called output$plot with a ggplot inside it
geom_bar(width=0.3) # Outline the bins in black
})

output$plot10 <- renderPlot({ggplot(pool %>% tbl("consents_and_demographics"), aes(x=ethnicity)) + # Create object called output$plot with a ggplot inside it
geom_bar(width=0.3) # Outline the bins in black
})

output$consents <- renderTable({
condata() %>% head(10)
})

output$anamnesis <- renderTable({
anamnesis_db %>% head(10)
})

output$plota1 <- renderPlot({ggplot(pool %>% tbl("anamnesis"), aes(x=diet)) +
geom_bar(width=0.3) # Outline the bins in black
})

output$plota2 <- renderPlot({ggplot(pool %>% tbl("anamnesis"), aes(x=smoking)) + # Create object called output$plot with a ggplot inside it
geom_bar(width=0.3) # Outline the bins in black
})

output$plota3 <- renderPlot({ggplot(pool %>% tbl("anamnesis"), aes(x=alcohol)) +
geom_bar(width=0.3) # Outline the bins in black
})

output$plota4 <- renderPlot({ggplot(pool %>% tbl("anamnesis"), aes(x=prednisolone_status)) + # Create object called output$plot with a ggplot inside it
geom_bar(width=0.3) # Outline the bins in black
})

output$plota5 <- renderPlot({ggplot(pool %>% tbl("anamnesis"), aes(x=aza_status)) + # Create object called output$plot with a ggplot inside it
geom_bar(width=0.3) # Outline the bins in black
})

output$clinical <- renderTable({
clinical_db %>% head(10)
})

output$plotcv1 <- renderPlot({ggplot(pool %>% tbl("clinical_visit"), aes(y=bmi)) +
geom_bar(width=0.3)

})

output$plotcv2 <- renderPlot({ggplot(pool %>% tbl("clinical_visit"), aes(x=cd_pro2)) + # Create object called output$plot with a ggplot inside it
geom_bar() # Outline the bins in black
})

output$plotcv3 <- renderPlot({ggplot(pool %>% tbl("clinical_visit"), aes(x=cd_pro3)) +
geom_bar(width=0.3) # Outline the bins in black
})

output$plotcv4 <- renderPlot({ggplot(pool %>% tbl("clinical_visit"), aes(x=cdai)) + # Create object called output$plot with a ggplot inside it
geom_bar(width=0.3) # Outline the bins in black
})

output$plotcv5 <- renderPlot({ggplot(pool %>% tbl("clinical_visit"), aes(x=uc_pro2)) + # Create object called output$plot with a ggplot inside it
geom_bar(width=0.3) # Outline the bins in black
})

output$plotcv6 <- renderPlot({ggplot(pool %>% tbl("clinical_visit"), aes(x=partial_mayo)) + # Create object called output$plot with a ggplot inside it
geom_bar() # Outline the bins in black
})

output$plotcv7 <- renderPlot({ggplot(pool %>% tbl("clinical_visit"), aes(x=complete_mayo)) +
geom_bar(width=0.3) # Outline the bins in black
})

output$plotcv8 <- renderPlot({ggplot(pool %>% tbl("clinical_visit"), aes(x=current_prednisolon_dose)) + # Create object called output$plot with a ggplot inside it
geom_bar(width=0.3) # Outline the bins in black
})

output$plotcv9 <- renderPlot({ggplot(pool %>% tbl("clinical_visit"), aes(x=current_dose)) + # Create object called output$plot with a ggplot inside it
geom_bar(width=0.3) # Outline the bins in black
})

output$plotcv10 <- renderPlot({ggplot(pool %>% tbl("clinical_visit"), aes(x=cumulative_dose)) + # Create object called output$plot with a ggplot inside it
geom_bar() # Outline the bins in black
})

output$plotcv11 <- renderPlot({ggplot(pool %>% tbl("clinical_visit"), aes(x=response)) +
geom_bar(width=0.3) # Outline the bins in black
})

output$plotcv12 <- renderPlot({ggplot(pool %>% tbl("clinical_visit"), aes(x=low_activity)) + # Create object called output$plot with a ggplot inside it
geom_bar(width=0.3) # Outline the bins in black
})

output$plotcv13 <- renderPlot({ggplot(pool %>% tbl("clinical_visit"), aes(x=remission)) + # Create object called output$plot with a ggplot inside it
geom_bar(width=0.3) # Outline the bins in black
})

output$routine <- renderTable({
routine_db %>% head(10)
})

output$plotr2 <- renderPlot({ggplot(pool %>% tbl("routine_lab_values"), aes(x=leucocytes)) +
geom_bar(width=0.3) # Outline the bins in black
})

output$plotr3 <- renderPlot({ggplot(pool %>% tbl("routine_lab_values"), aes(x=hemoglobin)) + # Create object called output$plot with a ggplot inside it
geom_bar(width=0.3) # Outline the bins in black
})

output$plotr4 <- renderPlot({ggplot(pool %>% tbl("routine_lab_values"), aes(x=hematokrit)) +
geom_bar(width=0.3) # Outline the bins in black
})

output$plotr5 <- renderPlot({ggplot(pool %>% tbl("routine_lab_values"), aes(x=thrombocytes)) + # Create object called output$plot with a ggplot inside it
geom_bar(width=0.3) # Outline the bins in black
})

output$plotr6 <- renderPlot({ggplot(pool %>% tbl("routine_lab_values"), aes(x=eosinophils)) + # Create object called output$plot with a ggplot inside it
geom_bar(width=0.3) # Outline the bins in black
})

output$plotr7 <- renderPlot({ggplot(pool %>% tbl("routine_lab_values"), aes(x=crp)) + # Create object called output$plot with a ggplot inside it
geom_bar(width=0.3) # Outline the bins in black
})

output$plotr8 <- renderPlot({ggplot(pool %>% tbl("routine_lab_values"), aes(x=il6)) +
geom_bar(width=0.3) # Outline the bins in black
})

output$plotr9 <- renderPlot({ggplot(pool %>% tbl("routine_lab_values"), aes(x=tryptophan)) + # Create object called output$plot with a ggplot inside it
geom_bar(width=0.3) # Outline the bins in black
})

output$plotr10 <- renderPlot({ggplot(pool %>% tbl("routine_lab_values"), aes(x=calprotectin)) + # Create object called output$plot with a ggplot inside it
geom_bar(width=0.3) # Outline the bins in black
})

output$endo <- renderTable({
endoscopy_db %>% head(10)
})

output$plote1 <- renderPlot({ggplot(pool %>% tbl("endoscopy"), aes(x=sescd)) +
geom_bar(width=0.3) # Outline the bins in black
})

output$plote2 <- renderPlot({ggplot(pool %>% tbl("endoscopy"), aes(x=cd_location)) + # Create object called output$plot with a ggplot inside it
geom_bar(width=0.3) # Outline the bins in black
})

output$plote3 <- renderPlot({ggplot(pool %>% tbl("endoscopy"), aes(x=cd_disease_behaviour)) +
geom_bar(width=0.3) # Outline the bins in black
})

output$plote4 <- renderPlot({ggplot(pool %>% tbl("endoscopy"), aes(x=cd_perianal)) + # Create object called output$plot with a ggplot inside it
geom_bar(width=0.3) # Outline the bins in black
})

output$plote5 <- renderPlot({ggplot(pool %>% tbl("endoscopy"), aes(x=extent_uc)) + # Create object called output$plot with a ggplot inside it
geom_bar(width=0.3) # Outline the bins in black
})

output$plote6 <- renderPlot({ggplot(pool %>% tbl("endoscopy"), aes(x=endo_mayo)) + # Create object called output$plot with a ggplot inside it
geom_bar(width=0.3) # Outline the bins in black
})

output$plote7 <- renderPlot({ggplot(pool %>% tbl("endoscopy"), aes(x=nancy_index)) +
geom_bar(width=0.3) # Outline the bins in black
})

output$plote8 <- renderPlot({ggplot(pool %>% tbl("endoscopy"), aes(x=nancy_index_ulceration)) + # Create object called output$plot with a ggplot inside it
geom_bar(width=0.3) # Outline the bins in black
})

output$plote9 <- renderPlot({ggplot(pool %>% tbl("endoscopy"), aes(x=nancy_index_acute)) + # Create object called output$plot with a ggplot inside it
geom_bar(width=0.3) # Outline the bins in black
})

output$plote10 <- renderPlot({ggplot(pool %>% tbl("endoscopy"), aes(x=nancy_index_chronic)) + # Create object called output$plot with a ggplot inside it
geom_bar(width=0.3) # Outline the bins in black
})

}

shinyApp( ui, server)

scottyd22 · November 18, 2022, 9:49pm

Welcome to the community @smruthi! Thanks for sharing your code. I couldn't get the full app to work, but looking through the code, I noticed there were no references to your inputs, so I believe this is the reason the filters are not working.

For example, condata is a reactive that returns a database connection, but there is no link to your inputs to cause the data returned to change. In the example below, I provide a look at one way to do this. With this configuration, each time "filter1" is changed, condata will update to include only those rows where diagnosis equals the filter selection. This will propagate to all other sections of the app that references condata(). If this resolves your issue, then you can incorporate the same approach to the other sections of your app and make reference to the other filters.

condata <- reactive({
  
  req(input$filter1)
  
  tbl(pool, "consents_and_demographics") %>%
    filter(diagnosis == input$filter1) %>%  # addded filter
    select(consent,consent_guide_ibd,
           diagnosis,diagnosis_year,
           used_drug,other_study_drug,
           sex,age,age_first_diagnosis,ethnicity)
})

smruthi · December 20, 2022, 1:39pm

Thank you very much for the reply @scottyd22 . I appreciate it.
Unfortunately, It didn't fix the issue. getting this error:
Warning: Error in : Cannot translate shiny inputs to SQL.
Do you want to force evaluation in R with (e.g.) !!input$x or local(input$x)?

scottyd22 · December 20, 2022, 7:29pm

Does it work if you collect() the data and then filter?

condata <- reactive({
  
  req(input$filter1)
  
  tbl(pool, "consents_and_demographics") %>%
    select(consent,consent_guide_ibd,
           diagnosis,diagnosis_year,
           used_drug,other_study_drug,
           sex,age,age_first_diagnosis,ethnicity) %>%
    collect() %>%
    filter(diagnosis == input$filter1)  # addded filter
})

system · February 12, 2023, 11:30pm

This topic was automatically closed 54 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.