---
subtitle: "LSE ME204 Week 03 Day 02 - Lecture Material"
title: "Intro to databases"
author: "Dr. Jon Cardoso-Silva"
date: "2023-07-25"
date-format: "DD MMMM YYYY"
format: 
    html:
        df-print: kable
        self-contained: true
        link-external-icon: true
        toc: true
        theme: simplex
        code-fold: true
        page-layout: full
        fig-width: 10
        fig-height: 6
 
---

# ⚙️ Setup

```{r setup, message=FALSE, warning=FALSE}
library(DBI)
library(RSQLite)
library(dplyr)
library(dbplyr)
library(ggplot2)

library(tidyr) # a new one today
```

Connect with database:

```{r}
con <- dbConnect(RSQLite::SQLite(), "../../data/imdb.db")
```

What are the tables?

```{r}
dbListTables(con)
```


```{r}
dbListFields(con, "title_ratings")
```

# Part 1: Warm Up

```{r}
title_basics_tbl <- tbl(con, "title_basics")
title_ratings_tbl <- tbl(con, "title_ratings")
title_episode_tbl <- tbl(con, "title_episode")
```

**Q: What is the distribution of ratings?**

```{r}
plot_df <- title_ratings_tbl |> select(averageRating) |> collect()

# Definition and mapping
g <- ggplot(plot_df, aes(x=`averageRating`))

# Geom
g <- g + geom_histogram(bins=20)

# Annotating
g <- g + 
  # This adds a vertical line, crossing at the xintercept
  geom_vline(xintercept = median(plot_df$averageRating), color="#d95f02") +
  annotate(geom="text", x=6.5, y=200000, color="#d95f02", hjust=1,
           label="The vertical line indicates the\nmedian of the average rating")

# Customising
nice_x_breaks <- sort(c(median(plot_df$averageRating), seq(0, 10, 2)))

g <- g + 
  scale_y_continuous(name="Count",
                     labels=scales::unit_format(unit = "K", scale = 1e-3)) +
  scale_x_continuous(name="Average Rating",
                     breaks=nice_x_breaks)

g <- g + 
  labs(title="Average ratings of titles in IMDb",
       subtitle="(Change the number of bins to see how the graph changes)")

# Themes
g <- g + 
  theme_minimal() +
  theme(axis.title = element_text(size=rel(1.3)),
        axis.text.y = element_text(size=rel(1.2)),
        axis.text.x = element_text(size=rel(1.4)),
        axis.title.x = element_text(margin=margin(t=15)),
        axis.title.y = element_text(margin=margin(r=15)),
        plot.title = element_text(size=rel(1.6), color="#D9230F"),
        plot.background = element_rect(fill="#FCFCFC", color = NA),
        legend.background = element_rect(fill="#fcfcfc", color="#dedede"),
        legend.position = c(0.21, 0.76))

g

```

I wonder if this varies by title type? Are TV series (overall) ranked any different from their individual episodes?

🎯 **ACTION POINT:**

Try to write code to solve the following question:

**First: how many titles are there per `titleType`?**

```{r}
# Your code goes here. This one is easy.
```


# Part 2: Joining Tables

🎯 **ACTION POINT:**

Try to write code to solve the following question:

**Q: What is the distribution of ratings by the following title types: `tvSeries` and `tvEpisode`?**

```{r}
# Your code goes here. Try whatever strategy comes to mind.
```

🎯 **ACTION POINT:**

Nice! Can you think of a way to plot both distributions in the same graph? (Hint: search for `ggplot2::facet_grid()`)

```{r}
# Your code goes here. Try whatever strategy comes to mind.
```

🎯 **ACTION POINT:**

Try to write code to solve the following question:

**Q: Select only those tv series who have received at least 100k votes from the public (really popular tv series):**

```{r}
# Your code goes here. Try whatever strategy comes to mind.
```

# Part 3: Pivoting

Take a closer look at the `name_basics` table:

```{r}
name_basics_tbl <- tbl(con, "name_basics")
name_basics_tbl |> head()
```

Note how the `knownForTitles` column contains a list of titles. This is a common way to store data in databases. It is called a "list column". If we were using PostgreSQL, this column could have been stored directly as a list (see [PostgreSQL arrays](https://www.postgresql.org/docs/current/arrays.html)). But SQLite does not support list columns, so we have stored them as text.

Let's use `pivot_wider()` to convert this column into a list column:

```{r}
name_basics_tbl |> 
  select(nconst, knownForTitles) |> 
  head()
```

which returns:

|nconst    |knownForTitles                          |
|:---------|:---------------------------------------|
|nm0000001 |tt0031983,tt0072308,tt0053137,tt0050419 |
|nm0000002 |tt0075213,tt0038355,tt0117057,tt0037382 |
|nm0000003 |tt0049189,tt0057345,tt0056404,tt0054452 |
|nm0000004 |tt0077975,tt0078723,tt0080455,tt0072562 |
|nm0000005 |tt0083922,tt0050976,tt0050986,tt0069467 |
|nm0000006 |tt0038109,tt0036855,tt0038787,tt0034583 |

Column `knownForTitles` is merely a string. Let's parse it and convert it to a list:

```{r}
name_basics_tbl |> 
  head(10) |>
  select(nconst, knownForTitles) |> 
  collect() |>
  mutate(knownForTitles = strsplit(knownForTitles, ","))
```

::: {.callout-note}

Sadly, we need to `collect()` the dataframe into memory to be able to use the `strsplit()` function. An alternative would be to construct an SQL query directly using [SQLite string operations](https://www.sqlitetutorial.net/sqlite-string-functions/) and then use `dbGetQuery()` to execute it. But that's a bit more advanced than we want to go today.

:::


Now, `knownForTitles` is a list column. Let's use `tidyr::unnest_wider()` to convert each element of the list into a column:


```{r}
name_basics_tbl |> 
  head(10) |>
  select(nconst, knownForTitles) |> 
  collect() |>
  mutate(knownForTitles = strsplit(knownForTitles, ",")) |> 
  unnest_wider(col = knownForTitles, names_sep="-")
```

Alternatively, we might want to use `tidyr::unnest_longer()` to convert each element of the list into a row:

```{r}
name_basics_tbl |> 
  head(10) |>
  select(nconst, knownForTitles) |> 
  collect() |>
  mutate(knownForTitles = strsplit(knownForTitles, ",")) |> 
  unnest_longer(col = knownForTitles)
```

`unnest_*` functions are useful when you have columns that are made of lists, but you can reshape from long to wide or wide to long with the `pivot_*` functions as well. 

For example, suppose your data didn't come as a list but as separate columns:

```{r}
name_basics_tbl |> 
  head(10) |>
  select(nconst, knownForTitles) |> 
  collect() |>
  mutate(knownForTitles = strsplit(knownForTitles, ",")) |> 
  unnest_wider(col = knownForTitles, names_sep="-") -> imagined_data
```

|nconst    |knownForTitles-1 |knownForTitles-2 |knownForTitles-3 |knownForTitles-4 |
|:---------|:----------------|:----------------|:----------------|:----------------|
|nm0000001 |tt0031983        |tt0072308        |tt0053137        |tt0050419        |
|nm0000002 |tt0075213        |tt0038355        |tt0117057        |tt0037382        |
|nm0000003 |tt0049189        |tt0057345        |tt0056404        |tt0054452        |
|nm0000004 |tt0077975        |tt0078723        |tt0080455        |tt0072562        |
|nm0000005 |tt0083922        |tt0050976        |tt0050986        |tt0069467        |
|nm0000006 |tt0038109        |tt0036855        |tt0038787        |tt0034583        |
|nm0000007 |tt0042593        |tt0037382        |tt0043265        |tt0034583        |
|nm0000008 |tt0047296        |tt0078788        |tt0070849        |tt0068646        |
|nm0000009 |tt0057877        |tt0087803        |tt0061184        |tt0059749        |
|nm0000010 |tt0042041        |tt0031867        |tt0035575        |tt0029870        |

If your data came in this format, you could use `pivot_longer()` to convert it to a long format:

```{r}
imagined_data |>
  pivot_longer(cols = starts_with("knownForTitles"), 
               names_to = "colunm_name", 
               values_to = "tconst")
```

|nconst    |colunm_name      |tconst    |
|:---------|:----------------|:---------|
|nm0000001 |knownForTitles-1 |tt0031983 |
|nm0000001 |knownForTitles-2 |tt0072308 |
|nm0000001 |knownForTitles-3 |tt0053137 |
|nm0000001 |knownForTitles-4 |tt0050419 |
|nm0000002 |knownForTitles-1 |tt0075213 |
|nm0000002 |knownForTitles-2 |tt0038355 |
|nm0000002 |knownForTitles-3 |tt0117057 |
|nm0000002 |knownForTitles-4 |tt0037382 |
|nm0000003 |knownForTitles-1 |tt0049189 |
|nm0000003 |knownForTitles-2 |tt0057345 |

(maybe you can even drop the `colunm_name` column afterwards)


🎯 **ACTION POINT:**

Using the functions above and your newly acquired knowledge of joins, try to answer the following question:

**Q: List the unique names of all actors who have participated in the tv series Game of Thrones.**

```{r}
# Your code goes here. Try whatever strategy comes to mind.
```
