SHINY Summarise info based on sheet selected by user after uploading file

Question

My goal is that user uploads an Excel file. Then, the user selects which sheets wants to be summarised, after the selection has been updated. I have managed to update selectInput with the name of the sheets but I have not been able to find\understand how to summarise based on what the sheet selected by the user. Thanks for any help.

library(shiny)
library(shinythemes)
library(data.table)
library(ggplot2)
library(dplyr)
library(readxl)

not_sel <- "Not Selected"

# Define UI for application that draws a histogram
ui <- fluidPage('MAIN TITLE',
                theme = shinytheme('flatly'),
  tabsetPanel(
    sidebarLayout(
      sidebarPanel(
        fileInput('files','Import File', accept = c('.csv','.xlsx'),
                  multiple = F),
        actionButton('boton1', 'Load', icon = icon('table')),
        br(),
        selectInput("indices", "Select SHEET:", choices = c(not_sel))
      ),
  mainPanel(
    tabsetPanel(
      tabPanel('Data',
        tableOutput('tabla'),
        tableOutput('cabeza')),
      
      tabPanel('Stats',
        # selectInput('var01', 'Variable to summarise', choices = c(not_sel)),
        tableOutput('stats')),      
        )
      )
    )
  )
)

##############

server <- function(input, output, session) {
  options(shiny.maxRequestSize=10*1024^2) 
  
  df <- eventReactive(input$boton1, {
    req(input$files)
    if(is.null(input$files))return(NULL)
    # else{
    read_excel(input$files$datapath)
    # }
  })
 
  # Sheets of file uploaded
  sheets_name <- reactive({
    if (!is.null(input$files)) {
      return(excel_sheets(path = input$files$datapath))  
    } else {
      return(NULL)
    }
  })

  # Update inputSelector with sheet names
  observeEvent(df(),{
    choices <- c(sheets_name())
    updateSelectInput(inputId = "indices", choices = choices)
  })
  
  
  # DATA Tab
  ## This will show the name of the file

  output$tabla <- renderTable({
    input$files$name
  })
  
  ## This Shows the head() but it is only showing the first sheet

  output$cabeza <- renderTable({
    tabla <- as_tibble(bind_cols(Date = format(as.Date(df()$Date),"%Y-%m-%d"),
                                 Close.Price = df()$Close))
    head(tabla)
  })
  
  # HERE is where I do not know how to calculate based on selection
  # Table for STATS
  output$stats <- renderTable({
    datos <- df()
    Value <- c(round(mean(datos$Close,na.rm = T),2)
               )
    Statistic <- c("Mean")
    data.table(Statistic, Value)
    })
}

# Run the application 
shinyApp(ui = ui, server = server)

I want to assume that by knowing how to calculate mean based on the sheet selected, I. can replicate the code for the top rows (head()) shown in the Data Panel. If I missed a similar question asked, I would appreciate any link and I will try the solution proposed first.

As I cannot share the file, this is how the file would look:

jamm_ing · Answer

After working with this answer I made my app work. If there is a 'cleaner'/'better' answer, I will be happy to read.

Following the recommendation in the linked answer my server ended up like this:

ui <-fluidPage{
#My UI stayed the same with the exception of adding 
        uiOutput("dropdownUI") #Whererever I needed to appear 
}

server <- function(input, output, session) {
...ANSWER FROM THE LINK...
  ## STATS Tab
  output$stats <- renderTable({
    Values <- c(round(mean(Dat()[,2],na.rm = T),2)
               )
    Statistics <- c("Mean")
    data.table(Statistics, Values)
    })
}

SHINY Summarise info based on sheet selected by user after uploading file

1 Answers