08-dataframe-wrangling-with-pandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Programming and Data Analysis\n",
    "\n",
    "> Dataframe Wrangling with Pandas\n",
    "\n",
    "Kuo, Yao-Jen <yaojenkuo@ntu.edu.tw> from [DATAINPOINT](https://www.datainpoint.com/)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "from datetime import date\n",
    "from datetime import timedelta\n",
    "import urllib.error\n",
    "import re\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## About `pandas`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## What is `pandas`?\n",
    "\n",
    "> Flexible and powerful data analysis / manipulation library for Python, providing labeled data structures similar to R data.frame objects, statistical functions, and much more.\n",
    "\n",
    "Source: <https://github.com/pandas-dev/pandas>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Why `pandas`?\n",
    "\n",
    "Python used to have a weak spot in its analysis capability due to it did not have an appropriate structure handling the common tabular datasets. Pythonists had to switch to a more data-centric language like R or Matlab during the analysis stage until the presence of `pandas`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Import Pandas with `import` command\n",
    "\n",
    "Pandas is officially aliased as `pd`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## If Pandas is not installed, we will encounter a `ModuleNotFoundError`\n",
    "\n",
    "```\n",
    "Traceback (most recent call last):\n",
    "  File \"<stdin>\", line 1, in <module>\n",
    "ModuleNotFoundError: No module named 'pandas'\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Use `pip install` at Terminal to install pandas\n",
    "\n",
    "```bash\n",
    "pip install pandas\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Check version and its installation file path\n",
    "\n",
    "- `__version__` attribute\n",
    "- `__file__` attribute"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1.2.4\n",
      "/Users/kuoyaojen/opt/miniconda3/envs/pyds/lib/python3.8/site-packages/pandas/__init__.py\n"
     ]
    }
   ],
   "source": [
    "print(pd.__version__)\n",
    "print(pd.__file__)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## What does `pandas` mean?\n",
    "\n",
    "![](https://media.giphy.com/media/46Zj6ze2Z2t4k/giphy.gif)\n",
    "\n",
    "Source: <https://giphy.com/>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Turns out its naming has nothing to do with panda the animal, it refers to three primary class customed by its author [Wes McKinney](https://wesmckinney.com/)\n",
    "\n",
    "- **Pan**el(Deprecated since version 0.20.0)\n",
    "- **Da**taFrame\n",
    "- **S**eries"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## In order to master `pandas`, it is vital to understand the relationships between `Index`, `ndarray`, `Series`, and `DataFrame`\n",
    "\n",
    "- An `Index` and a `ndarray` assembles a `Series`.\n",
    "- A couple of `Series` that sharing the same `Index` can then form a `DataFrame`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## `Index` from Pandas\n",
    "\n",
    "The simplest way to create an `Index` is using `pd.Index()`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pandas.core.indexes.numeric.Int64Index"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "prime_indices = pd.Index([2, 3, 5, 7, 11, 13, 17, 19, 23, 29])\n",
    "type(prime_indices)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## An `Index` is like a combination of `tuple` and `set`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index does not support mutable operations\n"
     ]
    }
   ],
   "source": [
    "# immutable\n",
    "prime_indices = pd.Index([2, 3, 5, 7, 11, 13, 17, 19, 23, 29])\n",
    "try:\n",
    "    prime_indices[-1] = 31\n",
    "except TypeError as e:\n",
    "    print(e)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Int64Index([3, 5, 7, 11, 13, 17, 19, 23, 29], dtype='int64')\n",
      "Int64Index([1, 2, 3, 5, 7, 9, 11, 13, 15, 17, 19, 21, 23, 25, 27, 29], dtype='int64')\n",
      "Int64Index([1, 2, 9, 15, 21, 25, 27], dtype='int64')\n",
      "Int64Index([2], dtype='int64')\n",
      "Int64Index([1, 9, 15, 21, 25, 27], dtype='int64')\n"
     ]
    }
   ],
   "source": [
    "# Index has the characteristics of a set\n",
    "odd_indices = pd.Index(range(1, 30, 2))\n",
    "print(prime_indices.intersection(odd_indices))         # prime_indices & odd_indices\n",
    "print(prime_indices.union(odd_indices))                # prime_indices | odd_indices\n",
    "print(prime_indices.symmetric_difference(odd_indices)) # prime_indices ^ odd_indices\n",
    "print(prime_indices.difference(odd_indices))\n",
    "print(odd_indices.difference(prime_indices))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## `Series` from Pandas\n",
    "\n",
    "The simplest way to create a `Series` is using `pd.Series()`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.series.Series'>\n",
      "0     2\n",
      "1     3\n",
      "2     5\n",
      "3     7\n",
      "4    11\n",
      "5    13\n",
      "6    17\n",
      "7    19\n",
      "8    23\n",
      "9    29\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "prime_series = pd.Series([2, 3, 5, 7, 11, 13, 17, 19, 23, 29])\n",
    "print(type(prime_series))\n",
    "print(prime_series)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## A `Series` is a combination of `Index` and `ndarray`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.indexes.range.RangeIndex'>\n",
      "<class 'numpy.ndarray'>\n"
     ]
    }
   ],
   "source": [
    "print(type(prime_series.index))\n",
    "print(type(prime_series.values))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## The index of a `Series` can be customized"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1      2\n",
       "2      3\n",
       "3      5\n",
       "4      7\n",
       "5     11\n",
       "6     13\n",
       "7     17\n",
       "8     19\n",
       "9     23\n",
       "10    29\n",
       "dtype: int64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "prime_series = pd.Series([2, 3, 5, 7, 11, 13, 17, 19, 23, 29],\n",
    "                        index=range(1, 11))\n",
    "prime_series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1      2\n",
       "2      3\n",
       "3      5\n",
       "4      7\n",
       "5     11\n",
       "6     13\n",
       "7     17\n",
       "8     19\n",
       "9     23\n",
       "10    29\n",
       "dtype: int64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "prime_series = pd.Series([2, 3, 5, 7, 11, 13, 17, 19, 23, 29])\n",
    "prime_series.index = range(1, 11)\n",
    "prime_series"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Indexing a `Series`\n",
    "\n",
    "Indexing via index positions or index labels."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "11\n",
      "11\n"
     ]
    }
   ],
   "source": [
    "prime_series = pd.Series([2, 3, 5, 7, 11],\n",
    "                        index=[\"1st\", \"2nd\", \"3rd\", \"4th\", \"5th\"])\n",
    "print(prime_series[-1])\n",
    "print(prime_series[\"5th\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Slicing a `Series`\n",
    "\n",
    "Slicing via index positions excludes `stop`, while slicing via index labels includes `stop`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1st    2\n",
      "2nd    3\n",
      "dtype: int64\n",
      "1st    2\n",
      "2nd    3\n",
      "3rd    5\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "print(prime_series[:2])\n",
    "print(prime_series[\"1st\":\"3rd\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## A `Series` contains a `ndarray` therefore they can be manipulated in the same way \n",
    "\n",
    "- Vectorization and broadcasting.\n",
    "- Fancy indexing.\n",
    "- Boolean indexing."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1st      4\n",
       "2nd      9\n",
       "3rd     25\n",
       "4th     49\n",
       "5th    121\n",
       "dtype: int64"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Vectorization and broadcasting\n",
    "prime_series**2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1st     2\n",
      "2nd     3\n",
      "5th    11\n",
      "dtype: int64\n",
      "1st     2\n",
      "2nd     3\n",
      "5th    11\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# Fancy indexing\n",
    "print(prime_series[[0, 1, 4]])\n",
    "print(prime_series[[\"1st\", \"2nd\", \"5th\"]])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2nd     3\n",
       "3rd     5\n",
       "4th     7\n",
       "5th    11\n",
       "dtype: int64"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Boolean indexing\n",
    "prime_series[prime_series % 2 == 1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## `DataFrame` from Pandas\n",
    "\n",
    "The simplest way to create a `DataFrame` is using `pd.DataFrame()`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pandas.core.frame.DataFrame"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# column-wise\n",
    "movie_df = pd.DataFrame()\n",
    "movie_df[\"title\"] = [\"The Shawshank Redemption\", \"The Dark Knight\", \"Schindler's List\", \"Forrest Gump\", \"Inception\"]\n",
    "movie_df[\"imdb_rating\"] = [9.3, 9.0, 8.9, 8.8, 8.7]\n",
    "type(movie_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>imdb_rating</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>The Shawshank Redemption</td>\n",
       "      <td>9.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>The Dark Knight</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Schindler's List</td>\n",
       "      <td>8.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Forrest Gump</td>\n",
       "      <td>8.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Inception</td>\n",
       "      <td>8.7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      title  imdb_rating\n",
       "0  The Shawshank Redemption          9.3\n",
       "1           The Dark Knight          9.0\n",
       "2          Schindler's List          8.9\n",
       "3              Forrest Gump          8.8\n",
       "4                 Inception          8.7"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movie_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## `DataFrame` from Pandas\n",
    "\n",
    "Creating a `DataFrame` with a `dict`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pandas.core.frame.DataFrame"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# column-wise\n",
    "movie_dict = {\n",
    "    \"title\": [\"The Shawshank Redemption\", \"The Dark Knight\", \"Schindler's List\", \"Forrest Gump\", \"Inception\"],\n",
    "    \"imdb_rating\": [9.3, 9.0, 8.9, 8.8, 8.7]\n",
    "}\n",
    "movie_df = pd.DataFrame(movie_dict)\n",
    "type(movie_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>imdb_rating</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>The Shawshank Redemption</td>\n",
       "      <td>9.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>The Dark Knight</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Schindler's List</td>\n",
       "      <td>8.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Forrest Gump</td>\n",
       "      <td>8.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Inception</td>\n",
       "      <td>8.7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      title  imdb_rating\n",
       "0  The Shawshank Redemption          9.3\n",
       "1           The Dark Knight          9.0\n",
       "2          Schindler's List          8.9\n",
       "3              Forrest Gump          8.8\n",
       "4                 Inception          8.7"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movie_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## `DataFrame` from Pandas\n",
    "\n",
    "Creating a `DataFrame` with a `list` of dictionaries."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pandas.core.frame.DataFrame"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# row-wise\n",
    "movie_list = [\n",
    "    {\"title\": \"The Shawshank Redemption\", \"imdb_rating\": 9.3},\n",
    "    {\"title\": \"The Dark Knight\", \"imdb_rating\": 9.0},\n",
    "    {\"title\": \"Schindler's List\", \"imdb_rating\": 8.9},\n",
    "    {\"title\": \"Forrest Gump\", \"imdb_rating\": 8.8},\n",
    "    {\"title\": \"Inception\", \"imdb_rating\": 8.7}\n",
    "]\n",
    "movie_df = pd.DataFrame(movie_list)\n",
    "type(movie_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>imdb_rating</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>The Shawshank Redemption</td>\n",
       "      <td>9.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>The Dark Knight</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Schindler's List</td>\n",
       "      <td>8.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Forrest Gump</td>\n",
       "      <td>8.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Inception</td>\n",
       "      <td>8.7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      title  imdb_rating\n",
       "0  The Shawshank Redemption          9.3\n",
       "1           The Dark Knight          9.0\n",
       "2          Schindler's List          8.9\n",
       "3              Forrest Gump          8.8\n",
       "4                 Inception          8.7"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movie_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## A `DataFrame` is a combination of multiple `Series` sharing the same `Index`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.indexes.range.RangeIndex'>\n",
      "<class 'pandas.core.series.Series'>\n",
      "<class 'pandas.core.series.Series'>\n"
     ]
    }
   ],
   "source": [
    "print(type(movie_df.index))\n",
    "print(type(movie_df[\"title\"]))\n",
    "print(type(movie_df[\"imdb_rating\"]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Review of the definition of modern data science\n",
    "\n",
    "> Modern data science is a huge field, it invovles applications and tools like importing, tidying, transformation, visualization, modeling, and communication. Surrounding all these is programming.\n",
    "\n",
    "![Imgur](https://i.imgur.com/din6Ig6.png)\n",
    "\n",
    "Source: [R for Data Science](https://r4ds.had.co.nz/)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Key functionalities analysts rely on `pandas` are\n",
    "\n",
    "- Importing\n",
    "- Tidying\n",
    "- Transforming"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Tidying and transforming together is also known as WRANGLING\n",
    "\n",
    "![](https://media.giphy.com/media/MnlZWRFHR4xruE4N2Z/giphy.gif)\n",
    "\n",
    "Source: <https://giphy.com>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Importing"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## `pandas` has massive functions importing tabular data\n",
    "\n",
    "- Flat text file\n",
    "- Database table\n",
    "- Spreadsheet\n",
    "- JSON\n",
    "- HTML `<table></table>` tags\n",
    "- ...etc.\n",
    "\n",
    "Source: <https://pandas.pydata.org/pandas-docs/stable/user_guide/io.html>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Using `read_csv` function for flat text files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [],
   "source": [
    "def get_latest_daily_report():\n",
    "    data_date = date.today()\n",
    "    day_delta = timedelta(days=1)\n",
    "    while True:\n",
    "        data_date_str = date.strftime(data_date, '%m-%d-%Y')\n",
    "        print(\"Try importing {} data...\".format(data_date_str))\n",
    "        daily_report_url = \"https://raw.githubusercontent.com\\\n",
    "/CSSEGISandData/COVID-19/master\\\n",
    "/csse_covid_19_data/csse_covid_19_daily_reports/{}.csv\".format(data_date_str)\n",
    "        try:\n",
    "            daily_report = pd.read_csv(daily_report_url)\n",
    "            print(\"Successfully imported {} data!\".format(data_date_str))\n",
    "            break\n",
    "        except urllib.error.HTTPError:\n",
    "            data_date -= day_delta\n",
    "    return daily_report"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Try importing 12-09-2021 data...\n",
      "Try importing 12-08-2021 data...\n",
      "Successfully imported 12-08-2021 data!\n"
     ]
    }
   ],
   "source": [
    "daily_report = get_latest_daily_report()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## The `data` folder of our environment\n",
    "\n",
    "[classroom-programming-and-data-analysis/data](https://mybinder.org/v2/gh/datainpoint/classroom-programming-and-data-analysis/HEAD?labpath=data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Using `read_sql` function for database tables\n",
    "\n",
    "```python\n",
    "import sqlite3\n",
    "\n",
    "conn = sqlite3.connect('YOUR_DATABASE.db')\n",
    "sql_query = \"\"\"\n",
    "SELECT * \n",
    "  FROM YOUR_TABLE\n",
    " LIMIT 10;\n",
    "\"\"\"\n",
    "pd.read_sql(sql_query, conn)\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Using `read_excel` function for spreadsheets\n",
    "\n",
    "```python\n",
    "excel_file_path = \"PATH/TO/YOUR/EXCEL/FILE\"\n",
    "pd.read_excel(excel_file_path)\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Using `read_json` function for JSON\n",
    "\n",
    "```python\n",
    "json_file_path = \"PATH/TO/YOUR/JSON/FILE\"\n",
    "pd.read_json(json_file_path)\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## What is JSON?\n",
    "\n",
    "> JSON (JavaScript Object Notation) is a lightweight data-interchange format. JSON is built on two structures:\n",
    ">\n",
    "> A collection of name/value pairs. In various languages, this is realized as an object, record, struct, dictionary, hash table, keyed list, or associative array. An ordered list of values. In most languages, this is realized as an array, vector, list, or sequence.\n",
    "\n",
    "Source: <https://www.json.org/json-en.html>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Two flavors of tabular data\n",
    "\n",
    "- Row-based JSON.\n",
    "- Column-based JSON."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Using `read_html` function for HTML `<table></table>` tags\n",
    "\n",
    "> The `<table>` tag defines an HTML table. An HTML table consists of one `<table>` element and one or more `<tr>`, `<th>`, and `<td>` elements. The `<tr>` element defines a table row, the `<th>` element defines a table header, and the `<td>` element defines a table cell.\n",
    "\n",
    "Source: <https://www.w3schools.com/default.asp>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'list'>\n",
      "1\n"
     ]
    }
   ],
   "source": [
    "request_url = \"https://www.imdb.com/chart/top\"\n",
    "html_tables = pd.read_html(request_url)\n",
    "print(type(html_tables))\n",
    "print(len(html_tables))"
   ]
  },
  {
   "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>Unnamed: 0</th>\n",
       "      <th>Rank &amp; Title</th>\n",
       "      <th>IMDb Rating</th>\n",
       "      <th>Your Rating</th>\n",
       "      <th>Unnamed: 4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>1.  刺激1995  (1994)</td>\n",
       "      <td>9.2</td>\n",
       "      <td>12345678910 NOT YET RELEASED  Seen</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>2.  教父  (1972)</td>\n",
       "      <td>9.1</td>\n",
       "      <td>12345678910 NOT YET RELEASED  Seen</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>3.  教父第二集  (1974)</td>\n",
       "      <td>9.0</td>\n",
       "      <td>12345678910 NOT YET RELEASED  Seen</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>4.  黑暗騎士  (2008)</td>\n",
       "      <td>9.0</td>\n",
       "      <td>12345678910 NOT YET RELEASED  Seen</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>5.  十二怒漢  (1957)</td>\n",
       "      <td>8.9</td>\n",
       "      <td>12345678910 NOT YET RELEASED  Seen</td>\n",
       "      <td>NaN</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>245</th>\n",
       "      <td>NaN</td>\n",
       "      <td>246.  96  (2018)</td>\n",
       "      <td>8.0</td>\n",
       "      <td>12345678910 NOT YET RELEASED  Seen</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>246</th>\n",
       "      <td>NaN</td>\n",
       "      <td>247.  卡比莉亞之夜  (1957)</td>\n",
       "      <td>8.0</td>\n",
       "      <td>12345678910 NOT YET RELEASED  Seen</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>247</th>\n",
       "      <td>NaN</td>\n",
       "      <td>248.  公主新娘  (1987)</td>\n",
       "      <td>8.0</td>\n",
       "      <td>12345678910 NOT YET RELEASED  Seen</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>248</th>\n",
       "      <td>NaN</td>\n",
       "      <td>249.  巴黎，德州  (1984)</td>\n",
       "      <td>8.0</td>\n",
       "      <td>12345678910 NOT YET RELEASED  Seen</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>249</th>\n",
       "      <td>NaN</td>\n",
       "      <td>250.  Ratsasan  (2018)</td>\n",
       "      <td>8.0</td>\n",
       "      <td>12345678910 NOT YET RELEASED  Seen</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>250 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     Unnamed: 0            Rank & Title  IMDb Rating  \\\n",
       "0           NaN      1.  刺激1995  (1994)          9.2   \n",
       "1           NaN          2.  教父  (1972)          9.1   \n",
       "2           NaN       3.  教父第二集  (1974)          9.0   \n",
       "3           NaN        4.  黑暗騎士  (2008)          9.0   \n",
       "4           NaN        5.  十二怒漢  (1957)          8.9   \n",
       "..          ...                     ...          ...   \n",
       "245         NaN        246.  96  (2018)          8.0   \n",
       "246         NaN    247.  卡比莉亞之夜  (1957)          8.0   \n",
       "247         NaN      248.  公主新娘  (1987)          8.0   \n",
       "248         NaN     249.  巴黎，德州  (1984)          8.0   \n",
       "249         NaN  250.  Ratsasan  (2018)          8.0   \n",
       "\n",
       "                            Your Rating  Unnamed: 4  \n",
       "0    12345678910 NOT YET RELEASED  Seen         NaN  \n",
       "1    12345678910 NOT YET RELEASED  Seen         NaN  \n",
       "2    12345678910 NOT YET RELEASED  Seen         NaN  \n",
       "3    12345678910 NOT YET RELEASED  Seen         NaN  \n",
       "4    12345678910 NOT YET RELEASED  Seen         NaN  \n",
       "..                                  ...         ...  \n",
       "245  12345678910 NOT YET RELEASED  Seen         NaN  \n",
       "246  12345678910 NOT YET RELEASED  Seen         NaN  \n",
       "247  12345678910 NOT YET RELEASED  Seen         NaN  \n",
       "248  12345678910 NOT YET RELEASED  Seen         NaN  \n",
       "249  12345678910 NOT YET RELEASED  Seen         NaN  \n",
       "\n",
       "[250 rows x 5 columns]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "html_tables[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Basic attributes and methods"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Basic attributes of a `DataFrame` object\n",
    "\n",
    "- `shape`\n",
    "- `dtypes`\n",
    "- `index`\n",
    "- `columns`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(4006, 14)\n",
      "FIPS                   float64\n",
      "Admin2                  object\n",
      "Province_State          object\n",
      "Country_Region          object\n",
      "Last_Update             object\n",
      "Lat                    float64\n",
      "Long_                  float64\n",
      "Confirmed                int64\n",
      "Deaths                   int64\n",
      "Recovered              float64\n",
      "Active                 float64\n",
      "Combined_Key            object\n",
      "Incident_Rate          float64\n",
      "Case_Fatality_Ratio    float64\n",
      "dtype: object\n",
      "RangeIndex(start=0, stop=4006, step=1)\n",
      "Index(['FIPS', 'Admin2', 'Province_State', 'Country_Region', 'Last_Update',\n",
      "       'Lat', 'Long_', 'Confirmed', 'Deaths', 'Recovered', 'Active',\n",
      "       'Combined_Key', 'Incident_Rate', 'Case_Fatality_Ratio'],\n",
      "      dtype='object')\n"
     ]
    }
   ],
   "source": [
    "print(daily_report.shape)\n",
    "print(daily_report.dtypes)\n",
    "print(daily_report.index)\n",
    "print(daily_report.columns)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Basic methods of a `DataFrame` object\n",
    "\n",
    "- `head(n)`\n",
    "- `tail(n)`\n",
    "- `describe()`\n",
    "- `info()`\n",
    "- `set_index()`\n",
    "- `reset_index()`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## `head(n)` returns the top n observations with header"
   ]
  },
  {
   "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>FIPS</th>\n",
       "      <th>Admin2</th>\n",
       "      <th>Province_State</th>\n",
       "      <th>Country_Region</th>\n",
       "      <th>Last_Update</th>\n",
       "      <th>Lat</th>\n",
       "      <th>Long_</th>\n",
       "      <th>Confirmed</th>\n",
       "      <th>Deaths</th>\n",
       "      <th>Recovered</th>\n",
       "      <th>Active</th>\n",
       "      <th>Combined_Key</th>\n",
       "      <th>Incident_Rate</th>\n",
       "      <th>Case_Fatality_Ratio</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>Afghanistan</td>\n",
       "      <td>2021-12-09 04:22:49</td>\n",
       "      <td>33.93911</td>\n",
       "      <td>67.709953</td>\n",
       "      <td>157542</td>\n",
       "      <td>7317</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>404.697441</td>\n",
       "      <td>4.644476</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Albania</td>\n",
       "      <td>2021-12-09 04:22:49</td>\n",
       "      <td>41.15330</td>\n",
       "      <td>20.168300</td>\n",
       "      <td>202641</td>\n",
       "      <td>3126</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Albania</td>\n",
       "      <td>7041.524776</td>\n",
       "      <td>1.542630</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Algeria</td>\n",
       "      <td>2021-12-09 04:22:49</td>\n",
       "      <td>28.03390</td>\n",
       "      <td>1.659600</td>\n",
       "      <td>212047</td>\n",
       "      <td>6122</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Algeria</td>\n",
       "      <td>483.562044</td>\n",
       "      <td>2.887096</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Andorra</td>\n",
       "      <td>2021-12-09 04:22:49</td>\n",
       "      <td>42.50630</td>\n",
       "      <td>1.521800</td>\n",
       "      <td>18815</td>\n",
       "      <td>133</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Andorra</td>\n",
       "      <td>24351.258655</td>\n",
       "      <td>0.706883</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Angola</td>\n",
       "      <td>2021-12-09 04:22:49</td>\n",
       "      <td>-11.20270</td>\n",
       "      <td>17.873900</td>\n",
       "      <td>65332</td>\n",
       "      <td>1735</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Angola</td>\n",
       "      <td>198.781316</td>\n",
       "      <td>2.655666</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   FIPS Admin2 Province_State Country_Region          Last_Update       Lat  \\\n",
       "0   NaN    NaN            NaN    Afghanistan  2021-12-09 04:22:49  33.93911   \n",
       "1   NaN    NaN            NaN        Albania  2021-12-09 04:22:49  41.15330   \n",
       "2   NaN    NaN            NaN        Algeria  2021-12-09 04:22:49  28.03390   \n",
       "3   NaN    NaN            NaN        Andorra  2021-12-09 04:22:49  42.50630   \n",
       "4   NaN    NaN            NaN         Angola  2021-12-09 04:22:49 -11.20270   \n",
       "\n",
       "       Long_  Confirmed  Deaths  Recovered  Active Combined_Key  \\\n",
       "0  67.709953     157542    7317        NaN     NaN  Afghanistan   \n",
       "1  20.168300     202641    3126        NaN     NaN      Albania   \n",
       "2   1.659600     212047    6122        NaN     NaN      Algeria   \n",
       "3   1.521800      18815     133        NaN     NaN      Andorra   \n",
       "4  17.873900      65332    1735        NaN     NaN       Angola   \n",
       "\n",
       "   Incident_Rate  Case_Fatality_Ratio  \n",
       "0     404.697441             4.644476  \n",
       "1    7041.524776             1.542630  \n",
       "2     483.562044             2.887096  \n",
       "3   24351.258655             0.706883  \n",
       "4     198.781316             2.655666  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "daily_report.head() # n is default to 5"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## `tail(n)` returns the bottom n observations with header"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "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>FIPS</th>\n",
       "      <th>Admin2</th>\n",
       "      <th>Province_State</th>\n",
       "      <th>Country_Region</th>\n",
       "      <th>Last_Update</th>\n",
       "      <th>Lat</th>\n",
       "      <th>Long_</th>\n",
       "      <th>Confirmed</th>\n",
       "      <th>Deaths</th>\n",
       "      <th>Recovered</th>\n",
       "      <th>Active</th>\n",
       "      <th>Combined_Key</th>\n",
       "      <th>Incident_Rate</th>\n",
       "      <th>Case_Fatality_Ratio</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4003</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yemen</td>\n",
       "      <td>2021-12-09 04:22:49</td>\n",
       "      <td>15.552727</td>\n",
       "      <td>48.516388</td>\n",
       "      <td>10047</td>\n",
       "      <td>1956</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yemen</td>\n",
       "      <td>33.685411</td>\n",
       "      <td>19.468498</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4004</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Zambia</td>\n",
       "      <td>2021-12-09 04:22:49</td>\n",
       "      <td>-13.133897</td>\n",
       "      <td>27.849332</td>\n",
       "      <td>210436</td>\n",
       "      <td>3668</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Zambia</td>\n",
       "      <td>1144.672017</td>\n",
       "      <td>1.743048</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4005</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>2021-12-09 04:22:49</td>\n",
       "      <td>-19.015438</td>\n",
       "      <td>29.154857</td>\n",
       "      <td>150628</td>\n",
       "      <td>4720</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>1013.447755</td>\n",
       "      <td>3.133548</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      FIPS Admin2 Province_State Country_Region          Last_Update  \\\n",
       "4003   NaN    NaN            NaN          Yemen  2021-12-09 04:22:49   \n",
       "4004   NaN    NaN            NaN         Zambia  2021-12-09 04:22:49   \n",
       "4005   NaN    NaN            NaN       Zimbabwe  2021-12-09 04:22:49   \n",
       "\n",
       "            Lat      Long_  Confirmed  Deaths  Recovered  Active Combined_Key  \\\n",
       "4003  15.552727  48.516388      10047    1956        NaN     NaN        Yemen   \n",
       "4004 -13.133897  27.849332     210436    3668        NaN     NaN       Zambia   \n",
       "4005 -19.015438  29.154857     150628    4720        NaN     NaN     Zimbabwe   \n",
       "\n",
       "      Incident_Rate  Case_Fatality_Ratio  \n",
       "4003      33.685411            19.468498  \n",
       "4004    1144.672017             1.743048  \n",
       "4005    1013.447755             3.133548  "
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "daily_report.tail(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## `describe()` returns the descriptive summary for numeric columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "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>FIPS</th>\n",
       "      <th>Lat</th>\n",
       "      <th>Long_</th>\n",
       "      <th>Confirmed</th>\n",
       "      <th>Deaths</th>\n",
       "      <th>Recovered</th>\n",
       "      <th>Active</th>\n",
       "      <th>Incident_Rate</th>\n",
       "      <th>Case_Fatality_Ratio</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>3268.000000</td>\n",
       "      <td>3916.000000</td>\n",
       "      <td>3916.000000</td>\n",
       "      <td>4.006000e+03</td>\n",
       "      <td>4006.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3915.000000</td>\n",
       "      <td>3964.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>32405.943390</td>\n",
       "      <td>35.804041</td>\n",
       "      <td>-71.352046</td>\n",
       "      <td>6.685715e+04</td>\n",
       "      <td>1317.823265</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14295.344321</td>\n",
       "      <td>2.811766</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>18056.381177</td>\n",
       "      <td>13.248365</td>\n",
       "      <td>54.904663</td>\n",
       "      <td>3.713593e+05</td>\n",
       "      <td>7253.747542</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5723.693073</td>\n",
       "      <td>46.395049</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>60.000000</td>\n",
       "      <td>-52.368000</td>\n",
       "      <td>-178.116500</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>19048.500000</td>\n",
       "      <td>33.204471</td>\n",
       "      <td>-96.600814</td>\n",
       "      <td>1.905250e+03</td>\n",
       "      <td>30.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11541.032519</td>\n",
       "      <td>1.152276</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>30068.000000</td>\n",
       "      <td>37.898574</td>\n",
       "      <td>-86.773508</td>\n",
       "      <td>5.352500e+03</td>\n",
       "      <td>88.500000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15515.023968</td>\n",
       "      <td>1.623308</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>47041.500000</td>\n",
       "      <td>42.178547</td>\n",
       "      <td>-77.393577</td>\n",
       "      <td>2.180900e+04</td>\n",
       "      <td>307.750000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>17993.447894</td>\n",
       "      <td>2.226409</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>99999.000000</td>\n",
       "      <td>71.706900</td>\n",
       "      <td>178.065000</td>\n",
       "      <td>9.000391e+06</td>\n",
       "      <td>154571.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>56541.670487</td>\n",
       "      <td>2796.551724</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               FIPS          Lat        Long_     Confirmed         Deaths  \\\n",
       "count   3268.000000  3916.000000  3916.000000  4.006000e+03    4006.000000   \n",
       "mean   32405.943390    35.804041   -71.352046  6.685715e+04    1317.823265   \n",
       "std    18056.381177    13.248365    54.904663  3.713593e+05    7253.747542   \n",
       "min       60.000000   -52.368000  -178.116500  0.000000e+00       0.000000   \n",
       "25%    19048.500000    33.204471   -96.600814  1.905250e+03      30.000000   \n",
       "50%    30068.000000    37.898574   -86.773508  5.352500e+03      88.500000   \n",
       "75%    47041.500000    42.178547   -77.393577  2.180900e+04     307.750000   \n",
       "max    99999.000000    71.706900   178.065000  9.000391e+06  154571.000000   \n",
       "\n",
       "       Recovered  Active  Incident_Rate  Case_Fatality_Ratio  \n",
       "count        0.0     0.0    3915.000000          3964.000000  \n",
       "mean         NaN     NaN   14295.344321             2.811766  \n",
       "std          NaN     NaN    5723.693073            46.395049  \n",
       "min          NaN     NaN       0.000000             0.000000  \n",
       "25%          NaN     NaN   11541.032519             1.152276  \n",
       "50%          NaN     NaN   15515.023968             1.623308  \n",
       "75%          NaN     NaN   17993.447894             2.226409  \n",
       "max          NaN     NaN   56541.670487          2796.551724  "
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "daily_report.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## `info()` returns the concise information of the dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 4006 entries, 0 to 4005\n",
      "Data columns (total 14 columns):\n",
      " #   Column               Non-Null Count  Dtype  \n",
      "---  ------               --------------  -----  \n",
      " 0   FIPS                 3268 non-null   float64\n",
      " 1   Admin2               3272 non-null   object \n",
      " 2   Province_State       3832 non-null   object \n",
      " 3   Country_Region       4006 non-null   object \n",
      " 4   Last_Update          4006 non-null   object \n",
      " 5   Lat                  3916 non-null   float64\n",
      " 6   Long_                3916 non-null   float64\n",
      " 7   Confirmed            4006 non-null   int64  \n",
      " 8   Deaths               4006 non-null   int64  \n",
      " 9   Recovered            0 non-null      float64\n",
      " 10  Active               0 non-null      float64\n",
      " 11  Combined_Key         4006 non-null   object \n",
      " 12  Incident_Rate        3915 non-null   float64\n",
      " 13  Case_Fatality_Ratio  3964 non-null   float64\n",
      "dtypes: float64(7), int64(2), object(5)\n",
      "memory usage: 438.3+ KB\n"
     ]
    }
   ],
   "source": [
    "daily_report.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## `set_index()` replaces current `Index` with a specific variable"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "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>FIPS</th>\n",
       "      <th>Admin2</th>\n",
       "      <th>Province_State</th>\n",
       "      <th>Country_Region</th>\n",
       "      <th>Last_Update</th>\n",
       "      <th>Lat</th>\n",
       "      <th>Long_</th>\n",
       "      <th>Confirmed</th>\n",
       "      <th>Deaths</th>\n",
       "      <th>Recovered</th>\n",
       "      <th>Active</th>\n",
       "      <th>Incident_Rate</th>\n",
       "      <th>Case_Fatality_Ratio</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Combined_Key</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Afghanistan</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>2021-12-09 04:22:49</td>\n",
       "      <td>33.939110</td>\n",
       "      <td>67.709953</td>\n",
       "      <td>157542</td>\n",
       "      <td>7317</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>404.697441</td>\n",
       "      <td>4.644476</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Albania</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Albania</td>\n",
       "      <td>2021-12-09 04:22:49</td>\n",
       "      <td>41.153300</td>\n",
       "      <td>20.168300</td>\n",
       "      <td>202641</td>\n",
       "      <td>3126</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7041.524776</td>\n",
       "      <td>1.542630</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Algeria</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Algeria</td>\n",
       "      <td>2021-12-09 04:22:49</td>\n",
       "      <td>28.033900</td>\n",
       "      <td>1.659600</td>\n",
       "      <td>212047</td>\n",
       "      <td>6122</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>483.562044</td>\n",
       "      <td>2.887096</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Andorra</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Andorra</td>\n",
       "      <td>2021-12-09 04:22:49</td>\n",
       "      <td>42.506300</td>\n",
       "      <td>1.521800</td>\n",
       "      <td>18815</td>\n",
       "      <td>133</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>24351.258655</td>\n",
       "      <td>0.706883</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Angola</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Angola</td>\n",
       "      <td>2021-12-09 04:22:49</td>\n",
       "      <td>-11.202700</td>\n",
       "      <td>17.873900</td>\n",
       "      <td>65332</td>\n",
       "      <td>1735</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>198.781316</td>\n",
       "      <td>2.655666</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Vietnam</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Vietnam</td>\n",
       "      <td>2021-12-09 04:22:49</td>\n",
       "      <td>14.058324</td>\n",
       "      <td>108.277199</td>\n",
       "      <td>1352122</td>\n",
       "      <td>26930</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1389.091518</td>\n",
       "      <td>1.991684</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>West Bank and Gaza</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>West Bank and Gaza</td>\n",
       "      <td>2021-12-09 04:22:49</td>\n",
       "      <td>31.952200</td>\n",
       "      <td>35.233200</td>\n",
       "      <td>462958</td>\n",
       "      <td>4823</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9075.088172</td>\n",
       "      <td>1.041779</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Yemen</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yemen</td>\n",
       "      <td>2021-12-09 04:22:49</td>\n",
       "      <td>15.552727</td>\n",
       "      <td>48.516388</td>\n",
       "      <td>10047</td>\n",
       "      <td>1956</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>33.685411</td>\n",
       "      <td>19.468498</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Zambia</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Zambia</td>\n",
       "      <td>2021-12-09 04:22:49</td>\n",
       "      <td>-13.133897</td>\n",
       "      <td>27.849332</td>\n",
       "      <td>210436</td>\n",
       "      <td>3668</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1144.672017</td>\n",
       "      <td>1.743048</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Zimbabwe</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>2021-12-09 04:22:49</td>\n",
       "      <td>-19.015438</td>\n",
       "      <td>29.154857</td>\n",
       "      <td>150628</td>\n",
       "      <td>4720</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1013.447755</td>\n",
       "      <td>3.133548</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4006 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                    FIPS Admin2 Province_State      Country_Region  \\\n",
       "Combined_Key                                                         \n",
       "Afghanistan          NaN    NaN            NaN         Afghanistan   \n",
       "Albania              NaN    NaN            NaN             Albania   \n",
       "Algeria              NaN    NaN            NaN             Algeria   \n",
       "Andorra              NaN    NaN            NaN             Andorra   \n",
       "Angola               NaN    NaN            NaN              Angola   \n",
       "...                  ...    ...            ...                 ...   \n",
       "Vietnam              NaN    NaN            NaN             Vietnam   \n",
       "West Bank and Gaza   NaN    NaN            NaN  West Bank and Gaza   \n",
       "Yemen                NaN    NaN            NaN               Yemen   \n",
       "Zambia               NaN    NaN            NaN              Zambia   \n",
       "Zimbabwe             NaN    NaN            NaN            Zimbabwe   \n",
       "\n",
       "                            Last_Update        Lat       Long_  Confirmed  \\\n",
       "Combined_Key                                                                \n",
       "Afghanistan         2021-12-09 04:22:49  33.939110   67.709953     157542   \n",
       "Albania             2021-12-09 04:22:49  41.153300   20.168300     202641   \n",
       "Algeria             2021-12-09 04:22:49  28.033900    1.659600     212047   \n",
       "Andorra             2021-12-09 04:22:49  42.506300    1.521800      18815   \n",
       "Angola              2021-12-09 04:22:49 -11.202700   17.873900      65332   \n",
       "...                                 ...        ...         ...        ...   \n",
       "Vietnam             2021-12-09 04:22:49  14.058324  108.277199    1352122   \n",
       "West Bank and Gaza  2021-12-09 04:22:49  31.952200   35.233200     462958   \n",
       "Yemen               2021-12-09 04:22:49  15.552727   48.516388      10047   \n",
       "Zambia              2021-12-09 04:22:49 -13.133897   27.849332     210436   \n",
       "Zimbabwe            2021-12-09 04:22:49 -19.015438   29.154857     150628   \n",
       "\n",
       "                    Deaths  Recovered  Active  Incident_Rate  \\\n",
       "Combined_Key                                                   \n",
       "Afghanistan           7317        NaN     NaN     404.697441   \n",
       "Albania               3126        NaN     NaN    7041.524776   \n",
       "Algeria               6122        NaN     NaN     483.562044   \n",
       "Andorra                133        NaN     NaN   24351.258655   \n",
       "Angola                1735        NaN     NaN     198.781316   \n",
       "...                    ...        ...     ...            ...   \n",
       "Vietnam              26930        NaN     NaN    1389.091518   \n",
       "West Bank and Gaza    4823        NaN     NaN    9075.088172   \n",
       "Yemen                 1956        NaN     NaN      33.685411   \n",
       "Zambia                3668        NaN     NaN    1144.672017   \n",
       "Zimbabwe              4720        NaN     NaN    1013.447755   \n",
       "\n",
       "                    Case_Fatality_Ratio  \n",
       "Combined_Key                             \n",
       "Afghanistan                    4.644476  \n",
       "Albania                        1.542630  \n",
       "Algeria                        2.887096  \n",
       "Andorra                        0.706883  \n",
       "Angola                         2.655666  \n",
       "...                                 ...  \n",
       "Vietnam                        1.991684  \n",
       "West Bank and Gaza             1.041779  \n",
       "Yemen                         19.468498  \n",
       "Zambia                         1.743048  \n",
       "Zimbabwe                       3.133548  \n",
       "\n",
       "[4006 rows x 13 columns]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "daily_report.set_index('Combined_Key')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## `reset_index()` resets current `Index` with default `RangeIndex` "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "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>Combined_Key</th>\n",
       "      <th>FIPS</th>\n",
       "      <th>Admin2</th>\n",
       "      <th>Province_State</th>\n",
       "      <th>Country_Region</th>\n",
       "      <th>Last_Update</th>\n",
       "      <th>Lat</th>\n",
       "      <th>Long_</th>\n",
       "      <th>Confirmed</th>\n",
       "      <th>Deaths</th>\n",
       "      <th>Recovered</th>\n",
       "      <th>Active</th>\n",
       "      <th>Incident_Rate</th>\n",
       "      <th>Case_Fatality_Ratio</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>2021-12-09 04:22:49</td>\n",
       "      <td>33.939110</td>\n",
       "      <td>67.709953</td>\n",
       "      <td>157542</td>\n",
       "      <td>7317</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>404.697441</td>\n",
       "      <td>4.644476</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Albania</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Albania</td>\n",
       "      <td>2021-12-09 04:22:49</td>\n",
       "      <td>41.153300</td>\n",
       "      <td>20.168300</td>\n",
       "      <td>202641</td>\n",
       "      <td>3126</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7041.524776</td>\n",
       "      <td>1.542630</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Algeria</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Algeria</td>\n",
       "      <td>2021-12-09 04:22:49</td>\n",
       "      <td>28.033900</td>\n",
       "      <td>1.659600</td>\n",
       "      <td>212047</td>\n",
       "      <td>6122</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>483.562044</td>\n",
       "      <td>2.887096</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Andorra</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Andorra</td>\n",
       "      <td>2021-12-09 04:22:49</td>\n",
       "      <td>42.506300</td>\n",
       "      <td>1.521800</td>\n",
       "      <td>18815</td>\n",
       "      <td>133</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>24351.258655</td>\n",
       "      <td>0.706883</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Angola</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Angola</td>\n",
       "      <td>2021-12-09 04:22:49</td>\n",
       "      <td>-11.202700</td>\n",
       "      <td>17.873900</td>\n",
       "      <td>65332</td>\n",
       "      <td>1735</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>198.781316</td>\n",
       "      <td>2.655666</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>4001</th>\n",
       "      <td>Vietnam</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Vietnam</td>\n",
       "      <td>2021-12-09 04:22:49</td>\n",
       "      <td>14.058324</td>\n",
       "      <td>108.277199</td>\n",
       "      <td>1352122</td>\n",
       "      <td>26930</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1389.091518</td>\n",
       "      <td>1.991684</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4002</th>\n",
       "      <td>West Bank and Gaza</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>West Bank and Gaza</td>\n",
       "      <td>2021-12-09 04:22:49</td>\n",
       "      <td>31.952200</td>\n",
       "      <td>35.233200</td>\n",
       "      <td>462958</td>\n",
       "      <td>4823</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9075.088172</td>\n",
       "      <td>1.041779</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4003</th>\n",
       "      <td>Yemen</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yemen</td>\n",
       "      <td>2021-12-09 04:22:49</td>\n",
       "      <td>15.552727</td>\n",
       "      <td>48.516388</td>\n",
       "      <td>10047</td>\n",
       "      <td>1956</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>33.685411</td>\n",
       "      <td>19.468498</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4004</th>\n",
       "      <td>Zambia</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Zambia</td>\n",
       "      <td>2021-12-09 04:22:49</td>\n",
       "      <td>-13.133897</td>\n",
       "      <td>27.849332</td>\n",
       "      <td>210436</td>\n",
       "      <td>3668</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1144.672017</td>\n",
       "      <td>1.743048</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4005</th>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>2021-12-09 04:22:49</td>\n",
       "      <td>-19.015438</td>\n",
       "      <td>29.154857</td>\n",
       "      <td>150628</td>\n",
       "      <td>4720</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1013.447755</td>\n",
       "      <td>3.133548</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4006 rows × 14 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            Combined_Key  FIPS Admin2 Province_State      Country_Region  \\\n",
       "0            Afghanistan   NaN    NaN            NaN         Afghanistan   \n",
       "1                Albania   NaN    NaN            NaN             Albania   \n",
       "2                Algeria   NaN    NaN            NaN             Algeria   \n",
       "3                Andorra   NaN    NaN            NaN             Andorra   \n",
       "4                 Angola   NaN    NaN            NaN              Angola   \n",
       "...                  ...   ...    ...            ...                 ...   \n",
       "4001             Vietnam   NaN    NaN            NaN             Vietnam   \n",
       "4002  West Bank and Gaza   NaN    NaN            NaN  West Bank and Gaza   \n",
       "4003               Yemen   NaN    NaN            NaN               Yemen   \n",
       "4004              Zambia   NaN    NaN            NaN              Zambia   \n",
       "4005            Zimbabwe   NaN    NaN            NaN            Zimbabwe   \n",
       "\n",
       "              Last_Update        Lat       Long_  Confirmed  Deaths  \\\n",
       "0     2021-12-09 04:22:49  33.939110   67.709953     157542    7317   \n",
       "1     2021-12-09 04:22:49  41.153300   20.168300     202641    3126   \n",
       "2     2021-12-09 04:22:49  28.033900    1.659600     212047    6122   \n",
       "3     2021-12-09 04:22:49  42.506300    1.521800      18815     133   \n",
       "4     2021-12-09 04:22:49 -11.202700   17.873900      65332    1735   \n",
       "...                   ...        ...         ...        ...     ...   \n",
       "4001  2021-12-09 04:22:49  14.058324  108.277199    1352122   26930   \n",
       "4002  2021-12-09 04:22:49  31.952200   35.233200     462958    4823   \n",
       "4003  2021-12-09 04:22:49  15.552727   48.516388      10047    1956   \n",
       "4004  2021-12-09 04:22:49 -13.133897   27.849332     210436    3668   \n",
       "4005  2021-12-09 04:22:49 -19.015438   29.154857     150628    4720   \n",
       "\n",
       "      Recovered  Active  Incident_Rate  Case_Fatality_Ratio  \n",
       "0           NaN     NaN     404.697441             4.644476  \n",
       "1           NaN     NaN    7041.524776             1.542630  \n",
       "2           NaN     NaN     483.562044             2.887096  \n",
       "3           NaN     NaN   24351.258655             0.706883  \n",
       "4           NaN     NaN     198.781316             2.655666  \n",
       "...         ...     ...            ...                  ...  \n",
       "4001        NaN     NaN    1389.091518             1.991684  \n",
       "4002        NaN     NaN    9075.088172             1.041779  \n",
       "4003        NaN     NaN      33.685411            19.468498  \n",
       "4004        NaN     NaN    1144.672017             1.743048  \n",
       "4005        NaN     NaN    1013.447755             3.133548  \n",
       "\n",
       "[4006 rows x 14 columns]"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "daily_report.set_index('Combined_Key').reset_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Basic Wrangling"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Basic wrangling is like writing SQL queries\n",
    "\n",
    "- Selecting: `SELECT FROM`\n",
    "- Filtering: `WHERE`\n",
    "- Subset: `SELECT FROM WHERE`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Basic wrangling is like writing SQL queries(cont'd)\n",
    "\n",
    "- Indexing\n",
    "- Sorting: `ORDER BY`\n",
    "- Deriving\n",
    "- Summarizing\n",
    "- Summarizing and Grouping: `GROUP BY`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Selecting a column as `Series`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0              Afghanistan\n",
      "1                  Albania\n",
      "2                  Algeria\n",
      "3                  Andorra\n",
      "4                   Angola\n",
      "               ...        \n",
      "4001               Vietnam\n",
      "4002    West Bank and Gaza\n",
      "4003                 Yemen\n",
      "4004                Zambia\n",
      "4005              Zimbabwe\n",
      "Name: Country_Region, Length: 4006, dtype: object\n",
      "<class 'pandas.core.series.Series'>\n"
     ]
    }
   ],
   "source": [
    "print(daily_report['Country_Region'])\n",
    "print(type(daily_report['Country_Region']))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Selecting a column as `DataFrame`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Country_Region</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Afghanistan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Albania</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Algeria</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Andorra</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Angola</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4001</th>\n",
       "      <td>Vietnam</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4002</th>\n",
       "      <td>West Bank and Gaza</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4003</th>\n",
       "      <td>Yemen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4004</th>\n",
       "      <td>Zambia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4005</th>\n",
       "      <td>Zimbabwe</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4006 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          Country_Region\n",
       "0            Afghanistan\n",
       "1                Albania\n",
       "2                Algeria\n",
       "3                Andorra\n",
       "4                 Angola\n",
       "...                  ...\n",
       "4001             Vietnam\n",
       "4002  West Bank and Gaza\n",
       "4003               Yemen\n",
       "4004              Zambia\n",
       "4005            Zimbabwe\n",
       "\n",
       "[4006 rows x 1 columns]"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(type(daily_report[['Country_Region']]))\n",
    "daily_report[['Country_Region']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Selecting multiple columns as `DataFrame`, for sure"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Country_Region</th>\n",
       "      <th>Confirmed</th>\n",
       "      <th>Deaths</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>157542</td>\n",
       "      <td>7317</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Albania</td>\n",
       "      <td>202641</td>\n",
       "      <td>3126</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Algeria</td>\n",
       "      <td>212047</td>\n",
       "      <td>6122</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Andorra</td>\n",
       "      <td>18815</td>\n",
       "      <td>133</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Angola</td>\n",
       "      <td>65332</td>\n",
       "      <td>1735</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4001</th>\n",
       "      <td>Vietnam</td>\n",
       "      <td>1352122</td>\n",
       "      <td>26930</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4002</th>\n",
       "      <td>West Bank and Gaza</td>\n",
       "      <td>462958</td>\n",
       "      <td>4823</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4003</th>\n",
       "      <td>Yemen</td>\n",
       "      <td>10047</td>\n",
       "      <td>1956</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4004</th>\n",
       "      <td>Zambia</td>\n",
       "      <td>210436</td>\n",
       "      <td>3668</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4005</th>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>150628</td>\n",
       "      <td>4720</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4006 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          Country_Region  Confirmed  Deaths\n",
       "0            Afghanistan     157542    7317\n",
       "1                Albania     202641    3126\n",
       "2                Algeria     212047    6122\n",
       "3                Andorra      18815     133\n",
       "4                 Angola      65332    1735\n",
       "...                  ...        ...     ...\n",
       "4001             Vietnam    1352122   26930\n",
       "4002  West Bank and Gaza     462958    4823\n",
       "4003               Yemen      10047    1956\n",
       "4004              Zambia     210436    3668\n",
       "4005            Zimbabwe     150628    4720\n",
       "\n",
       "[4006 rows x 3 columns]"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cols = ['Country_Region', 'Confirmed', 'Deaths']\n",
    "daily_report[cols]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Filtering rows with conditional statements"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>FIPS</th>\n",
       "      <th>Admin2</th>\n",
       "      <th>Province_State</th>\n",
       "      <th>Country_Region</th>\n",
       "      <th>Last_Update</th>\n",
       "      <th>Lat</th>\n",
       "      <th>Long_</th>\n",
       "      <th>Confirmed</th>\n",
       "      <th>Deaths</th>\n",
       "      <th>Recovered</th>\n",
       "      <th>Active</th>\n",
       "      <th>Combined_Key</th>\n",
       "      <th>Incident_Rate</th>\n",
       "      <th>Case_Fatality_Ratio</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>663</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Taiwan*</td>\n",
       "      <td>2021-12-09 04:22:49</td>\n",
       "      <td>23.7</td>\n",
       "      <td>121.0</td>\n",
       "      <td>16688</td>\n",
       "      <td>848</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Taiwan*</td>\n",
       "      <td>70.068261</td>\n",
       "      <td>5.081496</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     FIPS Admin2 Province_State Country_Region          Last_Update   Lat  \\\n",
       "663   NaN    NaN            NaN        Taiwan*  2021-12-09 04:22:49  23.7   \n",
       "\n",
       "     Long_  Confirmed  Deaths  Recovered  Active Combined_Key  Incident_Rate  \\\n",
       "663  121.0      16688     848        NaN     NaN      Taiwan*      70.068261   \n",
       "\n",
       "     Case_Fatality_Ratio  \n",
       "663             5.081496  "
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "is_taiwan = daily_report['Country_Region'] == 'Taiwan*'\n",
    "daily_report[is_taiwan]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Subsetting columns and rows simultaneously"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "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>Country_Region</th>\n",
       "      <th>Confirmed</th>\n",
       "      <th>Deaths</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>663</th>\n",
       "      <td>Taiwan*</td>\n",
       "      <td>16688</td>\n",
       "      <td>848</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Country_Region  Confirmed  Deaths\n",
       "663        Taiwan*      16688     848"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cols_to_select = ['Country_Region', 'Confirmed', 'Deaths']\n",
    "rows_to_filter = daily_report['Country_Region'] == 'Taiwan*'\n",
    "daily_report[rows_to_filter][cols_to_select]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Indexing `DataFrame` with\n",
    "\n",
    "- `loc[]`\n",
    "- `iloc[]`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## `loc[]` is indexing `DataFrame` with `Index` "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Country_Region    Taiwan*\n",
      "Confirmed           16688\n",
      "Name: 663, dtype: object\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Country_Region</th>\n",
       "      <th>Confirmed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>663</th>\n",
       "      <td>Taiwan*</td>\n",
       "      <td>16688</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Country_Region  Confirmed\n",
       "663        Taiwan*      16688"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "taiwan_row_index = daily_report[is_taiwan].index[0]\n",
    "print(daily_report.loc[taiwan_row_index, ['Country_Region', 'Confirmed']]) # as Series\n",
    "daily_report.loc[[taiwan_row_index], ['Country_Region', 'Confirmed']] # as DataFrame"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## `iloc[]` is indexing `DataFrame` with absolute position"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Country_Region    Taiwan*\n",
      "Confirmed           16688\n",
      "Name: 663, dtype: object\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Country_Region</th>\n",
       "      <th>Confirmed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>663</th>\n",
       "      <td>Taiwan*</td>\n",
       "      <td>16688</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Country_Region  Confirmed\n",
       "663        Taiwan*      16688"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(daily_report.iloc[taiwan_row_index, [3, 7]]) # as Series\n",
    "daily_report.iloc[[taiwan_row_index], [3, 7]] # as DataFrame"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Sorting `DataFrame` with\n",
    "\n",
    "- `sort_values`\n",
    "- `sort_index`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## `sort_values` sorts `DataFrame` with specific columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>FIPS</th>\n",
       "      <th>Admin2</th>\n",
       "      <th>Province_State</th>\n",
       "      <th>Country_Region</th>\n",
       "      <th>Last_Update</th>\n",
       "      <th>Lat</th>\n",
       "      <th>Long_</th>\n",
       "      <th>Confirmed</th>\n",
       "      <th>Deaths</th>\n",
       "      <th>Recovered</th>\n",
       "      <th>Active</th>\n",
       "      <th>Combined_Key</th>\n",
       "      <th>Incident_Rate</th>\n",
       "      <th>Case_Fatality_Ratio</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>Afghanistan</td>\n",
       "      <td>2021-12-09 04:22:49</td>\n",
       "      <td>33.939110</td>\n",
       "      <td>67.709953</td>\n",
       "      <td>157542</td>\n",
       "      <td>7317</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>404.697441</td>\n",
       "      <td>4.644476</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Albania</td>\n",
       "      <td>2021-12-09 04:22:49</td>\n",
       "      <td>41.153300</td>\n",
       "      <td>20.168300</td>\n",
       "      <td>202641</td>\n",
       "      <td>3126</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Albania</td>\n",
       "      <td>7041.524776</td>\n",
       "      <td>1.542630</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Algeria</td>\n",
       "      <td>2021-12-09 04:22:49</td>\n",
       "      <td>28.033900</td>\n",
       "      <td>1.659600</td>\n",
       "      <td>212047</td>\n",
       "      <td>6122</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Algeria</td>\n",
       "      <td>483.562044</td>\n",
       "      <td>2.887096</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Andorra</td>\n",
       "      <td>2021-12-09 04:22:49</td>\n",
       "      <td>42.506300</td>\n",
       "      <td>1.521800</td>\n",
       "      <td>18815</td>\n",
       "      <td>133</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Andorra</td>\n",
       "      <td>24351.258655</td>\n",
       "      <td>0.706883</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Angola</td>\n",
       "      <td>2021-12-09 04:22:49</td>\n",
       "      <td>-11.202700</td>\n",
       "      <td>17.873900</td>\n",
       "      <td>65332</td>\n",
       "      <td>1735</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Angola</td>\n",
       "      <td>198.781316</td>\n",
       "      <td>2.655666</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>4001</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Vietnam</td>\n",
       "      <td>2021-12-09 04:22:49</td>\n",
       "      <td>14.058324</td>\n",
       "      <td>108.277199</td>\n",
       "      <td>1352122</td>\n",
       "      <td>26930</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Vietnam</td>\n",
       "      <td>1389.091518</td>\n",
       "      <td>1.991684</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4002</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>West Bank and Gaza</td>\n",
       "      <td>2021-12-09 04:22:49</td>\n",
       "      <td>31.952200</td>\n",
       "      <td>35.233200</td>\n",
       "      <td>462958</td>\n",
       "      <td>4823</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>West Bank and Gaza</td>\n",
       "      <td>9075.088172</td>\n",
       "      <td>1.041779</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4003</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yemen</td>\n",
       "      <td>2021-12-09 04:22:49</td>\n",
       "      <td>15.552727</td>\n",
       "      <td>48.516388</td>\n",
       "      <td>10047</td>\n",
       "      <td>1956</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yemen</td>\n",
       "      <td>33.685411</td>\n",
       "      <td>19.468498</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4004</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Zambia</td>\n",
       "      <td>2021-12-09 04:22:49</td>\n",
       "      <td>-13.133897</td>\n",
       "      <td>27.849332</td>\n",
       "      <td>210436</td>\n",
       "      <td>3668</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Zambia</td>\n",
       "      <td>1144.672017</td>\n",
       "      <td>1.743048</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4005</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>2021-12-09 04:22:49</td>\n",
       "      <td>-19.015438</td>\n",
       "      <td>29.154857</td>\n",
       "      <td>150628</td>\n",
       "      <td>4720</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>1013.447755</td>\n",
       "      <td>3.133548</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4006 rows × 14 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      FIPS Admin2 Province_State      Country_Region          Last_Update  \\\n",
       "0      NaN    NaN            NaN         Afghanistan  2021-12-09 04:22:49   \n",
       "1      NaN    NaN            NaN             Albania  2021-12-09 04:22:49   \n",
       "2      NaN    NaN            NaN             Algeria  2021-12-09 04:22:49   \n",
       "3      NaN    NaN            NaN             Andorra  2021-12-09 04:22:49   \n",
       "4      NaN    NaN            NaN              Angola  2021-12-09 04:22:49   \n",
       "...    ...    ...            ...                 ...                  ...   \n",
       "4001   NaN    NaN            NaN             Vietnam  2021-12-09 04:22:49   \n",
       "4002   NaN    NaN            NaN  West Bank and Gaza  2021-12-09 04:22:49   \n",
       "4003   NaN    NaN            NaN               Yemen  2021-12-09 04:22:49   \n",
       "4004   NaN    NaN            NaN              Zambia  2021-12-09 04:22:49   \n",
       "4005   NaN    NaN            NaN            Zimbabwe  2021-12-09 04:22:49   \n",
       "\n",
       "            Lat       Long_  Confirmed  Deaths  Recovered  Active  \\\n",
       "0     33.939110   67.709953     157542    7317        NaN     NaN   \n",
       "1     41.153300   20.168300     202641    3126        NaN     NaN   \n",
       "2     28.033900    1.659600     212047    6122        NaN     NaN   \n",
       "3     42.506300    1.521800      18815     133        NaN     NaN   \n",
       "4    -11.202700   17.873900      65332    1735        NaN     NaN   \n",
       "...         ...         ...        ...     ...        ...     ...   \n",
       "4001  14.058324  108.277199    1352122   26930        NaN     NaN   \n",
       "4002  31.952200   35.233200     462958    4823        NaN     NaN   \n",
       "4003  15.552727   48.516388      10047    1956        NaN     NaN   \n",
       "4004 -13.133897   27.849332     210436    3668        NaN     NaN   \n",
       "4005 -19.015438   29.154857     150628    4720        NaN     NaN   \n",
       "\n",
       "            Combined_Key  Incident_Rate  Case_Fatality_Ratio  \n",
       "0            Afghanistan     404.697441             4.644476  \n",
       "1                Albania    7041.524776             1.542630  \n",
       "2                Algeria     483.562044             2.887096  \n",
       "3                Andorra   24351.258655             0.706883  \n",
       "4                 Angola     198.781316             2.655666  \n",
       "...                  ...            ...                  ...  \n",
       "4001             Vietnam    1389.091518             1.991684  \n",
       "4002  West Bank and Gaza    9075.088172             1.041779  \n",
       "4003               Yemen      33.685411            19.468498  \n",
       "4004              Zambia    1144.672017             1.743048  \n",
       "4005            Zimbabwe    1013.447755             3.133548  \n",
       "\n",
       "[4006 rows x 14 columns]"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "daily_report.sort_values(['Country_Region', 'Confirmed'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## `sort_index` sorts `DataFrame` with the `Index` of `DataFrame`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "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>FIPS</th>\n",
       "      <th>Admin2</th>\n",
       "      <th>Province_State</th>\n",
       "      <th>Country_Region</th>\n",
       "      <th>Last_Update</th>\n",
       "      <th>Lat</th>\n",
       "      <th>Long_</th>\n",
       "      <th>Confirmed</th>\n",
       "      <th>Deaths</th>\n",
       "      <th>Recovered</th>\n",
       "      <th>Active</th>\n",
       "      <th>Combined_Key</th>\n",
       "      <th>Incident_Rate</th>\n",
       "      <th>Case_Fatality_Ratio</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4005</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>2021-12-09 04:22:49</td>\n",
       "      <td>-19.015438</td>\n",
       "      <td>29.154857</td>\n",
       "      <td>150628</td>\n",
       "      <td>4720</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>1013.447755</td>\n",
       "      <td>3.133548</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4004</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Zambia</td>\n",
       "      <td>2021-12-09 04:22:49</td>\n",
       "      <td>-13.133897</td>\n",
       "      <td>27.849332</td>\n",
       "      <td>210436</td>\n",
       "      <td>3668</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Zambia</td>\n",
       "      <td>1144.672017</td>\n",
       "      <td>1.743048</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4003</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yemen</td>\n",
       "      <td>2021-12-09 04:22:49</td>\n",
       "      <td>15.552727</td>\n",
       "      <td>48.516388</td>\n",
       "      <td>10047</td>\n",
       "      <td>1956</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yemen</td>\n",
       "      <td>33.685411</td>\n",
       "      <td>19.468498</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4002</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>West Bank and Gaza</td>\n",
       "      <td>2021-12-09 04:22:49</td>\n",
       "      <td>31.952200</td>\n",
       "      <td>35.233200</td>\n",
       "      <td>462958</td>\n",
       "      <td>4823</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>West Bank and Gaza</td>\n",
       "      <td>9075.088172</td>\n",
       "      <td>1.041779</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4001</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Vietnam</td>\n",
       "      <td>2021-12-09 04:22:49</td>\n",
       "      <td>14.058324</td>\n",
       "      <td>108.277199</td>\n",
       "      <td>1352122</td>\n",
       "      <td>26930</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Vietnam</td>\n",
       "      <td>1389.091518</td>\n",
       "      <td>1.991684</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>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Angola</td>\n",
       "      <td>2021-12-09 04:22:49</td>\n",
       "      <td>-11.202700</td>\n",
       "      <td>17.873900</td>\n",
       "      <td>65332</td>\n",
       "      <td>1735</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Angola</td>\n",
       "      <td>198.781316</td>\n",
       "      <td>2.655666</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Andorra</td>\n",
       "      <td>2021-12-09 04:22:49</td>\n",
       "      <td>42.506300</td>\n",
       "      <td>1.521800</td>\n",
       "      <td>18815</td>\n",
       "      <td>133</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Andorra</td>\n",
       "      <td>24351.258655</td>\n",
       "      <td>0.706883</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Algeria</td>\n",
       "      <td>2021-12-09 04:22:49</td>\n",
       "      <td>28.033900</td>\n",
       "      <td>1.659600</td>\n",
       "      <td>212047</td>\n",
       "      <td>6122</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Algeria</td>\n",
       "      <td>483.562044</td>\n",
       "      <td>2.887096</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Albania</td>\n",
       "      <td>2021-12-09 04:22:49</td>\n",
       "      <td>41.153300</td>\n",
       "      <td>20.168300</td>\n",
       "      <td>202641</td>\n",
       "      <td>3126</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Albania</td>\n",
       "      <td>7041.524776</td>\n",
       "      <td>1.542630</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>2021-12-09 04:22:49</td>\n",
       "      <td>33.939110</td>\n",
       "      <td>67.709953</td>\n",
       "      <td>157542</td>\n",
       "      <td>7317</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>404.697441</td>\n",
       "      <td>4.644476</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4006 rows × 14 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      FIPS Admin2 Province_State      Country_Region          Last_Update  \\\n",
       "4005   NaN    NaN            NaN            Zimbabwe  2021-12-09 04:22:49   \n",
       "4004   NaN    NaN            NaN              Zambia  2021-12-09 04:22:49   \n",
       "4003   NaN    NaN            NaN               Yemen  2021-12-09 04:22:49   \n",
       "4002   NaN    NaN            NaN  West Bank and Gaza  2021-12-09 04:22:49   \n",
       "4001   NaN    NaN            NaN             Vietnam  2021-12-09 04:22:49   \n",
       "...    ...    ...            ...                 ...                  ...   \n",
       "4      NaN    NaN            NaN              Angola  2021-12-09 04:22:49   \n",
       "3      NaN    NaN            NaN             Andorra  2021-12-09 04:22:49   \n",
       "2      NaN    NaN            NaN             Algeria  2021-12-09 04:22:49   \n",
       "1      NaN    NaN            NaN             Albania  2021-12-09 04:22:49   \n",
       "0      NaN    NaN            NaN         Afghanistan  2021-12-09 04:22:49   \n",
       "\n",
       "            Lat       Long_  Confirmed  Deaths  Recovered  Active  \\\n",
       "4005 -19.015438   29.154857     150628    4720        NaN     NaN   \n",
       "4004 -13.133897   27.849332     210436    3668        NaN     NaN   \n",
       "4003  15.552727   48.516388      10047    1956        NaN     NaN   \n",
       "4002  31.952200   35.233200     462958    4823        NaN     NaN   \n",
       "4001  14.058324  108.277199    1352122   26930        NaN     NaN   \n",
       "...         ...         ...        ...     ...        ...     ...   \n",
       "4    -11.202700   17.873900      65332    1735        NaN     NaN   \n",
       "3     42.506300    1.521800      18815     133        NaN     NaN   \n",
       "2     28.033900    1.659600     212047    6122        NaN     NaN   \n",
       "1     41.153300   20.168300     202641    3126        NaN     NaN   \n",
       "0     33.939110   67.709953     157542    7317        NaN     NaN   \n",
       "\n",
       "            Combined_Key  Incident_Rate  Case_Fatality_Ratio  \n",
       "4005            Zimbabwe    1013.447755             3.133548  \n",
       "4004              Zambia    1144.672017             1.743048  \n",
       "4003               Yemen      33.685411            19.468498  \n",
       "4002  West Bank and Gaza    9075.088172             1.041779  \n",
       "4001             Vietnam    1389.091518             1.991684  \n",
       "...                  ...            ...                  ...  \n",
       "4                 Angola     198.781316             2.655666  \n",
       "3                Andorra   24351.258655             0.706883  \n",
       "2                Algeria     483.562044             2.887096  \n",
       "1                Albania    7041.524776             1.542630  \n",
       "0            Afghanistan     404.697441             4.644476  \n",
       "\n",
       "[4006 rows x 14 columns]"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "daily_report.sort_index(ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Deriving new variables from `DataFrame`\n",
    "\n",
    "- Simple operations\n",
    "- `map` with a `dict`\n",
    "- `map` with a function(or a lambda expression)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Deriving new variable with simple operations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0       0.046445\n",
       "1       0.015426\n",
       "2       0.028871\n",
       "3       0.007069\n",
       "4       0.026557\n",
       "          ...   \n",
       "4001    0.019917\n",
       "4002    0.010418\n",
       "4003    0.194685\n",
       "4004    0.017430\n",
       "4005    0.031335\n",
       "Length: 4006, dtype: float64"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "case_fatality_ratio = daily_report['Deaths'] / daily_report['Confirmed']\n",
    "case_fatality_ratio"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Deriving categorical from categorical with `map`\n",
    "\n",
    "- Passing a `dict`\n",
    "- Passing a function(or lambda expression)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "663    Taiwan\n",
       "Name: Country_Region, dtype: object"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Passing a dict\n",
    "country_name = {\n",
    "    'Taiwan*': 'Taiwan'\n",
    "}\n",
    "daily_report_tw = daily_report[is_taiwan]\n",
    "daily_report_tw['Country_Region'].map(country_name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Taiwan'"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Passing a function\n",
    "def replace_asterisk(x):\n",
    "    if \"*\" in x:\n",
    "        return x.replace(\"*\", \"\")\n",
    "    else:\n",
    "        return x\n",
    "\n",
    "daily_report['Country_Region'].map(replace_asterisk)[taiwan_row_index]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Taiwan'"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Passing a lambda expression)\n",
    "daily_report['Country_Region'].map(lambda x: x.replace(\"*\", \"\") if \"*\" in x else x)[taiwan_row_index]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Summarizing `DataFrame` with aggregate methods"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "267829762"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "daily_report['Confirmed'].sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Summarizing and grouping `DataFrame` with aggregate methods"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Country_Region\n",
       "Afghanistan            157542\n",
       "Albania                202641\n",
       "Algeria                212047\n",
       "Andorra                 18815\n",
       "Angola                  65332\n",
       "                       ...   \n",
       "Vietnam               1352122\n",
       "West Bank and Gaza     462958\n",
       "Yemen                   10047\n",
       "Zambia                 210436\n",
       "Zimbabwe               150628\n",
       "Name: Confirmed, Length: 196, dtype: int64"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "daily_report.groupby('Country_Region')['Confirmed'].sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Advanced Wrangling"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Other common wranglings 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": 49,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "4006\n",
      "174\n",
      "3832\n"
     ]
    }
   ],
   "source": [
    "print(daily_report['Province_State'].size)\n",
    "print(daily_report['Province_State'].isnull().sum())\n",
    "print(daily_report['Province_State'].notnull().sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(0, 14)\n",
      "0       0.0\n",
      "1       0.0\n",
      "2       0.0\n",
      "3       0.0\n",
      "4       0.0\n",
      "       ... \n",
      "4001    0.0\n",
      "4002    0.0\n",
      "4003    0.0\n",
      "4004    0.0\n",
      "4005    0.0\n",
      "Name: FIPS, Length: 4006, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "print(daily_report.dropna().shape)\n",
    "print(daily_report['FIPS'].fillna(0))"
   ]
  },
  {
   "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": 51,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "196\n",
      "['Afghanistan' 'Albania' 'Algeria' 'Andorra' 'Angola'\n",
      " 'Antigua and Barbuda' 'Argentina' 'Armenia' 'Australia' 'Austria'\n",
      " 'Azerbaijan' 'Bahamas' 'Bahrain' 'Bangladesh' 'Barbados' 'Belarus'\n",
      " 'Belgium' 'Belize' 'Benin' 'Bhutan' 'Bolivia' 'Bosnia and Herzegovina'\n",
      " 'Botswana' 'Brazil' 'Brunei' 'Bulgaria' 'Burkina Faso' 'Burma' 'Burundi'\n",
      " 'Cabo Verde' 'Cambodia' 'Cameroon' 'Canada' 'Central African Republic'\n",
      " 'Chad' 'Chile' 'China' 'Colombia' 'Comoros' 'Congo (Brazzaville)'\n",
      " 'Congo (Kinshasa)' 'Costa Rica' \"Cote d'Ivoire\" 'Croatia' 'Cuba' 'Cyprus'\n",
      " 'Czechia' 'Denmark' 'Diamond Princess' 'Djibouti' 'Dominica'\n",
      " 'Dominican Republic' 'Ecuador' 'Egypt' 'El Salvador' 'Equatorial Guinea'\n",
      " 'Eritrea' 'Estonia' 'Eswatini' 'Ethiopia' 'Fiji' 'Finland' 'France'\n",
      " 'Gabon' 'Gambia' 'Georgia' 'Germany' 'Ghana' 'Greece' 'Grenada'\n",
      " 'Guatemala' 'Guinea' 'Guinea-Bissau' 'Guyana' 'Haiti' 'Holy See'\n",
      " 'Honduras' 'Hungary' 'Iceland' 'India' 'Indonesia' 'Iran' 'Iraq'\n",
      " 'Ireland' 'Israel' 'Italy' 'Jamaica' 'Japan' 'Jordan' 'Kazakhstan'\n",
      " 'Kenya' 'Kiribati' 'Korea, South' 'Kosovo' 'Kuwait' 'Kyrgyzstan' 'Laos'\n",
      " 'Latvia' 'Lebanon' 'Lesotho' 'Liberia' 'Libya' 'Liechtenstein'\n",
      " 'Lithuania' 'Luxembourg' 'MS Zaandam' 'Madagascar' 'Malawi' 'Malaysia'\n",
      " 'Maldives' 'Mali' 'Malta' 'Marshall Islands' 'Mauritania' 'Mauritius'\n",
      " 'Mexico' 'Micronesia' 'Moldova' 'Monaco' 'Mongolia' 'Montenegro'\n",
      " 'Morocco' 'Mozambique' 'Namibia' 'Nepal' 'Netherlands' 'New Zealand'\n",
      " 'Nicaragua' 'Niger' 'Nigeria' 'North Macedonia' 'Norway' 'Oman'\n",
      " 'Pakistan' 'Palau' 'Panama' 'Papua New Guinea' 'Paraguay' 'Peru'\n",
      " 'Philippines' 'Poland' 'Portugal' 'Qatar' 'Romania' 'Russia' 'Rwanda'\n",
      " 'Saint Kitts and Nevis' 'Saint Lucia' 'Saint Vincent and the Grenadines'\n",
      " 'Samoa' 'San Marino' 'Sao Tome and Principe' 'Saudi Arabia' 'Senegal'\n",
      " 'Serbia' 'Seychelles' 'Sierra Leone' 'Singapore' 'Slovakia' 'Slovenia'\n",
      " 'Solomon Islands' 'Somalia' 'South Africa' 'South Sudan' 'Spain'\n",
      " 'Sri Lanka' 'Sudan' 'Summer Olympics 2020' 'Suriname' 'Sweden'\n",
      " 'Switzerland' 'Syria' 'Taiwan*' 'Tajikistan' 'Tanzania' 'Thailand'\n",
      " 'Timor-Leste' 'Togo' 'Tonga' 'Trinidad and Tobago' 'Tunisia' 'Turkey'\n",
      " 'US' 'Uganda' 'Ukraine' 'United Arab Emirates' 'United Kingdom' 'Uruguay'\n",
      " 'Uzbekistan' 'Vanuatu' 'Venezuela' 'Vietnam' 'West Bank and Gaza' 'Yemen'\n",
      " 'Zambia' 'Zimbabwe']\n"
     ]
    }
   ],
   "source": [
    "print(daily_report['Country_Region'].nunique())\n",
    "print(daily_report['Country_Region'].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "US                       3279\n",
       "Russia                     83\n",
       "Japan                      49\n",
       "India                      37\n",
       "Colombia                   34\n",
       "                         ... \n",
       "Guinea-Bissau               1\n",
       "Armenia                     1\n",
       "Sao Tome and Principe       1\n",
       "Senegal                     1\n",
       "Mauritius                   1\n",
       "Name: Country_Region, Length: 196, dtype: int64"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "daily_report['Country_Region'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Splitting strings with `str.split` as a `Series`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "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>Unnamed: 0</th>\n",
       "      <th>Rank &amp; Title</th>\n",
       "      <th>IMDb Rating</th>\n",
       "      <th>Your Rating</th>\n",
       "      <th>Unnamed: 4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>1.  刺激1995  (1994)</td>\n",
       "      <td>9.2</td>\n",
       "      <td>12345678910 NOT YET RELEASED  Seen</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>2.  教父  (1972)</td>\n",
       "      <td>9.1</td>\n",
       "      <td>12345678910 NOT YET RELEASED  Seen</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>3.  教父第二集  (1974)</td>\n",
       "      <td>9.0</td>\n",
       "      <td>12345678910 NOT YET RELEASED  Seen</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>4.  黑暗騎士  (2008)</td>\n",
       "      <td>9.0</td>\n",
       "      <td>12345678910 NOT YET RELEASED  Seen</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>5.  十二怒漢  (1957)</td>\n",
       "      <td>8.9</td>\n",
       "      <td>12345678910 NOT YET RELEASED  Seen</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0        Rank & Title  IMDb Rating  \\\n",
       "0         NaN  1.  刺激1995  (1994)          9.2   \n",
       "1         NaN      2.  教父  (1972)          9.1   \n",
       "2         NaN   3.  教父第二集  (1974)          9.0   \n",
       "3         NaN    4.  黑暗騎士  (2008)          9.0   \n",
       "4         NaN    5.  十二怒漢  (1957)          8.9   \n",
       "\n",
       "                          Your Rating  Unnamed: 4  \n",
       "0  12345678910 NOT YET RELEASED  Seen         NaN  \n",
       "1  12345678910 NOT YET RELEASED  Seen         NaN  \n",
       "2  12345678910 NOT YET RELEASED  Seen         NaN  \n",
       "3  12345678910 NOT YET RELEASED  Seen         NaN  \n",
       "4  12345678910 NOT YET RELEASED  Seen         NaN  "
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "request_url = \"https://www.imdb.com/chart/top\"\n",
    "html_tables = pd.read_html(request_url)\n",
    "html_table = html_tables[0]\n",
    "html_table.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0          [1., 刺激1995, (1994)]\n",
       "1              [2., 教父, (1972)]\n",
       "2           [3., 教父第二集, (1974)]\n",
       "3            [4., 黑暗騎士, (2008)]\n",
       "4            [5., 十二怒漢, (1957)]\n",
       "                 ...           \n",
       "245          [246., 96, (2018)]\n",
       "246      [247., 卡比莉亞之夜, (1957)]\n",
       "247        [248., 公主新娘, (1987)]\n",
       "248       [249., 巴黎，德州, (1984)]\n",
       "249    [250., Ratsasan, (2018)]\n",
       "Name: Rank & Title, Length: 250, dtype: object"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "html_table['Rank & Title'].str.split()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Splitting strings with `str.split` as a `DataFrame`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "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>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.</td>\n",
       "      <td>刺激1995</td>\n",
       "      <td>(1994)</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2.</td>\n",
       "      <td>教父</td>\n",
       "      <td>(1972)</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3.</td>\n",
       "      <td>教父第二集</td>\n",
       "      <td>(1974)</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4.</td>\n",
       "      <td>黑暗騎士</td>\n",
       "      <td>(2008)</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5.</td>\n",
       "      <td>十二怒漢</td>\n",
       "      <td>(1957)</td>\n",
       "      <td>None</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>245</th>\n",
       "      <td>246.</td>\n",
       "      <td>96</td>\n",
       "      <td>(2018)</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>246</th>\n",
       "      <td>247.</td>\n",
       "      <td>卡比莉亞之夜</td>\n",
       "      <td>(1957)</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>247</th>\n",
       "      <td>248.</td>\n",
       "      <td>公主新娘</td>\n",
       "      <td>(1987)</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>248</th>\n",
       "      <td>249.</td>\n",
       "      <td>巴黎，德州</td>\n",
       "      <td>(1984)</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>249</th>\n",
       "      <td>250.</td>\n",
       "      <td>Ratsasan</td>\n",
       "      <td>(2018)</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>250 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        0         1       2     3\n",
       "0      1.    刺激1995  (1994)  None\n",
       "1      2.        教父  (1972)  None\n",
       "2      3.     教父第二集  (1974)  None\n",
       "3      4.      黑暗騎士  (2008)  None\n",
       "4      5.      十二怒漢  (1957)  None\n",
       "..    ...       ...     ...   ...\n",
       "245  246.        96  (2018)  None\n",
       "246  247.    卡比莉亞之夜  (1957)  None\n",
       "247  248.      公主新娘  (1987)  None\n",
       "248  249.     巴黎，德州  (1984)  None\n",
       "249  250.  Ratsasan  (2018)  None\n",
       "\n",
       "[250 rows x 4 columns]"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "html_table['Rank & Title'].str.split(expand=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Replacing strings with `str.replace`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0        1\n",
       "1        2\n",
       "2        3\n",
       "3        4\n",
       "4        5\n",
       "      ... \n",
       "245    246\n",
       "246    247\n",
       "247    248\n",
       "248    249\n",
       "249    250\n",
       "Name: 0, Length: 250, dtype: object"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "split_dataframe = html_table['Rank & Title'].str.split(expand=True)\n",
    "ranks = split_dataframe[0].str.replace(\".\", '', regex=False)\n",
    "ranks"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0      1994\n",
       "1      1972\n",
       "2      1974\n",
       "3      2008\n",
       "4      1957\n",
       "       ... \n",
       "245    2018\n",
       "246    1957\n",
       "247    1987\n",
       "248    1984\n",
       "249    2018\n",
       "Name: 2, Length: 250, dtype: object"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "split_dataframe = html_table['Rank & Title'].str.split(expand=True)\n",
    "years = split_dataframe[2].str.replace(\"\\(|\\)\", '', regex=True)\n",
    "years"
   ]
  },
  {
   "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": 58,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "3\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>Rank &amp; Title</th>\n",
       "      <th>IMDb Rating</th>\n",
       "      <th>Your Rating</th>\n",
       "      <th>Unnamed: 4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>NaN</td>\n",
       "      <td>7.  魔戒三部曲：王者再臨  (2003)</td>\n",
       "      <td>8.9</td>\n",
       "      <td>12345678910 NOT YET RELEASED  Seen</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>NaN</td>\n",
       "      <td>10.  魔戒首部曲：魔戒現身  (2001)</td>\n",
       "      <td>8.8</td>\n",
       "      <td>12345678910 NOT YET RELEASED  Seen</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>NaN</td>\n",
       "      <td>14.  魔戒二部曲：雙城奇謀  (2002)</td>\n",
       "      <td>8.7</td>\n",
       "      <td>12345678910 NOT YET RELEASED  Seen</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Unnamed: 0             Rank & Title  IMDb Rating  \\\n",
       "6          NaN   7.  魔戒三部曲：王者再臨  (2003)          8.9   \n",
       "9          NaN  10.  魔戒首部曲：魔戒現身  (2001)          8.8   \n",
       "13         NaN  14.  魔戒二部曲：雙城奇謀  (2002)          8.7   \n",
       "\n",
       "                           Your Rating  Unnamed: 4  \n",
       "6   12345678910 NOT YET RELEASED  Seen         NaN  \n",
       "9   12345678910 NOT YET RELEASED  Seen         NaN  \n",
       "13  12345678910 NOT YET RELEASED  Seen         NaN  "
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(html_table['Rank & Title'].str.contains('魔戒').sum())\n",
    "html_table[html_table['Rank & Title'].str.contains('魔戒')]"
   ]
  },
  {
   "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": 59,
   "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": 59,
     "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": 60,
   "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": 60,
     "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": 61,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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": 61,
     "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": 62,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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": 62,
     "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": 63,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Province/State</th>\n",
       "      <th>Country/Region</th>\n",
       "      <th>Lat</th>\n",
       "      <th>Long</th>\n",
       "      <th>1/22/20</th>\n",
       "      <th>1/23/20</th>\n",
       "      <th>1/24/20</th>\n",
       "      <th>1/25/20</th>\n",
       "      <th>1/26/20</th>\n",
       "      <th>1/27/20</th>\n",
       "      <th>...</th>\n",
       "      <th>11/29/21</th>\n",
       "      <th>11/30/21</th>\n",
       "      <th>12/1/21</th>\n",
       "      <th>12/2/21</th>\n",
       "      <th>12/3/21</th>\n",
       "      <th>12/4/21</th>\n",
       "      <th>12/5/21</th>\n",
       "      <th>12/6/21</th>\n",
       "      <th>12/7/21</th>\n",
       "      <th>12/8/21</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>33.939110</td>\n",
       "      <td>67.709953</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>157260</td>\n",
       "      <td>157289</td>\n",
       "      <td>157359</td>\n",
       "      <td>157387</td>\n",
       "      <td>157412</td>\n",
       "      <td>157431</td>\n",
       "      <td>157445</td>\n",
       "      <td>157499</td>\n",
       "      <td>157508</td>\n",
       "      <td>157542</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Albania</td>\n",
       "      <td>41.153300</td>\n",
       "      <td>20.168300</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>199750</td>\n",
       "      <td>199945</td>\n",
       "      <td>200173</td>\n",
       "      <td>200639</td>\n",
       "      <td>201045</td>\n",
       "      <td>201402</td>\n",
       "      <td>201730</td>\n",
       "      <td>201902</td>\n",
       "      <td>202295</td>\n",
       "      <td>202641</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Algeria</td>\n",
       "      <td>28.033900</td>\n",
       "      <td>1.659600</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>210344</td>\n",
       "      <td>210531</td>\n",
       "      <td>210723</td>\n",
       "      <td>210921</td>\n",
       "      <td>211112</td>\n",
       "      <td>211297</td>\n",
       "      <td>211469</td>\n",
       "      <td>211662</td>\n",
       "      <td>211859</td>\n",
       "      <td>212047</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Andorra</td>\n",
       "      <td>42.506300</td>\n",
       "      <td>1.521800</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>16712</td>\n",
       "      <td>17115</td>\n",
       "      <td>17426</td>\n",
       "      <td>17658</td>\n",
       "      <td>18010</td>\n",
       "      <td>18010</td>\n",
       "      <td>18010</td>\n",
       "      <td>18631</td>\n",
       "      <td>18815</td>\n",
       "      <td>18815</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Angola</td>\n",
       "      <td>-11.202700</td>\n",
       "      <td>17.873900</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>65155</td>\n",
       "      <td>65168</td>\n",
       "      <td>65183</td>\n",
       "      <td>65208</td>\n",
       "      <td>65223</td>\n",
       "      <td>65244</td>\n",
       "      <td>65259</td>\n",
       "      <td>65259</td>\n",
       "      <td>65301</td>\n",
       "      <td>65332</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",
       "      <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>275</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Vietnam</td>\n",
       "      <td>14.058324</td>\n",
       "      <td>108.277199</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>1224110</td>\n",
       "      <td>1238082</td>\n",
       "      <td>1252590</td>\n",
       "      <td>1266288</td>\n",
       "      <td>1280780</td>\n",
       "      <td>1294778</td>\n",
       "      <td>1309092</td>\n",
       "      <td>1323683</td>\n",
       "      <td>1337523</td>\n",
       "      <td>1352122</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>276</th>\n",
       "      <td>NaN</td>\n",
       "      <td>West Bank and Gaza</td>\n",
       "      <td>31.952200</td>\n",
       "      <td>35.233200</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>460053</td>\n",
       "      <td>460053</td>\n",
       "      <td>460799</td>\n",
       "      <td>461157</td>\n",
       "      <td>461467</td>\n",
       "      <td>461467</td>\n",
       "      <td>461467</td>\n",
       "      <td>462219</td>\n",
       "      <td>462621</td>\n",
       "      <td>462958</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>277</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Yemen</td>\n",
       "      <td>15.552727</td>\n",
       "      <td>48.516388</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>9995</td>\n",
       "      <td>10004</td>\n",
       "      <td>10006</td>\n",
       "      <td>10006</td>\n",
       "      <td>10020</td>\n",
       "      <td>10021</td>\n",
       "      <td>10025</td>\n",
       "      <td>10034</td>\n",
       "      <td>10043</td>\n",
       "      <td>10047</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>278</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Zambia</td>\n",
       "      <td>-13.133897</td>\n",
       "      <td>27.849332</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>210150</td>\n",
       "      <td>210169</td>\n",
       "      <td>210195</td>\n",
       "      <td>210220</td>\n",
       "      <td>210262</td>\n",
       "      <td>210294</td>\n",
       "      <td>210312</td>\n",
       "      <td>210327</td>\n",
       "      <td>210374</td>\n",
       "      <td>210436</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>279</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>-19.015438</td>\n",
       "      <td>29.154857</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>134226</td>\n",
       "      <td>134625</td>\n",
       "      <td>135337</td>\n",
       "      <td>136379</td>\n",
       "      <td>137441</td>\n",
       "      <td>138523</td>\n",
       "      <td>139046</td>\n",
       "      <td>139046</td>\n",
       "      <td>141601</td>\n",
       "      <td>150628</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>280 rows × 691 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    Province/State      Country/Region        Lat        Long  1/22/20  \\\n",
       "0              NaN         Afghanistan  33.939110   67.709953        0   \n",
       "1              NaN             Albania  41.153300   20.168300        0   \n",
       "2              NaN             Algeria  28.033900    1.659600        0   \n",
       "3              NaN             Andorra  42.506300    1.521800        0   \n",
       "4              NaN              Angola -11.202700   17.873900        0   \n",
       "..             ...                 ...        ...         ...      ...   \n",
       "275            NaN             Vietnam  14.058324  108.277199        0   \n",
       "276            NaN  West Bank and Gaza  31.952200   35.233200        0   \n",
       "277            NaN               Yemen  15.552727   48.516388        0   \n",
       "278            NaN              Zambia -13.133897   27.849332        0   \n",
       "279            NaN            Zimbabwe -19.015438   29.154857        0   \n",
       "\n",
       "     1/23/20  1/24/20  1/25/20  1/26/20  1/27/20  ...  11/29/21  11/30/21  \\\n",
       "0          0        0        0        0        0  ...    157260    157289   \n",
       "1          0        0        0        0        0  ...    199750    199945   \n",
       "2          0        0        0        0        0  ...    210344    210531   \n",
       "3          0        0        0        0        0  ...     16712     17115   \n",
       "4          0        0        0        0        0  ...     65155     65168   \n",
       "..       ...      ...      ...      ...      ...  ...       ...       ...   \n",
       "275        2        2        2        2        2  ...   1224110   1238082   \n",
       "276        0        0        0        0        0  ...    460053    460053   \n",
       "277        0        0        0        0        0  ...      9995     10004   \n",
       "278        0        0        0        0        0  ...    210150    210169   \n",
       "279        0        0        0        0        0  ...    134226    134625   \n",
       "\n",
       "     12/1/21  12/2/21  12/3/21  12/4/21  12/5/21  12/6/21  12/7/21  12/8/21  \n",
       "0     157359   157387   157412   157431   157445   157499   157508   157542  \n",
       "1     200173   200639   201045   201402   201730   201902   202295   202641  \n",
       "2     210723   210921   211112   211297   211469   211662   211859   212047  \n",
       "3      17426    17658    18010    18010    18010    18631    18815    18815  \n",
       "4      65183    65208    65223    65244    65259    65259    65301    65332  \n",
       "..       ...      ...      ...      ...      ...      ...      ...      ...  \n",
       "275  1252590  1266288  1280780  1294778  1309092  1323683  1337523  1352122  \n",
       "276   460799   461157   461467   461467   461467   462219   462621   462958  \n",
       "277    10006    10006    10020    10021    10025    10034    10043    10047  \n",
       "278   210195   210220   210262   210294   210312   210327   210374   210436  \n",
       "279   135337   136379   137441   138523   139046   139046   141601   150628  \n",
       "\n",
       "[280 rows x 691 columns]"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ts_confirmed_global_url = \"https://raw.githubusercontent.com/CSSEGISandData\\\n",
    "/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series\\\n",
    "/time_series_covid19_confirmed_global.csv\"\n",
    "ts_confirmed_global = pd.read_csv(ts_confirmed_global_url)\n",
    "ts_confirmed_global"
   ]
  },
  {
   "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`: `['Province/State', 'Country/Region', 'Lat', 'Long']`\n",
    "- `value_vars`: The columns from `1/22/20` to the last column\n",
    "- `var_name`: Let's name it `Date`\n",
    "- `value_name`: Let's name it `Confirmed`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "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>Province/State</th>\n",
       "      <th>Country/Region</th>\n",
       "      <th>Lat</th>\n",
       "      <th>Long</th>\n",
       "      <th>Date</th>\n",
       "      <th>Confirmed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>33.939110</td>\n",
       "      <td>67.709953</td>\n",
       "      <td>1/22/20</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Albania</td>\n",
       "      <td>41.153300</td>\n",
       "      <td>20.168300</td>\n",
       "      <td>1/22/20</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Algeria</td>\n",
       "      <td>28.033900</td>\n",
       "      <td>1.659600</td>\n",
       "      <td>1/22/20</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Andorra</td>\n",
       "      <td>42.506300</td>\n",
       "      <td>1.521800</td>\n",
       "      <td>1/22/20</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Angola</td>\n",
       "      <td>-11.202700</td>\n",
       "      <td>17.873900</td>\n",
       "      <td>1/22/20</td>\n",
       "      <td>0</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>192355</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Vietnam</td>\n",
       "      <td>14.058324</td>\n",
       "      <td>108.277199</td>\n",
       "      <td>12/8/21</td>\n",
       "      <td>1352122</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>192356</th>\n",
       "      <td>NaN</td>\n",
       "      <td>West Bank and Gaza</td>\n",
       "      <td>31.952200</td>\n",
       "      <td>35.233200</td>\n",
       "      <td>12/8/21</td>\n",
       "      <td>462958</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>192357</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Yemen</td>\n",
       "      <td>15.552727</td>\n",
       "      <td>48.516388</td>\n",
       "      <td>12/8/21</td>\n",
       "      <td>10047</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>192358</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Zambia</td>\n",
       "      <td>-13.133897</td>\n",
       "      <td>27.849332</td>\n",
       "      <td>12/8/21</td>\n",
       "      <td>210436</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>192359</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>-19.015438</td>\n",
       "      <td>29.154857</td>\n",
       "      <td>12/8/21</td>\n",
       "      <td>150628</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>192360 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Province/State      Country/Region        Lat        Long     Date  \\\n",
       "0                 NaN         Afghanistan  33.939110   67.709953  1/22/20   \n",
       "1                 NaN             Albania  41.153300   20.168300  1/22/20   \n",
       "2                 NaN             Algeria  28.033900    1.659600  1/22/20   \n",
       "3                 NaN             Andorra  42.506300    1.521800  1/22/20   \n",
       "4                 NaN              Angola -11.202700   17.873900  1/22/20   \n",
       "...               ...                 ...        ...         ...      ...   \n",
       "192355            NaN             Vietnam  14.058324  108.277199  12/8/21   \n",
       "192356            NaN  West Bank and Gaza  31.952200   35.233200  12/8/21   \n",
       "192357            NaN               Yemen  15.552727   48.516388  12/8/21   \n",
       "192358            NaN              Zambia -13.133897   27.849332  12/8/21   \n",
       "192359            NaN            Zimbabwe -19.015438   29.154857  12/8/21   \n",
       "\n",
       "        Confirmed  \n",
       "0               0  \n",
       "1               0  \n",
       "2               0  \n",
       "3               0  \n",
       "4               0  \n",
       "...           ...  \n",
       "192355    1352122  \n",
       "192356     462958  \n",
       "192357      10047  \n",
       "192358     210436  \n",
       "192359     150628  \n",
       "\n",
       "[192360 rows x 6 columns]"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "idVars = ['Province/State', 'Country/Region', 'Lat', 'Long']\n",
    "ts_confirmed_global_long = pd.melt(ts_confirmed_global,\n",
    "                                   id_vars=idVars,\n",
    "                                   var_name='Date',\n",
    "                                   value_name='Confirmed')\n",
    "ts_confirmed_global_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": 65,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "def get_top_rated_movies():\n",
    "    request_url = \"https://www.imdb.com/chart/top\"\n",
    "    html_tables = pd.read_html(request_url)\n",
    "    html_table = html_tables[0]\n",
    "    split_dataframe = html_table['Rank & Title'].str.split(expand=True)\n",
    "    ranks = split_dataframe[0].str.replace(\".\", '', regex=False).values\n",
    "    split_series = html_table['Rank & Title'].str.split()\n",
    "    years, titles = list(), list()\n",
    "    for lst in split_series:\n",
    "        year = re.sub(\"\\(|\\)\", '', lst[-1])\n",
    "        title = \" \".join(lst[1:-1])\n",
    "        years.append(int(year))\n",
    "        titles.append(title)\n",
    "    ratings = html_table['IMDb Rating'].values\n",
    "    out = pd.DataFrame()\n",
    "    out['rank'] = ranks\n",
    "    out['title'] = titles\n",
    "    out['year'] = years\n",
    "    out['rating'] = ratings\n",
    "    return out\n",
    "\n",
    "top_rated_movies = get_top_rated_movies()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "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>rank</th>\n",
       "      <th>title</th>\n",
       "      <th>year</th>\n",
       "      <th>rating</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>黑暗騎士</td>\n",
       "      <td>2008</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71</th>\n",
       "      <td>72</td>\n",
       "      <td>黑暗騎士：黎明昇起</td>\n",
       "      <td>2012</td>\n",
       "      <td>8.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>136</th>\n",
       "      <td>137</td>\n",
       "      <td>蝙蝠俠：開戰時刻</td>\n",
       "      <td>2005</td>\n",
       "      <td>8.2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    rank      title  year  rating\n",
       "3      4       黑暗騎士  2008     9.0\n",
       "71    72  黑暗騎士：黎明昇起  2012     8.3\n",
       "136  137   蝙蝠俠：開戰時刻  2005     8.2"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "batman_trilogy = top_rated_movies[top_rated_movies[\"title\"].str.contains(\"蝙蝠俠|黑暗騎士\", regex=True)]\n",
    "batman_trilogy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "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>rank</th>\n",
       "      <th>title</th>\n",
       "      <th>year</th>\n",
       "      <th>rating</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>魔戒三部曲：王者再臨</td>\n",
       "      <td>2003</td>\n",
       "      <td>8.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>魔戒首部曲：魔戒現身</td>\n",
       "      <td>2001</td>\n",
       "      <td>8.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>14</td>\n",
       "      <td>魔戒二部曲：雙城奇謀</td>\n",
       "      <td>2002</td>\n",
       "      <td>8.7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   rank       title  year  rating\n",
       "6     7  魔戒三部曲：王者再臨  2003     8.9\n",
       "9    10  魔戒首部曲：魔戒現身  2001     8.8\n",
       "13   14  魔戒二部曲：雙城奇謀  2002     8.7"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lord_of_the_rings_trilogy = top_rated_movies[top_rated_movies[\"title\"].str.contains(\"魔戒\", regex=False)]\n",
    "lord_of_the_rings_trilogy"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Use `concat()` function to concatenate vertically or horizontally"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>rank</th>\n",
       "      <th>title</th>\n",
       "      <th>year</th>\n",
       "      <th>rating</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>黑暗騎士</td>\n",
       "      <td>2008</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71</th>\n",
       "      <td>72</td>\n",
       "      <td>黑暗騎士：黎明昇起</td>\n",
       "      <td>2012</td>\n",
       "      <td>8.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>136</th>\n",
       "      <td>137</td>\n",
       "      <td>蝙蝠俠：開戰時刻</td>\n",
       "      <td>2005</td>\n",
       "      <td>8.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>魔戒三部曲：王者再臨</td>\n",
       "      <td>2003</td>\n",
       "      <td>8.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>魔戒首部曲：魔戒現身</td>\n",
       "      <td>2001</td>\n",
       "      <td>8.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>14</td>\n",
       "      <td>魔戒二部曲：雙城奇謀</td>\n",
       "      <td>2002</td>\n",
       "      <td>8.7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    rank       title  year  rating\n",
       "3      4        黑暗騎士  2008     9.0\n",
       "71    72   黑暗騎士：黎明昇起  2012     8.3\n",
       "136  137    蝙蝠俠：開戰時刻  2005     8.2\n",
       "6      7  魔戒三部曲：王者再臨  2003     8.9\n",
       "9     10  魔戒首部曲：魔戒現身  2001     8.8\n",
       "13    14  魔戒二部曲：雙城奇謀  2002     8.7"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat((batman_trilogy, lord_of_the_rings_trilogy))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>title</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>黑暗騎士</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>NaN</td>\n",
       "      <td>魔戒三部曲：王者再臨</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>NaN</td>\n",
       "      <td>魔戒首部曲：魔戒現身</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>NaN</td>\n",
       "      <td>魔戒二部曲：雙城奇謀</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71</th>\n",
       "      <td>黑暗騎士：黎明昇起</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>136</th>\n",
       "      <td>蝙蝠俠：開戰時刻</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         title       title\n",
       "3         黑暗騎士         NaN\n",
       "6          NaN  魔戒三部曲：王者再臨\n",
       "9          NaN  魔戒首部曲：魔戒現身\n",
       "13         NaN  魔戒二部曲：雙城奇謀\n",
       "71   黑暗騎士：黎明昇起         NaN\n",
       "136   蝙蝠俠：開戰時刻         NaN"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat((batman_trilogy[\"title\"], lord_of_the_rings_trilogy[\"title\"]), axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>title</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>黑暗騎士</td>\n",
       "      <td>魔戒三部曲：王者再臨</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>黑暗騎士：黎明昇起</td>\n",
       "      <td>魔戒首部曲：魔戒現身</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>蝙蝠俠：開戰時刻</td>\n",
       "      <td>魔戒二部曲：雙城奇謀</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       title       title\n",
       "0       黑暗騎士  魔戒三部曲：王者再臨\n",
       "1  黑暗騎士：黎明昇起  魔戒首部曲：魔戒現身\n",
       "2   蝙蝠俠：開戰時刻  魔戒二部曲：雙城奇謀"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat((batman_trilogy[\"title\"].reset_index(drop=True),\n",
    "           lord_of_the_rings_trilogy[\"title\"].reset_index(drop=True)),\n",
    "          axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>刺激1995</td>\n",
       "      <td>1994</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>教父</td>\n",
       "      <td>1972</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>教父第二集</td>\n",
       "      <td>1974</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>黑暗騎士</td>\n",
       "      <td>2008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>十二怒漢</td>\n",
       "      <td>1957</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>辛德勒的名單</td>\n",
       "      <td>1993</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>魔戒三部曲：王者再臨</td>\n",
       "      <td>2003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>黑色追緝令</td>\n",
       "      <td>1994</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>黃昏三鏢客</td>\n",
       "      <td>1966</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>魔戒首部曲：魔戒現身</td>\n",
       "      <td>2001</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        title  year\n",
       "0      刺激1995  1994\n",
       "1          教父  1972\n",
       "2       教父第二集  1974\n",
       "3        黑暗騎士  2008\n",
       "4        十二怒漢  1957\n",
       "5      辛德勒的名單  1993\n",
       "6  魔戒三部曲：王者再臨  2003\n",
       "7       黑色追緝令  1994\n",
       "8       黃昏三鏢客  1966\n",
       "9  魔戒首部曲：魔戒現身  2001"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "left_df = top_rated_movies.loc[:9, ['title', 'year']]\n",
    "right_df = top_rated_movies[top_rated_movies['title'].str.contains('魔戒')][['title', 'rating']].reset_index(drop=True)\n",
    "left_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>rating</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>魔戒三部曲：王者再臨</td>\n",
       "      <td>8.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>魔戒首部曲：魔戒現身</td>\n",
       "      <td>8.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>魔戒二部曲：雙城奇謀</td>\n",
       "      <td>8.7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        title  rating\n",
       "0  魔戒三部曲：王者再臨     8.9\n",
       "1  魔戒首部曲：魔戒現身     8.8\n",
       "2  魔戒二部曲：雙城奇謀     8.7"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "right_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Using `merge` function to join dataframes on columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>year</th>\n",
       "      <th>rating</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>魔戒三部曲：王者再臨</td>\n",
       "      <td>2003</td>\n",
       "      <td>8.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>魔戒首部曲：魔戒現身</td>\n",
       "      <td>2001</td>\n",
       "      <td>8.8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        title  year  rating\n",
       "0  魔戒三部曲：王者再臨  2003     8.9\n",
       "1  魔戒首部曲：魔戒現身  2001     8.8"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# default: inner join\n",
    "pd.merge(left_df, right_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>year</th>\n",
       "      <th>rating</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>刺激1995</td>\n",
       "      <td>1994</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>教父</td>\n",
       "      <td>1972</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>教父第二集</td>\n",
       "      <td>1974</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>黑暗騎士</td>\n",
       "      <td>2008</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>十二怒漢</td>\n",
       "      <td>1957</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>辛德勒的名單</td>\n",
       "      <td>1993</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>魔戒三部曲：王者再臨</td>\n",
       "      <td>2003</td>\n",
       "      <td>8.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>黑色追緝令</td>\n",
       "      <td>1994</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>黃昏三鏢客</td>\n",
       "      <td>1966</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>魔戒首部曲：魔戒現身</td>\n",
       "      <td>2001</td>\n",
       "      <td>8.8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        title  year  rating\n",
       "0      刺激1995  1994     NaN\n",
       "1          教父  1972     NaN\n",
       "2       教父第二集  1974     NaN\n",
       "3        黑暗騎士  2008     NaN\n",
       "4        十二怒漢  1957     NaN\n",
       "5      辛德勒的名單  1993     NaN\n",
       "6  魔戒三部曲：王者再臨  2003     8.9\n",
       "7       黑色追緝令  1994     NaN\n",
       "8       黃昏三鏢客  1966     NaN\n",
       "9  魔戒首部曲：魔戒現身  2001     8.8"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# left join\n",
    "pd.merge(left_df, right_df, how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>year</th>\n",
       "      <th>rating</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>魔戒三部曲：王者再臨</td>\n",
       "      <td>2003.0</td>\n",
       "      <td>8.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>魔戒首部曲：魔戒現身</td>\n",
       "      <td>2001.0</td>\n",
       "      <td>8.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>魔戒二部曲：雙城奇謀</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        title    year  rating\n",
       "0  魔戒三部曲：王者再臨  2003.0     8.9\n",
       "1  魔戒首部曲：魔戒現身  2001.0     8.8\n",
       "2  魔戒二部曲：雙城奇謀     NaN     8.7"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# right join\n",
    "pd.merge(left_df, right_df, how='right')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Using `join` method to join dataframes on index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "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>title_x</th>\n",
       "      <th>year</th>\n",
       "      <th>title_y</th>\n",
       "      <th>rating</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>刺激1995</td>\n",
       "      <td>1994</td>\n",
       "      <td>魔戒三部曲：王者再臨</td>\n",
       "      <td>8.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>教父</td>\n",
       "      <td>1972</td>\n",
       "      <td>魔戒首部曲：魔戒現身</td>\n",
       "      <td>8.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>教父第二集</td>\n",
       "      <td>1974</td>\n",
       "      <td>魔戒二部曲：雙城奇謀</td>\n",
       "      <td>8.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>黑暗騎士</td>\n",
       "      <td>2008</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>十二怒漢</td>\n",
       "      <td>1957</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>辛德勒的名單</td>\n",
       "      <td>1993</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>魔戒三部曲：王者再臨</td>\n",
       "      <td>2003</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>黑色追緝令</td>\n",
       "      <td>1994</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>黃昏三鏢客</td>\n",
       "      <td>1966</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>魔戒首部曲：魔戒現身</td>\n",
       "      <td>2001</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      title_x  year     title_y  rating\n",
       "0      刺激1995  1994  魔戒三部曲：王者再臨     8.9\n",
       "1          教父  1972  魔戒首部曲：魔戒現身     8.8\n",
       "2       教父第二集  1974  魔戒二部曲：雙城奇謀     8.7\n",
       "3        黑暗騎士  2008         NaN     NaN\n",
       "4        十二怒漢  1957         NaN     NaN\n",
       "5      辛德勒的名單  1993         NaN     NaN\n",
       "6  魔戒三部曲：王者再臨  2003         NaN     NaN\n",
       "7       黑色追緝令  1994         NaN     NaN\n",
       "8       黃昏三鏢客  1966         NaN     NaN\n",
       "9  魔戒首部曲：魔戒現身  2001         NaN     NaN"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# join dataframes on index\n",
    "left_df.join(right_df, lsuffix='_x', rsuffix='_y')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "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>year</th>\n",
       "      <th>rating</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>title</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>刺激1995</th>\n",
       "      <td>1994</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>教父</th>\n",
       "      <td>1972</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>教父第二集</th>\n",
       "      <td>1974</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>黑暗騎士</th>\n",
       "      <td>2008</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>十二怒漢</th>\n",
       "      <td>1957</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>辛德勒的名單</th>\n",
       "      <td>1993</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>魔戒三部曲：王者再臨</th>\n",
       "      <td>2003</td>\n",
       "      <td>8.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>黑色追緝令</th>\n",
       "      <td>1994</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>黃昏三鏢客</th>\n",
       "      <td>1966</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>魔戒首部曲：魔戒現身</th>\n",
       "      <td>2001</td>\n",
       "      <td>8.8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            year  rating\n",
       "title                   \n",
       "刺激1995      1994     NaN\n",
       "教父          1972     NaN\n",
       "教父第二集       1974     NaN\n",
       "黑暗騎士        2008     NaN\n",
       "十二怒漢        1957     NaN\n",
       "辛德勒的名單      1993     NaN\n",
       "魔戒三部曲：王者再臨  2003     8.9\n",
       "黑色追緝令       1994     NaN\n",
       "黃昏三鏢客       1966     NaN\n",
       "魔戒首部曲：魔戒現身  2001     8.8"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "left_df = left_df.set_index('title')\n",
    "right_df = right_df.set_index('title')\n",
    "# default: left join\n",
    "left_df.join(right_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "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>year</th>\n",
       "      <th>rating</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>title</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>魔戒三部曲：王者再臨</th>\n",
       "      <td>2003</td>\n",
       "      <td>8.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>魔戒首部曲：魔戒現身</th>\n",
       "      <td>2001</td>\n",
       "      <td>8.8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            year  rating\n",
       "title                   \n",
       "魔戒三部曲：王者再臨  2003     8.9\n",
       "魔戒首部曲：魔戒現身  2001     8.8"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# inner join\n",
    "left_df.join(right_df, how='inner')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "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>year</th>\n",
       "      <th>rating</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>title</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>魔戒三部曲：王者再臨</th>\n",
       "      <td>2003.0</td>\n",
       "      <td>8.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>魔戒首部曲：魔戒現身</th>\n",
       "      <td>2001.0</td>\n",
       "      <td>8.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>魔戒二部曲：雙城奇謀</th>\n",
       "      <td>NaN</td>\n",
       "      <td>8.7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              year  rating\n",
       "title                     \n",
       "魔戒三部曲：王者再臨  2003.0     8.9\n",
       "魔戒首部曲：魔戒現身  2001.0     8.8\n",
       "魔戒二部曲：雙城奇謀     NaN     8.7"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# right join\n",
    "left_df.join(right_df, how='right')"
   ]
  }
 ],
 "metadata": {
  "celltoolbar": "Slideshow",
  "kernelspec": {
   "display_name": "Python Fifty Plus",
   "language": "python",
   "name": "pythonfiftyplus"
  },
  "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.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}