DATA607 ASS2: SQL and R

Introduction

I first created a survey of 6 movies which I have 5 persons responding. After saving the survey in csv, I stored the results in MySQL database.

Approach

I will analyze the rating of each movie to know what is the most rated movie. Then, go through each friend’s rating of individual movies and overall rating of movies. This will help me to know what type of the movie the friend likes and if he or she is a fun of movies. Then, after I will recommend a movie if necessary.

# Load the results from MySQL to R
library(tidyverse)

## -- Attaching packages --------------------------------------------------------------------------------------- tidyverse 1.3.0 --

## v ggplot2 3.3.2     v purrr   0.3.4
## v tibble  3.0.3     v dplyr   1.0.2
## v tidyr   1.1.2     v stringr 1.4.0
## v readr   1.3.1     v forcats 0.5.0

## -- Conflicts ------------------------------------------------------------------------------------------ tidyverse_conflicts() --
## x dplyr::filter() masks stats::filter()
## x dplyr::lag()    masks stats::lag()

library(RMySQL)

## Loading required package: DBI

# Accessing MySQL data base with password stored in a settings file

#rmariadb.settingsfile <- "C:\\data\\credentials.cnf"
#rmariadb.db <- "credentials"
#mydb <- dbConnect(RMariaDB::MariaDB(),default.file = rmariadb.settingsfile,group = rmariadb.db)

mydb <-dbConnect(RMariaDB::MariaDB(), user='root', password='mypassword', dbname ='labs', host='localhost')

# Store the result in a dataframe movies

movies<- dbGetQuery(mydb, "SELECT * FROM movies")

library(tidyverse)
glimpse(movies)

## Rows: 30
## Columns: 3
## $ Movie_title <chr> "American son", "The last thing he wanted", "Spenser co...
## $ Friend_name <chr> "Batila", "Batila", "Batila", "Batila", "Batila", "Bati...
## $ Rating      <int> 5, 4, 5, 3, 4, 5, NA, 4, 3, NA, 4, 5, NA, 5, 4, 4, 5, 5...

Handle missing values

# Replace all missing values (a move not seen by a person) by 0

movies[is.na(movies)] = 0

summary(movies)

##  Movie_title        Friend_name            Rating     
##  Length:30          Length:30          Min.   :0.000  
##  Class :character   Class :character   1st Qu.:4.000  
##  Mode  :character   Mode  :character   Median :4.000  
##                                        Mean   :3.633  
##                                        3rd Qu.:5.000  
##                                        Max.   :5.000

Best Movie by Average rating

movies %>%
  group_by(Movie_title) %>%
  summarise(mean_rating = mean(Rating)) %>%
  arrange(desc(mean_rating))

## `summarise()` ungrouping output (override with `.groups` argument)

## # A tibble: 6 x 2
##   Movie_title              mean_rating
##   <chr>                          <dbl>
## 1 Inside Man Most Wanted           4.8
## 2 Extraction                       4.4
## 3 The last thing he wanted         4.2
## 4 Spenser confidential             3.4
## 5 Six Underground                  3.2
## 6 American son                     1.8

movies %>%
  group_by(Movie_title) %>%
  summarise(mean_rating = mean(Rating)) %>%
  ggplot(., aes(y = Movie_title, x= mean_rating, fill = mean_rating)) + geom_bar(stat = 'identity')

## `summarise()` ungrouping output (override with `.groups` argument)

Individual rating by movie

# Batila's preference

batila_movie <- movies %>%
  filter(Friend_name == "Batila")

ggplot(data = batila_movie, aes( y = Movie_title, x = Rating, fill = Rating)) + geom_bar(stat = 'identity')

# Musengie's preference

musengie_movie <- movies %>%
  filter(Friend_name == "Musengie")

ggplot(data = musengie_movie, aes( y = Movie_title, x = Rating, fill = Rating)) + geom_bar(stat = 'identity')

# Jonathan's preference

jonathan_movie <- movies %>%
  filter(Friend_name == "Jonathan")

ggplot(data = jonathan_movie, aes( y = Movie_title, x = Rating, fill = Rating)) + geom_bar(stat = 'identity')

# Herve's preference

herve_movie <- movies %>%
  filter(Friend_name == "Herve")

ggplot(data = herve_movie, aes( y = Movie_title, x = Rating, fill = Rating)) + geom_bar(stat = 'identity')

MaV’s preference

MaV_movie <- movies %>%
  filter(Friend_name == "MaV")

ggplot(data = MaV_movie, aes( y = Movie_title, x = Rating, fill = Rating)) + geom_bar(stat = 'identity')

## Each friend’s overall rating

movies %>%
  group_by(Friend_name) %>%
  summarise(mean_rating = mean(Rating)) %>%
  ggplot(., aes(y = Friend_name, x= mean_rating, fill = mean_rating)) + geom_bar(stat = 'identity')

## `summarise()` ungrouping output (override with `.groups` argument)

Conclusion & recommendation

I realized that Batila has watched all the ovies and his overall rating for those movies were very high. Nothing I can recommend to him from my list of six movies. Musengie had watched 4 out 6 movies but her rating is kind of the average so I would to recommend a very good movie to her. When it comes to movie, “American son” had an average low rating just because it was watched only by two two people although the friends who’d seen, like it. It is a good option to recommend to others.

library(kableExtra)

## 
## Attaching package: 'kableExtra'

## The following object is masked from 'package:dplyr':
## 
##     group_rows

# Construct a table frame for friends and recommended movies

f <- c("Musengie", "Jonathan", "Herve", "MaV")
m <- c("Six Underground", "American son", "American son", "Spencer confidential")


df <- data.frame(f,m)


colnames(df) <- c("Friend's name", "Recommended Movie")


  
# Movie recomendation table

df %>%
  kbl(caption = "Movie recommendation", align = 'c') %>%
  kable_material(c("striped", "hover")) %>%
  row_spec(0, color = "indigo")

Movie recommendation

Friend’s name	Recommended Movie
Musengie	Six Underground
Jonathan	American son
Herve	American son
MaV	Spencer confidential