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 ==== done
5. Calculate user adjustments =====done
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

        name                      title rating
1    Areeb Q                The Odyssey      5
2    Areeb Q                Toy Story 5      3
3    Areeb Q              The Housemaid     NA
4    Areeb Q                    Michael      4
5    Areeb Q Spider-man, Brand New Day       5
6    Areeb Q                      Anora      5
7   Zainab N                The Odyssey      5
8   Zainab N                Toy Story 5      3
9   Zainab N              The Housemaid     NA
10  Zainab N                    Michael      5
11  Zainab N Spider-man, Brand New Day       5
12  Zainab N                      Anora      4
13   Nasir H                The Odyssey      5
14   Nasir H                Toy Story 5      3
15   Nasir H              The Housemaid      5
16   Nasir H                    Michael      5
17   Nasir H Spider-man, Brand New Day       3
18   Nasir H                      Anora      5
19   Mubin E                The Odyssey      5
20   Mubin E                Toy Story 5      3
21   Mubin E              The Housemaid      5
22   Mubin E                    Michael      4
23   Mubin E Spider-man, Brand New Day       4
24   Mubin E                      Anora      5
25 Shameer Q                The Odyssey      1
26 Shameer Q                Toy Story 5      1
27 Shameer Q              The Housemaid     NA
28 Shameer Q                    Michael      1
29 Shameer Q Spider-man, Brand New Day       1
30 Shameer Q                      Anora      1

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

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  Zainab N       4.400000
2   Mubin E       4.333333
3   Areeb Q       4.400000
4 Shameer Q       1.000000
5   Nasir H       4.333333

movie_summary

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

OverallAverage <- mean(movieratings$rating, na.rm = TRUE)

result_user<- userrating%>% select(name, avguserratings)%>%mutate(diff=avguserratings-OverallAverage)
result_movie<-movie_summary %>% 
              select(title, average_rating)%>% 
              mutate(moviedifftoavg = average_rating-OverallAverage)
df<-movie_summary%>%select(title, average_rating)

GlobalBaselineEstimate <- OverallAverage +
                                         result_movie %>%
                                         filter(title == "The Housemaid") %>%
                                         pull(moviedifftoavg) +
                                         result_user %>%
                                         filter(name == "Shameer Q") %>%
                                         pull(diff)

GlobalBaselineEstimate

[1] 2.259259

I would predict that Shameer Q would rate The Housemaid 2.259259 (he’s a tough critic).

#Pulling all the movies, shameer has NA as
recommendations <- movieratings %>%
  filter(name == "Shameer Q", is.na(rating))


recommendations <- recommendations %>%
  left_join(result_movie %>% select(title, moviedifftoavg),
    by = "title"
  ) %>%
  mutate(
    prediction = OverallAverage +
                 moviedifftoavg +
                 result_user %>%
                   filter(name == "Shameer Q") %>%
                   pull(diff)
  )

recommendations

       name         title rating moviedifftoavg prediction
1 Shameer Q The Housemaid     NA       1.259259   2.259259

Compare to other movies that Shameer has seen and rated, I predict he’d rate this one higher than others as this movie has been rated highly by the people that have seen it. I predict he’d rate it 2.259 (which is a lot higher than he’s rated any movies he’s seen).

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.