DATA607_Week2_Assignment
Mohamed Thasleem Kalikul Zaman
February 2, 2019
Oscars 2019 - Movie Rating Analysis
Picked the Oscars 2019 Best picture Movie Nominess and collected movie ratings from 5 different group of people, calculated the average rating of each movie among 5 groups and presented as pictorial representation
Tasks Performed
1. Created a MySQL Schema as ‘movie’ with 2 tables (created with Primary and Refrence Key to maintain Integirity ) as below
     Oscars2019 - This table holds the Movie Name and its category
     Ratings - This table holds Ratings of each movie from 5 different group of people
2. Used RMySQL library to connect MySQL database and fetch the above mentioned data
3. Using INNER JOIN, 2 tables are joined based Primary Key ‘MovieID’ and grouped by Movies
4. Used CAST function to limit the decimal values while calculating Average Rating
5. Using ggplot2 library, bar chart created for pictorial representation on movies with its Average Ratings
6. Disconnect from database
7. Conclusion
#Library for MySQL database
library(RMySQL)## Loading required package: DBI#Connection Details
movie = dbConnect(MySQL(), user='root', password='mohamed', dbname='movie', host='localhost')
#Listing all the tables from movie Schema
dbListTables(movie)## [1] "movierating" "oscars2019"#Fetching Oscars2019 table
Oscars2019 <- dbSendQuery(movie, "select * from movie.Oscars2019")
Oscars2019_List <- fetch(Oscars2019)
#Display Oscars2019 table Results
Oscars2019_List## MovieId MovieName Category
## 1 1 BLACK PANTHER PG-13
## 2 2 BLACKkKLANSMAN R
## 3 3 BOHEMIAN RHAPSODY PG-13
## 4 4 THE FAVOURITE R
## 5 5 GREEN BOOK PG-13
## 6 6 ROMA R
## 7 7 A STAR IS BORN R
## 8 8 VICE R#Fetching Ratings table
Ratings <- dbSendQuery(movie, "select MovieID, RatingCategory, GivenRating from movie.MovieRating")## Warning in .local(conn, statement, ...): Decimal MySQL column 2 imported as
## numericMovieRatings <- fetch(Ratings)
#Display Ratings table Results
MovieRatings## MovieID RatingCategory GivenRating
## 1 1 Family 4.0
## 2 1 Friend 4.0
## 3 1 Kids 5.0
## 4 1 OnlineCritics 3.5
## 5 1 GeneralAudience 4.0
## 6 2 Family 3.0
## 7 2 Friend 3.0
## 8 2 Kids 2.0
## 9 2 OnlineCritics 3.5
## 10 2 GeneralAudience 3.0
## 11 3 Family 4.0
## 12 3 Friend 4.0
## 13 3 Kids 2.5
## 14 3 OnlineCritics 3.5
## 15 3 GeneralAudience 3.5
## 16 4 Family 3.5
## 17 4 Friend 4.0
## 18 4 Kids 2.0
## 19 4 OnlineCritics 3.5
## 20 4 GeneralAudience 3.0
## 21 5 Family 4.0
## 22 5 Friend 4.0
## 23 5 Kids 5.0
## 24 5 OnlineCritics 3.5
## 25 5 GeneralAudience 3.0
## 26 6 Family 3.5
## 27 6 Friend 4.5
## 28 6 Kids 3.0
## 29 6 OnlineCritics 3.5
## 30 6 GeneralAudience 4.0
## 31 7 Family 4.0
## 32 7 Friend 3.5
## 33 7 Kids 2.0
## 34 7 OnlineCritics 3.0
## 35 7 GeneralAudience 3.0
## 36 8 Family 3.2
## 37 8 Friend 4.0
## 38 8 Kids 2.0
## 39 8 OnlineCritics 4.0
## 40 8 GeneralAudience 3.5#Query for getting Average Ratings
BestMovieRating <- dbSendQuery(movie, "select m.MovieName, CAST(AVG(r.GivenRating) AS DECIMAL(3,2)) as AvgRating from movie.Oscars2019 m inner join movie.MovieRating r on r.MovieId = m.MovieId group by m.MovieName order by AvgRating desc")## Warning in .local(conn, statement, ...): Decimal MySQL column 1 imported as
## numericRating_List <- fetch(BestMovieRating)
#Display Ratings Results
Rating_List## MovieName AvgRating
## 1 BLACK PANTHER 4.10
## 2 GREEN BOOK 3.90
## 3 ROMA 3.70
## 4 BOHEMIAN RHAPSODY 3.50
## 5 VICE 3.34
## 6 THE FAVOURITE 3.20
## 7 A STAR IS BORN 3.10
## 8 BLACKkKLANSMAN 2.90#Query for getting Propotions of Rating
Propotions = dbConnect(MySQL(), user='root', password='mohamed', dbname='movie', host='localhost')
Propotions_List <- dbSendQuery(Propotions, "select m.MovieName, RatingCategory,GivenRating from movie.Oscars2019 m inner join movie.MovieRating r on r.MovieId = m.MovieId")## Warning in .local(conn, statement, ...): Decimal MySQL column 2 imported as
## numericPropotions_Rating <- fetch(Propotions_List)
Propotions_Rating## MovieName RatingCategory GivenRating
## 1 BLACK PANTHER Family 4.0
## 2 BLACK PANTHER Friend 4.0
## 3 BLACK PANTHER Kids 5.0
## 4 BLACK PANTHER OnlineCritics 3.5
## 5 BLACK PANTHER GeneralAudience 4.0
## 6 BLACKkKLANSMAN Family 3.0
## 7 BLACKkKLANSMAN Friend 3.0
## 8 BLACKkKLANSMAN Kids 2.0
## 9 BLACKkKLANSMAN OnlineCritics 3.5
## 10 BLACKkKLANSMAN GeneralAudience 3.0
## 11 BOHEMIAN RHAPSODY Family 4.0
## 12 BOHEMIAN RHAPSODY Friend 4.0
## 13 BOHEMIAN RHAPSODY Kids 2.5
## 14 BOHEMIAN RHAPSODY OnlineCritics 3.5
## 15 BOHEMIAN RHAPSODY GeneralAudience 3.5
## 16 THE FAVOURITE Family 3.5
## 17 THE FAVOURITE Friend 4.0
## 18 THE FAVOURITE Kids 2.0
## 19 THE FAVOURITE OnlineCritics 3.5
## 20 THE FAVOURITE GeneralAudience 3.0
## 21 GREEN BOOK Family 4.0
## 22 GREEN BOOK Friend 4.0
## 23 GREEN BOOK Kids 5.0
## 24 GREEN BOOK OnlineCritics 3.5
## 25 GREEN BOOK GeneralAudience 3.0
## 26 ROMA Family 3.5
## 27 ROMA Friend 4.5
## 28 ROMA Kids 3.0
## 29 ROMA OnlineCritics 3.5
## 30 ROMA GeneralAudience 4.0
## 31 A STAR IS BORN Family 4.0
## 32 A STAR IS BORN Friend 3.5
## 33 A STAR IS BORN Kids 2.0
## 34 A STAR IS BORN OnlineCritics 3.0
## 35 A STAR IS BORN GeneralAudience 3.0
## 36 VICE Family 3.2
## 37 VICE Friend 4.0
## 38 VICE Kids 2.0
## 39 VICE OnlineCritics 4.0
## 40 VICE GeneralAudience 3.5#Library for Plotting graphs
library(ggplot2)
ggplot(data=Rating_List, aes(x=MovieName, y=AvgRating, fill=MovieName)) + geom_bar(stat="identity") + coord_flip() + geom_text(aes(label=AvgRating), vjust=0) + ggtitle("Movie Rating - Oscars 2019") + theme(plot.title = element_text(hjust = 0.15))
#Propotion of Rating Category
ggplot(Propotions_Rating, aes(fill=RatingCategory, y=GivenRating, x=MovieName)) +
geom_bar( stat="identity", position="fill") + geom_bar( stat="identity") + coord_flip() + ggtitle("Propotion of Movie Rating Category") +
theme(plot.title = element_text(hjust = 0.15))
#Disconnect from database
dbDisconnect(movie)## Warning: Closing open result sets## [1] TRUEConclusion
The result shows that BLACK PANTHER Movie got highest Average Rating among 5 different groups, it got majority of Ratings from Kids and Family