README.Rmd

---
title: "airlines"
output:
  github_document:
    html_preview: true
---

[![Travis-CI Build Status](https://travis-ci.org/beanumber/airlines.svg?branch=master)](https://travis-ci.org/beanumber/airlines)

The `airlines` package provides a user-friendly interface to create and maintain an SQL database of flight information from the [U.S. Bureau of Transportation Statistics Airline On-Time Performance](http://www.transtats.bts.gov/DatabaseInfo.asp?DB_ID=120&Link=0) data. The user of the `airlines` package only needs a valid place to store the data -- no sophisticated SQL administration skills are necessary. 

Several existing R package could be considered subsets of these data:

#. [nycflights13](http://github.com/hadley/nycflights13): all outgoing flights from the three New York City airports (LGA, JFK, and EWR) during 2013
#. [hflights](http://github.com/hadley/hflights): all outgoing flights from the three New York City airports (IAH and HOU) during 2011

This `airlines` package will allow you to download data for over 165 million flights from 1987 to present, from all domestic airports. 

## Install

The [`etl`](http://github.com/beanumber/etl) package (on CRAN) provides the generic framework for the `airlines` package. Since the `airlines` package currently lives on GitHub and not on CRAN, you have to install it using `devtools`: 

```{r, eval=FALSE, message=FALSE}
install.packages("devtools")
devtools::install_github("beanumber/airlines")
```

To begin, load the `airlines` package. Note that this loads `etl`, which in turn loads `dplyr`. 

```{r, message=FALSE}
library(airlines)
```

## Populate

Any `etl`-derived package can make use of the SQL backends supported by `dplyr`. Here, we illustrate how to set up a local MySQL database to store the flight data. This approach uses a MySQL options file located at `~/.my.cnf`. 

```{r, eval=TRUE, message=FALSE}
system("mysql -e 'CREATE DATABASE IF NOT EXISTS airlines;'")
db <- src_mysql_cnf(dbname = "airlines")
```

Once we have a database connection, we create an `etl` object, initialize the database, and then populate it with data. Please note that to update the database with all 30 years worth of flights may take a few hours. 

```{r}
ontime <- etl("airlines", db = db, dir = "~/dumps/airlines")
```

```{r, eval=FALSE}
ontime %>%
  etl_init() %>%
  etl_update(years = 1987:2016)
```

## Verify

There are over 300 months worth of files to download, and they will occupy more than 21 GB in their zipped and unzipped states. 

```{r}
summary(ontime)
```

The full flights table should contain about 169 million flights from October 1987 to June 2016. 

```{r, eval=TRUE}
ontime %>%
  tbl(from = "flights") %>%
  summarise(numFlights = n())
```

## Analyze

The number of flights per year seems to have peaked in 2007. 

```{r, eval=TRUE}
ontime %>%
  tbl(from = "flights") %>%
  group_by(year) %>%
  summarise(numMonths = n_distinct(month), numFlights = n()) %>%
  print(n = 40)
```

Please see [the vignette](https://github.com/beanumber/airlines/blob/master/vignettes/intro.Rmd) for more detail about how to use this package.