Data Tidying and Transformation in R.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "# Data Tidying and Transformation in R\n",
    "## by Diya Das, David DeTomaso, and Andrey Indukaev\n",
    "\n",
    "### The goal\n",
    "Data tidying is a necessary first step for data analysis - it's the process of taking your messily formatted data (missing values, unwieldy coding/organization, etc.) and literally tidying it up so it can be easily used for downstream analyses. To quote Hadley Wickham, \"Tidy datasets are easy to manipulate, model and visualise, and have a specific structure:\n",
    "each variable is a column, each observation is a row, and each type of observational unit\n",
    "is a table.\"\n",
    "\n",
    "These data are actually pretty tidy, so we're going to be focusing on cleaning and manipulation, but these manipulations will give you some idea of how to tidy untidy data.\n",
    "\n",
    "### The datasets\n",
    "We are going to be using the data from the R package [`nycflights13`](https://cran.r-project.org/web/packages/nycflights13/nycflights13.pdf). There are five datasets corresponding to flights departing NYC in 2013. We will **load directly into R from the library**, but the repository also includes TSV files we created for the purposes of the Python demo and can also be used to load the data into our R session."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "*** If you've never run Jupyter notebooks with R, please run `conda install -c r r-essentials`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "options(repos=structure(c(CRAN=\"http://cran.cnr.berkeley.edu/\", \n",
    "BioCsoft=\"http://www.bioconductor.org/packages/release/bioc/\")))\n",
    "ipak <- function(pkg){\n",
    "     new.pkg <- pkg[!(pkg %in% installed.packages()[, \"Package\"])]\n",
    "     if (length(new.pkg))\n",
    "         install.packages(new.pkg, dependencies = TRUE)\n",
    "     sapply(pkg, require, character.only = TRUE)\n",
    " } #https://gist.github.com/stevenworthington/3178163\n",
    "\n",
    "pkgs <- c(\"nycflights13\",\n",
    "       \"tidyr\",\n",
    "       \"dplyr\",\n",
    "       \"reshape\",\n",
    "       \"ggplot2\",\n",
    "       \"data.table\")\n",
    "ipak(pkgs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "#invisible(sapply(pkgs, library, character.only=TRUE )) \n",
    "    # if you knew you had installed the packages"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reading data from a file\n",
    "Let's read data from a file, though we won't be using it for this exercise, because we will be loading directly from the `nycflights13` library.\n",
    "\n",
    "First, if you are given a zip file, you can unzip the file directly from R."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "unzip(\"data/nycflights13.zip\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can list the files in the given directory."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "list.files(\"data\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now let's read in an example file, though we won't be using it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "read.delim(\"data/airlines.tsv\") # read.table is often used as well"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Inspecting a data frame // What's in the `flights` dataset?\n",
    "Let's run through an example using the `flights` dataset. This dataset includes...well what does it include? You could read the documentation, but let's take a look first. \n",
    "\n",
    "What are the dimensions of the flights data frame?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "flights <- data.frame(flights) # dplyr has introduced a new data format that I am ignoring\n",
    "dim(flights)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What are the first 6 rows?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "head(flights)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What are the last 6 rows?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "tail(flights)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What does the sample function do?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "sample(1:6,2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What happens when I use sample for indexing?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "flights[sample(1:nrow(flights),10),]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Identifying and removing NAs in a dataset\n",
    "We noticed some NAs above (hopefully). How do you find them and remove observations for which there are NAs? \n",
    "\n",
    "Are there any NAs in the flights data frame? Are all values NA?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "print(any(is.na(flights)))\n",
    "print(all(is.na(flights)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Selecting for flights where there is complete data, what are the dimensions? \n",
    "\n",
    "`complete.cases` returns a logical vector indicating whether all observations in a row are not-NA."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "message('Using base R...')\n",
    "flights_complete <- flights[complete.cases(flights),]\n",
    "print(dim(flights_complete))\n",
    "message('Using tidyR...')\n",
    "flights_complete2 <- drop_na(flights)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Are they the same datasets?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "identical(flights_complete,flights_complete2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "How might I obtain a summary of the original dataset?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "summary(flights)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Performing a function along an axis // Calculating mean times\n",
    "\n",
    "R allows easy application of descriptive function along an axis.\n",
    "\n",
    "`any` and `all`, which we used earlier, is an example of that.  If the data are boolean, `any` collapses a series of boolean values into True if *any* of the values are true. `all` collapses a series of boolean values into True if *all* of the values are true.\n",
    "\n",
    "What's the mean air time?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "mean(flights_complete$air_time)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Can we calculate the mean for multiple subsets of our data at once?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "timedat <- select(flights_complete, air_time, dep_delay, arr_delay)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Find mean of each row..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "head(apply(timedat,1, mean))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Find mean of each column..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "apply(timedat,2, mean)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Find mean of each column...yet another way..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "lapply(timedat, mean)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's fix the formatting..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "sapply(timedat, mean)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Using `dplyr`..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "timedat %>%  summarise_all(mean) %>% print()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Performing column-wise operations while grouping by other columns // Departure delay by airport of origin\n",
    "Sometimes you may want to perform some aggregate function on data by category, which is encoded in another column. Here we calculate the statistics for departure delay, grouping by origin of the flight - remember this is the greater NYC area, so there are only three origins!\n",
    "\n",
    "First let's use `tapply`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "tapply(flights_complete$dep_delay, flights_complete$origin, summary)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "That code is a bit messy, so using the with command for indicating the parent \n",
    "data frame..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "with(flights_complete, \n",
    "           tapply(dep_delay, origin, summary)\n",
    "          )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Using `dplyr`..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "flights_complete %>% group_by(origin) %>% summarise(avg_dep_delay=mean(dep_delay)) %>% print()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pipes in R: making code readable\n",
    "The last sections have used the operator `%>%`. This symbol is called a pipe. It was introduced in the `magrittr` package, but `dplyr` and `tidyr` also take advantage of this syntax.\n",
    "\n",
    "Pipes `%>%` exist because they help tidy up commands when performing a chain of operations. When we want to provide to a `function1` some data which is output of a `function2`, whose input is output from `function3`, we can end up with some very nested, difficult-to-read commands:\n",
    "`function1(function2(function3(data,parameters3),parameters2),parameters1)`\n",
    "\n",
    "Sometimes, `with` may help simplify your commands, as above, but piping can be more direct. \n",
    "\n",
    "`data %>% function(parameters)` is exactly the same as `function(data,parameters)`\n",
    "\n",
    "But the code is read (and written) from the left to the right (and not inside-out) and is easier to understand.\n",
    "\n",
    "\n",
    "## A more complicated example of piping // Calculating average departure delay in one call\n",
    "Let's remove rows with missing values (NA) from `flights` dataset, then calculate the average departure delay in one call, first using basic syntax then using pipes.\n",
    "\n",
    "Using standard R syntax:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "with(flights[complete.cases(flights),], \n",
    "           tapply(dep_delay, origin, summary)\n",
    "          )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Same operation using pipes and `dplyr`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "flights %>% filter(complete.cases(.)) %>% \n",
    "    group_by(origin) %>% \n",
    "        summarise(avg_dep_delay = mean(dep_delay)) %>% \n",
    "            print()\n",
    "\n",
    "flights %>% drop_na() %>%\n",
    "    group_by(origin) %>% \n",
    "        summarise(avg_dep_delay = mean(dep_delay)) %>% \n",
    "            print()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notes:\n",
    "1. `.` stands for the data frame in the call of type: `data %>% function1(function2(data))`.\n",
    "2. In case of multi-line call, `%>%` should be in the end of a line.\n",
    "3. There are different schools of thought on piping. Some prefer to make code readable by doing all operations step-by-step."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Merging tables 'vertically' // Subsetting and re-combining flights from different airlines\n",
    "You will likely need to combine datasets at some point. R provides quite a few tools to do that, and as you've seen, it's possible to do something many different ways.\n",
    "\n",
    "Here, we present a simple case of 'vertical' merging, using `rbind`. Let's create a data frame with information on flights by United Airlines and American Airlines only, by creating two data frames via subsetting data about each airline one by one and then merging. \n",
    "\n",
    "The main requirement is that the columns must have the same names (may be in different order).\n",
    "\n",
    "Subsetting the dataset to make 2 data frames:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "flightsUA <- flights[flights$carrier == 'UA',]\n",
    "flightsAA <- flights[flights$carrier == 'AA',]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Checking the number of rows in two data frames:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "print(nrow(flightsUA) + nrow(flightsAA))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Combining two data frames then checking the number of rows in the resulting data frame:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "flightsUAandAA <- rbind(flightsUA,flightsAA)\n",
    "print(nrow(flightsUAandAA))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Nothing special, just be sure the data frames have the columns with the same names and types.\n",
    "\n",
    "A useful tip is to use `do.call` in order to merge more than two data frames.\n",
    "`do.call` is a function that applies a function to a list of elements.\n",
    "\n",
    "`rbinding` 3 data frames and checking the number of rows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "print(nrow(do.call(rbind, list(flightsUA,flightsAA,flightsUAandAA))))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## A useful tip for populating data frames within a loop\n",
    "'rbind' is really useful for populating a data frames, but it can be a bit slow within loops. Each time we append a row to a data frame within a loop a new copy of a data frame is stored in memory :( \n",
    "\n",
    "The solution is to create a list of lists and then merge them with `do.call rbind` combo. But since ``rbind``, as many native R functions, is slow and not memory-efficient, for large datasets one may want to use\n",
    "``rbindlist`` function from ``data.table`` package, which does the same operation, but faster. \n",
    "\n",
    "Let's compare these approaches using the `system.time` function to see the execution times."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "message('execution time for rbind')\n",
    "system.time(do.call(rbind, list(flightsUA,flightsAA,flightsUAandAA)))\n",
    "\n",
    "message('execution time for rbindlist, written in C')\n",
    "system.time(rbindlist(list(flightsUA,flightsAA,flightsUAandAA)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And now the example of using rbindlist for populating a data frame.\n",
    "Let's pretend we forgot how to use `tapply` or `group_by` (`dplyr`) and we want to calculate the average arrival and departure delays per airline."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "Start <- Sys.time()\n",
    "carriers  <- unique(flights_complete$carrier)\n",
    "resList <- list()\n",
    "for (i in 1:length(carriers))\n",
    "{\n",
    "    meanDepDelay <- mean(flights_complete[flights_complete$carrier == carriers[i],]$dep_delay)\n",
    "    meanArrDelay <- mean(flights_complete[flights_complete$carrier == carriers[i],]$arr_delay) \n",
    "    resList[[i]] <- list(carriers[i],meanDepDelay,meanArrDelay)\n",
    "}\n",
    "DelaysByAirline <- rbindlist(resList)\n",
    "colnames(DelaysByAirline) <- c(\"carrier\",\"meanDepDelay\",\"meanArrDelay\")\n",
    "End <- Sys.time()\n",
    "message('It took us')\n",
    "print(End-Start)\n",
    "message('and here is the result for American Airlines')\n",
    "print(DelaysByAirline[DelaysByAirline$carrier == 'AA',])\n",
    "\n",
    "message('Same result without messing with loops')\n",
    "Start <- Sys.time()\n",
    "flights_complete %>% group_by(carrier)%>%\n",
    "    summarize(meanDepDelay = mean(dep_delay), meanArrDelay = mean(arr_delay))%>%\n",
    "        filter(carrier == 'AA') %>% print()\n",
    "End <- Sys.time()\n",
    "message('And it took us')\n",
    "print(End-Start)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In most cases loops are possible to avoid, but if you have to write one, the \"list of lists\" + `rbindlist` approach may save you a lot of time."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Merge two tables by a single column // What are the most common destination airports?\n",
    "The `flights` dataset has destination airports coded, as three-letter airport codes. I'm pretty good at decoding them, but you don't have to be. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "print(head(airports))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `airports` table gives us a key! Let's merge the `flights` data with the `airports` data, using `dest` in `flights` and `faa` in `airports`.\n",
    "\n",
    "This is pretty easy in base R..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "flights_readdest <- merge(flights_complete, airports, by.x='dest', by.y = 'faa', all.x=TRUE)\n",
    "head(flights_readdest)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And you can do it in `dplyr` too..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "flights_readdest2 <- left_join(flights_complete, airports, by = c(\"dest\" = \"faa\"))\n",
    "head(flights_readdest2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Why did we use `all.x = TRUE` and `left_join`?**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "setdiff(flights$dest, airports$faa)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Well this merged dataset is nice, but do we really need all of this information?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "colnames(flights_readdest)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "flights_sm <- select(flights_readdest,origin, dest=name, year, month, day, air_time)\n",
    "head(flights_sm)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Why would you want to ever use `select`? `dplyr` lets you chain operations using the pipes, as discussed above. Let's calculate the average air time for various flight paths, using origin and the readable version of destination airport."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "airtime <- left_join(flights_complete, airports, by = c(\"dest\" = \"faa\")) %>% \n",
    "    select(origin, dest=name, air_time) %>% \n",
    "    group_by(origin, dest) %>% \n",
    "    summarize(avg_air_time = mean(air_time))\n",
    "\n",
    "print(head(airtime))\n",
    "print(dim(airtime))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What's the longest flight from each airport, on average?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "with(airtime, by(avg_air_time, origin,  max)) \n",
    "with(airtime, tapply(avg_air_time, origin,  max)) \n",
    "airtime %>% group_by(origin) %>% summarise(max(avg_air_time))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's find the flight info for the max airtimes from each airport."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "with(airtime, tapply(avg_air_time, origin, function(x) which.max(x)))\n",
    "message('That\\'s the index in the grouped version, but not very useful')\n",
    "setDT(airtime)[,.SD[which.max(avg_air_time)],by=origin] #data.table package, http://stackoverflow.com/questions/30294088#comment48685310_30294186\n",
    "airtime %>% group_by(origin) %>% filter(avg_air_time == max(avg_air_time)) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pivot Table // Average flight time from origin to destination\n",
    "\n",
    "Let's put destinations in rows and origins in columns, and have `air_time` as values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "spread(airtime, origin, avg_air_time)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice the NA in the last dest row. Can you guess what happened? (This was human input error; we skipped a step in our analysis.)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Multi-column merge // What's the weather like for departing flights?\n",
    "Flights...get delayed. What's the first step if you want to know if the departing airport's weather is at all responsible for the delay? Luckily, we have a `weather` dataset for that.\n",
    "\n",
    "Let's take a look."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "head(weather)\n",
    "intersect(colnames(flights_complete), colnames(weather)) # What columns do they share?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "flights_weather <- merge(flights_complete, weather, \n",
    "                         by=c(\"year\", \"month\",\"day\",\"hour\", \"origin\"))\n",
    "print(dim(flights_complete))\n",
    "print(dim(flights_weather))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What if you only want to look at long delays?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "flights_weather_posdelays <- filter(flights_weather, dep_delay>200)\n",
    "print(dim(flights_weather_posdelays))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Arranging a data frame // What's the weather like for the most and least delayed flights?\n",
    "\n",
    "Let's sort the `flights_weather` data frame on `dep_delay` and get data for the top 10 and bottom 10 delays."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "flights_weather %>% arrange(desc(dep_delay)) %>% head(10)\n",
    "flights_weather %>% arrange(dep_delay) %>% tail(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "flights_weather %>% arrange(dep_delay) %>% head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Some other tidying\n",
    "## Capitalization issues\n",
    "Sometimes your data is messy, and capitalization is inconsistent. A lot of matching functions in R have flags to ignore case, but you can also bypass this issue altogether using `tolower` and `toupper`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "print(head(tolower(flights_complete$dest)))\n",
    "print(head(toupper(airports$name)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Wide to long formatted data\n",
    "Our current `flights_complete` data frame is wide-formatted data. Each variable (measurement) for an observation (flight) is a separate column in that row.\n",
    "\n",
    "For some plotting functions, including `ggplot` in R, it's easier to plot data for multiple series on the same plot axes if the values are all in one column, and the series label is another. To do that, we need to format our data in the long format. In this case, we're going to plot both arrival and departure delays on the same plot, so we need to create a column for \"delay time\" (here called `value` because of the `melt` function) and another column `type_of_delay` to encode the series."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "print(head(flights_complete))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Using `reshape`..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "day_delay <- melt(flights_complete, id.vars=c(\"time_hour\"), \n",
    "                measure.vars=c(\"dep_delay\",\"arr_delay\"), variable_name = \"type_of_delay\")\n",
    "print(head(day_delay))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Using `tidyr`..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "day_delay <- gather(flights_complete, `dep_delay`,`arr_delay`, \n",
    "                    key = \"type_of_delay\", value=\"value\") # note the backticks here!\n",
    "print(head(day_delay))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "ggplot(day_delay,aes(x=time_hour,y=value,colour=type_of_delay, group=type_of_delay)) + geom_point()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Well this is a bit hard to read. What about the first entry for each type of delay in each hour? \n",
    "\n",
    "## Removing duplicates, sorting on multiple columns\n",
    "We're going to use the `distinct` function from `tidyR` and first group by `time_hour` and `type_of_delay` before removing duplicates. We don't want to remove an entire series as would happen if you only grouped by `time_hour` on your *melted* data frame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "day_delay_first <- distinct(day_delay, time_hour, type_of_delay, .keep_all = TRUE) \n",
    " # keep all columns\n",
    "print(head(day_delay_first))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "ggplot(day_delay_first,aes(x=time_hour,y=value,colour=type_of_delay, group=type_of_delay)) + geom_point()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## An incomplete investigation of NAs \n",
    "\n",
    "Let's examine where there are NAs in the `flights` dataset.\n",
    "\n",
    "We can use `which` in R, with the `arr.ind` argument to get `(row, col)` positions. This is really handy if we just want to examine the columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "ind <- which(is.na(flights), arr.ind = TRUE)\n",
    "print(head(ind))\n",
    "\n",
    "message('Let\\'s make a handy summary using table which gives frequencies of each value:')\n",
    "ind2 <- table(ind[,2])\n",
    "print(ind2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "But what do those numbers, 4-15, mean? They're column numbers. We can use `colnames` to get the original column names, and `names` to access and rename the `ind2` vector. (It's not a matrix/data frame so it doesn't have *column* names.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "names(ind2) <- colnames(flights)[as.numeric(names(ind2))]\n",
    "  # This is kind of fancy-looking, but we are converting the names of ind2 to numeric because\n",
    "  # they are strings, and using those numeric indices to get the right names from colnames\n",
    "print(ind2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's just grab our rows with NAs."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "flights_incomplete <- flights[!complete.cases(flights),]\n",
    "print(dim(flights_incomplete))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Do flights with NA departure time also have an NA departure delay? One way to tell is to check if number of instances where both the departure time and the departure delay are NA is the same as the number of instances where at least one of them is NA."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "print(table(is.na(flights_incomplete$dep_time) & is.na(flights_incomplete$dep_delay)))\n",
    "print(table(is.na(flights_incomplete$dep_time) | is.na(flights_incomplete$dep_delay)))\n",
    "\n",
    "print(table(is.na(flights_incomplete$dep_time),is.na(flights_incomplete$dep_delay)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Yes. (My sneaking suspicion is that the flights were cancelled.)"
   ]
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "R",
   "language": "R",
   "name": "ir"
  },
  "language_info": {
   "codemirror_mode": "r",
   "file_extension": ".r",
   "mimetype": "text/x-r-source",
   "name": "R",
   "pygments_lexer": "r",
   "version": "3.3.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}