tutorials/Tutorial-1.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Tutorial 1: Loading and Manipulating Data in Pandas\n",
    "\n",
    "The goal of this tutorial is to get you comfortable doing data analysis using the Pandas library. Pandas is a very powerful library for manipulating data. The documentation can be found online [here](http://pandas.pydata.org/pandas-docs/stable/). You'll want to get comfortable with it. The tutorial assumes you are comfortable with basic constructs from computer programming such as variables, lists, and conditional statements. \n",
    "\n",
    "In this tutorial you'll learn how to:\n",
    "- Load data from .csv files. \n",
    "- Manipulate tabular data in a dataframe to select, filter, transform, sort, and aggregate it. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Finding Data\n",
    "Ok, let's get some data first, then we'll figure out how to load it. Every year the TSA (Transportation Security Agency) confiscates thousands of weapons at airports. Here's a [dataset](https://github.com/comp-journalism/UMD-J479V-J779V-Spring2017/raw/master/Data/tsa-dangerous-items-fy15.csv) of the more than 22,000 weapons that were confiscated in 2015. Right-click that link in the last sentence and download the .csv file locally to your computer. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Loading Data\n",
    "In order to use the Pandas library we need to import it so that Python knows how to access the functionality that it provides. The convention is to call it `pd` as in the following line of code:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are a variety of 'magic' functions that can be useful in Jupyter Notebooks. These are prefixed by the '%' character. \n",
    "\n",
    "Here we're interested to know the current directory of the .ipynb file (this file) so that we can load another file using a relative path. We can get this by using the `%pwd` magic function. (BTW, to see an entire listing of available magic functions use `%quickref`)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%pwd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that we know the current directory of this script, we can construct the correct relative path to our data file. You can use the `read_csv` function and pass the relative path to the file in order to load it. There are *a lot* of [other parameters](http://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_csv.html#pandas.read_csv), so be sure to get comfortable looking at the documentation to understand all of the options. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "pd.read_csv(\"Data/tsa-dangerous-items-fy15.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can easily assign the read data frame to a variable that we can then manipulate in different ways. Writing the variable as the last line in a Jupyter cell will print it for you. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "tsa_df = pd.read_csv(\"Data/tsa-dangerous-items-fy15.csv\")\n",
    "tsa_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice how it has numbered the rows in the first column on the left (that is called the index column) and how the first row is a header row with the names of the columns. If you just want to know the names of the columns you can:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "tsa_df.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's also parse the date column from strings into actual date objects. We can do this by passing an extra parameter to the `read_csv` function that tells it which columns to treat as dates. This is useful if we want to manipulate or filter by dates later on. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "tsa_df = pd.read_csv(\"Data/tsa-dangerous-items-fy15.csv\", parse_dates = [\"date\"])\n",
    "tsa_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note in the output the dates now follow a standard formatting of YYYY-MM-DD when printed, and internally they're stored as python [datetime objects](https://docs.python.org/2/library/datetime.html). "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Saving Data\n",
    "You can output a dataframe to a .csv file after you've manipulated it. We use a function called `to_csv` and a parameter to strip the index column back out. \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "tsa_df.to_csv(\"Data/test_output.csv\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Manipulating Data in Pandas\n",
    "There is a LOT that a DataFrame can do. You can familiarize yourself with all it offers in the [documentation](http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.html). \n",
    "\n",
    "To select a single column of data from the DataFrame you have a couple options:\n",
    "- Use the name of the column within brackets like this: *dataframe[\"column_name\"]*\n",
    "- Use the name of the column after a period like this: *dataframe.column_name*\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "tsa_df[\"item\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "tsa_df.item"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can get multiple columns by specifying them in a list.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "tsa_df[[\"item\", \"date\"]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And if we want to get just one row of data across columns we can use a function called `iloc` to extract that row based on the row index. There are other ways to get at data which you can read about in the [indexing documentation](http://pandas.pydata.org/pandas-docs/stable/indexing.html)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "tsa_df[[\"item\", \"date\"]].iloc[3]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can access the row as a python array of data by using the `values` property. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "tsa_df[[\"item\", \"date\"]].iloc[3].values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To access only a certain range of items from the column (called a [slice](http://pandas.pydata.org/pandas-docs/stable/indexing.html#slicing-ranges)) you can use brackets and specify the range of items"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "print tsa_df.item[0:5] # slices the first 5 objects (starting from the first index which is zero)\n",
    "print tsa_df.item[-1:-6:-1] # slices the last 5 objects"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Filtering\n",
    "You can filter based on data values using conditionals on the columns. For instance, to filter for just the items that were \"Firearms\" we can do something like this:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "tsa_firearms_df = tsa_df[tsa_df.item == \"Firearms\"]\n",
    "tsa_firearms_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "After all that filtering you might wonder how much data you have left. To check the shape (i.e. number of rows and columns) of a DataFrame just append ``.shape`` at the end. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "print tsa_df.shape\n",
    "print tsa_firearms_df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Or maybe we want the items that were Firearms that were confiscated at Baltimore airport, BWI. We can do this by putting each condition in parentheses and using the boolean AND operator which is the ampersand (i.e., \"&\"). You could also combine different filtering criteria using an OR operator which is the pipe (i.e., \"|\")."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "bwi_firearms_df = tsa_df[(tsa_df.item == \"Firearms\") & (tsa_df.airport_code == \"BWI\")]\n",
    "bwi_firearms_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You'll notice that in the filtered data frame the index starts from a different number \"1035\", but maybe we want to reset it to start at zero now that we're focused on BWI. We can do that using the `reset_index` function. The `drop = True` parameter tells it to reset the index to the default integer index. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "bwi_firearms_df = bwi_firearms_df.reset_index(drop=True)\n",
    "bwi_firearms_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Removing missing data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you scroll to the bottom of the original dataframe you will see that there are three rows that are listed \"Nan\". These are empty rows and we want to filter them out. You notice that a lot of datasets you work with are deficient in some way or another. For instance, they may be missing values in some rows and columns. When it loads a file in Pandas is smart enough to mark empty fields as \"NaN\" which stands for Not a Number. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can test for these values using the ``isnull`` and ``notnull`` functions which will return a True / False value based on the value of the item. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "tsa_df.item.isnull()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And we may want to filter out rows with those empty values. We can do that with a special selector syntax. In the following notice that within the brackets we tell it to select rows for which type_telemarketing is not null. Another useful function for removing missing data is `dropna()` which has [parameters](http://pandas.pydata.org/pandas-docs/version/0.17.1/generated/pandas.DataFrame.dropna.html) that allow you to drop rows or columns have have any or all values that are missing. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "tsa_df[tsa_df.item.notnull()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Another way to drop the rows with missing data\n",
    "tsa_df.dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "tsa_df = tsa_df.dropna()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Sorting\n",
    "Oftentimes you will want to sort your data to get an overview or see what is at the top or bottom of a ranking. To sort by values use the `sort_values` [function](http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.sort_values.html#pandas.DataFrame.sort_values). We can sort our BWI data by the date for instance:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "bwi_firearms_sorted_df = bwi_firearms_df.sort_values(by=\"date\", ascending=False)\n",
    "bwi_firearms_sorted_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Aggregation\n",
    "You'll often want to summarize DataFrames to get an overview of your data, or to aggregate it. The `describe()` function is useful for an initial overview, but there are many others such as `min()`, `max()`, `sum()`, `mean()`, and [many others](http://pandas.pydata.org/pandas-docs/stable/basics.html#descriptive-statistics)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "tsa_df.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A useful analytic operation is to create groups that can then be summarized or filtered. This can be accomplished with the `groupby()` [function](http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.groupby.html). Various [aggregation functions](http://pandas.pydata.org/pandas-docs/stable/groupby.html) can then be applied. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "grouped = tsa_df.groupby(\"airport_code\")\n",
    "grouped.size()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can get a single group."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "grouped.get_group(\"BWI\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can also filter groups based on different aggregate values, such as the size of the group. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Filter on airports with more than 500 confiscations, note after the filter we then have to regroup. \n",
    "grouped.filter(lambda x: len(x) > 500).groupby(\"airport_code\").size()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Transformation\n",
    "One of the problems with grabbing the \"BWI\" group above is that it misses some data that was mistakenly input as lowercase \"bwi\" (scroll to the bottom of the cell that lists the size of groups). To normalize that field of the data we need to transform it to be all uppercase. \n",
    "\n",
    "Sometimes you will want to transform your data by applying a transformation function to each datum within a column or row. We define a function which takes in an input datum (x in this case) and returns the transformed value of that. We use the `apply` [function](http://pandas.pydata.org/pandas-docs/version/0.17.1/generated/pandas.DataFrame.apply.html) on the dataframe to apply that function to an entire column (or to an entire row)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def uppercaser(x):\n",
    "    return x.upper()\n",
    "\n",
    "tsa_df.airport_code.apply(uppercaser)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "grouped = tsa_df.groupby(\"airport_code\")\n",
    "grouped.get_group(\"BWI\").shape"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [Root]",
   "language": "python",
   "name": "Python [Root]"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}