class 04 Joining Data with Pandas/join_pandas_class2.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "from matplotlib import pyplot as plt"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## left join"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img src= './media/left_join.png' width=\"500\" height=\"600\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img src = './media/leftjoin_table.png' width=\"700\" height=\"800\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Dataset: The Movies DB"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(4803, 4)\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>id</th>\n",
       "      <th>title</th>\n",
       "      <th>popularity</th>\n",
       "      <th>release_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>257</td>\n",
       "      <td>Oliver Twist</td>\n",
       "      <td>20.415572</td>\n",
       "      <td>2005-09-23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>14290</td>\n",
       "      <td>Better Luck Tomorrow</td>\n",
       "      <td>3.877036</td>\n",
       "      <td>2002-01-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>38365</td>\n",
       "      <td>Grown Ups</td>\n",
       "      <td>38.864027</td>\n",
       "      <td>2010-06-24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>9672</td>\n",
       "      <td>Infamous</td>\n",
       "      <td>3.680896</td>\n",
       "      <td>2006-11-16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>12819</td>\n",
       "      <td>Alpha and Omega</td>\n",
       "      <td>12.300789</td>\n",
       "      <td>2010-09-17</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      id                 title  popularity release_date\n",
       "0    257          Oliver Twist   20.415572   2005-09-23\n",
       "1  14290  Better Luck Tomorrow    3.877036   2002-01-12\n",
       "2  38365             Grown Ups   38.864027   2010-06-24\n",
       "3   9672              Infamous    3.680896   2006-11-16\n",
       "4  12819       Alpha and Omega   12.300789   2010-09-17"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#movies Table\n",
    "movies = pd.read_pickle('./dataset/movies/movies.p')\n",
    "print(movies.shape)\n",
    "movies.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(3955, 2)\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>id</th>\n",
       "      <th>tagline</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>19995</td>\n",
       "      <td>Enter the World of Pandora.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>285</td>\n",
       "      <td>At the end of the world, the adventure begins.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>206647</td>\n",
       "      <td>A Plan No One Escapes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>49026</td>\n",
       "      <td>The Legend Ends</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>49529</td>\n",
       "      <td>Lost in our world, found in another.</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       id                                         tagline\n",
       "0   19995                     Enter the World of Pandora.\n",
       "1     285  At the end of the world, the adventure begins.\n",
       "2  206647                           A Plan No One Escapes\n",
       "3   49026                                 The Legend Ends\n",
       "4   49529            Lost in our world, found in another."
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#tagline table\n",
    "taglines = pd.read_pickle('./dataset/movies/taglines.p')\n",
    "print(taglines.shape)\n",
    "taglines.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>title</th>\n",
       "      <th>popularity</th>\n",
       "      <th>release_date</th>\n",
       "      <th>tagline</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>257</td>\n",
       "      <td>Oliver Twist</td>\n",
       "      <td>20.415572</td>\n",
       "      <td>2005-09-23</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>14290</td>\n",
       "      <td>Better Luck Tomorrow</td>\n",
       "      <td>3.877036</td>\n",
       "      <td>2002-01-12</td>\n",
       "      <td>Never underestimate an overachiever.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>38365</td>\n",
       "      <td>Grown Ups</td>\n",
       "      <td>38.864027</td>\n",
       "      <td>2010-06-24</td>\n",
       "      <td>Boys will be boys. . . some longer than others.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>9672</td>\n",
       "      <td>Infamous</td>\n",
       "      <td>3.680896</td>\n",
       "      <td>2006-11-16</td>\n",
       "      <td>There's more to the story than you know</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>12819</td>\n",
       "      <td>Alpha and Omega</td>\n",
       "      <td>12.300789</td>\n",
       "      <td>2010-09-17</td>\n",
       "      <td>A Pawsome 3D Adventure</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>4798</th>\n",
       "      <td>3089</td>\n",
       "      <td>Red River</td>\n",
       "      <td>5.344815</td>\n",
       "      <td>1948-08-26</td>\n",
       "      <td>Big as the men who faced this challenge! Bold ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4799</th>\n",
       "      <td>11934</td>\n",
       "      <td>The Hudsucker Proxy</td>\n",
       "      <td>14.188982</td>\n",
       "      <td>1994-03-11</td>\n",
       "      <td>They took him for a fall guy... but he threw t...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4800</th>\n",
       "      <td>13807</td>\n",
       "      <td>Exiled</td>\n",
       "      <td>8.486390</td>\n",
       "      <td>2006-09-06</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4801</th>\n",
       "      <td>73873</td>\n",
       "      <td>Albert Nobbs</td>\n",
       "      <td>7.802245</td>\n",
       "      <td>2011-12-21</td>\n",
       "      <td>A man with a secret. A woman with a dream.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4802</th>\n",
       "      <td>11622</td>\n",
       "      <td>Blast from the Past</td>\n",
       "      <td>8.737058</td>\n",
       "      <td>1999-02-12</td>\n",
       "      <td>She'd never met anyone like him. He's never me...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4803 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         id                 title  popularity release_date  \\\n",
       "0       257          Oliver Twist   20.415572   2005-09-23   \n",
       "1     14290  Better Luck Tomorrow    3.877036   2002-01-12   \n",
       "2     38365             Grown Ups   38.864027   2010-06-24   \n",
       "3      9672              Infamous    3.680896   2006-11-16   \n",
       "4     12819       Alpha and Omega   12.300789   2010-09-17   \n",
       "...     ...                   ...         ...          ...   \n",
       "4798   3089             Red River    5.344815   1948-08-26   \n",
       "4799  11934   The Hudsucker Proxy   14.188982   1994-03-11   \n",
       "4800  13807                Exiled    8.486390   2006-09-06   \n",
       "4801  73873          Albert Nobbs    7.802245   2011-12-21   \n",
       "4802  11622   Blast from the Past    8.737058   1999-02-12   \n",
       "\n",
       "                                                tagline  \n",
       "0                                                   NaN  \n",
       "1                  Never underestimate an overachiever.  \n",
       "2       Boys will be boys. . . some longer than others.  \n",
       "3               There's more to the story than you know  \n",
       "4                                A Pawsome 3D Adventure  \n",
       "...                                                 ...  \n",
       "4798  Big as the men who faced this challenge! Bold ...  \n",
       "4799  They took him for a fall guy... but he threw t...  \n",
       "4800                                                NaN  \n",
       "4801         A man with a secret. A woman with a dream.  \n",
       "4802  She'd never met anyone like him. He's never me...  \n",
       "\n",
       "[4803 rows x 5 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Merge left join\n",
    "movies.merge(taglines, on='id', how='left')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img src='./media/right_join.png' width=500 height=600>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img src='./media/rightjoin_table.png' width=700 height=800>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(12160, 2)\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>movie_id</th>\n",
       "      <th>genre</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5</td>\n",
       "      <td>Crime</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5</td>\n",
       "      <td>Comedy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>11</td>\n",
       "      <td>Science Fiction</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>11</td>\n",
       "      <td>Action</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>11</td>\n",
       "      <td>Adventure</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   movie_id            genre\n",
       "0         5            Crime\n",
       "1         5           Comedy\n",
       "2        11  Science Fiction\n",
       "3        11           Action\n",
       "4        11        Adventure"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movies_to_genre = pd.read_pickle('./dataset/movies/movie_to_genres.p')\n",
    "print(movies_to_genre.shape)\n",
    "movies_to_genre.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>movie_id</th>\n",
       "      <th>genre</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4998</th>\n",
       "      <td>10947</td>\n",
       "      <td>TV Movie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5994</th>\n",
       "      <td>13187</td>\n",
       "      <td>TV Movie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7443</th>\n",
       "      <td>22488</td>\n",
       "      <td>TV Movie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10061</th>\n",
       "      <td>78814</td>\n",
       "      <td>TV Movie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10790</th>\n",
       "      <td>153397</td>\n",
       "      <td>TV Movie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10835</th>\n",
       "      <td>158150</td>\n",
       "      <td>TV Movie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11096</th>\n",
       "      <td>205321</td>\n",
       "      <td>TV Movie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11282</th>\n",
       "      <td>231617</td>\n",
       "      <td>TV Movie</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       movie_id     genre\n",
       "4998      10947  TV Movie\n",
       "5994      13187  TV Movie\n",
       "7443      22488  TV Movie\n",
       "10061     78814  TV Movie\n",
       "10790    153397  TV Movie\n",
       "10835    158150  TV Movie\n",
       "11096    205321  TV Movie\n",
       "11282    231617  TV Movie"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#filtering the data\n",
    "# condition = movies_to_genre['genre'] == 'TV Movie'\n",
    "#tv_genre = movies_to_genre[condition]\n",
    "tv_genre = movies_to_genre[movies_to_genre['genre'] == 'TV Movie']\n",
    "tv_genre"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# right join\n",
    "# movies.merge(tv_genre, on='movie_id', how='right') #<-- Error because didn't match columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>title</th>\n",
       "      <th>popularity</th>\n",
       "      <th>release_date</th>\n",
       "      <th>movie_id</th>\n",
       "      <th>genre</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10947</td>\n",
       "      <td>High School Musical</td>\n",
       "      <td>16.536374</td>\n",
       "      <td>2006-01-20</td>\n",
       "      <td>10947</td>\n",
       "      <td>TV Movie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>13187</td>\n",
       "      <td>A Charlie Brown Christmas</td>\n",
       "      <td>8.701183</td>\n",
       "      <td>1965-12-09</td>\n",
       "      <td>13187</td>\n",
       "      <td>TV Movie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>22488</td>\n",
       "      <td>Love's Abiding Joy</td>\n",
       "      <td>1.128559</td>\n",
       "      <td>2006-10-06</td>\n",
       "      <td>22488</td>\n",
       "      <td>TV Movie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>78814</td>\n",
       "      <td>We Have Your Husband</td>\n",
       "      <td>0.102003</td>\n",
       "      <td>2011-11-12</td>\n",
       "      <td>78814</td>\n",
       "      <td>TV Movie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>153397</td>\n",
       "      <td>Restless</td>\n",
       "      <td>0.812776</td>\n",
       "      <td>2012-12-07</td>\n",
       "      <td>153397</td>\n",
       "      <td>TV Movie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>158150</td>\n",
       "      <td>How to Fall in Love</td>\n",
       "      <td>1.923514</td>\n",
       "      <td>2012-07-21</td>\n",
       "      <td>158150</td>\n",
       "      <td>TV Movie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>205321</td>\n",
       "      <td>Sharknado</td>\n",
       "      <td>20.466433</td>\n",
       "      <td>2013-07-11</td>\n",
       "      <td>205321</td>\n",
       "      <td>TV Movie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>231617</td>\n",
       "      <td>Signed, Sealed, Delivered</td>\n",
       "      <td>1.444476</td>\n",
       "      <td>2013-10-13</td>\n",
       "      <td>231617</td>\n",
       "      <td>TV Movie</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       id                      title  popularity release_date  movie_id  \\\n",
       "0   10947        High School Musical   16.536374   2006-01-20     10947   \n",
       "1   13187  A Charlie Brown Christmas    8.701183   1965-12-09     13187   \n",
       "2   22488         Love's Abiding Joy    1.128559   2006-10-06     22488   \n",
       "3   78814       We Have Your Husband    0.102003   2011-11-12     78814   \n",
       "4  153397                   Restless    0.812776   2012-12-07    153397   \n",
       "5  158150        How to Fall in Love    1.923514   2012-07-21    158150   \n",
       "6  205321                  Sharknado   20.466433   2013-07-11    205321   \n",
       "7  231617  Signed, Sealed, Delivered    1.444476   2013-10-13    231617   \n",
       "\n",
       "      genre  \n",
       "0  TV Movie  \n",
       "1  TV Movie  \n",
       "2  TV Movie  \n",
       "3  TV Movie  \n",
       "4  TV Movie  \n",
       "5  TV Movie  \n",
       "6  TV Movie  \n",
       "7  TV Movie  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# right join\n",
    "movies.merge(tv_genre, how='right', left_on='id', right_on='movie_id') #<-- No Error because columns are defined"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img src='./media/outer_join.png' width=500 height=600>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img src='./media/outerjoin_table.png' width=700 height=800>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>movie_id</th>\n",
       "      <th>genre</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>12</td>\n",
       "      <td>Family</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>35</td>\n",
       "      <td>Family</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>111</th>\n",
       "      <td>105</td>\n",
       "      <td>Family</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     movie_id   genre\n",
       "5          12  Family\n",
       "33         35  Family\n",
       "111       105  Family"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "family = movies_to_genre[movies_to_genre['genre']== 'Family']\n",
    "family.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>movie_id</th>\n",
       "      <th>genre</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5</td>\n",
       "      <td>Comedy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>13</td>\n",
       "      <td>Comedy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>35</td>\n",
       "      <td>Comedy</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    movie_id   genre\n",
       "1          5  Comedy\n",
       "7         13  Comedy\n",
       "35        35  Comedy"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "comedy = movies_to_genre[movies_to_genre['genre']== 'Comedy']\n",
    "comedy.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>movie_id</th>\n",
       "      <th>genre_fam</th>\n",
       "      <th>genre_com</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>12</td>\n",
       "      <td>Family</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>35</td>\n",
       "      <td>Family</td>\n",
       "      <td>Comedy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>105</td>\n",
       "      <td>Family</td>\n",
       "      <td>Comedy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>118</td>\n",
       "      <td>Family</td>\n",
       "      <td>Comedy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>129</td>\n",
       "      <td>Family</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   movie_id genre_fam genre_com\n",
       "0        12    Family       NaN\n",
       "1        35    Family    Comedy\n",
       "2       105    Family    Comedy\n",
       "3       118    Family    Comedy\n",
       "4       129    Family       NaN"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "family_comedy = family.merge(comedy, on='movie_id', how='outer', suffixes=('_fam','_com'))\n",
    "family_comedy.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Merging a table to itself"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img src='./media/mergingtabletoitself.png'>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(4803, 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>id</th>\n",
       "      <th>title</th>\n",
       "      <th>sequel</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>19995</td>\n",
       "      <td>Avatar</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>862</td>\n",
       "      <td>Toy Story</td>\n",
       "      <td>863</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>863</td>\n",
       "      <td>Toy Story 2</td>\n",
       "      <td>10193</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>597</td>\n",
       "      <td>Titanic</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>24428</td>\n",
       "      <td>The Avengers</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      id         title  sequel\n",
       "0  19995        Avatar    <NA>\n",
       "1    862     Toy Story     863\n",
       "2    863   Toy Story 2   10193\n",
       "3    597       Titanic    <NA>\n",
       "4  24428  The Avengers    <NA>"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sequel = pd.read_pickle('./dataset/movies/sequels.p')\n",
    "print(sequel.shape)\n",
    "sequel.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id_org</th>\n",
       "      <th>title_org</th>\n",
       "      <th>sequel_org</th>\n",
       "      <th>id_seq</th>\n",
       "      <th>title_seq</th>\n",
       "      <th>sequel_seq</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>862</td>\n",
       "      <td>Toy Story</td>\n",
       "      <td>863</td>\n",
       "      <td>863</td>\n",
       "      <td>Toy Story 2</td>\n",
       "      <td>10193</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>863</td>\n",
       "      <td>Toy Story 2</td>\n",
       "      <td>10193</td>\n",
       "      <td>10193</td>\n",
       "      <td>Toy Story 3</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>675</td>\n",
       "      <td>Harry Potter and the Order of the Phoenix</td>\n",
       "      <td>767</td>\n",
       "      <td>767</td>\n",
       "      <td>Harry Potter and the Half-Blood Prince</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>121</td>\n",
       "      <td>The Lord of the Rings: The Two Towers</td>\n",
       "      <td>122</td>\n",
       "      <td>122</td>\n",
       "      <td>The Lord of the Rings: The Return of the King</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>120</td>\n",
       "      <td>The Lord of the Rings: The Fellowship of the Ring</td>\n",
       "      <td>121</td>\n",
       "      <td>121</td>\n",
       "      <td>The Lord of the Rings: The Two Towers</td>\n",
       "      <td>122</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>85</th>\n",
       "      <td>76</td>\n",
       "      <td>Before Sunrise</td>\n",
       "      <td>80</td>\n",
       "      <td>80</td>\n",
       "      <td>Before Sunset</td>\n",
       "      <td>132344</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86</th>\n",
       "      <td>2292</td>\n",
       "      <td>Clerks</td>\n",
       "      <td>2295</td>\n",
       "      <td>2295</td>\n",
       "      <td>Clerks II</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87</th>\n",
       "      <td>9367</td>\n",
       "      <td>El Mariachi</td>\n",
       "      <td>8068</td>\n",
       "      <td>8068</td>\n",
       "      <td>Desperado</td>\n",
       "      <td>1428</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>8374</td>\n",
       "      <td>The Boondock Saints</td>\n",
       "      <td>22821</td>\n",
       "      <td>22821</td>\n",
       "      <td>The Boondock Saints II: All Saints Day</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>16186</td>\n",
       "      <td>Diary of a Mad Black Woman</td>\n",
       "      <td>16781</td>\n",
       "      <td>16781</td>\n",
       "      <td>Madea's Family Reunion</td>\n",
       "      <td>15670</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>90 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    id_org                                          title_org  sequel_org  \\\n",
       "0      862                                          Toy Story         863   \n",
       "1      863                                        Toy Story 2       10193   \n",
       "2      675          Harry Potter and the Order of the Phoenix         767   \n",
       "3      121              The Lord of the Rings: The Two Towers         122   \n",
       "4      120  The Lord of the Rings: The Fellowship of the Ring         121   \n",
       "..     ...                                                ...         ...   \n",
       "85      76                                     Before Sunrise          80   \n",
       "86    2292                                             Clerks        2295   \n",
       "87    9367                                        El Mariachi        8068   \n",
       "88    8374                                The Boondock Saints       22821   \n",
       "89   16186                         Diary of a Mad Black Woman       16781   \n",
       "\n",
       "    id_seq                                      title_seq  sequel_seq  \n",
       "0      863                                    Toy Story 2       10193  \n",
       "1    10193                                    Toy Story 3        <NA>  \n",
       "2      767         Harry Potter and the Half-Blood Prince        <NA>  \n",
       "3      122  The Lord of the Rings: The Return of the King        <NA>  \n",
       "4      121          The Lord of the Rings: The Two Towers         122  \n",
       "..     ...                                            ...         ...  \n",
       "85      80                                  Before Sunset      132344  \n",
       "86    2295                                      Clerks II        <NA>  \n",
       "87    8068                                      Desperado        1428  \n",
       "88   22821         The Boondock Saints II: All Saints Day        <NA>  \n",
       "89   16781                         Madea's Family Reunion       15670  \n",
       "\n",
       "[90 rows x 6 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "original_sequels = sequel.merge(sequel, left_on='sequel', right_on='id', suffixes=('_org', '_seq'))\n",
    "original_sequels"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(0                                             Toy Story\n",
       " 1                                           Toy Story 2\n",
       " 2             Harry Potter and the Order of the Phoenix\n",
       " 3                 The Lord of the Rings: The Two Towers\n",
       " 4     The Lord of the Rings: The Fellowship of the Ring\n",
       "                             ...                        \n",
       " 85                                       Before Sunrise\n",
       " 86                                               Clerks\n",
       " 87                                          El Mariachi\n",
       " 88                                  The Boondock Saints\n",
       " 89                           Diary of a Mad Black Woman\n",
       " Name: title_org, Length: 90, dtype: object,\n",
       " 0                                      Toy Story 2\n",
       " 1                                      Toy Story 3\n",
       " 2           Harry Potter and the Half-Blood Prince\n",
       " 3    The Lord of the Rings: The Return of the King\n",
       " 4            The Lord of the Rings: The Two Towers\n",
       " Name: title_seq, dtype: object)"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "original_sequels['title_org'], original_sequels['title_seq'].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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_org</th>\n",
       "      <th>title_seq</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Toy Story</td>\n",
       "      <td>Toy Story 2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Toy Story 2</td>\n",
       "      <td>Toy Story 3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Harry Potter and the Order of the Phoenix</td>\n",
       "      <td>Harry Potter and the Half-Blood Prince</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>The Lord of the Rings: The Two Towers</td>\n",
       "      <td>The Lord of the Rings: The Return of the King</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>The Lord of the Rings: The Fellowship of the Ring</td>\n",
       "      <td>The Lord of the Rings: The Two Towers</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                           title_org  \\\n",
       "0                                          Toy Story   \n",
       "1                                        Toy Story 2   \n",
       "2          Harry Potter and the Order of the Phoenix   \n",
       "3              The Lord of the Rings: The Two Towers   \n",
       "4  The Lord of the Rings: The Fellowship of the Ring   \n",
       "\n",
       "                                       title_seq  \n",
       "0                                    Toy Story 2  \n",
       "1                                    Toy Story 3  \n",
       "2         Harry Potter and the Half-Blood Prince  \n",
       "3  The Lord of the Rings: The Return of the King  \n",
       "4          The Lord of the Rings: The Two Towers  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "original_sequels[['title_org', 'title_seq']].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Merging table to itself with left join"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id_org</th>\n",
       "      <th>title_org</th>\n",
       "      <th>sequel_org</th>\n",
       "      <th>id_seq</th>\n",
       "      <th>title_seq</th>\n",
       "      <th>sequel_seq</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>19995</td>\n",
       "      <td>Avatar</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>862</td>\n",
       "      <td>Toy Story</td>\n",
       "      <td>863</td>\n",
       "      <td>863</td>\n",
       "      <td>Toy Story 2</td>\n",
       "      <td>10193</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>863</td>\n",
       "      <td>Toy Story 2</td>\n",
       "      <td>10193</td>\n",
       "      <td>10193</td>\n",
       "      <td>Toy Story 3</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>597</td>\n",
       "      <td>Titanic</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>24428</td>\n",
       "      <td>The Avengers</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id_org     title_org  sequel_org  id_seq    title_seq  sequel_seq\n",
       "0   19995        Avatar        <NA>    <NA>          NaN        <NA>\n",
       "1     862     Toy Story         863     863  Toy Story 2       10193\n",
       "2     863   Toy Story 2       10193   10193  Toy Story 3        <NA>\n",
       "3     597       Titanic        <NA>    <NA>          NaN        <NA>\n",
       "4   24428  The Avengers        <NA>    <NA>          NaN        <NA>"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "original_sequels = sequel.merge(sequel,\n",
    "                                left_on='sequel',\n",
    "                                right_on='id',\n",
    "                                how='left',\n",
    "                               suffixes=('_org','_seq'))\n",
    "original_sequels.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Merging table to itself with right join"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id_org</th>\n",
       "      <th>title_org</th>\n",
       "      <th>sequel_org</th>\n",
       "      <th>id_seq</th>\n",
       "      <th>title_seq</th>\n",
       "      <th>sequel_seq</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>19995</td>\n",
       "      <td>Avatar</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>862</td>\n",
       "      <td>Toy Story</td>\n",
       "      <td>863</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>862</td>\n",
       "      <td>Toy Story</td>\n",
       "      <td>863</td>\n",
       "      <td>863</td>\n",
       "      <td>Toy Story 2</td>\n",
       "      <td>10193</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>597</td>\n",
       "      <td>Titanic</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>24428</td>\n",
       "      <td>The Avengers</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id_org  title_org  sequel_org  id_seq     title_seq  sequel_seq\n",
       "0    <NA>        NaN        <NA>   19995        Avatar        <NA>\n",
       "1    <NA>        NaN        <NA>     862     Toy Story         863\n",
       "2     862  Toy Story         863     863   Toy Story 2       10193\n",
       "3    <NA>        NaN        <NA>     597       Titanic        <NA>\n",
       "4    <NA>        NaN        <NA>   24428  The Avengers        <NA>"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "original_sequels = sequel.merge(sequel,\n",
    "                                left_on='sequel',\n",
    "                                right_on='id',\n",
    "                                how='right',\n",
    "                               suffixes=('_org','_seq'))\n",
    "original_sequels.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### When to merge at table to itself\n",
    "Common situations:\n",
    "- Hierarchical relationships eg: employee & manager\n",
    "- Sequential relationships eg: logistic movements \n",
    "- Graph data eg: network of friends"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Merging on Indexes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>popularity</th>\n",
       "      <th>release_date</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>257</th>\n",
       "      <td>Oliver Twist</td>\n",
       "      <td>20.415572</td>\n",
       "      <td>2005-09-23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14290</th>\n",
       "      <td>Better Luck Tomorrow</td>\n",
       "      <td>3.877036</td>\n",
       "      <td>2002-01-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38365</th>\n",
       "      <td>Grown Ups</td>\n",
       "      <td>38.864027</td>\n",
       "      <td>2010-06-24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9672</th>\n",
       "      <td>Infamous</td>\n",
       "      <td>3.680896</td>\n",
       "      <td>2006-11-16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12819</th>\n",
       "      <td>Alpha and Omega</td>\n",
       "      <td>12.300789</td>\n",
       "      <td>2010-09-17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3089</th>\n",
       "      <td>Red River</td>\n",
       "      <td>5.344815</td>\n",
       "      <td>1948-08-26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11934</th>\n",
       "      <td>The Hudsucker Proxy</td>\n",
       "      <td>14.188982</td>\n",
       "      <td>1994-03-11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13807</th>\n",
       "      <td>Exiled</td>\n",
       "      <td>8.486390</td>\n",
       "      <td>2006-09-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73873</th>\n",
       "      <td>Albert Nobbs</td>\n",
       "      <td>7.802245</td>\n",
       "      <td>2011-12-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11622</th>\n",
       "      <td>Blast from the Past</td>\n",
       "      <td>8.737058</td>\n",
       "      <td>1999-02-12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4803 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                      title  popularity release_date\n",
       "id                                                  \n",
       "257            Oliver Twist   20.415572   2005-09-23\n",
       "14290  Better Luck Tomorrow    3.877036   2002-01-12\n",
       "38365             Grown Ups   38.864027   2010-06-24\n",
       "9672               Infamous    3.680896   2006-11-16\n",
       "12819       Alpha and Omega   12.300789   2010-09-17\n",
       "...                     ...         ...          ...\n",
       "3089              Red River    5.344815   1948-08-26\n",
       "11934   The Hudsucker Proxy   14.188982   1994-03-11\n",
       "13807                Exiled    8.486390   2006-09-06\n",
       "73873          Albert Nobbs    7.802245   2011-12-21\n",
       "11622   Blast from the Past    8.737058   1999-02-12\n",
       "\n",
       "[4803 rows x 3 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#setting an index\n",
    "new_movie = movies.set_index('id')\n",
    "new_movie"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>tagline</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>19995</td>\n",
       "      <td>Enter the World of Pandora.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>285</td>\n",
       "      <td>At the end of the world, the adventure begins.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>206647</td>\n",
       "      <td>A Plan No One Escapes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>49026</td>\n",
       "      <td>The Legend Ends</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>49529</td>\n",
       "      <td>Lost in our world, found in another.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4795</th>\n",
       "      <td>124606</td>\n",
       "      <td>Sometimes you've got to break the rules</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4796</th>\n",
       "      <td>14337</td>\n",
       "      <td>What happens if it actually works?</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4798</th>\n",
       "      <td>9367</td>\n",
       "      <td>He didn't come looking for trouble, but troubl...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4799</th>\n",
       "      <td>72766</td>\n",
       "      <td>A newlywed couple's honeymoon is upended by th...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4801</th>\n",
       "      <td>126186</td>\n",
       "      <td>A New Yorker in Shanghai</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3955 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          id                                            tagline\n",
       "0      19995                        Enter the World of Pandora.\n",
       "1        285     At the end of the world, the adventure begins.\n",
       "2     206647                              A Plan No One Escapes\n",
       "3      49026                                    The Legend Ends\n",
       "4      49529               Lost in our world, found in another.\n",
       "...      ...                                                ...\n",
       "4795  124606            Sometimes you've got to break the rules\n",
       "4796   14337                 What happens if it actually works?\n",
       "4798    9367  He didn't come looking for trouble, but troubl...\n",
       "4799   72766  A newlywed couple's honeymoon is upended by th...\n",
       "4801  126186                           A New Yorker in Shanghai\n",
       "\n",
       "[3955 rows x 2 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "taglines"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>title</th>\n",
       "      <th>popularity</th>\n",
       "      <th>release_date</th>\n",
       "      <th>tagline</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>257</td>\n",
       "      <td>Oliver Twist</td>\n",
       "      <td>20.415572</td>\n",
       "      <td>2005-09-23</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>14290</td>\n",
       "      <td>Better Luck Tomorrow</td>\n",
       "      <td>3.877036</td>\n",
       "      <td>2002-01-12</td>\n",
       "      <td>Never underestimate an overachiever.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>38365</td>\n",
       "      <td>Grown Ups</td>\n",
       "      <td>38.864027</td>\n",
       "      <td>2010-06-24</td>\n",
       "      <td>Boys will be boys. . . some longer than others.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>9672</td>\n",
       "      <td>Infamous</td>\n",
       "      <td>3.680896</td>\n",
       "      <td>2006-11-16</td>\n",
       "      <td>There's more to the story than you know</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>12819</td>\n",
       "      <td>Alpha and Omega</td>\n",
       "      <td>12.300789</td>\n",
       "      <td>2010-09-17</td>\n",
       "      <td>A Pawsome 3D Adventure</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      id                 title  popularity release_date  \\\n",
       "0    257          Oliver Twist   20.415572   2005-09-23   \n",
       "1  14290  Better Luck Tomorrow    3.877036   2002-01-12   \n",
       "2  38365             Grown Ups   38.864027   2010-06-24   \n",
       "3   9672              Infamous    3.680896   2006-11-16   \n",
       "4  12819       Alpha and Omega   12.300789   2010-09-17   \n",
       "\n",
       "                                           tagline  \n",
       "0                                              NaN  \n",
       "1             Never underestimate an overachiever.  \n",
       "2  Boys will be boys. . . some longer than others.  \n",
       "3          There's more to the story than you know  \n",
       "4                           A Pawsome 3D Adventure  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_movie.merge(taglines, on='id', how='left').head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### multiple index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(106257, 6)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>movie_id</th>\n",
       "      <th>cast_id</th>\n",
       "      <th>character</th>\n",
       "      <th>gender</th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>5</td>\n",
       "      <td>22</td>\n",
       "      <td>Jezebel</td>\n",
       "      <td>1</td>\n",
       "      <td>3122</td>\n",
       "      <td>Sammi Davis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>5</td>\n",
       "      <td>23</td>\n",
       "      <td>Diana</td>\n",
       "      <td>1</td>\n",
       "      <td>3123</td>\n",
       "      <td>Amanda de Cadenet</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>5</td>\n",
       "      <td>24</td>\n",
       "      <td>Athena</td>\n",
       "      <td>1</td>\n",
       "      <td>3124</td>\n",
       "      <td>Valeria Golino</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>25</td>\n",
       "      <td>Elspeth</td>\n",
       "      <td>1</td>\n",
       "      <td>3125</td>\n",
       "      <td>Madonna</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>5</td>\n",
       "      <td>26</td>\n",
       "      <td>Eva</td>\n",
       "      <td>1</td>\n",
       "      <td>3126</td>\n",
       "      <td>Ione Skye</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    movie_id  cast_id character  gender    id               name\n",
       "7          5       22   Jezebel       1  3122        Sammi Davis\n",
       "8          5       23     Diana       1  3123  Amanda de Cadenet\n",
       "9          5       24    Athena       1  3124     Valeria Golino\n",
       "3          5       25   Elspeth       1  3125            Madonna\n",
       "12         5       26       Eva       1  3126          Ione Skye"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "main_casts = pd.read_pickle('./dataset/movies/casts.p')\n",
    "print(main_casts.shape)\n",
    "main_casts.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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></th>\n",
       "      <th>character</th>\n",
       "      <th>gender</th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>movie_id</th>\n",
       "      <th>cast_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">5</th>\n",
       "      <th>22</th>\n",
       "      <td>Jezebel</td>\n",
       "      <td>1</td>\n",
       "      <td>3122</td>\n",
       "      <td>Sammi Davis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Diana</td>\n",
       "      <td>1</td>\n",
       "      <td>3123</td>\n",
       "      <td>Amanda de Cadenet</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Athena</td>\n",
       "      <td>1</td>\n",
       "      <td>3124</td>\n",
       "      <td>Valeria Golino</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Elspeth</td>\n",
       "      <td>1</td>\n",
       "      <td>3125</td>\n",
       "      <td>Madonna</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Eva</td>\n",
       "      <td>1</td>\n",
       "      <td>3126</td>\n",
       "      <td>Ione Skye</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">433715</th>\n",
       "      <th>3</th>\n",
       "      <td>Amber</td>\n",
       "      <td>0</td>\n",
       "      <td>1500111</td>\n",
       "      <td>Nicole Smolen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BB</td>\n",
       "      <td>0</td>\n",
       "      <td>1734573</td>\n",
       "      <td>Kim Baldwin</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Sugar</td>\n",
       "      <td>0</td>\n",
       "      <td>1734574</td>\n",
       "      <td>Ariana Stephens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Drew</td>\n",
       "      <td>0</td>\n",
       "      <td>1734575</td>\n",
       "      <td>Bryson Funk</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>459488</th>\n",
       "      <th>0</th>\n",
       "      <td>Narrator</td>\n",
       "      <td>0</td>\n",
       "      <td>1354401</td>\n",
       "      <td>Tony Oppedisano</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>106257 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 character  gender       id               name\n",
       "movie_id cast_id                                              \n",
       "5        22        Jezebel       1     3122        Sammi Davis\n",
       "         23          Diana       1     3123  Amanda de Cadenet\n",
       "         24         Athena       1     3124     Valeria Golino\n",
       "         25        Elspeth       1     3125            Madonna\n",
       "         26            Eva       1     3126          Ione Skye\n",
       "...                    ...     ...      ...                ...\n",
       "433715   3           Amber       0  1500111      Nicole Smolen\n",
       "         4              BB       0  1734573        Kim Baldwin\n",
       "         5           Sugar       0  1734574    Ariana Stephens\n",
       "         6            Drew       0  1734575        Bryson Funk\n",
       "459488   0        Narrator       0  1354401    Tony Oppedisano\n",
       "\n",
       "[106257 rows x 4 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "main_casts = main_casts.set_index(['movie_id', 'cast_id'])\n",
    "main_casts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>character</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>movie_id</th>\n",
       "      <th>cast_id</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">5</th>\n",
       "      <th>22</th>\n",
       "      <td>Jezebel</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Diana</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Athena</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Elspeth</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Eva</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">433715</th>\n",
       "      <th>3</th>\n",
       "      <td>Amber</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Sugar</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Drew</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>459488</th>\n",
       "      <th>0</th>\n",
       "      <td>Narrator</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>106257 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 character\n",
       "movie_id cast_id          \n",
       "5        22        Jezebel\n",
       "         23          Diana\n",
       "         24         Athena\n",
       "         25        Elspeth\n",
       "         26            Eva\n",
       "...                    ...\n",
       "433715   3           Amber\n",
       "         4              BB\n",
       "         5           Sugar\n",
       "         6            Drew\n",
       "459488   0        Narrator\n",
       "\n",
       "[106257 rows x 1 columns]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "casts = main_casts['character']\n",
    "casts = pd.DataFrame(casts)\n",
    "casts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "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></th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>movie_id</th>\n",
       "      <th>cast_id</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>184</th>\n",
       "      <th>3</th>\n",
       "      <td>Samuel L. Jackson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>319</th>\n",
       "      <th>13</th>\n",
       "      <td>Samuel L. Jackson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>326</th>\n",
       "      <th>2</th>\n",
       "      <td>Samuel L. Jackson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>329</th>\n",
       "      <th>138</th>\n",
       "      <td>Samuel L. Jackson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>393</th>\n",
       "      <th>21</th>\n",
       "      <td>Samuel L. Jackson</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               name\n",
       "movie_id cast_id                   \n",
       "184      3        Samuel L. Jackson\n",
       "319      13       Samuel L. Jackson\n",
       "326      2        Samuel L. Jackson\n",
       "329      138      Samuel L. Jackson\n",
       "393      21       Samuel L. Jackson"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "samuel = main_casts[main_casts.name == 'Samuel L. Jackson']\n",
    "samuel = pd.DataFrame(samuel['name'])\n",
    "samuel.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(67, 2)\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></th>\n",
       "      <th>name</th>\n",
       "      <th>character</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>movie_id</th>\n",
       "      <th>cast_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>184</th>\n",
       "      <th>3</th>\n",
       "      <td>Samuel L. Jackson</td>\n",
       "      <td>Ordell Robbie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>319</th>\n",
       "      <th>13</th>\n",
       "      <td>Samuel L. Jackson</td>\n",
       "      <td>Big Don</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>326</th>\n",
       "      <th>2</th>\n",
       "      <td>Samuel L. Jackson</td>\n",
       "      <td>Neville Flynn</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>329</th>\n",
       "      <th>138</th>\n",
       "      <td>Samuel L. Jackson</td>\n",
       "      <td>Arnold</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>393</th>\n",
       "      <th>21</th>\n",
       "      <td>Samuel L. Jackson</td>\n",
       "      <td>Rufus</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               name      character\n",
       "movie_id cast_id                                  \n",
       "184      3        Samuel L. Jackson  Ordell Robbie\n",
       "319      13       Samuel L. Jackson        Big Don\n",
       "326      2        Samuel L. Jackson  Neville Flynn\n",
       "329      138      Samuel L. Jackson         Arnold\n",
       "393      21       Samuel L. Jackson          Rufus"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "samuel_casts = samuel.merge(casts, on=['movie_id', 'cast_id'])\n",
    "print(samuel_casts.shape)\n",
    "samuel_casts.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Index merge with left_on and right_on"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "movies = movies.set_index('id')\n",
    "movies_to_genre = movies_to_genre.set_index('movie_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>title</th>\n",
       "      <th>popularity</th>\n",
       "      <th>release_date</th>\n",
       "      <th>genre</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>Four Rooms</td>\n",
       "      <td>22.876230</td>\n",
       "      <td>1995-12-09</td>\n",
       "      <td>Crime</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>Four Rooms</td>\n",
       "      <td>22.876230</td>\n",
       "      <td>1995-12-09</td>\n",
       "      <td>Comedy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>11</td>\n",
       "      <td>Star Wars</td>\n",
       "      <td>126.393695</td>\n",
       "      <td>1977-05-25</td>\n",
       "      <td>Science Fiction</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>11</td>\n",
       "      <td>Star Wars</td>\n",
       "      <td>126.393695</td>\n",
       "      <td>1977-05-25</td>\n",
       "      <td>Action</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>11</td>\n",
       "      <td>Star Wars</td>\n",
       "      <td>126.393695</td>\n",
       "      <td>1977-05-25</td>\n",
       "      <td>Adventure</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    id       title  popularity release_date            genre\n",
       "5    5  Four Rooms   22.876230   1995-12-09            Crime\n",
       "5    5  Four Rooms   22.876230   1995-12-09           Comedy\n",
       "11  11   Star Wars  126.393695   1977-05-25  Science Fiction\n",
       "11  11   Star Wars  126.393695   1977-05-25           Action\n",
       "11  11   Star Wars  126.393695   1977-05-25        Adventure"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movies_genre = movies.merge(movies_to_genre, left_on='id', right_on='movie_id', left_index=True, right_index=True)\n",
    "movies_genre.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Practise"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Merge the movies table, as the left table, with the financials table using a left join, and save the result to movies_financials.\n",
    "- Count the number of rows in movies_financials with a null value in the budget column.\n",
    "- print number_of_missing_fin"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "movies = pd.read_pickle('./dataset/movies/movies.p')\n",
    "financials = pd.read_pickle('./dataset/movies/financials.p')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>title</th>\n",
       "      <th>popularity</th>\n",
       "      <th>release_date</th>\n",
       "      <th>budget</th>\n",
       "      <th>revenue</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>257</td>\n",
       "      <td>Oliver Twist</td>\n",
       "      <td>20.415572</td>\n",
       "      <td>2005-09-23</td>\n",
       "      <td>50000000.0</td>\n",
       "      <td>42093706.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>14290</td>\n",
       "      <td>Better Luck Tomorrow</td>\n",
       "      <td>3.877036</td>\n",
       "      <td>2002-01-12</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>38365</td>\n",
       "      <td>Grown Ups</td>\n",
       "      <td>38.864027</td>\n",
       "      <td>2010-06-24</td>\n",
       "      <td>80000000.0</td>\n",
       "      <td>271430189.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>9672</td>\n",
       "      <td>Infamous</td>\n",
       "      <td>3.680896</td>\n",
       "      <td>2006-11-16</td>\n",
       "      <td>13000000.0</td>\n",
       "      <td>1151330.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>12819</td>\n",
       "      <td>Alpha and Omega</td>\n",
       "      <td>12.300789</td>\n",
       "      <td>2010-09-17</td>\n",
       "      <td>20000000.0</td>\n",
       "      <td>39300000.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      id                 title  popularity release_date      budget  \\\n",
       "0    257          Oliver Twist   20.415572   2005-09-23  50000000.0   \n",
       "1  14290  Better Luck Tomorrow    3.877036   2002-01-12         NaN   \n",
       "2  38365             Grown Ups   38.864027   2010-06-24  80000000.0   \n",
       "3   9672              Infamous    3.680896   2006-11-16  13000000.0   \n",
       "4  12819       Alpha and Omega   12.300789   2010-09-17  20000000.0   \n",
       "\n",
       "       revenue  \n",
       "0   42093706.0  \n",
       "1          NaN  \n",
       "2  271430189.0  \n",
       "3    1151330.0  \n",
       "4   39300000.0  "
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Merge the movies table with the financials table with a left join\n",
    "movies_financials = movies.merge(financials, how='left', on='id')\n",
    "movies_financials.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Count the number of rows in the budget column that are missing\n",
    "number_of_missing_fin = movies_financials['budget'].isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1574\n"
     ]
    }
   ],
   "source": [
    "# Print the number of movies missing financials\n",
    "print(number_of_missing_fin)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Don't run below Code:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Merge toy_story and taglines on the id column with a left join, and save the result as toystory_tag.\n",
    "- With toy_story as the left table, merge to it taglines on the id column with an inner join, and save as toystory_tag."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Merge the toy_story and taglines tables with a left join\n",
    "toystory_tag = toy_story.merge(taglines, on='id', how='left')\n",
    "\n",
    "# Print the rows and shape of toystory_tag\n",
    "print(toystory_tag)\n",
    "print(toystory_tag.shape)\n",
    "\n",
    "# Merge the toy_story and taglines tables with a inner join\n",
    "toystory_tag = toy_story.merge(taglines, on='id')\n",
    "\n",
    "# Print the rows and shape of toystory_tag\n",
    "print(toystory_tag)\n",
    "print(toystory_tag.shape)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Merge action_movies and scifi_movies tables with a right join on movie_id. Save the result as action_scifi.\n",
    "- Update the merge to add suffixes, where '_act' and '_sci' are suffixes for the left and right tables, respectively.\n",
    "- From action_scifi, subset only the rows where the genre_act column is null.\n",
    "- Merge movies and scifi_only using the id column in the left table and the movie_id column in the right table with an inner join."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Merge action_movies to the scifi_movies with right join\n",
    "action_scifi = action_movies.merge(scifi_movies, on='movie_id', how='right',\n",
    "                                   suffixes=('_act','_sci'))\n",
    "\n",
    "# From action_scifi, select only the rows where the genre_act column is null\n",
    "scifi_only = action_scifi[action_scifi['genre_act'].isnull()]\n",
    "\n",
    "# Merge the movies and scifi_only tables with an inner join\n",
    "movies_and_scifi_only = movies.merge(scifi_only, left_on='id', right_on='movie_id')\n",
    "\n",
    "# Print the first few rows and shape of movies_and_scifi_only\n",
    "print(movies_and_scifi_only.head())\n",
    "print(movies_and_scifi_only.shape)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Merge movie_to_genres and pop_movies using a right join. Save the results as genres_movies.\n",
    "- Group genres_movies by genre and count the number of id values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Use right join to merge the movie_to_genres and pop_movies tables\n",
    "genres_movies = movie_to_genres.merge(pop_movies, how='right', \n",
    "                                      left_on='movie_id', \n",
    "                                      right_on= 'id')\n",
    "\n",
    "# Count the number of genres\n",
    "genre_count = genres_movies.groupby('genre').agg({'id':'count'})\n",
    "\n",
    "# Plot a bar chart of the genre_count\n",
    "genre_count.plot(kind='bar')\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- To a variable called crews_self_merged, merge the crews table to itself on the id column using an inner join, setting the suffixes to '_dir' and '_crew' for the left and right tables respectively.\n",
    "- Create a Boolean index, named boolean_filter, that selects rows from the left table with the job of 'Director' and avoids rows with the job of 'Director' in the right table.\n",
    "- Use the .head() method to print the first few rows of direct_crews."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Merge the crews table to itself\n",
    "crews_self_merged = crews.merge(crews, on='id', how='inner',\n",
    "                                suffixes=('_dir','_crew'))\n",
    "\n",
    "# Create a boolean index to select the appropriate rows\n",
    "boolean_filter = ((crews_self_merged['job_dir'] == 'Director') & \n",
    "                  (crews_self_merged['job_crew'] != 'Director'))\n",
    "direct_crews = crews_self_merged[boolean_filter]\n",
    "\n",
    "# Print the first few rows of direct_crews\n",
    "print(direct_crews.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- With the sequels table on the left, merge to it the financials table on index named id, ensuring that all the rows from the sequels are returned and some rows from the other table may not be returned, Save the results to sequels_fin.\n",
    "- Merge the sequels_fin table to itself with an inner join, where the left and right tables merge on sequel and id respectively with suffixes equal to ('_org','_seq'), saving to orig_seq.\n",
    "- Select the title_org, title_seq, and diff columns of orig_seq and save this as titles_diff.\n",
    "- Sort by titles_diff by diff in descending order and print the first few rows."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Merge sequels and financials on index id\n",
    "sequels_fin = sequels.merge(financials, on='id', how='left')\n",
    "\n",
    "# Self merge with suffixes as inner join with left on sequel and right on id\n",
    "orig_seq = sequels_fin.merge(sequels_fin, how='inner', left_on='sequel', \n",
    "                             right_on='id', right_index=True,\n",
    "                             suffixes=('_org','_seq'))\n",
    "\n",
    "# Add calculation to subtract revenue_org from revenue_seq \n",
    "orig_seq['diff'] = orig_seq['revenue_seq'] - orig_seq['revenue_org']\n",
    "\n",
    "# Select the title_org, title_seq, and diff \n",
    "titles_diff = orig_seq[['title_org','title_seq','diff']]\n",
    "\n",
    "# Print the first rows of the sorted titles_diff\n",
    "print(titles_diff.sort_values('diff', ascending=False).head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}