data-analysis/003-pandas-pivot-table/003-pandas-pivot-table.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Pandas pivot table\n",
    "\n",
    "This is a notebook for the medium article [A Practical Introduction to Pandas pivot_table() function](https://medium.com/@bindiatwork/a-practical-introduction-to-pandas-pivot-table-function-3e1002dcd4eb)\n",
    "\n",
    "Please check out article for instructions\n",
    "\n",
    "## Copyright\n",
    "\n",
    "This notebook and the medium article are Copyright (c) B. Chen. You are\n",
    "\n",
    "* free for study\n",
    "* free for making copies of the notebooks or forking this repo for your own private use\n",
    "* **not allowed for commercial or broadcast use**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## The Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "def load_data():\n",
    "    return pd.read_csv('data/coffee_sales.csv', parse_dates=['order_date'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>order_date</th>\n",
       "      <th>market</th>\n",
       "      <th>region</th>\n",
       "      <th>product_category</th>\n",
       "      <th>product</th>\n",
       "      <th>cost</th>\n",
       "      <th>inventory</th>\n",
       "      <th>net_profit</th>\n",
       "      <th>sales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2010-01-01</td>\n",
       "      <td>Wholesale</td>\n",
       "      <td>Central</td>\n",
       "      <td>Coffee</td>\n",
       "      <td>Amaretto</td>\n",
       "      <td>89</td>\n",
       "      <td>777</td>\n",
       "      <td>94</td>\n",
       "      <td>219</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2010-01-01</td>\n",
       "      <td>Wholesale</td>\n",
       "      <td>Central</td>\n",
       "      <td>Coffee</td>\n",
       "      <td>Columbian</td>\n",
       "      <td>83</td>\n",
       "      <td>623</td>\n",
       "      <td>68</td>\n",
       "      <td>190</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2010-01-01</td>\n",
       "      <td>Wholesale</td>\n",
       "      <td>Central</td>\n",
       "      <td>Coffee</td>\n",
       "      <td>Decaf Irish Cream</td>\n",
       "      <td>95</td>\n",
       "      <td>821</td>\n",
       "      <td>101</td>\n",
       "      <td>234</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2010-01-01</td>\n",
       "      <td>Wholesale</td>\n",
       "      <td>Central</td>\n",
       "      <td>Tea</td>\n",
       "      <td>Green Tea</td>\n",
       "      <td>44</td>\n",
       "      <td>623</td>\n",
       "      <td>30</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2010-01-01</td>\n",
       "      <td>Wholesale</td>\n",
       "      <td>Central</td>\n",
       "      <td>Espresso</td>\n",
       "      <td>Caffe Mocha</td>\n",
       "      <td>54</td>\n",
       "      <td>456</td>\n",
       "      <td>54</td>\n",
       "      <td>134</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  order_date     market   region product_category            product  cost  \\\n",
       "0 2010-01-01  Wholesale  Central           Coffee           Amaretto    89   \n",
       "1 2010-01-01  Wholesale  Central           Coffee          Columbian    83   \n",
       "2 2010-01-01  Wholesale  Central           Coffee  Decaf Irish Cream    95   \n",
       "3 2010-01-01  Wholesale  Central              Tea          Green Tea    44   \n",
       "4 2010-01-01  Wholesale  Central         Espresso        Caffe Mocha    54   \n",
       "\n",
       "   inventory  net_profit  sales  \n",
       "0        777          94    219  \n",
       "1        623          68    190  \n",
       "2        821         101    234  \n",
       "3        623          30    100  \n",
       "4        456          54    134  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = load_data()\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 4248 entries, 0 to 4247\n",
      "Data columns (total 9 columns):\n",
      " #   Column            Non-Null Count  Dtype         \n",
      "---  ------            --------------  -----         \n",
      " 0   order_date        4248 non-null   datetime64[ns]\n",
      " 1   market            4248 non-null   object        \n",
      " 2   region            4248 non-null   object        \n",
      " 3   product_category  4248 non-null   object        \n",
      " 4   product           4248 non-null   object        \n",
      " 5   cost              4248 non-null   int64         \n",
      " 6   inventory         4248 non-null   int64         \n",
      " 7   net_profit        4248 non-null   int64         \n",
      " 8   sales             4248 non-null   int64         \n",
      "dtypes: datetime64[ns](1), int64(4), object(4)\n",
      "memory usage: 298.8+ KB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Simplest Pivot table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>cost</th>\n",
       "      <th>inventory</th>\n",
       "      <th>net_profit</th>\n",
       "      <th>sales</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>region</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Central</th>\n",
       "      <td>84.157738</td>\n",
       "      <td>742.648810</td>\n",
       "      <td>69.830357</td>\n",
       "      <td>197.206101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>East</th>\n",
       "      <td>85.421171</td>\n",
       "      <td>778.191441</td>\n",
       "      <td>66.685811</td>\n",
       "      <td>201.099099</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>South</th>\n",
       "      <td>65.327381</td>\n",
       "      <td>478.273810</td>\n",
       "      <td>48.330357</td>\n",
       "      <td>154.651786</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>West</th>\n",
       "      <td>93.608631</td>\n",
       "      <td>872.632440</td>\n",
       "      <td>55.056548</td>\n",
       "      <td>202.577381</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              cost   inventory  net_profit       sales\n",
       "region                                                \n",
       "Central  84.157738  742.648810   69.830357  197.206101\n",
       "East     85.421171  778.191441   66.685811  201.099099\n",
       "South    65.327381  478.273810   48.330357  154.651786\n",
       "West     93.608631  872.632440   55.056548  202.577381"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.pivot_table(index='region')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>cost</th>\n",
       "      <th>inventory</th>\n",
       "      <th>net_profit</th>\n",
       "      <th>sales</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>region</th>\n",
       "      <th>product_category</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">Central</th>\n",
       "      <th>Coffee</th>\n",
       "      <td>74.390625</td>\n",
       "      <td>680.781250</td>\n",
       "      <td>60.583333</td>\n",
       "      <td>179.895833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Espresso</th>\n",
       "      <td>89.666667</td>\n",
       "      <td>882.090278</td>\n",
       "      <td>81.600694</td>\n",
       "      <td>207.295139</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Herbal tea</th>\n",
       "      <td>87.065476</td>\n",
       "      <td>763.952381</td>\n",
       "      <td>73.681548</td>\n",
       "      <td>202.047619</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Tea</th>\n",
       "      <td>87.690476</td>\n",
       "      <td>672.529762</td>\n",
       "      <td>66.458333</td>\n",
       "      <td>203.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">East</th>\n",
       "      <th>Coffee</th>\n",
       "      <td>110.976190</td>\n",
       "      <td>580.785714</td>\n",
       "      <td>184.476190</td>\n",
       "      <td>337.148810</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Espresso</th>\n",
       "      <td>95.600000</td>\n",
       "      <td>767.133333</td>\n",
       "      <td>26.016667</td>\n",
       "      <td>201.654167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Herbal tea</th>\n",
       "      <td>101.240741</td>\n",
       "      <td>963.212963</td>\n",
       "      <td>29.736111</td>\n",
       "      <td>191.486111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Tea</th>\n",
       "      <td>46.962121</td>\n",
       "      <td>762.484848</td>\n",
       "      <td>58.931818</td>\n",
       "      <td>121.882576</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">South</th>\n",
       "      <th>Coffee</th>\n",
       "      <td>78.916667</td>\n",
       "      <td>803.687500</td>\n",
       "      <td>60.947917</td>\n",
       "      <td>173.223958</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Espresso</th>\n",
       "      <td>62.305556</td>\n",
       "      <td>184.645833</td>\n",
       "      <td>52.100694</td>\n",
       "      <td>156.215278</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Herbal tea</th>\n",
       "      <td>56.270833</td>\n",
       "      <td>593.302083</td>\n",
       "      <td>30.057292</td>\n",
       "      <td>133.734375</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">West</th>\n",
       "      <th>Coffee</th>\n",
       "      <td>90.814103</td>\n",
       "      <td>931.583333</td>\n",
       "      <td>27.964744</td>\n",
       "      <td>185.410256</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Espresso</th>\n",
       "      <td>84.133333</td>\n",
       "      <td>828.933333</td>\n",
       "      <td>66.305556</td>\n",
       "      <td>194.188889</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Herbal tea</th>\n",
       "      <td>98.987179</td>\n",
       "      <td>802.282051</td>\n",
       "      <td>84.304487</td>\n",
       "      <td>231.692308</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Tea</th>\n",
       "      <td>100.844444</td>\n",
       "      <td>926.211111</td>\n",
       "      <td>41.938889</td>\n",
       "      <td>200.611111</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                cost   inventory  net_profit       sales\n",
       "region  product_category                                                \n",
       "Central Coffee             74.390625  680.781250   60.583333  179.895833\n",
       "        Espresso           89.666667  882.090278   81.600694  207.295139\n",
       "        Herbal tea         87.065476  763.952381   73.681548  202.047619\n",
       "        Tea                87.690476  672.529762   66.458333  203.500000\n",
       "East    Coffee            110.976190  580.785714  184.476190  337.148810\n",
       "        Espresso           95.600000  767.133333   26.016667  201.654167\n",
       "        Herbal tea        101.240741  963.212963   29.736111  191.486111\n",
       "        Tea                46.962121  762.484848   58.931818  121.882576\n",
       "South   Coffee             78.916667  803.687500   60.947917  173.223958\n",
       "        Espresso           62.305556  184.645833   52.100694  156.215278\n",
       "        Herbal tea         56.270833  593.302083   30.057292  133.734375\n",
       "West    Coffee             90.814103  931.583333   27.964744  185.410256\n",
       "        Espresso           84.133333  828.933333   66.305556  194.188889\n",
       "        Herbal tea         98.987179  802.282051   84.304487  231.692308\n",
       "        Tea               100.844444  926.211111   41.938889  200.611111"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# With multiple indexes\n",
    "df.pivot_table(index=['region', 'product_category'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>cost</th>\n",
       "      <th>inventory</th>\n",
       "      <th>net_profit</th>\n",
       "      <th>sales</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>product_category</th>\n",
       "      <th>region</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">Coffee</th>\n",
       "      <th>Central</th>\n",
       "      <td>74.390625</td>\n",
       "      <td>680.781250</td>\n",
       "      <td>60.583333</td>\n",
       "      <td>179.895833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>East</th>\n",
       "      <td>110.976190</td>\n",
       "      <td>580.785714</td>\n",
       "      <td>184.476190</td>\n",
       "      <td>337.148810</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>South</th>\n",
       "      <td>78.916667</td>\n",
       "      <td>803.687500</td>\n",
       "      <td>60.947917</td>\n",
       "      <td>173.223958</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>West</th>\n",
       "      <td>90.814103</td>\n",
       "      <td>931.583333</td>\n",
       "      <td>27.964744</td>\n",
       "      <td>185.410256</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">Espresso</th>\n",
       "      <th>Central</th>\n",
       "      <td>89.666667</td>\n",
       "      <td>882.090278</td>\n",
       "      <td>81.600694</td>\n",
       "      <td>207.295139</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>East</th>\n",
       "      <td>95.600000</td>\n",
       "      <td>767.133333</td>\n",
       "      <td>26.016667</td>\n",
       "      <td>201.654167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>South</th>\n",
       "      <td>62.305556</td>\n",
       "      <td>184.645833</td>\n",
       "      <td>52.100694</td>\n",
       "      <td>156.215278</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>West</th>\n",
       "      <td>84.133333</td>\n",
       "      <td>828.933333</td>\n",
       "      <td>66.305556</td>\n",
       "      <td>194.188889</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">Herbal tea</th>\n",
       "      <th>Central</th>\n",
       "      <td>87.065476</td>\n",
       "      <td>763.952381</td>\n",
       "      <td>73.681548</td>\n",
       "      <td>202.047619</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>East</th>\n",
       "      <td>101.240741</td>\n",
       "      <td>963.212963</td>\n",
       "      <td>29.736111</td>\n",
       "      <td>191.486111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>South</th>\n",
       "      <td>56.270833</td>\n",
       "      <td>593.302083</td>\n",
       "      <td>30.057292</td>\n",
       "      <td>133.734375</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>West</th>\n",
       "      <td>98.987179</td>\n",
       "      <td>802.282051</td>\n",
       "      <td>84.304487</td>\n",
       "      <td>231.692308</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Tea</th>\n",
       "      <th>Central</th>\n",
       "      <td>87.690476</td>\n",
       "      <td>672.529762</td>\n",
       "      <td>66.458333</td>\n",
       "      <td>203.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>East</th>\n",
       "      <td>46.962121</td>\n",
       "      <td>762.484848</td>\n",
       "      <td>58.931818</td>\n",
       "      <td>121.882576</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>West</th>\n",
       "      <td>100.844444</td>\n",
       "      <td>926.211111</td>\n",
       "      <td>41.938889</td>\n",
       "      <td>200.611111</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                cost   inventory  net_profit       sales\n",
       "product_category region                                                 \n",
       "Coffee           Central   74.390625  680.781250   60.583333  179.895833\n",
       "                 East     110.976190  580.785714  184.476190  337.148810\n",
       "                 South     78.916667  803.687500   60.947917  173.223958\n",
       "                 West      90.814103  931.583333   27.964744  185.410256\n",
       "Espresso         Central   89.666667  882.090278   81.600694  207.295139\n",
       "                 East      95.600000  767.133333   26.016667  201.654167\n",
       "                 South     62.305556  184.645833   52.100694  156.215278\n",
       "                 West      84.133333  828.933333   66.305556  194.188889\n",
       "Herbal tea       Central   87.065476  763.952381   73.681548  202.047619\n",
       "                 East     101.240741  963.212963   29.736111  191.486111\n",
       "                 South     56.270833  593.302083   30.057292  133.734375\n",
       "                 West      98.987179  802.282051   84.304487  231.692308\n",
       "Tea              Central   87.690476  672.529762   66.458333  203.500000\n",
       "                 East      46.962121  762.484848   58.931818  121.882576\n",
       "                 West     100.844444  926.211111   41.938889  200.611111"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Multiple indexes with different order\n",
    "df.pivot_table(index=['product_category', 'region'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Specifying `values` and performing aggregation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sales</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>region</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Central</th>\n",
       "      <td>197.206101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>East</th>\n",
       "      <td>201.099099</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>South</th>\n",
       "      <td>154.651786</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>West</th>\n",
       "      <td>202.577381</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              sales\n",
       "region             \n",
       "Central  197.206101\n",
       "East     201.099099\n",
       "South    154.651786\n",
       "West     202.577381"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Specifying values\n",
    "df.pivot_table(index=['region'], values=['sales'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sales</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>region</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Central</th>\n",
       "      <td>265045</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>East</th>\n",
       "      <td>178576</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>South</th>\n",
       "      <td>103926</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>West</th>\n",
       "      <td>272264</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          sales\n",
       "region         \n",
       "Central  265045\n",
       "East     178576\n",
       "South    103926\n",
       "West     272264"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.pivot_table(index=['region'], values=['sales'], aggfunc='sum')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead 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>sales</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>region</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Central</th>\n",
       "      <td>265045</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>East</th>\n",
       "      <td>178576</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>South</th>\n",
       "      <td>103926</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>West</th>\n",
       "      <td>272264</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          sales\n",
       "region         \n",
       "Central  265045\n",
       "East     178576\n",
       "South    103926\n",
       "West     272264"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# the dict equivalent\n",
    "df.pivot_table(index=['region'], values=['sales'], aggfunc={ 'sales': 'sum' })"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>sum</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>sales</th>\n",
       "      <th>sales</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>region</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Central</th>\n",
       "      <td>265045</td>\n",
       "      <td>1344</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>East</th>\n",
       "      <td>178576</td>\n",
       "      <td>888</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>South</th>\n",
       "      <td>103926</td>\n",
       "      <td>672</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>West</th>\n",
       "      <td>272264</td>\n",
       "      <td>1344</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            sum count\n",
       "          sales sales\n",
       "region               \n",
       "Central  265045  1344\n",
       "East     178576   888\n",
       "South    103926   672\n",
       "West     272264  1344"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.pivot_table(index=['region'], values=['sales'], aggfunc=['sum', 'count'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Seeing break down using `columns`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"4\" halign=\"left\">sales</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>product_category</th>\n",
       "      <th>Coffee</th>\n",
       "      <th>Espresso</th>\n",
       "      <th>Herbal tea</th>\n",
       "      <th>Tea</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>region</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Central</th>\n",
       "      <td>69080.0</td>\n",
       "      <td>59701.0</td>\n",
       "      <td>67888.0</td>\n",
       "      <td>68376.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>East</th>\n",
       "      <td>56641.0</td>\n",
       "      <td>48397.0</td>\n",
       "      <td>41361.0</td>\n",
       "      <td>32177.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>South</th>\n",
       "      <td>33259.0</td>\n",
       "      <td>44990.0</td>\n",
       "      <td>25677.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>West</th>\n",
       "      <td>57848.0</td>\n",
       "      <td>69908.0</td>\n",
       "      <td>72288.0</td>\n",
       "      <td>72220.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    sales                             \n",
       "product_category   Coffee Espresso Herbal tea      Tea\n",
       "region                                                \n",
       "Central           69080.0  59701.0    67888.0  68376.0\n",
       "East              56641.0  48397.0    41361.0  32177.0\n",
       "South             33259.0  44990.0    25677.0      NaN\n",
       "West              57848.0  69908.0    72288.0  72220.0"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# See break down by product category\n",
    "df.pivot_table(index=['region'], values=['sales'], aggfunc='sum', columns=['product_category'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Replacing missing values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"4\" halign=\"left\">sales</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>product_category</th>\n",
       "      <th>Coffee</th>\n",
       "      <th>Espresso</th>\n",
       "      <th>Herbal tea</th>\n",
       "      <th>Tea</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>region</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Central</th>\n",
       "      <td>69080</td>\n",
       "      <td>59701</td>\n",
       "      <td>67888</td>\n",
       "      <td>68376</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>East</th>\n",
       "      <td>56641</td>\n",
       "      <td>48397</td>\n",
       "      <td>41361</td>\n",
       "      <td>32177</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>South</th>\n",
       "      <td>33259</td>\n",
       "      <td>44990</td>\n",
       "      <td>25677</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>West</th>\n",
       "      <td>57848</td>\n",
       "      <td>69908</td>\n",
       "      <td>72288</td>\n",
       "      <td>72220</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  sales                           \n",
       "product_category Coffee Espresso Herbal tea    Tea\n",
       "region                                            \n",
       "Central           69080    59701      67888  68376\n",
       "East              56641    48397      41361  32177\n",
       "South             33259    44990      25677      0\n",
       "West              57848    69908      72288  72220"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Remove NaN from above output\n",
    "df.pivot_table(index=['region'], values=['sales'], aggfunc='sum', columns=['product_category'], fill_value=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. Displaying multiple `values` and adjusting view"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"4\" halign=\"left\">cost</th>\n",
       "      <th colspan=\"4\" halign=\"left\">sales</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>product_category</th>\n",
       "      <th>Coffee</th>\n",
       "      <th>Espresso</th>\n",
       "      <th>Herbal tea</th>\n",
       "      <th>Tea</th>\n",
       "      <th>Coffee</th>\n",
       "      <th>Espresso</th>\n",
       "      <th>Herbal tea</th>\n",
       "      <th>Tea</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>region</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Central</th>\n",
       "      <td>28566</td>\n",
       "      <td>25824</td>\n",
       "      <td>29254</td>\n",
       "      <td>29464</td>\n",
       "      <td>69080</td>\n",
       "      <td>59701</td>\n",
       "      <td>67888</td>\n",
       "      <td>68376</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>East</th>\n",
       "      <td>18644</td>\n",
       "      <td>22944</td>\n",
       "      <td>21868</td>\n",
       "      <td>12398</td>\n",
       "      <td>56641</td>\n",
       "      <td>48397</td>\n",
       "      <td>41361</td>\n",
       "      <td>32177</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>South</th>\n",
       "      <td>15152</td>\n",
       "      <td>17944</td>\n",
       "      <td>10804</td>\n",
       "      <td>0</td>\n",
       "      <td>33259</td>\n",
       "      <td>44990</td>\n",
       "      <td>25677</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>West</th>\n",
       "      <td>28334</td>\n",
       "      <td>30288</td>\n",
       "      <td>30884</td>\n",
       "      <td>36304</td>\n",
       "      <td>57848</td>\n",
       "      <td>69908</td>\n",
       "      <td>72288</td>\n",
       "      <td>72220</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   cost                             sales                      \\\n",
       "product_category Coffee Espresso Herbal tea    Tea Coffee Espresso Herbal tea   \n",
       "region                                                                          \n",
       "Central           28566    25824      29254  29464  69080    59701      67888   \n",
       "East              18644    22944      21868  12398  56641    48397      41361   \n",
       "South             15152    17944      10804      0  33259    44990      25677   \n",
       "West              28334    30288      30884  36304  57848    69908      72288   \n",
       "\n",
       "                         \n",
       "product_category    Tea  \n",
       "region                   \n",
       "Central           68376  \n",
       "East              32177  \n",
       "South                 0  \n",
       "West              72220  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.pivot_table(index=['region'], values=['sales', 'cost'], aggfunc='sum', columns=['product_category'], fill_value=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>cost</th>\n",
       "      <th>sales</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>region</th>\n",
       "      <th>product_category</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">Central</th>\n",
       "      <th>Coffee</th>\n",
       "      <td>28566</td>\n",
       "      <td>69080</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Espresso</th>\n",
       "      <td>25824</td>\n",
       "      <td>59701</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Herbal tea</th>\n",
       "      <td>29254</td>\n",
       "      <td>67888</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Tea</th>\n",
       "      <td>29464</td>\n",
       "      <td>68376</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">East</th>\n",
       "      <th>Coffee</th>\n",
       "      <td>18644</td>\n",
       "      <td>56641</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Espresso</th>\n",
       "      <td>22944</td>\n",
       "      <td>48397</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Herbal tea</th>\n",
       "      <td>21868</td>\n",
       "      <td>41361</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Tea</th>\n",
       "      <td>12398</td>\n",
       "      <td>32177</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">South</th>\n",
       "      <th>Coffee</th>\n",
       "      <td>15152</td>\n",
       "      <td>33259</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Espresso</th>\n",
       "      <td>17944</td>\n",
       "      <td>44990</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Herbal tea</th>\n",
       "      <td>10804</td>\n",
       "      <td>25677</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">West</th>\n",
       "      <th>Coffee</th>\n",
       "      <td>28334</td>\n",
       "      <td>57848</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Espresso</th>\n",
       "      <td>30288</td>\n",
       "      <td>69908</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Herbal tea</th>\n",
       "      <td>30884</td>\n",
       "      <td>72288</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Tea</th>\n",
       "      <td>36304</td>\n",
       "      <td>72220</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           cost  sales\n",
       "region  product_category              \n",
       "Central Coffee            28566  69080\n",
       "        Espresso          25824  59701\n",
       "        Herbal tea        29254  67888\n",
       "        Tea               29464  68376\n",
       "East    Coffee            18644  56641\n",
       "        Espresso          22944  48397\n",
       "        Herbal tea        21868  41361\n",
       "        Tea               12398  32177\n",
       "South   Coffee            15152  33259\n",
       "        Espresso          17944  44990\n",
       "        Herbal tea        10804  25677\n",
       "West    Coffee            28334  57848\n",
       "        Espresso          30288  69908\n",
       "        Herbal tea        30884  72288\n",
       "        Tea               36304  72220"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Move product_category to the index to get a better view\n",
    "df.pivot_table(index=['region', 'product_category'], values=['sales', 'cost'], aggfunc='sum', fill_value=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6. Showing total"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>cost</th>\n",
       "      <th>sales</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>region</th>\n",
       "      <th>product_category</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">Central</th>\n",
       "      <th>Coffee</th>\n",
       "      <td>28566</td>\n",
       "      <td>69080</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Espresso</th>\n",
       "      <td>25824</td>\n",
       "      <td>59701</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Herbal tea</th>\n",
       "      <td>29254</td>\n",
       "      <td>67888</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Tea</th>\n",
       "      <td>29464</td>\n",
       "      <td>68376</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">East</th>\n",
       "      <th>Coffee</th>\n",
       "      <td>18644</td>\n",
       "      <td>56641</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Espresso</th>\n",
       "      <td>22944</td>\n",
       "      <td>48397</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Herbal tea</th>\n",
       "      <td>21868</td>\n",
       "      <td>41361</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Tea</th>\n",
       "      <td>12398</td>\n",
       "      <td>32177</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">South</th>\n",
       "      <th>Coffee</th>\n",
       "      <td>15152</td>\n",
       "      <td>33259</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Espresso</th>\n",
       "      <td>17944</td>\n",
       "      <td>44990</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Herbal tea</th>\n",
       "      <td>10804</td>\n",
       "      <td>25677</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">West</th>\n",
       "      <th>Coffee</th>\n",
       "      <td>28334</td>\n",
       "      <td>57848</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Espresso</th>\n",
       "      <td>30288</td>\n",
       "      <td>69908</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Herbal tea</th>\n",
       "      <td>30884</td>\n",
       "      <td>72288</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Tea</th>\n",
       "      <td>36304</td>\n",
       "      <td>72220</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>All</th>\n",
       "      <th></th>\n",
       "      <td>358672</td>\n",
       "      <td>819811</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            cost   sales\n",
       "region  product_category                \n",
       "Central Coffee             28566   69080\n",
       "        Espresso           25824   59701\n",
       "        Herbal tea         29254   67888\n",
       "        Tea                29464   68376\n",
       "East    Coffee             18644   56641\n",
       "        Espresso           22944   48397\n",
       "        Herbal tea         21868   41361\n",
       "        Tea                12398   32177\n",
       "South   Coffee             15152   33259\n",
       "        Espresso           17944   44990\n",
       "        Herbal tea         10804   25677\n",
       "West    Coffee             28334   57848\n",
       "        Espresso           30288   69908\n",
       "        Herbal tea         30884   72288\n",
       "        Tea                36304   72220\n",
       "All                       358672  819811"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.pivot_table(index=['region', 'product_category'], values=['sales', 'cost'], aggfunc='sum', fill_value=0, margins=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7. Generating a monthly report"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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=\"12\" halign=\"left\">sum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"12\" halign=\"left\">sales</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>order_date</th>\n",
       "      <th>2010-01-31</th>\n",
       "      <th>2010-02-28</th>\n",
       "      <th>2010-03-31</th>\n",
       "      <th>2010-04-30</th>\n",
       "      <th>2010-05-31</th>\n",
       "      <th>2010-06-30</th>\n",
       "      <th>2010-07-31</th>\n",
       "      <th>2010-08-31</th>\n",
       "      <th>2010-09-30</th>\n",
       "      <th>2010-10-31</th>\n",
       "      <th>2010-11-30</th>\n",
       "      <th>2010-12-31</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>region</th>\n",
       "      <th>product_category</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">Central</th>\n",
       "      <th>Coffee</th>\n",
       "      <td>2663</td>\n",
       "      <td>2708</td>\n",
       "      <td>2703</td>\n",
       "      <td>2815</td>\n",
       "      <td>2878</td>\n",
       "      <td>3008</td>\n",
       "      <td>3148</td>\n",
       "      <td>3012</td>\n",
       "      <td>2734</td>\n",
       "      <td>2694</td>\n",
       "      <td>2632</td>\n",
       "      <td>2813</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Espresso</th>\n",
       "      <td>2370</td>\n",
       "      <td>2432</td>\n",
       "      <td>2467</td>\n",
       "      <td>2446</td>\n",
       "      <td>2497</td>\n",
       "      <td>2497</td>\n",
       "      <td>2556</td>\n",
       "      <td>2554</td>\n",
       "      <td>2394</td>\n",
       "      <td>2391</td>\n",
       "      <td>2282</td>\n",
       "      <td>2320</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Herbal tea</th>\n",
       "      <td>2664</td>\n",
       "      <td>2704</td>\n",
       "      <td>2691</td>\n",
       "      <td>2735</td>\n",
       "      <td>2795</td>\n",
       "      <td>2908</td>\n",
       "      <td>2976</td>\n",
       "      <td>2961</td>\n",
       "      <td>2747</td>\n",
       "      <td>2734</td>\n",
       "      <td>2631</td>\n",
       "      <td>2669</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Tea</th>\n",
       "      <td>2649</td>\n",
       "      <td>2659</td>\n",
       "      <td>2702</td>\n",
       "      <td>2774</td>\n",
       "      <td>2840</td>\n",
       "      <td>2863</td>\n",
       "      <td>2912</td>\n",
       "      <td>2909</td>\n",
       "      <td>2851</td>\n",
       "      <td>2762</td>\n",
       "      <td>2670</td>\n",
       "      <td>2860</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">East</th>\n",
       "      <th>Coffee</th>\n",
       "      <td>2105</td>\n",
       "      <td>2061</td>\n",
       "      <td>2126</td>\n",
       "      <td>2258</td>\n",
       "      <td>2347</td>\n",
       "      <td>2625</td>\n",
       "      <td>2735</td>\n",
       "      <td>2673</td>\n",
       "      <td>2362</td>\n",
       "      <td>2094</td>\n",
       "      <td>2066</td>\n",
       "      <td>2288</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Espresso</th>\n",
       "      <td>1853</td>\n",
       "      <td>1966</td>\n",
       "      <td>1907</td>\n",
       "      <td>1935</td>\n",
       "      <td>1896</td>\n",
       "      <td>2071</td>\n",
       "      <td>1992</td>\n",
       "      <td>1984</td>\n",
       "      <td>1887</td>\n",
       "      <td>2124</td>\n",
       "      <td>2076</td>\n",
       "      <td>1981</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Herbal tea</th>\n",
       "      <td>1609</td>\n",
       "      <td>1621</td>\n",
       "      <td>1638</td>\n",
       "      <td>1753</td>\n",
       "      <td>1779</td>\n",
       "      <td>1795</td>\n",
       "      <td>1926</td>\n",
       "      <td>1677</td>\n",
       "      <td>1539</td>\n",
       "      <td>1544</td>\n",
       "      <td>1605</td>\n",
       "      <td>1755</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Tea</th>\n",
       "      <td>1213</td>\n",
       "      <td>1272</td>\n",
       "      <td>1250</td>\n",
       "      <td>1267</td>\n",
       "      <td>1319</td>\n",
       "      <td>1404</td>\n",
       "      <td>1395</td>\n",
       "      <td>1469</td>\n",
       "      <td>1337</td>\n",
       "      <td>1339</td>\n",
       "      <td>1231</td>\n",
       "      <td>1249</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">South</th>\n",
       "      <th>Coffee</th>\n",
       "      <td>1207</td>\n",
       "      <td>1260</td>\n",
       "      <td>1265</td>\n",
       "      <td>1280</td>\n",
       "      <td>1369</td>\n",
       "      <td>1429</td>\n",
       "      <td>1489</td>\n",
       "      <td>1579</td>\n",
       "      <td>1389</td>\n",
       "      <td>1341</td>\n",
       "      <td>1294</td>\n",
       "      <td>1378</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Espresso</th>\n",
       "      <td>1770</td>\n",
       "      <td>1803</td>\n",
       "      <td>1781</td>\n",
       "      <td>1790</td>\n",
       "      <td>1849</td>\n",
       "      <td>1896</td>\n",
       "      <td>1923</td>\n",
       "      <td>1923</td>\n",
       "      <td>1844</td>\n",
       "      <td>1820</td>\n",
       "      <td>1796</td>\n",
       "      <td>1813</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Herbal tea</th>\n",
       "      <td>999</td>\n",
       "      <td>1019</td>\n",
       "      <td>1009</td>\n",
       "      <td>995</td>\n",
       "      <td>978</td>\n",
       "      <td>1016</td>\n",
       "      <td>1036</td>\n",
       "      <td>1139</td>\n",
       "      <td>1033</td>\n",
       "      <td>1119</td>\n",
       "      <td>1115</td>\n",
       "      <td>1100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">West</th>\n",
       "      <th>Coffee</th>\n",
       "      <td>2339</td>\n",
       "      <td>2298</td>\n",
       "      <td>2313</td>\n",
       "      <td>2332</td>\n",
       "      <td>2351</td>\n",
       "      <td>2495</td>\n",
       "      <td>2541</td>\n",
       "      <td>2523</td>\n",
       "      <td>2359</td>\n",
       "      <td>2234</td>\n",
       "      <td>2228</td>\n",
       "      <td>2293</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Espresso</th>\n",
       "      <td>2723</td>\n",
       "      <td>2759</td>\n",
       "      <td>2796</td>\n",
       "      <td>2798</td>\n",
       "      <td>2829</td>\n",
       "      <td>2897</td>\n",
       "      <td>2992</td>\n",
       "      <td>3077</td>\n",
       "      <td>2816</td>\n",
       "      <td>2857</td>\n",
       "      <td>2788</td>\n",
       "      <td>2868</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Herbal tea</th>\n",
       "      <td>2602</td>\n",
       "      <td>2702</td>\n",
       "      <td>2739</td>\n",
       "      <td>2884</td>\n",
       "      <td>2965</td>\n",
       "      <td>3133</td>\n",
       "      <td>3380</td>\n",
       "      <td>3386</td>\n",
       "      <td>2850</td>\n",
       "      <td>2879</td>\n",
       "      <td>2806</td>\n",
       "      <td>3065</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Tea</th>\n",
       "      <td>2789</td>\n",
       "      <td>2828</td>\n",
       "      <td>2858</td>\n",
       "      <td>2881</td>\n",
       "      <td>3000</td>\n",
       "      <td>3088</td>\n",
       "      <td>3160</td>\n",
       "      <td>3163</td>\n",
       "      <td>2950</td>\n",
       "      <td>2917</td>\n",
       "      <td>2783</td>\n",
       "      <td>2921</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                sum                                   \\\n",
       "                              sales                                    \n",
       "order_date               2010-01-31 2010-02-28 2010-03-31 2010-04-30   \n",
       "region  product_category                                               \n",
       "Central Coffee                 2663       2708       2703       2815   \n",
       "        Espresso               2370       2432       2467       2446   \n",
       "        Herbal tea             2664       2704       2691       2735   \n",
       "        Tea                    2649       2659       2702       2774   \n",
       "East    Coffee                 2105       2061       2126       2258   \n",
       "        Espresso               1853       1966       1907       1935   \n",
       "        Herbal tea             1609       1621       1638       1753   \n",
       "        Tea                    1213       1272       1250       1267   \n",
       "South   Coffee                 1207       1260       1265       1280   \n",
       "        Espresso               1770       1803       1781       1790   \n",
       "        Herbal tea              999       1019       1009        995   \n",
       "West    Coffee                 2339       2298       2313       2332   \n",
       "        Espresso               2723       2759       2796       2798   \n",
       "        Herbal tea             2602       2702       2739       2884   \n",
       "        Tea                    2789       2828       2858       2881   \n",
       "\n",
       "                                                                      \\\n",
       "                                                                       \n",
       "order_date               2010-05-31 2010-06-30 2010-07-31 2010-08-31   \n",
       "region  product_category                                               \n",
       "Central Coffee                 2878       3008       3148       3012   \n",
       "        Espresso               2497       2497       2556       2554   \n",
       "        Herbal tea             2795       2908       2976       2961   \n",
       "        Tea                    2840       2863       2912       2909   \n",
       "East    Coffee                 2347       2625       2735       2673   \n",
       "        Espresso               1896       2071       1992       1984   \n",
       "        Herbal tea             1779       1795       1926       1677   \n",
       "        Tea                    1319       1404       1395       1469   \n",
       "South   Coffee                 1369       1429       1489       1579   \n",
       "        Espresso               1849       1896       1923       1923   \n",
       "        Herbal tea              978       1016       1036       1139   \n",
       "West    Coffee                 2351       2495       2541       2523   \n",
       "        Espresso               2829       2897       2992       3077   \n",
       "        Herbal tea             2965       3133       3380       3386   \n",
       "        Tea                    3000       3088       3160       3163   \n",
       "\n",
       "                                                                      \n",
       "                                                                      \n",
       "order_date               2010-09-30 2010-10-31 2010-11-30 2010-12-31  \n",
       "region  product_category                                              \n",
       "Central Coffee                 2734       2694       2632       2813  \n",
       "        Espresso               2394       2391       2282       2320  \n",
       "        Herbal tea             2747       2734       2631       2669  \n",
       "        Tea                    2851       2762       2670       2860  \n",
       "East    Coffee                 2362       2094       2066       2288  \n",
       "        Espresso               1887       2124       2076       1981  \n",
       "        Herbal tea             1539       1544       1605       1755  \n",
       "        Tea                    1337       1339       1231       1249  \n",
       "South   Coffee                 1389       1341       1294       1378  \n",
       "        Espresso               1844       1820       1796       1813  \n",
       "        Herbal tea             1033       1119       1115       1100  \n",
       "West    Coffee                 2359       2234       2228       2293  \n",
       "        Espresso               2816       2857       2788       2868  \n",
       "        Herbal tea             2850       2879       2806       3065  \n",
       "        Tea                    2950       2917       2783       2921  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Let's generate a monthly sales report\n",
    "month_gp = pd.Grouper(key='order_date',freq='M')\n",
    "cond = df[\"order_date\"].dt.year == 2010\n",
    "\n",
    "(\n",
    "    df[cond]\n",
    "    .pivot_table(index=['region','product_category'],\n",
    "        columns=[month_gp],\n",
    "        values=['sales'],\n",
    "        aggfunc=['sum'])\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## That's it\n",
    "\n",
    "This is a notebook for the medium article [A Practical Introduction to Pandas pivot_table() function](https://medium.com/@bindiatwork/a-practical-introduction-to-pandas-pivot-table-function-3e1002dcd4eb)\n",
    "\n",
    "Please check out article for instructions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "tf-tutorial",
   "language": "python",
   "name": "tf-tutorial"
  },
  "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.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}