editor_options: markdown: wrap: 72 —
title: “Assignment 2: Sql and R” author: “Carol Campbell” date: “2026-09-23” output: pdf_document: default html_document: default —
Movie Ratings from random “critics”
I chose six movies that played in or are currently playing in theatres, and asked ten “friends” aka “critics” to rate each of the movies they had seen on a scale of 1 to 5. Not everyone saw every movie.
Using pgadmin from PostgreSQL, I created the “Movies” database which has three individual tables to store data.
##Install needed packages and/or libraries.
I created a new PostgreSQL database connection, “Movies”, for this assignment. We were instructed not to disclose our password for this assignment, so I used the superuser password that I created during PostgreSQL installation when prompted by the code to access the database.
# Connect PostgresSQL database to R to upload my "Movies" database.
myMoviesdb = dbConnect(RPostgres::Postgres(),
dbname='Movies',
host= Sys.getenv("local"),
port=5432,
user='postgres',
password=rstudioapi::askForPassword("Enter password"))Now that the database has been loaded, we should view the tables that we’ll be working with. database?
# See the database tables.
dbListTables(myMoviesdb)## [1] "critics" "movie" "score"The Movies database contains three tables: critics, movie and score. Next we’ll load and view each.
#load each table into a tibble using dbGetQuery()
movie <- as_tibble(dbGetQuery(myMoviesdb, "SELECT * FROM movie"))
movie## # A tibble: 6 × 3
## movie_id title genre
## <int> <chr> <chr>
## 1 1 Mortal Kombat II Action/Adventure
## 2 2 Project Hail Mary Sci-Fi/Adventure
## 3 3 Coyote vs. Acme Kids/Comedy/Adventure
## 4 4 Obsession Horror/Mystery/Thriller
## 5 5 The Invite Comedy/Drama
## 6 6 The Odyssey Action/Adventure/Fantasycritics <- as_tibble(dbGetQuery(myMoviesdb,"SELECT distinct critic_id, first_name from critics order by 1"))
critics## # A tibble: 10 × 2
## critic_id first_name
## <int> <chr>
## 1 201 Melvin
## 2 202 Yvonne
## 3 203 Lawrence
## 4 204 Betty
## 5 205 Melanie
## 6 206 Kendra
## 7 207 Jassiem
## 8 208 Lonnie
## 9 209 Trudy
## 10 210 Kimscore <- as_tibble(dbGetQuery(myMoviesdb,"select * from score"))
head(score)## # A tibble: 6 × 3
## movie_id critic_id rating
## <int> <int> <int>
## 1 1 201 5
## 2 2 201 4
## 3 3 201 4
## 4 4 201 NA
## 5 5 201 NA
## 6 6 201 5I then joined the tables via an SQL statement executed within R Studio to create one consolidated table named “movie_ratings” for analysis and manipulation in r
movie_ratings <- dbGetQuery(myMoviesdb, "SELECT
m.title,
c.first_name,
s.rating
FROM movie m
JOIN score s
ON m.movie_id = s.movie_id
LEFT JOIN critics c
ON c.critic_id = s.critic_id")
head(movie_ratings)## title first_name rating
## 1 Mortal Kombat II Melvin 5
## 2 Project Hail Mary Melvin 4
## 3 Coyote vs. Acme Melvin 4
## 4 Obsession Melvin NA
## 5 The Invite Melvin NA
## 6 The Odyssey Melvin 5For the sake of brevity, I limited the output by using the “head” function, but I know that there are many observations in the movie_ratings table. Let’s use “glimpse” function to see:
glimpse(movie_ratings)## Rows: 60
## Columns: 3
## $ title <chr> "Mortal Kombat II", "Project Hail Mary", "Coyote vs. Acme",…
## $ first_name <chr> "Melvin", "Melvin", "Melvin", "Melvin", "Melvin", "Melvin",…
## $ rating <int> 5, 4, 4, NA, NA, 5, NA, 4, 3, NA, 5, NA, 4, NA, NA, 4, 3, 5…Here we see that there are sixty (60) rows of data each with three rows.
Tidying the data
Now that we have our dataframe, movie_ratings, loaded, we need to tidy it for analysis
First, let’s rename the “first_name” column and store it in a new dataframe
#rename column and save to new df
movie_scores <- movie_ratings |> rename(firstname = first_name)
head(movie_scores)## title firstname rating
## 1 Mortal Kombat II Melvin 5
## 2 Project Hail Mary Melvin 4
## 3 Coyote vs. Acme Melvin 4
## 4 Obsession Melvin NA
## 5 The Invite Melvin NA
## 6 The Odyssey Melvin 5# Checked the structure of the movie_scores data. 60 rows. 5 columns.
str(movie_scores)## 'data.frame': 60 obs. of 3 variables:
## $ title : chr "Mortal Kombat II" "Project Hail Mary" "Coyote vs. Acme" "Obsession" ...
## $ firstname: chr "Melvin" "Melvin" "Melvin" "Melvin" ...
## $ rating : int 5 4 4 NA NA 5 NA 4 3 NA ...Tidy the data
Since every reviewer did not see every movie, we have to exclude the blank fields from any calculations. We accomplish this using “!is.na” in filter.
# filter null values using !is.na to exclude blank fields.
movie_scores_fil <- movie_scores |>
filter(!is.na(rating))
#view df new_ratings_filtered
head(movie_scores_fil)## title firstname rating
## 1 Mortal Kombat II Melvin 5
## 2 Project Hail Mary Melvin 4
## 3 Coyote vs. Acme Melvin 4
## 4 The Odyssey Melvin 5
## 5 Project Hail Mary Yvonne 4
## 6 Coyote vs. Acme Yvonne 3Let’s see the structure after we filter N/A values:
# Checked the structure of the movie_scores data. 60 rows. 5 columns.
str(movie_scores_fil)## 'data.frame': 33 obs. of 3 variables:
## $ title : chr "Mortal Kombat II" "Project Hail Mary" "Coyote vs. Acme" "The Odyssey" ...
## $ firstname: chr "Melvin" "Melvin" "Melvin" "Melvin" ...
## $ rating : int 5 4 4 5 4 3 5 4 4 3 ...Here we see that there are now thirty-three rows, instead of the sixty (60), each with three (3)columns.
Next, let’s transform data into wide format to improve readability.
movie_scores_wide <- movie_scores_fil |>
pivot_wider(
names_from = title,
values_from = rating
)
movie_scores_wide## # A tibble: 10 × 7
## firstname `Mortal Kombat II` `Project Hail Mary` `Coyote vs. Acme`
## <chr> <int> <int> <int>
## 1 Melvin 5 4 4
## 2 Yvonne NA 4 3
## 3 Lawrence 4 NA NA
## 4 Betty 2 3 NA
## 5 Melanie NA NA 5
## 6 Kendra 5 2 4
## 7 Jassiem NA 4 2
## 8 Lonnie NA 3 NA
## 9 Trudy 2 NA 4
## 10 Kim NA NA 2
## # ℹ 3 more variables: `The Odyssey` <int>, `The Invite` <int>, Obsession <int>The wide format groups our data by critic and shows their ratings for each movie across one line.
Export and save the movies_scores_wide data frame to local drive for future use
#convert tibble to dataframe
movie_scores_wide_df <- as.data.frame(movie_scores_wide)
movie_scores_wide_df## firstname Mortal Kombat II Project Hail Mary Coyote vs. Acme The Odyssey
## 1 Melvin 5 4 4 5
## 2 Yvonne NA 4 3 NA
## 3 Lawrence 4 NA NA 5
## 4 Betty 2 3 NA 3
## 5 Melanie NA NA 5 3
## 6 Kendra 5 2 4 NA
## 7 Jassiem NA 4 2 NA
## 8 Lonnie NA 3 NA NA
## 9 Trudy 2 NA 4 4
## 10 Kim NA NA 2 4
## The Invite Obsession
## 1 NA NA
## 2 5 NA
## 3 3 4
## 4 NA 1
## 5 4 NA
## 6 NA NA
## 7 NA 3
## 8 5 NA
## 9 5 NA
## 10 NA 3#save data_frame as .csv file
write.csv(movie_scores_wide_df,
"C:/Users/carol/Documents/Data 607/Assignment 2/movie_scores_wide_df.csv", row.names = FALSE)How many movies did each critic review?
#Count ratings per critic and movie
count_per_reviewer <- movie_ratings |>
group_by(first_name) %>%
summarize(
total_movies_rated = sum(!is.na(rating)),
.groups = "drop" # Good practice to remove the grouping afterward
)
count_per_reviewer## # A tibble: 10 × 2
## first_name total_movies_rated
## <chr> <int>
## 1 Betty 4
## 2 Jassiem 3
## 3 Kendra 3
## 4 Kim 3
## 5 Lawrence 4
## 6 Lonnie 2
## 7 Melanie 3
## 8 Melvin 4
## 9 Trudy 4
## 10 Yvonne 3What is the average score that each movie earned from our critics?
# Group by title to see the average score for each movie rated
movie_scores_fil_avg <- movie_scores_fil |>
group_by(title) |>
summarise(Avg_Score = mean(as.numeric(rating))) |>
arrange(desc(Avg_Score)
)
#view avg score sorted highest to smallest
movie_scores_fil_avg## # A tibble: 6 × 2
## title Avg_Score
## <chr> <dbl>
## 1 The Invite 4.4
## 2 The Odyssey 4
## 3 Mortal Kombat II 3.6
## 4 Coyote vs. Acme 3.43
## 5 Project Hail Mary 3.33
## 6 Obsession 2.75And the average rating per critic
reviewer_ratings <-movie_scores |>
group_by(firstname)|>
summarise (Avg_rating = mean(rating, na.rm=TRUE)
)
reviewer_ratings## # A tibble: 10 × 2
## firstname Avg_rating
## <chr> <dbl>
## 1 Betty 2.25
## 2 Jassiem 3
## 3 Kendra 3.67
## 4 Kim 3
## 5 Lawrence 4
## 6 Lonnie 4
## 7 Melanie 4
## 8 Melvin 4.5
## 9 Trudy 3.75
## 10 Yvonne 4Lets see how the reviewer ratings for each movie
reviewer_ratings |>
ggplot () +
geom_col(aes(firstname, Avg_rating)
)
At first glance, we can easily conclude that Betty “grades” movies more harshly than others with an on-average rating of approximately 2.3, which would be true if everyone saw/reviewed every movie, but such is not the case. Some reviewed two movies while others reviewed as many as four, in essence skewing the results. More data exploration is necessary, but I am unsure how to illustrate this graphically.
I look forward to working with this dataset in the future to perform more tidying and visualization as my skills in this course grow.
```