SQL and R – Movie Ratings

Approach: SQL and R – Movie Ratings

Introduction

For this project, I collected ratings from 10 participants which were my friends for six selected movies. Participants rated each movie they had seen on a scale from 1 to 5. The collected ratings were stored in a PostgreSQL relational database and will be loaded into R for data cleaning, exploration, and summary analysis.

The six movies selected for this project are The Wolf of Wall Street, The Godfather, Spider-Man, Home Alone, Titanic, and Harry Potter.

Research Question

What are the average ratings given by participants for each of the six selected movies?

1. Load Required R Packages

library(tidyverse)

── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
✔ dplyr     1.2.1     ✔ readr     2.2.0
✔ forcats   1.0.1     ✔ stringr   1.6.0
✔ ggplot2   4.0.3     ✔ tibble    3.3.1
✔ lubridate 1.9.5     ✔ tidyr     1.3.2
✔ purrr     1.2.2     
── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
✖ dplyr::filter() masks stats::filter()
✖ dplyr::lag()    masks stats::lag()
ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors

library(DBI)
library(RPostgres)

con <- dbConnect(
  RPostgres::Postgres(),
  dbname = "movie_ratings",
  host = "localhost",
  port = 5433,
  user = "postgres",
  password = Sys.getenv("PGPASSWORD")
)

dbListTables(con)

[1] "movies"  "ratings" "users"  

2. Load Data from PostgreSQL

ratings_data <- dbGetQuery(
  con,
  "SELECT * FROM ratings"
)

movies_data <- dbGetQuery(
  con,
  "SELECT * FROM movies"
)

users_data <- dbGetQuery(
  con,
  "SELECT * FROM users"
)

ratings_data

   user_id movie_id rating
1      U01      M01      2
2      U01      M02      5
3      U01      M03      3
4      U01      M04      3
5      U01      M05      4
6      U01      M06      2
7      U02      M01      5
8      U02      M02      5
9      U02      M03      5
10     U02      M05      4
11     U02      M06      4
12     U03      M01      5
13     U03      M02      5
14     U03      M03      4
15     U03      M04      5
16     U03      M05      5
17     U03      M06      5
18     U04      M03      5
19     U04      M04      3
20     U04      M05      5
21     U04      M06      5
22     U06      M01      4
23     U06      M03      3
24     U06      M04      5
25     U06      M05      3
26     U07      M01      5
27     U07      M02      4
28     U07      M03      4
29     U07      M04      5
30     U07      M05      5
31     U07      M06      3
32     U08      M02      5
33     U08      M03      4
34     U08      M05      4
35     U09      M01      4
36     U09      M02      4
37     U09      M03      4
38     U09      M04      4
39     U09      M05      4
40     U09      M06      4
41     U10      M01      4
42     U10      M02      5
43     U10      M03      3
44     U10      M04      3
45     U10      M05      5
46     U10      M06      3

movies_data

  movie_id                   title
1      M01 The Wolf of Wall Street
2      M02           The Godfather
3      M03              Spider-Man
4      M04              Home Alone
5      M05                 Titanic
6      M06            Harry Potter

users_data

   user_id
1      U01
2      U02
3      U03
4      U04
5      U05
6      U06
7      U07
8      U08
9      U09
10     U10

3. Combine Ratings with Movie Information

ratings_full <- ratings_data %>%
  left_join(movies_data, by = "movie_id")

head(ratings_full)

  user_id movie_id rating                   title
1     U01      M01      2 The Wolf of Wall Street
2     U01      M02      5           The Godfather
3     U01      M03      3              Spider-Man
4     U01      M04      3              Home Alone
5     U01      M05      4                 Titanic
6     U01      M06      2            Harry Potter

4. Check the Data

str(ratings_full)

'data.frame':   46 obs. of  4 variables:
 $ user_id : chr  "U01" "U01" "U01" "U01" ...
 $ movie_id: chr  "M01" "M02" "M03" "M04" ...
 $ rating  : int  2 5 3 3 4 2 5 5 5 4 ...
 $ title   : chr  "The Wolf of Wall Street" "The Godfather" "Spider-Man" "Home Alone" ...

summary(ratings_full)

   user_id            movie_id             rating        title          
 Length:46          Length:46          Min.   :2.00   Length:46         
 Class :character   Class :character   1st Qu.:4.00   Class :character  
 Mode  :character   Mode  :character   Median :4.00   Mode  :character  
                                       Mean   :4.13                     
                                       3rd Qu.:5.00                     
                                       Max.   :5.00                     

n_distinct(ratings_full$user_id)

[1] 9

n_distinct(ratings_full$movie_id)

[1] 6

sort(unique(ratings_full$rating))

[1] 2 3 4 5

ratings_full %>%
  filter(rating < 1| rating > 5)

[1] user_id  movie_id rating   title   
<0 rows> (or 0-length row.names)

5. Handling Missing Ratings

ratings_full %>%
  count(title, name="number_of_ratings")

                    title number_of_ratings
1            Harry Potter                 7
2              Home Alone                 7
3              Spider-Man                 9
4           The Godfather                 7
5 The Wolf of Wall Street                 7
6                 Titanic                 9

Missing ratings were excluded from the calculation of the average rating.

movie_summary <- ratings_full %>%
  group_by(title) %>%
  summarise(
    number_of_ratings = n(),
    average_rating = mean(rating, na.rm = TRUE)
  )

movie_summary

# A tibble: 6 × 3
  title                   number_of_ratings average_rating
  <chr>                               <int>          <dbl>
1 Harry Potter                            7           3.71
2 Home Alone                              7           4   
3 Spider-Man                              9           3.89
4 The Godfather                           7           4.71
5 The Wolf of Wall Street                 7           4.14
6 Titanic                                 9           4.33

ggplot(movie_summary, aes(x = reorder(title, average_rating),
                          y = average_rating)) +
  geom_col() +
  coord_flip() +
  labs(
    title = "Average Ratings by Movies",
    x = "Movies",
    y = "Average Rating"
  )

6. SQL Database Creation and Population

The movie-rating data were stored in a PostgreSQL database using three related tables: users, movies, and ratings. The ratings table connects users and movies and contains ratings from 1 to 5.

6.1 Users Table

The users table stores the IDs of the participants.

CREATE TABLE users (
    user_id VARCHAR(10) PRIMARY KEY
);

INSERT INTO users (user_id)
VALUES
('U01'),
('U02'),
('U03'),
('U04'),
('U05'),
('U06'),
('U07'),
('U08'),
('U09'),
('U10');

6.2 Movies Table

The movies table stores the movie IDs and movie titles.

CREATE TABLE movies (
    movie_id VARCHAR(10) PRIMARY KEY,
    title VARCHAR(100) NOT NULL
);

INSERT INTO movies (movie_id, title)
VALUES
('M01', 'The Wolf of Wall Street'),
('M02', 'The Godfather'),
('M03', 'Spider-Man'),
('M04', 'Home Alone'),
('M05', 'Titanic'),
('M06', 'Harry Potter');

6.3 Ratings Table

The ratings table stores the rating given by each participant for each movie.

CREATE TABLE ratings (
    user_id VARCHAR(10),
    movie_id VARCHAR(10),
    rating INTEGER CHECK (rating BETWEEN 1 AND 5),
    PRIMARY KEY (user_id, movie_id),
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (movie_id) REFERENCES movies(movie_id)
);

INSERT INTO ratings (user_id, movie_id, rating)
VALUES
('U01', 'M01', 2),
('U01', 'M02', 5),
('U01', 'M03', 3),
('U01', 'M04', 3),
('U01', 'M05', 4),
('U01', 'M06', 2),
('U02', 'M01', 5),
('U02', 'M02', 5),
('U02', 'M03', 5),
('U02', 'M05', 4),
('U02', 'M06', 4),
('U03', 'M01', 5),
('U03', 'M02', 5),
('U03', 'M03', 4),
('U03', 'M04', 5),
('U03', 'M05', 5),
('U03', 'M06', 5),
('U04', 'M03', 5),
('U04', 'M04', 3),
('U04', 'M05', 5),
('U04', 'M06', 5),
('U06', 'M01', 4),
('U06', 'M03', 3),
('U06', 'M04', 5),
('U06', 'M05', 3),
('U07', 'M01', 5),
('U07', 'M02', 4),
('U07', 'M03', 4),
('U07', 'M04', 5),
('U07', 'M05', 5),
('U07', 'M06', 3),
('U08', 'M02', 5),
('U08', 'M03', 4),
('U08', 'M05', 4),
('U09', 'M01', 4),
('U09', 'M02', 4),
('U09', 'M03', 4),
('U09', 'M04', 4),
('U09', 'M05', 4),
('U09', 'M06', 4),
('U10', 'M01', 4),
('U10', 'M02', 5),
('U10', 'M03', 3),
('U10', 'M04', 3),
('U10', 'M05', 5),
('U10', 'M06', 3);

7. Results and Interpretation

The analysis shows differences in the average ratings given to the six movies. The Godfather received the highest average rating of 4.71 based on 7 ratings. Titanic had the second-highest average rating of 4.33 based on 9 ratings, followed by The Wolf of Wall Street with an average rating of 4.14 based on 7 ratings. Home Alone received an average rating of 4.00 from 7 ratings. Spider-Man had an average rating of 3.89 based on 9 ratings, while Harry Potter had the lowest average rating of 3.71 based on 7 ratings.

The number of ratings varied across the movies because participants did not provide a rating for every movie. The analysis used the ratings that were actually provided. Missing ratings were not treated as zero because a missing rating does not indicate that a participant disliked the movie.

8. Conclusion

Overall, the analysis demonstrates how movie-rating data can be stored in a PostgreSQL database, retrieved and analyzed in R, and presented visually. The results show that the average ratings varied among the six movies. This project also demonstrates the use of SQL tables, relationships between tables, data joining, summary statistics, and data visualization.