doc/chapter5/数据透视.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 数据透视\n",
    "\n",
    "\n",
    "```{admonition} 在线刷题\n",
    ":class: seealso\n",
    "\n",
    "检查 or 强化 `Pandas` 数据分析操作？<a href=\"https://www.heywhale.com/mw/project/6146c0318447b8001769ff20\" target=\"_blank\">👉在线体验「Pandas进阶修炼300题」</a>\n",
    "```\n",
    "\n",
    "```{note} \n",
    "本页面代码可以[在线编辑、执行](../指引/在线执行.md)！\n",
    "```\n",
    "\n",
    "**数据透视** 可以算是利用 Pandas 进行数据分析最核心的操作，在 pandas 中可以通过 `pivot_table` 实现，\n",
    "如果你能熟练使用 `Pandas` 进行数据透视，将会极大提高工作销效率，因为：\n",
    "- 更快的速度，并且可以自动化\n",
    "- 更易理解（看代码你就知道它做了什么）\n",
    "- 易于使用以生成报告或电子邮件\n",
    "- 更灵活，因为您可以定义自定义聚合函数\n",
    "  \n",
    "但其中复杂的参数设置很容易弄混淆，本章第一部分就将通过真实的数据以期望帮助你快速熟悉、使用该函数，在开始之前还是先分享图解来帮助记忆👇\n",
    "```{figure} https://pic.liuzaoqi.com/picgo/202112201848914.png\n",
    ":width: 800px\n",
    ":align: center\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 本页数据说明\n",
    "\n",
    "为了更好的介绍相关操作，本页面使用 **某超市销售数据** 进行展开，你应该对数据字段、数值、类型等相关信息做一个大致了解！"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "tags": [
     "hide-input",
     "thebe-init"
    ]
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>订单ID</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",
       "      <th>利润</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A1000001</td>\n",
       "      <td>2013/1/1</td>\n",
       "      <td>二级</td>\n",
       "      <td>中国</td>\n",
       "      <td>中南</td>\n",
       "      <td>湖南</td>\n",
       "      <td>公司</td>\n",
       "      <td>办公用品</td>\n",
       "      <td>收纳具</td>\n",
       "      <td>Rogers</td>\n",
       "      <td>Rogers 文件车, 单宽度</td>\n",
       "      <td>5</td>\n",
       "      <td>3305</td>\n",
       "      <td>1322</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A1000001</td>\n",
       "      <td>2013/1/1</td>\n",
       "      <td>二级</td>\n",
       "      <td>中国</td>\n",
       "      <td>中南</td>\n",
       "      <td>湖南</td>\n",
       "      <td>公司</td>\n",
       "      <td>家具</td>\n",
       "      <td>桌子</td>\n",
       "      <td>Barricks</td>\n",
       "      <td>Barricks 圆桌, 白色</td>\n",
       "      <td>3</td>\n",
       "      <td>5289</td>\n",
       "      <td>-635</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A1000001</td>\n",
       "      <td>2013/1/1</td>\n",
       "      <td>二级</td>\n",
       "      <td>中国</td>\n",
       "      <td>中南</td>\n",
       "      <td>湖南</td>\n",
       "      <td>公司</td>\n",
       "      <td>技术</td>\n",
       "      <td>电话</td>\n",
       "      <td>诺基亚</td>\n",
       "      <td>诺基亚 智能手机, 整包</td>\n",
       "      <td>3</td>\n",
       "      <td>1725</td>\n",
       "      <td>69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A1000001</td>\n",
       "      <td>2013/1/1</td>\n",
       "      <td>二级</td>\n",
       "      <td>中国</td>\n",
       "      <td>中南</td>\n",
       "      <td>湖南</td>\n",
       "      <td>公司</td>\n",
       "      <td>技术</td>\n",
       "      <td>配件</td>\n",
       "      <td>贝尔金</td>\n",
       "      <td>贝尔金 记忆卡, 实惠</td>\n",
       "      <td>3</td>\n",
       "      <td>1607</td>\n",
       "      <td>611</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A1000003</td>\n",
       "      <td>2013/1/2</td>\n",
       "      <td>二级</td>\n",
       "      <td>中国</td>\n",
       "      <td>华东</td>\n",
       "      <td>福建</td>\n",
       "      <td>消费者</td>\n",
       "      <td>办公用品</td>\n",
       "      <td>收纳具</td>\n",
       "      <td>Rogers</td>\n",
       "      <td>Rogers 盒, 工业</td>\n",
       "      <td>4</td>\n",
       "      <td>456</td>\n",
       "      <td>128</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       订单ID      订单日期 邮寄方式  国家  地区 省/自治区   细分    类别  子类别       制造商  \\\n",
       "0  A1000001  2013/1/1   二级  中国  中南    湖南   公司  办公用品  收纳具    Rogers   \n",
       "1  A1000001  2013/1/1   二级  中国  中南    湖南   公司    家具   桌子  Barricks   \n",
       "2  A1000001  2013/1/1   二级  中国  中南    湖南   公司    技术   电话       诺基亚   \n",
       "3  A1000001  2013/1/1   二级  中国  中南    湖南   公司    技术   配件       贝尔金   \n",
       "4  A1000003  2013/1/2   二级  中国  华东    福建  消费者  办公用品  收纳具    Rogers   \n",
       "\n",
       "              产品名称  数量   销售额    利润  \n",
       "0  Rogers 文件车, 单宽度   5  3305  1322  \n",
       "1  Barricks 圆桌, 白色   3  5289  -635  \n",
       "2     诺基亚 智能手机, 整包   3  1725    69  \n",
       "3      贝尔金 记忆卡, 实惠   3  1607   611  \n",
       "4     Rogers 盒, 工业   4   456   128  "
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "df = pd.read_csv(\"某超市销售数据.csv\",thousands=',')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 默认方法\n",
    "\n",
    "制作各省「平均销售额」的数据透视表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "tags": [
     "output_scroll"
    ]
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>销售额</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>省/自治区</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>上海</th>\n",
       "      <td>1875.712934</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>云南</th>\n",
       "      <td>1863.789030</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>内蒙古</th>\n",
       "      <td>1314.826316</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>北京</th>\n",
       "      <td>1726.857143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>吉林</th>\n",
       "      <td>1701.563567</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>四川</th>\n",
       "      <td>1157.875536</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>天津</th>\n",
       "      <td>1611.206667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>宁夏</th>\n",
       "      <td>1651.680000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>安徽</th>\n",
       "      <td>1477.868251</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>山东</th>\n",
       "      <td>1642.659111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>山西</th>\n",
       "      <td>1751.359184</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>广东</th>\n",
       "      <td>1592.185340</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>广西</th>\n",
       "      <td>1675.061135</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>新疆</th>\n",
       "      <td>1402.384615</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>江苏</th>\n",
       "      <td>1279.898089</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>江西</th>\n",
       "      <td>1966.477876</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>河北</th>\n",
       "      <td>1780.275248</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>河南</th>\n",
       "      <td>1558.400335</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>浙江</th>\n",
       "      <td>1347.277778</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>海南</th>\n",
       "      <td>1721.982759</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>湖北</th>\n",
       "      <td>1324.808442</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>湖南</th>\n",
       "      <td>1603.702222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>甘肃</th>\n",
       "      <td>1602.893443</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>福建</th>\n",
       "      <td>1732.044693</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>西藏</th>\n",
       "      <td>201.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>贵州</th>\n",
       "      <td>1425.978723</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>辽宁</th>\n",
       "      <td>1363.381982</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>重庆</th>\n",
       "      <td>1383.082927</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>陕西</th>\n",
       "      <td>1880.606635</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>青海</th>\n",
       "      <td>2978.954545</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>黑龙江</th>\n",
       "      <td>1800.438503</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               销售额\n",
       "省/自治区             \n",
       "上海     1875.712934\n",
       "云南     1863.789030\n",
       "内蒙古    1314.826316\n",
       "北京     1726.857143\n",
       "吉林     1701.563567\n",
       "四川     1157.875536\n",
       "天津     1611.206667\n",
       "宁夏     1651.680000\n",
       "安徽     1477.868251\n",
       "山东     1642.659111\n",
       "山西     1751.359184\n",
       "广东     1592.185340\n",
       "广西     1675.061135\n",
       "新疆     1402.384615\n",
       "江苏     1279.898089\n",
       "江西     1966.477876\n",
       "河北     1780.275248\n",
       "河南     1558.400335\n",
       "浙江     1347.277778\n",
       "海南     1721.982759\n",
       "湖北     1324.808442\n",
       "湖南     1603.702222\n",
       "甘肃     1602.893443\n",
       "福建     1732.044693\n",
       "西藏      201.000000\n",
       "贵州     1425.978723\n",
       "辽宁     1363.381982\n",
       "重庆     1383.082927\n",
       "陕西     1880.606635\n",
       "青海     2978.954545\n",
       "黑龙江    1800.438503"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(df,values = ['销售额'],index = '省/自治区')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```{admonition} 思考\n",
    ":class: hint\n",
    "\n",
    "用分组如何实现？\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "tags": [
     "hide-cell",
     "output_scroll"
    ]
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "省/自治区\n",
       "上海     1875.712934\n",
       "云南     1863.789030\n",
       "内蒙古    1314.826316\n",
       "北京     1726.857143\n",
       "吉林     1701.563567\n",
       "四川     1157.875536\n",
       "天津     1611.206667\n",
       "宁夏     1651.680000\n",
       "安徽     1477.868251\n",
       "山东     1642.659111\n",
       "山西     1751.359184\n",
       "广东     1592.185340\n",
       "广西     1675.061135\n",
       "新疆     1402.384615\n",
       "江苏     1279.898089\n",
       "江西     1966.477876\n",
       "河北     1780.275248\n",
       "河南     1558.400335\n",
       "浙江     1347.277778\n",
       "海南     1721.982759\n",
       "湖北     1324.808442\n",
       "湖南     1603.702222\n",
       "甘肃     1602.893443\n",
       "福建     1732.044693\n",
       "西藏      201.000000\n",
       "贵州     1425.978723\n",
       "辽宁     1363.381982\n",
       "重庆     1383.082927\n",
       "陕西     1880.606635\n",
       "青海     2978.954545\n",
       "黑龙江    1800.438503\n",
       "Name: 销售额, dtype: float64"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby(\"省/自治区\")['销售额'].mean() "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 指定维度\n",
    "\n",
    "制作各省「销售总额」的数据透视表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "tags": [
     "output_scroll"
    ]
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>销售额</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>省/自治区</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>上海</th>\n",
       "      <td>594601</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>云南</th>\n",
       "      <td>441718</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>内蒙古</th>\n",
       "      <td>249817</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>北京</th>\n",
       "      <td>350552</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>吉林</th>\n",
       "      <td>896724</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>四川</th>\n",
       "      <td>269785</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>天津</th>\n",
       "      <td>483362</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>宁夏</th>\n",
       "      <td>41292</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>安徽</th>\n",
       "      <td>684253</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>山东</th>\n",
       "      <td>1884130</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>山西</th>\n",
       "      <td>429083</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>广东</th>\n",
       "      <td>1520537</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>广西</th>\n",
       "      <td>383589</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>新疆</th>\n",
       "      <td>72924</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>江苏</th>\n",
       "      <td>401888</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>江西</th>\n",
       "      <td>222212</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>河北</th>\n",
       "      <td>899039</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>河南</th>\n",
       "      <td>930365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>浙江</th>\n",
       "      <td>266761</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>海南</th>\n",
       "      <td>99875</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>湖北</th>\n",
       "      <td>408041</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>湖南</th>\n",
       "      <td>721666</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>甘肃</th>\n",
       "      <td>195553</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>福建</th>\n",
       "      <td>620072</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>西藏</th>\n",
       "      <td>201</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>贵州</th>\n",
       "      <td>67021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>辽宁</th>\n",
       "      <td>756677</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>重庆</th>\n",
       "      <td>283532</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>陕西</th>\n",
       "      <td>396808</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>青海</th>\n",
       "      <td>65537</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>黑龙江</th>\n",
       "      <td>1346728</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           销售额\n",
       "省/自治区         \n",
       "上海      594601\n",
       "云南      441718\n",
       "内蒙古     249817\n",
       "北京      350552\n",
       "吉林      896724\n",
       "四川      269785\n",
       "天津      483362\n",
       "宁夏       41292\n",
       "安徽      684253\n",
       "山东     1884130\n",
       "山西      429083\n",
       "广东     1520537\n",
       "广西      383589\n",
       "新疆       72924\n",
       "江苏      401888\n",
       "江西      222212\n",
       "河北      899039\n",
       "河南      930365\n",
       "浙江      266761\n",
       "海南       99875\n",
       "湖北      408041\n",
       "湖南      721666\n",
       "甘肃      195553\n",
       "福建      620072\n",
       "西藏         201\n",
       "贵州       67021\n",
       "辽宁      756677\n",
       "重庆      283532\n",
       "陕西      396808\n",
       "青海       65537\n",
       "黑龙江    1346728"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(df,values = ['销售额'],index = '省/自治区',aggfunc = sum)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 组合计算\n",
    "\n",
    "制作各省「销售总额」与「平均销售额」的数据透视表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": [
     "output_scroll"
    ]
   },
   "outputs": [],
   "source": [
    "pd.pivot_table(df,values = ['销售额'],index = '省/自治区',aggfunc = ['mean',sum])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 组合多列\n",
    "\n",
    "制作各省市「销售总额」与「利润总额」的数据透视表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": [
     "output_scroll"
    ]
   },
   "outputs": [],
   "source": [
    "pd.pivot_table(df,values = ['销售额','利润','数量'],index = '类别',aggfunc = sum)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 组合索引\n",
    "\n",
    "制作「各省市」与「不同类别」产品「销售总额」的数据透视表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": [
     "output_scroll"
    ]
   },
   "outputs": [],
   "source": [
    "pd.pivot_table(df,values = ['销售额'],index = ['省/自治区','类别'],aggfunc = sum)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 多层透视\n",
    "\n",
    "制作各省市「不同类别」产品的「销售总额」透视表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": [
     "output_scroll"
    ]
   },
   "outputs": [],
   "source": [
    "pd.pivot_table(df,values = ['销售额'],index = ['省/自治区'], columns='类别',aggfunc = sum)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 综合使用\n",
    "\n",
    "制作「各省市」、「不同类别」产品「销售量与销售额」的「均值与总和」的数据透视表，并在最后追加一行『合计』"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": [
     "output_scroll"
    ]
   },
   "outputs": [],
   "source": [
    "pd.pivot_table(df,values = ['销售额','数量'],index = ['省/自治区','类别'],aggfunc = ['mean',sum],margins=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 透视筛选\n",
    "\n",
    "在上一题的基础上，查询 **「类别」** 等于 **「办公用品」** 的详情"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {
    "tags": [
     "output_scroll"
    ]
   },
   "outputs": [],
   "source": [
    "table = pd.pivot_table(df,values = ['销售额','数量'],index = ['省/自治区','类别'],aggfunc = ['mean',sum],margins=True)\n",
    "table.query('类别 == [\"办公用品\"]')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 逆透视\n",
    "\n",
    "逆透视就是将宽的表转换为长的表，例如将第 5 题的透视表进行逆透视，其中不需要转换的列为『数量』列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": [
     "output_scroll"
    ]
   },
   "outputs": [],
   "source": [
    "table = pd.pivot_table(df,values = ['销售额','利润','数量'],index = '类别',aggfunc = sum)\n",
    "table.melt(id_vars=['数量'],var_name='分类',value_name='金额')"
   ]
  }
 ],
 "metadata": {
  "celltoolbar": "Tags",
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": false,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {
    "height": "calc(100% - 180px)",
    "left": "10px",
    "top": "150px",
    "width": "376px"
   },
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}