11-advanced-wrangling-with-pandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Data Visualization with Modern Data Science\n",
    "\n",
    "> Advanced Wrangling with Pandas\n",
    "\n",
    "Yao-Jen Kuo <yaojenkuo@ntu.edu.tw> from [DATAINPOINT](https://www.datainpoint.com/)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Advanced Wrangling"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/kuoyaojen/miniconda3/lib/python3.11/site-packages/openpyxl/styles/stylesheet.py:226: UserWarning: Workbook contains no default style, apply openpyxl's default\n",
      "  warn(\"Workbook contains no default style, apply openpyxl's default\")\n"
     ]
    }
   ],
   "source": [
    "excel_file_path = \"data/總統-A05-4-候選人得票數一覽表-各投開票所(臺北市).xlsx\"\n",
    "presidents_taipei = pd.read_excel(excel_file_path, skiprows=[0, 1, 3, 4], thousands=\",\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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>366854</td>\n",
       "      <td>587899</td>\n",
       "      <td>587258</td>\n",
       "      <td>1542011</td>\n",
       "      <td>10581</td>\n",
       "      <td>1552592</td>\n",
       "      <td>99</td>\n",
       "      <td>1552691</td>\n",
       "      <td>537371</td>\n",
       "      <td>2090062</td>\n",
       "      <td>74.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>北投區</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>35975</td>\n",
       "      <td>61151</td>\n",
       "      <td>51657</td>\n",
       "      <td>148783</td>\n",
       "      <td>1091</td>\n",
       "      <td>149874</td>\n",
       "      <td>6</td>\n",
       "      <td>149880</td>\n",
       "      <td>51950</td>\n",
       "      <td>201830</td>\n",
       "      <td>74.26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>建民里</td>\n",
       "      <td>1.0</td>\n",
       "      <td>208</td>\n",
       "      <td>401</td>\n",
       "      <td>311</td>\n",
       "      <td>920</td>\n",
       "      <td>6</td>\n",
       "      <td>926</td>\n",
       "      <td>0</td>\n",
       "      <td>926</td>\n",
       "      <td>283</td>\n",
       "      <td>1209</td>\n",
       "      <td>76.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>建民里</td>\n",
       "      <td>2.0</td>\n",
       "      <td>209</td>\n",
       "      <td>455</td>\n",
       "      <td>272</td>\n",
       "      <td>936</td>\n",
       "      <td>3</td>\n",
       "      <td>939</td>\n",
       "      <td>0</td>\n",
       "      <td>939</td>\n",
       "      <td>279</td>\n",
       "      <td>1218</td>\n",
       "      <td>77.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>建民里</td>\n",
       "      <td>3.0</td>\n",
       "      <td>221</td>\n",
       "      <td>439</td>\n",
       "      <td>306</td>\n",
       "      <td>966</td>\n",
       "      <td>11</td>\n",
       "      <td>977</td>\n",
       "      <td>0</td>\n",
       "      <td>977</td>\n",
       "      <td>263</td>\n",
       "      <td>1240</td>\n",
       "      <td>78.79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1772</th>\n",
       "      <td>NaN</td>\n",
       "      <td>政大里</td>\n",
       "      <td>1726.0</td>\n",
       "      <td>208</td>\n",
       "      <td>313</td>\n",
       "      <td>477</td>\n",
       "      <td>998</td>\n",
       "      <td>10</td>\n",
       "      <td>1008</td>\n",
       "      <td>0</td>\n",
       "      <td>1008</td>\n",
       "      <td>337</td>\n",
       "      <td>1345</td>\n",
       "      <td>74.94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1773</th>\n",
       "      <td>NaN</td>\n",
       "      <td>指南里</td>\n",
       "      <td>1727.0</td>\n",
       "      <td>217</td>\n",
       "      <td>323</td>\n",
       "      <td>409</td>\n",
       "      <td>949</td>\n",
       "      <td>7</td>\n",
       "      <td>956</td>\n",
       "      <td>0</td>\n",
       "      <td>956</td>\n",
       "      <td>335</td>\n",
       "      <td>1291</td>\n",
       "      <td>74.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1774</th>\n",
       "      <td>NaN</td>\n",
       "      <td>指南里</td>\n",
       "      <td>1728.0</td>\n",
       "      <td>279</td>\n",
       "      <td>314</td>\n",
       "      <td>378</td>\n",
       "      <td>971</td>\n",
       "      <td>5</td>\n",
       "      <td>976</td>\n",
       "      <td>0</td>\n",
       "      <td>976</td>\n",
       "      <td>353</td>\n",
       "      <td>1329</td>\n",
       "      <td>73.44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1775</th>\n",
       "      <td>NaN</td>\n",
       "      <td>指南里</td>\n",
       "      <td>1729.0</td>\n",
       "      <td>156</td>\n",
       "      <td>286</td>\n",
       "      <td>144</td>\n",
       "      <td>586</td>\n",
       "      <td>6</td>\n",
       "      <td>592</td>\n",
       "      <td>0</td>\n",
       "      <td>592</td>\n",
       "      <td>216</td>\n",
       "      <td>808</td>\n",
       "      <td>73.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1776</th>\n",
       "      <td>NaN</td>\n",
       "      <td>老泉里</td>\n",
       "      <td>1730.0</td>\n",
       "      <td>123</td>\n",
       "      <td>255</td>\n",
       "      <td>154</td>\n",
       "      <td>532</td>\n",
       "      <td>3</td>\n",
       "      <td>535</td>\n",
       "      <td>0</td>\n",
       "      <td>535</td>\n",
       "      <td>189</td>\n",
       "      <td>724</td>\n",
       "      <td>73.90</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1777 rows × 14 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     Unnamed: 0 Unnamed: 1  Unnamed: 2  (1)\\n柯文哲\\n吳欣盈  (2)\\n賴清德\\n蕭美琴  \\\n",
       "0           總　計        NaN         NaN         366854         587899   \n",
       "1          　北投區        NaN         NaN          35975          61151   \n",
       "2           NaN        建民里         1.0            208            401   \n",
       "3           NaN        建民里         2.0            209            455   \n",
       "4           NaN        建民里         3.0            221            439   \n",
       "...         ...        ...         ...            ...            ...   \n",
       "1772        NaN        政大里      1726.0            208            313   \n",
       "1773        NaN        指南里      1727.0            217            323   \n",
       "1774        NaN        指南里      1728.0            279            314   \n",
       "1775        NaN        指南里      1729.0            156            286   \n",
       "1776        NaN        老泉里      1730.0            123            255   \n",
       "\n",
       "      (3)\\n侯友宜\\n趙少康  Unnamed: 6  Unnamed: 7  Unnamed: 8  Unnamed: 9  \\\n",
       "0            587258     1542011       10581     1552592          99   \n",
       "1             51657      148783        1091      149874           6   \n",
       "2               311         920           6         926           0   \n",
       "3               272         936           3         939           0   \n",
       "4               306         966          11         977           0   \n",
       "...             ...         ...         ...         ...         ...   \n",
       "1772            477         998          10        1008           0   \n",
       "1773            409         949           7         956           0   \n",
       "1774            378         971           5         976           0   \n",
       "1775            144         586           6         592           0   \n",
       "1776            154         532           3         535           0   \n",
       "\n",
       "      Unnamed: 10  Unnamed: 11  Unnamed: 12  Unnamed: 13  \n",
       "0         1552691       537371      2090062        74.28  \n",
       "1          149880        51950       201830        74.26  \n",
       "2             926          283         1209        76.59  \n",
       "3             939          279         1218        77.09  \n",
       "4             977          263         1240        78.79  \n",
       "...           ...          ...          ...          ...  \n",
       "1772         1008          337         1345        74.94  \n",
       "1773          956          335         1291        74.05  \n",
       "1774          976          353         1329        73.44  \n",
       "1775          592          216          808        73.27  \n",
       "1776          535          189          724        73.90  \n",
       "\n",
       "[1777 rows x 14 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "presidents_taipei"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>總　計</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>366854</td>\n",
       "      <td>587899</td>\n",
       "      <td>587258</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>北投區</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>35975</td>\n",
       "      <td>61151</td>\n",
       "      <td>51657</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>建民里</td>\n",
       "      <td>1.0</td>\n",
       "      <td>208</td>\n",
       "      <td>401</td>\n",
       "      <td>311</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>建民里</td>\n",
       "      <td>2.0</td>\n",
       "      <td>209</td>\n",
       "      <td>455</td>\n",
       "      <td>272</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>建民里</td>\n",
       "      <td>3.0</td>\n",
       "      <td>221</td>\n",
       "      <td>439</td>\n",
       "      <td>306</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1772</th>\n",
       "      <td>NaN</td>\n",
       "      <td>政大里</td>\n",
       "      <td>1726.0</td>\n",
       "      <td>208</td>\n",
       "      <td>313</td>\n",
       "      <td>477</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1773</th>\n",
       "      <td>NaN</td>\n",
       "      <td>指南里</td>\n",
       "      <td>1727.0</td>\n",
       "      <td>217</td>\n",
       "      <td>323</td>\n",
       "      <td>409</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1774</th>\n",
       "      <td>NaN</td>\n",
       "      <td>指南里</td>\n",
       "      <td>1728.0</td>\n",
       "      <td>279</td>\n",
       "      <td>314</td>\n",
       "      <td>378</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1775</th>\n",
       "      <td>NaN</td>\n",
       "      <td>指南里</td>\n",
       "      <td>1729.0</td>\n",
       "      <td>156</td>\n",
       "      <td>286</td>\n",
       "      <td>144</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1776</th>\n",
       "      <td>NaN</td>\n",
       "      <td>老泉里</td>\n",
       "      <td>1730.0</td>\n",
       "      <td>123</td>\n",
       "      <td>255</td>\n",
       "      <td>154</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1777 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     Unnamed: 0 Unnamed: 1  Unnamed: 2  (1)\\n柯文哲\\n吳欣盈  (2)\\n賴清德\\n蕭美琴  \\\n",
       "0           總　計        NaN         NaN         366854         587899   \n",
       "1          　北投區        NaN         NaN          35975          61151   \n",
       "2           NaN        建民里         1.0            208            401   \n",
       "3           NaN        建民里         2.0            209            455   \n",
       "4           NaN        建民里         3.0            221            439   \n",
       "...         ...        ...         ...            ...            ...   \n",
       "1772        NaN        政大里      1726.0            208            313   \n",
       "1773        NaN        指南里      1727.0            217            323   \n",
       "1774        NaN        指南里      1728.0            279            314   \n",
       "1775        NaN        指南里      1729.0            156            286   \n",
       "1776        NaN        老泉里      1730.0            123            255   \n",
       "\n",
       "      (3)\\n侯友宜\\n趙少康  \n",
       "0            587258  \n",
       "1             51657  \n",
       "2               311  \n",
       "3               272  \n",
       "4               306  \n",
       "...             ...  \n",
       "1772            477  \n",
       "1773            409  \n",
       "1774            378  \n",
       "1775            144  \n",
       "1776            154  \n",
       "\n",
       "[1777 rows x 6 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "presidents_taipei = presidents_taipei.iloc[:, 0:6]\n",
    "presidents_taipei"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>366854</td>\n",
       "      <td>587899</td>\n",
       "      <td>587258</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>北投區</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>35975</td>\n",
       "      <td>61151</td>\n",
       "      <td>51657</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>建民里</td>\n",
       "      <td>1.0</td>\n",
       "      <td>208</td>\n",
       "      <td>401</td>\n",
       "      <td>311</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>建民里</td>\n",
       "      <td>2.0</td>\n",
       "      <td>209</td>\n",
       "      <td>455</td>\n",
       "      <td>272</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>建民里</td>\n",
       "      <td>3.0</td>\n",
       "      <td>221</td>\n",
       "      <td>439</td>\n",
       "      <td>306</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1772</th>\n",
       "      <td>NaN</td>\n",
       "      <td>政大里</td>\n",
       "      <td>1726.0</td>\n",
       "      <td>208</td>\n",
       "      <td>313</td>\n",
       "      <td>477</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1773</th>\n",
       "      <td>NaN</td>\n",
       "      <td>指南里</td>\n",
       "      <td>1727.0</td>\n",
       "      <td>217</td>\n",
       "      <td>323</td>\n",
       "      <td>409</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1774</th>\n",
       "      <td>NaN</td>\n",
       "      <td>指南里</td>\n",
       "      <td>1728.0</td>\n",
       "      <td>279</td>\n",
       "      <td>314</td>\n",
       "      <td>378</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1775</th>\n",
       "      <td>NaN</td>\n",
       "      <td>指南里</td>\n",
       "      <td>1729.0</td>\n",
       "      <td>156</td>\n",
       "      <td>286</td>\n",
       "      <td>144</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1776</th>\n",
       "      <td>NaN</td>\n",
       "      <td>老泉里</td>\n",
       "      <td>1730.0</td>\n",
       "      <td>123</td>\n",
       "      <td>255</td>\n",
       "      <td>154</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1777 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      town village  office  (1)\\n柯文哲\\n吳欣盈  (2)\\n賴清德\\n蕭美琴  (3)\\n侯友宜\\n趙少康\n",
       "0      總　計     NaN     NaN         366854         587899         587258\n",
       "1     　北投區     NaN     NaN          35975          61151          51657\n",
       "2      NaN     建民里     1.0            208            401            311\n",
       "3      NaN     建民里     2.0            209            455            272\n",
       "4      NaN     建民里     3.0            221            439            306\n",
       "...    ...     ...     ...            ...            ...            ...\n",
       "1772   NaN     政大里  1726.0            208            313            477\n",
       "1773   NaN     指南里  1727.0            217            323            409\n",
       "1774   NaN     指南里  1728.0            279            314            378\n",
       "1775   NaN     指南里  1729.0            156            286            144\n",
       "1776   NaN     老泉里  1730.0            123            255            154\n",
       "\n",
       "[1777 rows x 6 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rename_columns = {\n",
    "    \"Unnamed: 0\": \"town\",\n",
    "    \"Unnamed: 1\": \"village\",\n",
    "    \"Unnamed: 2\": \"office\"\n",
    "}\n",
    "presidents_taipei = presidents_taipei.rename(columns=rename_columns)\n",
    "presidents_taipei"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Advanced wrangling including\n",
    "\n",
    "- Dealing with missing values.\n",
    "- Dealing with text values.\n",
    "- Reshaping dataframes.\n",
    "- Concatenating, merging and joining dataframes."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Dealing with missing values\n",
    "\n",
    "- Using `isnull` or `notnull` to check if `np.nan` exists.\n",
    "- Using `dropna` to drop rows with `np.nan`.\n",
    "- Using `fillna` to fill `np.nan` with specific values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1777\n",
      "1764\n",
      "13\n"
     ]
    }
   ],
   "source": [
    "print(presidents_taipei.iloc[:, 0].size)\n",
    "print(presidents_taipei.iloc[:, 0].isnull().sum())\n",
    "print(presidents_taipei.iloc[:, 0].notnull().sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0        總　計\n",
       "1       　北投區\n",
       "2       　北投區\n",
       "3       　北投區\n",
       "4       　北投區\n",
       "        ... \n",
       "1772    　文山區\n",
       "1773    　文山區\n",
       "1774    　文山區\n",
       "1775    　文山區\n",
       "1776    　文山區\n",
       "Name: town, Length: 1777, dtype: object"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "presidents_taipei[\"town\"].fillna(method=\"ffill\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>2</th>\n",
       "      <td>北投區</td>\n",
       "      <td>建民里</td>\n",
       "      <td>1.0</td>\n",
       "      <td>208</td>\n",
       "      <td>401</td>\n",
       "      <td>311</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>北投區</td>\n",
       "      <td>建民里</td>\n",
       "      <td>2.0</td>\n",
       "      <td>209</td>\n",
       "      <td>455</td>\n",
       "      <td>272</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>北投區</td>\n",
       "      <td>建民里</td>\n",
       "      <td>3.0</td>\n",
       "      <td>221</td>\n",
       "      <td>439</td>\n",
       "      <td>306</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>北投區</td>\n",
       "      <td>文林里</td>\n",
       "      <td>4.0</td>\n",
       "      <td>181</td>\n",
       "      <td>396</td>\n",
       "      <td>282</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>北投區</td>\n",
       "      <td>文林里</td>\n",
       "      <td>5.0</td>\n",
       "      <td>206</td>\n",
       "      <td>445</td>\n",
       "      <td>299</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1772</th>\n",
       "      <td>文山區</td>\n",
       "      <td>政大里</td>\n",
       "      <td>1726.0</td>\n",
       "      <td>208</td>\n",
       "      <td>313</td>\n",
       "      <td>477</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1773</th>\n",
       "      <td>文山區</td>\n",
       "      <td>指南里</td>\n",
       "      <td>1727.0</td>\n",
       "      <td>217</td>\n",
       "      <td>323</td>\n",
       "      <td>409</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1774</th>\n",
       "      <td>文山區</td>\n",
       "      <td>指南里</td>\n",
       "      <td>1728.0</td>\n",
       "      <td>279</td>\n",
       "      <td>314</td>\n",
       "      <td>378</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1775</th>\n",
       "      <td>文山區</td>\n",
       "      <td>指南里</td>\n",
       "      <td>1729.0</td>\n",
       "      <td>156</td>\n",
       "      <td>286</td>\n",
       "      <td>144</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1776</th>\n",
       "      <td>文山區</td>\n",
       "      <td>老泉里</td>\n",
       "      <td>1730.0</td>\n",
       "      <td>123</td>\n",
       "      <td>255</td>\n",
       "      <td>154</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1764 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      town village  office  (1)\\n柯文哲\\n吳欣盈  (2)\\n賴清德\\n蕭美琴  (3)\\n侯友宜\\n趙少康\n",
       "2     　北投區     建民里     1.0            208            401            311\n",
       "3     　北投區     建民里     2.0            209            455            272\n",
       "4     　北投區     建民里     3.0            221            439            306\n",
       "5     　北投區     文林里     4.0            181            396            282\n",
       "6     　北投區     文林里     5.0            206            445            299\n",
       "...    ...     ...     ...            ...            ...            ...\n",
       "1772  　文山區     政大里  1726.0            208            313            477\n",
       "1773  　文山區     指南里  1727.0            217            323            409\n",
       "1774  　文山區     指南里  1728.0            279            314            378\n",
       "1775  　文山區     指南里  1729.0            156            286            144\n",
       "1776  　文山區     老泉里  1730.0            123            255            154\n",
       "\n",
       "[1764 rows x 6 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "presidents_taipei[\"town\"] = presidents_taipei[\"town\"].fillna(method=\"ffill\")\n",
    "presidents_taipei = presidents_taipei.dropna()\n",
    "presidents_taipei"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Summarizing text columns\n",
    "\n",
    "- `unique`\n",
    "- `nunique`\n",
    "- `value_counts`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "12\n",
      "['\\u3000北投區' '\\u3000士林區' '\\u3000大同區' '\\u3000中山區' '\\u3000松山區' '\\u3000內湖區'\n",
      " '\\u3000南港區' '\\u3000萬華區' '\\u3000中正區' '\\u3000大安區' '\\u3000信義區' '\\u3000文山區']\n"
     ]
    }
   ],
   "source": [
    "print(presidents_taipei['town'].nunique())\n",
    "print(presidents_taipei['town'].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "town\n",
       "　大安區    194\n",
       "　士林區    187\n",
       "　文山區    175\n",
       "　北投區    169\n",
       "　信義區    168\n",
       "　內湖區    165\n",
       "　中山區    152\n",
       "　萬華區    137\n",
       "　松山區    134\n",
       "　中正區    116\n",
       "　大同區     88\n",
       "　南港區     79\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "presidents_taipei['town'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Splitting strings with `str.split` as a `Series`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    (1)\\n柯文哲\\n吳欣盈\n",
       "1    (2)\\n賴清德\\n蕭美琴\n",
       "2    (3)\\n侯友宜\\n趙少康\n",
       "dtype: object"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "candidates_info = pd.Series(presidents_taipei.columns[-3:])\n",
    "candidates_info"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    [(1), 柯文哲, 吳欣盈]\n",
       "1    [(2), 賴清德, 蕭美琴]\n",
       "2    [(3), 侯友宜, 趙少康]\n",
       "dtype: object"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "candidates_info.str.split()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Replacing strings with `str.replace`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/0b/r__z5mpn6ldgb_w2j7_y_ntr0000gn/T/ipykernel_12497/2434960299.py:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  presidents_taipei['town'] = presidents_taipei['town'].str.replace(\"\\u3000\", \"\")\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "array(['北投區', '士林區', '大同區', '中山區', '松山區', '內湖區', '南港區', '萬華區', '中正區',\n",
       "       '大安區', '信義區', '文山區'], dtype=object)"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "presidents_taipei['town'] = presidents_taipei['town'].str.replace(\"\\u3000\", \"\")\n",
    "presidents_taipei['town'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    1\\n柯文哲\\n吳欣盈\n",
       "1    2\\n賴清德\\n蕭美琴\n",
       "2    3\\n侯友宜\\n趙少康\n",
       "dtype: object"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "candidates_info.str.replace(\"(\", \"\").str.replace(\")\", \"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Testing for strings that match or contain a pattern with `str.contains`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['大同區', '大安區'], dtype=object)"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "contains_da = presidents_taipei['town'].str.contains(\"大\")\n",
    "presidents_taipei['town'][contains_da].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Two formats of a table\n",
    "\n",
    "- Wide: data is presented with each different data variable in a separate column.\n",
    "- Long: data is presented with one column containing all the values and another column listing the context of the value.\n",
    "\n",
    "Source: <https://en.wikipedia.org/wiki/Wide_and_narrow_data>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Wide, or unstacked data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>Person</th>\n",
       "      <th>Age</th>\n",
       "      <th>Weight</th>\n",
       "      <th>Height</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bob</td>\n",
       "      <td>32</td>\n",
       "      <td>168</td>\n",
       "      <td>180</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alice</td>\n",
       "      <td>24</td>\n",
       "      <td>150</td>\n",
       "      <td>175</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Steve</td>\n",
       "      <td>64</td>\n",
       "      <td>144</td>\n",
       "      <td>165</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Person  Age  Weight  Height\n",
       "0    Bob   32     168     180\n",
       "1  Alice   24     150     175\n",
       "2  Steve   64     144     165"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "request_url = \"https://en.wikipedia.org/wiki/Wide_and_narrow_data\"\n",
    "wide_format = pd.read_html(request_url)[0]\n",
    "wide_format"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Narrow, stacked, or long data"
   ]
  },
  {
   "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>Person</th>\n",
       "      <th>Variable</th>\n",
       "      <th>Value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bob</td>\n",
       "      <td>Age</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bob</td>\n",
       "      <td>Weight</td>\n",
       "      <td>168</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Bob</td>\n",
       "      <td>Height</td>\n",
       "      <td>180</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Alice</td>\n",
       "      <td>Age</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Alice</td>\n",
       "      <td>Weight</td>\n",
       "      <td>150</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Alice</td>\n",
       "      <td>Height</td>\n",
       "      <td>175</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Steve</td>\n",
       "      <td>Age</td>\n",
       "      <td>64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Steve</td>\n",
       "      <td>Weight</td>\n",
       "      <td>144</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Steve</td>\n",
       "      <td>Height</td>\n",
       "      <td>165</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Person Variable  Value\n",
       "0    Bob      Age     32\n",
       "1    Bob   Weight    168\n",
       "2    Bob   Height    180\n",
       "3  Alice      Age     24\n",
       "4  Alice   Weight    150\n",
       "5  Alice   Height    175\n",
       "6  Steve      Age     64\n",
       "7  Steve   Weight    144\n",
       "8  Steve   Height    165"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "request_url = \"https://en.wikipedia.org/wiki/Wide_and_narrow_data\"\n",
    "long_format = pd.read_html(request_url)[1]\n",
    "long_format"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Reshaping dataframes from wide to long format with `pd.melt()`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Person</th>\n",
       "      <th>Variable</th>\n",
       "      <th>Value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bob</td>\n",
       "      <td>Age</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alice</td>\n",
       "      <td>Age</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Steve</td>\n",
       "      <td>Age</td>\n",
       "      <td>64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Bob</td>\n",
       "      <td>Weight</td>\n",
       "      <td>168</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Alice</td>\n",
       "      <td>Weight</td>\n",
       "      <td>150</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Steve</td>\n",
       "      <td>Weight</td>\n",
       "      <td>144</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Bob</td>\n",
       "      <td>Height</td>\n",
       "      <td>180</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Alice</td>\n",
       "      <td>Height</td>\n",
       "      <td>175</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Steve</td>\n",
       "      <td>Height</td>\n",
       "      <td>165</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Person Variable  Value\n",
       "0    Bob      Age     32\n",
       "1  Alice      Age     24\n",
       "2  Steve      Age     64\n",
       "3    Bob   Weight    168\n",
       "4  Alice   Weight    150\n",
       "5  Steve   Weight    144\n",
       "6    Bob   Height    180\n",
       "7  Alice   Height    175\n",
       "8  Steve   Height    165"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.melt(wide_format, \n",
    "        id_vars=\"Person\", value_vars=[\"Age\", \"Weight\", \"Height\"],\n",
    "        var_name=\"Variable\", value_name=\"Value\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Reshaping dataframes from long to wide format with `pivot()`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>Person</th>\n",
       "      <th>Age</th>\n",
       "      <th>Height</th>\n",
       "      <th>Weight</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alice</td>\n",
       "      <td>24</td>\n",
       "      <td>175</td>\n",
       "      <td>150</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bob</td>\n",
       "      <td>32</td>\n",
       "      <td>180</td>\n",
       "      <td>168</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Steve</td>\n",
       "      <td>64</td>\n",
       "      <td>165</td>\n",
       "      <td>144</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Person  Age  Height  Weight\n",
       "0  Alice   24     175     150\n",
       "1    Bob   32     180     168\n",
       "2  Steve   64     165     144"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "long_format.pivot(index=\"Person\", columns=\"Variable\", values=\"Value\").reset_index().rename_axis(None, axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## A common problem is that a dataset where some of the column names are not names of variables, but values of a variable"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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>2</th>\n",
       "      <td>北投區</td>\n",
       "      <td>建民里</td>\n",
       "      <td>1.0</td>\n",
       "      <td>208</td>\n",
       "      <td>401</td>\n",
       "      <td>311</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>北投區</td>\n",
       "      <td>建民里</td>\n",
       "      <td>2.0</td>\n",
       "      <td>209</td>\n",
       "      <td>455</td>\n",
       "      <td>272</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>北投區</td>\n",
       "      <td>建民里</td>\n",
       "      <td>3.0</td>\n",
       "      <td>221</td>\n",
       "      <td>439</td>\n",
       "      <td>306</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>北投區</td>\n",
       "      <td>文林里</td>\n",
       "      <td>4.0</td>\n",
       "      <td>181</td>\n",
       "      <td>396</td>\n",
       "      <td>282</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>北投區</td>\n",
       "      <td>文林里</td>\n",
       "      <td>5.0</td>\n",
       "      <td>206</td>\n",
       "      <td>445</td>\n",
       "      <td>299</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1772</th>\n",
       "      <td>文山區</td>\n",
       "      <td>政大里</td>\n",
       "      <td>1726.0</td>\n",
       "      <td>208</td>\n",
       "      <td>313</td>\n",
       "      <td>477</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1773</th>\n",
       "      <td>文山區</td>\n",
       "      <td>指南里</td>\n",
       "      <td>1727.0</td>\n",
       "      <td>217</td>\n",
       "      <td>323</td>\n",
       "      <td>409</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1774</th>\n",
       "      <td>文山區</td>\n",
       "      <td>指南里</td>\n",
       "      <td>1728.0</td>\n",
       "      <td>279</td>\n",
       "      <td>314</td>\n",
       "      <td>378</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1775</th>\n",
       "      <td>文山區</td>\n",
       "      <td>指南里</td>\n",
       "      <td>1729.0</td>\n",
       "      <td>156</td>\n",
       "      <td>286</td>\n",
       "      <td>144</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1776</th>\n",
       "      <td>文山區</td>\n",
       "      <td>老泉里</td>\n",
       "      <td>1730.0</td>\n",
       "      <td>123</td>\n",
       "      <td>255</td>\n",
       "      <td>154</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1764 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     town village  office  (1)\\n柯文哲\\n吳欣盈  (2)\\n賴清德\\n蕭美琴  (3)\\n侯友宜\\n趙少康\n",
       "2     北投區     建民里     1.0            208            401            311\n",
       "3     北投區     建民里     2.0            209            455            272\n",
       "4     北投區     建民里     3.0            221            439            306\n",
       "5     北投區     文林里     4.0            181            396            282\n",
       "6     北投區     文林里     5.0            206            445            299\n",
       "...   ...     ...     ...            ...            ...            ...\n",
       "1772  文山區     政大里  1726.0            208            313            477\n",
       "1773  文山區     指南里  1727.0            217            323            409\n",
       "1774  文山區     指南里  1728.0            279            314            378\n",
       "1775  文山區     指南里  1729.0            156            286            144\n",
       "1776  文山區     老泉里  1730.0            123            255            154\n",
       "\n",
       "[1764 rows x 6 columns]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "presidents_taipei"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## We can pivot the columns into a new pair of variables\n",
    "\n",
    "To describe that operation we need four parameters:\n",
    "\n",
    "- The set of columns whose names are not values\n",
    "- The set of columns whose names are values\n",
    "- The name of the variable to move the column names to\n",
    "- The name of the variable to move the column values to"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## In this example, the four parameters are\n",
    "\n",
    "- `id_vars`: `[\"town\", \"village\", \"office\"]`\n",
    "- `value_vars`: The columns except `id_vars`\n",
    "- `var_name`: We can name it `candidate_info`\n",
    "- `value_name`: Let's name it `votes`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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>1.0</td>\n",
       "      <td>(1)\\n柯文哲\\n吳欣盈</td>\n",
       "      <td>208</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>北投區</td>\n",
       "      <td>建民里</td>\n",
       "      <td>2.0</td>\n",
       "      <td>(1)\\n柯文哲\\n吳欣盈</td>\n",
       "      <td>209</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>北投區</td>\n",
       "      <td>建民里</td>\n",
       "      <td>3.0</td>\n",
       "      <td>(1)\\n柯文哲\\n吳欣盈</td>\n",
       "      <td>221</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>北投區</td>\n",
       "      <td>文林里</td>\n",
       "      <td>4.0</td>\n",
       "      <td>(1)\\n柯文哲\\n吳欣盈</td>\n",
       "      <td>181</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>北投區</td>\n",
       "      <td>文林里</td>\n",
       "      <td>5.0</td>\n",
       "      <td>(1)\\n柯文哲\\n吳欣盈</td>\n",
       "      <td>206</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>5287</th>\n",
       "      <td>文山區</td>\n",
       "      <td>政大里</td>\n",
       "      <td>1726.0</td>\n",
       "      <td>(3)\\n侯友宜\\n趙少康</td>\n",
       "      <td>477</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5288</th>\n",
       "      <td>文山區</td>\n",
       "      <td>指南里</td>\n",
       "      <td>1727.0</td>\n",
       "      <td>(3)\\n侯友宜\\n趙少康</td>\n",
       "      <td>409</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5289</th>\n",
       "      <td>文山區</td>\n",
       "      <td>指南里</td>\n",
       "      <td>1728.0</td>\n",
       "      <td>(3)\\n侯友宜\\n趙少康</td>\n",
       "      <td>378</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5290</th>\n",
       "      <td>文山區</td>\n",
       "      <td>指南里</td>\n",
       "      <td>1729.0</td>\n",
       "      <td>(3)\\n侯友宜\\n趙少康</td>\n",
       "      <td>144</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5291</th>\n",
       "      <td>文山區</td>\n",
       "      <td>老泉里</td>\n",
       "      <td>1730.0</td>\n",
       "      <td>(3)\\n侯友宜\\n趙少康</td>\n",
       "      <td>154</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5292 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     town village  office candidate_info  votes\n",
       "0     北投區     建民里     1.0  (1)\\n柯文哲\\n吳欣盈    208\n",
       "1     北投區     建民里     2.0  (1)\\n柯文哲\\n吳欣盈    209\n",
       "2     北投區     建民里     3.0  (1)\\n柯文哲\\n吳欣盈    221\n",
       "3     北投區     文林里     4.0  (1)\\n柯文哲\\n吳欣盈    181\n",
       "4     北投區     文林里     5.0  (1)\\n柯文哲\\n吳欣盈    206\n",
       "...   ...     ...     ...            ...    ...\n",
       "5287  文山區     政大里  1726.0  (3)\\n侯友宜\\n趙少康    477\n",
       "5288  文山區     指南里  1727.0  (3)\\n侯友宜\\n趙少康    409\n",
       "5289  文山區     指南里  1728.0  (3)\\n侯友宜\\n趙少康    378\n",
       "5290  文山區     指南里  1729.0  (3)\\n侯友宜\\n趙少康    144\n",
       "5291  文山區     老泉里  1730.0  (3)\\n侯友宜\\n趙少康    154\n",
       "\n",
       "[5292 rows x 5 columns]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "idVars = [\"town\", \"village\", \"office\"]\n",
    "presidents_taipei_long = pd.melt(presidents_taipei,\n",
    "                                 id_vars=idVars,\n",
    "                                 var_name=\"candidate_info\",\n",
    "                                 value_name=\"votes\")\n",
    "presidents_taipei_long"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Concatenating, merging and joining dataframes\n",
    "\n",
    "- `concat()` vertically or horizontally.\n",
    "- `merge()` horizontally on column names.\n",
    "- `join()` horizontally on index."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "db_file_path = \"data/taiwan_election_2024.db\"\n",
    "conn = sqlite3.connect(db_file_path)\n",
    "presidents = pd.read_sql(\"\"\"SELECT * FROM presidents;\"\"\", conn)\n",
    "candidates = pd.read_sql(\"\"\"SELECT * FROM candidates;\"\"\", conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Use `concat()` function to concatenate vertically or horizontally"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>party_id</th>\n",
       "      <th>election_type_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>丁學忠</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>傅崐萁</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>吳政杰</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>呂玉玲</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>周宏昌</td>\n",
       "      <td>1</td>\n",
       "      <td>2</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>327</th>\n",
       "      <td>328</td>\n",
       "      <td>高美珠</td>\n",
       "      <td>30</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>328</th>\n",
       "      <td>329</td>\n",
       "      <td>侯友宜/趙少康</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>328</th>\n",
       "      <td>329</td>\n",
       "      <td>侯友宜/趙少康</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>329</th>\n",
       "      <td>330</td>\n",
       "      <td>柯文哲/吳欣盈</td>\n",
       "      <td>15</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>330</th>\n",
       "      <td>331</td>\n",
       "      <td>賴清德/蕭美琴</td>\n",
       "      <td>29</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>332 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      id     name  party_id  election_type_id\n",
       "0      1      丁學忠         1                 2\n",
       "1      2      傅崐萁         1                 2\n",
       "2      3      吳政杰         1                 2\n",
       "3      4      呂玉玲         1                 2\n",
       "4      5      周宏昌         1                 2\n",
       "..   ...      ...       ...               ...\n",
       "327  328      高美珠        30                 4\n",
       "328  329  侯友宜/趙少康         1                 1\n",
       "328  329  侯友宜/趙少康         1                 1\n",
       "329  330  柯文哲/吳欣盈        15                 1\n",
       "330  331  賴清德/蕭美琴        29                 1\n",
       "\n",
       "[332 rows x 4 columns]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat((candidates.loc[:328, :], candidates.loc[328:, :]))"
   ]
  },
  {
   "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>id</th>\n",
       "      <th>name</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>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>326</th>\n",
       "      <td>327</td>\n",
       "      <td>陳政宗</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>327</th>\n",
       "      <td>328</td>\n",
       "      <td>高美珠</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>328</th>\n",
       "      <td>329</td>\n",
       "      <td>侯友宜/趙少康</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>329</th>\n",
       "      <td>330</td>\n",
       "      <td>柯文哲/吳欣盈</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>330</th>\n",
       "      <td>331</td>\n",
       "      <td>賴清德/蕭美琴</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>331 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      id     name\n",
       "0      1      丁學忠\n",
       "1      2      傅崐萁\n",
       "2      3      吳政杰\n",
       "3      4      呂玉玲\n",
       "4      5      周宏昌\n",
       "..   ...      ...\n",
       "326  327      陳政宗\n",
       "327  328      高美珠\n",
       "328  329  侯友宜/趙少康\n",
       "329  330  柯文哲/吳欣盈\n",
       "330  331  賴清德/蕭美琴\n",
       "\n",
       "[331 rows x 2 columns]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat((candidates[[\"id\"]], candidates[[\"name\"]]), axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Using `merge` function to join dataframes on columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id_x</th>\n",
       "      <th>number</th>\n",
       "      <th>district_id</th>\n",
       "      <th>candidate_id</th>\n",
       "      <th>votes</th>\n",
       "      <th>election_type_id_x</th>\n",
       "      <th>village_id</th>\n",
       "      <th>id_y</th>\n",
       "      <th>name</th>\n",
       "      <th>party_id</th>\n",
       "      <th>election_type_id_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>15035</td>\n",
       "      <td>330</td>\n",
       "      <td>146</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>330</td>\n",
       "      <td>柯文哲/吳欣盈</td>\n",
       "      <td>15</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>15036</td>\n",
       "      <td>330</td>\n",
       "      <td>128</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>330</td>\n",
       "      <td>柯文哲/吳欣盈</td>\n",
       "      <td>15</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>15037</td>\n",
       "      <td>330</td>\n",
       "      <td>239</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>330</td>\n",
       "      <td>柯文哲/吳欣盈</td>\n",
       "      <td>15</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>15038</td>\n",
       "      <td>330</td>\n",
       "      <td>208</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>330</td>\n",
       "      <td>柯文哲/吳欣盈</td>\n",
       "      <td>15</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>15039</td>\n",
       "      <td>330</td>\n",
       "      <td>210</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>330</td>\n",
       "      <td>柯文哲/吳欣盈</td>\n",
       "      <td>15</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53380</th>\n",
       "      <td>53381</td>\n",
       "      <td>3</td>\n",
       "      <td>7914</td>\n",
       "      <td>329</td>\n",
       "      <td>192</td>\n",
       "      <td>1</td>\n",
       "      <td>794</td>\n",
       "      <td>329</td>\n",
       "      <td>侯友宜/趙少康</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53381</th>\n",
       "      <td>53382</td>\n",
       "      <td>3</td>\n",
       "      <td>7915</td>\n",
       "      <td>329</td>\n",
       "      <td>268</td>\n",
       "      <td>1</td>\n",
       "      <td>5107</td>\n",
       "      <td>329</td>\n",
       "      <td>侯友宜/趙少康</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53382</th>\n",
       "      <td>53383</td>\n",
       "      <td>3</td>\n",
       "      <td>7916</td>\n",
       "      <td>329</td>\n",
       "      <td>224</td>\n",
       "      <td>1</td>\n",
       "      <td>5108</td>\n",
       "      <td>329</td>\n",
       "      <td>侯友宜/趙少康</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53383</th>\n",
       "      <td>53384</td>\n",
       "      <td>3</td>\n",
       "      <td>7917</td>\n",
       "      <td>329</td>\n",
       "      <td>238</td>\n",
       "      <td>1</td>\n",
       "      <td>484</td>\n",
       "      <td>329</td>\n",
       "      <td>侯友宜/趙少康</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53384</th>\n",
       "      <td>53385</td>\n",
       "      <td>3</td>\n",
       "      <td>7918</td>\n",
       "      <td>329</td>\n",
       "      <td>364</td>\n",
       "      <td>1</td>\n",
       "      <td>5109</td>\n",
       "      <td>329</td>\n",
       "      <td>侯友宜/趙少康</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>53385 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        id_x  number  district_id  candidate_id  votes  election_type_id_x  \\\n",
       "0          1       1        15035           330    146                   1   \n",
       "1          2       1        15036           330    128                   1   \n",
       "2          3       1        15037           330    239                   1   \n",
       "3          4       1        15038           330    208                   1   \n",
       "4          5       1        15039           330    210                   1   \n",
       "...      ...     ...          ...           ...    ...                 ...   \n",
       "53380  53381       3         7914           329    192                   1   \n",
       "53381  53382       3         7915           329    268                   1   \n",
       "53382  53383       3         7916           329    224                   1   \n",
       "53383  53384       3         7917           329    238                   1   \n",
       "53384  53385       3         7918           329    364                   1   \n",
       "\n",
       "       village_id  id_y     name  party_id  election_type_id_y  \n",
       "0               1   330  柯文哲/吳欣盈        15                   1  \n",
       "1               1   330  柯文哲/吳欣盈        15                   1  \n",
       "2               2   330  柯文哲/吳欣盈        15                   1  \n",
       "3               3   330  柯文哲/吳欣盈        15                   1  \n",
       "4               4   330  柯文哲/吳欣盈        15                   1  \n",
       "...           ...   ...      ...       ...                 ...  \n",
       "53380         794   329  侯友宜/趙少康         1                   1  \n",
       "53381        5107   329  侯友宜/趙少康         1                   1  \n",
       "53382        5108   329  侯友宜/趙少康         1                   1  \n",
       "53383         484   329  侯友宜/趙少康         1                   1  \n",
       "53384        5109   329  侯友宜/趙少康         1                   1  \n",
       "\n",
       "[53385 rows x 11 columns]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# default: inner join\n",
    "pd.merge(presidents, candidates, left_on=\"candidate_id\", right_on=\"id\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "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>id_x</th>\n",
       "      <th>number</th>\n",
       "      <th>district_id</th>\n",
       "      <th>candidate_id</th>\n",
       "      <th>votes</th>\n",
       "      <th>election_type_id_x</th>\n",
       "      <th>village_id</th>\n",
       "      <th>id_y</th>\n",
       "      <th>name</th>\n",
       "      <th>party_id</th>\n",
       "      <th>election_type_id_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>11</td>\n",
       "      <td>2</td>\n",
       "      <td>15035</td>\n",
       "      <td>331</td>\n",
       "      <td>56</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>331</td>\n",
       "      <td>賴清德/蕭美琴</td>\n",
       "      <td>29</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>12</td>\n",
       "      <td>2</td>\n",
       "      <td>15036</td>\n",
       "      <td>331</td>\n",
       "      <td>67</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>331</td>\n",
       "      <td>賴清德/蕭美琴</td>\n",
       "      <td>29</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>13</td>\n",
       "      <td>2</td>\n",
       "      <td>15037</td>\n",
       "      <td>331</td>\n",
       "      <td>103</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>331</td>\n",
       "      <td>賴清德/蕭美琴</td>\n",
       "      <td>29</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>14</td>\n",
       "      <td>2</td>\n",
       "      <td>15038</td>\n",
       "      <td>331</td>\n",
       "      <td>82</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>331</td>\n",
       "      <td>賴清德/蕭美琴</td>\n",
       "      <td>29</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>15</td>\n",
       "      <td>2</td>\n",
       "      <td>15039</td>\n",
       "      <td>331</td>\n",
       "      <td>84</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>331</td>\n",
       "      <td>賴清德/蕭美琴</td>\n",
       "      <td>29</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17790</th>\n",
       "      <td>51997</td>\n",
       "      <td>2</td>\n",
       "      <td>7914</td>\n",
       "      <td>331</td>\n",
       "      <td>83</td>\n",
       "      <td>1</td>\n",
       "      <td>794</td>\n",
       "      <td>331</td>\n",
       "      <td>賴清德/蕭美琴</td>\n",
       "      <td>29</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17791</th>\n",
       "      <td>51998</td>\n",
       "      <td>2</td>\n",
       "      <td>7915</td>\n",
       "      <td>331</td>\n",
       "      <td>58</td>\n",
       "      <td>1</td>\n",
       "      <td>5107</td>\n",
       "      <td>331</td>\n",
       "      <td>賴清德/蕭美琴</td>\n",
       "      <td>29</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17792</th>\n",
       "      <td>51999</td>\n",
       "      <td>2</td>\n",
       "      <td>7916</td>\n",
       "      <td>331</td>\n",
       "      <td>60</td>\n",
       "      <td>1</td>\n",
       "      <td>5108</td>\n",
       "      <td>331</td>\n",
       "      <td>賴清德/蕭美琴</td>\n",
       "      <td>29</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17793</th>\n",
       "      <td>52000</td>\n",
       "      <td>2</td>\n",
       "      <td>7917</td>\n",
       "      <td>331</td>\n",
       "      <td>48</td>\n",
       "      <td>1</td>\n",
       "      <td>484</td>\n",
       "      <td>331</td>\n",
       "      <td>賴清德/蕭美琴</td>\n",
       "      <td>29</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17794</th>\n",
       "      <td>52001</td>\n",
       "      <td>2</td>\n",
       "      <td>7918</td>\n",
       "      <td>331</td>\n",
       "      <td>183</td>\n",
       "      <td>1</td>\n",
       "      <td>5109</td>\n",
       "      <td>331</td>\n",
       "      <td>賴清德/蕭美琴</td>\n",
       "      <td>29</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>17795 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        id_x  number  district_id  candidate_id  votes  election_type_id_x  \\\n",
       "0         11       2        15035           331     56                   1   \n",
       "1         12       2        15036           331     67                   1   \n",
       "2         13       2        15037           331    103                   1   \n",
       "3         14       2        15038           331     82                   1   \n",
       "4         15       2        15039           331     84                   1   \n",
       "...      ...     ...          ...           ...    ...                 ...   \n",
       "17790  51997       2         7914           331     83                   1   \n",
       "17791  51998       2         7915           331     58                   1   \n",
       "17792  51999       2         7916           331     60                   1   \n",
       "17793  52000       2         7917           331     48                   1   \n",
       "17794  52001       2         7918           331    183                   1   \n",
       "\n",
       "       village_id  id_y     name  party_id  election_type_id_y  \n",
       "0               1   331  賴清德/蕭美琴        29                   1  \n",
       "1               1   331  賴清德/蕭美琴        29                   1  \n",
       "2               2   331  賴清德/蕭美琴        29                   1  \n",
       "3               3   331  賴清德/蕭美琴        29                   1  \n",
       "4               4   331  賴清德/蕭美琴        29                   1  \n",
       "...           ...   ...      ...       ...                 ...  \n",
       "17790         794   331  賴清德/蕭美琴        29                   1  \n",
       "17791        5107   331  賴清德/蕭美琴        29                   1  \n",
       "17792        5108   331  賴清德/蕭美琴        29                   1  \n",
       "17793         484   331  賴清德/蕭美琴        29                   1  \n",
       "17794        5109   331  賴清德/蕭美琴        29                   1  \n",
       "\n",
       "[17795 rows x 11 columns]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# left join\n",
    "pd.merge(presidents[presidents[\"number\"] == 2], candidates, left_on=\"candidate_id\", right_on=\"id\", how=\"left\")"
   ]
  },
  {
   "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>id_x</th>\n",
       "      <th>number</th>\n",
       "      <th>district_id</th>\n",
       "      <th>candidate_id</th>\n",
       "      <th>votes</th>\n",
       "      <th>election_type_id_x</th>\n",
       "      <th>village_id</th>\n",
       "      <th>id_y</th>\n",
       "      <th>name</th>\n",
       "      <th>party_id</th>\n",
       "      <th>election_type_id_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>丁學忠</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>傅崐萁</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "      <td>吳政杰</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4</td>\n",
       "      <td>呂玉玲</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5</td>\n",
       "      <td>周宏昌</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53708</th>\n",
       "      <td>51997.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>7914.0</td>\n",
       "      <td>331.0</td>\n",
       "      <td>83.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>794.0</td>\n",
       "      <td>331</td>\n",
       "      <td>賴清德/蕭美琴</td>\n",
       "      <td>29</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53709</th>\n",
       "      <td>51998.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>7915.0</td>\n",
       "      <td>331.0</td>\n",
       "      <td>58.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5107.0</td>\n",
       "      <td>331</td>\n",
       "      <td>賴清德/蕭美琴</td>\n",
       "      <td>29</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53710</th>\n",
       "      <td>51999.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>7916.0</td>\n",
       "      <td>331.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5108.0</td>\n",
       "      <td>331</td>\n",
       "      <td>賴清德/蕭美琴</td>\n",
       "      <td>29</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53711</th>\n",
       "      <td>52000.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>7917.0</td>\n",
       "      <td>331.0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>484.0</td>\n",
       "      <td>331</td>\n",
       "      <td>賴清德/蕭美琴</td>\n",
       "      <td>29</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53712</th>\n",
       "      <td>52001.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>7918.0</td>\n",
       "      <td>331.0</td>\n",
       "      <td>183.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5109.0</td>\n",
       "      <td>331</td>\n",
       "      <td>賴清德/蕭美琴</td>\n",
       "      <td>29</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>53713 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          id_x  number  district_id  candidate_id  votes  election_type_id_x  \\\n",
       "0          NaN     NaN          NaN           NaN    NaN                 NaN   \n",
       "1          NaN     NaN          NaN           NaN    NaN                 NaN   \n",
       "2          NaN     NaN          NaN           NaN    NaN                 NaN   \n",
       "3          NaN     NaN          NaN           NaN    NaN                 NaN   \n",
       "4          NaN     NaN          NaN           NaN    NaN                 NaN   \n",
       "...        ...     ...          ...           ...    ...                 ...   \n",
       "53708  51997.0     2.0       7914.0         331.0   83.0                 1.0   \n",
       "53709  51998.0     2.0       7915.0         331.0   58.0                 1.0   \n",
       "53710  51999.0     2.0       7916.0         331.0   60.0                 1.0   \n",
       "53711  52000.0     2.0       7917.0         331.0   48.0                 1.0   \n",
       "53712  52001.0     2.0       7918.0         331.0  183.0                 1.0   \n",
       "\n",
       "       village_id  id_y     name  party_id  election_type_id_y  \n",
       "0             NaN     1      丁學忠         1                   2  \n",
       "1             NaN     2      傅崐萁         1                   2  \n",
       "2             NaN     3      吳政杰         1                   2  \n",
       "3             NaN     4      呂玉玲         1                   2  \n",
       "4             NaN     5      周宏昌         1                   2  \n",
       "...           ...   ...      ...       ...                 ...  \n",
       "53708       794.0   331  賴清德/蕭美琴        29                   1  \n",
       "53709      5107.0   331  賴清德/蕭美琴        29                   1  \n",
       "53710      5108.0   331  賴清德/蕭美琴        29                   1  \n",
       "53711       484.0   331  賴清德/蕭美琴        29                   1  \n",
       "53712      5109.0   331  賴清德/蕭美琴        29                   1  \n",
       "\n",
       "[53713 rows x 11 columns]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# right join\n",
    "pd.merge(presidents, candidates, left_on=\"candidate_id\", right_on=\"id\", how=\"right\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Using `join` method to join dataframes on index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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>id</th>\n",
       "      <th>number</th>\n",
       "      <th>district_id</th>\n",
       "      <th>votes</th>\n",
       "      <th>election_type_id_x</th>\n",
       "      <th>village_id</th>\n",
       "      <th>name</th>\n",
       "      <th>party_id</th>\n",
       "      <th>election_type_id_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>331</th>\n",
       "      <td>11</td>\n",
       "      <td>2</td>\n",
       "      <td>15035</td>\n",
       "      <td>56</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>賴清德/蕭美琴</td>\n",
       "      <td>29</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>331</th>\n",
       "      <td>12</td>\n",
       "      <td>2</td>\n",
       "      <td>15036</td>\n",
       "      <td>67</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>賴清德/蕭美琴</td>\n",
       "      <td>29</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>331</th>\n",
       "      <td>13</td>\n",
       "      <td>2</td>\n",
       "      <td>15037</td>\n",
       "      <td>103</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>賴清德/蕭美琴</td>\n",
       "      <td>29</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>331</th>\n",
       "      <td>14</td>\n",
       "      <td>2</td>\n",
       "      <td>15038</td>\n",
       "      <td>82</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>賴清德/蕭美琴</td>\n",
       "      <td>29</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>331</th>\n",
       "      <td>15</td>\n",
       "      <td>2</td>\n",
       "      <td>15039</td>\n",
       "      <td>84</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>賴清德/蕭美琴</td>\n",
       "      <td>29</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>331</th>\n",
       "      <td>51997</td>\n",
       "      <td>2</td>\n",
       "      <td>7914</td>\n",
       "      <td>83</td>\n",
       "      <td>1</td>\n",
       "      <td>794</td>\n",
       "      <td>賴清德/蕭美琴</td>\n",
       "      <td>29</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>331</th>\n",
       "      <td>51998</td>\n",
       "      <td>2</td>\n",
       "      <td>7915</td>\n",
       "      <td>58</td>\n",
       "      <td>1</td>\n",
       "      <td>5107</td>\n",
       "      <td>賴清德/蕭美琴</td>\n",
       "      <td>29</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>331</th>\n",
       "      <td>51999</td>\n",
       "      <td>2</td>\n",
       "      <td>7916</td>\n",
       "      <td>60</td>\n",
       "      <td>1</td>\n",
       "      <td>5108</td>\n",
       "      <td>賴清德/蕭美琴</td>\n",
       "      <td>29</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>331</th>\n",
       "      <td>52000</td>\n",
       "      <td>2</td>\n",
       "      <td>7917</td>\n",
       "      <td>48</td>\n",
       "      <td>1</td>\n",
       "      <td>484</td>\n",
       "      <td>賴清德/蕭美琴</td>\n",
       "      <td>29</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>331</th>\n",
       "      <td>52001</td>\n",
       "      <td>2</td>\n",
       "      <td>7918</td>\n",
       "      <td>183</td>\n",
       "      <td>1</td>\n",
       "      <td>5109</td>\n",
       "      <td>賴清德/蕭美琴</td>\n",
       "      <td>29</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>17795 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        id  number  district_id  votes  election_type_id_x  village_id  \\\n",
       "331     11       2        15035     56                   1           1   \n",
       "331     12       2        15036     67                   1           1   \n",
       "331     13       2        15037    103                   1           2   \n",
       "331     14       2        15038     82                   1           3   \n",
       "331     15       2        15039     84                   1           4   \n",
       "..     ...     ...          ...    ...                 ...         ...   \n",
       "331  51997       2         7914     83                   1         794   \n",
       "331  51998       2         7915     58                   1        5107   \n",
       "331  51999       2         7916     60                   1        5108   \n",
       "331  52000       2         7917     48                   1         484   \n",
       "331  52001       2         7918    183                   1        5109   \n",
       "\n",
       "        name  party_id  election_type_id_y  \n",
       "331  賴清德/蕭美琴        29                   1  \n",
       "331  賴清德/蕭美琴        29                   1  \n",
       "331  賴清德/蕭美琴        29                   1  \n",
       "331  賴清德/蕭美琴        29                   1  \n",
       "331  賴清德/蕭美琴        29                   1  \n",
       "..       ...       ...                 ...  \n",
       "331  賴清德/蕭美琴        29                   1  \n",
       "331  賴清德/蕭美琴        29                   1  \n",
       "331  賴清德/蕭美琴        29                   1  \n",
       "331  賴清德/蕭美琴        29                   1  \n",
       "331  賴清德/蕭美琴        29                   1  \n",
       "\n",
       "[17795 rows x 9 columns]"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# join dataframes on index\n",
    "# default: left join\n",
    "presidents[presidents[\"number\"] == 2].set_index(\"candidate_id\").join(candidates.set_index(\"id\"), lsuffix='_x', rsuffix='_y')"
   ]
  },
  {
   "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>id</th>\n",
       "      <th>number</th>\n",
       "      <th>district_id</th>\n",
       "      <th>votes</th>\n",
       "      <th>election_type_id_x</th>\n",
       "      <th>village_id</th>\n",
       "      <th>name</th>\n",
       "      <th>party_id</th>\n",
       "      <th>election_type_id_y</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>candidate_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>329</th>\n",
       "      <td>21</td>\n",
       "      <td>3</td>\n",
       "      <td>15035</td>\n",
       "      <td>414</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>侯友宜/趙少康</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>329</th>\n",
       "      <td>22</td>\n",
       "      <td>3</td>\n",
       "      <td>15036</td>\n",
       "      <td>341</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>侯友宜/趙少康</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>329</th>\n",
       "      <td>23</td>\n",
       "      <td>3</td>\n",
       "      <td>15037</td>\n",
       "      <td>612</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>侯友宜/趙少康</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>329</th>\n",
       "      <td>24</td>\n",
       "      <td>3</td>\n",
       "      <td>15038</td>\n",
       "      <td>398</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>侯友宜/趙少康</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>329</th>\n",
       "      <td>25</td>\n",
       "      <td>3</td>\n",
       "      <td>15039</td>\n",
       "      <td>566</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>侯友宜/趙少康</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>331</th>\n",
       "      <td>51997</td>\n",
       "      <td>2</td>\n",
       "      <td>7914</td>\n",
       "      <td>83</td>\n",
       "      <td>1</td>\n",
       "      <td>794</td>\n",
       "      <td>賴清德/蕭美琴</td>\n",
       "      <td>29</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>331</th>\n",
       "      <td>51998</td>\n",
       "      <td>2</td>\n",
       "      <td>7915</td>\n",
       "      <td>58</td>\n",
       "      <td>1</td>\n",
       "      <td>5107</td>\n",
       "      <td>賴清德/蕭美琴</td>\n",
       "      <td>29</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>331</th>\n",
       "      <td>51999</td>\n",
       "      <td>2</td>\n",
       "      <td>7916</td>\n",
       "      <td>60</td>\n",
       "      <td>1</td>\n",
       "      <td>5108</td>\n",
       "      <td>賴清德/蕭美琴</td>\n",
       "      <td>29</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>331</th>\n",
       "      <td>52000</td>\n",
       "      <td>2</td>\n",
       "      <td>7917</td>\n",
       "      <td>48</td>\n",
       "      <td>1</td>\n",
       "      <td>484</td>\n",
       "      <td>賴清德/蕭美琴</td>\n",
       "      <td>29</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>331</th>\n",
       "      <td>52001</td>\n",
       "      <td>2</td>\n",
       "      <td>7918</td>\n",
       "      <td>183</td>\n",
       "      <td>1</td>\n",
       "      <td>5109</td>\n",
       "      <td>賴清德/蕭美琴</td>\n",
       "      <td>29</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>53385 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 id  number  district_id  votes  election_type_id_x  \\\n",
       "candidate_id                                                          \n",
       "329              21       3        15035    414                   1   \n",
       "329              22       3        15036    341                   1   \n",
       "329              23       3        15037    612                   1   \n",
       "329              24       3        15038    398                   1   \n",
       "329              25       3        15039    566                   1   \n",
       "...             ...     ...          ...    ...                 ...   \n",
       "331           51997       2         7914     83                   1   \n",
       "331           51998       2         7915     58                   1   \n",
       "331           51999       2         7916     60                   1   \n",
       "331           52000       2         7917     48                   1   \n",
       "331           52001       2         7918    183                   1   \n",
       "\n",
       "              village_id     name  party_id  election_type_id_y  \n",
       "candidate_id                                                     \n",
       "329                    1  侯友宜/趙少康         1                   1  \n",
       "329                    1  侯友宜/趙少康         1                   1  \n",
       "329                    2  侯友宜/趙少康         1                   1  \n",
       "329                    3  侯友宜/趙少康         1                   1  \n",
       "329                    4  侯友宜/趙少康         1                   1  \n",
       "...                  ...      ...       ...                 ...  \n",
       "331                  794  賴清德/蕭美琴        29                   1  \n",
       "331                 5107  賴清德/蕭美琴        29                   1  \n",
       "331                 5108  賴清德/蕭美琴        29                   1  \n",
       "331                  484  賴清德/蕭美琴        29                   1  \n",
       "331                 5109  賴清德/蕭美琴        29                   1  \n",
       "\n",
       "[53385 rows x 9 columns]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# inner join\n",
    "presidents.set_index(\"candidate_id\").join(candidates.set_index(\"id\"), lsuffix='_x', rsuffix='_y', how=\"inner\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "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>id</th>\n",
       "      <th>number</th>\n",
       "      <th>district_id</th>\n",
       "      <th>votes</th>\n",
       "      <th>election_type_id_x</th>\n",
       "      <th>village_id</th>\n",
       "      <th>name</th>\n",
       "      <th>party_id</th>\n",
       "      <th>election_type_id_y</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>candidate_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>丁學忠</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>傅崐萁</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>吳政杰</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>呂玉玲</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>周宏昌</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>331</th>\n",
       "      <td>51997.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>7914.0</td>\n",
       "      <td>83.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>794.0</td>\n",
       "      <td>賴清德/蕭美琴</td>\n",
       "      <td>29</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>331</th>\n",
       "      <td>51998.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>7915.0</td>\n",
       "      <td>58.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5107.0</td>\n",
       "      <td>賴清德/蕭美琴</td>\n",
       "      <td>29</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>331</th>\n",
       "      <td>51999.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>7916.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5108.0</td>\n",
       "      <td>賴清德/蕭美琴</td>\n",
       "      <td>29</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>331</th>\n",
       "      <td>52000.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>7917.0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>484.0</td>\n",
       "      <td>賴清德/蕭美琴</td>\n",
       "      <td>29</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>331</th>\n",
       "      <td>52001.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>7918.0</td>\n",
       "      <td>183.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5109.0</td>\n",
       "      <td>賴清德/蕭美琴</td>\n",
       "      <td>29</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>53713 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                   id  number  district_id  votes  election_type_id_x  \\\n",
       "candidate_id                                                            \n",
       "1                 NaN     NaN          NaN    NaN                 NaN   \n",
       "2                 NaN     NaN          NaN    NaN                 NaN   \n",
       "3                 NaN     NaN          NaN    NaN                 NaN   \n",
       "4                 NaN     NaN          NaN    NaN                 NaN   \n",
       "5                 NaN     NaN          NaN    NaN                 NaN   \n",
       "...               ...     ...          ...    ...                 ...   \n",
       "331           51997.0     2.0       7914.0   83.0                 1.0   \n",
       "331           51998.0     2.0       7915.0   58.0                 1.0   \n",
       "331           51999.0     2.0       7916.0   60.0                 1.0   \n",
       "331           52000.0     2.0       7917.0   48.0                 1.0   \n",
       "331           52001.0     2.0       7918.0  183.0                 1.0   \n",
       "\n",
       "              village_id     name  party_id  election_type_id_y  \n",
       "candidate_id                                                     \n",
       "1                    NaN      丁學忠         1                   2  \n",
       "2                    NaN      傅崐萁         1                   2  \n",
       "3                    NaN      吳政杰         1                   2  \n",
       "4                    NaN      呂玉玲         1                   2  \n",
       "5                    NaN      周宏昌         1                   2  \n",
       "...                  ...      ...       ...                 ...  \n",
       "331                794.0  賴清德/蕭美琴        29                   1  \n",
       "331               5107.0  賴清德/蕭美琴        29                   1  \n",
       "331               5108.0  賴清德/蕭美琴        29                   1  \n",
       "331                484.0  賴清德/蕭美琴        29                   1  \n",
       "331               5109.0  賴清德/蕭美琴        29                   1  \n",
       "\n",
       "[53713 rows x 9 columns]"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# right join\n",
    "presidents.set_index(\"candidate_id\").join(candidates.set_index(\"id\"), lsuffix='_x', rsuffix='_y', how=\"right\")"
   ]
  }
 ],
 "metadata": {
  "celltoolbar": "Slideshow",
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}