The following Quarto document requires additional *R* packages. Please run the code chunk before continuing.

```{r}
#| label: setup-chunk
#| echo: true
#| message: false
pkg_list = c("ggplot2", "DBI", "RSQLite", "readr", "gifski")
mia_pkgs = pkg_list[!(pkg_list %in% installed.packages()[,"Package"])]
if(length(mia_pkgs) > 0) install.packages(mia_pkgs)
loaded_pkgs = lapply(pkg_list, require, character.only=TRUE)
```

# Real-Data: Gapminder

For the next section, our focus shifts to working with the Gapminder dataset created by Hans Rosling from the Gapminder Foundation. The Gapminder data set represents a longitudinal study of various global developmental indicators over multiple countries across 3 centuries.

## Download Gapminder Data

Let's use `curl` a *shell* command to download the `gap-every-five-years.csv` data file at <https://coatless.github.io/raw-data/gap-every-five-years.csv>.

```{bash}
#| label: download-data
#| cache: true
curl -o gap-every-five-years.csv -LJ https://coatless.github.io/raw-data/gap-every-five-years.csv
```

## Import Data into SQLite

Prior to working with the data in SQL, we will need to import it into the database. We achieve this by first creating a file-backed database called `gapminder_db.sqlite3`:

```{r}
#| label: establish-database
#| echo: true

# Setup a connection into a file-backed SQLite database
gm_db = DBI::dbConnect(RSQLite::SQLite(), "gapminder_db.sqlite3")
```

Next, we read the data first into *R* and, then, using the `dbWriteTable()` function to write the data as the `gapminder` table into the database.

```{r}
#| label: copy-data-to-sql-table
#| echo: true
# Iteratively read in data to the database.
readr::read_csv_chunked(
    # Location of the data file
    file = "gap-every-five-years.csv",  
    
    # Process the data after it has been read in
    callback = function(chunk, dummy) {
        
        # Write data to the `gapminder` table iteratively
        dbWriteTable(gm_db, "gapminder", chunk, append = TRUE)
        
    },
    
    # Specify how much data should be written to a database in each chunk
    chunk_size = 1000
)
```

Next, let's look at the data inside of the `gapminder` table.

```{r}
#| label: view-data
#| echo: true
df = dbReadTable(gm_db, "gapminder")
head(df, 10)
```

Finally, we stop the database connection:

```{r}
#| label: stop-db-connection
#| echo: true
DBI::dbDisconnect(gm_db)
```

## Load SQLite Database from Disk

Next, we'll load the SQLite database we just created from disk.

```{r}
#| label: setup-db-connection-problems
#| echo: true

# Setup a connection into a file-backed SQLite database
gm_db = DBI::dbConnect(RSQLite::SQLite(), "gapminder_db.sqlite3")
```

For each of the next SQL code chunks, we're using the following options:

```
#| label: name-of-code-chunk
#| connection: gm_db
```

Note: We're using `gm_db` as that is the name of the connection object to the
database just established in the preceding code chunk.

## Querying Real Data

Let's begin by looking at a few exploratory components.

First, how many observations or records are in the Table?

```{sql}
#| label: count-all-observations
#| connection: gm_db
#| echo: true

SELECT COUNT(*) FROM gapminder
```

What do the first five records of each field look like?

```{sql}
#| label: head-of-table
#| connection: gm_db
#| echo: true

SELECT * FROM gapminder LIMIT 5;
```

What countries are in the data set?

```{sql}
#| label: unique-country-names
#| connection: gm_db
#| echo: true

SELECT DISTINCT country FROM gapminder LIMIT 10;
```

```{sql}
#| label: number-of-unique-countries
#| connection: gm_db
#| echo: true

SELECT COUNT(DISTINCT country) FROM gapminder;
```

What does the population look if we rescale by thousands?

```{sql}
#| label: rescale-population
#| connection: gm_db
#| echo: true

SELECT country, year, pop/1000.0 FROM gapminder LIMIT 5;
```

We probably want a better variable than `pop/1000.0`. We can change the name by placing after the modification `AS <new-field>`:

```{sql}
#| label: rescale-population-custom variable
#| connection: gm_db
#| echo: true

SELECT country, year, pop/1000.0 AS rescaled_pop FROM gapminder LIMIT 5;
```

How many people are represented in the data set?

```{sql}
#| label: number-of-people-in-gapminder
#| connection: gm_db
#| echo: true

SELECT SUM(pop) FROM gapminder; 
```

What does the GDP per capita across the years look like?

```{sql}
#| label: gdp-per-capita-over-years
#| connection: gm_db
#| echo: true

SELECT country, year, ROUND(gdpPercap, 2) AS gdp FROM gapminder;
```

What are the features of Iceland, Germany, and Spain after 2000?

```{sql}
#| label: features-of-iceland-germany-and-spain
#| connection: gm_db
#| echo: true

SELECT *
FROM gapminder
WHERE (year >= 2000) AND (country IN ("Iceland", "Germany", "Spain"));
```

## Visualization

What if we wanted to visualize the data?

Well, there are built-in methods associated with the sql results... But, let's take an interactive look at the data. To do so, we'll use the [`gganimate`](https://gganimate.com/index.html) package to rapidly make a graph.

Next, let's query all of the information from the table.

``` 
#| label: retrieve-table-from-db
#| connection: gm_db
#| echo: true
#| output.var: gm_df_from_db
SELECT * FROM gapminder;
```

```{sql}
#| label: retrieve-table-from-db
#| connection: gm_db
#| echo: false
#| output.var: gm_df_from_db
SELECT * FROM gapminder;
```

Note, we're exporting the data as: `gm_df_from_db`

With the query done, we turn our focus to creating our plot that compares life expectancy vs. GDP per capita under all countries in Gap minder across time.

```{r}
#| label: graph-data-drift-gapminder
#| echo: true
#| cache: true
library(ggplot2)
library(gganimate)

p = ggplot(gm_df_from_db) +
    aes(gdpPercap, lifeExp, size = pop, colour = country) +
    geom_point(show.legend = FALSE, alpha = 0.7) +
    scale_color_viridis_d() +
    scale_size(range = c(2, 12)) +
    scale_x_log10() +
    theme(legend.position = 'none') +
    labs(
        title = 'Year: {frame_time}', 
        x = 'GDP per Capita', 
        y = 'Life Expectancy'
    ) +
    transition_time(year) +
    ease_aes('linear')

animate(p)
```
