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.
#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)
library(tidyverse)## ── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
## ✔ dplyr 1.1.4 ✔ readr 2.1.5
## ✔ forcats 1.0.0 ✔ stringr 1.5.1
## ✔ ggplot2 3.5.2 ✔ tibble 3.3.0
## ✔ lubridate 1.9.4 ✔ tidyr 1.3.1
## ✔ purrr 1.1.0
## ── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
## ✖ dplyr::filter() masks stats::filter()
## ✖ dplyr::lag() masks stats::lag()
## ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errorslibrary(dplyr)
library(tidyr)
#Enter the values for you database connection
dsn_database = "postgres" # for example "compose"
dsn_hostname = "localhost" # for example "aws-us-east-1-portal.4.dblayer.com"
dsn_port = "5432" # for example 11101
dsn_uid = "postgres" # for example "admin"
dsn_pwd = password # 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" "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 NAGoing to just keep overall for purposes of this exercise
df <- df %>% select(interviewees, tv_shows, overall)Add averages per person and per movie
df <- df %>% arrange(desc(interviewees))
df <- df %>% group_by(interviewees) %>% mutate(user_avg = mean(overall, na.rm = TRUE))
df <- df %>% group_by(tv_shows) %>% mutate(show_avg = mean(overall, na.rm = TRUE))Average tv show rating
avg_all_shows <- mean(df$show_avg)User average rating - average for all shows
df <- df %>% mutate(user_avg_minus_avg_show = user_avg - avg_all_shows)show average - average for all shows
df <- df %>% mutate(show_avg_minus_avg_show = show_avg - avg_all_shows)Predict if someone will like something if not already filled in
#rating_prediction <- df %>% mutate(overall_prediction = ifelse(overall != is.na(),
# replace(avg_all_shows + show_avg_minus_avg_show + user_avg_minus_avg_show)
# ))
#not working, can't figure out why
#df %>% replace_na(list(overall = (avg_all_shows + show_avg_minus_avg_show + user_avg_minus_avg_show)))
#rating_prediction <- df %>% mutate(overall = replace_na(overall, (avg_all_shows + show_avg_minus_avg_show + user_avg_minus_avg_show)))
#it's replacing everything, not sure how to just do ones that are NAOk, so another approach for this part was to make another dataframe of all empty fields to perform the calculation. I will try that approach and output that table, then do some examples.
First, calculate values:
#table with empty "Overall" values
empty_table <- df %>% filter(is.na(overall))
#calculate values in overall field
empty_table <- empty_table %>% mutate(overall = avg_all_shows + show_avg_minus_avg_show + user_avg_minus_avg_show) #%>% select(interviewees, tv_shows, overall)
print(empty_table)## # A tibble: 28 × 7
## # Groups: tv_shows [6]
## interviewees tv_shows overall user_avg show_avg user_avg_minus_avg_s…¹
## <chr> <chr> <dbl> <dbl> <dbl> <dbl>
## 1 Valerie Game of Thrones 3.83 3.67 4.17 -0.336
## 2 Valerie Peaky Blinders 4.00 3.67 4.33 -0.336
## 3 Valerie Love Island 3.00 3.67 3.33 -0.336
## 4 Tyler Gilmore Girls 4.20 4.2 4 0.198
## 5 Stu Game of Thrones 4.16 4 4.17 -0.00231
## 6 Stu Gilmore Girls 4.00 4 4 -0.00231
## 7 Stu Peaky Blinders 4.33 4 4.33 -0.00231
## 8 Stu Love Island 3.33 4 3.33 -0.00231
## 9 Skyler Game of Thrones 4.66 4.5 4.17 0.498
## 10 Skyler Gilmore Girls 4.50 4.5 4 0.498
## # ℹ 18 more rows
## # ℹ abbreviated name: ¹user_avg_minus_avg_show
## # ℹ 1 more variable: show_avg_minus_avg_show <dbl>Stu hasn’t seen several shows. Let’s see which he should see next:
Stus_show <- empty_table %>% filter(interviewees == "Stu") #%>% summarize(Stus_pick = max(overall))
print(Stus_show)## # A tibble: 4 × 7
## # Groups: tv_shows [4]
## interviewees tv_shows overall user_avg show_avg user_avg_minus_avg_show
## <chr> <chr> <dbl> <dbl> <dbl> <dbl>
## 1 Stu Game of Thrones 4.16 4 4.17 -0.00231
## 2 Stu Gilmore Girls 4.00 4 4 -0.00231
## 3 Stu Peaky Blinders 4.33 4 4.33 -0.00231
## 4 Stu Love Island 3.33 4 3.33 -0.00231
## # ℹ 1 more variable: show_avg_minus_avg_show <dbl>#Stus_show <- empty_table %>% group_by(overall, tv_shows) %>% filter(interviewees == "Stu") %>% slice(which.max(overall))
#empty_table %>% filter(interviewees == "Stu") %>% select(interviewees, tv_shows, overall) %>% top_n(n=1)
#It keeps grouping them and does one for each tv_shows. I just want one for Stu.
Stus_show %>% arrange(desc(overall)) %>% head(n=1) %>% select(tv_shows)## # A tibble: 1 × 1
## # Groups: tv_shows [1]
## tv_shows
## <chr>
## 1 Peaky BlindersLooks like Stu should watch Peaky Blinders!
How about Valerie?
Vals_show <- empty_table %>% filter(interviewees == "Stu") %>% arrange(desc(overall))
Vals_show %>% head(n=1) %>% select(tv_shows)## # A tibble: 1 × 1
## # Groups: tv_shows [1]
## tv_shows
## <chr>
## 1 Peaky BlindersThis is good to know as I’ve been trying to get her to watch Peaky Blinders!
I think we’ve hit the assignment core concept but for fun let’s join the tables to get a filled out dataframe
df$overall <-as.double(df$overall)
glimpse(df)## Rows: 60
## Columns: 7
## Groups: tv_shows [6]
## $ interviewees <chr> "Valerie", "Valerie", "Valerie", "Valerie", "V…
## $ tv_shows <chr> "Its Always Sunny In Philadelphia", "Gilmore G…
## $ overall <dbl> 3, 5, 3, NA, NA, NA, 5, 4, 4, 3, 5, NA, 5, 3, …
## $ user_avg <dbl> 3.666667, 3.666667, 3.666667, 3.666667, 3.6666…
## $ show_avg <dbl> 4.555556, 4.000000, 3.625000, 4.166667, 4.3333…
## $ user_avg_minus_avg_show <dbl> -0.335648148, -0.335648148, -0.335648148, -0.3…
## $ show_avg_minus_avg_show <dbl> 0.553240741, -0.002314815, -0.377314815, 0.164…joined_empty <- left_join(df, empty_table, by = c("interviewees","tv_shows","user_avg","show_avg","user_avg_minus_avg_show","show_avg_minus_avg_show"))
knitr::kable(joined_empty)| interviewees | tv_shows | overall.x | user_avg | show_avg | user_avg_minus_avg_show | show_avg_minus_avg_show | overall.y |
|---|---|---|---|---|---|---|---|
| Valerie | Its Always Sunny In Philadelphia | 3 | 3.666667 | 4.555556 | -0.3356481 | 0.5532407 | NA |
| Valerie | Gilmore Girls | 5 | 3.666667 | 4.000000 | -0.3356481 | -0.0023148 | NA |
| Valerie | Guys Grocery Games | 3 | 3.666667 | 3.625000 | -0.3356481 | -0.3773148 | NA |
| Valerie | Game of Thrones | NA | 3.666667 | 4.166667 | -0.3356481 | 0.1643519 | 3.831018 |
| Valerie | Peaky Blinders | NA | 3.666667 | 4.333333 | -0.3356481 | 0.3310185 | 3.997685 |
| Valerie | Love Island | NA | 3.666667 | 3.333333 | -0.3356481 | -0.6689815 | 2.997685 |
| Tyler | Its Always Sunny In Philadelphia | 5 | 4.200000 | 4.555556 | 0.1976852 | 0.5532407 | NA |
| Tyler | Game of Thrones | 4 | 4.200000 | 4.166667 | 0.1976852 | 0.1643519 | NA |
| Tyler | Peaky Blinders | 4 | 4.200000 | 4.333333 | 0.1976852 | 0.3310185 | NA |
| Tyler | Love Island | 3 | 4.200000 | 3.333333 | 0.1976852 | -0.6689815 | NA |
| Tyler | Guys Grocery Games | 5 | 4.200000 | 3.625000 | 0.1976852 | -0.3773148 | NA |
| Tyler | Gilmore Girls | NA | 4.200000 | 4.000000 | 0.1976852 | -0.0023148 | 4.197685 |
| Stu | Its Always Sunny In Philadelphia | 5 | 4.000000 | 4.555556 | -0.0023148 | 0.5532407 | NA |
| Stu | Guys Grocery Games | 3 | 4.000000 | 3.625000 | -0.0023148 | -0.3773148 | NA |
| Stu | Game of Thrones | NA | 4.000000 | 4.166667 | -0.0023148 | 0.1643519 | 4.164352 |
| Stu | Gilmore Girls | NA | 4.000000 | 4.000000 | -0.0023148 | -0.0023148 | 3.997685 |
| Stu | Peaky Blinders | NA | 4.000000 | 4.333333 | -0.0023148 | 0.3310185 | 4.331018 |
| Stu | Love Island | NA | 4.000000 | 3.333333 | -0.0023148 | -0.6689815 | 3.331018 |
| Skyler | Its Always Sunny In Philadelphia | 5 | 4.500000 | 4.555556 | 0.4976852 | 0.5532407 | NA |
| Skyler | Guys Grocery Games | 4 | 4.500000 | 3.625000 | 0.4976852 | -0.3773148 | NA |
| Skyler | Game of Thrones | NA | 4.500000 | 4.166667 | 0.4976852 | 0.1643519 | 4.664352 |
| Skyler | Gilmore Girls | NA | 4.500000 | 4.000000 | 0.4976852 | -0.0023148 | 4.497685 |
| Skyler | Peaky Blinders | NA | 4.500000 | 4.333333 | 0.4976852 | 0.3310185 | 4.831018 |
| Skyler | Love Island | NA | 4.500000 | 3.333333 | 0.4976852 | -0.6689815 | 3.831018 |
| Mackenzie | Its Always Sunny In Philadelphia | 5 | 4.000000 | 4.555556 | -0.0023148 | 0.5532407 | NA |
| Mackenzie | Game of Thrones | 4 | 4.000000 | 4.166667 | -0.0023148 | 0.1643519 | NA |
| Mackenzie | Gilmore Girls | 3 | 4.000000 | 4.000000 | -0.0023148 | -0.0023148 | NA |
| Mackenzie | Peaky Blinders | 4 | 4.000000 | 4.333333 | -0.0023148 | 0.3310185 | NA |
| Mackenzie | Love Island | 3 | 4.000000 | 3.333333 | -0.0023148 | -0.6689815 | NA |
| Mackenzie | Guys Grocery Games | 5 | 4.000000 | 3.625000 | -0.0023148 | -0.3773148 | NA |
| Josh | Its Always Sunny In Philadelphia | 4 | 4.333333 | 4.555556 | 0.3310185 | 0.5532407 | NA |
| Josh | Game of Thrones | 5 | 4.333333 | 4.166667 | 0.3310185 | 0.1643519 | NA |
| Josh | Love Island | 4 | 4.333333 | 3.333333 | 0.3310185 | -0.6689815 | NA |
| Josh | Gilmore Girls | NA | 4.333333 | 4.000000 | 0.3310185 | -0.0023148 | 4.331018 |
| Josh | Peaky Blinders | NA | 4.333333 | 4.333333 | 0.3310185 | 0.3310185 | 4.664352 |
| Josh | Guys Grocery Games | NA | 4.333333 | 3.625000 | 0.3310185 | -0.3773148 | 3.956018 |
| Jake | Its Always Sunny In Philadelphia | 5 | 4.000000 | 4.555556 | -0.0023148 | 0.5532407 | NA |
| Jake | Game of Thrones | 4 | 4.000000 | 4.166667 | -0.0023148 | 0.1643519 | NA |
| Jake | Guys Grocery Games | 3 | 4.000000 | 3.625000 | -0.0023148 | -0.3773148 | NA |
| Jake | Gilmore Girls | NA | 4.000000 | 4.000000 | -0.0023148 | -0.0023148 | 3.997685 |
| Jake | Peaky Blinders | NA | 4.000000 | 4.333333 | -0.0023148 | 0.3310185 | 4.331018 |
| Jake | Love Island | NA | 4.000000 | 3.333333 | -0.0023148 | -0.6689815 | 3.331018 |
| Crystal | Its Always Sunny In Philadelphia | 5 | 4.333333 | 4.555556 | 0.3310185 | 0.5532407 | NA |
| Crystal | Game of Thrones | 4 | 4.333333 | 4.166667 | 0.3310185 | 0.1643519 | NA |
| Crystal | Gilmore Girls | 4 | 4.333333 | 4.000000 | 0.3310185 | -0.0023148 | NA |
| Crystal | Guys Grocery Games | NA | 4.333333 | 3.625000 | 0.3310185 | -0.3773148 | 3.956018 |
| Crystal | Peaky Blinders | NA | 4.333333 | 4.333333 | 0.3310185 | 0.3310185 | 4.664352 |
| Crystal | Love Island | NA | 4.333333 | 3.333333 | 0.3310185 | -0.6689815 | 3.664352 |
| Caitlin | Its Always Sunny In Philadelphia | 4 | 4.000000 | 4.555556 | -0.0023148 | 0.5532407 | NA |
| Caitlin | Game of Thrones | 4 | 4.000000 | 4.166667 | -0.0023148 | 0.1643519 | NA |
| Caitlin | Peaky Blinders | 5 | 4.000000 | 4.333333 | -0.0023148 | 0.3310185 | NA |
| Caitlin | Guys Grocery Games | 3 | 4.000000 | 3.625000 | -0.0023148 | -0.3773148 | NA |
| Caitlin | Gilmore Girls | NA | 4.000000 | 4.000000 | -0.0023148 | -0.0023148 | 3.997685 |
| Caitlin | Love Island | NA | 4.000000 | 3.333333 | -0.0023148 | -0.6689815 | 3.331018 |
| Alexis | Guys Grocery Games | 3 | 3.000000 | 3.625000 | -1.0023148 | -0.3773148 | NA |
| Alexis | Its Always Sunny In Philadelphia | NA | 3.000000 | 4.555556 | -1.0023148 | 0.5532407 | 3.553241 |
| Alexis | Game of Thrones | NA | 3.000000 | 4.166667 | -1.0023148 | 0.1643519 | 3.164352 |
| Alexis | Gilmore Girls | NA | 3.000000 | 4.000000 | -1.0023148 | -0.0023148 | 2.997685 |
| Alexis | Peaky Blinders | NA | 3.000000 | 4.333333 | -1.0023148 | 0.3310185 | 3.331018 |
| Alexis | Love Island | NA | 3.000000 | 3.333333 | -1.0023148 | -0.6689815 | 2.331018 |
I really don’t understand why there is overall.x and overall.y but if I include “overall” in the “by =” argument then the values from empty_table disappear. I made sure both are the same type, not sure why it’s not combining into one column.
Try a full join?
joined_empty <- full_join(df, empty_table) %>% group_by(interviewees,tv_shows)## Joining with `by = join_by(interviewees, tv_shows, overall, user_avg, show_avg,
## user_avg_minus_avg_show, show_avg_minus_avg_show)`knitr::kable(joined_empty)| interviewees | tv_shows | overall | user_avg | show_avg | user_avg_minus_avg_show | show_avg_minus_avg_show |
|---|---|---|---|---|---|---|
| Valerie | Its Always Sunny In Philadelphia | 3.000000 | 3.666667 | 4.555556 | -0.3356481 | 0.5532407 |
| Valerie | Gilmore Girls | 5.000000 | 3.666667 | 4.000000 | -0.3356481 | -0.0023148 |
| Valerie | Guys Grocery Games | 3.000000 | 3.666667 | 3.625000 | -0.3356481 | -0.3773148 |
| Valerie | Game of Thrones | NA | 3.666667 | 4.166667 | -0.3356481 | 0.1643519 |
| Valerie | Peaky Blinders | NA | 3.666667 | 4.333333 | -0.3356481 | 0.3310185 |
| Valerie | Love Island | NA | 3.666667 | 3.333333 | -0.3356481 | -0.6689815 |
| Tyler | Its Always Sunny In Philadelphia | 5.000000 | 4.200000 | 4.555556 | 0.1976852 | 0.5532407 |
| Tyler | Game of Thrones | 4.000000 | 4.200000 | 4.166667 | 0.1976852 | 0.1643519 |
| Tyler | Peaky Blinders | 4.000000 | 4.200000 | 4.333333 | 0.1976852 | 0.3310185 |
| Tyler | Love Island | 3.000000 | 4.200000 | 3.333333 | 0.1976852 | -0.6689815 |
| Tyler | Guys Grocery Games | 5.000000 | 4.200000 | 3.625000 | 0.1976852 | -0.3773148 |
| Tyler | Gilmore Girls | NA | 4.200000 | 4.000000 | 0.1976852 | -0.0023148 |
| Stu | Its Always Sunny In Philadelphia | 5.000000 | 4.000000 | 4.555556 | -0.0023148 | 0.5532407 |
| Stu | Guys Grocery Games | 3.000000 | 4.000000 | 3.625000 | -0.0023148 | -0.3773148 |
| Stu | Game of Thrones | NA | 4.000000 | 4.166667 | -0.0023148 | 0.1643519 |
| Stu | Gilmore Girls | NA | 4.000000 | 4.000000 | -0.0023148 | -0.0023148 |
| Stu | Peaky Blinders | NA | 4.000000 | 4.333333 | -0.0023148 | 0.3310185 |
| Stu | Love Island | NA | 4.000000 | 3.333333 | -0.0023148 | -0.6689815 |
| Skyler | Its Always Sunny In Philadelphia | 5.000000 | 4.500000 | 4.555556 | 0.4976852 | 0.5532407 |
| Skyler | Guys Grocery Games | 4.000000 | 4.500000 | 3.625000 | 0.4976852 | -0.3773148 |
| Skyler | Game of Thrones | NA | 4.500000 | 4.166667 | 0.4976852 | 0.1643519 |
| Skyler | Gilmore Girls | NA | 4.500000 | 4.000000 | 0.4976852 | -0.0023148 |
| Skyler | Peaky Blinders | NA | 4.500000 | 4.333333 | 0.4976852 | 0.3310185 |
| Skyler | Love Island | NA | 4.500000 | 3.333333 | 0.4976852 | -0.6689815 |
| Mackenzie | Its Always Sunny In Philadelphia | 5.000000 | 4.000000 | 4.555556 | -0.0023148 | 0.5532407 |
| Mackenzie | Game of Thrones | 4.000000 | 4.000000 | 4.166667 | -0.0023148 | 0.1643519 |
| Mackenzie | Gilmore Girls | 3.000000 | 4.000000 | 4.000000 | -0.0023148 | -0.0023148 |
| Mackenzie | Peaky Blinders | 4.000000 | 4.000000 | 4.333333 | -0.0023148 | 0.3310185 |
| Mackenzie | Love Island | 3.000000 | 4.000000 | 3.333333 | -0.0023148 | -0.6689815 |
| Mackenzie | Guys Grocery Games | 5.000000 | 4.000000 | 3.625000 | -0.0023148 | -0.3773148 |
| Josh | Its Always Sunny In Philadelphia | 4.000000 | 4.333333 | 4.555556 | 0.3310185 | 0.5532407 |
| Josh | Game of Thrones | 5.000000 | 4.333333 | 4.166667 | 0.3310185 | 0.1643519 |
| Josh | Love Island | 4.000000 | 4.333333 | 3.333333 | 0.3310185 | -0.6689815 |
| Josh | Gilmore Girls | NA | 4.333333 | 4.000000 | 0.3310185 | -0.0023148 |
| Josh | Peaky Blinders | NA | 4.333333 | 4.333333 | 0.3310185 | 0.3310185 |
| Josh | Guys Grocery Games | NA | 4.333333 | 3.625000 | 0.3310185 | -0.3773148 |
| Jake | Its Always Sunny In Philadelphia | 5.000000 | 4.000000 | 4.555556 | -0.0023148 | 0.5532407 |
| Jake | Game of Thrones | 4.000000 | 4.000000 | 4.166667 | -0.0023148 | 0.1643519 |
| Jake | Guys Grocery Games | 3.000000 | 4.000000 | 3.625000 | -0.0023148 | -0.3773148 |
| Jake | Gilmore Girls | NA | 4.000000 | 4.000000 | -0.0023148 | -0.0023148 |
| Jake | Peaky Blinders | NA | 4.000000 | 4.333333 | -0.0023148 | 0.3310185 |
| Jake | Love Island | NA | 4.000000 | 3.333333 | -0.0023148 | -0.6689815 |
| Crystal | Its Always Sunny In Philadelphia | 5.000000 | 4.333333 | 4.555556 | 0.3310185 | 0.5532407 |
| Crystal | Game of Thrones | 4.000000 | 4.333333 | 4.166667 | 0.3310185 | 0.1643519 |
| Crystal | Gilmore Girls | 4.000000 | 4.333333 | 4.000000 | 0.3310185 | -0.0023148 |
| Crystal | Guys Grocery Games | NA | 4.333333 | 3.625000 | 0.3310185 | -0.3773148 |
| Crystal | Peaky Blinders | NA | 4.333333 | 4.333333 | 0.3310185 | 0.3310185 |
| Crystal | Love Island | NA | 4.333333 | 3.333333 | 0.3310185 | -0.6689815 |
| Caitlin | Its Always Sunny In Philadelphia | 4.000000 | 4.000000 | 4.555556 | -0.0023148 | 0.5532407 |
| Caitlin | Game of Thrones | 4.000000 | 4.000000 | 4.166667 | -0.0023148 | 0.1643519 |
| Caitlin | Peaky Blinders | 5.000000 | 4.000000 | 4.333333 | -0.0023148 | 0.3310185 |
| Caitlin | Guys Grocery Games | 3.000000 | 4.000000 | 3.625000 | -0.0023148 | -0.3773148 |
| Caitlin | Gilmore Girls | NA | 4.000000 | 4.000000 | -0.0023148 | -0.0023148 |
| Caitlin | Love Island | NA | 4.000000 | 3.333333 | -0.0023148 | -0.6689815 |
| Alexis | Guys Grocery Games | 3.000000 | 3.000000 | 3.625000 | -1.0023148 | -0.3773148 |
| Alexis | Its Always Sunny In Philadelphia | NA | 3.000000 | 4.555556 | -1.0023148 | 0.5532407 |
| Alexis | Game of Thrones | NA | 3.000000 | 4.166667 | -1.0023148 | 0.1643519 |
| Alexis | Gilmore Girls | NA | 3.000000 | 4.000000 | -1.0023148 | -0.0023148 |
| Alexis | Peaky Blinders | NA | 3.000000 | 4.333333 | -1.0023148 | 0.3310185 |
| Alexis | Love Island | NA | 3.000000 | 3.333333 | -1.0023148 | -0.6689815 |
| Valerie | Game of Thrones | 3.831018 | 3.666667 | 4.166667 | -0.3356481 | 0.1643519 |
| Valerie | Peaky Blinders | 3.997685 | 3.666667 | 4.333333 | -0.3356481 | 0.3310185 |
| Valerie | Love Island | 2.997685 | 3.666667 | 3.333333 | -0.3356481 | -0.6689815 |
| Tyler | Gilmore Girls | 4.197685 | 4.200000 | 4.000000 | 0.1976852 | -0.0023148 |
| Stu | Game of Thrones | 4.164352 | 4.000000 | 4.166667 | -0.0023148 | 0.1643519 |
| Stu | Gilmore Girls | 3.997685 | 4.000000 | 4.000000 | -0.0023148 | -0.0023148 |
| Stu | Peaky Blinders | 4.331018 | 4.000000 | 4.333333 | -0.0023148 | 0.3310185 |
| Stu | Love Island | 3.331018 | 4.000000 | 3.333333 | -0.0023148 | -0.6689815 |
| Skyler | Game of Thrones | 4.664352 | 4.500000 | 4.166667 | 0.4976852 | 0.1643519 |
| Skyler | Gilmore Girls | 4.497685 | 4.500000 | 4.000000 | 0.4976852 | -0.0023148 |
| Skyler | Peaky Blinders | 4.831018 | 4.500000 | 4.333333 | 0.4976852 | 0.3310185 |
| Skyler | Love Island | 3.831018 | 4.500000 | 3.333333 | 0.4976852 | -0.6689815 |
| Josh | Gilmore Girls | 4.331018 | 4.333333 | 4.000000 | 0.3310185 | -0.0023148 |
| Josh | Peaky Blinders | 4.664352 | 4.333333 | 4.333333 | 0.3310185 | 0.3310185 |
| Josh | Guys Grocery Games | 3.956018 | 4.333333 | 3.625000 | 0.3310185 | -0.3773148 |
| Jake | Gilmore Girls | 3.997685 | 4.000000 | 4.000000 | -0.0023148 | -0.0023148 |
| Jake | Peaky Blinders | 4.331018 | 4.000000 | 4.333333 | -0.0023148 | 0.3310185 |
| Jake | Love Island | 3.331018 | 4.000000 | 3.333333 | -0.0023148 | -0.6689815 |
| Crystal | Guys Grocery Games | 3.956018 | 4.333333 | 3.625000 | 0.3310185 | -0.3773148 |
| Crystal | Peaky Blinders | 4.664352 | 4.333333 | 4.333333 | 0.3310185 | 0.3310185 |
| Crystal | Love Island | 3.664352 | 4.333333 | 3.333333 | 0.3310185 | -0.6689815 |
| Caitlin | Gilmore Girls | 3.997685 | 4.000000 | 4.000000 | -0.0023148 | -0.0023148 |
| Caitlin | Love Island | 3.331018 | 4.000000 | 3.333333 | -0.0023148 | -0.6689815 |
| Alexis | Its Always Sunny In Philadelphia | 3.553241 | 3.000000 | 4.555556 | -1.0023148 | 0.5532407 |
| Alexis | Game of Thrones | 3.164352 | 3.000000 | 4.166667 | -1.0023148 | 0.1643519 |
| Alexis | Gilmore Girls | 2.997685 | 3.000000 | 4.000000 | -1.0023148 | -0.0023148 |
| Alexis | Peaky Blinders | 3.331018 | 3.000000 | 4.333333 | -1.0023148 | 0.3310185 |
| Alexis | Love Island | 2.331018 | 3.000000 | 3.333333 | -1.0023148 | -0.6689815 |
#maybe rbind?
joined_empty <- rbind(df,empty_table)
knitr::kable(joined_empty)| interviewees | tv_shows | overall | user_avg | show_avg | user_avg_minus_avg_show | show_avg_minus_avg_show |
|---|---|---|---|---|---|---|
| Valerie | Its Always Sunny In Philadelphia | 3.000000 | 3.666667 | 4.555556 | -0.3356481 | 0.5532407 |
| Valerie | Gilmore Girls | 5.000000 | 3.666667 | 4.000000 | -0.3356481 | -0.0023148 |
| Valerie | Guys Grocery Games | 3.000000 | 3.666667 | 3.625000 | -0.3356481 | -0.3773148 |
| Valerie | Game of Thrones | NA | 3.666667 | 4.166667 | -0.3356481 | 0.1643519 |
| Valerie | Peaky Blinders | NA | 3.666667 | 4.333333 | -0.3356481 | 0.3310185 |
| Valerie | Love Island | NA | 3.666667 | 3.333333 | -0.3356481 | -0.6689815 |
| Tyler | Its Always Sunny In Philadelphia | 5.000000 | 4.200000 | 4.555556 | 0.1976852 | 0.5532407 |
| Tyler | Game of Thrones | 4.000000 | 4.200000 | 4.166667 | 0.1976852 | 0.1643519 |
| Tyler | Peaky Blinders | 4.000000 | 4.200000 | 4.333333 | 0.1976852 | 0.3310185 |
| Tyler | Love Island | 3.000000 | 4.200000 | 3.333333 | 0.1976852 | -0.6689815 |
| Tyler | Guys Grocery Games | 5.000000 | 4.200000 | 3.625000 | 0.1976852 | -0.3773148 |
| Tyler | Gilmore Girls | NA | 4.200000 | 4.000000 | 0.1976852 | -0.0023148 |
| Stu | Its Always Sunny In Philadelphia | 5.000000 | 4.000000 | 4.555556 | -0.0023148 | 0.5532407 |
| Stu | Guys Grocery Games | 3.000000 | 4.000000 | 3.625000 | -0.0023148 | -0.3773148 |
| Stu | Game of Thrones | NA | 4.000000 | 4.166667 | -0.0023148 | 0.1643519 |
| Stu | Gilmore Girls | NA | 4.000000 | 4.000000 | -0.0023148 | -0.0023148 |
| Stu | Peaky Blinders | NA | 4.000000 | 4.333333 | -0.0023148 | 0.3310185 |
| Stu | Love Island | NA | 4.000000 | 3.333333 | -0.0023148 | -0.6689815 |
| Skyler | Its Always Sunny In Philadelphia | 5.000000 | 4.500000 | 4.555556 | 0.4976852 | 0.5532407 |
| Skyler | Guys Grocery Games | 4.000000 | 4.500000 | 3.625000 | 0.4976852 | -0.3773148 |
| Skyler | Game of Thrones | NA | 4.500000 | 4.166667 | 0.4976852 | 0.1643519 |
| Skyler | Gilmore Girls | NA | 4.500000 | 4.000000 | 0.4976852 | -0.0023148 |
| Skyler | Peaky Blinders | NA | 4.500000 | 4.333333 | 0.4976852 | 0.3310185 |
| Skyler | Love Island | NA | 4.500000 | 3.333333 | 0.4976852 | -0.6689815 |
| Mackenzie | Its Always Sunny In Philadelphia | 5.000000 | 4.000000 | 4.555556 | -0.0023148 | 0.5532407 |
| Mackenzie | Game of Thrones | 4.000000 | 4.000000 | 4.166667 | -0.0023148 | 0.1643519 |
| Mackenzie | Gilmore Girls | 3.000000 | 4.000000 | 4.000000 | -0.0023148 | -0.0023148 |
| Mackenzie | Peaky Blinders | 4.000000 | 4.000000 | 4.333333 | -0.0023148 | 0.3310185 |
| Mackenzie | Love Island | 3.000000 | 4.000000 | 3.333333 | -0.0023148 | -0.6689815 |
| Mackenzie | Guys Grocery Games | 5.000000 | 4.000000 | 3.625000 | -0.0023148 | -0.3773148 |
| Josh | Its Always Sunny In Philadelphia | 4.000000 | 4.333333 | 4.555556 | 0.3310185 | 0.5532407 |
| Josh | Game of Thrones | 5.000000 | 4.333333 | 4.166667 | 0.3310185 | 0.1643519 |
| Josh | Love Island | 4.000000 | 4.333333 | 3.333333 | 0.3310185 | -0.6689815 |
| Josh | Gilmore Girls | NA | 4.333333 | 4.000000 | 0.3310185 | -0.0023148 |
| Josh | Peaky Blinders | NA | 4.333333 | 4.333333 | 0.3310185 | 0.3310185 |
| Josh | Guys Grocery Games | NA | 4.333333 | 3.625000 | 0.3310185 | -0.3773148 |
| Jake | Its Always Sunny In Philadelphia | 5.000000 | 4.000000 | 4.555556 | -0.0023148 | 0.5532407 |
| Jake | Game of Thrones | 4.000000 | 4.000000 | 4.166667 | -0.0023148 | 0.1643519 |
| Jake | Guys Grocery Games | 3.000000 | 4.000000 | 3.625000 | -0.0023148 | -0.3773148 |
| Jake | Gilmore Girls | NA | 4.000000 | 4.000000 | -0.0023148 | -0.0023148 |
| Jake | Peaky Blinders | NA | 4.000000 | 4.333333 | -0.0023148 | 0.3310185 |
| Jake | Love Island | NA | 4.000000 | 3.333333 | -0.0023148 | -0.6689815 |
| Crystal | Its Always Sunny In Philadelphia | 5.000000 | 4.333333 | 4.555556 | 0.3310185 | 0.5532407 |
| Crystal | Game of Thrones | 4.000000 | 4.333333 | 4.166667 | 0.3310185 | 0.1643519 |
| Crystal | Gilmore Girls | 4.000000 | 4.333333 | 4.000000 | 0.3310185 | -0.0023148 |
| Crystal | Guys Grocery Games | NA | 4.333333 | 3.625000 | 0.3310185 | -0.3773148 |
| Crystal | Peaky Blinders | NA | 4.333333 | 4.333333 | 0.3310185 | 0.3310185 |
| Crystal | Love Island | NA | 4.333333 | 3.333333 | 0.3310185 | -0.6689815 |
| Caitlin | Its Always Sunny In Philadelphia | 4.000000 | 4.000000 | 4.555556 | -0.0023148 | 0.5532407 |
| Caitlin | Game of Thrones | 4.000000 | 4.000000 | 4.166667 | -0.0023148 | 0.1643519 |
| Caitlin | Peaky Blinders | 5.000000 | 4.000000 | 4.333333 | -0.0023148 | 0.3310185 |
| Caitlin | Guys Grocery Games | 3.000000 | 4.000000 | 3.625000 | -0.0023148 | -0.3773148 |
| Caitlin | Gilmore Girls | NA | 4.000000 | 4.000000 | -0.0023148 | -0.0023148 |
| Caitlin | Love Island | NA | 4.000000 | 3.333333 | -0.0023148 | -0.6689815 |
| Alexis | Guys Grocery Games | 3.000000 | 3.000000 | 3.625000 | -1.0023148 | -0.3773148 |
| Alexis | Its Always Sunny In Philadelphia | NA | 3.000000 | 4.555556 | -1.0023148 | 0.5532407 |
| Alexis | Game of Thrones | NA | 3.000000 | 4.166667 | -1.0023148 | 0.1643519 |
| Alexis | Gilmore Girls | NA | 3.000000 | 4.000000 | -1.0023148 | -0.0023148 |
| Alexis | Peaky Blinders | NA | 3.000000 | 4.333333 | -1.0023148 | 0.3310185 |
| Alexis | Love Island | NA | 3.000000 | 3.333333 | -1.0023148 | -0.6689815 |
| Valerie | Game of Thrones | 3.831018 | 3.666667 | 4.166667 | -0.3356481 | 0.1643519 |
| Valerie | Peaky Blinders | 3.997685 | 3.666667 | 4.333333 | -0.3356481 | 0.3310185 |
| Valerie | Love Island | 2.997685 | 3.666667 | 3.333333 | -0.3356481 | -0.6689815 |
| Tyler | Gilmore Girls | 4.197685 | 4.200000 | 4.000000 | 0.1976852 | -0.0023148 |
| Stu | Game of Thrones | 4.164352 | 4.000000 | 4.166667 | -0.0023148 | 0.1643519 |
| Stu | Gilmore Girls | 3.997685 | 4.000000 | 4.000000 | -0.0023148 | -0.0023148 |
| Stu | Peaky Blinders | 4.331018 | 4.000000 | 4.333333 | -0.0023148 | 0.3310185 |
| Stu | Love Island | 3.331018 | 4.000000 | 3.333333 | -0.0023148 | -0.6689815 |
| Skyler | Game of Thrones | 4.664352 | 4.500000 | 4.166667 | 0.4976852 | 0.1643519 |
| Skyler | Gilmore Girls | 4.497685 | 4.500000 | 4.000000 | 0.4976852 | -0.0023148 |
| Skyler | Peaky Blinders | 4.831018 | 4.500000 | 4.333333 | 0.4976852 | 0.3310185 |
| Skyler | Love Island | 3.831018 | 4.500000 | 3.333333 | 0.4976852 | -0.6689815 |
| Josh | Gilmore Girls | 4.331018 | 4.333333 | 4.000000 | 0.3310185 | -0.0023148 |
| Josh | Peaky Blinders | 4.664352 | 4.333333 | 4.333333 | 0.3310185 | 0.3310185 |
| Josh | Guys Grocery Games | 3.956018 | 4.333333 | 3.625000 | 0.3310185 | -0.3773148 |
| Jake | Gilmore Girls | 3.997685 | 4.000000 | 4.000000 | -0.0023148 | -0.0023148 |
| Jake | Peaky Blinders | 4.331018 | 4.000000 | 4.333333 | -0.0023148 | 0.3310185 |
| Jake | Love Island | 3.331018 | 4.000000 | 3.333333 | -0.0023148 | -0.6689815 |
| Crystal | Guys Grocery Games | 3.956018 | 4.333333 | 3.625000 | 0.3310185 | -0.3773148 |
| Crystal | Peaky Blinders | 4.664352 | 4.333333 | 4.333333 | 0.3310185 | 0.3310185 |
| Crystal | Love Island | 3.664352 | 4.333333 | 3.333333 | 0.3310185 | -0.6689815 |
| Caitlin | Gilmore Girls | 3.997685 | 4.000000 | 4.000000 | -0.0023148 | -0.0023148 |
| Caitlin | Love Island | 3.331018 | 4.000000 | 3.333333 | -0.0023148 | -0.6689815 |
| Alexis | Its Always Sunny In Philadelphia | 3.553241 | 3.000000 | 4.555556 | -1.0023148 | 0.5532407 |
| Alexis | Game of Thrones | 3.164352 | 3.000000 | 4.166667 | -1.0023148 | 0.1643519 |
| Alexis | Gilmore Girls | 2.997685 | 3.000000 | 4.000000 | -1.0023148 | -0.0023148 |
| Alexis | Peaky Blinders | 3.331018 | 3.000000 | 4.333333 | -1.0023148 | 0.3310185 |
| Alexis | Love Island | 2.331018 | 3.000000 | 3.333333 | -1.0023148 | -0.6689815 |
Neither work, they just put the empty_table values at the bottom. Kinda lost on this aspect.