DATA607_Homework2
Load packages to connect to MySQL database
install.packages("RMySQL", repos = "http://cran.us.r-project.org")## Installing package into 'C:/Users/ltcan/Documents/R/win-library/3.6'
## (as 'lib' is unspecified)## package 'RMySQL' successfully unpacked and MD5 sums checked## Warning: cannot remove prior installation of package 'RMySQL'## Warning in file.copy(savedcopy, lib, recursive = TRUE): problem copying C:
## \Users\ltcan\Documents\R\win-library\3.6\00LOCK\RMySQL\libs\x64\RMySQL.dll
## to C:\Users\ltcan\Documents\R\win-library\3.6\RMySQL\libs\x64\RMySQL.dll:
## Permission denied## Warning: restored 'RMySQL'##
## The downloaded binary packages are in
## C:\Users\ltcan\AppData\Local\Temp\RtmpOamKPJ\downloaded_packagesinstall.packages("Rtools", repos = "http://cran.us.r-project.org")## Installing package into 'C:/Users/ltcan/Documents/R/win-library/3.6'
## (as 'lib' is unspecified)## Warning: package 'Rtools' is not available (for R version 3.6.1)library(DBI)
library(RMariaDB)
library(ggplot2)Connect to MySQL database
source("login_credentials.R")
mydb = dbConnect(RMariaDB::MariaDB(), user=username, password=password, dbname='movie',host='localhost')Query movies table from SQL
dbGetQuery(mydb,'SELECT *
FROM movie.movie_review
ORDER BY review_id')## review_id reviewer_id last_name first_name
## 1 26 1 Baggins Bilbo
## 2 27 1 Baggins Bilbo
## 3 28 1 Baggins Bilbo
## 4 29 1 Baggins Bilbo
## 5 30 1 Baggins Bilbo
## 6 31 2 Baggins Frodo
## 7 32 2 Baggins Frodo
## 8 33 2 Baggins Frodo
## 9 34 2 Baggins Frodo
## 10 35 2 Baggins Frodo
## 11 36 3 Gamgee Samwise
## 12 37 3 Gamgee Samwise
## 13 38 3 Gamgee Samwise
## 14 39 3 Gamgee Samwise
## 15 40 3 Gamgee Samwise
## 16 41 4 Took Peregrin
## 17 42 4 Took Peregrin
## 18 43 4 Took Peregrin
## 19 44 4 Took Peregrin
## 20 45 4 Took Peregrin
## 21 46 5 Brandybuck Meriadoc
## 22 47 5 Brandybuck Meriadoc
## 23 48 5 Brandybuck Meriadoc
## 24 49 5 Brandybuck Meriadoc
## 25 50 5 Brandybuck Meriadoc
## movie_name rating
## 1 Fast & Furious Presents: Hobbs & Shaw 4
## 2 Spider-Man: Far From Home 5
## 3 Stuber 1
## 4 The Lion King 4
## 5 Toy Story 4 5
## 6 Fast & Furious Presents: Hobbs & Shaw 3
## 7 Spider-Man: Far From Home 2
## 8 Stuber 3
## 9 The Lion King 5
## 10 Toy Story 4 5
## 11 Fast & Furious Presents: Hobbs & Shaw 4
## 12 Spider-Man: Far From Home 4
## 13 Stuber 5
## 14 The Lion King 4
## 15 Toy Story 4 5
## 16 Fast & Furious Presents: Hobbs & Shaw 5
## 17 Spider-Man: Far From Home 2
## 18 Stuber 5
## 19 The Lion King 3
## 20 Toy Story 4 4
## 21 Fast & Furious Presents: Hobbs & Shaw 1
## 22 Spider-Man: Far From Home 1
## 23 Stuber 4
## 24 The Lion King 1
## 25 Toy Story 4 3Load results from query to dataframe
movie_review_results <- dbGetQuery(mydb, 'SELECT *
FROM movie.movie_review
ORDER BY review_id')
movie_review_results## review_id reviewer_id last_name first_name
## 1 26 1 Baggins Bilbo
## 2 27 1 Baggins Bilbo
## 3 28 1 Baggins Bilbo
## 4 29 1 Baggins Bilbo
## 5 30 1 Baggins Bilbo
## 6 31 2 Baggins Frodo
## 7 32 2 Baggins Frodo
## 8 33 2 Baggins Frodo
## 9 34 2 Baggins Frodo
## 10 35 2 Baggins Frodo
## 11 36 3 Gamgee Samwise
## 12 37 3 Gamgee Samwise
## 13 38 3 Gamgee Samwise
## 14 39 3 Gamgee Samwise
## 15 40 3 Gamgee Samwise
## 16 41 4 Took Peregrin
## 17 42 4 Took Peregrin
## 18 43 4 Took Peregrin
## 19 44 4 Took Peregrin
## 20 45 4 Took Peregrin
## 21 46 5 Brandybuck Meriadoc
## 22 47 5 Brandybuck Meriadoc
## 23 48 5 Brandybuck Meriadoc
## 24 49 5 Brandybuck Meriadoc
## 25 50 5 Brandybuck Meriadoc
## movie_name rating
## 1 Fast & Furious Presents: Hobbs & Shaw 4
## 2 Spider-Man: Far From Home 5
## 3 Stuber 1
## 4 The Lion King 4
## 5 Toy Story 4 5
## 6 Fast & Furious Presents: Hobbs & Shaw 3
## 7 Spider-Man: Far From Home 2
## 8 Stuber 3
## 9 The Lion King 5
## 10 Toy Story 4 5
## 11 Fast & Furious Presents: Hobbs & Shaw 4
## 12 Spider-Man: Far From Home 4
## 13 Stuber 5
## 14 The Lion King 4
## 15 Toy Story 4 5
## 16 Fast & Furious Presents: Hobbs & Shaw 5
## 17 Spider-Man: Far From Home 2
## 18 Stuber 5
## 19 The Lion King 3
## 20 Toy Story 4 4
## 21 Fast & Furious Presents: Hobbs & Shaw 1
## 22 Spider-Man: Far From Home 1
## 23 Stuber 4
## 24 The Lion King 1
## 25 Toy Story 4 3Testing visualizations using ggplot
ggplot(movie_review_results, aes(x = rating)) + geom_bar()
Bar graph with labels
ggplot(movie_review_results, aes(x = rating)) + theme_bw() + geom_bar() + labs(y = "Rating Count", x = "Rating", title = "Overall Rating Totals")
Adding some color to the graph to assist with the data visualization
ggplot(movie_review_results, aes(x = rating, fill = movie_name)) + theme_bw() + geom_bar() + labs(y = "Rating Count", x = "Rating", title = "Overall Ratings")
Grouping ratings based on reviewer ID.
ggplot(movie_review_results, aes(x = rating, fill = movie_name)) + theme_bw() + facet_wrap(~ reviewer_id) + geom_bar() + labs(y = "Rating Count", x = "Rating", title = "Ratings Grouped by Reviewer ID")
Grouping ratings based on reviewer first name.
ggplot(movie_review_results, aes(x = rating, fill = movie_name)) + theme_bw() + facet_wrap(~ first_name) + geom_bar() + labs(y = "Rating Count", x = "Rating", title = "Ratings Grouped by Reviewer Name")
Plaing with boxplot for the same ratings but I prefer the colored bar graph
ggplot(movie_review_results, aes(x = movie_name, y = rating)) + theme_bw() + geom_boxplot() + labs(y = "Movie Rating", x = "Movie Name", title = "Movie Ratings")