# 0 Install and load required packages

Load required packages (if no 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
help(package = '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: Can you imagine what a combined data frame would look like?

# 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() %>% 
  filter(n > 1)
```

```{r}
airlines 
```

```{r}
weather 
```

```{r}
flights
```

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

# 3 Join tables

## 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?

Hint:

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

    ```{r}
    weather_flights <- weather %>% 
      ?_join(flights, by = "")
    ```

<!-- -->

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

    ```{r}
    use_weather_flights <- weather_flights %>% 
      select()
    ```

<!-- -->

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

    ```{r}
    use_weather_flights %>%
      group_by(?) %>%
      summarise(delay = mean(dep_delay, na.rm = TRUE)) %>%
      ggplot(aes(x = ?, y = delay)) +
      geom_line() + geom_point()
    ```

## 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.1 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?

Hint:

-   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 <- ?_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 <- ?_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))
    ```
