Grouping-and-Aggregating-Guide.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "loved-badge",
   "metadata": {},
   "source": [
    "# Comprehensive Guide to Grouping and Aggregating with Pandas\n",
    "Chris Mofitt. \"Comprehensive Guide to Grouping and Aggregating with Pandas\". _Practical Business Python_, 9 Nov. 2020, https://pbpython.com/groupby-agg.html."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "signed-hartford",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import seaborn as sns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "focused-atmosphere",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = sns.load_dataset('titanic')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "starting-sense",
   "metadata": {},
   "source": [
    "## Pandas aggregation options\n",
    "\n",
    "### List"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "interesting-salvation",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "sum     28693.949300\n",
       "mean       32.204208\n",
       "Name: fare, dtype: float64"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['fare'].agg(['sum', 'mean'])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "hybrid-mobile",
   "metadata": {},
   "source": [
    "### Dictionary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "patent-jason",
   "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>fare</th>\n",
       "      <th>sex</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>sum</th>\n",
       "      <td>28693.949300</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>32.204208</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>NaN</td>\n",
       "      <td>891.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               fare    sex\n",
       "sum    28693.949300    NaN\n",
       "mean      32.204208    NaN\n",
       "count           NaN  891.0"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.agg({'fare': ['sum', 'mean'],\n",
    "         'sex': ['count']})\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "quiet-indicator",
   "metadata": {},
   "source": [
    "### Tuple"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "unusual-daughter",
   "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>fare</th>\n",
       "      <th>sex</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>fare_sum</th>\n",
       "      <td>28693.949300</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>fare_mean</th>\n",
       "      <td>32.204208</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sex_count</th>\n",
       "      <td>NaN</td>\n",
       "      <td>891.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   fare    sex\n",
       "fare_sum   28693.949300    NaN\n",
       "fare_mean     32.204208    NaN\n",
       "sex_count           NaN  891.0"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.agg(fare_sum=('fare', 'sum'),\n",
    "       fare_mean=('fare', 'mean'),\n",
    "       sex_count=('sex', 'count'))\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "suited-sharp",
   "metadata": {},
   "source": [
    "## Groupby\n",
    "### Basic math"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "aggregate-defeat",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"9\" halign=\"left\">fare</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>sum</th>\n",
       "      <th>mean</th>\n",
       "      <th>median</th>\n",
       "      <th>min</th>\n",
       "      <th>max</th>\n",
       "      <th>std</th>\n",
       "      <th>var</th>\n",
       "      <th>mad</th>\n",
       "      <th>prod</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>embark_town</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Cherbourg</th>\n",
       "      <td>10072.30</td>\n",
       "      <td>59.95</td>\n",
       "      <td>29.70</td>\n",
       "      <td>4.01</td>\n",
       "      <td>512.33</td>\n",
       "      <td>83.91</td>\n",
       "      <td>7041.39</td>\n",
       "      <td>53.02</td>\n",
       "      <td>6.193716e+250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Queenstown</th>\n",
       "      <td>1022.25</td>\n",
       "      <td>13.28</td>\n",
       "      <td>7.75</td>\n",
       "      <td>6.75</td>\n",
       "      <td>90.00</td>\n",
       "      <td>14.19</td>\n",
       "      <td>201.30</td>\n",
       "      <td>7.87</td>\n",
       "      <td>6.458671e+78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Southampton</th>\n",
       "      <td>17439.40</td>\n",
       "      <td>27.08</td>\n",
       "      <td>13.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>263.00</td>\n",
       "      <td>35.89</td>\n",
       "      <td>1287.95</td>\n",
       "      <td>21.30</td>\n",
       "      <td>0.000000e+00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 fare                                                     \\\n",
       "                  sum   mean median   min     max    std      var    mad   \n",
       "embark_town                                                                \n",
       "Cherbourg    10072.30  59.95  29.70  4.01  512.33  83.91  7041.39  53.02   \n",
       "Queenstown    1022.25  13.28   7.75  6.75   90.00  14.19   201.30   7.87   \n",
       "Southampton  17439.40  27.08  13.00  0.00  263.00  35.89  1287.95  21.30   \n",
       "\n",
       "                            \n",
       "                      prod  \n",
       "embark_town                 \n",
       "Cherbourg    6.193716e+250  \n",
       "Queenstown    6.458671e+78  \n",
       "Southampton   0.000000e+00  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "agg_func_math = {\n",
    "    'fare':\n",
    "    ['sum', 'mean', 'median', 'min', 'max', 'std', 'var', 'mad', 'prod']\n",
    "}\n",
    "df.groupby(['embark_town']).agg(agg_func_math).round(2)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "knowing-pavilion",
   "metadata": {},
   "source": [
    "Use describe to run multiple built-in aggregations at once:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "blessed-arnold",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"8\" halign=\"left\">fare</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"8\" halign=\"left\">describe</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>std</th>\n",
       "      <th>min</th>\n",
       "      <th>25%</th>\n",
       "      <th>50%</th>\n",
       "      <th>75%</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>embark_town</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>Cherbourg</th>\n",
       "      <td>168.0</td>\n",
       "      <td>59.95</td>\n",
       "      <td>83.91</td>\n",
       "      <td>4.01</td>\n",
       "      <td>13.70</td>\n",
       "      <td>29.70</td>\n",
       "      <td>78.5</td>\n",
       "      <td>512.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Queenstown</th>\n",
       "      <td>77.0</td>\n",
       "      <td>13.28</td>\n",
       "      <td>14.19</td>\n",
       "      <td>6.75</td>\n",
       "      <td>7.75</td>\n",
       "      <td>7.75</td>\n",
       "      <td>15.5</td>\n",
       "      <td>90.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Southampton</th>\n",
       "      <td>644.0</td>\n",
       "      <td>27.08</td>\n",
       "      <td>35.89</td>\n",
       "      <td>0.00</td>\n",
       "      <td>8.05</td>\n",
       "      <td>13.00</td>\n",
       "      <td>27.9</td>\n",
       "      <td>263.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                fare                                                \n",
       "            describe                                                \n",
       "               count   mean    std   min    25%    50%   75%     max\n",
       "embark_town                                                         \n",
       "Cherbourg      168.0  59.95  83.91  4.01  13.70  29.70  78.5  512.33\n",
       "Queenstown      77.0  13.28  14.19  6.75   7.75   7.75  15.5   90.00\n",
       "Southampton    644.0  27.08  35.89  0.00   8.05  13.00  27.9  263.00"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "agg_func_describe = {'fare': ['describe']}\n",
    "df.groupby(['embark_town']).agg(agg_func_describe).round(2)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "banner-navigator",
   "metadata": {},
   "source": [
    "### Counting"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "lesser-subscription",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"3\" halign=\"left\">embark_town</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>nunique</th>\n",
       "      <th>size</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>deck</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>A</th>\n",
       "      <td>15</td>\n",
       "      <td>2</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B</th>\n",
       "      <td>45</td>\n",
       "      <td>2</td>\n",
       "      <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C</th>\n",
       "      <td>59</td>\n",
       "      <td>3</td>\n",
       "      <td>59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>D</th>\n",
       "      <td>33</td>\n",
       "      <td>2</td>\n",
       "      <td>33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E</th>\n",
       "      <td>32</td>\n",
       "      <td>3</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>F</th>\n",
       "      <td>13</td>\n",
       "      <td>3</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>G</th>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     embark_town             \n",
       "           count nunique size\n",
       "deck                         \n",
       "A             15       2   15\n",
       "B             45       2   47\n",
       "C             59       3   59\n",
       "D             33       2   33\n",
       "E             32       3   32\n",
       "F             13       3   13\n",
       "G              4       1    4"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "agg_func_count = {'embark_town': ['count', 'nunique', 'size']}\n",
    "df.groupby(['deck']).agg(agg_func_count)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "meaning-shooting",
   "metadata": {},
   "source": [
    "### First and last\n",
    "Select highest and lowest fare by embarked town (need to sort first to have first and last pick max and min values)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "interesting-weight",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">fare</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>first</th>\n",
       "      <th>last</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>embark_town</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Cherbourg</th>\n",
       "      <td>512.3292</td>\n",
       "      <td>4.0125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Queenstown</th>\n",
       "      <td>90.0000</td>\n",
       "      <td>6.7500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Southampton</th>\n",
       "      <td>263.0000</td>\n",
       "      <td>0.0000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 fare        \n",
       "                first    last\n",
       "embark_town                  \n",
       "Cherbourg    512.3292  4.0125\n",
       "Queenstown    90.0000  6.7500\n",
       "Southampton  263.0000  0.0000"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "agg_func_selection = {'fare': ['first', 'last']}\n",
    "df.sort_values(by=['fare'],\n",
    "            ascending=False).groupby(['embark_town'\n",
    "                                        ]).agg(agg_func_selection)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "selective-genre",
   "metadata": {},
   "source": [
    "Instead use idxmax and idxmin to select values that correspond to max and min:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "altered-builder",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">fare</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>idxmax</th>\n",
       "      <th>idxmin</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>embark_town</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Cherbourg</th>\n",
       "      <td>258</td>\n",
       "      <td>378</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Queenstown</th>\n",
       "      <td>245</td>\n",
       "      <td>143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Southampton</th>\n",
       "      <td>27</td>\n",
       "      <td>179</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              fare       \n",
       "            idxmax idxmin\n",
       "embark_town              \n",
       "Cherbourg      258    378\n",
       "Queenstown     245    143\n",
       "Southampton     27    179"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "agg_func_max_min = {'fare': ['idxmax', 'idxmin']}\n",
    "df.groupby(['embark_town']).agg(agg_func_max_min)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "looking-bronze",
   "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>survived</th>\n",
       "      <th>pclass</th>\n",
       "      <th>sex</th>\n",
       "      <th>age</th>\n",
       "      <th>sibsp</th>\n",
       "      <th>parch</th>\n",
       "      <th>fare</th>\n",
       "      <th>embarked</th>\n",
       "      <th>class</th>\n",
       "      <th>who</th>\n",
       "      <th>adult_male</th>\n",
       "      <th>deck</th>\n",
       "      <th>embark_town</th>\n",
       "      <th>alive</th>\n",
       "      <th>alone</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>258</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "      <td>35.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>512.3292</td>\n",
       "      <td>C</td>\n",
       "      <td>First</td>\n",
       "      <td>woman</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Cherbourg</td>\n",
       "      <td>yes</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>378</th>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>male</td>\n",
       "      <td>20.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4.0125</td>\n",
       "      <td>C</td>\n",
       "      <td>Third</td>\n",
       "      <td>man</td>\n",
       "      <td>True</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Cherbourg</td>\n",
       "      <td>no</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     survived  pclass     sex   age  sibsp  parch      fare embarked  class  \\\n",
       "258         1       1  female  35.0      0      0  512.3292        C  First   \n",
       "378         0       3    male  20.0      0      0    4.0125        C  Third   \n",
       "\n",
       "       who  adult_male deck embark_town alive  alone  \n",
       "258  woman       False  NaN   Cherbourg   yes   True  \n",
       "378    man        True  NaN   Cherbourg    no   True  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[[258, 378]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "joint-madagascar",
   "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>survived</th>\n",
       "      <th>pclass</th>\n",
       "      <th>sex</th>\n",
       "      <th>age</th>\n",
       "      <th>sibsp</th>\n",
       "      <th>parch</th>\n",
       "      <th>fare</th>\n",
       "      <th>embarked</th>\n",
       "      <th>class</th>\n",
       "      <th>who</th>\n",
       "      <th>adult_male</th>\n",
       "      <th>deck</th>\n",
       "      <th>embark_town</th>\n",
       "      <th>alive</th>\n",
       "      <th>alone</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>258</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "      <td>35.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>512.3292</td>\n",
       "      <td>C</td>\n",
       "      <td>First</td>\n",
       "      <td>woman</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Cherbourg</td>\n",
       "      <td>yes</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72</th>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>male</td>\n",
       "      <td>21.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>73.5000</td>\n",
       "      <td>S</td>\n",
       "      <td>Second</td>\n",
       "      <td>man</td>\n",
       "      <td>True</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Southampton</td>\n",
       "      <td>no</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159</th>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>male</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8</td>\n",
       "      <td>2</td>\n",
       "      <td>69.5500</td>\n",
       "      <td>S</td>\n",
       "      <td>Third</td>\n",
       "      <td>man</td>\n",
       "      <td>True</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Southampton</td>\n",
       "      <td>no</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     survived  pclass     sex   age  sibsp  parch      fare embarked   class  \\\n",
       "258         1       1  female  35.0      0      0  512.3292        C   First   \n",
       "72          0       2    male  21.0      0      0   73.5000        S  Second   \n",
       "159         0       3    male   NaN      8      2   69.5500        S   Third   \n",
       "\n",
       "       who  adult_male deck  embark_town alive  alone  \n",
       "258  woman       False  NaN    Cherbourg   yes   True  \n",
       "72     man        True  NaN  Southampton    no   True  \n",
       "159    man        True  NaN  Southampton    no  False  "
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[df.groupby('class')['fare'].idxmax()]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "closing-damages",
   "metadata": {},
   "source": [
    "### Other libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "automated-elder",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"3\" halign=\"left\">fare</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>skew</th>\n",
       "      <th>mode</th>\n",
       "      <th>mode</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>embark_town</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Cherbourg</th>\n",
       "      <td>3.305112</td>\n",
       "      <td>([7.2292], [15])</td>\n",
       "      <td>7.2292</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Queenstown</th>\n",
       "      <td>4.265111</td>\n",
       "      <td>([7.75], [30])</td>\n",
       "      <td>7.7500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Southampton</th>\n",
       "      <td>3.640276</td>\n",
       "      <td>([8.05], [43])</td>\n",
       "      <td>8.0500</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 fare                          \n",
       "                 skew              mode    mode\n",
       "embark_town                                    \n",
       "Cherbourg    3.305112  ([7.2292], [15])  7.2292\n",
       "Queenstown   4.265111    ([7.75], [30])  7.7500\n",
       "Southampton  3.640276    ([8.05], [43])  8.0500"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from scipy.stats import skew, mode\n",
    "agg_func_stats = {'fare': [skew, mode, pd.Series.mode]}\n",
    "df.groupby(['embark_town']).agg(agg_func_stats)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "advance-bernard",
   "metadata": {},
   "source": [
    "### Working with text"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "enormous-florist",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"3\" halign=\"left\">deck</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>nunique</th>\n",
       "      <th>mode</th>\n",
       "      <th>set</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>class</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>First</th>\n",
       "      <td>5</td>\n",
       "      <td>([C], [59])</td>\n",
       "      <td>{nan, E, C, A, D, B}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Second</th>\n",
       "      <td>3</td>\n",
       "      <td>([F], [8])</td>\n",
       "      <td>{nan, E, F, D}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Third</th>\n",
       "      <td>3</td>\n",
       "      <td>([F], [5])</td>\n",
       "      <td>{nan, E, G, F}</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          deck                                   \n",
       "       nunique         mode                   set\n",
       "class                                            \n",
       "First        5  ([C], [59])  {nan, E, C, A, D, B}\n",
       "Second       3   ([F], [8])        {nan, E, F, D}\n",
       "Third        3   ([F], [5])        {nan, E, G, F}"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "agg_func_text = {'deck': [ 'nunique', mode, set]}\n",
    "df.groupby(['class']).agg(agg_func_text)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "alone-perth",
   "metadata": {},
   "source": [
    "### Custom Functions\n",
    "Calculate the 25th percentile of the data using four approaches.\n",
    "\n",
    "First, partial function:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "collected-fifty",
   "metadata": {},
   "outputs": [],
   "source": [
    "from functools import partial\n",
    "# Use partial\n",
    "q_25 = partial(pd.Series.quantile, q=0.25)\n",
    "q_25.__name__ = '25%'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "affected-colors",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define a function\n",
    "def percentile_25(x):\n",
    "    return x.quantile(.25)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "superb-weapon",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define a lambda function\n",
    "lambda_25 = lambda x: x.quantile(.25)\n",
    "lambda_25.__name__ = 'lambda_25%'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "other-browser",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"4\" halign=\"left\">fare</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>25%</th>\n",
       "      <th>percentile_25</th>\n",
       "      <th>lambda_25%</th>\n",
       "      <th>&lt;lambda_0&gt;</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>embark_town</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Cherbourg</th>\n",
       "      <td>13.70</td>\n",
       "      <td>13.70</td>\n",
       "      <td>13.70</td>\n",
       "      <td>13.70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Queenstown</th>\n",
       "      <td>7.75</td>\n",
       "      <td>7.75</td>\n",
       "      <td>7.75</td>\n",
       "      <td>7.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Southampton</th>\n",
       "      <td>8.05</td>\n",
       "      <td>8.05</td>\n",
       "      <td>8.05</td>\n",
       "      <td>8.05</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              fare                                    \n",
       "               25% percentile_25 lambda_25% <lambda_0>\n",
       "embark_town                                           \n",
       "Cherbourg    13.70         13.70      13.70      13.70\n",
       "Queenstown    7.75          7.75       7.75       7.75\n",
       "Southampton   8.05          8.05       8.05       8.05"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Use a lambda function inline\n",
    "agg_func = {\n",
    "    'fare': [q_25, percentile_25, lambda_25, lambda x: x.quantile(.25)]\n",
    "}\n",
    "\n",
    "df.groupby(['embark_town']).agg(agg_func).round(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "revised-ratio",
   "metadata": {},
   "source": [
    "### Custom function examples\n",
    "Count number of null values:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "numerical-print",
   "metadata": {},
   "outputs": [],
   "source": [
    "def count_nulls(s):\n",
    "    return s.size - s.count()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "modular-hello",
   "metadata": {},
   "source": [
    "Include NaN values in unique counts:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "reserved-debate",
   "metadata": {},
   "outputs": [],
   "source": [
    "def unique_nan(s):\n",
    "    return s.nunique(dropna=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "earned-source",
   "metadata": {},
   "source": [
    "Summary of all values together:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "raising-bikini",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"6\" halign=\"left\">embark_town</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>nunique</th>\n",
       "      <th>size</th>\n",
       "      <th>unique_nan</th>\n",
       "      <th>count_nulls</th>\n",
       "      <th>set</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>deck</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>A</th>\n",
       "      <td>15</td>\n",
       "      <td>2</td>\n",
       "      <td>15</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>{Southampton, Cherbourg}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B</th>\n",
       "      <td>45</td>\n",
       "      <td>2</td>\n",
       "      <td>47</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>{nan, Southampton, Cherbourg}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C</th>\n",
       "      <td>59</td>\n",
       "      <td>3</td>\n",
       "      <td>59</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>{Queenstown, Southampton, Cherbourg}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>D</th>\n",
       "      <td>33</td>\n",
       "      <td>2</td>\n",
       "      <td>33</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>{Southampton, Cherbourg}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E</th>\n",
       "      <td>32</td>\n",
       "      <td>3</td>\n",
       "      <td>32</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>{Queenstown, Southampton, Cherbourg}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>F</th>\n",
       "      <td>13</td>\n",
       "      <td>3</td>\n",
       "      <td>13</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>{Queenstown, Southampton, Cherbourg}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>G</th>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>{Southampton}</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     embark_town                                      \\\n",
       "           count nunique size unique_nan count_nulls   \n",
       "deck                                                   \n",
       "A             15       2   15          2           0   \n",
       "B             45       2   47          3           2   \n",
       "C             59       3   59          3           0   \n",
       "D             33       2   33          2           0   \n",
       "E             32       3   32          3           0   \n",
       "F             13       3   13          3           0   \n",
       "G              4       1    4          1           0   \n",
       "\n",
       "                                            \n",
       "                                       set  \n",
       "deck                                        \n",
       "A                 {Southampton, Cherbourg}  \n",
       "B            {nan, Southampton, Cherbourg}  \n",
       "C     {Queenstown, Southampton, Cherbourg}  \n",
       "D                 {Southampton, Cherbourg}  \n",
       "E     {Queenstown, Southampton, Cherbourg}  \n",
       "F     {Queenstown, Southampton, Cherbourg}  \n",
       "G                            {Southampton}  "
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "agg_func_custom_count = {\n",
    "    'embark_town': ['count', 'nunique', 'size', unique_nan, count_nulls, set]\n",
    "}\n",
    "df.groupby(['deck']).agg(agg_func_custom_count)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "through-falls",
   "metadata": {},
   "source": [
    "To calculate the 90th percentile, use quantile:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "speaking-spectrum",
   "metadata": {},
   "outputs": [],
   "source": [
    "def percentile_90(x):\n",
    "    return x.quantile(.9)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "animal-cedar",
   "metadata": {},
   "source": [
    "For trimmed mean where lowest 10th percent is excluded, use scipy status function:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "built-second",
   "metadata": {},
   "outputs": [],
   "source": [
    "from scipy.stats import trim_mean\n",
    "def trim_mean_10(x):\n",
    "    return trim_mean(x, 0.1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "generic-cargo",
   "metadata": {},
   "source": [
    "For largest value, regardless of sort order:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "drawn-trunk",
   "metadata": {},
   "outputs": [],
   "source": [
    "def largest(x):\n",
    "    return x.nlargest(1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "distributed-reputation",
   "metadata": {},
   "source": [
    "Incorporate [sparklines](https://pbpython.com/styling-pandas.html):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "excess-coupon",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sparklines import sparklines\n",
    "import numpy as np\n",
    "def sparkline_str(x):\n",
    "    bins=np.histogram(x)[0]\n",
    "    sl = ''.join(sparklines(bins))\n",
    "    return sl"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "departmental-fundamentals",
   "metadata": {},
   "source": [
    "All put together:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "cellular-advocate",
   "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\">fare</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>percentile_90</th>\n",
       "      <th>trim_mean_10</th>\n",
       "      <th>largest</th>\n",
       "      <th>sparkline_str</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>class</th>\n",
       "      <th>embark_town</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">First</th>\n",
       "      <th>Cherbourg</th>\n",
       "      <td>227.5250</td>\n",
       "      <td>85.408335</td>\n",
       "      <td>512.3292</td>\n",
       "      <td>█▇▂▁▃▁▁▁▁▂</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Queenstown</th>\n",
       "      <td>90.0000</td>\n",
       "      <td>90.000000</td>\n",
       "      <td>90.0000</td>\n",
       "      <td>▁▁▁▁▁█▁▁▁▁</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Southampton</th>\n",
       "      <td>152.3150</td>\n",
       "      <td>60.500160</td>\n",
       "      <td>263.0000</td>\n",
       "      <td>▃█▄▃▂▂▁▁▂▂</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Second</th>\n",
       "      <th>Cherbourg</th>\n",
       "      <td>41.5792</td>\n",
       "      <td>25.167500</td>\n",
       "      <td>41.5792</td>\n",
       "      <td>█▄▁▁▄▂▄▁▄▅</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Queenstown</th>\n",
       "      <td>12.3500</td>\n",
       "      <td>12.350000</td>\n",
       "      <td>12.3500</td>\n",
       "      <td>▁▁▁▁▁█▁▁▁▁</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Southampton</th>\n",
       "      <td>31.7500</td>\n",
       "      <td>18.202273</td>\n",
       "      <td>73.5000</td>\n",
       "      <td>▂█▂▅▁▂▁▁▁▁</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Third</th>\n",
       "      <th>Cherbourg</th>\n",
       "      <td>19.0229</td>\n",
       "      <td>10.677941</td>\n",
       "      <td>22.3583</td>\n",
       "      <td>▁█▃▂▁▄▃▁▂▂</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Queenstown</th>\n",
       "      <td>24.0600</td>\n",
       "      <td>9.670476</td>\n",
       "      <td>29.1250</td>\n",
       "      <td>█▁▁▂▁▁▁▂▁▂</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Southampton</th>\n",
       "      <td>31.2750</td>\n",
       "      <td>11.501469</td>\n",
       "      <td>69.5500</td>\n",
       "      <td>▁█▂▂▂▁▁▁▁▁</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            fare                                     \n",
       "                   percentile_90 trim_mean_10   largest sparkline_str\n",
       "class  embark_town                                                   \n",
       "First  Cherbourg        227.5250    85.408335  512.3292    █▇▂▁▃▁▁▁▁▂\n",
       "       Queenstown        90.0000    90.000000   90.0000    ▁▁▁▁▁█▁▁▁▁\n",
       "       Southampton      152.3150    60.500160  263.0000    ▃█▄▃▂▂▁▁▂▂\n",
       "Second Cherbourg         41.5792    25.167500   41.5792    █▄▁▁▄▂▄▁▄▅\n",
       "       Queenstown        12.3500    12.350000   12.3500    ▁▁▁▁▁█▁▁▁▁\n",
       "       Southampton       31.7500    18.202273   73.5000    ▂█▂▅▁▂▁▁▁▁\n",
       "Third  Cherbourg         19.0229    10.677941   22.3583    ▁█▃▂▁▄▃▁▂▂\n",
       "       Queenstown        24.0600     9.670476   29.1250    █▁▁▂▁▁▁▂▁▂\n",
       "       Southampton       31.2750    11.501469   69.5500    ▁█▂▂▂▁▁▁▁▁"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "agg_func_largest = {\n",
    "    'fare': [percentile_90, trim_mean_10, largest, sparkline_str]\n",
    "}\n",
    "df.groupby(['class', 'embark_town']).agg(agg_func_largest)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "involved-puppy",
   "metadata": {},
   "source": [
    "Get total fares for top 10 and bottom 10:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "precious-parking",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">fare</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>top_10_sum</th>\n",
       "      <th>bottom_10_sum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>class</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>First</th>\n",
       "      <td>3361.2584</td>\n",
       "      <td>108.3709</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Second</th>\n",
       "      <td>622.2376</td>\n",
       "      <td>42.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Third</th>\n",
       "      <td>656.3374</td>\n",
       "      <td>36.1291</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             fare              \n",
       "       top_10_sum bottom_10_sum\n",
       "class                          \n",
       "First   3361.2584      108.3709\n",
       "Second   622.2376       42.0000\n",
       "Third    656.3374       36.1291"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def top_10_sum(x):\n",
    "    return x.nlargest(10).sum()\n",
    "\n",
    "def bottom_10_sum(x):\n",
    "    return x.nsmallest(10).sum()\n",
    "\n",
    "\n",
    "agg_func_top_bottom_sum = {\n",
    "    'fare': [top_10_sum, bottom_10_sum]\n",
    "}\n",
    "df.groupby('class').agg(agg_func_top_bottom_sum)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "norman-literature",
   "metadata": {},
   "source": [
    "### Custom functions with multiple columns\n",
    "Use groupby combined with apply:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "successful-destiny",
   "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>fare_sum</th>\n",
       "      <th>fare_mean</th>\n",
       "      <th>fare_range</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>class</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>First</th>\n",
       "      <td>18177.0</td>\n",
       "      <td>84.0</td>\n",
       "      <td>512.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Second</th>\n",
       "      <td>3802.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>74.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Third</th>\n",
       "      <td>6715.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        fare_sum  fare_mean  fare_range\n",
       "class                                  \n",
       "First    18177.0       84.0       512.0\n",
       "Second    3802.0       21.0        74.0\n",
       "Third     6715.0       14.0        70.0"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def summary(x):\n",
    "    result = {\n",
    "        'fare_sum': x['fare'].sum(),\n",
    "        'fare_mean': x['fare'].mean(),\n",
    "        'fare_range': x['fare'].max() - x['fare'].min()\n",
    "    }\n",
    "    return pd.Series(result).round(0)\n",
    "\n",
    "df.groupby(['class']).apply(summary)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "julian-civilization",
   "metadata": {},
   "source": [
    "## Working with group objects\n",
    "Figure what percentage of total fares sold can be attributed to each embark_town and class combination (using assign and lambda function to add a pct_total column):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "surprised-metadata",
   "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>fare</th>\n",
       "      <th>pct_total</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>embark_town</th>\n",
       "      <th>class</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Cherbourg</th>\n",
       "      <th>First</th>\n",
       "      <td>8901.0750</td>\n",
       "      <td>0.311947</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Second</th>\n",
       "      <td>431.0917</td>\n",
       "      <td>0.015108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Third</th>\n",
       "      <td>740.1295</td>\n",
       "      <td>0.025939</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Queenstown</th>\n",
       "      <th>First</th>\n",
       "      <td>180.0000</td>\n",
       "      <td>0.006308</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Second</th>\n",
       "      <td>37.0500</td>\n",
       "      <td>0.001298</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Third</th>\n",
       "      <td>805.2043</td>\n",
       "      <td>0.028219</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Southampton</th>\n",
       "      <th>First</th>\n",
       "      <td>8936.3375</td>\n",
       "      <td>0.313183</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Second</th>\n",
       "      <td>3333.7000</td>\n",
       "      <td>0.116833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Third</th>\n",
       "      <td>5169.3613</td>\n",
       "      <td>0.181165</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         fare  pct_total\n",
       "embark_town class                       \n",
       "Cherbourg   First   8901.0750   0.311947\n",
       "            Second   431.0917   0.015108\n",
       "            Third    740.1295   0.025939\n",
       "Queenstown  First    180.0000   0.006308\n",
       "            Second    37.0500   0.001298\n",
       "            Third    805.2043   0.028219\n",
       "Southampton First   8936.3375   0.313183\n",
       "            Second  3333.7000   0.116833\n",
       "            Third   5169.3613   0.181165"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby(['embark_town', 'class']).agg({\n",
    "    'fare': 'sum'\n",
    "}).assign(pct_total=lambda x: x / x.sum())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "coral-austria",
   "metadata": {},
   "source": [
    "Simpler to use [pd.crosstab](https://pbpython.com/pandas-crosstab.html):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "extensive-tomorrow",
   "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>class</th>\n",
       "      <th>First</th>\n",
       "      <th>Second</th>\n",
       "      <th>Third</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>embark_town</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Cherbourg</th>\n",
       "      <td>0.311947</td>\n",
       "      <td>0.015108</td>\n",
       "      <td>0.025939</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Queenstown</th>\n",
       "      <td>0.006308</td>\n",
       "      <td>0.001298</td>\n",
       "      <td>0.028219</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Southampton</th>\n",
       "      <td>0.313183</td>\n",
       "      <td>0.116833</td>\n",
       "      <td>0.181165</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "class           First    Second     Third\n",
       "embark_town                              \n",
       "Cherbourg    0.311947  0.015108  0.025939\n",
       "Queenstown   0.006308  0.001298  0.028219\n",
       "Southampton  0.313183  0.116833  0.181165"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.crosstab(df['embark_town'],\n",
    "            df['class'],\n",
    "            values=df['fare'],\n",
    "            aggfunc='sum',\n",
    "            normalize=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "suitable-consideration",
   "metadata": {},
   "source": [
    "Combine agg functions with pivot table:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "settled-manitoba",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"6\" halign=\"left\">fare</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"3\" halign=\"left\">bottom_10_sum</th>\n",
       "      <th colspan=\"3\" halign=\"left\">top_10_sum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>class</th>\n",
       "      <th>First</th>\n",
       "      <th>Second</th>\n",
       "      <th>Third</th>\n",
       "      <th>First</th>\n",
       "      <th>Second</th>\n",
       "      <th>Third</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>embark_town</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>Cherbourg</th>\n",
       "      <td>282.9957</td>\n",
       "      <td>172.2041</td>\n",
       "      <td>68.2500</td>\n",
       "      <td>3239.3542</td>\n",
       "      <td>334.6084</td>\n",
       "      <td>196.7457</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Queenstown</th>\n",
       "      <td>180.0000</td>\n",
       "      <td>37.0500</td>\n",
       "      <td>73.5916</td>\n",
       "      <td>180.0000</td>\n",
       "      <td>37.0500</td>\n",
       "      <td>264.5750</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Southampton</th>\n",
       "      <td>108.3709</td>\n",
       "      <td>42.0000</td>\n",
       "      <td>39.6291</td>\n",
       "      <td>2237.5251</td>\n",
       "      <td>614.5000</td>\n",
       "      <td>656.3374</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     fare                                                  \n",
       "            bottom_10_sum                    top_10_sum                    \n",
       "class               First    Second    Third      First    Second     Third\n",
       "embark_town                                                                \n",
       "Cherbourg        282.9957  172.2041  68.2500  3239.3542  334.6084  196.7457\n",
       "Queenstown       180.0000   37.0500  73.5916   180.0000   37.0500  264.5750\n",
       "Southampton      108.3709   42.0000  39.6291  2237.5251  614.5000  656.3374"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(data=df,\n",
    "            index=['embark_town'],\n",
    "            columns=['class'],\n",
    "            aggfunc=agg_func_top_bottom_sum)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "thousand-negotiation",
   "metadata": {},
   "source": [
    "Show cumulative total of fares by group and aggregate by town and class, then group:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "electric-significance",
   "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>fare</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>embark_town</th>\n",
       "      <th>class</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Cherbourg</th>\n",
       "      <th>First</th>\n",
       "      <td>8901.0750</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Second</th>\n",
       "      <td>9332.1667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Third</th>\n",
       "      <td>10072.2962</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Queenstown</th>\n",
       "      <th>First</th>\n",
       "      <td>180.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Second</th>\n",
       "      <td>217.0500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Third</th>\n",
       "      <td>1022.2543</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Southampton</th>\n",
       "      <th>First</th>\n",
       "      <td>8936.3375</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Second</th>\n",
       "      <td>12270.0375</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Third</th>\n",
       "      <td>17439.3988</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          fare\n",
       "embark_town class             \n",
       "Cherbourg   First    8901.0750\n",
       "            Second   9332.1667\n",
       "            Third   10072.2962\n",
       "Queenstown  First     180.0000\n",
       "            Second    217.0500\n",
       "            Third    1022.2543\n",
       "Southampton First    8936.3375\n",
       "            Second  12270.0375\n",
       "            Third   17439.3988"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fare_group = df.groupby(['embark_town', 'class']).agg({'fare': 'sum'})\n",
    "fare_group.groupby(level=0).cumsum()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "elementary-briefing",
   "metadata": {},
   "source": [
    "Summarize daily sales and convert to cumulative daily and quarterly view (use [pd.Grouper](https://pbpython.com/pandas-grouper-agg.html)).\n",
    "\n",
    "Here, include total daily sales as well as cumulative quarter amount:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "curious-mason",
   "metadata": {},
   "outputs": [],
   "source": [
    "sales = pd.read_excel('https://github.com/chris1610/pbpython/blob/master/data/2018_Sales_Total_v2.xlsx?raw=True')\n",
    "\n",
    "daily_sales = sales.groupby([pd.Grouper(key='date', freq='D')\n",
    "                            ]).agg(daily_sales=('ext price',\n",
    "                                                'sum')).reset_index()\n",
    "daily_sales['quarter_sales'] = daily_sales.groupby(\n",
    "    pd.Grouper(key='date', freq='Q')).agg({'daily_sales': 'cumsum'})\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "editorial-glucose",
   "metadata": {},
   "source": [
    "Group daily results, then group by quarter and use cumulative sum:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "meaningful-lancaster",
   "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>quarterly_sales</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-01-01</th>\n",
       "      <td>6766.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-02</th>\n",
       "      <td>8318.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-03</th>\n",
       "      <td>12597.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-04</th>\n",
       "      <td>18641.13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-05</th>\n",
       "      <td>20613.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-27</th>\n",
       "      <td>480817.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-28</th>\n",
       "      <td>484389.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-29</th>\n",
       "      <td>489227.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-30</th>\n",
       "      <td>494106.67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-31</th>\n",
       "      <td>496069.30</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>365 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            quarterly_sales\n",
       "date                       \n",
       "2018-01-01          6766.16\n",
       "2018-01-02          8318.07\n",
       "2018-01-03         12597.03\n",
       "2018-01-04         18641.13\n",
       "2018-01-05         20613.07\n",
       "...                     ...\n",
       "2018-12-27        480817.47\n",
       "2018-12-28        484389.92\n",
       "2018-12-29        489227.01\n",
       "2018-12-30        494106.67\n",
       "2018-12-31        496069.30\n",
       "\n",
       "[365 rows x 1 columns]"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sales.groupby([pd.Grouper(key='date', freq='D')\n",
    "            ]).agg(daily_sales=('ext price', 'sum')).groupby(\n",
    "                pd.Grouper(freq='Q')).agg({\n",
    "                    'daily_sales': 'cumsum'\n",
    "                }).rename(columns={'daily_sales': 'quarterly_sales'})"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "compliant-rwanda",
   "metadata": {},
   "source": [
    "## Flattening Hierarchical Column Indices"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "surgical-width",
   "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\">fare</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>sum</th>\n",
       "      <th>mean</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>embark_town</th>\n",
       "      <th>class</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Cherbourg</th>\n",
       "      <th>First</th>\n",
       "      <td>8901.0</td>\n",
       "      <td>105.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Second</th>\n",
       "      <td>431.0</td>\n",
       "      <td>25.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Third</th>\n",
       "      <td>740.0</td>\n",
       "      <td>11.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Queenstown</th>\n",
       "      <th>First</th>\n",
       "      <td>180.0</td>\n",
       "      <td>90.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Second</th>\n",
       "      <td>37.0</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Third</th>\n",
       "      <td>805.0</td>\n",
       "      <td>11.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Southampton</th>\n",
       "      <th>First</th>\n",
       "      <td>8936.0</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Second</th>\n",
       "      <td>3334.0</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Third</th>\n",
       "      <td>5169.0</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      fare       \n",
       "                       sum   mean\n",
       "embark_town class                \n",
       "Cherbourg   First   8901.0  105.0\n",
       "            Second   431.0   25.0\n",
       "            Third    740.0   11.0\n",
       "Queenstown  First    180.0   90.0\n",
       "            Second    37.0   12.0\n",
       "            Third    805.0   11.0\n",
       "Southampton First   8936.0   70.0\n",
       "            Second  3334.0   20.0\n",
       "            Third   5169.0   15.0"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby(['embark_town', 'class']).agg({'fare': ['sum', 'mean']}).round(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "allied-monaco",
   "metadata": {},
   "outputs": [],
   "source": [
    "multi_df = df.groupby(['embark_town', 'class'],\n",
    "                    as_index=False).agg({'fare': ['sum', 'mean']})\n",
    "\n",
    "multi_df.columns = [\n",
    "'_'.join(col).rstrip('_') for col in multi_df.columns.values\n",
    "]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bored-cursor",
   "metadata": {},
   "source": [
    "## Subtotals\n",
    "Add a subtotal using the [sidetable](https://github.com/chris1610/sidetable) package."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "id": "stunning-article",
   "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>fare</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>class</th>\n",
       "      <th>embark_town</th>\n",
       "      <th>sex</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"10\" valign=\"top\">First</th>\n",
       "      <th rowspan=\"3\" valign=\"top\">Cherbourg</th>\n",
       "      <th>female</th>\n",
       "      <td>4972.5333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>male</th>\n",
       "      <td>3928.5417</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>First | Cherbourg - subtotal</th>\n",
       "      <td>8901.0750</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Queenstown</th>\n",
       "      <th>female</th>\n",
       "      <td>90.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>male</th>\n",
       "      <td>90.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>First | Queenstown - subtotal</th>\n",
       "      <td>180.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Southampton</th>\n",
       "      <th>female</th>\n",
       "      <td>4753.2917</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>male</th>\n",
       "      <td>4183.0458</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>First | Southampton - subtotal</th>\n",
       "      <td>8936.3375</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>First - subtotal</th>\n",
       "      <th></th>\n",
       "      <td>18017.4125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"10\" valign=\"top\">Second</th>\n",
       "      <th rowspan=\"3\" valign=\"top\">Cherbourg</th>\n",
       "      <th>female</th>\n",
       "      <td>176.8792</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>male</th>\n",
       "      <td>254.2125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Second | Cherbourg - subtotal</th>\n",
       "      <td>431.0917</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Queenstown</th>\n",
       "      <th>female</th>\n",
       "      <td>24.7000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>male</th>\n",
       "      <td>12.3500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Second | Queenstown - subtotal</th>\n",
       "      <td>37.0500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Southampton</th>\n",
       "      <th>female</th>\n",
       "      <td>1468.1500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>male</th>\n",
       "      <td>1865.5500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Second | Southampton - subtotal</th>\n",
       "      <td>3333.7000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Second - subtotal</th>\n",
       "      <th></th>\n",
       "      <td>3801.8417</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"10\" valign=\"top\">Third</th>\n",
       "      <th rowspan=\"3\" valign=\"top\">Cherbourg</th>\n",
       "      <th>female</th>\n",
       "      <td>337.9833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>male</th>\n",
       "      <td>402.1462</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Third | Cherbourg - subtotal</th>\n",
       "      <td>740.1295</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Queenstown</th>\n",
       "      <th>female</th>\n",
       "      <td>340.1585</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>male</th>\n",
       "      <td>465.0458</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Third | Queenstown - subtotal</th>\n",
       "      <td>805.2043</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Southampton</th>\n",
       "      <th>female</th>\n",
       "      <td>1642.9668</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>male</th>\n",
       "      <td>3526.3945</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Third | Southampton - subtotal</th>\n",
       "      <td>5169.3613</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Third - subtotal</th>\n",
       "      <th></th>\n",
       "      <td>6714.6951</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>grand_total</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <td>28533.9493</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                     fare\n",
       "class       embark_town       sex                                        \n",
       "First       Cherbourg         female                            4972.5333\n",
       "                              male                              3928.5417\n",
       "                              First | Cherbourg - subtotal      8901.0750\n",
       "            Queenstown        female                              90.0000\n",
       "                              male                                90.0000\n",
       "                              First | Queenstown - subtotal      180.0000\n",
       "            Southampton       female                            4753.2917\n",
       "                              male                              4183.0458\n",
       "                              First | Southampton - subtotal    8936.3375\n",
       "            First - subtotal                                   18017.4125\n",
       "Second      Cherbourg         female                             176.8792\n",
       "                              male                               254.2125\n",
       "                              Second | Cherbourg - subtotal      431.0917\n",
       "            Queenstown        female                              24.7000\n",
       "                              male                                12.3500\n",
       "                              Second | Queenstown - subtotal      37.0500\n",
       "            Southampton       female                            1468.1500\n",
       "                              male                              1865.5500\n",
       "                              Second | Southampton - subtotal   3333.7000\n",
       "            Second - subtotal                                   3801.8417\n",
       "Third       Cherbourg         female                             337.9833\n",
       "                              male                               402.1462\n",
       "                              Third | Cherbourg - subtotal       740.1295\n",
       "            Queenstown        female                             340.1585\n",
       "                              male                               465.0458\n",
       "                              Third | Queenstown - subtotal      805.2043\n",
       "            Southampton       female                            1642.9668\n",
       "                              male                              3526.3945\n",
       "                              Third | Southampton - subtotal    5169.3613\n",
       "            Third - subtotal                                    6714.6951\n",
       "grand_total                                                    28533.9493"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import sidetable\n",
    "df.groupby(['class', 'embark_town', 'sex']).agg({'fare': 'sum'}).stb.subtotal()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "devoted-webster",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.9.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}