Python For Data Analysis Workbook V.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Python for Data Analysis - Workbook 5 (Aggregating and Grouping)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Preliminaries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 213,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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.161676</td>\n",
       "      <td>-0.382438</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>a</td>\n",
       "      <td>two</td>\n",
       "      <td>-1.666170</td>\n",
       "      <td>1.474923</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>b</td>\n",
       "      <td>one</td>\n",
       "      <td>-1.726285</td>\n",
       "      <td>0.521704</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>b</td>\n",
       "      <td>two</td>\n",
       "      <td>-0.459961</td>\n",
       "      <td>-0.998841</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>a</td>\n",
       "      <td>one</td>\n",
       "      <td>0.794591</td>\n",
       "      <td>0.142549</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  key1 key2     data1     data2\n",
       "0    a  one  1.161676 -0.382438\n",
       "1    a  two -1.666170  1.474923\n",
       "2    b  one -1.726285  0.521704\n",
       "3    b  two -0.459961 -0.998841\n",
       "4    a  one  0.794591  0.142549"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame({'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": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pandas.core.groupby.generic.SeriesGroupBy object at 0x11b096b38>"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Suppose we wanted to calculate the mean from data1 using keys from key1\n",
    "\n",
    "grouped = df['data1'].groupby(df['key1'])\n",
    "grouped # Creates special GroupBy object"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "key1\n",
       "a    0.096699\n",
       "b   -1.093123\n",
       "Name: data1, dtype: float64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Can now calculate mean from this\n",
    "\n",
    "grouped.mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "key1  key2\n",
      "a     one     0.978133\n",
      "      two    -1.666170\n",
      "b     one    -1.726285\n",
      "      two    -0.459961\n",
      "Name: data1, dtype: float64\n"
     ]
    },
    {
     "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.978133</td>\n",
       "      <td>-1.666170</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>-1.726285</td>\n",
       "      <td>-0.459961</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "key2       one       two\n",
       "key1                    \n",
       "a     0.978133 -1.666170\n",
       "b    -1.726285 -0.459961"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Passing in multiple Series creates a GroupBy object with a hierarchical index\n",
    "\n",
    "means = df['data1'].groupby([df['key1'], df['key2']]).mean()\n",
    "print(means)\n",
    "means.unstack()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "California  2005   -1.666170\n",
       "            2006   -1.726285\n",
       "Ohio        2005    0.350858\n",
       "            2006    0.794591\n",
       "Name: data1, dtype: float64"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Can also use any array that is the right length, not just Series in the same dataframe\n",
    "\n",
    "states = np.array(['Ohio', 'California', 'California', 'Ohio', 'Ohio'])\n",
    "years = np.array([2005, 2005, 2006, 2005, 2006])\n",
    "\n",
    "df['data1'].groupby([states, years]).mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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.096699</td>\n",
       "      <td>0.411678</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>-1.093123</td>\n",
       "      <td>-0.238569</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         data1     data2\n",
       "key1                    \n",
       "a     0.096699  0.411678\n",
       "b    -1.093123 -0.238569"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Can also just pass column names\n",
    "\n",
    "df.groupby('key1').mean()\n",
    "\n",
    "# Note that key2 is dropped because it can't be aggregated (it is categorical)\n",
    "# by default, all numeric columns are aggregated "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "key1  key2\n",
       "a     one     2\n",
       "      two     1\n",
       "b     one     1\n",
       "      two     1\n",
       "dtype: int64"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get number of items that end up being aggregated, though missing values from a group key will be excluded\n",
    "\n",
    "df.groupby(['key1', 'key2']).size()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Iterating over Groups"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "a\n",
      "  key1 key2     data1     data2\n",
      "0    a  one  1.161676 -0.382438\n",
      "1    a  two -1.666170  1.474923\n",
      "4    a  one  0.794591  0.142549\n",
      "b\n",
      "  key1 key2     data1     data2\n",
      "2    b  one -1.726285  0.521704\n",
      "3    b  two -0.459961 -0.998841\n"
     ]
    }
   ],
   "source": [
    "# GroupBy object supports iteration. It generates a sequence of 2-tuples containing the group name along with the \n",
    "# chunk of data\n",
    "\n",
    "for name, group in df.groupby('key1'):\n",
    "    print(name)\n",
    "    print(group)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "a one\n",
      "  key1 key2     data1     data2\n",
      "0    a  one  1.161676 -0.382438\n",
      "4    a  one  0.794591  0.142549\n",
      "a two\n",
      "  key1 key2    data1     data2\n",
      "1    a  two -1.66617  1.474923\n",
      "b one\n",
      "  key1 key2     data1     data2\n",
      "2    b  one -1.726285  0.521704\n",
      "b two\n",
      "  key1 key2     data1     data2\n",
      "3    b  two -0.459961 -0.998841\n"
     ]
    }
   ],
   "source": [
    "# In the case of multiple keys, the first element in the tuple will be the tuple of key values\n",
    "\n",
    "for (k1, k2), group in df.groupby(['key1', 'key2']):\n",
    "    print(k1, k2)\n",
    "    print(group)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "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>2</th>\n",
       "      <td>b</td>\n",
       "      <td>one</td>\n",
       "      <td>-1.726285</td>\n",
       "      <td>0.521704</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>b</td>\n",
       "      <td>two</td>\n",
       "      <td>-0.459961</td>\n",
       "      <td>-0.998841</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  key1 key2     data1     data2\n",
       "2    b  one -1.726285  0.521704\n",
       "3    b  two -0.459961 -0.998841"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# One application for this is to create a dict of the data pieces as a one-liner\n",
    "\n",
    "pieces = dict(list(df.groupby('key1')))\n",
    "# pieces\n",
    "pieces['b']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "float64\n",
      "      data1     data2\n",
      "0  1.161676 -0.382438\n",
      "1 -1.666170  1.474923\n",
      "2 -1.726285  0.521704\n",
      "3 -0.459961 -0.998841\n",
      "4  0.794591  0.142549\n",
      "object\n",
      "  key1 key2\n",
      "0    a  one\n",
      "1    a  two\n",
      "2    b  one\n",
      "3    b  two\n",
      "4    a  one\n"
     ]
    }
   ],
   "source": [
    "# Default grouping happens on axis = 0, but can also do column-wise as well\n",
    "\n",
    "grouped = df.groupby(df.dtypes, axis = 1)\n",
    "for dtype, group in grouped:\n",
    "    print(dtype)\n",
    "    print(group)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "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>data2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>key1</th>\n",
       "      <th>key2</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.119944</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>two</th>\n",
       "      <td>1.474923</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">b</th>\n",
       "      <th>one</th>\n",
       "      <td>0.521704</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>two</th>\n",
       "      <td>-0.998841</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              data2\n",
       "key1 key2          \n",
       "a    one  -0.119944\n",
       "     two   1.474923\n",
       "b    one   0.521704\n",
       "     two  -0.998841"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Selecting only a subset of columns for groupby\n",
    "\n",
    "df.groupby(['key1', 'key2'])[['data2']].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "               a         b         c         d         e\n",
      "Joe     1.073571  1.567554  1.219167 -0.209018 -0.471810\n",
      "Steve   0.985780  0.083055 -0.362425 -0.723889  0.492432\n",
      "Wes    -0.696793       NaN       NaN -0.788155 -1.535000\n",
      "Jim    -0.520464 -0.111967  0.444795 -2.111839 -0.864139\n",
      "Travis -0.408202 -0.381875  0.981163 -1.517212 -0.028128\n"
     ]
    },
    {
     "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>1.010149</td>\n",
       "      <td>2.169315</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Steve</th>\n",
       "      <td>-1.086313</td>\n",
       "      <td>1.561266</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wes</th>\n",
       "      <td>-0.788155</td>\n",
       "      <td>-2.231793</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jim</th>\n",
       "      <td>-1.667044</td>\n",
       "      <td>-1.496571</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Travis</th>\n",
       "      <td>-0.536049</td>\n",
       "      <td>-0.818206</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            blue       red\n",
       "Joe     1.010149  2.169315\n",
       "Steve  -1.086313  1.561266\n",
       "Wes    -0.788155 -2.231793\n",
       "Jim    -1.667044 -1.496571\n",
       "Travis -0.536049 -0.818206"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Grouping with dicts\n",
    "\n",
    "people = pd.DataFrame(np.random.randn(5, 5), columns = list('abcde'), index = ['Joe', 'Steve', 'Wes', 'Jim', 'Travis'])\n",
    "people.iloc[2:3, [1,2]] = np.nan # add a few na values\n",
    "\n",
    "print(people)\n",
    "\n",
    "mapping = {'a': 'red', 'b': 'red', 'c': 'blue', 'd': 'blue', 'e': 'red', 'f':'orange'}\n",
    "\n",
    "people.groupby(mapping, axis = 1).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "a       red\n",
      "b       red\n",
      "c      blue\n",
      "d      blue\n",
      "e       red\n",
      "f    orange\n",
      "dtype: object\n"
     ]
    },
    {
     "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>1.010149</td>\n",
       "      <td>2.169315</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Steve</th>\n",
       "      <td>-1.086313</td>\n",
       "      <td>1.561266</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wes</th>\n",
       "      <td>-0.788155</td>\n",
       "      <td>-2.231793</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jim</th>\n",
       "      <td>-1.667044</td>\n",
       "      <td>-1.496571</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Travis</th>\n",
       "      <td>-0.536049</td>\n",
       "      <td>-0.818206</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            blue       red\n",
       "Joe     1.010149  2.169315\n",
       "Steve  -1.086313  1.561266\n",
       "Wes    -0.788155 -2.231793\n",
       "Jim    -1.667044 -1.496571\n",
       "Travis -0.536049 -0.818206"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Also works with Series\n",
    "\n",
    "map_series = pd.Series(mapping)\n",
    "print(map_series)\n",
    "people.groupby(mapping, axis = 1).sum()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Grouping with Functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "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>-0.143686</td>\n",
       "      <td>1.455587</td>\n",
       "      <td>1.663962</td>\n",
       "      <td>-3.109013</td>\n",
       "      <td>-2.870950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0.985780</td>\n",
       "      <td>0.083055</td>\n",
       "      <td>-0.362425</td>\n",
       "      <td>-0.723889</td>\n",
       "      <td>0.492432</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>-0.408202</td>\n",
       "      <td>-0.381875</td>\n",
       "      <td>0.981163</td>\n",
       "      <td>-1.517212</td>\n",
       "      <td>-0.028128</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          a         b         c         d         e\n",
       "3 -0.143686  1.455587  1.663962 -3.109013 -2.870950\n",
       "5  0.985780  0.083055 -0.362425 -0.723889  0.492432\n",
       "6 -0.408202 -0.381875  0.981163 -1.517212 -0.028128"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Grouping with functions is a more generic way of defining a group mapping vs a dict or series\n",
    "# Any function passed as a group key will be called once per index value, w/ return values used as group names\n",
    "\n",
    "people.groupby(len).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "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>-0.696793</td>\n",
       "      <td>1.567554</td>\n",
       "      <td>1.219167</td>\n",
       "      <td>-0.788155</td>\n",
       "      <td>-1.535000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>two</th>\n",
       "      <td>-0.520464</td>\n",
       "      <td>-0.111967</td>\n",
       "      <td>0.444795</td>\n",
       "      <td>-2.111839</td>\n",
       "      <td>-0.864139</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <th>one</th>\n",
       "      <td>0.985780</td>\n",
       "      <td>0.083055</td>\n",
       "      <td>-0.362425</td>\n",
       "      <td>-0.723889</td>\n",
       "      <td>0.492432</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <th>two</th>\n",
       "      <td>-0.408202</td>\n",
       "      <td>-0.381875</td>\n",
       "      <td>0.981163</td>\n",
       "      <td>-1.517212</td>\n",
       "      <td>-0.028128</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              a         b         c         d         e\n",
       "3 one -0.696793  1.567554  1.219167 -0.788155 -1.535000\n",
       "  two -0.520464 -0.111967  0.444795 -2.111839 -0.864139\n",
       "5 one  0.985780  0.083055 -0.362425 -0.723889  0.492432\n",
       "6 two -0.408202 -0.381875  0.981163 -1.517212 -0.028128"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Can also mix functions with arrays, dicts etc\n",
    "\n",
    "key_list = ['one', 'one', 'one', 'two', 'two']\n",
    "\n",
    "people.groupby([len, key_list]).min()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Grouping by Index Level"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "city        US                            JP          \n",
      "tnr          1         3         5         1         3\n",
      "0     1.411802 -1.412733  1.022808  0.832527 -0.314085\n",
      "1     0.938582 -0.180290 -1.967836 -0.298716 -0.225837\n",
      "2    -0.186946  2.138659  0.404939  0.701811  1.136043\n",
      "3     0.007405 -1.147697  0.483667 -0.728572  0.517681\n"
     ]
    },
    {
     "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>city</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": [
       "city  JP  US\n",
       "0      2   3\n",
       "1      2   3\n",
       "2      2   3\n",
       "3      2   3"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Can aggregate using only one level of an axis index\n",
    "\n",
    "columns = pd.MultiIndex.from_arrays([['US', 'US', 'US', 'JP', 'JP'], [1, 3, 5, 1, 3]], names = ['city', 'tnr'])\n",
    "hier_df = pd.DataFrame(np.random.randn(4, 5), columns = columns)\n",
    "print(hier_df)\n",
    "hier_df.groupby(level = 'city', axis = 1).count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Data Aggregation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "key1\n",
       "a    1.088259\n",
       "b   -0.586593\n",
       "Name: data1, dtype: float64"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Can use predefined and optimized methods like count, sum, mean, std, min, max\n",
    "# Or can call any method from the grouped object. \n",
    "\n",
    "df.groupby('key1')['data1'].quantile(0.9)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "key1\n",
       "a    2.827846\n",
       "b    1.266324\n",
       "Name: data1, dtype: float64"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Can also pass in custom aggregation functions, but these will be slower than the pre-baked ones provided by the API\n",
    "\n",
    "def peak_to_peak(arr):\n",
    "    return arr.max() - arr.min()\n",
    "df.groupby('key1')['data1'].agg(peak_to_peak)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "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",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>25.29</td>\n",
       "      <td>4.71</td>\n",
       "      <td>No</td>\n",
       "      <td>Sun</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>4</td>\n",
       "      <td>0.186240</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   total_bill   tip smoker  day    time  size   tip_pct\n",
       "0       16.99  1.01     No  Sun  Dinner     2  0.059447\n",
       "1       10.34  1.66     No  Sun  Dinner     3  0.160542\n",
       "2       21.01  3.50     No  Sun  Dinner     3  0.166587\n",
       "3       23.68  3.31     No  Sun  Dinner     2  0.139780\n",
       "4       24.59  3.61     No  Sun  Dinner     4  0.146808\n",
       "5       25.29  4.71     No  Sun  Dinner     4  0.186240"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# A couple of examples\n",
    " \n",
    "tips = pd.read_csv('pydata-book/examples/tips.csv')\n",
    "\n",
    "# Add tip % to the total bill\n",
    "\n",
    "tips['tip_pct'] = tips['tip'] / (tips['total_bill'])\n",
    "\n",
    "tips[:6]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "day   smoker\n",
       "Fri   No        0.151650\n",
       "      Yes       0.174783\n",
       "Sat   No        0.158048\n",
       "      Yes       0.147906\n",
       "Sun   No        0.160113\n",
       "      Yes       0.187250\n",
       "Thur  No        0.160298\n",
       "      Yes       0.163863\n",
       "Name: tip_pct, dtype: float64"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped = tips.groupby(['day', 'smoker'])\n",
    "grouped['tip_pct'].agg('mean') # can also pass in the name of the function as a string for descriptive statistics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "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\n",
       "day  smoker                                  \n",
       "Fri  No      0.151650  0.028123      0.067349\n",
       "     Yes     0.174783  0.051293      0.159925\n",
       "Sat  No      0.158048  0.039767      0.235193\n",
       "     Yes     0.147906  0.061375      0.290095\n",
       "Sun  No      0.160113  0.042347      0.193226\n",
       "     Yes     0.187250  0.154134      0.644685\n",
       "Thur No      0.160298  0.038774      0.193350\n",
       "     Yes     0.163863  0.039389      0.151240"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Passing a list of functions returns a dataframe with a list of columns w/ names taken from each of the functions\n",
    "\n",
    "grouped['tip_pct'].agg(['mean', 'std', peak_to_peak])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "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",
       "      <th>baz</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": [
       "                  foo       bar       baz\n",
       "day  smoker                              \n",
       "Fri  No      0.151650  0.028123  0.067349\n",
       "     Yes     0.174783  0.051293  0.159925\n",
       "Sat  No      0.158048  0.039767  0.235193\n",
       "     Yes     0.147906  0.061375  0.290095\n",
       "Sun  No      0.160113  0.042347  0.193226\n",
       "     Yes     0.187250  0.154134  0.644685\n",
       "Thur No      0.160298  0.038774  0.193350\n",
       "     Yes     0.163863  0.039389  0.151240"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Can change the column names by passing in a list of 2-tuples as a mapping\n",
    "\n",
    "grouped['tip_pct'].agg([('foo','mean'), ('bar','std'), ('baz', peak_to_peak)])"
   ]
  },
  {
   "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 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\n",
       "day  smoker                                                         \n",
       "Fri  No           4  0.151650  0.187735          4  18.420000  22.75\n",
       "     Yes         15  0.174783  0.263480         15  16.813333  40.17\n",
       "Sat  No          45  0.158048  0.291990         45  19.661778  48.33\n",
       "     Yes         42  0.147906  0.325733         42  21.276667  50.81\n",
       "Sun  No          57  0.160113  0.252672         57  20.506667  48.17\n",
       "     Yes         19  0.187250  0.710345         19  24.120000  45.35\n",
       "Thur 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": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "functions = ['count', 'mean', 'max']\n",
    "\n",
    "grouped[['tip_pct', 'total_bill']].agg(functions)"
   ]
  },
  {
   "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>tip</th>\n",
       "      <th>size</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>3.50</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Yes</th>\n",
       "      <td>4.73</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Sat</th>\n",
       "      <th>No</th>\n",
       "      <td>9.00</td>\n",
       "      <td>115</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Yes</th>\n",
       "      <td>10.00</td>\n",
       "      <td>104</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Sun</th>\n",
       "      <th>No</th>\n",
       "      <td>6.00</td>\n",
       "      <td>167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Yes</th>\n",
       "      <td>6.50</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Thur</th>\n",
       "      <th>No</th>\n",
       "      <td>6.70</td>\n",
       "      <td>112</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Yes</th>\n",
       "      <td>5.00</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               tip  size\n",
       "day  smoker             \n",
       "Fri  No       3.50     9\n",
       "     Yes      4.73    31\n",
       "Sat  No       9.00   115\n",
       "     Yes     10.00   104\n",
       "Sun  No       6.00   167\n",
       "     Yes      6.50    49\n",
       "Thur No       6.70   112\n",
       "     Yes      5.00    40"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Can also apply different functions to each column individually\n",
    "\n",
    "grouped.agg({'tip': np.max, 'size': 'sum'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "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\n",
       "day  smoker                                             \n",
       "Fri  No      0.120385  0.187735  0.151650  0.028123    9\n",
       "     Yes     0.103555  0.263480  0.174783  0.051293   31\n",
       "Sat  No      0.056797  0.291990  0.158048  0.039767  115\n",
       "     Yes     0.035638  0.325733  0.147906  0.061375  104\n",
       "Sun  No      0.059447  0.252672  0.160113  0.042347  167\n",
       "     Yes     0.065660  0.710345  0.187250  0.154134   49\n",
       "Thur No      0.072961  0.266312  0.160298  0.038774  112\n",
       "     Yes     0.090014  0.241255  0.163863  0.039389   40"
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped.agg({'tip_pct': ['min', 'max', 'mean', 'std'], 'size': 'sum'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "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\n",
       "0   Fri     No   18.420000  2.812500  2.250000  0.151650\n",
       "1   Fri    Yes   16.813333  2.714000  2.066667  0.174783\n",
       "2   Sat     No   19.661778  3.102889  2.555556  0.158048\n",
       "3   Sat    Yes   21.276667  2.875476  2.476190  0.147906\n",
       "4   Sun     No   20.506667  3.167895  2.929825  0.160113\n",
       "5   Sun    Yes   24.120000  3.516842  2.578947  0.187250\n",
       "6  Thur     No   17.113111  2.673778  2.488889  0.160298\n",
       "7  Thur    Yes   19.190588  3.030000  2.352941  0.163863"
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Can also suppress the automated indexing behavior and melt the indexes back into the resulting df as columns\n",
    "\n",
    "tips.groupby(['day', 'smoker'], as_index = False).mean() # can also just call reset_index on the result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### The Apply Method - general split/apply/combine"
   ]
  },
  {
   "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>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\n",
       "109       14.31  4.00    Yes  Sat  Dinner     2  0.279525\n",
       "183       23.17  6.50    Yes  Sun  Dinner     4  0.280535\n",
       "232       11.61  3.39     No  Sat  Dinner     2  0.291990\n",
       "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": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Example, select the top five tip_pct values by group\n",
    "\n",
    "def top(df, n=5, column = 'tip_pct'):\n",
    "    return df.sort_values(by = column)[-n:]\n",
    "\n",
    "top(tips, n = 6)"
   ]
  },
  {
   "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></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=\"5\" valign=\"top\">No</th>\n",
       "      <th>88</th>\n",
       "      <td>24.71</td>\n",
       "      <td>5.85</td>\n",
       "      <td>No</td>\n",
       "      <td>Thur</td>\n",
       "      <td>Lunch</td>\n",
       "      <td>2</td>\n",
       "      <td>0.236746</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>185</th>\n",
       "      <td>20.69</td>\n",
       "      <td>5.00</td>\n",
       "      <td>No</td>\n",
       "      <td>Sun</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>5</td>\n",
       "      <td>0.241663</td>\n",
       "    </tr>\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 rowspan=\"5\" valign=\"top\">Yes</th>\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>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\n",
       "smoker                                                           \n",
       "No     88        24.71  5.85     No  Thur   Lunch     2  0.236746\n",
       "       185       20.69  5.00     No   Sun  Dinner     5  0.241663\n",
       "       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\n",
       "Yes    109       14.31  4.00    Yes   Sat  Dinner     2  0.279525\n",
       "       183       23.17  6.50    Yes   Sun  Dinner     4  0.280535\n",
       "       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": 93,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# What if we group by smoker?\n",
    "\n",
    "tips.groupby('smoker').apply(top)"
   ]
  },
  {
   "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 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></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>day</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=\"4\" valign=\"top\">No</th>\n",
       "      <th>Fri</th>\n",
       "      <th>94</th>\n",
       "      <td>22.75</td>\n",
       "      <td>3.25</td>\n",
       "      <td>No</td>\n",
       "      <td>Fri</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>2</td>\n",
       "      <td>0.142857</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sat</th>\n",
       "      <th>212</th>\n",
       "      <td>48.33</td>\n",
       "      <td>9.00</td>\n",
       "      <td>No</td>\n",
       "      <td>Sat</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>4</td>\n",
       "      <td>0.186220</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sun</th>\n",
       "      <th>156</th>\n",
       "      <td>48.17</td>\n",
       "      <td>5.00</td>\n",
       "      <td>No</td>\n",
       "      <td>Sun</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>6</td>\n",
       "      <td>0.103799</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Thur</th>\n",
       "      <th>142</th>\n",
       "      <td>41.19</td>\n",
       "      <td>5.00</td>\n",
       "      <td>No</td>\n",
       "      <td>Thur</td>\n",
       "      <td>Lunch</td>\n",
       "      <td>5</td>\n",
       "      <td>0.121389</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">Yes</th>\n",
       "      <th>Fri</th>\n",
       "      <th>95</th>\n",
       "      <td>40.17</td>\n",
       "      <td>4.73</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Fri</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>4</td>\n",
       "      <td>0.117750</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sat</th>\n",
       "      <th>170</th>\n",
       "      <td>50.81</td>\n",
       "      <td>10.00</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Sat</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>3</td>\n",
       "      <td>0.196812</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sun</th>\n",
       "      <th>182</th>\n",
       "      <td>45.35</td>\n",
       "      <td>3.50</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Sun</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>3</td>\n",
       "      <td>0.077178</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Thur</th>\n",
       "      <th>197</th>\n",
       "      <td>43.11</td>\n",
       "      <td>5.00</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Thur</td>\n",
       "      <td>Lunch</td>\n",
       "      <td>4</td>\n",
       "      <td>0.115982</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 total_bill    tip smoker   day    time  size   tip_pct\n",
       "smoker day                                                             \n",
       "No     Fri  94        22.75   3.25     No   Fri  Dinner     2  0.142857\n",
       "       Sat  212       48.33   9.00     No   Sat  Dinner     4  0.186220\n",
       "       Sun  156       48.17   5.00     No   Sun  Dinner     6  0.103799\n",
       "       Thur 142       41.19   5.00     No  Thur   Lunch     5  0.121389\n",
       "Yes    Fri  95        40.17   4.73    Yes   Fri  Dinner     4  0.117750\n",
       "       Sat  170       50.81  10.00    Yes   Sat  Dinner     3  0.196812\n",
       "       Sun  182       45.35   3.50    Yes   Sun  Dinner     3  0.077178\n",
       "       Thur 197       43.11   5.00    Yes  Thur   Lunch     4  0.115982"
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Can pass arguments to apply functions as well\n",
    "\n",
    "tips.groupby(['smoker', 'day']).apply(top, n = 1, column = 'total_bill')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "        count      mean       std       min       25%       50%       75%  \\\n",
      "smoker                                                                      \n",
      "No      151.0  0.159328  0.039910  0.056797  0.136906  0.155625  0.185014   \n",
      "Yes      93.0  0.163196  0.085119  0.035638  0.106771  0.153846  0.195059   \n",
      "\n",
      "             max  \n",
      "smoker            \n",
      "No      0.291990  \n",
      "Yes     0.710345  \n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "       smoker\n",
       "count  No        151.000000\n",
       "       Yes        93.000000\n",
       "mean   No          0.159328\n",
       "       Yes         0.163196\n",
       "std    No          0.039910\n",
       "       Yes         0.085119\n",
       "min    No          0.056797\n",
       "       Yes         0.035638\n",
       "25%    No          0.136906\n",
       "       Yes         0.106771\n",
       "50%    No          0.155625\n",
       "       Yes         0.153846\n",
       "75%    No          0.185014\n",
       "       Yes         0.195059\n",
       "max    No          0.291990\n",
       "       Yes         0.710345\n",
       "dtype: float64"
      ]
     },
     "execution_count": 101,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# The describe() method also returns a groupby object...and can be manipulated in a similar fashion\n",
    "\n",
    "result = tips.groupby('smoker')['tip_pct'].describe()\n",
    "print(result)\n",
    "result.unstack('smoker')"
   ]
  },
  {
   "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>88</th>\n",
       "      <td>24.71</td>\n",
       "      <td>5.85</td>\n",
       "      <td>No</td>\n",
       "      <td>Thur</td>\n",
       "      <td>Lunch</td>\n",
       "      <td>2</td>\n",
       "      <td>0.236746</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>185</th>\n",
       "      <td>20.69</td>\n",
       "      <td>5.00</td>\n",
       "      <td>No</td>\n",
       "      <td>Sun</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>5</td>\n",
       "      <td>0.241663</td>\n",
       "    </tr>\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>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>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\n",
       "88        24.71  5.85     No  Thur   Lunch     2  0.236746\n",
       "185       20.69  5.00     No   Sun  Dinner     5  0.241663\n",
       "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\n",
       "109       14.31  4.00    Yes   Sat  Dinner     2  0.279525\n",
       "183       23.17  6.50    Yes   Sun  Dinner     4  0.280535\n",
       "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": 104,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Groupby automatically uses the group keys as an index in the hierarchy, but we can suppress that behavior\n",
    "\n",
    "tips.groupby('smoker', group_keys = False).apply(top)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Quantile and Bucket Analysis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Groupby + cut and qcut makes for easy bucket or quantile analysis\n",
    "\n",
    "frame = pd.DataFrame({'data1': np.random.randn(1000), 'data2': np.random.randn(1000)})\n",
    "quartiles = pd.cut(frame.data1, 4)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    (-1.483, 0.138]\n",
       "1    (-1.483, 0.138]\n",
       "2    (-1.483, 0.138]\n",
       "3     (0.138, 1.758]\n",
       "4    (-1.483, 0.138]\n",
       "5     (0.138, 1.758]\n",
       "6    (-1.483, 0.138]\n",
       "7    (-1.483, 0.138]\n",
       "8     (0.138, 1.758]\n",
       "9    (-1.483, 0.138]\n",
       "Name: data1, dtype: category\n",
       "Categories (4, interval[float64]): [(-3.11, -1.483] < (-1.483, 0.138] < (0.138, 1.758] < (1.758, 3.378]]"
      ]
     },
     "execution_count": 110,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "quartiles[:10]"
   ]
  },
  {
   "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>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.11, -1.483]</th>\n",
       "      <td>-2.055139</td>\n",
       "      <td>2.287701</td>\n",
       "      <td>62.0</td>\n",
       "      <td>0.167308</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(-1.483, 0.138]</th>\n",
       "      <td>-2.231525</td>\n",
       "      <td>2.887238</td>\n",
       "      <td>489.0</td>\n",
       "      <td>0.051885</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(0.138, 1.758]</th>\n",
       "      <td>-2.943179</td>\n",
       "      <td>2.791713</td>\n",
       "      <td>409.0</td>\n",
       "      <td>-0.003603</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(1.758, 3.378]</th>\n",
       "      <td>-1.667625</td>\n",
       "      <td>1.619606</td>\n",
       "      <td>40.0</td>\n",
       "      <td>0.085913</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      min       max  count      mean\n",
       "data1                                               \n",
       "(-3.11, -1.483] -2.055139  2.287701   62.0  0.167308\n",
       "(-1.483, 0.138] -2.231525  2.887238  489.0  0.051885\n",
       "(0.138, 1.758]  -2.943179  2.791713  409.0 -0.003603\n",
       "(1.758, 3.378]  -1.667625  1.619606   40.0  0.085913"
      ]
     },
     "execution_count": 115,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Categorical object returned by cut can be passed directly into groupby\n",
    "# Here's how we would combine to compute the summary statistics for the data2 column\n",
    "\n",
    "def get_stats(group):\n",
    "    return({'min': group.min(), 'max': group.max(), 'count': group.count(), 'mean': group.mean()})\n",
    "\n",
    "grouped = frame.data2.groupby(quartiles)\n",
    "grouped.apply(get_stats).unstack()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Groupby Example 1: Filling missing values with group-specific values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Ohio          0.128497\n",
       "New York     -0.555623\n",
       "Vermont            NaN\n",
       "Florida      -0.528744\n",
       "Oregon        1.005982\n",
       "Nevada             NaN\n",
       "California    0.493904\n",
       "Idaho              NaN\n",
       "dtype: float64"
      ]
     },
     "execution_count": 131,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Sometimes you want to handle na's differently for different columns of data. Groupby can help with that\n",
    "\n",
    "states = ['Ohio', 'New York', 'Vermont', 'Florida', 'Oregon', 'Nevada', 'California', 'Idaho']\n",
    "\n",
    "group_key = ['East'] * 4 + ['West'] * 4\n",
    "data = pd.Series(np.random.randn(8), index = states)\n",
    "data[['Vermont', 'Nevada', 'Idaho']] = np.nan # Set some values as missing for example\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Ohio          1.165969\n",
       "New York     -0.817639\n",
       "Vermont      -0.465578\n",
       "Florida      -2.016265\n",
       "Oregon       -0.416362\n",
       "Nevada       -0.465578\n",
       "California   -0.243595\n",
       "Idaho        -0.465578\n",
       "dtype: float64"
      ]
     },
     "execution_count": 123,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.fillna(data.mean())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 167,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "East   -0.318623\n",
       "West    0.749943\n",
       "dtype: float64"
      ]
     },
     "execution_count": 167,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.groupby(group_key).mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 168,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Ohio          0.128497\n",
       "New York     -0.555623\n",
       "Vermont      -0.318623\n",
       "Florida      -0.528744\n",
       "Oregon        1.005982\n",
       "Nevada        0.749943\n",
       "California    0.493904\n",
       "Idaho         0.749943\n",
       "dtype: float64"
      ]
     },
     "execution_count": 168,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Fill NA values using group means\n",
    "\n",
    "fill_mean = lambda g: g.fillna(g.mean())\n",
    "data.groupby(group_key).apply(fill_mean)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Or use predefined fill values that vary by group\n",
    "\n",
    "fill_values = {'East': 0.5, 'West': -1.}\n",
    "fill_func = lambda g: g.fillna(fill_values[g.name])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Ohio          0.128497\n",
       "New York     -0.555623\n",
       "Vermont       0.500000\n",
       "Florida      -0.528744\n",
       "Oregon        1.005982\n",
       "Nevada       -1.000000\n",
       "California    0.493904\n",
       "Idaho        -1.000000\n",
       "dtype: float64"
      ]
     },
     "execution_count": 138,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.groupby(group_key).apply(fill_func)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Groupby Example 2: Random Sampling"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['AH',\n",
       " '2H',\n",
       " '3H',\n",
       " '4H',\n",
       " '5H',\n",
       " '6H',\n",
       " '7H',\n",
       " '8H',\n",
       " '9H',\n",
       " '10H',\n",
       " 'JH',\n",
       " 'QH',\n",
       " 'KH',\n",
       " 'AS',\n",
       " '2S',\n",
       " '3S',\n",
       " '4S',\n",
       " '5S',\n",
       " '6S',\n",
       " '7S',\n",
       " '8S',\n",
       " '9S',\n",
       " '10S',\n",
       " 'JS',\n",
       " 'QS',\n",
       " 'KS',\n",
       " 'AD',\n",
       " '2D',\n",
       " '3D',\n",
       " '4D',\n",
       " '5D',\n",
       " '6D',\n",
       " '7D',\n",
       " '8D',\n",
       " '9D',\n",
       " '10D',\n",
       " 'JD',\n",
       " 'QD',\n",
       " 'KD',\n",
       " 'AC',\n",
       " '2C',\n",
       " '3C',\n",
       " '4C',\n",
       " '5C',\n",
       " '6C',\n",
       " '7C',\n",
       " '8C',\n",
       " '9C',\n",
       " '10C',\n",
       " 'JC',\n",
       " 'QC',\n",
       " 'KC']"
      ]
     },
     "execution_count": 146,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Replicating a deck of cards\n",
    "\n",
    "suits = ['H', 'S', 'D', 'C']\n",
    "card_vals = (list(range(1,11)) + [10] * 3) * 4\n",
    "base_names = ['A'] + list(range(2, 11)) + ['J', 'Q', 'K']\n",
    "cards = []\n",
    "\n",
    "for suit in suits:\n",
    "    cards.extend(str(num) + suit for num in base_names)\n",
    "    \n",
    "cards"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 155,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "JC    10\n",
       "KS    10\n",
       "QC    10\n",
       "QS    10\n",
       "9S     9\n",
       "dtype: int64"
      ]
     },
     "execution_count": 155,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "deck = pd.Series(card_vals, index = cards)\n",
    "\n",
    "def draw(deck, n=5):\n",
    "    return deck.sample(n)\n",
    "\n",
    "draw(deck)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "8C     8\n",
       "KC    10\n",
       "3D     3\n",
       "9D     9\n",
       "QH    10\n",
       "8H     8\n",
       "AS     1\n",
       "3S     3\n",
       "dtype: int64"
      ]
     },
     "execution_count": 166,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Draw two random cards from each suit. Because the suit is the last character of each card name\n",
    "# we can group based on this and use apply\n",
    "\n",
    "get_suit = lambda card: card[-1]\n",
    "deck.groupby(get_suit, group_keys = False).apply(draw, n = 2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Groupby Example 3: Calculating Weighted Average"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 175,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  category      data   weights\n",
      "0        a -0.184807 -0.623070\n",
      "1        a  0.380423 -0.274543\n",
      "2        a  1.121744 -1.618175\n",
      "3        a -0.596228 -0.321341\n",
      "4        b  0.402979  0.812993\n",
      "5        b  0.369341 -0.005584\n",
      "6        b  1.280265 -1.435692\n",
      "7        b -0.505462  0.214212\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "category\n",
       "a    0.568490\n",
       "b    3.914262\n",
       "dtype: float64"
      ]
     },
     "execution_count": 175,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# under the split-apply-combine paradigm of groupby, operations between columns in a DataFrame or Series, \n",
    "# such as a group weighted average, are possible. \n",
    "\n",
    "df = pd.DataFrame({'category': list('aaaabbbb'), 'data': np.random.randn(8), 'weights': np.random.randn(8)})\n",
    "\n",
    "print(df)\n",
    "grouped = df.groupby('category')\n",
    "\n",
    "get_wavg = lambda g: np.average(g['data'], weights = g['weights'])\n",
    "\n",
    "grouped.apply(get_wavg)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Groupby Example 4: Stock Correlations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 178,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "DatetimeIndex: 2214 entries, 2003-01-02 to 2011-10-14\n",
      "Data columns (total 4 columns):\n",
      " #   Column  Non-Null Count  Dtype  \n",
      "---  ------  --------------  -----  \n",
      " 0   AAPL    2214 non-null   float64\n",
      " 1   MSFT    2214 non-null   float64\n",
      " 2   XOM     2214 non-null   float64\n",
      " 3   SPX     2214 non-null   float64\n",
      "dtypes: float64(4)\n",
      "memory usage: 86.5 KB\n"
     ]
    }
   ],
   "source": [
    "close_px = pd.read_csv('pydata-book/examples/stock_px_2.csv', parse_dates = True, index_col = 0)\n",
    "close_px.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 180,
   "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>AAPL</th>\n",
       "      <th>MSFT</th>\n",
       "      <th>XOM</th>\n",
       "      <th>SPX</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2011-10-11</th>\n",
       "      <td>400.29</td>\n",
       "      <td>27.00</td>\n",
       "      <td>76.27</td>\n",
       "      <td>1195.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011-10-12</th>\n",
       "      <td>402.19</td>\n",
       "      <td>26.96</td>\n",
       "      <td>77.16</td>\n",
       "      <td>1207.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011-10-13</th>\n",
       "      <td>408.43</td>\n",
       "      <td>27.18</td>\n",
       "      <td>76.37</td>\n",
       "      <td>1203.66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011-10-14</th>\n",
       "      <td>422.00</td>\n",
       "      <td>27.27</td>\n",
       "      <td>78.11</td>\n",
       "      <td>1224.58</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              AAPL   MSFT    XOM      SPX\n",
       "2011-10-11  400.29  27.00  76.27  1195.54\n",
       "2011-10-12  402.19  26.96  77.16  1207.25\n",
       "2011-10-13  408.43  27.18  76.37  1203.66\n",
       "2011-10-14  422.00  27.27  78.11  1224.58"
      ]
     },
     "execution_count": 180,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "close_px[-4:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 184,
   "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>AAPL</th>\n",
       "      <th>MSFT</th>\n",
       "      <th>XOM</th>\n",
       "      <th>SPX</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2003</th>\n",
       "      <td>0.541124</td>\n",
       "      <td>0.745174</td>\n",
       "      <td>0.661265</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2004</th>\n",
       "      <td>0.374283</td>\n",
       "      <td>0.588531</td>\n",
       "      <td>0.557742</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2005</th>\n",
       "      <td>0.467540</td>\n",
       "      <td>0.562374</td>\n",
       "      <td>0.631010</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2006</th>\n",
       "      <td>0.428267</td>\n",
       "      <td>0.406126</td>\n",
       "      <td>0.518514</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2007</th>\n",
       "      <td>0.508118</td>\n",
       "      <td>0.658770</td>\n",
       "      <td>0.786264</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2008</th>\n",
       "      <td>0.681434</td>\n",
       "      <td>0.804626</td>\n",
       "      <td>0.828303</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009</th>\n",
       "      <td>0.707103</td>\n",
       "      <td>0.654902</td>\n",
       "      <td>0.797921</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010</th>\n",
       "      <td>0.710105</td>\n",
       "      <td>0.730118</td>\n",
       "      <td>0.839057</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011</th>\n",
       "      <td>0.691931</td>\n",
       "      <td>0.800996</td>\n",
       "      <td>0.859975</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          AAPL      MSFT       XOM  SPX\n",
       "2003  0.541124  0.745174  0.661265  1.0\n",
       "2004  0.374283  0.588531  0.557742  1.0\n",
       "2005  0.467540  0.562374  0.631010  1.0\n",
       "2006  0.428267  0.406126  0.518514  1.0\n",
       "2007  0.508118  0.658770  0.786264  1.0\n",
       "2008  0.681434  0.804626  0.828303  1.0\n",
       "2009  0.707103  0.654902  0.797921  1.0\n",
       "2010  0.710105  0.730118  0.839057  1.0\n",
       "2011  0.691931  0.800996  0.859975  1.0"
      ]
     },
     "execution_count": 184,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create a function to compute pairwise correlation of each column with the 'SPX' column\n",
    "\n",
    "spx_corr = lambda x: x.corrwith(x['SPX']) #corrwith is a pairwise correlation between variable\n",
    "\n",
    "# Compute percent change\n",
    "\n",
    "rets = close_px.pct_change().dropna()\n",
    "\n",
    "# Group percent changes year, extracted from each row label with a one-line function that returns the year\n",
    "\n",
    "get_year = lambda x: x.year\n",
    "by_year = rets.groupby(get_year)\n",
    "\n",
    "by_year.apply(spx_corr)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 185,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2003    0.480868\n",
       "2004    0.259024\n",
       "2005    0.300093\n",
       "2006    0.161735\n",
       "2007    0.417738\n",
       "2008    0.611901\n",
       "2009    0.432738\n",
       "2010    0.571946\n",
       "2011    0.581987\n",
       "dtype: float64"
      ]
     },
     "execution_count": 185,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Computing inter-column correlations\n",
    "\n",
    "by_year.apply(lambda g:g['AAPL'].corr(g['MSFT']))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Groupby Example 5: Group-Wise Linear Regression"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 190,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Econometrics library for statistics. Slightly better suited for explanatory statistics\n",
    "# vs. scikit-learn which is slightly better for ML\n",
    "\n",
    "import statsmodels.api as sm\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 191,
   "metadata": {},
   "outputs": [],
   "source": [
    "def regress(data, yvar, xvars):\n",
    "    Y = data[yvar]\n",
    "    X = data[xvars]\n",
    "    X['intercept'] = 1.\n",
    "    result = sm.OLS(Y, X).fit()\n",
    "    return result.params\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 192,
   "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>SPX</th>\n",
       "      <th>intercept</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2003</th>\n",
       "      <td>1.195406</td>\n",
       "      <td>0.000710</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2004</th>\n",
       "      <td>1.363463</td>\n",
       "      <td>0.004201</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2005</th>\n",
       "      <td>1.766415</td>\n",
       "      <td>0.003246</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2006</th>\n",
       "      <td>1.645496</td>\n",
       "      <td>0.000080</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2007</th>\n",
       "      <td>1.198761</td>\n",
       "      <td>0.003438</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2008</th>\n",
       "      <td>0.968016</td>\n",
       "      <td>-0.001110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009</th>\n",
       "      <td>0.879103</td>\n",
       "      <td>0.002954</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010</th>\n",
       "      <td>1.052608</td>\n",
       "      <td>0.001261</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011</th>\n",
       "      <td>0.806605</td>\n",
       "      <td>0.001514</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           SPX  intercept\n",
       "2003  1.195406   0.000710\n",
       "2004  1.363463   0.004201\n",
       "2005  1.766415   0.003246\n",
       "2006  1.645496   0.000080\n",
       "2007  1.198761   0.003438\n",
       "2008  0.968016  -0.001110\n",
       "2009  0.879103   0.002954\n",
       "2010  1.052608   0.001261\n",
       "2011  0.806605   0.001514"
      ]
     },
     "execution_count": 192,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "by_year.apply(regress, 'AAPL', ['SPX'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Pivot Tables and Cross-tabulation"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Pivot Tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 196,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   total_bill   tip smoker  day    time  size   tip_pct\n",
      "0       16.99  1.01     No  Sun  Dinner     2  0.059447\n",
      "1       10.34  1.66     No  Sun  Dinner     3  0.160542\n",
      "2       21.01  3.50     No  Sun  Dinner     3  0.166587\n",
      "3       23.68  3.31     No  Sun  Dinner     2  0.139780\n",
      "4       24.59  3.61     No  Sun  Dinner     4  0.146808\n",
      "5       25.29  4.71     No  Sun  Dinner     4  0.186240\n"
     ]
    },
    {
     "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\n",
       "day  smoker                                          \n",
       "Fri  No      2.250000  2.812500  0.151650   18.420000\n",
       "     Yes     2.066667  2.714000  0.174783   16.813333\n",
       "Sat  No      2.555556  3.102889  0.158048   19.661778\n",
       "     Yes     2.476190  2.875476  0.147906   21.276667\n",
       "Sun  No      2.929825  3.167895  0.160113   20.506667\n",
       "     Yes     2.578947  3.516842  0.187250   24.120000\n",
       "Thur No      2.488889  2.673778  0.160298   17.113111\n",
       "     Yes     2.352941  3.030000  0.163863   19.190588"
      ]
     },
     "execution_count": 196,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Pivot table is an aggregated table of data by one or more keys arranged in a rectangle \n",
    "# with some keys as rows and some as cols\n",
    "\n",
    "# Note, pivot() is a less powerful function that does not aggregate and can work with non-numeric data\n",
    "print(tips[:6])\n",
    "tips.pivot_table(index = ['day', 'smoker']) # default aggregation method is mean"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 197,
   "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          \n",
       "smoker             No       Yes        No       Yes\n",
       "time   day                                         \n",
       "Dinner 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\n",
       "Lunch  Fri   3.000000  1.833333  0.187735  0.188937\n",
       "       Thur  2.500000  2.352941  0.160311  0.163863"
      ]
     },
     "execution_count": 197,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Aggregate only tip_pct and size, but group by time\n",
    "\n",
    "tips.pivot_table(['tip_pct', 'size'], index = ['time', 'day'], columns = 'smoker')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 200,
   "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                    \n",
       "smoker             No       Yes       All        No       Yes       All\n",
       "time   day                                                             \n",
       "Dinner 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\n",
       "Lunch  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\n",
       "All          2.668874  2.408602  2.569672  0.159328  0.163196  0.160803"
      ]
     },
     "execution_count": 200,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Can include partial totals/subtotals by passing margins=True, which has the effect of adding all row and \n",
    "# column labels corrsponding values are group statistics for all the data within a single tier\n",
    "\n",
    "tips.pivot_table(['tip_pct', 'size'], index = ['time', 'day'], columns = 'smoker', margins = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 206,
   "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</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</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</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</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</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "day             Fri   Sat   Sun  Thur  All\n",
       "time   smoker                             \n",
       "Dinner No       3.0  45.0  57.0   1.0  106\n",
       "       Yes      9.0  42.0  19.0   NaN   70\n",
       "Lunch  No       1.0   NaN   NaN  44.0   45\n",
       "       Yes      6.0   NaN   NaN  17.0   23\n",
       "All            19.0  87.0  76.0  62.0  244"
      ]
     },
     "execution_count": 206,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Can pass different agg function using aggfunc (as in the below example, using group counts)\n",
    "# pass an optional fill value for NAs\n",
    "\n",
    "tips.pivot_table('tip_pct', index = ['time', 'smoker'], columns = 'day', aggfunc = 'count', margins = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 208,
   "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>day</th>\n",
       "      <th>Fri</th>\n",
       "      <th>Sat</th>\n",
       "      <th>Sun</th>\n",
       "      <th>Thur</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>time</th>\n",
       "      <th>size</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=\"11\" valign=\"top\">Dinner</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">1</th>\n",
       "      <th>No</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.137931</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Yes</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.325733</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">2</th>\n",
       "      <th>No</th>\n",
       "      <td>0.139622</td>\n",
       "      <td>0.162705</td>\n",
       "      <td>0.168859</td>\n",
       "      <td>0.159744</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Yes</th>\n",
       "      <td>0.171297</td>\n",
       "      <td>0.148668</td>\n",
       "      <td>0.207893</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">3</th>\n",
       "      <th>No</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.154661</td>\n",
       "      <td>0.152663</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Yes</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.144995</td>\n",
       "      <td>0.152660</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">4</th>\n",
       "      <th>No</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.150096</td>\n",
       "      <td>0.148143</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Yes</th>\n",
       "      <td>0.117750</td>\n",
       "      <td>0.124515</td>\n",
       "      <td>0.193370</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">5</th>\n",
       "      <th>No</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.206928</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Yes</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.106572</td>\n",
       "      <td>0.065660</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <th>No</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.103799</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"10\" valign=\"top\">Lunch</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">1</th>\n",
       "      <th>No</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.181728</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Yes</th>\n",
       "      <td>0.223776</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">2</th>\n",
       "      <th>No</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.166005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Yes</th>\n",
       "      <td>0.181969</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.158843</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">3</th>\n",
       "      <th>No</th>\n",
       "      <td>0.187735</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.084246</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Yes</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.204952</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">4</th>\n",
       "      <th>No</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.138919</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Yes</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.155410</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <th>No</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.121389</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <th>No</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.173706</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "day                      Fri       Sat       Sun      Thur\n",
       "time   size smoker                                        \n",
       "Dinner 1    No      0.000000  0.137931  0.000000  0.000000\n",
       "            Yes     0.000000  0.325733  0.000000  0.000000\n",
       "       2    No      0.139622  0.162705  0.168859  0.159744\n",
       "            Yes     0.171297  0.148668  0.207893  0.000000\n",
       "       3    No      0.000000  0.154661  0.152663  0.000000\n",
       "            Yes     0.000000  0.144995  0.152660  0.000000\n",
       "       4    No      0.000000  0.150096  0.148143  0.000000\n",
       "            Yes     0.117750  0.124515  0.193370  0.000000\n",
       "       5    No      0.000000  0.000000  0.206928  0.000000\n",
       "            Yes     0.000000  0.106572  0.065660  0.000000\n",
       "       6    No      0.000000  0.000000  0.103799  0.000000\n",
       "Lunch  1    No      0.000000  0.000000  0.000000  0.181728\n",
       "            Yes     0.223776  0.000000  0.000000  0.000000\n",
       "       2    No      0.000000  0.000000  0.000000  0.166005\n",
       "            Yes     0.181969  0.000000  0.000000  0.158843\n",
       "       3    No      0.187735  0.000000  0.000000  0.084246\n",
       "            Yes     0.000000  0.000000  0.000000  0.204952\n",
       "       4    No      0.000000  0.000000  0.000000  0.138919\n",
       "            Yes     0.000000  0.000000  0.000000  0.155410\n",
       "       5    No      0.000000  0.000000  0.000000  0.121389\n",
       "       6    No      0.000000  0.000000  0.000000  0.173706"
      ]
     },
     "execution_count": 208,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# pass an optional fill value for NAs\n",
    "\n",
    "tips.pivot_table('tip_pct', index = ['time', 'size', 'smoker'], columns = 'day', aggfunc = 'mean', fill_value = 0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Crosstabs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 212,
   "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\n",
       "time   day                \n",
       "Dinner Fri     3    9   12\n",
       "       Sat    45   42   87\n",
       "       Sun    57   19   76\n",
       "       Thur    1    0    1\n",
       "Lunch  Fri     1    6    7\n",
       "       Thur   44   17   61\n",
       "All          151   93  244"
      ]
     },
     "execution_count": 212,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# a special case of a pivot table that computes group frequencies\n",
    "\n",
    "pd.crosstab([tips.time, tips.day], tips.smoker, margins = True)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}