get_twelection_data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "flexible-klein",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Tidying 總統-A05-4-候選人得票數一覽表-各投開票所(宜蘭縣).xls\n",
      "Tidying 總統-A05-4-候選人得票數一覽表-各投開票所(彰化縣).xls\n",
      "Tidying 總統-A05-4-候選人得票數一覽表-各投開票所(金門縣).xls\n",
      "Tidying 總統-A05-4-候選人得票數一覽表-各投開票所(桃園市).xls\n",
      "Tidying 總統-A05-4-候選人得票數一覽表-各投開票所(苗栗縣).xls\n",
      "Tidying 總統-A05-4-候選人得票數一覽表-各投開票所(臺南市).xls\n",
      "Tidying 總統-A05-4-候選人得票數一覽表-各投開票所(雲林縣).xls\n",
      "Tidying 總統-A05-4-候選人得票數一覽表-各投開票所(南投縣).xls\n",
      "Tidying 總統-A05-4-候選人得票數一覽表-各投開票所(高雄市).xls\n",
      "Tidying 總統-A05-4-候選人得票數一覽表-各投開票所(臺北市).xls\n",
      "Tidying 總統-A05-4-候選人得票數一覽表-各投開票所(新北市).xls\n",
      "Tidying 總統-A05-4-候選人得票數一覽表-各投開票所(花蓮縣).xls\n",
      "Tidying 總統-A05-4-候選人得票數一覽表-各投開票所(新竹市).xls\n",
      "Tidying 總統-A05-4-候選人得票數一覽表-各投開票所(新竹縣).xls\n",
      "Tidying 總統-A05-4-候選人得票數一覽表-各投開票所(基隆市).xls\n",
      "Tidying 總統-A05-4-候選人得票數一覽表-各投開票所(連江縣).xls\n",
      "Tidying 總統-A05-4-候選人得票數一覽表-各投開票所(嘉義縣).xls\n",
      "Tidying 總統-A05-4-候選人得票數一覽表-各投開票所(嘉義市).xls\n",
      "Tidying 總統-A05-4-候選人得票數一覽表-各投開票所(屏東縣).xls\n",
      "Tidying 總統-A05-4-候選人得票數一覽表-各投開票所(澎湖縣).xls\n",
      "Tidying 總統-A05-4-候選人得票數一覽表-各投開票所(臺東縣).xls\n",
      "Tidying 總統-A05-4-候選人得票數一覽表-各投開票所(臺中市).xls\n",
      "Tidying 宜蘭縣選舉區 of 區域立委-A05-2-得票數一覽表(宜蘭縣).xls\n",
      "Tidying 彰化縣第1選舉區 of 區域立委-A05-2-得票數一覽表(彰化縣).xls\n",
      "Tidying 彰化縣第2選舉區 of 區域立委-A05-2-得票數一覽表(彰化縣).xls\n",
      "Tidying 彰化縣第3選舉區 of 區域立委-A05-2-得票數一覽表(彰化縣).xls\n",
      "Tidying 彰化縣第4選舉區 of 區域立委-A05-2-得票數一覽表(彰化縣).xls\n",
      "Tidying 金門縣選舉區 of 區域立委-A05-2-得票數一覽表(金門縣).xls\n",
      "Tidying 桃園市第1選舉區 of 區域立委-A05-2-得票數一覽表(桃園市).xls\n",
      "Tidying 桃園市第2選舉區 of 區域立委-A05-2-得票數一覽表(桃園市).xls\n",
      "Tidying 桃園市第3選舉區 of 區域立委-A05-2-得票數一覽表(桃園市).xls\n",
      "Tidying 桃園市第4選舉區 of 區域立委-A05-2-得票數一覽表(桃園市).xls\n",
      "Tidying 桃園市第5選舉區 of 區域立委-A05-2-得票數一覽表(桃園市).xls\n",
      "Tidying 桃園市第6選舉區 of 區域立委-A05-2-得票數一覽表(桃園市).xls\n",
      "Tidying 苗栗縣第1選舉區 of 區域立委-A05-2-得票數一覽表(苗栗縣).xls\n",
      "Tidying 苗栗縣第2選舉區 of 區域立委-A05-2-得票數一覽表(苗栗縣).xls\n",
      "Tidying 臺南市第1選舉區 of 區域立委-A05-2-得票數一覽表(臺南市).xls\n",
      "Tidying 臺南市第2選舉區 of 區域立委-A05-2-得票數一覽表(臺南市).xls\n",
      "Tidying 臺南市第3選舉區 of 區域立委-A05-2-得票數一覽表(臺南市).xls\n",
      "Tidying 臺南市第4選舉區 of 區域立委-A05-2-得票數一覽表(臺南市).xls\n",
      "Tidying 臺南市第5選舉區 of 區域立委-A05-2-得票數一覽表(臺南市).xls\n",
      "Tidying 臺南市第6選舉區 of 區域立委-A05-2-得票數一覽表(臺南市).xls\n",
      "Tidying 雲林縣第1選舉區 of 區域立委-A05-2-得票數一覽表(雲林縣).xls\n",
      "Tidying 雲林縣第2選舉區 of 區域立委-A05-2-得票數一覽表(雲林縣).xls\n",
      "Tidying 南投縣第1選舉區 of 區域立委-A05-2-得票數一覽表(南投縣).xls\n",
      "Tidying 南投縣第2選舉區 of 區域立委-A05-2-得票數一覽表(南投縣).xls\n",
      "Tidying 高雄市第1選舉區 of 區域立委-A05-2-得票數一覽表(高雄市).xls\n",
      "Tidying 高雄市第2選舉區 of 區域立委-A05-2-得票數一覽表(高雄市).xls\n",
      "Tidying 高雄市第3選舉區 of 區域立委-A05-2-得票數一覽表(高雄市).xls\n",
      "Tidying 高雄市第4選舉區 of 區域立委-A05-2-得票數一覽表(高雄市).xls\n",
      "Tidying 高雄市第5選舉區 of 區域立委-A05-2-得票數一覽表(高雄市).xls\n",
      "Tidying 高雄市第6選舉區 of 區域立委-A05-2-得票數一覽表(高雄市).xls\n",
      "Tidying 高雄市第7選舉區 of 區域立委-A05-2-得票數一覽表(高雄市).xls\n",
      "Tidying 高雄市第8選舉區 of 區域立委-A05-2-得票數一覽表(高雄市).xls\n",
      "Tidying 臺北市第1選舉區 of 區域立委-A05-2-得票數一覽表(臺北市).xls\n",
      "Tidying 臺北市第2選舉區 of 區域立委-A05-2-得票數一覽表(臺北市).xls\n",
      "Tidying 臺北市第3選舉區 of 區域立委-A05-2-得票數一覽表(臺北市).xls\n",
      "Tidying 臺北市第4選舉區 of 區域立委-A05-2-得票數一覽表(臺北市).xls\n",
      "Tidying 臺北市第5選舉區 of 區域立委-A05-2-得票數一覽表(臺北市).xls\n",
      "Tidying 臺北市第6選舉區 of 區域立委-A05-2-得票數一覽表(臺北市).xls\n",
      "Tidying 臺北市第7選舉區 of 區域立委-A05-2-得票數一覽表(臺北市).xls\n",
      "Tidying 臺北市第8選舉區 of 區域立委-A05-2-得票數一覽表(臺北市).xls\n",
      "Tidying 新北市第1選舉區 of 區域立委-A05-2-得票數一覽表(新北市).xls\n",
      "Tidying 新北市第2選舉區 of 區域立委-A05-2-得票數一覽表(新北市).xls\n",
      "Tidying 新北市第3選舉區 of 區域立委-A05-2-得票數一覽表(新北市).xls\n",
      "Tidying 新北市第4選舉區 of 區域立委-A05-2-得票數一覽表(新北市).xls\n",
      "Tidying 新北市第5選舉區 of 區域立委-A05-2-得票數一覽表(新北市).xls\n",
      "Tidying 新北市第6選舉區 of 區域立委-A05-2-得票數一覽表(新北市).xls\n",
      "Tidying 新北市第7選舉區 of 區域立委-A05-2-得票數一覽表(新北市).xls\n",
      "Tidying 新北市第8選舉區 of 區域立委-A05-2-得票數一覽表(新北市).xls\n",
      "Tidying 新北市第9選舉區 of 區域立委-A05-2-得票數一覽表(新北市).xls\n",
      "Tidying 新北市第10選舉區 of 區域立委-A05-2-得票數一覽表(新北市).xls\n",
      "Tidying 新北市第11選舉區 of 區域立委-A05-2-得票數一覽表(新北市).xls\n",
      "Tidying 新北市第12選舉區 of 區域立委-A05-2-得票數一覽表(新北市).xls\n",
      "Tidying 花蓮縣選舉區 of 區域立委-A05-2-得票數一覽表(花蓮縣).xls\n",
      "Tidying 新竹市選舉區 of 區域立委-A05-2-得票數一覽表(新竹市).xls\n",
      "Tidying 新竹縣第1選舉區 of 區域立委-A05-2-得票數一覽表(新竹縣).xls\n",
      "Tidying 新竹縣第2選舉區 of 區域立委-A05-2-得票數一覽表(新竹縣).xls\n",
      "Tidying 基隆市選舉區 of 區域立委-A05-2-得票數一覽表(基隆市).xls\n",
      "Tidying 連江縣選舉區 of 區域立委-A05-2-得票數一覽表(連江縣).xls\n",
      "Tidying 嘉義縣第1選舉區 of 區域立委-A05-2-得票數一覽表(嘉義縣).xls\n",
      "Tidying 嘉義縣第2選舉區 of 區域立委-A05-2-得票數一覽表(嘉義縣).xls\n",
      "Tidying 嘉義市選舉區 of 區域立委-A05-2-得票數一覽表(嘉義市).xls\n",
      "Tidying 屏東縣第1選舉區 of 區域立委-A05-2-得票數一覽表(屏東縣).xls\n",
      "Tidying 屏東縣第2選舉區 of 區域立委-A05-2-得票數一覽表(屏東縣).xls\n",
      "Tidying 澎湖縣選舉區 of 區域立委-A05-2-得票數一覽表(澎湖縣).xls\n",
      "Tidying 臺東縣選舉區 of 區域立委-A05-2-得票數一覽表(臺東縣).xls\n",
      "Tidying 臺中市第1選舉區 of 區域立委-A05-2-得票數一覽表(臺中市).xls\n",
      "Tidying 臺中市第2選舉區 of 區域立委-A05-2-得票數一覽表(臺中市).xls\n",
      "Tidying 臺中市第3選舉區 of 區域立委-A05-2-得票數一覽表(臺中市).xls\n",
      "Tidying 臺中市第4選舉區 of 區域立委-A05-2-得票數一覽表(臺中市).xls\n",
      "Tidying 臺中市第5選舉區 of 區域立委-A05-2-得票數一覽表(臺中市).xls\n",
      "Tidying 臺中市第6選舉區 of 區域立委-A05-2-得票數一覽表(臺中市).xls\n",
      "Tidying 臺中市第7選舉區 of 區域立委-A05-2-得票數一覽表(臺中市).xls\n",
      "Tidying 臺中市第8選舉區 of 區域立委-A05-2-得票數一覽表(臺中市).xls\n",
      "Tidying 山地立委-A05-4-得票數一覽表(宜蘭縣).xls\n",
      "Tidying 平地立委-A05-4-得票數一覽表(宜蘭縣).xls\n",
      "Tidying 山地立委-A05-4-得票數一覽表(彰化縣).xls\n",
      "Tidying 平地立委-A05-4-得票數一覽表(彰化縣).xls\n",
      "Tidying 山地立委-A05-4-得票數一覽表(金門縣).xls\n",
      "Tidying 平地立委-A05-4-得票數一覽表(金門縣).xls\n",
      "Tidying 山地立委-A05-4-得票數一覽表(桃園市).xls\n",
      "Tidying 平地立委-A05-4-得票數一覽表(桃園市).xls\n",
      "Tidying 山地立委-A05-4-得票數一覽表(苗栗縣).xls\n",
      "Tidying 平地立委-A05-4-得票數一覽表(苗栗縣).xls\n",
      "Tidying 山地立委-A05-4-得票數一覽表(臺南市).xls\n",
      "Tidying 平地立委-A05-4-得票數一覽表(臺南市).xls\n",
      "Tidying 山地立委-A05-4-得票數一覽表(雲林縣).xls\n",
      "Tidying 平地立委-A05-4-得票數一覽表(雲林縣).xls\n",
      "Tidying 山地立委-A05-4-得票數一覽表(南投縣).xls\n",
      "Tidying 平地立委-A05-4-得票數一覽表(南投縣).xls\n",
      "Tidying 山地立委-A05-4-得票數一覽表(高雄市).xls\n",
      "Tidying 平地立委-A05-4-得票數一覽表(高雄市).xls\n",
      "Tidying 山地立委-A05-4-得票數一覽表(臺北市).xls\n",
      "Tidying 平地立委-A05-4-得票數一覽表(臺北市).xls\n",
      "Tidying 山地立委-A05-4-得票數一覽表(新北市).xls\n",
      "Tidying 平地立委-A05-4-得票數一覽表(新北市).xls\n",
      "Tidying 山地立委-A05-4-得票數一覽表(花蓮縣).xls\n",
      "Tidying 平地立委-A05-4-得票數一覽表(花蓮縣).xls\n",
      "Tidying 山地立委-A05-4-得票數一覽表(新竹市).xls\n",
      "Tidying 平地立委-A05-4-得票數一覽表(新竹市).xls\n",
      "Tidying 山地立委-A05-4-得票數一覽表(新竹縣).xls\n",
      "Tidying 平地立委-A05-4-得票數一覽表(新竹縣).xls\n",
      "Tidying 山地立委-A05-4-得票數一覽表(基隆市).xls\n",
      "Tidying 平地立委-A05-4-得票數一覽表(基隆市).xls\n",
      "Tidying 山地立委-A05-4-得票數一覽表(連江縣).xls\n",
      "Tidying 平地立委-A05-4-得票數一覽表(連江縣).xls\n",
      "Tidying 山地立委-A05-4-得票數一覽表(嘉義縣).xls\n",
      "Tidying 平地立委-A05-4-得票數一覽表(嘉義縣).xls\n",
      "Tidying 山地立委-A05-4-得票數一覽表(嘉義市).xls\n",
      "Tidying 平地立委-A05-4-得票數一覽表(嘉義市).xls\n",
      "Tidying 山地立委-A05-4-得票數一覽表(屏東縣).xls\n",
      "Tidying 平地立委-A05-4-得票數一覽表(屏東縣).xls\n",
      "Tidying 山地立委-A05-4-得票數一覽表(澎湖縣).xls\n",
      "Tidying 平地立委-A05-4-得票數一覽表(澎湖縣).xls\n",
      "Tidying 山地立委-A05-4-得票數一覽表(臺東縣).xls\n",
      "Tidying 平地立委-A05-4-得票數一覽表(臺東縣).xls\n",
      "Tidying 山地立委-A05-4-得票數一覽表(臺中市).xls\n",
      "Tidying 平地立委-A05-4-得票數一覽表(臺中市).xls\n",
      "Tidying 不分區立委-A05-6-得票數一覽表(宜蘭縣).xls\n",
      "Tidying 不分區立委-A05-6-得票數一覽表(彰化縣).xls\n",
      "Tidying 不分區立委-A05-6-得票數一覽表(金門縣).xls\n",
      "Tidying 不分區立委-A05-6-得票數一覽表(桃園市).xls\n",
      "Tidying 不分區立委-A05-6-得票數一覽表(苗栗縣).xls\n",
      "Tidying 不分區立委-A05-6-得票數一覽表(臺南市).xls\n",
      "Tidying 不分區立委-A05-6-得票數一覽表(雲林縣).xls\n",
      "Tidying 不分區立委-A05-6-得票數一覽表(南投縣).xls\n",
      "Tidying 不分區立委-A05-6-得票數一覽表(高雄市).xls\n",
      "Tidying 不分區立委-A05-6-得票數一覽表(臺北市).xls\n",
      "Tidying 不分區立委-A05-6-得票數一覽表(新北市).xls\n",
      "Tidying 不分區立委-A05-6-得票數一覽表(花蓮縣).xls\n",
      "Tidying 不分區立委-A05-6-得票數一覽表(新竹市).xls\n",
      "Tidying 不分區立委-A05-6-得票數一覽表(新竹縣).xls\n",
      "Tidying 不分區立委-A05-6-得票數一覽表(基隆市).xls\n",
      "Tidying 不分區立委-A05-6-得票數一覽表(連江縣).xls\n",
      "Tidying 不分區立委-A05-6-得票數一覽表(嘉義縣).xls\n",
      "Tidying 不分區立委-A05-6-得票數一覽表(嘉義市).xls\n",
      "Tidying 不分區立委-A05-6-得票數一覽表(屏東縣).xls\n",
      "Tidying 不分區立委-A05-6-得票數一覽表(澎湖縣).xls\n",
      "Tidying 不分區立委-A05-6-得票數一覽表(臺東縣).xls\n",
      "Tidying 不分區立委-A05-6-得票數一覽表(臺中市).xls\n"
     ]
    }
   ],
   "source": [
    "import sqlite3\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "from taiwan_election_2020 import TaiwanElection2020\n",
    "\n",
    "tw_election = TaiwanElection2020()\n",
    "presidential = tw_election.generate_presidential()\n",
    "legislative_regional = tw_election.generate_legislative_regional()\n",
    "legislative_at_large = tw_election.generate_legislative_at_large()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "political-coating",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(51678, 7)\n",
      "(459000, 9)\n",
      "(327294, 7)\n"
     ]
    }
   ],
   "source": [
    "print(presidential.shape)\n",
    "print(legislative_regional.shape)\n",
    "print(legislative_at_large.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "opposed-think",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>party</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>一邊一國行動黨</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>中國和平統一黨</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>中國國民黨</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>中國生產黨</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>中華婦女黨</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>中華愛國同心黨</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>中華文化復興在理黨</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>中華新住民黨</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>中華照生黨</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>中華統一促進黨</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>11</td>\n",
       "      <td>中華聯合黨</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>12</td>\n",
       "      <td>人民民主黨</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>13</td>\n",
       "      <td>勞動黨</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>14</td>\n",
       "      <td>勞工黨</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>15</td>\n",
       "      <td>台澎黨</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>16</td>\n",
       "      <td>台灣動物保護黨</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>17</td>\n",
       "      <td>台灣團結聯盟</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>18</td>\n",
       "      <td>台灣基進</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>19</td>\n",
       "      <td>台灣工黨</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>20</td>\n",
       "      <td>台灣整復師聯盟工黨</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>21</td>\n",
       "      <td>台灣民意黨</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>22</td>\n",
       "      <td>台灣民眾黨</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>23</td>\n",
       "      <td>台灣獨立黨</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>24</td>\n",
       "      <td>台灣維新</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>25</td>\n",
       "      <td>台灣革命黨</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>26</td>\n",
       "      <td>司法正義黨</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>27</td>\n",
       "      <td>合一行動聯盟</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>28</td>\n",
       "      <td>喜樂島聯盟</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>29</td>\n",
       "      <td>國會政黨聯盟</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>30</td>\n",
       "      <td>圓黨</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>31</td>\n",
       "      <td>天宙和平統一家庭黨</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>32</td>\n",
       "      <td>安定力量</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>33</td>\n",
       "      <td>宗教聯盟</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>34</td>\n",
       "      <td>新黨</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>35</td>\n",
       "      <td>時代力量</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>36</td>\n",
       "      <td>正黨</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>37</td>\n",
       "      <td>民主進步黨</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>38</td>\n",
       "      <td>民生公益</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>39</td>\n",
       "      <td>無</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>40</td>\n",
       "      <td>綠黨</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>41</td>\n",
       "      <td>聾國黨</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>42</td>\n",
       "      <td>臺灣前進黨</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>43</td>\n",
       "      <td>興中同盟會</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>44</td>\n",
       "      <td>親民黨</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>45</td>\n",
       "      <td>言論自由聯盟</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>46</td>\n",
       "      <td>金門高粱黨</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    id      party\n",
       "0    1    一邊一國行動黨\n",
       "1    2    中國和平統一黨\n",
       "2    3      中國國民黨\n",
       "3    4      中國生產黨\n",
       "4    5      中華婦女黨\n",
       "5    6    中華愛國同心黨\n",
       "6    7  中華文化復興在理黨\n",
       "7    8     中華新住民黨\n",
       "8    9      中華照生黨\n",
       "9   10    中華統一促進黨\n",
       "10  11      中華聯合黨\n",
       "11  12      人民民主黨\n",
       "12  13        勞動黨\n",
       "13  14        勞工黨\n",
       "14  15        台澎黨\n",
       "15  16    台灣動物保護黨\n",
       "16  17     台灣團結聯盟\n",
       "17  18       台灣基進\n",
       "18  19       台灣工黨\n",
       "19  20  台灣整復師聯盟工黨\n",
       "20  21      台灣民意黨\n",
       "21  22      台灣民眾黨\n",
       "22  23      台灣獨立黨\n",
       "23  24       台灣維新\n",
       "24  25      台灣革命黨\n",
       "25  26      司法正義黨\n",
       "26  27     合一行動聯盟\n",
       "27  28      喜樂島聯盟\n",
       "28  29     國會政黨聯盟\n",
       "29  30         圓黨\n",
       "30  31  天宙和平統一家庭黨\n",
       "31  32       安定力量\n",
       "32  33       宗教聯盟\n",
       "33  34         新黨\n",
       "34  35       時代力量\n",
       "35  36         正黨\n",
       "36  37      民主進步黨\n",
       "37  38       民生公益\n",
       "38  39          無\n",
       "39  40         綠黨\n",
       "40  41        聾國黨\n",
       "41  42      臺灣前進黨\n",
       "42  43      興中同盟會\n",
       "43  44        親民黨\n",
       "44  45     言論自由聯盟\n",
       "45  46      金門高粱黨"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Creating lookup_table\n",
    "## parties - id/party\n",
    "legislative_regional_parties = legislative_regional['party'].unique()\n",
    "legislative_at_large_parties = legislative_at_large['party'].unique()\n",
    "all_parties = np.concatenate([legislative_regional_parties, legislative_at_large_parties])\n",
    "unique_parties = np.unique(all_parties)\n",
    "unique_parties = np.sort(unique_parties)\n",
    "party_ids = range(1, unique_parties.size + 1)\n",
    "parties = pd.DataFrame()\n",
    "parties['id'] = party_ids\n",
    "parties['party'] = unique_parties\n",
    "parties"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "viral-edition",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>party_id</th>\n",
       "      <th>type</th>\n",
       "      <th>number</th>\n",
       "      <th>candidate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>44</td>\n",
       "      <td>總統副總統</td>\n",
       "      <td>1</td>\n",
       "      <td>宋楚瑜/余湘</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>總統副總統</td>\n",
       "      <td>2</td>\n",
       "      <td>韓國瑜/張善政</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>37</td>\n",
       "      <td>總統副總統</td>\n",
       "      <td>3</td>\n",
       "      <td>蔡英文/賴清德</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>區域立委</td>\n",
       "      <td>1</td>\n",
       "      <td>郭正典</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>區域立委</td>\n",
       "      <td>2</td>\n",
       "      <td>葉宏仁</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>429</th>\n",
       "      <td>430</td>\n",
       "      <td>44</td>\n",
       "      <td>區域立委</td>\n",
       "      <td>6</td>\n",
       "      <td>賴建豪</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>430</th>\n",
       "      <td>431</td>\n",
       "      <td>44</td>\n",
       "      <td>區域立委</td>\n",
       "      <td>7</td>\n",
       "      <td>秦詩雁</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>431</th>\n",
       "      <td>432</td>\n",
       "      <td>44</td>\n",
       "      <td>區域立委</td>\n",
       "      <td>9</td>\n",
       "      <td>蘇博廷</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>432</th>\n",
       "      <td>433</td>\n",
       "      <td>45</td>\n",
       "      <td>區域立委</td>\n",
       "      <td>5</td>\n",
       "      <td>蘇伊文</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>433</th>\n",
       "      <td>434</td>\n",
       "      <td>46</td>\n",
       "      <td>區域立委</td>\n",
       "      <td>1</td>\n",
       "      <td>洪志恒</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>434 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      id  party_id   type number candidate\n",
       "0      1        44  總統副總統      1    宋楚瑜/余湘\n",
       "1      2         3  總統副總統      2   韓國瑜/張善政\n",
       "2      3        37  總統副總統      3   蔡英文/賴清德\n",
       "3      4         1   區域立委      1       郭正典\n",
       "4      5         1   區域立委      2       葉宏仁\n",
       "..   ...       ...    ...    ...       ...\n",
       "429  430        44   區域立委      6       賴建豪\n",
       "430  431        44   區域立委      7       秦詩雁\n",
       "431  432        44   區域立委      9       蘇博廷\n",
       "432  433        45   區域立委      5       蘇伊文\n",
       "433  434        46   區域立委      1       洪志恒\n",
       "\n",
       "[434 rows x 5 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Creating lookup_table\n",
    "## candidates - id/party_id/number/name/type\n",
    "presidential_candidates = presidential.groupby(['number', 'candidate'])\n",
    "legislative_regional_candidates = legislative_regional.groupby(['party', 'number', 'candidate'])\n",
    "presidential_candidates_numbers = [n for n, p in presidential_candidates.count().index]\n",
    "presidential_candidates_names = [p for n, p in presidential_candidates.count().index]\n",
    "presidential_candidates_party_ids = [44, 3, 37]\n",
    "legislative_regional_candidates_parties = [p for p, n, c in legislative_regional_candidates.count().index]\n",
    "legislative_regional_candidates_numbers = [n for p, n, c in legislative_regional_candidates.count().index]\n",
    "legislative_regional_candidates_names = [c for p, n, c in legislative_regional_candidates.count().index]\n",
    "presidential_df = pd.DataFrame()\n",
    "presidential_df['party_id'] = presidential_candidates_party_ids\n",
    "presidential_df['number'] = presidential_candidates_numbers\n",
    "presidential_df['candidate'] = presidential_candidates_names\n",
    "presidential_df['type'] = '總統副總統'\n",
    "legislative_regional_candidates_party_ids = []\n",
    "for p in legislative_regional_candidates_parties:\n",
    "    partial_df = parties[parties['party'] == p]\n",
    "    legislative_regional_candidates_party_ids.append(partial_df['id'].values[0])\n",
    "legislative_regional_df = pd.DataFrame()\n",
    "legislative_regional_df['party_id'] = legislative_regional_candidates_party_ids\n",
    "legislative_regional_df['number'] = legislative_regional_candidates_numbers\n",
    "legislative_regional_df['candidate'] = legislative_regional_candidates_names\n",
    "legislative_regional_df['type'] = '區域立委'\n",
    "candidates = pd.concat([presidential_df, legislative_regional_df])\n",
    "candidates = candidates.reset_index(drop=True)\n",
    "candidates['id'] = range(1, candidates.shape[0] + 1)\n",
    "candidates = candidates[['id', 'party_id', 'type', 'number', 'candidate']]\n",
    "candidates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "crude-diversity",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>county</th>\n",
       "      <th>town</th>\n",
       "      <th>village</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>南投縣</td>\n",
       "      <td>中寮鄉</td>\n",
       "      <td>中寮村</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>南投縣</td>\n",
       "      <td>中寮鄉</td>\n",
       "      <td>內城村</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>南投縣</td>\n",
       "      <td>中寮鄉</td>\n",
       "      <td>八仙村</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>南投縣</td>\n",
       "      <td>中寮鄉</td>\n",
       "      <td>和興村</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>南投縣</td>\n",
       "      <td>中寮鄉</td>\n",
       "      <td>崁頂村</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7732</th>\n",
       "      <td>7733</td>\n",
       "      <td>高雄市</td>\n",
       "      <td>鼓山區</td>\n",
       "      <td>鼓岩里</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7733</th>\n",
       "      <td>7734</td>\n",
       "      <td>高雄市</td>\n",
       "      <td>鼓山區</td>\n",
       "      <td>鼓峰里</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7734</th>\n",
       "      <td>7735</td>\n",
       "      <td>高雄市</td>\n",
       "      <td>鼓山區</td>\n",
       "      <td>龍井里</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7735</th>\n",
       "      <td>7736</td>\n",
       "      <td>高雄市</td>\n",
       "      <td>鼓山區</td>\n",
       "      <td>龍子里</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7736</th>\n",
       "      <td>7737</td>\n",
       "      <td>高雄市</td>\n",
       "      <td>鼓山區</td>\n",
       "      <td>龍水里</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>7737 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        id county town village\n",
       "0        1    南投縣  中寮鄉     中寮村\n",
       "1        2    南投縣  中寮鄉     內城村\n",
       "2        3    南投縣  中寮鄉     八仙村\n",
       "3        4    南投縣  中寮鄉     和興村\n",
       "4        5    南投縣  中寮鄉     崁頂村\n",
       "...    ...    ...  ...     ...\n",
       "7732  7733    高雄市  鼓山區     鼓岩里\n",
       "7733  7734    高雄市  鼓山區     鼓峰里\n",
       "7734  7735    高雄市  鼓山區     龍井里\n",
       "7735  7736    高雄市  鼓山區     龍子里\n",
       "7736  7737    高雄市  鼓山區     龍水里\n",
       "\n",
       "[7737 rows x 4 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Creating lookup_table\n",
    "## admin_regions - id/county/town/village\n",
    "county_town_village = presidential.groupby(['county', 'town', 'village']).count().index\n",
    "counties, towns, villages = [], [], []\n",
    "for c, t, v in county_town_village:\n",
    "    counties.append(c)\n",
    "    towns.append(t)\n",
    "    villages.append(v)\n",
    "region_ids = range(1, county_town_village.shape[0] + 1)\n",
    "admin_regions = pd.DataFrame()\n",
    "admin_regions['id'] = region_ids\n",
    "admin_regions['county'] = counties\n",
    "admin_regions['town'] = towns\n",
    "admin_regions['village'] = villages\n",
    "admin_regions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "loose-queens",
   "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>admin_region_id</th>\n",
       "      <th>office</th>\n",
       "      <th>candidate_id</th>\n",
       "      <th>votes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>996</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>996</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>983</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>983</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>998</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51673</th>\n",
       "      <td>4237</td>\n",
       "      <td>1845</td>\n",
       "      <td>3</td>\n",
       "      <td>132</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51674</th>\n",
       "      <td>4237</td>\n",
       "      <td>1846</td>\n",
       "      <td>3</td>\n",
       "      <td>107</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51675</th>\n",
       "      <td>4237</td>\n",
       "      <td>1847</td>\n",
       "      <td>3</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51676</th>\n",
       "      <td>4236</td>\n",
       "      <td>1848</td>\n",
       "      <td>3</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51677</th>\n",
       "      <td>4236</td>\n",
       "      <td>1849</td>\n",
       "      <td>3</td>\n",
       "      <td>102</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>51678 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       admin_region_id  office  candidate_id  votes\n",
       "0                  996       1             1     37\n",
       "1                  996       2             1     31\n",
       "2                  983       3             1     19\n",
       "3                  983       4             1     29\n",
       "4                  998       5             1     25\n",
       "...                ...     ...           ...    ...\n",
       "51673             4237    1845             3    132\n",
       "51674             4237    1846             3    107\n",
       "51675             4237    1847             3     40\n",
       "51676             4236    1848             3     24\n",
       "51677             4236    1849             3    102\n",
       "\n",
       "[51678 rows x 4 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Normalize presidential\n",
    "presidential_admin_regions = pd.merge(presidential, admin_regions, left_on=['county', 'town', 'village'], right_on=['county', 'town', 'village'])\n",
    "presidential_admin_regions = presidential_admin_regions[['id', 'office', 'candidate', 'votes']]\n",
    "presidential_admin_regions.columns = ['admin_region_id', 'office', 'candidate', 'votes']\n",
    "presidential_candidates = pd.merge(presidential_admin_regions, candidates, left_on='candidate', right_on='candidate')\n",
    "presidential_normalized = presidential_candidates[['admin_region_id', 'office', 'id', 'votes']]\n",
    "presidential_normalized.columns = ['admin_region_id', 'office', 'candidate_id', 'votes']\n",
    "presidential_normalized"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "northern-madonna",
   "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>admin_region_id</th>\n",
       "      <th>electoral_district</th>\n",
       "      <th>office</th>\n",
       "      <th>candidate_id</th>\n",
       "      <th>votes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>996</td>\n",
       "      <td>宜蘭縣選舉區</td>\n",
       "      <td>1</td>\n",
       "      <td>332</td>\n",
       "      <td>128</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>996</td>\n",
       "      <td>宜蘭縣選舉區</td>\n",
       "      <td>2</td>\n",
       "      <td>332</td>\n",
       "      <td>138</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>983</td>\n",
       "      <td>宜蘭縣選舉區</td>\n",
       "      <td>3</td>\n",
       "      <td>332</td>\n",
       "      <td>122</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>983</td>\n",
       "      <td>宜蘭縣選舉區</td>\n",
       "      <td>4</td>\n",
       "      <td>332</td>\n",
       "      <td>104</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>998</td>\n",
       "      <td>宜蘭縣選舉區</td>\n",
       "      <td>5</td>\n",
       "      <td>332</td>\n",
       "      <td>129</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>458995</th>\n",
       "      <td>4237</td>\n",
       "      <td>臺中市第8選舉區</td>\n",
       "      <td>1845</td>\n",
       "      <td>362</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>458996</th>\n",
       "      <td>4237</td>\n",
       "      <td>臺中市第8選舉區</td>\n",
       "      <td>1846</td>\n",
       "      <td>362</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>458997</th>\n",
       "      <td>4237</td>\n",
       "      <td>臺中市第8選舉區</td>\n",
       "      <td>1847</td>\n",
       "      <td>362</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>458998</th>\n",
       "      <td>4236</td>\n",
       "      <td>臺中市第8選舉區</td>\n",
       "      <td>1848</td>\n",
       "      <td>362</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>458999</th>\n",
       "      <td>4236</td>\n",
       "      <td>臺中市第8選舉區</td>\n",
       "      <td>1849</td>\n",
       "      <td>362</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>459000 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        admin_region_id electoral_district  office  candidate_id  votes\n",
       "0                   996             宜蘭縣選舉區       1           332    128\n",
       "1                   996             宜蘭縣選舉區       2           332    138\n",
       "2                   983             宜蘭縣選舉區       3           332    122\n",
       "3                   983             宜蘭縣選舉區       4           332    104\n",
       "4                   998             宜蘭縣選舉區       5           332    129\n",
       "...                 ...                ...     ...           ...    ...\n",
       "458995             4237           臺中市第8選舉區    1845           362      0\n",
       "458996             4237           臺中市第8選舉區    1846           362      0\n",
       "458997             4237           臺中市第8選舉區    1847           362      0\n",
       "458998             4236           臺中市第8選舉區    1848           362      0\n",
       "458999             4236           臺中市第8選舉區    1849           362      1\n",
       "\n",
       "[459000 rows x 5 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Normalize legislative_regional\n",
    "legislative_regional_admin_regions = pd.merge(legislative_regional, admin_regions, left_on=['county', 'town', 'village'], right_on=['county', 'town', 'village'])\n",
    "legislative_regional_admin_regions = legislative_regional_admin_regions[['id', 'electoral_district', 'office', 'party', 'candidate', 'votes']]\n",
    "legislative_regional_admin_regions.columns = ['admin_region_id', 'electoral_district', 'office', 'party', 'candidate', 'votes']\n",
    "candidates_parties = pd.merge(candidates, parties, left_on='party_id', right_on='id')\n",
    "legislative_regional_candidates = pd.merge(legislative_regional_admin_regions, candidates_parties, left_on=['candidate', 'party'], right_on=['candidate', 'party'])\n",
    "legislative_regional_normalized = legislative_regional_candidates[['admin_region_id', 'electoral_district', 'office', 'id_x', 'votes']]\n",
    "legislative_regional_normalized.columns = ['admin_region_id', 'electoral_district', 'office', 'candidate_id', 'votes']\n",
    "legislative_regional_normalized"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "solved-alabama",
   "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>admin_region_id</th>\n",
       "      <th>office</th>\n",
       "      <th>party_id</th>\n",
       "      <th>votes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>996</td>\n",
       "      <td>1</td>\n",
       "      <td>27</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>996</td>\n",
       "      <td>2</td>\n",
       "      <td>27</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>983</td>\n",
       "      <td>3</td>\n",
       "      <td>27</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>983</td>\n",
       "      <td>4</td>\n",
       "      <td>27</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>998</td>\n",
       "      <td>5</td>\n",
       "      <td>27</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>327289</th>\n",
       "      <td>4237</td>\n",
       "      <td>1845</td>\n",
       "      <td>17</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>327290</th>\n",
       "      <td>4237</td>\n",
       "      <td>1846</td>\n",
       "      <td>17</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>327291</th>\n",
       "      <td>4237</td>\n",
       "      <td>1847</td>\n",
       "      <td>17</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>327292</th>\n",
       "      <td>4236</td>\n",
       "      <td>1848</td>\n",
       "      <td>17</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>327293</th>\n",
       "      <td>4236</td>\n",
       "      <td>1849</td>\n",
       "      <td>17</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>327294 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        admin_region_id  office  party_id  votes\n",
       "0                   996       1        27      2\n",
       "1                   996       2        27      1\n",
       "2                   983       3        27      2\n",
       "3                   983       4        27      0\n",
       "4                   998       5        27      3\n",
       "...                 ...     ...       ...    ...\n",
       "327289             4237    1845        17      1\n",
       "327290             4237    1846        17      1\n",
       "327291             4237    1847        17      0\n",
       "327292             4236    1848        17      0\n",
       "327293             4236    1849        17      0\n",
       "\n",
       "[327294 rows x 4 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Normalize legislative_at_large\n",
    "legislative_at_large_admin_regions = pd.merge(legislative_at_large, admin_regions, left_on=['county', 'town', 'village'], right_on=['county', 'town', 'village'])\n",
    "legislative_at_large_admin_regions = legislative_at_large_admin_regions[['id', 'office', 'number', 'party', 'votes']]\n",
    "legislative_at_large_admin_regions.columns = ['admin_region_id', 'office', 'number', 'party', 'votes']\n",
    "legislative_at_large_normalized = pd.merge(legislative_at_large_admin_regions, parties, left_on='party', right_on='party')\n",
    "legislative_at_large_normalized = legislative_at_large_normalized[['admin_region_id', 'office', 'id', 'votes']]\n",
    "legislative_at_large_normalized.columns = ['admin_region_id', 'office', 'party_id', 'votes']\n",
    "legislative_at_large_normalized"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "negative-royal",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = sqlite3.connect('twElection2020.db')\n",
    "parties.to_sql('parties', conn, index=False)\n",
    "candidates.to_sql('candidates', conn, index=False)\n",
    "admin_regions.to_sql('admin_regions', conn, index=False)\n",
    "presidential_normalized.to_sql('presidential', conn, index=False)\n",
    "legislative_regional_normalized.to_sql('legislative_regional', conn, index=False)\n",
    "legislative_at_large_normalized.to_sql('legislative_at_large', conn, index=False)"
   ]
  }
 ],
 "metadata": {
  "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": 5
}