This assignment 2A - SQL & R related to movie ratings, here movie ratings are collected from five people for six selected movies. Every person will rate the movies based on have seen, scale from 1 to 5. If any participant has not seen a movie, rating will be treated as missing rather than assigning an arbitrary value. So I will create a database name “MovieRatings” using GUI pgAdmin 4 from PostgreSQL and will store all collected data into relevant three tables:
After creating and populating the database, I will use SQL queries to join the tables and retrieve the movie rating data. And then connect to PostgreSQL database to R and load the query results into a dataframe.
For Data Analysis part, I will inspect and clean the data, address
any missing ratings through R language and use
dplyr package to calculate simple summaries - such as the
number of ratings and average rating for each movie.
INSERT INTO users (name)
VALUES
('Rudolf Schenker'),
('John Poli'),
('Robert Burnham'),
('Tracy Harrington'),
('Nancy Accorsi');
INSERT INTO movies (title)
VALUES
('Jason Bourne'),
('Pirates of the Caribbean'),
('Patriot'),
('Italian Job'),
('Robinhood & Sherwood Jungle'),
('Godzilla');I collected and organized all relevant movie ratings data in Google Sheets, leaving cells blank when a participant had not seen or rated a movie. Then transferred all those data into PostgreSQL using three related tables for users, movies, and ratings. Each available rating was inserted into the ratings table using the corresponding user and movie IDs. Missing ratings were not inserted into the ratings table.
INSERT INTO ratings (user_id, movie_id, rating)
VALUES
(1, 1, 4),
(1, 2, 2),
(1, 3, 3),
(1, 4, 5),
(1, 5, 4),
(1, 6, 1),
(2, 1, 4),
(2, 2, 4),
(2, 4, 4),
(2, 5, 2),
(3, 1, 1),
(3, 2, 5),
(3, 3, 2),
(3, 5, 4),
(3, 6, 4),
(4, 1, 5),
(4, 3, 5),
(4, 4, 5),
(4, 5, 4),
(4, 6, 5),
(5, 1, 3),
(5, 2, 3),
(5, 3, 2),
(5, 4, 2),
(5, 5, 3),
(5, 6, 5);The three tables were then joined using the user and movie IDs to produce a readable dataset containing each participant, movie, and corresponding rating.
SELECT
users.name,
movies.title,
ratings.rating
FROM ratings
JOIN users
ON ratings.user_id = users.user_id
JOIN movies
ON ratings.movie_id = movies.movie_id;The PostgreSQL database was connected directly to R using the
DBI and RPostgres packages.
movie_ratings <- dbGetQuery(con, "
SELECT
users.name,
movies.title,
ratings.rating
FROM users
CROSS JOIN movies
LEFT JOIN ratings
ON users.user_id = ratings.user_id
AND movies.movie_id = ratings.movie_id
")
movie_ratings## name title rating
## 1 Rudolf Schenker Jason Bourne 4
## 2 Rudolf Schenker Pirates of the Caribbean 2
## 3 Rudolf Schenker Patriot 3
## 4 Rudolf Schenker Italian Job 5
## 5 Rudolf Schenker Robinhood & Sherwood Jungle 4
## 6 Rudolf Schenker Godzilla 1
## 7 John Poli Jason Bourne 4
## 8 John Poli Pirates of the Caribbean 4
## 9 John Poli Patriot NA
## 10 John Poli Italian Job 4
## 11 John Poli Robinhood & Sherwood Jungle 2
## 12 John Poli Godzilla NA
## 13 Robert Burnham Jason Bourne 1
## 14 Robert Burnham Pirates of the Caribbean 5
## 15 Robert Burnham Patriot 2
## 16 Robert Burnham Italian Job NA
## 17 Robert Burnham Robinhood & Sherwood Jungle 4
## 18 Robert Burnham Godzilla 4
## 19 Tracy Harrington Jason Bourne 5
## 20 Tracy Harrington Pirates of the Caribbean NA
## 21 Tracy Harrington Patriot 5
## 22 Tracy Harrington Italian Job 5
## 23 Tracy Harrington Robinhood & Sherwood Jungle 4
## 24 Tracy Harrington Godzilla 5
## 25 Nancy Accorsi Jason Bourne 3
## 26 Nancy Accorsi Pirates of the Caribbean 3
## 27 Nancy Accorsi Patriot 2
## 28 Nancy Accorsi Italian Job 2
## 29 Nancy Accorsi Robinhood & Sherwood Jungle 3
## 30 Nancy Accorsi Godzilla 5At first, I inspect the data frame and checked for missing ratings. There are four missing ratings in the dataset. These missing values represent movies that participants did not rate. Rather than replacing the missing values with an artificial rating, I will exclude them when calculating summary statistics. This prevents unrated movies from affecting the average ratings.
head(movie_ratings)## name title rating
## 1 Rudolf Schenker Jason Bourne 4
## 2 Rudolf Schenker Pirates of the Caribbean 2
## 3 Rudolf Schenker Patriot 3
## 4 Rudolf Schenker Italian Job 5
## 5 Rudolf Schenker Robinhood & Sherwood Jungle 4
## 6 Rudolf Schenker Godzilla 1str(movie_ratings)## 'data.frame': 30 obs. of 3 variables:
## $ name : chr "Rudolf Schenker" "Rudolf Schenker" "Rudolf Schenker" "Rudolf Schenker" ...
## $ title : chr "Jason Bourne" "Pirates of the Caribbean" "Patriot" "Italian Job" ...
## $ rating: int 4 2 3 5 4 1 4 4 NA 4 ...sum(is.na(movie_ratings$rating))## [1] 4movie_summary <- movie_ratings %>%
group_by(title) %>%
summarise(
number_of_ratings = sum(!is.na(rating)),
average_rating = mean(rating, na.rm = TRUE)
) %>%
arrange(desc(average_rating))
movie_summary## # A tibble: 6 × 3
## title number_of_ratings average_rating
## <chr> <int> <dbl>
## 1 Italian Job 4 4
## 2 Godzilla 4 3.75
## 3 Pirates of the Caribbean 4 3.5
## 4 Jason Bourne 5 3.4
## 5 Robinhood & Sherwood Jungle 5 3.4
## 6 Patriot 4 3The results show that “Italian Job” had the highest average rating at 4.00, while “Patriot” had the lowest average rating at 3.00. “Jason Bourne” and “Robinhood & Sherwood Jungle” received ratings from all five participants, while the remaining movies each received four ratings. Missing ratings were excluded from the calculation of the averages.
Database connection close -
dbDisconnect(con)