1.tools/L1 - Pandas-2.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Pivot Table on Pandas\n",
    "\n",
    "In this notebook, we focus on the `pivot_table` feature of `pandas`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Import Modules"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import plotly.plotly as py\n",
    "import plotly.graph_objs as go"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Import data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>suburb</th>\n",
       "      <th>council</th>\n",
       "      <th>type</th>\n",
       "      <th>distance_to_CBD</th>\n",
       "      <th>value</th>\n",
       "      <th>sold</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Turrella</td>\n",
       "      <td>Rockdale</td>\n",
       "      <td>house</td>\n",
       "      <td>9.9</td>\n",
       "      <td>$839,676</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Sydenham</td>\n",
       "      <td>Marrickville</td>\n",
       "      <td>house</td>\n",
       "      <td>7.4</td>\n",
       "      <td>$845,771</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>St Peters</td>\n",
       "      <td>Marrickville</td>\n",
       "      <td>house</td>\n",
       "      <td>6.9</td>\n",
       "      <td>$920,169</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Tempe</td>\n",
       "      <td>Marrickville</td>\n",
       "      <td>house</td>\n",
       "      <td>8.3</td>\n",
       "      <td>$921,390</td>\n",
       "      <td>59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Waterloo</td>\n",
       "      <td>Sydney</td>\n",
       "      <td>house</td>\n",
       "      <td>4.7</td>\n",
       "      <td>$937,316</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      suburb       council   type  distance_to_CBD      value  sold\n",
       "0   Turrella      Rockdale  house              9.9  $839,676     17\n",
       "1   Sydenham  Marrickville  house              7.4  $845,771     21\n",
       "2  St Peters  Marrickville  house              6.9  $920,169     50\n",
       "3      Tempe  Marrickville  house              8.3  $921,390     59\n",
       "4   Waterloo        Sydney  house              4.7  $937,316     11"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('./asset/sydney_housing_market.txt', sep='\\t')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Pivot Table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In order to build up a pivot table, we must specify an index. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>distance_to_CBD</th>\n",
       "      <th>sold</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>type</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>house</th>\n",
       "      <td>6.585</td>\n",
       "      <td>69.20</td>\n",
       "      <td>1024662.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unit</th>\n",
       "      <td>6.280</td>\n",
       "      <td>108.55</td>\n",
       "      <td>585739.05</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       distance_to_CBD    sold       value\n",
       "type                                      \n",
       "house            6.585   69.20  1024662.90\n",
       "unit             6.280  108.55   585739.05"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(df, index=['type'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that the default aggregation function is `np.mean`. We can specify the aggregation function in the `aggfunc` parameter, as shown below. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>distance_to_CBD</th>\n",
       "      <th>sold</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>type</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>house</th>\n",
       "      <td>6.585</td>\n",
       "      <td>1384</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unit</th>\n",
       "      <td>6.280</td>\n",
       "      <td>2171</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       distance_to_CBD  sold\n",
       "type                        \n",
       "house            6.585  1384\n",
       "unit             6.280  2171"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(df, index=['type'], aggfunc={'distance_to_CBD':np.mean, 'sold':np.sum})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For simplicity, we will stick with the default aggregation function. \n",
    "\n",
    "We also want to see *value*, but we need to change it into floats first"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>distance_to_CBD</th>\n",
       "      <th>sold</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>type</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>house</th>\n",
       "      <td>6.585</td>\n",
       "      <td>69.20</td>\n",
       "      <td>1024662.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unit</th>\n",
       "      <td>6.280</td>\n",
       "      <td>108.55</td>\n",
       "      <td>585739.05</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       distance_to_CBD    sold       value\n",
       "type                                      \n",
       "house            6.585   69.20  1024662.90\n",
       "unit             6.280  108.55   585739.05"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['value']=df['value'].replace('[\\$,]','',regex=True).astype(float)\n",
    "pd.pivot_table(df, index=['type'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We could also choose more than one column as index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>distance_to_CBD</th>\n",
       "      <th>sold</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>type</th>\n",
       "      <th>council</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"8\" valign=\"top\">house</th>\n",
       "      <th>Ashfield</th>\n",
       "      <td>7.600000</td>\n",
       "      <td>79.500000</td>\n",
       "      <td>1.101914e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Botany Bay</th>\n",
       "      <td>8.200000</td>\n",
       "      <td>29.000000</td>\n",
       "      <td>1.090744e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Canada Bay</th>\n",
       "      <td>9.200000</td>\n",
       "      <td>15.000000</td>\n",
       "      <td>1.036064e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Canterbury</th>\n",
       "      <td>9.100000</td>\n",
       "      <td>36.000000</td>\n",
       "      <td>1.066214e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Leichhardt</th>\n",
       "      <td>5.200000</td>\n",
       "      <td>225.000000</td>\n",
       "      <td>1.076623e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Marrickville</th>\n",
       "      <td>6.742857</td>\n",
       "      <td>81.285714</td>\n",
       "      <td>9.933390e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Rockdale</th>\n",
       "      <td>9.900000</td>\n",
       "      <td>17.000000</td>\n",
       "      <td>8.396760e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sydney</th>\n",
       "      <td>4.616667</td>\n",
       "      <td>55.666667</td>\n",
       "      <td>1.037789e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"9\" valign=\"top\">unit</th>\n",
       "      <th>Ashfield</th>\n",
       "      <td>7.233333</td>\n",
       "      <td>109.666667</td>\n",
       "      <td>5.933117e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Botany Bay</th>\n",
       "      <td>8.200000</td>\n",
       "      <td>56.000000</td>\n",
       "      <td>4.844870e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Burwood</th>\n",
       "      <td>8.400000</td>\n",
       "      <td>43.000000</td>\n",
       "      <td>6.059270e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Canterbury</th>\n",
       "      <td>9.100000</td>\n",
       "      <td>15.000000</td>\n",
       "      <td>5.930810e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Lane Cove</th>\n",
       "      <td>7.000000</td>\n",
       "      <td>220.000000</td>\n",
       "      <td>6.112070e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Marrickville</th>\n",
       "      <td>6.550000</td>\n",
       "      <td>87.333333</td>\n",
       "      <td>5.929137e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Randwick</th>\n",
       "      <td>5.200000</td>\n",
       "      <td>53.000000</td>\n",
       "      <td>5.778140e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ryde</th>\n",
       "      <td>7.900000</td>\n",
       "      <td>139.000000</td>\n",
       "      <td>5.729230e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sydney</th>\n",
       "      <td>3.760000</td>\n",
       "      <td>158.400000</td>\n",
       "      <td>5.863850e+05</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    distance_to_CBD        sold         value\n",
       "type  council                                                \n",
       "house Ashfield             7.600000   79.500000  1.101914e+06\n",
       "      Botany Bay           8.200000   29.000000  1.090744e+06\n",
       "      Canada Bay           9.200000   15.000000  1.036064e+06\n",
       "      Canterbury           9.100000   36.000000  1.066214e+06\n",
       "      Leichhardt           5.200000  225.000000  1.076623e+06\n",
       "      Marrickville         6.742857   81.285714  9.933390e+05\n",
       "      Rockdale             9.900000   17.000000  8.396760e+05\n",
       "      Sydney               4.616667   55.666667  1.037789e+06\n",
       "unit  Ashfield             7.233333  109.666667  5.933117e+05\n",
       "      Botany Bay           8.200000   56.000000  4.844870e+05\n",
       "      Burwood              8.400000   43.000000  6.059270e+05\n",
       "      Canterbury           9.100000   15.000000  5.930810e+05\n",
       "      Lane Cove            7.000000  220.000000  6.112070e+05\n",
       "      Marrickville         6.550000   87.333333  5.929137e+05\n",
       "      Randwick             5.200000   53.000000  5.778140e+05\n",
       "      Ryde                 7.900000  139.000000  5.729230e+05\n",
       "      Sydney               3.760000  158.400000  5.863850e+05"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(df, index=['type','council'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```columns``` provide an additional way to segment the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">distance_to_CBD</th>\n",
       "      <th colspan=\"2\" halign=\"left\">value</th>\n",
       "      <th colspan=\"2\" halign=\"left\">sold</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>type</th>\n",
       "      <th>house</th>\n",
       "      <th>unit</th>\n",
       "      <th>house</th>\n",
       "      <th>unit</th>\n",
       "      <th>house</th>\n",
       "      <th>unit</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>council</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Ashfield</th>\n",
       "      <td>7.600000</td>\n",
       "      <td>7.233333</td>\n",
       "      <td>1.101914e+06</td>\n",
       "      <td>593311.666667</td>\n",
       "      <td>79.500000</td>\n",
       "      <td>109.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Botany Bay</th>\n",
       "      <td>8.200000</td>\n",
       "      <td>8.200000</td>\n",
       "      <td>1.090744e+06</td>\n",
       "      <td>484487.000000</td>\n",
       "      <td>29.000000</td>\n",
       "      <td>56.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Burwood</th>\n",
       "      <td>NaN</td>\n",
       "      <td>8.400000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>605927.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>43.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Canada Bay</th>\n",
       "      <td>9.200000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.036064e+06</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Canterbury</th>\n",
       "      <td>9.100000</td>\n",
       "      <td>9.100000</td>\n",
       "      <td>1.066214e+06</td>\n",
       "      <td>593081.000000</td>\n",
       "      <td>36.000000</td>\n",
       "      <td>15.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Lane Cove</th>\n",
       "      <td>NaN</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>611207.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>220.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Leichhardt</th>\n",
       "      <td>5.200000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.076623e+06</td>\n",
       "      <td>NaN</td>\n",
       "      <td>225.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Marrickville</th>\n",
       "      <td>6.742857</td>\n",
       "      <td>6.550000</td>\n",
       "      <td>9.933390e+05</td>\n",
       "      <td>592913.666667</td>\n",
       "      <td>81.285714</td>\n",
       "      <td>87.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Randwick</th>\n",
       "      <td>NaN</td>\n",
       "      <td>5.200000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>577814.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>53.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Rockdale</th>\n",
       "      <td>9.900000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.396760e+05</td>\n",
       "      <td>NaN</td>\n",
       "      <td>17.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ryde</th>\n",
       "      <td>NaN</td>\n",
       "      <td>7.900000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>572923.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>139.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sydney</th>\n",
       "      <td>4.616667</td>\n",
       "      <td>3.760000</td>\n",
       "      <td>1.037789e+06</td>\n",
       "      <td>586385.000000</td>\n",
       "      <td>55.666667</td>\n",
       "      <td>158.400000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             distance_to_CBD                   value                 \\\n",
       "type                   house      unit         house           unit   \n",
       "council                                                               \n",
       "Ashfield            7.600000  7.233333  1.101914e+06  593311.666667   \n",
       "Botany Bay          8.200000  8.200000  1.090744e+06  484487.000000   \n",
       "Burwood                  NaN  8.400000           NaN  605927.000000   \n",
       "Canada Bay          9.200000       NaN  1.036064e+06            NaN   \n",
       "Canterbury          9.100000  9.100000  1.066214e+06  593081.000000   \n",
       "Lane Cove                NaN  7.000000           NaN  611207.000000   \n",
       "Leichhardt          5.200000       NaN  1.076623e+06            NaN   \n",
       "Marrickville        6.742857  6.550000  9.933390e+05  592913.666667   \n",
       "Randwick                 NaN  5.200000           NaN  577814.000000   \n",
       "Rockdale            9.900000       NaN  8.396760e+05            NaN   \n",
       "Ryde                     NaN  7.900000           NaN  572923.000000   \n",
       "Sydney              4.616667  3.760000  1.037789e+06  586385.000000   \n",
       "\n",
       "                    sold              \n",
       "type               house        unit  \n",
       "council                               \n",
       "Ashfield       79.500000  109.666667  \n",
       "Botany Bay     29.000000   56.000000  \n",
       "Burwood              NaN   43.000000  \n",
       "Canada Bay     15.000000         NaN  \n",
       "Canterbury     36.000000   15.000000  \n",
       "Lane Cove            NaN  220.000000  \n",
       "Leichhardt    225.000000         NaN  \n",
       "Marrickville   81.285714   87.333333  \n",
       "Randwick             NaN   53.000000  \n",
       "Rockdale       17.000000         NaN  \n",
       "Ryde                 NaN  139.000000  \n",
       "Sydney         55.666667  158.400000  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(df, index=['council'], columns=['type'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that ```NaN``` implies that there is no data here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The default ```aggfunc``` is ```avg``` but we could use other functions such as ```np.sum```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>distance_to_CBD</th>\n",
       "      <th>sold</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>type</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>house</th>\n",
       "      <td>131.7</td>\n",
       "      <td>1384</td>\n",
       "      <td>20493258.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unit</th>\n",
       "      <td>125.6</td>\n",
       "      <td>2171</td>\n",
       "      <td>11714781.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       distance_to_CBD  sold       value\n",
       "type                                    \n",
       "house            131.7  1384  20493258.0\n",
       "unit             125.6  2171  11714781.0"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(df, index=['type'],aggfunc=np.sum)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Use ```margins=True``` to show the total numbers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>distance_to_CBD</th>\n",
       "      <th>sold</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>type</th>\n",
       "      <th>council</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"8\" valign=\"top\">house</th>\n",
       "      <th>Ashfield</th>\n",
       "      <td>15.2</td>\n",
       "      <td>159.0</td>\n",
       "      <td>2203829.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Botany Bay</th>\n",
       "      <td>8.2</td>\n",
       "      <td>29.0</td>\n",
       "      <td>1090744.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Canada Bay</th>\n",
       "      <td>9.2</td>\n",
       "      <td>15.0</td>\n",
       "      <td>1036064.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Canterbury</th>\n",
       "      <td>9.1</td>\n",
       "      <td>36.0</td>\n",
       "      <td>1066214.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Leichhardt</th>\n",
       "      <td>5.2</td>\n",
       "      <td>225.0</td>\n",
       "      <td>1076623.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Marrickville</th>\n",
       "      <td>47.2</td>\n",
       "      <td>569.0</td>\n",
       "      <td>6953373.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Rockdale</th>\n",
       "      <td>9.9</td>\n",
       "      <td>17.0</td>\n",
       "      <td>839676.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sydney</th>\n",
       "      <td>27.7</td>\n",
       "      <td>334.0</td>\n",
       "      <td>6226735.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"9\" valign=\"top\">unit</th>\n",
       "      <th>Ashfield</th>\n",
       "      <td>21.7</td>\n",
       "      <td>329.0</td>\n",
       "      <td>1779935.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Botany Bay</th>\n",
       "      <td>8.2</td>\n",
       "      <td>56.0</td>\n",
       "      <td>484487.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Burwood</th>\n",
       "      <td>8.4</td>\n",
       "      <td>43.0</td>\n",
       "      <td>605927.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Canterbury</th>\n",
       "      <td>9.1</td>\n",
       "      <td>15.0</td>\n",
       "      <td>593081.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Lane Cove</th>\n",
       "      <td>7.0</td>\n",
       "      <td>220.0</td>\n",
       "      <td>611207.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Marrickville</th>\n",
       "      <td>39.3</td>\n",
       "      <td>524.0</td>\n",
       "      <td>3557482.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Randwick</th>\n",
       "      <td>5.2</td>\n",
       "      <td>53.0</td>\n",
       "      <td>577814.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ryde</th>\n",
       "      <td>7.9</td>\n",
       "      <td>139.0</td>\n",
       "      <td>572923.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sydney</th>\n",
       "      <td>18.8</td>\n",
       "      <td>792.0</td>\n",
       "      <td>2931925.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>All</th>\n",
       "      <th></th>\n",
       "      <td>257.3</td>\n",
       "      <td>3555.0</td>\n",
       "      <td>32208039.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    distance_to_CBD    sold       value\n",
       "type  council                                          \n",
       "house Ashfield                 15.2   159.0   2203829.0\n",
       "      Botany Bay                8.2    29.0   1090744.0\n",
       "      Canada Bay                9.2    15.0   1036064.0\n",
       "      Canterbury                9.1    36.0   1066214.0\n",
       "      Leichhardt                5.2   225.0   1076623.0\n",
       "      Marrickville             47.2   569.0   6953373.0\n",
       "      Rockdale                  9.9    17.0    839676.0\n",
       "      Sydney                   27.7   334.0   6226735.0\n",
       "unit  Ashfield                 21.7   329.0   1779935.0\n",
       "      Botany Bay                8.2    56.0    484487.0\n",
       "      Burwood                   8.4    43.0    605927.0\n",
       "      Canterbury                9.1    15.0    593081.0\n",
       "      Lane Cove                 7.0   220.0    611207.0\n",
       "      Marrickville             39.3   524.0   3557482.0\n",
       "      Randwick                  5.2    53.0    577814.0\n",
       "      Ryde                      7.9   139.0    572923.0\n",
       "      Sydney                   18.8   792.0   2931925.0\n",
       "All                           257.3  3555.0  32208039.0"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(df, index=['type','council'], aggfunc=np.sum, margins=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We should use avg for *value* but sum for *sold*, and we do not want to see *distance_to_CBD* for now"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"3\" halign=\"left\">value</th>\n",
       "      <th colspan=\"3\" halign=\"left\">sold</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>type</th>\n",
       "      <th>house</th>\n",
       "      <th>unit</th>\n",
       "      <th>All</th>\n",
       "      <th>house</th>\n",
       "      <th>unit</th>\n",
       "      <th>All</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>council</th>\n",
       "      <th>suburb</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Ashfield</th>\n",
       "      <th>Ashfield</th>\n",
       "      <td>1123787.0</td>\n",
       "      <td>575096.00</td>\n",
       "      <td>849441.500</td>\n",
       "      <td>110.0</td>\n",
       "      <td>228.0</td>\n",
       "      <td>338.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Haberfield</th>\n",
       "      <td>NaN</td>\n",
       "      <td>573347.00</td>\n",
       "      <td>573347.000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>13.0</td>\n",
       "      <td>13.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Summer Hill</th>\n",
       "      <td>1080042.0</td>\n",
       "      <td>631492.00</td>\n",
       "      <td>855767.000</td>\n",
       "      <td>49.0</td>\n",
       "      <td>88.0</td>\n",
       "      <td>137.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Botany Bay</th>\n",
       "      <th>Eastlakes</th>\n",
       "      <td>1090744.0</td>\n",
       "      <td>484487.00</td>\n",
       "      <td>787615.500</td>\n",
       "      <td>29.0</td>\n",
       "      <td>56.0</td>\n",
       "      <td>85.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Burwood</th>\n",
       "      <th>Croydon</th>\n",
       "      <td>NaN</td>\n",
       "      <td>605927.00</td>\n",
       "      <td>605927.000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>43.0</td>\n",
       "      <td>43.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Canada Bay</th>\n",
       "      <th>Mortlake</th>\n",
       "      <td>1036064.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1036064.000</td>\n",
       "      <td>15.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Canterbury</th>\n",
       "      <th>Hurlstone Park</th>\n",
       "      <td>1066214.0</td>\n",
       "      <td>593081.00</td>\n",
       "      <td>829647.500</td>\n",
       "      <td>36.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>51.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Lane Cove</th>\n",
       "      <th>Lane Cove North</th>\n",
       "      <td>NaN</td>\n",
       "      <td>611207.00</td>\n",
       "      <td>611207.000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>220.0</td>\n",
       "      <td>220.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Leichhardt</th>\n",
       "      <th>Leichhardt</th>\n",
       "      <td>1076623.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1076623.000</td>\n",
       "      <td>225.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>225.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"9\" valign=\"top\">Marrickville</th>\n",
       "      <th>Dulwich Hill</th>\n",
       "      <td>NaN</td>\n",
       "      <td>622141.00</td>\n",
       "      <td>622141.000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>143.0</td>\n",
       "      <td>143.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Enmore</th>\n",
       "      <td>1079403.0</td>\n",
       "      <td>607195.00</td>\n",
       "      <td>843299.000</td>\n",
       "      <td>58.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>73.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Lewisham</th>\n",
       "      <td>NaN</td>\n",
       "      <td>569889.00</td>\n",
       "      <td>569889.000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20.0</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Marrickville</th>\n",
       "      <td>994026.0</td>\n",
       "      <td>552713.00</td>\n",
       "      <td>773369.500</td>\n",
       "      <td>191.0</td>\n",
       "      <td>225.0</td>\n",
       "      <td>416.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Petersham</th>\n",
       "      <td>1122372.0</td>\n",
       "      <td>610890.00</td>\n",
       "      <td>866631.000</td>\n",
       "      <td>82.0</td>\n",
       "      <td>69.0</td>\n",
       "      <td>151.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>St Peters</th>\n",
       "      <td>920169.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>920169.000</td>\n",
       "      <td>50.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>50.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Stanmore</th>\n",
       "      <td>1070242.0</td>\n",
       "      <td>594654.00</td>\n",
       "      <td>832448.000</td>\n",
       "      <td>108.0</td>\n",
       "      <td>52.0</td>\n",
       "      <td>160.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sydenham</th>\n",
       "      <td>845771.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>845771.000</td>\n",
       "      <td>21.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>21.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Tempe</th>\n",
       "      <td>921390.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>921390.000</td>\n",
       "      <td>59.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>59.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Randwick</th>\n",
       "      <th>Centennial Park</th>\n",
       "      <td>NaN</td>\n",
       "      <td>577814.00</td>\n",
       "      <td>577814.000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>53.0</td>\n",
       "      <td>53.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Rockdale</th>\n",
       "      <th>Turrella</th>\n",
       "      <td>839676.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>839676.000</td>\n",
       "      <td>17.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>17.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ryde</th>\n",
       "      <th>Gladesville</th>\n",
       "      <td>NaN</td>\n",
       "      <td>572923.00</td>\n",
       "      <td>572923.000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>139.0</td>\n",
       "      <td>139.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"10\" valign=\"top\">Sydney</th>\n",
       "      <th>Alexandria</th>\n",
       "      <td>NaN</td>\n",
       "      <td>617024.00</td>\n",
       "      <td>617024.000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>211.0</td>\n",
       "      <td>211.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Chippendale</th>\n",
       "      <td>1060415.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1060415.000</td>\n",
       "      <td>31.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Darlington</th>\n",
       "      <td>1062426.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1062426.000</td>\n",
       "      <td>32.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>32.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Elizabeth Bay</th>\n",
       "      <td>NaN</td>\n",
       "      <td>615967.00</td>\n",
       "      <td>615967.000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>182.0</td>\n",
       "      <td>182.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Erskineville</th>\n",
       "      <td>1005107.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1005107.000</td>\n",
       "      <td>84.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>84.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Newtown</th>\n",
       "      <td>1044330.0</td>\n",
       "      <td>559175.00</td>\n",
       "      <td>801752.500</td>\n",
       "      <td>166.0</td>\n",
       "      <td>104.0</td>\n",
       "      <td>270.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Potts Point</th>\n",
       "      <td>NaN</td>\n",
       "      <td>565141.00</td>\n",
       "      <td>565141.000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>217.0</td>\n",
       "      <td>217.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Rushcutters Bay</th>\n",
       "      <td>NaN</td>\n",
       "      <td>574618.00</td>\n",
       "      <td>574618.000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>78.0</td>\n",
       "      <td>78.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Waterloo</th>\n",
       "      <td>937316.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>937316.000</td>\n",
       "      <td>11.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Zetland</th>\n",
       "      <td>1117141.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1117141.000</td>\n",
       "      <td>10.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>All</th>\n",
       "      <th></th>\n",
       "      <td>1024662.9</td>\n",
       "      <td>585739.05</td>\n",
       "      <td>805200.975</td>\n",
       "      <td>1384.0</td>\n",
       "      <td>2171.0</td>\n",
       "      <td>3555.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                  value                            sold  \\\n",
       "type                              house       unit          All   house   \n",
       "council      suburb                                                       \n",
       "Ashfield     Ashfield         1123787.0  575096.00   849441.500   110.0   \n",
       "             Haberfield             NaN  573347.00   573347.000     NaN   \n",
       "             Summer Hill      1080042.0  631492.00   855767.000    49.0   \n",
       "Botany Bay   Eastlakes        1090744.0  484487.00   787615.500    29.0   \n",
       "Burwood      Croydon                NaN  605927.00   605927.000     NaN   \n",
       "Canada Bay   Mortlake         1036064.0        NaN  1036064.000    15.0   \n",
       "Canterbury   Hurlstone Park   1066214.0  593081.00   829647.500    36.0   \n",
       "Lane Cove    Lane Cove North        NaN  611207.00   611207.000     NaN   \n",
       "Leichhardt   Leichhardt       1076623.0        NaN  1076623.000   225.0   \n",
       "Marrickville Dulwich Hill           NaN  622141.00   622141.000     NaN   \n",
       "             Enmore           1079403.0  607195.00   843299.000    58.0   \n",
       "             Lewisham               NaN  569889.00   569889.000     NaN   \n",
       "             Marrickville      994026.0  552713.00   773369.500   191.0   \n",
       "             Petersham        1122372.0  610890.00   866631.000    82.0   \n",
       "             St Peters         920169.0        NaN   920169.000    50.0   \n",
       "             Stanmore         1070242.0  594654.00   832448.000   108.0   \n",
       "             Sydenham          845771.0        NaN   845771.000    21.0   \n",
       "             Tempe             921390.0        NaN   921390.000    59.0   \n",
       "Randwick     Centennial Park        NaN  577814.00   577814.000     NaN   \n",
       "Rockdale     Turrella          839676.0        NaN   839676.000    17.0   \n",
       "Ryde         Gladesville            NaN  572923.00   572923.000     NaN   \n",
       "Sydney       Alexandria             NaN  617024.00   617024.000     NaN   \n",
       "             Chippendale      1060415.0        NaN  1060415.000    31.0   \n",
       "             Darlington       1062426.0        NaN  1062426.000    32.0   \n",
       "             Elizabeth Bay          NaN  615967.00   615967.000     NaN   \n",
       "             Erskineville     1005107.0        NaN  1005107.000    84.0   \n",
       "             Newtown          1044330.0  559175.00   801752.500   166.0   \n",
       "             Potts Point            NaN  565141.00   565141.000     NaN   \n",
       "             Rushcutters Bay        NaN  574618.00   574618.000     NaN   \n",
       "             Waterloo          937316.0        NaN   937316.000    11.0   \n",
       "             Zetland          1117141.0        NaN  1117141.000    10.0   \n",
       "All                           1024662.9  585739.05   805200.975  1384.0   \n",
       "\n",
       "                                              \n",
       "type                            unit     All  \n",
       "council      suburb                           \n",
       "Ashfield     Ashfield          228.0   338.0  \n",
       "             Haberfield         13.0    13.0  \n",
       "             Summer Hill        88.0   137.0  \n",
       "Botany Bay   Eastlakes          56.0    85.0  \n",
       "Burwood      Croydon            43.0    43.0  \n",
       "Canada Bay   Mortlake            NaN    15.0  \n",
       "Canterbury   Hurlstone Park     15.0    51.0  \n",
       "Lane Cove    Lane Cove North   220.0   220.0  \n",
       "Leichhardt   Leichhardt          NaN   225.0  \n",
       "Marrickville Dulwich Hill      143.0   143.0  \n",
       "             Enmore             15.0    73.0  \n",
       "             Lewisham           20.0    20.0  \n",
       "             Marrickville      225.0   416.0  \n",
       "             Petersham          69.0   151.0  \n",
       "             St Peters           NaN    50.0  \n",
       "             Stanmore           52.0   160.0  \n",
       "             Sydenham            NaN    21.0  \n",
       "             Tempe               NaN    59.0  \n",
       "Randwick     Centennial Park    53.0    53.0  \n",
       "Rockdale     Turrella            NaN    17.0  \n",
       "Ryde         Gladesville       139.0   139.0  \n",
       "Sydney       Alexandria        211.0   211.0  \n",
       "             Chippendale         NaN    31.0  \n",
       "             Darlington          NaN    32.0  \n",
       "             Elizabeth Bay     182.0   182.0  \n",
       "             Erskineville        NaN    84.0  \n",
       "             Newtown           104.0   270.0  \n",
       "             Potts Point       217.0   217.0  \n",
       "             Rushcutters Bay    78.0    78.0  \n",
       "             Waterloo            NaN    11.0  \n",
       "             Zetland             NaN    10.0  \n",
       "All                           2171.0  3555.0  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(df, index=['council','suburb'], \n",
    "               columns=['type'], \n",
    "               values=['sold', 'value'], \n",
    "               aggfunc={'sold':np.sum, 'value':np.mean}, \n",
    "               margins=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Advanced Filtering over Pivot Table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We firstly build a pivot table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"3\" halign=\"left\">sold</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>type</th>\n",
       "      <th>house</th>\n",
       "      <th>unit</th>\n",
       "      <th>All</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>council</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Ashfield</th>\n",
       "      <td>159.0</td>\n",
       "      <td>329.0</td>\n",
       "      <td>488.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Botany Bay</th>\n",
       "      <td>29.0</td>\n",
       "      <td>56.0</td>\n",
       "      <td>85.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Burwood</th>\n",
       "      <td>NaN</td>\n",
       "      <td>43.0</td>\n",
       "      <td>43.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Canada Bay</th>\n",
       "      <td>15.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Canterbury</th>\n",
       "      <td>36.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>51.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Lane Cove</th>\n",
       "      <td>NaN</td>\n",
       "      <td>220.0</td>\n",
       "      <td>220.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Leichhardt</th>\n",
       "      <td>225.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>225.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Marrickville</th>\n",
       "      <td>569.0</td>\n",
       "      <td>524.0</td>\n",
       "      <td>1093.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Randwick</th>\n",
       "      <td>NaN</td>\n",
       "      <td>53.0</td>\n",
       "      <td>53.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Rockdale</th>\n",
       "      <td>17.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>17.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ryde</th>\n",
       "      <td>NaN</td>\n",
       "      <td>139.0</td>\n",
       "      <td>139.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sydney</th>\n",
       "      <td>334.0</td>\n",
       "      <td>792.0</td>\n",
       "      <td>1126.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>All</th>\n",
       "      <td>1384.0</td>\n",
       "      <td>2171.0</td>\n",
       "      <td>3555.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                sold                \n",
       "type           house    unit     All\n",
       "council                             \n",
       "Ashfield       159.0   329.0   488.0\n",
       "Botany Bay      29.0    56.0    85.0\n",
       "Burwood          NaN    43.0    43.0\n",
       "Canada Bay      15.0     NaN    15.0\n",
       "Canterbury      36.0    15.0    51.0\n",
       "Lane Cove        NaN   220.0   220.0\n",
       "Leichhardt     225.0     NaN   225.0\n",
       "Marrickville   569.0   524.0  1093.0\n",
       "Randwick         NaN    53.0    53.0\n",
       "Rockdale        17.0     NaN    17.0\n",
       "Ryde             NaN   139.0   139.0\n",
       "Sydney         334.0   792.0  1126.0\n",
       "All           1384.0  2171.0  3555.0"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "table = pd.pivot_table(df, index=['council'], columns=['type'], values=['sold'], aggfunc=np.sum, margins=True)\n",
    "table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can just look at data from one city"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"3\" halign=\"left\">sold</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>type</th>\n",
       "      <th>house</th>\n",
       "      <th>unit</th>\n",
       "      <th>All</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>council</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Randwick</th>\n",
       "      <td>NaN</td>\n",
       "      <td>53.0</td>\n",
       "      <td>53.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          sold            \n",
       "type     house  unit   All\n",
       "council                   \n",
       "Randwick   NaN  53.0  53.0"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "table.query('council==[\"Randwick\"]')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also specify multiple values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"3\" halign=\"left\">sold</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>type</th>\n",
       "      <th>house</th>\n",
       "      <th>unit</th>\n",
       "      <th>All</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>council</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Lane Cove</th>\n",
       "      <td>NaN</td>\n",
       "      <td>220.0</td>\n",
       "      <td>220.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Rockdale</th>\n",
       "      <td>17.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>17.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           sold              \n",
       "type      house   unit    All\n",
       "council                      \n",
       "Lane Cove   NaN  220.0  220.0\n",
       "Rockdale   17.0    NaN   17.0"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "table.query('council==[\"Rockdale\",\"Lane Cove\"]')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note: we can not query a pivot table from the ```columns```. So you should put *item* in ```index``` if you want to query it."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Plot the data using plot.ly\n",
    "\n",
    "Before you can execute the code below, you need to install `plotly`, register a free account with them, and create a profile that contains your own API key. See https://plot.ly/python/getting-started/ "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "plot_table = table[:-1]  # get rid of ALL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "council\n",
       "Ashfield        159.0\n",
       "Botany Bay       29.0\n",
       "Burwood           NaN\n",
       "Canada Bay       15.0\n",
       "Canterbury       36.0\n",
       "Lane Cove         NaN\n",
       "Leichhardt      225.0\n",
       "Marrickville    569.0\n",
       "Randwick          NaN\n",
       "Rockdale         17.0\n",
       "Ryde              NaN\n",
       "Sydney          334.0\n",
       "Name: house, dtype: float64"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "plot_table.sold.house"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "council\n",
       "Ashfield         159.0\n",
       "Botany Bay        29.0\n",
       "Burwood            NaN\n",
       "Canada Bay        15.0\n",
       "Canterbury        36.0\n",
       "Lane Cove          NaN\n",
       "Leichhardt       225.0\n",
       "Marrickville     569.0\n",
       "Randwick           NaN\n",
       "Rockdale          17.0\n",
       "Ryde               NaN\n",
       "Sydney           334.0\n",
       "All             1384.0\n",
       "Name: house, dtype: float64"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "table.sold.house"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Ashfield', 'Botany Bay', 'Burwood', 'Canada Bay', 'Canterbury',\n",
       "       'Lane Cove', 'Leichhardt', 'Marrickville', 'Randwick', 'Rockdale',\n",
       "       'Ryde', 'Sydney'],\n",
       "      dtype='object', name='council')"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "plot_table.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<iframe id=\"igraph\" scrolling=\"no\" style=\"border:none;\" seamless=\"seamless\" src=\"https://plot.ly/~weiwangoz/4.embed\" height=\"525px\" width=\"100%\"></iframe>"
      ],
      "text/plain": [
       "<plotly.tools.PlotlyDisplay object>"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "trace1 = go.Bar(\n",
    "    x=plot_table.index,\n",
    "    y=plot_table.sold.house,\n",
    "    name='House'\n",
    ")\n",
    "trace2 = go.Bar(\n",
    "    x=plot_table.index,\n",
    "    y=plot_table.sold.unit,\n",
    "    name='Unit'\n",
    ")\n",
    "\n",
    "data = [trace1, trace2]\n",
    "layout = go.Layout(\n",
    "    barmode='group'\n",
    ")\n",
    "\n",
    "fig = go.Figure(data=data, layout=layout)\n",
    "py.iplot(fig, filename='pandas-notebook-plot2')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Exercise\n",
    "\n",
    "Perform some analysis that interests you using the Sydney Acution Data at https://auction-results.domain.com.au/Proofed/PDF/Sydney_Domain.pdf\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}