{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "toc": true
   },
   "source": [
    "<h1>Table of Contents<span class=\"tocSkip\"></span></h1>\n",
    "<div class=\"toc\"><ul class=\"toc-item\"><li><span><a href=\"#Read-in-the-data\" data-toc-modified-id=\"Read-in-the-data-1\"><span class=\"toc-item-num\">1&nbsp;&nbsp;</span>Read in the data</a></span></li><li><span><a href=\"#Pivot-the-data\" data-toc-modified-id=\"Pivot-the-data-2\"><span class=\"toc-item-num\">2&nbsp;&nbsp;</span>Pivot the data</a></span><ul class=\"toc-item\"><li><ul class=\"toc-item\"><li><span><a href=\"#Alternatieve-syntax\" data-toc-modified-id=\"Alternatieve-syntax-2.0.1\"><span class=\"toc-item-num\">2.0.1&nbsp;&nbsp;</span>Alternatieve syntax</a></span></li></ul></li></ul></li><li><span><a href=\"#Advanced-Pivot-Table-Filtering\" data-toc-modified-id=\"Advanced-Pivot-Table-Filtering-3\"><span class=\"toc-item-num\">3&nbsp;&nbsp;</span>Advanced Pivot Table Filtering</a></span></li></ul></div>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The pivot table is a powerful tool to summarize and present data. Pandas has a function which allows you to quickly convert a DataFrame to a pivot table - \n",
    "http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.pivot_table.html\n",
    "\n",
    "This function is very useful but sometimes it can be tricky to remember how to use it to get the data formatted in a way you need.\n",
    "\n",
    "This notebook will walk through how to use the pivot_table.\n",
    "\n",
    "The full blog post for this article is here - http://pbpython.com/pandas-pivot-table-explained.html"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Read in the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Read in our sales funnel data into our DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Account</th>\n",
       "      <th>Name</th>\n",
       "      <th>Rep</th>\n",
       "      <th>Manager</th>\n",
       "      <th>Product</th>\n",
       "      <th>Quantity</th>\n",
       "      <th>Price</th>\n",
       "      <th>Status</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>714466</td>\n",
       "      <td>Trantow-Barrows</td>\n",
       "      <td>Craig Booker</td>\n",
       "      <td>Debra Henley</td>\n",
       "      <td>CPU</td>\n",
       "      <td>1</td>\n",
       "      <td>30000</td>\n",
       "      <td>presented</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>714466</td>\n",
       "      <td>Trantow-Barrows</td>\n",
       "      <td>Craig Booker</td>\n",
       "      <td>Debra Henley</td>\n",
       "      <td>Software</td>\n",
       "      <td>1</td>\n",
       "      <td>10000</td>\n",
       "      <td>presented</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>714466</td>\n",
       "      <td>Trantow-Barrows</td>\n",
       "      <td>Craig Booker</td>\n",
       "      <td>Debra Henley</td>\n",
       "      <td>Maintenance</td>\n",
       "      <td>2</td>\n",
       "      <td>5000</td>\n",
       "      <td>pending</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>737550</td>\n",
       "      <td>Fritsch, Russel and Anderson</td>\n",
       "      <td>Craig Booker</td>\n",
       "      <td>Debra Henley</td>\n",
       "      <td>CPU</td>\n",
       "      <td>1</td>\n",
       "      <td>35000</td>\n",
       "      <td>declined</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>146832</td>\n",
       "      <td>Kiehn-Spinka</td>\n",
       "      <td>Daniel Hilton</td>\n",
       "      <td>Debra Henley</td>\n",
       "      <td>CPU</td>\n",
       "      <td>2</td>\n",
       "      <td>65000</td>\n",
       "      <td>won</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Account                          Name            Rep       Manager  \\\n",
       "0   714466               Trantow-Barrows   Craig Booker  Debra Henley   \n",
       "1   714466               Trantow-Barrows   Craig Booker  Debra Henley   \n",
       "2   714466               Trantow-Barrows   Craig Booker  Debra Henley   \n",
       "3   737550  Fritsch, Russel and Anderson   Craig Booker  Debra Henley   \n",
       "4   146832                  Kiehn-Spinka  Daniel Hilton  Debra Henley   \n",
       "\n",
       "       Product  Quantity  Price     Status  \n",
       "0          CPU         1  30000  presented  \n",
       "1     Software         1  10000  presented  \n",
       "2  Maintenance         2   5000    pending  \n",
       "3          CPU         1  35000   declined  \n",
       "4          CPU         2  65000        won  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_excel(\"http://maartenmarx.nl/teaching/DataScience/Data/sales-funnel.xlsx\")  # locally the file is in the ../Data folder\n",
    "#df = pd.read_excel(\"../in/sales-funnel.xlsx\")\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For convenience sake, let's define the status column as a category and set the order we want to view.\n",
    "\n",
    "This isn't strictly required but helps us keep the order we want as we work through analyzing the data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"Status\"] = df[\"Status\"].astype(\"category\")\n",
    "df[\"Status\"].cat.set_categories([\"won\",\"pending\",\"presented\",\"declined\"],inplace=True)\n",
    "#df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Pivot the data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As we build up the pivot table, I think it's easiest to take it one step at a time. Add items one at a time and check each step to verify you are getting the results you expect.\n",
    "\n",
    "The simplest pivot table must have a dataframe and an index. In this case, let's use the Name as our index."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Account</th>\n",
       "      <th>Price</th>\n",
       "      <th>Quantity</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Name</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Barton LLC</th>\n",
       "      <td>740150</td>\n",
       "      <td>35000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Fritsch, Russel and Anderson</th>\n",
       "      <td>737550</td>\n",
       "      <td>35000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Herman LLC</th>\n",
       "      <td>141962</td>\n",
       "      <td>65000</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jerde-Hilpert</th>\n",
       "      <td>412290</td>\n",
       "      <td>5000</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kassulke, Ondricka and Metz</th>\n",
       "      <td>307599</td>\n",
       "      <td>7000</td>\n",
       "      <td>3.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Keeling LLC</th>\n",
       "      <td>688981</td>\n",
       "      <td>100000</td>\n",
       "      <td>5.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kiehn-Spinka</th>\n",
       "      <td>146832</td>\n",
       "      <td>65000</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Koepp Ltd</th>\n",
       "      <td>729833</td>\n",
       "      <td>35000</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kulas Inc</th>\n",
       "      <td>218895</td>\n",
       "      <td>25000</td>\n",
       "      <td>1.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Purdy-Kunde</th>\n",
       "      <td>163416</td>\n",
       "      <td>30000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Stokes LLC</th>\n",
       "      <td>239344</td>\n",
       "      <td>7500</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Trantow-Barrows</th>\n",
       "      <td>714466</td>\n",
       "      <td>15000</td>\n",
       "      <td>1.333333</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              Account   Price  Quantity\n",
       "Name                                                   \n",
       "Barton LLC                     740150   35000  1.000000\n",
       "Fritsch, Russel and Anderson   737550   35000  1.000000\n",
       "Herman LLC                     141962   65000  2.000000\n",
       "Jerde-Hilpert                  412290    5000  2.000000\n",
       "Kassulke, Ondricka and Metz    307599    7000  3.000000\n",
       "Keeling LLC                    688981  100000  5.000000\n",
       "Kiehn-Spinka                   146832   65000  2.000000\n",
       "Koepp Ltd                      729833   35000  2.000000\n",
       "Kulas Inc                      218895   25000  1.500000\n",
       "Purdy-Kunde                    163416   30000  1.000000\n",
       "Stokes LLC                     239344    7500  1.000000\n",
       "Trantow-Barrows                714466   15000  1.333333"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(df,index=[\"Name\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Alternatieve syntax\n",
    "* Gebruik de `pivot_table` methode van een DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Account</th>\n",
       "      <th>Price</th>\n",
       "      <th>Quantity</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Name</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Barton LLC</th>\n",
       "      <td>740150</td>\n",
       "      <td>35000</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Fritsch, Russel and Anderson</th>\n",
       "      <td>737550</td>\n",
       "      <td>35000</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Herman LLC</th>\n",
       "      <td>141962</td>\n",
       "      <td>65000</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jerde-Hilpert</th>\n",
       "      <td>412290</td>\n",
       "      <td>5000</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kassulke, Ondricka and Metz</th>\n",
       "      <td>307599</td>\n",
       "      <td>7000</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              Account  Price  Quantity\n",
       "Name                                                  \n",
       "Barton LLC                     740150  35000       1.0\n",
       "Fritsch, Russel and Anderson   737550  35000       1.0\n",
       "Herman LLC                     141962  65000       2.0\n",
       "Jerde-Hilpert                  412290   5000       2.0\n",
       "Kassulke, Ondricka and Metz    307599   7000       3.0"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.pivot_table(index=[\"Name\"]).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can have multiple indexes as well. In fact, most of the pivot_table args can take multiple values via a list."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>Account</th>\n",
       "      <th>Price</th>\n",
       "      <th>Quantity</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Name</th>\n",
       "      <th>Rep</th>\n",
       "      <th>Manager</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Barton LLC</th>\n",
       "      <th>John Smith</th>\n",
       "      <th>Debra Henley</th>\n",
       "      <td>740150</td>\n",
       "      <td>35000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Fritsch, Russel and Anderson</th>\n",
       "      <th>Craig Booker</th>\n",
       "      <th>Debra Henley</th>\n",
       "      <td>737550</td>\n",
       "      <td>35000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Herman LLC</th>\n",
       "      <th>Cedric Moss</th>\n",
       "      <th>Fred Anderson</th>\n",
       "      <td>141962</td>\n",
       "      <td>65000</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jerde-Hilpert</th>\n",
       "      <th>John Smith</th>\n",
       "      <th>Debra Henley</th>\n",
       "      <td>412290</td>\n",
       "      <td>5000</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kassulke, Ondricka and Metz</th>\n",
       "      <th>Wendy Yule</th>\n",
       "      <th>Fred Anderson</th>\n",
       "      <td>307599</td>\n",
       "      <td>7000</td>\n",
       "      <td>3.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Keeling LLC</th>\n",
       "      <th>Wendy Yule</th>\n",
       "      <th>Fred Anderson</th>\n",
       "      <td>688981</td>\n",
       "      <td>100000</td>\n",
       "      <td>5.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kiehn-Spinka</th>\n",
       "      <th>Daniel Hilton</th>\n",
       "      <th>Debra Henley</th>\n",
       "      <td>146832</td>\n",
       "      <td>65000</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Koepp Ltd</th>\n",
       "      <th>Wendy Yule</th>\n",
       "      <th>Fred Anderson</th>\n",
       "      <td>729833</td>\n",
       "      <td>35000</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kulas Inc</th>\n",
       "      <th>Daniel Hilton</th>\n",
       "      <th>Debra Henley</th>\n",
       "      <td>218895</td>\n",
       "      <td>25000</td>\n",
       "      <td>1.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Purdy-Kunde</th>\n",
       "      <th>Cedric Moss</th>\n",
       "      <th>Fred Anderson</th>\n",
       "      <td>163416</td>\n",
       "      <td>30000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Stokes LLC</th>\n",
       "      <th>Cedric Moss</th>\n",
       "      <th>Fred Anderson</th>\n",
       "      <td>239344</td>\n",
       "      <td>7500</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Trantow-Barrows</th>\n",
       "      <th>Craig Booker</th>\n",
       "      <th>Debra Henley</th>\n",
       "      <td>714466</td>\n",
       "      <td>15000</td>\n",
       "      <td>1.333333</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                          Account   Price  \\\n",
       "Name                         Rep           Manager                          \n",
       "Barton LLC                   John Smith    Debra Henley    740150   35000   \n",
       "Fritsch, Russel and Anderson Craig Booker  Debra Henley    737550   35000   \n",
       "Herman LLC                   Cedric Moss   Fred Anderson   141962   65000   \n",
       "Jerde-Hilpert                John Smith    Debra Henley    412290    5000   \n",
       "Kassulke, Ondricka and Metz  Wendy Yule    Fred Anderson   307599    7000   \n",
       "Keeling LLC                  Wendy Yule    Fred Anderson   688981  100000   \n",
       "Kiehn-Spinka                 Daniel Hilton Debra Henley    146832   65000   \n",
       "Koepp Ltd                    Wendy Yule    Fred Anderson   729833   35000   \n",
       "Kulas Inc                    Daniel Hilton Debra Henley    218895   25000   \n",
       "Purdy-Kunde                  Cedric Moss   Fred Anderson   163416   30000   \n",
       "Stokes LLC                   Cedric Moss   Fred Anderson   239344    7500   \n",
       "Trantow-Barrows              Craig Booker  Debra Henley    714466   15000   \n",
       "\n",
       "                                                          Quantity  \n",
       "Name                         Rep           Manager                  \n",
       "Barton LLC                   John Smith    Debra Henley   1.000000  \n",
       "Fritsch, Russel and Anderson Craig Booker  Debra Henley   1.000000  \n",
       "Herman LLC                   Cedric Moss   Fred Anderson  2.000000  \n",
       "Jerde-Hilpert                John Smith    Debra Henley   2.000000  \n",
       "Kassulke, Ondricka and Metz  Wendy Yule    Fred Anderson  3.000000  \n",
       "Keeling LLC                  Wendy Yule    Fred Anderson  5.000000  \n",
       "Kiehn-Spinka                 Daniel Hilton Debra Henley   2.000000  \n",
       "Koepp Ltd                    Wendy Yule    Fred Anderson  2.000000  \n",
       "Kulas Inc                    Daniel Hilton Debra Henley   1.500000  \n",
       "Purdy-Kunde                  Cedric Moss   Fred Anderson  1.000000  \n",
       "Stokes LLC                   Cedric Moss   Fred Anderson  1.000000  \n",
       "Trantow-Barrows              Craig Booker  Debra Henley   1.333333  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(df,index=[\"Name\",\"Rep\",\"Manager\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is interesting but not particularly useful. What we probably want to do is look at this by Manager and Director.\n",
    "It's easy enough to do by changing the index."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>Account</th>\n",
       "      <th>Price</th>\n",
       "      <th>Quantity</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Manager</th>\n",
       "      <th>Rep</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Debra Henley</th>\n",
       "      <th>Craig Booker</th>\n",
       "      <td>720237.0</td>\n",
       "      <td>20000.000000</td>\n",
       "      <td>1.250000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Daniel Hilton</th>\n",
       "      <td>194874.0</td>\n",
       "      <td>38333.333333</td>\n",
       "      <td>1.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>John Smith</th>\n",
       "      <td>576220.0</td>\n",
       "      <td>20000.000000</td>\n",
       "      <td>1.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Fred Anderson</th>\n",
       "      <th>Cedric Moss</th>\n",
       "      <td>196016.5</td>\n",
       "      <td>27500.000000</td>\n",
       "      <td>1.250000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wendy Yule</th>\n",
       "      <td>614061.5</td>\n",
       "      <td>44250.000000</td>\n",
       "      <td>3.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              Account         Price  Quantity\n",
       "Manager       Rep                                            \n",
       "Debra Henley  Craig Booker   720237.0  20000.000000  1.250000\n",
       "              Daniel Hilton  194874.0  38333.333333  1.666667\n",
       "              John Smith     576220.0  20000.000000  1.500000\n",
       "Fred Anderson Cedric Moss    196016.5  27500.000000  1.250000\n",
       "              Wendy Yule     614061.5  44250.000000  3.000000"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(df,index=[\"Manager\",\"Rep\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can see that the pivot table is smart enough to start aggregating the data and summarizing it by grouping the reps with their managers. Now we start to get a glimpse of what a pivot table can do for us.\n",
    "\n",
    "For this purpose, the Account and Quantity columns aren't really useful. Let's remove it by explicitly defining the columns we care about using the values field."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>Price</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Manager</th>\n",
       "      <th>Rep</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Debra Henley</th>\n",
       "      <th>Craig Booker</th>\n",
       "      <td>20000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Daniel Hilton</th>\n",
       "      <td>38333.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>John Smith</th>\n",
       "      <td>20000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Fred Anderson</th>\n",
       "      <th>Cedric Moss</th>\n",
       "      <td>27500.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wendy Yule</th>\n",
       "      <td>44250.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                    Price\n",
       "Manager       Rep                        \n",
       "Debra Henley  Craig Booker   20000.000000\n",
       "              Daniel Hilton  38333.333333\n",
       "              John Smith     20000.000000\n",
       "Fred Anderson Cedric Moss    27500.000000\n",
       "              Wendy Yule     44250.000000"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(df,index=[\"Manager\",\"Rep\"],values=[\"Price\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The price column automatically averages the data but we can do a count or a sum. Adding them is simple using aggfunc."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>Price</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Manager</th>\n",
       "      <th>Rep</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Debra Henley</th>\n",
       "      <th>Craig Booker</th>\n",
       "      <td>80000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Daniel Hilton</th>\n",
       "      <td>115000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>John Smith</th>\n",
       "      <td>40000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Fred Anderson</th>\n",
       "      <th>Cedric Moss</th>\n",
       "      <td>110000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wendy Yule</th>\n",
       "      <td>177000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              Price\n",
       "Manager       Rep                  \n",
       "Debra Henley  Craig Booker    80000\n",
       "              Daniel Hilton  115000\n",
       "              John Smith      40000\n",
       "Fred Anderson Cedric Moss    110000\n",
       "              Wendy Yule     177000"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(df,index=[\"Manager\",\"Rep\"],values=[\"Price\"],aggfunc=sum)# .reset_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "aggfunc can take a list of functions. Let's try a mean using the numpy functions and len to get a count."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>mean</th>\n",
       "      <th>len</th>\n",
       "      <th>sum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>Price</th>\n",
       "      <th>Price</th>\n",
       "      <th>Price</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Manager</th>\n",
       "      <th>Rep</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Debra Henley</th>\n",
       "      <th>Craig Booker</th>\n",
       "      <td>20000.000000</td>\n",
       "      <td>4</td>\n",
       "      <td>80000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Daniel Hilton</th>\n",
       "      <td>38333.333333</td>\n",
       "      <td>3</td>\n",
       "      <td>115000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>John Smith</th>\n",
       "      <td>20000.000000</td>\n",
       "      <td>2</td>\n",
       "      <td>40000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Fred Anderson</th>\n",
       "      <th>Cedric Moss</th>\n",
       "      <td>27500.000000</td>\n",
       "      <td>4</td>\n",
       "      <td>110000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wendy Yule</th>\n",
       "      <td>44250.000000</td>\n",
       "      <td>4</td>\n",
       "      <td>177000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     mean   len     sum\n",
       "                                    Price Price   Price\n",
       "Manager       Rep                                      \n",
       "Debra Henley  Craig Booker   20000.000000     4   80000\n",
       "              Daniel Hilton  38333.333333     3  115000\n",
       "              John Smith     20000.000000     2   40000\n",
       "Fred Anderson Cedric Moss    27500.000000     4  110000\n",
       "              Wendy Yule     44250.000000     4  177000"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(df,index=[\"Manager\",\"Rep\"],values=[\"Price\"],aggfunc=[np.mean,len,sum ])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we want to see sales broken down by the products, the columns variable allows us to define one or more columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead 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></th>\n",
       "      <th colspan=\"4\" halign=\"left\">sum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"4\" halign=\"left\">Price</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>Product</th>\n",
       "      <th>CPU</th>\n",
       "      <th>Maintenance</th>\n",
       "      <th>Monitor</th>\n",
       "      <th>Software</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Manager</th>\n",
       "      <th>Rep</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\">Debra Henley</th>\n",
       "      <th>Craig Booker</th>\n",
       "      <td>65000.0</td>\n",
       "      <td>5000.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Daniel Hilton</th>\n",
       "      <td>105000.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>John Smith</th>\n",
       "      <td>35000.0</td>\n",
       "      <td>5000.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Fred Anderson</th>\n",
       "      <th>Cedric Moss</th>\n",
       "      <td>95000.0</td>\n",
       "      <td>5000.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wendy Yule</th>\n",
       "      <td>165000.0</td>\n",
       "      <td>7000.0</td>\n",
       "      <td>5000.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                  sum                             \n",
       "                                Price                             \n",
       "Product                           CPU Maintenance Monitor Software\n",
       "Manager       Rep                                                 \n",
       "Debra Henley  Craig Booker    65000.0      5000.0     NaN  10000.0\n",
       "              Daniel Hilton  105000.0         NaN     NaN  10000.0\n",
       "              John Smith      35000.0      5000.0     NaN      NaN\n",
       "Fred Anderson Cedric Moss     95000.0      5000.0     NaN  10000.0\n",
       "              Wendy Yule     165000.0      7000.0  5000.0      NaN"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(df,index=[\"Manager\",\"Rep\"],values=[\"Price\"],\n",
    "               columns=[\"Product\"],aggfunc=[np.sum])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The NaN's are a bit distracting. If we want to remove them, we could use fill_value to set them to 0."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead 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></th>\n",
       "      <th colspan=\"4\" halign=\"left\">sum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"4\" halign=\"left\">Price</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>Product</th>\n",
       "      <th>CPU</th>\n",
       "      <th>Maintenance</th>\n",
       "      <th>Monitor</th>\n",
       "      <th>Software</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Manager</th>\n",
       "      <th>Rep</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\">Debra Henley</th>\n",
       "      <th>Craig Booker</th>\n",
       "      <td>65000</td>\n",
       "      <td>5000</td>\n",
       "      <td>0</td>\n",
       "      <td>10000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Daniel Hilton</th>\n",
       "      <td>105000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>10000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>John Smith</th>\n",
       "      <td>35000</td>\n",
       "      <td>5000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Fred Anderson</th>\n",
       "      <th>Cedric Moss</th>\n",
       "      <td>95000</td>\n",
       "      <td>5000</td>\n",
       "      <td>0</td>\n",
       "      <td>10000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wendy Yule</th>\n",
       "      <td>165000</td>\n",
       "      <td>7000</td>\n",
       "      <td>5000</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                sum                             \n",
       "                              Price                             \n",
       "Product                         CPU Maintenance Monitor Software\n",
       "Manager       Rep                                               \n",
       "Debra Henley  Craig Booker    65000        5000       0    10000\n",
       "              Daniel Hilton  105000           0       0    10000\n",
       "              John Smith      35000        5000       0        0\n",
       "Fred Anderson Cedric Moss     95000        5000       0    10000\n",
       "              Wendy Yule     165000        7000    5000        0"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(df,index=[\"Manager\",\"Rep\"],values=[\"Price\"],\n",
    "               columns=[\"Product\"],aggfunc=[np.sum],fill_value=0)\n",
    "# Alternative way of filling the NaN's\n",
    "#pd.pivot_table(df,index=[\"Manager\",\"Rep\"],values=[\"Price\"],\n",
    "#               columns=[\"Product\"],aggfunc=[np.sum]).fillna(0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I think it would be useful to add the quantity as well. Add Quantity to the values list."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead 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></th>\n",
       "      <th colspan=\"8\" halign=\"left\">sum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"4\" halign=\"left\">Price</th>\n",
       "      <th colspan=\"4\" halign=\"left\">Quantity</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>Product</th>\n",
       "      <th>CPU</th>\n",
       "      <th>Maintenance</th>\n",
       "      <th>Monitor</th>\n",
       "      <th>Software</th>\n",
       "      <th>CPU</th>\n",
       "      <th>Maintenance</th>\n",
       "      <th>Monitor</th>\n",
       "      <th>Software</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Manager</th>\n",
       "      <th>Rep</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 rowspan=\"3\" valign=\"top\">Debra Henley</th>\n",
       "      <th>Craig Booker</th>\n",
       "      <td>65000</td>\n",
       "      <td>5000</td>\n",
       "      <td>0</td>\n",
       "      <td>10000</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Daniel Hilton</th>\n",
       "      <td>105000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>10000</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>John Smith</th>\n",
       "      <td>35000</td>\n",
       "      <td>5000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Fred Anderson</th>\n",
       "      <th>Cedric Moss</th>\n",
       "      <td>95000</td>\n",
       "      <td>5000</td>\n",
       "      <td>0</td>\n",
       "      <td>10000</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wendy Yule</th>\n",
       "      <td>165000</td>\n",
       "      <td>7000</td>\n",
       "      <td>5000</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                sum                                        \\\n",
       "                              Price                              Quantity   \n",
       "Product                         CPU Maintenance Monitor Software      CPU   \n",
       "Manager       Rep                                                           \n",
       "Debra Henley  Craig Booker    65000        5000       0    10000        2   \n",
       "              Daniel Hilton  105000           0       0    10000        4   \n",
       "              John Smith      35000        5000       0        0        1   \n",
       "Fred Anderson Cedric Moss     95000        5000       0    10000        3   \n",
       "              Wendy Yule     165000        7000    5000        0        7   \n",
       "\n",
       "                                                          \n",
       "                                                          \n",
       "Product                     Maintenance Monitor Software  \n",
       "Manager       Rep                                         \n",
       "Debra Henley  Craig Booker            2       0        1  \n",
       "              Daniel Hilton           0       0        1  \n",
       "              John Smith              2       0        0  \n",
       "Fred Anderson Cedric Moss             1       0        1  \n",
       "              Wendy Yule              3       2        0  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(df,index=[\"Manager\",\"Rep\"],values=[\"Price\",\"Quantity\"],\n",
    "               columns=[\"Product\"],aggfunc=[np.sum],fill_value=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What's interesting is that you can move items to the index to get a different visual representation. We can add the Products to the index."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead 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></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">sum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>Price</th>\n",
       "      <th>Quantity</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Manager</th>\n",
       "      <th>Rep</th>\n",
       "      <th>Product</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"7\" valign=\"top\">Debra Henley</th>\n",
       "      <th rowspan=\"3\" valign=\"top\">Craig Booker</th>\n",
       "      <th>CPU</th>\n",
       "      <td>65000</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Maintenance</th>\n",
       "      <td>5000</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Software</th>\n",
       "      <td>10000</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Daniel Hilton</th>\n",
       "      <th>CPU</th>\n",
       "      <td>105000</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Software</th>\n",
       "      <td>10000</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">John Smith</th>\n",
       "      <th>CPU</th>\n",
       "      <td>35000</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Maintenance</th>\n",
       "      <td>5000</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"6\" valign=\"top\">Fred Anderson</th>\n",
       "      <th rowspan=\"3\" valign=\"top\">Cedric Moss</th>\n",
       "      <th>CPU</th>\n",
       "      <td>95000</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Maintenance</th>\n",
       "      <td>5000</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Software</th>\n",
       "      <td>10000</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Wendy Yule</th>\n",
       "      <th>CPU</th>\n",
       "      <td>165000</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Maintenance</th>\n",
       "      <td>7000</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Monitor</th>\n",
       "      <td>5000</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                            sum         \n",
       "                                          Price Quantity\n",
       "Manager       Rep           Product                     \n",
       "Debra Henley  Craig Booker  CPU           65000        2\n",
       "                            Maintenance    5000        2\n",
       "                            Software      10000        1\n",
       "              Daniel Hilton CPU          105000        4\n",
       "                            Software      10000        1\n",
       "              John Smith    CPU           35000        1\n",
       "                            Maintenance    5000        2\n",
       "Fred Anderson Cedric Moss   CPU           95000        3\n",
       "                            Maintenance    5000        1\n",
       "                            Software      10000        1\n",
       "              Wendy Yule    CPU          165000        7\n",
       "                            Maintenance    7000        3\n",
       "                            Monitor        5000        2"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(df,index=[\"Manager\",\"Rep\",\"Product\"],\n",
    "               values=[\"Price\",\"Quantity\"],aggfunc=[np.sum],fill_value=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For this data set, this representation makes more sense. Now, what if I want to see some totals? margins=True does that for us."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead 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></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">sum</th>\n",
       "      <th colspan=\"2\" halign=\"left\">mean</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>Price</th>\n",
       "      <th>Quantity</th>\n",
       "      <th>Price</th>\n",
       "      <th>Quantity</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Manager</th>\n",
       "      <th>Rep</th>\n",
       "      <th>Product</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"7\" valign=\"top\">Debra Henley</th>\n",
       "      <th rowspan=\"3\" valign=\"top\">Craig Booker</th>\n",
       "      <th>CPU</th>\n",
       "      <td>65000</td>\n",
       "      <td>2</td>\n",
       "      <td>32500</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Maintenance</th>\n",
       "      <td>5000</td>\n",
       "      <td>2</td>\n",
       "      <td>5000</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Software</th>\n",
       "      <td>10000</td>\n",
       "      <td>1</td>\n",
       "      <td>10000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Daniel Hilton</th>\n",
       "      <th>CPU</th>\n",
       "      <td>105000</td>\n",
       "      <td>4</td>\n",
       "      <td>52500</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Software</th>\n",
       "      <td>10000</td>\n",
       "      <td>1</td>\n",
       "      <td>10000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">John Smith</th>\n",
       "      <th>CPU</th>\n",
       "      <td>35000</td>\n",
       "      <td>1</td>\n",
       "      <td>35000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Maintenance</th>\n",
       "      <td>5000</td>\n",
       "      <td>2</td>\n",
       "      <td>5000</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"6\" valign=\"top\">Fred Anderson</th>\n",
       "      <th rowspan=\"3\" valign=\"top\">Cedric Moss</th>\n",
       "      <th>CPU</th>\n",
       "      <td>95000</td>\n",
       "      <td>3</td>\n",
       "      <td>47500</td>\n",
       "      <td>1.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Maintenance</th>\n",
       "      <td>5000</td>\n",
       "      <td>1</td>\n",
       "      <td>5000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Software</th>\n",
       "      <td>10000</td>\n",
       "      <td>1</td>\n",
       "      <td>10000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Wendy Yule</th>\n",
       "      <th>CPU</th>\n",
       "      <td>165000</td>\n",
       "      <td>7</td>\n",
       "      <td>82500</td>\n",
       "      <td>3.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Maintenance</th>\n",
       "      <td>7000</td>\n",
       "      <td>3</td>\n",
       "      <td>7000</td>\n",
       "      <td>3.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Monitor</th>\n",
       "      <td>5000</td>\n",
       "      <td>2</td>\n",
       "      <td>5000</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>All</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <td>522000</td>\n",
       "      <td>30</td>\n",
       "      <td>30705</td>\n",
       "      <td>1.764706</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                            sum            mean          \n",
       "                                          Price Quantity  Price  Quantity\n",
       "Manager       Rep           Product                                      \n",
       "Debra Henley  Craig Booker  CPU           65000        2  32500  1.000000\n",
       "                            Maintenance    5000        2   5000  2.000000\n",
       "                            Software      10000        1  10000  1.000000\n",
       "              Daniel Hilton CPU          105000        4  52500  2.000000\n",
       "                            Software      10000        1  10000  1.000000\n",
       "              John Smith    CPU           35000        1  35000  1.000000\n",
       "                            Maintenance    5000        2   5000  2.000000\n",
       "Fred Anderson Cedric Moss   CPU           95000        3  47500  1.500000\n",
       "                            Maintenance    5000        1   5000  1.000000\n",
       "                            Software      10000        1  10000  1.000000\n",
       "              Wendy Yule    CPU          165000        7  82500  3.500000\n",
       "                            Maintenance    7000        3   7000  3.000000\n",
       "                            Monitor        5000        2   5000  2.000000\n",
       "All                                      522000       30  30705  1.764706"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(df,index=[\"Manager\",\"Rep\",\"Product\"],\n",
    "               values=[\"Price\",\"Quantity\"],\n",
    "               aggfunc=[np.sum,np.mean],fill_value=0,margins=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's move the analysis up a level and look at our pipeline at the manager level. Notice how the status is ordered based on our earlier category definition."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>sum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>Price</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Manager</th>\n",
       "      <th>Status</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">Debra Henley</th>\n",
       "      <th>won</th>\n",
       "      <td>65000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>pending</th>\n",
       "      <td>50000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>presented</th>\n",
       "      <td>50000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>declined</th>\n",
       "      <td>70000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">Fred Anderson</th>\n",
       "      <th>won</th>\n",
       "      <td>172000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>pending</th>\n",
       "      <td>5000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>presented</th>\n",
       "      <td>45000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>declined</th>\n",
       "      <td>65000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>All</th>\n",
       "      <th></th>\n",
       "      <td>522000.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              sum\n",
       "                            Price\n",
       "Manager       Status             \n",
       "Debra Henley  won         65000.0\n",
       "              pending     50000.0\n",
       "              presented   50000.0\n",
       "              declined    70000.0\n",
       "Fred Anderson won        172000.0\n",
       "              pending      5000.0\n",
       "              presented   45000.0\n",
       "              declined    65000.0\n",
       "All                      522000.0"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(df,index=[\"Manager\",\"Status\"],values=[\"Price\"],\n",
    "               aggfunc=[np.sum],fill_value=0,margins=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A really handy feature is the ability to pass a dictionary to the aggfunc so you can perform different functions on each of the values you select. This has a side-effect of making the labels a little cleaner."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"5\" halign=\"left\">Price</th>\n",
       "      <th colspan=\"5\" halign=\"left\">Quantity</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>Product</th>\n",
       "      <th>CPU</th>\n",
       "      <th>Maintenance</th>\n",
       "      <th>Monitor</th>\n",
       "      <th>Software</th>\n",
       "      <th>All</th>\n",
       "      <th>CPU</th>\n",
       "      <th>Maintenance</th>\n",
       "      <th>Monitor</th>\n",
       "      <th>Software</th>\n",
       "      <th>All</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Manager</th>\n",
       "      <th>Status</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 rowspan=\"4\" valign=\"top\">Debra Henley</th>\n",
       "      <th>won</th>\n",
       "      <td>65000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>65000.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>pending</th>\n",
       "      <td>40000.0</td>\n",
       "      <td>10000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>50000.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>presented</th>\n",
       "      <td>30000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>20000.0</td>\n",
       "      <td>50000.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>declined</th>\n",
       "      <td>70000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>70000.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">Fred Anderson</th>\n",
       "      <th>won</th>\n",
       "      <td>165000.0</td>\n",
       "      <td>7000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>172000.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>pending</th>\n",
       "      <td>0.0</td>\n",
       "      <td>5000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>presented</th>\n",
       "      <td>30000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5000.0</td>\n",
       "      <td>10000.0</td>\n",
       "      <td>45000.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>declined</th>\n",
       "      <td>65000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>65000.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>All</th>\n",
       "      <th></th>\n",
       "      <td>465000.0</td>\n",
       "      <td>22000.0</td>\n",
       "      <td>5000.0</td>\n",
       "      <td>30000.0</td>\n",
       "      <td>522000.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>17.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            Price                                         \\\n",
       "Product                       CPU Maintenance Monitor Software       All   \n",
       "Manager       Status                                                       \n",
       "Debra Henley  won         65000.0         0.0     0.0      0.0   65000.0   \n",
       "              pending     40000.0     10000.0     0.0      0.0   50000.0   \n",
       "              presented   30000.0         0.0     0.0  20000.0   50000.0   \n",
       "              declined    70000.0         0.0     0.0      0.0   70000.0   \n",
       "Fred Anderson won        165000.0      7000.0     0.0      0.0  172000.0   \n",
       "              pending         0.0      5000.0     0.0      0.0    5000.0   \n",
       "              presented   30000.0         0.0  5000.0  10000.0   45000.0   \n",
       "              declined    65000.0         0.0     0.0      0.0   65000.0   \n",
       "All                      465000.0     22000.0  5000.0  30000.0  522000.0   \n",
       "\n",
       "                        Quantity                                     \n",
       "Product                      CPU Maintenance Monitor Software   All  \n",
       "Manager       Status                                                 \n",
       "Debra Henley  won            1.0         0.0     0.0      0.0   1.0  \n",
       "              pending        1.0         2.0     0.0      0.0   3.0  \n",
       "              presented      1.0         0.0     0.0      2.0   3.0  \n",
       "              declined       2.0         0.0     0.0      0.0   2.0  \n",
       "Fred Anderson won            2.0         1.0     0.0      0.0   3.0  \n",
       "              pending        0.0         1.0     0.0      0.0   1.0  \n",
       "              presented      1.0         0.0     1.0      1.0   3.0  \n",
       "              declined       1.0         0.0     0.0      0.0   1.0  \n",
       "All                          9.0         4.0     1.0      3.0  17.0  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(df,index=[\"Manager\",\"Status\"],columns=[\"Product\"],values=[\"Quantity\",\"Price\"],\n",
    "               aggfunc={\"Quantity\":len,\"Price\":np.sum},fill_value=0,margins=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can provide a list of aggfunctions to apply to each value too:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"8\" halign=\"left\">Price</th>\n",
       "      <th colspan=\"4\" halign=\"left\">Quantity</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"4\" halign=\"left\">mean</th>\n",
       "      <th colspan=\"4\" halign=\"left\">sum</th>\n",
       "      <th colspan=\"4\" halign=\"left\">len</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>Product</th>\n",
       "      <th>CPU</th>\n",
       "      <th>Maintenance</th>\n",
       "      <th>Monitor</th>\n",
       "      <th>Software</th>\n",
       "      <th>CPU</th>\n",
       "      <th>Maintenance</th>\n",
       "      <th>Monitor</th>\n",
       "      <th>Software</th>\n",
       "      <th>CPU</th>\n",
       "      <th>Maintenance</th>\n",
       "      <th>Monitor</th>\n",
       "      <th>Software</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Manager</th>\n",
       "      <th>Status</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",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">Debra Henley</th>\n",
       "      <th>won</th>\n",
       "      <td>65000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>65000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>pending</th>\n",
       "      <td>40000</td>\n",
       "      <td>5000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>40000</td>\n",
       "      <td>10000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>presented</th>\n",
       "      <td>30000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>10000</td>\n",
       "      <td>30000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>20000</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>declined</th>\n",
       "      <td>35000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>70000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">Fred Anderson</th>\n",
       "      <th>won</th>\n",
       "      <td>82500</td>\n",
       "      <td>7000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>165000</td>\n",
       "      <td>7000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>pending</th>\n",
       "      <td>0</td>\n",
       "      <td>5000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>presented</th>\n",
       "      <td>30000</td>\n",
       "      <td>0</td>\n",
       "      <td>5000</td>\n",
       "      <td>10000</td>\n",
       "      <td>30000</td>\n",
       "      <td>0</td>\n",
       "      <td>5000</td>\n",
       "      <td>10000</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>declined</th>\n",
       "      <td>65000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>65000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         Price                                       \\\n",
       "                          mean                                  sum   \n",
       "Product                    CPU Maintenance Monitor Software     CPU   \n",
       "Manager       Status                                                  \n",
       "Debra Henley  won        65000           0       0        0   65000   \n",
       "              pending    40000        5000       0        0   40000   \n",
       "              presented  30000           0       0    10000   30000   \n",
       "              declined   35000           0       0        0   70000   \n",
       "Fred Anderson won        82500        7000       0        0  165000   \n",
       "              pending        0        5000       0        0       0   \n",
       "              presented  30000           0    5000    10000   30000   \n",
       "              declined   65000           0       0        0   65000   \n",
       "\n",
       "                                                     Quantity              \\\n",
       "                                                          len               \n",
       "Product                 Maintenance Monitor Software      CPU Maintenance   \n",
       "Manager       Status                                                        \n",
       "Debra Henley  won                 0       0        0        1           0   \n",
       "              pending         10000       0        0        1           2   \n",
       "              presented           0       0    20000        1           0   \n",
       "              declined            0       0        0        2           0   \n",
       "Fred Anderson won              7000       0        0        2           1   \n",
       "              pending          5000       0        0        0           1   \n",
       "              presented           0    5000    10000        1           0   \n",
       "              declined            0       0        0        1           0   \n",
       "\n",
       "                                          \n",
       "                                          \n",
       "Product                 Monitor Software  \n",
       "Manager       Status                      \n",
       "Debra Henley  won             0        0  \n",
       "              pending         0        0  \n",
       "              presented       0        2  \n",
       "              declined        0        0  \n",
       "Fred Anderson won             0        0  \n",
       "              pending         0        0  \n",
       "              presented       1        1  \n",
       "              declined        0        0  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "table = pd.pivot_table(df,index=[\"Manager\",\"Status\"],columns=[\"Product\"],values=[\"Quantity\",\"Price\"],\n",
    "               aggfunc={\"Quantity\":len,\"Price\":[np.sum,np.mean]},fill_value=0)\n",
    "table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It can look daunting to try to pull this all together at once but as soon as you start playing with the data and slowly add the items, you can get a feel for how it works."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Advanced Pivot Table Filtering"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Once you have generated your data, it is in a DataFrame so you can filter on it using your normal DataFrame functions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"8\" halign=\"left\">Price</th>\n",
       "      <th colspan=\"4\" halign=\"left\">Quantity</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"4\" halign=\"left\">mean</th>\n",
       "      <th colspan=\"4\" halign=\"left\">sum</th>\n",
       "      <th colspan=\"4\" halign=\"left\">len</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>Product</th>\n",
       "      <th>CPU</th>\n",
       "      <th>Maintenance</th>\n",
       "      <th>Monitor</th>\n",
       "      <th>Software</th>\n",
       "      <th>CPU</th>\n",
       "      <th>Maintenance</th>\n",
       "      <th>Monitor</th>\n",
       "      <th>Software</th>\n",
       "      <th>CPU</th>\n",
       "      <th>Maintenance</th>\n",
       "      <th>Monitor</th>\n",
       "      <th>Software</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Manager</th>\n",
       "      <th>Status</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",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">Debra Henley</th>\n",
       "      <th>won</th>\n",
       "      <td>65000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>65000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>pending</th>\n",
       "      <td>40000</td>\n",
       "      <td>5000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>40000</td>\n",
       "      <td>10000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>presented</th>\n",
       "      <td>30000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>10000</td>\n",
       "      <td>30000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>20000</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>declined</th>\n",
       "      <td>35000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>70000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        Price                                                  \\\n",
       "                         mean                                 sum               \n",
       "Product                   CPU Maintenance Monitor Software    CPU Maintenance   \n",
       "Manager      Status                                                             \n",
       "Debra Henley won        65000           0       0        0  65000           0   \n",
       "             pending    40000        5000       0        0  40000       10000   \n",
       "             presented  30000           0       0    10000  30000           0   \n",
       "             declined   35000           0       0        0  70000           0   \n",
       "\n",
       "                                        Quantity                               \n",
       "                                             len                               \n",
       "Product                Monitor Software      CPU Maintenance Monitor Software  \n",
       "Manager      Status                                                            \n",
       "Debra Henley won             0        0        1           0       0        0  \n",
       "             pending         0        0        1           2       0        0  \n",
       "             presented       0    20000        1           0       0        2  \n",
       "             declined        0        0        2           0       0        0  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "table.query('Manager == [\"Debra Henley\"]')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"8\" halign=\"left\">Price</th>\n",
       "      <th colspan=\"4\" halign=\"left\">Quantity</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"4\" halign=\"left\">mean</th>\n",
       "      <th colspan=\"4\" halign=\"left\">sum</th>\n",
       "      <th colspan=\"4\" halign=\"left\">len</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>Product</th>\n",
       "      <th>CPU</th>\n",
       "      <th>Maintenance</th>\n",
       "      <th>Monitor</th>\n",
       "      <th>Software</th>\n",
       "      <th>CPU</th>\n",
       "      <th>Maintenance</th>\n",
       "      <th>Monitor</th>\n",
       "      <th>Software</th>\n",
       "      <th>CPU</th>\n",
       "      <th>Maintenance</th>\n",
       "      <th>Monitor</th>\n",
       "      <th>Software</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Manager</th>\n",
       "      <th>Status</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",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Debra Henley</th>\n",
       "      <th>won</th>\n",
       "      <td>65000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>65000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>pending</th>\n",
       "      <td>40000</td>\n",
       "      <td>5000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>40000</td>\n",
       "      <td>10000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Fred Anderson</th>\n",
       "      <th>won</th>\n",
       "      <td>82500</td>\n",
       "      <td>7000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>165000</td>\n",
       "      <td>7000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>pending</th>\n",
       "      <td>0</td>\n",
       "      <td>5000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       Price                                                   \\\n",
       "                        mean                                  sum               \n",
       "Product                  CPU Maintenance Monitor Software     CPU Maintenance   \n",
       "Manager       Status                                                            \n",
       "Debra Henley  won      65000           0       0        0   65000           0   \n",
       "              pending  40000        5000       0        0   40000       10000   \n",
       "Fred Anderson won      82500        7000       0        0  165000        7000   \n",
       "              pending      0        5000       0        0       0        5000   \n",
       "\n",
       "                                       Quantity                               \n",
       "                                            len                               \n",
       "Product               Monitor Software      CPU Maintenance Monitor Software  \n",
       "Manager       Status                                                          \n",
       "Debra Henley  won           0        0        1           0       0        0  \n",
       "              pending       0        0        1           2       0        0  \n",
       "Fred Anderson won           0        0        2           1       0        0  \n",
       "              pending       0        0        0           1       0        0  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "table.query('Status == [\"pending\",\"won\"]')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I hope this tutorial has shown you how to use pivot tables on your data sets."
   ]
  }
 ],
 "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.3"
  },
  "toc": {
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": true,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}