BerkeleySalary.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "# Illustration of datascience Tables on Open Data from Berkeley\n",
    "\n",
    "**David E. Culler**\n",
    "\n",
    "Datascience Table provides a simple, yet powerful data structure for a range of analyses.  The basic concept is an ordered set of named columns.  \n",
    "\n",
    "* It builds on the intuition many develop with *excel* - data is represented as rectangular regions.  But, rather than labeling cells, the column labels really mean something. \n",
    "* Tables embed smoothly in jupyter notebooks, so the user experience is that of a computational document, rather than a spreadsheet. This provides a much clearer sequence of steps from raw data to finished product, at least if they are well constructed.  There is no need to break out into *visual basic* or the like when you need more than the basics.\n",
    "* Tables draws heavily from relational database tables, but there is no separate language (e.g., SQL) required to do relational operations in them.\n",
    "* Tables provide also the concepts associated with *pivot tables* in Excel, which are closely related to relational operations, but often more natural.\n",
    "* Tables can be viewed as simple variants of the *dataframes* in **R** or *Pandas*.  The key is simplicity.  They are simple rectangular structures.  The cells of a table can hold arbitrary values, although columns are homogeneous, so the additional power (and complexity) of higher dimensions are rarely needed.\n",
    "* Tables builds directly on the scipy ecosystem.  Almost any sequence can go in, `numpy.array`s come out.  Thus, it is natural to manipulate data directly from Tables. Basic visualization is provide directly in terms of Tables, so you can go a long ways before reading `matplotlib` documentation.  However, if you need more, a lot of it can be gained through `keyword args` - and if that is not enough, drop into scipy.\n",
    "\n",
    "This notebook illustrates some of the use of Tables using municipal salary data made possible though the City of Berkeley's open data portal."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# This useful nonsense just goes at the top\n",
    "from datascience import *\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plots\n",
    "plots.style.use('fivethirtyeight')\n",
    "%matplotlib inline\n",
    "# datascience version number of last run of this notebook\n",
    "version.__version__"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "## Reading raw data into a Table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "Let's pull a big wad of City Employee salary data from the Berkeley Open Data portal.  \n",
    "\n",
    "Since this was a trial till June 30, 2015 and you have to mouse around to get csv files, we happen to have pulled a local copy."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "raw_berkeley_sal_2011 = Table.read_table(\"http://data8.org/tables-notebooks/data/City_of_Berkeley_Employee_Salaries_-_2011.csv\")"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "Let's take a peek at what we have got."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# Tables are rendered to work in a notebook, even if they are large.  Only part shows.\n",
    "# You can adjust how much of it you see, but here we'd have ~1500 rows!\n",
    "raw_berkeley_sal_2011"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "## Accessing data in a Table\n",
    "\n",
    "A column of Table data is accessed by indexing it by name.  This returns the column as a numpy array."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "raw_berkeley_sal_2011['Base Pay']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "Some prefer the selectors - column and row"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "raw_berkeley_sal_2011.column('Overtime Pay')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "Rows in the table can be indexed and sliced.  A row is a little like a record or dict.  It is an tupled, ordered according to the table it comes from and keeping the column names.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "raw_berkeley_sal_2011.rows[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "raw_berkeley_sal_2011.row(0).item('Base Pay')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "raw_berkeley_sal_2011.row(0)[2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "raw_berkeley_sal_2011.row(0).asdict()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "raw_berkeley_sal_2011.rows[0:10]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "## Converting data in a Table to make it workable\n",
    "\n",
    "When we read in data from a csv file we got a bunch of columns filled with a bunch of strings.  As is often the case, we want the data in a column to represent values that we can analyze, whereas we want the printed format of a column to reflect its meaning.  Currency is the most common such situation.  Let's clean up our salary table."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "We might start by getting ahold of the names of column that we want to clean up"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "paylabels = raw_berkeley_sal_2011.labels[2:]\n",
    "paylabels"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Clean derivatives of raw tables\n",
    "\n",
    "It is good hygene to keep the raw data raw and produce distinct, clean derivatives.  Let's start by making a copy of the raw table.  A new name and a new table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "berkeley_sal_2011 = raw_berkeley_sal_2011.copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Tables allow columns to have customized formatters\n",
    "\n",
    "In Excel you do this by formatting the cells.  We want to have the data as numbers, keep track of the type, and have it look nice."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "berkeley_sal_2011.set_format(paylabels, CurrencyFormatter)\n",
    "berkeley_sal_2011"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "Now we get values we can compute on - and they still display as currency."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "berkeley_sal_2011['Base Pay']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "max(berkeley_sal_2011['Total Pay Benefits'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "## Descriptive Statistics Summary\n",
    "\n",
    "Now we can try to get a summary of the data with some descriptive statistics.  \n",
    "\n",
    "The `stats` method on Tables computes a list of statistics over each column and creates a new Table containing these statistics.  The default is tailored to the Berkeley Data8 course. Here we provide what you expect from the `summary` operation in **R**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "def firstQtile(x) : return np.percentile(x,25)\n",
    "def thirdQtile(x) : return np.percentile(x,25)\n",
    "summary_ops = (min, firstQtile, np.median, np.mean, thirdQtile, max)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "berkeley_sal_2011.select(paylabels).stats(ops=summary_ops)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "OK, so it looks like the average salary is about 86k, and it ranges up to 300k with some hefty overtime pay.  Let's see if we can understand what is going on."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "## Visualizing data\n",
    "\n",
    "* `Table.select` creates a new table consisting of the specified list of columns.\n",
    "* `Table.hist` plots a histogram of each of the columns in a table.  It can either overlay the histograms or show them separately.  Here we have specified the number of bins"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "berkeley_sal_2011.select([\"Base Pay\", \n",
    "                          \"Overtime Pay\", \n",
    "                          \"Total Pay Benefits\"]).hist(overlay=False,normed=False,\n",
    "                                                      bins=40)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "Interesting.  Base pay is bimodal.  Most employees get no overtime, but there is a looong tale.  Let's look at the individual level.  Who's at the top?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "berkeley_sal_2011.sort('Total Pay Benefits', descending=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "So where does the $alary go? First, how many employees?\n",
    "\n",
    "`Table.num_rows` returns just what you'd think. The number of rows.  Which in this case is the number of employees on the city payroll."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "berkeley_sal_2011.num_rows"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "## Grouping and Sorting Table data\n",
    "\n",
    "* `Table.drop` creates a new Table without some columns.  It is like select, but you don't have to name everythng you want.\n",
    "* `Table.group` aggregates data by grouping all the rows that contain a common value in one (or more) columns.  Here we group in \"Job Title\" summing the entries in all other columns for each group.  We placed a column full of `1` to get a count, while summing salaries and such.\n",
    "* `Table.sort` sorts the rows in a Table by a column - just like sort in Excel."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# lose the individual names\n",
    "job_titles = berkeley_sal_2011.drop(\"Employee Name\") \n",
    "# Build a handy column full of 1s\n",
    "job_titles[\"Title\"] = 1\n",
    "# Group by title summing the number of rows per\n",
    "by_title = job_titles.group(\"Job Title\", sum)  \n",
    "# Sort by the number of employees per title\n",
    "ordered_by_title = by_title.sort('Title sum', descending = True) \n",
    "# let's see what we get\n",
    "ordered_by_title "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "ordered_by_title.num_rows"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "Wow, 305 Job Titles for 1437 employees!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "\"{0:.3} employees per Job Code\".format(berkeley_sal_2011.num_rows/ordered_by_title.num_rows)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "## Plotting data\n",
    "\n",
    "* `Table.plot` plots each of the columns in a table, either on separate charts or overlayed on a single chart.  Optionally one of the columns can be specified as the horizontal axis and all others plotted against this."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "ordered_by_title.select(['Title sum','Total Pay Benefits sum']).sort('Title sum').plot(overlay=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "How about that, a few job categories have most of the employes and most of the spend, but it is far from uniform.  Let's look a little deeper.  Which categories consume most of the budget?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "by_title.sort('Total Pay Benefits sum', descending = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "by_title.sort('Total Pay Benefits sum', descending = True).row(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "by_title.select(('Job Title', 'Total Pay Benefits sum')).sort('Total Pay Benefits sum', descending=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "As is often the case in the real world, the categorization used for operations is not directly useful for analysis.  We often need to build categories in order to get a handle on what's going on.\n",
    "\n",
    "What do all those job titles look like"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "ordered_by_title['Job Title']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "categories = {\n",
    "    'Police': [\"POLICE\"], \n",
    "    'Fire': [\"FIRE\"], \n",
    "    'Animal Control':[\"ANIMAL\"], \n",
    "    'Health': [\"HEALTH\", \"PSYCH\", \"HLTH\"],\n",
    "    'Library': ['LIBRARY','LIBRARIAN'],\n",
    "    'Offical' : ['MAYOR','COUNCIL', 'COMMISSIONER', 'CITY MANAGER'],\n",
    "    'Trades' :[\"ELECTRICIAN\",\"MECHANIC\", \"ENGINEER\"],\n",
    "    'Parking' : [\"PARKING\"],\n",
    "    'Recreation' : [\"RECREATION\", \"AQUATICS\"],\n",
    "    'Gardener' : [\"GARDEN\"],\n",
    "    \"Labor\" : [\"LABOR\", \"JANITOR\"],\n",
    "    'Community': [\"COMMUNITY\"],\n",
    "    'Admin' : [\"ADMIN\"],\n",
    "    'Traffic' : [\"TRAFFIC\"],\n",
    "    'Accounting' : [\"ACCOUNT\"],\n",
    "    'Dispatch' : [\"DISPATCH\"],\n",
    "    'Waste' : [\"WASTE\", \"SEWER\"],\n",
    "    'Analyst' : [\"ANALYS\"],\n",
    "    'Office' : [\"OFFICE \"],\n",
    "    'Legal' : ['LEGISLAT', 'ATTORN', 'ATTY'],\n",
    "    'IT' : [\"PROG\", \"INFORMATION SYSTEMS\"],\n",
    "    'School' : [\"SCHOOL\"],\n",
    "    'Architect' : [\"ARCHITECT\"],\n",
    "    'Planner' : [\"PLANNER\", \"PERMIT\"]\n",
    "    }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "categories"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "## Applying a function to create a new column\n",
    "\n",
    "* `table.apply`: applies a function to every element in a column.  \n",
    "\n",
    "One of the best examples of high-order functions and tables is in categorizing data. As is often the case, we create a new column with the results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "def categorize (title) : \n",
    "    for category, keywords in categories.items():\n",
    "        for word in keywords :\n",
    "            if title.find(word) >= 0 : return category\n",
    "    return 'Other'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "berkeley_sal_2011['Category'] = berkeley_sal_2011.apply(categorize, 'Job Title')\n",
    "berkeley_sal_2011"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# lose the individual names\n",
    "job_categories = berkeley_sal_2011.drop(\"Employee Name\") \n",
    "job_categories[\"Cat\"] = 1\n",
    "by_categories = job_categories.group(\"Category\", sum)\n",
    "by_categories.sort(\"Total Pay Benefits sum\", descending=True).show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "As is often the case working with real data, we often need to iterate a bit to get what we want out of it.  With all those titles, a lot of stuff is likely to end up as other.  \n",
    "\n",
    "Here we have a little iterative process to get enough of the job titles categorized"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "job_categories.where('Category', 'Other')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "job_categories.where('Category', 'Other').group('Job Title',sum).sort('Cat sum', descending=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "job_categories.where('Category', 'Other').group('Job Title',sum).sort('Total Pay Benefits sum', descending=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "source": [
    "So no job title left has more than 10 employees in it, but some have quite a bit of cost.  We could go back and add more entries to our category table and iterate a bit.  The important thing is that we create new tables, we don't clobber old ones.  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "Well this shows the challenge in managing budget pretty nicely.  Most of the money is spent in a few job categories.  But then there are still over 200 employees in a zillion other categories that are stile the #2 spend."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "by_categories.sort('Total Pay Benefits sum', descending=True).barh('Category', select=['Total Pay Benefits sum', 'Cat sum'], overlay=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "So let's try to understand the police category a bit more. \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "police = job_categories.where('Category', 'Police')\n",
    "police"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "How do the pay labels spread across the force?\n",
    "\n",
    "We can look at histograms by pay label.  First all toegether and then broken apart."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "police.select(paylabels).hist(bins=30,normed=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "police.select(paylabels).hist(bins=30,normed=False, overlay=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "Base pay seems to chunk into categories, perhaps by job title.  \n",
    "\n",
    "Most members of the force do little overtime, but a few do a lot!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "How many are in each Job Title?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "police.group('Job Title')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# We can actually get all the data by title\n",
    "police.select(['Job Title','Base Pay', 'Overtime Pay']).group('Job Title', collect=lambda x:x)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "We can't just pivot by Job Title because we don't have a uniform number of rows, but what we can do is for pivot and bin (or histogram) so we can see the distribution of a column by job title.\n",
    "\n",
    "Sure enough.  Officers cluster around 100-120k, sergeants at 130-140k, but there's a little overlap."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "police.pivot_bin('Job Title', 'Base Pay', bins=np.arange(0,200000,10000)).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "police.pivot_bin('Job Title', 'Base Pay', bins=np.arange(0,200000,10000)).bar('bin')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "police.pivot_bin('Job Title', 'Overtime Pay', bins=np.arange(0,200000,10000)).bar('bin')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "police.pivot_bin('Job Title', 'Total Pay Benefits', bins=np.arange(0,420000,10000)).bar('bin')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "fire = job_categories.where('Category','Fire')\n",
    "fire.select(paylabels).hist(bins=30)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "fire.group('Job Title')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "fire.pivot_bin('Job Title', 'Total Pay Benefits', bins=np.arange(0,420000,10000)).bar('bin')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "Let's compare the 2011 data with more recent 2013 data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "raw_berkeley_sal_2013 = Table.read_table(\"http://data8.org/tables-notebooks/data/City_of_Berkeley_Employee_Salaries_-_2013.csv\")\n",
    "raw_berkeley_sal_2013"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "Well, the data base changed.  It picked up a few columns over the years. And we need to convert the salary strings to numbers so we can do analysis on them.  All in one go..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "berkeley_sal_2013 = raw_berkeley_sal_2013.drop(['Year','Notes','Agency'])\n",
    "berkeley_sal_2013"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "berkeley_sal_2013.set_format(berkeley_sal_2013.labels[2:], CurrencyFormatter)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "berkeley_sal_2013[\"Total Pay & Benefits\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "berkeley_sal_2013.sort('Total Pay & Benefits',descending=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "Isn't that interesting.  They seem to have gotten their overtime under control.  Was that management, end of the occupy movement, something else?  Let's do a bit of comparison.\n",
    "\n",
    "First we need to do some clean up and get labels we can deal with."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "b2011 = berkeley_sal_2011.select([\"Employee Name\", \"Job Title\", \"Total Pay Benefits\"])\n",
    "b2011.relabel('Total Pay Benefits', \"Total 2011\")\n",
    "b2011.sort('Total 2011', descending=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "b2013 = berkeley_sal_2013.select([\"Employee Name\", \"Job Title\", \"Total Pay & Benefits\"])\n",
    "b2013.relabel('Job Title','Title 2013')\n",
    "b2013.relabel(\"Total Pay & Benefits\", \"Total 2013\")\n",
    "b2013.sort('Total 2013', descending=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "Snap! They decided that case was a good idea for proper nouns.  Let's go back to the old way."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "b2013['Employee Name'] = b2013.apply(str.upper, 'Employee Name')\n",
    "b2013"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "Now we can put the two tables together to see what happened with employees who were around in both years.  Here we get to use another powerful operations on tables.\n",
    "\n",
    "* `Table.join`: joins two tables together using a column of each that contains common values.\n",
    "\n",
    "Here we have the employee names in each table.  The join will give us the title and salary in both years for those employees in both tables, i.e., working for the city in both years"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "b11_13 = b2011.join('Employee Name', b2013)\n",
    "b11_13"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "Let's add a column with increase in total pay."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "b11_13[\"Increase\"] =b11_13['Total 2013'] - b11_13['Total 2011']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "b11_13.sort('Increase', \"decr\").select('Increase').plot()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "On the tails we have people who joined part way through 2011 or left part way through 2013."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "b11_13.stats(summary_ops)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "Well that's interesting.  Total compensation seems to have dropped.  Did the budget actually go down?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "sum2011 = np.sum(berkeley_sal_2011['Total Pay Benefits'])\n",
    "\"${:,}\".format(int(sum2011))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "sum2013 = np.sum(berkeley_sal_2013['Total Pay & Benefits'])\n",
    "\"${:,}\".format(int(sum2013))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "\"${:,}\".format(int(sum2013-sum2011))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "\"{:.1%}\".format((sum2013-sum2011)/sum2011)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "Look at that."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "np.sum(berkeley_sal_2011['Overtime Pay'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "np.sum(berkeley_sal_2013['Overtime Pay'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "Let's see who got promoted or demoted"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "b11_13.where(b11_13['Job Title'] != b11_13['Title 2013']).sort('Total 2013', descending=True).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "b11_13.where(b11_13['Job Title'] == b11_13['Title 2013']).sort('Increase', descending=True).show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "Perhaps we might want to look at the relationship of these two variables.  That leads to another useful operator\n",
    "\n",
    "* `Table.scatter`: does a scatter plot of columns against one columns\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "b11_13.scatter('Total 2011', 'Total 2013')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "## Summary\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "source": [
    "This notebook has provided a introduction to many of the concepts and features in datascience tables in the context of a fairly complete example on open public data.\n",
    "\n",
    "* Creating tables: `Table.read_table` - reads a file or url into a Table.  It is primarily used for csv files.  Tables can also be created from local data structures by constructing a tables with `Table()` and filling it using `with_columns` or `with_rows`.\n",
    "* Accessing columns, rows, and elements of table.\n",
    "* Cleaning up raw tables and setting formatters for table displays.\n",
    "* Getting descriptive statistics with `stats` to sumarize the columns in a table.\n",
    "* Working with portions of a table using `select` to select columns `where` to filter rows, `drop` to select all but the specified columns.\n",
    "* Visualizing data with `hist`, `plot`, `barh`, `bar`, and `scatter`.\n",
    "* Sorting tables with `sort` using columns as keys.\n",
    "* Grouping entries in tables using `group`, where groups are defined by rows with common values in a specified collection of columns; the values in the remaining columns are then aggregated using a collection function.  The identity collector all all the values in a group to be collected into a list.\n",
    "* Applying functions to all the elements of a column of a table, using `apply`\n",
    "* Distributing columns of a table using `pivot_bin` where each unique set of values in a specified collection of columns serves as a \"key\" which is a column name in the result.  Values in the remaining columns are binned to produce the rows in the result.  This is used when the number of entries for each key varies.  Where there is a single value for each key, `pivot` can be used.\n",
    "\n"
   ]
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python [default]",
   "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.5.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}