Movies

Querying the ‘Movies’ database created in MySQL- Data Manipulation Language

library(RMySQL)

## Loading required package: DBI

library(dplyr)

## 
## Attaching package: 'dplyr'

## The following objects are masked from 'package:stats':
## 
##     filter, lag

## The following objects are masked from 'package:base':
## 
##     intersect, setdiff, setequal, union

library(reshape)

## 
## Attaching package: 'reshape'

## The following object is masked from 'package:dplyr':
## 
##     rename

library(car)

## 
## Attaching package: 'car'

## The following object is masked from 'package:dplyr':
## 
##     recode

library(htmlTable)
library(sjPlot)

## #refugeeswelcome

myfilm = dbConnect(RMySQL::MySQL(), user='root', password='password', dbname='Movies', host='localhost')

#List All Tables
dbListTables(myfilm)

## [1] "movierating" "movies"      "people"

#Results fetching names, ratings and movies
res_sql<- "SELECT firstname, rating, moviename, genre, director FROM people  
INNER JOIN movierating 
ON people.nameid = movierating.nameid 
INNER JOIN movies 
ON movierating.movieid=movies.movieid ;"

res <- dbGetQuery(myfilm, res_sql)

htmlTable(res, caption = 'Movies db')

Movies db
	firstname	rating	moviename	genre	director
1	Nicholas	2	Twice Born	Drama	Sergio Castellitto
2	Nicholas	4	Smetto Quando Voglio	Comedy	Sydney Sibilia
3	Nicholas	5	The Imitation Game	Drama	Morten Tyldum
4	Nicholas	4	Rosewater	Drama	Jon Stewart
5	Nicholas	2	The Secret Life of Pets	3D	Chris Renaud
6	Nicholas	3	Moonrise Kingdom	Drama	Wes Anderson
7	Luca	1	Twice Born	Drama	Sergio Castellitto
8	Luca	2	Smetto Quando Voglio	Comedy	Sydney Sibilia
9	Luca	2	The Imitation Game	Drama	Morten Tyldum
10	Luca	1	Rosewater	Drama	Jon Stewart
11	Luca	5	The Secret Life of Pets	3D	Chris Renaud
12	Luca	4	Moonrise Kingdom	Drama	Wes Anderson
13	Ambra	4	Twice Born	Drama	Sergio Castellitto
14	Ambra	4	Smetto Quando Voglio	Comedy	Sydney Sibilia
15	Ambra	5	The Imitation Game	Drama	Morten Tyldum
16	Ambra	5	Rosewater	Drama	Jon Stewart
17	Ambra	3	The Secret Life of Pets	3D	Chris Renaud
18	Ambra	4	Moonrise Kingdom	Drama	Wes Anderson
19	Daniele	1	Twice Born	Drama	Sergio Castellitto
20	Daniele	5	Smetto Quando Voglio	Comedy	Sydney Sibilia
21	Daniele	3	The Imitation Game	Drama	Morten Tyldum
22	Daniele	5	Rosewater	Drama	Jon Stewart
23	Daniele	1	The Secret Life of Pets	3D	Chris Renaud
24	Daniele	2	Moonrise Kingdom	Drama	Wes Anderson
25	IldeAlfonzo	4	Twice Born	Drama	Sergio Castellitto
26	IldeAlfonzo	2	Smetto Quando Voglio	Comedy	Sydney Sibilia
27	IldeAlfonzo	5	The Imitation Game	Drama	Morten Tyldum
28	IldeAlfonzo	5	Rosewater	Drama	Jon Stewart
29	IldeAlfonzo	1	The Secret Life of Pets	3D	Chris Renaud
30	IldeAlfonzo	2	Moonrise Kingdom	Drama	Wes Anderson

#Rank movies by rating

rnks_sql<- "SELECT rating, moviename FROM movies
INNER JOIN movierating
ON movierating.movieid=movies.movieid
GROUP by moviename
ORDER by avg(rating) desc;"

rnks <- dbGetQuery(myfilm, rnks_sql)

htmlTable(rnks, caption = 'Movies Rank')

Movies Rank
	rating	moviename
1	5	The Imitation Game
2	4	Rosewater
3	4	Smetto Quando Voglio
4	3	Moonrise Kingdom
5	2	Twice Born
6	2	The Secret Life of Pets

#Favorite movies by firstname
favorite_sql<- "SELECT firstname, moviename, genre, rating FROM people
INNER JOIN movierating ON people.nameid = movierating.nameid 
INNER JOIN movies ON movierating.movieid = movies.movieid 
WHERE rating = 5 ;  "

favorite<- dbGetQuery(myfilm, favorite_sql)
htmlTable(favorite, caption = 'Movies Rank')

Movies Rank
	firstname	moviename	genre	rating
1	Nicholas	The Imitation Game	Drama	5
2	Luca	The Secret Life of Pets	3D	5
3	Ambra	The Imitation Game	Drama	5
4	Ambra	Rosewater	Drama	5
5	Daniele	Smetto Quando Voglio	Comedy	5
6	Daniele	Rosewater	Drama	5
7	IldeAlfonzo	The Imitation Game	Drama	5
8	IldeAlfonzo	Rosewater	Drama	5

#Likert plot of movies

rating_sql<-"SELECT nameid, moviename, rating FROM movies INNER JOIN movierating ON movierating.movieid=movies.movieid;"

dfrating<-dbGetQuery(myfilm, rating_sql)

#cast observatins into variables to resemble a survey response table

aggrating<-cast(dfrating, nameid ~ moviename, value.var="rating")

## Using rating as value column.  Use the value argument to cast to override this choice

dim(aggrating)

## [1] 5 7

#selecting only movies and movierating 
sjp.setTheme(theme = "539",
             geom.label.color = "black", 
             geom.label.size = 2.5,
             axis.textsize = .8, 
             axis.title.size = .9,
             legend.size = .7,
             legend.item.size = .5)
set.seed(1)

# Have to invert the scale each time I plot a Likert- still cannot figure out why
rating1 <- apply(aggrating, 2, function(x) recode(x,"1 = 5; 2 = 4 ; 3 = 3 ; 4 = 2; 5= 1"))

ratingdf<- as.data.frame(rating1)


labels <- c("Strongly Like" ,"Like", "Neither Like nor Dislike", "Dislike", "Strongly Dislike")

sjp.likert(cat.neutral = 3, ratingdf, title= "Movies rating", axis.titles= c(" ", "Aggregate Ratings"),  intercept.line.color = "white", values = "sum.outside", show.n = FALSE, show.prc.sign = TRUE, legend.labels = labels, sort.frq = "pos.asc", reverse.colors = TRUE, grid.range = 1.4)

## Warning: Detected uneven category count in items. Dropping last category.