pandas_pivot_table.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img src=\"https://raw.githubusercontent.com/dark-teal-coder/dark-teal-coder/main/images/coder-no-background-000-128-128.png\" alt=\"coder-black-background-000-128-128.png\" width=\"100\" height=\"100\" align=\"right\" style=\"margin:0px 5%; padding: 5px;\">\n",
    "<p>\n",
    "\tGitHub: <a href=\"https://github.com/dark-teal-coder\">@dark-teal-coder</a>\n",
    "\t<br />\n",
    "\tFirst Published Date: 2022-02-23\n",
    "\t<br />\n",
    "\tLast Modified Date: 2022-02-23\n",
    "</p>\n",
    "\n",
    "<br />\n",
    "\n",
    "___"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Pandas Pivot Table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Reference: \n",
    "https://pbpython.com/pandas-pivot-table-explained.html"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 143,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 144,
   "metadata": {
    "scrolled": false
   },
   "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",
       "      <td>0</td>\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",
       "      <td>1</td>\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",
       "      <td>2</td>\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",
       "      <td>3</td>\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",
       "      <td>4</td>\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": 144,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Read in our sales funnel data into our DataFrame\n",
    "df = pd.read_excel(\"C:/Users/Asus/Desktop/sales-funnel.xlsx\")\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 145,
   "metadata": {},
   "outputs": [],
   "source": [
    "# To cast the \"Status\" column of the datafrome to column-specific data types\n",
    "df[\"Status\"] = df[\"Status\"].astype(\"category\")\n",
    "df[\"Status\"].cat.set_categories([\"won\", \"pending\", \"presented\", \"declined\"], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     presented\n",
       "1     presented\n",
       "2       pending\n",
       "3      declined\n",
       "4           won\n",
       "5       pending\n",
       "6     presented\n",
       "7       pending\n",
       "8      declined\n",
       "9           won\n",
       "10    presented\n",
       "11      pending\n",
       "12    presented\n",
       "13          won\n",
       "14          won\n",
       "15     declined\n",
       "16    presented\n",
       "Name: Status, dtype: category\n",
       "Categories (4, object): [won, pending, presented, declined]"
      ]
     },
     "execution_count": 146,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Try printing df[\"Status\"] to see what it's like:\n",
    "df[\"Status\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 147,
   "metadata": {
    "scrolled": false
   },
   "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",
       "      <td>Barton LLC</td>\n",
       "      <td>740150</td>\n",
       "      <td>35000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Fritsch, Russel and Anderson</td>\n",
       "      <td>737550</td>\n",
       "      <td>35000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Herman LLC</td>\n",
       "      <td>141962</td>\n",
       "      <td>65000</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Jerde-Hilpert</td>\n",
       "      <td>412290</td>\n",
       "      <td>5000</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Kassulke, Ondricka and Metz</td>\n",
       "      <td>307599</td>\n",
       "      <td>7000</td>\n",
       "      <td>3.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Keeling LLC</td>\n",
       "      <td>688981</td>\n",
       "      <td>100000</td>\n",
       "      <td>5.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Kiehn-Spinka</td>\n",
       "      <td>146832</td>\n",
       "      <td>65000</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Koepp Ltd</td>\n",
       "      <td>729833</td>\n",
       "      <td>35000</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Kulas Inc</td>\n",
       "      <td>218895</td>\n",
       "      <td>25000</td>\n",
       "      <td>1.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Purdy-Kunde</td>\n",
       "      <td>163416</td>\n",
       "      <td>30000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Stokes LLC</td>\n",
       "      <td>239344</td>\n",
       "      <td>7500</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Trantow-Barrows</td>\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": 147,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Average/mean from aggfunc default\n",
    "# Only numerical data in Account, Price and Quantity columns can be averaged\n",
    "pd.pivot_table(df, index=[\"Name\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 148,
   "metadata": {
    "scrolled": false
   },
   "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",
       "      <td>Barton LLC</td>\n",
       "      <td>740150</td>\n",
       "      <td>35000</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Fritsch, Russel and Anderson</td>\n",
       "      <td>737550</td>\n",
       "      <td>35000</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Herman LLC</td>\n",
       "      <td>141962</td>\n",
       "      <td>65000</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Jerde-Hilpert</td>\n",
       "      <td>412290</td>\n",
       "      <td>5000</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Kassulke, Ondricka and Metz</td>\n",
       "      <td>307599</td>\n",
       "      <td>7000</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Keeling LLC</td>\n",
       "      <td>688981</td>\n",
       "      <td>100000</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Kiehn-Spinka</td>\n",
       "      <td>146832</td>\n",
       "      <td>65000</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Koepp Ltd</td>\n",
       "      <td>1459666</td>\n",
       "      <td>70000</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Kulas Inc</td>\n",
       "      <td>437790</td>\n",
       "      <td>50000</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Purdy-Kunde</td>\n",
       "      <td>163416</td>\n",
       "      <td>30000</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Stokes LLC</td>\n",
       "      <td>478688</td>\n",
       "      <td>15000</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Trantow-Barrows</td>\n",
       "      <td>2143398</td>\n",
       "      <td>45000</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              Account   Price  Quantity\n",
       "Name                                                   \n",
       "Barton LLC                     740150   35000         1\n",
       "Fritsch, Russel and Anderson   737550   35000         1\n",
       "Herman LLC                     141962   65000         2\n",
       "Jerde-Hilpert                  412290    5000         2\n",
       "Kassulke, Ondricka and Metz    307599    7000         3\n",
       "Keeling LLC                    688981  100000         5\n",
       "Kiehn-Spinka                   146832   65000         2\n",
       "Koepp Ltd                     1459666   70000         4\n",
       "Kulas Inc                      437790   50000         3\n",
       "Purdy-Kunde                    163416   30000         1\n",
       "Stokes LLC                     478688   15000         2\n",
       "Trantow-Barrows               2143398   45000         4"
      ]
     },
     "execution_count": 148,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Setting aggfunc to 'sum' using aggfunc=\"sum\":\n",
    "pd.pivot_table(df, index=[\"Name\"], aggfunc=\"sum\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 149,
   "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",
       "      <td>Barton LLC</td>\n",
       "      <td>740150</td>\n",
       "      <td>35000</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Fritsch, Russel and Anderson</td>\n",
       "      <td>737550</td>\n",
       "      <td>35000</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Herman LLC</td>\n",
       "      <td>141962</td>\n",
       "      <td>65000</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Jerde-Hilpert</td>\n",
       "      <td>412290</td>\n",
       "      <td>5000</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Kassulke, Ondricka and Metz</td>\n",
       "      <td>307599</td>\n",
       "      <td>7000</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Keeling LLC</td>\n",
       "      <td>688981</td>\n",
       "      <td>100000</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Kiehn-Spinka</td>\n",
       "      <td>146832</td>\n",
       "      <td>65000</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Koepp Ltd</td>\n",
       "      <td>1459666</td>\n",
       "      <td>70000</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Kulas Inc</td>\n",
       "      <td>437790</td>\n",
       "      <td>50000</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Purdy-Kunde</td>\n",
       "      <td>163416</td>\n",
       "      <td>30000</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Stokes LLC</td>\n",
       "      <td>478688</td>\n",
       "      <td>15000</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Trantow-Barrows</td>\n",
       "      <td>2143398</td>\n",
       "      <td>45000</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              Account   Price  Quantity\n",
       "Name                                                   \n",
       "Barton LLC                     740150   35000         1\n",
       "Fritsch, Russel and Anderson   737550   35000         1\n",
       "Herman LLC                     141962   65000         2\n",
       "Jerde-Hilpert                  412290    5000         2\n",
       "Kassulke, Ondricka and Metz    307599    7000         3\n",
       "Keeling LLC                    688981  100000         5\n",
       "Kiehn-Spinka                   146832   65000         2\n",
       "Koepp Ltd                     1459666   70000         4\n",
       "Kulas Inc                      437790   50000         3\n",
       "Purdy-Kunde                    163416   30000         1\n",
       "Stokes LLC                     478688   15000         2\n",
       "Trantow-Barrows               2143398   45000         4"
      ]
     },
     "execution_count": 149,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Alternatively, use np.sum:\n",
    "pd.pivot_table(df, index=[\"Name\"], aggfunc=np.sum)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 150,
   "metadata": {
    "scrolled": true
   },
   "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",
       "      <td>Barton LLC</td>\n",
       "      <td>John Smith</td>\n",
       "      <td>Debra Henley</td>\n",
       "      <td>740150</td>\n",
       "      <td>35000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Fritsch, Russel and Anderson</td>\n",
       "      <td>Craig Booker</td>\n",
       "      <td>Debra Henley</td>\n",
       "      <td>737550</td>\n",
       "      <td>35000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Herman LLC</td>\n",
       "      <td>Cedric Moss</td>\n",
       "      <td>Fred Anderson</td>\n",
       "      <td>141962</td>\n",
       "      <td>65000</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Jerde-Hilpert</td>\n",
       "      <td>John Smith</td>\n",
       "      <td>Debra Henley</td>\n",
       "      <td>412290</td>\n",
       "      <td>5000</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Kassulke, Ondricka and Metz</td>\n",
       "      <td>Wendy Yule</td>\n",
       "      <td>Fred Anderson</td>\n",
       "      <td>307599</td>\n",
       "      <td>7000</td>\n",
       "      <td>3.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Keeling LLC</td>\n",
       "      <td>Wendy Yule</td>\n",
       "      <td>Fred Anderson</td>\n",
       "      <td>688981</td>\n",
       "      <td>100000</td>\n",
       "      <td>5.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Kiehn-Spinka</td>\n",
       "      <td>Daniel Hilton</td>\n",
       "      <td>Debra Henley</td>\n",
       "      <td>146832</td>\n",
       "      <td>65000</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Koepp Ltd</td>\n",
       "      <td>Wendy Yule</td>\n",
       "      <td>Fred Anderson</td>\n",
       "      <td>729833</td>\n",
       "      <td>35000</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Kulas Inc</td>\n",
       "      <td>Daniel Hilton</td>\n",
       "      <td>Debra Henley</td>\n",
       "      <td>218895</td>\n",
       "      <td>25000</td>\n",
       "      <td>1.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Purdy-Kunde</td>\n",
       "      <td>Cedric Moss</td>\n",
       "      <td>Fred Anderson</td>\n",
       "      <td>163416</td>\n",
       "      <td>30000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Stokes LLC</td>\n",
       "      <td>Cedric Moss</td>\n",
       "      <td>Fred Anderson</td>\n",
       "      <td>239344</td>\n",
       "      <td>7500</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Trantow-Barrows</td>\n",
       "      <td>Craig Booker</td>\n",
       "      <td>Debra Henley</td>\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": 150,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Multiple indices\n",
    "pd.pivot_table(df, index=[\"Name\", \"Rep\", \"Manager\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 151,
   "metadata": {
    "scrolled": true
   },
   "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",
       "      <td rowspan=\"3\" valign=\"top\">Debra Henley</td>\n",
       "      <td>Craig Booker</td>\n",
       "      <td>720237.0</td>\n",
       "      <td>20000.000000</td>\n",
       "      <td>1.250000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Daniel Hilton</td>\n",
       "      <td>194874.0</td>\n",
       "      <td>38333.333333</td>\n",
       "      <td>1.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>John Smith</td>\n",
       "      <td>576220.0</td>\n",
       "      <td>20000.000000</td>\n",
       "      <td>1.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td rowspan=\"2\" valign=\"top\">Fred Anderson</td>\n",
       "      <td>Cedric Moss</td>\n",
       "      <td>196016.5</td>\n",
       "      <td>27500.000000</td>\n",
       "      <td>1.250000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Wendy Yule</td>\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": 151,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(df, index=[\"Manager\", \"Rep\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 152,
   "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>Rep</th>\n",
       "      <th>Manager</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>Cedric Moss</td>\n",
       "      <td>Fred Anderson</td>\n",
       "      <td>196016.5</td>\n",
       "      <td>27500.000000</td>\n",
       "      <td>1.250000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Craig Booker</td>\n",
       "      <td>Debra Henley</td>\n",
       "      <td>720237.0</td>\n",
       "      <td>20000.000000</td>\n",
       "      <td>1.250000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Daniel Hilton</td>\n",
       "      <td>Debra Henley</td>\n",
       "      <td>194874.0</td>\n",
       "      <td>38333.333333</td>\n",
       "      <td>1.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>John Smith</td>\n",
       "      <td>Debra Henley</td>\n",
       "      <td>576220.0</td>\n",
       "      <td>20000.000000</td>\n",
       "      <td>1.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Wendy Yule</td>\n",
       "      <td>Fred Anderson</td>\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",
       "Rep           Manager                                        \n",
       "Cedric Moss   Fred Anderson  196016.5  27500.000000  1.250000\n",
       "Craig Booker  Debra Henley   720237.0  20000.000000  1.250000\n",
       "Daniel Hilton Debra Henley   194874.0  38333.333333  1.666667\n",
       "John Smith    Debra Henley   576220.0  20000.000000  1.500000\n",
       "Wendy Yule    Fred Anderson  614061.5  44250.000000  3.000000"
      ]
     },
     "execution_count": 152,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Try swapping the indices:\n",
    "pd.pivot_table(df, index=[\"Rep\", \"Manager\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 153,
   "metadata": {
    "scrolled": true
   },
   "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",
       "      <td rowspan=\"3\" valign=\"top\">Debra Henley</td>\n",
       "      <td>Craig Booker</td>\n",
       "      <td>20000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Daniel Hilton</td>\n",
       "      <td>38333.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>John Smith</td>\n",
       "      <td>20000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td rowspan=\"2\" valign=\"top\">Fred Anderson</td>\n",
       "      <td>Cedric Moss</td>\n",
       "      <td>27500.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Wendy Yule</td>\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": 153,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# The price column automatically averages the data from aggfunc default value\n",
    "pd.pivot_table(df, index=[\"Manager\", \"Rep\"], values=[\"Price\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 154,
   "metadata": {
    "scrolled": false
   },
   "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",
       "      <td rowspan=\"3\" valign=\"top\">Debra Henley</td>\n",
       "      <td>Craig Booker</td>\n",
       "      <td>80000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Daniel Hilton</td>\n",
       "      <td>115000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>John Smith</td>\n",
       "      <td>40000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td rowspan=\"2\" valign=\"top\">Fred Anderson</td>\n",
       "      <td>Cedric Moss</td>\n",
       "      <td>110000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Wendy Yule</td>\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": 154,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Adding the price column using aggfunc=np.sum\n",
    "pd.pivot_table(df, index=[\"Manager\", \"Rep\"], values=[\"Price\"], aggfunc=np.sum)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 155,
   "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>mean</th>\n",
       "      <th>len</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td rowspan=\"3\" valign=\"top\">Debra Henley</td>\n",
       "      <td>Craig Booker</td>\n",
       "      <td>20000.000000</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Daniel Hilton</td>\n",
       "      <td>38333.333333</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>John Smith</td>\n",
       "      <td>20000.000000</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td rowspan=\"2\" valign=\"top\">Fred Anderson</td>\n",
       "      <td>Cedric Moss</td>\n",
       "      <td>27500.000000</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Wendy Yule</td>\n",
       "      <td>44250.000000</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     mean   len\n",
       "                                    Price Price\n",
       "Manager       Rep                              \n",
       "Debra Henley  Craig Booker   20000.000000     4\n",
       "              Daniel Hilton  38333.333333     3\n",
       "              John Smith     20000.000000     2\n",
       "Fred Anderson Cedric Moss    27500.000000     4\n",
       "              Wendy Yule     44250.000000     4"
      ]
     },
     "execution_count": 155,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# To show several columns of calculated results, aggfunc can take a list of functions\n",
    "# e.g. numpy mean to get a mean and len to get a count \n",
    "# e.g. 3 instances of Manager as Debra Henley and Rep as Daniel Hilton\n",
    "pd.pivot_table(df, index=[\"Manager\", \"Rep\"], values=[\"Price\"], aggfunc=[np.mean, len])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "metadata": {
    "scrolled": true
   },
   "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",
       "      <td rowspan=\"3\" valign=\"top\">Debra Henley</td>\n",
       "      <td>Craig Booker</td>\n",
       "      <td>65000.0</td>\n",
       "      <td>5000.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Daniel Hilton</td>\n",
       "      <td>105000.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>John Smith</td>\n",
       "      <td>35000.0</td>\n",
       "      <td>5000.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td rowspan=\"2\" valign=\"top\">Fred Anderson</td>\n",
       "      <td>Cedric Moss</td>\n",
       "      <td>95000.0</td>\n",
       "      <td>5000.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Wendy Yule</td>\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": 156,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(df, index=[\"Manager\", \"Rep\"], values=[\"Price\"], columns=[\"Product\"], aggfunc=[np.sum])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 157,
   "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>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>Rep</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td rowspan=\"3\" valign=\"top\">Debra Henley</td>\n",
       "      <td>Craig Booker</td>\n",
       "      <td>80000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Daniel Hilton</td>\n",
       "      <td>115000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>John Smith</td>\n",
       "      <td>40000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td rowspan=\"2\" valign=\"top\">Fred Anderson</td>\n",
       "      <td>Cedric Moss</td>\n",
       "      <td>110000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Wendy Yule</td>\n",
       "      <td>177000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                sum\n",
       "                              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": 157,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Try removing column=[] to see how it works:\n",
    "pd.pivot_table(df, index=[\"Manager\", \"Rep\"], values=[\"Price\"], aggfunc=[np.sum])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 158,
   "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",
       "      <td rowspan=\"3\" valign=\"top\">Debra Henley</td>\n",
       "      <td>Craig Booker</td>\n",
       "      <td>65000</td>\n",
       "      <td>5000</td>\n",
       "      <td>0</td>\n",
       "      <td>10000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Daniel Hilton</td>\n",
       "      <td>105000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>10000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>John Smith</td>\n",
       "      <td>35000</td>\n",
       "      <td>5000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td rowspan=\"2\" valign=\"top\">Fred Anderson</td>\n",
       "      <td>Cedric Moss</td>\n",
       "      <td>95000</td>\n",
       "      <td>5000</td>\n",
       "      <td>0</td>\n",
       "      <td>10000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Wendy Yule</td>\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": 158,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# To remove NaN's, use fill_value to set them to 0\n",
    "pd.pivot_table(df, index=[\"Manager\", \"Rep\"], values=[\"Price\"], columns=[\"Product\"], aggfunc=[np.sum], fill_value=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 159,
   "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",
       "      <td rowspan=\"3\" valign=\"top\">Debra Henley</td>\n",
       "      <td>Craig Booker</td>\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",
       "      <td>Daniel Hilton</td>\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",
       "      <td>John Smith</td>\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",
       "      <td rowspan=\"2\" valign=\"top\">Fred Anderson</td>\n",
       "      <td>Cedric Moss</td>\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",
       "      <td>Wendy Yule</td>\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": 159,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# If Quantity is also useful, add it to the values list\n",
    "pd.pivot_table(df, index=[\"Manager\", \"Rep\"], values=[\"Price\", \"Quantity\"], columns=[\"Product\"], aggfunc=[np.sum], fill_value=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 160,
   "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",
       "      <td rowspan=\"7\" valign=\"top\">Debra Henley</td>\n",
       "      <td rowspan=\"3\" valign=\"top\">Craig Booker</td>\n",
       "      <td>CPU</td>\n",
       "      <td>65000</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Maintenance</td>\n",
       "      <td>5000</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Software</td>\n",
       "      <td>10000</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td rowspan=\"2\" valign=\"top\">Daniel Hilton</td>\n",
       "      <td>CPU</td>\n",
       "      <td>105000</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Software</td>\n",
       "      <td>10000</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td rowspan=\"2\" valign=\"top\">John Smith</td>\n",
       "      <td>CPU</td>\n",
       "      <td>35000</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Maintenance</td>\n",
       "      <td>5000</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td rowspan=\"6\" valign=\"top\">Fred Anderson</td>\n",
       "      <td rowspan=\"3\" valign=\"top\">Cedric Moss</td>\n",
       "      <td>CPU</td>\n",
       "      <td>95000</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Maintenance</td>\n",
       "      <td>5000</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Software</td>\n",
       "      <td>10000</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td rowspan=\"3\" valign=\"top\">Wendy Yule</td>\n",
       "      <td>CPU</td>\n",
       "      <td>165000</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Maintenance</td>\n",
       "      <td>7000</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Monitor</td>\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": 160,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# To get a different visual representation, remove Product from columns and index it\n",
    "pd.pivot_table(df, index=[\"Manager\", \"Rep\", \"Product\"], values=[\"Price\", \"Quantity\"], aggfunc=[np.sum], fill_value=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 161,
   "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",
       "      <td rowspan=\"7\" valign=\"top\">Debra Henley</td>\n",
       "      <td rowspan=\"3\" valign=\"top\">Craig Booker</td>\n",
       "      <td>CPU</td>\n",
       "      <td>65000</td>\n",
       "      <td>2</td>\n",
       "      <td>32500</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Maintenance</td>\n",
       "      <td>5000</td>\n",
       "      <td>2</td>\n",
       "      <td>5000</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Software</td>\n",
       "      <td>10000</td>\n",
       "      <td>1</td>\n",
       "      <td>10000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td rowspan=\"2\" valign=\"top\">Daniel Hilton</td>\n",
       "      <td>CPU</td>\n",
       "      <td>105000</td>\n",
       "      <td>4</td>\n",
       "      <td>52500</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Software</td>\n",
       "      <td>10000</td>\n",
       "      <td>1</td>\n",
       "      <td>10000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td rowspan=\"2\" valign=\"top\">John Smith</td>\n",
       "      <td>CPU</td>\n",
       "      <td>35000</td>\n",
       "      <td>1</td>\n",
       "      <td>35000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Maintenance</td>\n",
       "      <td>5000</td>\n",
       "      <td>2</td>\n",
       "      <td>5000</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td rowspan=\"6\" valign=\"top\">Fred Anderson</td>\n",
       "      <td rowspan=\"3\" valign=\"top\">Cedric Moss</td>\n",
       "      <td>CPU</td>\n",
       "      <td>95000</td>\n",
       "      <td>3</td>\n",
       "      <td>47500</td>\n",
       "      <td>1.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Maintenance</td>\n",
       "      <td>5000</td>\n",
       "      <td>1</td>\n",
       "      <td>5000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Software</td>\n",
       "      <td>10000</td>\n",
       "      <td>1</td>\n",
       "      <td>10000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td rowspan=\"3\" valign=\"top\">Wendy Yule</td>\n",
       "      <td>CPU</td>\n",
       "      <td>165000</td>\n",
       "      <td>7</td>\n",
       "      <td>82500</td>\n",
       "      <td>3.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Maintenance</td>\n",
       "      <td>7000</td>\n",
       "      <td>3</td>\n",
       "      <td>7000</td>\n",
       "      <td>3.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Monitor</td>\n",
       "      <td>5000</td>\n",
       "      <td>2</td>\n",
       "      <td>5000</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>All</td>\n",
       "      <td></td>\n",
       "      <td></td>\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": 161,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Set margins=True to get the totals\n",
    "pd.pivot_table(df, index=[\"Manager\", \"Rep\", \"Product\"], \n",
    "               values=[\"Price\", \"Quantity\"], aggfunc=[np.sum, np.mean], \n",
    "               fill_value=0, margins=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 162,
   "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>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",
       "      <td rowspan=\"4\" valign=\"top\">Debra Henley</td>\n",
       "      <td>won</td>\n",
       "      <td>65000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>pending</td>\n",
       "      <td>50000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>presented</td>\n",
       "      <td>50000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>declined</td>\n",
       "      <td>70000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td rowspan=\"4\" valign=\"top\">Fred Anderson</td>\n",
       "      <td>won</td>\n",
       "      <td>172000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>pending</td>\n",
       "      <td>5000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>presented</td>\n",
       "      <td>45000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>declined</td>\n",
       "      <td>65000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>All</td>\n",
       "      <td></td>\n",
       "      <td>522000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            sum\n",
       "                          Price\n",
       "Manager       Status           \n",
       "Debra Henley  won         65000\n",
       "              pending     50000\n",
       "              presented   50000\n",
       "              declined    70000\n",
       "Fred Anderson won        172000\n",
       "              pending      5000\n",
       "              presented   45000\n",
       "              declined    65000\n",
       "All                      522000"
      ]
     },
     "execution_count": 162,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Notice how the status is ordered based on our earlier category definition\n",
    "pd.pivot_table(df, index=[\"Manager\", \"Status\"], values=[\"Price\"], \n",
    "               aggfunc=[np.sum], fill_value=0, margins=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 163,
   "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\">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>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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td rowspan=\"4\" valign=\"top\">Debra Henley</td>\n",
       "      <td>won</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",
       "      <td>pending</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",
       "      <td>presented</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",
       "      <td>declined</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",
       "      <td rowspan=\"4\" valign=\"top\">Fred Anderson</td>\n",
       "      <td>won</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",
       "      <td>pending</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",
       "      <td>presented</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",
       "      <td>declined</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                              Quantity  \\\n",
       "Product                     CPU Maintenance Monitor Software      CPU   \n",
       "Manager       Status                                                    \n",
       "Debra Henley  won         65000           0       0        0        1   \n",
       "              pending     40000       10000       0        0        1   \n",
       "              presented   30000           0       0    20000        1   \n",
       "              declined    70000           0       0        0        2   \n",
       "Fred Anderson won        165000        7000       0        0        2   \n",
       "              pending         0        5000       0        0        0   \n",
       "              presented   30000           0    5000    10000        1   \n",
       "              declined    65000           0       0        0        1   \n",
       "\n",
       "                                                      \n",
       "Product                 Maintenance Monitor Software  \n",
       "Manager       Status                                  \n",
       "Debra Henley  won                 0       0        0  \n",
       "              pending             2       0        0  \n",
       "              presented           0       0        2  \n",
       "              declined            0       0        0  \n",
       "Fred Anderson won                 1       0        0  \n",
       "              pending             1       0        0  \n",
       "              presented           0       1        1  \n",
       "              declined            0       0        0  "
      ]
     },
     "execution_count": 163,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# To pass a dictionary to the aggfunc to perform different functions on each values column\n",
    "pd.pivot_table(df, index=[\"Manager\", \"Status\"], columns=[\"Product\"], \n",
    "               values=[\"Quantity\", \"Price\"], \n",
    "               aggfunc={\"Quantity\":len, \"Price\":np.sum}, fill_value=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 164,
   "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",
       "      <th>Quantity</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Manager</th>\n",
       "      <th>Status</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td rowspan=\"4\" valign=\"top\">Debra Henley</td>\n",
       "      <td>won</td>\n",
       "      <td>65000</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>pending</td>\n",
       "      <td>50000</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>presented</td>\n",
       "      <td>50000</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>declined</td>\n",
       "      <td>70000</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td rowspan=\"4\" valign=\"top\">Fred Anderson</td>\n",
       "      <td>won</td>\n",
       "      <td>172000</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>pending</td>\n",
       "      <td>5000</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>presented</td>\n",
       "      <td>45000</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>declined</td>\n",
       "      <td>65000</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          Price  Quantity\n",
       "Manager       Status                     \n",
       "Debra Henley  won         65000         1\n",
       "              pending     50000         3\n",
       "              presented   50000         3\n",
       "              declined    70000         2\n",
       "Fred Anderson won        172000         3\n",
       "              pending      5000         1\n",
       "              presented   45000         3\n",
       "              declined    65000         1"
      ]
     },
     "execution_count": 164,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Try removing columns=[\"Product\"] for ease of understanding: \n",
    "pd.pivot_table(df, index=[\"Manager\", \"Status\"], \n",
    "               values=[\"Quantity\", \"Price\"], \n",
    "               aggfunc={\"Quantity\":len, \"Price\":np.sum}, fill_value=0)\n",
    "# e.g. Counting instances where the quantity Manager=\"Debra Henley\" and Status=\"pending\" gives 3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 165,
   "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=\"2\" halign=\"left\">sum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>Price</th>\n",
       "      <th>Quantity</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Manager</th>\n",
       "      <th>Status</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td rowspan=\"4\" valign=\"top\">Debra Henley</td>\n",
       "      <td>won</td>\n",
       "      <td>65000</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>pending</td>\n",
       "      <td>50000</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>presented</td>\n",
       "      <td>50000</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>declined</td>\n",
       "      <td>70000</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td rowspan=\"4\" valign=\"top\">Fred Anderson</td>\n",
       "      <td>won</td>\n",
       "      <td>172000</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>pending</td>\n",
       "      <td>5000</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>presented</td>\n",
       "      <td>45000</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>declined</td>\n",
       "      <td>65000</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            sum         \n",
       "                          Price Quantity\n",
       "Manager       Status                    \n",
       "Debra Henley  won         65000        2\n",
       "              pending     50000        6\n",
       "              presented   50000        3\n",
       "              declined    70000        2\n",
       "Fred Anderson won        172000       10\n",
       "              pending      5000        1\n",
       "              presented   45000        4\n",
       "              declined    65000        2"
      ]
     },
     "execution_count": 165,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Then, try using the original function list: \n",
    "pd.pivot_table(df, index=[\"Manager\", \"Status\"], \n",
    "               values=[\"Quantity\", \"Price\"], \n",
    "               aggfunc=[np.sum], fill_value=0)\n",
    "# e.g. Summing of the values of Quantity where Manager=\"Debra Henley\" and Status=\"pending\" gives 6"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "metadata": {
    "scrolled": true
   },
   "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\">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",
       "      <td rowspan=\"4\" valign=\"top\">Debra Henley</td>\n",
       "      <td>won</td>\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",
       "      <td>pending</td>\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",
       "      <td>presented</td>\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",
       "      <td>declined</td>\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",
       "      <td rowspan=\"4\" valign=\"top\">Fred Anderson</td>\n",
       "      <td>won</td>\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",
       "      <td>pending</td>\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",
       "      <td>presented</td>\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",
       "      <td>declined</td>\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": 166,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# To provide a list of aggregation functions to apply to each value too\n",
    "pd.pivot_table(df, index=[\"Manager\", \"Status\"], columns=[\"Product\"], \n",
    "               values=[\"Quantity\", \"Price\"], \n",
    "               aggfunc={\"Quantity\":len, \"Price\":[np.sum, np.mean]}, fill_value=0)"
   ]
  },
  {
   "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 standard DataFrame functions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 170,
   "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\">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",
       "      <td rowspan=\"4\" valign=\"top\">Debra Henley</td>\n",
       "      <td>won</td>\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",
       "      <td>pending</td>\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",
       "      <td>presented</td>\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",
       "      <td>declined</td>\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": 170,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "table = pd.pivot_table(df, index=[\"Manager\", \"Status\"], columns=[\"Product\"], \n",
    "               values=[\"Quantity\", \"Price\"], \n",
    "               aggfunc={\"Quantity\":len, \"Price\":[np.sum, np.mean]}, fill_value=0)\n",
    "# To look at just one manager\n",
    "table.query('Manager == [\"Debra Henley\"]')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 168,
   "metadata": {
    "scrolled": true
   },
   "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\">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",
       "      <td rowspan=\"2\" valign=\"top\">Debra Henley</td>\n",
       "      <td>won</td>\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",
       "      <td>pending</td>\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",
       "      <td rowspan=\"2\" valign=\"top\">Fred Anderson</td>\n",
       "      <td>won</td>\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",
       "      <td>pending</td>\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": 168,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# To look at all of the \"pending\" and \"won\" deals\n",
    "table.query('Status == [\"pending\", \"won\"]')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}