---
title: "Case Study: Flight Delays for NYC Airports"
author: "Keith VanderLinden"
date: "Spring, 2022"
output: html_document
---

```{r packages, warning=FALSE, message=FALSE}
library(tidyverse)
```

This document describes a simple analysis of the well-known [`nycflights13` dataset](https://www.rdocumentation.org/packages/nycflights13/versions/1.0.1), which includes data on all flights departing from NYC airports in 2013.

## The Data

We start by load the data, which is pre-packaged in the `nycflights13` package. There are five dataframes, often called *tables*.

```{r}
library(nycflights13)

flights
airports
airlines
planes
weather
```

These are all flights from the New-York-area airports (Newark, La Guardia, &amp; John F. Kennedy).

```{r}
flights %>%
  distinct(origin)

flights %>%
  distinct(year)
```
### The Structure of the Data

As datasets grow in complexity, they are increasingly difficult to visualize.
To help understand the structure of the data, here is what is known as a *schema* (i.e., structure) of the data tables. It depicts the separate dataframes and the relationships between them.

![NYC Flights Database Schema](images/nycflights13-schema.png)

### Key Values

The schema marks the *primary key*, if any, for each table. Primary key values uniquely identify one record in the table. They cannot be NA/NULL, but they can comprise more than one field. Note that not all these tables have primary keys.

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

weather %>% 
  count(year, month, day, hour, origin) %>% 
  filter(n > 1)

flights %>%
  count(year, month, day, hour, flight) %>% 
  filter(n > 1)
  
```

The schema also marks the *foreign keys*. A foreign key is a field in the current table that references the primary key of another table. This allows us to represent more flexible inter-table relationships than were shown the class slides. It is important that the foreign key references a primary key in the other table because the foreign key value must reference a unique row in that other table. Foreign key values in the current table can appear multiple times and can be NA/NULL.

## Join Queries

It is efficient to store data in separate tables because it allows us to represent important data only once in the dataset. For example, it&rsquo;s much better to store the names and locations of the airports in one, central place rather than storing them for every flight that arrives or departs from that airport.

However, when analyzing data, we often need to &ldquo;join&rdquo; these table back together, e.g., to find the location of the the destination airport in the flights table. 

### 1-to-Many Joins

Here we try to find all the flights to Gerald R. Ford airport. 

If we happened to know the three-character code for the Ford airport (`GRR`), then we could simply use the `flights` table. If we don&rsquo;t, however, we need to join the flights and airports tables to relate flights with the associated airports (including their name).

```{r}
flights_airports <- inner_join(flights, airports, by = c("dest" = "faa"))
flights_airports
```

We can then query the flights to the Ford airport by airport name.

```{r}
flights_airports %>%
  filter(str_detect(name, "Ford")) %>%
  select(origin, year, month, day, carrier, name)
```

### Many-to-Many Joins

Here, we try to find all the flights to the GR Ford airport operated by Endeavor Airlines.

Again, if we happened to know the code for Endeavor Airlines (`EV`), then we could just use the `flights` table. If we don&rsquo;t, however, we need to join the flights and airlines tables to relate flights with the associated airlines (including their name).

```{r}
flights_airports_airlines <- flights %>%
  left_join(airports, 
            by = c("dest" = "faa")
            ) %>%
  left_join(airlines, 
            by = c("carrier" = "carrier"), 
            suffix = c(".airport", ".airline"),
            )
flights_airports_airlines
```

Here, we needed to specify which keys should match up for the joins. The first join matches `flights$dest` with `airports$faa`, the second `flights$carrier` with `airlines$carrier`. 

We also needed to deal with the issue of name conflicts. Both the airports and airlines tables have a `name` field. We add the `suffix` to the last query to distinguish `name.airport` and `name.airline`.

Now, we can query flights to Ford airport by Endeavor by name.

```{r}
flights_airports_airlines %>%
  filter(
    str_detect(name.airport, "Ford"),
    str_detect(name.airline, "Endeavor"),
    ) %>%
  select(year, month, day, carrier, name.airport, name.airline)
```

## Assessing Delays

We new attempt to find patterns in delays on flights departing from NYC. 

### According to Season

Here, we plot average departure delays over time.

```{r warning=FALSE, message=FALSE}
library(lubridate)

flights %>%
  mutate(date = ymd(paste(year, month, day))) %>%
  group_by(date) %>%
  summarize(avg_dep_delay = mean(dep_delay, na.rm = TRUE)) %>%
  ggplot() +
  aes(x = date, y = avg_dep_delay) +
  geom_point() +
  geom_smooth() +
  labs(
    title = "Average Departure Delays from NYC Airports",
    x = "Date",
    y = "Average Delay (mins)",
  )
```
The delays show a moderate rise in the summer and holiday months.

### According to Location

We now consider the effect of geography on flight delays. 

Here is a simple scatter plot based on latitude and longitude of all the airports in the dataset. Perhaps you recognize the pattern. 

```{r warning=FALSE, message=FALSE}
airports %>%
  filter(lon < 0) %>%
  ggplot() +
  aes(x = lon, y = lat) +
  geom_point()
```

The pattern is a bit easier if we filter out positive longitude values. These simple lat-long plots are not particularly accurate, geospatially, which we&rsquo;ll address later in the course, but they can be marginally useful.

Here, we plot the average delay associated with airport, along with the volume of flight traffic.

```{r warning=FALSE, message=FALSE}
flights_airports %>%
  group_by(dest, lat, lon) %>%
  summarize(avg_dep_delay = mean(dep_delay, na.rm = TRUE),
            count_dep = n(),
            ) %>%
  ggplot() +
  aes(x = lon, 
      y = lat, 
      color = avg_dep_delay, 
      size = count_dep,
      ) +
  geom_point() +
  labs(
    title = "Average Departure Delays From NYC by Destination",
    x = "Date",
    y = "Average Delay (mins)",
  ) 
```

There don't appear to be obvious geospatial patterns here, but the high-volume airports appear to have smaller delays that some of the less-frequented airports.

