WK2A

Quarto

Quarto enables you to weave together content and executable code into a finished document. To learn more about Quarto see https://quarto.org.

Running Code

When you click the Render button a document will be generated that includes both content and the output of embedded code. You can embed code like this:

# ============================================================
# WEEK 2A: SQL AND R - MOVIE RATINGS
# ============================================================

# ------------------------------------------------------------
# 1. LOAD REQUIRED PACKAGES
# ------------------------------------------------------------

library(DBI)
library(RSQLite)
library(dplyr)

Attaching package: 'dplyr'

The following objects are masked from 'package:stats':

    filter, lag

The following objects are masked from 'package:base':

    intersect, setdiff, setequal, union

library(tidyr)
library(ggplot2)

print("All required packages loaded successfully.")

[1] "All required packages loaded successfully."

# ------------------------------------------------------------
# 2. CREATE / CONNECT TO SQLITE DATABASE
# ------------------------------------------------------------

con <- dbConnect(
  SQLite(),
  dbname = "week2a_movie_ratings.sqlite"
)

print("Database connection created.")

[1] "Database connection created."

print(con)

<SQLiteConnection>
  Path: C:\Users\Reggie\OneDrive\Documentos\WK2A\week2a_movie_ratings.sqlite
  Extensions: TRUE

print("Working directory:")

[1] "Working directory:"

print(getwd())

[1] "C:/Users/Reggie/OneDrive/Documentos/WK2A"

# ------------------------------------------------------------
# 3. TURN ON FOREIGN KEYS
# ------------------------------------------------------------

dbExecute(
  con,
  "PRAGMA foreign_keys = ON;"
)

[1] 0

print("Foreign keys enabled.")

[1] "Foreign keys enabled."

print(
  dbGetQuery(
    con,
    "PRAGMA foreign_keys;"
  )
)

  foreign_keys
1            1

# ------------------------------------------------------------
# 4. REMOVE OLD TABLES
# This makes the script reproducible when rerun.
# ------------------------------------------------------------

dbExecute(con, "DROP TABLE IF EXISTS ratings;")

[1] 0

dbExecute(con, "DROP TABLE IF EXISTS movies;")

[1] 0

dbExecute(con, "DROP TABLE IF EXISTS critics;")

[1] 0

print("Old tables removed if they existed.")

[1] "Old tables removed if they existed."

print("Current tables:")

[1] "Current tables:"

print(dbListTables(con))

character(0)

# ------------------------------------------------------------
# 5. CREATE CRITICS TABLE
# ------------------------------------------------------------

result_critics <- dbExecute(con, "
CREATE TABLE critics (
    critic_id INTEGER PRIMARY KEY,
    critic_name TEXT NOT NULL
);
")

print("Critics table created.")

[1] "Critics table created."

print(result_critics)

[1] 0

print(dbListTables(con))

[1] "critics"

# ------------------------------------------------------------
# 6. CREATE MOVIES TABLE
# ------------------------------------------------------------

result_movies <- dbExecute(con, "
CREATE TABLE movies (
    movie_id INTEGER PRIMARY KEY,
    title TEXT NOT NULL
);
")

print("Movies table created.")

[1] "Movies table created."

print(result_movies)

[1] 0

print(dbListTables(con))

[1] "critics" "movies" 

# ------------------------------------------------------------
# 7. CREATE RATINGS TABLE
# ------------------------------------------------------------

result_ratings <- dbExecute(con, "
CREATE TABLE ratings (
    critic_id INTEGER NOT NULL,
    movie_id INTEGER NOT NULL,
    rating INTEGER,

    PRIMARY KEY (critic_id, movie_id),

    FOREIGN KEY (critic_id)
        REFERENCES critics(critic_id),

    FOREIGN KEY (movie_id)
        REFERENCES movies(movie_id)
);
")

print("Ratings table created.")

[1] "Ratings table created."

print(result_ratings)

[1] 0

print("All database tables:")

[1] "All database tables:"

print(dbListTables(con))

[1] "critics" "movies"  "ratings"

# ------------------------------------------------------------
# 8. PRINT TABLE STRUCTURES
# ------------------------------------------------------------

print("STRUCTURE OF CRITICS TABLE")

[1] "STRUCTURE OF CRITICS TABLE"

print(
  dbGetQuery(
    con,
    "PRAGMA table_info(critics);"
  )
)

  cid        name    type notnull dflt_value pk
1   0   critic_id INTEGER       0         NA  1
2   1 critic_name    TEXT       1         NA  0

print("STRUCTURE OF MOVIES TABLE")

[1] "STRUCTURE OF MOVIES TABLE"

print(
  dbGetQuery(
    con,
    "PRAGMA table_info(movies);"
  )
)

  cid     name    type notnull dflt_value pk
1   0 movie_id INTEGER       0         NA  1
2   1    title    TEXT       1         NA  0

print("STRUCTURE OF RATINGS TABLE")

[1] "STRUCTURE OF RATINGS TABLE"

print(
  dbGetQuery(
    con,
    "PRAGMA table_info(ratings);"
  )
)

  cid      name    type notnull dflt_value pk
1   0 critic_id INTEGER       1         NA  1
2   1  movie_id INTEGER       1         NA  2
3   2    rating INTEGER       0         NA  0

# ------------------------------------------------------------
# 9. INSERT FIVE CRITICS
# ------------------------------------------------------------

insert_critics <- dbExecute(con, "
INSERT INTO critics
(critic_id, critic_name)
VALUES
(1, 'Critic 1'),
(2, 'Critic 2'),
(3, 'Critic 3'),
(4, 'Critic 4'),
(5, 'Critic 5');
")

print("Number of critics inserted:")

[1] "Number of critics inserted:"

print(insert_critics)

[1] 5

critics_table <- dbReadTable(
  con,
  "critics"
)

print("CRITICS TABLE")

[1] "CRITICS TABLE"

print(critics_table)

  critic_id critic_name
1         1    Critic 1
2         2    Critic 2
3         3    Critic 3
4         4    Critic 4
5         5    Critic 5

# ------------------------------------------------------------
# 10. INSERT SIX MOVIES
# ------------------------------------------------------------

insert_movies <- dbExecute(con, "
INSERT INTO movies
(movie_id, title)
VALUES
(1, 'Superman'),
(2, 'Weapons'),
(3, 'Jurassic World: Rebirth'),
(4, 'Thunderbolts*'),
(5, 'Mission: Impossible - The Final Reckoning'),
(6, 'F1: The Movie');
")

print("Number of movies inserted:")

[1] "Number of movies inserted:"

print(insert_movies)

[1] 6

movies_table <- dbReadTable(
  con,
  "movies"
)

print("MOVIES TABLE")

[1] "MOVIES TABLE"

print(movies_table)

  movie_id                                     title
1        1                                  Superman
2        2                                   Weapons
3        3                   Jurassic World: Rebirth
4        4                             Thunderbolts*
5        5 Mission: Impossible - The Final Reckoning
6        6                             F1: The Movie

# ------------------------------------------------------------
# 11. INSERT RATINGS
# NULL = participant did not rate the movie
# ------------------------------------------------------------

insert_ratings <- dbExecute(con, "
INSERT INTO ratings
(critic_id, movie_id, rating)
VALUES

(1,1,5),
(1,2,4),
(1,3,3),
(1,4,4),
(1,5,5),
(1,6,5),

(2,1,4),
(2,2,NULL),
(2,3,3),
(2,4,4),
(2,5,4),
(2,6,5),

(3,1,5),
(3,2,5),
(3,3,NULL),
(3,4,4),
(3,5,5),
(3,6,4),

(4,1,4),
(4,2,4),
(4,3,2),
(4,4,NULL),
(4,5,4),
(4,6,5),

(5,1,5),
(5,2,3),
(5,3,4),
(5,4,5),
(5,5,NULL),
(5,6,4);
")

print("Number of ratings inserted:")

[1] "Number of ratings inserted:"

print(insert_ratings)

[1] 30

ratings_table <- dbReadTable(
  con,
  "ratings"
)

print("RATINGS TABLE")

[1] "RATINGS TABLE"

print(ratings_table)

   critic_id movie_id rating
1          1        1      5
2          1        2      4
3          1        3      3
4          1        4      4
5          1        5      5
6          1        6      5
7          2        1      4
8          2        2     NA
9          2        3      3
10         2        4      4
11         2        5      4
12         2        6      5
13         3        1      5
14         3        2      5
15         3        3     NA
16         3        4      4
17         3        5      5
18         3        6      4
19         4        1      4
20         4        2      4
21         4        3      2
22         4        4     NA
23         4        5      4
24         4        6      5
25         5        1      5
26         5        2      3
27         5        3      4
28         5        4      5
29         5        5     NA
30         5        6      4

# ------------------------------------------------------------
# 12. PRINT ALL THREE TABLES
# ------------------------------------------------------------

print("====================================")

[1] "===================================="

print("CRITICS TABLE")

[1] "CRITICS TABLE"

print("====================================")

[1] "===================================="

print(dbReadTable(con, "critics"))

  critic_id critic_name
1         1    Critic 1
2         2    Critic 2
3         3    Critic 3
4         4    Critic 4
5         5    Critic 5

print("====================================")

[1] "===================================="

print("MOVIES TABLE")

[1] "MOVIES TABLE"

print("====================================")

[1] "===================================="

print(dbReadTable(con, "movies"))

  movie_id                                     title
1        1                                  Superman
2        2                                   Weapons
3        3                   Jurassic World: Rebirth
4        4                             Thunderbolts*
5        5 Mission: Impossible - The Final Reckoning
6        6                             F1: The Movie

print("====================================")

[1] "===================================="

print("RATINGS TABLE")

[1] "RATINGS TABLE"

print("====================================")

[1] "===================================="

print(dbReadTable(con, "ratings"))

   critic_id movie_id rating
1          1        1      5
2          1        2      4
3          1        3      3
4          1        4      4
5          1        5      5
6          1        6      5
7          2        1      4
8          2        2     NA
9          2        3      3
10         2        4      4
11         2        5      4
12         2        6      5
13         3        1      5
14         3        2      5
15         3        3     NA
16         3        4      4
17         3        5      5
18         3        6      4
19         4        1      4
20         4        2      4
21         4        3      2
22         4        4     NA
23         4        5      4
24         4        6      5
25         5        1      5
26         5        2      3
27         5        3      4
28         5        4      5
29         5        5     NA
30         5        6      4

# ------------------------------------------------------------
# 13. CREATE SQL JOIN QUERY
# ------------------------------------------------------------

movie_query <- "
SELECT
    r.critic_id,
    c.critic_name,
    r.movie_id,
    m.title,
    r.rating
FROM ratings AS r

JOIN critics AS c
    ON r.critic_id = c.critic_id

JOIN movies AS m
    ON r.movie_id = m.movie_id

ORDER BY
    r.critic_id,
    r.movie_id;
"

print("SQL JOIN QUERY:")

[1] "SQL JOIN QUERY:"

print(movie_query)

[1] "\nSELECT\n    r.critic_id,\n    c.critic_name,\n    r.movie_id,\n    m.title,\n    r.rating\nFROM ratings AS r\n\nJOIN critics AS c\n    ON r.critic_id = c.critic_id\n\nJOIN movies AS m\n    ON r.movie_id = m.movie_id\n\nORDER BY\n    r.critic_id,\n    r.movie_id;\n"

# ------------------------------------------------------------
# 14. LOAD SQL DATA INTO R AS A DATAFRAME
# ------------------------------------------------------------

ratings_df <- dbGetQuery(
  con,
  movie_query
)

print("JOINED R DATAFRAME")

[1] "JOINED R DATAFRAME"

print(ratings_df)

   critic_id critic_name movie_id                                     title
1          1    Critic 1        1                                  Superman
2          1    Critic 1        2                                   Weapons
3          1    Critic 1        3                   Jurassic World: Rebirth
4          1    Critic 1        4                             Thunderbolts*
5          1    Critic 1        5 Mission: Impossible - The Final Reckoning
6          1    Critic 1        6                             F1: The Movie
7          2    Critic 2        1                                  Superman
8          2    Critic 2        2                                   Weapons
9          2    Critic 2        3                   Jurassic World: Rebirth
10         2    Critic 2        4                             Thunderbolts*
11         2    Critic 2        5 Mission: Impossible - The Final Reckoning
12         2    Critic 2        6                             F1: The Movie
13         3    Critic 3        1                                  Superman
14         3    Critic 3        2                                   Weapons
15         3    Critic 3        3                   Jurassic World: Rebirth
16         3    Critic 3        4                             Thunderbolts*
17         3    Critic 3        5 Mission: Impossible - The Final Reckoning
18         3    Critic 3        6                             F1: The Movie
19         4    Critic 4        1                                  Superman
20         4    Critic 4        2                                   Weapons
21         4    Critic 4        3                   Jurassic World: Rebirth
22         4    Critic 4        4                             Thunderbolts*
23         4    Critic 4        5 Mission: Impossible - The Final Reckoning
24         4    Critic 4        6                             F1: The Movie
25         5    Critic 5        1                                  Superman
26         5    Critic 5        2                                   Weapons
27         5    Critic 5        3                   Jurassic World: Rebirth
28         5    Critic 5        4                             Thunderbolts*
29         5    Critic 5        5 Mission: Impossible - The Final Reckoning
30         5    Critic 5        6                             F1: The Movie
   rating
1       5
2       4
3       3
4       4
5       5
6       5
7       4
8      NA
9       3
10      4
11      4
12      5
13      5
14      5
15     NA
16      4
17      5
18      4
19      4
20      4
21      2
22     NA
23      4
24      5
25      5
26      3
27      4
28      5
29     NA
30      4

# ------------------------------------------------------------
# 15. VERIFY DATAFRAME
# ------------------------------------------------------------

print("Class of ratings_df:")

[1] "Class of ratings_df:"

print(class(ratings_df))

[1] "data.frame"

print("Structure of ratings_df:")

[1] "Structure of ratings_df:"

str(ratings_df)

'data.frame':   30 obs. of  5 variables:
 $ critic_id  : int  1 1 1 1 1 1 2 2 2 2 ...
 $ critic_name: chr  "Critic 1" "Critic 1" "Critic 1" "Critic 1" ...
 $ movie_id   : int  1 2 3 4 5 6 1 2 3 4 ...
 $ title      : chr  "Superman" "Weapons" "Jurassic World: Rebirth" "Thunderbolts*" ...
 $ rating     : int  5 4 3 4 5 5 4 NA 3 4 ...

print("Dimensions of ratings_df:")

[1] "Dimensions of ratings_df:"

print(dim(ratings_df))

[1] 30  5

print("Number of rows:")

[1] "Number of rows:"

print(nrow(ratings_df))

[1] 30

print("Number of columns:")

[1] "Number of columns:"

print(ncol(ratings_df))

[1] 5

print("First six rows:")

[1] "First six rows:"

print(head(ratings_df))

  critic_id critic_name movie_id                                     title
1         1    Critic 1        1                                  Superman
2         1    Critic 1        2                                   Weapons
3         1    Critic 1        3                   Jurassic World: Rebirth
4         1    Critic 1        4                             Thunderbolts*
5         1    Critic 1        5 Mission: Impossible - The Final Reckoning
6         1    Critic 1        6                             F1: The Movie
  rating
1      5
2      4
3      3
4      4
5      5
6      5

print("Last six rows:")

[1] "Last six rows:"

print(tail(ratings_df))

   critic_id critic_name movie_id                                     title
25         5    Critic 5        1                                  Superman
26         5    Critic 5        2                                   Weapons
27         5    Critic 5        3                   Jurassic World: Rebirth
28         5    Critic 5        4                             Thunderbolts*
29         5    Critic 5        5 Mission: Impossible - The Final Reckoning
30         5    Critic 5        6                             F1: The Movie
   rating
25      5
26      3
27      4
28      5
29     NA
30      4

# ------------------------------------------------------------
# 16. IDENTIFY MISSING RATINGS
# ------------------------------------------------------------

missing_ratings <- ratings_df %>%
  filter(is.na(rating))

print("MISSING RATINGS")

[1] "MISSING RATINGS"

print(missing_ratings)

  critic_id critic_name movie_id                                     title
1         2    Critic 2        2                                   Weapons
2         3    Critic 3        3                   Jurassic World: Rebirth
3         4    Critic 4        4                             Thunderbolts*
4         5    Critic 5        5 Mission: Impossible - The Final Reckoning
  rating
1     NA
2     NA
3     NA
4     NA

print("Total number of missing ratings:")

[1] "Total number of missing ratings:"

print(sum(is.na(ratings_df$rating)))

[1] 4

# ------------------------------------------------------------
# 17. MOVIE SUMMARY
# ------------------------------------------------------------

movie_summary <- ratings_df %>%
  group_by(
    movie_id,
    title
  ) %>%
  summarise(
    number_of_ratings = sum(!is.na(rating)),
    missing_ratings = sum(is.na(rating)),
    average_rating = mean(
      rating,
      na.rm = TRUE
    ),
    .groups = "drop"
  ) %>%
  arrange(
    desc(average_rating)
  )

print("MOVIE SUMMARY")

[1] "MOVIE SUMMARY"

print(movie_summary)

# A tibble: 6 × 5
  movie_id title                number_of_ratings missing_ratings average_rating
     <int> <chr>                            <int>           <int>          <dbl>
1        1 Superman                             5               0           4.6 
2        6 F1: The Movie                        5               0           4.6 
3        5 Mission: Impossible…                 4               1           4.5 
4        4 Thunderbolts*                        4               1           4.25
5        2 Weapons                              4               1           4   
6        3 Jurassic World: Reb…                 4               1           3   

# ------------------------------------------------------------
# 18. CRITIC SUMMARY
# ------------------------------------------------------------

critic_summary <- ratings_df %>%
  group_by(
    critic_id,
    critic_name
  ) %>%
  summarise(
    movies_rated = sum(!is.na(rating)),
    movies_not_rated = sum(is.na(rating)),
    average_rating = mean(
      rating,
      na.rm = TRUE
    ),
    .groups = "drop"
  )

print("CRITIC SUMMARY")

[1] "CRITIC SUMMARY"

print(critic_summary)

# A tibble: 5 × 5
  critic_id critic_name movies_rated movies_not_rated average_rating
      <int> <chr>              <int>            <int>          <dbl>
1         1 Critic 1               6                0           4.33
2         2 Critic 2               5                1           4   
3         3 Critic 3               5                1           4.6 
4         4 Critic 4               5                1           3.8 
5         5 Critic 5               5                1           4.2 

# ------------------------------------------------------------
# 19. CREATE USER-ITEM RATING MATRIX
# ------------------------------------------------------------

rating_matrix <- ratings_df %>%
  select(
    critic_name,
    title,
    rating
  ) %>%
  pivot_wider(
    names_from = title,
    values_from = rating
  )

print("USER-ITEM RATING MATRIX")

[1] "USER-ITEM RATING MATRIX"

print(rating_matrix)

# A tibble: 5 × 7
  critic_name Superman Weapons `Jurassic World: Rebirth` `Thunderbolts*`
  <chr>          <int>   <int>                     <int>           <int>
1 Critic 1           5       4                         3               4
2 Critic 2           4      NA                         3               4
3 Critic 3           5       5                        NA               4
4 Critic 4           4       4                         2              NA
5 Critic 5           5       3                         4               5
# ℹ 2 more variables: `Mission: Impossible - The Final Reckoning` <int>,
#   `F1: The Movie` <int>

print("Rating matrix dimensions:")

[1] "Rating matrix dimensions:"

print(dim(rating_matrix))

[1] 5 7

# ------------------------------------------------------------
# 20. SQL SUMMARY QUERY
# Average rating directly from SQL
# ------------------------------------------------------------

sql_movie_summary <- dbGetQuery(con, "
SELECT
    m.movie_id,
    m.title,
    COUNT(r.rating) AS number_of_ratings,
    ROUND(AVG(r.rating), 2) AS average_rating
FROM movies AS m

LEFT JOIN ratings AS r
    ON m.movie_id = r.movie_id

GROUP BY
    m.movie_id,
    m.title

ORDER BY
    average_rating DESC;
")

print("MOVIE SUMMARY CALCULATED IN SQL")

[1] "MOVIE SUMMARY CALCULATED IN SQL"

print(sql_movie_summary)

  movie_id                                     title number_of_ratings
1        1                                  Superman                 5
2        6                             F1: The Movie                 5
3        5 Mission: Impossible - The Final Reckoning                 4
4        4                             Thunderbolts*                 4
5        2                                   Weapons                 4
6        3                   Jurassic World: Rebirth                 4
  average_rating
1           4.60
2           4.60
3           4.50
4           4.25
5           4.00
6           3.00

# ------------------------------------------------------------
# 21. SQL QUERY FOR MISSING RATINGS
# ------------------------------------------------------------

sql_missing <- dbGetQuery(con, "
SELECT
    c.critic_name,
    m.title,
    r.rating
FROM ratings AS r

JOIN critics AS c
    ON r.critic_id = c.critic_id

JOIN movies AS m
    ON r.movie_id = m.movie_id

WHERE r.rating IS NULL;
")

print("MISSING RATINGS FOUND USING SQL")

[1] "MISSING RATINGS FOUND USING SQL"

print(sql_missing)

  critic_name                                     title rating
1    Critic 2                                   Weapons     NA
2    Critic 3                   Jurassic World: Rebirth     NA
3    Critic 4                             Thunderbolts*     NA
4    Critic 5 Mission: Impossible - The Final Reckoning     NA

# ------------------------------------------------------------
# 22. GRAPH 1 - AVERAGE MOVIE RATINGS
# ------------------------------------------------------------

graph1 <- ggplot(
  movie_summary,
  aes(
    x = reorder(
      title,
      average_rating
    ),
    y = average_rating
  )
) +
  geom_col() +
  coord_flip() +
  labs(
    title = "Average Movie Ratings",
    subtitle = "Ratings on a 1-5 Scale",
    x = "Movie",
    y = "Average Rating"
  ) +
  ylim(0, 5) +
  theme_minimal()

print(graph1)

# ------------------------------------------------------------
# 23. GRAPH 2 - NUMBER OF RATINGS PER MOVIE
# ------------------------------------------------------------

graph2 <- ggplot(
  movie_summary,
  aes(
    x = reorder(
      title,
      number_of_ratings
    ),
    y = number_of_ratings
  )
) +
  geom_col() +
  coord_flip() +
  labs(
    title = "Number of Ratings per Movie",
    x = "Movie",
    y = "Number of Ratings"
  ) +
  theme_minimal()

print(graph2)

# ------------------------------------------------------------
# 24. GRAPH 3 - INDIVIDUAL MOVIE RATINGS
# ------------------------------------------------------------

graph3 <- ggplot(
  ratings_df %>%
    filter(!is.na(rating)),
  aes(
    x = title,
    y = rating,
    shape = critic_name
  )
) +
  geom_point(
    size = 3
  ) +
  coord_flip() +
  scale_y_continuous(
    breaks = 1:5,
    limits = c(1, 5)
  ) +
  labs(
    title = "Movie Ratings by Critic",
    x = "Movie",
    y = "Rating",
    shape = "Critic"
  ) +
  theme_minimal()

print(graph3)

# ------------------------------------------------------------
# 25. GRAPH 4 - AVERAGE RATING BY CRITIC
# ------------------------------------------------------------

graph4 <- ggplot(
  critic_summary,
  aes(
    x = critic_name,
    y = average_rating
  )
) +
  geom_col() +
  labs(
    title = "Average Rating Given by Each Critic",
    x = "Critic",
    y = "Average Rating"
  ) +
  ylim(0, 5) +
  theme_minimal()

print(graph4)

# ------------------------------------------------------------
# 26. GRAPH 5 - DISTRIBUTION OF RATINGS
# ------------------------------------------------------------

graph5 <- ggplot(
  ratings_df %>%
    filter(!is.na(rating)),
  aes(
    x = rating
  )
) +
  geom_bar() +
  scale_x_continuous(
    breaks = 1:5
  ) +
  labs(
    title = "Distribution of Movie Ratings",
    x = "Rating",
    y = "Frequency"
  ) +
  theme_minimal()

print(graph5)

# ------------------------------------------------------------
# 27. GRAPH 6 - MISSING RATINGS BY MOVIE
# ------------------------------------------------------------

graph6 <- ggplot(
  movie_summary,
  aes(
    x = reorder(
      title,
      missing_ratings
    ),
    y = missing_ratings
  )
) +
  geom_col() +
  coord_flip() +
  labs(
    title = "Missing Ratings by Movie",
    subtitle = "Missing means the participant did not rate the movie",
    x = "Movie",
    y = "Number of Missing Ratings"
  ) +
  theme_minimal()

print(graph6)

# ------------------------------------------------------------
# 28. OPTIONAL STANDARDIZATION OF RATINGS
# ------------------------------------------------------------

ratings_standardized <- ratings_df %>%
  group_by(
    critic_id,
    critic_name
  ) %>%
  mutate(
    mean_critic_rating =
      mean(
        rating,
        na.rm = TRUE
      ),
    
    sd_critic_rating =
      sd(
        rating,
        na.rm = TRUE
      ),
    
    z_rating =
      ifelse(
        !is.na(rating) &
          sd_critic_rating > 0,
        (rating - mean_critic_rating) /
          sd_critic_rating,
        NA_real_
      )
  ) %>%
  ungroup()

print("STANDARDIZED RATINGS")

[1] "STANDARDIZED RATINGS"

print(ratings_standardized)

# A tibble: 30 × 8
   critic_id critic_name movie_id title                rating mean_critic_rating
       <int> <chr>          <int> <chr>                 <int>              <dbl>
 1         1 Critic 1           1 Superman                  5               4.33
 2         1 Critic 1           2 Weapons                   4               4.33
 3         1 Critic 1           3 Jurassic World: Reb…      3               4.33
 4         1 Critic 1           4 Thunderbolts*             4               4.33
 5         1 Critic 1           5 Mission: Impossible…      5               4.33
 6         1 Critic 1           6 F1: The Movie             5               4.33
 7         2 Critic 2           1 Superman                  4               4   
 8         2 Critic 2           2 Weapons                  NA               4   
 9         2 Critic 2           3 Jurassic World: Reb…      3               4   
10         2 Critic 2           4 Thunderbolts*             4               4   
# ℹ 20 more rows
# ℹ 2 more variables: sd_critic_rating <dbl>, z_rating <dbl>

# ------------------------------------------------------------
# 29. OPTIONAL GRAPH OF STANDARDIZED RATINGS
# ------------------------------------------------------------

graph7 <- ggplot(
  ratings_standardized %>%
    filter(!is.na(z_rating)),
  aes(
    x = title,
    y = z_rating,
    shape = critic_name
  )
) +
  geom_point(
    size = 3
  ) +
  coord_flip() +
  labs(
    title = "Standardized Movie Ratings",
    x = "Movie",
    y = "Standardized Rating (z-score)",
    shape = "Critic"
  ) +
  theme_minimal()

print(graph7)

# ------------------------------------------------------------
# 30. FINAL CHECK
# ------------------------------------------------------------

print("====================================")

[1] "===================================="

print("FINAL DATABASE CHECK")

[1] "FINAL DATABASE CHECK"

print("====================================")

[1] "===================================="

print("Tables:")

[1] "Tables:"

print(dbListTables(con))

[1] "critics" "movies"  "ratings"

print("Number of critics:")

[1] "Number of critics:"

print(nrow(critics_table))

[1] 5

print("Number of movies:")

[1] "Number of movies:"

print(nrow(movies_table))

[1] 6

print("Number of possible rating records:")

[1] "Number of possible rating records:"

print(nrow(ratings_df))

[1] 30

print("Number of observed ratings:")

[1] "Number of observed ratings:"

print(sum(!is.na(ratings_df$rating)))

[1] 26

print("Number of missing ratings:")

[1] "Number of missing ratings:"

print(sum(is.na(ratings_df$rating)))

[1] 4

# ------------------------------------------------------------
# 31. CLOSE DATABASE CONNECTION
# ------------------------------------------------------------

dbDisconnect(con)

print("Database connection closed successfully.")

[1] "Database connection closed successfully."

You can add options to executable code like this

[1] 4

The echo: false option disables the printing of code (only output is displayed).