Movie_Survey

libraries

There are few libraries that is to be installed

I used RMariaDB package to connect to MySQL

## install.packages("RMariaDB")
library(RMariaDB)
library("RMySQL")
## Loading required package: DBI
library("DT")
library("knitr")
library("reshape2")
library("tidyr")
## 
## Attaching package: 'tidyr'
## The following object is masked from 'package:reshape2':
## 
##     smiths
library(ggplot2)

Movie Survey

I created a google form survey to collect movie ratings from 5 of my friends

knitr::include_graphics("C:/Users/Admin/Documents/data 607/assignment_2/image.jpg")

Connetion to MySQL Database

  1. Add a user to connect to the database
  2. Create an R script that connects to the database

Connecting to the database with a password

localuserpassword <- "data607@19"
movieDb <- dbConnect(RMariaDB::MariaDB(), user='data607', password=localuserpassword, dbname='movie_rating', host='localhost')

List tables in movieDb and display table movie_survey

dbListTables(movieDb)
## [1] "movie_survey"
movieRatings <- dbGetQuery( movieDb, "select
Name, Movie_Name,Ratings from movie_survey" )
datatable(movieRatings)

Analyzing the datatable

Aggregate of the movie ratings

movieRatingsAgg <- dcast( movieRatings, Movie_Name~Ratings)
## Using Ratings as value column: use value.var to override.
## Aggregation function missing: defaulting to length
kable( movieRatingsAgg )
Movie_Name Excellent Good Very Good
Finding Nemo 4 1 0
Frozen 1 0 4
Shrek 1 2 2
The Incredibles 2 1 2
The Jungle Book 0 1 4
Toy Story 1 2 1

Maximum no. of ratings a movie got

most_rated <- dbGetQuery(movieDb, "select Movie_Name, count(Ratings)
                 from movie_survey")
most_rated
##        Movie_Name count(Ratings)
## 1 The Incredibles             29

Creating a bar plot of the Movie rating

ggplot(movieRatings, aes(movieRatings$Movie_Name, fill = Ratings)) +
geom_bar(stat = "count")

Close the connection

dbDisconnect(movieDb)