Chapter-10/10.4 Pivot Tables and Cross-Tabulation（数据透视表和交叉表）.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 10.4 Pivot Tables and Cross-Tabulation（数据透视表和交叉表）\n",
    "\n",
    "Pivot Tables（数据透视表）是一种常见的数据汇总工具，常见与各种spreadsheet programs（电子表格程序，比如Excel）和一些数据分析软件。它能按一个或多个keys来把数据聚合为表格，能沿着行或列，根据组键来整理数据。\n",
    "\n",
    "数据透视表可以用pandas的groupby来制作，这个本节会进行介绍，除此之外还会有介绍如何利用多层级索引来进行reshape（更改形状）操作。DataFrame有一个pivot_table方法，另外还有一个pandas.pivot_table函数。为了有一个更方便的groupby借口，pivot_table能添加partial totals（部分合计）,也被称作margins(边界)。\n",
    "\n",
    "回到之前提到的tipping数据集，假设我们想要计算一个含有组平均值的表格(a table of group means，这个平均值也是pivot_table默认的聚合类型)，按day和smoker来分组："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "tips = pd.read_csv('../examples/tips.csv')\n",
    "# Add tip percentage of total bill\n",
    "tips['tip_pct'] = tips['tip'] / tips['total_bill']"
   ]
  },
  {
   "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>total_bill</th>\n",
       "      <th>tip</th>\n",
       "      <th>smoker</th>\n",
       "      <th>day</th>\n",
       "      <th>time</th>\n",
       "      <th>size</th>\n",
       "      <th>tip_pct</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>16.99</td>\n",
       "      <td>1.01</td>\n",
       "      <td>No</td>\n",
       "      <td>Sun</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>2</td>\n",
       "      <td>0.059447</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10.34</td>\n",
       "      <td>1.66</td>\n",
       "      <td>No</td>\n",
       "      <td>Sun</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>3</td>\n",
       "      <td>0.160542</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>21.01</td>\n",
       "      <td>3.50</td>\n",
       "      <td>No</td>\n",
       "      <td>Sun</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>3</td>\n",
       "      <td>0.166587</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>23.68</td>\n",
       "      <td>3.31</td>\n",
       "      <td>No</td>\n",
       "      <td>Sun</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>2</td>\n",
       "      <td>0.139780</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>24.59</td>\n",
       "      <td>3.61</td>\n",
       "      <td>No</td>\n",
       "      <td>Sun</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>4</td>\n",
       "      <td>0.146808</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   total_bill   tip smoker  day    time  size   tip_pct\n",
       "0       16.99  1.01     No  Sun  Dinner     2  0.059447\n",
       "1       10.34  1.66     No  Sun  Dinner     3  0.160542\n",
       "2       21.01  3.50     No  Sun  Dinner     3  0.166587\n",
       "3       23.68  3.31     No  Sun  Dinner     2  0.139780\n",
       "4       24.59  3.61     No  Sun  Dinner     4  0.146808"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tips.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>size</th>\n",
       "      <th>tip</th>\n",
       "      <th>tip_pct</th>\n",
       "      <th>total_bill</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>day</th>\n",
       "      <th>smoker</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\">Fri</th>\n",
       "      <th>No</th>\n",
       "      <td>2.250000</td>\n",
       "      <td>2.812500</td>\n",
       "      <td>0.151650</td>\n",
       "      <td>18.420000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Yes</th>\n",
       "      <td>2.066667</td>\n",
       "      <td>2.714000</td>\n",
       "      <td>0.174783</td>\n",
       "      <td>16.813333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Sat</th>\n",
       "      <th>No</th>\n",
       "      <td>2.555556</td>\n",
       "      <td>3.102889</td>\n",
       "      <td>0.158048</td>\n",
       "      <td>19.661778</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Yes</th>\n",
       "      <td>2.476190</td>\n",
       "      <td>2.875476</td>\n",
       "      <td>0.147906</td>\n",
       "      <td>21.276667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Sun</th>\n",
       "      <th>No</th>\n",
       "      <td>2.929825</td>\n",
       "      <td>3.167895</td>\n",
       "      <td>0.160113</td>\n",
       "      <td>20.506667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Yes</th>\n",
       "      <td>2.578947</td>\n",
       "      <td>3.516842</td>\n",
       "      <td>0.187250</td>\n",
       "      <td>24.120000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Thur</th>\n",
       "      <th>No</th>\n",
       "      <td>2.488889</td>\n",
       "      <td>2.673778</td>\n",
       "      <td>0.160298</td>\n",
       "      <td>17.113111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Yes</th>\n",
       "      <td>2.352941</td>\n",
       "      <td>3.030000</td>\n",
       "      <td>0.163863</td>\n",
       "      <td>19.190588</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 size       tip   tip_pct  total_bill\n",
       "day  smoker                                          \n",
       "Fri  No      2.250000  2.812500  0.151650   18.420000\n",
       "     Yes     2.066667  2.714000  0.174783   16.813333\n",
       "Sat  No      2.555556  3.102889  0.158048   19.661778\n",
       "     Yes     2.476190  2.875476  0.147906   21.276667\n",
       "Sun  No      2.929825  3.167895  0.160113   20.506667\n",
       "     Yes     2.578947  3.516842  0.187250   24.120000\n",
       "Thur No      2.488889  2.673778  0.160298   17.113111\n",
       "     Yes     2.352941  3.030000  0.163863   19.190588"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tips.pivot_table(index=['day', 'smoker'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "这个结果也可以通过groupby直接得到。\n",
    "\n",
    "现在假设我们想要按time分组，然后对tip_pct和size进行聚合。我们会把smoker放在列上，而day用于行："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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=\"2\" halign=\"left\">tip_pct</th>\n",
       "      <th colspan=\"2\" halign=\"left\">size</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>smoker</th>\n",
       "      <th>No</th>\n",
       "      <th>Yes</th>\n",
       "      <th>No</th>\n",
       "      <th>Yes</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>time</th>\n",
       "      <th>day</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\">Dinner</th>\n",
       "      <th>Fri</th>\n",
       "      <td>0.139622</td>\n",
       "      <td>0.165347</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>2.222222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sat</th>\n",
       "      <td>0.158048</td>\n",
       "      <td>0.147906</td>\n",
       "      <td>2.555556</td>\n",
       "      <td>2.476190</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sun</th>\n",
       "      <td>0.160113</td>\n",
       "      <td>0.187250</td>\n",
       "      <td>2.929825</td>\n",
       "      <td>2.578947</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Thur</th>\n",
       "      <td>0.159744</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Lunch</th>\n",
       "      <th>Fri</th>\n",
       "      <td>0.187735</td>\n",
       "      <td>0.188937</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>1.833333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Thur</th>\n",
       "      <td>0.160311</td>\n",
       "      <td>0.163863</td>\n",
       "      <td>2.500000</td>\n",
       "      <td>2.352941</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              tip_pct                size          \n",
       "smoker             No       Yes        No       Yes\n",
       "time   day                                         \n",
       "Dinner Fri   0.139622  0.165347  2.000000  2.222222\n",
       "       Sat   0.158048  0.147906  2.555556  2.476190\n",
       "       Sun   0.160113  0.187250  2.929825  2.578947\n",
       "       Thur  0.159744       NaN  2.000000       NaN\n",
       "Lunch  Fri   0.187735  0.188937  3.000000  1.833333\n",
       "       Thur  0.160311  0.163863  2.500000  2.352941"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tips.pivot_table(['tip_pct', 'size'], index=['time', 'day'],\n",
    "                 columns='smoker')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "我们也快成把这个表格加强一下，通过设置margins=True来添加部分合计（partial total）。这么做的话有一个效果，会给行和列各添加All标签，这个All表示的是当前组对于整个数据的统计值："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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=\"3\" halign=\"left\">tip_pct</th>\n",
       "      <th colspan=\"3\" halign=\"left\">size</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>smoker</th>\n",
       "      <th>No</th>\n",
       "      <th>Yes</th>\n",
       "      <th>All</th>\n",
       "      <th>No</th>\n",
       "      <th>Yes</th>\n",
       "      <th>All</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>time</th>\n",
       "      <th>day</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\">Dinner</th>\n",
       "      <th>Fri</th>\n",
       "      <td>0.139622</td>\n",
       "      <td>0.165347</td>\n",
       "      <td>0.158916</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>2.222222</td>\n",
       "      <td>2.166667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sat</th>\n",
       "      <td>0.158048</td>\n",
       "      <td>0.147906</td>\n",
       "      <td>0.153152</td>\n",
       "      <td>2.555556</td>\n",
       "      <td>2.476190</td>\n",
       "      <td>2.517241</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sun</th>\n",
       "      <td>0.160113</td>\n",
       "      <td>0.187250</td>\n",
       "      <td>0.166897</td>\n",
       "      <td>2.929825</td>\n",
       "      <td>2.578947</td>\n",
       "      <td>2.842105</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Thur</th>\n",
       "      <td>0.159744</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.159744</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Lunch</th>\n",
       "      <th>Fri</th>\n",
       "      <td>0.187735</td>\n",
       "      <td>0.188937</td>\n",
       "      <td>0.188765</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>1.833333</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Thur</th>\n",
       "      <td>0.160311</td>\n",
       "      <td>0.163863</td>\n",
       "      <td>0.161301</td>\n",
       "      <td>2.500000</td>\n",
       "      <td>2.352941</td>\n",
       "      <td>2.459016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>All</th>\n",
       "      <th></th>\n",
       "      <td>0.159328</td>\n",
       "      <td>0.163196</td>\n",
       "      <td>0.160803</td>\n",
       "      <td>2.668874</td>\n",
       "      <td>2.408602</td>\n",
       "      <td>2.569672</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              tip_pct                          size                    \n",
       "smoker             No       Yes       All        No       Yes       All\n",
       "time   day                                                             \n",
       "Dinner Fri   0.139622  0.165347  0.158916  2.000000  2.222222  2.166667\n",
       "       Sat   0.158048  0.147906  0.153152  2.555556  2.476190  2.517241\n",
       "       Sun   0.160113  0.187250  0.166897  2.929825  2.578947  2.842105\n",
       "       Thur  0.159744       NaN  0.159744  2.000000       NaN  2.000000\n",
       "Lunch  Fri   0.187735  0.188937  0.188765  3.000000  1.833333  2.000000\n",
       "       Thur  0.160311  0.163863  0.161301  2.500000  2.352941  2.459016\n",
       "All          0.159328  0.163196  0.160803  2.668874  2.408602  2.569672"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tips.pivot_table(['tip_pct', 'size'], index=['time', 'day'],\n",
    "                 columns='smoker', margins=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "这里，对于All列，这一列的值是不考虑吸烟周和非吸烟者的平均值（smoker versus nonsmoker）。对于All行，这一行的值是不考虑任何组中任意两个组的平均值（any of the two levels of grouping）。\n",
    "\n",
    "想要使用不同的聚合函数，传递给aggfunc即可。例如，count或len可以给我们一个关于组大小（group size）的交叉表格："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>day</th>\n",
       "      <th>Fri</th>\n",
       "      <th>Sat</th>\n",
       "      <th>Sun</th>\n",
       "      <th>Thur</th>\n",
       "      <th>All</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>time</th>\n",
       "      <th>smoker</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\">Dinner</th>\n",
       "      <th>No</th>\n",
       "      <td>3.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>57.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>106.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Yes</th>\n",
       "      <td>9.0</td>\n",
       "      <td>42.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Lunch</th>\n",
       "      <th>No</th>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>44.0</td>\n",
       "      <td>45.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Yes</th>\n",
       "      <td>6.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>17.0</td>\n",
       "      <td>23.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>All</th>\n",
       "      <th></th>\n",
       "      <td>19.0</td>\n",
       "      <td>87.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>244.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "day             Fri   Sat   Sun  Thur    All\n",
       "time   smoker                               \n",
       "Dinner No       3.0  45.0  57.0   1.0  106.0\n",
       "       Yes      9.0  42.0  19.0   NaN   70.0\n",
       "Lunch  No       1.0   NaN   NaN  44.0   45.0\n",
       "       Yes      6.0   NaN   NaN  17.0   23.0\n",
       "All            19.0  87.0  76.0  62.0  244.0"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tips.pivot_table('tip_pct', index=['time', 'smoker'], columns='day',\n",
    "                 aggfunc=len, margins=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "如果一些组合是空的（或NA），我们希望直接用fill_value来填充："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>day</th>\n",
       "      <th>Fri</th>\n",
       "      <th>Sat</th>\n",
       "      <th>Sun</th>\n",
       "      <th>Thur</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>time</th>\n",
       "      <th>size</th>\n",
       "      <th>smoker</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"11\" valign=\"top\">Dinner</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">1</th>\n",
       "      <th>No</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.137931</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Yes</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.325733</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">2</th>\n",
       "      <th>No</th>\n",
       "      <td>0.139622</td>\n",
       "      <td>0.162705</td>\n",
       "      <td>0.168859</td>\n",
       "      <td>0.159744</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Yes</th>\n",
       "      <td>0.171297</td>\n",
       "      <td>0.148668</td>\n",
       "      <td>0.207893</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">3</th>\n",
       "      <th>No</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.154661</td>\n",
       "      <td>0.152663</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Yes</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.144995</td>\n",
       "      <td>0.152660</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">4</th>\n",
       "      <th>No</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.150096</td>\n",
       "      <td>0.148143</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Yes</th>\n",
       "      <td>0.117750</td>\n",
       "      <td>0.124515</td>\n",
       "      <td>0.193370</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">5</th>\n",
       "      <th>No</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.206928</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Yes</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.106572</td>\n",
       "      <td>0.065660</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <th>No</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.103799</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"10\" valign=\"top\">Lunch</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">1</th>\n",
       "      <th>No</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.181728</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Yes</th>\n",
       "      <td>0.223776</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">2</th>\n",
       "      <th>No</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.166005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Yes</th>\n",
       "      <td>0.181969</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.158843</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">3</th>\n",
       "      <th>No</th>\n",
       "      <td>0.187735</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.084246</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Yes</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.204952</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">4</th>\n",
       "      <th>No</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.138919</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Yes</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.155410</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <th>No</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.121389</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <th>No</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.173706</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "day                      Fri       Sat       Sun      Thur\n",
       "time   size smoker                                        \n",
       "Dinner 1    No      0.000000  0.137931  0.000000  0.000000\n",
       "            Yes     0.000000  0.325733  0.000000  0.000000\n",
       "       2    No      0.139622  0.162705  0.168859  0.159744\n",
       "            Yes     0.171297  0.148668  0.207893  0.000000\n",
       "       3    No      0.000000  0.154661  0.152663  0.000000\n",
       "            Yes     0.000000  0.144995  0.152660  0.000000\n",
       "       4    No      0.000000  0.150096  0.148143  0.000000\n",
       "            Yes     0.117750  0.124515  0.193370  0.000000\n",
       "       5    No      0.000000  0.000000  0.206928  0.000000\n",
       "            Yes     0.000000  0.106572  0.065660  0.000000\n",
       "       6    No      0.000000  0.000000  0.103799  0.000000\n",
       "Lunch  1    No      0.000000  0.000000  0.000000  0.181728\n",
       "            Yes     0.223776  0.000000  0.000000  0.000000\n",
       "       2    No      0.000000  0.000000  0.000000  0.166005\n",
       "            Yes     0.181969  0.000000  0.000000  0.158843\n",
       "       3    No      0.187735  0.000000  0.000000  0.084246\n",
       "            Yes     0.000000  0.000000  0.000000  0.204952\n",
       "       4    No      0.000000  0.000000  0.000000  0.138919\n",
       "            Yes     0.000000  0.000000  0.000000  0.155410\n",
       "       5    No      0.000000  0.000000  0.000000  0.121389\n",
       "       6    No      0.000000  0.000000  0.000000  0.173706"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tips.pivot_table('tip_pct', index=['time', 'size', 'smoker'],\n",
    "                 columns='day', aggfunc='mean', fill_value=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "下面是关于pivot_table方法的一些选项：\n",
    "\n",
    "![](http://oydgk2hgw.bkt.clouddn.com/pydata-book/doyxv.png)\n",
    "\n",
    "# 1 Cross-Tabulations: Crosstab（交叉表：Crosstab）\n",
    "\n",
    "cross-tabulation（交叉表，简写为crosstab），是数据透视表的一个特殊形式，只计算组频率（group frequencies）。这里有个例子："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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>Handedness</th>\n",
       "      <th>Nationality</th>\n",
       "      <th>Sample</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Right-handed</td>\n",
       "      <td>USA</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Left-handed</td>\n",
       "      <td>Japan</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Right-handed</td>\n",
       "      <td>USA</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Right-handed</td>\n",
       "      <td>Japan</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Left-handed</td>\n",
       "      <td>Japan</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Right-handed</td>\n",
       "      <td>Japan</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Right-handed</td>\n",
       "      <td>USA</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Left-handed</td>\n",
       "      <td>USA</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Right-handed</td>\n",
       "      <td>Japan</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Right-handed</td>\n",
       "      <td>USA</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Handedness Nationality  Sample\n",
       "0  Right-handed         USA       1\n",
       "1   Left-handed       Japan       2\n",
       "2  Right-handed         USA       3\n",
       "3  Right-handed       Japan       4\n",
       "4   Left-handed       Japan       5\n",
       "5  Right-handed       Japan       6\n",
       "6  Right-handed         USA       7\n",
       "7   Left-handed         USA       8\n",
       "8  Right-handed       Japan       9\n",
       "9  Right-handed         USA      10"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = pd.DataFrame({'Sample': np.arange(1, 11),\n",
    "        'Nationality': ['USA', 'Japan', 'USA', 'Japan', 'Japan', 'Japan', 'USA', 'USA', 'Japan', 'USA'],\n",
    "        'Handedness': ['Right-handed', 'Left-handed', 'Right-handed', 'Right-handed', 'Left-handed', 'Right-handed', 'Right-handed', 'Left-handed', 'Right-handed', 'Right-handed']})\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "作为调查分析（survey analysis）的一部分，我们想要按国家和惯用手来进行汇总。我们可以使用pivot_table来做到这点，不过pandas.crosstab函数会更方便一些："
   ]
  },
  {
   "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>Handedness</th>\n",
       "      <th>Left-handed</th>\n",
       "      <th>Right-handed</th>\n",
       "      <th>All</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Nationality</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Japan</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>USA</th>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>All</th>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Handedness   Left-handed  Right-handed  All\n",
       "Nationality                                \n",
       "Japan                  2             3    5\n",
       "USA                    1             4    5\n",
       "All                    3             7   10"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.crosstab(data.Nationality, data.Handedness, margins=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "crosstab的前两个参数可以是数组或Series或由数组组成的列表（a list of array）。对于tips数据，可以这么写："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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>smoker</th>\n",
       "      <th>No</th>\n",
       "      <th>Yes</th>\n",
       "      <th>All</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>time</th>\n",
       "      <th>day</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">Dinner</th>\n",
       "      <th>Fri</th>\n",
       "      <td>3</td>\n",
       "      <td>9</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sat</th>\n",
       "      <td>45</td>\n",
       "      <td>42</td>\n",
       "      <td>87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sun</th>\n",
       "      <td>57</td>\n",
       "      <td>19</td>\n",
       "      <td>76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Thur</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Lunch</th>\n",
       "      <th>Fri</th>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Thur</th>\n",
       "      <td>44</td>\n",
       "      <td>17</td>\n",
       "      <td>61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>All</th>\n",
       "      <th></th>\n",
       "      <td>151</td>\n",
       "      <td>93</td>\n",
       "      <td>244</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "smoker        No  Yes  All\n",
       "time   day                \n",
       "Dinner Fri     3    9   12\n",
       "       Sat    45   42   87\n",
       "       Sun    57   19   76\n",
       "       Thur    1    0    1\n",
       "Lunch  Fri     1    6    7\n",
       "       Thur   44   17   61\n",
       "All          151   93  244"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.crosstab([tips.time, tips.day], tips.smoker, margins=True)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [py35]",
   "language": "python",
   "name": "Python [py35]"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}