Assignment Overview

Choose six recent popular movies. Ask at least five people that you know (friends, family, classmates, imaginary friends) to rate each of these movie that they have seen on a scale of 1 to 5. Take the results (observations) and store them in a SQL database. Load the information into an R dataframe. Your deliverables should include your SQL scripts and your R Markdown code, posted to GitHub. This is by design a very open ended assignment. A variety of reasonable approaches are acceptable. You can (and should) blank out your SQL password if your solution requires it; otherwise, full credit requires that your code is “reproducible,” with the assumption that I have the same database server and R software.

My Approach

My approach to this assignment is as follows:

  • Created simple (unTidy) moviesurvey Mysql database to persist my survey data
  • Created Shiny App to take survey and store results in Mysql table.
  • Used DBI package to connect to Mysql table and create a tibble
  • Used tidyr::gather to Tidy moviesurvey data
  • Produced plot using tidy data and ggplot2

Shiny Survey App

This Shiny App connects to my local mysql database. I have provided a link to
an alternative version that uses rstudio as its backend:

https://mundymsds.shinyapps.io/Assignment2v2/

library(shiny)
library(RMySQL)

options(mysql = list(
  "host" = "127.0.0.1",
  "port" = 3306,
  "user" = "root",
  "password" = "dolphin"
))

databaseName <- "moviesurvey"
table <- "moviesurvey"

saveData <- function(data) {
  # Connect to the database
  db <- dbConnect(MySQL(), dbname = databaseName, host = options()$mysql$host, 
                  port = options()$mysql$port, user = options()$mysql$user, 
                  password = options()$mysql$password)
  # Construct the update query by looping over the data fields
  query <- sprintf(
    "INSERT INTO %s (%s) VALUES ('%s')",
    table, 
    paste(names(data), collapse = ", "),
    paste(data, collapse = "', '")
  )
  # Submit the update query and disconnect
  dbGetQuery(db, query)
  dbDisconnect(db)
}

loadData <- function() {
  # Connect to the database
  db <- dbConnect(MySQL(), dbname = databaseName, host = options()$mysql$host, 
                  port = options()$mysql$port, user = options()$mysql$user, 
                  password = options()$mysql$password)
  # Construct the fetching query
  query <- sprintf("SELECT * FROM %s", table)
  # Submit the fetch query and disconnect
  data <- dbGetQuery(db, query)
  dbDisconnect(db)
  data
}

# Define the fields we want to save from the form
fields <- c("name", "godfather", "shawshank", "schindler", "raging_bull", "casablanca", "citizen_kane")

# Shiny app with 3 fields that the user can submit data for
shinyApp(
  ui = fluidPage(
    titlePanel("Survey: Top Six Movies of All Time"),
    h4("Rate each movie from 0 to 5 (zero=did not see - 5=highest rating)"),
    h6(""),
    DT::dataTableOutput("responses", width = 300), tags$hr(),
    textInput("name", "Enter your Name to begin Survey:", ""),
    sliderInput("godfather", "The Godfather (1972) R",
                0, 5, 0, ticks = TRUE),
    sliderInput("shawshank", "The Shawshank Redemption (1994) R",
                0, 5, 0, ticks = TRUE),
    sliderInput("schindler", "Schindler's List (1993) R",
                0, 5, 0, ticks = TRUE),
    sliderInput("raging_bull", "Raging Bull (1980) R  ",
                0, 5, 0, ticks = TRUE),
    sliderInput("casablanca", "Casablanca (1942)",
                0, 5, 0, ticks = TRUE),
    sliderInput("citizen_kane", "Citizen Kane (1941)",
                0, 5, 0, ticks = TRUE),
    actionButton("submit", "Submit")
  ),
  server = function(input, output, session) {
    
    # Whenever a field is filled, aggregate all form data
    formData <- reactive({
      data <- sapply(fields, function(x) input[[x]])
      data
    })
    
    # When the Submit button is clicked, save the form data
    observeEvent(input$submit, {
      saveData(formData())
    })
    
    # Show the previous responses
    # (update with current response when Submit is clicked)
    output$responses <- DT::renderDataTable({
      input$submit
      loadData()
    })     
  }
)
Shiny applications not supported in static R Markdown documents

Load Movie Survey In Tibble

name godfather shawshank schindler raging_bull casablanca citizen_kane
Jim Mundy 5 5 4 3 3 3
Gus Mundy 5 5 0 4 4 5
Aaron Judge 5 5 5 5 5 5
CC Sabathtia 5 5 4 5 4 4
Derek Jeter 5 5 5 5 4 4
Gary Sanchez 4 5 4 4 4 0
Domingo German 4 5 4 4 5 5
DJ LeMahieu 5 5 4 5 5 5
Paul Pierce 4 4 4 4 5 3

Tidy Data

movies <- movie_survey %>% 
  gather('godfather', 'shawshank', 'schindler', 'raging_bull', 'casablanca', 'citizen_kane', key="movie", value="rating")

glimpse(movies)
## Observations: 54
## Variables: 3
## $ name   <chr> "Jim Mundy", "Gus Mundy", "Aaron Judge", "CC Sabathtia"...
## $ movie  <chr> "godfather", "godfather", "godfather", "godfather", "go...
## $ rating <int> 5, 5, 5, 5, 5, 4, 4, 5, 4, 5, 5, 5, 5, 5, 5, 5, 5, 4, 4...

Plot Data

ggplot(movies, aes(name, rating)) +   
  geom_bar(aes(fill = movie), position = "dodge", stat="identity") +
  coord_flip()