# Github link for mysql script file
#https://github.com/johnsuh23/DATA-607/blob/master/DATA%20607%20WK%202%20MYSQLDB.sqllibrary(RMySQL)## Loading required package: DBIlibrary(tidyverse)## -- Attaching packages ------------------ tidyverse 1.2.1 --## v ggplot2 3.2.1 v purrr 0.3.2
## v tibble 2.1.1 v dplyr 0.8.1
## v tidyr 0.8.3 v stringr 1.4.0
## v readr 1.3.1 v forcats 0.4.0## -- Conflicts --------------------- tidyverse_conflicts() --
## x dplyr::filter() masks stats::filter()
## x dplyr::lag() masks stats::lag()#code for actual db credentials have been hidden, below is the code without user/psswrd/host
#db <- dbConnect(MySQL(), user=' ', password=' ', dbname='moviereview', host=' ')DATAFRAME FROM MYSQL MOVIEREVIEW DATABASE
df<-collect(dbGetQuery(db,"SELECT m.movieid,m.title,m.genre,r.userid,r.username,r.rating
FROM movie as m
JOIN reviews as r
ON m.movieid=r.movieid
Order by m.movieid,r.username"))
df## movieid title
## 1 1 Rampage (2018)
## 2 1 Rampage (2018)
## 3 1 Rampage (2018)
## 4 1 Rampage (2018)
## 5 1 Rampage (2018)
## 6 2 Jurassic World: Fallen Kingdom (2018)
## 7 2 Jurassic World: Fallen Kingdom (2018)
## 8 2 Jurassic World: Fallen Kingdom (2018)
## 9 2 Jurassic World: Fallen Kingdom (2018)
## 10 2 Jurassic World: Fallen Kingdom (2018)
## 11 3 Incredibles 2 (2018)
## 12 3 Incredibles 2 (2018)
## 13 3 Incredibles 2 (2018)
## 14 3 Incredibles 2 (2018)
## 15 3 Incredibles 2 (2018)
## 16 4 Deadpool 2 (2018)
## 17 4 Deadpool 2 (2018)
## 18 4 Deadpool 2 (2018)
## 19 4 Deadpool 2 (2018)
## 20 4 Deadpool 2 (2018)
## 21 5 Solo: A Star Wars Story (2018)
## 22 5 Solo: A Star Wars Story (2018)
## 23 5 Solo: A Star Wars Story (2018)
## 24 5 Solo: A Star Wars Story (2018)
## 25 5 Solo: A Star Wars Story (2018)
## genre userid username rating
## 1 Action|Adventure|Sci-Fi 3 Jane NA
## 2 Action|Adventure|Sci-Fi 4 Joe 4
## 3 Action|Adventure|Sci-Fi 1 John 3
## 4 Action|Adventure|Sci-Fi 2 Mary 4
## 5 Action|Adventure|Sci-Fi 5 Ray 1
## 6 Action|Adventure|Drama|Sci-Fi|Thriller 3 Jane 3
## 7 Action|Adventure|Drama|Sci-Fi|Thriller 4 Joe 2
## 8 Action|Adventure|Drama|Sci-Fi|Thriller 1 John 2
## 9 Action|Adventure|Drama|Sci-Fi|Thriller 2 Mary 4
## 10 Action|Adventure|Drama|Sci-Fi|Thriller 5 Ray 3
## 11 Action|Adventure|Animation|Children 3 Jane 1
## 12 Action|Adventure|Animation|Children 4 Joe 3
## 13 Action|Adventure|Animation|Children 1 John 4
## 14 Action|Adventure|Animation|Children 2 Mary 4
## 15 Action|Adventure|Animation|Children 5 Ray 5
## 16 Action|Comedy|Sci-Fi 3 Jane 5
## 17 Action|Comedy|Sci-Fi 4 Joe 2
## 18 Action|Comedy|Sci-Fi 1 John 5
## 19 Action|Comedy|Sci-Fi 2 Mary 4
## 20 Action|Comedy|Sci-Fi 5 Ray 4
## 21 Action|Adventure|Children|Sci-Fi 3 Jane 3
## 22 Action|Adventure|Children|Sci-Fi 4 Joe 3
## 23 Action|Adventure|Children|Sci-Fi 1 John 4
## 24 Action|Adventure|Children|Sci-Fi 2 Mary 4
## 25 Action|Adventure|Children|Sci-Fi 5 Ray 5dim(df)## [1] 25 6str(df)## 'data.frame': 25 obs. of 6 variables:
## $ movieid : int 1 1 1 1 1 2 2 2 2 2 ...
## $ title : chr "Rampage (2018)" "Rampage (2018)" "Rampage (2018)" "Rampage (2018)" ...
## $ genre : chr "Action|Adventure|Sci-Fi" "Action|Adventure|Sci-Fi" "Action|Adventure|Sci-Fi" "Action|Adventure|Sci-Fi" ...
## $ userid : int 3 4 1 2 5 3 4 1 2 5 ...
## $ username: chr "Jane" "Joe" "John" "Mary" ...
## $ rating : int NA 4 3 4 1 3 2 2 4 3 ...summary(df)## movieid title genre userid
## Min. :1 Length:25 Length:25 Min. :1
## 1st Qu.:2 Class :character Class :character 1st Qu.:2
## Median :3 Mode :character Mode :character Median :3
## Mean :3 Mean :3
## 3rd Qu.:4 3rd Qu.:4
## Max. :5 Max. :5
##
## username rating
## Length:25 Min. :1.000
## Class :character 1st Qu.:3.000
## Mode :character Median :4.000
## Mean :3.417
## 3rd Qu.:4.000
## Max. :5.000
## NA's :1```