glgeo1 glgeo1 - 3 months ago 5
R Question

Cleaning a data frame inputed with fileInput and plotting it - R Shiny

Goal: Create a shiny app that can take in data from the user using the fileInput() function and then clean the data that is being brought in with the help of dplyr and a lot of other data manipulation techniques and then plot this data and display a table of the cleaned data and also have a download button to download the cleansed data.

What I have done: I have successfully created the app without the fileInput. I simply cleansed the data using a R script and then added the script before the user interface portion of the app and ran the app and it worked fine. However, this did not have user input capability.

The code below is a simplified version of my ui and server side of the app that attempts to add user input functionality.

Dept <- c(1,2,3)
DepartmentName <-c("abc","def","ghi")
Dept_Names <- cbind.data.frame(Dept,DepartmentName)

ui <- dashboardPage(
dashboardHeader(title="ABC"),
dashboardSidebar(width = 150,
sidebarMenu(
menuItem("DataInput",tabName = "DataInput"),
menuItem("Dashboard", tabName = "Dashboard")
)),
dashboardBody(
tabItems(
tabItem(tabName = "DataInput",
box(fileInput('file1', 'Choose CSV File',
accept=c('.csv')),
tags$hr(),
checkboxInput('header', 'Header', TRUE),
radioButtons('sep', 'Separator',
c(Comma=',',
Semicolon=';',
Tab='\t'),
','),
actionButton("Load", "Load the File"))
),
tabItem( tabName = "Dashboard",
fluidRow(column(9,box(status = "primary",plotOutput("plot1"))
),
column(3,
box(title="Controls",
selectInput(inputId = "Dept",
label = "Select",
choices = C(1,2,3)),
numericInput(inputId = "ClassNum",
label = "Enter the Class Number", value = 1, min=0, max=100000),
status = "warning", solidHeader = TRUE, width="100%"),

downloadButton('downloadData', 'Download')

)
),
box(DT::dataTableOutput("table"), width = "100%,",status = "primary")
)
)
)
)

server <- function(input,output) {

data <- reactive({
if(input$Load == 0){return()}
inFile <- input$file1
if (is.null(inFile)){return(NULL)}

isolate({
input$Load
a <- read.csv(inFile$datapath, header = input$header,sep = input$sep,stringsAsFactors =FALSE)

options(stringsAsFactors = FALSE)
a <- a[,1:5]
names(a) <- c("Dept","Description","Price","ClassNum","Quantity")

a <- a %>%
filter(Quantity > 0) %>%
filter(!(Price==""))

for (i in 1:length(a)) {
for (j in 1:nrow(a)) {
if (i==2) {next}
a[j,i] <- gsub(",", "", a[j,i])
}
}
rm(i,j)

a <- merge(a,Dept_Names, by="Dept")

for (j in 1:nrow(a)) {
if (a$ClassNum[j]=="") {a[j,4] <-0} else {a[j,4] <- a$ClassNum[j]}
}
rm(j)

a$Dept <- as.numeric(as.character(a$Dept))
a$ClassNum <- as.numeric(as.character(a$ClassNum))
a$Price <- as.numeric(as.character(a$Price))
a$Quantity <- as.numeric(as.character(a$Quantity))

a <- a %>%
mutate(Revenue=Price*Quantity,Key1=paste(Dept, "_", ClassNum, sep=""))

total_complete <- a %>%
group_by(Dept, ClassNum) %>%
summarise(Revenue=sum(Revenue)) %>%
arrange(Dept, desc(Revenue)) %>%
mutate(Key1=paste(Dept, "_", ClassNum, sep=""))

ofn <- paste0("ABC",Sys.time(),".csv")
ofn <- gsub(":","_",ofn)

})

})


output$downloadData <- downloadHandler(
filename = data()$ofn,
content = function(file) {
write.csv(data()$total_complete, file, row.names = FALSE)
}
)


output$table <- DT::renderDataTable(DT::datatable({data1 <- data()$total_complete[data()$total_complete[,1]==input$Dept ,]
}))

output$plot1 <- renderPlot({

ggplot(total_complete[total_complete$Department==input$Dept,],
aes(reorder(x=Key1,desc(Revenue)), y=Revenue, fill=Key1)) +
geom_bar(stat="identity") +
theme(axis.text.x=element_text(angle=90,hjust=1,vjust=0.5))
})



}



shinyApp(ui, server)


The input data looks like this

Dept # Description Retail Class # Q
10 MOP 11.99 100 1
10 Broom 7.99 101 2
10 soap 5.99 102 3
10 key 7.99 103 4
10 salmon 34.99 104 5
10 steak 11.99 105 5
10 wine 9.99 106 7


Thanks for all help.

Answer

I used the data that you had provided above and I managed to debug your code.

  • First of all I changed the last value of the variable dept to 10 and similarly the choices in selectInput because the merging of a and Dept_Names had resulted in an empty data frame and the app had crashed. To prevent crashing I added a function validate that requires that the data set a after merging is non empty.

  • I changed C to c within selectInput

  • data had no return value and I set it to the list where total_complete is a cleaned data frame and ofn is the name of the downloaded data frame:

    return(list(total_complete = total_complete, ofn = ofn))
    
  • within two render* functions I added req(data()) which prevents errors if the data() is not available (when it is NULL)

  • added data()$ within render* functions to total_complete and changed Departments to Dept

To get nonempty plot and a nonempty table, after uploading the data which you included in your question, set the value of the selectInput to 10

I've made comments in the code to indicate all above changes.

Full code:

library(shiny)
library(shinydashboard)
library(ggplot2)
library(dplyr)

rm(ui)
rm(server)

Dept <- c(1,2,10) # changed 3 to 10 to avoid an empty data set a after merging.
DepartmentName <-c("abc","def","ghi")
Dept_Names <- cbind.data.frame(Dept,DepartmentName)

ui <- dashboardPage(   
  dashboardHeader(title="ABC"),
  dashboardSidebar(width = 150,
                   sidebarMenu(  
                     menuItem("DataInput",tabName = "DataInput"),      
                     menuItem("Dashboard", tabName = "Dashboard")
                   )),
  dashboardBody(
    tabItems(
      tabItem(tabName = "DataInput", 
              box(fileInput('file1', 'Choose CSV File',
                            accept=c('.csv')),
                  tags$hr(),
                  checkboxInput('header', 'Header', TRUE),
                  radioButtons('sep', 'Separator',
                               c(Comma=',',
                                 Semicolon=';',
                                 Tab='\t',
                                 Whitespace = " "),
                               ','),
                  actionButton("Load", "Load the File"))
      ),
      tabItem( tabName = "Dashboard",
               fluidRow(column(9,box(status = "primary", plotOutput("plot1"))
               ),
               column(3,
                      box(title="Controls",
                          selectInput(inputId = "Dept", 
                                      label = "Select", 
                                      choices = c(1,2,10)), # changed C to c and 3 to 10 
                          numericInput(inputId = "ClassNum", 
                                       label = "Enter the Class Number", value = 1, min=0, max=100000),
                          status = "warning", solidHeader = TRUE, width="100%"),

                      downloadButton('downloadData', 'Download')

               )
               ), 
               box(DT::dataTableOutput("table"), width = "100%,",status = "primary")
      )
    )
  )
)

server <- function(input,output) { 

  data <- reactive({
    if(input$Load == 0) {
      return(NULL)
    }
    inFile <- input$file1
    if (is.null(inFile)) {
      return(NULL)
    }
      a <- read.csv(inFile$datapath, header = input$header,sep = input$sep,stringsAsFactors =FALSE)

      options(stringsAsFactors = FALSE) 
      a <- a[,1:5] 
      names(a) <- c("Dept","Description","Price","ClassNum","Quantity") 

      a <- a %>%
        filter(Quantity > 0) %>%
        filter(!(Price==""))  

      for (i in 1:length(a)) {  
        for (j in 1:nrow(a)) {
          if (i==2) {next}
          a[j,i] <- gsub(",", "", a[j,i])
        }  
      }  
      rm(i,j)  

      # You have to be careful here because it can be an empty data frame!
      a <- merge(a, Dept_Names, by="Dept")

      validate(
        need(nrow(a) != 0, 'Merge was not successful')
      )


      for (j in 1:nrow(a)) { 
        if (a$ClassNum[j]=="") {
          a[j,4] <-0
        } else {
          a[j,4] <- a$ClassNum[j]
        }
      } 
      rm(j)

      a$Dept <- as.numeric(as.character(a$Dept))
      a$ClassNum <- as.numeric(as.character(a$ClassNum))
      a$Price <- as.numeric(as.character(a$Price))
      a$Quantity <- as.numeric(as.character(a$Quantity))

      a <- a %>%
        mutate(Revenue=Price*Quantity,Key1=paste(Dept, "_", ClassNum, sep="")) 

      total_complete <- a %>%
        group_by(Dept, ClassNum) %>%
        summarise(Revenue=sum(Revenue)) %>%
        arrange(Dept, desc(Revenue)) %>%
        mutate(Key1=paste(Dept, "_", ClassNum, sep="")) 

      ofn <- paste0("ABC",Sys.time(),".csv")
      ofn <- gsub(":","_",ofn)


      # It looks like that you want to return a list
      # total_complete is a processed data frame and ofn is a name of the file
      # that is going to be downloaded
      return(list(total_complete = total_complete, ofn = ofn))

    })


  output$downloadData <- downloadHandler( 
    filename = function() { # added a function as in an example from ?downloadHandler
      data()$ofn
    },
    content = function(file) {
      write.csv(data()$total_complete, file, row.names = FALSE)
    }
  )


  output$table <- DT::renderDataTable({ 
    req(data()) # require that data() is available
    DT::datatable(data()$total_complete[data()$total_complete[,1]==input$Dept ,])
  })

  output$plot1 <- renderPlot({
    req(data()) # require that data() is available

    # there is no such variable as "Department" hence changed to $Dept
    # added data()$
    ggplot(data()$total_complete[data()$total_complete$Dept==input$Dept,], 
           aes(reorder(x=Key1,desc(Revenue)), y=Revenue, fill=Key1)) +
      geom_bar(stat="identity") +
      theme(axis.text.x=element_text(angle=90,hjust=1,vjust=0.5)) 
  })
} 

shinyApp(ui, server)
Comments