Load Data from MySQL

The below code chunk will retrieve data in MySQL from table ratings. Please not that after fetching data, do close the database connection.

res <- dbSendQuery(mydb, "SELECT * FROM ratings")
mRatings <- fetch(res, n=-1)
# Disconnect from the database
dbDisconnect(mydb)
## [1] TRUE

View Data

Lets view data that we have collected from five distinct users. The table has five columns: CriticName : Name of the Viewer, MovieName: Movie Title, Year: Year of Release. Genre: Genre of the movie, Ratings: Ratings given by the Viewers on the scale of 1 to 5.

# View Data
knitr::kable(mRatings)
CriticName MovieName Year Genre Rating
Ann Doe Baar Baar Dekho 2016 Romance 4
Ann Doe Bad Moms 2016 Comedy 4
Ann Doe Ben-Hur 2016 Action 3
Ann Doe Ghostbusters 2016 Comedy 3
Ann Doe Jason Bourne 2016 Thriller 4
Ann Doe Morgan 2016 Horror 2
Jane Doe Baar Baar Dekho 2016 Romance 2
Jane Doe Bad Moms 2016 Comedy 5
Jane Doe Ben-Hur 2016 Action 1
Jane Doe Ghostbusters 2016 Comedy 2
Jane Doe Jason Bourne 2016 Thriller 3
Jane Doe Morgan 2016 Horror 1
John Doe Baar Baar Dekho 2016 Romance 3
John Doe Bad Moms 2016 Comedy 2
John Doe Ben-Hur 2016 Action 3
John Doe Ghostbusters 2016 Comedy 2
John Doe Jason Bourne 2016 Thriller 5
John Doe Morgan 2016 Horror 1
Mike Doe Baar Baar Dekho 2016 Romance 4
Mike Doe Bad Moms 2016 Comedy 3
Mike Doe Ben-Hur 2016 Action 2
Mike Doe Ghostbusters 2016 Comedy 2
Mike Doe Jason Bourne 2016 Thriller 4
Mike Doe Morgan 2016 Horror 1
Neil Doe Baar Baar Dekho 2016 Romance 3
Neil Doe Bad Moms 2016 Comedy 3
Neil Doe Ben-Hur 2016 Action 4
Neil Doe Ghostbusters 2016 Comedy 2
Neil Doe Jason Bourne 2016 Thriller 4
Neil Doe Morgan 2016 Horror 4

Get Movie Names

Below are the name of the movies, that viewer has rated

MovieName

Baar Baar Dekho Bad Moms
Ben-Hur
Ghostbusters
Jason Bourne
Morgan

Mean Ratings

We have taken the mean of the ratings given by viewer for each movie and shown below

mNameRat1 <- bind_rows(movieList1)
colnames(mNameRat1) <- c("Title", "Rating")
knitr::kable(mNameRat1)
Title Rating
Baar Baar Dekho 3.2
Bad Moms 3.4
Ben-Hur 2.6
Ghostbusters 2.2
Jason Bourne 4.0
Morgan 1.8

Bar Plot

Lets create a bar plot of the captured data and see which movie has highest rating. As seen in the below bar plot, movie Jason Bourne has highest rating and movie Morgan has lowest.

p <- plot_ly(
  data = mNameRat1,
  x = Title,
  y = Rating,
  name = "Viewer Ratings",
  type = "bar") 
p

Additional Information

Our data is very limited to the ratings given by the viewers. Lets bring other information like cast, IMDB Ratings & Director for each movie. To retrieve this information we can leverage various APIs available on internet. For the below information I have used http://www.omdbapi.com/

mNameRat2 <- bind_rows(movieList2)
colnames(mNameRat2) <- c("Title", "Viewer Rating", "Rated", "Released", "Director","Cast","IMDB Rating","Poster")
knitr::kable(mNameRat2)
Title Viewer Rating Rated Released Director Cast IMDB Rating Poster
Baar Baar Dekho 3.2 N/A 09 Sep 2016 Nitya Mehra Sidharth Malhotra, Katrina Kaif, Sayani Gupta, Rajit Kapoor 7.7
Bad Moms 3.4 R 29 Jul 2016 Jon Lucas, Scott Moore Mila Kunis, Kathryn Hahn, Kristen Bell, Christina Applegate 6.7
Ben-Hur 2.6 PG-13 19 Aug 2016 Timur Bekmambetov Jack Huston, Toby Kebbell, Rodrigo Santoro, Nazanin Boniadi 5.7
Ghostbusters 2.2 PG-13 15 Jul 2016 Paul Feig Zach Woods, Kristen Wiig, Ed Begley Jr., Charles Dance 5.5
Jason Bourne 4.0 PG-13 29 Jul 2016 Paul Greengrass Matt Damon, Tommy Lee Jones, Alicia Vikander, Vincent Cassel 7.0
Morgan 1.8 R 02 Sep 2016 Luke Scott Kate Mara, Rose Leslie, Jennifer Jason Leigh, Anya Taylor-Joy N/A