Please note my friends don’t watch a lot of movies that overlap so I decided to go with TV shows, and created some categories to get more data that could be explored later. I gathered data in an excel sheet “Interviews.xlsx” because I was on vacation with them this past week and gathered live.
#utilizing IBM site here for connection https://dataplatform.cloud.ibm.com/exchange/public/entry/view/bf1d847b1638af654a0eb849842f85ee?context=cpdaas
#Note: had to install RTools
#install.packages("RPostgreSQL")
#install.packages("DBI")
library(DBI)
library(RPostgreSQL)
#Enter the values for you database connection
dsn_database = "week2Asurvey" # for example "compose"
dsn_hostname = "localhost" # for example "aws-us-east-1-portal.4.dblayer.com"
dsn_port = "5432" # for example 11101
dsn_uid = "jacshap" # for example "admin"
#dsn_pwd = "" # note: included in above chunk that is hidden in published version. Will delete as part of upload to github & this submission
tryCatch({
drv <- dbDriver("PostgreSQL")
print("Connecting to database")
conn <- dbConnect(drv,
dbname = dsn_database,
host = dsn_hostname,
port = dsn_port,
user = dsn_uid,
password = dsn_pwd)
print("Connected!")
},
error=function(cond) {
print("Unable to connect to database.")
})## [1] "Connecting to database"
## [1] "Connected!"#Big Note: kept getting error that password was incorrect. Had to follow this video to fix (changed conf file and altered user in pgadmin) https://www.youtube.com/watch?v=CHYjDuaYA4M&ab_channel=DatabaseStar
#Tables that exist in the db
cursor <- dbGetQuery(conn, "SELECT datname from pg_database")
cursor$datname## [1] "postgres" "week2Asurvey" "template1" "template0"df <- dbGetQuery(conn, "SELECT * FROM shows")
print(df)## interviewees tv_shows overall acting plots recommend
## 1 Mackenzie Its Always Sunny In Philadelphia 5 4 3 TRUE
## 2 Mackenzie Game of Thrones 4 4 3 TRUE
## 3 Mackenzie Gilmore Girls 3 3 2 TRUE
## 4 Mackenzie Peaky Blinders 4 4 4 TRUE
## 5 Mackenzie Love Island 3 2 3 TRUE
## 6 Mackenzie Guys Grocery Games 5 2 4 TRUE
## 7 Crystal Its Always Sunny In Philadelphia 5 5 3 TRUE
## 8 Crystal Game of Thrones 4 5 5 TRUE
## 9 Crystal Gilmore Girls 4 4 3 TRUE
## 10 Tyler Its Always Sunny In Philadelphia 5 5 5 TRUE
## 11 Tyler Game of Thrones 4 5 4 TRUE
## 12 Tyler Peaky Blinders 4 4 3 TRUE
## 13 Tyler Love Island 3 1 2 FALSE
## 14 Tyler Guys Grocery Games 5 5 5 TRUE
## 15 Jake Its Always Sunny In Philadelphia 5 5 4 TRUE
## 16 Jake Game of Thrones 4 4 5 TRUE
## 17 Jake Guys Grocery Games 3 1 4 FALSE
## 18 Caitlin Its Always Sunny In Philadelphia 4 3 3 TRUE
## 19 Caitlin Game of Thrones 4 3 5 TRUE
## 20 Caitlin Peaky Blinders 5 5 5 TRUE
## 21 Caitlin Guys Grocery Games 3 2 4 TRUE
## 22 Alexis Guys Grocery Games 3 4 5 TRUE
## 23 Josh Its Always Sunny In Philadelphia 4 5 3 TRUE
## 24 Josh Game of Thrones 5 5 5 TRUE
## 25 Josh Love Island 4 3 5 TRUE
## 26 Stu Its Always Sunny In Philadelphia 5 5 5 TRUE
## 27 Stu Guys Grocery Games 3 2 4 FALSE
## 28 Skyler Its Always Sunny In Philadelphia 5 5 5 TRUE
## 29 Skyler Guys Grocery Games 4 1 5 TRUE
## 30 Valerie Its Always Sunny In Philadelphia 3 3 4 FALSE
## 31 Valerie Gilmore Girls 5 5 5 TRUE
## 32 Valerie Guys Grocery Games 3 1 1 TRUE
## 33 Crystal Guys Grocery Games NA NA NA TRUE
## 34 Crystal Peaky Blinders NA NA NA NA
## 35 Crystal Love Island NA NA NA NA
## 36 Tyler Gilmore Girls NA NA NA NA
## 37 Jake Gilmore Girls NA NA NA NA
## 38 Jake Peaky Blinders NA NA NA NA
## 39 Jake Love Island NA NA NA NA
## 40 Caitlin Gilmore Girls NA NA NA NA
## 41 Caitlin Love Island NA NA NA NA
## 42 Alexis Its Always Sunny In Philadelphia NA NA NA NA
## 43 Alexis Game of Thrones NA NA NA NA
## 44 Alexis Gilmore Girls NA NA NA NA
## 45 Alexis Peaky Blinders NA NA NA NA
## 46 Alexis Love Island NA NA NA NA
## 47 Josh Gilmore Girls NA NA NA NA
## 48 Josh Peaky Blinders NA NA NA NA
## 49 Josh Guys Grocery Games NA NA NA NA
## 50 Stu Game of Thrones NA NA NA NA
## 51 Stu Gilmore Girls NA NA NA NA
## 52 Stu Peaky Blinders NA NA NA NA
## 53 Stu Love Island NA NA NA NA
## 54 Skyler Game of Thrones NA NA NA NA
## 55 Skyler Gilmore Girls NA NA NA NA
## 56 Skyler Peaky Blinders NA NA NA NA
## 57 Skyler Love Island NA NA NA NA
## 58 Valerie Game of Thrones NA NA NA NA
## 59 Valerie Peaky Blinders NA NA NA NA
## 60 Valerie Love Island NA NA NA NA