Pandas/SplitApplyCombine.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pandas Split/Apply/Combine Functionality\n",
    "\n",
    "In this tutorial, we will review how to operate on different portions of your data in different ways. We will also learn how to use more advanced functionality straight in Pandas split/apply/combine api to perform operations. \n",
    "\n",
    "The tutorial will follow:\n",
    "\n",
    "* [Ingesting Data](#ingest)\n",
    "* [Basic Split/Apply/Combine](#basic)\n",
    "* [Enhanced Split/Apply/Combine Features](#enhanced)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import requests\n",
    "import io"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Ingesting Data <a id='ingest' > </a>\n",
    "\n",
    "To keep the tutorial self contained, we will pull our data straight from the web. We will be using the [UCI Machine Learning wine quality dataset](https://archive.ics.uci.edu/ml/datasets/wine+quality). "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# UCI machine learning has a white wine and red wine quality dataset\n",
    "red_url = 'https://archive.ics.uci.edu/ml/machine-learning-databases/wine-quality/winequality-red.csv'\n",
    "white_url = 'https://archive.ics.uci.edu/ml/machine-learning-databases/wine-quality/winequality-white.csv'\n",
    "\n",
    "# use requests to secure the data\n",
    "red_raw = requests.get(red_url).content\n",
    "white_raw = requests.get(white_url).content"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# we can use the io package to decode the data and feed into pandas.\n",
    "# A good discussion on this can be located at: https://stackoverflow.com/questions/32400867/pandas-read-csv-from-url\n",
    "\n",
    "red_df = pd.read_csv(io.StringIO(red_raw.decode('utf-8')), sep=';')\n",
    "white_df = pd.read_csv(io.StringIO(white_raw.decode('utf-8')), sep=';')\n",
    "\n",
    "# assign the type of wine\n",
    "red_df['Type'] = 'Red'\n",
    "white_df['Type'] = 'White'\n",
    "\n",
    "# and finally merge into a single dataset\n",
    "df = pd.concat([red_df, white_df])\n",
    "\n",
    "assert df.shape == (6497, 13), 'Incorrect number of rows obtains'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>fixed acidity</th>\n",
       "      <th>volatile acidity</th>\n",
       "      <th>citric acid</th>\n",
       "      <th>residual sugar</th>\n",
       "      <th>chlorides</th>\n",
       "      <th>free sulfur dioxide</th>\n",
       "      <th>total sulfur dioxide</th>\n",
       "      <th>density</th>\n",
       "      <th>pH</th>\n",
       "      <th>sulphates</th>\n",
       "      <th>alcohol</th>\n",
       "      <th>quality</th>\n",
       "      <th>Type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7.4</td>\n",
       "      <td>0.70</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.9</td>\n",
       "      <td>0.076</td>\n",
       "      <td>11.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>0.9978</td>\n",
       "      <td>3.51</td>\n",
       "      <td>0.56</td>\n",
       "      <td>9.4</td>\n",
       "      <td>5</td>\n",
       "      <td>Red</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7.8</td>\n",
       "      <td>0.88</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2.6</td>\n",
       "      <td>0.098</td>\n",
       "      <td>25.0</td>\n",
       "      <td>67.0</td>\n",
       "      <td>0.9968</td>\n",
       "      <td>3.20</td>\n",
       "      <td>0.68</td>\n",
       "      <td>9.8</td>\n",
       "      <td>5</td>\n",
       "      <td>Red</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7.8</td>\n",
       "      <td>0.76</td>\n",
       "      <td>0.04</td>\n",
       "      <td>2.3</td>\n",
       "      <td>0.092</td>\n",
       "      <td>15.0</td>\n",
       "      <td>54.0</td>\n",
       "      <td>0.9970</td>\n",
       "      <td>3.26</td>\n",
       "      <td>0.65</td>\n",
       "      <td>9.8</td>\n",
       "      <td>5</td>\n",
       "      <td>Red</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>11.2</td>\n",
       "      <td>0.28</td>\n",
       "      <td>0.56</td>\n",
       "      <td>1.9</td>\n",
       "      <td>0.075</td>\n",
       "      <td>17.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>0.9980</td>\n",
       "      <td>3.16</td>\n",
       "      <td>0.58</td>\n",
       "      <td>9.8</td>\n",
       "      <td>6</td>\n",
       "      <td>Red</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7.4</td>\n",
       "      <td>0.70</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.9</td>\n",
       "      <td>0.076</td>\n",
       "      <td>11.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>0.9978</td>\n",
       "      <td>3.51</td>\n",
       "      <td>0.56</td>\n",
       "      <td>9.4</td>\n",
       "      <td>5</td>\n",
       "      <td>Red</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>7.4</td>\n",
       "      <td>0.66</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.8</td>\n",
       "      <td>0.075</td>\n",
       "      <td>13.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>0.9978</td>\n",
       "      <td>3.51</td>\n",
       "      <td>0.56</td>\n",
       "      <td>9.4</td>\n",
       "      <td>5</td>\n",
       "      <td>Red</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7.9</td>\n",
       "      <td>0.60</td>\n",
       "      <td>0.06</td>\n",
       "      <td>1.6</td>\n",
       "      <td>0.069</td>\n",
       "      <td>15.0</td>\n",
       "      <td>59.0</td>\n",
       "      <td>0.9964</td>\n",
       "      <td>3.30</td>\n",
       "      <td>0.46</td>\n",
       "      <td>9.4</td>\n",
       "      <td>5</td>\n",
       "      <td>Red</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7.3</td>\n",
       "      <td>0.65</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.2</td>\n",
       "      <td>0.065</td>\n",
       "      <td>15.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>0.9946</td>\n",
       "      <td>3.39</td>\n",
       "      <td>0.47</td>\n",
       "      <td>10.0</td>\n",
       "      <td>7</td>\n",
       "      <td>Red</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>7.8</td>\n",
       "      <td>0.58</td>\n",
       "      <td>0.02</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.073</td>\n",
       "      <td>9.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>0.9968</td>\n",
       "      <td>3.36</td>\n",
       "      <td>0.57</td>\n",
       "      <td>9.5</td>\n",
       "      <td>7</td>\n",
       "      <td>Red</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>7.5</td>\n",
       "      <td>0.50</td>\n",
       "      <td>0.36</td>\n",
       "      <td>6.1</td>\n",
       "      <td>0.071</td>\n",
       "      <td>17.0</td>\n",
       "      <td>102.0</td>\n",
       "      <td>0.9978</td>\n",
       "      <td>3.35</td>\n",
       "      <td>0.80</td>\n",
       "      <td>10.5</td>\n",
       "      <td>5</td>\n",
       "      <td>Red</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   fixed acidity  volatile acidity  citric acid  residual sugar  chlorides  \\\n",
       "0            7.4              0.70         0.00             1.9      0.076   \n",
       "1            7.8              0.88         0.00             2.6      0.098   \n",
       "2            7.8              0.76         0.04             2.3      0.092   \n",
       "3           11.2              0.28         0.56             1.9      0.075   \n",
       "4            7.4              0.70         0.00             1.9      0.076   \n",
       "5            7.4              0.66         0.00             1.8      0.075   \n",
       "6            7.9              0.60         0.06             1.6      0.069   \n",
       "7            7.3              0.65         0.00             1.2      0.065   \n",
       "8            7.8              0.58         0.02             2.0      0.073   \n",
       "9            7.5              0.50         0.36             6.1      0.071   \n",
       "\n",
       "   free sulfur dioxide  total sulfur dioxide  density    pH  sulphates  \\\n",
       "0                 11.0                  34.0   0.9978  3.51       0.56   \n",
       "1                 25.0                  67.0   0.9968  3.20       0.68   \n",
       "2                 15.0                  54.0   0.9970  3.26       0.65   \n",
       "3                 17.0                  60.0   0.9980  3.16       0.58   \n",
       "4                 11.0                  34.0   0.9978  3.51       0.56   \n",
       "5                 13.0                  40.0   0.9978  3.51       0.56   \n",
       "6                 15.0                  59.0   0.9964  3.30       0.46   \n",
       "7                 15.0                  21.0   0.9946  3.39       0.47   \n",
       "8                  9.0                  18.0   0.9968  3.36       0.57   \n",
       "9                 17.0                 102.0   0.9978  3.35       0.80   \n",
       "\n",
       "   alcohol  quality Type  \n",
       "0      9.4        5  Red  \n",
       "1      9.8        5  Red  \n",
       "2      9.8        5  Red  \n",
       "3      9.8        6  Red  \n",
       "4      9.4        5  Red  \n",
       "5      9.4        5  Red  \n",
       "6      9.4        5  Red  \n",
       "7     10.0        7  Red  \n",
       "8      9.5        7  Red  \n",
       "9     10.5        5  Red  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    6497.000000\n",
       "mean       10.491801\n",
       "std         1.192712\n",
       "min         8.000000\n",
       "25%         9.500000\n",
       "50%        10.300000\n",
       "75%        11.300000\n",
       "max        14.900000\n",
       "Name: alcohol, dtype: float64"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# we will bin the alcohol content into low, medium, high to further illustrate pandas split/apply/combine functionality\n",
    "df['alcohol'].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create the three bins\n",
    "df['alcoholbins'] = pd.cut(df['alcohol'], 3, labels=['low', 'medium', 'high'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Basic split/apply/combine functionality <a id='basic' ></a>\n",
    "\n",
    "Based on the bins above, we may be interested in understanding what is the average alcohol content across low, medium, and high. We can start to use pandas built in functionality for this. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "alcoholbins\n",
       "low        9.514633\n",
       "medium    11.302820\n",
       "high      13.052491\n",
       "Name: alcohol, dtype: float64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# get the mean alcohol content for low, medium, high bins\n",
    "df.groupby('alcoholbins')['alcohol'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "alcoholbins\n",
       "low       3295\n",
       "medium    2846\n",
       "high       356\n",
       "Name: alcohol, dtype: int64"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# and lets see how many observations there are under each\n",
    "df.groupby('alcoholbins')['alcohol'].count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "low\n",
      "medium\n",
      "high\n"
     ]
    }
   ],
   "source": [
    "# you can also iterate over a groupby in a regular for loop\n",
    "for name, group in df.groupby('alcoholbins'):\n",
    "    print(name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "---------------\n",
      "Current group: low\n",
      "   fixed acidity  volatile acidity  citric acid  residual sugar  chlorides  \\\n",
      "0            7.4              0.70          0.0             1.9      0.076   \n",
      "1            7.8              0.88          0.0             2.6      0.098   \n",
      "\n",
      "   free sulfur dioxide  total sulfur dioxide  density    pH  sulphates  \\\n",
      "0                 11.0                  34.0   0.9978  3.51       0.56   \n",
      "1                 25.0                  67.0   0.9968  3.20       0.68   \n",
      "\n",
      "   alcohol  quality Type alcoholbins  \n",
      "0      9.4        5  Red         low  \n",
      "1      9.8        5  Red         low  \n",
      "---------------\n",
      "Current group: medium\n",
      "    fixed acidity  volatile acidity  citric acid  residual sugar  chlorides  \\\n",
      "9             7.5               0.5         0.36             6.1      0.071   \n",
      "11            7.5               0.5         0.36             6.1      0.071   \n",
      "\n",
      "    free sulfur dioxide  total sulfur dioxide  density    pH  sulphates  \\\n",
      "9                  17.0                 102.0   0.9978  3.35        0.8   \n",
      "11                 17.0                 102.0   0.9978  3.35        0.8   \n",
      "\n",
      "    alcohol  quality Type alcoholbins  \n",
      "9      10.5        5  Red      medium  \n",
      "11     10.5        5  Red      medium  \n",
      "---------------\n",
      "Current group: high\n",
      "    fixed acidity  volatile acidity  citric acid  residual sugar  chlorides  \\\n",
      "45            4.6              0.52         0.15             2.1      0.054   \n",
      "95            4.7              0.60         0.17             2.3      0.058   \n",
      "\n",
      "    free sulfur dioxide  total sulfur dioxide  density    pH  sulphates  \\\n",
      "45                  8.0                  65.0   0.9934  3.90       0.56   \n",
      "95                 17.0                 106.0   0.9932  3.85       0.60   \n",
      "\n",
      "    alcohol  quality Type alcoholbins  \n",
      "45     13.1        4  Red        high  \n",
      "95     12.9        6  Red        high  \n"
     ]
    }
   ],
   "source": [
    "# and to see further what is happening, each group assigned to the group variable in the for loop \n",
    "# is your actual data for that group. You can see this by peeking into the groups:\n",
    "\n",
    "for name, group in df.groupby('alcoholbins'):\n",
    "    print('---------------')\n",
    "    print('Current group: {}'.format(name))\n",
    "    print(group.head(2))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>fixed acidity</th>\n",
       "      <th>volatile acidity</th>\n",
       "      <th>citric acid</th>\n",
       "      <th>residual sugar</th>\n",
       "      <th>chlorides</th>\n",
       "      <th>free sulfur dioxide</th>\n",
       "      <th>total sulfur dioxide</th>\n",
       "      <th>density</th>\n",
       "      <th>pH</th>\n",
       "      <th>sulphates</th>\n",
       "      <th>alcohol</th>\n",
       "      <th>quality</th>\n",
       "      <th>Type</th>\n",
       "      <th>alcoholbins</th>\n",
       "      <th>alcoholcount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7.4</td>\n",
       "      <td>0.70</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.9</td>\n",
       "      <td>0.076</td>\n",
       "      <td>11.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>0.9978</td>\n",
       "      <td>3.51</td>\n",
       "      <td>0.56</td>\n",
       "      <td>9.4</td>\n",
       "      <td>5</td>\n",
       "      <td>Red</td>\n",
       "      <td>low</td>\n",
       "      <td>3295</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7.8</td>\n",
       "      <td>0.88</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2.6</td>\n",
       "      <td>0.098</td>\n",
       "      <td>25.0</td>\n",
       "      <td>67.0</td>\n",
       "      <td>0.9968</td>\n",
       "      <td>3.20</td>\n",
       "      <td>0.68</td>\n",
       "      <td>9.8</td>\n",
       "      <td>5</td>\n",
       "      <td>Red</td>\n",
       "      <td>low</td>\n",
       "      <td>3295</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7.8</td>\n",
       "      <td>0.76</td>\n",
       "      <td>0.04</td>\n",
       "      <td>2.3</td>\n",
       "      <td>0.092</td>\n",
       "      <td>15.0</td>\n",
       "      <td>54.0</td>\n",
       "      <td>0.9970</td>\n",
       "      <td>3.26</td>\n",
       "      <td>0.65</td>\n",
       "      <td>9.8</td>\n",
       "      <td>5</td>\n",
       "      <td>Red</td>\n",
       "      <td>low</td>\n",
       "      <td>3295</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>11.2</td>\n",
       "      <td>0.28</td>\n",
       "      <td>0.56</td>\n",
       "      <td>1.9</td>\n",
       "      <td>0.075</td>\n",
       "      <td>17.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>0.9980</td>\n",
       "      <td>3.16</td>\n",
       "      <td>0.58</td>\n",
       "      <td>9.8</td>\n",
       "      <td>6</td>\n",
       "      <td>Red</td>\n",
       "      <td>low</td>\n",
       "      <td>3295</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7.4</td>\n",
       "      <td>0.70</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.9</td>\n",
       "      <td>0.076</td>\n",
       "      <td>11.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>0.9978</td>\n",
       "      <td>3.51</td>\n",
       "      <td>0.56</td>\n",
       "      <td>9.4</td>\n",
       "      <td>5</td>\n",
       "      <td>Red</td>\n",
       "      <td>low</td>\n",
       "      <td>3295</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   fixed acidity  volatile acidity  citric acid  residual sugar  chlorides  \\\n",
       "0            7.4              0.70         0.00             1.9      0.076   \n",
       "1            7.8              0.88         0.00             2.6      0.098   \n",
       "2            7.8              0.76         0.04             2.3      0.092   \n",
       "3           11.2              0.28         0.56             1.9      0.075   \n",
       "4            7.4              0.70         0.00             1.9      0.076   \n",
       "\n",
       "   free sulfur dioxide  total sulfur dioxide  density    pH  sulphates  \\\n",
       "0                 11.0                  34.0   0.9978  3.51       0.56   \n",
       "1                 25.0                  67.0   0.9968  3.20       0.68   \n",
       "2                 15.0                  54.0   0.9970  3.26       0.65   \n",
       "3                 17.0                  60.0   0.9980  3.16       0.58   \n",
       "4                 11.0                  34.0   0.9978  3.51       0.56   \n",
       "\n",
       "   alcohol  quality Type alcoholbins  alcoholcount  \n",
       "0      9.4        5  Red         low          3295  \n",
       "1      9.8        5  Red         low          3295  \n",
       "2      9.8        5  Red         low          3295  \n",
       "3      9.8        6  Red         low          3295  \n",
       "4      9.4        5  Red         low          3295  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# occasionally, you will want to derive new features using aggregate values for a group. In order to assign these\n",
    "# values back to the original dataframe, we can reset the index and merge back in using the following:\n",
    "\n",
    "# create a copy of the dataframe\n",
    "dfcopy = df.copy(deep=True)\n",
    "\n",
    "# get the counts by group, reset the idnex, and give the column a more meaningful name\n",
    "alc_counts = df.groupby('alcoholbins')['alcohol'].count().reset_index().rename(columns={'alcohol': 'alcoholcount'})\n",
    "\n",
    "# merge back in with original data\n",
    "dfcopy = pd.merge(dfcopy, alc_counts, on='alcoholbins', how='inner')\n",
    "\n",
    "# and now we have assigned the total observations per alcohol bin to all rows of the dataframe\n",
    "dfcopy.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "9.514633282751674"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# to unpack the groupby mean, it is the same as doing the following except for all groups\n",
    "\n",
    "df[df['alcoholbins'] == 'low'].loc[:, 'alcohol'].mean() # which matches our original groupby value for the low bin"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Type   alcoholbins\n",
       "Red    low             9.621848\n",
       "       medium         11.223283\n",
       "       high           13.122917\n",
       "White  low             9.475868\n",
       "       medium         11.326834\n",
       "       high           13.037055\n",
       "Name: alcohol, dtype: float64"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# now what if we were interested in the average alcohol content by alcohol bin and by type of wine?\n",
    "# simply add the additional groupby variable\n",
    "df.groupby(['Type', 'alcoholbins'])['alcohol'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "9.621847619047628"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# this is the same as the following\n",
    "df[(df['Type'] == 'Red') & (df['alcoholbins'] == 'low')].loc[:, 'alcohol'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Type</th>\n",
       "      <th>alcoholbins</th>\n",
       "      <th>chl_mean</th>\n",
       "      <th>acid_std</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Red</td>\n",
       "      <td>low</td>\n",
       "      <td>0.095206</td>\n",
       "      <td>0.164005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Red</td>\n",
       "      <td>medium</td>\n",
       "      <td>0.079427</td>\n",
       "      <td>0.191759</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Red</td>\n",
       "      <td>high</td>\n",
       "      <td>0.064563</td>\n",
       "      <td>0.157561</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>White</td>\n",
       "      <td>low</td>\n",
       "      <td>0.053418</td>\n",
       "      <td>0.102411</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>White</td>\n",
       "      <td>medium</td>\n",
       "      <td>0.038816</td>\n",
       "      <td>0.093599</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Type alcoholbins  chl_mean  acid_std\n",
       "0    Red         low  0.095206  0.164005\n",
       "1    Red      medium  0.079427  0.191759\n",
       "2    Red        high  0.064563  0.157561\n",
       "3  White         low  0.053418  0.102411\n",
       "4  White      medium  0.038816  0.093599"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# sometimes we may be interested in different types of aggregation methods based on the columns.\n",
    "# for instance, if we want to find the average chlorides by group by the standard deviation for volatile acidity\n",
    "# we can use:\n",
    "\n",
    "res = df.groupby(['Type', 'alcoholbins']).agg({'chlorides': 'mean',\n",
    "                                        'volatile acidity': 'std'}).reset_index().rename(columns={'chlorides': 'chl_mean',\n",
    "                                                                                         'volatile acidity': 'acid_std'})\n",
    "\n",
    "res.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>fixed acidity</th>\n",
       "      <th>volatile acidity</th>\n",
       "      <th>citric acid</th>\n",
       "      <th>residual sugar</th>\n",
       "      <th>chlorides</th>\n",
       "      <th>free sulfur dioxide</th>\n",
       "      <th>total sulfur dioxide</th>\n",
       "      <th>density</th>\n",
       "      <th>pH</th>\n",
       "      <th>sulphates</th>\n",
       "      <th>alcohol</th>\n",
       "      <th>quality</th>\n",
       "      <th>Type</th>\n",
       "      <th>alcoholbins</th>\n",
       "      <th>alcoholcount</th>\n",
       "      <th>chl_mean</th>\n",
       "      <th>acid_std</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7.4</td>\n",
       "      <td>0.70</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.9</td>\n",
       "      <td>0.076</td>\n",
       "      <td>11.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>0.9978</td>\n",
       "      <td>3.51</td>\n",
       "      <td>0.56</td>\n",
       "      <td>9.4</td>\n",
       "      <td>5</td>\n",
       "      <td>Red</td>\n",
       "      <td>low</td>\n",
       "      <td>3295</td>\n",
       "      <td>0.095206</td>\n",
       "      <td>0.164005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7.8</td>\n",
       "      <td>0.88</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2.6</td>\n",
       "      <td>0.098</td>\n",
       "      <td>25.0</td>\n",
       "      <td>67.0</td>\n",
       "      <td>0.9968</td>\n",
       "      <td>3.20</td>\n",
       "      <td>0.68</td>\n",
       "      <td>9.8</td>\n",
       "      <td>5</td>\n",
       "      <td>Red</td>\n",
       "      <td>low</td>\n",
       "      <td>3295</td>\n",
       "      <td>0.095206</td>\n",
       "      <td>0.164005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7.8</td>\n",
       "      <td>0.76</td>\n",
       "      <td>0.04</td>\n",
       "      <td>2.3</td>\n",
       "      <td>0.092</td>\n",
       "      <td>15.0</td>\n",
       "      <td>54.0</td>\n",
       "      <td>0.9970</td>\n",
       "      <td>3.26</td>\n",
       "      <td>0.65</td>\n",
       "      <td>9.8</td>\n",
       "      <td>5</td>\n",
       "      <td>Red</td>\n",
       "      <td>low</td>\n",
       "      <td>3295</td>\n",
       "      <td>0.095206</td>\n",
       "      <td>0.164005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>11.2</td>\n",
       "      <td>0.28</td>\n",
       "      <td>0.56</td>\n",
       "      <td>1.9</td>\n",
       "      <td>0.075</td>\n",
       "      <td>17.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>0.9980</td>\n",
       "      <td>3.16</td>\n",
       "      <td>0.58</td>\n",
       "      <td>9.8</td>\n",
       "      <td>6</td>\n",
       "      <td>Red</td>\n",
       "      <td>low</td>\n",
       "      <td>3295</td>\n",
       "      <td>0.095206</td>\n",
       "      <td>0.164005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7.4</td>\n",
       "      <td>0.70</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.9</td>\n",
       "      <td>0.076</td>\n",
       "      <td>11.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>0.9978</td>\n",
       "      <td>3.51</td>\n",
       "      <td>0.56</td>\n",
       "      <td>9.4</td>\n",
       "      <td>5</td>\n",
       "      <td>Red</td>\n",
       "      <td>low</td>\n",
       "      <td>3295</td>\n",
       "      <td>0.095206</td>\n",
       "      <td>0.164005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>7.4</td>\n",
       "      <td>0.66</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.8</td>\n",
       "      <td>0.075</td>\n",
       "      <td>13.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>0.9978</td>\n",
       "      <td>3.51</td>\n",
       "      <td>0.56</td>\n",
       "      <td>9.4</td>\n",
       "      <td>5</td>\n",
       "      <td>Red</td>\n",
       "      <td>low</td>\n",
       "      <td>3295</td>\n",
       "      <td>0.095206</td>\n",
       "      <td>0.164005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7.9</td>\n",
       "      <td>0.60</td>\n",
       "      <td>0.06</td>\n",
       "      <td>1.6</td>\n",
       "      <td>0.069</td>\n",
       "      <td>15.0</td>\n",
       "      <td>59.0</td>\n",
       "      <td>0.9964</td>\n",
       "      <td>3.30</td>\n",
       "      <td>0.46</td>\n",
       "      <td>9.4</td>\n",
       "      <td>5</td>\n",
       "      <td>Red</td>\n",
       "      <td>low</td>\n",
       "      <td>3295</td>\n",
       "      <td>0.095206</td>\n",
       "      <td>0.164005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7.3</td>\n",
       "      <td>0.65</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.2</td>\n",
       "      <td>0.065</td>\n",
       "      <td>15.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>0.9946</td>\n",
       "      <td>3.39</td>\n",
       "      <td>0.47</td>\n",
       "      <td>10.0</td>\n",
       "      <td>7</td>\n",
       "      <td>Red</td>\n",
       "      <td>low</td>\n",
       "      <td>3295</td>\n",
       "      <td>0.095206</td>\n",
       "      <td>0.164005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>7.8</td>\n",
       "      <td>0.58</td>\n",
       "      <td>0.02</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.073</td>\n",
       "      <td>9.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>0.9968</td>\n",
       "      <td>3.36</td>\n",
       "      <td>0.57</td>\n",
       "      <td>9.5</td>\n",
       "      <td>7</td>\n",
       "      <td>Red</td>\n",
       "      <td>low</td>\n",
       "      <td>3295</td>\n",
       "      <td>0.095206</td>\n",
       "      <td>0.164005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>6.7</td>\n",
       "      <td>0.58</td>\n",
       "      <td>0.08</td>\n",
       "      <td>1.8</td>\n",
       "      <td>0.097</td>\n",
       "      <td>15.0</td>\n",
       "      <td>65.0</td>\n",
       "      <td>0.9959</td>\n",
       "      <td>3.28</td>\n",
       "      <td>0.54</td>\n",
       "      <td>9.2</td>\n",
       "      <td>5</td>\n",
       "      <td>Red</td>\n",
       "      <td>low</td>\n",
       "      <td>3295</td>\n",
       "      <td>0.095206</td>\n",
       "      <td>0.164005</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   fixed acidity  volatile acidity  citric acid  residual sugar  chlorides  \\\n",
       "0            7.4              0.70         0.00             1.9      0.076   \n",
       "1            7.8              0.88         0.00             2.6      0.098   \n",
       "2            7.8              0.76         0.04             2.3      0.092   \n",
       "3           11.2              0.28         0.56             1.9      0.075   \n",
       "4            7.4              0.70         0.00             1.9      0.076   \n",
       "5            7.4              0.66         0.00             1.8      0.075   \n",
       "6            7.9              0.60         0.06             1.6      0.069   \n",
       "7            7.3              0.65         0.00             1.2      0.065   \n",
       "8            7.8              0.58         0.02             2.0      0.073   \n",
       "9            6.7              0.58         0.08             1.8      0.097   \n",
       "\n",
       "   free sulfur dioxide  total sulfur dioxide  density    pH  sulphates  \\\n",
       "0                 11.0                  34.0   0.9978  3.51       0.56   \n",
       "1                 25.0                  67.0   0.9968  3.20       0.68   \n",
       "2                 15.0                  54.0   0.9970  3.26       0.65   \n",
       "3                 17.0                  60.0   0.9980  3.16       0.58   \n",
       "4                 11.0                  34.0   0.9978  3.51       0.56   \n",
       "5                 13.0                  40.0   0.9978  3.51       0.56   \n",
       "6                 15.0                  59.0   0.9964  3.30       0.46   \n",
       "7                 15.0                  21.0   0.9946  3.39       0.47   \n",
       "8                  9.0                  18.0   0.9968  3.36       0.57   \n",
       "9                 15.0                  65.0   0.9959  3.28       0.54   \n",
       "\n",
       "   alcohol  quality Type alcoholbins  alcoholcount  chl_mean  acid_std  \n",
       "0      9.4        5  Red         low          3295  0.095206  0.164005  \n",
       "1      9.8        5  Red         low          3295  0.095206  0.164005  \n",
       "2      9.8        5  Red         low          3295  0.095206  0.164005  \n",
       "3      9.8        6  Red         low          3295  0.095206  0.164005  \n",
       "4      9.4        5  Red         low          3295  0.095206  0.164005  \n",
       "5      9.4        5  Red         low          3295  0.095206  0.164005  \n",
       "6      9.4        5  Red         low          3295  0.095206  0.164005  \n",
       "7     10.0        7  Red         low          3295  0.095206  0.164005  \n",
       "8      9.5        7  Red         low          3295  0.095206  0.164005  \n",
       "9      9.2        5  Red         low          3295  0.095206  0.164005  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# and to merge back in with the original data\n",
    "dfcopy = pd.merge(dfcopy, res, on=['Type', 'alcoholbins'], how='inner')\n",
    "\n",
    "dfcopy.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>fixed acidity</th>\n",
       "      <th>volatile acidity</th>\n",
       "      <th>citric acid</th>\n",
       "      <th>residual sugar</th>\n",
       "      <th>chlorides</th>\n",
       "      <th>free sulfur dioxide</th>\n",
       "      <th>total sulfur dioxide</th>\n",
       "      <th>density</th>\n",
       "      <th>pH</th>\n",
       "      <th>sulphates</th>\n",
       "      <th>alcohol</th>\n",
       "      <th>quality</th>\n",
       "      <th>Type</th>\n",
       "      <th>alcoholbins</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7.0</td>\n",
       "      <td>0.27</td>\n",
       "      <td>0.36</td>\n",
       "      <td>20.7</td>\n",
       "      <td>0.045</td>\n",
       "      <td>45.0</td>\n",
       "      <td>170.0</td>\n",
       "      <td>1.0010</td>\n",
       "      <td>3.00</td>\n",
       "      <td>0.45</td>\n",
       "      <td>8.8</td>\n",
       "      <td>6</td>\n",
       "      <td>White</td>\n",
       "      <td>low</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6.3</td>\n",
       "      <td>0.30</td>\n",
       "      <td>0.34</td>\n",
       "      <td>1.6</td>\n",
       "      <td>0.049</td>\n",
       "      <td>14.0</td>\n",
       "      <td>132.0</td>\n",
       "      <td>0.9940</td>\n",
       "      <td>3.30</td>\n",
       "      <td>0.49</td>\n",
       "      <td>9.5</td>\n",
       "      <td>6</td>\n",
       "      <td>White</td>\n",
       "      <td>low</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8.1</td>\n",
       "      <td>0.28</td>\n",
       "      <td>0.40</td>\n",
       "      <td>6.9</td>\n",
       "      <td>0.050</td>\n",
       "      <td>30.0</td>\n",
       "      <td>97.0</td>\n",
       "      <td>0.9951</td>\n",
       "      <td>3.26</td>\n",
       "      <td>0.44</td>\n",
       "      <td>10.1</td>\n",
       "      <td>6</td>\n",
       "      <td>White</td>\n",
       "      <td>low</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7.2</td>\n",
       "      <td>0.23</td>\n",
       "      <td>0.32</td>\n",
       "      <td>8.5</td>\n",
       "      <td>0.058</td>\n",
       "      <td>47.0</td>\n",
       "      <td>186.0</td>\n",
       "      <td>0.9956</td>\n",
       "      <td>3.19</td>\n",
       "      <td>0.40</td>\n",
       "      <td>9.9</td>\n",
       "      <td>6</td>\n",
       "      <td>White</td>\n",
       "      <td>low</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7.2</td>\n",
       "      <td>0.23</td>\n",
       "      <td>0.32</td>\n",
       "      <td>8.5</td>\n",
       "      <td>0.058</td>\n",
       "      <td>47.0</td>\n",
       "      <td>186.0</td>\n",
       "      <td>0.9956</td>\n",
       "      <td>3.19</td>\n",
       "      <td>0.40</td>\n",
       "      <td>9.9</td>\n",
       "      <td>6</td>\n",
       "      <td>White</td>\n",
       "      <td>low</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   fixed acidity  volatile acidity  citric acid  residual sugar  chlorides  \\\n",
       "0            7.0              0.27         0.36            20.7      0.045   \n",
       "1            6.3              0.30         0.34             1.6      0.049   \n",
       "2            8.1              0.28         0.40             6.9      0.050   \n",
       "3            7.2              0.23         0.32             8.5      0.058   \n",
       "4            7.2              0.23         0.32             8.5      0.058   \n",
       "\n",
       "   free sulfur dioxide  total sulfur dioxide  density    pH  sulphates  \\\n",
       "0                 45.0                 170.0   1.0010  3.00       0.45   \n",
       "1                 14.0                 132.0   0.9940  3.30       0.49   \n",
       "2                 30.0                  97.0   0.9951  3.26       0.44   \n",
       "3                 47.0                 186.0   0.9956  3.19       0.40   \n",
       "4                 47.0                 186.0   0.9956  3.19       0.40   \n",
       "\n",
       "   alcohol  quality   Type alcoholbins  \n",
       "0      8.8        6  White         low  \n",
       "1      9.5        6  White         low  \n",
       "2     10.1        6  White         low  \n",
       "3      9.9        6  White         low  \n",
       "4      9.9        6  White         low  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# we can also retrieve specific groups after a groupby has been applied\n",
    "\n",
    "groups = df.groupby(['Type', 'alcoholbins']).get_group(('White', 'low'))\n",
    "\n",
    "groups.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Enhanced Split/Apply/Combine Functionality <a id='enhanced' ></a>\n",
    "\n",
    "Using basic pandas functionality is great, however it can only get you so far. Sometime we may want to perform very specific operations on groups of our data. To start with custom functions, we can do the easiest case where there is only one parameter to the function, which is the group itself. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def custom_group_func(group):\n",
    "    \"\"\"\n",
    "    perform custom operation on group of data. Here we will tag quality groups defined as average quality\n",
    "    score above 9\n",
    "    \"\"\"\n",
    "    group_mean = group['quality'].mean()\n",
    "    if group_mean > 6:\n",
    "        group['Quals'] = 'High Quality Group'\n",
    "    else:\n",
    "        group['Quals'] = 'Regular Quality Group'\n",
    "    return group.reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>fixed acidity</th>\n",
       "      <th>volatile acidity</th>\n",
       "      <th>citric acid</th>\n",
       "      <th>residual sugar</th>\n",
       "      <th>chlorides</th>\n",
       "      <th>free sulfur dioxide</th>\n",
       "      <th>total sulfur dioxide</th>\n",
       "      <th>density</th>\n",
       "      <th>pH</th>\n",
       "      <th>sulphates</th>\n",
       "      <th>alcohol</th>\n",
       "      <th>quality</th>\n",
       "      <th>Type</th>\n",
       "      <th>alcoholbins</th>\n",
       "      <th>Quals</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7.4</td>\n",
       "      <td>0.70</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.9</td>\n",
       "      <td>0.076</td>\n",
       "      <td>11.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>0.9978</td>\n",
       "      <td>3.51</td>\n",
       "      <td>0.56</td>\n",
       "      <td>9.4</td>\n",
       "      <td>5</td>\n",
       "      <td>Red</td>\n",
       "      <td>low</td>\n",
       "      <td>Regular Quality Group</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7.8</td>\n",
       "      <td>0.88</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2.6</td>\n",
       "      <td>0.098</td>\n",
       "      <td>25.0</td>\n",
       "      <td>67.0</td>\n",
       "      <td>0.9968</td>\n",
       "      <td>3.20</td>\n",
       "      <td>0.68</td>\n",
       "      <td>9.8</td>\n",
       "      <td>5</td>\n",
       "      <td>Red</td>\n",
       "      <td>low</td>\n",
       "      <td>Regular Quality Group</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7.8</td>\n",
       "      <td>0.76</td>\n",
       "      <td>0.04</td>\n",
       "      <td>2.3</td>\n",
       "      <td>0.092</td>\n",
       "      <td>15.0</td>\n",
       "      <td>54.0</td>\n",
       "      <td>0.9970</td>\n",
       "      <td>3.26</td>\n",
       "      <td>0.65</td>\n",
       "      <td>9.8</td>\n",
       "      <td>5</td>\n",
       "      <td>Red</td>\n",
       "      <td>low</td>\n",
       "      <td>Regular Quality Group</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>11.2</td>\n",
       "      <td>0.28</td>\n",
       "      <td>0.56</td>\n",
       "      <td>1.9</td>\n",
       "      <td>0.075</td>\n",
       "      <td>17.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>0.9980</td>\n",
       "      <td>3.16</td>\n",
       "      <td>0.58</td>\n",
       "      <td>9.8</td>\n",
       "      <td>6</td>\n",
       "      <td>Red</td>\n",
       "      <td>low</td>\n",
       "      <td>Regular Quality Group</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7.4</td>\n",
       "      <td>0.70</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.9</td>\n",
       "      <td>0.076</td>\n",
       "      <td>11.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>0.9978</td>\n",
       "      <td>3.51</td>\n",
       "      <td>0.56</td>\n",
       "      <td>9.4</td>\n",
       "      <td>5</td>\n",
       "      <td>Red</td>\n",
       "      <td>low</td>\n",
       "      <td>Regular Quality Group</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   fixed acidity  volatile acidity  citric acid  residual sugar  chlorides  \\\n",
       "0            7.4              0.70         0.00             1.9      0.076   \n",
       "1            7.8              0.88         0.00             2.6      0.098   \n",
       "2            7.8              0.76         0.04             2.3      0.092   \n",
       "3           11.2              0.28         0.56             1.9      0.075   \n",
       "4            7.4              0.70         0.00             1.9      0.076   \n",
       "\n",
       "   free sulfur dioxide  total sulfur dioxide  density    pH  sulphates  \\\n",
       "0                 11.0                  34.0   0.9978  3.51       0.56   \n",
       "1                 25.0                  67.0   0.9968  3.20       0.68   \n",
       "2                 15.0                  54.0   0.9970  3.26       0.65   \n",
       "3                 17.0                  60.0   0.9980  3.16       0.58   \n",
       "4                 11.0                  34.0   0.9978  3.51       0.56   \n",
       "\n",
       "   alcohol  quality Type alcoholbins                  Quals  \n",
       "0      9.4        5  Red         low  Regular Quality Group  \n",
       "1      9.8        5  Red         low  Regular Quality Group  \n",
       "2      9.8        5  Red         low  Regular Quality Group  \n",
       "3      9.8        6  Red         low  Regular Quality Group  \n",
       "4      9.4        5  Red         low  Regular Quality Group  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "res = pd.concat([df.groupby(['Type', 'alcoholbins']).apply(custom_group_func)]).reset_index(drop=True)\n",
    "res.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Type</th>\n",
       "      <th>alcoholbins</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1535</th>\n",
       "      <td>Red</td>\n",
       "      <td>high</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4019</th>\n",
       "      <td>White</td>\n",
       "      <td>medium</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6205</th>\n",
       "      <td>White</td>\n",
       "      <td>high</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Type alcoholbins\n",
       "1535    Red        high\n",
       "4019  White      medium\n",
       "6205  White        high"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# So what ar ethe high quality groups?\n",
    "res[res['Quals'].str.contains('High')][['Type', 'alcoholbins']].drop_duplicates()\n",
    "# Red Wine with high alcohol\n",
    "# white wine with medium alcohol\n",
    "# White wine with high alcohol\n",
    "# seems as if people really enjoy their high alcohol content"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Average Quality: 5.818377712790519\n",
      "        \n",
      "--------------\n",
      "Quality Groups Standard Deviation: 0.873255271531111\n",
      "        \n",
      "--------------\n",
      "alcoholbins\n",
      "low       5.484674\n",
      "medium    6.099789\n",
      "high      6.657303\n",
      "Name: quality, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "# just out of pure curiousity, we can see the average quality by alcohol content\n",
    "qual_means_groups = df.groupby('alcoholbins')['quality'].mean()\n",
    "qual_mean = df['quality'].mean()\n",
    "std = df['quality'].std()\n",
    "print(\"\"\"Average Quality: {}\n",
    "        \\n--------------\\nQuality Groups Standard Deviation: {}\n",
    "        \\n--------------\\n{}\"\"\".format(qual_mean, std, qual_means_groups))\n",
    "\n",
    "# wines with high alcohol content are nearly a full standard deviation away from the average quality across all wines\n",
    "# people definitely enjoy their alcohol content :) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Our custom function applied to pandas groupby could only handle 1 parameter. What if we wanted more?\n",
    "Pandas allows you to feed in multiple parameters into an apply statement, however the syntax is a bit different\n",
    "than one might originally expect. \n",
    "\n",
    "Going back to the wine quality and alcohol content example, what if we wanted to flag all groups that are at least half a standard deviation away from the population mean? "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def flag_n_std(group, \n",
    "                std_flag=2, \n",
    "                pop_mean=None,\n",
    "                pop_std=None,\n",
    "                col_of_interest='quality',\n",
    "                flag='STD_FLAG'):\n",
    "    \"\"\"\n",
    "    flag any groups more than std_flag above the population mean\n",
    "    \n",
    "    params\n",
    "    -------\n",
    "    group --> group of data being operated on\n",
    "    std_flag --> number of standard deviations away \n",
    "    col_of_interest --> Column we want to flag as being std_flag away from the population mean\n",
    "    pop_mean --> population mean for column\n",
    "    pop_std --> population standard deviation\n",
    "    flag --> value to fill in as our flag\n",
    "    \"\"\"\n",
    "    # get group mean\n",
    "    group_mean = group.loc[:, col_of_interest].mean()\n",
    "    # get the threshold\n",
    "    thresh = std_flag * pop_std\n",
    "    # get the difference\n",
    "    diff = group_mean - pop_mean\n",
    "    # check\n",
    "    if diff > thresh:\n",
    "        group['STD_FLAG'] = flag\n",
    "    else:\n",
    "        group['STD_FLAG'] = None\n",
    "    return group.reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# set some basic parameter values\n",
    "col_of_interest = 'quality'\n",
    "pop_mean = df[col_of_interest].mean()\n",
    "pop_std = df[col_of_interest].std()\n",
    "flag = 'GREAT_QUALITY'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Type</th>\n",
       "      <th>alcoholbins</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1535</th>\n",
       "      <td>Red</td>\n",
       "      <td>high</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6205</th>\n",
       "      <td>White</td>\n",
       "      <td>high</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Type alcoholbins\n",
       "1535    Red        high\n",
       "6205  White        high"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# apply partial function to dataframe\n",
    "res = pd.concat([df.groupby(['Type', 'alcoholbins']).apply(flag_n_std, std_flag=0.5,\n",
    "                                                          pop_mean=pop_mean,\n",
    "                                                          pop_std=pop_std,\n",
    "                                                          flag=flag).reset_index(drop=True)])\n",
    "\n",
    "res[res['STD_FLAG'].notnull()][['Type', 'alcoholbins']].drop_duplicates()\n",
    "# across both red and white wines, high alcohol content translates into better quality :) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And thats a wrap. Pandas split/apply/combine can be very powerful. It can support your base aggregation methods all the way up to more advanced custom functions. The key thing to remember is all it is doing is operating on a specific portion of your data, where the groupby columns equal specific combinations of values. The full code for this tutorial can be downloaded [here](https://github.com/datawrestler/Tutorials/tree/master/Pandas)"
   ]
  }
 ],
 "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.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}