Data Tidying and Transformation in Python.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "# Data Tidying and Transformation in Python\n",
    "## by David DeTomaso, Diya Das, 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 transformation, 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 CSV files we created for the purposes of the Python demo."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Python requirements\n",
    "\n",
    "For this tutorial we'll be using the following packages in Python\n",
    "- pandas (depends on numpy)\n",
    "- seaborn (depends on matplotlib)\n",
    "\n",
    "You can install these with either `pip` or `conda`\n",
    "\n",
    "### pandas\n",
    "\n",
    "Pandas is an extremely useful package for data-manipulation in python.  It allows for a few things:\n",
    "\n",
    "- Mixed types in a data matrix\n",
    "- Non-numeric row/column indexes\n",
    "- Database-like join/merge/group-by operations\n",
    "- Data import/export to a variety of formats [(text, Excel, JSON)](http://pandas.pydata.org/pandas-docs/stable/api.html#input-output)\n",
    "\n",
    "The core pandas object is a 'DataFrame' - modeled after data frames in R."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from __future__ import print_function # For the python2 people\n",
    "import pandas as pd # This is typically how pandas is loaded"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reading data from a file\n",
    "Let's read data from a file\n",
    "\n",
    "There are five tables we'll be using as part of the `nycflights13` dataset\n",
    "\n",
    "They are located in the `data` folder as TSV files, but you can also extract the archive that comes with this repo using bash:\n",
    "\n",
    "```bash\n",
    "unzip data/nycflights13.zip -d data\n",
    "```\n",
    "\n",
    "Now, to read them in as DataFrames, we'll use the **[read_table](http://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_table.html)** function from pandas\n",
    "\n",
    "This is a general purpose function for reading tabular data in a text file format.  If you follow the link, you can see that there are many configurable options.  We'll just use the defaults (assumes tab-delimited)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "airlines = pd.read_table(\"data/airlines.tsv\")\n",
    "airports = pd.read_table(\"data/airports.tsv\")\n",
    "flights = pd.read_table(\"data/flights.tsv\")\n",
    "planes = pd.read_table(\"data/planes.tsv\")\n",
    "weather = pd.read_table(\"data/weather.tsv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Inspecting a DataFrame // 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."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Anatomy of a pandas DataFrame\n",
    "\n",
    "There are a couple of concepts that are important to understand when working with DataFrames\n",
    "- DataFrame class\n",
    "- Series\n",
    "- Index / Columns\n",
    "\n",
    "To understand these, let's look at the 'planes' DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "print(type(planes)) # Yup, it's a DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# What does it look like?\n",
    "planes # Jupyter Notebooks do some nifty formatting here"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# How big is it?\n",
    "print(planes.shape) # Works like numpy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "print(planes.columns) # What are the column labels?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "print(planes.index) # What are the row labels?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Let's grab a column\n",
    "planes['manufacturer']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Inspecting this column further\n",
    "manufacturer = planes['manufacturer']\n",
    "print(type(manufacturer)) # It's a Series"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Series\n",
    "one-dimensional and only have one set of labels (called index)\n",
    "\n",
    "### DataFrames \n",
    "two-dimensional and have row-labels (called index) and column-labels (called columns)\n",
    "DataFrames are made up of Series (each column is a series)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Indexing into Series\n",
    "print(\"Indexing into Series: \", manufacturer[3])\n",
    "\n",
    "# Indexing into DataFrame\n",
    "print(\"Indexing into DataFrame: \", planes.loc[3, 'manufacturer'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### DataFrame Indexing\n",
    "\n",
    "We already showed that you can grab a column using\n",
    "```\n",
    "dataframe[column_name]\n",
    "```\n",
    "\n",
    "To grab a row, use:\n",
    "```\n",
    "dataframe.loc[row_name]\n",
    "```\n",
    "\n",
    "And to grab a specific element use:\n",
    "```\n",
    "dataframe.loc[row_name, column_name]\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "third_row = planes.loc[3] # get the third row\n",
    "third_row"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "print(type(third_row))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### DataFrame index\n",
    "\n",
    "So far the row-index has been numeric (just 0 through ~3300).  However, we might want to use labels here too.\n",
    "\n",
    "To do this, we can select a column to be the DataFrame's index\n",
    "**Only do this if the column contains unique data**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "planes = planes.set_index('tailnum')\n",
    "\n",
    "# OR\n",
    "\n",
    "planes = pd.read_table('data/planes.tsv', index_col=0) #Set the first column as the index\n",
    "\n",
    "planes.loc['N10156']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### But now how do I get the 3rd row?\n",
    "\n",
    "Here's where **iloc** comes into play.\n",
    "\n",
    "Works like **loc** but uses integers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "print(planes.iloc[3]) # Get the third row\n",
    "print(planes.iloc[:, 3]) # Get the third column"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exploring our dataset - let's look at the `flights` table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "print('What are the first 5 rows?')\n",
    "flights.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "print('What are the last 5 rows?')\n",
    "flights.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "print('Sample random rows')\n",
    "flights.sample(3, axis=0) # Axis 0 represents the rows, axis 1, the columns "
   ]
  },
  {
   "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? "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "print('What are the dimensions of the flights DataFrame?\\n')\n",
    "print(flights.shape)\n",
    "\n",
    "print('Are there any NAs in the flights DataFrame?\\n')\n",
    "print(flights.isnull().any())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "print('Selecting for flights where there is complete data, what are the dimensions?\\n')\n",
    "print(\"Original Matrix Shape:\", flights.shape)\n",
    "null_rows = flights.isnull().any(axis=1) # Rows where any value is null\n",
    "flights_complete = flights.loc[~null_rows]\n",
    "print(\"Complete-rows shape:\", flights_complete.shape)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Aside: Why does this work with loc?  \n",
    "\n",
    "Earlier I showed .loc operating on row/column labels.\n",
    "\n",
    "Well, it can also operate on boolean (true/false) lists (or numpy arrays, or **pandas Series**)\n",
    "\n",
    "Above, what is null_rows?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "print(type(null_rows))\n",
    "null_rows"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The great thing about Pandas is that if you pass in a Series, the order of the elements in it doesn't matter anymore.  It uses the index to align the Series to the row/column index of the DataFrame.\n",
    "\n",
    "This is very useful when creating a boolean index from one DataFrame to be used to select rows in another!\n",
    "\n",
    "Alternately, with removing NA values there is a [dropna](http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.dropna.html) function that can be used.\n",
    "\n",
    "Now...back to `flights`!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "print('How might I obtain a summary of the original dataset?')\n",
    "flights.describe() # Similar to R's 'summary'\n",
    "# use include='all' to include the non-numberic columns too"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Performing a function along an axis\n",
    "\n",
    "Pandas allows easy application of descriptive function along an axis.\n",
    "\n",
    "**any** which we used earlier, is an example of that.  If the data is boolean, any collapses a series of boolean values into True if *any* of the values are true (otherwise, False)\n",
    "\n",
    "Can also use min, max, mean, var, std, count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# An example\n",
    "flights['air_time'].mean() # Returns a single value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "subset = flights[['air_time', 'dep_delay', 'arr_delay']]\n",
    "subset.mean(axis=0) # Axis 0: collapse all rows, result has Index = to original Columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you want to apply an arbitrary function along an axis, look into the [apply function](http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.apply.html)"
   ]
  },
  {
   "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!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "result = flights_complete.groupby('origin')['dep_delay'].mean()\n",
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# What is this object?\n",
    "print(type(result))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Other descriptive functions work here, like 'std', 'count', 'min', 'max'\n",
    "\n",
    "Also: describe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "flights_complete.groupby('origin')['dep_delay'].describe()"
   ]
  },
  {
   "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.  For simple acts of stitching two DataFrames together, the pandas **concat** method is used.\n",
    "\n",
    "Let's create a DataFrame with information on flights by United Airlines and American Airlines only, by creating two DataFrames 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)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "print('Subsetting the dataset to create 2 DataFrames')\n",
    "flightsUA = flights.loc[flights.carrier == 'UA',]\n",
    "flightsAA = flights.loc[flights.carrier == 'AA',]\n",
    "print('Checking the number of rows in 2 DataFrames')\n",
    "print(flightsUA.shape[0] + flightsAA.shape[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "print('Combining two DataFrames then checking the number of rows in the resulting DataFrame')\n",
    "flightsUAandAA = pd.concat([flightsUA,flightsAA], axis=0) # axis=1 would stitch them together horizontally\n",
    "print(flightsUAandAA.shape[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Nothing special, just be sure the DataFrames have columns with the same names and types.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "print('Binding 3 DataFrames and checking the number of rows')\n",
    "allthree = pd.concat([flightsUA,flightsAA,flightsUAandAA])\n",
    "allthree.shape[0]"
   ]
  },
  {
   "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": [
    "airports.head()"
   ]
  },
  {
   "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`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "print('Merging in pandas')\n",
    "flights_readdest = flights_complete.merge(airports, left_on='dest', right_on = 'faa', how='left')\n",
    "flights_readdest.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Why did we use `how='left'`?**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "len(set(airports.faa) - set(flights.dest))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are 1357 airports in the airports table that aren't in the flights table at all.\n",
    "\n",
    "Here are the different arguments for how and what they'd do:\n",
    "\n",
    "- 'left': use all rows for flights_complete, and only rows from airports that match\n",
    "- 'right': use all rows for airports, and only rows from flights that match\n",
    "- 'inner': use only rows for airports and flights that match on the dest/faa columns\n",
    "- 'outer': use all rows from both airports and flights"
   ]
  },
  {
   "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": [
    "flights_readdest.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "flights_sm = flights_readdest[['origin', 'name', 'year', 'month', 'day', 'air_time']]\n",
    "flights_sm.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Renaming is not so simple in pandas\n",
    "flights_sm = flights_sm.rename(columns = {'name': 'dest'})\n",
    "flights_sm.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Since each operation gives us back a DataFrame, they are easily chained:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "airtime = flights_complete.merge(airports, left_on='dest', right_on='faa', how='left') \\\n",
    "    .loc[:, ['origin', 'name', 'air_time']] \\\n",
    "    .groupby(['origin', 'name'])['air_time'] \\\n",
    "    .mean()\n",
    "\n",
    "print(airtime.shape)\n",
    "airtime.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Goal: What's the longest flight from each airport, on average?**\n",
    "\n",
    "Here, `airtime` is a little abnormal because its Index has two levels:\n",
    "    - First level is the `origin`\n",
    "    - Second level is the name of the destination\n",
    "    \n",
    "This is because we grouped by two variables.\n",
    "\n",
    "Now we need to group by `origin` and apply the `max` function.  `groupby` can work for the levels of a multi-index too."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "airtime.groupby(level='origin').max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# What if we want to know where the flight goes?\n",
    "\n",
    "rows = airtime.groupby(level='origin').idxmax() # This returns the indices in airtime where the max was found\n",
    "\n",
    "airtime[rows] # Index by it to get the max rows"
   ]
  },
  {
   "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": [
    "pvt_airtime = airtime.unstack() # Since airtime has a hierarchical index, we can use unstack\n",
    "pvt_airtime"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "However, often you want to pivot just a regular DataFrame.  I'll create one from airtime for an example:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "airtime_df = pd.DataFrame(airtime).reset_index()\n",
    "airtime_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "airtime_pv = airtime_df.pivot(index='origin', \n",
    "                columns='name',\n",
    "                values='air_time')\n",
    "airtime_pv"
   ]
  },
  {
   "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": [
    "weather.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "print(flights_complete.columns & weather.columns) # What columns do they share?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "flights_weather = flights_complete.merge(weather, \n",
    "                         on=[\"year\", \"month\",\"day\",\"hour\", \"origin\"])\n",
    "\n",
    "print(flights_complete.shape)\n",
    "print(flights_weather.shape) \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`flights_weather` has less rows.  Default behavior of merge is 'inner' and so this means there are some flight year/month/day/hour/origin combos where we don't have a weather entry."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Let's grab flights+weather where the delay was greater than 200 minutes\n",
    "\n",
    "flights_weather_posdelays = flights_weather.loc[flights_weather.dep_delay > 200]\n",
    "flights_weather_posdelays.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# Anything unusual about these flights?\n",
    "%matplotlib notebook\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "\n",
    "plt.figure()\n",
    "plt.hist(flights_weather.dropna().wind_gust, 30, range=(0, 50), normed=True, label='normal', alpha=.7)\n",
    "plt.hist(flights_weather_posdelays.dropna().wind_gust, 30, range=(0,50), normed=True, label='delayed', alpha=.7)\n",
    "plt.legend(loc='best')\n",
    "plt.title('Wind Gust')\n",
    "\n",
    "plt.figure()\n",
    "plt.hist(flights_weather.dropna().pressure, 30,  normed=True, label='normal', alpha=.7)\n",
    "plt.hist(flights_weather_posdelays.dropna().pressure, 30,  normed=True, label='delayed', alpha=.7)\n",
    "plt.legend(loc='best')\n",
    "plt.title('Pressure')\n",
    "\n",
    "plt.figure()\n",
    "plt.hist(flights_weather.dropna().hour, 30,  normed=True, label='normal', alpha=.7)\n",
    "plt.hist(flights_weather_posdelays.dropna().hour, 30,  normed=True, label='delayed', alpha=.7)\n",
    "plt.legend(loc='best')\n",
    "plt.title('Hour')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Arranging a DataFrame // What's the weather like for the most and least delayed flights?\n",
    "\n",
    "Let's sort the `flights_weather` DataFrame 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.sort_values('dep_delay').head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "flights_weather.sort_values('dep_delay', ascending=False).head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Some other tidying\n",
    "## Capitalization issues."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "flights_complete.dest.str.lower().head() # For string columns, use .str to access string methods"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "flights_complete.dest.str.upper().head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Wide to long formatted data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "flights_complete.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "day_delay = pd.melt(flights_complete, id_vars=['hour', 'time_hour'], value_vars=['dep_delay', 'arr_delay'], var_name='type_of_delay')\n",
    "day_delay"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "plt.figure()\n",
    "sns.stripplot(x='hour', y='value', hue='type_of_delay', data=day_delay)"
   ]
  },
  {
   "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"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "day_delay_first = day_delay.drop_duplicates('time_hour', keep='first')\n",
    "day_delay_first.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## An incomplete investigation of NAs \n",
    "\n",
    "Let's examine where there are NAs in the `flights` dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "flights.isnull().sum(axis=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "flights_incomplete = flights.loc[flights.isnull().any(axis=1)]\n",
    "flights_incomplete.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Do flights with NA departure time also have an NA departure delay?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "pd.crosstab(\n",
    "    index=flights_incomplete.dep_time.isnull(),   # Series of bool values\n",
    "    columns=flights_incomplete.dep_delay.isnull() # series of bool values\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Yes"
   ]
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python [Root]",
   "language": "python",
   "name": "Python [Root]"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}