Class 607, Assignment 2a

Introduction

This dataset was created in Visual Studio Code using PostgreSQL extension and was created by asking family members and friends to rate 1 (low) - 5 (high) popular movies from 2025-26. If individual asked had not seen the movie, “99” was entered.

Tackling the problem: The plan is to install PostgreSQL and connect it to Visual Studio Code and Rstudio.

Anticipated data challenges: I am new to SQL so this will be a bit challenging. I’ll need to utilize resources to get everything connected and working. I think my biggest hurdle will be correctly downloading PostgreSQL and using the Visual Studio Code environment.

Citations:

• Anthropic. (2025). Claude Opus 4.5 [Large language model]. (https://claude.ai/) Accessed January 8, 2026. Link to chat.

Analysis

#Not shown: Location on computer where I am grabbing the data and creating a saved location

#Install packages needed 
pacman::p_load(
  # Package Install and Management
  pacman,           # package install/load
  janitor,          #clean up data names 
  
  # Project and File Management
  readr,            # import data
  httr,             #github passkey 
  # General Data Management
  dplyr,            # data management
  tidyr,            # data management
  lubridate,        # work with dates
  zoo,              # work with dates
  tidyverse        # work with dates
)


#Load data
#sql 
#library("RPostgres", "DBI", "rstuioapi")

#connect to database 
#sql <- dbConnect(Postgres(), host = "localhost", port = 5432, dbname = "postgres", user = "postgres", password = rstudioapi::askForPassword("Database password"))

#extract data created 
#?RPostgres
#movies <- dbGetQuery(sql, "SELECT * FROM movies;")

#desktop
movies <- read.csv(paste0(csv_location, "movies.csv"), header=TRUE, stringsAsFactors = FALSE)

##Review and clean data 

#looking at dataframe 
ls(movies)

 [1] "first_name"     "id"             "movie_1"        "movie_1_rating"
 [5] "movie_2"        "movie_2_rating" "movie_3"        "movie_3_rating"
 [9] "movie_4"        "movie_4_rating" "movie_5"        "movie_5_rating"
[13] "movie_6"        "movie_6_rating"

#cleaning names slightly 
clean <- movies %>% clean_names
ls(clean)

 [1] "first_name"     "id"             "movie_1"        "movie_1_rating"
 [5] "movie_2"        "movie_2_rating" "movie_3"        "movie_3_rating"
 [9] "movie_4"        "movie_4_rating" "movie_5"        "movie_5_rating"
[13] "movie_6"        "movie_6_rating"

head(clean)

  id first_name            movie_1 movie_1_rating           movie_2
1  1     Jordan KPop Demon Hunters              4 Project Hail Mary
2  2    Kathryn KPop Demon Hunters              3 Project Hail Mary
3  3      Chris KPop Demon Hunters             99 Project Hail Mary
4  4       Mike KPop Demon Hunters             99 Project Hail Mary
5  5    Jenelle KPop Demon Hunters              5 Project Hail Mary
  movie_2_rating        movie_3 movie_3_rating movie_4 movie_4_rating
1             99 Disclosure Day             99 Michael             99
2              5 Disclosure Day             99 Michael              4
3              4 Disclosure Day             99 Michael              3
4              5 Disclosure Day              2 Michael              3
5              4 Disclosure Day             99 Michael             99
      movie_5 movie_5_rating                   movie_6 movie_6_rating
1 The Odyssey              5 Spider-Man: Brand New Day              4
2 The Odyssey              5 Spider-Man: Brand New Day              5
3 The Odyssey              4 Spider-Man: Brand New Day              3
4 The Odyssey              4 Spider-Man: Brand New Day              4
5 The Odyssey              3 Spider-Man: Brand New Day              5

Conclusion

My biggest challenge was indeed downloading SQL correctly as well as properly connecting PostGresSQL and R. Creating the SQL table was not too difficult. If I use this dataset again I would like to play more with extracting the table directly from SQL to R as well as with the formatting and additional table creation in SQL.