Part 1 - Basics.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## About this Workshop\n",
    "\n",
    "This workshop is designed as an introduction to using the package known as pandas.  \n",
    "\n",
    "By the end of this workshop, you should be able to:  \n",
    "    -read a csv into a dataframe  \n",
    "    -filter by columns  \n",
    "    -run some basic statistics on that dataframe  \n",
    "    -graph the data using a second package called seaborn.  \n",
    "\n",
    "In addition to the 3 notebooks we will be working off of here, a second version is available at: https://github.com/mkitti/pandas-workshop which has been designed to run through google's [Colab environment](https://colab.research.google.com/notebooks/welcome.ipynb#recent=true).  If you are having difficulty downloading the files, this option allows for you to run all of the code through the web."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Introduction to Pandas\n",
    "\n",
    "[Pandas](http://pandas.pydata.org/) is the essential data analysis library for Python programmers. It provides fast and flexible data structures built on top of [numpy](http://www.numpy.org/).\n",
    "\n",
    "It is well suited to handle \"tabular\" data (that might be found in a spreadsheet), time series data, or pretty much anything you care to put in a matrix with rows and named columns.\n",
    "\n",
    "It contains two primary data structures, the `Series` (1-dimensional) and the `DataFrame` (2-dimensional) as well as a host of convenience methods for loading and working with data.\n",
    "\n",
    "The main thing that makes pandas pandas is that all data is *intrinsically aligned*. That means each data structure, `DataFrame` or `Series` has something called an **Index** that links data values with a label. That link will always be there (unless you explicitly break or change it) and it's what allows pandas to quickly and efficiently \"do the right thing\" when working with data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# The canonical way to import pandas:\n",
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## The `Series` Object\n",
    "\n",
    "A `Series` is a one-dimensional array of indexed data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "data = pd.Series([0.1, 0.2, 0.3, 0.4])\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `Series` wraps a 1-d `ndarray` from numpy and an `Index` object."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(data.values)\n",
    "type(data.values)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# This particular index type, the `RangeIndex`, lets us use the\n",
    "# same square-bracket notation as a `ndarray` to access elements:\n",
    "data[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.values[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# or even a slice:\n",
    "data[1:3]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We don't have to use this auto-generated list of integers as the index though. Index values can be specified manually and don't even have to be integers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = pd.Series([0.1, 0.2, 0.3, 0.4], index=['a', 'b', 'c', 'd'])\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Item access works just like before, with square brackets, \n",
    "# even though the index values are strings\n",
    "data['a']\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#once you have labels, you can also access them this way (assuming no spaces in name)\n",
    "data.a"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# slices still work! But note the last element is included this time.\n",
    "# This is the default behavior for indexes.\n",
    "data['a':'c']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# We could create a non-sequential integer index:\n",
    "data = pd.Series([0.1, 0.2, 0.3, 0.4], index=[5, 8, 2, 1])\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.values[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Why?\n",
    "data[1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Remember that the values command (data.values) is converting the column into a numpy array.  That means any indexing follows the numpy rules (which are based on position), not the pandas rules (which are based on index)\n",
    "\n",
    "`Series` are in fact a cross between a numpy array and a python dictionary. You can think of them as a dictionary with *typed* keys and *typed* values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# in fact it is easy to convert a dictionary into a series\n",
    "max_depths_dict = {\n",
    "    'Erie': 64,\n",
    "    'Huron': 229,\n",
    "    'Michigan': 281,\n",
    "    'Ontario': 244,\n",
    "    'Superior': 406,\n",
    "}\n",
    "max_depths = pd.Series(max_depths_dict)\n",
    "max_depths"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# squint and it looks like a dictionary!\n",
    "max_depths['Michigan']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "max_depths_dict['Michigan']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Notice the index in this case was constructed automatically\n",
    "# from the dictionary keys.\n",
    "max_depths.index"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## `Series` Exercise\n",
    "\n",
    "Create `Series` objects from a list, a numpy 1-dimensional `ndarray`, and a dictionary."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Numpy and `Series`\n",
    "\n",
    "Because the values in a `Series` are contained in a numpy `ndarray`, `Series` provides all the benefits of numpy! Namely, this means we get ultra-fast vectorized math operations on the elements of a `Series`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "max_depths * 10"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can use most numpy functions directly on a `Series` object (and later, we'll see `DataFrame` objects as well), but pandas also provides access to these numpy functions through the `Series` object methods."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "np.sin(max_depths)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "np.mean(max_depths)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "max_depths.mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#and if you are lazy and just want a bunch of standard stats\n",
    "max_depths.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`Series` objects also support numpy-style Boolean mask indexing:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "max_depths[max_depths > max_depths.mean()]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And numpy's so-called \"fancy indexing\", IE using a list or array to specify values to access:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "max_depths[['Erie', 'Huron']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## The DataFrame Object\n",
    "\n",
    "Much like the `Series` is a one-dimensional array of indexed data, a `DataFrame` is a two-dimensional array of indexed data.\n",
    "\n",
    "You can think of a `DataFrame` as a sequence of `Series` objects all sharing the same index."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "avg_depths_dict = {\n",
    "    'Erie': 19,\n",
    "    'Huron': 59,\n",
    "    'Michigan': 85,\n",
    "    'Ontario': 86,\n",
    "    'Superior': 149,\n",
    "}\n",
    "\n",
    "avg_depths = pd.Series(avg_depths_dict)\n",
    "\n",
    "lakes = pd.DataFrame({'Max Depth (m)': max_depths, 'Avg Depth (m)': avg_depths})\n",
    "lakes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Just like the `Series`, a `DataFrame` has an `index` property."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "lakes.index"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And a `values` property that exposes the underlying `ndarray`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "lakes.values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And unlike the Series, the DataFrame has a `columns` property, which is also an index."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "lakes.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can get the shape of a dataframe, just like a numpy ndarray:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "lakes.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can do dictionary-style lookups into the dataframe by column name to get a single `Series`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "lakes['Max Depth (m)']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To select more than one column put a list of column names inside the dictionary-style square brackets:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "lakes[['Max Depth (m)','Avg Depth (m)']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Creating new columns\n",
    "\n",
    "Once we have a `DataFrame`, creating new columns is done through simple assignment."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "surface_area = pd.Series({\n",
    "    'Superior': 82097,\n",
    "    'Michigan': 57753,\n",
    "    'Huron': 59565,\n",
    "    'Erie': 25655,\n",
    "    'Ontario': 19009,\n",
    "})\n",
    "\n",
    "lakes['Surface Area (sq km)'] = surface_area\n",
    "lakes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice how the index values allowed pandas to \"align\" the new data with the existing data!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It's also possible to create new columns from existing columns. Say for example we wanted a column to track the difference between the avg depth and max depth. We'll call this the \"depth spread\"."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "lakes['Depth Spread'] = lakes['Max Depth (m)'] - lakes['Avg Depth (m)']\n",
    "lakes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "DataFrames can be created from many different kinds of data structures (`Series` objects, lists, dictionaries, numpy arrays, etc.)\n",
    "\n",
    "If you don't specify an index explicitly when creating the DataFrame, or you are using data without implicit indexes, pandas will create a `RangeIndex` for you:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "call_signs = ['WLUW', 'WNUR', 'WBEZ', 'WXRT', 'WFMT']\n",
    "frequencies = [88.7, 89.3, 91.5, 93.1, 98.7]\n",
    "formats = ['College', 'College', 'Public Radio', 'Adult Album Alternative', 'Classical']\n",
    "radio_station_df = pd.DataFrame(data={'Call Sign': call_signs, 'Frequency': frequencies, 'Format': formats})\n",
    "radio_station_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Setting the index\n",
    "\n",
    "You may want to \"move\" one of the columns to be the index. You can do this with the DataFrame's `set_index` method. By default this returns a new DataFrame with the index replaced with the values in the chosen column.\n",
    "\n",
    "The `inplace` parameter will make the change to the existing DataFrame rather than returning a new one."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "radio_station_df.set_index('Call Sign', inplace=True)\n",
    "radio_station_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It is possible to move the index back to a column with the `reset_index` method:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "radio_station_df.reset_index(inplace=True)\n",
    "radio_station_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## DataFrame Exercise\n",
    "\n",
    "Create a DataFrame using the data in these lists:\n",
    "```\n",
    "titles = [\"Dangerously in Love\", \"B'Day\", \"I Am... Sasha Fierce\", \"4\", \"Beyoncé\", \"Lemonade\"]\n",
    "release_dates = [\"2003-06-23\", \"2006-09-01\", \"2008-11-14\", \"2011-06-24\", \"2013-12-13\", \"2016-04-23\"]\n",
    "total_sales = [11000000, 8000000, 8000000, 2600000, 5000000, 2500000]\n",
    "us_sales = [5000000, 3410000, 3200000, 1500000, 2300000, 1554000]\n",
    "```\n",
    "\n",
    "Each list should be a column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now change the DataFrame so that the index values are the album titles."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create a new column, \"Non-US Sales\", that contains the difference of the Total Sales and US Sales columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data Indexing and Selection\n",
    "\n",
    "Now that we can load data into pandas objects, we need to be able to access it. Pandas offers a variety of methods for accessing the data we need."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First, both `Series` and `DataFrame` objects support dictionary-style access with square brackets. Think of index label values as dictionary keys:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# We saw this above -- access a series like a dictionary to get a single value.\n",
    "avg_depths['Michigan']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# DataFrame dictionary-style access returns the Series with that column index label:\n",
    "lakes['Avg Depth (m)']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Boolean masking and fancy indexing work with DataFrames, just like Series objects:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# use a Boolean mask to select just the items we want:\n",
    "lakes[avg_depths > 60]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This works because the Boolean mask creates a new `Series` with the same index values!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "avg_depths > 60"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To select a subset of columns from a DataFrame, use fancy indexing with the column names in a list:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "lakes[['Avg Depth (m)', 'Max Depth (m)']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# There is a potential problem with non-sequential integer indexes:\n",
    "data_implicit = pd.Series([100, 200, 300, 400])\n",
    "data_explicit = pd.Series([100, 200, 300, 400], index=[4, 9, 8, 1])\n",
    "print('data_implicit')\n",
    "print(data_implicit)\n",
    "print()\n",
    "print('data_explicit')\n",
    "print(data_explicit)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To handle this potential confusion between label-based and position-based access and make data access easier in general, pandas provides two \"indexers\": `Series` and `DataFrame` attributes that expose differents ways to access the data.\n",
    "\n",
    "- `iloc`: always integer position-based\n",
    "- `loc`: always label-based"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_implicit.iloc[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_explicit.iloc[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "data_implicit.loc[4]  # Note that this should result in an error"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_explicit.loc[4] # # Note that this does not result in an error"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# We can use slices to select more than one value as well. Here, get all values after the first one:\n",
    "data_implicit.iloc[1:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Let's get all rows of the lakes dataframe except the last one:\n",
    "lakes.iloc[0:-1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "These indexers (`.iloc` and `.loc`) take two parameters: the row index values to include, and the *column* index values to include. By default, all columns of a DataFrame are included, but it is possible to retrieve only a subset:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# The first two rows and first two columns only\n",
    "lakes.iloc[:2, :2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "lakes.loc['Erie']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`loc` accepts the following types of inputs:\n",
    "\n",
    "- a single label (as above)\n",
    "- a list or array of labels, e.g. ['a', 'b', 'c']\n",
    "- a slice object with labels e.g. 'a':'c' (note that contrary to usual python slices, both the start and the stop are included!)\n",
    "- A boolean array\n",
    "- A callable function with one argument (the calling Series, DataFrame or Panel) and that returns valid output for indexing (one of the above)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "lakes.loc[['Michigan', 'Superior'], ['Max Depth (m)']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It is also possible to assign to the values at the locations you specify with the `iloc` and `loc` indexers! They aren't read-only."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame(np.random.randint(0, 10, (3, 3)), columns=['A', 'B', 'C'])\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Assign the value 100 to the cells 0,B and 1,B.\n",
    "# Remember with label-based access, which `loc` uses, the \"stop\" of the slice is *included*.\n",
    "df.loc[:1, 'B'] = 100\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## DataFrame Selection Exercise\n",
    "\n",
    "Construct a DataFrame from the following data (note you can use the `index` parameter to `pd.DataFrame` to set the index when creating a DataFrame):\n",
    "```\n",
    "data = {'animal': ['cat', 'cat', 'snake', 'dog', 'dog', 'cat', 'snake', 'cat', 'dog', 'dog'],\n",
    "        'age': [2.5, 3, 0.5, np.nan, 5, 2, 4.5, np.nan, 7, 3],\n",
    "        'visits': [1, 3, 2, 3, 2, 3, 1, 1, 2, 1],\n",
    "        'priority': ['yes', 'yes', 'no', 'yes', 'no', 'no', 'no', 'yes', 'no', 'no']}\n",
    "\n",
    "labels = ['a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j']\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Display the first three rows using the `.iloc` indexer:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Display the last three rows using the `.iloc` indexer:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Use the `.loc` indexer to access rows `a` through `d`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Display just the \"animal\" and \"age\" columns (you can use either the `.loc` indexer or dictionary-style access for this):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Select the data where the animal's age is greater than 3:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Examining Data\n",
    "\n",
    "While you can manipulate and operate on your data in any way you can dream up, pandas does provide basic descriptive statistics and sorting functionality for you. I **highly** recommend reading the [Pandas documentation](https://pandas.pydata.org/pandas-docs/stable/api.html#computations-descriptive-stats) to see what methods are available and save yourself some work!\n",
    "\n",
    "The `describe` method is very useful with numeric data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "lakes.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can get the highest value for a given Series with `max`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "lakes['Max Depth (m)'].max()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "But what if we wanted the top 2? `sort_values` is the answer:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "lakes['Max Depth (m)'].sort_values(ascending=False).head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is so common that there is actually a shortcut for it:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "max_depths.nlargest(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Which naturally works on DataFrames as well:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "lakes.nlargest(2, 'Avg Depth (m)')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Loading Data\n",
    "\n",
    "Pandas provides a bunch of functions for reading data from a variety of sources, including CSV, Excel files, SQL databases, HDF5, even your computer's clipboard! The always-comprehensive pandas documentation has more info here: [https://pandas.pydata.org/pandas-docs/stable/io.html](https://pandas.pydata.org/pandas-docs/stable/io.html).\n",
    "\n",
    "Let's read a local CSV dataset into a dataframe using the `read_csv` function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(\"data/Speed_Camera_Violations.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This particular `DataFrame` contains speed camera violation data provided by the city of Chicago. This dataset is available at [https://catalog.data.gov/dataset/speed-camera-violations-997eb](https://catalog.data.gov/dataset/speed-camera-violations-997eb).\n",
    "\n",
    "Let's start inspecting it by using the `head` method to look at the first five rows."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "When data is loaded from an external source, pandas will try to guess the datatype for each column. Let's see how it did:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.Series({col: df[col].dtype for col in df.columns})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data types\n",
    "\n",
    "Much of pandas functionality depends on the data types of the `Series` it's working with. For instance we can get summary measures and do numpy-like parallel operations on numeric types (`int64`, `float64`), or do date-based arithmetic with `date` series.\n",
    "\n",
    "Notice above that the data type of the `VIOLATION DATE` column is \"object\", which, just like in numpy, means it is a generic type that isn't very useful. Let's turn those date strings into actual date objects, which are much better to work with."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# given a Series, pd.to_datetime returns a new Series with the string dates parsed as actual dates.\n",
    "# We can then directly assign that Series back to the original column in our dataframe and pandas' magical Index\n",
    "# functionality will make it all line up properly.\n",
    "df[\"VIOLATION DATE\"] = pd.to_datetime(df[\"VIOLATION DATE\"], format=\"%m/%d/%Y\")\n",
    "\n",
    "df[\"VIOLATION DATE\"].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Filtering\n",
    "\n",
    "Now that we have a date column, we can do things like filter to only look at violations in 2015.\n",
    "\n",
    "To do this, we'll create a \"filter\", essentially a boolean expression that works just like a mask or \"fancy indexing\" expression in numpy, and apply that filter to our dataframe to get just the rows we want.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import datetime\n",
    "\n",
    "# datetime.date() will create a datetime object (outside of pandas). \n",
    "# pd.Timestamp ensures that pandas interprets this date as a specific point in time. \n",
    "#   (rather than something else, for instance ... hypothetically ... the last day of the indicated\n",
    "#   year. To avoid ambiguity, omission of pd.Timestamp will yield an error in future pandas versions,\n",
    "#   and will already create a warning in current pandas versions.)\n",
    "\n",
    "is_above_earliest_date = df[\"VIOLATION DATE\"] >= pd.Timestamp(datetime.date(2015,1,1)) \n",
    "is_below_latest_date = df[\"VIOLATION DATE\"] < pd.Timestamp(datetime.date(2016,1,1))\n",
    "\n",
    "# now create a Boolean Mask where both of the above Boolean Masks,\n",
    "# is_above_earliest_date and is_below_latest_date, are TRUE\n",
    "date_filter = is_above_earliest_date & is_below_latest_date\n",
    "\n",
    "# date_filter now contains a series of true/false values that we can use to extract just the values we are interested in\n",
    "# by putting it in square brackets after the dataframe variable.\n",
    "print(date_filter.head())\n",
    "print()\n",
    "print(date_filter.tail())\n",
    "\n",
    "df_2015 = df[date_filter]\n",
    "\n",
    "df_2015.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This kind of filtering works for any kind of data type, provided you take care to make sure pandas is using the right data types for your data!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You may have noticed that many of the rows in this dataframe are missing lat/lon data. Pandas uses the \"NaN\" placeholder for missing data and offers some methods for dealing with it.\n",
    "\n",
    "Both `Series` and `DataFrame` objects have `fillna` method that will replace missing data with a specified value.\n",
    "\n",
    "In thise case however we may want to just drop those records that have missing data entirely:\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_no_nans = df.dropna(axis=0, how=\"any\")\n",
    "df_no_nans.head()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "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.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}