Install pgAdmin4 and create database to analyse movies and their ratings.
I inputted data into my database I created in pgAdmin4. I inserted tables for user, movies and ratings.
library(DBI)
library(RPostgres)
library(dplyr)
Attaching package: 'dplyr'The following objects are masked from 'package:stats':
filter, lagThe following objects are masked from 'package:base':
intersect, setdiff, setequal, unionlibrary(tidyr)I connect to the database below. I figured out how to add it and hide my password. I used Gemini to help me figure out how to do that in Rstudio.
con <- dbConnect(
RPostgres::Postgres(),
dbname = "MoviesCCB",
host = "localhost",
port = 5432,
user = "postgres",
password = Sys.getenv("DB_PASSWORD")
)ratings <- dbGetQuery(con, "
SELECT
u.user_name,
m.movie_title,
r.rating_value
FROM Ratings r
JOIN Users u
ON r.user_id = u.user_id
JOIN Movies m
ON r.movie_id = m.movie_id;
")users <- dbReadTable(con, "users")
movies <- dbReadTable(con, "movies")
ratings <- dbReadTable(con, "ratings")dbListTables(con)[1] "movies" "ratings" "users" dbDisconnect(con)users user_id user_name
1 1 Jennifer
2 2 Charles
3 3 Jordi
4 4 Ashley
5 5 Kevinmovies movie_id movie_title
1 1 Spiderman: Brand New Day
2 2 Odyssey
3 3 Devil Wears Prada 2
4 4 The Drama
5 5 Project Hail Maryratings user_id movie_id rating_value
1 1 1 5
2 1 2 4
3 1 3 3
4 1 4 5
5 1 5 4
6 2 1 4
7 2 3 4
8 2 4 3
9 2 5 5
10 3 1 3
11 3 2 4
12 3 3 5
13 3 4 4
14 3 5 3
15 4 1 5
16 4 2 3
17 4 3 4
18 4 5 3
19 5 1 4
20 5 2 5
21 5 3 3
22 5 4 4
23 5 5 4movie_ratings <- ratings %>%
left_join(users, by = "user_id") %>%
left_join(movies, by = "movie_id")movie_ratings user_id movie_id rating_value user_name movie_title
1 1 1 5 Jennifer Spiderman: Brand New Day
2 1 2 4 Jennifer Odyssey
3 1 3 3 Jennifer Devil Wears Prada 2
4 1 4 5 Jennifer The Drama
5 1 5 4 Jennifer Project Hail Mary
6 2 1 4 Charles Spiderman: Brand New Day
7 2 3 4 Charles Devil Wears Prada 2
8 2 4 3 Charles The Drama
9 2 5 5 Charles Project Hail Mary
10 3 1 3 Jordi Spiderman: Brand New Day
11 3 2 4 Jordi Odyssey
12 3 3 5 Jordi Devil Wears Prada 2
13 3 4 4 Jordi The Drama
14 3 5 3 Jordi Project Hail Mary
15 4 1 5 Ashley Spiderman: Brand New Day
16 4 2 3 Ashley Odyssey
17 4 3 4 Ashley Devil Wears Prada 2
18 4 5 3 Ashley Project Hail Mary
19 5 1 4 Kevin Spiderman: Brand New Day
20 5 2 5 Kevin Odyssey
21 5 3 3 Kevin Devil Wears Prada 2
22 5 4 4 Kevin The Drama
23 5 5 4 Kevin Project Hail Marycomplete_ratings <- movie_ratings %>%
complete(
user_name,
movie_title
)complete_ratings %>%
filter(is.na(rating_value))# A tibble: 2 × 5
user_name movie_title user_id movie_id rating_value
<chr> <chr> <int> <int> <int>
1 Ashley The Drama NA NA NA
2 Charles Odyssey NA NA NAmovie_summary <- complete_ratings %>%
group_by(movie_title) %>%
summarise(
average_score = mean(rating_value, na.rm = TRUE),
missing_ratings = sum(is.na(rating_value)),
.groups = "drop"
)movie_summary# A tibble: 5 × 3
movie_title average_score missing_ratings
<chr> <dbl> <int>
1 Devil Wears Prada 2 3.8 0
2 Odyssey 4 1
3 Project Hail Mary 3.8 0
4 Spiderman: Brand New Day 4.2 0
5 The Drama 4 1user_summary <- complete_ratings %>%
group_by(user_name) %>%
summarise(
movies_watched = sum(!is.na(rating_value)),
average_score = mean(rating_value, na.rm = TRUE),
.groups = "drop"
)user_summary# A tibble: 5 × 3
user_name movies_watched average_score
<chr> <int> <dbl>
1 Ashley 4 3.75
2 Charles 4 4
3 Jennifer 5 4.2
4 Jordi 5 3.8
5 Kevin 5 4 From my data, the movie with the highest average rating was Spiderman: Brand New Day. The second highest average rated movie is a tie between The Drama and Odyssey. Spiderman was the highest rated and all 5 friends had seen the movie.