PythonForDataAnalysis/pfda_ch10.ipynb

{
 "nbformat": 4,
 "nbformat_minor": 2,
 "metadata": {
  "language_info": {
   "name": "python",
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   }
  },
  "orig_nbformat": 2,
  "file_extension": ".py",
  "mimetype": "text/x-python",
  "name": "python",
  "npconvert_exporter": "python",
  "pygments_lexer": "ipython3",
  "version": 3
 },
 "cells": [
  {
   "cell_type": "markdown",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Chapter 10. Data aggregation and group operations\n",
    "\n",
    "## 10.1 Groupby mechanics\n",
    "\n",
    "The grouping key can be provided in different forms:\n",
    "\n",
    "1. a list of values the same length oas the axis being grouped\n",
    "2. a column name\n",
    "3. a dictionary or Series that corresponds the values on the axis being grouped and the group names\n",
    "4. a function to be invoked on the axis index or individual index labels\n",
    "\n",
    "The following are some examples using these methods."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "np.random.seed(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>key1</th>\n      <th>key2</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>one</td>\n      <td>1.764052</td>\n      <td>-0.977278</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>a</td>\n      <td>two</td>\n      <td>0.400157</td>\n      <td>0.950088</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>b</td>\n      <td>one</td>\n      <td>0.978738</td>\n      <td>-0.151357</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>b</td>\n      <td>two</td>\n      <td>2.240893</td>\n      <td>-0.103219</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>a</td>\n      <td>one</td>\n      <td>1.867558</td>\n      <td>0.410599</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": "  key1 key2     data1     data2\n0    a  one  1.764052 -0.977278\n1    a  two  0.400157  0.950088\n2    b  one  0.978738 -0.151357\n3    b  two  2.240893 -0.103219\n4    a  one  1.867558  0.410599"
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame({\n",
    "    'key1' : ['a', 'a', 'b', 'b', 'a'],\n",
    "    'key2' : ['one', 'two', 'one', 'two', 'one'],\n",
    "    'data1' : np.random.randn(5), \n",
    "    'data2' : np.random.randn(5)\n",
    "})\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>key1</th>\n      <th>key2</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>one</td>\n      <td>0.144044</td>\n      <td>0.333674</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>a</td>\n      <td>two</td>\n      <td>1.454274</td>\n      <td>1.494079</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>b</td>\n      <td>one</td>\n      <td>0.761038</td>\n      <td>-0.205158</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>b</td>\n      <td>two</td>\n      <td>0.121675</td>\n      <td>0.313068</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>a</td>\n      <td>one</td>\n      <td>0.443863</td>\n      <td>-0.854096</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": "  key1 key2     data1     data2\n0    a  one  0.144044  0.333674\n1    a  two  1.454274  1.494079\n2    b  one  0.761038 -0.205158\n3    b  two  0.121675  0.313068\n4    a  one  0.443863 -0.854096"
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame({\n",
    "    'key1' : ['a', 'a', 'b', 'b', 'a'],\n",
    "    'key2' : ['one', 'two', 'one', 'two', 'one'],\n",
    "    'data1' : np.random.randn(5), \n",
    "    'data2' : np.random.randn(5)\n",
    "})\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": "<pandas.core.groupby.generic.SeriesGroupBy object at 0x12d2e21d0>"
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# `groupby()` creates a new `GroupBy` object.\n",
    "grouped = df['data1'].groupby(df['key1'])\n",
    "grouped"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": "key1\na    0.680727\nb    0.441356\nName: data1, dtype: float64"
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Mean of the data in the 'data1' column, grouped by 'key1'.\n",
    "grouped.mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": "key1  key2\na     one     0.293953\n      two     1.454274\nb     one     0.761038\n      two     0.121675\nName: data1, dtype: float64"
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Group by two columns.\n",
    "means = df['data1'].groupby([df['key1'], df['key2']]).mean()\n",
    "means"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th>key2</th>\n      <th>one</th>\n      <th>two</th>\n    </tr>\n    <tr>\n      <th>key1</th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>a</th>\n      <td>0.293953</td>\n      <td>1.454274</td>\n    </tr>\n    <tr>\n      <th>b</th>\n      <td>0.761038</td>\n      <td>0.121675</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": "key2       one       two\nkey1                    \na     0.293953  1.454274\nb     0.761038  0.121675"
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "means.unstack()"
   ]
  },
  {
   "cell_type": "markdown",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "If the grouping information is a column of the same DataFrame, then only the grouping column name is required."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\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>key1</th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>a</th>\n      <td>0.680727</td>\n      <td>0.324553</td>\n    </tr>\n    <tr>\n      <th>b</th>\n      <td>0.441356</td>\n      <td>0.053955</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": "         data1     data2\nkey1                    \na     0.680727  0.324553\nb     0.441356  0.053955"
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby('key1').mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\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    <tr>\n      <th>key1</th>\n      <th>key2</th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">a</th>\n      <th>one</th>\n      <td>0.293953</td>\n      <td>-0.260211</td>\n    </tr>\n    <tr>\n      <th>two</th>\n      <td>1.454274</td>\n      <td>1.494079</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">b</th>\n      <th>one</th>\n      <td>0.761038</td>\n      <td>-0.205158</td>\n    </tr>\n    <tr>\n      <th>two</th>\n      <td>0.121675</td>\n      <td>0.313068</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": "              data1     data2\nkey1 key2                    \na    one   0.293953 -0.260211\n     two   1.454274  1.494079\nb    one   0.761038 -0.205158\n     two   0.121675  0.313068"
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby(['key1', 'key2']).mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "A frequently useful method on a grouped DataFrame is `size()`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": "key1  key2\na     one     2\n      two     1\nb     one     1\n      two     1\ndtype: int64"
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby(['key1', 'key2']).size()"
   ]
  },
  {
   "cell_type": "markdown",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "### Iterating over groups\n",
    "\n",
    "The GroupBy object created by `groupby()` supports iteration over a sequence of 2-tuples containing the group name and the data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": "group name: a\n  key1 key2     data1     data2\n0    a  one  0.144044  0.333674\n1    a  two  1.454274  1.494079\n4    a  one  0.443863 -0.854096\n\ngroup name: b\n  key1 key2     data1     data2\n2    b  one  0.761038 -0.205158\n3    b  two  0.121675  0.313068\n\n"
    }
   ],
   "source": [
    "for name, group in df.groupby('key1'):\n",
    "    print(f'group name: {name}')\n",
    "    print(group)\n",
    "    print('')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": "group name: a-one\n  key1 key2     data1     data2\n0    a  one  0.144044  0.333674\n4    a  one  0.443863 -0.854096\n\ngroup name: a-two\n  key1 key2     data1     data2\n1    a  two  1.454274  1.494079\n\ngroup name: b-one\n  key1 key2     data1     data2\n2    b  one  0.761038 -0.205158\n\ngroup name: b-two\n  key1 key2     data1     data2\n3    b  two  0.121675  0.313068\n\n"
    }
   ],
   "source": [
    "for name, group in df.groupby(['key1', 'key2']):\n",
    "    print(f'group name: {name[0]}-{name[1]}')\n",
    "    print(group)\n",
    "    print('')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "By default, `groupby()` groups on `axis=0`, though the columns could also be grouped."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": "key1      object\nkey2      object\ndata1    float64\ndata2    float64\ndtype: object"
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": "data type: float64\n      data1     data2\n0  0.144044  0.333674\n1  1.454274  1.494079\n2  0.761038 -0.205158\n3  0.121675  0.313068\n4  0.443863 -0.854096\n\ndata type: object\n  key1 key2\n0    a  one\n1    a  two\n2    b  one\n3    b  two\n4    a  one\n\n"
    }
   ],
   "source": [
    "grouped = df.groupby(df.dtypes, axis=1)\n",
    "for dtype, group in grouped:\n",
    "    print(f'data type: {dtype}')\n",
    "    print(group)\n",
    "    print('')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "### Selecting a column or subset of columns\n",
    "\n",
    "A GroupBy object can still be indexed by column name.\n",
    "The next two statements are equivalent."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": "<pandas.core.groupby.generic.SeriesGroupBy object at 0x12d2db190>"
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby('key1')['data1']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": "<pandas.core.groupby.generic.SeriesGroupBy object at 0x12d2f02d0>"
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['data1'].groupby(df['key1'])"
   ]
  },
  {
   "cell_type": "markdown",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "### Grouping with dictionaries and Series\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\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      <th>c</th>\n      <th>d</th>\n      <th>e</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>Joe</th>\n      <td>-2.552990</td>\n      <td>0.653619</td>\n      <td>0.864436</td>\n      <td>-0.742165</td>\n      <td>2.269755</td>\n    </tr>\n    <tr>\n      <th>Steve</th>\n      <td>-1.454366</td>\n      <td>0.045759</td>\n      <td>-0.187184</td>\n      <td>1.532779</td>\n      <td>1.469359</td>\n    </tr>\n    <tr>\n      <th>Wex</th>\n      <td>0.154947</td>\n      <td>0.378163</td>\n      <td>-0.887786</td>\n      <td>-1.980796</td>\n      <td>-0.347912</td>\n    </tr>\n    <tr>\n      <th>Jim</th>\n      <td>0.156349</td>\n      <td>1.230291</td>\n      <td>1.202380</td>\n      <td>-0.387327</td>\n      <td>-0.302303</td>\n    </tr>\n    <tr>\n      <th>Travis</th>\n      <td>-1.048553</td>\n      <td>-1.420018</td>\n      <td>-1.706270</td>\n      <td>1.950775</td>\n      <td>-0.509652</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": "               a         b         c         d         e\nJoe    -2.552990  0.653619  0.864436 -0.742165  2.269755\nSteve  -1.454366  0.045759 -0.187184  1.532779  1.469359\nWex     0.154947  0.378163 -0.887786 -1.980796 -0.347912\nJim     0.156349  1.230291  1.202380 -0.387327 -0.302303\nTravis -1.048553 -1.420018 -1.706270  1.950775 -0.509652"
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "people = pd.DataFrame(np.random.randn(5, 5),\n",
    "                      columns=list('abcde'),\n",
    "                      index=['Joe', 'Steve', 'Wex', 'Jim', 'Travis'])\n",
    "people"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\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      <th>c</th>\n      <th>d</th>\n      <th>e</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>Joe</th>\n      <td>-2.552990</td>\n      <td>0.653619</td>\n      <td>0.864436</td>\n      <td>-0.742165</td>\n      <td>2.269755</td>\n    </tr>\n    <tr>\n      <th>Steve</th>\n      <td>-1.454366</td>\n      <td>0.045759</td>\n      <td>-0.187184</td>\n      <td>1.532779</td>\n      <td>1.469359</td>\n    </tr>\n    <tr>\n      <th>Wex</th>\n      <td>0.154947</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>-1.980796</td>\n      <td>-0.347912</td>\n    </tr>\n    <tr>\n      <th>Jim</th>\n      <td>0.156349</td>\n      <td>1.230291</td>\n      <td>1.202380</td>\n      <td>-0.387327</td>\n      <td>-0.302303</td>\n    </tr>\n    <tr>\n      <th>Travis</th>\n      <td>-1.048553</td>\n      <td>-1.420018</td>\n      <td>-1.706270</td>\n      <td>1.950775</td>\n      <td>-0.509652</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": "               a         b         c         d         e\nJoe    -2.552990  0.653619  0.864436 -0.742165  2.269755\nSteve  -1.454366  0.045759 -0.187184  1.532779  1.469359\nWex     0.154947       NaN       NaN -1.980796 -0.347912\nJim     0.156349  1.230291  1.202380 -0.387327 -0.302303\nTravis -1.048553 -1.420018 -1.706270  1.950775 -0.509652"
     },
     "execution_count": 93,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "people.iloc[2, [1, 2]] = np.nan\n",
    "people"
   ]
  },
  {
   "cell_type": "markdown",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "If I have a group correspondence for the columns and want to sum together the columns by these groups, I can just pass the dictionary for grouping."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [],
   "source": [
    "mapping = {\n",
    "    'a': 'red', 'b': 'red', 'c': 'blue',\n",
    "    'd': 'blue', 'e': 'red', 'f' : 'orange'\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>blue</th>\n      <th>red</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>Joe</th>\n      <td>0.122271</td>\n      <td>0.370383</td>\n    </tr>\n    <tr>\n      <th>Steve</th>\n      <td>1.345595</td>\n      <td>0.060752</td>\n    </tr>\n    <tr>\n      <th>Wex</th>\n      <td>-1.980796</td>\n      <td>-0.192965</td>\n    </tr>\n    <tr>\n      <th>Jim</th>\n      <td>0.815053</td>\n      <td>1.084337</td>\n    </tr>\n    <tr>\n      <th>Travis</th>\n      <td>0.244505</td>\n      <td>-2.978223</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": "            blue       red\nJoe     0.122271  0.370383\nSteve   1.345595  0.060752\nWex    -1.980796 -0.192965\nJim     0.815053  1.084337\nTravis  0.244505 -2.978223"
     },
     "execution_count": 95,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "by_column = people.groupby(mapping, axis=1)\n",
    "by_column.sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "### Grouping with functions\n",
    "\n",
    "A function can be used to create the mappings.\n",
    "Each group key will be passed once, and the return value defines the groups.\n",
    "\n",
    "Here is an example of grouping by the length of the first names."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\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      <th>c</th>\n      <th>d</th>\n      <th>e</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>3</th>\n      <td>-2.241693</td>\n      <td>1.883909</td>\n      <td>2.066816</td>\n      <td>-3.110288</td>\n      <td>1.619540</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>-1.454366</td>\n      <td>0.045759</td>\n      <td>-0.187184</td>\n      <td>1.532779</td>\n      <td>1.469359</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>-1.048553</td>\n      <td>-1.420018</td>\n      <td>-1.706270</td>\n      <td>1.950775</td>\n      <td>-0.509652</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": "          a         b         c         d         e\n3 -2.241693  1.883909  2.066816 -3.110288  1.619540\n5 -1.454366  0.045759 -0.187184  1.532779  1.469359\n6 -1.048553 -1.420018 -1.706270  1.950775 -0.509652"
     },
     "execution_count": 96,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "people.groupby(len).sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "It is possible to use both a function and an array or dictionary for grouping at the same time."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\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>a</th>\n      <th>b</th>\n      <th>c</th>\n      <th>d</th>\n      <th>e</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">3</th>\n      <th>one</th>\n      <td>-2.552990</td>\n      <td>0.653619</td>\n      <td>0.864436</td>\n      <td>-1.980796</td>\n      <td>-0.347912</td>\n    </tr>\n    <tr>\n      <th>two</th>\n      <td>0.156349</td>\n      <td>1.230291</td>\n      <td>1.202380</td>\n      <td>-0.387327</td>\n      <td>-0.302303</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <th>one</th>\n      <td>-1.454366</td>\n      <td>0.045759</td>\n      <td>-0.187184</td>\n      <td>1.532779</td>\n      <td>1.469359</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <th>two</th>\n      <td>-1.048553</td>\n      <td>-1.420018</td>\n      <td>-1.706270</td>\n      <td>1.950775</td>\n      <td>-0.509652</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": "              a         b         c         d         e\n3 one -2.552990  0.653619  0.864436 -1.980796 -0.347912\n  two  0.156349  1.230291  1.202380 -0.387327 -0.302303\n5 one -1.454366  0.045759 -0.187184  1.532779  1.469359\n6 two -1.048553 -1.420018 -1.706270  1.950775 -0.509652"
     },
     "execution_count": 97,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "key_list = ['one', 'one', 'one', 'two', 'two']\n",
    "people.groupby([len, key_list]).min()"
   ]
  },
  {
   "cell_type": "markdown",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "### Grouping by index levels\n",
    "\n",
    "For hierarchically indexed data structures, the levels of the axis can be used for grouping."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead tr th {\n        text-align: left;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr>\n      <th>cty</th>\n      <th colspan=\"3\" halign=\"left\">US</th>\n      <th colspan=\"2\" halign=\"left\">JP</th>\n    </tr>\n    <tr>\n      <th>tenor</th>\n      <th>1</th>\n      <th>3</th>\n      <th>5</th>\n      <th>1</th>\n      <th>3</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>-0.438074</td>\n      <td>-1.252795</td>\n      <td>0.777490</td>\n      <td>-1.613898</td>\n      <td>-0.212740</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>-0.895467</td>\n      <td>0.386902</td>\n      <td>-0.510805</td>\n      <td>-1.180632</td>\n      <td>-0.028182</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>0.428332</td>\n      <td>0.066517</td>\n      <td>0.302472</td>\n      <td>-0.634322</td>\n      <td>-0.362741</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>-0.672460</td>\n      <td>-0.359553</td>\n      <td>-0.813146</td>\n      <td>-1.726283</td>\n      <td>0.177426</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": "cty          US                            JP          \ntenor         1         3         5         1         3\n0     -0.438074 -1.252795  0.777490 -1.613898 -0.212740\n1     -0.895467  0.386902 -0.510805 -1.180632 -0.028182\n2      0.428332  0.066517  0.302472 -0.634322 -0.362741\n3     -0.672460 -0.359553 -0.813146 -1.726283  0.177426"
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "columns = pd.MultiIndex.from_arrays([['US', 'US', 'US', 'JP', 'JP'],\n",
    "                                     [1, 3, 5, 1, 3]],\n",
    "                                    names=['cty', 'tenor'])\n",
    "hier_df = pd.DataFrame(np.random.randn(4, 5), columns=columns)\n",
    "hier_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th>cty</th>\n      <th>JP</th>\n      <th>US</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>2</td>\n      <td>3</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>2</td>\n      <td>3</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>2</td>\n      <td>3</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>2</td>\n      <td>3</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": "cty  JP  US\n0     2   3\n1     2   3\n2     2   3\n3     2   3"
     },
     "execution_count": 99,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hier_df.groupby(level='cty', axis=1).count()"
   ]
  },
  {
   "cell_type": "markdown",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## 10.2 Data Aggregation\n",
    "\n",
    "An aggregation method is a data transformation that turns an array into a scalar value.\n",
    "When used on a GroupBy object, the transformation is applied to each group, separately."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>key1</th>\n      <th>key2</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>one</td>\n      <td>0.144044</td>\n      <td>0.333674</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>a</td>\n      <td>two</td>\n      <td>1.454274</td>\n      <td>1.494079</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>b</td>\n      <td>one</td>\n      <td>0.761038</td>\n      <td>-0.205158</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>b</td>\n      <td>two</td>\n      <td>0.121675</td>\n      <td>0.313068</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>a</td>\n      <td>one</td>\n      <td>0.443863</td>\n      <td>-0.854096</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": "  key1 key2     data1     data2\n0    a  one  0.144044  0.333674\n1    a  two  1.454274  1.494079\n2    b  one  0.761038 -0.205158\n3    b  two  0.121675  0.313068\n4    a  one  0.443863 -0.854096"
     },
     "execution_count": 100,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": "key1\na    0.443863\nb    0.441356\nName: data1, dtype: float64"
     },
     "execution_count": 101,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped = df.groupby('key1')\n",
    "grouped['data1'].quantile()"
   ]
  },
  {
   "cell_type": "markdown",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "It is common to define custom aggregation methods.\n",
    "The can be applied to grouped data by passing them to the `agg()` method of a GroupBy object."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\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>key1</th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>a</th>\n      <td>1.310230</td>\n      <td>2.348175</td>\n    </tr>\n    <tr>\n      <th>b</th>\n      <td>0.639363</td>\n      <td>0.518226</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": "         data1     data2\nkey1                    \na     1.310230  2.348175\nb     0.639363  0.518226"
     },
     "execution_count": 102,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def peak_to_peak(arr):\n",
    "    return arr.max() - arr.min()\n",
    "\n",
    "grouped.agg(peak_to_peak)"
   ]
  },
  {
   "cell_type": "markdown",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Other methods will also perform as expected, even though they are not sticktly aggregations."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead tr th {\n        text-align: left;\n    }\n\n    .dataframe thead tr:last-of-type th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr>\n      <th></th>\n      <th colspan=\"8\" halign=\"left\">data1</th>\n      <th colspan=\"8\" halign=\"left\">data2</th>\n    </tr>\n    <tr>\n      <th></th>\n      <th>count</th>\n      <th>mean</th>\n      <th>std</th>\n      <th>min</th>\n      <th>25%</th>\n      <th>50%</th>\n      <th>75%</th>\n      <th>max</th>\n      <th>count</th>\n      <th>mean</th>\n      <th>std</th>\n      <th>min</th>\n      <th>25%</th>\n      <th>50%</th>\n      <th>75%</th>\n      <th>max</th>\n    </tr>\n    <tr>\n      <th>key1</th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></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>3.0</td>\n      <td>0.680727</td>\n      <td>0.686479</td>\n      <td>0.144044</td>\n      <td>0.293953</td>\n      <td>0.443863</td>\n      <td>0.949068</td>\n      <td>1.454274</td>\n      <td>3.0</td>\n      <td>0.324553</td>\n      <td>1.174114</td>\n      <td>-0.854096</td>\n      <td>-0.260211</td>\n      <td>0.333674</td>\n      <td>0.913877</td>\n      <td>1.494079</td>\n    </tr>\n    <tr>\n      <th>b</th>\n      <td>2.0</td>\n      <td>0.441356</td>\n      <td>0.452098</td>\n      <td>0.121675</td>\n      <td>0.281516</td>\n      <td>0.441356</td>\n      <td>0.601197</td>\n      <td>0.761038</td>\n      <td>2.0</td>\n      <td>0.053955</td>\n      <td>0.366441</td>\n      <td>-0.205158</td>\n      <td>-0.075602</td>\n      <td>0.053955</td>\n      <td>0.183511</td>\n      <td>0.313068</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": "     data1                                                              \\\n     count      mean       std       min       25%       50%       75%   \nkey1                                                                     \na      3.0  0.680727  0.686479  0.144044  0.293953  0.443863  0.949068   \nb      2.0  0.441356  0.452098  0.121675  0.281516  0.441356  0.601197   \n\n               data2                                                    \\\n           max count      mean       std       min       25%       50%   \nkey1                                                                     \na     1.454274   3.0  0.324553  1.174114 -0.854096 -0.260211  0.333674   \nb     0.761038   2.0  0.053955  0.366441 -0.205158 -0.075602  0.053955   \n\n                          \n           75%       max  \nkey1                      \na     0.913877  1.494079  \nb     0.183511  0.313068  "
     },
     "execution_count": 103,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "### Column-wise and multiple function application\n",
    "\n",
    "It is common to need to use a different aggregation method for each column.\n",
    "This is demonstrated using an example."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>total_bill</th>\n      <th>tip</th>\n      <th>smoker</th>\n      <th>day</th>\n      <th>time</th>\n      <th>size</th>\n      <th>tip_pct</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>16.99</td>\n      <td>1.01</td>\n      <td>No</td>\n      <td>Sun</td>\n      <td>Dinner</td>\n      <td>2</td>\n      <td>0.059447</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>10.34</td>\n      <td>1.66</td>\n      <td>No</td>\n      <td>Sun</td>\n      <td>Dinner</td>\n      <td>3</td>\n      <td>0.160542</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>21.01</td>\n      <td>3.50</td>\n      <td>No</td>\n      <td>Sun</td>\n      <td>Dinner</td>\n      <td>3</td>\n      <td>0.166587</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>23.68</td>\n      <td>3.31</td>\n      <td>No</td>\n      <td>Sun</td>\n      <td>Dinner</td>\n      <td>2</td>\n      <td>0.139780</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>24.59</td>\n      <td>3.61</td>\n      <td>No</td>\n      <td>Sun</td>\n      <td>Dinner</td>\n      <td>4</td>\n      <td>0.146808</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": "   total_bill   tip smoker  day    time  size   tip_pct\n0       16.99  1.01     No  Sun  Dinner     2  0.059447\n1       10.34  1.66     No  Sun  Dinner     3  0.160542\n2       21.01  3.50     No  Sun  Dinner     3  0.166587\n3       23.68  3.31     No  Sun  Dinner     2  0.139780\n4       24.59  3.61     No  Sun  Dinner     4  0.146808"
     },
     "execution_count": 104,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Read in data on tipping at restaurants.\n",
    "tips = pd.read_csv('assets/examples/tips.csv')\n",
    "\n",
    "# Calculate the percent of the bill the tip covered.\n",
    "tips['tip_pct'] = tips['tip'] / tips['total_bill']\n",
    "tips.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": "day   smoker\nFri   No        0.151650\n      Yes       0.174783\nSat   No        0.158048\n      Yes       0.147906\nSun   No        0.160113\n      Yes       0.187250\nThur  No        0.160298\n      Yes       0.163863\nName: tip_pct, dtype: float64"
     },
     "execution_count": 105,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped = tips.groupby(['day', 'smoker'])\n",
    "grouped_pct = grouped['tip_pct']\n",
    "\n",
    "# The average tipping percentage per day, split into smokers and non-smokers.\n",
    "grouped_pct.agg('mean')"
   ]
  },
  {
   "cell_type": "markdown",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "The `agg` method can be passed a list of functions or function names to apply to the grouped data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\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>mean</th>\n      <th>std</th>\n      <th>peak_to_peak</th>\n    </tr>\n    <tr>\n      <th>day</th>\n      <th>smoker</th>\n      <th></th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Fri</th>\n      <th>No</th>\n      <td>0.151650</td>\n      <td>0.028123</td>\n      <td>0.067349</td>\n    </tr>\n    <tr>\n      <th>Yes</th>\n      <td>0.174783</td>\n      <td>0.051293</td>\n      <td>0.159925</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Sat</th>\n      <th>No</th>\n      <td>0.158048</td>\n      <td>0.039767</td>\n      <td>0.235193</td>\n    </tr>\n    <tr>\n      <th>Yes</th>\n      <td>0.147906</td>\n      <td>0.061375</td>\n      <td>0.290095</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Sun</th>\n      <th>No</th>\n      <td>0.160113</td>\n      <td>0.042347</td>\n      <td>0.193226</td>\n    </tr>\n    <tr>\n      <th>Yes</th>\n      <td>0.187250</td>\n      <td>0.154134</td>\n      <td>0.644685</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Thur</th>\n      <th>No</th>\n      <td>0.160298</td>\n      <td>0.038774</td>\n      <td>0.193350</td>\n    </tr>\n    <tr>\n      <th>Yes</th>\n      <td>0.163863</td>\n      <td>0.039389</td>\n      <td>0.151240</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": "                 mean       std  peak_to_peak\nday  smoker                                  \nFri  No      0.151650  0.028123      0.067349\n     Yes     0.174783  0.051293      0.159925\nSat  No      0.158048  0.039767      0.235193\n     Yes     0.147906  0.061375      0.290095\nSun  No      0.160113  0.042347      0.193226\n     Yes     0.187250  0.154134      0.644685\nThur No      0.160298  0.038774      0.193350\n     Yes     0.163863  0.039389      0.151240"
     },
     "execution_count": 106,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped_pct.agg(['mean', 'std', peak_to_peak])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\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>foo</th>\n      <th>bar</th>\n    </tr>\n    <tr>\n      <th>day</th>\n      <th>smoker</th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Fri</th>\n      <th>No</th>\n      <td>0.151650</td>\n      <td>0.028123</td>\n    </tr>\n    <tr>\n      <th>Yes</th>\n      <td>0.174783</td>\n      <td>0.051293</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Sat</th>\n      <th>No</th>\n      <td>0.158048</td>\n      <td>0.039767</td>\n    </tr>\n    <tr>\n      <th>Yes</th>\n      <td>0.147906</td>\n      <td>0.061375</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Sun</th>\n      <th>No</th>\n      <td>0.160113</td>\n      <td>0.042347</td>\n    </tr>\n    <tr>\n      <th>Yes</th>\n      <td>0.187250</td>\n      <td>0.154134</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Thur</th>\n      <th>No</th>\n      <td>0.160298</td>\n      <td>0.038774</td>\n    </tr>\n    <tr>\n      <th>Yes</th>\n      <td>0.163863</td>\n      <td>0.039389</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": "                  foo       bar\nday  smoker                    \nFri  No      0.151650  0.028123\n     Yes     0.174783  0.051293\nSat  No      0.158048  0.039767\n     Yes     0.147906  0.061375\nSun  No      0.160113  0.042347\n     Yes     0.187250  0.154134\nThur No      0.160298  0.038774\n     Yes     0.163863  0.039389"
     },
     "execution_count": 107,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Passing 2-tuples provides the name of the new column and the aggregation method.\n",
    "grouped_pct.agg([('foo', 'mean'), ('bar', np.std)])"
   ]
  },
  {
   "cell_type": "markdown",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "The above example was using a grouped Series.\n",
    "With a DataFrame, a list of functions can be applied to all columns, or specific functions can be applied to specified columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead tr th {\n        text-align: left;\n    }\n\n    .dataframe thead tr:last-of-type th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr>\n      <th></th>\n      <th></th>\n      <th colspan=\"3\" halign=\"left\">tip_pct</th>\n      <th colspan=\"3\" halign=\"left\">total_bill</th>\n    </tr>\n    <tr>\n      <th></th>\n      <th></th>\n      <th>count</th>\n      <th>mean</th>\n      <th>max</th>\n      <th>count</th>\n      <th>mean</th>\n      <th>max</th>\n    </tr>\n    <tr>\n      <th>day</th>\n      <th>smoker</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 rowspan=\"2\" valign=\"top\">Fri</th>\n      <th>No</th>\n      <td>4</td>\n      <td>0.151650</td>\n      <td>0.187735</td>\n      <td>4</td>\n      <td>18.420000</td>\n      <td>22.75</td>\n    </tr>\n    <tr>\n      <th>Yes</th>\n      <td>15</td>\n      <td>0.174783</td>\n      <td>0.263480</td>\n      <td>15</td>\n      <td>16.813333</td>\n      <td>40.17</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Sat</th>\n      <th>No</th>\n      <td>45</td>\n      <td>0.158048</td>\n      <td>0.291990</td>\n      <td>45</td>\n      <td>19.661778</td>\n      <td>48.33</td>\n    </tr>\n    <tr>\n      <th>Yes</th>\n      <td>42</td>\n      <td>0.147906</td>\n      <td>0.325733</td>\n      <td>42</td>\n      <td>21.276667</td>\n      <td>50.81</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Sun</th>\n      <th>No</th>\n      <td>57</td>\n      <td>0.160113</td>\n      <td>0.252672</td>\n      <td>57</td>\n      <td>20.506667</td>\n      <td>48.17</td>\n    </tr>\n    <tr>\n      <th>Yes</th>\n      <td>19</td>\n      <td>0.187250</td>\n      <td>0.710345</td>\n      <td>19</td>\n      <td>24.120000</td>\n      <td>45.35</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Thur</th>\n      <th>No</th>\n      <td>45</td>\n      <td>0.160298</td>\n      <td>0.266312</td>\n      <td>45</td>\n      <td>17.113111</td>\n      <td>41.19</td>\n    </tr>\n    <tr>\n      <th>Yes</th>\n      <td>17</td>\n      <td>0.163863</td>\n      <td>0.241255</td>\n      <td>17</td>\n      <td>19.190588</td>\n      <td>43.11</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": "            tip_pct                     total_bill                  \n              count      mean       max      count       mean    max\nday  smoker                                                         \nFri  No           4  0.151650  0.187735          4  18.420000  22.75\n     Yes         15  0.174783  0.263480         15  16.813333  40.17\nSat  No          45  0.158048  0.291990         45  19.661778  48.33\n     Yes         42  0.147906  0.325733         42  21.276667  50.81\nSun  No          57  0.160113  0.252672         57  20.506667  48.17\n     Yes         19  0.187250  0.710345         19  24.120000  45.35\nThur No          45  0.160298  0.266312         45  17.113111  41.19\n     Yes         17  0.163863  0.241255         17  19.190588  43.11"
     },
     "execution_count": 108,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "functions = ['count', 'mean', 'max']\n",
    "result = grouped['tip_pct', 'total_bill'].agg(functions)\n",
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead tr th {\n        text-align: left;\n    }\n\n    .dataframe thead tr:last-of-type th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr>\n      <th></th>\n      <th></th>\n      <th colspan=\"4\" halign=\"left\">tip_pct</th>\n      <th>size</th>\n    </tr>\n    <tr>\n      <th></th>\n      <th></th>\n      <th>min</th>\n      <th>max</th>\n      <th>mean</th>\n      <th>std</th>\n      <th>sum</th>\n    </tr>\n    <tr>\n      <th>day</th>\n      <th>smoker</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 rowspan=\"2\" valign=\"top\">Fri</th>\n      <th>No</th>\n      <td>0.120385</td>\n      <td>0.187735</td>\n      <td>0.151650</td>\n      <td>0.028123</td>\n      <td>9</td>\n    </tr>\n    <tr>\n      <th>Yes</th>\n      <td>0.103555</td>\n      <td>0.263480</td>\n      <td>0.174783</td>\n      <td>0.051293</td>\n      <td>31</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Sat</th>\n      <th>No</th>\n      <td>0.056797</td>\n      <td>0.291990</td>\n      <td>0.158048</td>\n      <td>0.039767</td>\n      <td>115</td>\n    </tr>\n    <tr>\n      <th>Yes</th>\n      <td>0.035638</td>\n      <td>0.325733</td>\n      <td>0.147906</td>\n      <td>0.061375</td>\n      <td>104</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Sun</th>\n      <th>No</th>\n      <td>0.059447</td>\n      <td>0.252672</td>\n      <td>0.160113</td>\n      <td>0.042347</td>\n      <td>167</td>\n    </tr>\n    <tr>\n      <th>Yes</th>\n      <td>0.065660</td>\n      <td>0.710345</td>\n      <td>0.187250</td>\n      <td>0.154134</td>\n      <td>49</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Thur</th>\n      <th>No</th>\n      <td>0.072961</td>\n      <td>0.266312</td>\n      <td>0.160298</td>\n      <td>0.038774</td>\n      <td>112</td>\n    </tr>\n    <tr>\n      <th>Yes</th>\n      <td>0.090014</td>\n      <td>0.241255</td>\n      <td>0.163863</td>\n      <td>0.039389</td>\n      <td>40</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": "              tip_pct                               size\n                  min       max      mean       std  sum\nday  smoker                                             \nFri  No      0.120385  0.187735  0.151650  0.028123    9\n     Yes     0.103555  0.263480  0.174783  0.051293   31\nSat  No      0.056797  0.291990  0.158048  0.039767  115\n     Yes     0.035638  0.325733  0.147906  0.061375  104\nSun  No      0.059447  0.252672  0.160113  0.042347  167\n     Yes     0.065660  0.710345  0.187250  0.154134   49\nThur No      0.072961  0.266312  0.160298  0.038774  112\n     Yes     0.090014  0.241255  0.163863  0.039389   40"
     },
     "execution_count": 109,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# A mapping of column names to functions.\n",
    "fxn_mapping = {\n",
    "    'tip_pct': ['min', 'max', 'mean', 'std'],\n",
    "    'size': 'sum'\n",
    "}\n",
    "grouped.agg(fxn_mapping)"
   ]
  },
  {
   "cell_type": "markdown",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "### Return aggregated data without row indexes\n",
    "\n",
    "So far, the returned aggregations have the group key combinations for an index, often hierarchical.\n",
    "This can be disabled by passing `as_index=False`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\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>total_bill</th>\n      <th>tip</th>\n      <th>size</th>\n      <th>tip_pct</th>\n    </tr>\n    <tr>\n      <th>day</th>\n      <th>smoker</th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Fri</th>\n      <th>No</th>\n      <td>18.420000</td>\n      <td>2.812500</td>\n      <td>2.250000</td>\n      <td>0.151650</td>\n    </tr>\n    <tr>\n      <th>Yes</th>\n      <td>16.813333</td>\n      <td>2.714000</td>\n      <td>2.066667</td>\n      <td>0.174783</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Sat</th>\n      <th>No</th>\n      <td>19.661778</td>\n      <td>3.102889</td>\n      <td>2.555556</td>\n      <td>0.158048</td>\n    </tr>\n    <tr>\n      <th>Yes</th>\n      <td>21.276667</td>\n      <td>2.875476</td>\n      <td>2.476190</td>\n      <td>0.147906</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Sun</th>\n      <th>No</th>\n      <td>20.506667</td>\n      <td>3.167895</td>\n      <td>2.929825</td>\n      <td>0.160113</td>\n    </tr>\n    <tr>\n      <th>Yes</th>\n      <td>24.120000</td>\n      <td>3.516842</td>\n      <td>2.578947</td>\n      <td>0.187250</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Thur</th>\n      <th>No</th>\n      <td>17.113111</td>\n      <td>2.673778</td>\n      <td>2.488889</td>\n      <td>0.160298</td>\n    </tr>\n    <tr>\n      <th>Yes</th>\n      <td>19.190588</td>\n      <td>3.030000</td>\n      <td>2.352941</td>\n      <td>0.163863</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": "             total_bill       tip      size   tip_pct\nday  smoker                                          \nFri  No       18.420000  2.812500  2.250000  0.151650\n     Yes      16.813333  2.714000  2.066667  0.174783\nSat  No       19.661778  3.102889  2.555556  0.158048\n     Yes      21.276667  2.875476  2.476190  0.147906\nSun  No       20.506667  3.167895  2.929825  0.160113\n     Yes      24.120000  3.516842  2.578947  0.187250\nThur No       17.113111  2.673778  2.488889  0.160298\n     Yes      19.190588  3.030000  2.352941  0.163863"
     },
     "execution_count": 110,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# with index\n",
    "tips.groupby(['day', 'smoker']).mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>day</th>\n      <th>smoker</th>\n      <th>total_bill</th>\n      <th>tip</th>\n      <th>size</th>\n      <th>tip_pct</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>Fri</td>\n      <td>No</td>\n      <td>18.420000</td>\n      <td>2.812500</td>\n      <td>2.250000</td>\n      <td>0.151650</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>Fri</td>\n      <td>Yes</td>\n      <td>16.813333</td>\n      <td>2.714000</td>\n      <td>2.066667</td>\n      <td>0.174783</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>Sat</td>\n      <td>No</td>\n      <td>19.661778</td>\n      <td>3.102889</td>\n      <td>2.555556</td>\n      <td>0.158048</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>Sat</td>\n      <td>Yes</td>\n      <td>21.276667</td>\n      <td>2.875476</td>\n      <td>2.476190</td>\n      <td>0.147906</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>Sun</td>\n      <td>No</td>\n      <td>20.506667</td>\n      <td>3.167895</td>\n      <td>2.929825</td>\n      <td>0.160113</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>Sun</td>\n      <td>Yes</td>\n      <td>24.120000</td>\n      <td>3.516842</td>\n      <td>2.578947</td>\n      <td>0.187250</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>Thur</td>\n      <td>No</td>\n      <td>17.113111</td>\n      <td>2.673778</td>\n      <td>2.488889</td>\n      <td>0.160298</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>Thur</td>\n      <td>Yes</td>\n      <td>19.190588</td>\n      <td>3.030000</td>\n      <td>2.352941</td>\n      <td>0.163863</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": "    day smoker  total_bill       tip      size   tip_pct\n0   Fri     No   18.420000  2.812500  2.250000  0.151650\n1   Fri    Yes   16.813333  2.714000  2.066667  0.174783\n2   Sat     No   19.661778  3.102889  2.555556  0.158048\n3   Sat    Yes   21.276667  2.875476  2.476190  0.147906\n4   Sun     No   20.506667  3.167895  2.929825  0.160113\n5   Sun    Yes   24.120000  3.516842  2.578947  0.187250\n6  Thur     No   17.113111  2.673778  2.488889  0.160298\n7  Thur    Yes   19.190588  3.030000  2.352941  0.163863"
     },
     "execution_count": 111,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# without index\n",
    "tips.groupby(['day', 'smoker'], as_index=False).mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>day</th>\n      <th>smoker</th>\n      <th>total_bill</th>\n      <th>tip</th>\n      <th>size</th>\n      <th>tip_pct</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>Fri</td>\n      <td>No</td>\n      <td>18.420000</td>\n      <td>2.812500</td>\n      <td>2.250000</td>\n      <td>0.151650</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>Fri</td>\n      <td>Yes</td>\n      <td>16.813333</td>\n      <td>2.714000</td>\n      <td>2.066667</td>\n      <td>0.174783</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>Sat</td>\n      <td>No</td>\n      <td>19.661778</td>\n      <td>3.102889</td>\n      <td>2.555556</td>\n      <td>0.158048</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>Sat</td>\n      <td>Yes</td>\n      <td>21.276667</td>\n      <td>2.875476</td>\n      <td>2.476190</td>\n      <td>0.147906</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>Sun</td>\n      <td>No</td>\n      <td>20.506667</td>\n      <td>3.167895</td>\n      <td>2.929825</td>\n      <td>0.160113</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>Sun</td>\n      <td>Yes</td>\n      <td>24.120000</td>\n      <td>3.516842</td>\n      <td>2.578947</td>\n      <td>0.187250</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>Thur</td>\n      <td>No</td>\n      <td>17.113111</td>\n      <td>2.673778</td>\n      <td>2.488889</td>\n      <td>0.160298</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>Thur</td>\n      <td>Yes</td>\n      <td>19.190588</td>\n      <td>3.030000</td>\n      <td>2.352941</td>\n      <td>0.163863</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": "    day smoker  total_bill       tip      size   tip_pct\n0   Fri     No   18.420000  2.812500  2.250000  0.151650\n1   Fri    Yes   16.813333  2.714000  2.066667  0.174783\n2   Sat     No   19.661778  3.102889  2.555556  0.158048\n3   Sat    Yes   21.276667  2.875476  2.476190  0.147906\n4   Sun     No   20.506667  3.167895  2.929825  0.160113\n5   Sun    Yes   24.120000  3.516842  2.578947  0.187250\n6  Thur     No   17.113111  2.673778  2.488889  0.160298\n7  Thur    Yes   19.190588  3.030000  2.352941  0.163863"
     },
     "execution_count": 112,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# also without index\n",
    "tips.groupby(['day', 'smoker']).mean().reset_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## 10.3 Apply: general split-apply-combine\n",
    "\n",
    "Apply splits the object being manipulated into pieces, invokes a provided function on each piece, and then concatenates the pieces together.\n",
    "Here is an example using the tipping data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>total_bill</th>\n      <th>tip</th>\n      <th>smoker</th>\n      <th>day</th>\n      <th>time</th>\n      <th>size</th>\n      <th>tip_pct</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>109</th>\n      <td>14.31</td>\n      <td>4.00</td>\n      <td>Yes</td>\n      <td>Sat</td>\n      <td>Dinner</td>\n      <td>2</td>\n      <td>0.279525</td>\n    </tr>\n    <tr>\n      <th>183</th>\n      <td>23.17</td>\n      <td>6.50</td>\n      <td>Yes</td>\n      <td>Sun</td>\n      <td>Dinner</td>\n      <td>4</td>\n      <td>0.280535</td>\n    </tr>\n    <tr>\n      <th>232</th>\n      <td>11.61</td>\n      <td>3.39</td>\n      <td>No</td>\n      <td>Sat</td>\n      <td>Dinner</td>\n      <td>2</td>\n      <td>0.291990</td>\n    </tr>\n    <tr>\n      <th>67</th>\n      <td>3.07</td>\n      <td>1.00</td>\n      <td>Yes</td>\n      <td>Sat</td>\n      <td>Dinner</td>\n      <td>1</td>\n      <td>0.325733</td>\n    </tr>\n    <tr>\n      <th>178</th>\n      <td>9.60</td>\n      <td>4.00</td>\n      <td>Yes</td>\n      <td>Sun</td>\n      <td>Dinner</td>\n      <td>2</td>\n      <td>0.416667</td>\n    </tr>\n    <tr>\n      <th>172</th>\n      <td>7.25</td>\n      <td>5.15</td>\n      <td>Yes</td>\n      <td>Sun</td>\n      <td>Dinner</td>\n      <td>2</td>\n      <td>0.710345</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": "     total_bill   tip smoker  day    time  size   tip_pct\n109       14.31  4.00    Yes  Sat  Dinner     2  0.279525\n183       23.17  6.50    Yes  Sun  Dinner     4  0.280535\n232       11.61  3.39     No  Sat  Dinner     2  0.291990\n67         3.07  1.00    Yes  Sat  Dinner     1  0.325733\n178        9.60  4.00    Yes  Sun  Dinner     2  0.416667\n172        7.25  5.15    Yes  Sun  Dinner     2  0.710345"
     },
     "execution_count": 113,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def top(df, n=5, column='tip_pct'):\n",
    "    '''\n",
    "    Return rows with the top `n` values in `column`.\n",
    "    '''\n",
    "    return df.sort_values(by=column)[-n:]\n",
    "\n",
    "top(tips, n=6)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\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>total_bill</th>\n      <th>tip</th>\n      <th>smoker</th>\n      <th>day</th>\n      <th>time</th>\n      <th>size</th>\n      <th>tip_pct</th>\n    </tr>\n    <tr>\n      <th>smoker</th>\n      <th></th>\n      <th></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 rowspan=\"3\" valign=\"top\">No</th>\n      <th>51</th>\n      <td>10.29</td>\n      <td>2.60</td>\n      <td>No</td>\n      <td>Sun</td>\n      <td>Dinner</td>\n      <td>2</td>\n      <td>0.252672</td>\n    </tr>\n    <tr>\n      <th>149</th>\n      <td>7.51</td>\n      <td>2.00</td>\n      <td>No</td>\n      <td>Thur</td>\n      <td>Lunch</td>\n      <td>2</td>\n      <td>0.266312</td>\n    </tr>\n    <tr>\n      <th>232</th>\n      <td>11.61</td>\n      <td>3.39</td>\n      <td>No</td>\n      <td>Sat</td>\n      <td>Dinner</td>\n      <td>2</td>\n      <td>0.291990</td>\n    </tr>\n    <tr>\n      <th rowspan=\"3\" valign=\"top\">Yes</th>\n      <th>67</th>\n      <td>3.07</td>\n      <td>1.00</td>\n      <td>Yes</td>\n      <td>Sat</td>\n      <td>Dinner</td>\n      <td>1</td>\n      <td>0.325733</td>\n    </tr>\n    <tr>\n      <th>178</th>\n      <td>9.60</td>\n      <td>4.00</td>\n      <td>Yes</td>\n      <td>Sun</td>\n      <td>Dinner</td>\n      <td>2</td>\n      <td>0.416667</td>\n    </tr>\n    <tr>\n      <th>172</th>\n      <td>7.25</td>\n      <td>5.15</td>\n      <td>Yes</td>\n      <td>Sun</td>\n      <td>Dinner</td>\n      <td>2</td>\n      <td>0.710345</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": "            total_bill   tip smoker   day    time  size   tip_pct\nsmoker                                                           \nNo     51        10.29  2.60     No   Sun  Dinner     2  0.252672\n       149        7.51  2.00     No  Thur   Lunch     2  0.266312\n       232       11.61  3.39     No   Sat  Dinner     2  0.291990\nYes    67         3.07  1.00    Yes   Sat  Dinner     1  0.325733\n       178        9.60  4.00    Yes   Sun  Dinner     2  0.416667\n       172        7.25  5.15    Yes   Sun  Dinner     2  0.710345"
     },
     "execution_count": 114,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tips.groupby('smoker').apply(top, n=3)"
   ]
  },
  {
   "cell_type": "markdown",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "### Suppressing the group keys\n",
    "\n",
    "To prevent the resulting DataFrame from having the grouping keys for row indexes, use `group_keys=False` in `groupby()`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>total_bill</th>\n      <th>tip</th>\n      <th>smoker</th>\n      <th>day</th>\n      <th>time</th>\n      <th>size</th>\n      <th>tip_pct</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>51</th>\n      <td>10.29</td>\n      <td>2.60</td>\n      <td>No</td>\n      <td>Sun</td>\n      <td>Dinner</td>\n      <td>2</td>\n      <td>0.252672</td>\n    </tr>\n    <tr>\n      <th>149</th>\n      <td>7.51</td>\n      <td>2.00</td>\n      <td>No</td>\n      <td>Thur</td>\n      <td>Lunch</td>\n      <td>2</td>\n      <td>0.266312</td>\n    </tr>\n    <tr>\n      <th>232</th>\n      <td>11.61</td>\n      <td>3.39</td>\n      <td>No</td>\n      <td>Sat</td>\n      <td>Dinner</td>\n      <td>2</td>\n      <td>0.291990</td>\n    </tr>\n    <tr>\n      <th>67</th>\n      <td>3.07</td>\n      <td>1.00</td>\n      <td>Yes</td>\n      <td>Sat</td>\n      <td>Dinner</td>\n      <td>1</td>\n      <td>0.325733</td>\n    </tr>\n    <tr>\n      <th>178</th>\n      <td>9.60</td>\n      <td>4.00</td>\n      <td>Yes</td>\n      <td>Sun</td>\n      <td>Dinner</td>\n      <td>2</td>\n      <td>0.416667</td>\n    </tr>\n    <tr>\n      <th>172</th>\n      <td>7.25</td>\n      <td>5.15</td>\n      <td>Yes</td>\n      <td>Sun</td>\n      <td>Dinner</td>\n      <td>2</td>\n      <td>0.710345</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": "     total_bill   tip smoker   day    time  size   tip_pct\n51        10.29  2.60     No   Sun  Dinner     2  0.252672\n149        7.51  2.00     No  Thur   Lunch     2  0.266312\n232       11.61  3.39     No   Sat  Dinner     2  0.291990\n67         3.07  1.00    Yes   Sat  Dinner     1  0.325733\n178        9.60  4.00    Yes   Sun  Dinner     2  0.416667\n172        7.25  5.15    Yes   Sun  Dinner     2  0.710345"
     },
     "execution_count": 115,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tips.groupby('smoker', group_keys=False).apply(top, n=3)"
   ]
  },
  {
   "cell_type": "markdown",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "### Quantile and bucket analysis\n",
    "\n",
    "pandas includes `cut()` and `qcut()` for dividing data into quantiles.\n",
    "This often is useful in conjunction with `groupby()`.\n",
    "The categorical object returned by `cut()` can be passed directly to `groupby()` to use to separate the data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": "0    (-1.492, 0.0624]\n1    (-3.052, -1.492]\n2     (0.0624, 1.617]\n3    (-1.492, 0.0624]\n4    (-1.492, 0.0624]\nName: data1, dtype: category\nCategories (4, interval[float64]): [(-3.052, -1.492] < (-1.492, 0.0624] < (0.0624, 1.617] < (1.617, 3.171]]"
     },
     "execution_count": 116,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frame = pd.DataFrame({'data1': np.random.randn(1000),\n",
    "                      'data2': np.random.randn(1000)})\n",
    "quartiles = pd.cut(frame.data1, 4)\n",
    "quartiles.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>min</th>\n      <th>max</th>\n      <th>count</th>\n      <th>mean</th>\n    </tr>\n    <tr>\n      <th>data1</th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>(-3.052, -1.492]</th>\n      <td>-1.730276</td>\n      <td>2.153120</td>\n      <td>68.0</td>\n      <td>0.097696</td>\n    </tr>\n    <tr>\n      <th>(-1.492, 0.0624]</th>\n      <td>-2.777359</td>\n      <td>2.680571</td>\n      <td>482.0</td>\n      <td>0.056230</td>\n    </tr>\n    <tr>\n      <th>(0.0624, 1.617]</th>\n      <td>-3.116857</td>\n      <td>2.929096</td>\n      <td>398.0</td>\n      <td>-0.046616</td>\n    </tr>\n    <tr>\n      <th>(1.617, 3.171]</th>\n      <td>-2.158069</td>\n      <td>2.042072</td>\n      <td>52.0</td>\n      <td>-0.156563</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": "                       min       max  count      mean\ndata1                                                \n(-3.052, -1.492] -1.730276  2.153120   68.0  0.097696\n(-1.492, 0.0624] -2.777359  2.680571  482.0  0.056230\n(0.0624, 1.617]  -3.116857  2.929096  398.0 -0.046616\n(1.617, 3.171]   -2.158069  2.042072   52.0 -0.156563"
     },
     "execution_count": 117,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def get_stats(group):\n",
    "    return {\n",
    "        'min': group.min(),\n",
    "        'max': group.max(),\n",
    "        'count': group.count(),\n",
    "        'mean': group.mean()\n",
    "    }\n",
    "\n",
    "frame.data2.groupby(quartiles).apply(get_stats).unstack()"
   ]
  },
  {
   "cell_type": "markdown",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Use `qcut()` to get equally-sized buckets."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>min</th>\n      <th>max</th>\n      <th>count</th>\n      <th>mean</th>\n    </tr>\n    <tr>\n      <th>data1</th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>-2.534554</td>\n      <td>2.642936</td>\n      <td>100.0</td>\n      <td>-0.013313</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>-1.641703</td>\n      <td>2.232016</td>\n      <td>100.0</td>\n      <td>0.059095</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>-2.777359</td>\n      <td>2.620574</td>\n      <td>100.0</td>\n      <td>0.193168</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>-1.830029</td>\n      <td>2.488442</td>\n      <td>100.0</td>\n      <td>-0.103425</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>-2.123890</td>\n      <td>2.526368</td>\n      <td>100.0</td>\n      <td>0.169418</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>-2.121176</td>\n      <td>2.680571</td>\n      <td>100.0</td>\n      <td>0.044411</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>-3.116857</td>\n      <td>2.929096</td>\n      <td>100.0</td>\n      <td>0.083797</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>-2.994613</td>\n      <td>2.540232</td>\n      <td>100.0</td>\n      <td>-0.305252</td>\n    </tr>\n    <tr>\n      <th>8</th>\n      <td>-1.980566</td>\n      <td>2.190898</td>\n      <td>100.0</td>\n      <td>-0.058111</td>\n    </tr>\n    <tr>\n      <th>9</th>\n      <td>-2.802203</td>\n      <td>2.198296</td>\n      <td>100.0</td>\n      <td>0.000731</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": "            min       max  count      mean\ndata1                                     \n0     -2.534554  2.642936  100.0 -0.013313\n1     -1.641703  2.232016  100.0  0.059095\n2     -2.777359  2.620574  100.0  0.193168\n3     -1.830029  2.488442  100.0 -0.103425\n4     -2.123890  2.526368  100.0  0.169418\n5     -2.121176  2.680571  100.0  0.044411\n6     -3.116857  2.929096  100.0  0.083797\n7     -2.994613  2.540232  100.0 -0.305252\n8     -1.980566  2.190898  100.0 -0.058111\n9     -2.802203  2.198296  100.0  0.000731"
     },
     "execution_count": 118,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouping = pd.qcut(frame.data1, 10, labels=False)\n",
    "frame.data2.groupby(grouping).apply(get_stats).unstack()"
   ]
  },
  {
   "cell_type": "markdown",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## 10.4 Pivot tables and cross-tabulation\n",
    "\n",
    "Pivot tables are made with pandas by using `groupby()` and some reshaping operations.\n",
    "There is a `pivot_table()` method for DataFrames and a top-level `pd.pivot_table()` function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\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>size</th>\n      <th>tip</th>\n      <th>tip_pct</th>\n      <th>total_bill</th>\n    </tr>\n    <tr>\n      <th>day</th>\n      <th>smoker</th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Fri</th>\n      <th>No</th>\n      <td>2.250000</td>\n      <td>2.812500</td>\n      <td>0.151650</td>\n      <td>18.420000</td>\n    </tr>\n    <tr>\n      <th>Yes</th>\n      <td>2.066667</td>\n      <td>2.714000</td>\n      <td>0.174783</td>\n      <td>16.813333</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Sat</th>\n      <th>No</th>\n      <td>2.555556</td>\n      <td>3.102889</td>\n      <td>0.158048</td>\n      <td>19.661778</td>\n    </tr>\n    <tr>\n      <th>Yes</th>\n      <td>2.476190</td>\n      <td>2.875476</td>\n      <td>0.147906</td>\n      <td>21.276667</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Sun</th>\n      <th>No</th>\n      <td>2.929825</td>\n      <td>3.167895</td>\n      <td>0.160113</td>\n      <td>20.506667</td>\n    </tr>\n    <tr>\n      <th>Yes</th>\n      <td>2.578947</td>\n      <td>3.516842</td>\n      <td>0.187250</td>\n      <td>24.120000</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Thur</th>\n      <th>No</th>\n      <td>2.488889</td>\n      <td>2.673778</td>\n      <td>0.160298</td>\n      <td>17.113111</td>\n    </tr>\n    <tr>\n      <th>Yes</th>\n      <td>2.352941</td>\n      <td>3.030000</td>\n      <td>0.163863</td>\n      <td>19.190588</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": "                 size       tip   tip_pct  total_bill\nday  smoker                                          \nFri  No      2.250000  2.812500  0.151650   18.420000\n     Yes     2.066667  2.714000  0.174783   16.813333\nSat  No      2.555556  3.102889  0.158048   19.661778\n     Yes     2.476190  2.875476  0.147906   21.276667\nSun  No      2.929825  3.167895  0.160113   20.506667\n     Yes     2.578947  3.516842  0.187250   24.120000\nThur No      2.488889  2.673778  0.160298   17.113111\n     Yes     2.352941  3.030000  0.163863   19.190588"
     },
     "execution_count": 119,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tips.pivot_table(index=['day', 'smoker'])"
   ]
  },
  {
   "cell_type": "markdown",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "The following example only aggragates `'tip_pct'` and `'size'` and additionally groups by `'time'`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead tr th {\n        text-align: left;\n    }\n\n    .dataframe thead tr:last-of-type th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr>\n      <th></th>\n      <th></th>\n      <th colspan=\"2\" halign=\"left\">size</th>\n      <th colspan=\"2\" halign=\"left\">tip_pct</th>\n    </tr>\n    <tr>\n      <th></th>\n      <th>smoker</th>\n      <th>No</th>\n      <th>Yes</th>\n      <th>No</th>\n      <th>Yes</th>\n    </tr>\n    <tr>\n      <th>time</th>\n      <th>day</th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th rowspan=\"4\" valign=\"top\">Dinner</th>\n      <th>Fri</th>\n      <td>2.000000</td>\n      <td>2.222222</td>\n      <td>0.139622</td>\n      <td>0.165347</td>\n    </tr>\n    <tr>\n      <th>Sat</th>\n      <td>2.555556</td>\n      <td>2.476190</td>\n      <td>0.158048</td>\n      <td>0.147906</td>\n    </tr>\n    <tr>\n      <th>Sun</th>\n      <td>2.929825</td>\n      <td>2.578947</td>\n      <td>0.160113</td>\n      <td>0.187250</td>\n    </tr>\n    <tr>\n      <th>Thur</th>\n      <td>2.000000</td>\n      <td>NaN</td>\n      <td>0.159744</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Lunch</th>\n      <th>Fri</th>\n      <td>3.000000</td>\n      <td>1.833333</td>\n      <td>0.187735</td>\n      <td>0.188937</td>\n    </tr>\n    <tr>\n      <th>Thur</th>\n      <td>2.500000</td>\n      <td>2.352941</td>\n      <td>0.160311</td>\n      <td>0.163863</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": "                 size             tip_pct          \nsmoker             No       Yes        No       Yes\ntime   day                                         \nDinner Fri   2.000000  2.222222  0.139622  0.165347\n       Sat   2.555556  2.476190  0.158048  0.147906\n       Sun   2.929825  2.578947  0.160113  0.187250\n       Thur  2.000000       NaN  0.159744       NaN\nLunch  Fri   3.000000  1.833333  0.187735  0.188937\n       Thur  2.500000  2.352941  0.160311  0.163863"
     },
     "execution_count": 120,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tips.pivot_table(['tip_pct', 'size'], index=['time', 'day'], columns='smoker')"
   ]
  },
  {
   "cell_type": "markdown",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Setting `margins=True` computes partial totals, adding an `'All'` row and column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead tr th {\n        text-align: left;\n    }\n\n    .dataframe thead tr:last-of-type th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr>\n      <th></th>\n      <th></th>\n      <th colspan=\"3\" halign=\"left\">size</th>\n      <th colspan=\"3\" halign=\"left\">tip_pct</th>\n    </tr>\n    <tr>\n      <th></th>\n      <th>smoker</th>\n      <th>No</th>\n      <th>Yes</th>\n      <th>All</th>\n      <th>No</th>\n      <th>Yes</th>\n      <th>All</th>\n    </tr>\n    <tr>\n      <th>time</th>\n      <th>day</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 rowspan=\"4\" valign=\"top\">Dinner</th>\n      <th>Fri</th>\n      <td>2.000000</td>\n      <td>2.222222</td>\n      <td>2.166667</td>\n      <td>0.139622</td>\n      <td>0.165347</td>\n      <td>0.158916</td>\n    </tr>\n    <tr>\n      <th>Sat</th>\n      <td>2.555556</td>\n      <td>2.476190</td>\n      <td>2.517241</td>\n      <td>0.158048</td>\n      <td>0.147906</td>\n      <td>0.153152</td>\n    </tr>\n    <tr>\n      <th>Sun</th>\n      <td>2.929825</td>\n      <td>2.578947</td>\n      <td>2.842105</td>\n      <td>0.160113</td>\n      <td>0.187250</td>\n      <td>0.166897</td>\n    </tr>\n    <tr>\n      <th>Thur</th>\n      <td>2.000000</td>\n      <td>NaN</td>\n      <td>2.000000</td>\n      <td>0.159744</td>\n      <td>NaN</td>\n      <td>0.159744</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Lunch</th>\n      <th>Fri</th>\n      <td>3.000000</td>\n      <td>1.833333</td>\n      <td>2.000000</td>\n      <td>0.187735</td>\n      <td>0.188937</td>\n      <td>0.188765</td>\n    </tr>\n    <tr>\n      <th>Thur</th>\n      <td>2.500000</td>\n      <td>2.352941</td>\n      <td>2.459016</td>\n      <td>0.160311</td>\n      <td>0.163863</td>\n      <td>0.161301</td>\n    </tr>\n    <tr>\n      <th>All</th>\n      <th></th>\n      <td>2.668874</td>\n      <td>2.408602</td>\n      <td>2.569672</td>\n      <td>0.159328</td>\n      <td>0.163196</td>\n      <td>0.160803</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": "                 size                       tip_pct                    \nsmoker             No       Yes       All        No       Yes       All\ntime   day                                                             \nDinner Fri   2.000000  2.222222  2.166667  0.139622  0.165347  0.158916\n       Sat   2.555556  2.476190  2.517241  0.158048  0.147906  0.153152\n       Sun   2.929825  2.578947  2.842105  0.160113  0.187250  0.166897\n       Thur  2.000000       NaN  2.000000  0.159744       NaN  0.159744\nLunch  Fri   3.000000  1.833333  2.000000  0.187735  0.188937  0.188765\n       Thur  2.500000  2.352941  2.459016  0.160311  0.163863  0.161301\nAll          2.668874  2.408602  2.569672  0.159328  0.163196  0.160803"
     },
     "execution_count": 121,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tips.pivot_table(['tip_pct', 'size'],\n",
    "                 index=['time', 'day'], \n",
    "                 columns='smoker', \n",
    "                 margins=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "The default aggregation behaviour is to take the mean.\n",
    "This can be changed by passing a different function to `aggfunc`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>day</th>\n      <th>Fri</th>\n      <th>Sat</th>\n      <th>Sun</th>\n      <th>Thur</th>\n      <th>All</th>\n    </tr>\n    <tr>\n      <th>time</th>\n      <th>smoker</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 rowspan=\"2\" valign=\"top\">Dinner</th>\n      <th>No</th>\n      <td>3.0</td>\n      <td>45.0</td>\n      <td>57.0</td>\n      <td>1.0</td>\n      <td>106.0</td>\n    </tr>\n    <tr>\n      <th>Yes</th>\n      <td>9.0</td>\n      <td>42.0</td>\n      <td>19.0</td>\n      <td>NaN</td>\n      <td>70.0</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Lunch</th>\n      <th>No</th>\n      <td>1.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>44.0</td>\n      <td>45.0</td>\n    </tr>\n    <tr>\n      <th>Yes</th>\n      <td>6.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>17.0</td>\n      <td>23.0</td>\n    </tr>\n    <tr>\n      <th>All</th>\n      <th></th>\n      <td>19.0</td>\n      <td>87.0</td>\n      <td>76.0</td>\n      <td>62.0</td>\n      <td>244.0</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": "day             Fri   Sat   Sun  Thur    All\ntime   smoker                               \nDinner No       3.0  45.0  57.0   1.0  106.0\n       Yes      9.0  42.0  19.0   NaN   70.0\nLunch  No       1.0   NaN   NaN  44.0   45.0\n       Yes      6.0   NaN   NaN  17.0   23.0\nAll            19.0  87.0  76.0  62.0  244.0"
     },
     "execution_count": 122,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tips.pivot_table('tip_pct', \n",
    "                 index=['time', 'smoker'], \n",
    "                 columns='day', \n",
    "                 aggfunc=len, \n",
    "                 margins=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>day</th>\n      <th>Fri</th>\n      <th>Sat</th>\n      <th>Sun</th>\n      <th>Thur</th>\n      <th>All</th>\n    </tr>\n    <tr>\n      <th>time</th>\n      <th>smoker</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 rowspan=\"2\" valign=\"top\">Dinner</th>\n      <th>No</th>\n      <td>3</td>\n      <td>45</td>\n      <td>57</td>\n      <td>1</td>\n      <td>106.0</td>\n    </tr>\n    <tr>\n      <th>Yes</th>\n      <td>9</td>\n      <td>42</td>\n      <td>19</td>\n      <td>0</td>\n      <td>70.0</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Lunch</th>\n      <th>No</th>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>44</td>\n      <td>45.0</td>\n    </tr>\n    <tr>\n      <th>Yes</th>\n      <td>6</td>\n      <td>0</td>\n      <td>0</td>\n      <td>17</td>\n      <td>23.0</td>\n    </tr>\n    <tr>\n      <th>All</th>\n      <th></th>\n      <td>19</td>\n      <td>87</td>\n      <td>76</td>\n      <td>62</td>\n      <td>244.0</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": "day            Fri  Sat  Sun  Thur    All\ntime   smoker                            \nDinner No        3   45   57     1  106.0\n       Yes       9   42   19     0   70.0\nLunch  No        1    0    0    44   45.0\n       Yes       6    0    0    17   23.0\nAll             19   87   76    62  244.0"
     },
     "execution_count": 123,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tips.pivot_table('tip_pct',\n",
    "                 index=['time', 'smoker'], \n",
    "                 columns='day', \n",
    "                 aggfunc=len, \n",
    "                 margins=True, \n",
    "                 fill_value=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "### Cross-tabulations (crosstab)\n",
    "\n",
    "Cross-tabulation is a special case of a pivot table the computes group frequencies."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>Sample</th>\n      <th>Nationality</th>\n      <th>Handedness</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>1</td>\n      <td>USA</td>\n      <td>Right-handed</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>2</td>\n      <td>Japan</td>\n      <td>Left-handed</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>3</td>\n      <td>USA</td>\n      <td>Right-handed</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>4</td>\n      <td>Japan</td>\n      <td>Right-handed</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>5</td>\n      <td>Japan</td>\n      <td>Left-handed</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>6</td>\n      <td>Japan</td>\n      <td>Right-handed</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>7</td>\n      <td>USA</td>\n      <td>Right-handed</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>8</td>\n      <td>USA</td>\n      <td>Left-handed</td>\n    </tr>\n    <tr>\n      <th>8</th>\n      <td>9</td>\n      <td>Japan</td>\n      <td>Right-handed</td>\n    </tr>\n    <tr>\n      <th>9</th>\n      <td>10</td>\n      <td>USA</td>\n      <td>Right-handed</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": "   Sample Nationality    Handedness\n0       1         USA  Right-handed\n1       2       Japan   Left-handed\n2       3         USA  Right-handed\n3       4       Japan  Right-handed\n4       5       Japan   Left-handed\n5       6       Japan  Right-handed\n6       7         USA  Right-handed\n7       8         USA   Left-handed\n8       9       Japan  Right-handed\n9      10         USA  Right-handed"
     },
     "execution_count": 124,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = pd.DataFrame({\n",
    "    'Sample': list(range(1, 11)),\n",
    "    'Nationality': ['USA', 'Japan', 'USA', 'Japan', 'Japan', 'Japan', 'USA', 'USA', 'Japan', 'USA'],\n",
    "    'Handedness': ['Right-handed', 'Left-handed', 'Right-handed', 'Right-handed', 'Left-handed', 'Right-handed', 'Right-handed', 'Left-handed', 'Right-handed', 'Right-handed']\n",
    "})\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th>Handedness</th>\n      <th>Left-handed</th>\n      <th>Right-handed</th>\n      <th>All</th>\n    </tr>\n    <tr>\n      <th>Nationality</th>\n      <th></th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>Japan</th>\n      <td>2</td>\n      <td>3</td>\n      <td>5</td>\n    </tr>\n    <tr>\n      <th>USA</th>\n      <td>1</td>\n      <td>4</td>\n      <td>5</td>\n    </tr>\n    <tr>\n      <th>All</th>\n      <td>3</td>\n      <td>7</td>\n      <td>10</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": "Handedness   Left-handed  Right-handed  All\nNationality                                \nJapan                  2             3    5\nUSA                    1             4    5\nAll                    3             7   10"
     },
     "execution_count": 125,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.crosstab(data.Nationality, data.Handedness, margins=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>smoker</th>\n      <th>No</th>\n      <th>Yes</th>\n      <th>All</th>\n    </tr>\n    <tr>\n      <th>time</th>\n      <th>day</th>\n      <th></th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th rowspan=\"4\" valign=\"top\">Dinner</th>\n      <th>Fri</th>\n      <td>3</td>\n      <td>9</td>\n      <td>12</td>\n    </tr>\n    <tr>\n      <th>Sat</th>\n      <td>45</td>\n      <td>42</td>\n      <td>87</td>\n    </tr>\n    <tr>\n      <th>Sun</th>\n      <td>57</td>\n      <td>19</td>\n      <td>76</td>\n    </tr>\n    <tr>\n      <th>Thur</th>\n      <td>1</td>\n      <td>0</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Lunch</th>\n      <th>Fri</th>\n      <td>1</td>\n      <td>6</td>\n      <td>7</td>\n    </tr>\n    <tr>\n      <th>Thur</th>\n      <td>44</td>\n      <td>17</td>\n      <td>61</td>\n    </tr>\n    <tr>\n      <th>All</th>\n      <th></th>\n      <td>151</td>\n      <td>93</td>\n      <td>244</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": "smoker        No  Yes  All\ntime   day                \nDinner Fri     3    9   12\n       Sat    45   42   87\n       Sun    57   19   76\n       Thur    1    0    1\nLunch  Fri     1    6    7\n       Thur   44   17   61\nAll          151   93  244"
     },
     "execution_count": 126,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.crosstab([tips.time, tips.day], tips.smoker, margins=True)"
   ]
  }
 ]
}