Data Cleaning & Data Munging 03.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 目录\n",
    "* [两个df或者Series的重叠关系](#check-Pandas-Column-contains-a-particular-value)\n",
    "\n",
    "* [透视表专题](#透视表相关)\n",
    "\n",
    "\n",
    "**======================================================**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### check Pandas Column contains a particular value\n",
    "\n",
    "* 很多使用需要查看两个df直接的联系，例如某个列的值是否在另一个df中的某个列中有出现；\n",
    "* 根据需求或者情形的不同，有多种不同的实现方式"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "先看看最简单常用的关键字“in”"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    a\n",
       "1    b\n",
       "2    c\n",
       "dtype: object"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s = pd.Series(list('abc'))\n",
    "s"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "最先想到使用的检查方法是：\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "'a' in s"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "结果却不是意料中的True，这是因为\n",
    "\n",
    "in of a Series checks whether the value is in the **index**:\n",
    "\n",
    "只检查是否在index中出现。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 所以一下操作为True\n",
    "\n",
    "1 in s"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "一直比较迂回点的办法是先转变为array再使用in来检查，例如借助unique()方法：\n",
    "\n",
    "但是这种办法是比较蠢的，因为需要花费一定资源先计算unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['a', 'b', 'c'], dtype=object)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "'a' in s.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The slowest run took 15.06 times longer than the fastest. This could mean that an intermediate result is being cached \n",
      "100000 loops, best of 3: 18.3 µs per loop\n"
     ]
    }
   ],
   "source": [
    "%timeit 'a' in s.unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "比这个好的办法当然是使用s.values:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The slowest run took 12.81 times longer than the fastest. This could mean that an intermediate result is being cached \n",
      "100000 loops, best of 3: 4.01 µs per loop\n"
     ]
    }
   ],
   "source": [
    "%timeit 'a' in s.values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "以上的做法是判断单个值是否在column中出现，\n",
    "\n",
    "很多时候在项目中是处理整个列，使用上述方法需要配合apply方法逐row判断"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Series中有个很好用的method是 isin ,可以直接判断整个Series并返回相同shape的Series（bool dtype）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "s1 = pd.Series(list('abcdads2er'))\n",
    "s2 = pd.Series(list('dvadtqqtdd'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     True\n",
       "1    False\n",
       "2    False\n",
       "3     True\n",
       "4     True\n",
       "5     True\n",
       "6    False\n",
       "7    False\n",
       "8    False\n",
       "9    False\n",
       "dtype: bool"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s1.isin(s2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "isin() 所带的参数不一定是Series，可以是所以list like的object 例如list set 等："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     True\n",
       "1    False\n",
       "2    False\n",
       "3     True\n",
       "4     True\n",
       "5     True\n",
       "6     True\n",
       "7    False\n",
       "8    False\n",
       "9    False\n",
       "dtype: bool"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s1.isin(list('asdfasdf'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df = pd.DataFrame({'s1':s1, 's2': s2})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "这个bool type的Series可以利用的地方还是很多的：\n",
    "* 现在新的column\n",
    "* 基于True Fasle取相关组合"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>s1</th>\n",
       "      <th>s2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a</td>\n",
       "      <td>d</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>b</td>\n",
       "      <td>v</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>c</td>\n",
       "      <td>a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>d</td>\n",
       "      <td>d</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>a</td>\n",
       "      <td>t</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>d</td>\n",
       "      <td>q</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>s</td>\n",
       "      <td>q</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2</td>\n",
       "      <td>t</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>e</td>\n",
       "      <td>d</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>r</td>\n",
       "      <td>d</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  s1 s2\n",
       "0  a  d\n",
       "1  b  v\n",
       "2  c  a\n",
       "3  d  d\n",
       "4  a  t\n",
       "5  d  q\n",
       "6  s  q\n",
       "7  2  t\n",
       "8  e  d\n",
       "9  r  d"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df['s1_in_s2'] = df.s1.isin(df.s2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>s1</th>\n",
       "      <th>s2</th>\n",
       "      <th>s1_in_s2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a</td>\n",
       "      <td>d</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>b</td>\n",
       "      <td>v</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>c</td>\n",
       "      <td>a</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>d</td>\n",
       "      <td>d</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>a</td>\n",
       "      <td>t</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>d</td>\n",
       "      <td>q</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>s</td>\n",
       "      <td>q</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2</td>\n",
       "      <td>t</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>e</td>\n",
       "      <td>d</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>r</td>\n",
       "      <td>d</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  s1 s2 s1_in_s2\n",
       "0  a  d     True\n",
       "1  b  v    False\n",
       "2  c  a    False\n",
       "3  d  d     True\n",
       "4  a  t     True\n",
       "5  d  q     True\n",
       "6  s  q    False\n",
       "7  2  t    False\n",
       "8  e  d    False\n",
       "9  r  d    False"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>s1</th>\n",
       "      <th>s2</th>\n",
       "      <th>s1_in_s2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>s</td>\n",
       "      <td>q</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>e</td>\n",
       "      <td>d</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  s1 s2 s1_in_s2\n",
       "6  s  q    False\n",
       "8  e  d    False"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df.s1.isin(list('test'))]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>s1</th>\n",
       "      <th>s2</th>\n",
       "      <th>s1_in_s2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a</td>\n",
       "      <td>d</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>b</td>\n",
       "      <td>v</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>c</td>\n",
       "      <td>a</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>d</td>\n",
       "      <td>d</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>a</td>\n",
       "      <td>t</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>d</td>\n",
       "      <td>q</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2</td>\n",
       "      <td>t</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>r</td>\n",
       "      <td>d</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  s1 s2 s1_in_s2\n",
       "0  a  d     True\n",
       "1  b  v    False\n",
       "2  c  a    False\n",
       "3  d  d     True\n",
       "4  a  t     True\n",
       "5  d  q     True\n",
       "7  2  t    False\n",
       "9  r  d    False"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[~df.s1.isin(list('test'))]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 透视表相关\n",
    "\n",
    "示例数据： data/olive.csv\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "data = pd.read_csv('data/olive.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>region</th>\n",
       "      <th>area</th>\n",
       "      <th>palmitic</th>\n",
       "      <th>palmitoleic</th>\n",
       "      <th>stearic</th>\n",
       "      <th>oleic</th>\n",
       "      <th>linoleic</th>\n",
       "      <th>linolenic</th>\n",
       "      <th>arachidic</th>\n",
       "      <th>eicosenoic</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.North-Apulia</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1075</td>\n",
       "      <td>75</td>\n",
       "      <td>226</td>\n",
       "      <td>7823</td>\n",
       "      <td>672</td>\n",
       "      <td>36</td>\n",
       "      <td>60</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2.North-Apulia</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1088</td>\n",
       "      <td>73</td>\n",
       "      <td>224</td>\n",
       "      <td>7709</td>\n",
       "      <td>781</td>\n",
       "      <td>31</td>\n",
       "      <td>61</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3.North-Apulia</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>911</td>\n",
       "      <td>54</td>\n",
       "      <td>246</td>\n",
       "      <td>8113</td>\n",
       "      <td>549</td>\n",
       "      <td>31</td>\n",
       "      <td>63</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4.North-Apulia</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>966</td>\n",
       "      <td>57</td>\n",
       "      <td>240</td>\n",
       "      <td>7952</td>\n",
       "      <td>619</td>\n",
       "      <td>50</td>\n",
       "      <td>78</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5.North-Apulia</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1051</td>\n",
       "      <td>67</td>\n",
       "      <td>259</td>\n",
       "      <td>7771</td>\n",
       "      <td>672</td>\n",
       "      <td>50</td>\n",
       "      <td>80</td>\n",
       "      <td>46</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Unnamed: 0  region  area  palmitic  palmitoleic  stearic  oleic  \\\n",
       "0  1.North-Apulia       1     1      1075           75      226   7823   \n",
       "1  2.North-Apulia       1     1      1088           73      224   7709   \n",
       "2  3.North-Apulia       1     1       911           54      246   8113   \n",
       "3  4.North-Apulia       1     1       966           57      240   7952   \n",
       "4  5.North-Apulia       1     1      1051           67      259   7771   \n",
       "\n",
       "   linoleic  linolenic  arachidic  eicosenoic  \n",
       "0       672         36         60          29  \n",
       "1       781         31         61          29  \n",
       "2       549         31         63          29  \n",
       "3       619         50         78          35  \n",
       "4       672         50         80          46  "
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([1, 2, 3], dtype=int64)"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.region.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([1, 2, 3, 4, 5, 6, 9, 7, 8], dtype=int64)"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.area.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>region</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>area</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>25</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>56</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>206</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>36</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0</td>\n",
       "      <td>65</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0</td>\n",
       "      <td>33</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>51</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "region    1   2   3\n",
       "area               \n",
       "1        25   0   0\n",
       "2        56   0   0\n",
       "3       206   0   0\n",
       "4        36   0   0\n",
       "5         0  65   0\n",
       "6         0  33   0\n",
       "7         0   0  50\n",
       "8         0   0  50\n",
       "9         0   0  51"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.crosstab(data.area, data.region)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "根据http://pbpython.com/pandas-pivot-table-explained.html 完整学习一下`pivot_table `\n",
    "\n",
    "data: data/sales-funnel.xlsx\n",
    "\n",
    "数据是某个渠道的销售数据\n",
    "\n",
    "* 透视表能解决什么样的问题\n",
    "* 如何高效的利用pandas搞定透视表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Account</th>\n",
       "      <th>Name</th>\n",
       "      <th>Rep</th>\n",
       "      <th>Manager</th>\n",
       "      <th>Product</th>\n",
       "      <th>Quantity</th>\n",
       "      <th>Price</th>\n",
       "      <th>Status</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>714466</td>\n",
       "      <td>Trantow-Barrows</td>\n",
       "      <td>Craig Booker</td>\n",
       "      <td>Debra Henley</td>\n",
       "      <td>CPU</td>\n",
       "      <td>1</td>\n",
       "      <td>30000</td>\n",
       "      <td>presented</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>714466</td>\n",
       "      <td>Trantow-Barrows</td>\n",
       "      <td>Craig Booker</td>\n",
       "      <td>Debra Henley</td>\n",
       "      <td>Software</td>\n",
       "      <td>1</td>\n",
       "      <td>10000</td>\n",
       "      <td>presented</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>714466</td>\n",
       "      <td>Trantow-Barrows</td>\n",
       "      <td>Craig Booker</td>\n",
       "      <td>Debra Henley</td>\n",
       "      <td>Maintenance</td>\n",
       "      <td>2</td>\n",
       "      <td>5000</td>\n",
       "      <td>pending</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>737550</td>\n",
       "      <td>Fritsch, Russel and Anderson</td>\n",
       "      <td>Craig Booker</td>\n",
       "      <td>Debra Henley</td>\n",
       "      <td>CPU</td>\n",
       "      <td>1</td>\n",
       "      <td>35000</td>\n",
       "      <td>declined</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>146832</td>\n",
       "      <td>Kiehn-Spinka</td>\n",
       "      <td>Daniel Hilton</td>\n",
       "      <td>Debra Henley</td>\n",
       "      <td>CPU</td>\n",
       "      <td>2</td>\n",
       "      <td>65000</td>\n",
       "      <td>won</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Account                          Name            Rep       Manager  \\\n",
       "0   714466               Trantow-Barrows   Craig Booker  Debra Henley   \n",
       "1   714466               Trantow-Barrows   Craig Booker  Debra Henley   \n",
       "2   714466               Trantow-Barrows   Craig Booker  Debra Henley   \n",
       "3   737550  Fritsch, Russel and Anderson   Craig Booker  Debra Henley   \n",
       "4   146832                  Kiehn-Spinka  Daniel Hilton  Debra Henley   \n",
       "\n",
       "       Product  Quantity  Price     Status  \n",
       "0          CPU         1  30000  presented  \n",
       "1     Software         1  10000  presented  \n",
       "2  Maintenance         2   5000    pending  \n",
       "3          CPU         1  35000   declined  \n",
       "4          CPU         2  65000        won  "
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_excel(\"data/sales-funnel.xlsx\")\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([u'presented', u'pending', u'declined', u'won'], dtype=object)"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.Status.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df[\"Status\"] = df[\"Status\"].astype(\"category\")\n",
    "df[\"Status\"].cat.set_categories([\"won\",\"pending\",\"presented\",\"declined\"],inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Account        int64\n",
       "Name          object\n",
       "Rep           object\n",
       "Manager       object\n",
       "Product       object\n",
       "Quantity       int64\n",
       "Price          int64\n",
       "Status      category\n",
       "dtype: object"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "最简单的透视表必须有一个数据帧和一个索引。在本例中，我们将使用“Name（名字）”列作为我们的索引。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Account</th>\n",
       "      <th>Price</th>\n",
       "      <th>Quantity</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Name</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Barton LLC</th>\n",
       "      <td>740150</td>\n",
       "      <td>35000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Fritsch, Russel and Anderson</th>\n",
       "      <td>737550</td>\n",
       "      <td>35000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Herman LLC</th>\n",
       "      <td>141962</td>\n",
       "      <td>65000</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jerde-Hilpert</th>\n",
       "      <td>412290</td>\n",
       "      <td>5000</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kassulke, Ondricka and Metz</th>\n",
       "      <td>307599</td>\n",
       "      <td>7000</td>\n",
       "      <td>3.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Keeling LLC</th>\n",
       "      <td>688981</td>\n",
       "      <td>100000</td>\n",
       "      <td>5.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kiehn-Spinka</th>\n",
       "      <td>146832</td>\n",
       "      <td>65000</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Koepp Ltd</th>\n",
       "      <td>729833</td>\n",
       "      <td>35000</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kulas Inc</th>\n",
       "      <td>218895</td>\n",
       "      <td>25000</td>\n",
       "      <td>1.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Purdy-Kunde</th>\n",
       "      <td>163416</td>\n",
       "      <td>30000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Stokes LLC</th>\n",
       "      <td>239344</td>\n",
       "      <td>7500</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Trantow-Barrows</th>\n",
       "      <td>714466</td>\n",
       "      <td>15000</td>\n",
       "      <td>1.333333</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              Account   Price  Quantity\n",
       "Name                                                   \n",
       "Barton LLC                     740150   35000  1.000000\n",
       "Fritsch, Russel and Anderson   737550   35000  1.000000\n",
       "Herman LLC                     141962   65000  2.000000\n",
       "Jerde-Hilpert                  412290    5000  2.000000\n",
       "Kassulke, Ondricka and Metz    307599    7000  3.000000\n",
       "Keeling LLC                    688981  100000  5.000000\n",
       "Kiehn-Spinka                   146832   65000  2.000000\n",
       "Koepp Ltd                      729833   35000  2.000000\n",
       "Kulas Inc                      218895   25000  1.500000\n",
       "Purdy-Kunde                    163416   30000  1.000000\n",
       "Stokes LLC                     239344    7500  1.000000\n",
       "Trantow-Barrows                714466   15000  1.333333"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(df,index=[\"Name\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "透视表所产生的总结，自动把int或者float属性的字段取平均值并返回。例如上面的accout，price和quantity"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "此外，你也可以有多个索引。实际上，大多数的pivot_table参数可以通过列表获取多个值。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>Account</th>\n",
       "      <th>Price</th>\n",
       "      <th>Quantity</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Name</th>\n",
       "      <th>Rep</th>\n",
       "      <th>Manager</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Barton LLC</th>\n",
       "      <th>John Smith</th>\n",
       "      <th>Debra Henley</th>\n",
       "      <td>740150</td>\n",
       "      <td>35000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Fritsch, Russel and Anderson</th>\n",
       "      <th>Craig Booker</th>\n",
       "      <th>Debra Henley</th>\n",
       "      <td>737550</td>\n",
       "      <td>35000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Herman LLC</th>\n",
       "      <th>Cedric Moss</th>\n",
       "      <th>Fred Anderson</th>\n",
       "      <td>141962</td>\n",
       "      <td>65000</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jerde-Hilpert</th>\n",
       "      <th>John Smith</th>\n",
       "      <th>Debra Henley</th>\n",
       "      <td>412290</td>\n",
       "      <td>5000</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kassulke, Ondricka and Metz</th>\n",
       "      <th>Wendy Yule</th>\n",
       "      <th>Fred Anderson</th>\n",
       "      <td>307599</td>\n",
       "      <td>7000</td>\n",
       "      <td>3.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Keeling LLC</th>\n",
       "      <th>Wendy Yule</th>\n",
       "      <th>Fred Anderson</th>\n",
       "      <td>688981</td>\n",
       "      <td>100000</td>\n",
       "      <td>5.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kiehn-Spinka</th>\n",
       "      <th>Daniel Hilton</th>\n",
       "      <th>Debra Henley</th>\n",
       "      <td>146832</td>\n",
       "      <td>65000</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Koepp Ltd</th>\n",
       "      <th>Wendy Yule</th>\n",
       "      <th>Fred Anderson</th>\n",
       "      <td>729833</td>\n",
       "      <td>35000</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kulas Inc</th>\n",
       "      <th>Daniel Hilton</th>\n",
       "      <th>Debra Henley</th>\n",
       "      <td>218895</td>\n",
       "      <td>25000</td>\n",
       "      <td>1.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Purdy-Kunde</th>\n",
       "      <th>Cedric Moss</th>\n",
       "      <th>Fred Anderson</th>\n",
       "      <td>163416</td>\n",
       "      <td>30000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Stokes LLC</th>\n",
       "      <th>Cedric Moss</th>\n",
       "      <th>Fred Anderson</th>\n",
       "      <td>239344</td>\n",
       "      <td>7500</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Trantow-Barrows</th>\n",
       "      <th>Craig Booker</th>\n",
       "      <th>Debra Henley</th>\n",
       "      <td>714466</td>\n",
       "      <td>15000</td>\n",
       "      <td>1.333333</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                          Account   Price  \\\n",
       "Name                         Rep           Manager                          \n",
       "Barton LLC                   John Smith    Debra Henley    740150   35000   \n",
       "Fritsch, Russel and Anderson Craig Booker  Debra Henley    737550   35000   \n",
       "Herman LLC                   Cedric Moss   Fred Anderson   141962   65000   \n",
       "Jerde-Hilpert                John Smith    Debra Henley    412290    5000   \n",
       "Kassulke, Ondricka and Metz  Wendy Yule    Fred Anderson   307599    7000   \n",
       "Keeling LLC                  Wendy Yule    Fred Anderson   688981  100000   \n",
       "Kiehn-Spinka                 Daniel Hilton Debra Henley    146832   65000   \n",
       "Koepp Ltd                    Wendy Yule    Fred Anderson   729833   35000   \n",
       "Kulas Inc                    Daniel Hilton Debra Henley    218895   25000   \n",
       "Purdy-Kunde                  Cedric Moss   Fred Anderson   163416   30000   \n",
       "Stokes LLC                   Cedric Moss   Fred Anderson   239344    7500   \n",
       "Trantow-Barrows              Craig Booker  Debra Henley    714466   15000   \n",
       "\n",
       "                                                          Quantity  \n",
       "Name                         Rep           Manager                  \n",
       "Barton LLC                   John Smith    Debra Henley   1.000000  \n",
       "Fritsch, Russel and Anderson Craig Booker  Debra Henley   1.000000  \n",
       "Herman LLC                   Cedric Moss   Fred Anderson  2.000000  \n",
       "Jerde-Hilpert                John Smith    Debra Henley   2.000000  \n",
       "Kassulke, Ondricka and Metz  Wendy Yule    Fred Anderson  3.000000  \n",
       "Keeling LLC                  Wendy Yule    Fred Anderson  5.000000  \n",
       "Kiehn-Spinka                 Daniel Hilton Debra Henley   2.000000  \n",
       "Koepp Ltd                    Wendy Yule    Fred Anderson  2.000000  \n",
       "Kulas Inc                    Daniel Hilton Debra Henley   1.500000  \n",
       "Purdy-Kunde                  Cedric Moss   Fred Anderson  1.000000  \n",
       "Stokes LLC                   Cedric Moss   Fred Anderson  1.000000  \n",
       "Trantow-Barrows              Craig Booker  Debra Henley   1.333333  "
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(df,index=[\"Name\",\"Rep\",\"Manager\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "这样很有趣但并不是特别有用。我们可能想做的是通过将“Manager”和“Rep”设置为索引来查看结果。要实现它其实很简单，只需要改变索引就可以。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "x = pd.pivot_table(df,index=[\"Manager\",\"Rep\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "x.to_excel('D://pivot.xlsx','Sheet1')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\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",
       "      <th>Cedric Moss</th>\n",
       "      <th>Fred Anderson</th>\n",
       "      <td>196016.5</td>\n",
       "      <td>27500.000000</td>\n",
       "      <td>1.250000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Craig Booker</th>\n",
       "      <th>Debra Henley</th>\n",
       "      <td>720237.0</td>\n",
       "      <td>20000.000000</td>\n",
       "      <td>1.250000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Daniel Hilton</th>\n",
       "      <th>Debra Henley</th>\n",
       "      <td>194874.0</td>\n",
       "      <td>38333.333333</td>\n",
       "      <td>1.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>John Smith</th>\n",
       "      <th>Debra Henley</th>\n",
       "      <td>576220.0</td>\n",
       "      <td>20000.000000</td>\n",
       "      <td>1.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wendy Yule</th>\n",
       "      <th>Fred Anderson</th>\n",
       "      <td>614061.5</td>\n",
       "      <td>44250.000000</td>\n",
       "      <td>3.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              Account         Price  Quantity\n",
       "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": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# index 的顺序很重要，这里应该把Manager放在Rep前面\n",
    "pd.pivot_table(df,index=[\"Rep\",\"Manager\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "“Account”和“Quantity”列对于我们来说并没什么用。所以，通过利用“values”域显式地定义我们关心的列，就可以实现移除那些不关心的列。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>Price</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Manager</th>\n",
       "      <th>Rep</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Debra Henley</th>\n",
       "      <th>Craig Booker</th>\n",
       "      <td>20000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Daniel Hilton</th>\n",
       "      <td>38333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>John Smith</th>\n",
       "      <td>20000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Fred Anderson</th>\n",
       "      <th>Cedric Moss</th>\n",
       "      <td>27500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wendy Yule</th>\n",
       "      <td>44250</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             Price\n",
       "Manager       Rep                 \n",
       "Debra Henley  Craig Booker   20000\n",
       "              Daniel Hilton  38333\n",
       "              John Smith     20000\n",
       "Fred Anderson Cedric Moss    27500\n",
       "              Wendy Yule     44250"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(df,index=[\"Manager\",\"Rep\"],values=[\"Price\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "“Price”列会自动计算数据的平均值，但是我们也可以对该列元素进行计数或求和。要添加这些功能，使用aggfunc和np.sum就很容易实现。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>Price</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Manager</th>\n",
       "      <th>Rep</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Debra Henley</th>\n",
       "      <th>Craig Booker</th>\n",
       "      <td>80000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Daniel Hilton</th>\n",
       "      <td>115000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>John Smith</th>\n",
       "      <td>40000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Fred Anderson</th>\n",
       "      <th>Cedric Moss</th>\n",
       "      <td>110000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wendy Yule</th>\n",
       "      <td>177000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              Price\n",
       "Manager       Rep                  \n",
       "Debra Henley  Craig Booker    80000\n",
       "              Daniel Hilton  115000\n",
       "              John Smith      40000\n",
       "Fred Anderson Cedric Moss    110000\n",
       "              Wendy Yule     177000"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(df,index=[\"Manager\",\"Rep\"],values=[\"Price\"],aggfunc=np.sum)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "aggfunc可以包含很多函数，下面就让我们尝试一种方法，即使用numpy中的函数mean和len来进行计数。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\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",
       "      <th rowspan=\"3\" valign=\"top\">Debra Henley</th>\n",
       "      <th>Craig Booker</th>\n",
       "      <td>20000</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Daniel Hilton</th>\n",
       "      <td>38333</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>John Smith</th>\n",
       "      <td>20000</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Fred Anderson</th>\n",
       "      <th>Cedric Moss</th>\n",
       "      <td>27500</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wendy Yule</th>\n",
       "      <td>44250</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     4\n",
       "              Daniel Hilton  38333     3\n",
       "              John Smith     20000     2\n",
       "Fred Anderson Cedric Moss    27500     4\n",
       "              Wendy Yule     44250     4"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(df,index=[\"Manager\",\"Rep\"],values=[\"Price\"],aggfunc=[np.mean,len])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "如果需要看来自两个column的数据呢？例如price* quantity = total\n",
    "\n",
    "一种方法应该是在生成透视表之前先计算出来作为一个新列。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df['total'] = df['Quantity'] * df['Price']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\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",
       "      <th>total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>714466</td>\n",
       "      <td>Trantow-Barrows</td>\n",
       "      <td>Craig Booker</td>\n",
       "      <td>Debra Henley</td>\n",
       "      <td>CPU</td>\n",
       "      <td>1</td>\n",
       "      <td>30000</td>\n",
       "      <td>presented</td>\n",
       "      <td>30000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>714466</td>\n",
       "      <td>Trantow-Barrows</td>\n",
       "      <td>Craig Booker</td>\n",
       "      <td>Debra Henley</td>\n",
       "      <td>Software</td>\n",
       "      <td>1</td>\n",
       "      <td>10000</td>\n",
       "      <td>presented</td>\n",
       "      <td>10000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>714466</td>\n",
       "      <td>Trantow-Barrows</td>\n",
       "      <td>Craig Booker</td>\n",
       "      <td>Debra Henley</td>\n",
       "      <td>Maintenance</td>\n",
       "      <td>2</td>\n",
       "      <td>5000</td>\n",
       "      <td>pending</td>\n",
       "      <td>10000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>737550</td>\n",
       "      <td>Fritsch, Russel and Anderson</td>\n",
       "      <td>Craig Booker</td>\n",
       "      <td>Debra Henley</td>\n",
       "      <td>CPU</td>\n",
       "      <td>1</td>\n",
       "      <td>35000</td>\n",
       "      <td>declined</td>\n",
       "      <td>35000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>146832</td>\n",
       "      <td>Kiehn-Spinka</td>\n",
       "      <td>Daniel Hilton</td>\n",
       "      <td>Debra Henley</td>\n",
       "      <td>CPU</td>\n",
       "      <td>2</td>\n",
       "      <td>65000</td>\n",
       "      <td>won</td>\n",
       "      <td>130000</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   total  \n",
       "0          CPU         1  30000  presented   30000  \n",
       "1     Software         1  10000  presented   10000  \n",
       "2  Maintenance         2   5000    pending   10000  \n",
       "3          CPU         1  35000   declined   35000  \n",
       "4          CPU         2  65000        won  130000  "
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def get_total(row):\n",
    "    print row\n",
    "    return row.Quantity * row.Price"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "pd.pivot_table(df,index=[\"Manager\",\"Rep\"],values=[\"Price\", \"Quantity\"],aggfunc=[get_total])\n",
    "\n",
    "# 发现并不可行，传入get_total的单column"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "我认为pivot_table中一个令人困惑的地方是“columns（列）”和“values（值）”的使用。记住，变量“columns（列）”是可选的，它提供一种额外的方法来**分割**你所关心的实际值。然而，聚合函数aggfunc最后是被应用到了变量“values”中你所列举的项目上。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\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\">total</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>Product</th>\n",
       "      <th>CPU</th>\n",
       "      <th>Maintenance</th>\n",
       "      <th>Monitor</th>\n",
       "      <th>Software</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Manager</th>\n",
       "      <th>Rep</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Debra Henley</th>\n",
       "      <th>Craig Booker</th>\n",
       "      <td>65000</td>\n",
       "      <td>10000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Daniel Hilton</th>\n",
       "      <td>210000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>John Smith</th>\n",
       "      <td>35000</td>\n",
       "      <td>10000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Fred Anderson</th>\n",
       "      <th>Cedric Moss</th>\n",
       "      <td>160000</td>\n",
       "      <td>5000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wendy Yule</th>\n",
       "      <td>630000</td>\n",
       "      <td>21000</td>\n",
       "      <td>10000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                sum                             \n",
       "                              total                             \n",
       "Product                         CPU Maintenance Monitor Software\n",
       "Manager       Rep                                               \n",
       "Debra Henley  Craig Booker    65000       10000     NaN    10000\n",
       "              Daniel Hilton  210000         NaN     NaN    10000\n",
       "              John Smith      35000       10000     NaN      NaN\n",
       "Fred Anderson Cedric Moss    160000        5000     NaN    10000\n",
       "              Wendy Yule     630000       21000   10000      NaN"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(df,index=[\"Manager\",\"Rep\"],values=[\"total\"],\n",
    "               columns=[\"Product\"],aggfunc=[np.sum])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "然而，非数值（NaN）有点令人分心。如果想移除它们，我们可以使用“fill_value”将其设置为0。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\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\">total</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>Product</th>\n",
       "      <th>CPU</th>\n",
       "      <th>Maintenance</th>\n",
       "      <th>Monitor</th>\n",
       "      <th>Software</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Manager</th>\n",
       "      <th>Rep</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Debra Henley</th>\n",
       "      <th>Craig Booker</th>\n",
       "      <td>65000</td>\n",
       "      <td>10000</td>\n",
       "      <td>0</td>\n",
       "      <td>10000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Daniel Hilton</th>\n",
       "      <td>210000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>10000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>John Smith</th>\n",
       "      <td>35000</td>\n",
       "      <td>10000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Fred Anderson</th>\n",
       "      <th>Cedric Moss</th>\n",
       "      <td>160000</td>\n",
       "      <td>5000</td>\n",
       "      <td>0</td>\n",
       "      <td>10000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wendy Yule</th>\n",
       "      <td>630000</td>\n",
       "      <td>21000</td>\n",
       "      <td>10000</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                sum                             \n",
       "                              total                             \n",
       "Product                         CPU Maintenance Monitor Software\n",
       "Manager       Rep                                               \n",
       "Debra Henley  Craig Booker    65000       10000       0    10000\n",
       "              Daniel Hilton  210000           0       0    10000\n",
       "              John Smith      35000       10000       0        0\n",
       "Fred Anderson Cedric Moss    160000        5000       0    10000\n",
       "              Wendy Yule     630000       21000   10000        0"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(df,index=[\"Manager\",\"Rep\"],values=[\"total\"],\n",
    "               columns=[\"Product\"],aggfunc=[np.sum],fill_value=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "有趣的是，你可以将几个项目设置为索引来获得不同的可视化表示。下面的代码中，我们将“Product”从“columns”中移除，并添加到“index”变量中。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\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>Quantity</th>\n",
       "      <th>total</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Manager</th>\n",
       "      <th>Rep</th>\n",
       "      <th>Product</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"7\" valign=\"top\">Debra Henley</th>\n",
       "      <th rowspan=\"3\" valign=\"top\">Craig Booker</th>\n",
       "      <th>CPU</th>\n",
       "      <td>2</td>\n",
       "      <td>65000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Maintenance</th>\n",
       "      <td>2</td>\n",
       "      <td>10000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Software</th>\n",
       "      <td>1</td>\n",
       "      <td>10000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Daniel Hilton</th>\n",
       "      <th>CPU</th>\n",
       "      <td>4</td>\n",
       "      <td>210000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Software</th>\n",
       "      <td>1</td>\n",
       "      <td>10000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">John Smith</th>\n",
       "      <th>CPU</th>\n",
       "      <td>1</td>\n",
       "      <td>35000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Maintenance</th>\n",
       "      <td>2</td>\n",
       "      <td>10000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"6\" valign=\"top\">Fred Anderson</th>\n",
       "      <th rowspan=\"3\" valign=\"top\">Cedric Moss</th>\n",
       "      <th>CPU</th>\n",
       "      <td>3</td>\n",
       "      <td>160000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Maintenance</th>\n",
       "      <td>1</td>\n",
       "      <td>5000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Software</th>\n",
       "      <td>1</td>\n",
       "      <td>10000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Wendy Yule</th>\n",
       "      <th>CPU</th>\n",
       "      <td>7</td>\n",
       "      <td>630000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Maintenance</th>\n",
       "      <td>3</td>\n",
       "      <td>21000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Monitor</th>\n",
       "      <td>2</td>\n",
       "      <td>10000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             sum        \n",
       "                                        Quantity   total\n",
       "Manager       Rep           Product                     \n",
       "Debra Henley  Craig Booker  CPU                2   65000\n",
       "                            Maintenance        2   10000\n",
       "                            Software           1   10000\n",
       "              Daniel Hilton CPU                4  210000\n",
       "                            Software           1   10000\n",
       "              John Smith    CPU                1   35000\n",
       "                            Maintenance        2   10000\n",
       "Fred Anderson Cedric Moss   CPU                3  160000\n",
       "                            Maintenance        1    5000\n",
       "                            Software           1   10000\n",
       "              Wendy Yule    CPU                7  630000\n",
       "                            Maintenance        3   21000\n",
       "                            Monitor            2   10000"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(df,index=[\"Manager\",\"Rep\", \"Product\"],values=[\"total\", \"Quantity\"],\n",
    "               aggfunc=[np.sum],fill_value=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "如果我想查看一些总和数据呢？“margins=True”就可以为我们实现这种功能。\n",
    "\n",
    "自动在最底下生成total row"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\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>Quantity</th>\n",
       "      <th>total</th>\n",
       "      <th>Quantity</th>\n",
       "      <th>total</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Manager</th>\n",
       "      <th>Rep</th>\n",
       "      <th>Product</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"7\" valign=\"top\">Debra Henley</th>\n",
       "      <th rowspan=\"3\" valign=\"top\">Craig Booker</th>\n",
       "      <th>CPU</th>\n",
       "      <td>2</td>\n",
       "      <td>65000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>32500.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Maintenance</th>\n",
       "      <td>2</td>\n",
       "      <td>10000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>10000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Software</th>\n",
       "      <td>1</td>\n",
       "      <td>10000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>10000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Daniel Hilton</th>\n",
       "      <th>CPU</th>\n",
       "      <td>4</td>\n",
       "      <td>210000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>105000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Software</th>\n",
       "      <td>1</td>\n",
       "      <td>10000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>10000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">John Smith</th>\n",
       "      <th>CPU</th>\n",
       "      <td>1</td>\n",
       "      <td>35000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>35000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Maintenance</th>\n",
       "      <td>2</td>\n",
       "      <td>10000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>10000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"6\" valign=\"top\">Fred Anderson</th>\n",
       "      <th rowspan=\"3\" valign=\"top\">Cedric Moss</th>\n",
       "      <th>CPU</th>\n",
       "      <td>3</td>\n",
       "      <td>160000</td>\n",
       "      <td>1.500000</td>\n",
       "      <td>80000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Maintenance</th>\n",
       "      <td>1</td>\n",
       "      <td>5000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>5000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Software</th>\n",
       "      <td>1</td>\n",
       "      <td>10000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>10000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Wendy Yule</th>\n",
       "      <th>CPU</th>\n",
       "      <td>7</td>\n",
       "      <td>630000</td>\n",
       "      <td>3.500000</td>\n",
       "      <td>315000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Maintenance</th>\n",
       "      <td>3</td>\n",
       "      <td>21000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>21000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Monitor</th>\n",
       "      <td>2</td>\n",
       "      <td>10000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>10000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>All</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <td>30</td>\n",
       "      <td>1186000</td>\n",
       "      <td>1.764706</td>\n",
       "      <td>69764.705882</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             sum               mean  \\\n",
       "                                        Quantity    total  Quantity   \n",
       "Manager       Rep           Product                                   \n",
       "Debra Henley  Craig Booker  CPU                2    65000  1.000000   \n",
       "                            Maintenance        2    10000  2.000000   \n",
       "                            Software           1    10000  1.000000   \n",
       "              Daniel Hilton CPU                4   210000  2.000000   \n",
       "                            Software           1    10000  1.000000   \n",
       "              John Smith    CPU                1    35000  1.000000   \n",
       "                            Maintenance        2    10000  2.000000   \n",
       "Fred Anderson Cedric Moss   CPU                3   160000  1.500000   \n",
       "                            Maintenance        1     5000  1.000000   \n",
       "                            Software           1    10000  1.000000   \n",
       "              Wendy Yule    CPU                7   630000  3.500000   \n",
       "                            Maintenance        3    21000  3.000000   \n",
       "                            Monitor            2    10000  2.000000   \n",
       "All                                           30  1186000  1.764706   \n",
       "\n",
       "                                                        \n",
       "                                                 total  \n",
       "Manager       Rep           Product                     \n",
       "Debra Henley  Craig Booker  CPU           32500.000000  \n",
       "                            Maintenance   10000.000000  \n",
       "                            Software      10000.000000  \n",
       "              Daniel Hilton CPU          105000.000000  \n",
       "                            Software      10000.000000  \n",
       "              John Smith    CPU           35000.000000  \n",
       "                            Maintenance   10000.000000  \n",
       "Fred Anderson Cedric Moss   CPU           80000.000000  \n",
       "                            Maintenance    5000.000000  \n",
       "                            Software      10000.000000  \n",
       "              Wendy Yule    CPU          315000.000000  \n",
       "                            Maintenance   21000.000000  \n",
       "                            Monitor       10000.000000  \n",
       "All                                       69764.705882  "
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(df,index=[\"Manager\",\"Rep\",\"Product\"],\n",
    "               values=[\"total\",\"Quantity\"],\n",
    "               aggfunc=[np.sum,np.mean],fill_value=0,margins=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "查看Manager 及 相关状态的情况："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\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>total</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Manager</th>\n",
       "      <th>Status</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">Debra Henley</th>\n",
       "      <th>won</th>\n",
       "      <td>130000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>pending</th>\n",
       "      <td>100000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>presented</th>\n",
       "      <td>50000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>declined</th>\n",
       "      <td>70000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">Fred Anderson</th>\n",
       "      <th>won</th>\n",
       "      <td>651000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>pending</th>\n",
       "      <td>5000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>presented</th>\n",
       "      <td>50000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>declined</th>\n",
       "      <td>130000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>All</th>\n",
       "      <th></th>\n",
       "      <td>1186000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             sum\n",
       "                           total\n",
       "Manager       Status            \n",
       "Debra Henley  won         130000\n",
       "              pending     100000\n",
       "              presented    50000\n",
       "              declined     70000\n",
       "Fred Anderson won         651000\n",
       "              pending       5000\n",
       "              presented    50000\n",
       "              declined    130000\n",
       "All                      1186000"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(df,index=[\"Manager\",\"Status\"],values=[\"total\"],\n",
    "               aggfunc=[np.sum],fill_value=0,margins=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "一个很方便的特性是，为了对你选择的**不同值执行不同的函数**，你可以向aggfunc传递一个字典。不过，这样做有一个副作用，那就是必须将标签做的更加简洁才行。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"4\" halign=\"left\">total</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",
       "      <th rowspan=\"4\" valign=\"top\">Debra Henley</th>\n",
       "      <th>won</th>\n",
       "      <td>130000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>pending</th>\n",
       "      <td>80000</td>\n",
       "      <td>20000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>presented</th>\n",
       "      <td>30000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>20000</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>declined</th>\n",
       "      <td>70000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">Fred Anderson</th>\n",
       "      <th>won</th>\n",
       "      <td>630000</td>\n",
       "      <td>21000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>pending</th>\n",
       "      <td>0</td>\n",
       "      <td>5000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>presented</th>\n",
       "      <td>30000</td>\n",
       "      <td>0</td>\n",
       "      <td>10000</td>\n",
       "      <td>10000</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>declined</th>\n",
       "      <td>130000</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": [
       "                          total                              Quantity  \\\n",
       "Product                     CPU Maintenance Monitor Software      CPU   \n",
       "Manager       Status                                                    \n",
       "Debra Henley  won        130000           0       0        0        1   \n",
       "              pending     80000       20000       0        0        1   \n",
       "              presented   30000           0       0    20000        1   \n",
       "              declined    70000           0       0        0        2   \n",
       "Fred Anderson won        630000       21000       0        0        2   \n",
       "              pending         0        5000       0        0        0   \n",
       "              presented   30000           0   10000    10000        1   \n",
       "              declined   130000           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": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(df,index=[\"Manager\",\"Status\"],columns=[\"Product\"],values=[\"Quantity\",\"total\"],\n",
    "               aggfunc={\"Quantity\":len,\"total\":np.sum},fill_value=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can provide a list of aggfunctions to apply to each value too:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"8\" halign=\"left\">total</th>\n",
       "      <th colspan=\"4\" halign=\"left\">Quantity</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"4\" halign=\"left\">mean</th>\n",
       "      <th colspan=\"4\" halign=\"left\">sum</th>\n",
       "      <th colspan=\"4\" halign=\"left\">len</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>Product</th>\n",
       "      <th>CPU</th>\n",
       "      <th>Maintenance</th>\n",
       "      <th>Monitor</th>\n",
       "      <th>Software</th>\n",
       "      <th>CPU</th>\n",
       "      <th>Maintenance</th>\n",
       "      <th>Monitor</th>\n",
       "      <th>Software</th>\n",
       "      <th>CPU</th>\n",
       "      <th>Maintenance</th>\n",
       "      <th>Monitor</th>\n",
       "      <th>Software</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Manager</th>\n",
       "      <th>Status</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">Debra Henley</th>\n",
       "      <th>won</th>\n",
       "      <td>130000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>130000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>pending</th>\n",
       "      <td>80000</td>\n",
       "      <td>10000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>80000</td>\n",
       "      <td>20000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>presented</th>\n",
       "      <td>30000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>10000</td>\n",
       "      <td>30000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>20000</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>declined</th>\n",
       "      <td>35000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>70000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">Fred Anderson</th>\n",
       "      <th>won</th>\n",
       "      <td>315000</td>\n",
       "      <td>21000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>630000</td>\n",
       "      <td>21000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>pending</th>\n",
       "      <td>0</td>\n",
       "      <td>5000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>presented</th>\n",
       "      <td>30000</td>\n",
       "      <td>0</td>\n",
       "      <td>10000</td>\n",
       "      <td>10000</td>\n",
       "      <td>30000</td>\n",
       "      <td>0</td>\n",
       "      <td>10000</td>\n",
       "      <td>10000</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>declined</th>\n",
       "      <td>130000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>130000</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": [
       "                          total                                       \\\n",
       "                           mean                                  sum   \n",
       "Product                     CPU Maintenance Monitor Software     CPU   \n",
       "Manager       Status                                                   \n",
       "Debra Henley  won        130000           0       0        0  130000   \n",
       "              pending     80000       10000       0        0   80000   \n",
       "              presented   30000           0       0    10000   30000   \n",
       "              declined    35000           0       0        0   70000   \n",
       "Fred Anderson won        315000       21000       0        0  630000   \n",
       "              pending         0        5000       0        0       0   \n",
       "              presented   30000           0   10000    10000   30000   \n",
       "              declined   130000           0       0        0  130000   \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         20000       0        0        1           2   \n",
       "              presented           0       0    20000        1           0   \n",
       "              declined            0       0        0        2           0   \n",
       "Fred Anderson won             21000       0        0        2           1   \n",
       "              pending          5000       0        0        0           1   \n",
       "              presented           0   10000    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": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "table = pd.pivot_table(df,index=[\"Manager\",\"Status\"],columns=[\"Product\"],values=[\"Quantity\",\"total\"],\n",
    "               aggfunc={\"Quantity\":len,\"total\":[np.sum,np.mean]},fill_value=0)\n",
    "table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "也许，同一时间将这些东西全都放在一起会有点令人望而生畏，但是一旦你开始处理这些数据，并一步一步地添加新项目，你将能够领略到它是如何工作的。我一般的经验法则是，**一旦你使用多个“grouby”，那么你需要评估此时使用透视表是否是一种好的选择。**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here is a short example. Using the data above, I could use groupby like this:\n",
    "df.groupby(['Name','Rep','Manager']).mean()\n",
    "\n",
    "Which gives the same output as:\n",
    "pd.pivot_table(df,index=[\"Name\",\"Rep\",\"Manager\"])\n",
    "\n",
    "The pivot table is essentially a **wrapper** around groupby's that also allows you to do more (as I show in subsequent steps).\n",
    "\n",
    "In this case, I personally think it is easier to do my data manipulations using pivot_table than trying to extend my groupby syntax.\n",
    "\n",
    "There is nothing inherently wrong with groupby. I just notice that sometimes I start two or three groupby's and suddenly realize I've manually created a pivot table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\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>Quantity</th>\n",
       "      <th>Price</th>\n",
       "      <th>total</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Manager</th>\n",
       "      <th>Rep</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Debra Henley</th>\n",
       "      <th>Craig Booker</th>\n",
       "      <td>720237.0</td>\n",
       "      <td>1.250000</td>\n",
       "      <td>20000.000000</td>\n",
       "      <td>21250.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Daniel Hilton</th>\n",
       "      <td>194874.0</td>\n",
       "      <td>1.666667</td>\n",
       "      <td>38333.333333</td>\n",
       "      <td>73333.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>John Smith</th>\n",
       "      <td>576220.0</td>\n",
       "      <td>1.500000</td>\n",
       "      <td>20000.000000</td>\n",
       "      <td>22500.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Fred Anderson</th>\n",
       "      <th>Cedric Moss</th>\n",
       "      <td>196016.5</td>\n",
       "      <td>1.250000</td>\n",
       "      <td>27500.000000</td>\n",
       "      <td>43750.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wendy Yule</th>\n",
       "      <td>614061.5</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>44250.000000</td>\n",
       "      <td>165250.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              Account  Quantity         Price          total\n",
       "Manager       Rep                                                           \n",
       "Debra Henley  Craig Booker   720237.0  1.250000  20000.000000   21250.000000\n",
       "              Daniel Hilton  194874.0  1.666667  38333.333333   73333.333333\n",
       "              John Smith     576220.0  1.500000  20000.000000   22500.000000\n",
       "Fred Anderson Cedric Moss    196016.5  1.250000  27500.000000   43750.000000\n",
       "              Wendy Yule     614061.5  3.000000  44250.000000  165250.000000"
      ]
     },
     "execution_count": 102,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby(['Manager', 'Rep']).mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "关于透视表的过滤：\n",
    "\n",
    "可以直接使用标准的过滤函数对df进行过滤，（因为生成的透视表也是一个标准的df）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pandas.core.frame.DataFrame"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(table)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(8, 12)"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "table.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "130000"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 注意上面的shape是不包括各种index和column name的；\n",
    "# 因此[0,0]的cell对应的是左上角130000这个值\n",
    "table.iat[0,0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"8\" halign=\"left\">total</th>\n",
       "      <th colspan=\"4\" halign=\"left\">Quantity</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"4\" halign=\"left\">mean</th>\n",
       "      <th colspan=\"4\" halign=\"left\">sum</th>\n",
       "      <th colspan=\"4\" halign=\"left\">len</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>Product</th>\n",
       "      <th>CPU</th>\n",
       "      <th>Maintenance</th>\n",
       "      <th>Monitor</th>\n",
       "      <th>Software</th>\n",
       "      <th>CPU</th>\n",
       "      <th>Maintenance</th>\n",
       "      <th>Monitor</th>\n",
       "      <th>Software</th>\n",
       "      <th>CPU</th>\n",
       "      <th>Maintenance</th>\n",
       "      <th>Monitor</th>\n",
       "      <th>Software</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Manager</th>\n",
       "      <th>Status</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Debra Henley</th>\n",
       "      <th>won</th>\n",
       "      <td>130000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>130000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>pending</th>\n",
       "      <td>80000</td>\n",
       "      <td>10000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>80000</td>\n",
       "      <td>20000</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",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       total                                                   \\\n",
       "                        mean                                  sum               \n",
       "Product                  CPU Maintenance Monitor Software     CPU Maintenance   \n",
       "Manager      Status                                                             \n",
       "Debra Henley won      130000           0       0        0  130000           0   \n",
       "             pending   80000       10000       0        0   80000       20000   \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  "
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "table.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "table.query('Manager == [\"Debra Henley\"]')\n",
    "\n",
    "# 当前版本有bug：http://stackoverflow.com/questions/30445044/having-trouble-with-multiple-groupby-with-a-variable-and-a-category-binned-da"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "table.query('Status == [\"pending\",\"won\"]')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "test = pd.DataFrame(dict(A = np.random.rand(3),\n",
    "                        B = pd.Series(['a', 'a', 'b'],dtype='category'),\n",
    "                        C = pd.Series(['x', 'y', 'z'])))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.662318</td>\n",
       "      <td>a</td>\n",
       "      <td>x</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.513908</td>\n",
       "      <td>a</td>\n",
       "      <td>y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.907551</td>\n",
       "      <td>b</td>\n",
       "      <td>z</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          A  B  C\n",
       "0  0.662318  a  x\n",
       "1  0.513908  a  y\n",
       "2  0.907551  b  z"
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.886707</td>\n",
       "      <td>a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.834855</td>\n",
       "      <td>a</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          A  B\n",
       "0  0.886707  a\n",
       "1  0.834855  a"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test.query('B==\"a\"')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "test_pt = pd.pivot_table(test,index=[\"B\", \"C\"], fill_value=0, aggfunc=[np.sum, len])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>sum</th>\n",
       "      <th>len</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>A</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">a</th>\n",
       "      <th>x</th>\n",
       "      <td>0.662318</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>y</th>\n",
       "      <td>0.513908</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>z</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">b</th>\n",
       "      <th>x</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>y</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>z</th>\n",
       "      <td>0.907551</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          sum len\n",
       "            A   A\n",
       "B C              \n",
       "a x  0.662318   1\n",
       "  y  0.513908   1\n",
       "  z  0.000000   0\n",
       "b x  0.000000   0\n",
       "  y  0.000000   0\n",
       "  z  0.907551   1"
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_pt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>sum</th>\n",
       "      <th>len</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>A</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">a</th>\n",
       "      <th>x</th>\n",
       "      <td>0.662318</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>y</th>\n",
       "      <td>0.513908</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>z</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          sum len\n",
       "            A   A\n",
       "B C              \n",
       "a x  0.662318   1\n",
       "  y  0.513908   1\n",
       "  z  0.000000   0"
      ]
     },
     "execution_count": 99,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_pt.query(\"B == ['a']\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 关于透视表的备忘\n",
    "<img src='img/pivot-table-datasheet.png'>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "#### 透视表实战\n",
    "需求按银行日期细分交易信息\n",
    "\n",
    "data: data/pivot_table.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(17637, 5)"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('data/pivot_table.csv')\n",
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "txn_id               int64\n",
       "bank_id              int64\n",
       "currency_amount    float64\n",
       "txn_status           int64\n",
       "update_time          int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>txn_id</th>\n",
       "      <th>bank_id</th>\n",
       "      <th>currency_amount</th>\n",
       "      <th>txn_status</th>\n",
       "      <th>update_time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>10000</td>\n",
       "      <td>100</td>\n",
       "      <td>0</td>\n",
       "      <td>1444118813</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>10000</td>\n",
       "      <td>100</td>\n",
       "      <td>0</td>\n",
       "      <td>1444118843</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>10000</td>\n",
       "      <td>100</td>\n",
       "      <td>0</td>\n",
       "      <td>1444118994</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>10006</td>\n",
       "      <td>100</td>\n",
       "      <td>0</td>\n",
       "      <td>1444121142</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>10001</td>\n",
       "      <td>100</td>\n",
       "      <td>0</td>\n",
       "      <td>1444121176</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   txn_id  bank_id  currency_amount  txn_status  update_time\n",
       "0       1    10000              100           0   1444118813\n",
       "1       2    10000              100           0   1444118843\n",
       "2       3    10000              100           0   1444118994\n",
       "3       4    10006              100           0   1444121142\n",
       "4       5    10001              100           0   1444121176"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# timestamp to datetime！\n",
    "# 使用pd.to_datetime()\n",
    "# http://pandas.pydata.org/pandas-docs/version/0.17.0/generated/pandas.to_datetime.html\n",
    "# timestamp 是秒为单位\n",
    "df['update_time'] = pd.to_datetime(df.update_time, unit='s')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>txn_id</th>\n",
       "      <th>bank_id</th>\n",
       "      <th>currency_amount</th>\n",
       "      <th>txn_status</th>\n",
       "      <th>update_time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>10000</td>\n",
       "      <td>100</td>\n",
       "      <td>0</td>\n",
       "      <td>2015-10-06 08:06:53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>10000</td>\n",
       "      <td>100</td>\n",
       "      <td>0</td>\n",
       "      <td>2015-10-06 08:07:23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>10000</td>\n",
       "      <td>100</td>\n",
       "      <td>0</td>\n",
       "      <td>2015-10-06 08:09:54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>10006</td>\n",
       "      <td>100</td>\n",
       "      <td>0</td>\n",
       "      <td>2015-10-06 08:45:42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>10001</td>\n",
       "      <td>100</td>\n",
       "      <td>0</td>\n",
       "      <td>2015-10-06 08:46:16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   txn_id  bank_id  currency_amount  txn_status         update_time\n",
       "0       1    10000              100           0 2015-10-06 08:06:53\n",
       "1       2    10000              100           0 2015-10-06 08:07:23\n",
       "2       3    10000              100           0 2015-10-06 08:09:54\n",
       "3       4    10006              100           0 2015-10-06 08:45:42\n",
       "4       5    10001              100           0 2015-10-06 08:46:16"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"3\" halign=\"left\">update_time</th>\n",
       "      <th colspan=\"3\" halign=\"left\">txn_id</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>bank_id</th>\n",
       "      <th>10000</th>\n",
       "      <th>10001</th>\n",
       "      <th>10006</th>\n",
       "      <th>10000</th>\n",
       "      <th>10001</th>\n",
       "      <th>10006</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>update_time</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>2015-10-06</th>\n",
       "      <td>9</td>\n",
       "      <td>11</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>11</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-10-07</th>\n",
       "      <td>3</td>\n",
       "      <td>10</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>10</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-10-08</th>\n",
       "      <td>4</td>\n",
       "      <td>9</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>9</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-10-09</th>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-10-12</th>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>10</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-10-14</th>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-10-15</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-10-16</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-10-20</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-10-21</th>\n",
       "      <td>5</td>\n",
       "      <td>7</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>7</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-10-22</th>\n",
       "      <td>12</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-10-26</th>\n",
       "      <td>16</td>\n",
       "      <td>32</td>\n",
       "      <td>16</td>\n",
       "      <td>16</td>\n",
       "      <td>32</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-10-27</th>\n",
       "      <td>184</td>\n",
       "      <td>399</td>\n",
       "      <td>205</td>\n",
       "      <td>184</td>\n",
       "      <td>399</td>\n",
       "      <td>205</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-10-28</th>\n",
       "      <td>218</td>\n",
       "      <td>362</td>\n",
       "      <td>212</td>\n",
       "      <td>218</td>\n",
       "      <td>362</td>\n",
       "      <td>212</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-10-29</th>\n",
       "      <td>214</td>\n",
       "      <td>287</td>\n",
       "      <td>185</td>\n",
       "      <td>214</td>\n",
       "      <td>287</td>\n",
       "      <td>185</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-10-30</th>\n",
       "      <td>161</td>\n",
       "      <td>315</td>\n",
       "      <td>253</td>\n",
       "      <td>161</td>\n",
       "      <td>315</td>\n",
       "      <td>253</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-10-31</th>\n",
       "      <td>188</td>\n",
       "      <td>379</td>\n",
       "      <td>213</td>\n",
       "      <td>188</td>\n",
       "      <td>379</td>\n",
       "      <td>213</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-11-01</th>\n",
       "      <td>201</td>\n",
       "      <td>342</td>\n",
       "      <td>200</td>\n",
       "      <td>201</td>\n",
       "      <td>342</td>\n",
       "      <td>200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-11-02</th>\n",
       "      <td>133</td>\n",
       "      <td>291</td>\n",
       "      <td>128</td>\n",
       "      <td>133</td>\n",
       "      <td>291</td>\n",
       "      <td>128</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-11-03</th>\n",
       "      <td>97</td>\n",
       "      <td>231</td>\n",
       "      <td>137</td>\n",
       "      <td>97</td>\n",
       "      <td>231</td>\n",
       "      <td>137</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-11-04</th>\n",
       "      <td>114</td>\n",
       "      <td>173</td>\n",
       "      <td>134</td>\n",
       "      <td>114</td>\n",
       "      <td>173</td>\n",
       "      <td>134</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-11-05</th>\n",
       "      <td>194</td>\n",
       "      <td>353</td>\n",
       "      <td>227</td>\n",
       "      <td>194</td>\n",
       "      <td>353</td>\n",
       "      <td>227</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-11-06</th>\n",
       "      <td>148</td>\n",
       "      <td>272</td>\n",
       "      <td>166</td>\n",
       "      <td>148</td>\n",
       "      <td>272</td>\n",
       "      <td>166</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-11-07</th>\n",
       "      <td>100</td>\n",
       "      <td>204</td>\n",
       "      <td>116</td>\n",
       "      <td>100</td>\n",
       "      <td>204</td>\n",
       "      <td>116</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-11-08</th>\n",
       "      <td>149</td>\n",
       "      <td>289</td>\n",
       "      <td>176</td>\n",
       "      <td>149</td>\n",
       "      <td>289</td>\n",
       "      <td>176</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-11-09</th>\n",
       "      <td>133</td>\n",
       "      <td>318</td>\n",
       "      <td>214</td>\n",
       "      <td>133</td>\n",
       "      <td>318</td>\n",
       "      <td>214</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-11-10</th>\n",
       "      <td>214</td>\n",
       "      <td>300</td>\n",
       "      <td>253</td>\n",
       "      <td>214</td>\n",
       "      <td>300</td>\n",
       "      <td>253</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-11-11</th>\n",
       "      <td>329</td>\n",
       "      <td>472</td>\n",
       "      <td>269</td>\n",
       "      <td>329</td>\n",
       "      <td>472</td>\n",
       "      <td>269</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-11-12</th>\n",
       "      <td>196</td>\n",
       "      <td>312</td>\n",
       "      <td>167</td>\n",
       "      <td>196</td>\n",
       "      <td>312</td>\n",
       "      <td>167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-11-13</th>\n",
       "      <td>246</td>\n",
       "      <td>416</td>\n",
       "      <td>196</td>\n",
       "      <td>246</td>\n",
       "      <td>416</td>\n",
       "      <td>196</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-11-14</th>\n",
       "      <td>192</td>\n",
       "      <td>428</td>\n",
       "      <td>217</td>\n",
       "      <td>192</td>\n",
       "      <td>428</td>\n",
       "      <td>217</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-11-15</th>\n",
       "      <td>268</td>\n",
       "      <td>624</td>\n",
       "      <td>319</td>\n",
       "      <td>268</td>\n",
       "      <td>624</td>\n",
       "      <td>319</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-11-16</th>\n",
       "      <td>262</td>\n",
       "      <td>540</td>\n",
       "      <td>292</td>\n",
       "      <td>262</td>\n",
       "      <td>540</td>\n",
       "      <td>292</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-11-17</th>\n",
       "      <td>440</td>\n",
       "      <td>657</td>\n",
       "      <td>398</td>\n",
       "      <td>440</td>\n",
       "      <td>657</td>\n",
       "      <td>398</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-11-18</th>\n",
       "      <td>100</td>\n",
       "      <td>179</td>\n",
       "      <td>115</td>\n",
       "      <td>100</td>\n",
       "      <td>179</td>\n",
       "      <td>115</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            update_time             txn_id            \n",
       "bank_id           10000 10001 10006  10000 10001 10006\n",
       "update_time                                           \n",
       "2015-10-06            9    11     9      9    11     9\n",
       "2015-10-07            3    10     6      3    10     6\n",
       "2015-10-08            4     9     3      4     9     3\n",
       "2015-10-09            5     6   NaN      5     6   NaN\n",
       "2015-10-12            3     4    10      3     4    10\n",
       "2015-10-14          NaN     3   NaN    NaN     3   NaN\n",
       "2015-10-15            1     1     1      1     1     1\n",
       "2015-10-16            3     3     4      3     3     4\n",
       "2015-10-20          NaN   NaN    15    NaN   NaN    15\n",
       "2015-10-21            5     7     5      5     7     5\n",
       "2015-10-22           12     4     1     12     4     1\n",
       "2015-10-26           16    32    16     16    32    16\n",
       "2015-10-27          184   399   205    184   399   205\n",
       "2015-10-28          218   362   212    218   362   212\n",
       "2015-10-29          214   287   185    214   287   185\n",
       "2015-10-30          161   315   253    161   315   253\n",
       "2015-10-31          188   379   213    188   379   213\n",
       "2015-11-01          201   342   200    201   342   200\n",
       "2015-11-02          133   291   128    133   291   128\n",
       "2015-11-03           97   231   137     97   231   137\n",
       "2015-11-04          114   173   134    114   173   134\n",
       "2015-11-05          194   353   227    194   353   227\n",
       "2015-11-06          148   272   166    148   272   166\n",
       "2015-11-07          100   204   116    100   204   116\n",
       "2015-11-08          149   289   176    149   289   176\n",
       "2015-11-09          133   318   214    133   318   214\n",
       "2015-11-10          214   300   253    214   300   253\n",
       "2015-11-11          329   472   269    329   472   269\n",
       "2015-11-12          196   312   167    196   312   167\n",
       "2015-11-13          246   416   196    246   416   196\n",
       "2015-11-14          192   428   217    192   428   217\n",
       "2015-11-15          268   624   319    268   624   319\n",
       "2015-11-16          262   540   292    262   540   292\n",
       "2015-11-17          440   657   398    440   657   398\n",
       "2015-11-18          100   179   115    100   179   115"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 这里的index需要以天为单位，因此必须使用group\n",
    "# 官方有记录该用法： http://pandas.pydata.org/pandas-docs/stable/reshaping.html\n",
    "# 另外一点是我们需要计算多少条记录，而不是默认的平均值\n",
    "pd.pivot_table(df,index=pd.Grouper(freq='1D', key='update_time'),values=[\"txn_id\"],\n",
    "               columns=[\"bank_id\"],aggfunc='count')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}