607: Assignment 2

library(DBI); library(ggplot2);

Introduction

In this open ended assignment, I will load the data two ways. First from sql,then I will generate a barplot and then from a csv file.
- Run the MovieSurvey.sql script on “https://raw.githubusercontent.com/nilsabermudez/607/master/MovieSurvey.sql” - Load the data from the database - List the tables - Populate the variables for BarPlot - Show the BarPlot - List the movies by average rating as shown in BarPlot - Load the data into an R dataframe from the csv file from github

Load the data from the database

con =  DBI::dbConnect(RMySQL::MySQL(), user="root", password="root", dbname="movies", host="localhost")
result <- DBI::dbGetQuery(con, "select name, Age, Parent, MovieName, Rating from Reviewers 
          inner join Ratings on Reviewers.ID =Ratings.ReviewerID inner join Movies on Movies.ID =Ratings.MovieID;")

List the tables

DBI::dbListTables(con)

## [1] "movies"    "ratings"   "reviewers"

head(result)

##      name Age Parent               MovieName Rating
## 1   Angel  45      Y Jumanji: The Next Level      4
## 2   Maria  32      Y Jumanji: The Next Level      4
## 3 Anabell  36      Y Jumanji: The Next Level      3
## 4 Jessica  34      Y Jumanji: The Next Level      4
## 5   Laura  42      Y Jumanji: The Next Level      3
## 6   Angel  45      Y                Frozen 2      3

Populate variables for a bar plot

res <- DBI::dbGetQuery(con, "SELECT m.ID AS MovieID, AVG(r.rating) AS avg_rating
                                  FROM ratings r 
                                  INNER JOIN
                                      movies m ON r.movieid = m.id
                                  GROUP BY m.ID
                                  ORDER BY AVG(r.rating) DESC; ")

## Warning in .local(conn, statement, ...): Decimal MySQL column 1 imported as
## numeric

barplot(height=res$avg_rating, names.arg = res$MovieID, xlab = "Movie ID", ylab ="Average Rating", main = "Friends' Movie Ratings", col.main="red", font.main=8)

Movies by average rating as shown in BarPlot

ShowMovieDetail <- DBI::dbGetQuery(con, "SELECT m.ID, m.MovieName, AVG(r.rating) AS avg_rating
                                  FROM ratings r 
                                  INNER JOIN
                                      movies m ON r.movieid = m.id
                                  GROUP BY m.ID, m.MovieName
                                  ORDER BY AVG(r.rating) DESC;")

## Warning in .local(conn, statement, ...): Decimal MySQL column 2 imported as
## numeric

ShowMovieDetail

##   ID                        MovieName avg_rating
## 1  4 Star Wars: The Rise of Skywalker        4.0
## 2  1          Jumanji: The Next Level        3.6
## 3  2                         Frozen 2        3.0
## 4  3                      Like a Boss        1.6
## 5  5                    The Gentlemen        0.0
## 6  6                Bad Boys for Life        0.0

My friends are all moviegoers. They saw all 6 movies. However, if a rating of 0 meant they had not seen the movie, this would be the result:

ShowMovieDetailExcluding0 <- DBI::dbGetQuery(con, "SELECT m.ID, m.MovieName, AVG(r.rating) AS avg_rating
                                  FROM ratings r 
                                  INNER JOIN
                                      movies m ON r.movieid = m.id
                                  where r.rating <>0
                                  GROUP BY m.ID, m.MovieName
                                  ORDER BY AVG(r.rating) DESC;")

## Warning in .local(conn, statement, ...): Decimal MySQL column 2 imported as
## numeric

ShowMovieDetailExcluding0 

##   ID                        MovieName avg_rating
## 1  3                      Like a Boss        4.0
## 2  4 Star Wars: The Rise of Skywalker        4.0
## 3  1          Jumanji: The Next Level        3.6
## 4  2                         Frozen 2        3.0

Load the data from the csv file from github

ratingsdf <- read.csv("https://raw.githubusercontent.com/nilsabermudez/607/master/MovieRatingsRaw.csv",header = TRUE, sep = ",")

head(ratingsdf)

##   ï..Name Age Parent                            Movie Rating
## 1   Angel  45      Y          Jumanji: The Next Level      4
## 2   Angel  45      Y                         Frozen 2      3
## 3   Angel  45      Y                      Like a Boss      0
## 4   Angel  45      Y Star Wars: The Rise of Skywalker      4
## 5   Angel  45      Y                    The Gentlemen      0
## 6   Angel  45      Y                Bad Boys for Life      0

Show the worst movies (according to my friends)

subset(ratingsdf, (Rating == 0))

##    ï..Name Age Parent             Movie Rating
## 3    Angel  45      Y       Like a Boss      0
## 5    Angel  45      Y     The Gentlemen      0
## 6    Angel  45      Y Bad Boys for Life      0
## 9    Maria  32      Y       Like a Boss      0
## 11   Maria  32      Y     The Gentlemen      0
## 12   Maria  32      Y Bad Boys for Life      0
## 17 Anabell  36      Y     The Gentlemen      0
## 18 Anabell  36      Y Bad Boys for Life      0
## 23 Jessica  34      Y     The Gentlemen      0
## 24 Jessica  34      Y Bad Boys for Life      0
## 29   Laura  42      Y     The Gentlemen      0
## 30   Laura  42      Y Bad Boys for Life      0