05-pandas-data-wrangling.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# 成為資料分析師 | Python 與資料科學應用\n",
    "\n",
    "> Pandas 專題：資料處理\n",
    "\n",
    "## 郭耀仁"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "> Tidy datasets are all alike, but every messy dataset is messy in its own way.\n",
    ">\n",
    "> Hadley Wickham"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 2020 總統（副總統）各投票所明細與概況\n",
    "\n",
    "<https://db.cec.gov.tw/histFile?voteCode=20200101P1A1&resourceCode=S1>\n",
    "\n",
    "Source: <https://db.cec.gov.tw/histMain.jsp?voteSel=20200101A1>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 大綱\n",
    "\n",
    "- 盤點單張試算表處理步驟\n",
    "- 盤點多張試算表處理步驟\n",
    "- 輸出"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## 盤點單張試算表處理步驟"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 處理前資料的問題\n",
    "\n",
    "- 合併儲存格\n",
    "- 總計、小計與明細資訊夾雜\n",
    "- 有遺漏值"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 單張試算表處理步驟\n",
    "\n",
    "- 讀入試算表為資料框\n",
    "- 判斷候選人人數\n",
    "- 給定欄位名\n",
    "- 填補行政區缺失\n",
    "- 清理行政區的空字串\n",
    "- 刪除得票數總計、小計列"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 讀入試算表為資料框"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "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>Unnamed: 0</th>\n",
       "      <th>Unnamed: 1</th>\n",
       "      <th>Unnamed: 2</th>\n",
       "      <th>(1)\\n宋楚瑜\\n余湘</th>\n",
       "      <th>(2)\\n韓國瑜\\n張善政</th>\n",
       "      <th>(3)\\n蔡英文\\n賴清德</th>\n",
       "      <th>Unnamed: 6</th>\n",
       "      <th>Unnamed: 7</th>\n",
       "      <th>Unnamed: 8</th>\n",
       "      <th>Unnamed: 9</th>\n",
       "      <th>Unnamed: 10</th>\n",
       "      <th>Unnamed: 11</th>\n",
       "      <th>Unnamed: 12</th>\n",
       "      <th>Unnamed: 13</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>總　計</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>70,769</td>\n",
       "      <td>685,830</td>\n",
       "      <td>875,854</td>\n",
       "      <td>1,632,453</td>\n",
       "      <td>21,381</td>\n",
       "      <td>1,653,834</td>\n",
       "      <td>143</td>\n",
       "      <td>1,653,977</td>\n",
       "      <td>513,287</td>\n",
       "      <td>2,167,264</td>\n",
       "      <td>76.3098</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>松山區</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5,436</td>\n",
       "      <td>55,918</td>\n",
       "      <td>64,207</td>\n",
       "      <td>125,561</td>\n",
       "      <td>1,762</td>\n",
       "      <td>127,323</td>\n",
       "      <td>2</td>\n",
       "      <td>127,325</td>\n",
       "      <td>37,329</td>\n",
       "      <td>164,654</td>\n",
       "      <td>77.3276</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>莊敬里</td>\n",
       "      <td>573.0</td>\n",
       "      <td>36</td>\n",
       "      <td>391</td>\n",
       "      <td>429</td>\n",
       "      <td>856</td>\n",
       "      <td>14</td>\n",
       "      <td>870</td>\n",
       "      <td>0</td>\n",
       "      <td>870</td>\n",
       "      <td>230</td>\n",
       "      <td>1,100</td>\n",
       "      <td>79.0909</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>莊敬里</td>\n",
       "      <td>574.0</td>\n",
       "      <td>46</td>\n",
       "      <td>382</td>\n",
       "      <td>438</td>\n",
       "      <td>866</td>\n",
       "      <td>12</td>\n",
       "      <td>878</td>\n",
       "      <td>0</td>\n",
       "      <td>878</td>\n",
       "      <td>259</td>\n",
       "      <td>1,137</td>\n",
       "      <td>77.2208</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>莊敬里</td>\n",
       "      <td>575.0</td>\n",
       "      <td>48</td>\n",
       "      <td>393</td>\n",
       "      <td>389</td>\n",
       "      <td>830</td>\n",
       "      <td>22</td>\n",
       "      <td>852</td>\n",
       "      <td>0</td>\n",
       "      <td>852</td>\n",
       "      <td>262</td>\n",
       "      <td>1,114</td>\n",
       "      <td>76.4811</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Unnamed: 0 Unnamed: 1  Unnamed: 2 (1)\\n宋楚瑜\\n余湘 (2)\\n韓國瑜\\n張善政 (3)\\n蔡英文\\n賴清德  \\\n",
       "0        總　計        NaN         NaN       70,769       685,830       875,854   \n",
       "1       　松山區        NaN         NaN        5,436        55,918        64,207   \n",
       "2        NaN        莊敬里       573.0           36           391           429   \n",
       "3        NaN        莊敬里       574.0           46           382           438   \n",
       "4        NaN        莊敬里       575.0           48           393           389   \n",
       "\n",
       "  Unnamed: 6 Unnamed: 7 Unnamed: 8  Unnamed: 9 Unnamed: 10 Unnamed: 11  \\\n",
       "0  1,632,453     21,381  1,653,834         143   1,653,977     513,287   \n",
       "1    125,561      1,762    127,323           2     127,325      37,329   \n",
       "2        856         14        870           0         870         230   \n",
       "3        866         12        878           0         878         259   \n",
       "4        830         22        852           0         852         262   \n",
       "\n",
       "  Unnamed: 12  Unnamed: 13  \n",
       "0   2,167,264      76.3098  \n",
       "1     164,654      77.3276  \n",
       "2       1,100      79.0909  \n",
       "3       1,137      77.2208  \n",
       "4       1,114      76.4811  "
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "file_name = \"總統-A05-4-候選人得票數一覽表-各投開票所(臺北市).xls\"\n",
    "file_path = \"presidential-2020/{}\".format(file_name)\n",
    "xls_df = pd.read_excel(file_path, skiprows=[0, 1, 3, 4])\n",
    "xls_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 判斷候選人人數"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['(1)\\n宋楚瑜\\n余湘', '(2)\\n韓國瑜\\n張善政', '(3)\\n蔡英文\\n賴清德']\n"
     ]
    }
   ],
   "source": [
    "column_names = list(xls_df.columns)\n",
    "n_candidates = len(column_names) - 11\n",
    "candidate_numbers_names = column_names[3:(3+n_candidates)]\n",
    "print(candidate_numbers_names)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 給定欄位名"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>town</th>\n",
       "      <th>village</th>\n",
       "      <th>office</th>\n",
       "      <th>(1)\\n宋楚瑜\\n余湘</th>\n",
       "      <th>(2)\\n韓國瑜\\n張善政</th>\n",
       "      <th>(3)\\n蔡英文\\n賴清德</th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "      <th>E</th>\n",
       "      <th>F</th>\n",
       "      <th>G</th>\n",
       "      <th>H</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>總　計</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>70,769</td>\n",
       "      <td>685,830</td>\n",
       "      <td>875,854</td>\n",
       "      <td>1,632,453</td>\n",
       "      <td>21,381</td>\n",
       "      <td>1,653,834</td>\n",
       "      <td>143</td>\n",
       "      <td>1,653,977</td>\n",
       "      <td>513,287</td>\n",
       "      <td>2,167,264</td>\n",
       "      <td>76.3098</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>松山區</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5,436</td>\n",
       "      <td>55,918</td>\n",
       "      <td>64,207</td>\n",
       "      <td>125,561</td>\n",
       "      <td>1,762</td>\n",
       "      <td>127,323</td>\n",
       "      <td>2</td>\n",
       "      <td>127,325</td>\n",
       "      <td>37,329</td>\n",
       "      <td>164,654</td>\n",
       "      <td>77.3276</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>莊敬里</td>\n",
       "      <td>573.0</td>\n",
       "      <td>36</td>\n",
       "      <td>391</td>\n",
       "      <td>429</td>\n",
       "      <td>856</td>\n",
       "      <td>14</td>\n",
       "      <td>870</td>\n",
       "      <td>0</td>\n",
       "      <td>870</td>\n",
       "      <td>230</td>\n",
       "      <td>1,100</td>\n",
       "      <td>79.0909</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>莊敬里</td>\n",
       "      <td>574.0</td>\n",
       "      <td>46</td>\n",
       "      <td>382</td>\n",
       "      <td>438</td>\n",
       "      <td>866</td>\n",
       "      <td>12</td>\n",
       "      <td>878</td>\n",
       "      <td>0</td>\n",
       "      <td>878</td>\n",
       "      <td>259</td>\n",
       "      <td>1,137</td>\n",
       "      <td>77.2208</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>莊敬里</td>\n",
       "      <td>575.0</td>\n",
       "      <td>48</td>\n",
       "      <td>393</td>\n",
       "      <td>389</td>\n",
       "      <td>830</td>\n",
       "      <td>22</td>\n",
       "      <td>852</td>\n",
       "      <td>0</td>\n",
       "      <td>852</td>\n",
       "      <td>262</td>\n",
       "      <td>1,114</td>\n",
       "      <td>76.4811</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   town village  office (1)\\n宋楚瑜\\n余湘 (2)\\n韓國瑜\\n張善政 (3)\\n蔡英文\\n賴清德          A  \\\n",
       "0   總　計     NaN     NaN       70,769       685,830       875,854  1,632,453   \n",
       "1  　松山區     NaN     NaN        5,436        55,918        64,207    125,561   \n",
       "2   NaN     莊敬里   573.0           36           391           429        856   \n",
       "3   NaN     莊敬里   574.0           46           382           438        866   \n",
       "4   NaN     莊敬里   575.0           48           393           389        830   \n",
       "\n",
       "        B          C    D          E        F          G        H  \n",
       "0  21,381  1,653,834  143  1,653,977  513,287  2,167,264  76.3098  \n",
       "1   1,762    127,323    2    127,325   37,329    164,654  77.3276  \n",
       "2      14        870    0        870      230      1,100  79.0909  \n",
       "3      12        878    0        878      259      1,137  77.2208  \n",
       "4      22        852    0        852      262      1,114  76.4811  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from string import ascii_uppercase\n",
    "\n",
    "column_names = [\"town\", \"village\", \"office\"] + candidate_numbers_names + list(ascii_uppercase[:8])\n",
    "xls_df.columns = column_names\n",
    "xls_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 填補行政區缺失"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "RJKxbVt_w1sG",
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [],
   "source": [
    "imputed_town = xls_df['town'].fillna(method='ffill')\n",
    "xls_df = xls_df.drop('town', axis=1)\n",
    "xls_df.insert(0, 'town', imputed_town)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "RJKxbVt_w1sG",
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "#imputed_town = list(xls_df[\"town\"].values)\n",
    "#for i in range(len(imputed_town)):\n",
    "#    if len(imputed_town[i]) == 0:\n",
    "#        imputed_town[i] = imputed_town[i-1]\n",
    "#xls_df[\"town\"] = imputed_town"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "RJKxbVt_w1sG",
    "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>town</th>\n",
       "      <th>village</th>\n",
       "      <th>office</th>\n",
       "      <th>(1)\\n宋楚瑜\\n余湘</th>\n",
       "      <th>(2)\\n韓國瑜\\n張善政</th>\n",
       "      <th>(3)\\n蔡英文\\n賴清德</th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "      <th>E</th>\n",
       "      <th>F</th>\n",
       "      <th>G</th>\n",
       "      <th>H</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>總　計</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>70,769</td>\n",
       "      <td>685,830</td>\n",
       "      <td>875,854</td>\n",
       "      <td>1,632,453</td>\n",
       "      <td>21,381</td>\n",
       "      <td>1,653,834</td>\n",
       "      <td>143</td>\n",
       "      <td>1,653,977</td>\n",
       "      <td>513,287</td>\n",
       "      <td>2,167,264</td>\n",
       "      <td>76.3098</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>松山區</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5,436</td>\n",
       "      <td>55,918</td>\n",
       "      <td>64,207</td>\n",
       "      <td>125,561</td>\n",
       "      <td>1,762</td>\n",
       "      <td>127,323</td>\n",
       "      <td>2</td>\n",
       "      <td>127,325</td>\n",
       "      <td>37,329</td>\n",
       "      <td>164,654</td>\n",
       "      <td>77.3276</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>松山區</td>\n",
       "      <td>莊敬里</td>\n",
       "      <td>573.0</td>\n",
       "      <td>36</td>\n",
       "      <td>391</td>\n",
       "      <td>429</td>\n",
       "      <td>856</td>\n",
       "      <td>14</td>\n",
       "      <td>870</td>\n",
       "      <td>0</td>\n",
       "      <td>870</td>\n",
       "      <td>230</td>\n",
       "      <td>1,100</td>\n",
       "      <td>79.0909</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>松山區</td>\n",
       "      <td>莊敬里</td>\n",
       "      <td>574.0</td>\n",
       "      <td>46</td>\n",
       "      <td>382</td>\n",
       "      <td>438</td>\n",
       "      <td>866</td>\n",
       "      <td>12</td>\n",
       "      <td>878</td>\n",
       "      <td>0</td>\n",
       "      <td>878</td>\n",
       "      <td>259</td>\n",
       "      <td>1,137</td>\n",
       "      <td>77.2208</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>松山區</td>\n",
       "      <td>莊敬里</td>\n",
       "      <td>575.0</td>\n",
       "      <td>48</td>\n",
       "      <td>393</td>\n",
       "      <td>389</td>\n",
       "      <td>830</td>\n",
       "      <td>22</td>\n",
       "      <td>852</td>\n",
       "      <td>0</td>\n",
       "      <td>852</td>\n",
       "      <td>262</td>\n",
       "      <td>1,114</td>\n",
       "      <td>76.4811</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   town village  office (1)\\n宋楚瑜\\n余湘 (2)\\n韓國瑜\\n張善政 (3)\\n蔡英文\\n賴清德          A  \\\n",
       "0   總　計     NaN     NaN       70,769       685,830       875,854  1,632,453   \n",
       "1  　松山區     NaN     NaN        5,436        55,918        64,207    125,561   \n",
       "2  　松山區     莊敬里   573.0           36           391           429        856   \n",
       "3  　松山區     莊敬里   574.0           46           382           438        866   \n",
       "4  　松山區     莊敬里   575.0           48           393           389        830   \n",
       "\n",
       "        B          C    D          E        F          G        H  \n",
       "0  21,381  1,653,834  143  1,653,977  513,287  2,167,264  76.3098  \n",
       "1   1,762    127,323    2    127,325   37,329    164,654  77.3276  \n",
       "2      14        870    0        870      230      1,100  79.0909  \n",
       "3      12        878    0        878      259      1,137  77.2208  \n",
       "4      22        852    0        852      262      1,114  76.4811  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "xls_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 清理行政區的空字串"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['總\\u3000計' '\\u3000松山區' '\\u3000信義區' '\\u3000大安區' '\\u3000中山區' '\\u3000中正區'\n",
      " '\\u3000大同區' '\\u3000萬華區' '\\u3000文山區' '\\u3000南港區' '\\u3000內湖區' '\\u3000士林區'\n",
      " '\\u3000北投區']\n"
     ]
    }
   ],
   "source": [
    "print(xls_df[\"town\"].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "xls_df[\"town\"] = xls_df[\"town\"].str.replace('\\u3000', '').str.strip()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['總計' '松山區' '信義區' '大安區' '中山區' '中正區' '大同區' '萬華區' '文山區' '南港區' '內湖區' '士林區'\n",
      " '北投區']\n"
     ]
    }
   ],
   "source": [
    "print(xls_df[\"town\"].unique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 刪除得票數小計、總計列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [],
   "source": [
    "xls_df = xls_df.dropna().reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>town</th>\n",
       "      <th>village</th>\n",
       "      <th>office</th>\n",
       "      <th>(1)\\n宋楚瑜\\n余湘</th>\n",
       "      <th>(2)\\n韓國瑜\\n張善政</th>\n",
       "      <th>(3)\\n蔡英文\\n賴清德</th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "      <th>E</th>\n",
       "      <th>F</th>\n",
       "      <th>G</th>\n",
       "      <th>H</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>松山區</td>\n",
       "      <td>莊敬里</td>\n",
       "      <td>573.0</td>\n",
       "      <td>36</td>\n",
       "      <td>391</td>\n",
       "      <td>429</td>\n",
       "      <td>856</td>\n",
       "      <td>14</td>\n",
       "      <td>870</td>\n",
       "      <td>0</td>\n",
       "      <td>870</td>\n",
       "      <td>230</td>\n",
       "      <td>1,100</td>\n",
       "      <td>79.0909</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>松山區</td>\n",
       "      <td>莊敬里</td>\n",
       "      <td>574.0</td>\n",
       "      <td>46</td>\n",
       "      <td>382</td>\n",
       "      <td>438</td>\n",
       "      <td>866</td>\n",
       "      <td>12</td>\n",
       "      <td>878</td>\n",
       "      <td>0</td>\n",
       "      <td>878</td>\n",
       "      <td>259</td>\n",
       "      <td>1,137</td>\n",
       "      <td>77.2208</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>松山區</td>\n",
       "      <td>莊敬里</td>\n",
       "      <td>575.0</td>\n",
       "      <td>48</td>\n",
       "      <td>393</td>\n",
       "      <td>389</td>\n",
       "      <td>830</td>\n",
       "      <td>22</td>\n",
       "      <td>852</td>\n",
       "      <td>0</td>\n",
       "      <td>852</td>\n",
       "      <td>262</td>\n",
       "      <td>1,114</td>\n",
       "      <td>76.4811</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>松山區</td>\n",
       "      <td>莊敬里</td>\n",
       "      <td>576.0</td>\n",
       "      <td>43</td>\n",
       "      <td>389</td>\n",
       "      <td>462</td>\n",
       "      <td>894</td>\n",
       "      <td>14</td>\n",
       "      <td>908</td>\n",
       "      <td>0</td>\n",
       "      <td>908</td>\n",
       "      <td>271</td>\n",
       "      <td>1,179</td>\n",
       "      <td>77.0144</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>松山區</td>\n",
       "      <td>東榮里</td>\n",
       "      <td>577.0</td>\n",
       "      <td>38</td>\n",
       "      <td>431</td>\n",
       "      <td>545</td>\n",
       "      <td>1,014</td>\n",
       "      <td>18</td>\n",
       "      <td>1,032</td>\n",
       "      <td>0</td>\n",
       "      <td>1,032</td>\n",
       "      <td>272</td>\n",
       "      <td>1,304</td>\n",
       "      <td>79.1411</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  town village  office (1)\\n宋楚瑜\\n余湘 (2)\\n韓國瑜\\n張善政 (3)\\n蔡英文\\n賴清德      A   B  \\\n",
       "0  松山區     莊敬里   573.0           36           391           429    856  14   \n",
       "1  松山區     莊敬里   574.0           46           382           438    866  12   \n",
       "2  松山區     莊敬里   575.0           48           393           389    830  22   \n",
       "3  松山區     莊敬里   576.0           43           389           462    894  14   \n",
       "4  松山區     東榮里   577.0           38           431           545  1,014  18   \n",
       "\n",
       "       C  D      E    F      G        H  \n",
       "0    870  0    870  230  1,100  79.0909  \n",
       "1    878  0    878  259  1,137  77.2208  \n",
       "2    852  0    852  262  1,114  76.4811  \n",
       "3    908  0    908  271  1,179  77.0144  \n",
       "4  1,032  0  1,032  272  1,304  79.1411  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "xls_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 將處理單張試算表的程式封裝為一個函數 `get_tidy_data()`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from string import ascii_uppercase\n",
    "\n",
    "def get_tidy_data(file_path):\n",
    "    xls_df = pd.read_excel(file_path, skiprows=[0, 1, 3, 4])\n",
    "    column_names = list(xls_df.columns)\n",
    "    n_candidates = len(column_names) - 11\n",
    "    candidate_numbers_names = column_names[3:(3+n_candidates)]\n",
    "    column_names = [\"town\", \"village\", \"office\"] + candidate_numbers_names + list(ascii_uppercase[:8])\n",
    "    xls_df.columns = column_names\n",
    "    imputed_town = xls_df['town'].fillna(method='ffill')\n",
    "    xls_df = xls_df.drop('town', axis=1)\n",
    "    xls_df.insert(0, 'town', imputed_town)\n",
    "    xls_df[\"town\"] = xls_df[\"town\"].str.replace('\\u3000', '').str.strip()\n",
    "    xls_df = xls_df.dropna().reset_index(drop=True)\n",
    "    return xls_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>town</th>\n",
       "      <th>village</th>\n",
       "      <th>office</th>\n",
       "      <th>(1)\\n宋楚瑜\\n余湘</th>\n",
       "      <th>(2)\\n韓國瑜\\n張善政</th>\n",
       "      <th>(3)\\n蔡英文\\n賴清德</th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "      <th>E</th>\n",
       "      <th>F</th>\n",
       "      <th>G</th>\n",
       "      <th>H</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>板橋區</td>\n",
       "      <td>留侯里</td>\n",
       "      <td>1124.0</td>\n",
       "      <td>58</td>\n",
       "      <td>430</td>\n",
       "      <td>557</td>\n",
       "      <td>1,045</td>\n",
       "      <td>24</td>\n",
       "      <td>1,069</td>\n",
       "      <td>0</td>\n",
       "      <td>1,069</td>\n",
       "      <td>304</td>\n",
       "      <td>1,373</td>\n",
       "      <td>77.8587</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>板橋區</td>\n",
       "      <td>流芳里</td>\n",
       "      <td>1125.0</td>\n",
       "      <td>50</td>\n",
       "      <td>345</td>\n",
       "      <td>534</td>\n",
       "      <td>929</td>\n",
       "      <td>6</td>\n",
       "      <td>935</td>\n",
       "      <td>0</td>\n",
       "      <td>935</td>\n",
       "      <td>311</td>\n",
       "      <td>1,246</td>\n",
       "      <td>75.0401</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>板橋區</td>\n",
       "      <td>赤松里</td>\n",
       "      <td>1126.0</td>\n",
       "      <td>24</td>\n",
       "      <td>218</td>\n",
       "      <td>336</td>\n",
       "      <td>578</td>\n",
       "      <td>15</td>\n",
       "      <td>593</td>\n",
       "      <td>0</td>\n",
       "      <td>593</td>\n",
       "      <td>153</td>\n",
       "      <td>746</td>\n",
       "      <td>79.4906</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>板橋區</td>\n",
       "      <td>黃石里</td>\n",
       "      <td>1127.0</td>\n",
       "      <td>35</td>\n",
       "      <td>248</td>\n",
       "      <td>423</td>\n",
       "      <td>706</td>\n",
       "      <td>13</td>\n",
       "      <td>719</td>\n",
       "      <td>0</td>\n",
       "      <td>719</td>\n",
       "      <td>230</td>\n",
       "      <td>949</td>\n",
       "      <td>75.7640</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>板橋區</td>\n",
       "      <td>挹秀里</td>\n",
       "      <td>1128.0</td>\n",
       "      <td>37</td>\n",
       "      <td>289</td>\n",
       "      <td>351</td>\n",
       "      <td>677</td>\n",
       "      <td>13</td>\n",
       "      <td>690</td>\n",
       "      <td>0</td>\n",
       "      <td>690</td>\n",
       "      <td>188</td>\n",
       "      <td>878</td>\n",
       "      <td>78.5877</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  town village  office (1)\\n宋楚瑜\\n余湘 (2)\\n韓國瑜\\n張善政 (3)\\n蔡英文\\n賴清德      A   B  \\\n",
       "0  板橋區     留侯里  1124.0           58           430           557  1,045  24   \n",
       "1  板橋區     流芳里  1125.0           50           345           534    929   6   \n",
       "2  板橋區     赤松里  1126.0           24           218           336    578  15   \n",
       "3  板橋區     黃石里  1127.0           35           248           423    706  13   \n",
       "4  板橋區     挹秀里  1128.0           37           289           351    677  13   \n",
       "\n",
       "       C  D      E    F      G        H  \n",
       "0  1,069  0  1,069  304  1,373  77.8587  \n",
       "1    935  0    935  311  1,246  75.0401  \n",
       "2    593  0    593  153    746  79.4906  \n",
       "3    719  0    719  230    949  75.7640  \n",
       "4    690  0    690  188    878  78.5877  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "file_name = \"總統-A05-4-候選人得票數一覽表-各投開票所(新北市).xls\"\n",
    "file_path = \"presidential-2020/{}\".format(file_name)\n",
    "get_tidy_data(file_path).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## 盤點多張試算表處理步驟"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 單張試算表已經處理妥當\n",
    "\n",
    "更換資料來源，就可以讀入 22 個縣市的投票所明細"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 準備 ITERABLES"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [],
   "source": [
    "counties = [\"臺北市\", \"新北市\", \"桃園市\", \"臺中市\", \"臺南市\", \"高雄市\", \"新竹縣\", \"苗栗縣\", \"彰化縣\", \"南投縣\", \"雲林縣\", \"嘉義縣\", \"屏東縣\", \"宜蘭縣\", \"花蓮縣\", \"臺東縣\", \"澎湖縣\", \"基隆市\", \"新竹市\", \"嘉義市\", \"金門縣\", \"連江縣\"]\n",
    "file_names = [\"總統-A05-4-候選人得票數一覽表-各投開票所({}).xls\".format(county) for county in counties]\n",
    "file_paths = [\"presidential-2020/{}\".format(file_name) for file_name in file_names]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 讀入 22 個縣市的投票所明細"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "現在正在處理臺北市的資料...\n",
      "資料外觀為： (1728, 14)\n",
      "現在正在處理新北市的資料...\n",
      "資料外觀為： (2559, 14)\n",
      "現在正在處理桃園市的資料...\n",
      "資料外觀為： (1242, 14)\n",
      "現在正在處理臺中市的資料...\n",
      "資料外觀為： (1849, 14)\n",
      "現在正在處理臺南市的資料...\n",
      "資料外觀為： (1507, 14)\n",
      "現在正在處理高雄市的資料...\n",
      "資料外觀為： (2006, 14)\n",
      "現在正在處理新竹縣的資料...\n",
      "資料外觀為： (445, 14)\n",
      "現在正在處理苗栗縣的資料...\n",
      "資料外觀為： (480, 14)\n",
      "現在正在處理彰化縣的資料...\n",
      "資料外觀為： (1071, 14)\n",
      "現在正在處理南投縣的資料...\n",
      "資料外觀為： (492, 14)\n",
      "現在正在處理雲林縣的資料...\n",
      "資料外觀為： (609, 14)\n",
      "現在正在處理嘉義縣的資料...\n",
      "資料外觀為： (530, 14)\n",
      "現在正在處理屏東縣的資料...\n",
      "資料外觀為： (705, 14)\n",
      "現在正在處理宜蘭縣的資料...\n",
      "資料外觀為： (421, 14)\n",
      "現在正在處理花蓮縣的資料...\n",
      "資料外觀為： (324, 14)\n",
      "現在正在處理臺東縣的資料...\n",
      "資料外觀為： (236, 14)\n",
      "現在正在處理澎湖縣的資料...\n",
      "資料外觀為： (119, 14)\n",
      "現在正在處理基隆市的資料...\n",
      "資料外觀為： (274, 14)\n",
      "現在正在處理新竹市的資料...\n",
      "資料外觀為： (344, 14)\n",
      "現在正在處理嘉義市的資料...\n",
      "資料外觀為： (191, 14)\n",
      "現在正在處理金門縣的資料...\n",
      "資料外觀為： (84, 14)\n",
      "現在正在處理連江縣的資料...\n",
      "資料外觀為： (10, 14)\n"
     ]
    }
   ],
   "source": [
    "df_dict = {}\n",
    "for file_path, county in zip(file_paths, counties):\n",
    "    tidy_df = get_tidy_data(file_path)\n",
    "    df_dict[county] = tidy_df\n",
    "    print(\"現在正在處理{}的資料...\".format(county))\n",
    "    print(\"資料外觀為：\", tidy_df.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>town</th>\n",
       "      <th>village</th>\n",
       "      <th>office</th>\n",
       "      <th>(1)\\n宋楚瑜\\n余湘</th>\n",
       "      <th>(2)\\n韓國瑜\\n張善政</th>\n",
       "      <th>(3)\\n蔡英文\\n賴清德</th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "      <th>E</th>\n",
       "      <th>F</th>\n",
       "      <th>G</th>\n",
       "      <th>H</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>松山區</td>\n",
       "      <td>莊敬里</td>\n",
       "      <td>573.0</td>\n",
       "      <td>36</td>\n",
       "      <td>391</td>\n",
       "      <td>429</td>\n",
       "      <td>856</td>\n",
       "      <td>14</td>\n",
       "      <td>870</td>\n",
       "      <td>0</td>\n",
       "      <td>870</td>\n",
       "      <td>230</td>\n",
       "      <td>1,100</td>\n",
       "      <td>79.0909</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>松山區</td>\n",
       "      <td>莊敬里</td>\n",
       "      <td>574.0</td>\n",
       "      <td>46</td>\n",
       "      <td>382</td>\n",
       "      <td>438</td>\n",
       "      <td>866</td>\n",
       "      <td>12</td>\n",
       "      <td>878</td>\n",
       "      <td>0</td>\n",
       "      <td>878</td>\n",
       "      <td>259</td>\n",
       "      <td>1,137</td>\n",
       "      <td>77.2208</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>松山區</td>\n",
       "      <td>莊敬里</td>\n",
       "      <td>575.0</td>\n",
       "      <td>48</td>\n",
       "      <td>393</td>\n",
       "      <td>389</td>\n",
       "      <td>830</td>\n",
       "      <td>22</td>\n",
       "      <td>852</td>\n",
       "      <td>0</td>\n",
       "      <td>852</td>\n",
       "      <td>262</td>\n",
       "      <td>1,114</td>\n",
       "      <td>76.4811</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>松山區</td>\n",
       "      <td>莊敬里</td>\n",
       "      <td>576.0</td>\n",
       "      <td>43</td>\n",
       "      <td>389</td>\n",
       "      <td>462</td>\n",
       "      <td>894</td>\n",
       "      <td>14</td>\n",
       "      <td>908</td>\n",
       "      <td>0</td>\n",
       "      <td>908</td>\n",
       "      <td>271</td>\n",
       "      <td>1,179</td>\n",
       "      <td>77.0144</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>松山區</td>\n",
       "      <td>東榮里</td>\n",
       "      <td>577.0</td>\n",
       "      <td>38</td>\n",
       "      <td>431</td>\n",
       "      <td>545</td>\n",
       "      <td>1,014</td>\n",
       "      <td>18</td>\n",
       "      <td>1,032</td>\n",
       "      <td>0</td>\n",
       "      <td>1,032</td>\n",
       "      <td>272</td>\n",
       "      <td>1,304</td>\n",
       "      <td>79.1411</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  town village  office (1)\\n宋楚瑜\\n余湘 (2)\\n韓國瑜\\n張善政 (3)\\n蔡英文\\n賴清德      A   B  \\\n",
       "0  松山區     莊敬里   573.0           36           391           429    856  14   \n",
       "1  松山區     莊敬里   574.0           46           382           438    866  12   \n",
       "2  松山區     莊敬里   575.0           48           393           389    830  22   \n",
       "3  松山區     莊敬里   576.0           43           389           462    894  14   \n",
       "4  松山區     東榮里   577.0           38           431           545  1,014  18   \n",
       "\n",
       "       C  D      E    F      G        H  \n",
       "0    870  0    870  230  1,100  79.0909  \n",
       "1    878  0    878  259  1,137  77.2208  \n",
       "2    852  0    852  262  1,114  76.4811  \n",
       "3    908  0    908  271  1,179  77.0144  \n",
       "4  1,032  0  1,032  272  1,304  79.1411  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_dict['臺北市'].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>town</th>\n",
       "      <th>village</th>\n",
       "      <th>office</th>\n",
       "      <th>(1)\\n宋楚瑜\\n余湘</th>\n",
       "      <th>(2)\\n韓國瑜\\n張善政</th>\n",
       "      <th>(3)\\n蔡英文\\n賴清德</th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "      <th>E</th>\n",
       "      <th>F</th>\n",
       "      <th>G</th>\n",
       "      <th>H</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>板橋區</td>\n",
       "      <td>留侯里</td>\n",
       "      <td>1124.0</td>\n",
       "      <td>58</td>\n",
       "      <td>430</td>\n",
       "      <td>557</td>\n",
       "      <td>1,045</td>\n",
       "      <td>24</td>\n",
       "      <td>1,069</td>\n",
       "      <td>0</td>\n",
       "      <td>1,069</td>\n",
       "      <td>304</td>\n",
       "      <td>1,373</td>\n",
       "      <td>77.8587</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>板橋區</td>\n",
       "      <td>流芳里</td>\n",
       "      <td>1125.0</td>\n",
       "      <td>50</td>\n",
       "      <td>345</td>\n",
       "      <td>534</td>\n",
       "      <td>929</td>\n",
       "      <td>6</td>\n",
       "      <td>935</td>\n",
       "      <td>0</td>\n",
       "      <td>935</td>\n",
       "      <td>311</td>\n",
       "      <td>1,246</td>\n",
       "      <td>75.0401</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>板橋區</td>\n",
       "      <td>赤松里</td>\n",
       "      <td>1126.0</td>\n",
       "      <td>24</td>\n",
       "      <td>218</td>\n",
       "      <td>336</td>\n",
       "      <td>578</td>\n",
       "      <td>15</td>\n",
       "      <td>593</td>\n",
       "      <td>0</td>\n",
       "      <td>593</td>\n",
       "      <td>153</td>\n",
       "      <td>746</td>\n",
       "      <td>79.4906</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>板橋區</td>\n",
       "      <td>黃石里</td>\n",
       "      <td>1127.0</td>\n",
       "      <td>35</td>\n",
       "      <td>248</td>\n",
       "      <td>423</td>\n",
       "      <td>706</td>\n",
       "      <td>13</td>\n",
       "      <td>719</td>\n",
       "      <td>0</td>\n",
       "      <td>719</td>\n",
       "      <td>230</td>\n",
       "      <td>949</td>\n",
       "      <td>75.7640</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>板橋區</td>\n",
       "      <td>挹秀里</td>\n",
       "      <td>1128.0</td>\n",
       "      <td>37</td>\n",
       "      <td>289</td>\n",
       "      <td>351</td>\n",
       "      <td>677</td>\n",
       "      <td>13</td>\n",
       "      <td>690</td>\n",
       "      <td>0</td>\n",
       "      <td>690</td>\n",
       "      <td>188</td>\n",
       "      <td>878</td>\n",
       "      <td>78.5877</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  town village  office (1)\\n宋楚瑜\\n余湘 (2)\\n韓國瑜\\n張善政 (3)\\n蔡英文\\n賴清德      A   B  \\\n",
       "0  板橋區     留侯里  1124.0           58           430           557  1,045  24   \n",
       "1  板橋區     流芳里  1125.0           50           345           534    929   6   \n",
       "2  板橋區     赤松里  1126.0           24           218           336    578  15   \n",
       "3  板橋區     黃石里  1127.0           35           248           423    706  13   \n",
       "4  板橋區     挹秀里  1128.0           37           289           351    677  13   \n",
       "\n",
       "       C  D      E    F      G        H  \n",
       "0  1,069  0  1,069  304  1,373  77.8587  \n",
       "1    935  0    935  311  1,246  75.0401  \n",
       "2    593  0    593  153    746  79.4906  \n",
       "3    719  0    719  230    949  75.7640  \n",
       "4    690  0    690  188    878  78.5877  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_dict['新北市'].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 有什麼不足的嗎？\n",
    "\n",
    "使用 22 個資料框無法做「跨縣市」的分析"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 採用垂直合併 22 個資料框"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 多張試算表處理步驟\n",
    "\n",
    "- 將歸屬於「投票所」的變數欄位捨棄\n",
    "- 轉置資料框將得票數歸屬到「候選人」\n",
    "- 垂直合併 22 個資料框\n",
    "- 新增「候選人號碼」與「候選人姓名」\n",
    "- 新增「政黨」\n",
    "- 調整變數的型別"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 將歸屬於「投票所」的變數欄位捨棄"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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>town</th>\n",
       "      <th>village</th>\n",
       "      <th>office</th>\n",
       "      <th>(1)\\n宋楚瑜\\n余湘</th>\n",
       "      <th>(2)\\n韓國瑜\\n張善政</th>\n",
       "      <th>(3)\\n蔡英文\\n賴清德</th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "      <th>E</th>\n",
       "      <th>F</th>\n",
       "      <th>G</th>\n",
       "      <th>H</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>松山區</td>\n",
       "      <td>莊敬里</td>\n",
       "      <td>573.0</td>\n",
       "      <td>36</td>\n",
       "      <td>391</td>\n",
       "      <td>429</td>\n",
       "      <td>856</td>\n",
       "      <td>14</td>\n",
       "      <td>870</td>\n",
       "      <td>0</td>\n",
       "      <td>870</td>\n",
       "      <td>230</td>\n",
       "      <td>1,100</td>\n",
       "      <td>79.0909</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>松山區</td>\n",
       "      <td>莊敬里</td>\n",
       "      <td>574.0</td>\n",
       "      <td>46</td>\n",
       "      <td>382</td>\n",
       "      <td>438</td>\n",
       "      <td>866</td>\n",
       "      <td>12</td>\n",
       "      <td>878</td>\n",
       "      <td>0</td>\n",
       "      <td>878</td>\n",
       "      <td>259</td>\n",
       "      <td>1,137</td>\n",
       "      <td>77.2208</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>松山區</td>\n",
       "      <td>莊敬里</td>\n",
       "      <td>575.0</td>\n",
       "      <td>48</td>\n",
       "      <td>393</td>\n",
       "      <td>389</td>\n",
       "      <td>830</td>\n",
       "      <td>22</td>\n",
       "      <td>852</td>\n",
       "      <td>0</td>\n",
       "      <td>852</td>\n",
       "      <td>262</td>\n",
       "      <td>1,114</td>\n",
       "      <td>76.4811</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>松山區</td>\n",
       "      <td>莊敬里</td>\n",
       "      <td>576.0</td>\n",
       "      <td>43</td>\n",
       "      <td>389</td>\n",
       "      <td>462</td>\n",
       "      <td>894</td>\n",
       "      <td>14</td>\n",
       "      <td>908</td>\n",
       "      <td>0</td>\n",
       "      <td>908</td>\n",
       "      <td>271</td>\n",
       "      <td>1,179</td>\n",
       "      <td>77.0144</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>松山區</td>\n",
       "      <td>東榮里</td>\n",
       "      <td>577.0</td>\n",
       "      <td>38</td>\n",
       "      <td>431</td>\n",
       "      <td>545</td>\n",
       "      <td>1,014</td>\n",
       "      <td>18</td>\n",
       "      <td>1,032</td>\n",
       "      <td>0</td>\n",
       "      <td>1,032</td>\n",
       "      <td>272</td>\n",
       "      <td>1,304</td>\n",
       "      <td>79.1411</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  town village  office (1)\\n宋楚瑜\\n余湘 (2)\\n韓國瑜\\n張善政 (3)\\n蔡英文\\n賴清德      A   B  \\\n",
       "0  松山區     莊敬里   573.0           36           391           429    856  14   \n",
       "1  松山區     莊敬里   574.0           46           382           438    866  12   \n",
       "2  松山區     莊敬里   575.0           48           393           389    830  22   \n",
       "3  松山區     莊敬里   576.0           43           389           462    894  14   \n",
       "4  松山區     東榮里   577.0           38           431           545  1,014  18   \n",
       "\n",
       "       C  D      E    F      G        H  \n",
       "0    870  0    870  230  1,100  79.0909  \n",
       "1    878  0    878  259  1,137  77.2208  \n",
       "2    852  0    852  262  1,114  76.4811  \n",
       "3    908  0    908  271  1,179  77.0144  \n",
       "4  1,032  0  1,032  272  1,304  79.1411  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tpe = df_dict[\"臺北市\"]\n",
    "tpe.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "tpe = tpe.drop(labels = [\"A\", \"B\", \"C\", \"D\", \"E\", \"F\", \"G\", \"H\"], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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>town</th>\n",
       "      <th>village</th>\n",
       "      <th>office</th>\n",
       "      <th>(1)\\n宋楚瑜\\n余湘</th>\n",
       "      <th>(2)\\n韓國瑜\\n張善政</th>\n",
       "      <th>(3)\\n蔡英文\\n賴清德</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>松山區</td>\n",
       "      <td>莊敬里</td>\n",
       "      <td>573.0</td>\n",
       "      <td>36</td>\n",
       "      <td>391</td>\n",
       "      <td>429</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>松山區</td>\n",
       "      <td>莊敬里</td>\n",
       "      <td>574.0</td>\n",
       "      <td>46</td>\n",
       "      <td>382</td>\n",
       "      <td>438</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>松山區</td>\n",
       "      <td>莊敬里</td>\n",
       "      <td>575.0</td>\n",
       "      <td>48</td>\n",
       "      <td>393</td>\n",
       "      <td>389</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>松山區</td>\n",
       "      <td>莊敬里</td>\n",
       "      <td>576.0</td>\n",
       "      <td>43</td>\n",
       "      <td>389</td>\n",
       "      <td>462</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>松山區</td>\n",
       "      <td>東榮里</td>\n",
       "      <td>577.0</td>\n",
       "      <td>38</td>\n",
       "      <td>431</td>\n",
       "      <td>545</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  town village  office (1)\\n宋楚瑜\\n余湘 (2)\\n韓國瑜\\n張善政 (3)\\n蔡英文\\n賴清德\n",
       "0  松山區     莊敬里   573.0           36           391           429\n",
       "1  松山區     莊敬里   574.0           46           382           438\n",
       "2  松山區     莊敬里   575.0           48           393           389\n",
       "3  松山區     莊敬里   576.0           43           389           462\n",
       "4  松山區     東榮里   577.0           38           431           545"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tpe.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 轉置資料框將得票數歸屬到「候選人」"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "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>town</th>\n",
       "      <th>village</th>\n",
       "      <th>office</th>\n",
       "      <th>candidate_info</th>\n",
       "      <th>votes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>松山區</td>\n",
       "      <td>莊敬里</td>\n",
       "      <td>573.0</td>\n",
       "      <td>(1)\\n宋楚瑜\\n余湘</td>\n",
       "      <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>松山區</td>\n",
       "      <td>莊敬里</td>\n",
       "      <td>574.0</td>\n",
       "      <td>(1)\\n宋楚瑜\\n余湘</td>\n",
       "      <td>46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>松山區</td>\n",
       "      <td>莊敬里</td>\n",
       "      <td>575.0</td>\n",
       "      <td>(1)\\n宋楚瑜\\n余湘</td>\n",
       "      <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>松山區</td>\n",
       "      <td>莊敬里</td>\n",
       "      <td>576.0</td>\n",
       "      <td>(1)\\n宋楚瑜\\n余湘</td>\n",
       "      <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>松山區</td>\n",
       "      <td>東榮里</td>\n",
       "      <td>577.0</td>\n",
       "      <td>(1)\\n宋楚瑜\\n余湘</td>\n",
       "      <td>38</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  town village  office candidate_info votes\n",
       "0  松山區     莊敬里   573.0   (1)\\n宋楚瑜\\n余湘    36\n",
       "1  松山區     莊敬里   574.0   (1)\\n宋楚瑜\\n余湘    46\n",
       "2  松山區     莊敬里   575.0   (1)\\n宋楚瑜\\n余湘    48\n",
       "3  松山區     莊敬里   576.0   (1)\\n宋楚瑜\\n余湘    43\n",
       "4  松山區     東榮里   577.0   (1)\\n宋楚瑜\\n余湘    38"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "candidate_infos = list(tpe.columns[3:])\n",
    "tpe = tpe.melt(id_vars=['town', 'village', 'office'], value_vars=candidate_infos, var_name=\"candidate_info\", value_name='votes')\n",
    "tpe.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 垂直合併 22 個資料框"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [],
   "source": [
    "presidential_votes = pd.DataFrame()\n",
    "for k in df_dict:\n",
    "    df = df_dict[k]\n",
    "    df = df.drop(labels = [\"A\", \"B\", \"C\", \"D\", \"E\", \"F\", \"G\", \"H\"], axis=1)\n",
    "    candidate_infos = list(df.columns[3:])\n",
    "    df = df.melt(id_vars=['town', 'village', 'office'], value_vars=candidate_infos, var_name=\"candidate_info\", value_name='votes')\n",
    "    df[\"county\"] = k\n",
    "    presidential_votes = presidential_votes.append(df)\n",
    "presidential_votes = presidential_votes.reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(51678, 6)\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>town</th>\n",
       "      <th>village</th>\n",
       "      <th>office</th>\n",
       "      <th>candidate_info</th>\n",
       "      <th>votes</th>\n",
       "      <th>county</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>松山區</td>\n",
       "      <td>莊敬里</td>\n",
       "      <td>573.0</td>\n",
       "      <td>(1)\\n宋楚瑜\\n余湘</td>\n",
       "      <td>36</td>\n",
       "      <td>臺北市</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>松山區</td>\n",
       "      <td>莊敬里</td>\n",
       "      <td>574.0</td>\n",
       "      <td>(1)\\n宋楚瑜\\n余湘</td>\n",
       "      <td>46</td>\n",
       "      <td>臺北市</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>松山區</td>\n",
       "      <td>莊敬里</td>\n",
       "      <td>575.0</td>\n",
       "      <td>(1)\\n宋楚瑜\\n余湘</td>\n",
       "      <td>48</td>\n",
       "      <td>臺北市</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>松山區</td>\n",
       "      <td>莊敬里</td>\n",
       "      <td>576.0</td>\n",
       "      <td>(1)\\n宋楚瑜\\n余湘</td>\n",
       "      <td>43</td>\n",
       "      <td>臺北市</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>松山區</td>\n",
       "      <td>東榮里</td>\n",
       "      <td>577.0</td>\n",
       "      <td>(1)\\n宋楚瑜\\n余湘</td>\n",
       "      <td>38</td>\n",
       "      <td>臺北市</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  town village  office candidate_info votes county\n",
       "0  松山區     莊敬里   573.0   (1)\\n宋楚瑜\\n余湘    36    臺北市\n",
       "1  松山區     莊敬里   574.0   (1)\\n宋楚瑜\\n余湘    46    臺北市\n",
       "2  松山區     莊敬里   575.0   (1)\\n宋楚瑜\\n余湘    48    臺北市\n",
       "3  松山區     莊敬里   576.0   (1)\\n宋楚瑜\\n余湘    43    臺北市\n",
       "4  松山區     東榮里   577.0   (1)\\n宋楚瑜\\n余湘    38    臺北市"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(presidential_votes.shape)\n",
    "presidential_votes.head()"
   ]
  },
  {
   "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>town</th>\n",
       "      <th>village</th>\n",
       "      <th>office</th>\n",
       "      <th>candidate_info</th>\n",
       "      <th>votes</th>\n",
       "      <th>county</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>51673</th>\n",
       "      <td>北竿鄉</td>\n",
       "      <td>塘岐村、后沃村</td>\n",
       "      <td>6.0</td>\n",
       "      <td>(3)\\n蔡英文\\n賴清德</td>\n",
       "      <td>121</td>\n",
       "      <td>連江縣</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51674</th>\n",
       "      <td>北竿鄉</td>\n",
       "      <td>橋仔村、芹壁村、坂里村、白沙村</td>\n",
       "      <td>7.0</td>\n",
       "      <td>(3)\\n蔡英文\\n賴清德</td>\n",
       "      <td>80</td>\n",
       "      <td>連江縣</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51675</th>\n",
       "      <td>莒光鄉</td>\n",
       "      <td>青帆村、田沃村、西坵村</td>\n",
       "      <td>8.0</td>\n",
       "      <td>(3)\\n蔡英文\\n賴清德</td>\n",
       "      <td>43</td>\n",
       "      <td>連江縣</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51676</th>\n",
       "      <td>莒光鄉</td>\n",
       "      <td>福正村、大坪村</td>\n",
       "      <td>9.0</td>\n",
       "      <td>(3)\\n蔡英文\\n賴清德</td>\n",
       "      <td>62</td>\n",
       "      <td>連江縣</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51677</th>\n",
       "      <td>東引鄉</td>\n",
       "      <td>中柳村、樂華村</td>\n",
       "      <td>10.0</td>\n",
       "      <td>(3)\\n蔡英文\\n賴清德</td>\n",
       "      <td>136</td>\n",
       "      <td>連江縣</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      town          village  office candidate_info votes county\n",
       "51673  北竿鄉          塘岐村、后沃村     6.0  (3)\\n蔡英文\\n賴清德   121    連江縣\n",
       "51674  北竿鄉  橋仔村、芹壁村、坂里村、白沙村     7.0  (3)\\n蔡英文\\n賴清德    80    連江縣\n",
       "51675  莒光鄉      青帆村、田沃村、西坵村     8.0  (3)\\n蔡英文\\n賴清德    43    連江縣\n",
       "51676  莒光鄉          福正村、大坪村     9.0  (3)\\n蔡英文\\n賴清德    62    連江縣\n",
       "51677  東引鄉          中柳村、樂華村    10.0  (3)\\n蔡英文\\n賴清德   136    連江縣"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "presidential_votes.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 新增「候選人號碼」與「候選人姓名」"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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>town</th>\n",
       "      <th>village</th>\n",
       "      <th>office</th>\n",
       "      <th>candidate_info</th>\n",
       "      <th>votes</th>\n",
       "      <th>county</th>\n",
       "      <th>number</th>\n",
       "      <th>candidates</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>松山區</td>\n",
       "      <td>莊敬里</td>\n",
       "      <td>573.0</td>\n",
       "      <td>(1)\\n宋楚瑜\\n余湘</td>\n",
       "      <td>36</td>\n",
       "      <td>臺北市</td>\n",
       "      <td>1</td>\n",
       "      <td>宋楚瑜/余湘</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>松山區</td>\n",
       "      <td>莊敬里</td>\n",
       "      <td>574.0</td>\n",
       "      <td>(1)\\n宋楚瑜\\n余湘</td>\n",
       "      <td>46</td>\n",
       "      <td>臺北市</td>\n",
       "      <td>1</td>\n",
       "      <td>宋楚瑜/余湘</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>松山區</td>\n",
       "      <td>莊敬里</td>\n",
       "      <td>575.0</td>\n",
       "      <td>(1)\\n宋楚瑜\\n余湘</td>\n",
       "      <td>48</td>\n",
       "      <td>臺北市</td>\n",
       "      <td>1</td>\n",
       "      <td>宋楚瑜/余湘</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>松山區</td>\n",
       "      <td>莊敬里</td>\n",
       "      <td>576.0</td>\n",
       "      <td>(1)\\n宋楚瑜\\n余湘</td>\n",
       "      <td>43</td>\n",
       "      <td>臺北市</td>\n",
       "      <td>1</td>\n",
       "      <td>宋楚瑜/余湘</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>松山區</td>\n",
       "      <td>東榮里</td>\n",
       "      <td>577.0</td>\n",
       "      <td>(1)\\n宋楚瑜\\n余湘</td>\n",
       "      <td>38</td>\n",
       "      <td>臺北市</td>\n",
       "      <td>1</td>\n",
       "      <td>宋楚瑜/余湘</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  town village  office candidate_info votes county number candidates\n",
       "0  松山區     莊敬里   573.0   (1)\\n宋楚瑜\\n余湘    36    臺北市      1     宋楚瑜/余湘\n",
       "1  松山區     莊敬里   574.0   (1)\\n宋楚瑜\\n余湘    46    臺北市      1     宋楚瑜/余湘\n",
       "2  松山區     莊敬里   575.0   (1)\\n宋楚瑜\\n余湘    48    臺北市      1     宋楚瑜/余湘\n",
       "3  松山區     莊敬里   576.0   (1)\\n宋楚瑜\\n余湘    43    臺北市      1     宋楚瑜/余湘\n",
       "4  松山區     東榮里   577.0   (1)\\n宋楚瑜\\n余湘    38    臺北市      1     宋楚瑜/余湘"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "split_candidate_info = presidential_votes[\"candidate_info\"].str.split(\"\\n\", expand=True)\n",
    "presidential_votes[\"number\"] = split_candidate_info[0].str.replace('\\(', '').str.replace('\\)', '')\n",
    "presidential_votes[\"candidates\"] = split_candidate_info[1].str.cat(split_candidate_info[2], '/')\n",
    "presidential_votes.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 新增「政黨」"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [],
   "source": [
    "def get_party(x):\n",
    "    if x == '1':\n",
    "        party = '親民黨'\n",
    "    elif x == '2':\n",
    "        party = '中國國民黨'\n",
    "    elif x == '3':\n",
    "        party = '民主進步黨'\n",
    "    return party\n",
    "\n",
    "presidential_votes['party'] = presidential_votes['number'].apply(get_party)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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>town</th>\n",
       "      <th>village</th>\n",
       "      <th>office</th>\n",
       "      <th>candidate_info</th>\n",
       "      <th>votes</th>\n",
       "      <th>county</th>\n",
       "      <th>number</th>\n",
       "      <th>candidates</th>\n",
       "      <th>party</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>松山區</td>\n",
       "      <td>莊敬里</td>\n",
       "      <td>573.0</td>\n",
       "      <td>(1)\\n宋楚瑜\\n余湘</td>\n",
       "      <td>36</td>\n",
       "      <td>臺北市</td>\n",
       "      <td>1</td>\n",
       "      <td>宋楚瑜/余湘</td>\n",
       "      <td>親民黨</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>松山區</td>\n",
       "      <td>莊敬里</td>\n",
       "      <td>574.0</td>\n",
       "      <td>(1)\\n宋楚瑜\\n余湘</td>\n",
       "      <td>46</td>\n",
       "      <td>臺北市</td>\n",
       "      <td>1</td>\n",
       "      <td>宋楚瑜/余湘</td>\n",
       "      <td>親民黨</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>松山區</td>\n",
       "      <td>莊敬里</td>\n",
       "      <td>575.0</td>\n",
       "      <td>(1)\\n宋楚瑜\\n余湘</td>\n",
       "      <td>48</td>\n",
       "      <td>臺北市</td>\n",
       "      <td>1</td>\n",
       "      <td>宋楚瑜/余湘</td>\n",
       "      <td>親民黨</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>松山區</td>\n",
       "      <td>莊敬里</td>\n",
       "      <td>576.0</td>\n",
       "      <td>(1)\\n宋楚瑜\\n余湘</td>\n",
       "      <td>43</td>\n",
       "      <td>臺北市</td>\n",
       "      <td>1</td>\n",
       "      <td>宋楚瑜/余湘</td>\n",
       "      <td>親民黨</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>松山區</td>\n",
       "      <td>東榮里</td>\n",
       "      <td>577.0</td>\n",
       "      <td>(1)\\n宋楚瑜\\n余湘</td>\n",
       "      <td>38</td>\n",
       "      <td>臺北市</td>\n",
       "      <td>1</td>\n",
       "      <td>宋楚瑜/余湘</td>\n",
       "      <td>親民黨</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  town village  office candidate_info votes county number candidates party\n",
       "0  松山區     莊敬里   573.0   (1)\\n宋楚瑜\\n余湘    36    臺北市      1     宋楚瑜/余湘   親民黨\n",
       "1  松山區     莊敬里   574.0   (1)\\n宋楚瑜\\n余湘    46    臺北市      1     宋楚瑜/余湘   親民黨\n",
       "2  松山區     莊敬里   575.0   (1)\\n宋楚瑜\\n余湘    48    臺北市      1     宋楚瑜/余湘   親民黨\n",
       "3  松山區     莊敬里   576.0   (1)\\n宋楚瑜\\n余湘    43    臺北市      1     宋楚瑜/余湘   親民黨\n",
       "4  松山區     東榮里   577.0   (1)\\n宋楚瑜\\n余湘    38    臺北市      1     宋楚瑜/余湘   親民黨"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "presidential_votes.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 調整變數位置為 `[\"county\", \"town\", \"village\", \"office\", \"number\", \"party\", \"candidates\", \"votes\"]`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [],
   "source": [
    "presidential_votes = presidential_votes[[\"county\", \"town\", \"village\", \"office\", \"number\", \"party\", \"candidates\", \"votes\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "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>county</th>\n",
       "      <th>town</th>\n",
       "      <th>village</th>\n",
       "      <th>office</th>\n",
       "      <th>number</th>\n",
       "      <th>party</th>\n",
       "      <th>candidates</th>\n",
       "      <th>votes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>臺北市</td>\n",
       "      <td>松山區</td>\n",
       "      <td>莊敬里</td>\n",
       "      <td>573.0</td>\n",
       "      <td>1</td>\n",
       "      <td>親民黨</td>\n",
       "      <td>宋楚瑜/余湘</td>\n",
       "      <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>臺北市</td>\n",
       "      <td>松山區</td>\n",
       "      <td>莊敬里</td>\n",
       "      <td>574.0</td>\n",
       "      <td>1</td>\n",
       "      <td>親民黨</td>\n",
       "      <td>宋楚瑜/余湘</td>\n",
       "      <td>46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>臺北市</td>\n",
       "      <td>松山區</td>\n",
       "      <td>莊敬里</td>\n",
       "      <td>575.0</td>\n",
       "      <td>1</td>\n",
       "      <td>親民黨</td>\n",
       "      <td>宋楚瑜/余湘</td>\n",
       "      <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>臺北市</td>\n",
       "      <td>松山區</td>\n",
       "      <td>莊敬里</td>\n",
       "      <td>576.0</td>\n",
       "      <td>1</td>\n",
       "      <td>親民黨</td>\n",
       "      <td>宋楚瑜/余湘</td>\n",
       "      <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>臺北市</td>\n",
       "      <td>松山區</td>\n",
       "      <td>東榮里</td>\n",
       "      <td>577.0</td>\n",
       "      <td>1</td>\n",
       "      <td>親民黨</td>\n",
       "      <td>宋楚瑜/余湘</td>\n",
       "      <td>38</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  county town village  office number party candidates votes\n",
       "0    臺北市  松山區     莊敬里   573.0      1   親民黨     宋楚瑜/余湘    36\n",
       "1    臺北市  松山區     莊敬里   574.0      1   親民黨     宋楚瑜/余湘    46\n",
       "2    臺北市  松山區     莊敬里   575.0      1   親民黨     宋楚瑜/余湘    48\n",
       "3    臺北市  松山區     莊敬里   576.0      1   親民黨     宋楚瑜/余湘    43\n",
       "4    臺北市  松山區     東榮里   577.0      1   親民黨     宋楚瑜/余湘    38"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "presidential_votes.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 調整變數的型別"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "presidential_votes['number'] = presidential_votes['number'].astype(int)\n",
    "presidential_votes['office'] = presidential_votes['office'].astype(int)\n",
    "presidential_votes['votes'] = presidential_votes['votes'].astype(str)\n",
    "presidential_votes['votes'] = presidential_votes['votes'].str.replace(',', '').astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 51678 entries, 0 to 51677\n",
      "Data columns (total 8 columns):\n",
      " #   Column      Non-Null Count  Dtype \n",
      "---  ------      --------------  ----- \n",
      " 0   county      51678 non-null  object\n",
      " 1   town        51678 non-null  object\n",
      " 2   village     51678 non-null  object\n",
      " 3   office      51678 non-null  int64 \n",
      " 4   number      51678 non-null  int64 \n",
      " 5   party       51678 non-null  object\n",
      " 6   candidates  51678 non-null  object\n",
      " 7   votes       51678 non-null  int64 \n",
      "dtypes: int64(3), object(5)\n",
      "memory usage: 3.2+ MB\n"
     ]
    }
   ],
   "source": [
    "presidential_votes.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## 輸出"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [],
   "source": [
    "presidential_votes.to_csv(\"presidential_2020.csv\", index=False)"
   ]
  }
 ],
 "metadata": {
  "celltoolbar": "Slideshow",
  "colab": {
   "name": "cec-votes-2018.ipynb",
   "provenance": [],
   "version": "0.3.2"
  },
  "kernelspec": {
   "display_name": "Python Data Analysis",
   "language": "python",
   "name": "pyda"
  },
  "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.6.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}