notebooks/session_11-visualization/Data-Visualization.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Visualization in Python"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Introduction\n",
    "\n",
    "In this module, you will learn to quickly and flexibly make a wide series of visualizations for exploratory data analysis and communicating to your audience. This module contains a practical introduction to data visualization in Python and covers important rules that any data visualizer should follow."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Learning Objectives\n",
    "\n",
    "* Learn critical rules about data visualization (using the correct graph types, correctly labeling all visual encodings, properly sourcing data).\n",
    "\n",
    "* Become familiar with a core base of data visualization tools in Python - specifically matplotlib and seaborn.\n",
    "\n",
    "* Start to develop the ability to conceptualize what visualizations are going to best reveal various types of patterns in your data.\n",
    "\n",
    "* Learn more about Illinois administrative data with exploratory analyses."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Setup - Load Python Packages"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "import matplotlib as mplib\n",
    "import matplotlib.pyplot as plt # visualization package\n",
    "import seaborn as sns\n",
    "\n",
    "# database connections\n",
    "from sqlalchemy import create_engine # to get data from database\n",
    "from sqlalchemy import __version__ as sql_version\n",
    "from sqlalchemy import inspect\n",
    "\n",
    "# so images get plotted in the notebook\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Setup - Loading Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# set up sqlalchemy engine\n",
    "engine = create_engine('postgresql://10.10.2.10/appliedda')\n",
    "\n",
    "# See all available schemas:\n",
    "pd.read_sql(\"SELECT schema_name FROM information_schema.schemata LIMIT 10;\", engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# We can look at the tables within each schema:\n",
    "pd.read_sql(\"SELECT * FROM pg_tables WHERE schemaname = 'ides'\",engine)\n",
    "# pd.read_sql(\"SELECT * FROM pg_tables WHERE schemaname = 'ildoc'\",engine)\n",
    "# pd.read_sql(\"SELECT * FROM pg_tables WHERE schemaname = 'class1'\",engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# We can look at column names within tables:\n",
    "pd.read_sql(\"SELECT * FROM information_schema.columns WHERE table_schema = 'class1' AND table_name = 'il_wage_person'\",engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "select_string = \"SELECT ssn, year, SUM(wage) total_wages FROM class1.il_wage_person\"\n",
    "select_string += \" WHERE year in (2005, 2010, 2015) AND quarter = 1\"\n",
    "select_string += \" GROUP BY ssn, year\"\n",
    "\n",
    "print(select_string)\n",
    "\n",
    "person_wages = pd.read_sql(select_string, engine)\n",
    "\n",
    "print(\"Number of rows returned: \" + str(len(person_wages)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Our First Chart in matplotlib\n",
    "\n",
    "Below, we make our first chart in matplotlib. We'll come back to the choice of this particular library in a second, but for now just appreciate that the visualization is creating sensible scales, tick marks, and gridlines on its own."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "## Wages often has a very strong right skew:\n",
    "max_wage = person_wages[\"total_wages\"].max()\n",
    "print(\"Maximum wage = \" + str(max_wage))\n",
    "\n",
    "## But most people earn under ##### in a quarter:\n",
    "(person_wages[\"total_wages\"] < ######).value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "## So let's just look at the wages under $#####\n",
    "person_wages_lim = person_wages[person_wages[\"total_wages\"] < #####]\n",
    "\n",
    "# Make a simple histogram:\n",
    "plt.hist(person_wages_lim.total_wages)\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "## We can change options within the hist function (e.g. number of bins, color, transparency:\n",
    "plt.hist(person_wages_lim.total_wages, bins=20, facecolor=\"purple\", alpha=0.5)\n",
    "\n",
    "## And we can affect the plot options too:\n",
    "plt.xlabel('Quarterly Income')\n",
    "plt.ylabel('Number of Offenders')\n",
    "plt.title('Most Offenders Earn Under $### per Quarter')\n",
    "\n",
    "## And add Data sourcing:\n",
    "### xy are measured in percent of axes length, from bottom left of graph:\n",
    "plt.annotate('Source: IL DOC & IDES', xy=(0.7,-0.2), xycoords=\"axes fraction\")\n",
    "\n",
    "## We use plt.show() to display the graph once we are done setting options:\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### A Note on Data Sourcing\n",
    "\n",
    "Data sourcing is a critical aspect of any data visualization. Although here we are simply referencing the agencies that created the data, it is ideal to provide as direct of a path as possible for the viewer to find the data the graph is based on. When this is not possible (e.g. the data is sequestered), directing the viewer to documentation or methodology for the data is a good alternative. Regardless, providing clear sourcing for the underlying data is an **absolutely requirement** of any respectable visualization, and further builds trusts and enables reproducibility."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Layering in Matplotlib\n",
    "This functionality - where we can make consecutive changes to the same plot - also allows us to layer on multiple plots. By default, the first graph you create will be at the bottom, with ensuing graphs on top.\n",
    "\n",
    "Below, we see the 2005 histogram, in blue, is beneath the 2015 histogram, in orange. You might also notice that the distribution of income for offenders has shifted over that ten year period."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "plt.hist(person_wages_lim[person_wages_lim[\"year\"] == 2005].total_wages, facecolor=\"blue\", alpha=0.5)\n",
    "plt.hist(person_wages_lim[person_wages_lim[\"year\"] == 2015].total_wages, facecolor=\"orange\", alpha=0.5)\n",
    "plt.annotate('Source: IL DOC & IDES', xy=(0.7,-0.2), xycoords=\"axes fraction\")\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Our First Chart in seaborn\n",
    "\n",
    "Below, we quickly use pandas to create an aggregation of our wages data - the average wages by year. Then we pass the data to the barplot function in the `seaborn` function, which recall we imported as `sns` for short."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "## Calculate average wages by year:\n",
    "avg_annual_wages = person_wages.groupby('year')['total_wages'].mean().reset_index()\n",
    "avg_annual_wages.columns = ['year','average_wages']\n",
    "\n",
    "print(type(avg_annual_wages))\n",
    "print(\"***********\")\n",
    "print(avg_annual_wages)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "## Barplot function\n",
    "# Note we can reference column names (in quotes) in the specified data:\n",
    "sns.barplot(x='year', y='average_wages', data=avg_annual_wages)\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You might notice that if you don't include plt.show(), Jupyter will still produce a chart. However this is not the case in other environments. So we will continue using plt.show() to more formally ask for Python to display the chart we have constructed, after adding all layers and setting all options."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "## Seaborn has a great series of charts for showing distributions across a categorical variable:\n",
    "sns.factorplot(x='year', y='total_wages', hue='year', data=person_wages_lim, kind='box')\n",
    "plt.show()\n",
    "\n",
    "## Other options for the 'kind' argument include 'bar' and 'violin'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Already you might notice some differences between matplotlib and seaborn - at the very least seaborn allows us to more easily reference column names within a pandas dataframe, whereas matplotlib clearly has a plethora of options."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Choosing a Data Visualization Package\n",
    "\n",
    "There are many excellent data visualiation modules available in Python, but for the tutorial we will stick to the tried and true combination of `matplotlib` and `seaborn`. You can read more about different options for data visualization in Python in the [More Resources](#More-Resources:) section at the bottom of this notebook. \n",
    "\n",
    "`matplotlib` is very expressive, meaning it has functionality that can easily account for fine-tuned graph creation and adjustment. However, this also means that `matplotlib` is somewhat more complex to code.\n",
    "\n",
    "`seaborn` is a higher-level visualization module, which means it is much less expressive and flexible than matplotlib, but far more concise and easier to code.\n",
    "\n",
    "It may seem like we need to choose between these two approaches, but this is not the case! Since `seaborn` is itself written in `matplotlib` (you will sometimes see `seaborn` be called a `matplotlib` 'wrapper'), we can use `seaborn` for making graphs quickly and then `matplotlib` for specific adjustments. When you see `plt` referenced in the code below, we are using `matplotlib`'s pyplot submodule.\n",
    "\n",
    "\n",
    "`seaborn` also improves on `matplotlib` in important ways, such as the ability to more easily visualize regression model results, creating small multiples, enabling better color palettes, and improve default aesthetics. From [`seaborn`'s documentation](https://seaborn.pydata.org/introduction.html):\n",
    "\n",
    "> If matplotlib 'tries to make easy things easy and hard things possible', seaborn tries to make a well-defined set of hard things easy too. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "## Seaborn offers a powerful tool called FacetGrid for making small multiples of matplotlib graphs:\n",
    "\n",
    "### Create an empty set of grids:\n",
    "facet_histograms = sns.FacetGrid(person_wages_lim, col='year', hue='year')\n",
    "\n",
    "## \"map' a histogram to each grid:\n",
    "facet_histograms = facet_histograms.map(plt.hist, 'total_wages')\n",
    "\n",
    "## Data Sourcing:\n",
    "plt.annotate('Source: IL DOC & IDES', xy=(0.6,-0.35), xycoords=\"axes fraction\")\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Seaborn and matplotlib \n",
    "\n",
    "Below, we use seaborn for setting an overall aesthetic style and then faceting (created small multiples). We then use matplotlib to set very specific adjustments - things like adding the title, adjusting the locations of the plots, and sizing th graph space. This is a pretty protoyptical use of the power of these two libraries together. \n",
    "\n",
    "More on [Seaborn's set_style function](https://seaborn.pydata.org/generated/seaborn.set_style.html).\n",
    "More on [matplotlib's figure (fig) API](https://matplotlib.org/api/figure_api.html)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Seaborn's set_style function allows us to set many aesthetic parameters.\n",
    "sns.set_style(\"whitegrid\")\n",
    "\n",
    "facet_histograms = sns.FacetGrid(person_wages_lim, col='year', hue='year')\n",
    "facet_histograms.map(plt.hist, 'total_wages')\n",
    "\n",
    "## We can still change options with matplotlib, using facet_histograms.fig\n",
    "facet_histograms.fig.subplots_adjust(top=0.85)\n",
    "facet_histograms.fig.suptitle(\"Offenders Income Gains Stagnated in 2010\", fontsize=14)\n",
    "facet_histograms.fig.set_size_inches(10,5)\n",
    "\n",
    "## Add a legend for hue (color):\n",
    "facet_histograms = facet_histograms.add_legend()\n",
    "\n",
    "## Data Sourcing:\n",
    "plt.annotate('Source: IL DOC & IDES', xy=(0.6,-0.35), xycoords=\"axes fraction\")\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Visual Encodings\n",
    "\n",
    "We often start with charts that use 2-dimensional position (like a scatterplot) or that use height (like histograms and bar charts). This is because these visual encodings - the visible mark that represents the data - are particularly perceptually strong. This means that when humans view these visual encodings, they are more accurate in estimating the underlying numbers than encodings like size (think circle size in a bubble chart) or angle (e.g. pie chart).\n",
    "\n",
    "For more information on visual encodings and data visualization theory, see:\n",
    "\n",
    "* [Designing Data Visualizations, Chapter 4](http://www.safaribooksonline.com/library/view/designing-data-visualizations/9781449314774/ch04.html) by Julie Steele and Noah Iliinsky\n",
    "\n",
    "* Now You See It - book by Stephen Few"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "select_string = \"SELECT year, avg(total_wages) avg_wages, count(*) num_offenders\"\n",
    "select_string += \" FROM (SELECT ssn, year, SUM(wage) total_wages FROM class1.il_wage_person\"\n",
    "select_string += \" WHERE quarter = 1\"\n",
    "select_string += \" GROUP BY ssn, year) tmp\"\n",
    "select_string += \" GROUP BY year\"\n",
    "\n",
    "print(select_string)\n",
    "\n",
    "yearly_avg_wages = pd.read_sql(select_string, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "yearly_avg_wages = yearly_avg_wages.sort_values('year')\n",
    "yearly_avg_wages.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "## We can pass a single value to a the tsplot function to get a simple line chart:\n",
    "sns.tsplot(data=yearly_avg_wages['avg_wages'], color=\"#179809\")\n",
    "\n",
    "## Data Sourcing:\n",
    "plt.annotate('Source: IL IDES', xy=(0.8,-0.20), xycoords=\"axes fraction\")\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Using Hex Codes for Color\n",
    "\n",
    "In the graph above, you can see I set the color of the graph with pund sign `#` followed by a series of six numbers. This is a hexcode - which is short for hexadecimal code. A hexadecimal code lets you specify one of over 16 million colors using combinations of red, green, and blue. It first has two digits for red, then two digits for green, and lastly two digits for blue: `#RRGGBB`\n",
    "\n",
    "Further, these codes allow for you to specify sixteen integers (thus hexadecimal) for each digits, in this order:\n",
    "\n",
    "(0,1,2,3,4,5,6,7,8,9,A,B,C,D,E,F)\n",
    "\n",
    "Over time, it gets easier to read these codes. For instance, above, I used the hex code \"#179809\". Understanding how hex codes work, I can see that there is a relatively low number for red (17) and fairly high number for green (98) and another low number for blue (09). Thus it shouldn't be too surprising that a green color resulted in the graph.\n",
    "\n",
    "Tools like [Adobe Color](https://color.adobe.com) and this [Hex Calculator](https://www.w3schoosl.com/colors/colors_hexadecimal.asp) can help you get used to this system.\n",
    "\n",
    "Most modern browsers also support eighit digit hex codes, in which the first two enable transparency, which is often called 'alpha' in data visualization: `#AARRGGBB`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "## We can add the time argument to set the x-axis correctly. And let's change the color, since we can:\n",
    "sns.tsplot(data=yearly_avg_wages['avg_wages'], time=yearly_avg_wages['year'], color=\"#B088CD\")\n",
    "\n",
    "# Color Note: B088CD\n",
    "## The highest values are red 'B0' and blue 'CD', so we can expect a mix of those\n",
    "## Further this is high in all three colors, so it'll be light, not dark\n",
    "\n",
    "## Data Sourcing:\n",
    "plt.annotate('Source: IL IDES', xy=(0.8,-0.20), xycoords=\"axes fraction\")\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Saving Charts As a Variable\n",
    "\n",
    "Although as you can see above, we can immediately print our plots on a page, it is generally better to save them as variable. We can then alter the charts over several lines before finally displaying them with the `show()` function, which comes from the `matplotlib` `pyplot` module we loaded earlier."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "## Save the line chart as 'graph'\n",
    "graph = sns.tsplot(data=yearly_avg_wages['avg_wages'], time=yearly_avg_wages['year'])\n",
    "\n",
    "## To add data labels, we loop over each row and use graph.text()\n",
    "for i, row, in yearly_avg_wages.iterrows():\n",
    "    graph.text(row[\"year\"] + 0.05, row[\"avg_wages\"] - 50, int(row[\"year\"]))\n",
    "    \n",
    "## Now change x-axis and y-axis labels:\n",
    "graph.set(xlabel=\"Year\", ylabel=\"Average Annual Wages\")\n",
    "graph.set(title=\"Average Wages Over Time\")\n",
    "\n",
    "plt.annotate('Source: IL IDES', xy=(0.8,-0.20), xycoords=\"axes fraction\")\n",
    "\n",
    "## Then display the plot:\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# We can also look at a scatterplot of the number of people and averages wages in each year:\n",
    "scatter = sns.lmplot(x='num_offenders', y='avg_wages', data=yearly_avg_wages, fit_reg=False)\n",
    "scatter.set(xlabel=\"Number of Offenders\", ylabel=\"Average Annual Wages\", title=\"Number and Wages of IL Offenders\")\n",
    " \n",
    "## Sourcing:\n",
    "plt.annotate('Source: IL IDES', xy=(0.8,-0.20), xycoords=\"axes fraction\")\n",
    "\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Directed Scatterplot\n",
    "\n",
    "A directed scatterplot still uses one point for each year, but then uses the x-axis and the y-axis for variabes. In order to maintain the ordinal relationship, a line is drawn between the years. To do this in seaborn, we actually use sns.FacetGrid, which allows us to overlay different plots together. Specifically, it lets us overlay a scatterplot (`plt.scatter` and a line chart `plt.plot`)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### An Important Note on Graph Titles:\n",
    "\n",
    "The title of a visualization occupies the most valuable real estate on the page. If nothing else, you can be reasonably sure a viewer will at least read the title and glance at your visualization. This is why you want to put thought into making a clear and effective title that acts as a **narrative** for your chart. Many novice visualizers default to an **explanatory** title, something like: \"Income and Number of Offenders over Time (2005-2015)\". This title is correct - it just isn't very useful. This is particularly true since any good graph will have explained what the visualization is through the axes and legends. Instead, use the title to reinforce and explain the core point of the visualization. It should answer the question \"Why is this graph important?\" and focus the viewer onto the most critical take-away."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "cncted_scatter = sns.FacetGrid(data=yearly_avg_wages, size=7)\n",
    "cncted_scatter.map(plt.scatter, 'num_offenders', 'avg_wages', color=\"#A72313\")\n",
    "cncted_scatter.map(plt.plot, 'num_offenders', 'avg_wages', color=\"#A72313\")\n",
    "cncted_scatter.set(title=\"Rising Wages of Former Offenders\", xlabel=\"Number of Offenders\", ylabel=\"Average Wages\")\n",
    "\n",
    "## Adding data labels:\n",
    "for i, row, in yearly_avg_wages.iterrows():\n",
    "    plt.text(row[\"num_offenders\"], row[\"avg_wages\"], int(row[\"year\"]))\n",
    "    \n",
    "## Sourcing:\n",
    "plt.annotate('Source: IL IDES', xy=(0.8,-0.10), xycoords=\"axes fraction\")\n",
    "\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exporting Completed Graphs\n",
    "\n",
    "When you are satisfied with your visualization, you may want to save a a copy outside of your notebook. You can do this with `matplotlib`'s savefig function. You simply need to run:\n",
    "\n",
    "plt.savefig(\"fileName.fileExtension\")\n",
    "\n",
    "The file extension is actually surprisingly important. Image formats like png and jpeg are actually **not ideal**. These file formats store your graph as a giant grid of pixels, which is space-efficient, but can't be edited later. Saving your visualizations instead as a PDF is strongly advised. PDFs are a type of vector image, which means all the component of the graph will be maintained.\n",
    "\n",
    "With PDFs, you can later open the image in a program like Adobe Illustrator and make changes like the size or typeface of your text, move your legends, or adjust the colors of your visual encodings. All of this would be impossible with a png or jpeg."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "cncted_scatter = sns.FacetGrid(data=yearly_avg_wages, size=7)\n",
    "cncted_scatter.map(plt.scatter, 'num_offenders', 'avg_wages', color=\"#A72313\")\n",
    "cncted_scatter.map(plt.plot, 'num_offenders', 'avg_wages', color=\"#A72313\")\n",
    "cncted_scatter.set(title=\"Rising Wages of Former Offenders\", xlabel=\"Number of Offenders\", ylabel=\"Average Wages\")\n",
    "\n",
    "## Adding data labels:\n",
    "for i, row, in yearly_avg_wages.iterrows():\n",
    "    plt.text(row[\"num_offenders\"], row[\"avg_wages\"], int(row[\"year\"]))\n",
    "    \n",
    "## Sourcing:\n",
    "plt.annotate('Source: IL IDES', xy=(0.8,-0.10), xycoords=\"axes fraction\")\n",
    "\n",
    "plt.savefig('dscatter.png')\n",
    "plt.savefig('dscatter.pdf')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "pd.read_sql(\"SELECT * FROM pg_tables WHERE schemaname = 'ildoc'\",engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "## See all the variables in the data:\n",
    "pd.set_option('display.height', 200)\n",
    "pd.set_option('display.max_rows', 200)\n",
    "\n",
    "pd.read_sql(\"SELECT * FROM information_schema.columns WHERE table_schema = 'ildoc' AND table_name = 'ildoc_admit'\",engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercises & Practice"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Excercise 1: Heatmap\n",
    "\n",
    "Below, I query the database for the average sentence of men, broken out by their race and education level. I then format this data into a wider form using pandas - where each row corresponds to an education level and each column corresponds to a race. This grid is format that `seaborn`'s heatmap function is expecting. Understanding the relationship between your data and the resulting graph is an important aspect of data visualization - and you can really only master this with practice.\n",
    "\n",
    "Query one of the tables again and create dataframe in the correct format, then pass that along to seaborn's heatmap function. Use the code you learned above to add a title, better axis labels, and data sourcing.\n",
    "\n",
    "Note that the color map used here `viridis` is a scientifically derived color palette meant to be perceptually linear. The color maps `inferno`, `plasma` and `magama` also all meet this criteria.\n",
    "\n",
    "\n",
    "#### More information:\n",
    "* [seaborn heatmap documentation](http://seaborn.pydata.org/generated/seaborn.heatmap.html)\n",
    "\n",
    "* [matplotlib color map documentation](http://matplotlib.org/users/colormap.html)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "## Querying Average Jailtime by Race and Gender \n",
    "select_string = \"SELECT race, educlvl, avg(jailtime) as avg_jailtime\"\n",
    "select_string += \" FROM ildoc.ildoc_admit\"\n",
    "select_string += \" WHERE sex = 'M'\"\n",
    "select_string += \" GROUP BY race, educlvl\"\n",
    "\n",
    "print(select_string)\n",
    "\n",
    "jail = pd.read_sql(select_string, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "## Format the data for a heatmap:\n",
    "jail = jail.pivot(\"educlvl\", \"race\", \"avg_jailtime\")\n",
    "jail = jail.round(0)\n",
    "jail = jail.apply(pd.to_numeric)\n",
    "print(jail)\n",
    "\n",
    "## Create a heatmap, with annotations:\n",
    "sns.heatmap(jail, annot=True, fmt='g', cmap=\"viridis\")\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "## Enter your code for excercise 1 here:\n",
    "\n",
    "    \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "### Exercise 2\n",
    "\n",
    "Let's see if we can use seaborn's FacetGrid to create small multiple scatterplots. First you need to query a database and get at least one categorical variable and at least two continuous variables (floats).\n",
    "\n",
    "Then try passing this data to the FacetGrid function from `seaborn` and the scatter function from `matplotlib`. \n",
    "\n",
    "[FacetGrid Documentation](http://seanborn.pydata.org/examples/many_facets.html)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "## Pseudo-code to get you started:\n",
    "grid = sns.FacetGrid(dataframe, col = \"categorical_var\", hue=\"categorical_var\", col_wrap=2)\n",
    "grid.map(plt.scatter(\"x_var\", \"y_var\"))\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "## Enter your code for excercise 2 here:\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exercise 3:\n",
    "\n",
    "Test your mettle. Check out the seaborn [data visualization gallery](http://seaborn.pydata.org/examples) and see if you can implement an interesting visualization."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## More Resources\n",
    "\n",
    "* [A Thorough Comparison of Python's DataViz Modules](https://dsaber.com/2016/10/02/a-dramatic-tour-through-pythons-data-visualization-landscape-including-ggplot-and-altair)\n",
    "\n",
    "* [Seaborn Documentation](http://seaborn.pydata.org)\n",
    "\n",
    "* [Matplotlib Documentation](https://matplotlib.org)\n",
    "\n",
    "* [Advanced Functionality in Seaborn](blog.insightdatalabs.com/advanced-functionality-in-seaborn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Other Python Visualization Libraries\n",
    "\n",
    "* [Bokeh](http://bokeh.pydata.org)\n",
    "\n",
    "* [Altair](https://altair-viz.github.io)\n",
    "\n",
    "* [ggplot](http://ggplot.yhathq.com.com)\n",
    "\n",
    "* [Plotly](https://plot.ly)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "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": 2
}