Using pandas.groupby.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Using pandas.groupby\n",
    "\n",
    "The `groupby` method of Pandas dataframes is useful when you have operations that you want to carry out separately on different groups within your data - for example, `count`, `sum`, `min`, `max`, `mean`, and `std`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'3.6.6'"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from platform import python_version\n",
    "python_version()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Some data\n",
    "\n",
    "Say you have a time series of data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>Date</th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>0.676620</td>\n",
       "      <td>55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018-01-06</td>\n",
       "      <td>0.217035</td>\n",
       "      <td>61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2018-01-11</td>\n",
       "      <td>0.471222</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018-01-16</td>\n",
       "      <td>-1.321683</td>\n",
       "      <td>82</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018-01-21</td>\n",
       "      <td>0.161252</td>\n",
       "      <td>68</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Date         A   B\n",
       "0 2018-01-01  0.676620  55\n",
       "1 2018-01-06  0.217035  61\n",
       "2 2018-01-11  0.471222  90\n",
       "3 2018-01-16 -1.321683  82\n",
       "4 2018-01-21  0.161252  68"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "date_range = pd.date_range('2018-01-01', '2018-06-05', freq='5D')\n",
    "n = len(date_range)\n",
    "df = pd.DataFrame({\n",
    "    'Date': date_range,\n",
    "    'A': np.random.randn(n),\n",
    "    'B': np.random.randint(0, 100, size=n)\n",
    "})\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "and you want to see totals by quarter."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>Date</th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>Quarter</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>0.676620</td>\n",
       "      <td>55</td>\n",
       "      <td>2018Q1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018-01-06</td>\n",
       "      <td>0.217035</td>\n",
       "      <td>61</td>\n",
       "      <td>2018Q1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2018-01-11</td>\n",
       "      <td>0.471222</td>\n",
       "      <td>90</td>\n",
       "      <td>2018Q1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018-01-16</td>\n",
       "      <td>-1.321683</td>\n",
       "      <td>82</td>\n",
       "      <td>2018Q1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018-01-21</td>\n",
       "      <td>0.161252</td>\n",
       "      <td>68</td>\n",
       "      <td>2018Q1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Date         A   B Quarter\n",
       "0 2018-01-01  0.676620  55  2018Q1\n",
       "1 2018-01-06  0.217035  61  2018Q1\n",
       "2 2018-01-11  0.471222  90  2018Q1\n",
       "3 2018-01-16 -1.321683  82  2018Q1\n",
       "4 2018-01-21  0.161252  68  2018Q1"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Quarter'] = df['Date'].dt.to_period('Q')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## First, define a groupby object"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Date</th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Quarter</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018Q1</th>\n",
       "      <td>18</td>\n",
       "      <td>18</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018Q2</th>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Date   A   B\n",
       "Quarter              \n",
       "2018Q1     18  18  18\n",
       "2018Q2     14  14  14"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "groupby_quarter = df.groupby('Quarter')\n",
    "\n",
    "groupby_quarter.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Quarter</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018Q1</th>\n",
       "      <td>-0.135362</td>\n",
       "      <td>56.388889</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018Q2</th>\n",
       "      <td>0.100012</td>\n",
       "      <td>33.857143</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                A          B\n",
       "Quarter                     \n",
       "2018Q1  -0.135362  56.388889\n",
       "2018Q2   0.100012  33.857143"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "groupby_quarter.mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Grouping by index values\n",
    "\n",
    "The group-by will also look in the index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Quarter</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018Q1</th>\n",
       "      <td>1.533886</td>\n",
       "      <td>97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018Q2</th>\n",
       "      <td>1.574606</td>\n",
       "      <td>68</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                A   B\n",
       "Quarter              \n",
       "2018Q1   1.533886  97\n",
       "2018Q2   1.574606  68"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.set_index(['Quarter', 'Date']).groupby('Quarter').max()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Iterate through the groups"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'tuple'> 2\n"
     ]
    }
   ],
   "source": [
    "for group in groupby_quarter:\n",
    "\n",
    "    print(type(group), len(group))\n",
    "    break"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Period('2018Q1', 'Q-DEC')"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "group[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>Date</th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>Quarter</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>0.676620</td>\n",
       "      <td>55</td>\n",
       "      <td>2018Q1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018-01-06</td>\n",
       "      <td>0.217035</td>\n",
       "      <td>61</td>\n",
       "      <td>2018Q1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2018-01-11</td>\n",
       "      <td>0.471222</td>\n",
       "      <td>90</td>\n",
       "      <td>2018Q1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018-01-16</td>\n",
       "      <td>-1.321683</td>\n",
       "      <td>82</td>\n",
       "      <td>2018Q1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018-01-21</td>\n",
       "      <td>0.161252</td>\n",
       "      <td>68</td>\n",
       "      <td>2018Q1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2018-01-26</td>\n",
       "      <td>-2.100279</td>\n",
       "      <td>38</td>\n",
       "      <td>2018Q1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2018-01-31</td>\n",
       "      <td>0.776922</td>\n",
       "      <td>88</td>\n",
       "      <td>2018Q1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2018-02-05</td>\n",
       "      <td>1.005232</td>\n",
       "      <td>28</td>\n",
       "      <td>2018Q1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2018-02-10</td>\n",
       "      <td>-0.956780</td>\n",
       "      <td>45</td>\n",
       "      <td>2018Q1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2018-02-15</td>\n",
       "      <td>1.153897</td>\n",
       "      <td>97</td>\n",
       "      <td>2018Q1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2018-02-20</td>\n",
       "      <td>-0.831922</td>\n",
       "      <td>69</td>\n",
       "      <td>2018Q1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2018-02-25</td>\n",
       "      <td>-0.321854</td>\n",
       "      <td>39</td>\n",
       "      <td>2018Q1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2018-03-02</td>\n",
       "      <td>-0.414905</td>\n",
       "      <td>28</td>\n",
       "      <td>2018Q1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2018-03-07</td>\n",
       "      <td>-0.825584</td>\n",
       "      <td>9</td>\n",
       "      <td>2018Q1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2018-03-12</td>\n",
       "      <td>-1.443649</td>\n",
       "      <td>48</td>\n",
       "      <td>2018Q1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2018-03-17</td>\n",
       "      <td>0.868241</td>\n",
       "      <td>72</td>\n",
       "      <td>2018Q1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2018-03-22</td>\n",
       "      <td>-1.084172</td>\n",
       "      <td>12</td>\n",
       "      <td>2018Q1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2018-03-27</td>\n",
       "      <td>1.533886</td>\n",
       "      <td>86</td>\n",
       "      <td>2018Q1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Date         A   B Quarter\n",
       "0  2018-01-01  0.676620  55  2018Q1\n",
       "1  2018-01-06  0.217035  61  2018Q1\n",
       "2  2018-01-11  0.471222  90  2018Q1\n",
       "3  2018-01-16 -1.321683  82  2018Q1\n",
       "4  2018-01-21  0.161252  68  2018Q1\n",
       "5  2018-01-26 -2.100279  38  2018Q1\n",
       "6  2018-01-31  0.776922  88  2018Q1\n",
       "7  2018-02-05  1.005232  28  2018Q1\n",
       "8  2018-02-10 -0.956780  45  2018Q1\n",
       "9  2018-02-15  1.153897  97  2018Q1\n",
       "10 2018-02-20 -0.831922  69  2018Q1\n",
       "11 2018-02-25 -0.321854  39  2018Q1\n",
       "12 2018-03-02 -0.414905  28  2018Q1\n",
       "13 2018-03-07 -0.825584   9  2018Q1\n",
       "14 2018-03-12 -1.443649  48  2018Q1\n",
       "15 2018-03-17  0.868241  72  2018Q1\n",
       "16 2018-03-22 -1.084172  12  2018Q1\n",
       "17 2018-03-27  1.533886  86  2018Q1"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "group[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'group_data' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m--------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m                    Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-11-f5f2d96cd99e>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mgroup_data\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'B'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m: name 'group_data' is not defined"
     ]
    }
   ],
   "source": [
    "group_data['B']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for quarter, group_data in groupby_quarter:\n",
    "    \n",
    "    group_data['B'].plot.bar()\n",
    "    plt.title(quarter)\n",
    "    plt.grid()\n",
    "    plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python (py34)",
   "language": "python",
   "name": "py34"
  },
  "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.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}