Specifying data range from excel in Shiny

Question

I would like to know if its possible to create a shiny app which allows you to upload an excel file and which allows you to select a data range based on sheet name and cell range. I would like to build upon it in order to showcase some regression analysis but haven't been able to find a starting point.

micstr · Accepted Answer

John, it is always a good idea to take a look at the Shiny gallery and take a look at past answers on Stack Overflow for code examples when faced with issues like these.

Here is a example tutorial for data upload. This can be CSV and not just xls. https://shiny.rstudio.com/gallery/file-upload.html. But code layout may be useful for you to set up your inputs.

Keep it simple? You might be able to save the data range you want out as a csv file so your users do not have specify data range and sheet. I do this so users just simply need to look at what data sets they want in a select box and not go hunt for the data. See example below. (This may save you lots of error trapping code).

Do not forget to transform your data. Note this example where you might need to factor some of your variables.

As outlined above by Parth see https://www.r-bloggers.com/read-excel-files-from-r/ for more detail on packages Xl_Connect and xlsx. You can specify sheets.

WORKING WITH FILES

Some code snippets that may help you. I have the data blocks already available as csv files. Setting up an selectInput with a list of these files

# in ui.R
selectInput(("d1"), "Data:", choices = data.choices)

I fill data.choices in global.R with this code.

# filter on .csv
data.files <- list.files(path = "data", pattern = ".csv") 

# dataset choices (later perhaps break by date)
#  sort by date most recent so selectInput takes first one
data.choices <- sort(data.files, decreasing = TRUE)

I have a reactive around the selectInput that then loads the data. (I use data.tables package fread so you will need to install this package and use library(data.tables) if you use this code).

dataset1 <- reactive({

  validate(
    need(input$d1 != "", "Please select a data set")
  )

  if (!is.null(input$d1)) {

    k.filename <- input$d1 # e.g. 'screendata20160405.csv'
    isolate({
      ## part of code this reactive should NOT take dependency on

      # LOAD CSV
      s.dt <- fread(file.path("data", k.filename), 
                    na.strings = c("NA", "#N/A")) %>% 
        rename(ticker = Ticker)

      # You might choose to rather dot.the.column.names to save DT issues
      #setnames(DT, make.names(colnames(DT))) 

      # SET KEYS IF RELEVANT
      k.id.cols <- c("ticker") 
      if ("date" %in% names(s.dt)) {
        k.id.cols <- c(k.id.cols, "date")
      }
      setkeyv(s.dt, k.id.cols)

      # NAME CHANGES rename columns if necessary
      setnames(s.dt, "Short Name", "name")

   })
  } else {
    s.dt <- NULL #input$d1 is null
  }
  s.dt
})

Note the validates as my data is plotted and I want to avoid error messages. Please appreciate the key setting and renaming columns code above is not necessary but specific to my example, but shows you what you can do to get your data "ready" for user.

GET SHEET NAMES OUT

John this is very useful. Take a look at this long thread on google groups https://groups.google.com/forum/#!topic/shiny-discuss/Mj2KFfECBhU

Huidong Tian had this very useful code at 3/17/14 (but also see Stephane Laurent's code about closing XLConnect too to manage memory):

library(XLConnect)

shinyServer(function(input, output) { 
  Dat <- reactiveValues()
  observe({
    if (!is.null(input$iFile)) {
      inFile <- input$iFile
      wb <- loadWorkbook(inFile$datapath)
      sheets <- getSheets(wb)
      Dat$wb <- wb
      Dat$sheets <- sheets
    }
  })

  output$ui <- renderUI({
    if (!is.null(Dat$sheets)) {
      selectInput(inputId = "sheet", label = "Select a sheet:", choices = Dat$sheets)
    }
  })

  observe({
    if (!is.null(Dat$wb)) {
      if (!is.null(input$sheet)){
        dat <- readWorksheet(Dat$wb, input$sheet)
        print(names(dat))
        output$columns <- renderUI({
          checkboxGroupInput("columns", "Choose columns", 
                             choices  = names(dat))
        })
      }
    }
  })
})



shinyUI(pageWithSidebar(
  # Include css file;
  tagList(
    tags$head(
      tags$title("Upload Data"),
      tags$h1("Test")
    )
  ),

  # Control panel;
  sidebarPanel(
    fileInput(inputId = "iFile", label = "Escolha um arquivo:", accept="application/vnd.ms-excel"),
    radioButtons("model", "Escolha do Modelo:",
                 list("CRS" = "crs",
                      "VRS" = "vrs")),
    br(),
    tags$hr(),
    uiOutput(outputId = "ui"),
    uiOutput(outputId = "columns")
  ),
  # Output panel;

mainPanel()
))

Specifying data range from excel in Shiny

Answers (2)

Related Questions