This assignment uses movie and television show ratings collected from family and friends to demonstrate how survey data can be stored in a relational database and analyzed in R. Participants rated media they had seen on a 1–5 scale, while titles they had not rated were treated as missing values.
PostgreSQL was used to organize the data into separate tables for users, media, and ratings. The completed dataset was then exported as a CSV file and loaded into R from GitHub. The analysis focuses on identifying missing ratings and using dplyr, tidyr, is.na(), and mean() to summarize the collected data.
library(readr)
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, unionlibrary(stringr)
library(tidyr)movie_ratings <-read_csv("https://github.com/jmald1987/DATA607-Movie_Ratings/raw/refs/heads/main/movie_ratings.csv"
)## Rows: 77 Columns: 4
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr (4): name, title, media_type, rating
##
## ℹ Use `spec()` to retrieve the full column specification for this data.
## ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.names(movie_ratings)## [1] "name" "title" "media_type" "rating"head(movie_ratings)## # A tibble: 6 × 4
## name title media_type rating
## <chr> <chr> <chr> <chr>
## 1 Daniel C Cape Fear TV Show NULL
## 2 Daniel C Criminal Minds TV Show NULL
## 3 Daniel C Dexter Original Sin TV Show NULL
## 4 Daniel C Dexter Ressurection TV Show NULL
## 5 Daniel C Gaurdians of The Galaxy V.3 Movie 4
## 6 Daniel C Hey Duggee TV Show NULLstr(movie_ratings)## spc_tbl_ [77 × 4] (S3: spec_tbl_df/tbl_df/tbl/data.frame)
## $ name : chr [1:77] "Daniel C" "Daniel C" "Daniel C" "Daniel C" ...
## $ title : chr [1:77] "Cape Fear" "Criminal Minds" "Dexter Original Sin" "Dexter Ressurection" ...
## $ media_type: chr [1:77] "TV Show" "TV Show" "TV Show" "TV Show" ...
## $ rating : chr [1:77] "NULL" "NULL" "NULL" "NULL" ...
## - attr(*, "spec")=
## .. cols(
## .. name = col_character(),
## .. title = col_character(),
## .. media_type = col_character(),
## .. rating = col_character()
## .. )
## - attr(*, "problems")=<externalptr>movie_ratings <- movie_ratings %>%
mutate(
rating = na_if(rating, "NULL"),
rating = as.numeric(rating)
)sum(is.na(movie_ratings$rating))## [1] 64ratings_clean <- movie_ratings %>%
drop_na(rating)
glimpse(ratings_clean)## Rows: 13
## Columns: 4
## $ name <chr> "Daniel C", "Juilo C", "Juilo C", "Juilo C", "Juilo C", "Ju…
## $ title <chr> "Gaurdians of The Galaxy V.3", "Cape Fear", "Dexter Origina…
## $ media_type <chr> "Movie", "TV Show", "TV Show", "TV Show", "Movie", "TV Show…
## $ rating <dbl> 4, 5, 3, 3, 5, 5, 4, 4, 5, 5, 4, 5, 5After cleaning the missing ratings, the remaining data can be summarized to better understand how participants rated each movie or show. Grouping the data by title allows the number of ratings and average rating for each media item to be compared. A second summary by participant shows how many titles each person rated and their average rating across those selections.
movie_summary <- ratings_clean %>%
group_by(title, media_type) %>%
summarise(
number_of_ratings = n(),
average_rating = mean(rating)
)## `summarise()` has regrouped the output.
## ℹ Summaries were computed grouped by title and media_type.
## ℹ Output is grouped by title.
## ℹ Use `summarise(.groups = "drop_last")` to silence this message.
## ℹ Use `summarise(.by = c(title, media_type))` for per-operation grouping
## (`?dplyr::dplyr_by`) instead.movie_summary## # A tibble: 11 × 4
## # Groups: title [11]
## title media_type number_of_ratings average_rating
## <chr> <chr> <int> <dbl>
## 1 Cape Fear TV Show 3 4.67
## 2 Criminal Minds TV Show 1 5
## 3 Dexter Original Sin TV Show 1 3
## 4 Dexter Ressurection TV Show 1 3
## 5 Gaurdians of The Galaxy V.3 Movie 1 4
## 6 Hey Duggee TV Show 1 5
## 7 Lioness TV Show 1 5
## 8 Master of The Universe Movie 1 5
## 9 Mouse Trap TV Show 1 5
## 10 The 100 TV Show 1 4
## 11 Things Heard And Seen Movie 1 4user_summary <- ratings_clean %>%
group_by(name) %>%
summarise(
number_of_ratings = n(),
average_rating = mean(rating)
)
user_summary## # A tibble: 7 × 3
## name number_of_ratings average_rating
## <chr> <int> <dbl>
## 1 Daniel C 1 4
## 2 Juilo C 4 4
## 3 Julie E 1 5
## 4 Krystal C 2 4
## 5 Lesley R 2 5
## 6 Mike E 1 4
## 7 Sabrina H 2 5The analysis showed that the collected ratings were unevenly distributed across the selected movies and shows, with some titles receiving more ratings than others. Cape Fear received the most ratings, while several other titles were rated by only one participant. The participant summaries also showed differences in how many titles each person rated, with Julio providing the most ratings.
Overall, the assignment demonstrated how normalized SQL tables can be used to store user, media, and rating data separately and then combine the information for analysis in R. After converting missing SQL values into R missing values, the cleaned data could be summarized using counts and average ratings to compare both media titles and participant behavior.