Week 2 Assignment - SQL and R

Introduction

we are trying to find highly rated movie.

The following packages are loaded first if required:
DT
RMySQL
DBI
ggplot2

## Loading required package: DT

## Loading required package: RMySQL

## Loading required package: DBI

## Loading required package: ggplot2

Load data

To load the SQL data into R, the dbConnect and dbGetQuery functions are used from the RMySQL package.

connString = dbConnect(MySQL(), user='root', password = 'tester', dbname='moviesdb', host='localhost')
dbListTables(connString)

## [1] "movie_table" "reviewer"    "reviews"

movie_ratings.df <- dbGetQuery(connString, "SELECT p.first_name, p.last_name, m.movie_name, r.ratings FROM moviesdb.reviewer p
    LEFT JOIN moviesdb.reviews r
        ON p.reviewer_id = r.reviewer_id
    LEFT JOIN moviesdb.movie_table m
        ON m.movie_id = r.movie_id
    ORDER BY m.movie_name")


movies <- dbGetQuery(connString, "select movie_name as Movie from movie_table")

Recent movies

datatable(movies, options = list(pageLength = 6))

## Rating

movie_ratings.df

##    first_name last_name                 movie_name ratings
## 1     Jovaria      Asif      Avengers Infinity War       5
## 2      Naimul     Islam      Avengers Infinity War       4
## 3       Mansi   Thakkar      Avengers Infinity War       5
## 4    Davinder     Singh      Avengers Infinity War       4
## 5      Lesile   Remirez      Avengers Infinity War       4
## 6     Jovaria      Asif Mission Impossible Fallout       4
## 7      Naimul     Islam Mission Impossible Fallout       4
## 8       Mansi   Thakkar Mission Impossible Fallout       4
## 9    Davinder     Singh Mission Impossible Fallout       4
## 10     Lesile   Remirez Mission Impossible Fallout       4
## 11    Jovaria      Asif                      Stree       3
## 12     Naimul     Islam                      Stree       4
## 13      Mansi   Thakkar                      Stree       4
## 14   Davinder     Singh                      Stree       4
## 15     Lesile   Remirez                      Stree       4
## 16    Jovaria      Asif                    The Meg       2
## 17     Naimul     Islam                    The Meg       2
## 18      Mansi   Thakkar                    The Meg       2
## 19   Davinder     Singh                    The Meg       2
## 20     Lesile   Remirez                    The Meg       1
## 21    Jovaria      Asif                    The Num       3
## 22     Naimul     Islam                    The Num       4
## 23      Mansi   Thakkar                    The Num       3
## 24   Davinder     Singh                    The Num       4
## 25     Lesile   Remirez                    The Num       3
## 26    Jovaria      Asif               The Predator       2
## 27     Naimul     Islam               The Predator       3
## 28      Mansi   Thakkar               The Predator       2
## 29   Davinder     Singh               The Predator       3
## 30     Lesile   Remirez               The Predator       3

Graph

movie_ratings.df$movie_name <- as.factor(movie_ratings.df$movie_name)
ggplot(movie_ratings.df) + geom_bar(aes(movie_name, ratings, fill=movie_name), stat="summary", fun.y="mean") + labs(x="Movies", y="Avg.Ratings", title="") + theme(axis.text.x=element_blank())

Conclusion

Result suggests that Avengers Infinity War is highly rated movie among my friends.

DATA 607: R and MySQL, Week2HW