I am trying to create a dashboard using an excel file which contains multiple sheets. Each sheet can be selected by the user and based on the sheet selection, the data tables of selected sheets are displayed.

One of the excel sheets contains data in this manner,

library(shiny)
library(shinythemes)
library(openxlsx)
library(DT)

# Define UI for application that draws a histogram
ui <-tagList(
  #shinythemes::themeSelector(),
  navbarPage(id = "home",
             title = actionLink("title","Home"),
             navbarMenu("Statistics",
                        tabPanel("Incidence",
                                 sidebarLayout(
                                   sidebarPanel(
                                     selectInput(
                                       "incidence", "Select Cancer Incidence Rates By:",
                                       choices = c("Age" = "I_Age",
                                                   "Cancer Type" = "I_Site",
                                                   "Trends" = "I_Trend",
                                                   "Trend By Age" = "I_Tr_Age",
                                                   "Admission Type" = "I_Admin",
                                                   "Stage" = "I_Stage",
                                                   "Stage By Age" = "I_St_Age",
                                                   )
                                     ),
                                      #each choices are different sheets in a workbook
                                     selectInput("cancer", "Cancer Type",
                                                 choices = 
                                                 c("All cancers", 
                                                 "All cancers ex NMSC", 
                                                 "Bladder cancer",
                                                 "Brain cancer",
                                                 "Breast cancer",
                                                 "Brain non-invasive tumours",
                                                 "Brain tumours",
                                                 ),
                                                 ),
                                     conditionalPanel(
                                       condition = "input.incidence != 'I_Tr_Age' && 
                                                    input.incidence != 'I_St_Age'",
                                       selectInput("gender","Gender",
                                                   choices = unique(table$Gender))
                                    )),
                        mainPanel(
                          tabsetPanel( id = "mp",
                            tabPanel("Plot", plotlyOutput("plot"), value = 1),
                            tabPanel("Data", DT::dataTableOutput("data"),value = 2)
                          )
                        )
                      )
                    ),
                        tabPanel("Survival"),
                        tabPanel("Mortality"),
                        tabPanel("Prevalence")
)
)
)

server <- function(input, output){
  data <- reactive({
    x <- read.xlsx("incidence.xlsx", sheet = input$incidence)
    x <- x %>% 
      filter(
        req(!is.null(Gender %in% input$gender)),
        req(!is.null(Cancertype %in% input$cancer))
      )
    x
     })
output$data <- renderDataTable({
    DT :: datatable(data())
  })
}
# Run the application 
shinyApp(ui = ui, server = server)

The above code is not working when I try to filter the data based on user selection such as gender and cancer type. Any help would be appreciated.

Thank you.

CodePudding user response：

First issue with your code is that the names of the Cancertype categories in your example data are different from the labels you use in the selectInput, i.e. Cancertype %in% input$cancer is always FALSE. Second, using req(!is.null(Gender %in% input$gender)) isn't that useful to filter your dataset. Not sure where you learned this kind of code. In general req is used to check that e.g. a required input has a value, i.e. to prevent that code breaks because the user hasn't made a selection yet. To this end you could add e.g. req(input$gender) to the beginning of the reactive then filter your data using Gender %in% input$gender.

x <- data.frame(
  Age = c(10L, 20L, 30L),
  Gender = c("M", "M", "F"),
  Cancertype = c("Brain", "Colon", "Breast"),
  Rate = c(20L, 12L, 13L)
)

library(shiny)
library(shinythemes)
library(dplyr)
library(plotly)
library(DT)

# Define UI for application that draws a histogram
ui <- tagList(
  # shinythemes::themeSelector(),
  navbarPage(
    id = "home",
    title = actionLink("title", "Home"),
    navbarMenu(
      "Statistics",
      tabPanel(
        "Incidence",
        sidebarLayout(
          sidebarPanel(
            selectInput(
              "incidence", "Select Cancer Incidence Rates By:",
              choices = c(
                "Age" = "I_Age",
                "Cancer Type" = "I_Site",
                "Trends" = "I_Trend",
                "Trend By Age" = "I_Tr_Age",
                "Admission Type" = "I_Admin",
                "Stage" = "I_Stage",
                "Stage By Age" = "I_St_Age"
              )
            ),
            selectInput("cancer", "Cancer Type",
              choices = unique(x$Cancertype)
            ),
            conditionalPanel(
              condition = "input.incidence != 'I_Tr_Age' &&
                                                    input.incidence != 'I_St_Age'",
              selectInput("gender", "Gender",
                choices = unique(x$Gender)
              )
            )
          ),
          mainPanel(
            tabsetPanel(
              id = "mp",
              #tabPanel("Plot", plotlyOutput("plot"), value = 1),
              tabPanel("Data", DT::dataTableOutput("data"), value = 2)
            )
          )
        )
      ),
      tabPanel("Survival"),
      tabPanel("Mortality"),
      tabPanel("Prevalence")
    )
  )
)

server <- function(input, output) {
  data <- reactive({
    req(input$gender)
    req(input$cancer)

    x %>%
      filter(
        Gender %in% input$gender,
        Cancertype %in% input$cancer
      )
  })

  output$data <- renderDataTable({
    DT::datatable(data())
  })
}
# Run the application
shinyApp(ui = ui, server = server)
#> 
#> Listening on http://127.0.0.1:7045