Week 1 Assignment – SQL and R

The Overview:

The goal of this assignment is to create and analyze a survey of six movies and add the data to SQL database where we implement the data from a secure password host to the R markdown. the finding of this report is analyzing the movies and find out which one has the most rating.

I conducted the survey by asking friends and coworkers about the six movies, some of them has seen most of them and some saw only few or the movies. I uploaded the files the Github and you can find the data here

And you can check the files on Github

Loading packages

library(RMySQL)
library(RCurl);
library(sqldf)

## Warning in doTryCatch(return(expr), name, parentenv, handler): unable to load shared object '/Library/Frameworks/R.framework/Resources/modules//R_X11.so':
##   dlopen(/Library/Frameworks/R.framework/Resources/modules//R_X11.so, 6): Library not loaded: /opt/X11/lib/libSM.6.dylib
##   Referenced from: /Library/Frameworks/R.framework/Resources/modules//R_X11.so
##   Reason: image not found

library(ggplot2);

Establish Connection

Connect to MySql local database to pull the tables in the Movies database.

moviescon <- dbConnect(MySQL(),
                 user="root", password= "karim312",
                 dbname="movies", host="localhost")
summary(moviescon)

## <MySQLConnection:0,0>
##   User:   root 
##   Host:   localhost 
##   Dbname: movies 
##   Connection type: Localhost via UNIX socket 
## 
## Results:

Check the tables in the database

I will mainly use the movies database

dbListTables(moviescon);

## [1] "movies" "people"

Create data frame

create data frame and read the table “movies”

moviesdata <- dbReadTable(moviescon, "movies")
moviesdata

##    ID          Movie People Rating
## 1   1       Frozen 2  Alice      5
## 2   1       Frozen 2   Tony      4
## 3   1       Frozen 2    Zen      5
## 4   1       Frozen 2  Majed     NA
## 5   1       Frozen 2   Gary     NA
## 6   2 Murder Mystery  Alice      4
## 7   2 Murder Mystery   Tony      3
## 8   2 Murder Mystery    Zen      2
## 9   2 Murder Mystery  Majed      5
## 10  2 Murder Mystery   Gary      4
## 11  3          Moana  Alice      5
## 12  3          Moana   Tony      3
## 13  3          Moana    Zen      5
## 14  3          Moana  Majed     NA
## 15  3          Moana   Gary     NA
## 16  4          Joker  Alice     NA
## 17  4          Joker   Tony      4
## 18  4          Joker    Zen      5
## 19  4          Joker  Majed      5
## 20  4          Joker   Gary      4
## 21  5   The Big Kick  Alice      3
## 22  5   The Big Kick   Tony     NA
## 23  5   The Big Kick    Zen      1
## 24  5   The Big Kick  Majed      2
## 25  5   The Big Kick   Gary      3
## 26  6        Get out  Alice      4
## 27  6        Get out   Tony      5
## 28  6        Get out    Zen      5
## 29  6        Get out  Majed      3
## 30  6        Get out   Gary      3

Display the data on a bar chart

We start by pulling the average rating for each movie in the table

average <- c(14/3, 18/5, 13/5, 18/4, 9/4, 20/5)

average1 <- as.table(setNames(average, moviesdata))

Here we display the data, we start by changing the axis size to make sure the labels are readable when running the report.

par(mar=c(5,2,2,0))

barplot(average1, main="Movies Rating", xlab="Movie", ylab="Rating", ylim=c(0,6), horiz=FALSE,cex.lab=1 , col= c("lightcyan","mistyrose","beige","yellow","red","green"), names.arg=c("Frozen 2", "MurderMystery", "Moana", "Joker", "The Big Kick", "Get out" ))

Conclusion:

As we see in the barchart, the movies Frozen 2 and Joker got the highest average rating evethough Frozen2 had been watched only by 3 people, while Joker had been watched by all of the people in the rating list. on the other side The Big Sick movie got the worst rating of around only 2 out of 5 and it was watched by 4 people, it seems like they didn’t enjoy it a lot.

Thanks, Karim Hammoud