These are the packages I installed initially
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 errorslibrary(dbplyr)##
## Attaching package: 'dbplyr'
##
## The following objects are masked from 'package:dplyr':
##
## ident, sql, sql_escape_ident, sql_escape_stringlibrary(dplyr)Also, I converted the Excel file in which I had inserted the actual rankings into a CSV. However, some of the names included colons in the title which messed things up when selecting columns. Also, later, I had to create a new Excel/CSV file where I inserted zeros instead of empty spaces for non-responses. R converted the empty spaces into NAs. This also prevented me from doing some calculations. However, I could not start with the file with zeros for NAs, because I had to calculate averages and the zeros would have been counted as numbers.
url<- "https://raw.githubusercontent.com/Enrique01234/607-Fall-2026/refs/heads/main/Movies%20to%20Export.xlsx%20-%20Sheet3.csv"
Mov2 <- read_csv(file = url,
show_col_types = FALSE,
progress = FALSE)This is the way the actual rankings look like
Mov2## # A tibble: 5 × 6
## Name Barbie Captain Dune Nomadland Oppenheimer
## <chr> <dbl> <dbl> <dbl> <dbl> <dbl>
## 1 Brenda 4 NA NA 3 NA
## 2 Cornelia 1 NA NA 5 NA
## 3 Alberto 3 NA 3 NA 5
## 4 Nathaniel 5 3 NA NA NA
## 5 Enrique 4 3 NA NA NAIn order to calculate the Mean Movie Rating (MMR), first the averages by person and by movie need to be calculated. These will be used to calculate the relative to average ratings (again by person and by movie), which go into the MMR formula.
First, I calculated the averages per person (person_mean) and added it to the data fare. The row mean is calculated using all columns with numbers (i.e., excluding the column with names). Also, the na.rm = TRUE is used to skip the NAs in calculating the average.
Mov2 <- Mov2 %>%
mutate(person_mean = rowMeans(pick(where(is.numeric)), na.rm = TRUE))
glimpse(Mov2)## Rows: 5
## Columns: 7
## $ Name <chr> "Brenda", "Cornelia", "Alberto", "Nathaniel", "Enrique"
## $ Barbie <dbl> 4, 1, 3, 5, 4
## $ Captain <dbl> NA, NA, NA, 3, 3
## $ Dune <dbl> NA, NA, 3, NA, NA
## $ Nomadland <dbl> 3, 5, NA, NA, NA
## $ Oppenheimer <dbl> NA, NA, 5, NA, NA
## $ person_mean <dbl> 3.500000, 3.000000, 3.666667, 4.000000, 3.500000Then I calculated the average ranking per movie. Each one is shown separately and the names are always constructed by adding “_mean” to the movie name.
Mov2 <- Mov2 %>%
mutate(Barbie_mean = mean(Barbie))
Mov2$Barbie_mean## [1] 3.4 3.4 3.4 3.4 3.4Barbie is the only movie that had been watched by everybody in my sample. The na.rm = TRUE command is added in the calculation of the other means in order to deal with the NAs
Mov2 <- Mov2 %>%
mutate(Captain_mean = mean(Captain, na.rm = TRUE))
Mov2$Captain_mean## [1] 3 3 3 3 3Mov2 <- Mov2 %>%
mutate(Dune_mean = mean(Dune, na.rm = TRUE))
Mov2$Dune_mean## [1] 3 3 3 3 3Mov2 <- Mov2 %>%
mutate(Nomad_mean = mean(Nomadland, na.rm = TRUE))
Mov2$Nomad_mean## [1] 4 4 4 4 4Mov2 <- Mov2 %>%
mutate(Oppen_mean = mean(Oppenheimer, na.rm = TRUE))
Mov2$Oppen_mean## [1] 5 5 5 5 5Although these numbers matched what I had in the Excel table, at this stage I tried to reproduce the averages by calculating the sums and then dividing by the counts. However, I could not count only the numbers. In other words, I did not find a way to skip the NAs and count only the instances of a value. Nevertheless, I was able to proceed with the steps for calculating the MMR. However, this issue of avoiding the NAs came up later.
Having calculated the sums of the ratings per movie in order to reproduced the average was needed in any case. They are used to calculate the overall average rating (MMR). However, as I could not get R to count the total observations automatically, I generated a new column called “counted” which I manually set at eleven.
Mov2 <- Mov2 %>%
mutate(Barbie_sum = sum(Barbie))
Mov2$Barbie_sum## [1] 17 17 17 17 17Mov2 <- Mov2 %>%
mutate(Captain_sum = sum (Captain, na.rm = TRUE))
Mov2$Captain_sum## [1] 6 6 6 6 6Mov2 <- Mov2 %>%
mutate(Dune_sum = sum(Dune, na.rm = TRUE))
Mov2$Dune_sum## [1] 3 3 3 3 3Mov2 <- Mov2 %>%
mutate(Nomad_sum = sum(Nomadland, na.rm = TRUE))
Mov2$Nomad_sum## [1] 8 8 8 8 8Mov2 <- Mov2 %>%
mutate(Oppen_sum = sum(Oppenheimer, na.rm = TRUE))
Mov2$Oppen_sum## [1] 5 5 5 5 5All these individual movie sums were added and the new variable (Movies_sum) included in the data frame.
Mov2 <- Mov2 %>%
mutate(Movies_sum = Barbie_sum + Captain_sum + Dune_sum + Nomad_sum + Oppen_sum)
Mov2$Movies_sum## [1] 39 39 39 39 39The MMR, then, was calculated diving Movie_sum by 11 (as explained above).
Mov2 <- Mov2 %>%
mutate(counted = 11)
Mov2$counted## [1] 11 11 11 11 11MMR = Movies_sum/ counted
Mov2 <- Mov2 %>%
mutate(MMR = Movies_sum/ counted)
Mov2$MMR## [1] 3.545455 3.545455 3.545455 3.545455 3.545455In order to calculate the Global Baseline Estimates,the movie and personal relative rankings are needed. They are just the difference between MMR and the individual movie and personal average rankings. All of them have been labeled by adding “_Relv” to the movie’s (or person’s) name.
Mov2 <- Mov2 %>%
mutate(Barbie_Relv = Barbie_mean - MMR)
Mov2$Barbie_Relv## [1] -0.1454545 -0.1454545 -0.1454545 -0.1454545 -0.1454545Mov2 <- Mov2 %>%
mutate(Captain_Relv = Captain_mean - MMR)
Mov2$Captain_Relv## [1] -0.5454545 -0.5454545 -0.5454545 -0.5454545 -0.5454545Mov2 <- Mov2 %>%
mutate(Dune_Relv = Dune_mean - MMR)
Mov2$Dune_Relv## [1] -0.5454545 -0.5454545 -0.5454545 -0.5454545 -0.5454545Mov2 <- Mov2 %>%
mutate(Nomad_Relv = Nomad_mean - MMR)
Mov2$Nomad_Relv## [1] 0.4545455 0.4545455 0.4545455 0.4545455 0.4545455 Mov2 <- Mov2 %>%
mutate(Oppen_Relv = Oppen_mean - MMR)
Mov2$Oppen_Relv## [1] 1.454545 1.454545 1.454545 1.454545 1.454545Mov2 <- Mov2 %>%
mutate(person_Relv = person_mean - MMR)
Mov2$person_Relv## [1] -0.04545455 -0.54545455 0.12121212 0.45454545 -0.04545455Now it is possible to calculate the Global Baseline Estimate, which simply sums the MMR, the movie relative ranking and the personal movie ranking. It is not calculated for Barbie because, as mentioned above, all the persons in my sample had already watched it.
Mov2 <- Mov2 %>%
mutate(Captain_Recomm = MMR + Captain_Relv + person_Relv)
Mov2$Captain_Recomm## [1] 2.954545 2.454545 3.121212 3.454545 2.954545Mov2 <- Mov2 %>%
mutate(Dune_Recomm = MMR + Dune_Relv + person_Relv)
Mov2$Dune_Recomm## [1] 2.954545 2.454545 3.121212 3.454545 2.954545Mov2 <- Mov2 %>%
mutate(Nomad_Recomm = MMR + Nomad_Relv + person_Relv)
Mov2$Nomad_Recomm## [1] 3.954545 3.454545 4.121212 4.454545 3.954545Mov2 <- Mov2 %>%
mutate(Oppen_Recomm = MMR + Oppen_Relv + person_Relv)
Mov2$Oppen_Recomm## [1] 4.954545 4.454545 5.121212 5.454545 4.954545These recommendation can now be inserted in the original matrix with the actual rankings to find the specific recommendation for each movie for each person. However, as mentioned above, I have not figured out how to deal with NAs, in this case to insert these recommendations without erasing the actual rankings by each person. In order to deal with this issue, I had to generate and use a new data frame.
First, I generated a new Excel file where I filled the empty cells (where the recommendations should go) with zeros. I converted it to a CSV file, placed in GitHub and loaded it. I called this data frame WithoutNAs.
url<- "https://raw.githubusercontent.com/Enrique01234/607-Fall-2026/refs/heads/main/Movies%20to%20Export%20without%20NAs.xlsx%20-%20Sheet3.csv"
WithoutNAs <- read_csv(file = url,
show_col_types = FALSE,
progress = FALSE)
WithoutNAs## # A tibble: 5 × 6
## Name BarbienoNAS CaptainnoNAS DunenoNAS NomadlandnoNAS OppenheimernoNAS
## <chr> <dbl> <dbl> <dbl> <dbl> <dbl>
## 1 Brenda 4 0 0 3 0
## 2 Cornelia 1 0 0 5 0
## 3 Alberto 3 0 3 0 5
## 4 Nathaniel 5 3 0 0 0
## 5 Enrique 4 3 0 0 0Then I joined the two data frames. Better said, I added (left_join) the new data frame into the other one. One the one hand I did not want to lose all the variables I had generated. On the other one, I needed all the information in the new data frame which was already arranged in the same rows as the old one.
Mov2<- Mov2 %>%
left_join(WithoutNAs)## Joining with `by = join_by(Name)`Then I started exploring how to construct a new column that included both the original ranking and the recommendations. This was not a problem, as discussed above, for Barbie. Eventually, for Captain America, I was able to construct the new column (FullCaptain), and include it in the data frame.
Mov2<- Mov2 %>%
mutate(FullCaptain = if_else(CaptainnoNAS ==0,Captain_Recomm, CaptainnoNAS)
)
Mov2$FullCaptain## [1] 2.954545 2.454545 3.121212 3.000000 3.000000The round numbers with many zeros after the decimal point are the actual rankings. The other ones are the recommendations.
The same was done for the other three movies.
Mov2<- Mov2 %>%
mutate(FullDune = if_else(DunenoNAS ==0,Dune_Recomm, DunenoNAS))
Mov2$FullDune## [1] 2.954545 2.454545 3.000000 3.454545 2.954545Mov2<- Mov2 %>%
mutate(FullNomad = if_else(NomadlandnoNAS ==0,Nomad_Recomm, NomadlandnoNAS))
Mov2$FullNomad## [1] 3.000000 5.000000 4.121212 4.454545 3.954545Mov2<- Mov2 %>%
mutate(FullOppen = if_else(OppenheimernoNAS ==0,Oppen_Recomm, OppenheimernoNAS))
Mov2$FullOppen## [1] 4.954545 4.454545 5.000000 5.454545 4.954545Now, I had all the information I needed to construct a table with all the recommendations. However, I needed an additional package to construct the table.
I installed an additional package (gt)
install.packages("gt")## Installing package into '/cloud/lib/x86_64-pc-linux-gnu-library/4.6'
## (as 'lib' is unspecified)library(gt)Then I constructed the summary table. It includes the original rankings (the round numbers) as well as the recommendations.
Mov2 %>%
gt() %>%
tab_spanner(
label = "Movies",
columns = c(Barbie, FullCaptain, FullDune, FullNomad, FullOppen))| Name |
Movies
|
Captain | Dune | Nomadland | Oppenheimer | person_mean | Barbie_mean | Captain_mean | Dune_mean | Nomad_mean | Oppen_mean | Barbie_sum | Captain_sum | Dune_sum | Nomad_sum | Oppen_sum | Movies_sum | counted | MMR | Barbie_Relv | Captain_Relv | Dune_Relv | Nomad_Relv | Oppen_Relv | person_Relv | Captain_Recomm | Dune_Recomm | Nomad_Recomm | Oppen_Recomm | BarbienoNAS | CaptainnoNAS | DunenoNAS | NomadlandnoNAS | OppenheimernoNAS | ||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Barbie | FullCaptain | FullDune | FullNomad | FullOppen | ||||||||||||||||||||||||||||||||||
| Brenda | 4 | 2.954545 | 2.954545 | 3.000000 | 4.954545 | NA | NA | 3 | NA | 3.500000 | 3.4 | 3 | 3 | 4 | 5 | 17 | 6 | 3 | 8 | 5 | 39 | 11 | 3.545455 | -0.1454545 | -0.5454545 | -0.5454545 | 0.4545455 | 1.454545 | -0.04545455 | 2.954545 | 2.954545 | 3.954545 | 4.954545 | 4 | 0 | 0 | 3 | 0 |
| Cornelia | 1 | 2.454545 | 2.454545 | 5.000000 | 4.454545 | NA | NA | 5 | NA | 3.000000 | 3.4 | 3 | 3 | 4 | 5 | 17 | 6 | 3 | 8 | 5 | 39 | 11 | 3.545455 | -0.1454545 | -0.5454545 | -0.5454545 | 0.4545455 | 1.454545 | -0.54545455 | 2.454545 | 2.454545 | 3.454545 | 4.454545 | 1 | 0 | 0 | 5 | 0 |
| Alberto | 3 | 3.121212 | 3.000000 | 4.121212 | 5.000000 | NA | 3 | NA | 5 | 3.666667 | 3.4 | 3 | 3 | 4 | 5 | 17 | 6 | 3 | 8 | 5 | 39 | 11 | 3.545455 | -0.1454545 | -0.5454545 | -0.5454545 | 0.4545455 | 1.454545 | 0.12121212 | 3.121212 | 3.121212 | 4.121212 | 5.121212 | 3 | 0 | 3 | 0 | 5 |
| Nathaniel | 5 | 3.000000 | 3.454545 | 4.454545 | 5.454545 | 3 | NA | NA | NA | 4.000000 | 3.4 | 3 | 3 | 4 | 5 | 17 | 6 | 3 | 8 | 5 | 39 | 11 | 3.545455 | -0.1454545 | -0.5454545 | -0.5454545 | 0.4545455 | 1.454545 | 0.45454545 | 3.454545 | 3.454545 | 4.454545 | 5.454545 | 5 | 3 | 0 | 0 | 0 |
| Enrique | 4 | 3.000000 | 2.954545 | 3.954545 | 4.954545 | 3 | NA | NA | NA | 3.500000 | 3.4 | 3 | 3 | 4 | 5 | 17 | 6 | 3 | 8 | 5 | 39 | 11 | 3.545455 | -0.1454545 | -0.5454545 | -0.5454545 | 0.4545455 | 1.454545 | -0.04545455 | 2.954545 | 2.954545 | 3.954545 | 4.954545 | 4 | 3 | 0 | 0 | 0 |
Two further thoughts. First, I have not figured out yet why additional columns appear in the table. In any case, the first five ones after the reviewers’ names (with the “Movies” header) are the ones that matter.
More importantly, one of the recommendations seems to be wrong. It has a value above 5. It is the one for Nathaniel/Oppenheimer.
It is possible that it is wrong. However, I suspect it is OK. I actually get the same result when I do the exercise in Excel. It may be the case that as both Nathaniel and Oppenheimer have relative rankings exceeding the average, when both of those rankings are added to the MMR, it surpasses 5.
The problem may be in having so few observations. Nathaniel had only watched one movie and Oppenheimer was watched by only one person. If this is the case, it should be easy to fix adding a cap, probably using the else_if command to make the value be 5 if the result of the calculation exceeds five. Something similar could be done if the rating falls below 1.