# 0 Install and load required packages

Load required packages (if not intalled yet, install first)

```{r}
if (!require(tidyverse)) install.packages('tidyverse')
if (!require(nycflights13)) install.packages('nycflights13')

library(tidyverse)
library(nycflights13)
```

(**Optional**) Update R to the latest version

```{r}
# The symbol # is used to add comments or annotation to r code
# Delete the # at the beginning of the following lines and then click run 

# if (!require(installr)) install.packages('installr')
# library(installr)
# updateR()
```

# 1 Get to know `nycflights13`

Look at the R document for the package

```{r}
?nycflights13
```

`flight`: All flights that departed from New York City (NYC) in 2013

```{r}
?flights
View(flights)  
```

`weather`: Hourly meterological data for each airport in NYC:

-   John F.Kennedy International Airport (JFK)
-   LaGuardia Airport (LGA)
-   Newark Liberty International Airport (EWR)

```{r}
?weather
View(weather) 
```

`planes`: Construction information about each plane

```{r}
?planes  
View(planes)
```

`airports`: Airport names and locations

```{r}
?airports  
View(airports)
```

`airline`: Translation between two letter carrier codes and names

```{r}
?airlines
View(airlines)
```

## 1.1 Exercise 1

Why don't we put all the information in a data frame? What are the
benefits of creating multiple data frames?

Hint: Could you imagine what an overall combined data frame would look
like?

It cuts down the information in each data frame. For specific questions,
one only needs to use relevant information from one or two tables. A
combined data frame would be clumsy and overwhelming for the human brain
to process. Also, a combined data frame takes up more computer memory
and slows analysis speed.

# 2 Keys

> A **primary key** uniquely identifies an observation in its own table.

> A **foreign key** uniquely identifies an observation in another table.

Let's say we make a guess that `tailnum` is the primary key for the
`planes` data frame, then we can check if any `tailnum` shows up more
than once in `planes` by the `count()` function.

```{r}
planes %>% 
  count(tailnum) %>% 
  filter(n > 1)
```

## 2.1 Exercise 2

What's the primary key in the other four tables?

```{r}
airports %>% 
  count(faa) %>% 
  filter(n > 1)
```

```{r}
airlines %>%
  count(carrier) %>%
  filter(n > 1)
```

```{r}
weather %>%
  count(time_hour, origin) %>%
  filter(n > 1)

# compound key: more than one variable
```

```{r}
flights %>%
  count(time_hour, flight, carrier) %>%
  filter(n > 1)

# compound key: more than one variable
```

Could you find an example of foreign key from those five data frames?

`faa` is the primary key in the `airports` data frame. As the `airport`
data frame can be combined with the `flights` data frame. `faa` should
be the foreign key in the `flights` data frame because it uniquely
identifies a row in the `airport` data frame. As for a flight, there is
a departure airport and an arriving airport. So the foreign key `faa`
become `origin` and `dest`.

Similarly,

-   `planes$tailnum` is a primary key, so `flights$tailnum` is a foreign
    key.
-   `airlines$carrier` is a primary key, so `flights$carrier` is a
    foreign key.
-   `weather[c('time_hour', 'origin')]` is a (compound) primary key, so
    `flights[c('time_hour', 'origin')]` is a (compound) foreign key.

# 3 Join tables

> **Mutating joins** add new ***variables*** to one data frame from
> matching observations in another

> **Filtering joins** filter ***observations*** from one data frame
> based on whether or not they match an observation in the other table

## 3.1 Mutating joins

Check the help information for the four type of join functions and run their examples.

```{r}
# run the following code line by line
# after run each line, look at the help tab in the right bottom
# scroll down to the examples section and run examples
?inner_join
?full_join
?left_join
?right_join
```

### 3.1.1 Exercise 3

[Soure](https://jrnold.github.io/r4ds-exercise-solutions/relational-data.html#exercise-13.4.4)

> What weather conditions make it more likely to see a delay in
> departure?

Hints:

-   Step 1: Join the `weather` and `flights` data frames. What type of
    join to use? What is the key?

    ```{r}
    weather_flights <- weather %>% 
      inner_join(flights, by = c('time_hour', 'origin'))
    ```

<!-- -->

-   Step 2: Select the necessary variables, weather conditions from
    `temp` to `visib` and `dep_delay` .

    ```{r}
    use_weather_flights <- weather_flights %>% 
      select(temp:visib, dep_delay)
    ```

<!-- -->

-   Step 3: Examine the relationship between each weather condition and
    departure delay.

    ```{r}
    use_weather_flights %>%
      group_by(temp) %>%
      summarise(delay = mean(dep_delay, na.rm = TRUE)) %>%
      ggplot(aes(x = temp, y = delay)) +
      geom_line() + geom_point() # no clear relationship with temperature

    use_weather_flights %>%
      group_by(dewp) %>%
      summarise(delay = mean(dep_delay, na.rm = TRUE)) %>%
      ggplot(aes(x = dewp, y = delay)) +
      geom_line() + geom_point() # no clear relationship with dewpoint

    use_weather_flights %>%
      group_by(humid) %>%
      summarise(delay = mean(dep_delay, na.rm = TRUE)) %>%
      ggplot(aes(x = humid, y = delay)) +
      geom_line() + geom_point() # no clear relationship with humidity

    use_weather_flights %>%
      group_by(wind_speed) %>%
      summarise(delay = mean(dep_delay, na.rm = TRUE)) %>%
      ggplot(aes(x = wind_speed, y = delay)) +
      geom_line() + geom_point() # more likely a longer delay with higher wind speed

    use_weather_flights %>%
      group_by(precip) %>%
      summarise(delay = mean(dep_delay, na.rm = TRUE)) %>%
      ggplot(aes(x = precip, y = delay)) +
      geom_line() + geom_point() # no clear relationship with precipitation/rain

    use_weather_flights %>%
      group_by(visib) %>%
      summarise(delay = mean(dep_delay, na.rm = TRUE)) %>%
      ggplot(aes(x = visib, y = delay)) +
      geom_line() + geom_point() # more likely a longer delay with lower visibility
    ```

## 3.2 Filtering joins

Check the help information for the four type of join functions and run their examples.

```{r}
# run the following code line by line
# after run each line, look at the help tab in the right bottom
# scroll down to the examples section and run examples
?semi_join
?anti_join
```

### 3.2.3 Exercise 4

[Source](https://jrnold.github.io/r4ds-exercise-solutions/relational-data.html#exercise-13.5.4)

> Find the 48 hours (over the course of the whole year) that have the
> worst departure delays. Cross-reference it with the weather data. Can
> you see any patterns?

Hints:

-   Step 1: To find the 48 hours with the worst departure delays, group
    flights by hour of scheduled departure time and calculate the
    average delay, arrange by delay, and select the 48 observations
    (hours) with the highest average delay.

    ```{r}
    worst_hours <- flights %>%
      mutate(hour = sched_dep_time %/% 100) %>%
      group_by(origin, time_hour) %>%
      summarise(dep_delay = mean(dep_delay, na.rm = TRUE)) %>%
      ungroup() %>%
      arrange(desc(dep_delay)) %>%
      slice(1:48)
    ```

-   Step 2: To get the weather for these hours, choose a type of
    filtering joins.

    ```{r}
    weather_most_delayed <- semi_join(weather, worst_hours, 
                                      by = c("origin", "time_hour"))
    ```

-   Step 3: To get the weather for hours except for those hours, choose
    a type of filtering joins.

    ```{r}
    weather_less_delayed <- anti_join(weather, worst_hours, 
                                      by = c("origin", "time_hour"))
    ```

-   Step 4: Compare the average weather conditions in the most and less
    delayed hours.

    ```{r}
    weather_less_delayed <- weather_less_delayed %>%
      mutate(most_delayed = 'no') 

    weather_most_delayed %>%
      mutate(most_delayed = 'yes') %>%
      rows_append(weather_less_delayed) %>%
      group_by(most_delayed) %>%
      summarise(temp = mean(temp, na.rm = TRUE),
                dewp = mean(dewp, na.rm = TRUE),
                humid = mean(humid, na.rm = TRUE),
                wind_speed = mean(wind_speed, na.rm = TRUE),
                precip = mean(precip, na.rm = TRUE),
                visib = mean(visib, na.rm = TRUE))
    ```
