library("RMySQL")
library("DT")
library("knitr")
library("reshape2")
library("tidyr")

Overview

Assignment

Choose six recent popular movies. Ask at least five people that you know (friends, family, classmates, imaginary friends) to rate each of these movie that they have seen on a scale of 1 to 5. Take the results (observations) and store them in a mySQL database. Load the information into an R dataframe.

My take on the project

I used a Survey Monkey survey and distributed it to my friends to collect survery results.

I extracted the Survey Monkey data into CSV file. I then loaded the CSV file into MySQL and viewed the data with R

Get the data

conn <- dbConnect(MySQL(), user='rnash', host='localhost', dbname='movies')

movieRatings <- dbGetQuery( conn, "select 
                                respondentid, movie, rating from movie_ratings" ) 
datatable ( movieRatings )

Aggregate the data

movieRatingsAgg <- dcast ( movieRatings, movie~rating )
movieRatingsAgg <- movieRatingsAgg[ , c("movie", "Ridiculous", "Awful", "Bad", "ehh", "Average", "Good", "Very Good", "Excellent", "Amazing")]
kable( movieRatingsAgg )
movie Ridiculous Awful Bad ehh Average Good Very Good Excellent Amazing
Captain America: Civil War 1 0 1 2 4 1 0 3 0
Deadpool 0 0 1 1 2 0 4 2 2
Finding Dory 0 0 1 0 1 4 4 1 1
The Jungle Book (2016) 0 1 0 4 2 3 1 1 0
The Secret Life of Pets 0 0 0 2 3 3 2 2 0
Zootopia 0 0 1 0 1 4 3 1 2

Shape into a Likert type data frame

movieRatingsLikert <- spread( movieRatings, movie, rating )
kable  ( movieRatingsLikert , )
respondentid Captain America: Civil War Deadpool Finding Dory The Jungle Book (2016) The Secret Life of Pets Zootopia
4954935988 Good ehh Bad Average Average Bad
4954981646 Average Very Good Excellent Good Average Very Good
4954983924 Excellent Very Good Very Good Very Good ehh Average
4954984464 Average Very Good Good Good ehh Good
4955009009 Excellent Amazing Very Good Excellent Very Good Excellent
4955501675 Ridiculous Average Very Good ehh Good Good
4955668552 Average Average Amazing Average Excellent Amazing
4955672003 Average Excellent Very Good ehh Good Amazing
4955726106 Excellent Excellent Good Awful Good Good
4955727441 Bad Amazing Good ehh Average Very Good
4957138056 ehh Very Good Good Good Excellent Very Good
4957189616 ehh Bad Average ehh Very Good Good