12_pivot.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Pivot tables\n",
    "* A pivot table is itself a DataFrame, which compares two groups on some shared columns, thus:\n",
    "  1. the rows represent one variable that you're interested in\n",
    "  2. the columns another variables, and \n",
    "  3. the cell content is some *aggregate* value of a third column \n",
    "* Often a pivot table includes marginal values as well, which are comparisons across multiple groups (more in a minute)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>world_rank</th>\n",
       "      <th>institution</th>\n",
       "      <th>country</th>\n",
       "      <th>national_rank</th>\n",
       "      <th>quality_of_education</th>\n",
       "      <th>alumni_employment</th>\n",
       "      <th>quality_of_faculty</th>\n",
       "      <th>publications</th>\n",
       "      <th>influence</th>\n",
       "      <th>citations</th>\n",
       "      <th>broad_impact</th>\n",
       "      <th>patents</th>\n",
       "      <th>score</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Harvard University</td>\n",
       "      <td>USA</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5</td>\n",
       "      <td>100.00</td>\n",
       "      <td>2012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Massachusetts Institute of Technology</td>\n",
       "      <td>USA</td>\n",
       "      <td>2</td>\n",
       "      <td>9</td>\n",
       "      <td>17</td>\n",
       "      <td>3</td>\n",
       "      <td>12</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>91.67</td>\n",
       "      <td>2012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Stanford University</td>\n",
       "      <td>USA</td>\n",
       "      <td>3</td>\n",
       "      <td>17</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15</td>\n",
       "      <td>89.50</td>\n",
       "      <td>2012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>University of Cambridge</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>24</td>\n",
       "      <td>4</td>\n",
       "      <td>16</td>\n",
       "      <td>16</td>\n",
       "      <td>11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>50</td>\n",
       "      <td>86.17</td>\n",
       "      <td>2012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>California Institute of Technology</td>\n",
       "      <td>USA</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>29</td>\n",
       "      <td>7</td>\n",
       "      <td>37</td>\n",
       "      <td>22</td>\n",
       "      <td>22</td>\n",
       "      <td>NaN</td>\n",
       "      <td>18</td>\n",
       "      <td>85.21</td>\n",
       "      <td>2012</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   world_rank                            institution         country  \\\n",
       "0           1                     Harvard University             USA   \n",
       "1           2  Massachusetts Institute of Technology             USA   \n",
       "2           3                    Stanford University             USA   \n",
       "3           4                University of Cambridge  United Kingdom   \n",
       "4           5     California Institute of Technology             USA   \n",
       "\n",
       "   national_rank  quality_of_education  alumni_employment  quality_of_faculty  \\\n",
       "0              1                     7                  9                   1   \n",
       "1              2                     9                 17                   3   \n",
       "2              3                    17                 11                   5   \n",
       "3              1                    10                 24                   4   \n",
       "4              4                     2                 29                   7   \n",
       "\n",
       "   publications  influence  citations  broad_impact  patents   score  year  \n",
       "0             1          1          1           NaN        5  100.00  2012  \n",
       "1            12          4          4           NaN        1   91.67  2012  \n",
       "2             4          2          2           NaN       15   89.50  2012  \n",
       "3            16         16         11           NaN       50   86.17  2012  \n",
       "4            37         22         22           NaN       18   85.21  2012  "
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Here we have the Times Higher Education World University Ranking dataset\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "df = pd.read_csv('datasets/cwurData.csv')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [],
   "source": [
    "# Let's say we want to create a new column called *Rank_Level*, where institutions with world ranking 1-100 are\n",
    "# categorized as *first tier* and those with world ranking 101 - 200 are *second tier*, ranking 201 - 300 are\n",
    "# *third tier*, after 301 is *other* top universities.\n",
    "\n",
    "# You do that now, please."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>world_rank</th>\n",
       "      <th>institution</th>\n",
       "      <th>country</th>\n",
       "      <th>national_rank</th>\n",
       "      <th>quality_of_education</th>\n",
       "      <th>alumni_employment</th>\n",
       "      <th>quality_of_faculty</th>\n",
       "      <th>publications</th>\n",
       "      <th>influence</th>\n",
       "      <th>citations</th>\n",
       "      <th>broad_impact</th>\n",
       "      <th>patents</th>\n",
       "      <th>score</th>\n",
       "      <th>year</th>\n",
       "      <th>Rank_Level</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Harvard University</td>\n",
       "      <td>USA</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5</td>\n",
       "      <td>100.00</td>\n",
       "      <td>2012</td>\n",
       "      <td>first tier</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Massachusetts Institute of Technology</td>\n",
       "      <td>USA</td>\n",
       "      <td>2</td>\n",
       "      <td>9</td>\n",
       "      <td>17</td>\n",
       "      <td>3</td>\n",
       "      <td>12</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>91.67</td>\n",
       "      <td>2012</td>\n",
       "      <td>first tier</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Stanford University</td>\n",
       "      <td>USA</td>\n",
       "      <td>3</td>\n",
       "      <td>17</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15</td>\n",
       "      <td>89.50</td>\n",
       "      <td>2012</td>\n",
       "      <td>first tier</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>University of Cambridge</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>24</td>\n",
       "      <td>4</td>\n",
       "      <td>16</td>\n",
       "      <td>16</td>\n",
       "      <td>11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>50</td>\n",
       "      <td>86.17</td>\n",
       "      <td>2012</td>\n",
       "      <td>first tier</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>California Institute of Technology</td>\n",
       "      <td>USA</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>29</td>\n",
       "      <td>7</td>\n",
       "      <td>37</td>\n",
       "      <td>22</td>\n",
       "      <td>22</td>\n",
       "      <td>NaN</td>\n",
       "      <td>18</td>\n",
       "      <td>85.21</td>\n",
       "      <td>2012</td>\n",
       "      <td>first tier</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   world_rank                            institution         country  \\\n",
       "0           1                     Harvard University             USA   \n",
       "1           2  Massachusetts Institute of Technology             USA   \n",
       "2           3                    Stanford University             USA   \n",
       "3           4                University of Cambridge  United Kingdom   \n",
       "4           5     California Institute of Technology             USA   \n",
       "\n",
       "   national_rank  quality_of_education  alumni_employment  quality_of_faculty  \\\n",
       "0              1                     7                  9                   1   \n",
       "1              2                     9                 17                   3   \n",
       "2              3                    17                 11                   5   \n",
       "3              1                    10                 24                   4   \n",
       "4              4                     2                 29                   7   \n",
       "\n",
       "   publications  influence  citations  broad_impact  patents   score  year  \\\n",
       "0             1          1          1           NaN        5  100.00  2012   \n",
       "1            12          4          4           NaN        1   91.67  2012   \n",
       "2             4          2          2           NaN       15   89.50  2012   \n",
       "3            16         16         11           NaN       50   86.17  2012   \n",
       "4            37         22         22           NaN       18   85.21  2012   \n",
       "\n",
       "   Rank_Level  \n",
       "0  first tier  \n",
       "1  first tier  \n",
       "2  first tier  \n",
       "3  first tier  \n",
       "4  first tier  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Put interesting student solution here.\n",
    "def set_rank(row):\n",
    "    if row[\"world_rank\"] <= 100:\n",
    "        row[\"Rank_Level\"]=\"first tier\"\n",
    "    elif row[\"world_rank\"] <= 200:\n",
    "        row[\"Rank_Level\"]=\"second tier\"\n",
    "    elif row[\"world_rank\"] <= 300:\n",
    "        row[\"Rank_Level\"]=\"third tier\"\n",
    "    else:\n",
    "        row[\"Rank_Level\"]=\"other top universities\"\n",
    "    return row\n",
    "\n",
    "df=df.apply(set_rank, axis=1)\n",
    "df.head()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "* Let's pivot! We need two columns, let's say the *country* and our *rank level* these will become our new rows (index)/columns (labels)\n",
    "* Now we need one column of interest for the cell value, let's use the *score*\n",
    "* Then we need on aggregation function, which we'll apply to *score* let's use `np.mean`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "* essentially this means we're comparing two groups, \"Countries\" vs. \"Rank Level\" with respect to score using an average. Think for a moment how you might tackle this with group by"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"4\" halign=\"left\">mean</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Rank_Level</th>\n",
       "      <th>first tier</th>\n",
       "      <th>other top universities</th>\n",
       "      <th>second tier</th>\n",
       "      <th>third tier</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>country</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Argentina</th>\n",
       "      <td>NaN</td>\n",
       "      <td>44.672857</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Australia</th>\n",
       "      <td>47.942500</td>\n",
       "      <td>44.645750</td>\n",
       "      <td>49.242500</td>\n",
       "      <td>47.285000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Austria</th>\n",
       "      <td>NaN</td>\n",
       "      <td>44.864286</td>\n",
       "      <td>NaN</td>\n",
       "      <td>47.066667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Belgium</th>\n",
       "      <td>51.875000</td>\n",
       "      <td>45.081000</td>\n",
       "      <td>49.084000</td>\n",
       "      <td>46.746667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Brazil</th>\n",
       "      <td>NaN</td>\n",
       "      <td>44.499706</td>\n",
       "      <td>49.565000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bulgaria</th>\n",
       "      <td>NaN</td>\n",
       "      <td>44.335000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Canada</th>\n",
       "      <td>53.633846</td>\n",
       "      <td>44.760541</td>\n",
       "      <td>49.218182</td>\n",
       "      <td>46.826364</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Chile</th>\n",
       "      <td>NaN</td>\n",
       "      <td>44.767500</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>China</th>\n",
       "      <td>53.592500</td>\n",
       "      <td>44.564267</td>\n",
       "      <td>47.868000</td>\n",
       "      <td>46.926250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Colombia</th>\n",
       "      <td>NaN</td>\n",
       "      <td>44.432500</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Croatia</th>\n",
       "      <td>NaN</td>\n",
       "      <td>44.770000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Cyprus</th>\n",
       "      <td>NaN</td>\n",
       "      <td>44.210000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Czech Republic</th>\n",
       "      <td>NaN</td>\n",
       "      <td>44.587778</td>\n",
       "      <td>NaN</td>\n",
       "      <td>46.320000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Denmark</th>\n",
       "      <td>49.180000</td>\n",
       "      <td>45.177500</td>\n",
       "      <td>48.965000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Egypt</th>\n",
       "      <td>NaN</td>\n",
       "      <td>44.230000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Estonia</th>\n",
       "      <td>NaN</td>\n",
       "      <td>44.810000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Finland</th>\n",
       "      <td>44.415000</td>\n",
       "      <td>45.062500</td>\n",
       "      <td>50.400000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>France</th>\n",
       "      <td>51.914444</td>\n",
       "      <td>44.609028</td>\n",
       "      <td>48.888571</td>\n",
       "      <td>47.125833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Germany</th>\n",
       "      <td>49.153636</td>\n",
       "      <td>44.978305</td>\n",
       "      <td>48.695556</td>\n",
       "      <td>46.910370</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Greece</th>\n",
       "      <td>NaN</td>\n",
       "      <td>44.854286</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Hong Kong</th>\n",
       "      <td>NaN</td>\n",
       "      <td>45.284286</td>\n",
       "      <td>48.625000</td>\n",
       "      <td>47.100000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Hungary</th>\n",
       "      <td>NaN</td>\n",
       "      <td>44.603333</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Iceland</th>\n",
       "      <td>NaN</td>\n",
       "      <td>44.980000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>India</th>\n",
       "      <td>NaN</td>\n",
       "      <td>44.713226</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Iran</th>\n",
       "      <td>NaN</td>\n",
       "      <td>44.270000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ireland</th>\n",
       "      <td>NaN</td>\n",
       "      <td>44.542500</td>\n",
       "      <td>47.845000</td>\n",
       "      <td>46.780000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Israel</th>\n",
       "      <td>56.307143</td>\n",
       "      <td>45.013333</td>\n",
       "      <td>50.005000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Italy</th>\n",
       "      <td>48.736667</td>\n",
       "      <td>44.964177</td>\n",
       "      <td>48.618000</td>\n",
       "      <td>46.861111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Japan</th>\n",
       "      <td>58.812692</td>\n",
       "      <td>44.641583</td>\n",
       "      <td>49.157143</td>\n",
       "      <td>46.546667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Lebanon</th>\n",
       "      <td>NaN</td>\n",
       "      <td>44.655000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Lithuania</th>\n",
       "      <td>NaN</td>\n",
       "      <td>44.355000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Malaysia</th>\n",
       "      <td>NaN</td>\n",
       "      <td>45.008333</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mexico</th>\n",
       "      <td>NaN</td>\n",
       "      <td>45.102500</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Netherlands</th>\n",
       "      <td>48.378333</td>\n",
       "      <td>45.163333</td>\n",
       "      <td>49.963636</td>\n",
       "      <td>46.656667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>New Zealand</th>\n",
       "      <td>NaN</td>\n",
       "      <td>44.832000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>46.515000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Norway</th>\n",
       "      <td>47.056667</td>\n",
       "      <td>44.950000</td>\n",
       "      <td>50.700000</td>\n",
       "      <td>46.446667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Poland</th>\n",
       "      <td>NaN</td>\n",
       "      <td>44.497222</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Portugal</th>\n",
       "      <td>NaN</td>\n",
       "      <td>44.717273</td>\n",
       "      <td>NaN</td>\n",
       "      <td>46.480000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Puerto Rico</th>\n",
       "      <td>NaN</td>\n",
       "      <td>44.175000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Romania</th>\n",
       "      <td>NaN</td>\n",
       "      <td>44.133333</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Russia</th>\n",
       "      <td>51.846667</td>\n",
       "      <td>44.462500</td>\n",
       "      <td>NaN</td>\n",
       "      <td>46.520000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Saudi Arabia</th>\n",
       "      <td>NaN</td>\n",
       "      <td>44.511250</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Serbia</th>\n",
       "      <td>NaN</td>\n",
       "      <td>44.420000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Singapore</th>\n",
       "      <td>50.720000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>49.320000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Slovak Republic</th>\n",
       "      <td>NaN</td>\n",
       "      <td>44.490000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Slovenia</th>\n",
       "      <td>NaN</td>\n",
       "      <td>44.615000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>South Africa</th>\n",
       "      <td>NaN</td>\n",
       "      <td>45.246667</td>\n",
       "      <td>49.575000</td>\n",
       "      <td>46.680000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>South Korea</th>\n",
       "      <td>55.990000</td>\n",
       "      <td>44.805714</td>\n",
       "      <td>49.136667</td>\n",
       "      <td>47.490000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Spain</th>\n",
       "      <td>NaN</td>\n",
       "      <td>44.724730</td>\n",
       "      <td>49.310000</td>\n",
       "      <td>46.675000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sweden</th>\n",
       "      <td>50.672000</td>\n",
       "      <td>45.272500</td>\n",
       "      <td>49.306250</td>\n",
       "      <td>46.456667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Switzerland</th>\n",
       "      <td>54.005000</td>\n",
       "      <td>44.625000</td>\n",
       "      <td>48.184000</td>\n",
       "      <td>47.930000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Taiwan</th>\n",
       "      <td>54.210000</td>\n",
       "      <td>44.476667</td>\n",
       "      <td>NaN</td>\n",
       "      <td>47.065000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Thailand</th>\n",
       "      <td>NaN</td>\n",
       "      <td>44.830000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>46.550000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Turkey</th>\n",
       "      <td>NaN</td>\n",
       "      <td>44.481000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>USA</th>\n",
       "      <td>61.066726</td>\n",
       "      <td>44.871718</td>\n",
       "      <td>49.069524</td>\n",
       "      <td>46.818333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Uganda</th>\n",
       "      <td>NaN</td>\n",
       "      <td>44.280000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>United Arab Emirates</th>\n",
       "      <td>NaN</td>\n",
       "      <td>44.220000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>United Kingdom</th>\n",
       "      <td>63.937931</td>\n",
       "      <td>44.881299</td>\n",
       "      <td>48.957500</td>\n",
       "      <td>46.862273</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Uruguay</th>\n",
       "      <td>NaN</td>\n",
       "      <td>44.255000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           mean                                              \n",
       "Rank_Level           first tier other top universities second tier third tier\n",
       "country                                                                      \n",
       "Argentina                   NaN              44.672857         NaN        NaN\n",
       "Australia             47.942500              44.645750   49.242500  47.285000\n",
       "Austria                     NaN              44.864286         NaN  47.066667\n",
       "Belgium               51.875000              45.081000   49.084000  46.746667\n",
       "Brazil                      NaN              44.499706   49.565000        NaN\n",
       "Bulgaria                    NaN              44.335000         NaN        NaN\n",
       "Canada                53.633846              44.760541   49.218182  46.826364\n",
       "Chile                       NaN              44.767500         NaN        NaN\n",
       "China                 53.592500              44.564267   47.868000  46.926250\n",
       "Colombia                    NaN              44.432500         NaN        NaN\n",
       "Croatia                     NaN              44.770000         NaN        NaN\n",
       "Cyprus                      NaN              44.210000         NaN        NaN\n",
       "Czech Republic              NaN              44.587778         NaN  46.320000\n",
       "Denmark               49.180000              45.177500   48.965000        NaN\n",
       "Egypt                       NaN              44.230000         NaN        NaN\n",
       "Estonia                     NaN              44.810000         NaN        NaN\n",
       "Finland               44.415000              45.062500   50.400000        NaN\n",
       "France                51.914444              44.609028   48.888571  47.125833\n",
       "Germany               49.153636              44.978305   48.695556  46.910370\n",
       "Greece                      NaN              44.854286         NaN        NaN\n",
       "Hong Kong                   NaN              45.284286   48.625000  47.100000\n",
       "Hungary                     NaN              44.603333         NaN        NaN\n",
       "Iceland                     NaN              44.980000         NaN        NaN\n",
       "India                       NaN              44.713226         NaN        NaN\n",
       "Iran                        NaN              44.270000         NaN        NaN\n",
       "Ireland                     NaN              44.542500   47.845000  46.780000\n",
       "Israel                56.307143              45.013333   50.005000        NaN\n",
       "Italy                 48.736667              44.964177   48.618000  46.861111\n",
       "Japan                 58.812692              44.641583   49.157143  46.546667\n",
       "Lebanon                     NaN              44.655000         NaN        NaN\n",
       "Lithuania                   NaN              44.355000         NaN        NaN\n",
       "Malaysia                    NaN              45.008333         NaN        NaN\n",
       "Mexico                      NaN              45.102500         NaN        NaN\n",
       "Netherlands           48.378333              45.163333   49.963636  46.656667\n",
       "New Zealand                 NaN              44.832000         NaN  46.515000\n",
       "Norway                47.056667              44.950000   50.700000  46.446667\n",
       "Poland                      NaN              44.497222         NaN        NaN\n",
       "Portugal                    NaN              44.717273         NaN  46.480000\n",
       "Puerto Rico                 NaN              44.175000         NaN        NaN\n",
       "Romania                     NaN              44.133333         NaN        NaN\n",
       "Russia                51.846667              44.462500         NaN  46.520000\n",
       "Saudi Arabia                NaN              44.511250         NaN        NaN\n",
       "Serbia                      NaN              44.420000         NaN        NaN\n",
       "Singapore             50.720000                    NaN   49.320000        NaN\n",
       "Slovak Republic             NaN              44.490000         NaN        NaN\n",
       "Slovenia                    NaN              44.615000         NaN        NaN\n",
       "South Africa                NaN              45.246667   49.575000  46.680000\n",
       "South Korea           55.990000              44.805714   49.136667  47.490000\n",
       "Spain                       NaN              44.724730   49.310000  46.675000\n",
       "Sweden                50.672000              45.272500   49.306250  46.456667\n",
       "Switzerland           54.005000              44.625000   48.184000  47.930000\n",
       "Taiwan                54.210000              44.476667         NaN  47.065000\n",
       "Thailand                    NaN              44.830000         NaN  46.550000\n",
       "Turkey                      NaN              44.481000         NaN        NaN\n",
       "USA                   61.066726              44.871718   49.069524  46.818333\n",
       "Uganda                      NaN              44.280000         NaN        NaN\n",
       "United Arab Emirates        NaN              44.220000         NaN        NaN\n",
       "United Kingdom        63.937931              44.881299   48.957500  46.862273\n",
       "Uruguay                     NaN              44.255000         NaN        NaN"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.pivot_table(values='score', \n",
    "               index='country', \n",
    "               columns='Rank_Level', \n",
    "               aggfunc=[np.mean])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "* we notice that there are some NaN values, e.g. Argentia has only observations in the \"Other Top Unversities\" category"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "* pivot tables aren't limited to one aggregation! We could use multiple functions and see those results with heirarchical column labels"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"4\" halign=\"left\">mean</th>\n",
       "      <th colspan=\"4\" halign=\"left\">amax</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Rank_Level</th>\n",
       "      <th>first tier</th>\n",
       "      <th>other top universities</th>\n",
       "      <th>second tier</th>\n",
       "      <th>third tier</th>\n",
       "      <th>first tier</th>\n",
       "      <th>other top universities</th>\n",
       "      <th>second tier</th>\n",
       "      <th>third tier</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>country</th>\n",
       "      <th></th>\n",
       "      <th></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>Argentina</th>\n",
       "      <td>NaN</td>\n",
       "      <td>44.672857</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>45.66</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Australia</th>\n",
       "      <td>47.9425</td>\n",
       "      <td>44.645750</td>\n",
       "      <td>49.2425</td>\n",
       "      <td>47.285000</td>\n",
       "      <td>51.61</td>\n",
       "      <td>45.97</td>\n",
       "      <td>50.40</td>\n",
       "      <td>47.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Austria</th>\n",
       "      <td>NaN</td>\n",
       "      <td>44.864286</td>\n",
       "      <td>NaN</td>\n",
       "      <td>47.066667</td>\n",
       "      <td>NaN</td>\n",
       "      <td>46.29</td>\n",
       "      <td>NaN</td>\n",
       "      <td>47.78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Belgium</th>\n",
       "      <td>51.8750</td>\n",
       "      <td>45.081000</td>\n",
       "      <td>49.0840</td>\n",
       "      <td>46.746667</td>\n",
       "      <td>52.03</td>\n",
       "      <td>46.21</td>\n",
       "      <td>49.73</td>\n",
       "      <td>47.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Brazil</th>\n",
       "      <td>NaN</td>\n",
       "      <td>44.499706</td>\n",
       "      <td>49.5650</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>46.08</td>\n",
       "      <td>49.82</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 mean                                                \\\n",
       "Rank_Level first tier other top universities second tier third tier   \n",
       "country                                                               \n",
       "Argentina         NaN              44.672857         NaN        NaN   \n",
       "Australia     47.9425              44.645750     49.2425  47.285000   \n",
       "Austria           NaN              44.864286         NaN  47.066667   \n",
       "Belgium       51.8750              45.081000     49.0840  46.746667   \n",
       "Brazil            NaN              44.499706     49.5650        NaN   \n",
       "\n",
       "                 amax                                                \n",
       "Rank_Level first tier other top universities second tier third tier  \n",
       "country                                                              \n",
       "Argentina         NaN                  45.66         NaN        NaN  \n",
       "Australia       51.61                  45.97       50.40      47.47  \n",
       "Austria           NaN                  46.29         NaN      47.78  \n",
       "Belgium         52.03                  46.21       49.73      47.14  \n",
       "Brazil            NaN                  46.08       49.82        NaN  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.pivot_table(values='score', index='country', columns='Rank_Level', \n",
    "               aggfunc=[np.mean, np.max]).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"5\" halign=\"left\">mean</th>\n",
       "      <th colspan=\"5\" halign=\"left\">std</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Rank_Level</th>\n",
       "      <th>first tier</th>\n",
       "      <th>other top universities</th>\n",
       "      <th>second tier</th>\n",
       "      <th>third tier</th>\n",
       "      <th>All</th>\n",
       "      <th>first tier</th>\n",
       "      <th>other top universities</th>\n",
       "      <th>second tier</th>\n",
       "      <th>third tier</th>\n",
       "      <th>All</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>country</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></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>Argentina</th>\n",
       "      <td>NaN</td>\n",
       "      <td>44.672857</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>44.672857</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.599130</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.599130</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Australia</th>\n",
       "      <td>47.9425</td>\n",
       "      <td>44.645750</td>\n",
       "      <td>49.2425</td>\n",
       "      <td>47.285000</td>\n",
       "      <td>45.825517</td>\n",
       "      <td>3.798397</td>\n",
       "      <td>0.386542</td>\n",
       "      <td>0.820640</td>\n",
       "      <td>0.261630</td>\n",
       "      <td>2.297206</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Austria</th>\n",
       "      <td>NaN</td>\n",
       "      <td>44.864286</td>\n",
       "      <td>NaN</td>\n",
       "      <td>47.066667</td>\n",
       "      <td>45.139583</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.590191</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.695725</td>\n",
       "      <td>0.947929</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Belgium</th>\n",
       "      <td>51.8750</td>\n",
       "      <td>45.081000</td>\n",
       "      <td>49.0840</td>\n",
       "      <td>46.746667</td>\n",
       "      <td>47.011000</td>\n",
       "      <td>0.219203</td>\n",
       "      <td>0.786419</td>\n",
       "      <td>0.829958</td>\n",
       "      <td>0.481283</td>\n",
       "      <td>2.461225</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Brazil</th>\n",
       "      <td>NaN</td>\n",
       "      <td>44.499706</td>\n",
       "      <td>49.5650</td>\n",
       "      <td>NaN</td>\n",
       "      <td>44.781111</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.490476</td>\n",
       "      <td>0.360624</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.270909</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 mean                                                \\\n",
       "Rank_Level first tier other top universities second tier third tier   \n",
       "country                                                               \n",
       "Argentina         NaN              44.672857         NaN        NaN   \n",
       "Australia     47.9425              44.645750     49.2425  47.285000   \n",
       "Austria           NaN              44.864286         NaN  47.066667   \n",
       "Belgium       51.8750              45.081000     49.0840  46.746667   \n",
       "Brazil            NaN              44.499706     49.5650        NaN   \n",
       "\n",
       "                             std                                     \\\n",
       "Rank_Level        All first tier other top universities second tier   \n",
       "country                                                               \n",
       "Argentina   44.672857        NaN               0.599130         NaN   \n",
       "Australia   45.825517   3.798397               0.386542    0.820640   \n",
       "Austria     45.139583        NaN               0.590191         NaN   \n",
       "Belgium     47.011000   0.219203               0.786419    0.829958   \n",
       "Brazil      44.781111        NaN               0.490476    0.360624   \n",
       "\n",
       "                                 \n",
       "Rank_Level third tier       All  \n",
       "country                          \n",
       "Argentina         NaN  0.599130  \n",
       "Australia    0.261630  2.297206  \n",
       "Austria      0.695725  0.947929  \n",
       "Belgium      0.481283  2.461225  \n",
       "Brazil            NaN  1.270909  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# we can also provide those marginal values\n",
    "df.pivot_table(values='score', \n",
    "               index='country', \n",
    "               columns='Rank_Level', \n",
    "               aggfunc=[np.mean, np.std], \n",
    "               margins=True).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['Argentina', 'Australia', 'Austria', 'Belgium', 'Brazil', 'Bulgaria',\n",
      "       'Canada', 'Chile', 'China', 'Colombia', 'Croatia', 'Cyprus',\n",
      "       'Czech Republic', 'Denmark', 'Egypt', 'Estonia', 'Finland', 'France',\n",
      "       'Germany', 'Greece', 'Hong Kong', 'Hungary', 'Iceland', 'India', 'Iran',\n",
      "       'Ireland', 'Israel', 'Italy', 'Japan', 'Lebanon', 'Lithuania',\n",
      "       'Malaysia', 'Mexico', 'Netherlands', 'New Zealand', 'Norway', 'Poland',\n",
      "       'Portugal', 'Puerto Rico', 'Romania', 'Russia', 'Saudi Arabia',\n",
      "       'Serbia', 'Singapore', 'Slovak Republic', 'Slovenia', 'South Africa',\n",
      "       'South Korea', 'Spain', 'Sweden', 'Switzerland', 'Taiwan', 'Thailand',\n",
      "       'Turkey', 'USA', 'Uganda', 'United Arab Emirates', 'United Kingdom',\n",
      "       'Uruguay', 'All'],\n",
      "      dtype='object', name='country')\n",
      "MultiIndex([('mean',             'first tier'),\n",
      "            ('mean', 'other top universities'),\n",
      "            ('mean',            'second tier'),\n",
      "            ('mean',             'third tier'),\n",
      "            ('mean',                    'All'),\n",
      "            ('amax',             'first tier'),\n",
      "            ('amax', 'other top universities'),\n",
      "            ('amax',            'second tier'),\n",
      "            ('amax',             'third tier'),\n",
      "            ('amax',                    'All')],\n",
      "           names=[None, 'Rank_Level'])\n"
     ]
    }
   ],
   "source": [
    "# A pivot table is just a multi-level dataframe\n",
    "new_df=df.pivot_table(values='score', index='country', columns='Rank_Level', aggfunc=[np.mean, np.max], \n",
    "               margins=True)\n",
    "# Now let's look at the index\n",
    "print(new_df.index)\n",
    "# And let's look at the columns\n",
    "print(new_df.columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [],
   "source": [
    "# How would we query this if we want to get the average scores of First Tier universities broken down by country?\n",
    "# You do that now, please."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "* Let's get weird. We can `stack` and `unstack` columns in our dataframe.\n",
    "* `stack` takes pivots the lowermost column index to become the innermost row index. unstack is the inverse\n",
    "* let's look back at that pivot table..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "new_df.head() #we want to take the tier of uni and move it to a row index, so we are stacking...."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>mean</th>\n",
       "      <th>amax</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>country</th>\n",
       "      <th>Rank_Level</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Argentina</th>\n",
       "      <th>other top universities</th>\n",
       "      <td>44.672857</td>\n",
       "      <td>45.66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>All</th>\n",
       "      <td>44.672857</td>\n",
       "      <td>45.66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Australia</th>\n",
       "      <th>first tier</th>\n",
       "      <td>47.942500</td>\n",
       "      <td>51.61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>other top universities</th>\n",
       "      <td>44.645750</td>\n",
       "      <td>45.97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>second tier</th>\n",
       "      <td>49.242500</td>\n",
       "      <td>50.40</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                       mean   amax\n",
       "country   Rank_Level                              \n",
       "Argentina other top universities  44.672857  45.66\n",
       "          All                     44.672857  45.66\n",
       "Australia first tier              47.942500  51.61\n",
       "          other top universities  44.645750  45.97\n",
       "          second tier             49.242500  50.40"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_df.stack().head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"5\" halign=\"left\">mean</th>\n",
       "      <th colspan=\"5\" halign=\"left\">amax</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Rank_Level</th>\n",
       "      <th>first tier</th>\n",
       "      <th>other top universities</th>\n",
       "      <th>second tier</th>\n",
       "      <th>third tier</th>\n",
       "      <th>All</th>\n",
       "      <th>first tier</th>\n",
       "      <th>other top universities</th>\n",
       "      <th>second tier</th>\n",
       "      <th>third tier</th>\n",
       "      <th>All</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>country</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></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>Argentina</th>\n",
       "      <td>NaN</td>\n",
       "      <td>44.672857</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>44.672857</td>\n",
       "      <td>NaN</td>\n",
       "      <td>45.66</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>45.66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Australia</th>\n",
       "      <td>47.9425</td>\n",
       "      <td>44.645750</td>\n",
       "      <td>49.2425</td>\n",
       "      <td>47.285000</td>\n",
       "      <td>45.825517</td>\n",
       "      <td>51.61</td>\n",
       "      <td>45.97</td>\n",
       "      <td>50.40</td>\n",
       "      <td>47.47</td>\n",
       "      <td>51.61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Austria</th>\n",
       "      <td>NaN</td>\n",
       "      <td>44.864286</td>\n",
       "      <td>NaN</td>\n",
       "      <td>47.066667</td>\n",
       "      <td>45.139583</td>\n",
       "      <td>NaN</td>\n",
       "      <td>46.29</td>\n",
       "      <td>NaN</td>\n",
       "      <td>47.78</td>\n",
       "      <td>47.78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Belgium</th>\n",
       "      <td>51.8750</td>\n",
       "      <td>45.081000</td>\n",
       "      <td>49.0840</td>\n",
       "      <td>46.746667</td>\n",
       "      <td>47.011000</td>\n",
       "      <td>52.03</td>\n",
       "      <td>46.21</td>\n",
       "      <td>49.73</td>\n",
       "      <td>47.14</td>\n",
       "      <td>52.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Brazil</th>\n",
       "      <td>NaN</td>\n",
       "      <td>44.499706</td>\n",
       "      <td>49.5650</td>\n",
       "      <td>NaN</td>\n",
       "      <td>44.781111</td>\n",
       "      <td>NaN</td>\n",
       "      <td>46.08</td>\n",
       "      <td>49.82</td>\n",
       "      <td>NaN</td>\n",
       "      <td>49.82</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 mean                                                \\\n",
       "Rank_Level first tier other top universities second tier third tier   \n",
       "country                                                               \n",
       "Argentina         NaN              44.672857         NaN        NaN   \n",
       "Australia     47.9425              44.645750     49.2425  47.285000   \n",
       "Austria           NaN              44.864286         NaN  47.066667   \n",
       "Belgium       51.8750              45.081000     49.0840  46.746667   \n",
       "Brazil            NaN              44.499706     49.5650        NaN   \n",
       "\n",
       "                            amax                                     \\\n",
       "Rank_Level        All first tier other top universities second tier   \n",
       "country                                                               \n",
       "Argentina   44.672857        NaN                  45.66         NaN   \n",
       "Australia   45.825517      51.61                  45.97       50.40   \n",
       "Austria     45.139583        NaN                  46.29         NaN   \n",
       "Belgium     47.011000      52.03                  46.21       49.73   \n",
       "Brazil      44.781111        NaN                  46.08       49.82   \n",
       "\n",
       "                              \n",
       "Rank_Level third tier    All  \n",
       "country                       \n",
       "Argentina         NaN  45.66  \n",
       "Australia       47.47  51.61  \n",
       "Austria         47.78  47.78  \n",
       "Belgium         47.14  52.03  \n",
       "Brazil            NaN  49.82  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# It can get complex! You are just comparing two groups and a value (or multiple values in this case!)\n",
    "# we can unstack() all the way if we want to, which means move a row index into a column index\n",
    "new_df.head() #let's pivot rank_level now into the column, what shape do you think this will create?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "      Rank_Level  country  \n",
       "mean  first tier  Argentina          NaN\n",
       "                  Australia    47.942500\n",
       "                  Austria            NaN\n",
       "                  Belgium      51.875000\n",
       "                  Brazil             NaN\n",
       "                  Bulgaria           NaN\n",
       "                  Canada       53.633846\n",
       "                  Chile              NaN\n",
       "                  China        53.592500\n",
       "                  Colombia           NaN\n",
       "dtype: float64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_df.unstack().head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "* Remember, you can pass any function you want to the aggregate function, including those that you define yourself!"
   ]
  }
 ],
 "metadata": {
  "celltoolbar": "Slideshow",
  "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.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}