Class 607, Assignment 2a

Introduction

This dataset was created in Visual Studio Code using PostgreSQL extension. This dataset was created by asking family members to rate 1 (low) - 5 (high) popular movies from 2025-26.

Tackling the problem: 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 and 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"

Conclusion

My biggest challenges were getting SQL and connecting between PostGresSQL and R. With this dataset, I would like to play in SQL with the formatting and in R with grabbing the dataset.