This project will examine rating data for the first six episodes of the television series “Gibi”. Ratings will be collected from five users using a 1-5 scale and organized into a dataset that can be stored and analyzed using SQL and R.
First, rating data will be collected from five users for the first six episodes of “Gibi”. Users will rate only the episodes they have watched. If a user has not watched an episode, the rating will be recorded as “NULL” in the SQL database. The collected data will then be stored in a PostgreSQL database using separate tables for users, episodes, and ratings. Finally, the data will be loaded into R as a dataframe, where the number of ratings and average rating will be calculated for each episode. Missing ratings will be excluded from the average-rating calculations.
One anticipated challenge is ensuring that the data are transferred correctly from the SQL database into R as a dataframe. The SQL query will need to retrieve the correct user, episode, and rating information, while missing values will need to be handled appropriately in R.
The analysis will use “DBI” and “RPostgres” to connect R to the PostgreSQL database. The “dplyr” package will be used to inspect and summarize the data after it is loaded into R.
library(DBI)
library(RPostgres)
library(dplyr)
Attaching package: 'dplyr'The following objects are masked from 'package:stats':
filter, lagThe following objects are masked from 'package:base':
intersect, setdiff, setequal, unionlibrary(rstudioapi)
library(ggplot2)The database tables were created in PostgreSQL using SQL. Separate tables were used for users, episodes, and ratings to organize the data and establish relationships between participants, episodes, and ratings.
CREATE TABLE users (
user_id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL
);CREATE TABLE episodes (
episode_id SERIAL PRIMARY KEY,
episode_number INTEGER NOT NULL,
title VARCHAR(100) NOT NULL
);CREATE TABLE ratings (
rating_id SERIAL PRIMARY KEY,
user_id INTEGER NOT NULL REFERENCES users(user_id),
episode_id INTEGER NOT NULL REFERENCES episodes(episode_id),
rating INTEGER CHECK (rating >= 1 AND rating <= 5)
);INSERT INTO users (name)
VALUES
('Serpil'),
('Koray'),
('Okan'),
('Deniz'),
('Pelin');SELECT * FROM users;INSERT INTO episodes (episode_number, title)
VALUES
(1, 'Kokaric'),
(2, 'Vatka'),
(3, 'Nu Model'),
(4, 'Erasmusla GY'),
(5, 'Yanlis Mentor'),
(6, 'Karanlik Guc');SELECT * FROM episodes;INSERT INTO ratings (user_id, episode_id, rating)
VALUES
(1, 1, 5),
(1, 2, 4),
(1, 3, 4),
(1, 4, 5),
(1, 5, NULL),
(1, 6, 5),
(2, 1, 5),
(2, 2, 5),
(2, 3, 4),
(2, 4, 5),
(2, 5, 3),
(2, 6, 5),
(3, 1, 5),
(3, 2, 4),
(3, 3, NULL),
(3, 4, 5),
(3, 5, 3),
(3, 6, 5),
(4, 1, 5),
(4, 2, 4),
(4, 3, NULL),
(4, 4, 5),
(4, 5, NULL),
(4, 6, 4),
(5, 1, 4),
(5, 2, 5),
(5, 3, 4),
(5, 4, 5),
(5, 5, 3),
(5, 6, 5);SELECT * FROM ratings;SELECT *
FROM ratings
WHERE rating IS NULL;SELECT
u.name,
e.episode_number,
e.title,
r.rating
FROM ratings r
JOIN users u ON r.user_id = u.user_id
JOIN episodes e ON r.episode_id = e.episode_id
ORDER BY e.episode_number, u.name;This section establishes a connection between R and the PostgreSQL database. The connection allows the analysis to access the stored users, episodes, and ratings data directly from R.
con <- dbConnect(
RPostgres::Postgres(),
dbname = "DATA607",
host = "localhost",
port = 5432,
user = "postgres",
password = rstudioapi::askForPassword("Enter PostgreSQL password")
)The PostgreSQL connection was used to create and retrieve the data during development. The connection code is included to show how the data were accessed, but it is not run during rendering because it requires a local database and password. The data are also saved as a CSV file on GitHub so the analysis can be reproduced without the local database.
This section retrieves the rating data from the PostgreSQL database and loads it into R as a dataframe. The SQL query combines the users, episodes, and ratings tables using joins so that each rating is associated with the corresponding user and episode.
gibi_ratings <- dbGetQuery(
con, "
select
u.name,
e.episode_number,
e.title,
r.rating
from ratings r
join users u on r.user_id = u.user_id
join episodes e on r.episode_id = e.episode_id
order by e.episode_number, u.name;
"
)
gibi_ratingsThe PostgreSQL query was used during development to retrieve the rating data by joining the users, episodes, and ratings tables. It is not evaluated during rendering because the local PostgreSQL database is not available in the rendering environment. The same data are loaded from the GitHub CSV file for the reproducible analysis.
The rating data are also available as a CSV file in the GitHub repository. This allows the analysis to be reproduced without requiring access to the local PostgreSQL database.
gibi_ratings <- read.csv("https://raw.githubusercontent.com/OzgeG01/DATA607-Assignment/refs/heads/main/gibi_ratings.csv")
gibi_ratings name episode_number title rating
1 Deniz 1 Kokaric 5
2 Koray 1 Kokaric 5
3 Okan 1 Kokaric 5
4 Pelin 1 Kokaric 4
5 Serpil 1 Kokaric 5
6 Deniz 2 Vatka 4
7 Koray 2 Vatka 5
8 Okan 2 Vatka 4
9 Pelin 2 Vatka 5
10 Serpil 2 Vatka 4
11 Deniz 3 Nu Model NA
12 Koray 3 Nu Model 4
13 Okan 3 Nu Model NA
14 Pelin 3 Nu Model 4
15 Serpil 3 Nu Model 4
16 Deniz 4 Erasmusla GY 5
17 Koray 4 Erasmusla GY 5
18 Okan 4 Erasmusla GY 5
19 Pelin 4 Erasmusla GY 5
20 Serpil 4 Erasmusla GY 5
21 Deniz 5 Yanlis Mentor NA
22 Koray 5 Yanlis Mentor 3
23 Okan 5 Yanlis Mentor 3
24 Pelin 5 Yanlis Mentor 3
25 Serpil 5 Yanlis Mentor NA
26 Deniz 6 Karanlik Guc 4
27 Koray 6 Karanlik Guc 5
28 Okan 6 Karanlik Guc 5
29 Pelin 6 Karanlik Guc 5
30 Serpil 6 Karanlik Guc 5This section checks the structure of the dataframe to confirm the number of observations and variables and to verify that the data were imported with the expected data types.
str(gibi_ratings)'data.frame': 30 obs. of 4 variables:
$ name : chr "Deniz" "Koray" "Okan" "Pelin" ...
$ episode_number: int 1 1 1 1 1 2 2 2 2 2 ...
$ title : chr "Kokaric" "Kokaric" "Kokaric" "Kokaric" ...
$ rating : int 5 5 5 4 5 4 5 4 5 4 ...This section checks the dataframe for missing rating values. Missing ratings represent episodes that a participant did not rate, so they are excluded when calculating rating counts and average ratings.
sum(is.na(gibi_ratings$rating))[1] 4missing_data <- gibi_ratings %>%
filter(is.na(rating))
missing_data name episode_number title rating
1 Deniz 3 Nu Model NA
2 Okan 3 Nu Model NA
3 Deniz 5 Yanlis Mentor NA
4 Serpil 5 Yanlis Mentor NAThis section counts the number of valid ratings provided by each user. Missing ratings are excluded from the count because they represent episodes that a user did not rate.
ratings_per_user <- gibi_ratings %>%
group_by(name) %>%
summarise(
number_of_ratings = sum(!is.na(rating)),
.groups = "drop"
)
ratings_per_user# A tibble: 5 × 2
name number_of_ratings
<chr> <int>
1 Deniz 4
2 Koray 6
3 Okan 5
4 Pelin 6
5 Serpil 5This section counts the number of valid ratings received by each episode. Missing ratings are excluded from the count.
ratings_per_episode <- gibi_ratings %>%
group_by(episode_number, title) %>%
summarise(
number_of_ratings = sum(!is.na(rating)),
.groups = "drop"
)
ratings_per_episode# A tibble: 6 × 3
episode_number title number_of_ratings
<int> <chr> <int>
1 1 Kokaric 5
2 2 Vatka 5
3 3 Nu Model 3
4 4 Erasmusla GY 5
5 5 Yanlis Mentor 3
6 6 Karanlik Guc 5This section calculates the average rating for each episode. Missing ratings are excluded from the calculation.
average_ratings <- gibi_ratings %>%
group_by(episode_number, title) %>%
summarise(
average_rating = mean(rating, na.rm = TRUE),
.groups = "drop"
) %>%
arrange(desc(average_rating))
average_ratings# A tibble: 6 × 3
episode_number title average_rating
<int> <chr> <dbl>
1 4 Erasmusla GY 5
2 1 Kokaric 4.8
3 6 Karanlik Guc 4.8
4 2 Vatka 4.4
5 3 Nu Model 4
6 5 Yanlis Mentor 3 The average ratings can be compared using the following bar chart.
ggplot(average_ratings, aes(x = reorder(title, average_rating),
y = average_rating)) +
geom_col() +
coord_flip() +
labs(
title = "Average Ratings by Gibi Episode",
x = "Episode",
y = "Average Rating"
) +
theme_minimal()
The analysis successfully loaded the “Gibi” episode ratings from PostgreSQL into R and summarized the available ratings by participant and episode. Missing ratings were identified and excluded from the rating counts and average rating calculations.
The results show that “Erasmusla GY” received the highest average rating of 5.0, while “Yanlis Mentor” received the lowest average rating of 3.0. “Kokaric” and “Karanlik Guc” both received an average rating of 4.8, followed by “Vatka” with 4.4 and “Nu Model” with 4.0.
As a recommendation, the analysis could be extended by collecting ratings from more participants to make the results more representative. Future analysis could also compare individual participant preferences and examine whether the episode rankings change as additional ratings are collected.