library(DBI)
library(RPostgres)
library(tidyverse)
library(dplyr)
library(readr)Week 2A Assignment - SQL and R – Movie Ratings
Movie Ratings Analysis
Approach
For this assignment, I had to create a survey using Google Form to collect data on opinions on 6 recently popular movies:
Spider-Man: Brand New Day
The Odyssey
Coyote vs ACME
The Whisper Man
Toy Story 5
Project Hail Mary
What I will do with this information is to convert the responses from Google Form into a .csv file so reading in the data can be easier. Once I read in the data using PostgreSQL,
I will then connect the PostgreSQL server to Rstudio to handle missing values, conduct some plot analysis such as comparing average ratings, comparing movies that people have not watched, and other calculations that will follow.
Codebase
To start this assignment, I have taken the survey results and downloaded a CSV file. When I had created the survey, I had thought of an idea of how to handle missing values prior to coding a solution, and it was to create another option of “Have not seen it”. If I had gone and set a label to “0” as a way to indicate someone has not seen something, it would mess up the data arithmetically, so a better way was to label it with a string text.
Next I am going to open PostgreSQL in order for me to connect to R and to continue this assignment.
glimpse(raw_data)Rows: 19
Columns: 7
$ Timestamp <chr> "9/9/2026 13:25:23", "9/9/2026 13:25:46", …
$ `Spider-Man: Brand New Day` <chr> "4", "4", "4", "4", "Have Not seen it", "5…
$ `The Odyssey` <chr> "Have Not Seen It", "3", "3", "5", "Have N…
$ `Coyote vs ACME` <chr> "Have Not Seen it", "Have Not Seen it", "H…
$ `The Whisper Man` <chr> "Have Not Seen It", "Have Not Seen It", "H…
$ `Toy Story 5` <chr> "4", "Have Not Seen It", "2", "4", "Have N…
$ `Project Hail Mary` <chr> "5", "5", "5", "5", "Have Not Seen It", "5…Notes:
Missing Data: Realizing upon after using
glimpse, I need to actually use NULL as a value instead of having string answers because it will not make the columns numerical.Will need to rename
Timestampcolumn touser_idand replace its values with a categorical number to represent an id number.Will need to convert columns to numerical as mentioned for the movies
Change column names to appropriate column names
Then will connect PostgreSQL afterwards
column_rename <- raw_data %>%
mutate(spider_man_bnd = as.integer(`Spider-Man: Brand New Day`)) %>%
mutate(the_odyssey = as.integer(`The Odyssey`)) %>%
mutate(coyote_vs_acme = as.integer(`Coyote vs ACME`)) %>%
mutate(the_whisper_man = as.integer(`The Whisper Man`)) %>%
mutate(toy_story_5 = as.integer(`Toy Story 5`)) %>%
mutate(project_hail_mary = as.integer(`Project Hail Mary`))glimpse(column_rename)Rows: 19
Columns: 13
$ Timestamp <chr> "9/9/2026 13:25:23", "9/9/2026 13:25:46", …
$ `Spider-Man: Brand New Day` <chr> "4", "4", "4", "4", "Have Not seen it", "5…
$ `The Odyssey` <chr> "Have Not Seen It", "3", "3", "5", "Have N…
$ `Coyote vs ACME` <chr> "Have Not Seen it", "Have Not Seen it", "H…
$ `The Whisper Man` <chr> "Have Not Seen It", "Have Not Seen It", "H…
$ `Toy Story 5` <chr> "4", "Have Not Seen It", "2", "4", "Have N…
$ `Project Hail Mary` <chr> "5", "5", "5", "5", "Have Not Seen It", "5…
$ spider_man_bnd <int> 4, 4, 4, 4, NA, 5, 5, NA, NA, 3, 4, NA, 5,…
$ the_odyssey <int> NA, 3, 3, 5, NA, NA, 4, NA, NA, 5, NA, NA,…
$ coyote_vs_acme <int> NA, NA, NA, NA, NA, 5, 5, NA, NA, NA, NA, …
$ the_whisper_man <int> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA…
$ toy_story_5 <int> 4, NA, 2, 4, NA, 1, 4, NA, NA, NA, NA, NA,…
$ project_hail_mary <int> 5, 5, 5, 5, NA, 5, 5, NA, NA, 5, 4, 1, 5, …almost_clean <- column_rename %>%
mutate(user_id = row_number()) # creates a replacement for timestamp
glimpse(almost_clean)Rows: 19
Columns: 14
$ Timestamp <chr> "9/9/2026 13:25:23", "9/9/2026 13:25:46", …
$ `Spider-Man: Brand New Day` <chr> "4", "4", "4", "4", "Have Not seen it", "5…
$ `The Odyssey` <chr> "Have Not Seen It", "3", "3", "5", "Have N…
$ `Coyote vs ACME` <chr> "Have Not Seen it", "Have Not Seen it", "H…
$ `The Whisper Man` <chr> "Have Not Seen It", "Have Not Seen It", "H…
$ `Toy Story 5` <chr> "4", "Have Not Seen It", "2", "4", "Have N…
$ `Project Hail Mary` <chr> "5", "5", "5", "5", "Have Not Seen It", "5…
$ spider_man_bnd <int> 4, 4, 4, 4, NA, 5, 5, NA, NA, 3, 4, NA, 5,…
$ the_odyssey <int> NA, 3, 3, 5, NA, NA, 4, NA, NA, 5, NA, NA,…
$ coyote_vs_acme <int> NA, NA, NA, NA, NA, 5, 5, NA, NA, NA, NA, …
$ the_whisper_man <int> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA…
$ toy_story_5 <int> 4, NA, 2, 4, NA, 1, 4, NA, NA, NA, NA, NA,…
$ project_hail_mary <int> 5, 5, 5, 5, NA, 5, 5, NA, NA, 5, 4, 1, 5, …
$ user_id <int> 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13,…colnames(almost_clean) [1] "Timestamp" "Spider-Man: Brand New Day"
[3] "The Odyssey" "Coyote vs ACME"
[5] "The Whisper Man" "Toy Story 5"
[7] "Project Hail Mary" "spider_man_bnd"
[9] "the_odyssey" "coyote_vs_acme"
[11] "the_whisper_man" "toy_story_5"
[13] "project_hail_mary" "user_id" Last step to clean the data, select the columns we want
movies_df_clean <- almost_clean |>
select(user_id,
spider_man_bnd,
the_odyssey,
coyote_vs_acme,
the_whisper_man,
toy_story_5,
project_hail_mary)
# user_id is an int, need to convert to chr as it is a categorical column
movies_df_clean$user_id <- as.character(movies_df_clean$user_id)
# Cleaned, here is the head of the table
head(movies_df_clean)# A tibble: 6 × 7
user_id spider_man_bnd the_odyssey coyote_vs_acme the_whisper_man toy_story_5
<chr> <int> <int> <int> <int> <int>
1 1 4 NA NA NA 4
2 2 4 3 NA NA NA
3 3 4 3 NA NA 2
4 4 4 5 NA NA 4
5 5 NA NA NA NA NA
6 6 5 NA 5 NA 1
# ℹ 1 more variable: project_hail_mary <int>Data has been cleaned so now we can start the process of analyzing and connecting to PostgreSQL
Connection Chunk has been hidden for obvious reasons.
# Creates the table to postgresql
dbWriteTable(connection, "movie_ratings", movies_df_clean, overwrite = TRUE)
dbExistsTable(connection, "movie_ratings") # table exists and checked in PostgreSQL[1] TRUEdbGetQuery(connection, "SELECT * FROM movie_ratings") user_id spider_man_bnd the_odyssey coyote_vs_acme the_whisper_man
1 1 4 NA NA NA
2 2 4 3 NA NA
3 3 4 3 NA NA
4 4 4 5 NA NA
5 5 NA NA NA NA
6 6 5 NA 5 NA
7 7 5 4 5 NA
8 8 NA NA NA NA
9 9 NA NA NA NA
10 10 3 5 NA NA
11 11 4 NA NA NA
12 12 NA NA NA NA
13 13 5 4 4 4
14 14 5 5 4 NA
15 15 NA NA NA NA
16 16 5 1 3 5
17 17 3 1 2 4
18 18 5 5 NA NA
19 19 5 NA NA NA
toy_story_5 project_hail_mary
1 4 5
2 NA 5
3 2 5
4 4 5
5 NA NA
6 1 5
7 4 5
8 NA NA
9 NA NA
10 NA 5
11 NA 4
12 NA 1
13 5 5
14 3 3
15 NA 5
16 5 2
17 NA 5
18 NA 5
19 5 5dbDisconnect(connection)Now that the SQL portion of the assignment is done, now to do some analysis:
Summary Statistics
To handle missing values, using the na.rm argument in the mean() function, it ignores NA values
# Spider-Man: Brand New Day
bnd_summary <- movies_df_clean %>%
summarise(
Movie = "Spider-Man: Brand New Day",
mean_rating = round(mean(spider_man_bnd, na.rm = TRUE), 2),
missing_ratings = sum(is.na(spider_man_bnd)),
review_counts = sum(!is.na(spider_man_bnd)),
total_review_counts = n()
)
# The Odyssey
odyssey_summary <- movies_df_clean %>%
summarise(
Movie = "The Odyssey",
mean_rating = round(mean(the_odyssey, na.rm = TRUE), 2),
missing_ratings = sum(is.na(the_odyssey)),
review_counts = sum(!is.na(the_odyssey)),
total_review_counts = n()
)
# Coyote vs ACME
coyote_vs_acme_summary <- movies_df_clean %>%
summarise(
Movie = "Coyote vs ACME",
mean_rating = round(mean(coyote_vs_acme, na.rm = TRUE), 2),
missing_ratings = sum(is.na(coyote_vs_acme)),
review_counts = sum(!is.na(coyote_vs_acme)),
total_review_counts = n()
)
# The Whisper Man
whisper_man_summary <- movies_df_clean %>%
summarise(
Movie = "The Whisper Man",
mean_rating = round(mean(the_whisper_man, na.rm = TRUE), 2),
missing_ratings = sum(is.na(the_whisper_man)),
review_counts = sum(!is.na(the_whisper_man)),
total_review_counts = n()
)
# Toy Story 5
toy_story_5_summary <- movies_df_clean %>%
summarise(
Movie = "Toy Story 5",
mean_rating = round(mean(toy_story_5, na.rm = TRUE), 2),
missing_ratings = sum(is.na(toy_story_5)),
review_counts = sum(!is.na(toy_story_5)),
total_review_counts = n()
)
# Project Hail Mary
project_hail_mary_summary <- movies_df_clean %>%
summarise(
Movie = "Project Hail Mary",
mean_rating = round(mean(project_hail_mary, na.rm = TRUE), 2),
missing_ratings = sum(is.na(project_hail_mary)),
review_counts = sum(!is.na(project_hail_mary)),
total_review_counts = n()
)# After creating 6 separate summary tables, now I need to stack them or rather bind them:
final_summary_table <- bind_rows(bnd_summary, odyssey_summary, coyote_vs_acme_summary, whisper_man_summary, toy_story_5_summary, project_hail_mary_summary)
final_summary_table# A tibble: 6 × 5
Movie mean_rating missing_ratings review_counts total_review_counts
<chr> <dbl> <int> <int> <int>
1 Spider-Man: Bra… 4.36 5 14 19
2 The Odyssey 3.6 9 10 19
3 Coyote vs ACME 3.83 13 6 19
4 The Whisper Man 4.33 16 3 19
5 Toy Story 5 3.67 10 9 19
6 Project Hail Ma… 4.38 3 16 19After combining the summary tables, there are some noticeable findings in the survey collections:
Spider-Man: Brand New Day, The Whisper Man and Project Hail Mary have almost the same average ratings
The Odyssey is the lowest ranked movie on this list
A lot of people that filled the survey out has not seen The Whisper Man and yet it still came out as a high average rating
A lot of people have seen Project Hail Mary and then Spider-Man: Brand New Day
Coyote vs ACME and Toy Story 5 seems to me in the middle in terms of average rating and people that have seen the movie
Conclusion
Collecting data through surveys or polls is one of the most important ways to get data as it can be used as a reflection peoples opinions, such as in politics. In this assignment, even though I had gotten 19 responses within a day, it’d be nicer if I had a better way to reach out to people to collect more information which can be something I could figure out if I were to do future work in survey collecting.
As far as workload is concerned, I have had experience in handling missing values when I had taken a class using R in my undergradutate studies, but it did give me enjoyment that even though I did all this before I am still going to use it no matter what. The new thing I had learned whilst doing this was stacking rows on top of each other, when doing the summary tables, I wanted to do it the way in terms of combining the tables using SQL, but I had gone for a different approach and then learned the bind_rows command if I ever want to do something like this again.
Video Explainer Link -