Part 1 - Basics.ipynb

{
 "cells": [
  {
   "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
}