第4章 变形.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 第4章 变形"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>School</th>\n",
       "      <th>Class</th>\n",
       "      <th>ID</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Address</th>\n",
       "      <th>Height</th>\n",
       "      <th>Weight</th>\n",
       "      <th>Math</th>\n",
       "      <th>Physics</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>S_1</td>\n",
       "      <td>C_1</td>\n",
       "      <td>1101</td>\n",
       "      <td>M</td>\n",
       "      <td>street_1</td>\n",
       "      <td>173</td>\n",
       "      <td>63</td>\n",
       "      <td>34.0</td>\n",
       "      <td>A+</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>S_1</td>\n",
       "      <td>C_1</td>\n",
       "      <td>1102</td>\n",
       "      <td>F</td>\n",
       "      <td>street_2</td>\n",
       "      <td>192</td>\n",
       "      <td>73</td>\n",
       "      <td>32.5</td>\n",
       "      <td>B+</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>S_1</td>\n",
       "      <td>C_1</td>\n",
       "      <td>1103</td>\n",
       "      <td>M</td>\n",
       "      <td>street_2</td>\n",
       "      <td>186</td>\n",
       "      <td>82</td>\n",
       "      <td>87.2</td>\n",
       "      <td>B+</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>S_1</td>\n",
       "      <td>C_1</td>\n",
       "      <td>1104</td>\n",
       "      <td>F</td>\n",
       "      <td>street_2</td>\n",
       "      <td>167</td>\n",
       "      <td>81</td>\n",
       "      <td>80.4</td>\n",
       "      <td>B-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>S_1</td>\n",
       "      <td>C_1</td>\n",
       "      <td>1105</td>\n",
       "      <td>F</td>\n",
       "      <td>street_4</td>\n",
       "      <td>159</td>\n",
       "      <td>64</td>\n",
       "      <td>84.8</td>\n",
       "      <td>B+</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  School Class    ID Gender   Address  Height  Weight  Math Physics\n",
       "0    S_1   C_1  1101      M  street_1     173      63  34.0      A+\n",
       "1    S_1   C_1  1102      F  street_2     192      73  32.5      B+\n",
       "2    S_1   C_1  1103      M  street_2     186      82  87.2      B+\n",
       "3    S_1   C_1  1104      F  street_2     167      81  80.4      B-\n",
       "4    S_1   C_1  1105      F  street_4     159      64  84.8      B+"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "df = pd.read_csv('data/table.csv')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 一、透视表\n",
    "### 1. pivot\n",
    "#### 一般状态下，数据在DataFrame会以压缩（stacked）状态存放，例如上面的Gender，两个类别被叠在一列中，pivot函数可将某一列作为新的cols："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>Gender</th>\n",
       "      <th>F</th>\n",
       "      <th>M</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1101</th>\n",
       "      <td>NaN</td>\n",
       "      <td>173.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1102</th>\n",
       "      <td>192.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1103</th>\n",
       "      <td>NaN</td>\n",
       "      <td>186.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1104</th>\n",
       "      <td>167.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1105</th>\n",
       "      <td>159.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Gender      F      M\n",
       "ID                  \n",
       "1101      NaN  173.0\n",
       "1102    192.0    NaN\n",
       "1103      NaN  186.0\n",
       "1104    167.0    NaN\n",
       "1105    159.0    NaN"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.pivot(index='ID',columns='Gender',values='Height').head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 然而pivot函数具有很强的局限性，除了功能上较少之外，还不允许values中出现重复的行列索引对（pair），例如下面的语句就会报错："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "#df.pivot(index='School',columns='Gender',values='Height').head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 因此，更多的时候会选择使用强大的pivot_table函数\n",
    "### 2. pivot_table\n",
    "#### 首先，再现上面的操作："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>Gender</th>\n",
       "      <th>F</th>\n",
       "      <th>M</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1101</th>\n",
       "      <td>NaN</td>\n",
       "      <td>173.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1102</th>\n",
       "      <td>192.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1103</th>\n",
       "      <td>NaN</td>\n",
       "      <td>186.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1104</th>\n",
       "      <td>167.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1105</th>\n",
       "      <td>159.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Gender      F      M\n",
       "ID                  \n",
       "1101      NaN  173.0\n",
       "1102    192.0    NaN\n",
       "1103      NaN  186.0\n",
       "1104    167.0    NaN\n",
       "1105    159.0    NaN"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(df,index='ID',columns='Gender',values='Height').head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 由于功能更多，速度上自然是比不上原来的pivot函数："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2.28 ms ± 74.8 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)\n",
      "9.77 ms ± 498 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)\n"
     ]
    }
   ],
   "source": [
    "%timeit df.pivot(index='ID',columns='Gender',values='Height')\n",
    "%timeit pd.pivot_table(df,index='ID',columns='Gender',values='Height')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Pandas中提供了各种选项，下面介绍常用参数：\n",
    "#### ① aggfunc：对组内进行聚合统计，可传入各类函数，默认为'mean'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">mean</th>\n",
       "      <th colspan=\"2\" halign=\"left\">sum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Gender</th>\n",
       "      <th>F</th>\n",
       "      <th>M</th>\n",
       "      <th>F</th>\n",
       "      <th>M</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>School</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>S_1</th>\n",
       "      <td>173.125000</td>\n",
       "      <td>178.714286</td>\n",
       "      <td>1385</td>\n",
       "      <td>1251</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S_2</th>\n",
       "      <td>173.727273</td>\n",
       "      <td>172.000000</td>\n",
       "      <td>1911</td>\n",
       "      <td>1548</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              mean               sum      \n",
       "Gender           F           M     F     M\n",
       "School                                    \n",
       "S_1     173.125000  178.714286  1385  1251\n",
       "S_2     173.727273  172.000000  1911  1548"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(df,index='School',columns='Gender',values='Height',aggfunc=['mean','sum']).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### ② margins：汇总边际状态"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"3\" halign=\"left\">mean</th>\n",
       "      <th colspan=\"3\" halign=\"left\">sum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Gender</th>\n",
       "      <th>F</th>\n",
       "      <th>M</th>\n",
       "      <th>All</th>\n",
       "      <th>F</th>\n",
       "      <th>M</th>\n",
       "      <th>All</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>School</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>S_1</th>\n",
       "      <td>173.125000</td>\n",
       "      <td>178.714286</td>\n",
       "      <td>175.733333</td>\n",
       "      <td>1385</td>\n",
       "      <td>1251</td>\n",
       "      <td>2636</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S_2</th>\n",
       "      <td>173.727273</td>\n",
       "      <td>172.000000</td>\n",
       "      <td>172.950000</td>\n",
       "      <td>1911</td>\n",
       "      <td>1548</td>\n",
       "      <td>3459</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>All</th>\n",
       "      <td>173.473684</td>\n",
       "      <td>174.937500</td>\n",
       "      <td>174.142857</td>\n",
       "      <td>3296</td>\n",
       "      <td>2799</td>\n",
       "      <td>6095</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              mean                           sum            \n",
       "Gender           F           M         All     F     M   All\n",
       "School                                                      \n",
       "S_1     173.125000  178.714286  175.733333  1385  1251  2636\n",
       "S_2     173.727273  172.000000  172.950000  1911  1548  3459\n",
       "All     173.473684  174.937500  174.142857  3296  2799  6095"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(df,index='School',columns='Gender',values='Height',aggfunc=['mean','sum'],margins=True).head()\n",
    "#margins_name可以设置名字，默认为'All'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### ③ 行、列、值都可以为多级"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"10\" halign=\"left\">Height</th>\n",
       "      <th>...</th>\n",
       "      <th colspan=\"10\" halign=\"left\">Weight</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>Gender</th>\n",
       "      <th colspan=\"6\" halign=\"left\">F</th>\n",
       "      <th colspan=\"4\" halign=\"left\">M</th>\n",
       "      <th>...</th>\n",
       "      <th colspan=\"4\" halign=\"left\">F</th>\n",
       "      <th colspan=\"6\" halign=\"left\">M</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>Address</th>\n",
       "      <th>street_1</th>\n",
       "      <th>street_2</th>\n",
       "      <th>street_4</th>\n",
       "      <th>street_5</th>\n",
       "      <th>street_6</th>\n",
       "      <th>street_7</th>\n",
       "      <th>street_1</th>\n",
       "      <th>street_2</th>\n",
       "      <th>street_4</th>\n",
       "      <th>street_5</th>\n",
       "      <th>...</th>\n",
       "      <th>street_4</th>\n",
       "      <th>street_5</th>\n",
       "      <th>street_6</th>\n",
       "      <th>street_7</th>\n",
       "      <th>street_1</th>\n",
       "      <th>street_2</th>\n",
       "      <th>street_4</th>\n",
       "      <th>street_5</th>\n",
       "      <th>street_6</th>\n",
       "      <th>street_7</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>School</th>\n",
       "      <th>Class</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",
       "      <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=\"3\" valign=\"top\">S_1</th>\n",
       "      <th>C_1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>179.5</td>\n",
       "      <td>159.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>173.0</td>\n",
       "      <td>186.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>64.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>63.0</td>\n",
       "      <td>82.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>176.0</td>\n",
       "      <td>162.0</td>\n",
       "      <td>167.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>188.0</td>\n",
       "      <td>...</td>\n",
       "      <td>94.0</td>\n",
       "      <td>63.0</td>\n",
       "      <td>63.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>68.0</td>\n",
       "      <td>53.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_3</th>\n",
       "      <td>175.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>187.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>195.0</td>\n",
       "      <td>161.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>69.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>70.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>82.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">S_2</th>\n",
       "      <th>C_1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>159.0</td>\n",
       "      <td>161.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>163.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>97.0</td>\n",
       "      <td>61.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>71.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>84.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>188.5</td>\n",
       "      <td>175.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>155.0</td>\n",
       "      <td>193.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>76.5</td>\n",
       "      <td>74.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>91.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>157.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>164.0</td>\n",
       "      <td>190.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>187.0</td>\n",
       "      <td>171.0</td>\n",
       "      <td>...</td>\n",
       "      <td>78.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>81.0</td>\n",
       "      <td>99.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>73.0</td>\n",
       "      <td>88.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C_4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>176.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>175.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>57.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>82.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>7 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               Height                                                        \\\n",
       "Gender              F                                                     M   \n",
       "Address      street_1 street_2 street_4 street_5 street_6 street_7 street_1   \n",
       "School Class                                                                  \n",
       "S_1    C_1        NaN    179.5    159.0      NaN      NaN      NaN    173.0   \n",
       "       C_2        NaN      NaN    176.0    162.0    167.0      NaN      NaN   \n",
       "       C_3      175.0      NaN      NaN    187.0      NaN      NaN      NaN   \n",
       "S_2    C_1        NaN      NaN      NaN    159.0    161.0      NaN      NaN   \n",
       "       C_2        NaN      NaN      NaN      NaN      NaN    188.5    175.0   \n",
       "       C_3        NaN      NaN    157.0      NaN    164.0    190.0      NaN   \n",
       "       C_4        NaN    176.0      NaN      NaN    175.5      NaN      NaN   \n",
       "\n",
       "                                         ...   Weight                    \\\n",
       "Gender                                   ...        F                     \n",
       "Address      street_2 street_4 street_5  ... street_4 street_5 street_6   \n",
       "School Class                             ...                              \n",
       "S_1    C_1      186.0      NaN      NaN  ...     64.0      NaN      NaN   \n",
       "       C_2        NaN      NaN    188.0  ...     94.0     63.0     63.0   \n",
       "       C_3      195.0    161.0      NaN  ...      NaN     69.0      NaN   \n",
       "S_2    C_1        NaN    163.5      NaN  ...      NaN     97.0     61.0   \n",
       "       C_2        NaN    155.0    193.0  ...      NaN      NaN      NaN   \n",
       "       C_3        NaN    187.0    171.0  ...     78.0      NaN     81.0   \n",
       "       C_4        NaN      NaN      NaN  ...      NaN      NaN     57.0   \n",
       "\n",
       "                                                                             \n",
       "Gender                       M                                               \n",
       "Address      street_7 street_1 street_2 street_4 street_5 street_6 street_7  \n",
       "School Class                                                                 \n",
       "S_1    C_1        NaN     63.0     82.0      NaN      NaN      NaN      NaN  \n",
       "       C_2        NaN      NaN      NaN      NaN     68.0     53.0      NaN  \n",
       "       C_3        NaN      NaN     70.0     68.0      NaN      NaN     82.0  \n",
       "S_2    C_1        NaN      NaN      NaN     71.0      NaN      NaN     84.0  \n",
       "       C_2       76.5     74.0      NaN     91.0    100.0      NaN      NaN  \n",
       "       C_3       99.0      NaN      NaN     73.0     88.0      NaN      NaN  \n",
       "       C_4        NaN      NaN      NaN      NaN      NaN      NaN     82.0  \n",
       "\n",
       "[7 rows x 24 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(df,index=['School','Class'],\n",
    "               columns=['Gender','Address'],\n",
    "               values=['Height','Weight'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3. crosstab（交叉表）\n",
    "#### 交叉表是一种特殊的透视表，典型的用途如分组统计，如现在想要统计关于街道和性别分组的频数："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>Gender</th>\n",
       "      <th>F</th>\n",
       "      <th>M</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Address</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>street_1</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>street_2</th>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>street_4</th>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>street_5</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>street_6</th>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>street_7</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Gender    F  M\n",
       "Address       \n",
       "street_1  1  2\n",
       "street_2  4  2\n",
       "street_4  3  5\n",
       "street_5  3  3\n",
       "street_6  5  1\n",
       "street_7  3  3"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.crosstab(index=df['Address'],columns=df['Gender'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 交叉表的功能也很强大（但目前还不支持多级分组），下面说明一些重要参数：\n",
    "#### ① values和aggfunc：分组对某些数据进行聚合操作，这两个参数必须成对出现"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>Gender</th>\n",
       "      <th>F</th>\n",
       "      <th>M</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Address</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>street_1</th>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>street_2</th>\n",
       "      <td>10</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>street_4</th>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>street_5</th>\n",
       "      <td>10</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>street_6</th>\n",
       "      <td>9</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>street_7</th>\n",
       "      <td>8</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Gender     F  M\n",
       "Address        \n",
       "street_1   6  4\n",
       "street_2  10  5\n",
       "street_4   6  2\n",
       "street_5  10  8\n",
       "street_6   9  4\n",
       "street_7   8  4"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.crosstab(index=df['Address'],columns=df['Gender'],\n",
    "            values=np.random.randint(1,20,df.shape[0]),aggfunc='min')\n",
    "#默认参数等于如下方法：\n",
    "#pd.crosstab(index=df['Address'],columns=df['Gender'],values=1,aggfunc='count')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### ② 除了边际参数margins外，还引入了normalize参数，可选'all','index','columns'参数值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>Gender</th>\n",
       "      <th>F</th>\n",
       "      <th>M</th>\n",
       "      <th>All</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Address</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>street_1</th>\n",
       "      <td>0.028571</td>\n",
       "      <td>0.057143</td>\n",
       "      <td>0.085714</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>street_2</th>\n",
       "      <td>0.114286</td>\n",
       "      <td>0.057143</td>\n",
       "      <td>0.171429</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>street_4</th>\n",
       "      <td>0.085714</td>\n",
       "      <td>0.142857</td>\n",
       "      <td>0.228571</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>street_5</th>\n",
       "      <td>0.085714</td>\n",
       "      <td>0.085714</td>\n",
       "      <td>0.171429</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>street_6</th>\n",
       "      <td>0.142857</td>\n",
       "      <td>0.028571</td>\n",
       "      <td>0.171429</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>street_7</th>\n",
       "      <td>0.085714</td>\n",
       "      <td>0.085714</td>\n",
       "      <td>0.171429</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>All</th>\n",
       "      <td>0.542857</td>\n",
       "      <td>0.457143</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Gender           F         M       All\n",
       "Address                               \n",
       "street_1  0.028571  0.057143  0.085714\n",
       "street_2  0.114286  0.057143  0.171429\n",
       "street_4  0.085714  0.142857  0.228571\n",
       "street_5  0.085714  0.085714  0.171429\n",
       "street_6  0.142857  0.028571  0.171429\n",
       "street_7  0.085714  0.085714  0.171429\n",
       "All       0.542857  0.457143  1.000000"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.crosstab(index=df['Address'],columns=df['Gender'],normalize='all',margins=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 二、其他变形方法\n",
    "### 1. melt\n",
    "#### melt函数可以认为是pivot函数的逆操作，将unstacked状态的数据，压缩成stacked，使“宽”的DataFrame变“窄”"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead 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>Gender</th>\n",
       "      <th>Math</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1101</td>\n",
       "      <td>M</td>\n",
       "      <td>34.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1102</td>\n",
       "      <td>F</td>\n",
       "      <td>32.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1103</td>\n",
       "      <td>M</td>\n",
       "      <td>87.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1104</td>\n",
       "      <td>F</td>\n",
       "      <td>80.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1105</td>\n",
       "      <td>F</td>\n",
       "      <td>84.8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     ID Gender  Math\n",
       "0  1101      M  34.0\n",
       "1  1102      F  32.5\n",
       "2  1103      M  87.2\n",
       "3  1104      F  80.4\n",
       "4  1105      F  84.8"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_m = df[['ID','Gender','Math']]\n",
    "df_m.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>Gender</th>\n",
       "      <th>F</th>\n",
       "      <th>M</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1101</th>\n",
       "      <td>NaN</td>\n",
       "      <td>34.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1102</th>\n",
       "      <td>32.5</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1103</th>\n",
       "      <td>NaN</td>\n",
       "      <td>87.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1104</th>\n",
       "      <td>80.4</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1105</th>\n",
       "      <td>84.8</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Gender     F     M\n",
       "ID                \n",
       "1101     NaN  34.0\n",
       "1102    32.5   NaN\n",
       "1103     NaN  87.2\n",
       "1104    80.4   NaN\n",
       "1105    84.8   NaN"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.pivot(index='ID',columns='Gender',values='Math').head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### melt函数中的id_vars表示需要保留的列，value_vars表示需要stack的一组列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pivoted = df.pivot(index='ID',columns='Gender',values='Math')\n",
    "result = pivoted.reset_index().melt(id_vars=['ID'],value_vars=['F','M'],value_name='Math')\\\n",
    "                     .dropna().set_index('ID').sort_index()\n",
    "#检验是否与展开前的df相同，可以分别将这些链式方法的中间步骤展开，看看是什么结果\n",
    "result.equals(df_m.set_index('ID'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. 压缩与展开\n",
    "#### （1）stack：这是最基础的变形函数，总共只有两个参数：level和dropna"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">Height</th>\n",
       "      <th colspan=\"2\" halign=\"left\">Weight</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>Gender</th>\n",
       "      <th>F</th>\n",
       "      <th>M</th>\n",
       "      <th>F</th>\n",
       "      <th>M</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Class</th>\n",
       "      <th>ID</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\">C_1</th>\n",
       "      <th>1101</th>\n",
       "      <td>NaN</td>\n",
       "      <td>173.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>63.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1102</th>\n",
       "      <td>192.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>73.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">C_2</th>\n",
       "      <th>1201</th>\n",
       "      <td>NaN</td>\n",
       "      <td>188.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>68.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1202</th>\n",
       "      <td>176.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>94.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">C_3</th>\n",
       "      <th>1301</th>\n",
       "      <td>NaN</td>\n",
       "      <td>161.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>68.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1302</th>\n",
       "      <td>175.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>57.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">C_4</th>\n",
       "      <th>2401</th>\n",
       "      <td>192.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>62.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2402</th>\n",
       "      <td>NaN</td>\n",
       "      <td>166.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>82.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           Height        Weight      \n",
       "Gender          F      M      F     M\n",
       "Class ID                             \n",
       "C_1   1101    NaN  173.0    NaN  63.0\n",
       "      1102  192.0    NaN   73.0   NaN\n",
       "C_2   1201    NaN  188.0    NaN  68.0\n",
       "      1202  176.0    NaN   94.0   NaN\n",
       "C_3   1301    NaN  161.0    NaN  68.0\n",
       "      1302  175.0    NaN   57.0   NaN\n",
       "C_4   2401  192.0    NaN   62.0   NaN\n",
       "      2402    NaN  166.0    NaN  82.0"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_s = pd.pivot_table(df,index=['Class','ID'],columns='Gender',values=['Height','Weight'])\n",
    "df_s.groupby('Class').head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>Height</th>\n",
       "      <th>Weight</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Class</th>\n",
       "      <th>ID</th>\n",
       "      <th>Gender</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">C_1</th>\n",
       "      <th>1101</th>\n",
       "      <th>M</th>\n",
       "      <td>173.0</td>\n",
       "      <td>63.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1102</th>\n",
       "      <th>F</th>\n",
       "      <td>192.0</td>\n",
       "      <td>73.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">C_2</th>\n",
       "      <th>1201</th>\n",
       "      <th>M</th>\n",
       "      <td>188.0</td>\n",
       "      <td>68.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1202</th>\n",
       "      <th>F</th>\n",
       "      <td>176.0</td>\n",
       "      <td>94.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">C_3</th>\n",
       "      <th>1301</th>\n",
       "      <th>M</th>\n",
       "      <td>161.0</td>\n",
       "      <td>68.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1302</th>\n",
       "      <th>F</th>\n",
       "      <td>175.0</td>\n",
       "      <td>57.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">C_4</th>\n",
       "      <th>2401</th>\n",
       "      <th>F</th>\n",
       "      <td>192.0</td>\n",
       "      <td>62.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2402</th>\n",
       "      <th>M</th>\n",
       "      <td>166.0</td>\n",
       "      <td>82.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   Height  Weight\n",
       "Class ID   Gender                \n",
       "C_1   1101 M        173.0    63.0\n",
       "      1102 F        192.0    73.0\n",
       "C_2   1201 M        188.0    68.0\n",
       "      1202 F        176.0    94.0\n",
       "C_3   1301 M        161.0    68.0\n",
       "      1302 F        175.0    57.0\n",
       "C_4   2401 F        192.0    62.0\n",
       "      2402 M        166.0    82.0"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_stacked = df_s.stack()\n",
    "df_stacked.groupby('Class').head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### stack函数可以看做将横向的索引放到纵向，因此功能类似与melt，参数level可指定变化的列索引是哪一层（或哪几层，需要列表）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>Gender</th>\n",
       "      <th>F</th>\n",
       "      <th>M</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Class</th>\n",
       "      <th>ID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">C_1</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">1101</th>\n",
       "      <th>Height</th>\n",
       "      <td>NaN</td>\n",
       "      <td>173.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Weight</th>\n",
       "      <td>NaN</td>\n",
       "      <td>63.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">C_2</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">1201</th>\n",
       "      <th>Height</th>\n",
       "      <td>NaN</td>\n",
       "      <td>188.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Weight</th>\n",
       "      <td>NaN</td>\n",
       "      <td>68.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">C_3</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">1301</th>\n",
       "      <th>Height</th>\n",
       "      <td>NaN</td>\n",
       "      <td>161.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Weight</th>\n",
       "      <td>NaN</td>\n",
       "      <td>68.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">C_4</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">2401</th>\n",
       "      <th>Height</th>\n",
       "      <td>192.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Weight</th>\n",
       "      <td>62.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Gender                 F      M\n",
       "Class ID                       \n",
       "C_1   1101 Height    NaN  173.0\n",
       "           Weight    NaN   63.0\n",
       "C_2   1201 Height    NaN  188.0\n",
       "           Weight    NaN   68.0\n",
       "C_3   1301 Height    NaN  161.0\n",
       "           Weight    NaN   68.0\n",
       "C_4   2401 Height  192.0    NaN\n",
       "           Weight   62.0    NaN"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_stacked = df_s.stack(0)\n",
    "df_stacked.groupby('Class').head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### (2) unstack：stack的逆函数，功能上类似于pivot_table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>Gender</th>\n",
       "      <th>F</th>\n",
       "      <th>M</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Class</th>\n",
       "      <th>ID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">C_1</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">1101</th>\n",
       "      <th>Height</th>\n",
       "      <td>NaN</td>\n",
       "      <td>173.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Weight</th>\n",
       "      <td>NaN</td>\n",
       "      <td>63.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">1102</th>\n",
       "      <th>Height</th>\n",
       "      <td>192.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Weight</th>\n",
       "      <td>73.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1103</th>\n",
       "      <th>Height</th>\n",
       "      <td>NaN</td>\n",
       "      <td>186.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Gender                 F      M\n",
       "Class ID                       \n",
       "C_1   1101 Height    NaN  173.0\n",
       "           Weight    NaN   63.0\n",
       "      1102 Height  192.0    NaN\n",
       "           Weight   73.0    NaN\n",
       "      1103 Height    NaN  186.0"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_stacked.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = df_stacked.unstack().swaplevel(1,0,axis=1).sort_index(axis=1)\n",
    "result.equals(df_s)\n",
    "#同样在unstack中可以指定level参数"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 三、哑变量与因子化\n",
    "### 1. Dummy Variable（哑变量）\n",
    "#### 这里主要介绍get_dummies函数，其功能主要是进行one-hot编码："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Class</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Weight</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>C_1</td>\n",
       "      <td>M</td>\n",
       "      <td>63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>C_1</td>\n",
       "      <td>F</td>\n",
       "      <td>73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>C_1</td>\n",
       "      <td>M</td>\n",
       "      <td>82</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>C_1</td>\n",
       "      <td>F</td>\n",
       "      <td>81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>C_1</td>\n",
       "      <td>F</td>\n",
       "      <td>64</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Class Gender  Weight\n",
       "0   C_1      M      63\n",
       "1   C_1      F      73\n",
       "2   C_1      M      82\n",
       "3   C_1      F      81\n",
       "4   C_1      F      64"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_d = df[['Class','Gender','Weight']]\n",
    "df_d.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 现在希望将上面的表格前两列转化为哑变量，并加入第三列Weight数值："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Class_C_1</th>\n",
       "      <th>Class_C_2</th>\n",
       "      <th>Class_C_3</th>\n",
       "      <th>Class_C_4</th>\n",
       "      <th>Gender_F</th>\n",
       "      <th>Gender_M</th>\n",
       "      <th>Weight</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>82</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>64</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Class_C_1  Class_C_2  Class_C_3  Class_C_4  Gender_F  Gender_M  Weight\n",
       "0          1          0          0          0         0         1      63\n",
       "1          1          0          0          0         1         0      73\n",
       "2          1          0          0          0         0         1      82\n",
       "3          1          0          0          0         1         0      81\n",
       "4          1          0          0          0         1         0      64"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.get_dummies(df_d[['Class','Gender']]).join(df_d['Weight']).head()\n",
    "#可选prefix参数添加前缀，prefix_sep添加分隔符"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. factorize方法\n",
    "#### 该方法主要用于自然数编码，并且缺失值会被记做-1，其中sort参数表示是否排序后赋值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([ 1, -1,  0,  2,  1])"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "array(['a', 'b', 'c'], dtype=object)"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "codes, uniques = pd.factorize(['b', None, 'a', 'c', 'b'], sort=True)\n",
    "display(codes)\n",
    "display(uniques)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 四、问题与练习"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1. 问题\n",
    "#### 【问题一】 上面提到了许多变形函数，如melt/crosstab/pivot/pivot_table/stack/unstack函数，请总结它们各自的使用特点。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 【问题二】 变形函数和多级索引是什么关系？哪些变形函数会使得索引维数变化？具体如何变化？\n",
    "#### 【问题三】 请举出一个除了上文提过的关于哑变量方法的例子。\n",
    "#### 【问题四】 使用完stack后立即使用unstack一定能保证变化结果与原始表完全一致吗？\n",
    "#### 【问题五】 透视表中涉及了三个函数，请分别使用它们完成相同的目标（任务自定）并比较哪个速度最快。\n",
    "#### 【问题六】 既然melt起到了stack的功能，为什么再设计stack函数？"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. 练习\n",
    "#### 【练习一】 继续使用上一章的药物数据集："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>YYYY</th>\n",
       "      <th>State</th>\n",
       "      <th>COUNTY</th>\n",
       "      <th>SubstanceName</th>\n",
       "      <th>DrugReports</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2010</td>\n",
       "      <td>VA</td>\n",
       "      <td>ACCOMACK</td>\n",
       "      <td>Propoxyphene</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2010</td>\n",
       "      <td>OH</td>\n",
       "      <td>ADAMS</td>\n",
       "      <td>Morphine</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2010</td>\n",
       "      <td>PA</td>\n",
       "      <td>ADAMS</td>\n",
       "      <td>Methadone</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2010</td>\n",
       "      <td>VA</td>\n",
       "      <td>ALEXANDRIA CITY</td>\n",
       "      <td>Heroin</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2010</td>\n",
       "      <td>PA</td>\n",
       "      <td>ALLEGHENY</td>\n",
       "      <td>Hydromorphone</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   YYYY State           COUNTY  SubstanceName  DrugReports\n",
       "0  2010    VA         ACCOMACK   Propoxyphene            1\n",
       "1  2010    OH            ADAMS       Morphine            9\n",
       "2  2010    PA            ADAMS      Methadone            2\n",
       "3  2010    VA  ALEXANDRIA CITY         Heroin            5\n",
       "4  2010    PA        ALLEGHENY  Hydromorphone            5"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_csv('data/Drugs.csv').head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### (a) 现在请你将数据表转化成如下形态，每行需要显示每种药物在每个地区的10年至17年的变化情况，且前三列需要排序：\n",
    "![avatar](picture/drug_pic.png)\n",
    "#### (b) 现在请将(a)中的结果恢复到原数据表，并通过equal函数检验初始表与新的结果是否一致（返回True）"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 【练习二】 现有一份关于某地区地震情况的数据集，请解决如下问题："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>日期</th>\n",
       "      <th>时间</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>2003.05.20</td>\n",
       "      <td>12:17:44 AM</td>\n",
       "      <td>39.04</td>\n",
       "      <td>40.38</td>\n",
       "      <td>west</td>\n",
       "      <td>0.1</td>\n",
       "      <td>10.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2007.08.01</td>\n",
       "      <td>12:03:08 AM</td>\n",
       "      <td>40.79</td>\n",
       "      <td>30.09</td>\n",
       "      <td>west</td>\n",
       "      <td>0.1</td>\n",
       "      <td>5.2</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1978.05.07</td>\n",
       "      <td>12:41:37 AM</td>\n",
       "      <td>38.58</td>\n",
       "      <td>27.61</td>\n",
       "      <td>south_west</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1997.03.22</td>\n",
       "      <td>12:31:45 AM</td>\n",
       "      <td>39.47</td>\n",
       "      <td>36.44</td>\n",
       "      <td>south_west</td>\n",
       "      <td>0.1</td>\n",
       "      <td>10.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2000.04.02</td>\n",
       "      <td>12:57:38 AM</td>\n",
       "      <td>40.80</td>\n",
       "      <td>30.24</td>\n",
       "      <td>south_west</td>\n",
       "      <td>0.1</td>\n",
       "      <td>7.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           日期           时间     维度     经度          方向   距离    深度   烈度\n",
       "0  2003.05.20  12:17:44 AM  39.04  40.38        west  0.1  10.0  0.0\n",
       "1  2007.08.01  12:03:08 AM  40.79  30.09        west  0.1   5.2  4.0\n",
       "2  1978.05.07  12:41:37 AM  38.58  27.61  south_west  0.1   0.0  0.0\n",
       "3  1997.03.22  12:31:45 AM  39.47  36.44  south_west  0.1  10.0  0.0\n",
       "4  2000.04.02  12:57:38 AM  40.80  30.24  south_west  0.1   7.0  0.0"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_csv('data/Earthquake.csv').head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### (a) 现在请你将数据表转化成如下形态，将方向列展开，并将距离、深度和烈度三个属性压缩：\n",
    "![avatar](picture/earthquake_pic.png)\n",
    "#### (b) 现在请将(a)中的结果恢复到原数据表，并通过equal函数检验初始表与新的结果是否一致（返回True）"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}