参考答案.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 参考答案"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 第1章：练习一\n",
    "### (a)"
   ]
  },
  {
   "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></th>\n",
       "      <th>Release Date</th>\n",
       "      <th>Season</th>\n",
       "      <th>Episode</th>\n",
       "      <th>Episode Title</th>\n",
       "      <th>Name</th>\n",
       "      <th>Sentence</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2011/4/17</td>\n",
       "      <td>Season 1</td>\n",
       "      <td>Episode 1</td>\n",
       "      <td>Winter is Coming</td>\n",
       "      <td>waymar royce</td>\n",
       "      <td>What do you expect? They're savages. One lot s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2011/4/17</td>\n",
       "      <td>Season 1</td>\n",
       "      <td>Episode 1</td>\n",
       "      <td>Winter is Coming</td>\n",
       "      <td>will</td>\n",
       "      <td>I've never seen wildlings do a thing like this...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2011/4/17</td>\n",
       "      <td>Season 1</td>\n",
       "      <td>Episode 1</td>\n",
       "      <td>Winter is Coming</td>\n",
       "      <td>waymar royce</td>\n",
       "      <td>How close did you get?</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2011/4/17</td>\n",
       "      <td>Season 1</td>\n",
       "      <td>Episode 1</td>\n",
       "      <td>Winter is Coming</td>\n",
       "      <td>will</td>\n",
       "      <td>Close as any man would.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2011/4/17</td>\n",
       "      <td>Season 1</td>\n",
       "      <td>Episode 1</td>\n",
       "      <td>Winter is Coming</td>\n",
       "      <td>gared</td>\n",
       "      <td>We should head back to the wall.</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Release Date    Season    Episode     Episode Title          Name  \\\n",
       "0    2011/4/17  Season 1  Episode 1  Winter is Coming  waymar royce   \n",
       "1    2011/4/17  Season 1  Episode 1  Winter is Coming          will   \n",
       "2    2011/4/17  Season 1  Episode 1  Winter is Coming  waymar royce   \n",
       "3    2011/4/17  Season 1  Episode 1  Winter is Coming          will   \n",
       "4    2011/4/17  Season 1  Episode 1  Winter is Coming         gared   \n",
       "\n",
       "                                            Sentence  \n",
       "0  What do you expect? They're savages. One lot s...  \n",
       "1  I've never seen wildlings do a thing like this...  \n",
       "2                             How close did you get?  \n",
       "3                            Close as any man would.  \n",
       "4                   We should head back to the wall.  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('data/Game_of_Thrones_Script.csv')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "564"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Name'].nunique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (b)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'tyrion lannister'"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Name'].value_counts().index[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (c) 由于还没有学分组，因此方法繁琐"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Release Date</th>\n",
       "      <th>Season</th>\n",
       "      <th>Episode</th>\n",
       "      <th>Episode Title</th>\n",
       "      <th>Name</th>\n",
       "      <th>Sentence</th>\n",
       "      <th>Words</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>276</th>\n",
       "      <td>2011/4/17</td>\n",
       "      <td>Season 1</td>\n",
       "      <td>Episode 1</td>\n",
       "      <td>Winter is Coming</td>\n",
       "      <td>a voice</td>\n",
       "      <td>It's Maester Luwin, my lord.</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3012</th>\n",
       "      <td>2011/6/19</td>\n",
       "      <td>Season 1</td>\n",
       "      <td>Episode 10</td>\n",
       "      <td>Fire and Blood</td>\n",
       "      <td>addam marbrand</td>\n",
       "      <td>ls it true about Stannis and Renly?</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3017</th>\n",
       "      <td>2011/6/19</td>\n",
       "      <td>Season 1</td>\n",
       "      <td>Episode 10</td>\n",
       "      <td>Fire and Blood</td>\n",
       "      <td>addam marbrand</td>\n",
       "      <td>Kevan Lannister</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13610</th>\n",
       "      <td>2014/6/8</td>\n",
       "      <td>Season 4</td>\n",
       "      <td>Episode 9</td>\n",
       "      <td>The Watchers on the Wall</td>\n",
       "      <td>aemon</td>\n",
       "      <td>And what is it that couldn't wait until mornin...</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13614</th>\n",
       "      <td>2014/6/8</td>\n",
       "      <td>Season 4</td>\n",
       "      <td>Episode 9</td>\n",
       "      <td>The Watchers on the Wall</td>\n",
       "      <td>aemon</td>\n",
       "      <td>Oh, no need. I know my way around this library...</td>\n",
       "      <td>48</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Release Date    Season     Episode             Episode Title  \\\n",
       "276      2011/4/17  Season 1   Episode 1          Winter is Coming   \n",
       "3012     2011/6/19  Season 1  Episode 10            Fire and Blood   \n",
       "3017     2011/6/19  Season 1  Episode 10            Fire and Blood   \n",
       "13610     2014/6/8  Season 4   Episode 9  The Watchers on the Wall   \n",
       "13614     2014/6/8  Season 4   Episode 9  The Watchers on the Wall   \n",
       "\n",
       "                 Name                                           Sentence  \\\n",
       "276           a voice                       It's Maester Luwin, my lord.   \n",
       "3012   addam marbrand                ls it true about Stannis and Renly?   \n",
       "3017   addam marbrand                                    Kevan Lannister   \n",
       "13610           aemon  And what is it that couldn't wait until mornin...   \n",
       "13614           aemon  Oh, no need. I know my way around this library...   \n",
       "\n",
       "       Words  \n",
       "276        5  \n",
       "3012       7  \n",
       "3017       2  \n",
       "13610     10  \n",
       "13614     48  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_words = df.assign(Words=df['Sentence'].apply(lambda x:len(x.split()))).sort_values(by='Name')\n",
    "df_words.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'tyrion lannister'"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "L_count = []\n",
    "N_words = list(zip(df_words['Name'],df_words['Words']))\n",
    "for i in N_words:\n",
    "    if i == N_words[0]:\n",
    "        L_count.append(i[1])\n",
    "        last = i[0]\n",
    "    else:\n",
    "        L_count.append(L_count[-1]+i[1] if i[0]==last else i[1])\n",
    "        last = i[0]\n",
    "df_words['Count']=L_count\n",
    "df_words['Name'][df_words['Count'].idxmax()]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 第1章：练习二"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (a)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>action_type</th>\n",
       "      <th>combined_shot_type</th>\n",
       "      <th>game_event_id</th>\n",
       "      <th>game_id</th>\n",
       "      <th>lat</th>\n",
       "      <th>loc_x</th>\n",
       "      <th>loc_y</th>\n",
       "      <th>lon</th>\n",
       "      <th>minutes_remaining</th>\n",
       "      <th>period</th>\n",
       "      <th>...</th>\n",
       "      <th>shot_made_flag</th>\n",
       "      <th>shot_type</th>\n",
       "      <th>shot_zone_area</th>\n",
       "      <th>shot_zone_basic</th>\n",
       "      <th>shot_zone_range</th>\n",
       "      <th>team_id</th>\n",
       "      <th>team_name</th>\n",
       "      <th>game_date</th>\n",
       "      <th>matchup</th>\n",
       "      <th>opponent</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>shot_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",
       "      <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>1</th>\n",
       "      <td>Jump Shot</td>\n",
       "      <td>Jump Shot</td>\n",
       "      <td>10</td>\n",
       "      <td>20000012</td>\n",
       "      <td>33.9723</td>\n",
       "      <td>167</td>\n",
       "      <td>72</td>\n",
       "      <td>-118.1028</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2PT Field Goal</td>\n",
       "      <td>Right Side(R)</td>\n",
       "      <td>Mid-Range</td>\n",
       "      <td>16-24 ft.</td>\n",
       "      <td>1610612747</td>\n",
       "      <td>Los Angeles Lakers</td>\n",
       "      <td>2000/10/31</td>\n",
       "      <td>LAL @ POR</td>\n",
       "      <td>POR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Jump Shot</td>\n",
       "      <td>Jump Shot</td>\n",
       "      <td>12</td>\n",
       "      <td>20000012</td>\n",
       "      <td>34.0443</td>\n",
       "      <td>-157</td>\n",
       "      <td>0</td>\n",
       "      <td>-118.4268</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2PT Field Goal</td>\n",
       "      <td>Left Side(L)</td>\n",
       "      <td>Mid-Range</td>\n",
       "      <td>8-16 ft.</td>\n",
       "      <td>1610612747</td>\n",
       "      <td>Los Angeles Lakers</td>\n",
       "      <td>2000/10/31</td>\n",
       "      <td>LAL @ POR</td>\n",
       "      <td>POR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Jump Shot</td>\n",
       "      <td>Jump Shot</td>\n",
       "      <td>35</td>\n",
       "      <td>20000012</td>\n",
       "      <td>33.9093</td>\n",
       "      <td>-101</td>\n",
       "      <td>135</td>\n",
       "      <td>-118.3708</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2PT Field Goal</td>\n",
       "      <td>Left Side Center(LC)</td>\n",
       "      <td>Mid-Range</td>\n",
       "      <td>16-24 ft.</td>\n",
       "      <td>1610612747</td>\n",
       "      <td>Los Angeles Lakers</td>\n",
       "      <td>2000/10/31</td>\n",
       "      <td>LAL @ POR</td>\n",
       "      <td>POR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Jump Shot</td>\n",
       "      <td>Jump Shot</td>\n",
       "      <td>43</td>\n",
       "      <td>20000012</td>\n",
       "      <td>33.8693</td>\n",
       "      <td>138</td>\n",
       "      <td>175</td>\n",
       "      <td>-118.1318</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2PT Field Goal</td>\n",
       "      <td>Right Side Center(RC)</td>\n",
       "      <td>Mid-Range</td>\n",
       "      <td>16-24 ft.</td>\n",
       "      <td>1610612747</td>\n",
       "      <td>Los Angeles Lakers</td>\n",
       "      <td>2000/10/31</td>\n",
       "      <td>LAL @ POR</td>\n",
       "      <td>POR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Driving Dunk Shot</td>\n",
       "      <td>Dunk</td>\n",
       "      <td>155</td>\n",
       "      <td>20000012</td>\n",
       "      <td>34.0443</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>-118.2698</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2PT Field Goal</td>\n",
       "      <td>Center(C)</td>\n",
       "      <td>Restricted Area</td>\n",
       "      <td>Less Than 8 ft.</td>\n",
       "      <td>1610612747</td>\n",
       "      <td>Los Angeles Lakers</td>\n",
       "      <td>2000/10/31</td>\n",
       "      <td>LAL @ POR</td>\n",
       "      <td>POR</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               action_type combined_shot_type  game_event_id   game_id  \\\n",
       "shot_id                                                                  \n",
       "1                Jump Shot          Jump Shot             10  20000012   \n",
       "2                Jump Shot          Jump Shot             12  20000012   \n",
       "3                Jump Shot          Jump Shot             35  20000012   \n",
       "4                Jump Shot          Jump Shot             43  20000012   \n",
       "5        Driving Dunk Shot               Dunk            155  20000012   \n",
       "\n",
       "             lat  loc_x  loc_y       lon  minutes_remaining  period  ...  \\\n",
       "shot_id                                                              ...   \n",
       "1        33.9723    167     72 -118.1028                 10       1  ...   \n",
       "2        34.0443   -157      0 -118.4268                 10       1  ...   \n",
       "3        33.9093   -101    135 -118.3708                  7       1  ...   \n",
       "4        33.8693    138    175 -118.1318                  6       1  ...   \n",
       "5        34.0443      0      0 -118.2698                  6       2  ...   \n",
       "\n",
       "         shot_made_flag       shot_type         shot_zone_area  \\\n",
       "shot_id                                                          \n",
       "1                   NaN  2PT Field Goal          Right Side(R)   \n",
       "2                   0.0  2PT Field Goal           Left Side(L)   \n",
       "3                   1.0  2PT Field Goal   Left Side Center(LC)   \n",
       "4                   0.0  2PT Field Goal  Right Side Center(RC)   \n",
       "5                   1.0  2PT Field Goal              Center(C)   \n",
       "\n",
       "         shot_zone_basic  shot_zone_range     team_id           team_name  \\\n",
       "shot_id                                                                     \n",
       "1              Mid-Range        16-24 ft.  1610612747  Los Angeles Lakers   \n",
       "2              Mid-Range         8-16 ft.  1610612747  Los Angeles Lakers   \n",
       "3              Mid-Range        16-24 ft.  1610612747  Los Angeles Lakers   \n",
       "4              Mid-Range        16-24 ft.  1610612747  Los Angeles Lakers   \n",
       "5        Restricted Area  Less Than 8 ft.  1610612747  Los Angeles Lakers   \n",
       "\n",
       "          game_date    matchup  opponent  \n",
       "shot_id                                   \n",
       "1        2000/10/31  LAL @ POR       POR  \n",
       "2        2000/10/31  LAL @ POR       POR  \n",
       "3        2000/10/31  LAL @ POR       POR  \n",
       "4        2000/10/31  LAL @ POR       POR  \n",
       "5        2000/10/31  LAL @ POR       POR  \n",
       "\n",
       "[5 rows x 24 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('data/Kobe_data.csv',index_col='shot_id')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "('Jump Shot', 'Jump Shot')"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.Series(list(zip(df['action_type'],df['combined_shot_type']))).value_counts().index[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (b)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'SAS'"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.Series(list(list(zip(*(pd.Series(list(zip(df['game_id'],df['opponent'])))\n",
    "                          .unique()).tolist()))[1])).value_counts().index[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 第2章：练习一"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (a)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>datetime</th>\n",
       "      <th>shape</th>\n",
       "      <th>duration</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10/10/1949 20:30</td>\n",
       "      <td>cylinder</td>\n",
       "      <td>2700.0</td>\n",
       "      <td>29.883056</td>\n",
       "      <td>-97.941111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10/10/1949 21:00</td>\n",
       "      <td>light</td>\n",
       "      <td>7200.0</td>\n",
       "      <td>29.384210</td>\n",
       "      <td>-98.581082</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10/10/1955 17:00</td>\n",
       "      <td>circle</td>\n",
       "      <td>20.0</td>\n",
       "      <td>53.200000</td>\n",
       "      <td>-2.916667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10/10/1956 21:00</td>\n",
       "      <td>circle</td>\n",
       "      <td>20.0</td>\n",
       "      <td>28.978333</td>\n",
       "      <td>-96.645833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10/10/1960 20:00</td>\n",
       "      <td>light</td>\n",
       "      <td>900.0</td>\n",
       "      <td>21.418056</td>\n",
       "      <td>-157.803611</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           datetime     shape  duration   latitude   longitude\n",
       "0  10/10/1949 20:30  cylinder    2700.0  29.883056  -97.941111\n",
       "1  10/10/1949 21:00     light    7200.0  29.384210  -98.581082\n",
       "2  10/10/1955 17:00    circle      20.0  53.200000   -2.916667\n",
       "3  10/10/1956 21:00    circle      20.0  28.978333  -96.645833\n",
       "4  10/10/1960 20:00     light     900.0  21.418056 -157.803611"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('data/UFO.csv')\n",
    "df.rename(columns={'duration (seconds)':'duration'},inplace=True)\n",
    "df['duration'].astype('float')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'light'"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.query('duration > 60')['shape'].value_counts().index[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (b)"
   ]
  },
  {
   "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>datetime</th>\n",
       "      <th>shape</th>\n",
       "      <th>duration</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>cuts_long</th>\n",
       "      <th>cuts_la</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10/10/1949 20:30</td>\n",
       "      <td>cylinder</td>\n",
       "      <td>2700.0</td>\n",
       "      <td>29.883056</td>\n",
       "      <td>-97.941111</td>\n",
       "      <td>(-120.0, -90.0]</td>\n",
       "      <td>(18.0, 36.0]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10/10/1949 21:00</td>\n",
       "      <td>light</td>\n",
       "      <td>7200.0</td>\n",
       "      <td>29.384210</td>\n",
       "      <td>-98.581082</td>\n",
       "      <td>(-120.0, -90.0]</td>\n",
       "      <td>(18.0, 36.0]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10/10/1955 17:00</td>\n",
       "      <td>circle</td>\n",
       "      <td>20.0</td>\n",
       "      <td>53.200000</td>\n",
       "      <td>-2.916667</td>\n",
       "      <td>(-30.0, 0.0]</td>\n",
       "      <td>(36.0, 54.0]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10/10/1956 21:00</td>\n",
       "      <td>circle</td>\n",
       "      <td>20.0</td>\n",
       "      <td>28.978333</td>\n",
       "      <td>-96.645833</td>\n",
       "      <td>(-120.0, -90.0]</td>\n",
       "      <td>(18.0, 36.0]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10/10/1960 20:00</td>\n",
       "      <td>light</td>\n",
       "      <td>900.0</td>\n",
       "      <td>21.418056</td>\n",
       "      <td>-157.803611</td>\n",
       "      <td>(-180.0, -150.0]</td>\n",
       "      <td>(18.0, 36.0]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           datetime     shape  duration   latitude   longitude  \\\n",
       "0  10/10/1949 20:30  cylinder    2700.0  29.883056  -97.941111   \n",
       "1  10/10/1949 21:00     light    7200.0  29.384210  -98.581082   \n",
       "2  10/10/1955 17:00    circle      20.0  53.200000   -2.916667   \n",
       "3  10/10/1956 21:00    circle      20.0  28.978333  -96.645833   \n",
       "4  10/10/1960 20:00     light     900.0  21.418056 -157.803611   \n",
       "\n",
       "          cuts_long       cuts_la  \n",
       "0   (-120.0, -90.0]  (18.0, 36.0]  \n",
       "1   (-120.0, -90.0]  (18.0, 36.0]  \n",
       "2      (-30.0, 0.0]  (36.0, 54.0]  \n",
       "3   (-120.0, -90.0]  (18.0, 36.0]  \n",
       "4  (-180.0, -150.0]  (18.0, 36.0]  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bins_long = np.linspace(-180,180,13).tolist()\n",
    "bins_la = np.linspace(-90,90,11).tolist()\n",
    "cuts_long = pd.cut(df['longitude'],bins=bins_long)\n",
    "df['cuts_long'] = cuts_long\n",
    "cuts_la = pd.cut(df['latitude'],bins=bins_la)\n",
    "df['cuts_la'] = cuts_la\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((-90.0, -60.0], (36.0, 54.0])      27891\n",
       "((-120.0, -90.0], (18.0, 36.0])     14280\n",
       "((-120.0, -90.0], (36.0, 54.0])     11960\n",
       "((-90.0, -60.0], (18.0, 36.0])       9923\n",
       "((-150.0, -120.0], (36.0, 54.0])     9658\n",
       "dtype: int64"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.set_index(['cuts_long','cuts_la']).index.value_counts().head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 第2章：练习二"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (a)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead 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>Name</th>\n",
       "      <th>Type 1</th>\n",
       "      <th>Type 2</th>\n",
       "      <th>Total</th>\n",
       "      <th>HP</th>\n",
       "      <th>Attack</th>\n",
       "      <th>Defense</th>\n",
       "      <th>Sp. Atk</th>\n",
       "      <th>Sp. Def</th>\n",
       "      <th>Speed</th>\n",
       "      <th>Generation</th>\n",
       "      <th>Legendary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Bulbasaur</td>\n",
       "      <td>Grass</td>\n",
       "      <td>Poison</td>\n",
       "      <td>318</td>\n",
       "      <td>45</td>\n",
       "      <td>49</td>\n",
       "      <td>49</td>\n",
       "      <td>65</td>\n",
       "      <td>65</td>\n",
       "      <td>45</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Ivysaur</td>\n",
       "      <td>Grass</td>\n",
       "      <td>Poison</td>\n",
       "      <td>405</td>\n",
       "      <td>60</td>\n",
       "      <td>62</td>\n",
       "      <td>63</td>\n",
       "      <td>80</td>\n",
       "      <td>80</td>\n",
       "      <td>60</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Venusaur</td>\n",
       "      <td>Grass</td>\n",
       "      <td>Poison</td>\n",
       "      <td>525</td>\n",
       "      <td>80</td>\n",
       "      <td>82</td>\n",
       "      <td>83</td>\n",
       "      <td>100</td>\n",
       "      <td>100</td>\n",
       "      <td>80</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>VenusaurMega Venusaur</td>\n",
       "      <td>Grass</td>\n",
       "      <td>Poison</td>\n",
       "      <td>625</td>\n",
       "      <td>80</td>\n",
       "      <td>100</td>\n",
       "      <td>123</td>\n",
       "      <td>122</td>\n",
       "      <td>120</td>\n",
       "      <td>80</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>Charmander</td>\n",
       "      <td>Fire</td>\n",
       "      <td>NaN</td>\n",
       "      <td>309</td>\n",
       "      <td>39</td>\n",
       "      <td>52</td>\n",
       "      <td>43</td>\n",
       "      <td>60</td>\n",
       "      <td>50</td>\n",
       "      <td>65</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   #                   Name Type 1  Type 2  Total  HP  Attack  Defense  \\\n",
       "0  1              Bulbasaur  Grass  Poison    318  45      49       49   \n",
       "1  2                Ivysaur  Grass  Poison    405  60      62       63   \n",
       "2  3               Venusaur  Grass  Poison    525  80      82       83   \n",
       "3  3  VenusaurMega Venusaur  Grass  Poison    625  80     100      123   \n",
       "4  4             Charmander   Fire     NaN    309  39      52       43   \n",
       "\n",
       "   Sp. Atk  Sp. Def  Speed  Generation  Legendary  \n",
       "0       65       65     45           1      False  \n",
       "1       80       80     60           1      False  \n",
       "2      100      100     80           1      False  \n",
       "3      122      120     80           1      False  \n",
       "4       60       50     65           1      False  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('data/Pokemon.csv')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.5175"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Type 2'].count()/df.shape[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (b)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True     0.575221\n",
       "False    0.424779\n",
       "Name: Legendary, dtype: float64"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.query('Total >= 580')['Legendary'].value_counts(normalize=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (c) 分别为Maga形态的路卡利欧、修缮老头、怪力"
   ]
  },
  {
   "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>Name</th>\n",
       "      <th>Type 1</th>\n",
       "      <th>Type 2</th>\n",
       "      <th>Total</th>\n",
       "      <th>HP</th>\n",
       "      <th>Attack</th>\n",
       "      <th>Defense</th>\n",
       "      <th>Sp. Atk</th>\n",
       "      <th>Sp. Def</th>\n",
       "      <th>Speed</th>\n",
       "      <th>Generation</th>\n",
       "      <th>Legendary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>498</th>\n",
       "      <td>448</td>\n",
       "      <td>LucarioMega Lucario</td>\n",
       "      <td>Fighting</td>\n",
       "      <td>Steel</td>\n",
       "      <td>625</td>\n",
       "      <td>70</td>\n",
       "      <td>145</td>\n",
       "      <td>88</td>\n",
       "      <td>140</td>\n",
       "      <td>70</td>\n",
       "      <td>112</td>\n",
       "      <td>4</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>594</th>\n",
       "      <td>534</td>\n",
       "      <td>Conkeldurr</td>\n",
       "      <td>Fighting</td>\n",
       "      <td>NaN</td>\n",
       "      <td>505</td>\n",
       "      <td>105</td>\n",
       "      <td>140</td>\n",
       "      <td>95</td>\n",
       "      <td>55</td>\n",
       "      <td>65</td>\n",
       "      <td>45</td>\n",
       "      <td>5</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td>68</td>\n",
       "      <td>Machamp</td>\n",
       "      <td>Fighting</td>\n",
       "      <td>NaN</td>\n",
       "      <td>505</td>\n",
       "      <td>90</td>\n",
       "      <td>130</td>\n",
       "      <td>80</td>\n",
       "      <td>65</td>\n",
       "      <td>85</td>\n",
       "      <td>55</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       #                 Name    Type 1 Type 2  Total   HP  Attack  Defense  \\\n",
       "498  448  LucarioMega Lucario  Fighting  Steel    625   70     145       88   \n",
       "594  534           Conkeldurr  Fighting    NaN    505  105     140       95   \n",
       "74    68              Machamp  Fighting    NaN    505   90     130       80   \n",
       "\n",
       "     Sp. Atk  Sp. Def  Speed  Generation  Legendary  \n",
       "498      140       70    112           4      False  \n",
       "594       55       65     45           5      False  \n",
       "74        65       85     55           1      False  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['Type 1']=='Fighting'].sort_values(by='Attack',ascending=False).iloc[:3]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (d) 钢系的极差均值最大"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Steel'"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['range'] = df.iloc[:,5:11].max(axis=1)-df.iloc[:,5:11].min(axis=1)\n",
    "attribute = df[['Type 1','range']].set_index('Type 1')\n",
    "max_range = 0\n",
    "result = ''\n",
    "for i in attribute.index.unique():\n",
    "    temp = attribute.loc[i,:].mean()\n",
    "    if temp.values[0] > max_range:\n",
    "        max_range = temp.values[0]\n",
    "        result = i\n",
    "result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (e) 超能系占比最高，但普通系均值最高（因为创世神的关系）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Psychic'"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.query('Legendary == True')['Type 1'].value_counts(normalize=True).index[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Normal'"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "attribute = df.query('Legendary == True')[['Type 1','Total']].set_index('Type 1')\n",
    "max_value = 0\n",
    "result = ''\n",
    "for i in attribute.index.unique():\n",
    "    temp = float(attribute.loc[i,:].mean())\n",
    "    if temp > max_value:\n",
    "        max_value = temp\n",
    "        result = i\n",
    "result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 第3章：练习一"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (a) 极差为17561"
   ]
  },
  {
   "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>carat</th>\n",
       "      <th>color</th>\n",
       "      <th>depth</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.23</td>\n",
       "      <td>E</td>\n",
       "      <td>61.5</td>\n",
       "      <td>326</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.21</td>\n",
       "      <td>E</td>\n",
       "      <td>59.8</td>\n",
       "      <td>326</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.23</td>\n",
       "      <td>E</td>\n",
       "      <td>56.9</td>\n",
       "      <td>327</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.29</td>\n",
       "      <td>I</td>\n",
       "      <td>62.4</td>\n",
       "      <td>334</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.31</td>\n",
       "      <td>J</td>\n",
       "      <td>63.3</td>\n",
       "      <td>335</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   carat color  depth  price\n",
       "0   0.23     E   61.5    326\n",
       "1   0.21     E   59.8    326\n",
       "2   0.23     E   56.9    327\n",
       "3   0.29     I   62.4    334\n",
       "4   0.31     J   63.3    335"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('data/Diamonds.csv')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "17561"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_r = df.query('carat>1')['price']\n",
    "df_r.max()-df_r.min()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (b) 0-0.2分位数区间最多的为‘E’，其余区间都为‘G’"
   ]
  },
  {
   "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>carat</th>\n",
       "      <th>color</th>\n",
       "      <th>depth</th>\n",
       "      <th>price</th>\n",
       "      <th>cuts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.23</td>\n",
       "      <td>E</td>\n",
       "      <td>61.5</td>\n",
       "      <td>326</td>\n",
       "      <td>(60.8, 61.6]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.21</td>\n",
       "      <td>E</td>\n",
       "      <td>59.8</td>\n",
       "      <td>326</td>\n",
       "      <td>(43.0, 60.8]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.23</td>\n",
       "      <td>E</td>\n",
       "      <td>56.9</td>\n",
       "      <td>327</td>\n",
       "      <td>(43.0, 60.8]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.29</td>\n",
       "      <td>I</td>\n",
       "      <td>62.4</td>\n",
       "      <td>334</td>\n",
       "      <td>(62.1, 62.7]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.31</td>\n",
       "      <td>J</td>\n",
       "      <td>63.3</td>\n",
       "      <td>335</td>\n",
       "      <td>(62.7, 79.0]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   carat color  depth  price          cuts\n",
       "0   0.23     E   61.5    326  (60.8, 61.6]\n",
       "1   0.21     E   59.8    326  (43.0, 60.8]\n",
       "2   0.23     E   56.9    327  (43.0, 60.8]\n",
       "3   0.29     I   62.4    334  (62.1, 62.7]\n",
       "4   0.31     J   63.3    335  (62.7, 79.0]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bins = df['depth'].quantile(np.linspace(0,1,6)).tolist()\n",
    "cuts = pd.cut(df['depth'],bins=bins) #可选label添加自定义标签\n",
    "df['cuts'] = cuts\n",
    "df.head()"
   ]
  },
  {
   "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>count</th>\n",
       "      <th>unique</th>\n",
       "      <th>top</th>\n",
       "      <th>freq</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>cuts</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>(43.0, 60.8]</th>\n",
       "      <td>11294</td>\n",
       "      <td>7</td>\n",
       "      <td>E</td>\n",
       "      <td>2259</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(60.8, 61.6]</th>\n",
       "      <td>11831</td>\n",
       "      <td>7</td>\n",
       "      <td>G</td>\n",
       "      <td>2593</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(61.6, 62.1]</th>\n",
       "      <td>10403</td>\n",
       "      <td>7</td>\n",
       "      <td>G</td>\n",
       "      <td>2247</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(62.1, 62.7]</th>\n",
       "      <td>10137</td>\n",
       "      <td>7</td>\n",
       "      <td>G</td>\n",
       "      <td>2193</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(62.7, 79.0]</th>\n",
       "      <td>10273</td>\n",
       "      <td>7</td>\n",
       "      <td>G</td>\n",
       "      <td>2000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              count unique top  freq\n",
       "cuts                                \n",
       "(43.0, 60.8]  11294      7   E  2259\n",
       "(60.8, 61.6]  11831      7   G  2593\n",
       "(61.6, 62.1]  10403      7   G  2247\n",
       "(62.1, 62.7]  10137      7   G  2193\n",
       "(62.7, 79.0]  10273      7   G  2000"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "color_result = df.groupby('cuts')['color'].describe()\n",
    "color_result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 前三个分位数区间不满足条件，后两个区间中数量最多的颜色的确是均重价格中最贵的"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "cuts\n",
       "(43.0, 60.8]    False\n",
       "(60.8, 61.6]    False\n",
       "(61.6, 62.1]    False\n",
       "(62.1, 62.7]     True\n",
       "(62.7, 79.0]     True\n",
       "Name: top, dtype: bool"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['均重价格']=df['price']/df['carat']\n",
    "color_result['top'] == [i[1] for i in df.groupby(['cuts'\n",
    "                                ,'color'])['均重价格'].mean().groupby(['cuts']).idxmax().values]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (c) 结果见下："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "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>carat</th>\n",
       "      <th>color</th>\n",
       "      <th>depth</th>\n",
       "      <th>price</th>\n",
       "      <th>cuts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.23</td>\n",
       "      <td>E</td>\n",
       "      <td>61.5</td>\n",
       "      <td>326</td>\n",
       "      <td>(0.0, 0.5]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.21</td>\n",
       "      <td>E</td>\n",
       "      <td>59.8</td>\n",
       "      <td>326</td>\n",
       "      <td>(0.0, 0.5]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.23</td>\n",
       "      <td>E</td>\n",
       "      <td>56.9</td>\n",
       "      <td>327</td>\n",
       "      <td>(0.0, 0.5]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.29</td>\n",
       "      <td>I</td>\n",
       "      <td>62.4</td>\n",
       "      <td>334</td>\n",
       "      <td>(0.0, 0.5]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.31</td>\n",
       "      <td>J</td>\n",
       "      <td>63.3</td>\n",
       "      <td>335</td>\n",
       "      <td>(0.0, 0.5]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   carat color  depth  price        cuts\n",
       "0   0.23     E   61.5    326  (0.0, 0.5]\n",
       "1   0.21     E   59.8    326  (0.0, 0.5]\n",
       "2   0.23     E   56.9    327  (0.0, 0.5]\n",
       "3   0.29     I   62.4    334  (0.0, 0.5]\n",
       "4   0.31     J   63.3    335  (0.0, 0.5]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df.drop(columns='均重价格')\n",
    "cuts = pd.cut(df['carat'],bins=[0,0.5,1,1.5,2,np.inf]) #可选label添加自定义标签\n",
    "df['cuts'] = cuts\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "def f(nums):\n",
    "    if not nums:        \n",
    "        return 0\n",
    "    res = 1                            \n",
    "    cur_len = 1                        \n",
    "    for i in range(1, len(nums)):      \n",
    "        if nums[i-1] < nums[i]:        \n",
    "            cur_len += 1                \n",
    "            res = max(cur_len, res)     \n",
    "        else:                       \n",
    "            cur_len = 1                 \n",
    "    return res"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(0.0, 0.5] 8\n",
      "(0.5, 1.0] 8\n",
      "(1.0, 1.5] 7\n",
      "(1.5, 2.0] 11\n",
      "(2.0, inf] 7\n"
     ]
    }
   ],
   "source": [
    "for name,group in df.groupby('cuts'):\n",
    "    group = group.sort_values(by='depth')\n",
    "    s = group['price']\n",
    "    print(name,f(s.tolist()))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (d) 计算结果如下："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "当颜色为D时，截距项为：-2361.017152，回归系数为：8408.353126\n",
      "当颜色为E时，截距项为：-2381.049600，回归系数为：8296.212783\n",
      "当颜色为F时，截距项为：-2665.806191，回归系数为：8676.658344\n",
      "当颜色为G时，截距项为：-2575.527643，回归系数为：8525.345779\n",
      "当颜色为H时，截距项为：-2460.418046，回归系数为：7619.098320\n",
      "当颜色为I时，截距项为：-2878.150356，回归系数为：7761.041169\n",
      "当颜色为J时，截距项为：-2920.603337，回归系数为：7094.192092\n"
     ]
    }
   ],
   "source": [
    "for name,group in df[['carat','price','color']].groupby('color'):\n",
    "    L1 = np.array([np.ones(group.shape[0]),group['carat']]).reshape(2,group.shape[0])\n",
    "    L2 = group['price']\n",
    "    result = (np.linalg.inv(L1.dot(L1.T)).dot(L1)).dot(L2).reshape(2,1)\n",
    "    print('当颜色为%s时，截距项为：%f，回归系数为：%f'%(name,result[0],result[1]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 第3章：练习二"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (a)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "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": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('data/Drugs.csv')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "在2010年，PHILADELPHIA县的报告数最多，它所属的州PA也是报告数最多的\n",
      "在2011年，PHILADELPHIA县的报告数最多，但它所属的州PA不是报告数最多的，OH州报告数最多\n",
      "在2012年，PHILADELPHIA县的报告数最多，但它所属的州PA不是报告数最多的，OH州报告数最多\n",
      "在2013年，PHILADELPHIA县的报告数最多，但它所属的州PA不是报告数最多的，OH州报告数最多\n",
      "在2014年，PHILADELPHIA县的报告数最多，但它所属的州PA不是报告数最多的，OH州报告数最多\n",
      "在2015年，PHILADELPHIA县的报告数最多，但它所属的州PA不是报告数最多的，OH州报告数最多\n",
      "在2016年，HAMILTON县的报告数最多，它所属的州OH也是报告数最多的\n",
      "在2017年，HAMILTON县的报告数最多，它所属的州OH也是报告数最多的\n"
     ]
    }
   ],
   "source": [
    "idx=pd.IndexSlice\n",
    "for i in range(2010,2018):\n",
    "    county = (df.groupby(['COUNTY','YYYY']).sum().loc[idx[:,i],:].idxmax()[0][0])\n",
    "    state = df.query('COUNTY == \"%s\"'%county)['State'].iloc[0]\n",
    "    state_true = df.groupby(['State','YYYY']).sum().loc[idx[:,i],:].idxmax()[0][0]\n",
    "    if state==state_true:\n",
    "        print('在%d年，%s县的报告数最多，它所属的州%s也是报告数最多的'%(i,county,state))\n",
    "    else:\n",
    "        print('在%d年，%s县的报告数最多，但它所属的州%s不是报告数最多的，%s州报告数最多'%(i,county,state,state_true))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (b) OH州增加最多，Heroin是增量最大的，但增幅最大的是Acetyl fentanyl"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DrugReports    OH\n",
       "dtype: object"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_b = df[(df['YYYY'].isin([2014,2015]))&(df['SubstanceName']=='Heroin')]\n",
    "df_add = df_b.groupby(['YYYY','State']).sum()\n",
    "(df_add.loc[2015]-df_add.loc[2014]).idxmax()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DrugReports    Heroin\n",
       "dtype: object"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "DrugReports    Acetyl fentanyl\n",
       "dtype: object"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df_b = df[(df['YYYY'].isin([2014,2015]))&(df['State']=='OH')]\n",
    "df_add = df_b.groupby(['YYYY','SubstanceName']).sum()\n",
    "display((df_add.loc[2015]-df_add.loc[2014]).idxmax()) #这里利用了索引对齐的特点\n",
    "display((df_add.loc[2015]/df_add.loc[2014]).idxmax())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 第4章：练习一"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (a)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "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>YYYY</th>\n",
       "      <th>SubstanceName</th>\n",
       "      <th>DrugReports</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>State</th>\n",
       "      <th>COUNTY</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">KY</th>\n",
       "      <th>ADAIR</th>\n",
       "      <td>2010</td>\n",
       "      <td>Methadone</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ADAIR</th>\n",
       "      <td>2010</td>\n",
       "      <td>Hydrocodone</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ADAIR</th>\n",
       "      <td>2011</td>\n",
       "      <td>Oxycodone</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ADAIR</th>\n",
       "      <td>2011</td>\n",
       "      <td>Buprenorphine</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ADAIR</th>\n",
       "      <td>2011</td>\n",
       "      <td>Morphine</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              YYYY  SubstanceName  DrugReports\n",
       "State COUNTY                                  \n",
       "KY    ADAIR   2010      Methadone            1\n",
       "      ADAIR   2010    Hydrocodone            6\n",
       "      ADAIR   2011      Oxycodone            4\n",
       "      ADAIR   2011  Buprenorphine            3\n",
       "      ADAIR   2011       Morphine            2"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('data/Drugs.csv',index_col=['State','COUNTY']).sort_index()\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "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>State</th>\n",
       "      <th>COUNTY</th>\n",
       "      <th>SubstanceName</th>\n",
       "      <th>2010</th>\n",
       "      <th>2011</th>\n",
       "      <th>2012</th>\n",
       "      <th>2013</th>\n",
       "      <th>2014</th>\n",
       "      <th>2015</th>\n",
       "      <th>2016</th>\n",
       "      <th>2017</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>KY</td>\n",
       "      <td>ADAIR</td>\n",
       "      <td>Buprenorphine</td>\n",
       "      <td>-</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>27</td>\n",
       "      <td>5</td>\n",
       "      <td>7</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>KY</td>\n",
       "      <td>ADAIR</td>\n",
       "      <td>Codeine</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>1</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>KY</td>\n",
       "      <td>ADAIR</td>\n",
       "      <td>Fentanyl</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>1</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>KY</td>\n",
       "      <td>ADAIR</td>\n",
       "      <td>Heroin</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>-</td>\n",
       "      <td>1</td>\n",
       "      <td>-</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>KY</td>\n",
       "      <td>ADAIR</td>\n",
       "      <td>Hydrocodone</td>\n",
       "      <td>6</td>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>9</td>\n",
       "      <td>7</td>\n",
       "      <td>11</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  State COUNTY  SubstanceName 2010 2011 2012 2013 2014 2015 2016 2017\n",
       "0    KY  ADAIR  Buprenorphine    -    3    5    4   27    5    7   10\n",
       "1    KY  ADAIR        Codeine    -    -    1    -    -    -    -    1\n",
       "2    KY  ADAIR       Fentanyl    -    -    1    -    -    -    -    -\n",
       "3    KY  ADAIR         Heroin    -    -    1    2    -    1    -    2\n",
       "4    KY  ADAIR    Hydrocodone    6    9   10   10    9    7   11    3"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = pd.pivot_table(df,index=['State','COUNTY','SubstanceName']\n",
    "                 ,columns='YYYY'\n",
    "                 ,values='DrugReports',fill_value='-').reset_index().rename_axis(columns={'YYYY':''})\n",
    "result.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (b)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "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>State</th>\n",
       "      <th>COUNTY</th>\n",
       "      <th>YYYY</th>\n",
       "      <th>SubstanceName</th>\n",
       "      <th>DrugReports</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>KY</td>\n",
       "      <td>ADAIR</td>\n",
       "      <td>2010</td>\n",
       "      <td>Hydrocodone</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>KY</td>\n",
       "      <td>ADAIR</td>\n",
       "      <td>2010</td>\n",
       "      <td>Methadone</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>KY</td>\n",
       "      <td>ADAIR</td>\n",
       "      <td>2011</td>\n",
       "      <td>Buprenorphine</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>KY</td>\n",
       "      <td>ADAIR</td>\n",
       "      <td>2011</td>\n",
       "      <td>Hydrocodone</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>KY</td>\n",
       "      <td>ADAIR</td>\n",
       "      <td>2011</td>\n",
       "      <td>Morphine</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  State COUNTY  YYYY  SubstanceName  DrugReports\n",
       "0    KY  ADAIR  2010    Hydrocodone            6\n",
       "1    KY  ADAIR  2010      Methadone            1\n",
       "2    KY  ADAIR  2011  Buprenorphine            3\n",
       "3    KY  ADAIR  2011    Hydrocodone            9\n",
       "4    KY  ADAIR  2011       Morphine            2"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result_melted = result.melt(id_vars=result.columns[:3],value_vars=result.columns[-8:]\n",
    "                ,var_name='YYYY',value_name='DrugReports').query('DrugReports != \"-\"')\n",
    "result2 = result_melted.sort_values(by=['State','COUNTY','YYYY'\n",
    "                                    ,'SubstanceName']).reset_index().drop(columns='index')\n",
    "#下面其实无关紧要，只是交换两个列再改一下类型（因为‘-’所以type变成object了）\n",
    "cols = list(result2.columns)\n",
    "a, b = cols.index('SubstanceName'), cols.index('YYYY')\n",
    "cols[b], cols[a] = cols[a], cols[b]\n",
    "result2 = result2[cols].astype({'DrugReports':'int','YYYY':'int'})\n",
    "result2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "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>State</th>\n",
       "      <th>COUNTY</th>\n",
       "      <th>YYYY</th>\n",
       "      <th>SubstanceName</th>\n",
       "      <th>DrugReports</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>KY</td>\n",
       "      <td>ADAIR</td>\n",
       "      <td>2010</td>\n",
       "      <td>Hydrocodone</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>KY</td>\n",
       "      <td>ADAIR</td>\n",
       "      <td>2010</td>\n",
       "      <td>Methadone</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>KY</td>\n",
       "      <td>ADAIR</td>\n",
       "      <td>2011</td>\n",
       "      <td>Buprenorphine</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>KY</td>\n",
       "      <td>ADAIR</td>\n",
       "      <td>2011</td>\n",
       "      <td>Hydrocodone</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>KY</td>\n",
       "      <td>ADAIR</td>\n",
       "      <td>2011</td>\n",
       "      <td>Morphine</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  State COUNTY  YYYY  SubstanceName  DrugReports\n",
       "0    KY  ADAIR  2010    Hydrocodone            6\n",
       "1    KY  ADAIR  2010      Methadone            1\n",
       "2    KY  ADAIR  2011  Buprenorphine            3\n",
       "3    KY  ADAIR  2011    Hydrocodone            9\n",
       "4    KY  ADAIR  2011       Morphine            2"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_tidy = df.reset_index().sort_values(by=result2.columns[:4].tolist()).reset_index().drop(columns='index')\n",
    "df_tidy.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_tidy.equals(result2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 第4章：练习二"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (a)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "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>south_east</td>\n",
       "      <td>1912.08.09</td>\n",
       "      <td>12:29:00 AM</td>\n",
       "      <td>16.0</td>\n",
       "      <td>6.7</td>\n",
       "      <td>27.2</td>\n",
       "      <td>40.6</td>\n",
       "      <td>4.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>south_west</td>\n",
       "      <td>1912.08.10</td>\n",
       "      <td>12:23:00 AM</td>\n",
       "      <td>15.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>27.1</td>\n",
       "      <td>40.6</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>south_west</td>\n",
       "      <td>1912.08.10</td>\n",
       "      <td>12:30:00 AM</td>\n",
       "      <td>15.0</td>\n",
       "      <td>5.2</td>\n",
       "      <td>27.1</td>\n",
       "      <td>40.6</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>south_east</td>\n",
       "      <td>1912.08.11</td>\n",
       "      <td>12:19:04 AM</td>\n",
       "      <td>30.0</td>\n",
       "      <td>4.9</td>\n",
       "      <td>27.2</td>\n",
       "      <td>40.6</td>\n",
       "      <td>4.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>south_west</td>\n",
       "      <td>1912.08.11</td>\n",
       "      <td>12:20:00 AM</td>\n",
       "      <td>15.0</td>\n",
       "      <td>4.5</td>\n",
       "      <td>27.1</td>\n",
       "      <td>40.6</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           方向          日期           时间    深度   烈度    经度    维度   距离\n",
       "0  south_east  1912.08.09  12:29:00 AM  16.0  6.7  27.2  40.6  4.3\n",
       "1  south_west  1912.08.10  12:23:00 AM  15.0  6.0  27.1  40.6  2.0\n",
       "2  south_west  1912.08.10  12:30:00 AM  15.0  5.2  27.1  40.6  2.0\n",
       "3  south_east  1912.08.11  12:19:04 AM  30.0  4.9  27.2  40.6  4.3\n",
       "4  south_west  1912.08.11  12:20:00 AM  15.0  4.5  27.1  40.6  2.0"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('data/Earthquake.csv')\n",
    "df = df.sort_values(by=df.columns.tolist()[:3]).sort_index(axis=1).reset_index().drop(columns='index')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "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>east</th>\n",
       "      <th>north</th>\n",
       "      <th>north_east</th>\n",
       "      <th>north_west</th>\n",
       "      <th>south</th>\n",
       "      <th>south_east</th>\n",
       "      <th>south_west</th>\n",
       "      <th>west</th>\n",
       "    </tr>\n",
       "    <tr>\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\">1912.08.09</th>\n",
       "      <th rowspan=\"3\" valign=\"top\">12:29:00 AM</th>\n",
       "      <th rowspan=\"3\" valign=\"top\">40.6</th>\n",
       "      <th rowspan=\"3\" valign=\"top\">27.2</th>\n",
       "      <th>深度</th>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>16</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>烈度</th>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>6.7</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>距离</th>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>4.3</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">1912.08.10</th>\n",
       "      <th rowspan=\"3\" valign=\"top\">12:23:00 AM</th>\n",
       "      <th rowspan=\"3\" valign=\"top\">40.6</th>\n",
       "      <th rowspan=\"3\" valign=\"top\">27.1</th>\n",
       "      <th>深度</th>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>15</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>烈度</th>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>6</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>距离</th>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>2</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "方向                                    east north north_east north_west south  \\\n",
       "日期         时间          维度   经度   地震参数                                          \n",
       "1912.08.09 12:29:00 AM 40.6 27.2 深度      -     -          -          -     -   \n",
       "                                 烈度      -     -          -          -     -   \n",
       "                                 距离      -     -          -          -     -   \n",
       "1912.08.10 12:23:00 AM 40.6 27.1 深度      -     -          -          -     -   \n",
       "                                 烈度      -     -          -          -     -   \n",
       "                                 距离      -     -          -          -     -   \n",
       "\n",
       "方向                                    south_east south_west west  \n",
       "日期         时间          维度   经度   地震参数                             \n",
       "1912.08.09 12:29:00 AM 40.6 27.2 深度           16          -    -  \n",
       "                                 烈度          6.7          -    -  \n",
       "                                 距离          4.3          -    -  \n",
       "1912.08.10 12:23:00 AM 40.6 27.1 深度            -         15    -  \n",
       "                                 烈度            -          6    -  \n",
       "                                 距离            -          2    -  "
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = pd.pivot_table(df,index=['日期','时间','维度','经度']\n",
    "            ,columns='方向'\n",
    "            ,values=['烈度','深度','距离'],fill_value='-').stack(level=0).rename_axis(index={None:'地震参数'})\n",
    "result.head(6)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (b)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "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>south_east</td>\n",
       "      <td>1912.08.09</td>\n",
       "      <td>12:29:00 AM</td>\n",
       "      <td>16.0</td>\n",
       "      <td>6.7</td>\n",
       "      <td>27.2</td>\n",
       "      <td>40.6</td>\n",
       "      <td>4.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>south_west</td>\n",
       "      <td>1912.08.10</td>\n",
       "      <td>12:23:00 AM</td>\n",
       "      <td>15.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>27.1</td>\n",
       "      <td>40.6</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>south_west</td>\n",
       "      <td>1912.08.10</td>\n",
       "      <td>12:30:00 AM</td>\n",
       "      <td>15.0</td>\n",
       "      <td>5.2</td>\n",
       "      <td>27.1</td>\n",
       "      <td>40.6</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>south_east</td>\n",
       "      <td>1912.08.11</td>\n",
       "      <td>12:19:04 AM</td>\n",
       "      <td>30.0</td>\n",
       "      <td>4.9</td>\n",
       "      <td>27.2</td>\n",
       "      <td>40.6</td>\n",
       "      <td>4.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>south_west</td>\n",
       "      <td>1912.08.11</td>\n",
       "      <td>12:20:00 AM</td>\n",
       "      <td>15.0</td>\n",
       "      <td>4.5</td>\n",
       "      <td>27.1</td>\n",
       "      <td>40.6</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           方向          日期           时间    深度   烈度    经度    维度   距离\n",
       "0  south_east  1912.08.09  12:29:00 AM  16.0  6.7  27.2  40.6  4.3\n",
       "1  south_west  1912.08.10  12:23:00 AM  15.0  6.0  27.1  40.6  2.0\n",
       "2  south_west  1912.08.10  12:30:00 AM  15.0  5.2  27.1  40.6  2.0\n",
       "3  south_east  1912.08.11  12:19:04 AM  30.0  4.9  27.2  40.6  4.3\n",
       "4  south_west  1912.08.11  12:20:00 AM  15.0  4.5  27.1  40.6  2.0"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_result = result.unstack().stack(0)[(~(result.unstack().stack(0)=='-')).any(1)].reset_index()\n",
    "df_result.columns.name=None\n",
    "df_result = df_result.sort_index(axis=1).astype({'深度':'float64','烈度':'float64','距离':'float64'})\n",
    "df_result.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "方向     object\n",
       "日期     object\n",
       "时间     object\n",
       "深度    float64\n",
       "烈度    float64\n",
       "经度    float64\n",
       "维度    float64\n",
       "距离    float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_result.astype({'深度':'float64','烈度':'float64','距离':'float64'},copy=False).dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.equals(df_result)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 第5章：练习一"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (a)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "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>Company</th>\n",
       "      <th>Name</th>\n",
       "      <th>Age</th>\n",
       "      <th>Height</th>\n",
       "      <th>Weight</th>\n",
       "      <th>Salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>a1</td>\n",
       "      <td>47</td>\n",
       "      <td>188</td>\n",
       "      <td>63.7</td>\n",
       "      <td>25819</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A</td>\n",
       "      <td>a3</td>\n",
       "      <td>39</td>\n",
       "      <td>172</td>\n",
       "      <td>55.9</td>\n",
       "      <td>21983</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A</td>\n",
       "      <td>a4</td>\n",
       "      <td>43</td>\n",
       "      <td>158</td>\n",
       "      <td>62.5</td>\n",
       "      <td>21755</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A</td>\n",
       "      <td>a6</td>\n",
       "      <td>42</td>\n",
       "      <td>182</td>\n",
       "      <td>76.9</td>\n",
       "      <td>17354</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A</td>\n",
       "      <td>a7</td>\n",
       "      <td>49</td>\n",
       "      <td>171</td>\n",
       "      <td>94.6</td>\n",
       "      <td>6177</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Company Name  Age  Height  Weight  Salary\n",
       "0       A   a1   47     188    63.7   25819\n",
       "1       A   a3   39     172    55.9   21983\n",
       "2       A   a4   43     158    62.5   21755\n",
       "3       A   a6   42     182    76.9   17354\n",
       "4       A   a7   49     171    94.6    6177"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = pd.read_csv('data/Employee1.csv')\n",
    "df2 = pd.read_csv('data/Employee2.csv')\n",
    "df1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "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>Company</th>\n",
       "      <th>Name</th>\n",
       "      <th>Age</th>\n",
       "      <th>Height</th>\n",
       "      <th>Weight</th>\n",
       "      <th>Salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>a1</td>\n",
       "      <td>30</td>\n",
       "      <td>156</td>\n",
       "      <td>91.2</td>\n",
       "      <td>28133</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A</td>\n",
       "      <td>a2</td>\n",
       "      <td>50</td>\n",
       "      <td>190</td>\n",
       "      <td>83.4</td>\n",
       "      <td>6673</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A</td>\n",
       "      <td>a3</td>\n",
       "      <td>34</td>\n",
       "      <td>168</td>\n",
       "      <td>96.6</td>\n",
       "      <td>16503</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A</td>\n",
       "      <td>a5</td>\n",
       "      <td>51</td>\n",
       "      <td>176</td>\n",
       "      <td>97.2</td>\n",
       "      <td>23294</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A</td>\n",
       "      <td>a6</td>\n",
       "      <td>37</td>\n",
       "      <td>183</td>\n",
       "      <td>93.2</td>\n",
       "      <td>19256</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Company Name  Age  Height  Weight  Salary\n",
       "0       A   a1   30     156    91.2   28133\n",
       "1       A   a2   50     190    83.4    6673\n",
       "2       A   a3   34     168    96.6   16503\n",
       "3       A   a5   51     176    97.2   23294\n",
       "4       A   a6   37     183    93.2   19256"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['b7',\n",
       " 'e8',\n",
       " 'd10',\n",
       " 'c12',\n",
       " 'b15',\n",
       " 'b3',\n",
       " 'a1',\n",
       " 'd5',\n",
       " 'c13',\n",
       " 'e10',\n",
       " 'c3',\n",
       " 'a3',\n",
       " 'b1',\n",
       " 'c10',\n",
       " 'a6',\n",
       " 'e11']"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "L = list(set(df1['Name']).intersection(set(df2['Name'])))\n",
    "L"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (b)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "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>Company</th>\n",
       "      <th>Age</th>\n",
       "      <th>Height</th>\n",
       "      <th>Weight</th>\n",
       "      <th>Salary</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Name</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>a4</th>\n",
       "      <td>A</td>\n",
       "      <td>43</td>\n",
       "      <td>158</td>\n",
       "      <td>62.5</td>\n",
       "      <td>21755</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>a7</th>\n",
       "      <td>A</td>\n",
       "      <td>49</td>\n",
       "      <td>171</td>\n",
       "      <td>94.6</td>\n",
       "      <td>6177</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>a8</th>\n",
       "      <td>A</td>\n",
       "      <td>51</td>\n",
       "      <td>168</td>\n",
       "      <td>89.5</td>\n",
       "      <td>3246</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>a9</th>\n",
       "      <td>A</td>\n",
       "      <td>36</td>\n",
       "      <td>186</td>\n",
       "      <td>62.8</td>\n",
       "      <td>3569</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>a13</th>\n",
       "      <td>A</td>\n",
       "      <td>58</td>\n",
       "      <td>190</td>\n",
       "      <td>75.9</td>\n",
       "      <td>21854</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Company  Age  Height  Weight  Salary\n",
       "Name                                     \n",
       "a4         A   43     158    62.5   21755\n",
       "a7         A   49     171    94.6    6177\n",
       "a8         A   51     168    89.5    3246\n",
       "a9         A   36     186    62.8    3569\n",
       "a13        A   58     190    75.9   21854"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_b1 = df1[~df1['Name'].isin(L)]\n",
    "df_b2 = df2[~df2['Name'].isin(L)]\n",
    "df_b = pd.concat([df_b1,df_b2]).set_index('Name')\n",
    "df_b.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (c)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "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>Age</th>\n",
       "      <th>Height</th>\n",
       "      <th>Weight</th>\n",
       "      <th>Salary</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Company</th>\n",
       "      <th>Number</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">A</th>\n",
       "      <th>1</th>\n",
       "      <td>47.0</td>\n",
       "      <td>188.0</td>\n",
       "      <td>91.2</td>\n",
       "      <td>25819.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>50.0</td>\n",
       "      <td>190.0</td>\n",
       "      <td>83.4</td>\n",
       "      <td>6673.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>39.0</td>\n",
       "      <td>172.0</td>\n",
       "      <td>96.6</td>\n",
       "      <td>16503.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>43.0</td>\n",
       "      <td>158.0</td>\n",
       "      <td>62.5</td>\n",
       "      <td>21755.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>51.0</td>\n",
       "      <td>176.0</td>\n",
       "      <td>97.2</td>\n",
       "      <td>23294.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">E</th>\n",
       "      <th>12</th>\n",
       "      <td>54.0</td>\n",
       "      <td>157.0</td>\n",
       "      <td>79.4</td>\n",
       "      <td>18490.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>57.0</td>\n",
       "      <td>180.0</td>\n",
       "      <td>54.8</td>\n",
       "      <td>26837.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>39.0</td>\n",
       "      <td>163.0</td>\n",
       "      <td>83.0</td>\n",
       "      <td>20554.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>80 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 Age  Height  Weight   Salary\n",
       "Company Number                               \n",
       "A       1       47.0   188.0    91.2  25819.0\n",
       "        2       50.0   190.0    83.4   6673.0\n",
       "        3       39.0   172.0    96.6  16503.0\n",
       "        4       43.0   158.0    62.5  21755.0\n",
       "        5       51.0   176.0    97.2  23294.0\n",
       "...              ...     ...     ...      ...\n",
       "E       12      54.0   157.0    79.4  18490.0\n",
       "        13      57.0   180.0    54.8  26837.0\n",
       "        14      39.0   163.0    83.0  20554.0\n",
       "        15       NaN     NaN     NaN      NaN\n",
       "        16       NaN     NaN     NaN      NaN\n",
       "\n",
       "[80 rows x 4 columns]"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = pd.read_csv('data/Employee1.csv')\n",
    "df2 = pd.read_csv('data/Employee2.csv')\n",
    "df1['重复'] = ['Y_1' if df1.loc[i,'Name'] in L else 'N' for i in range(df1.shape[0])]\n",
    "df2['重复'] = ['Y_2' if df2.loc[i,'Name'] in L else 'N' for i in range(df2.shape[0])]\n",
    "df1 = df1.set_index(['Name','重复'])\n",
    "df2 = df2.set_index(['Name','重复'])\n",
    "df_c = pd.concat([df1,df2])\n",
    "result = pd.DataFrame({'Company':[],'Name':[],'Age':[],'Height':[],'Weight':[],'Salary':[]})\n",
    "group = df_c.groupby(['Company','重复'])\n",
    "for i in L:\n",
    "    first = group.get_group((i[0].upper(),'Y_1')).reset_index(level=1).loc[i,:][-4:]\n",
    "    second = group.get_group((i[0].upper(),'Y_2')).reset_index(level=1).loc[i,:][-4:]\n",
    "    mean = group.get_group((i[0].upper(),'N')).reset_index(level=1).mean()\n",
    "    final = [i[0].upper(),i]\n",
    "    for j in range(4):\n",
    "        final.append(first[j] if abs(first[j]-mean[j])<abs(second[j]-mean[j]) else second[j])\n",
    "    result = pd.concat([result,pd.DataFrame({result.columns.tolist()[k]:[final[k]] for k in range(6)})])\n",
    "result = pd.concat([result.set_index('Name'),df_b])\n",
    "for i in list('abcde'):\n",
    "    for j in range(1,17):\n",
    "        item = i+str(j)\n",
    "        if item not in result.index:\n",
    "            result = pd.concat([result,pd.DataFrame({'Company':[i.upper()],'Name':[item]\n",
    "                 ,'Age':[np.nan],'Height':[np.nan],'Weight':[np.nan],'Salary':[np.nan]}).set_index('Name')])\n",
    "result['Number'] = [int(i[1:]) for i in result.index]\n",
    "result.reset_index().drop(columns='Name').set_index(['Company','Number']).sort_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 第5章：练习二"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (a)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>云计算应用与开发</td>\n",
       "      <td>专业选修课</td>\n",
       "      <td>3</td>\n",
       "      <td>96.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>社会计算</td>\n",
       "      <td>专业选修课</td>\n",
       "      <td>3</td>\n",
       "      <td>78.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>深度学习</td>\n",
       "      <td>专业选修课</td>\n",
       "      <td>3</td>\n",
       "      <td>75.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>人工智能导论</td>\n",
       "      <td>专业必修课</td>\n",
       "      <td>3</td>\n",
       "      <td>84.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>数据结构与算法</td>\n",
       "      <td>学科基础课</td>\n",
       "      <td>5</td>\n",
       "      <td>82.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       课程名字   课程类别  学分    分数\n",
       "1  云计算应用与开发  专业选修课   3  96.0\n",
       "2      社会计算  专业选修课   3  78.0\n",
       "3      深度学习  专业选修课   3  75.0\n",
       "4    人工智能导论  专业必修课   3  84.0\n",
       "6   数据结构与算法  学科基础课   5  82.0"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = pd.read_csv('data/Course1.csv')\n",
    "df2 = pd.read_csv('data/Course2.csv')\n",
    "df_a11,df_a12,df_a21,df_a22 =0,0,0,0\n",
    "df_a11= df1.query('课程类别 in [\"学科基础课\",\"专业必修课\",\"专业选修课\"]')\n",
    "df_a12= df1.query('课程类别 not in [\"学科基础课\",\"专业必修课\",\"专业选修课\"]')\n",
    "df_a21= df2.query('课程类别 in [\"学科基础课\",\"专业必修课\",\"专业选修课\"]')\n",
    "df_a22= df2.query('课程类别 not in [\"学科基础课\",\"专业必修课\",\"专业选修课\"]')\n",
    "df_a11.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (b)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [课程名字, 课程类别, 学分, 分数]\n",
       "Index: []"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "special = pd.concat([df_a11,df_a21])\n",
    "common = pd.concat([df_a12,df_a22])\n",
    "special.query('课程类别 not in [\"学科基础课\",\"专业必修课\",\"专业选修课\"]')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [课程名字, 课程类别, 学分, 分数]\n",
       "Index: []"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "common.query('课程类别 in [\"学科基础课\",\"专业必修课\",\"专业选修课\"]')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (c)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(True, True)"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.concat([df1,df2])\n",
    "special2 = df.query('课程类别 in [\"学科基础课\",\"专业必修课\",\"专业选修课\"]')\n",
    "common2 = df.query('课程类别 not in [\"学科基础课\",\"专业必修课\",\"专业选修课\"]')\n",
    "(special.equals(special2),common.equals(common2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (d)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "课程名字    False\n",
       "课程类别    False\n",
       "学分      False\n",
       "分数      False\n",
       "dtype: bool"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['分数'] = df.groupby('课程类别').transform(lambda x: x.fillna(x.mean()))['分数']\n",
    "df.isnull().all()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>思想道德修养与法律基础</td>\n",
       "      <td>思政类</td>\n",
       "      <td>3</td>\n",
       "      <td>89.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>中国近代史纲要</td>\n",
       "      <td>思政类</td>\n",
       "      <td>3</td>\n",
       "      <td>97.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>网球（初）</td>\n",
       "      <td>体育类</td>\n",
       "      <td>1</td>\n",
       "      <td>81.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>极端性气候与陆地生态系统</td>\n",
       "      <td>公共任意选修类</td>\n",
       "      <td>2</td>\n",
       "      <td>78.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>游泳（初）</td>\n",
       "      <td>体育类</td>\n",
       "      <td>1</td>\n",
       "      <td>75.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            课程名字     课程类别  学分    分数\n",
       "0    思想道德修养与法律基础      思政类   3  89.0\n",
       "5        中国近代史纲要      思政类   3  97.0\n",
       "8          网球（初）      体育类   1  81.0\n",
       "10  极端性气候与陆地生态系统  公共任意选修类   2  78.0\n",
       "13         游泳（初）      体育类   1  75.0"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "special3 = df.query('课程类别 in [\"学科基础课\",\"专业必修课\",\"专业选修课\"]')\n",
    "common3 = df.query('课程类别 not in [\"学科基础课\",\"专业必修课\",\"专业选修课\"]')\n",
    "common3.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 第6章：练习一\n",
    "### (a)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "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>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>not_NaN</td>\n",
       "      <td>0.922</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>not_NaN</td>\n",
       "      <td>0.700</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>not_NaN</td>\n",
       "      <td>0.503</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>not_NaN</td>\n",
       "      <td>0.938</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>not_NaN</td>\n",
       "      <td>0.952</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         A      B     C\n",
       "0  not_NaN  0.922     4\n",
       "1  not_NaN  0.700  <NA>\n",
       "2  not_NaN  0.503     8\n",
       "3  not_NaN  0.938     4\n",
       "4  not_NaN  0.952    10"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('data/Missing_data_one.csv').convert_dtypes()\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "A     string\n",
       "B    float64\n",
       "C      Int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "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>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>not_NaN</td>\n",
       "      <td>0.700</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>not_NaN</td>\n",
       "      <td>0.972</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>not_NaN</td>\n",
       "      <td>0.736</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>not_NaN</td>\n",
       "      <td>0.684</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>not_NaN</td>\n",
       "      <td>0.913</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          A      B     C\n",
       "1   not_NaN  0.700  <NA>\n",
       "5   not_NaN  0.972  <NA>\n",
       "11  not_NaN  0.736  <NA>\n",
       "19  not_NaN  0.684  <NA>\n",
       "21  not_NaN  0.913  <NA>"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['C'].isna()]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (b)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "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>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>not_NaN</td>\n",
       "      <td>0.922</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>not_NaN</td>\n",
       "      <td>0.700</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>not_NaN</td>\n",
       "      <td>0.503</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>0.938</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>0.952</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         A      B     C\n",
       "0  not_NaN  0.922     4\n",
       "1  not_NaN  0.700  <NA>\n",
       "2  not_NaN  0.503     8\n",
       "3      NaN  0.938     4\n",
       "4      NaN  0.952    10"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('data/Missing_data_one.csv').convert_dtypes()\n",
    "total_b = df['B'].sum()\n",
    "min_b = df['B'].min()\n",
    "df['A'] = pd.Series(list(zip(df['A'].values\n",
    "                    ,df['B'].values))).apply(lambda x:x[0] if np.random.rand()>0.25*x[1]/min_b else np.nan)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 第6章：练习二"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (a)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>A</td>\n",
       "      <td>157.50</td>\n",
       "      <td>NaN</td>\n",
       "      <td>47.0</td>\n",
       "      <td>15905.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>B</td>\n",
       "      <td>202.00</td>\n",
       "      <td>91.80</td>\n",
       "      <td>25.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>C</td>\n",
       "      <td>169.09</td>\n",
       "      <td>62.18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>A</td>\n",
       "      <td>166.61</td>\n",
       "      <td>59.95</td>\n",
       "      <td>77.0</td>\n",
       "      <td>5434.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>B</td>\n",
       "      <td>185.19</td>\n",
       "      <td>NaN</td>\n",
       "      <td>62.0</td>\n",
       "      <td>4242.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   编号 地区      身高     体重    年龄       工资\n",
       "0   1  A  157.50    NaN  47.0  15905.0\n",
       "1   2  B  202.00  91.80  25.0      NaN\n",
       "2   3  C  169.09  62.18   NaN      NaN\n",
       "3   4  A  166.61  59.95  77.0   5434.0\n",
       "4   5  B  185.19    NaN  62.0   4242.0"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('data/Missing_data_two.csv')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "编号    0.000000\n",
       "地区    0.000000\n",
       "身高    0.000000\n",
       "体重    0.222222\n",
       "年龄    0.250000\n",
       "工资    0.222222\n",
       "dtype: float64"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.isna().sum()/df.shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>A</td>\n",
       "      <td>157.50</td>\n",
       "      <td>NaN</td>\n",
       "      <td>47.0</td>\n",
       "      <td>15905.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>B</td>\n",
       "      <td>202.00</td>\n",
       "      <td>91.80</td>\n",
       "      <td>25.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>A</td>\n",
       "      <td>166.61</td>\n",
       "      <td>59.95</td>\n",
       "      <td>77.0</td>\n",
       "      <td>5434.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>B</td>\n",
       "      <td>185.19</td>\n",
       "      <td>NaN</td>\n",
       "      <td>62.0</td>\n",
       "      <td>4242.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>A</td>\n",
       "      <td>187.13</td>\n",
       "      <td>78.42</td>\n",
       "      <td>55.0</td>\n",
       "      <td>13959.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   编号 地区      身高     体重    年龄       工资\n",
       "0   1  A  157.50    NaN  47.0  15905.0\n",
       "1   2  B  202.00  91.80  25.0      NaN\n",
       "3   4  A  166.61  59.95  77.0   5434.0\n",
       "4   5  B  185.19    NaN  62.0   4242.0\n",
       "5   6  A  187.13  78.42  55.0  13959.0"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_not2na = df[df.iloc[:,-3:].isna().sum(1)<=1]\n",
    "df_not2na.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (b)\n",
    "#### 分地区，用排序后的身高信息进行线性插值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>A</td>\n",
       "      <td>157.50</td>\n",
       "      <td>53.58</td>\n",
       "      <td>47.0</td>\n",
       "      <td>15905.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>B</td>\n",
       "      <td>202.00</td>\n",
       "      <td>91.80</td>\n",
       "      <td>25.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>C</td>\n",
       "      <td>169.09</td>\n",
       "      <td>62.18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>A</td>\n",
       "      <td>166.61</td>\n",
       "      <td>59.95</td>\n",
       "      <td>77.0</td>\n",
       "      <td>5434.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>B</td>\n",
       "      <td>185.19</td>\n",
       "      <td>81.75</td>\n",
       "      <td>62.0</td>\n",
       "      <td>4242.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   编号 地区      身高     体重    年龄       工资\n",
       "0   1  A  157.50  53.58  47.0  15905.0\n",
       "1   2  B  202.00  91.80  25.0      NaN\n",
       "2   3  C  169.09  62.18   NaN      NaN\n",
       "3   4  A  166.61  59.95  77.0   5434.0\n",
       "4   5  B  185.19  81.75  62.0   4242.0"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_method_1 = df.copy()\n",
    "for name,group in df_method_1.groupby('地区'):\n",
    "    df_method_1.loc[group.index,'体重'] = group[['身高','体重']].sort_values(by='身高').interpolate()['体重']\n",
    "df_method_1['体重'] = df_method_1['体重'].round(decimals=2)\n",
    "df_method_1.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 第7章：练习一\n",
    "### (a)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "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",
       "    </tr>\n",
       "    <tr>\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>1</th>\n",
       "      <td>aesfd</td>\n",
       "      <td>2</td>\n",
       "      <td>男</td>\n",
       "      <td>1942</td>\n",
       "      <td>8</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>fasefa</td>\n",
       "      <td>5</td>\n",
       "      <td>女</td>\n",
       "      <td>1985</td>\n",
       "      <td>10</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>aeagd</td>\n",
       "      <td>4</td>\n",
       "      <td>女</td>\n",
       "      <td>1946</td>\n",
       "      <td>10</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>aef</td>\n",
       "      <td>4</td>\n",
       "      <td>男</td>\n",
       "      <td>1999</td>\n",
       "      <td>5</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>eaf</td>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>2010</td>\n",
       "      <td>6</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          姓名 国籍 性别   出生年 出生月 出生日\n",
       "人员编号                            \n",
       "1      aesfd  2  男  1942   8  10\n",
       "2     fasefa  5  女  1985  10   4\n",
       "3      aeagd  4  女  1946  10  15\n",
       "4        aef  4  男  1999   5  13\n",
       "5        eaf  1  女  2010   6  24"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('data/String_data_one.csv',index_col='人员编号').astype('str')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>人员编号</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>aesfd:2国人，性别男，生于1942年8月10日</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>fasefa:5国人，性别女，生于1985年10月4日</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>aeagd:4国人，性别女，生于1946年10月15日</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>aef:4国人，性别男，生于1999年5月13日</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>eaf:1国人，性别女，生于2010年6月24日</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               ID\n",
       "人员编号                             \n",
       "1      aesfd:2国人，性别男，生于1942年8月10日\n",
       "2     fasefa:5国人，性别女，生于1985年10月4日\n",
       "3     aeagd:4国人，性别女，生于1946年10月15日\n",
       "4        aef:4国人，性别男，生于1999年5月13日\n",
       "5        eaf:1国人，性别女，生于2010年6月24日"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(df['姓名']+':'+df['国籍']+'国人，性别'\n",
    "          +df['性别']+'，生于'\n",
    "          +df['出生年']+'年'\n",
    "          +df['出生月']+'月'+df['出生日']+'日').to_frame().rename(columns={0:'ID'}).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (b)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>人员编号</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>aesfd:2国人，性别男，生于一九四二年八月十日</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>fasefa:5国人，性别女，生于一九八五年十月四日</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>aeagd:4国人，性别女，生于一九四六年十月十五日</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>aef:4国人，性别男，生于一九九九年五月十三日</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>eaf:1国人，性别女，生于二零一零年六月二十四日</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              ID\n",
       "人员编号                            \n",
       "1      aesfd:2国人，性别男，生于一九四二年八月十日\n",
       "2     fasefa:5国人，性别女，生于一九八五年十月四日\n",
       "3     aeagd:4国人，性别女，生于一九四六年十月十五日\n",
       "4       aef:4国人，性别男，生于一九九九年五月十三日\n",
       "5      eaf:1国人，性别女，生于二零一零年六月二十四日"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "L_year = list('零一二三四五六七八九')\n",
    "L_one = [s.strip() for s in list('  二三四五六七八九')]\n",
    "L_two = [s.strip() for s in list(' 一二三四五六七八九')]\n",
    "df_new = (df['姓名']+':'+df['国籍']+'国人，性别'+df['性别']+'，生于'\n",
    "          +df['出生年'].str.replace(r'\\d',lambda x:L_year[int(x.group(0))])+'年'\n",
    "          +df['出生月'].apply(lambda x:x if len(x)==2 else '0'+x)\\\n",
    "                      .str.replace(r'(?P<one>[\\d])(?P<two>\\d?)',lambda x:L_one[int(x.group('one'))]\n",
    "                      +bool(int(x.group('one')))*'十'+L_two[int(x.group('two'))])+'月'\n",
    "          +df['出生日'].apply(lambda x:x if len(x)==2 else '0'+x)\\\n",
    "                      .str.replace(r'(?P<one>[\\d])(?P<two>\\d?)',lambda x:L_one[int(x.group('one'))]\n",
    "                      +bool(int(x.group('one')))*'十'+L_two[int(x.group('two'))])+'日')\\\n",
    "          .to_frame().rename(columns={0:'ID'})\n",
    "df_new.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (c)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "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",
       "    </tr>\n",
       "    <tr>\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>1</th>\n",
       "      <td>aesfd</td>\n",
       "      <td>2</td>\n",
       "      <td>男</td>\n",
       "      <td>1942</td>\n",
       "      <td>8</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>fasefa</td>\n",
       "      <td>5</td>\n",
       "      <td>女</td>\n",
       "      <td>1985</td>\n",
       "      <td>10</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>aeagd</td>\n",
       "      <td>4</td>\n",
       "      <td>女</td>\n",
       "      <td>1946</td>\n",
       "      <td>10</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>aef</td>\n",
       "      <td>4</td>\n",
       "      <td>男</td>\n",
       "      <td>1999</td>\n",
       "      <td>5</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>eaf</td>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>2010</td>\n",
       "      <td>6</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          姓名 国籍 性别   出生年 出生月 出生日\n",
       "人员编号                            \n",
       "1      aesfd  2  男  1942   8  10\n",
       "2     fasefa  5  女  1985  10   4\n",
       "3      aeagd  4  女  1946  10  15\n",
       "4        aef  4  男  1999   5  13\n",
       "5        eaf  1  女  2010   6  24"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dic_year = {i[0]:i[1] for i in zip(list('零一二三四五六七八九'),list('0123456789'))}\n",
    "dic_two = {i[0]:i[1] for i in zip(list('十一二三四五六七八九'),list('0123456789'))}\n",
    "dic_one = {'十':'1','二十':'2','三十':'3',None:''}\n",
    "df_res = df_new['ID'].str.extract(r'(?P<姓名>[a-zA-Z]+):(?P<国籍>[\\d])国人，性别(?P<性别>[\\w])，生于(?P<出生年>[\\w]{4})年(?P<出生月>[\\w]+)月(?P<出生日>[\\w]+)日')\n",
    "df_res['出生年'] = df_res['出生年'].str.replace(r'(\\w)+',lambda x:''.join([dic_year[x.group(0)[i]] for i in range(4)]))\n",
    "df_res['出生月'] = df_res['出生月'].str.replace(r'(?P<one>\\w?十)?(?P<two>[\\w])',lambda x:dic_one[x.group('one')]+dic_two[x.group('two')]).str.replace(r'0','10')\n",
    "df_res['出生日'] = df_res['出生日'].str.replace(r'(?P<one>\\w?十)?(?P<two>[\\w])',lambda x:dic_one[x.group('one')]+dic_two[x.group('two')]).str.replace(r'^0','10')\n",
    "df_res.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_res.equals(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 第7章：练习二\n",
    "#### 看题目可能会觉得疑惑，为什么会有(b)和(c)两道那么水的题，但是其中的坑只有做过/实际数据处理中遇到过才知道"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (a)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "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>col1</th>\n",
       "      <th>col2</th>\n",
       "      <th>col3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>鄂尔多斯市第2例确诊患者治愈出院</td>\n",
       "      <td>19</td>\n",
       "      <td>363.6923</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>云南新增2例，累计124例</td>\n",
       "      <td>-67</td>\n",
       "      <td>-152.281</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>武汉协和医院14名感染医护出院</td>\n",
       "      <td>-86</td>\n",
       "      <td>325.6221</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>山东新增9例，累计307例</td>\n",
       "      <td>-74</td>\n",
       "      <td>-204.9313</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>上海开学日期延至3月</td>\n",
       "      <td>-95</td>\n",
       "      <td>4.05</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               col1 col2     col3  \n",
       "0  鄂尔多斯市第2例确诊患者治愈出院   19   363.6923\n",
       "1     云南新增2例，累计124例  -67   -152.281\n",
       "2   武汉协和医院14名感染医护出院  -86   325.6221\n",
       "3     山东新增9例，累计307例  -74  -204.9313\n",
       "4        上海开学日期延至3月  -95       4.05"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('data/String_data_two.csv')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "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>col1</th>\n",
       "      <th>col2</th>\n",
       "      <th>col3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>上海开学日期延至3月</td>\n",
       "      <td>-95</td>\n",
       "      <td>4.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>北京新增25例确诊病例，累计确诊253例</td>\n",
       "      <td>-4</td>\n",
       "      <td>-289.1719</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>上海新增10例，累计243例</td>\n",
       "      <td>2</td>\n",
       "      <td>-73.7105</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>上海新增14例累计233例</td>\n",
       "      <td>-55</td>\n",
       "      <td>-83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>上海新增14例累计233例</td>\n",
       "      <td>-88</td>\n",
       "      <td>-99</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    col1 col2     col3  \n",
       "4             上海开学日期延至3月  -95       4.05\n",
       "5   北京新增25例确诊病例，累计确诊253例   -4  -289.1719\n",
       "6         上海新增10例，累计243例    2   -73.7105\n",
       "36         上海新增14例累计233例  -55        -83\n",
       "40         上海新增14例累计233例  -88        -99"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['col1'].str.contains(r'[北京]{2}|[上海]{2}')].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (b)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "309    0-\n",
       "396    9`\n",
       "485    /7\n",
       "Name: col2, dtype: object"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#df['col2'].mean() #报错\n",
    "df['col2'][~(df['col2'].str.replace(r'-?\\d+','True')=='True')] #这三行有问题"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "-0.984"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[[309,396,485],'col2'] = [0,9,7]\n",
    "df['col2'].astype('int').mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (c)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['col1', 'col2', 'col3  '], dtype='object')"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#df['col3'].mean() #报错\n",
    "#df['col3'] #报错\n",
    "df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['col1', 'col2', 'col3'], dtype='object')"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns = df.columns.str.strip()\n",
    "df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     363.6923\n",
       "1     -152.281\n",
       "2     325.6221\n",
       "3    -204.9313\n",
       "4         4.05\n",
       "Name: col3, dtype: object"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['col3'].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "28      355`.3567\n",
       "37             -5\n",
       "73              1\n",
       "122    9056.\\2253\n",
       "332    3534.6554{\n",
       "370             7\n",
       "Name: col3, dtype: object"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#df['col3'].mean() #报错\n",
    "df['col3'][~(df['col3'].str.replace(r'-?\\d+\\.?\\d+','True')=='True')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "24.707485"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[[28,122,332],'col3'] = [355.3567,9056.2253, 3534.6554]\n",
    "df['col3'].astype('float').mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 第8章 练习一"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (a)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "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": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('data/Earthquake.csv')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "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",
       "    </tr>\n",
       "    <tr>\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>Ⅰ</th>\n",
       "      <td>2009.09.09</td>\n",
       "      <td>12:54:13 AM</td>\n",
       "      <td>42.42</td>\n",
       "      <td>43.03</td>\n",
       "      <td>north_east</td>\n",
       "      <td>95.4</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ⅰ</th>\n",
       "      <td>1997.06.16</td>\n",
       "      <td>12:18:04 AM</td>\n",
       "      <td>37.92</td>\n",
       "      <td>29.17</td>\n",
       "      <td>north_east</td>\n",
       "      <td>3.2</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ⅰ</th>\n",
       "      <td>2011.10.25</td>\n",
       "      <td>12:29:45 AM</td>\n",
       "      <td>38.96</td>\n",
       "      <td>43.64</td>\n",
       "      <td>south_east</td>\n",
       "      <td>1.6</td>\n",
       "      <td>3.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ⅰ</th>\n",
       "      <td>1995.07.23</td>\n",
       "      <td>12:05:04 AM</td>\n",
       "      <td>37.61</td>\n",
       "      <td>29.29</td>\n",
       "      <td>north_east</td>\n",
       "      <td>3.2</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ⅰ</th>\n",
       "      <td>2013.06.10</td>\n",
       "      <td>12:39:19 AM</td>\n",
       "      <td>38.53</td>\n",
       "      <td>43.85</td>\n",
       "      <td>south_east</td>\n",
       "      <td>1.6</td>\n",
       "      <td>3.7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            日期           时间     维度     经度          方向    距离   烈度\n",
       "深度                                                              \n",
       "Ⅰ   2009.09.09  12:54:13 AM  42.42  43.03  north_east  95.4  0.0\n",
       "Ⅰ   1997.06.16  12:18:04 AM  37.92  29.17  north_east   3.2  0.0\n",
       "Ⅰ   2011.10.25  12:29:45 AM  38.96  43.64  south_east   1.6  3.9\n",
       "Ⅰ   1995.07.23  12:05:04 AM  37.61  29.29  north_east   3.2  0.0\n",
       "Ⅰ   2013.06.10  12:39:19 AM  38.53  43.85  south_east   1.6  3.7"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_a = df.copy()\n",
    "df_a['深度'] = pd.cut(df_a['深度'], [-1e-10,5,10,15,20,30,50,np.inf],labels=['Ⅰ','Ⅱ','Ⅲ','Ⅳ','Ⅴ','Ⅵ','Ⅶ'])\n",
    "df_a.set_index('深度').sort_index().head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (b)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "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",
       "    </tr>\n",
       "    <tr>\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=\"5\" valign=\"top\">Ⅰ</th>\n",
       "      <th>Ⅰ</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ⅰ</th>\n",
       "      <td>2000.02.07</td>\n",
       "      <td>12:11:45 AM</td>\n",
       "      <td>40.05</td>\n",
       "      <td>34.07</td>\n",
       "      <td>south_east</td>\n",
       "      <td>0.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ⅰ</th>\n",
       "      <td>1971.05.20</td>\n",
       "      <td>12:08:46 AM</td>\n",
       "      <td>37.72</td>\n",
       "      <td>30.00</td>\n",
       "      <td>north_east</td>\n",
       "      <td>0.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ⅰ</th>\n",
       "      <td>1985.01.28</td>\n",
       "      <td>12:20:56 AM</td>\n",
       "      <td>38.85</td>\n",
       "      <td>29.06</td>\n",
       "      <td>north_east</td>\n",
       "      <td>0.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ⅰ</th>\n",
       "      <td>1990.07.05</td>\n",
       "      <td>12:43:04 AM</td>\n",
       "      <td>37.87</td>\n",
       "      <td>29.18</td>\n",
       "      <td>east</td>\n",
       "      <td>0.1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               日期           时间     维度     经度          方向   距离\n",
       "深度 烈度                                                        \n",
       "Ⅰ  Ⅰ   1978.05.07  12:41:37 AM  38.58  27.61  south_west  0.1\n",
       "   Ⅰ   2000.02.07  12:11:45 AM  40.05  34.07  south_east  0.1\n",
       "   Ⅰ   1971.05.20  12:08:46 AM  37.72  30.00  north_east  0.1\n",
       "   Ⅰ   1985.01.28  12:20:56 AM  38.85  29.06  north_east  0.1\n",
       "   Ⅰ   1990.07.05  12:43:04 AM  37.87  29.18        east  0.1"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_a['烈度'] = pd.cut(df_a['烈度'], [-1e-10,3,4,5,np.inf],labels=['Ⅰ','Ⅱ','Ⅲ','Ⅳ'])\n",
    "df_a.set_index(['深度','烈度']).sort_index().head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 第8章 练习二"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "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>col_0</th>\n",
       "      <th>d</th>\n",
       "      <th>e</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>row_0</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "col_0  d  e\n",
       "row_0      \n",
       "a      1  0\n",
       "b      0  1"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "foo = pd.Categorical(['a', 'b'], categories=['a', 'b', 'c'])\n",
    "bar = pd.Categorical(['d', 'e'], categories=['d', 'e', 'f'])\n",
    "pd.crosstab(foo, bar)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "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>2st var</th>\n",
       "      <th>d</th>\n",
       "      <th>e</th>\n",
       "      <th>f</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1nd var</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "2st var  d  e  f\n",
       "1nd var         \n",
       "a        1  0  0\n",
       "b        0  1  0\n",
       "c        0  0  0"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def my_crosstab(foo,bar):\n",
    "    num = len(foo)\n",
    "    s1 = pd.Series([i for i in list(foo.categories.union(set(foo)))],name='1nd var')\n",
    "    s2 = [i for i in list(bar.categories.union(set(bar)))]\n",
    "    df = pd.DataFrame({i:[0]*len(s1) for i in s2},index=s1)\n",
    "    for i in range(num):\n",
    "        df.at[foo[i],bar[i]] += 1\n",
    "    return df.rename_axis('2st var',axis=1)\n",
    "my_crosstab(foo,bar)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 第9章：练习一"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2017-02-17</td>\n",
       "      <td>2154</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017-02-18</td>\n",
       "      <td>2095</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017-02-19</td>\n",
       "      <td>3459</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017-02-20</td>\n",
       "      <td>2198</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017-02-21</td>\n",
       "      <td>2413</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          日期   销售额\n",
       "0 2017-02-17  2154\n",
       "1 2017-02-18  2095\n",
       "2 2017-02-19  3459\n",
       "3 2017-02-20  2198\n",
       "4 2017-02-21  2413"
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('data/time_series_one.csv', parse_dates=['日期'])\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (a) 周日，注意dayofweek函数结果里0对应周一；6对应周日"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "6"
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['日期'].dt.dayofweek[df['销售额'].idxmax()]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (b)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>日期</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2017-02-01</th>\n",
       "      <td>31740</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-03-01</th>\n",
       "      <td>80000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-04-01</th>\n",
       "      <td>74734</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-05-01</th>\n",
       "      <td>76237</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-06-01</th>\n",
       "      <td>80750</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              销售额\n",
       "日期               \n",
       "2017-02-01  31740\n",
       "2017-03-01  80000\n",
       "2017-04-01  74734\n",
       "2017-05-01  76237\n",
       "2017-06-01  80750"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "holiday = pd.date_range(start='20170501', end='20170503').append(\n",
    "          pd.date_range(start='20171001', end='20171007')).append(\n",
    "          pd.date_range(start='20180215', end='20180221')).append(\n",
    "          pd.date_range(start='20180501', end='20180503')).append(\n",
    "          pd.date_range(start='20181001', end='20181007')).append(\n",
    "          pd.date_range(start='20190204', end='20190224')).append(\n",
    "          pd.date_range(start='20190501', end='20190503')).append(\n",
    "          pd.date_range(start='20191001', end='20191007'))\n",
    "result = df[~df['日期'].isin(holiday)].set_index('日期').resample('MS').sum()\n",
    "result.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (c)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>日期</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2017-01-01</th>\n",
       "      <td>32894</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-04-01</th>\n",
       "      <td>66692</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-07-01</th>\n",
       "      <td>69099</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-10-01</th>\n",
       "      <td>70384</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-01</th>\n",
       "      <td>74671</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              销售额\n",
       "日期               \n",
       "2017-01-01  32894\n",
       "2017-04-01  66692\n",
       "2017-07-01  69099\n",
       "2017-10-01  70384\n",
       "2018-01-01  74671"
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = df[df['日期'].dt.dayofweek.isin([5,6])].set_index('日期').resample('QS').sum()\n",
    "result.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (d) 这里结果的日期是5天里的最后一天"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "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>5天总额</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>日期</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2017-02-22</th>\n",
       "      <td>9855</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-03-01</th>\n",
       "      <td>12296</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-03-08</th>\n",
       "      <td>13323</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-03-15</th>\n",
       "      <td>13845</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-03-22</th>\n",
       "      <td>11356</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             5天总额\n",
       "日期               \n",
       "2017-02-22   9855\n",
       "2017-03-01  12296\n",
       "2017-03-08  13323\n",
       "2017-03-15  13845\n",
       "2017-03-22  11356"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_temp = df[~df['日期'].dt.dayofweek.isin([5,6])].set_index('日期').iloc[::-1]\n",
    "L_temp,date_temp = [],[0]*df_temp.shape[0]\n",
    "for i in range(df_temp.shape[0]//5):\n",
    "    L_temp.extend([i]*5)\n",
    "L_temp.extend([df_temp.shape[0]//5]*(df_temp.shape[0]-df_temp.shape[0]//5*5))\n",
    "date_temp = pd.Series([i%5==0 for i in range(df_temp.shape[0])])\n",
    "df_temp['num'] = L_temp\n",
    "result = pd.DataFrame({'5天总额':df_temp.groupby('num')['销售额'].sum().values},\n",
    "                       index=df_temp.reset_index()[date_temp]['日期']).iloc[::-1]\n",
    "result.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (e)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>318</th>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>2863.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>353</th>\n",
       "      <td>2018-02-05</td>\n",
       "      <td>2321.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>381</th>\n",
       "      <td>2018-03-05</td>\n",
       "      <td>2705.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>409</th>\n",
       "      <td>2018-04-02</td>\n",
       "      <td>2487.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>444</th>\n",
       "      <td>2018-05-07</td>\n",
       "      <td>3204.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>472</th>\n",
       "      <td>2018-06-04</td>\n",
       "      <td>2927.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>500</th>\n",
       "      <td>2018-07-02</td>\n",
       "      <td>2574.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>535</th>\n",
       "      <td>2018-08-06</td>\n",
       "      <td>2504.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>563</th>\n",
       "      <td>2018-09-03</td>\n",
       "      <td>2483.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>591</th>\n",
       "      <td>2018-10-01</td>\n",
       "      <td>2431.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>626</th>\n",
       "      <td>2018-11-05</td>\n",
       "      <td>2395.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>654</th>\n",
       "      <td>2018-12-03</td>\n",
       "      <td>2373.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            日期     销售额\n",
       "318 2018-01-01  2863.0\n",
       "353 2018-02-05  2321.0\n",
       "381 2018-03-05  2705.0\n",
       "409 2018-04-02  2487.0\n",
       "444 2018-05-07  3204.0\n",
       "472 2018-06-04  2927.0\n",
       "500 2018-07-02  2574.0\n",
       "535 2018-08-06  2504.0\n",
       "563 2018-09-03  2483.0\n",
       "591 2018-10-01  2431.0\n",
       "626 2018-11-05  2395.0\n",
       "654 2018-12-03  2373.0"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from datetime import datetime \n",
    "df_temp = df.copy()\n",
    "df_fri = df.shift(4)[df.shift(4)['日期'].dt.dayofweek==1]['销售额']\n",
    "df_mon = df.shift(-4)[df.shift(-4)['日期'].dt.dayofweek==5]['销售额']\n",
    "df_temp.loc[df_fri.index,['销售额']] = df_fri\n",
    "df_temp.loc[df_mon.index,['销售额']] = df_mon\n",
    "df_temp.loc[df_temp[df_temp['日期'].dt.year==2018]['日期'][\n",
    "        df_temp[df_temp['日期'].dt.year==2018]['日期'].apply(\n",
    "        lambda x:True if datetime.strptime(str(x).split()[0],'%Y-%m-%d').weekday() == 0 \n",
    "        and 1 <= datetime.strptime(str(x).split()[0],'%Y-%m-%d').day <= 7 else False)].index,:]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 第9章：练习二"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (a)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "日期\n",
       "2017-02-17    2154.000000\n",
       "2017-02-18    2124.500000\n",
       "2017-02-19    2569.333333\n",
       "2017-02-20    2476.500000\n",
       "2017-02-21    2463.800000\n",
       "Name: 销售额, dtype: float64"
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('data/time_series_one.csv',index_col='日期',parse_dates=['日期'])\n",
    "df['销售额'].rolling(window=50,min_periods=1).mean().head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "日期\n",
       "2017-02-17    2154.0\n",
       "2017-02-18    2154.0\n",
       "2017-02-19    3459.0\n",
       "2017-02-20    3459.0\n",
       "2017-02-21    3459.0\n",
       "Name: 销售额, dtype: float64"
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['销售额'].rolling(window=50,min_periods=1).max().head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (b)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-01-01</th>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-02</th>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-03</th>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-04</th>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-05</th>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            销售额\n",
       "2018-01-01  1.0\n",
       "2018-01-02  0.0\n",
       "2018-01-03  0.0\n",
       "2018-01-04  0.0\n",
       "2018-01-05  0.0"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def f(x):\n",
    "    if len(x) == 6:\n",
    "        return 1 if x[-1]>np.mean(x[:-1]) else 0\n",
    "    else:\n",
    "        return 0\n",
    "result_b = df.loc[pd.date_range(start='20171227',end='20181231'),:].rolling(\n",
    "                                                    window=6,min_periods=1).agg(f)[5:].head()\n",
    "result_b.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (c) 比较巧合，与(b)的结果一样"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-01-01</th>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-02</th>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-03</th>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-04</th>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-05</th>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            销售额\n",
       "2018-01-01  1.0\n",
       "2018-01-02  0.0\n",
       "2018-01-03  0.0\n",
       "2018-01-04  0.0\n",
       "2018-01-05  0.0"
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def f(x):\n",
    "    if len(x) == 8:\n",
    "        return 1 if x[-1]>np.mean(x[:-1][pd.Series([\n",
    "            False if i in [5,6] else True for i in x[:-1].index.dayofweek],index=x[:-1].index)]) else 0\n",
    "    else:\n",
    "        return 0\n",
    "result_c = df.loc[pd.date_range(start='20171225',end='20181231'),:].rolling(\n",
    "                                    window=8,min_periods=1).agg(f)[7:].head()\n",
    "result_c.head()"
   ]
  }
 ],
 "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": 4
}