{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# HIDDEN\n",
    "import numpy as np\n",
    "from datascience import *\n",
    "np.set_printoptions(threshold=50)\n",
    "\n",
    "url = 'http://www.census.gov/popest/data/national/asrh/2014/files/NC-EST2014-AGESEX-RES.csv'\n",
    "full = Table.read_table(url)\n",
    "partial = full.select(['SEX', 'AGE', 4, 8])\n",
    "simple = partial.relabeled(2, '2010').relabeled(3, '2014')\n",
    "census = simple.with_columns([\n",
    "        'Change', simple.column('2014')-simple.column('2010'),\n",
    "        'Growth', (simple.column('2014')/simple.column('2010')) ** (1/4) - 1\n",
    "    ])\n",
    "census.set_format('Growth', PercentFormatter)\n",
    "census.set_format([2, 3, 4], NumberFormatter)\n",
    "pass"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this section, we will continue to use the US Census dataset. We will focus only on the 2014 population estimate."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>SEX</th> <th>AGE</th> <th>2014</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>0   </td> <td>0   </td> <td>3,948,350  </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "        <tr>\n",
       "            <td>0   </td> <td>1   </td> <td>3,962,123  </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "        <tr>\n",
       "            <td>0   </td> <td>2   </td> <td>3,957,772  </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "        <tr>\n",
       "            <td>0   </td> <td>3   </td> <td>4,005,190  </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "        <tr>\n",
       "            <td>0   </td> <td>4   </td> <td>4,003,448  </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "        <tr>\n",
       "            <td>0   </td> <td>5   </td> <td>4,004,858  </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "        <tr>\n",
       "            <td>0   </td> <td>6   </td> <td>4,134,352  </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "        <tr>\n",
       "            <td>0   </td> <td>7   </td> <td>4,154,000  </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "        <tr>\n",
       "            <td>0   </td> <td>8   </td> <td>4,119,524  </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "        <tr>\n",
       "            <td>0   </td> <td>9   </td> <td>4,106,832  </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>\n",
       "<p>... (293 rows omitted)</p"
      ],
      "text/plain": [
       "SEX  | AGE  | 2014\n",
       "0    | 0    | 3,948,350\n",
       "0    | 1    | 3,962,123\n",
       "0    | 2    | 3,957,772\n",
       "0    | 3    | 4,005,190\n",
       "0    | 4    | 4,003,448\n",
       "0    | 5    | 4,004,858\n",
       "0    | 6    | 4,134,352\n",
       "0    | 7    | 4,154,000\n",
       "0    | 8    | 4,119,524\n",
       "0    | 9    | 4,106,832\n",
       "... (293 rows omitted)"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "census_2014 = census.select(['SEX', 'AGE', '2014']).where(census.column('AGE') != 999)\n",
    "census_2014"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Functions can be used to compute new columns in a table based on existing column values. For example, we can transform the codes in the `SEX` column to strings that are easier to interpret."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def male_female(code):\n",
    "    if code == 0:\n",
    "        return 'Total'\n",
    "    elif code == 1:\n",
    "        return 'Male'\n",
    "    elif code == 2:\n",
    "        return 'Female'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This function takes an individual code — 0, 1, or 2 — and returns a string that describes its meaning."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Total'"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "male_female(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Male'"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "male_female(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Female'"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "male_female(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We could also transform ages into age categories."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Teen'"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def age_group(age):\n",
    "    if age < 2:\n",
    "        return 'Baby'\n",
    "    elif age < 13:\n",
    "        return 'Child'\n",
    "    elif age < 20:\n",
    "        return 'Teen'\n",
    "    else:\n",
    "        return 'Adult'\n",
    "    \n",
    "age_group(15)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Apply.** The `apply` method of a table calls a function on each element of a column, forming a new array of return values. To indicate which function to call, just name it (without quotation marks). The name of the column of input values must still appear within quotation marks."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Total', 'Total', 'Total', ..., 'Female', 'Female', 'Female'], \n",
       "      dtype='<U6')"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "census_2014.apply(male_female, 'SEX')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This array, which has the same length as the original `SEX` column of the `population` table, can be used as the values in a new column called `Male/Female` alongside the existing `AGE` and `Population` columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Male/Female</th> <th>Age Group</th> <th>Population</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Total      </td> <td>Baby     </td> <td>3948350   </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "        <tr>\n",
       "            <td>Total      </td> <td>Baby     </td> <td>3962123   </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "        <tr>\n",
       "            <td>Total      </td> <td>Child    </td> <td>3957772   </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "        <tr>\n",
       "            <td>Total      </td> <td>Child    </td> <td>4005190   </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "        <tr>\n",
       "            <td>Total      </td> <td>Child    </td> <td>4003448   </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "        <tr>\n",
       "            <td>Total      </td> <td>Child    </td> <td>4004858   </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "        <tr>\n",
       "            <td>Total      </td> <td>Child    </td> <td>4134352   </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "        <tr>\n",
       "            <td>Total      </td> <td>Child    </td> <td>4154000   </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "        <tr>\n",
       "            <td>Total      </td> <td>Child    </td> <td>4119524   </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "        <tr>\n",
       "            <td>Total      </td> <td>Child    </td> <td>4106832   </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>\n",
       "<p>... (293 rows omitted)</p"
      ],
      "text/plain": [
       "Male/Female | Age Group | Population\n",
       "Total       | Baby      | 3948350\n",
       "Total       | Baby      | 3962123\n",
       "Total       | Child     | 3957772\n",
       "Total       | Child     | 4005190\n",
       "Total       | Child     | 4003448\n",
       "Total       | Child     | 4004858\n",
       "Total       | Child     | 4134352\n",
       "Total       | Child     | 4154000\n",
       "Total       | Child     | 4119524\n",
       "Total       | Child     | 4106832\n",
       "... (293 rows omitted)"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "population = Table().with_columns([\n",
    "        'Male/Female', census_2014.apply(male_female, 'SEX'),\n",
    "        'Age Group',   census_2014.apply(age_group, 'AGE'),\n",
    "        'Population',  census_2014.column('2014')\n",
    "    ])\n",
    "population"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Groups.** The `group` method with a single argument counts the number of rows for each category in a column. The result contains one row per unique value in the grouped column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Age Group</th> <th>count</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Adult    </td> <td>243  </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "        <tr>\n",
       "            <td>Baby     </td> <td>6    </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "        <tr>\n",
       "            <td>Child    </td> <td>33   </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "        <tr>\n",
       "            <td>Teen     </td> <td>21   </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "Age Group | count\n",
       "Adult     | 243\n",
       "Baby      | 6\n",
       "Child     | 33\n",
       "Teen      | 21"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "population.group('Age Group')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The optional second argument names the function that will be used to aggregate values in other columns for all of those rows. For instance, `sum` will sum up the populations in all rows that match each category. This result also contains one row per unique value in the grouped column, but it has the same number of columns as the original table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Age Group</th> <th>Population</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Baby     </td> <td>3948350   </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "        <tr>\n",
       "            <td>Baby     </td> <td>3962123   </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "        <tr>\n",
       "            <td>Child    </td> <td>3957772   </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "        <tr>\n",
       "            <td>Child    </td> <td>4005190   </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "        <tr>\n",
       "            <td>Child    </td> <td>4003448   </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "        <tr>\n",
       "            <td>Child    </td> <td>4004858   </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "        <tr>\n",
       "            <td>Child    </td> <td>4134352   </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "        <tr>\n",
       "            <td>Child    </td> <td>4154000   </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "        <tr>\n",
       "            <td>Child    </td> <td>4119524   </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "        <tr>\n",
       "            <td>Child    </td> <td>4106832   </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>\n",
       "<p>... (91 rows omitted)</p"
      ],
      "text/plain": [
       "Age Group | Population\n",
       "Baby      | 3948350\n",
       "Baby      | 3962123\n",
       "Child     | 3957772\n",
       "Child     | 4005190\n",
       "Child     | 4003448\n",
       "Child     | 4004858\n",
       "Child     | 4134352\n",
       "Child     | 4154000\n",
       "Child     | 4119524\n",
       "Child     | 4106832\n",
       "... (91 rows omitted)"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "totals = population.where(0, 'Total').select(['Age Group', 'Population'])\n",
    "totals"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Age Group</th> <th>Population sum</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Adult    </td> <td>236721454     </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "        <tr>\n",
       "            <td>Baby     </td> <td>7910473       </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "        <tr>\n",
       "            <td>Child    </td> <td>44755656      </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "        <tr>\n",
       "            <td>Teen     </td> <td>29469473      </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "Age Group | Population sum\n",
       "Adult     | 236721454\n",
       "Baby      | 7910473\n",
       "Child     | 44755656\n",
       "Teen      | 29469473"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "totals.group('Age Group', sum)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `groups` method behaves in the same way, but accepts a list of columns as its first argument. The resulting table has one row for every *unique combination* of values that appear together in the grouped columns. Again, a single argument (a list, in this case) gives row counts."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Male/Female</th> <th>Age Group</th> <th>count</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Female     </td> <td>Adult    </td> <td>81   </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "        <tr>\n",
       "            <td>Female     </td> <td>Baby     </td> <td>2    </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "        <tr>\n",
       "            <td>Female     </td> <td>Child    </td> <td>11   </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "        <tr>\n",
       "            <td>Female     </td> <td>Teen     </td> <td>7    </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "        <tr>\n",
       "            <td>Male       </td> <td>Adult    </td> <td>81   </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "        <tr>\n",
       "            <td>Male       </td> <td>Baby     </td> <td>2    </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "        <tr>\n",
       "            <td>Male       </td> <td>Child    </td> <td>11   </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "        <tr>\n",
       "            <td>Male       </td> <td>Teen     </td> <td>7    </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "        <tr>\n",
       "            <td>Total      </td> <td>Adult    </td> <td>81   </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "        <tr>\n",
       "            <td>Total      </td> <td>Baby     </td> <td>2    </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>\n",
       "<p>... (2 rows omitted)</p"
      ],
      "text/plain": [
       "Male/Female | Age Group | count\n",
       "Female      | Adult     | 81\n",
       "Female      | Baby      | 2\n",
       "Female      | Child     | 11\n",
       "Female      | Teen      | 7\n",
       "Male        | Adult     | 81\n",
       "Male        | Baby      | 2\n",
       "Male        | Child     | 11\n",
       "Male        | Teen      | 7\n",
       "Total       | Adult     | 81\n",
       "Total       | Baby      | 2\n",
       "... (2 rows omitted)"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "population.groups(['Male/Female', 'Age Group'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A second argument to `groups` aggregates all other columns that do not appear in the list of grouped columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Male/Female</th> <th>Age Group</th> <th>Population sum</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Female     </td> <td>Adult    </td> <td>121754366     </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "        <tr>\n",
       "            <td>Female     </td> <td>Baby     </td> <td>3869363       </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "        <tr>\n",
       "            <td>Female     </td> <td>Child    </td> <td>21903805      </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "        <tr>\n",
       "            <td>Female     </td> <td>Teen     </td> <td>14393035      </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "        <tr>\n",
       "            <td>Male       </td> <td>Adult    </td> <td>114967088     </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "        <tr>\n",
       "            <td>Male       </td> <td>Baby     </td> <td>4041110       </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "        <tr>\n",
       "            <td>Male       </td> <td>Child    </td> <td>22851851      </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "        <tr>\n",
       "            <td>Male       </td> <td>Teen     </td> <td>15076438      </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "        <tr>\n",
       "            <td>Total      </td> <td>Adult    </td> <td>236721454     </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "        <tr>\n",
       "            <td>Total      </td> <td>Baby     </td> <td>7910473       </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>\n",
       "<p>... (2 rows omitted)</p"
      ],
      "text/plain": [
       "Male/Female | Age Group | Population sum\n",
       "Female      | Adult     | 121754366\n",
       "Female      | Baby      | 3869363\n",
       "Female      | Child     | 21903805\n",
       "Female      | Teen      | 14393035\n",
       "Male        | Adult     | 114967088\n",
       "Male        | Baby      | 4041110\n",
       "Male        | Child     | 22851851\n",
       "Male        | Teen      | 15076438\n",
       "Total       | Adult     | 236721454\n",
       "Total       | Baby      | 7910473\n",
       "... (2 rows omitted)"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "population.groups(['Male/Female', 'Age Group'], sum)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Pivot.** The `pivot` method is closely related to the `groups` method: it groups together rows that share a combination of values. It differs from `groups` because it organizes the resulting values in a grid. The first argument to `pivot` is a column that contains the values that will be used to form new columns in the result. The second argument is a column used for grouping rows. The result gives the count of all rows that share the combination of column and row values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Age Group</th> <th>Female</th> <th>Male</th> <th>Total</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Adult    </td> <td>81    </td> <td>81  </td> <td>81   </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "        <tr>\n",
       "            <td>Baby     </td> <td>2     </td> <td>2   </td> <td>2    </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "        <tr>\n",
       "            <td>Child    </td> <td>11    </td> <td>11  </td> <td>11   </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "        <tr>\n",
       "            <td>Teen     </td> <td>7     </td> <td>7   </td> <td>7    </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "Age Group | Female | Male | Total\n",
       "Adult     | 81     | 81   | 81\n",
       "Baby      | 2      | 2    | 2\n",
       "Child     | 11     | 11   | 11\n",
       "Teen      | 7      | 7    | 7"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "population.pivot('Male/Female', 'Age Group')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "An optional third argument indicates a column of values that will replace the counts in each cell of the grid. The fourth argument indicates how to aggregate all of the values that match the combination of column and row values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Age Group</th> <th>Female</th> <th>Male</th> <th>Total</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Adult    </td> <td>121754366</td> <td>114967088</td> <td>236721454</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "        <tr>\n",
       "            <td>Baby     </td> <td>3869363  </td> <td>4041110  </td> <td>7910473  </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "        <tr>\n",
       "            <td>Child    </td> <td>21903805 </td> <td>22851851 </td> <td>44755656 </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "        <tr>\n",
       "            <td>Teen     </td> <td>14393035 </td> <td>15076438 </td> <td>29469473 </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "Age Group | Female    | Male      | Total\n",
       "Adult     | 121754366 | 114967088 | 236721454\n",
       "Baby      | 3869363   | 4041110   | 7910473\n",
       "Child     | 21903805  | 22851851  | 44755656\n",
       "Teen      | 14393035  | 15076438  | 29469473"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pivoted = population.pivot('Male/Female', 'Age Group', 'Population', sum)\n",
    "pivoted"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The advantage of pivot is that it places grouped values into adjacent columns, so that they can be combined. For instance, this pivoted table allows us to compute the proportion of each age group that is male. We find the surprising result that younger age groups are predominantly male, but among adults there are substantially more females."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Age Group</th> <th>Female</th> <th>Male</th> <th>Total</th> <th>Male Percentage</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Adult    </td> <td>121754366</td> <td>114967088</td> <td>236721454</td> <td>48.57%         </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "        <tr>\n",
       "            <td>Baby     </td> <td>3869363  </td> <td>4041110  </td> <td>7910473  </td> <td>51.09%         </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "        <tr>\n",
       "            <td>Child    </td> <td>21903805 </td> <td>22851851 </td> <td>44755656 </td> <td>51.06%         </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "        <tr>\n",
       "            <td>Teen     </td> <td>14393035 </td> <td>15076438 </td> <td>29469473 </td> <td>51.16%         </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "Age Group | Female    | Male      | Total     | Male Percentage\n",
       "Adult     | 121754366 | 114967088 | 236721454 | 48.57%\n",
       "Baby      | 3869363   | 4041110   | 7910473   | 51.09%\n",
       "Child     | 21903805  | 22851851  | 44755656  | 51.06%\n",
       "Teen      | 14393035  | 15076438  | 29469473  | 51.16%"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pivoted.with_column('Male Percentage', pivoted.column('Male')/pivoted.column('Total')).set_format(4, PercentFormatter)"
   ]
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "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.5.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}