The goal of this assignment is to get us familiar with use of SQL, and linking R to SQL. Preliminary work to this was creating a survey of movies for my friends to fill out, that I could then upload into a SQL database to do further work on. This survey contains ratings from 1-5 for all movies with an additional option to rate the movie as “did not see”.
The Code
First I’ll load in tidyverse.
library("tidyverse")
Warning: package 'tidyverse' was built under R version 4.5.3
Warning: package 'ggplot2' was built under R version 4.5.3
── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
✔ dplyr 1.2.0 ✔ 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.1
── 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
Then I want to read the raw .csv again.
Movies <-read.csv("Movies_raw.csv")Movies
Timestamp The.Odyssey..The.Odyssey. The.Odyssey..Dune.2.
1 2026/09/10 7:22:14 AM AST 5
2 2026/09/10 7:26:19 AM AST 5 - Amazing 5 - Amazing
3 2026/09/10 8:43:32 AM AST 2 did not see
4 2026/09/10 8:53:03 AM AST 5 - Amazing 3 - Ok
5 2026/09/10 9:08:15 AM AST 5 - Amazing 3 - Ok
6 2026/09/10 10:21:28 AM AST did not see did not see
7 2026/09/10 10:51:44 AM AST 2 3 - Ok
8 2026/09/10 10:54:53 AM AST 3 - Ok 3 - Ok
9 2026/09/10 10:58:26 AM AST 4
10 2026/09/10 11:01:48 AM AST did not see did not see
11 2026/09/10 11:53:36 AM AST 4 2
The.Odyssey..Minecraft.Movie. The.Odyssey..Barbie. The.Odyssey..Backrooms.
1
2 3 - Ok 5 - Amazing did not see
3 did not see 5 - Amazing did not see
4 2 5 - Amazing 3 - Ok
5 2 4 4
6 3 - Ok 3 - Ok did not see
7 3 - Ok 3 - Ok 2
8 4 3 - Ok 4
9 3 - Ok 4 4
10 3 - Ok 4 3 - Ok
11 1 - Bad 2 2
The.Odyssey..Five.Nights.at.Freddy.s.2. X X.1
1 3 2
2 1 - Bad NA NA
3 did not see NA NA
4 2 NA NA
5 3 - Ok NA NA
6 did not see NA NA
7 2 NA NA
8 3 - Ok NA NA
9 4 NA NA
10 1 - Bad NA NA
11 1 - Bad NA NA
Remove the one erroneous row in there.
Movies <- Movies[-1,]
And rename the columns before I do any meaningful manipulation.
Movies <-rename(Movies,"The Odyssey"= The.Odyssey..The.Odyssey.,"Dune 2"= The.Odyssey..Dune.2.,"Minecraft Movie"= The.Odyssey..Minecraft.Movie.,"Barbie"= The.Odyssey..Barbie.,"Backrooms"= The.Odyssey..Backrooms.,"Five Nights at Freddys 2"= The.Odyssey..Five.Nights.at.Freddy.s.2.)
Now I will generate the average values that were seen in the example excel. We are looking to generate an average of each column per row, and then an average of each row per column. This will allow us to build out the rest of the values for our global baseline estimate model. To do this I will mutate the original table and use rowwise() in order to get means per row, and then I will create a new dataframe, ungroup it, and find the column-based averages using summarize().
Now to build out the Global Baseline Estimate. For this we are going to look at Index = 1, and determine their rating of the movie “Backrooms”. We will call this Submitter 1.
Submitter 1 has seen all the other movies. We will calculate what his rating for Backrooms would be by starting with the Mean movie rating of all movies, and then using both the Backrooms rating relative to average and Submitter 1’s ratings relative to average in order to triangulate the guessed value. I first need to get the adjusted averages
Movies <- Movies |>mutate(User_Avg_Mean_Movie = User_Avg -3.151667)Movie_Ratings_Avg_Mean_Movie <- Movie_Ratings_Avg |>summarize(across(c(`The Odyssey`, `Dune 2`, `Minecraft Movie`, Barbie, Backrooms, `Five Nights at Freddys 2`, User_Avg), \(x) x -3.151667))
Now we will use these to calculate Submitter 1’s rating for “Backrooms”.
Global Baseline Estimate = Mean Movie Rating + Backrooms rating relative to average + Submitter 1’s rating relative to average
As you can see from these calculations, This submitters backrooms score came back to be 3.79. This uses the Mean movie Rating (3.15) as a starting point, which then is augmented by the deviance of other ratings for that column, and the deviance of that users rankings versus the average. This cleverly uses the data present to generate a best guess for the rating.
To expand on this model would be to integrate more automation and apply the logic to all blanks. I could see a means of calculating for all “NA’s” in the table, and it may make it easier to compile all the data in the same table rather than segmented into 3-4 like mine. With all the data in one place it would be easier to automate the calculation for the hypothetical rankings.
