intro-to-pandas-2-Working-With-DataFrames.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Working with DataFrames"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that we can get data into a DataFrame, we can finally start working with them. pandas has an abundance of functionality, far too much for me to cover in this introduction. I'd encourage anyone interested in diving deeper into the library to check out its [excellent documentation](http://pandas.pydata.org/pandas-docs/stable/). Or just use Google - there are a lot of Stack Overflow questions and blog posts covering specifics of the library.\n",
    "\n",
    "We'll be using the [MovieLens](http://www.grouplens.org/node/73) dataset in many examples going forward. The dataset contains 100,000 ratings made by 943 users on 1,682 movies."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from IPython.display import display"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "!head -n 5 data/ml-100k/u.user"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "!head -n 3 data/ml-100k/u.data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "!head -n 1 data/ml-100k/u.item"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# pass in column names for each CSV (info in README file)\n",
    "u_cols = ['user_id', 'age', 'sex', 'occupation', 'zip_code']\n",
    "users = pd.read_csv('data/ml-100k/u.user', sep='|', names=u_cols)\n",
    "\n",
    "r_cols = ['user_id', 'movie_id', 'rating', 'unix_timestamp']\n",
    "ratings = pd.read_csv('data/ml-100k/u.data', sep='\\t', names=r_cols)\n",
    "\n",
    "# the movies file contains columns indicating the movie's genres\n",
    "# let's only load the first five columns of the file with usecols\n",
    "m_cols = ['movie_id', 'title', 'release_date', 'video_release_date', 'imdb_url']\n",
    "movies = pd.read_csv('data/ml-100k/u.item', sep='|', names=m_cols, usecols=range(5), encoding='ISO-8859-1')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "users.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "ratings.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "movies.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Inspection\n",
    "\n",
    "pandas has a variety of functions for getting basic information about your DataFrame, the most basic of which is using the `info` method."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "movies.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The output tells a few things about our DataFrame.\n",
    "\n",
    "1. It's obviously an instance of a DataFrame.\n",
    "2. Each row was assigned an index of 0 to N-1, where N is the number of rows in the DataFrame. pandas will do this by default if an index is not specified. Don't worry, this can be changed later.\n",
    "3. There are 1,682 rows (every row must have an index).\n",
    "4. Our dataset has five total columns, one of which isn't populated at all (video_release_date) and two that are missing some values (release_date and imdb_url).\n",
    "5. The last line displays the datatypes of each column, but not necessarily in the corresponding order to the listed columns. You should use the `dtypes` method to get the datatype for each column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "movies.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "DataFrame's also have a `describe` method, which is great for seeing basic statistics about the dataset's numeric columns. Be careful though, since this will return information on **all** columns of a numeric datatype."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "users.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice *user_id* was included since it's numeric. Since this is an ID value, the stats for it don't really matter.\n",
    "\n",
    "We can quickly see the average age of our users is just above 34 years old, with the youngest being 7 and the oldest being 73. The median age is 31, with the youngest quartile of users being 25 or younger, and the oldest quartile being at least 43."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You've probably noticed that I've used the `head` method regularly throughout this post - by default, `head` displays the first five records of the dataset, while `tail` displays the last five."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "movies.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "movies.tail(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Alternatively, Python's regular [slicing](http://docs.python.org/release/2.3.5/whatsnew/section-slices.html) syntax works as well."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "movies[20:22]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Selecting\n",
    "\n",
    "You can think of a DataFrame as a group of Series that share an index (in this case the column headers). This makes it easy to select specific columns.\n",
    "\n",
    "Selecting a single column from the DataFrame will return a Series object."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "users['occupation'].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To select multiple columns, simply pass a list of column names to the DataFrame, the output of which will be a DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "users[['occupation']].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "display(users[['age', 'zip_code']].head())\n",
    "\n",
    "# can also store in a variable to use later\n",
    "columns_you_want = ['occupation', 'sex'] \n",
    "display(users[columns_you_want].head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Row selection can be done multiple ways, but doing so by an individual index or boolean indexing are typically easiest."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "print('users older than 25')\n",
    "display(users[users.age > 25].head(3))\n",
    "\n",
    "print('users aged 40 AND male')\n",
    "display(users[(users.age == 40) & (users.sex == 'M')].head(3))\n",
    "\n",
    "print('users younger than 30 OR female')\n",
    "display(users[(users.sex == 'F') | (users.age < 30)].head(3))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Since our index is kind of meaningless right now, let's set it to the `user_id` using the `set_index` method. By default, `set_index` returns a new DataFrame, so you'll have to specify if you'd like the changes to occur in place.\n",
    "\n",
    "This has confused me in the past, so look carefully at the code and output below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "display(users.set_index('user_id').head())\n",
    "\n",
    "print('after set_index')\n",
    "display(users.head())\n",
    "print(\"^^^ I didn't actually change the DataFrame. ^^^\\n\")\n",
    "\n",
    "with_new_index = users.set_index('user_id')\n",
    "display(with_new_index.head())\n",
    "print(\"^^^ set_index actually returns a new DataFrame. ^^^\\n\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you want to modify your existing DataFrame, use the `inplace` parameter."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "users.set_index('user_id', inplace=True)\n",
    "users.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice that we've lost the default pandas 0-based index and moved the user_id into its place.  We can select rows based on the index using the `ix` method."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "print('select one row')\n",
    "display(users.ix[99])\n",
    "\n",
    "print('select multiple rows')\n",
    "display(users.ix[[1, 50, 300]])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we realize later that we liked the old pandas default index, we can just `reset_index`.  The same rules for `inplace` apply."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "users.reset_index(inplace=True)\n",
    "users.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I've found that I can usually get by with boolean indexing and the `ix` method, but pandas has a whole host of [other ways to do selection](http://pandas.pydata.org/pandas-docs/stable/indexing.html)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Joining\n",
    "\n",
    "Throughout an analysis, we'll often need to merge/join datasets as data is typically stored in a [relational](http://en.wikipedia.org/wiki/Relational_database) manner.\n",
    "\n",
    "Our MovieLens data is a good example of this - a rating requires both a user and a movie, and the datasets are linked together by a key - in this case, the user_id and movie_id. It's possible for a user to be associated with zero or many ratings and movies. Likewise, a movie can be rated zero or many times, by a number of different users.\n",
    "\n",
    "Like SQL's **JOIN** clause, `pandas.merge` allows two DataFrames to be joined on one or more keys. The function provides a series of parameters `(on, left_on, right_on, left_index, right_index)` allowing you to specify the columns or indexes on which to join.\n",
    "\n",
    "By default, `pandas.merge` operates as an *inner join*, which can be changed using the `how` parameter.\n",
    "\n",
    "From the function's docstring:\n",
    "\n",
    "> how : {'left', 'right', 'outer', 'inner'}, default 'inner'\n",
    "\n",
    ">    * left: use only keys from left frame (SQL: left outer join)\n",
    "\n",
    ">    * right: use only keys from right frame (SQL: right outer join)\n",
    "\n",
    ">    * outer: use union of keys from both frames (SQL: full outer join)\n",
    "\n",
    ">    * inner: use intersection of keys from both frames (SQL: inner join)\n",
    "\n",
    "Below are some examples of what each look like."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "left_frame = pd.DataFrame({'key': range(5), \n",
    "                           'left_value': ['a', 'b', 'c', 'd', 'e']})\n",
    "right_frame = pd.DataFrame({'key': range(2, 7), \n",
    "                           'right_value': ['f', 'g', 'h', 'i', 'j']})\n",
    "print('left frame')\n",
    "display(left_frame)\n",
    "print('right frame')\n",
    "display(right_frame)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**inner join (default)**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "pd.merge(left_frame, right_frame, on='key', how='inner')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We lose values from both frames since certain keys do not match up.  The SQL equivalent is:\n",
    "\n",
    "```\n",
    "    SELECT left_frame.key, left_frame.left_value, right_frame.right_value\n",
    "    FROM left_frame\n",
    "    INNER JOIN right_frame\n",
    "        ON left_frame.key = right_frame.key;\n",
    "```\n",
    "\n",
    "Had our *key* columns not been named the same, we could have used the *left_on* and *right_on* parameters to specify which fields to join from each frame.\n",
    "```python\n",
    "    pd.merge(left_frame, right_frame, left_on='left_key', right_on='right_key')\n",
    "```\n",
    "Alternatively, if our keys were indexes, we could use the `left_index` or `right_index` parameters, which accept a True/False value. You can mix and match columns and indexes like so:\n",
    "```python\n",
    "    pd.merge(left_frame, right_frame, left_on='key', right_index=True)\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**left outer join**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "pd.merge(left_frame, right_frame, on='key', how='left')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We keep everything from the left frame, pulling in the value from the right frame where the keys match up. The right_value is NULL where keys do not match (NaN).\n",
    "\n",
    "SQL Equivalent:\n",
    "\n",
    "    SELECT left_frame.key, left_frame.left_value, right_frame.right_value\n",
    "    FROM left_frame\n",
    "    LEFT JOIN right_frame\n",
    "        ON left_frame.key = right_frame.key;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**right outer join**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "pd.merge(left_frame, right_frame, on='key', how='right')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This time we've kept everything from the right frame with the left_value being NULL where the right frame's key did not find a match.\n",
    "\n",
    "SQL Equivalent:\n",
    "\n",
    "    SELECT right_frame.key, left_frame.left_value, right_frame.right_value\n",
    "    FROM left_frame\n",
    "    RIGHT JOIN right_frame\n",
    "        ON left_frame.key = right_frame.key;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**full outer join**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "pd.merge(left_frame, right_frame, on='key', how='outer')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We've kept everything from both frames, regardless of whether or not there was a match on both sides. Where there was not a match, the values corresponding to that key are NULL.\n",
    "\n",
    "SQL Equivalent (though some databases don't allow FULL JOINs (e.g. MySQL)):\n",
    "\n",
    "    SELECT IFNULL(left_frame.key, right_frame.key) key\n",
    "            , left_frame.left_value, right_frame.right_value\n",
    "    FROM left_frame\n",
    "    FULL OUTER JOIN right_frame\n",
    "        ON left_frame.key = right_frame.key;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Combining\n",
    "\n",
    "pandas also provides a way to combine DataFrames along an axis - `pandas.concat`. While the function is equivalent to SQL's UNION clause, there's a lot more that can be done with it.\n",
    "\n",
    "`pandas.concat` takes a list of Series or DataFrames and returns a Series or DataFrame of the concatenated objects. Note that because the function takes list, you can combine many objects at once."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "pd.concat([left_frame, right_frame])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "By default, the function will vertically append the objects to one another, combining columns with the same name. We can see above that values not matching up will be NULL.\n",
    "\n",
    "Additionally, objects can be concatentated side-by-side using the function's *axis* parameter."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "pd.concat([left_frame, right_frame], axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`pandas.concat` can be used in a variety of ways; however, I've typically only used it to combine Series/DataFrames into one unified object. The [documentation](http://pandas.pydata.org/pandas-docs/stable/merging.html#concatenating-objects) has some examples on the ways it can be used."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Grouping\n",
    "\n",
    "Grouping in pandas took some time for me to grasp, but it's pretty awesome once it clicks.\n",
    "\n",
    "pandas `groupby` method draws largely from the [split-apply-combine strategy for data analysis](http://www.jstatsoft.org/v40/i01/paper).  If you're not familiar with this methodology, I highly suggest you read up on it.  It does a great job of illustrating how to properly think through a data problem, which I feel is more important than any technical skill a data analyst/scientist can possess.\n",
    "\n",
    "When approaching a data analysis problem, you'll often break it apart into manageable pieces, perform some operations on each of the pieces, and then put everything back together again (this is the gist split-apply-combine strategy). pandas `groupby` is great for these problems (R users should check out the [plyr](http://plyr.had.co.nz/) and [dplyr](https://github.com/hadley/dplyr) packages).\n",
    "\n",
    "If you've ever used SQL's GROUP BY or an Excel Pivot Table, you've thought with this mindset, probably without realizing it.\n",
    "\n",
    "Assume we have a DataFrame and want to get the average for each group - visually, the split-apply-combine method looks like this:\n",
    "\n",
    "![Source: Gratuitously borrowed from [Hadley Wickham's Data Science in R slides](http://courses.had.co.nz/12-oscon/)](http://i.imgur.com/yjNkiwL.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The City of Chicago is kind enough to publish all city employee salaries to its open data portal. Let's go through some basic `groupby` examples using this data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "!head -n 3 data/city-of-chicago-salaries.csv"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Since the data contains a dollar sign for each salary, python will treat the field as a series of strings. We can use the `converters` parameter to change this when reading in the file.\n",
    "\n",
    ">converters : dict. optional\n",
    "\n",
    ">* Dict of functions for converting values in certain columns. Keys can either be integers or column labels"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "headers = ['name', 'title', 'department', 'salary']\n",
    "chicago = pd.read_csv('data/city-of-chicago-salaries.csv',\n",
    "                      header=0,\n",
    "                      names=headers,\n",
    "                      converters={'salary': lambda x: float(x.replace('$', ''))})\n",
    "chicago.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "pandas `groupby` returns a DataFrameGroupBy object which has a variety of methods, many of which are similar to standard SQL aggregate functions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "by_dept = chicago.groupby('department')\n",
    "by_dept"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Calling `count` returns the total number of NOT NULL values within each column. If we were interested in the total number of records in each group, we could use `size`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "display(by_dept.count().head()) # NOT NULL records within each column\n",
    "display(by_dept.size().head()) # total records for each department"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Summation can be done via `sum`, averaging by `mean`, etc. (if it's a SQL function, chances are it exists in pandas). Oh, and there's median too, something not available in most databases."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "display(by_dept.sum()[20:25]) # total salaries of each department\n",
    "display(by_dept.mean()[20:25]) # average salary of each department\n",
    "display(by_dept.median()[20:25]) # take that, RDBMS!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Operations can also be done on an individual Series within a grouped object. Say we were curious about the five departments with the most distinct titles - the pandas equivalent to:\n",
    "\n",
    "    SELECT department, COUNT(DISTINCT title)\n",
    "    FROM chicago\n",
    "    GROUP BY department\n",
    "    ORDER BY 2 DESC\n",
    "    LIMIT 5;\n",
    "\n",
    "pandas is a lot less verbose here ..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "by_dept.title.nunique().sort_values(ascending=False)[:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### split-apply-combine\n",
    "\n",
    "The real power of `groupby` comes from it's split-apply-combine ability.\n",
    "\n",
    "What if we wanted to see the highest paid employee within each department. Given our current dataset, we'd have to do something like this in SQL:\n",
    "\n",
    "    SELECT *\n",
    "    FROM chicago c\n",
    "    INNER JOIN (\n",
    "        SELECT department, max(salary) max_salary\n",
    "        FROM chicago\n",
    "        GROUP BY department\n",
    "    ) m\n",
    "    ON c.department = m.department\n",
    "    AND c.salary = m.max_salary;\n",
    "    \n",
    "This would give you the highest paid person in each department, but it would return multiple if there were many equally high paid people within a department.\n",
    "\n",
    "Alternatively, you could alter the table, add a column, and then write an update statement to populate that column. However, that's not always an option.\n",
    "\n",
    "_Note: This would be a lot easier in PostgreSQL, T-SQL, and possibly Oracle due to the existence of partition/window/analytic functions. I've chosen to use MySQL syntax throughout this tutorial because of it's popularity. Unfortunately, MySQL doesn't have similar functions._"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Using `groupby` we can define a function (which we'll call `ranker`) that will label each record from 1 to N, where N is the number of employees within the department. We can then call `apply` to, well, _apply_ that function to each group (in this case, each department)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def ranker(df):\n",
    "    \"\"\"Assigns a rank to each employee based on salary, with 1 being the highest paid.\n",
    "    Assumes the data is DESC sorted.\"\"\"\n",
    "    df['dept_rank'] = np.arange(len(df)) + 1\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "chicago.sort_values(by='salary', ascending=False, inplace=True)\n",
    "chicago = chicago.groupby('department').apply(ranker)\n",
    "display(chicago[chicago.dept_rank == 1].head(10))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "chicago[chicago.department == \"LAW\"][:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can now see where each employee ranks within their department based on salary."
   ]
  }
 ],
 "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.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}