3-Indexing.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Indexing\n",
    "\n",
    "A big chunk of pandas' complexity (both in the library and as a user) revolves around indexing.\n",
    "It's a complex task, since we want to support so many use-cases\n",
    "\n",
    "- Like lists, you can index by location.\n",
    "- Like dictionaries, you can index by label.\n",
    "- Like NumPy arrays, you can index by boolean masks.\n",
    "- Any of these indexers could be scalar indexes, or they could be arrays, or they could be slices.\n",
    "- Any of these should work on the index (row labels) or columns of a DataFrame.\n",
    "- And any of these should work on Hierarchical indexes."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The data we'll work with is a subset of the data from beeradvocate.com, via [Standford](https://snap.stanford.edu/data/web-RateBeer.html). The raw data is strangely formatted.\n",
    "\n",
    "```\n",
    "beer/name: Sausa Weizen\n",
    "beer/beerId: 47986\n",
    "beer/brewerId: 10325\n",
    "beer/ABV: 5.00\n",
    "beer/style: Hefeweizen\n",
    "review/appearance: 2.5\n",
    "review/aroma: 2\n",
    "review/palate: 1.5\n",
    "review/taste: 1.5\n",
    "review/overall: 1.5\n",
    "review/time: 1234817823\n",
    "review/profileName: stcules\n",
    "review/text: A lot of foam. But a lot.\tIn the smell some banana, and then lactic and tart. Not a good start.\tQuite dark orange in color, with a lively carbonation (now visible, under the foam).\tAgain tending to lactic sourness.\tSame for the taste. With some yeast and banana.\t\t\n",
    "\n",
    "beer/name: Red Moon\n",
    "beer/beerId: 48213\n",
    "beer/brewerId: 10325\n",
    "beer/ABV: 6.20\n",
    " ...\n",
    "```\n",
    "\n",
    "The dataset was a bit large to processess all at once\n",
    "\n",
    "```bash\n",
    "$ wc -l beeradvocate.txt\n",
    " 22212596 beeradvocate.txt\n",
    "```\n",
    "\n",
    "So I parsed them in chunks.\n",
    "\n",
    "```python\n",
    "def format_review(review):\n",
    "    return dict(map(lambda x: x.strip().split(\": \", 1), review))\n",
    "\n",
    "def as_dataframe(reviews):\n",
    "    col_names = {\n",
    "        'beer/ABV': 'abv',\n",
    "        'beer/beerId': 'beer_id',\n",
    "        'beer/brewerId': 'brewer_id',\n",
    "        'beer/name': 'beer_name',\n",
    "        'beer/style': 'beer_style',\n",
    "        'review/appearance': 'review_appearance',\n",
    "        'review/aroma': 'review_aroma',\n",
    "        'review/overall': 'review_overall',\n",
    "        'review/palate': 'review_palate',\n",
    "        'review/profileName': 'profile_name',\n",
    "        'review/taste': 'review_taste',\n",
    "        'review/text': 'text',\n",
    "        'review/time': 'time'\n",
    "    }\n",
    "    df = pd.DataFrame(list(reviews))\n",
    "    numeric = ['abv', 'review_appearance', 'review_aroma',\n",
    "               'review_overall', 'review_palate', 'review_taste']\n",
    "    df = (df.rename(columns=col_names)\n",
    "            .replace('', np.nan))\n",
    "    df[numeric] = df[numeric].astype(float)\n",
    "    df['time'] = pd.to_datetime(df.time.astype(int), unit='s')\n",
    "    return df\n",
    "\n",
    "def main():\n",
    "    with open('beeradvocate.txt') as f:\n",
    "        reviews = filter(lambda x: x != ('\\n',),\n",
    "                         partitionby(lambda x: x == '\\n', f))\n",
    "        reviews = map(format_review, reviews)\n",
    "        reviews = partition_all(100000, reviews)\n",
    "        os.makedirs('beer_reviews', exist_ok=True)\n",
    "\n",
    "        for i, subset in enumerate(reviews):\n",
    "            print(i, end='\\r')\n",
    "            df = as_dataframe(subset)\n",
    "            df.to_csv('beer_reviews/review_%s.csv' % i, index=False)\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Aside: dask\n",
    "\n",
    "To select the subset we'll work with, about a 10th of the reviews, I used [`dask`](http://dask.readthedocs.org).\n",
    "All of those files wouldn't fit in memory at once. But we can compute quantiles in chunks and aggregate those together. \n",
    "\n",
    "```python\n",
    "\n",
    "In [1]: import dask.dataframe as dd\n",
    "\n",
    "In [2]: df = dd.read_csv('beer_reviews/*.csv', parse_dates=['time'])\n",
    "\n",
    "In [3]: cutoffs = df.time.quantiles([.5, .6])\n",
    "\n",
    "In [4]: %time cutoffs = cutoffs.compute()\n",
    "CPU times: user 20.7 s, sys: 8.37 s, total: 29.1 s\n",
    "Wall time: 28.2 s\n",
    "\n",
    "In [5]: %time subset = df[(df.time >= cutoffs[0]) & (df.time <= cutoffs[1])].compute()\n",
    "CPU times: user 20.9 s, sys: 7.68 s, total: 28.6 s\n",
    "Wall time: 27.5 s\n",
    "\n",
    "In [6]: subset.to_csv('../notebooks/data/beer_subset.csv', index=False)\n",
    "```\n",
    "\n",
    "Just writing `cutoff = df.time.quantile([10])` doesn't actually do the computation, instead it build of dask graph of what it needs to do when asked for the result."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "pd.options.display.max_rows = 10"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df = pd.read_csv('data/beer_subset.csv.gz', parse_dates=['time'], compression='gzip')\n",
    "review_cols = ['review_appearance', 'review_aroma', 'review_overall',\n",
    "               'review_palate', 'review_taste']\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Boolean indexing\n",
    "\n",
    "Like a where clause in SQL. The indexer (or boolean mask) should be 1-dimensional and the same length as the thing being indexed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df.abv < 5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df[df.abv < 5].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice that we just used `[]` there. We can pass the boolean indexer in to `.loc` as well."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df.loc[df.abv < 5, ['beer_style', 'review_overall']].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Again, you can get complicated"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df[((df.abv < 5) & (df.time > pd.Timestamp('2009-06'))) | (df.review_overall >= 4.5)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Be careful with the order of operations. In python `&` and `|` have lower precedence than `>, =, <`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "2 > 1 & 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "(2 > 1) & 0"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-success\">\n",
    "    <b>Exercise</b>: Find the IPAs\n",
    "</div>\n",
    "\n",
    "Select just the rows where the `beer_style` contains `'IPA'`. \n",
    "\n",
    "Hint: `Series` containing strings have a bunch of [useful methods](http://pandas.pydata.org/pandas-docs/stable/text.html#method-summary) under the `DataFrame.<column>.str` namespace. Typically they corrospond to regular python string methods, but\n",
    "\n",
    "- They gracefully propogate missing values\n",
    "- They're a bit more liberal about accepting regular expressions\n",
    "\n",
    "We can't use `'IPA' in df['beer_style']`, since `in` is used to check membership in the series itself, not the strings. But `in` uses `__contains__`, so look for a string method like that."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df.beer_style.str.  # <tab>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Your solution\n",
    "is_ipa = ...\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%load -r 1:4 solutions/solutions_indexing.py"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is quite powerful. Any method that returns a boolean array is potentially an indexer."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-success\">\n",
    "    <b>Exercise</b>: Find a subset of beer styles\n",
    "</div>\n",
    "\n",
    "Find the rows where the beer style is either `'American IPA'` or `'Pilsner'`.\n",
    "Remeber that we use `&` and `|` to do elementwise logical `and`s and `or`s"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Your code here"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%load -r 5:7 solutions/solutions_indexing.py"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# isin\n",
    "\n",
    "Useful for seeing if a value is contained in a collection. We can rewrite the last exercise as:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df[df.beer_style.isin(['American IPA', 'Pilsner'])].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's collect the brewers and beers with the most reviews..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "brewer = df[['brewer_id', 'beer_id']]\n",
    "brewer.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "brewer_ids = df.brewer_id.value_counts().index[:10]\n",
    "beer_ids = df.beer_id.value_counts().index[:10]\n",
    "brewer_ids"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And filter to the reviews that were for the top items.\n",
    "\n",
    "`DataFrame.isin()` can take a dictionary."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "to_find = {\n",
    "    'brewer_id': brewer_ids,\n",
    "    'beer_id': beer_ids\n",
    "}\n",
    "brewer.isin(to_find)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Two dimensional boolean indexing is a bit different:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "brewer[brewer.isin(to_find)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What happened?\n",
    "\n",
    "The result of `DataFrame.isin` is always the same shape as the input.\n",
    "Use `.any` or `.all` if you intend to index with the result.\n",
    "\n",
    "For example, to get reviews where *both* the `brewer_id` and the `beer_id` are in the top 10:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "brewer[brewer.isin(to_find).all('columns')].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "brewer[brewer.isin(to_find).any('columns')].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-success\">\n",
    "    <b>Exercise</b>: Find the rows where all of the reviews are at least 4\n",
    "</div>\n",
    "\n",
    "Select the rows where the scores of the 5 `review_cols` ('review_appearance', 'review_aroma', 'review_overall', 'review_palate', 'review_taste') are *all* at least 4.0.\n",
    "\n",
    "hint: Like NumPy arrays, DataFrames have an `any` and `all` methods that check whether it contains `any` or `all` True values. These methods also take an `axis` argument for the dimension to remove.\n",
    "\n",
    "- `0` or `'index'` removes (or aggregates over) the vertical dimension\n",
    "- `1` or `'columns'` removes (aggregates over) the horizontal dimension."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "review_cols"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# your code goes here"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%load -r 9:20 solutions/solutions_indexing.py"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-success\">\n",
    "    <b>Exercise</b>: Find the rows where the average of the reviews is at least 4\n",
    "</div>\n",
    "\n",
    "select rows where the average of the 5 `review_cols` ('review_appearance', 'review_aroma', 'review_overall', 'review_palate', 'review_taste') is at least 4."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Your code here"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%load -r 20:22 solutions/solutions_indexing.py"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Hierarchical Indexing\n",
    "\n",
    "One of the most powerful and most complicated features of pandas.\n",
    "Let's you represent high-dimensional datasets in a table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "reviews = df.set_index(['profile_name', 'beer_id', 'time'])\n",
    "reviews.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You'll almost always want to sort your MultiIndex."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "reviews = reviews.sort_index()\n",
    "reviews.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Top Reviewers\n",
    "\n",
    "Let's select all the reviews by the top reviewers, by label."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "top_reviewers = df['profile_name'].value_counts().head(5).index\n",
    "top_reviewers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "reviews.loc[top_reviewers, :, :].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The syntax is a bit trickier when you want to specify a row Indexer *and* a column Indexer."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "reviews.loc[(top_reviewers, 99, :), ['beer_name', 'brewer_name']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "reviews.loc[pd.IndexSlice[top_reviewers, 99, :], ['beer_name', 'brewer_id']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-success\">\n",
    "    <b>Exercise</b>: Select the most-reviewd beers\n",
    "</div>\n",
    "\n",
    "Use `.loc` to select the `beer_name` and `beer_style` for the 10 most popular beers, as measured by number of reviews."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Your solution goes here"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%load -r 24:27 solutions/solutions_indexing.py"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Pitfalls\n",
    "\n",
    "\n",
    "Chained indexing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df.loc[df.beer_style.str.contains('IPA')]['beer_name'] = 'yummy'\n",
    "df.loc[df.beer_style.str.contains('IPA')]['beer_name']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Anytime you see back-to-back square-brackets, `][`, you're asking for trouble."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "good = df.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "good.loc[df.beer_style.str.contains('IPA'), 'beer_name']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "good.loc[good.beer_style.str.contains('IPA'), 'beer_name'] = 'yummy'\n",
    "good.loc[good.beer_style.str.contains('IPA'), 'beer_name']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Recap\n",
    "\n",
    "- Boolean masks should always be 1-dimensional and the same length\n",
    "- sort your `MultiIndexes`\n",
    "- `isin` + `.any()` or `.all()` for comparing to collections"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}