homework-week2

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 SQL database. Load the information into an R dataframe.

conexion <- dbConnect(MySQL(),user="root",password="root", 
                      dbname="movies", host = "127.0.0.1", port=3306)

Tables of Movies Database

dbListTables(conexion)

## [1] "movies"  "ratings"

_ Load data into the dataframe

result_set <- dbSendQuery(conexion,"select movie_name, movie_rating from movies m inner join ratings s on s.movie_id = m.movie_id ")

movies_dt <- fetch(result_set)

movies_dt

##                 movie_name movie_rating
## 1                  Aquaman            5
## 2                  Aquaman            4
## 3                  Aquaman            3
## 4                  Aquaman            3
## 5                  Aquaman            4
## 6                  Aquaman            2
## 7            Black Panther            3
## 8            Black Panther            4
## 9            Black Panther            1
## 10           Black Panther            3
## 11           Black Panther            4
## 12           Black Panther            2
## 13 Advengers: Infinity War            5
## 14 Advengers: Infinity War            4
## 15 Advengers: Infinity War            3
## 16 Advengers: Infinity War            3
## 17 Advengers: Infinity War            4
## 18 Advengers: Infinity War            2
## 19           A Quiet Place            4
## 20           A Quiet Place            1
## 21           A Quiet Place            3
## 22           A Quiet Place            3
## 23           A Quiet Place            1
## 24           A Quiet Place            2
## 25                 The Num            5
## 26                 The Num            4
## 27                 The Num            3
## 28                 The Num            3
## 29                 The Num            2
## 30                 The Num            2
## 31                 The Meg            3
## 32                 The Meg            2
## 33                 The Meg            1
## 34                 The Meg            1
## 35                 The Meg            4
## 36                 The Meg            2

Now lets get the average rating for movies

result_set <- dbSendQuery(conexion,"select movie_name, AVG(movie_rating) as 'average_rating' from movies m inner join ratings s on s.movie_id = m.movie_id group by movie_name")

movies_dt <- fetch(result_set)

movies_dt

##                movie_name average_rating
## 1           A Quiet Place         2.3333
## 2 Advengers: Infinity War         3.5000
## 3                 Aquaman         3.5000
## 4           Black Panther         2.8333
## 5                 The Meg         2.1667
## 6                 The Num         3.1667