Pandas-Aggregation-and-Grouping.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Aggregation and Grouping"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Define a ``display`` magic function for side-by-side viewing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "class display(object):\n",
    "    \"\"\"Display HTML representation of multiple objects\"\"\"\n",
    "    template = \"\"\"<div style=\"float: left; padding: 10px;\">\n",
    "    <p style='font-family:\"Courier New\", Courier, monospace'>{0}</p>{1}\n",
    "    </div>\"\"\"\n",
    "    def __init__(self, *args):\n",
    "        self.args = args\n",
    "        \n",
    "    def _repr_html_(self):\n",
    "        return '\\n'.join(self.template.format(a, eval(a)._repr_html_())\n",
    "                         for a in self.args)\n",
    "    \n",
    "    def __repr__(self):\n",
    "        return '\\n\\n'.join(a + '\\n' + repr(eval(a))\n",
    "                           for a in self.args)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Planets Data\n",
    "\n",
    "- Download the Planets dataset, available via the [Seaborn package](http://seaborn.pydata.org/):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1035, 6)"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import seaborn as sns\n",
    "planets = sns.load_dataset('planets')\n",
    "planets.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>method</th>\n",
       "      <th>number</th>\n",
       "      <th>orbital_period</th>\n",
       "      <th>mass</th>\n",
       "      <th>distance</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Radial Velocity</td>\n",
       "      <td>1</td>\n",
       "      <td>269.300</td>\n",
       "      <td>7.10</td>\n",
       "      <td>77.40</td>\n",
       "      <td>2006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Radial Velocity</td>\n",
       "      <td>1</td>\n",
       "      <td>874.774</td>\n",
       "      <td>2.21</td>\n",
       "      <td>56.95</td>\n",
       "      <td>2008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Radial Velocity</td>\n",
       "      <td>1</td>\n",
       "      <td>763.000</td>\n",
       "      <td>2.60</td>\n",
       "      <td>19.84</td>\n",
       "      <td>2011</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Radial Velocity</td>\n",
       "      <td>1</td>\n",
       "      <td>326.030</td>\n",
       "      <td>19.40</td>\n",
       "      <td>110.62</td>\n",
       "      <td>2007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Radial Velocity</td>\n",
       "      <td>1</td>\n",
       "      <td>516.220</td>\n",
       "      <td>10.50</td>\n",
       "      <td>119.47</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            method  number  orbital_period   mass  distance  year\n",
       "0  Radial Velocity       1         269.300   7.10     77.40  2006\n",
       "1  Radial Velocity       1         874.774   2.21     56.95  2008\n",
       "2  Radial Velocity       1         763.000   2.60     19.84  2011\n",
       "3  Radial Velocity       1         326.030  19.40    110.62  2007\n",
       "4  Radial Velocity       1         516.220  10.50    119.47  2009"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "planets.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Simple Aggregation\n",
    "- Aggregates return __a single value__ from a Pandas ``Series``."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    0.374540\n",
       "1    0.950714\n",
       "2    0.731994\n",
       "3    0.598658\n",
       "4    0.156019\n",
       "dtype: float64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rng = np.random.RandomState(42)\n",
    "ser = pd.Series(rng.rand(5))\n",
    "ser"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2.811925491708157"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ser.sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.5623850983416314"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ser.mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Aggregates return __results within each column__ of a ``DataFrame``."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.155995</td>\n",
       "      <td>0.020584</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.058084</td>\n",
       "      <td>0.969910</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.866176</td>\n",
       "      <td>0.832443</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.601115</td>\n",
       "      <td>0.212339</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.708073</td>\n",
       "      <td>0.181825</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          A         B\n",
       "0  0.155995  0.020584\n",
       "1  0.058084  0.969910\n",
       "2  0.866176  0.832443\n",
       "3  0.601115  0.212339\n",
       "4  0.708073  0.181825"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame({'A': rng.rand(5),\n",
    "                   'B': rng.rand(5)})\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "A    0.477888\n",
       "B    0.443420\n",
       "dtype: float64"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- You can aggregate __by row__ by specifying the ``axis`` argument."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    0.088290\n",
       "1    0.513997\n",
       "2    0.849309\n",
       "3    0.406727\n",
       "4    0.444949\n",
       "dtype: float64"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.mean(axis='columns')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- ``describe()`` returns several common aggregates for each column. (First, let's drop rows with missing values from the Planets dataset.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>number</th>\n",
       "      <th>orbital_period</th>\n",
       "      <th>mass</th>\n",
       "      <th>distance</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>498.00000</td>\n",
       "      <td>498.000000</td>\n",
       "      <td>498.000000</td>\n",
       "      <td>498.000000</td>\n",
       "      <td>498.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>1.73494</td>\n",
       "      <td>835.778671</td>\n",
       "      <td>2.509320</td>\n",
       "      <td>52.068213</td>\n",
       "      <td>2007.377510</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>1.17572</td>\n",
       "      <td>1469.128259</td>\n",
       "      <td>3.636274</td>\n",
       "      <td>46.596041</td>\n",
       "      <td>4.167284</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>1.00000</td>\n",
       "      <td>1.328300</td>\n",
       "      <td>0.003600</td>\n",
       "      <td>1.350000</td>\n",
       "      <td>1989.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>1.00000</td>\n",
       "      <td>38.272250</td>\n",
       "      <td>0.212500</td>\n",
       "      <td>24.497500</td>\n",
       "      <td>2005.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>1.00000</td>\n",
       "      <td>357.000000</td>\n",
       "      <td>1.245000</td>\n",
       "      <td>39.940000</td>\n",
       "      <td>2009.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>2.00000</td>\n",
       "      <td>999.600000</td>\n",
       "      <td>2.867500</td>\n",
       "      <td>59.332500</td>\n",
       "      <td>2011.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>6.00000</td>\n",
       "      <td>17337.500000</td>\n",
       "      <td>25.000000</td>\n",
       "      <td>354.000000</td>\n",
       "      <td>2014.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          number  orbital_period        mass    distance         year\n",
       "count  498.00000      498.000000  498.000000  498.000000   498.000000\n",
       "mean     1.73494      835.778671    2.509320   52.068213  2007.377510\n",
       "std      1.17572     1469.128259    3.636274   46.596041     4.167284\n",
       "min      1.00000        1.328300    0.003600    1.350000  1989.000000\n",
       "25%      1.00000       38.272250    0.212500   24.497500  2005.000000\n",
       "50%      1.00000      357.000000    1.245000   39.940000  2009.000000\n",
       "75%      2.00000      999.600000    2.867500   59.332500  2011.000000\n",
       "max      6.00000    17337.500000   25.000000  354.000000  2014.000000"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "planets.dropna().describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The following table summarizes some other built-in Pandas aggregations:\n",
    "\n",
    "| Aggregation              | Description                     |\n",
    "|--------------------------|---------------------------------|\n",
    "| ``count()``              | Total number of items           |\n",
    "| ``first()``, ``last()``  | First and last item             |\n",
    "| ``mean()``, ``median()`` | Mean and median                 |\n",
    "| ``min()``, ``max()``     | Minimum and maximum             |\n",
    "| ``std()``, ``var()``     | Standard deviation and variance |\n",
    "| ``mad()``                | Mean absolute deviation         |\n",
    "| ``prod()``               | Product of all items            |\n",
    "| ``sum()``                | Sum of all items                |\n",
    "\n",
    "These are all methods of ``DataFrame`` and ``Series`` objects."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### GroupBy: Split, Apply, Combine\n",
    "\n",
    "- ``groupby``: to aggregate based on a label or index. The 'split, apply, combine' flow is a good illustration."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![](figures/03.08-split-apply-combine.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- The *split* step involves breaking up and grouping a ``DataFrame`` depending on the value of the specified key.\n",
    "- The *apply* step involves computing some function, usually an aggregate, transformation, or filtering, within the individual groups.\n",
    "- The *combine* step merges the results of these operations into an output array.\n",
    "- The power of the ``GroupBy`` is that __it abstracts away these steps__: the user need not think about *how* the computation is done under the hood, but rather thinks about the *operation as a whole*."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>key</th>\n",
       "      <th>data</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>B</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>C</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>B</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>C</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  key  data\n",
       "0   A     0\n",
       "1   B     1\n",
       "2   C     2\n",
       "3   A     3\n",
       "4   B     4\n",
       "5   C     5"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame({'key': ['A', 'B', 'C', 'A', 'B', 'C'], 'data': range(6)}, \n",
    "                  columns=['key', 'data'])\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Start with the ``groupby()`` method of ``DataFrame``s, passing the name of the desired key column:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pandas.core.groupby.DataFrameGroupBy object at 0x7f391033ae10>"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby('key')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- ``groupby()`` returns a ``DataFrameGroupBy`` object. Think of it as a special view of a ``DataFrame``, which does no  computation until an aggregation is applied. This \"lazy evaluation\" means that aggregates can be implemented very efficiently in a way that is almost transparent to the user."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>data</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>key</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>A</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B</th>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C</th>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     data\n",
       "key      \n",
       "A       3\n",
       "B       5\n",
       "C       7"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby('key').sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### The GroupBy object\n",
    "\n",
    "- Treat a ``GroupBy`` object as if it's a collection of ``DataFrame``s.\n",
    "- Some important operations made available by a ``GroupBy`` are *aggregate*, *filter*, *transform*, and *apply*."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Column indexing\n",
    "- The ``GroupBy`` object supports column indexing in the same way as the ``DataFrame``, and returns a modified ``GroupBy`` object."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pandas.core.groupby.DataFrameGroupBy object at 0x7f39103ac160>"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "planets.groupby('method')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pandas.core.groupby.SeriesGroupBy object at 0x7f391033a860>"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "planets.groupby('method')['orbital_period']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "method\n",
       "Astrometry                         631.180000\n",
       "Eclipse Timing Variations         4343.500000\n",
       "Imaging                          27500.000000\n",
       "Microlensing                      3300.000000\n",
       "Orbital Brightness Modulation        0.342887\n",
       "Pulsar Timing                       66.541900\n",
       "Pulsation Timing Variations       1170.000000\n",
       "Radial Velocity                    360.200000\n",
       "Transit                              5.714932\n",
       "Transit Timing Variations           57.011000\n",
       "Name: orbital_period, dtype: float64"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "planets.groupby('method')['orbital_period'].median()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Iteration over groups\n",
    "\n",
    "- ``GroupBy`` supports direct iteration over groups, returning each group as a ``Series`` or ``DataFrame``:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Astrometry                     shape=(2, 6)\n",
      "Eclipse Timing Variations      shape=(9, 6)\n",
      "Imaging                        shape=(38, 6)\n",
      "Microlensing                   shape=(23, 6)\n",
      "Orbital Brightness Modulation  shape=(3, 6)\n",
      "Pulsar Timing                  shape=(5, 6)\n",
      "Pulsation Timing Variations    shape=(1, 6)\n",
      "Radial Velocity                shape=(553, 6)\n",
      "Transit                        shape=(397, 6)\n",
      "Transit Timing Variations      shape=(4, 6)\n"
     ]
    }
   ],
   "source": [
    "for (method, group) in planets.groupby('method'):\n",
    "    print(\"{0:30s} shape={1}\".format(method, group.shape))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Dispatch methods\n",
    "\n",
    "- Through some Python class magic, any method not explicitly implemented by ``GroupBy`` will be passed through and called on the groups, whether they are ``DataFrame`` or ``Series`` objects.\n",
    "- For example, you can use ``describe()`` to aggregations that describe each group in the data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "       method                       \n",
       "count  Astrometry                          2.000000\n",
       "       Eclipse Timing Variations           9.000000\n",
       "       Imaging                            38.000000\n",
       "       Microlensing                       23.000000\n",
       "       Orbital Brightness Modulation       3.000000\n",
       "       Pulsar Timing                       5.000000\n",
       "       Pulsation Timing Variations         1.000000\n",
       "       Radial Velocity                   553.000000\n",
       "       Transit                           397.000000\n",
       "       Transit Timing Variations           4.000000\n",
       "mean   Astrometry                       2011.500000\n",
       "       Eclipse Timing Variations        2010.000000\n",
       "       Imaging                          2009.131579\n",
       "       Microlensing                     2009.782609\n",
       "       Orbital Brightness Modulation    2011.666667\n",
       "       Pulsar Timing                    1998.400000\n",
       "       Pulsation Timing Variations      2007.000000\n",
       "       Radial Velocity                  2007.518987\n",
       "       Transit                          2011.236776\n",
       "       Transit Timing Variations        2012.500000\n",
       "std    Astrometry                          2.121320\n",
       "       Eclipse Timing Variations           1.414214\n",
       "       Imaging                             2.781901\n",
       "       Microlensing                        2.859697\n",
       "       Orbital Brightness Modulation       1.154701\n",
       "       Pulsar Timing                       8.384510\n",
       "       Pulsation Timing Variations              NaN\n",
       "       Radial Velocity                     4.249052\n",
       "       Transit                             2.077867\n",
       "       Transit Timing Variations           1.290994\n",
       "                                           ...     \n",
       "50%    Astrometry                       2011.500000\n",
       "       Eclipse Timing Variations        2010.000000\n",
       "       Imaging                          2009.000000\n",
       "       Microlensing                     2010.000000\n",
       "       Orbital Brightness Modulation    2011.000000\n",
       "       Pulsar Timing                    1994.000000\n",
       "       Pulsation Timing Variations      2007.000000\n",
       "       Radial Velocity                  2009.000000\n",
       "       Transit                          2012.000000\n",
       "       Transit Timing Variations        2012.500000\n",
       "75%    Astrometry                       2012.250000\n",
       "       Eclipse Timing Variations        2011.000000\n",
       "       Imaging                          2011.000000\n",
       "       Microlensing                     2012.000000\n",
       "       Orbital Brightness Modulation    2012.000000\n",
       "       Pulsar Timing                    2003.000000\n",
       "       Pulsation Timing Variations      2007.000000\n",
       "       Radial Velocity                  2011.000000\n",
       "       Transit                          2013.000000\n",
       "       Transit Timing Variations        2013.250000\n",
       "max    Astrometry                       2013.000000\n",
       "       Eclipse Timing Variations        2012.000000\n",
       "       Imaging                          2013.000000\n",
       "       Microlensing                     2013.000000\n",
       "       Orbital Brightness Modulation    2013.000000\n",
       "       Pulsar Timing                    2011.000000\n",
       "       Pulsation Timing Variations      2007.000000\n",
       "       Radial Velocity                  2014.000000\n",
       "       Transit                          2014.000000\n",
       "       Transit Timing Variations        2014.000000\n",
       "Length: 80, dtype: float64"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "planets.groupby('method')['year'].describe().unstack()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Notice that the dispatch method is applied *to each individual group*, and the results are then combined within ``GroupBy`` and returned."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Aggregate, filter, transform, apply\n",
    "\n",
    "- ``GroupBy`` objects have ``aggregate()``, ``filter()``, ``transform()``, and ``apply()`` methods that implement a variety of operations before combining the grouped data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>key</th>\n",
       "      <th>data1</th>\n",
       "      <th>data2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>B</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>C</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>B</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>C</td>\n",
       "      <td>5</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  key  data1  data2\n",
       "0   A      0      5\n",
       "1   B      1      0\n",
       "2   C      2      3\n",
       "3   A      3      3\n",
       "4   B      4      7\n",
       "5   C      5      9"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rng = np.random.RandomState(0)\n",
    "df = pd.DataFrame({'key': ['A', 'B', 'C', 'A', 'B', 'C'],\n",
    "                   'data1': range(6),\n",
    "                   'data2': rng.randint(0, 10, 6)},\n",
    "                   columns = ['key', 'data1', 'data2'])\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Aggregation\n",
    "\n",
    "- We're familiar with ``GroupBy`` aggregations with ``sum()``, ``median()``, etc, but ``aggregate()`` is more flexible. It accepts a string, function, or list thereof, and computes all the aggregates at once."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"3\" halign=\"left\">data1</th>\n",
       "      <th colspan=\"3\" halign=\"left\">data2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>min</th>\n",
       "      <th>median</th>\n",
       "      <th>max</th>\n",
       "      <th>min</th>\n",
       "      <th>median</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>key</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>A</th>\n",
       "      <td>0</td>\n",
       "      <td>1.5</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>4.0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B</th>\n",
       "      <td>1</td>\n",
       "      <td>2.5</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>3.5</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C</th>\n",
       "      <td>2</td>\n",
       "      <td>3.5</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>6.0</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    data1            data2           \n",
       "      min median max   min median max\n",
       "key                                  \n",
       "A       0    1.5   3     3    4.0   5\n",
       "B       1    2.5   4     0    3.5   7\n",
       "C       2    3.5   5     3    6.0   9"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby('key').aggregate(['min', np.median, max])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- We can pass a dictionary mapping column names to operations to be applied on that column:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>data1</th>\n",
       "      <th>data2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>key</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>A</th>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B</th>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C</th>\n",
       "      <td>2</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     data1  data2\n",
       "key              \n",
       "A        0      5\n",
       "B        1      7\n",
       "C        2      9"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby('key').aggregate({'data1': 'min',\n",
    "                             'data2': 'max'})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Filtering\n",
    "\n",
    "- Filtering allows you to drop data based on group properties. \n",
    "- For example, we might want to keep all groups in which the standard deviation is larger than some critical value. The filter function should return a Boolean value specifying whether the group passes the filtering. Here because group A does not have a standard deviation greater than 4, it is dropped from the result."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"float: left; padding: 10px;\">\n",
       "    <p style='font-family:\"Courier New\", Courier, monospace'>df</p><div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>key</th>\n",
       "      <th>data1</th>\n",
       "      <th>data2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>B</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>C</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>B</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>C</td>\n",
       "      <td>5</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "    </div>\n",
       "<div style=\"float: left; padding: 10px;\">\n",
       "    <p style='font-family:\"Courier New\", Courier, monospace'>df.groupby('key').std()</p><div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>data1</th>\n",
       "      <th>data2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>key</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>A</th>\n",
       "      <td>2.12132</td>\n",
       "      <td>1.414214</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B</th>\n",
       "      <td>2.12132</td>\n",
       "      <td>4.949747</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C</th>\n",
       "      <td>2.12132</td>\n",
       "      <td>4.242641</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "    </div>\n",
       "<div style=\"float: left; padding: 10px;\">\n",
       "    <p style='font-family:\"Courier New\", Courier, monospace'>df.groupby('key').filter(filter_func)</p><div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>key</th>\n",
       "      <th>data1</th>\n",
       "      <th>data2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>B</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>C</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>B</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>C</td>\n",
       "      <td>5</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "    </div>"
      ],
      "text/plain": [
       "df\n",
       "  key  data1  data2\n",
       "0   A      0      5\n",
       "1   B      1      0\n",
       "2   C      2      3\n",
       "3   A      3      3\n",
       "4   B      4      7\n",
       "5   C      5      9\n",
       "\n",
       "df.groupby('key').std()\n",
       "       data1     data2\n",
       "key                   \n",
       "A    2.12132  1.414214\n",
       "B    2.12132  4.949747\n",
       "C    2.12132  4.242641\n",
       "\n",
       "df.groupby('key').filter(filter_func)\n",
       "  key  data1  data2\n",
       "1   B      1      0\n",
       "2   C      2      3\n",
       "4   B      4      7\n",
       "5   C      5      9"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def filter_func(x):\n",
    "    return x['data2'].std() > 4\n",
    "\n",
    "display('df', \"df.groupby('key').std()\", \"df.groupby('key').filter(filter_func)\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Transformation\n",
    "\n",
    "- Aggregations return a reduced version of the data; transformations return a transformed version of the full dataset to recombine. For such a transformation, the output is the same shape as the input. A common example is to center the data by subtracting the group-wise mean:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>data1</th>\n",
       "      <th>data2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>-1.5</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>-1.5</td>\n",
       "      <td>-3.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>-1.5</td>\n",
       "      <td>-3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.5</td>\n",
       "      <td>-1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1.5</td>\n",
       "      <td>3.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1.5</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   data1  data2\n",
       "0   -1.5    1.0\n",
       "1   -1.5   -3.5\n",
       "2   -1.5   -3.0\n",
       "3    1.5   -1.0\n",
       "4    1.5    3.5\n",
       "5    1.5    3.0"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby('key').transform(lambda x: x - x.mean())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### The apply() method\n",
    "\n",
    "- ``apply()`` lets you apply an arbitrary function to the group results.\n",
    "- The function accepts a ``DataFrame``, and returns either a Pandas object (e.g., ``DataFrame``, ``Series``) or a scalar; the combine operation will adapt to the output type.\n",
    "- This example normalizes the first column by the sum of the second:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"float: left; padding: 10px;\">\n",
       "    <p style='font-family:\"Courier New\", Courier, monospace'>df</p><div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>key</th>\n",
       "      <th>data1</th>\n",
       "      <th>data2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>B</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>C</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>B</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>C</td>\n",
       "      <td>5</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "    </div>\n",
       "<div style=\"float: left; padding: 10px;\">\n",
       "    <p style='font-family:\"Courier New\", Courier, monospace'>df.groupby('key').apply(norm_by_data2)</p><div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>key</th>\n",
       "      <th>data1</th>\n",
       "      <th>data2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>B</td>\n",
       "      <td>0.142857</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>C</td>\n",
       "      <td>0.166667</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A</td>\n",
       "      <td>0.375000</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>B</td>\n",
       "      <td>0.571429</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>C</td>\n",
       "      <td>0.416667</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "    </div>"
      ],
      "text/plain": [
       "df\n",
       "  key  data1  data2\n",
       "0   A      0      5\n",
       "1   B      1      0\n",
       "2   C      2      3\n",
       "3   A      3      3\n",
       "4   B      4      7\n",
       "5   C      5      9\n",
       "\n",
       "df.groupby('key').apply(norm_by_data2)\n",
       "  key     data1  data2\n",
       "0   A  0.000000      5\n",
       "1   B  0.142857      0\n",
       "2   C  0.166667      3\n",
       "3   A  0.375000      3\n",
       "4   B  0.571429      7\n",
       "5   C  0.416667      9"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def norm_by_data2(x):\n",
    "    # x is a DataFrame of group values\n",
    "    x['data1'] /= x['data2'].sum()\n",
    "    return x\n",
    "\n",
    "display('df', \"df.groupby('key').apply(norm_by_data2)\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Specifying the split key\n",
    "\n",
    "- In the simple examples presented before, we split the ``DataFrame`` on a single column name.\n",
    "- This is just one of many options by which the groups can be defined."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- The key can be any series or list with a length matching that of the ``DataFrame``:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"float: left; padding: 10px;\">\n",
       "    <p style='font-family:\"Courier New\", Courier, monospace'>df</p><div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>key</th>\n",
       "      <th>data1</th>\n",
       "      <th>data2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>B</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>C</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>B</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>C</td>\n",
       "      <td>5</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "    </div>\n",
       "<div style=\"float: left; padding: 10px;\">\n",
       "    <p style='font-family:\"Courier New\", Courier, monospace'>df.groupby(L).sum()</p><div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>data1</th>\n",
       "      <th>data2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "    </div>"
      ],
      "text/plain": [
       "df\n",
       "  key  data1  data2\n",
       "0   A      0      5\n",
       "1   B      1      0\n",
       "2   C      2      3\n",
       "3   A      3      3\n",
       "4   B      4      7\n",
       "5   C      5      9\n",
       "\n",
       "df.groupby(L).sum()\n",
       "   data1  data2\n",
       "0      7     17\n",
       "1      4      3\n",
       "2      4      7"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "L = [0, 1, 0, 1, 2, 0]\n",
    "display('df', 'df.groupby(L).sum()')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- This means there's another, more verbose way of accomplishing the ``df.groupby('key')`` from before:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"float: left; padding: 10px;\">\n",
       "    <p style='font-family:\"Courier New\", Courier, monospace'>df</p><div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>key</th>\n",
       "      <th>data1</th>\n",
       "      <th>data2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>B</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>C</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>B</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>C</td>\n",
       "      <td>5</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "    </div>\n",
       "<div style=\"float: left; padding: 10px;\">\n",
       "    <p style='font-family:\"Courier New\", Courier, monospace'>df.groupby(df['key']).sum()</p><div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>data1</th>\n",
       "      <th>data2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>key</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>A</th>\n",
       "      <td>3</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B</th>\n",
       "      <td>5</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C</th>\n",
       "      <td>7</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "    </div>"
      ],
      "text/plain": [
       "df\n",
       "  key  data1  data2\n",
       "0   A      0      5\n",
       "1   B      1      0\n",
       "2   C      2      3\n",
       "3   A      3      3\n",
       "4   B      4      7\n",
       "5   C      5      9\n",
       "\n",
       "df.groupby(df['key']).sum()\n",
       "     data1  data2\n",
       "key              \n",
       "A        3      8\n",
       "B        5      7\n",
       "C        7     12"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "display('df', \"df.groupby(df['key']).sum()\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Another method is to provide a dictionary that maps index values to the group keys:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"float: left; padding: 10px;\">\n",
       "    <p style='font-family:\"Courier New\", Courier, monospace'>df2</p><div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>data1</th>\n",
       "      <th>data2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>key</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>A</th>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B</th>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C</th>\n",
       "      <td>5</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "    </div>\n",
       "<div style=\"float: left; padding: 10px;\">\n",
       "    <p style='font-family:\"Courier New\", Courier, monospace'>df2.groupby(mapping).sum()</p><div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>data1</th>\n",
       "      <th>data2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>consonant</th>\n",
       "      <td>12</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>vowel</th>\n",
       "      <td>3</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "    </div>"
      ],
      "text/plain": [
       "df2\n",
       "     data1  data2\n",
       "key              \n",
       "A        0      5\n",
       "B        1      0\n",
       "C        2      3\n",
       "A        3      3\n",
       "B        4      7\n",
       "C        5      9\n",
       "\n",
       "df2.groupby(mapping).sum()\n",
       "           data1  data2\n",
       "consonant     12     19\n",
       "vowel          3      8"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2 = df.set_index('key')\n",
    "mapping = {'A': 'vowel', 'B': 'consonant', 'C': 'consonant'}\n",
    "display('df2', 'df2.groupby(mapping).sum()')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Similar to mapping, you can pass any Python function that will input the index value and output the group:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"float: left; padding: 10px;\">\n",
       "    <p style='font-family:\"Courier New\", Courier, monospace'>df2</p><div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>data1</th>\n",
       "      <th>data2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>key</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>A</th>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B</th>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C</th>\n",
       "      <td>5</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "    </div>\n",
       "<div style=\"float: left; padding: 10px;\">\n",
       "    <p style='font-family:\"Courier New\", Courier, monospace'>df2.groupby(str.lower).mean()</p><div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>data1</th>\n",
       "      <th>data2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>1.5</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>2.5</td>\n",
       "      <td>3.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c</th>\n",
       "      <td>3.5</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "    </div>"
      ],
      "text/plain": [
       "df2\n",
       "     data1  data2\n",
       "key              \n",
       "A        0      5\n",
       "B        1      0\n",
       "C        2      3\n",
       "A        3      3\n",
       "B        4      7\n",
       "C        5      9\n",
       "\n",
       "df2.groupby(str.lower).mean()\n",
       "   data1  data2\n",
       "a    1.5    4.0\n",
       "b    2.5    3.5\n",
       "c    3.5    6.0"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "display('df2', 'df2.groupby(str.lower).mean()')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Further, any of the preceding key choices can be combined to group on a multi-index:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>data1</th>\n",
       "      <th>data2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <th>vowel</th>\n",
       "      <td>1.5</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <th>consonant</th>\n",
       "      <td>2.5</td>\n",
       "      <td>3.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c</th>\n",
       "      <th>consonant</th>\n",
       "      <td>3.5</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             data1  data2\n",
       "a vowel        1.5    4.0\n",
       "b consonant    2.5    3.5\n",
       "c consonant    3.5    6.0"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.groupby([str.lower, mapping]).mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Grouping example\n",
    "\n",
    "- As an example of this, in a couple lines of Python code we can put all these together and count discovered planets by method and by decade:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>decade</th>\n",
       "      <th>1980s</th>\n",
       "      <th>1990s</th>\n",
       "      <th>2000s</th>\n",
       "      <th>2010s</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>method</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Astrometry</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Eclipse Timing Variations</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Imaging</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>21.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Microlensing</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Orbital Brightness Modulation</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pulsar Timing</th>\n",
       "      <td>0.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pulsation Timing Variations</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Radial Velocity</th>\n",
       "      <td>1.0</td>\n",
       "      <td>52.0</td>\n",
       "      <td>475.0</td>\n",
       "      <td>424.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Transit</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>712.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Transit Timing Variations</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "decade                         1980s  1990s  2000s  2010s\n",
       "method                                                   \n",
       "Astrometry                       0.0    0.0    0.0    2.0\n",
       "Eclipse Timing Variations        0.0    0.0    5.0   10.0\n",
       "Imaging                          0.0    0.0   29.0   21.0\n",
       "Microlensing                     0.0    0.0   12.0   15.0\n",
       "Orbital Brightness Modulation    0.0    0.0    0.0    5.0\n",
       "Pulsar Timing                    0.0    9.0    1.0    1.0\n",
       "Pulsation Timing Variations      0.0    0.0    1.0    0.0\n",
       "Radial Velocity                  1.0   52.0  475.0  424.0\n",
       "Transit                          0.0    0.0   64.0  712.0\n",
       "Transit Timing Variations        0.0    0.0    0.0    9.0"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "decade = 10 * (planets['year'] // 10)\n",
    "decade = decade.astype(str) + 's'\n",
    "decade.name = 'decade'\n",
    "planets.groupby(['method', decade])['number'].sum().unstack().fillna(0)"
   ]
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "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.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}