12_Aggregate Data with Groupby.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 12_Aggregate Data with Groupby\n",
    "### Syntax:\n",
    "\n",
    "I found the <code>groupby</code> syntax a little difficult to remember at first, so here it is broken down:\n",
    "\n",
    "><code>df.groupby([<span style=\"color:blue\">\"col\", \"optional_col\", \"etc\"</span>])[<span style=\"color:red\">\"col\", \"optional_col\", \"etc\"</span>].<span style=\"color:green\">function()</span> </code>\n",
    "\n",
    "The column(s) in <span style=\"color:blue\">blue</span> are the features that you want to group *by*.\n",
    "\n",
    "The column(s) in <span style=\"color:red\">red</span> are the features you want to aggregate (i.e. the data you want to summarise with a function such as mean, sum, max, min, etc.)\n",
    "\n",
    "The function in <span style=\"color:green\">green</span> is how you want to aggregate the data, for example, \"mean\" or \"sum\".\n",
    "\n",
    "### Example:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "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>Date</th>\n",
       "      <th>feature_1</th>\n",
       "      <th>feature_2</th>\n",
       "      <th>feature_3</th>\n",
       "      <th>feature_4</th>\n",
       "      <th>categorical_feature</th>\n",
       "      <th>weekday</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>01/02/2017</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>37</td>\n",
       "      <td>0</td>\n",
       "      <td>foo</td>\n",
       "      <td>Wednesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>02/02/2017</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>168</td>\n",
       "      <td>0</td>\n",
       "      <td>foo</td>\n",
       "      <td>Thursday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>03/02/2017</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>157</td>\n",
       "      <td>0</td>\n",
       "      <td>other</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>04/02/2017</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>720</td>\n",
       "      <td>0</td>\n",
       "      <td>other</td>\n",
       "      <td>Saturday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>05/02/2017</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>721</td>\n",
       "      <td>0</td>\n",
       "      <td>bar</td>\n",
       "      <td>Sunday</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Date  feature_1  feature_2  feature_3  feature_4 categorical_feature  \\\n",
       "0  01/02/2017          0          0         37          0                 foo   \n",
       "1  02/02/2017          0          0        168          0                 foo   \n",
       "2  03/02/2017          0          0        157          0               other   \n",
       "3  04/02/2017          0          0        720          0               other   \n",
       "4  05/02/2017          0          0        721          0                 bar   \n",
       "\n",
       "     weekday  \n",
       "0  Wednesday  \n",
       "1   Thursday  \n",
       "2     Friday  \n",
       "3   Saturday  \n",
       "4     Sunday  "
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# Load the example timeseries_daily.csv dataset:\n",
    "df = pd.read_csv(\"./data_etc/timeseries_daily.csv\")\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Group 1 feature by another feature:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "weekday\n",
       "Friday       1484.565217\n",
       "Monday       1355.822222\n",
       "Saturday     1855.043478\n",
       "Sunday       1825.847826\n",
       "Thursday     1407.826087\n",
       "Tuesday      1388.400000\n",
       "Wednesday    1389.652174\n",
       "Name: feature_3, dtype: float64"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby([\"weekday\"])[\"feature_3\"].mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Group multiple features by 1 feature:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "weekday    categorical_feature\n",
       "Friday     bar                    1748.272727\n",
       "           foo                    1253.117647\n",
       "           other                  1542.000000\n",
       "Monday     bar                    1412.250000\n",
       "           foo                    1566.307692\n",
       "           other                  1185.150000\n",
       "Saturday   bar                    1716.000000\n",
       "           foo                    1918.666667\n",
       "           other                  1860.631579\n",
       "Sunday     bar                    2013.600000\n",
       "           foo                    1838.000000\n",
       "           other                  1678.350000\n",
       "Thursday   bar                    1263.000000\n",
       "           foo                    1264.769231\n",
       "           other                  1631.833333\n",
       "Tuesday    bar                    1414.235294\n",
       "           foo                    1302.000000\n",
       "           other                  1443.428571\n",
       "Wednesday  bar                    1332.363636\n",
       "           foo                    1422.700000\n",
       "           other                  1387.600000\n",
       "Name: feature_3, dtype: float64"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby([\"weekday\", \"categorical_feature\"])[\"feature_3\"].mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Group multiple features by multiple features:"
   ]
  },
  {
   "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></th>\n",
       "      <th>feature_3</th>\n",
       "      <th>feature_1</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>weekday</th>\n",
       "      <th>categorical_feature</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Friday</th>\n",
       "      <th>bar</th>\n",
       "      <td>1748.272727</td>\n",
       "      <td>551.545455</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>foo</th>\n",
       "      <td>1253.117647</td>\n",
       "      <td>271.705882</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>other</th>\n",
       "      <td>1542.000000</td>\n",
       "      <td>455.388889</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Monday</th>\n",
       "      <th>bar</th>\n",
       "      <td>1412.250000</td>\n",
       "      <td>376.166667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>foo</th>\n",
       "      <td>1566.307692</td>\n",
       "      <td>319.538462</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>other</th>\n",
       "      <td>1185.150000</td>\n",
       "      <td>160.600000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Saturday</th>\n",
       "      <th>bar</th>\n",
       "      <td>1716.000000</td>\n",
       "      <td>682.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>foo</th>\n",
       "      <td>1918.666667</td>\n",
       "      <td>528.277778</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>other</th>\n",
       "      <td>1860.631579</td>\n",
       "      <td>568.578947</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Sunday</th>\n",
       "      <th>bar</th>\n",
       "      <td>2013.600000</td>\n",
       "      <td>655.466667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>foo</th>\n",
       "      <td>1838.000000</td>\n",
       "      <td>365.545455</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>other</th>\n",
       "      <td>1678.350000</td>\n",
       "      <td>569.400000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Thursday</th>\n",
       "      <th>bar</th>\n",
       "      <td>1263.000000</td>\n",
       "      <td>325.866667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>foo</th>\n",
       "      <td>1264.769231</td>\n",
       "      <td>319.153846</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>other</th>\n",
       "      <td>1631.833333</td>\n",
       "      <td>477.555556</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Tuesday</th>\n",
       "      <th>bar</th>\n",
       "      <td>1414.235294</td>\n",
       "      <td>253.764706</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>foo</th>\n",
       "      <td>1302.000000</td>\n",
       "      <td>214.785714</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>other</th>\n",
       "      <td>1443.428571</td>\n",
       "      <td>302.571429</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Wednesday</th>\n",
       "      <th>bar</th>\n",
       "      <td>1332.363636</td>\n",
       "      <td>232.363636</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>foo</th>\n",
       "      <td>1422.700000</td>\n",
       "      <td>252.900000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>other</th>\n",
       "      <td>1387.600000</td>\n",
       "      <td>206.200000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 feature_3   feature_1\n",
       "weekday   categorical_feature                         \n",
       "Friday    bar                  1748.272727  551.545455\n",
       "          foo                  1253.117647  271.705882\n",
       "          other                1542.000000  455.388889\n",
       "Monday    bar                  1412.250000  376.166667\n",
       "          foo                  1566.307692  319.538462\n",
       "          other                1185.150000  160.600000\n",
       "Saturday  bar                  1716.000000  682.333333\n",
       "          foo                  1918.666667  528.277778\n",
       "          other                1860.631579  568.578947\n",
       "Sunday    bar                  2013.600000  655.466667\n",
       "          foo                  1838.000000  365.545455\n",
       "          other                1678.350000  569.400000\n",
       "Thursday  bar                  1263.000000  325.866667\n",
       "          foo                  1264.769231  319.153846\n",
       "          other                1631.833333  477.555556\n",
       "Tuesday   bar                  1414.235294  253.764706\n",
       "          foo                  1302.000000  214.785714\n",
       "          other                1443.428571  302.571429\n",
       "Wednesday bar                  1332.363636  232.363636\n",
       "          foo                  1422.700000  252.900000\n",
       "          other                1387.600000  206.200000"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby([\"weekday\", \"categorical_feature\"])[\"feature_3\", \"feature_1\"].mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Use <code>as_index=False</code> to return a 'flat' DataFrame:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>weekday</th>\n",
       "      <th>categorical_feature</th>\n",
       "      <th>feature_3</th>\n",
       "      <th>feature_1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Friday</td>\n",
       "      <td>bar</td>\n",
       "      <td>1748.272727</td>\n",
       "      <td>551.545455</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Friday</td>\n",
       "      <td>foo</td>\n",
       "      <td>1253.117647</td>\n",
       "      <td>271.705882</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Friday</td>\n",
       "      <td>other</td>\n",
       "      <td>1542.000000</td>\n",
       "      <td>455.388889</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Monday</td>\n",
       "      <td>bar</td>\n",
       "      <td>1412.250000</td>\n",
       "      <td>376.166667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Monday</td>\n",
       "      <td>foo</td>\n",
       "      <td>1566.307692</td>\n",
       "      <td>319.538462</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Monday</td>\n",
       "      <td>other</td>\n",
       "      <td>1185.150000</td>\n",
       "      <td>160.600000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Saturday</td>\n",
       "      <td>bar</td>\n",
       "      <td>1716.000000</td>\n",
       "      <td>682.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Saturday</td>\n",
       "      <td>foo</td>\n",
       "      <td>1918.666667</td>\n",
       "      <td>528.277778</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Saturday</td>\n",
       "      <td>other</td>\n",
       "      <td>1860.631579</td>\n",
       "      <td>568.578947</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Sunday</td>\n",
       "      <td>bar</td>\n",
       "      <td>2013.600000</td>\n",
       "      <td>655.466667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Sunday</td>\n",
       "      <td>foo</td>\n",
       "      <td>1838.000000</td>\n",
       "      <td>365.545455</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Sunday</td>\n",
       "      <td>other</td>\n",
       "      <td>1678.350000</td>\n",
       "      <td>569.400000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Thursday</td>\n",
       "      <td>bar</td>\n",
       "      <td>1263.000000</td>\n",
       "      <td>325.866667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Thursday</td>\n",
       "      <td>foo</td>\n",
       "      <td>1264.769231</td>\n",
       "      <td>319.153846</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Thursday</td>\n",
       "      <td>other</td>\n",
       "      <td>1631.833333</td>\n",
       "      <td>477.555556</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Tuesday</td>\n",
       "      <td>bar</td>\n",
       "      <td>1414.235294</td>\n",
       "      <td>253.764706</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Tuesday</td>\n",
       "      <td>foo</td>\n",
       "      <td>1302.000000</td>\n",
       "      <td>214.785714</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Tuesday</td>\n",
       "      <td>other</td>\n",
       "      <td>1443.428571</td>\n",
       "      <td>302.571429</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Wednesday</td>\n",
       "      <td>bar</td>\n",
       "      <td>1332.363636</td>\n",
       "      <td>232.363636</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Wednesday</td>\n",
       "      <td>foo</td>\n",
       "      <td>1422.700000</td>\n",
       "      <td>252.900000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Wednesday</td>\n",
       "      <td>other</td>\n",
       "      <td>1387.600000</td>\n",
       "      <td>206.200000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      weekday categorical_feature    feature_3   feature_1\n",
       "0      Friday                 bar  1748.272727  551.545455\n",
       "1      Friday                 foo  1253.117647  271.705882\n",
       "2      Friday               other  1542.000000  455.388889\n",
       "3      Monday                 bar  1412.250000  376.166667\n",
       "4      Monday                 foo  1566.307692  319.538462\n",
       "5      Monday               other  1185.150000  160.600000\n",
       "6    Saturday                 bar  1716.000000  682.333333\n",
       "7    Saturday                 foo  1918.666667  528.277778\n",
       "8    Saturday               other  1860.631579  568.578947\n",
       "9      Sunday                 bar  2013.600000  655.466667\n",
       "10     Sunday                 foo  1838.000000  365.545455\n",
       "11     Sunday               other  1678.350000  569.400000\n",
       "12   Thursday                 bar  1263.000000  325.866667\n",
       "13   Thursday                 foo  1264.769231  319.153846\n",
       "14   Thursday               other  1631.833333  477.555556\n",
       "15    Tuesday                 bar  1414.235294  253.764706\n",
       "16    Tuesday                 foo  1302.000000  214.785714\n",
       "17    Tuesday               other  1443.428571  302.571429\n",
       "18  Wednesday                 bar  1332.363636  232.363636\n",
       "19  Wednesday                 foo  1422.700000  252.900000\n",
       "20  Wednesday               other  1387.600000  206.200000"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby([\"weekday\", \"categorical_feature\"], as_index=False)[\"feature_3\", \"feature_1\"].mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Change data type of output:\n",
    "Add the <code>astype()</code> flag to the end to change the data type, for exmaple from <code>float</code> to <code>int</code>:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "weekday\n",
       "Friday       1484\n",
       "Monday       1355\n",
       "Saturday     1855\n",
       "Sunday       1825\n",
       "Thursday     1407\n",
       "Tuesday      1388\n",
       "Wednesday    1389\n",
       "Name: feature_3, dtype: int32"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby([\"weekday\"])[\"feature_3\"].mean().astype(int)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Change format of output:\n",
    "The format of the object returned can also be changed, for example using <code>.to_json()</code>, <code>.to_dict()</code>, <code>.to_csv()</code>, etc."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'Friday': 1484,\n",
       " 'Monday': 1355,\n",
       " 'Saturday': 1855,\n",
       " 'Sunday': 1825,\n",
       " 'Thursday': 1407,\n",
       " 'Tuesday': 1388,\n",
       " 'Wednesday': 1389}"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby([\"weekday\"])[\"feature_3\"].mean().astype(int).to_dict()"
   ]
  }
 ],
 "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.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}