Global Baseline Estimate

Author

Mubin

Approach

I’ll use the data that I collected in last week 2A. The formula provided is:

Global Baseline Estimate = Mean Movie Rating + Pitch Perfect 2’s rating relative to average + Param’s rating relative to average

Predicted Ratings = µ + b_u + b_i

I already have µ as average_rating in my code. I’ll calculate Avg User Ratings as

   userrating <- dbGetQuery(
                             MovieRatingConnection,
                             "SELECT users.name, 
                             AVG(ratings.rating) 
                             AS avgUserRatings
                             FROM users 
                             LEFT JOIN ratings 
                             ON users.user_id = ratings.user_id 
                             GROUP BY users.name")

User ratings will help me calculate user bias. Since none of the code is due at this time. My main goal is to 1. Get ratings data into R ==done
2. Calculate each user’s average ===done
3. Calculate each movie’s average ===done
4. Calculate overall average ==== would need to do
5. Calculate user adjustments ===== would need user ratings which I’ve already calculated
6. Calculate movie adjustments ==== I might or might not need it
7. Use those values to predict a missing rating would be fiddling with R to do this.
8. Make a movie recommendation ==>final goal

Previous assignment

library(DBI)
library(RPostgres)
library(dplyr)


Attaching package: 'dplyr'

The following objects are masked from 'package:stats':

    filter, lag

The following objects are masked from 'package:base':

    intersect, setdiff, setequal, union

MovieRatingConnection <- dbConnect( RPostgres::Postgres(), dbname = "data607", host = "localhost", port = 5432, user = "postgres", password = Sys.getenv("PG_PASSWORD") )

movieratings <- dbGetQuery(MovieRatingConnection, "
  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
")

movieratings |>
  group_by(title) |>
  summarise(
    number_of_ratings = sum(!is.na(rating)),
    average_rating = mean(rating, na.rm = TRUE)
  )

# A tibble: 6 × 3
  title                        number_of_ratings average_rating
  <chr>                                    <int>          <dbl>
1 "Anora"                                      5            4.4
2 "Michael"                                    5            4.2
3 "Spider-man, Brand New Day "                 5            4.2
4 "The Housemaid"                              2            5  
5 "The Odyssey"                                5            5  
6 "Toy Story 5"                                5            3

userrating <- dbGetQuery(MovieRatingConnection, "
SELECT
    users.name,
    AVG(ratings.rating) AS avgUserRatings
FROM users
LEFT JOIN ratings
    ON users.user_id = ratings.user_id
GROUP BY users.name")

userrating

       name avguserratings
1 Shameer H       3.600000
2  Zainab N       4.400000
3   Mubin E       4.333333
4   Areeb Q       4.400000
5   Nasir H       4.333333

Challenge:

Running SQL queries and R code still remains my top challenge, and I believe, is the main goal of this assignment. I am getting better but still takes me long time.