library(DBI)
library(RSQLite)
library(dplyr)
library(tidyr)
library(ggplot2)Week 2A SQL and R: Movie Ratings
DATA 607 — Data Acquisition & Management
M.S. in Data Science | CUNY School of Professional Studies
Student: Sarah Abdelrahman
Instructor: Professor Darwin Gomez
Introduction
The purpose of this assignment is to collect movie-rating data, store it in a relational SQL database, and analyze the data in R.
Six movies were selected, and at least five participants were asked to rate the movies they had seen using a scale from 1 to 5.
Movies that a participant had not seen were recorded as missing values.
Setup
Create SQLite Database
con <- dbConnect(
RSQLite::SQLite(),
"movie_ratings.db"
)Create Tables
The database uses three normalized tables:
usersmoviesratings
The ratings table acts as a junction table connecting users and movies.
dbExecute(con, "DROP TABLE IF EXISTS ratings;")[1] 0dbExecute(con, "DROP TABLE IF EXISTS movies;")[1] 0dbExecute(con, "DROP TABLE IF EXISTS users;")[1] 0dbExecute(con, "
CREATE TABLE users (
user_id INTEGER PRIMARY KEY,
name TEXT NOT NULL
);
")[1] 0dbExecute(con, "
CREATE TABLE movies (
movie_id INTEGER PRIMARY KEY,
title TEXT NOT NULL
);
")[1] 0dbExecute(con, "
CREATE TABLE ratings (
user_id INTEGER,
movie_id INTEGER,
rating INTEGER,
PRIMARY KEY (user_id, movie_id),
FOREIGN KEY (user_id) REFERENCES users(user_id),
FOREIGN KEY (movie_id) REFERENCES movies(movie_id),
CHECK (rating BETWEEN 1 AND 5 OR rating IS NULL)
);
")[1] 0Populate Users Table
dbExecute(con, "
INSERT INTO users (user_id, name) VALUES
(1, 'Person 1'),
(2, 'Person 2'),
(3, 'Person 3'),
(4, 'Person 4'),
(5, 'Person 5');
")[1] 5Populate Movies Table
dbExecute(con, "
INSERT INTO movies (movie_id, title) VALUES
(1, 'Inside Out 2'),
(2, 'Deadpool & Wolverine'),
(3, 'Dune: Part Two'),
(4, 'Wicked'),
(5, 'Moana 2'),
(6, 'The Wild Robot');
")[1] 6Populate Ratings Table
NULL represents a movie that the participant did not watch.
dbExecute(con, "
INSERT INTO ratings (user_id, movie_id, rating) VALUES
(1, 1, 5),
(1, 2, 4),
(1, 3, 4),
(1, 4, NULL),
(1, 5, 5),
(1, 6, 4),
(2, 1, 4),
(2, 2, NULL),
(2, 3, 5),
(2, 4, 4),
(2, 5, 3),
(2, 6, NULL),
(3, 1, 5),
(3, 2, 3),
(3, 3, NULL),
(3, 4, 5),
(3, 5, 4),
(3, 6, 5),
(4, 1, 4),
(4, 2, 4),
(4, 3, 5),
(4, 4, NULL),
(4, 5, 4),
(4, 6, 5),
(5, 1, NULL),
(5, 2, 5),
(5, 3, 4),
(5, 4, 4),
(5, 5, 5),
(5, 6, 4);
")[1] 30Query SQL Data
A SQL JOIN is used to combine the three tables.
ratings_df <- dbGetQuery(con, "
SELECT
u.user_id,
u.name,
m.movie_id,
m.title,
r.rating
FROM ratings r
JOIN users u
ON r.user_id = u.user_id
JOIN movies m
ON r.movie_id = m.movie_id
ORDER BY u.user_id, m.movie_id;
")ratings_df user_id name movie_id title rating
1 1 Person 1 1 Inside Out 2 5
2 1 Person 1 2 Deadpool & Wolverine 4
3 1 Person 1 3 Dune: Part Two 4
4 1 Person 1 4 Wicked NA
5 1 Person 1 5 Moana 2 5
6 1 Person 1 6 The Wild Robot 4
7 2 Person 2 1 Inside Out 2 4
8 2 Person 2 2 Deadpool & Wolverine NA
9 2 Person 2 3 Dune: Part Two 5
10 2 Person 2 4 Wicked 4
11 2 Person 2 5 Moana 2 3
12 2 Person 2 6 The Wild Robot NA
13 3 Person 3 1 Inside Out 2 5
14 3 Person 3 2 Deadpool & Wolverine 3
15 3 Person 3 3 Dune: Part Two NA
16 3 Person 3 4 Wicked 5
17 3 Person 3 5 Moana 2 4
18 3 Person 3 6 The Wild Robot 5
19 4 Person 4 1 Inside Out 2 4
20 4 Person 4 2 Deadpool & Wolverine 4
21 4 Person 4 3 Dune: Part Two 5
22 4 Person 4 4 Wicked NA
23 4 Person 4 5 Moana 2 4
24 4 Person 4 6 The Wild Robot 5
25 5 Person 5 1 Inside Out 2 NA
26 5 Person 5 2 Deadpool & Wolverine 5
27 5 Person 5 3 Dune: Part Two 4
28 5 Person 5 4 Wicked 4
29 5 Person 5 5 Moana 2 5
30 5 Person 5 6 The Wild Robot 4Inspect the Data
str(ratings_df)'data.frame': 30 obs. of 5 variables:
$ user_id : int 1 1 1 1 1 1 2 2 2 2 ...
$ name : chr "Person 1" "Person 1" "Person 1" "Person 1" ...
$ movie_id: int 1 2 3 4 5 6 1 2 3 4 ...
$ title : chr "Inside Out 2" "Deadpool & Wolverine" "Dune: Part Two" "Wicked" ...
$ rating : int 5 4 4 NA 5 4 4 NA 5 4 ...summary(ratings_df) user_id name movie_id title rating
Min. :1 Length :30 Min. :1.0 Length :30 Min. :3.000
1st Qu.:2 N.unique : 5 1st Qu.:2.0 N.unique : 6 1st Qu.:4.000
Median :3 N.blank : 0 Median :3.5 N.blank : 0 Median :4.000
Mean :3 Min.nchar: 8 Mean :3.5 Min.nchar: 6 Mean :4.333
3rd Qu.:4 Max.nchar: 8 3rd Qu.:5.0 Max.nchar:20 3rd Qu.:5.000
Max. :5 Max. :6.0 Max. :5.000
NAs :6 Missing Data
Movies that a participant did not watch are stored as SQL NULL values. When loaded into R, these values become NA.
I kept these ratings as missing instead of replacing them with zero because zero is not part of the 1–5 rating scale. Replacing missing values with zero would incorrectly lower the average rating.
ratings_df %>%
summarise(
total_observations = n(),
completed_ratings = sum(!is.na(rating)),
missing_ratings = sum(is.na(rating))
) total_observations completed_ratings missing_ratings
1 30 24 6Ratings Per Movie
movie_summary <- ratings_df %>%
group_by(title) %>%
summarise(
number_of_ratings = sum(!is.na(rating)),
average_rating = mean(rating, na.rm = TRUE),
.groups = "drop"
) %>%
arrange(desc(average_rating))
movie_summary# A tibble: 6 × 3
title number_of_ratings average_rating
<chr> <int> <dbl>
1 Dune: Part Two 4 4.5
2 Inside Out 2 4 4.5
3 The Wild Robot 4 4.5
4 Wicked 3 4.33
5 Moana 2 5 4.2
6 Deadpool & Wolverine 4 4 Ratings Per Participant
user_summary <- ratings_df %>%
group_by(name) %>%
summarise(
movies_rated = sum(!is.na(rating)),
average_rating = mean(rating, na.rm = TRUE),
.groups = "drop"
)
user_summary# A tibble: 5 × 3
name movies_rated average_rating
<chr> <int> <dbl>
1 Person 1 5 4.4
2 Person 2 4 4
3 Person 3 5 4.4
4 Person 4 5 4.4
5 Person 5 5 4.4User-Item Matrix
The data can also be represented as a user-item matrix, where each row represents a participant and each column represents a movie.
rating_matrix <- ratings_df %>%
select(name, title, rating) %>%
pivot_wider(
names_from = title,
values_from = rating
)
rating_matrix# A tibble: 5 × 7
name `Inside Out 2` `Deadpool & Wolverine` `Dune: Part Two` Wicked `Moana 2`
<chr> <int> <int> <int> <int> <int>
1 Perso… 5 4 4 NA 5
2 Perso… 4 NA 5 4 3
3 Perso… 5 3 NA 5 4
4 Perso… 4 4 5 NA 4
5 Perso… NA 5 4 4 5
# ℹ 1 more variable: `The Wild Robot` <int>This format resembles the type of data commonly used as input for collaborative-filtering recommender systems.
Visualization
ggplot(
movie_summary,
aes(
x = reorder(title, average_rating),
y = average_rating
)
) +
geom_col() +
coord_flip() +
ylim(0, 5) +
labs(
title = "Average Movie Ratings",
x = "Movie",
y = "Average Rating"
) +
theme_minimal()
Conclusion
This assignment demonstrated how a small movie-rating dataset can be stored and analyzed using SQL and R.
I created a normalized relational database containing separate users, movies, and ratings tables. SQL joins were used to combine the tables, and the resulting data were loaded directly into R using the DBI and RSQLite packages.
Missing movie ratings were preserved as NULL values in SQL and NA values in R rather than being replaced with zero. This prevented missing responses from incorrectly affecting average ratings.
Finally, I summarized the number and average of ratings by movie and participant, created a user-item matrix, and visualized the average movie ratings.
dbDisconnect(con)