SQLite-GettingStarted.Rmd

---
title: 'Airline delay in R: setting up your own SQLite database using dplyr'
author: "Nicholas J. Horton and Randall Pruim"
date: "March 23, 2015"
output:
  html_document:
    fig_height: 4
    fig_width: 6
  pdf_document:
    fig_height: 4
    keep_tex: yes
  word_document:
    fig_height: 4
    fig_width: 6
---


```{r include=FALSE}
# Don't delete this chunk if you are using the mosaic package
# This loads the mosaic and dplyr packages
require(mosaic)
```

```{r include=FALSE}
# Some customization.  
# You can alter or delete as desired (if you know what you are doing).

# This changes the default colors in lattice plots.
trellis.par.set(theme=theme.mosaic())  

# knitr settings to control how R chunks work.
require(knitr)
opts_chunk$set(
  tidy=FALSE,     # display code as typed
  size="small"    # slightly smaller font for code
)
```


This file describes how to create an SQLite database containing a subset 
of the airline delays data using the `dplyr` package in R.


1. [Create a directory to store the data and database](#set_working_dir)
2. [Install necessary R packages](#prelim)
3. [Set up the database](#create_db)
4. [Test the setup](#test_db)

### Create a directory to store the database
<a name="set_working_dir"></a>

By default, the database will be created in the directory where you compile this
RMarkdown file, but you can edit the line below to choose a different location.

```{r set-working-dir}
dbdir <- "."  
# example alternative
# dbdir <- "~/Desktop/AirlineDelays"
```

Let's create the directory (if it doesn't already exist) used to store the database, and change to that working directory.

```{r create-working-directory}
if (!file.exists(dbdir)) {
  if (! try(dir.create(dbdir))) {
   stop("ERROR: can't create ", dbdir, "\n")
  }
}
setwd(dbdir)
old <- opts_knit$set(root.dir=getwd())  # also need to move within markdown 
```


### Install necessary packages
<a name="prelim"></a>

Let's just check what version of R and RStudio that you are running:

```{r version-info}
try(rstudio::versionInfo())
sessionInfo()
```

This should be at least RStudio 0.98 and R 3.1 or later.

Before you begin, you need to install necessary packages.
```{r, load-packages, message=FALSE}
lapply(
  c("RSQLite", "dplyr", "tidyr", "mosaic", "knitr", "lubridate"),
  function(pkg) {
    if (!require(pkg, character.only=TRUE)) try(install.packages(pkg))
  }
)
```


### Set up the database using dplyr
<a name="create_db"></a>

We will read in some CSV data files, change some of the names to make them more
consistent, and the store the results in an SQLite database.
```{r, fetch-files, message=FALSE}
planes <- 
  read.file("https://raw.githubusercontent.com/ProjectMOSAIC/databases/master/Data/plane-data.csv") %>%
  rename( 
    TailNum = tailnum,
    Type = type,
    Manufacturer = manufacturer,
    IssueDate = issue_date,
    Model = model,
    Status = status,
    AircraftType = aircraft_type,
    EngineType = engine_type,
    Year = year
  )
  
airports <- read.file("https://raw.githubusercontent.com/ProjectMOSAIC/databases/master/Data/airports.csv") %>% 
  rename(
    IATA = iata,
    Airport = airport,
    City = city,
    State = state,
    Country = country,
    Latitude = lat,
    Longitude = long
    )

# finally one with names we can keep as is
carriers <- 
  read.file("https://raw.githubusercontent.com/ProjectMOSAIC/databases/master/Data/carriers.csv") 
```

```{r fetch-flights}
# this is one breaking with a "back-end read error" when attempting from github
flights <- read.file("http://www.amherst.edu/~nhorton/precursors/files/2014-01.csv") %>%
  rename(
    DayOfMonth = DayofMonth
  )
```

```{r build-database}
# create a new (empty) data base
if (file.exists("Airlines.sqlite3")) { file.remove("Airlines.sqlite3") }
Airlines_db <- src_sqlite("Airlines.sqlite3", create = TRUE)

# now pump in some tables, adding indices to make access faster
copy_to(Airlines_db, planes,  temporary = FALSE, 
        indexes = list( 
          "tailnum")
        )

copy_to(Airlines_db, airports, temporary=FALSE,
        indexes = list(
          "iata",
          "airport",
          c("city", "state", "country")
          )
        )

copy_to(Airlines_db, carriers, temporary=FALSE,
        indexes = list(
          "code"
          )
        )

copy_to(Airlines_db, flights, temporary=FALSE,
        indexes = list(
          c("Year", "Month", "DayofMonth"),
          "DayOfWeek",
          "UniqueCarrier",
          "FlightNum",
          "TailNum",
          "Origin",
          "Dest"
          )
        )
```


### Test the setup
<a name="test_db"></a>

```{r test-extraction}
my_db <- src_sqlite(path="Airlines.sqlite3")
flights <- tbl(my_db, "flights")   # link to some useful tables
airports <- tbl(my_db, "airports")
carriers <- tbl(my_db, "carriers")
planes <- tbl(my_db, "planes")
airports %>% 
  filter(IATA %in% c('ALB', 'BDL', 'BTV'))   # what are these airports?
```

If this displays information about Albany, Bradley, and Burlington airport, then you have 
successfully set up a database system and at least the `airports` table would appear
to have the correct data in it.

```{r}
opts_knit$set(root.dir=old$root.dir)
```

To perform additional tests, download and knit [this additional Rmd file](  "https://raw.githubusercontent.com/ProjectMOSAIC/databases/master/SQLite-Testing.Rmd")
```