04-pandas-102.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# 成為資料分析師 | Python 與資料科學應用\n",
    "\n",
    "> Pandas 102：處理表格式資料的 Python 套件\n",
    "\n",
    "## 郭耀仁"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 大綱\n",
    "\n",
    "- DataFrame 的進階操作"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## DataFrame 的進階操作"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 不那麼基礎的 DataFrame 操作\n",
    "\n",
    "- 處理遺漏值\n",
    "- 多層索引值\n",
    "- 轉置\n",
    "- 合併"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Pandas 常用於判斷、刪除和填補遺漏值的方法有四個：\n",
    "\n",
    "- `.isnull()`\n",
    "- `.notnull()`\n",
    "- `.dropna()`\n",
    "- `.fillna()`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## .isnull() 方法能夠輸出一個布林陣列將遺漏值標記為 True ，非遺漏值記錄為 False ；而 .notnull() 則是輸出與前者恰恰相反的布林陣列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0    False\n",
      "1     True\n",
      "2    False\n",
      "3     True\n",
      "dtype: bool\n",
      "\n",
      "\n",
      "0     True\n",
      "1    False\n",
      "2     True\n",
      "3    False\n",
      "dtype: bool\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "ser = pd.Series([5, None, 6, np.NaN])\n",
    "print(ser.isnull())\n",
    "print(\"\\n\")\n",
    "print(ser.notnull())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## .dropna() 方法能夠將資料中遺漏值的部分捨棄，輸出非遺漏值的資料"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0    5.0\n",
      "1    NaN\n",
      "2    6.0\n",
      "3    NaN\n",
      "dtype: float64\n",
      "\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "0    5.0\n",
       "2    6.0\n",
       "dtype: float64"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ser = pd.Series([5, None, 6, np.NaN])\n",
    "print(ser)\n",
    "print(\"\\n\")\n",
    "ser.dropna()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 對 Series 來說，.dropna() 方法運作的方式非常直觀；但是面對資料框我們無法捨棄單一個資料點，只能夠選擇捨棄一整個列（觀測值）或一整個欄（變數），這時可以傳入參數 axis=0 指定列（預設）、axis=1 指定欄"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "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>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>6.0</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     0    1    2\n",
       "0  1.0  NaN  7.0\n",
       "1  2.0  5.0  8.0\n",
       "2  NaN  6.0  9.0"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame([\n",
    "    [1,      np.nan, 7.],\n",
    "    [2,      5,      8.],\n",
    "    [np.nan, 6,      9.]\n",
    "])\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "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>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     0    1    2\n",
       "1  2.0  5.0  8.0"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dropna() # default dropping rows with any NaN"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "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>2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     2\n",
       "0  7.0\n",
       "1  8.0\n",
       "2  9.0"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dropna(axis=1) # dropping columns with any NaN"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 面對遺漏值我們會選擇填補而非捨棄，Pandas 提供了.fillna() 方法，輸出以指定值替代 NaN 的資料"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0    5.0\n",
      "1    NaN\n",
      "2    6.0\n",
      "3    NaN\n",
      "dtype: float64\n",
      "\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "0       5.0\n",
       "1    5566.0\n",
       "2       6.0\n",
       "3    5566.0\n",
       "dtype: float64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ser = pd.Series([5, None, 6, np.NaN])\n",
    "print(ser)\n",
    "print(\"\\n\")\n",
    "ser.fillna(5566)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 除了以指定值填補遺漏值以外，亦可使用參數 method='ffill' 規則為碰到遺漏值時用前一筆資料填補，同理參數 method='bfill' 規則恰好相反，碰到遺漏值時用後一筆資料填補"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0    5.0\n",
      "1    NaN\n",
      "2    6.0\n",
      "3    NaN\n",
      "4    7.0\n",
      "dtype: float64\n",
      "\n",
      "\n",
      "0    5.0\n",
      "1    5.0\n",
      "2    6.0\n",
      "3    6.0\n",
      "4    7.0\n",
      "dtype: float64\n",
      "\n",
      "\n",
      "0    5.0\n",
      "1    6.0\n",
      "2    6.0\n",
      "3    7.0\n",
      "4    7.0\n",
      "dtype: float64\n"
     ]
    }
   ],
   "source": [
    "ser = pd.Series([5, None, 6, np.NaN, 7])\n",
    "print(ser)\n",
    "print(\"\\n\")\n",
    "print(ser.fillna(method='ffill'))\n",
    "print(\"\\n\")\n",
    "print(ser.fillna(method='bfill'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 對資料框應用「分組摘要」的技巧在一些特殊情況之下我們會得到一個索引值比較複雜的 Series 輸出，例如在 .groupby() 方法之中傳入兩個以上的類別變數作為分組依據，這時我們將會得到一種名為 MultiIndex 的類別，所謂的多層索引值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "pos  country               \n",
      "C    Austria                   2.130000\n",
      "     Bahamas                   2.160000\n",
      "     Bosnia and Herzegovina    2.130000\n",
      "     Canada                    2.060000\n",
      "     Croatia                   2.135000\n",
      "                                 ...   \n",
      "G-F  France                    2.006667\n",
      "     Italy                     1.960000\n",
      "     Japan                     2.060000\n",
      "     Turkey                    2.010000\n",
      "     USA                       1.993214\n",
      "Name: heightMeters, Length: 88, dtype: float64\n",
      "<class 'pandas.core.indexes.multi.MultiIndex'>\n"
     ]
    }
   ],
   "source": [
    "player_profile = pd.read_csv(\"https://python4ds.s3-ap-northeast-1.amazonaws.com/player_profile.csv\")\n",
    "groupby_object = player_profile.groupby([\"pos\", \"country\"])\n",
    "print(groupby_object[\"heightMeters\"].mean()) # Average height by pos and country\n",
    "print(type(groupby_object[\"heightMeters\"].mean().index))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 面對具有多層索引值的 Series ，數值部分同樣使用 .values 屬性即可拆解，至於索引值的拆解較為複雜，必須像是面對多維度陣列的索引，運用 [m, n, ...] 的方式選取所需資料，例如想知道前述例子中，聯盟中的美國（USA）後衛（G）平均身高，就可以運用 [\"G\", \"USA\"] 取值；假如想知道聯盟中的美國（USA）搖擺人（G-F、F-G）平均身高，就運用 [[\"G-F\", \"F-G\"]][:, \"USA\"] 取值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[2.13       2.16       2.13       2.06       2.135      2.06\n",
      " 2.13333333 2.11       2.11       2.13       2.13       2.13\n",
      " 2.2        2.17       2.135      2.18       2.08       2.11\n",
      " 2.11961538 2.16       2.08       2.08       2.08       2.08\n",
      " 2.13       2.09       2.06       2.08       2.055      2.06\n",
      " 2.06       2.07       2.082      1.98       2.13       2.00666667\n",
      " 2.06       2.07       2.06666667 2.06       2.11       2.07\n",
      " 2.03       2.07       2.06       2.045      2.07       2.06\n",
      " 2.085      2.06       2.06       2.055      2.03795455 1.995\n",
      " 2.08       2.13       2.11       2.13       2.21       2.09933333\n",
      " 2.02       2.03       2.01       1.98636364 1.92       1.93\n",
      " 1.85       1.94142857 1.96       1.97       1.94       2.03\n",
      " 1.93       1.98       1.9        1.93       1.9216763  2.03\n",
      " 1.98       2.045      2.06       1.98       2.01       2.00666667\n",
      " 1.96       2.06       2.01       1.99321429]\n",
      "1.9216763005780348\n",
      "pos\n",
      "F-G    1.986364\n",
      "G-F    1.993214\n",
      "Name: heightMeters, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "player_profile = pd.read_csv(\"https://python4ds.s3-ap-northeast-1.amazonaws.com/player_profile.csv\")\n",
    "groupby_object = player_profile.groupby([\"pos\", \"country\"])\n",
    "ser_w_multi_index = groupby_object[\"heightMeters\"].mean() # Average height by pos and country\n",
    "print(ser_w_multi_index.values) # values attribute of a multi-index series\n",
    "print(ser_w_multi_index[\"G\", \"USA\"]) # average height of USA's guards\n",
    "print(ser_w_multi_index[[\"G-F\", \"F-G\"]][:, \"USA\"]) # average heights of USA's swingmen"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 常見的轉置應用是寬表格（Wide Format）與長表格（Long Format）之間的互相轉換"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 寬表格是比較熟悉的資料框樣式，一列是獨立的觀測值，加入資訊是以增添欄位方式實踐，故得其名為寬表格；長表格是比較陌生的資料框樣式，具有以一欄 key 搭配一欄 value 來紀錄資料的項目與值，加入資訊是以增添列數方式實踐，故得其名為長表格"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 多數時候我們所使用的資料皆是寬表格的外觀，像是 NBA 球員的基本資料，一列是獨特的一名球員"
   ]
  },
  {
   "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>temporaryDisplayName</th>\n",
       "      <th>heightMeters</th>\n",
       "      <th>weightKilograms</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Adams, Steven</td>\n",
       "      <td>2.13</td>\n",
       "      <td>120.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Adebayo, Bam</td>\n",
       "      <td>2.08</td>\n",
       "      <td>115.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Adel, Deng</td>\n",
       "      <td>2.01</td>\n",
       "      <td>90.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Aldridge, LaMarcus</td>\n",
       "      <td>2.11</td>\n",
       "      <td>117.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Alexander, Kyle</td>\n",
       "      <td>2.11</td>\n",
       "      <td>99.8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  temporaryDisplayName  heightMeters  weightKilograms\n",
       "0        Adams, Steven          2.13            120.2\n",
       "1         Adebayo, Bam          2.08            115.7\n",
       "2           Adel, Deng          2.01             90.7\n",
       "3   Aldridge, LaMarcus          2.11            117.9\n",
       "4      Alexander, Kyle          2.11             99.8"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "player_profile = pd.read_csv(\"https://python4ds.s3-ap-northeast-1.amazonaws.com/player_profile.csv\")\n",
    "wide_format = player_profile[[\"temporaryDisplayName\", \"heightMeters\", \"weightKilograms\"]]\n",
    "wide_format.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 將寬表格的外觀轉換為長表格，表示以一個變數（Key）記錄身高或體重，再以一個變數（Value）記錄身高的高度與體重的重量，我們可以使用 pd.melt() 函數"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>temporaryDisplayName</th>\n",
       "      <th>key</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Adams, Steven</td>\n",
       "      <td>heightMeters</td>\n",
       "      <td>2.13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>524</th>\n",
       "      <td>Adams, Steven</td>\n",
       "      <td>weightKilograms</td>\n",
       "      <td>120.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>525</th>\n",
       "      <td>Adebayo, Bam</td>\n",
       "      <td>weightKilograms</td>\n",
       "      <td>115.70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Adebayo, Bam</td>\n",
       "      <td>heightMeters</td>\n",
       "      <td>2.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Adel, Deng</td>\n",
       "      <td>heightMeters</td>\n",
       "      <td>2.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>526</th>\n",
       "      <td>Adel, Deng</td>\n",
       "      <td>weightKilograms</td>\n",
       "      <td>90.70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Aldridge, LaMarcus</td>\n",
       "      <td>heightMeters</td>\n",
       "      <td>2.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>527</th>\n",
       "      <td>Aldridge, LaMarcus</td>\n",
       "      <td>weightKilograms</td>\n",
       "      <td>117.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Alexander, Kyle</td>\n",
       "      <td>heightMeters</td>\n",
       "      <td>2.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>528</th>\n",
       "      <td>Alexander, Kyle</td>\n",
       "      <td>weightKilograms</td>\n",
       "      <td>99.80</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    temporaryDisplayName              key   value\n",
       "0          Adams, Steven     heightMeters    2.13\n",
       "524        Adams, Steven  weightKilograms  120.20\n",
       "525         Adebayo, Bam  weightKilograms  115.70\n",
       "1           Adebayo, Bam     heightMeters    2.08\n",
       "2             Adel, Deng     heightMeters    2.01\n",
       "526           Adel, Deng  weightKilograms   90.70\n",
       "3     Aldridge, LaMarcus     heightMeters    2.11\n",
       "527   Aldridge, LaMarcus  weightKilograms  117.90\n",
       "4        Alexander, Kyle     heightMeters    2.11\n",
       "528      Alexander, Kyle  weightKilograms   99.80"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "player_profile = pd.read_csv(\"https://python4ds.s3-ap-northeast-1.amazonaws.com/player_profile.csv\")\n",
    "wide_format = player_profile[[\"temporaryDisplayName\", \"heightMeters\", \"weightKilograms\"]]\n",
    "long_format = pd.melt(wide_format, id_vars=\"temporaryDisplayName\", value_vars=[\"heightMeters\", \"weightKilograms\"], var_name=\"key\", value_name=\"value\")\n",
    "long_format.sort_values(\"temporaryDisplayName\").head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "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>temporaryDisplayName</th>\n",
       "      <th>key</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Adams, Steven</td>\n",
       "      <td>heightMeters</td>\n",
       "      <td>2.13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>524</th>\n",
       "      <td>Adams, Steven</td>\n",
       "      <td>weightKilograms</td>\n",
       "      <td>120.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>525</th>\n",
       "      <td>Adebayo, Bam</td>\n",
       "      <td>weightKilograms</td>\n",
       "      <td>115.70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Adebayo, Bam</td>\n",
       "      <td>heightMeters</td>\n",
       "      <td>2.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Adel, Deng</td>\n",
       "      <td>heightMeters</td>\n",
       "      <td>2.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>526</th>\n",
       "      <td>Adel, Deng</td>\n",
       "      <td>weightKilograms</td>\n",
       "      <td>90.70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Aldridge, LaMarcus</td>\n",
       "      <td>heightMeters</td>\n",
       "      <td>2.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>527</th>\n",
       "      <td>Aldridge, LaMarcus</td>\n",
       "      <td>weightKilograms</td>\n",
       "      <td>117.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Alexander, Kyle</td>\n",
       "      <td>heightMeters</td>\n",
       "      <td>2.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>528</th>\n",
       "      <td>Alexander, Kyle</td>\n",
       "      <td>weightKilograms</td>\n",
       "      <td>99.80</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    temporaryDisplayName              key   value\n",
       "0          Adams, Steven     heightMeters    2.13\n",
       "524        Adams, Steven  weightKilograms  120.20\n",
       "525         Adebayo, Bam  weightKilograms  115.70\n",
       "1           Adebayo, Bam     heightMeters    2.08\n",
       "2             Adel, Deng     heightMeters    2.01\n",
       "526           Adel, Deng  weightKilograms   90.70\n",
       "3     Aldridge, LaMarcus     heightMeters    2.11\n",
       "527   Aldridge, LaMarcus  weightKilograms  117.90\n",
       "4        Alexander, Kyle     heightMeters    2.11\n",
       "528      Alexander, Kyle  weightKilograms   99.80"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "long_format.sort_values(\"temporaryDisplayName\").head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 將長表格的外觀轉換為寬表格，會應用到類似分組的操作，以球員姓名作為分組依據，將數值資料樞紐回兩個變數，使用資料框的 .pivot() 方法"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>key</th>\n",
       "      <th>heightMeters</th>\n",
       "      <th>weightKilograms</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>temporaryDisplayName</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Adams, Steven</th>\n",
       "      <td>2.13</td>\n",
       "      <td>120.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Adebayo, Bam</th>\n",
       "      <td>2.08</td>\n",
       "      <td>115.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Adel, Deng</th>\n",
       "      <td>2.01</td>\n",
       "      <td>90.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Aldridge, LaMarcus</th>\n",
       "      <td>2.11</td>\n",
       "      <td>117.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Alexander, Kyle</th>\n",
       "      <td>2.11</td>\n",
       "      <td>99.8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "key                   heightMeters  weightKilograms\n",
       "temporaryDisplayName                               \n",
       "Adams, Steven                 2.13            120.2\n",
       "Adebayo, Bam                  2.08            115.7\n",
       "Adel, Deng                    2.01             90.7\n",
       "Aldridge, LaMarcus            2.11            117.9\n",
       "Alexander, Kyle               2.11             99.8"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "player_profile = pd.read_csv(\"https://python4ds.s3-ap-northeast-1.amazonaws.com/player_profile.csv\")\n",
    "wide_format = player_profile[[\"temporaryDisplayName\", \"heightMeters\", \"weightKilograms\"]]\n",
    "long_format = pd.melt(wide_format, id_vars=\"temporaryDisplayName\", value_vars=[\"heightMeters\", \"weightKilograms\"], var_name=\"key\", value_name=\"value\")\n",
    "long_format.pivot(index=\"temporaryDisplayName\", columns=\"key\", values=\"value\").head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 最後稍微整理一下，利用 .reset_index() 以及刪除列索引的名稱，就能將樞紐後的表格回復成與原本一模一樣的寬表格"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "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>temporaryDisplayName</th>\n",
       "      <th>heightMeters</th>\n",
       "      <th>weightKilograms</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Adams, Steven</td>\n",
       "      <td>2.13</td>\n",
       "      <td>120.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Adebayo, Bam</td>\n",
       "      <td>2.08</td>\n",
       "      <td>115.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Adel, Deng</td>\n",
       "      <td>2.01</td>\n",
       "      <td>90.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Aldridge, LaMarcus</td>\n",
       "      <td>2.11</td>\n",
       "      <td>117.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Alexander, Kyle</td>\n",
       "      <td>2.11</td>\n",
       "      <td>99.8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  temporaryDisplayName  heightMeters  weightKilograms\n",
       "0        Adams, Steven          2.13            120.2\n",
       "1         Adebayo, Bam          2.08            115.7\n",
       "2           Adel, Deng          2.01             90.7\n",
       "3   Aldridge, LaMarcus          2.11            117.9\n",
       "4      Alexander, Kyle          2.11             99.8"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "player_profile = pd.read_csv(\"https://python4ds.s3-ap-northeast-1.amazonaws.com/player_profile.csv\")\n",
    "wide_format = player_profile[[\"temporaryDisplayName\", \"heightMeters\", \"weightKilograms\"]]\n",
    "long_format = pd.melt(wide_format, id_vars=\"temporaryDisplayName\", value_vars=[\"heightMeters\", \"weightKilograms\"], var_name=\"key\", value_name=\"value\")\n",
    "wide_format = long_format.pivot(index=\"temporaryDisplayName\", columns=\"key\", values=\"value\").reset_index()\n",
    "wide_format = wide_format.rename_axis(None, axis=1)\n",
    "wide_format.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Pandas 套件有四種常用函數或方法能夠協助使用者合併不同資料源\n",
    "\n",
    "- `pd.concat()`\n",
    "- `df.append()`\n",
    "- `pd.merge()`\n",
    "- `df.join()`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 簡單合併 pd.concat()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "upper_df = pd.DataFrame()\n",
    "lower_df = pd.DataFrame()\n",
    "upper_df[\"character\"] = [\"Rachel Green\", \"Monica Geller\", \"Phoebe Buffay\"]\n",
    "upper_df[\"cast\"] = [\"Jennifer Aniston\", \"Courteney Cox\", \"Lisa Kudrow\"]\n",
    "lower_df[\"character\"] = [\"Joey Tribbiani\", \"Chandler Bing\", \"Ross Geller\"]\n",
    "lower_df[\"cast\"] = [\"Matt LeBlanc\", \"Matthew Perry\", \"David Schwimmer\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Upper df:\n",
      "Lower df:\n",
      "Concatenated vertically:\n"
     ]
    },
    {
     "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>character</th>\n",
       "      <th>cast</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Rachel Green</td>\n",
       "      <td>Jennifer Aniston</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Monica Geller</td>\n",
       "      <td>Courteney Cox</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Phoebe Buffay</td>\n",
       "      <td>Lisa Kudrow</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Joey Tribbiani</td>\n",
       "      <td>Matt LeBlanc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Chandler Bing</td>\n",
       "      <td>Matthew Perry</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Ross Geller</td>\n",
       "      <td>David Schwimmer</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        character              cast\n",
       "0    Rachel Green  Jennifer Aniston\n",
       "1   Monica Geller     Courteney Cox\n",
       "2   Phoebe Buffay       Lisa Kudrow\n",
       "0  Joey Tribbiani      Matt LeBlanc\n",
       "1   Chandler Bing     Matthew Perry\n",
       "2     Ross Geller   David Schwimmer"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(\"Upper df:\")\n",
    "upper_df\n",
    "print(\"Lower df:\")\n",
    "lower_df\n",
    "print(\"Concatenated vertically:\")\n",
    "pd.concat([upper_df, lower_df]) # axis=0 as default"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 合併後的資料框具備了重複的列索引，如果希望可以重設列索引，可以在 pd.concat() 函數中加入參數 ignore_index=True"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 指定參數 axis=1 則為水平合併"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "left_df = pd.DataFrame()\n",
    "right_df = pd.DataFrame()\n",
    "left_df[\"character\"] = [\"Rachel Green\", \"Monica Geller\", \"Phoebe Buffay\", \"Joey Tribbiani\", \"Chandler Bing\", \"Ross Geller\"]\n",
    "right_df[\"cast\"] = [\"Jennifer Aniston\", \"Courteney Cox\", \"Lisa Kudrow\", \"Matt LeBlanc\", \"Matthew Perry\", \"David Schwimmer\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Left df:\n",
      "Right df:\n",
      "Concatenated horizontally:\n"
     ]
    },
    {
     "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>character</th>\n",
       "      <th>cast</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Rachel Green</td>\n",
       "      <td>Jennifer Aniston</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Monica Geller</td>\n",
       "      <td>Courteney Cox</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Phoebe Buffay</td>\n",
       "      <td>Lisa Kudrow</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Joey Tribbiani</td>\n",
       "      <td>Matt LeBlanc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Chandler Bing</td>\n",
       "      <td>Matthew Perry</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Ross Geller</td>\n",
       "      <td>David Schwimmer</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        character              cast\n",
       "0    Rachel Green  Jennifer Aniston\n",
       "1   Monica Geller     Courteney Cox\n",
       "2   Phoebe Buffay       Lisa Kudrow\n",
       "3  Joey Tribbiani      Matt LeBlanc\n",
       "4   Chandler Bing     Matthew Perry\n",
       "5     Ross Geller   David Schwimmer"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(\"Left df:\")\n",
    "left_df\n",
    "print(\"Right df:\")\n",
    "right_df\n",
    "print(\"Concatenated horizontally:\")\n",
    "pd.concat([left_df, right_df], axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 垂直合併 df.append()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "upper_df = pd.DataFrame()\n",
    "lower_df = pd.DataFrame()\n",
    "upper_df[\"character\"] = [\"Rachel Green\", \"Monica Geller\", \"Phoebe Buffay\"]\n",
    "upper_df[\"cast\"] = [\"Jennifer Aniston\", \"Courteney Cox\", \"Lisa Kudrow\"]\n",
    "lower_df[\"character\"] = [\"Joey Tribbiani\", \"Chandler Bing\", \"Ross Geller\"]\n",
    "lower_df[\"cast\"] = [\"Matt LeBlanc\", \"Matthew Perry\", \"David Schwimmer\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Upper df:\n",
      "Lower df:\n",
      "Concatenated vertically using append method:\n"
     ]
    },
    {
     "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>character</th>\n",
       "      <th>cast</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Rachel Green</td>\n",
       "      <td>Jennifer Aniston</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Monica Geller</td>\n",
       "      <td>Courteney Cox</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Phoebe Buffay</td>\n",
       "      <td>Lisa Kudrow</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Joey Tribbiani</td>\n",
       "      <td>Matt LeBlanc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Chandler Bing</td>\n",
       "      <td>Matthew Perry</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Ross Geller</td>\n",
       "      <td>David Schwimmer</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        character              cast\n",
       "0    Rachel Green  Jennifer Aniston\n",
       "1   Monica Geller     Courteney Cox\n",
       "2   Phoebe Buffay       Lisa Kudrow\n",
       "0  Joey Tribbiani      Matt LeBlanc\n",
       "1   Chandler Bing     Matthew Perry\n",
       "2     Ross Geller   David Schwimmer"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(\"Upper df:\")\n",
    "upper_df\n",
    "print(\"Lower df:\")\n",
    "lower_df\n",
    "print(\"Concatenated vertically using append method:\")\n",
    "upper_df.append(lower_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 聯結 pd.merge()\n",
    "\n",
    "在 Pandas 中若想要高效能操作類似關聯式資料庫表格聯結和合併，主要的實踐函數是 pd.merge() ，她沿用關聯式資料庫的正規法則 Relational Algebra，實踐正規法則所規範四種基礎聯結\n",
    "\n",
    "- 一對一聯結（one-to-one）\n",
    "- 一對多聯結（one-to-many）\n",
    "- 多對一聯結（many-to-one）\n",
    "- 多對多聯結（many-to-many）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "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>title</th>\n",
       "      <th>release_year</th>\n",
       "      <th>rating</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>The Avengers</td>\n",
       "      <td>2012</td>\n",
       "      <td>8.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Avengers: Age of Ultron</td>\n",
       "      <td>2015</td>\n",
       "      <td>7.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Avengers: Infinity War</td>\n",
       "      <td>2018</td>\n",
       "      <td>8.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Avengers: Endgame</td>\n",
       "      <td>2019</td>\n",
       "      <td>8.6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     title  release_year  rating\n",
       "0             The Avengers          2012     8.5\n",
       "1  Avengers: Age of Ultron          2015     7.3\n",
       "2   Avengers: Infinity War          2018     8.5\n",
       "3        Avengers: Endgame          2019     8.6"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#一對一聯結（one-to-one）\n",
    "left_df = pd.DataFrame()\n",
    "right_df = pd.DataFrame()\n",
    "left_df[\"title\"] = [\"The Avengers\", \"Avengers: Age of Ultron\", \"Avengers: Infinity War\", \"Avengers: Endgame\"]\n",
    "left_df[\"release_year\"] = [2012, 2015, 2018, 2019]\n",
    "right_df[\"title\"] = [\"Avengers: Infinity War\", \"Avengers: Endgame\", \"The Avengers\", \"Avengers: Age of Ultron\"]\n",
    "right_df[\"rating\"] = [8.5, 8.6, 8.5, 7.3]\n",
    "left_df\n",
    "right_df\n",
    "pd.merge(left_df, right_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "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>title</th>\n",
       "      <th>release_year</th>\n",
       "      <th>genre</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>The Avengers</td>\n",
       "      <td>2012</td>\n",
       "      <td>Action</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>The Avengers</td>\n",
       "      <td>2012</td>\n",
       "      <td>Adventure</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>The Avengers</td>\n",
       "      <td>2012</td>\n",
       "      <td>Sci-Fi</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          title  release_year      genre\n",
       "0  The Avengers          2012     Action\n",
       "1  The Avengers          2012  Adventure\n",
       "2  The Avengers          2012     Sci-Fi"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#一對多聯結（one-to-many）\n",
    "left_df = pd.DataFrame()\n",
    "right_df = pd.DataFrame()\n",
    "left_df[\"title\"] = [\"The Avengers\"]\n",
    "left_df[\"release_year\"] = [2012]\n",
    "right_df[\"title\"] = [\"The Avengers\", \"The Avengers\", \"The Avengers\"]\n",
    "right_df[\"genre\"] = [\"Action\", \"Adventure\", \"Sci-Fi\"]\n",
    "left_df\n",
    "right_df\n",
    "pd.merge(left_df, right_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "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>title</th>\n",
       "      <th>genre</th>\n",
       "      <th>release_year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>The Avengers</td>\n",
       "      <td>Action</td>\n",
       "      <td>2012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>The Avengers</td>\n",
       "      <td>Adventure</td>\n",
       "      <td>2012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>The Avengers</td>\n",
       "      <td>Sci-Fi</td>\n",
       "      <td>2012</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          title      genre  release_year\n",
       "0  The Avengers     Action          2012\n",
       "1  The Avengers  Adventure          2012\n",
       "2  The Avengers     Sci-Fi          2012"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#多對一聯結（many-to-one）\n",
    "left_df = pd.DataFrame()\n",
    "right_df = pd.DataFrame()\n",
    "left_df[\"title\"] = [\"The Avengers\", \"The Avengers\", \"The Avengers\"]\n",
    "left_df[\"genre\"] = [\"Action\", \"Adventure\", \"Sci-Fi\"]\n",
    "right_df[\"title\"] = [\"The Avengers\"]\n",
    "right_df[\"release_year\"] = [2012]\n",
    "left_df\n",
    "right_df\n",
    "pd.merge(left_df, right_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "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>title</th>\n",
       "      <th>genre</th>\n",
       "      <th>avengers</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>The Avengers</td>\n",
       "      <td>Action</td>\n",
       "      <td>Ironman</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>The Avengers</td>\n",
       "      <td>Action</td>\n",
       "      <td>Captain America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>The Avengers</td>\n",
       "      <td>Action</td>\n",
       "      <td>The Hulk</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>The Avengers</td>\n",
       "      <td>Action</td>\n",
       "      <td>Thor</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>The Avengers</td>\n",
       "      <td>Action</td>\n",
       "      <td>Black Widow</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>The Avengers</td>\n",
       "      <td>Action</td>\n",
       "      <td>Hawkeye</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>The Avengers</td>\n",
       "      <td>Adventure</td>\n",
       "      <td>Ironman</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>The Avengers</td>\n",
       "      <td>Adventure</td>\n",
       "      <td>Captain America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>The Avengers</td>\n",
       "      <td>Adventure</td>\n",
       "      <td>The Hulk</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>The Avengers</td>\n",
       "      <td>Adventure</td>\n",
       "      <td>Thor</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>The Avengers</td>\n",
       "      <td>Adventure</td>\n",
       "      <td>Black Widow</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>The Avengers</td>\n",
       "      <td>Adventure</td>\n",
       "      <td>Hawkeye</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>The Avengers</td>\n",
       "      <td>Sci-Fi</td>\n",
       "      <td>Ironman</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>The Avengers</td>\n",
       "      <td>Sci-Fi</td>\n",
       "      <td>Captain America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>The Avengers</td>\n",
       "      <td>Sci-Fi</td>\n",
       "      <td>The Hulk</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>The Avengers</td>\n",
       "      <td>Sci-Fi</td>\n",
       "      <td>Thor</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>The Avengers</td>\n",
       "      <td>Sci-Fi</td>\n",
       "      <td>Black Widow</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>The Avengers</td>\n",
       "      <td>Sci-Fi</td>\n",
       "      <td>Hawkeye</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           title      genre         avengers\n",
       "0   The Avengers     Action          Ironman\n",
       "1   The Avengers     Action  Captain America\n",
       "2   The Avengers     Action         The Hulk\n",
       "3   The Avengers     Action             Thor\n",
       "4   The Avengers     Action      Black Widow\n",
       "5   The Avengers     Action          Hawkeye\n",
       "6   The Avengers  Adventure          Ironman\n",
       "7   The Avengers  Adventure  Captain America\n",
       "8   The Avengers  Adventure         The Hulk\n",
       "9   The Avengers  Adventure             Thor\n",
       "10  The Avengers  Adventure      Black Widow\n",
       "11  The Avengers  Adventure          Hawkeye\n",
       "12  The Avengers     Sci-Fi          Ironman\n",
       "13  The Avengers     Sci-Fi  Captain America\n",
       "14  The Avengers     Sci-Fi         The Hulk\n",
       "15  The Avengers     Sci-Fi             Thor\n",
       "16  The Avengers     Sci-Fi      Black Widow\n",
       "17  The Avengers     Sci-Fi          Hawkeye"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#多對多聯結（many-to-many）\n",
    "left_df = pd.DataFrame()\n",
    "right_df = pd.DataFrame()\n",
    "left_df[\"title\"] = [\"The Avengers\", \"The Avengers\", \"The Avengers\"]\n",
    "left_df[\"genre\"] = [\"Action\", \"Adventure\", \"Sci-Fi\"]\n",
    "right_df[\"title\"] = [\"The Avengers\"]*6\n",
    "right_df[\"avengers\"] = [\"Ironman\", \"Captain America\", \"The Hulk\", \"Thor\", \"Black Widow\", \"Hawkeye\"]\n",
    "left_df\n",
    "right_df\n",
    "pd.merge(left_df, right_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 加入 left_on 與 right_on 參數指定要用哪些變數進行聯結的對照依據"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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>title</th>\n",
       "      <th>release_year</th>\n",
       "      <th>movie_name</th>\n",
       "      <th>rating</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>The Avengers</td>\n",
       "      <td>2012</td>\n",
       "      <td>The Avengers</td>\n",
       "      <td>8.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Avengers: Age of Ultron</td>\n",
       "      <td>2015</td>\n",
       "      <td>Avengers: Age of Ultron</td>\n",
       "      <td>7.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Avengers: Infinity War</td>\n",
       "      <td>2018</td>\n",
       "      <td>Avengers: Infinity War</td>\n",
       "      <td>8.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Avengers: Endgame</td>\n",
       "      <td>2019</td>\n",
       "      <td>Avengers: Endgame</td>\n",
       "      <td>8.6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     title  release_year               movie_name  rating\n",
       "0             The Avengers          2012             The Avengers     8.5\n",
       "1  Avengers: Age of Ultron          2015  Avengers: Age of Ultron     7.3\n",
       "2   Avengers: Infinity War          2018   Avengers: Infinity War     8.5\n",
       "3        Avengers: Endgame          2019        Avengers: Endgame     8.6"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "left_df = pd.DataFrame()\n",
    "right_df = pd.DataFrame()\n",
    "left_df[\"title\"] = [\"The Avengers\", \"Avengers: Age of Ultron\", \"Avengers: Infinity War\", \"Avengers: Endgame\"]\n",
    "left_df[\"release_year\"] = [2012, 2015, 2018, 2019]\n",
    "right_df[\"movie_name\"] = [\"Avengers: Infinity War\", \"Avengers: Endgame\", \"The Avengers\", \"Avengers: Age of Ultron\"]\n",
    "right_df[\"rating\"] = [8.5, 8.6, 8.5, 7.3]\n",
    "left_df\n",
    "right_df\n",
    "pd.merge(left_df, right_df, left_on=\"title\", right_on=\"movie_name\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 加入 how 參數則可以指定聯結後的資料框要採用交集（預設）、以左邊資料框存在的觀測值為主、以右邊資料框存在的觀測值為主或聯集"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Inner join:\n"
     ]
    },
    {
     "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>title</th>\n",
       "      <th>release_year</th>\n",
       "      <th>rating</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Avengers: Age of Ultron</td>\n",
       "      <td>2015</td>\n",
       "      <td>7.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Avengers: Infinity War</td>\n",
       "      <td>2018</td>\n",
       "      <td>8.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     title  release_year  rating\n",
       "0  Avengers: Age of Ultron          2015     7.3\n",
       "1   Avengers: Infinity War          2018     8.5"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#交集（預設）\n",
    "left_df = pd.DataFrame()\n",
    "right_df = pd.DataFrame()\n",
    "left_df[\"title\"] = [\"The Avengers\", \"Avengers: Age of Ultron\", \"Avengers: Infinity War\"]\n",
    "left_df[\"release_year\"] = [2012, 2015, 2018]\n",
    "right_df[\"title\"] = [\"Avengers: Infinity War\", \"Avengers: Endgame\", \"Avengers: Age of Ultron\"]\n",
    "right_df[\"rating\"] = [8.5, 8.6, 7.3]\n",
    "left_df\n",
    "right_df\n",
    "print(\"Inner join:\")\n",
    "pd.merge(left_df, right_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Left join:\n"
     ]
    },
    {
     "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>title</th>\n",
       "      <th>release_year</th>\n",
       "      <th>rating</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>The Avengers</td>\n",
       "      <td>2012</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Avengers: Age of Ultron</td>\n",
       "      <td>2015</td>\n",
       "      <td>7.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Avengers: Infinity War</td>\n",
       "      <td>2018</td>\n",
       "      <td>8.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     title  release_year  rating\n",
       "0             The Avengers          2012     NaN\n",
       "1  Avengers: Age of Ultron          2015     7.3\n",
       "2   Avengers: Infinity War          2018     8.5"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#以左邊資料框存在的觀測值為主\n",
    "left_df = pd.DataFrame()\n",
    "right_df = pd.DataFrame()\n",
    "left_df[\"title\"] = [\"The Avengers\", \"Avengers: Age of Ultron\", \"Avengers: Infinity War\"]\n",
    "left_df[\"release_year\"] = [2012, 2015, 2018]\n",
    "right_df[\"title\"] = [\"Avengers: Infinity War\", \"Avengers: Endgame\", \"Avengers: Age of Ultron\"]\n",
    "right_df[\"rating\"] = [8.5, 8.6, 7.3]\n",
    "left_df\n",
    "right_df\n",
    "print(\"Left join:\")\n",
    "pd.merge(left_df, right_df, how=\"left\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Right join:\n"
     ]
    },
    {
     "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>title</th>\n",
       "      <th>release_year</th>\n",
       "      <th>rating</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Avengers: Age of Ultron</td>\n",
       "      <td>2015.0</td>\n",
       "      <td>7.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Avengers: Infinity War</td>\n",
       "      <td>2018.0</td>\n",
       "      <td>8.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Avengers: Endgame</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     title  release_year  rating\n",
       "0  Avengers: Age of Ultron        2015.0     7.3\n",
       "1   Avengers: Infinity War        2018.0     8.5\n",
       "2        Avengers: Endgame           NaN     8.6"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#以右邊資料框存在的觀測值為主\n",
    "left_df = pd.DataFrame()\n",
    "right_df = pd.DataFrame()\n",
    "left_df[\"title\"] = [\"The Avengers\", \"Avengers: Age of Ultron\", \"Avengers: Infinity War\"]\n",
    "left_df[\"release_year\"] = [2012, 2015, 2018]\n",
    "right_df[\"title\"] = [\"Avengers: Infinity War\", \"Avengers: Endgame\", \"Avengers: Age of Ultron\"]\n",
    "right_df[\"rating\"] = [8.5, 8.6, 7.3]\n",
    "left_df\n",
    "right_df\n",
    "print(\"Right join:\")\n",
    "pd.merge(left_df, right_df, how=\"right\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Outer join:\n"
     ]
    },
    {
     "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>title</th>\n",
       "      <th>release_year</th>\n",
       "      <th>rating</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>The Avengers</td>\n",
       "      <td>2012.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Avengers: Age of Ultron</td>\n",
       "      <td>2015.0</td>\n",
       "      <td>7.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Avengers: Infinity War</td>\n",
       "      <td>2018.0</td>\n",
       "      <td>8.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Avengers: Endgame</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     title  release_year  rating\n",
       "0             The Avengers        2012.0     NaN\n",
       "1  Avengers: Age of Ultron        2015.0     7.3\n",
       "2   Avengers: Infinity War        2018.0     8.5\n",
       "3        Avengers: Endgame           NaN     8.6"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#聯集\n",
    "left_df = pd.DataFrame()\n",
    "right_df = pd.DataFrame()\n",
    "left_df[\"title\"] = [\"The Avengers\", \"Avengers: Age of Ultron\", \"Avengers: Infinity War\"]\n",
    "left_df[\"release_year\"] = [2012, 2015, 2018]\n",
    "right_df[\"title\"] = [\"Avengers: Infinity War\", \"Avengers: Endgame\", \"Avengers: Age of Ultron\"]\n",
    "right_df[\"rating\"] = [8.5, 8.6, 7.3]\n",
    "left_df\n",
    "right_df\n",
    "print(\"Outer join:\")\n",
    "pd.merge(left_df, right_df, how=\"outer\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 用列索引聯結 df.join()"
   ]
  },
  {
   "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>release_year</th>\n",
       "      <th>rating</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>title</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>The Avengers</th>\n",
       "      <td>2012</td>\n",
       "      <td>8.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Avengers: Age of Ultron</th>\n",
       "      <td>2015</td>\n",
       "      <td>7.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Avengers: Infinity War</th>\n",
       "      <td>2018</td>\n",
       "      <td>8.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Avengers: Endgame</th>\n",
       "      <td>2019</td>\n",
       "      <td>8.6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         release_year  rating\n",
       "title                                        \n",
       "The Avengers                     2012     8.5\n",
       "Avengers: Age of Ultron          2015     7.3\n",
       "Avengers: Infinity War           2018     8.5\n",
       "Avengers: Endgame                2019     8.6"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "left_df = pd.DataFrame()\n",
    "right_df = pd.DataFrame()\n",
    "left_df[\"title\"] = [\"The Avengers\", \"Avengers: Age of Ultron\", \"Avengers: Infinity War\", \"Avengers: Endgame\"]\n",
    "left_df[\"release_year\"] = [2012, 2015, 2018, 2019]\n",
    "right_df[\"title\"] = [\"Avengers: Infinity War\", \"Avengers: Endgame\", \"The Avengers\", \"Avengers: Age of Ultron\"]\n",
    "right_df[\"rating\"] = [8.5, 8.6, 8.5, 7.3]\n",
    "left_df = left_df.set_index(\"title\")\n",
    "right_df = right_df.set_index(\"title\")\n",
    "left_df\n",
    "right_df\n",
    "left_df.join(right_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## 延伸閱讀\n",
    "\n",
    "[pandas: powerful Python data analysis toolkit](http://pandas.pydata.org/pandas-docs/stable/)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## 隨堂練習\n",
    "\n",
    "[隨堂練習：美國普查](https://mybinder.org/v2/gh/yaojenkuo/python-data-analysis/master?filepath=exercises%2F04-exercises.ipynb)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 題庫來源\n",
    "\n",
    "[Introduction to Data Science in Python](https://www.coursera.org/learn/python-data-analysis)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(3193, 100)"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "census_df = pd.read_csv('https://storage.googleapis.com/py_ml_datasets/census.csv')\n",
    "census_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "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>SUMLEV</th>\n",
       "      <th>REGION</th>\n",
       "      <th>DIVISION</th>\n",
       "      <th>STATE</th>\n",
       "      <th>COUNTY</th>\n",
       "      <th>STNAME</th>\n",
       "      <th>CTYNAME</th>\n",
       "      <th>CENSUS2010POP</th>\n",
       "      <th>ESTIMATESBASE2010</th>\n",
       "      <th>POPESTIMATE2010</th>\n",
       "      <th>...</th>\n",
       "      <th>RDOMESTICMIG2011</th>\n",
       "      <th>RDOMESTICMIG2012</th>\n",
       "      <th>RDOMESTICMIG2013</th>\n",
       "      <th>RDOMESTICMIG2014</th>\n",
       "      <th>RDOMESTICMIG2015</th>\n",
       "      <th>RNETMIG2011</th>\n",
       "      <th>RNETMIG2012</th>\n",
       "      <th>RNETMIG2013</th>\n",
       "      <th>RNETMIG2014</th>\n",
       "      <th>RNETMIG2015</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>40</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>4779736</td>\n",
       "      <td>4780127</td>\n",
       "      <td>4785161</td>\n",
       "      <td>...</td>\n",
       "      <td>0.002295</td>\n",
       "      <td>-0.193196</td>\n",
       "      <td>0.381066</td>\n",
       "      <td>0.582002</td>\n",
       "      <td>-0.467369</td>\n",
       "      <td>1.030015</td>\n",
       "      <td>0.826644</td>\n",
       "      <td>1.383282</td>\n",
       "      <td>1.724718</td>\n",
       "      <td>0.712594</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>50</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>54571</td>\n",
       "      <td>54571</td>\n",
       "      <td>54660</td>\n",
       "      <td>...</td>\n",
       "      <td>7.242091</td>\n",
       "      <td>-2.915927</td>\n",
       "      <td>-3.012349</td>\n",
       "      <td>2.265971</td>\n",
       "      <td>-2.530799</td>\n",
       "      <td>7.606016</td>\n",
       "      <td>-2.626146</td>\n",
       "      <td>-2.722002</td>\n",
       "      <td>2.592270</td>\n",
       "      <td>-2.187333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>50</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Baldwin County</td>\n",
       "      <td>182265</td>\n",
       "      <td>182265</td>\n",
       "      <td>183193</td>\n",
       "      <td>...</td>\n",
       "      <td>14.832960</td>\n",
       "      <td>17.647293</td>\n",
       "      <td>21.845705</td>\n",
       "      <td>19.243287</td>\n",
       "      <td>17.197872</td>\n",
       "      <td>15.844176</td>\n",
       "      <td>18.559627</td>\n",
       "      <td>22.727626</td>\n",
       "      <td>20.317142</td>\n",
       "      <td>18.293499</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>50</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Barbour County</td>\n",
       "      <td>27457</td>\n",
       "      <td>27457</td>\n",
       "      <td>27341</td>\n",
       "      <td>...</td>\n",
       "      <td>-4.728132</td>\n",
       "      <td>-2.500690</td>\n",
       "      <td>-7.056824</td>\n",
       "      <td>-3.904217</td>\n",
       "      <td>-10.543299</td>\n",
       "      <td>-4.874741</td>\n",
       "      <td>-2.758113</td>\n",
       "      <td>-7.167664</td>\n",
       "      <td>-3.978583</td>\n",
       "      <td>-10.543299</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>50</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Bibb County</td>\n",
       "      <td>22915</td>\n",
       "      <td>22919</td>\n",
       "      <td>22861</td>\n",
       "      <td>...</td>\n",
       "      <td>-5.527043</td>\n",
       "      <td>-5.068871</td>\n",
       "      <td>-6.201001</td>\n",
       "      <td>-0.177537</td>\n",
       "      <td>0.177258</td>\n",
       "      <td>-5.088389</td>\n",
       "      <td>-4.363636</td>\n",
       "      <td>-5.403729</td>\n",
       "      <td>0.754533</td>\n",
       "      <td>1.107861</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 100 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   SUMLEV  REGION  DIVISION  STATE  COUNTY   STNAME         CTYNAME  \\\n",
       "0      40       3         6      1       0  Alabama         Alabama   \n",
       "1      50       3         6      1       1  Alabama  Autauga County   \n",
       "2      50       3         6      1       3  Alabama  Baldwin County   \n",
       "3      50       3         6      1       5  Alabama  Barbour County   \n",
       "4      50       3         6      1       7  Alabama     Bibb County   \n",
       "\n",
       "   CENSUS2010POP  ESTIMATESBASE2010  POPESTIMATE2010  ...  RDOMESTICMIG2011  \\\n",
       "0        4779736            4780127          4785161  ...          0.002295   \n",
       "1          54571              54571            54660  ...          7.242091   \n",
       "2         182265             182265           183193  ...         14.832960   \n",
       "3          27457              27457            27341  ...         -4.728132   \n",
       "4          22915              22919            22861  ...         -5.527043   \n",
       "\n",
       "   RDOMESTICMIG2012  RDOMESTICMIG2013  RDOMESTICMIG2014  RDOMESTICMIG2015  \\\n",
       "0         -0.193196          0.381066          0.582002         -0.467369   \n",
       "1         -2.915927         -3.012349          2.265971         -2.530799   \n",
       "2         17.647293         21.845705         19.243287         17.197872   \n",
       "3         -2.500690         -7.056824         -3.904217        -10.543299   \n",
       "4         -5.068871         -6.201001         -0.177537          0.177258   \n",
       "\n",
       "   RNETMIG2011  RNETMIG2012  RNETMIG2013  RNETMIG2014  RNETMIG2015  \n",
       "0     1.030015     0.826644     1.383282     1.724718     0.712594  \n",
       "1     7.606016    -2.626146    -2.722002     2.592270    -2.187333  \n",
       "2    15.844176    18.559627    22.727626    20.317142    18.293499  \n",
       "3    -4.874741    -2.758113    -7.167664    -3.978583   -10.543299  \n",
       "4    -5.088389    -4.363636    -5.403729     0.754533     1.107861  \n",
       "\n",
       "[5 rows x 100 columns]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "census_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "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>SUMLEV</th>\n",
       "      <th>REGION</th>\n",
       "      <th>DIVISION</th>\n",
       "      <th>STATE</th>\n",
       "      <th>COUNTY</th>\n",
       "      <th>STNAME</th>\n",
       "      <th>CTYNAME</th>\n",
       "      <th>CENSUS2010POP</th>\n",
       "      <th>ESTIMATESBASE2010</th>\n",
       "      <th>POPESTIMATE2010</th>\n",
       "      <th>...</th>\n",
       "      <th>RDOMESTICMIG2011</th>\n",
       "      <th>RDOMESTICMIG2012</th>\n",
       "      <th>RDOMESTICMIG2013</th>\n",
       "      <th>RDOMESTICMIG2014</th>\n",
       "      <th>RDOMESTICMIG2015</th>\n",
       "      <th>RNETMIG2011</th>\n",
       "      <th>RNETMIG2012</th>\n",
       "      <th>RNETMIG2013</th>\n",
       "      <th>RNETMIG2014</th>\n",
       "      <th>RNETMIG2015</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3188</th>\n",
       "      <td>50</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>56</td>\n",
       "      <td>37</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Sweetwater County</td>\n",
       "      <td>43806</td>\n",
       "      <td>43806</td>\n",
       "      <td>43593</td>\n",
       "      <td>...</td>\n",
       "      <td>1.072643</td>\n",
       "      <td>16.243199</td>\n",
       "      <td>-5.339774</td>\n",
       "      <td>-14.252889</td>\n",
       "      <td>-14.248864</td>\n",
       "      <td>1.255221</td>\n",
       "      <td>16.243199</td>\n",
       "      <td>-5.295460</td>\n",
       "      <td>-14.075283</td>\n",
       "      <td>-14.070195</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3189</th>\n",
       "      <td>50</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>56</td>\n",
       "      <td>39</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Teton County</td>\n",
       "      <td>21294</td>\n",
       "      <td>21294</td>\n",
       "      <td>21297</td>\n",
       "      <td>...</td>\n",
       "      <td>-1.589565</td>\n",
       "      <td>0.972695</td>\n",
       "      <td>19.525929</td>\n",
       "      <td>14.143021</td>\n",
       "      <td>-0.564849</td>\n",
       "      <td>0.654527</td>\n",
       "      <td>2.408578</td>\n",
       "      <td>21.160658</td>\n",
       "      <td>16.308671</td>\n",
       "      <td>1.520747</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3190</th>\n",
       "      <td>50</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>56</td>\n",
       "      <td>41</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Uinta County</td>\n",
       "      <td>21118</td>\n",
       "      <td>21118</td>\n",
       "      <td>21102</td>\n",
       "      <td>...</td>\n",
       "      <td>-17.755986</td>\n",
       "      <td>-4.916350</td>\n",
       "      <td>-6.902954</td>\n",
       "      <td>-14.215862</td>\n",
       "      <td>-12.127022</td>\n",
       "      <td>-18.136812</td>\n",
       "      <td>-5.536861</td>\n",
       "      <td>-7.521840</td>\n",
       "      <td>-14.740608</td>\n",
       "      <td>-12.606351</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3191</th>\n",
       "      <td>50</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>56</td>\n",
       "      <td>43</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Washakie County</td>\n",
       "      <td>8533</td>\n",
       "      <td>8533</td>\n",
       "      <td>8545</td>\n",
       "      <td>...</td>\n",
       "      <td>-11.637475</td>\n",
       "      <td>-0.827815</td>\n",
       "      <td>-2.013502</td>\n",
       "      <td>-17.781491</td>\n",
       "      <td>1.682288</td>\n",
       "      <td>-11.990126</td>\n",
       "      <td>-1.182592</td>\n",
       "      <td>-2.250385</td>\n",
       "      <td>-18.020168</td>\n",
       "      <td>1.441961</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3192</th>\n",
       "      <td>50</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>56</td>\n",
       "      <td>45</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Weston County</td>\n",
       "      <td>7208</td>\n",
       "      <td>7208</td>\n",
       "      <td>7181</td>\n",
       "      <td>...</td>\n",
       "      <td>-11.752361</td>\n",
       "      <td>-8.040059</td>\n",
       "      <td>12.372583</td>\n",
       "      <td>1.533635</td>\n",
       "      <td>6.935294</td>\n",
       "      <td>-12.032179</td>\n",
       "      <td>-8.040059</td>\n",
       "      <td>12.372583</td>\n",
       "      <td>1.533635</td>\n",
       "      <td>6.935294</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 100 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      SUMLEV  REGION  DIVISION  STATE  COUNTY   STNAME            CTYNAME  \\\n",
       "3188      50       4         8     56      37  Wyoming  Sweetwater County   \n",
       "3189      50       4         8     56      39  Wyoming       Teton County   \n",
       "3190      50       4         8     56      41  Wyoming       Uinta County   \n",
       "3191      50       4         8     56      43  Wyoming    Washakie County   \n",
       "3192      50       4         8     56      45  Wyoming      Weston County   \n",
       "\n",
       "      CENSUS2010POP  ESTIMATESBASE2010  POPESTIMATE2010  ...  \\\n",
       "3188          43806              43806            43593  ...   \n",
       "3189          21294              21294            21297  ...   \n",
       "3190          21118              21118            21102  ...   \n",
       "3191           8533               8533             8545  ...   \n",
       "3192           7208               7208             7181  ...   \n",
       "\n",
       "      RDOMESTICMIG2011  RDOMESTICMIG2012  RDOMESTICMIG2013  RDOMESTICMIG2014  \\\n",
       "3188          1.072643         16.243199         -5.339774        -14.252889   \n",
       "3189         -1.589565          0.972695         19.525929         14.143021   \n",
       "3190        -17.755986         -4.916350         -6.902954        -14.215862   \n",
       "3191        -11.637475         -0.827815         -2.013502        -17.781491   \n",
       "3192        -11.752361         -8.040059         12.372583          1.533635   \n",
       "\n",
       "      RDOMESTICMIG2015  RNETMIG2011  RNETMIG2012  RNETMIG2013  RNETMIG2014  \\\n",
       "3188        -14.248864     1.255221    16.243199    -5.295460   -14.075283   \n",
       "3189         -0.564849     0.654527     2.408578    21.160658    16.308671   \n",
       "3190        -12.127022   -18.136812    -5.536861    -7.521840   -14.740608   \n",
       "3191          1.682288   -11.990126    -1.182592    -2.250385   -18.020168   \n",
       "3192          6.935294   -12.032179    -8.040059    12.372583     1.533635   \n",
       "\n",
       "      RNETMIG2015  \n",
       "3188   -14.070195  \n",
       "3189     1.520747  \n",
       "3190   -12.606351  \n",
       "3191     1.441961  \n",
       "3192     6.935294  \n",
       "\n",
       "[5 rows x 100 columns]"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "census_df.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 隨堂練習：哪個州（state）的郡（county）數最多？"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [],
   "source": [
    "def most_counties(census_df):\n",
    "    \"\"\"\n",
    "    >>> most_counties(census_df)\n",
    "    'Texas'\n",
    "    \"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 隨堂練習：僅考慮每州（state）人口最多的三個郡（county）計算人口總和（CENSUS2010POP），哪三個州總和數最多？（請注意 SUMLEV 變數）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [],
   "source": [
    "def top_three_states(census_df):\n",
    "    \"\"\"\n",
    "    >>> top_three_states(census_df)\n",
    "    ['California', 'Texas', 'Illinois']\n",
    "    \"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 隨堂練習：哪個郡（county）在 2010-2015 期間人口改變數量最高？（POPESTIMATE2010:POPESTIMATE2015 這六個變數）\n",
    "\n",
    "提示：如果 6 年的人口數分別為 120, 80, 105, 100, 130, 120 則人口改變數量為 130-80 = 50"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [],
   "source": [
    "def pop_change_most_county(census_df):\n",
    "    \"\"\"\n",
    "    >>> pop_change_most_county(census_df)\n",
    "    'Harris County'\n",
    "    \"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 隨堂練習：篩選出屬於 REGION 1 或 2、開頭名稱為 Washington 並且 POPESTIMATE2015 大於 POPESTIMATE2014 的郡（county）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [],
   "source": [
    "def filter_counties(census_df):\n",
    "    \"\"\"\n",
    "    >>> filter_counties(census_df)\n",
    "             STNAME            CTYNAME\n",
    "    0          Iowa  Washington County\n",
    "    1     Minnesota  Washington County\n",
    "    2  Pennsylvania  Washington County\n",
    "    3  Rhode Island  Washington County\n",
    "    4     Wisconsin  Washington County\n",
    "    \"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## 隨堂練習參考解答\n",
    "\n",
    "[隨堂練習：美國普查參考解答](https://mybinder.org/v2/gh/yaojenkuo/python-data-analysis/master?filepath=suggested_answers%2F04-suggested-answers.ipynb)"
   ]
  }
 ],
 "metadata": {
  "celltoolbar": "Slideshow",
  "kernelspec": {
   "display_name": "Python Web Scraping",
   "language": "python",
   "name": "pywebscraping"
  },
  "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.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}