Phase 1 - IMDB ETL.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "20650815",
   "metadata": {},
   "source": [
    "# **Movie Production Business Analysis - IMDB ETL**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7f433c19",
   "metadata": {},
   "source": [
    "- Yvon Bilodeau\n",
    "- May 2022"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "20f2c7ae",
   "metadata": {},
   "source": [
    "## **Business Problem**\n",
    "\n",
    "For this project, a client has request the production of a MySQL database on Movies from a subset of IMDB's publicly available dataset. They would like an analysis of this database to help determine what makes a movie successful and recommendations on how to make a successful movie."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7cbc811e",
   "metadata": {},
   "source": [
    "## **The Data**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0762117d",
   "metadata": {},
   "source": [
    "IMDB Provides Several Files with varied information for Movies, TV Shows, Made for TV Movies, etc.\n",
    "\n",
    "Overview/Data Dictionary: [https://www.imdb.com/interfaces/](https://www.imdb.com/interfaces/)\n",
    "\n",
    "Downloads page: [https://datasets.imdbws.com/](https://datasets.imdbws.com/)\n",
    "        \n",
    "From their previous research, they realized they want to focus on the following files:\n",
    "\n",
    "- title.basics.tsv.gz\n",
    "- title.ratings.tsv.gz\n",
    "- title.akas.tsv.gz"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e5cc62ca",
   "metadata": {},
   "source": [
    "### **Specifications**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3c99b5f3",
   "metadata": {},
   "source": [
    "The stakeholder only wants you to include information for movies based on the following specifications:\n",
    "\n",
    "- Exclude any movie with missing values for genre or runtime\n",
    "- Include only full-length movies (titleType = \"movie\").\n",
    "- Include only fictional movies (not from documentary genre)\n",
    "- Include only movies that were released 2000 - 2021 (include 2000 and 2021)\n",
    "- Include only movies that were released in the United States"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3367fc13",
   "metadata": {},
   "source": [
    "### **Download**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "fd132fda",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create url variables\n",
    "basics_url = \"https://datasets.imdbws.com/title.basics.tsv.gz\"\n",
    "ratings_url =\"https://datasets.imdbws.com/title.ratings.tsv.gz\"\n",
    "akas_url = \"https://datasets.imdbws.com/title.akas.tsv.gz\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "09fdb78b",
   "metadata": {},
   "source": [
    "### **Preprocessing**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "df7fe85f",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import os"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1ae45a51",
   "metadata": {},
   "source": [
    "#### **Title Basics**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "191fa9a8",
   "metadata": {},
   "source": [
    "- Replace \"\\N\" with np.nan\n",
    "- Eliminate movies that are null for runtimeMinutes\n",
    "- Eliminate movies that are null for genre\n",
    "- Keep only titleType==Movie\n",
    "- Keep startYear 2000-2022\n",
    "- Eliminate movies that include  \"Documentary\" in genre"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "6b301d06",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create dataframe\n",
    "basics_df = pd.read_csv(basics_url,sep='\\t', low_memory=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "fb2018d8",
   "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>tconst</th>\n",
       "      <th>titleType</th>\n",
       "      <th>primaryTitle</th>\n",
       "      <th>originalTitle</th>\n",
       "      <th>isAdult</th>\n",
       "      <th>startYear</th>\n",
       "      <th>endYear</th>\n",
       "      <th>runtimeMinutes</th>\n",
       "      <th>genres</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>tt0000001</td>\n",
       "      <td>short</td>\n",
       "      <td>Carmencita</td>\n",
       "      <td>Carmencita</td>\n",
       "      <td>0</td>\n",
       "      <td>1894</td>\n",
       "      <td>\\N</td>\n",
       "      <td>1</td>\n",
       "      <td>Documentary,Short</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>tt0000002</td>\n",
       "      <td>short</td>\n",
       "      <td>Le clown et ses chiens</td>\n",
       "      <td>Le clown et ses chiens</td>\n",
       "      <td>0</td>\n",
       "      <td>1892</td>\n",
       "      <td>\\N</td>\n",
       "      <td>5</td>\n",
       "      <td>Animation,Short</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>tt0000003</td>\n",
       "      <td>short</td>\n",
       "      <td>Pauvre Pierrot</td>\n",
       "      <td>Pauvre Pierrot</td>\n",
       "      <td>0</td>\n",
       "      <td>1892</td>\n",
       "      <td>\\N</td>\n",
       "      <td>4</td>\n",
       "      <td>Animation,Comedy,Romance</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>tt0000004</td>\n",
       "      <td>short</td>\n",
       "      <td>Un bon bock</td>\n",
       "      <td>Un bon bock</td>\n",
       "      <td>0</td>\n",
       "      <td>1892</td>\n",
       "      <td>\\N</td>\n",
       "      <td>12</td>\n",
       "      <td>Animation,Short</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>tt0000005</td>\n",
       "      <td>short</td>\n",
       "      <td>Blacksmith Scene</td>\n",
       "      <td>Blacksmith Scene</td>\n",
       "      <td>0</td>\n",
       "      <td>1893</td>\n",
       "      <td>\\N</td>\n",
       "      <td>1</td>\n",
       "      <td>Comedy,Short</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      tconst titleType            primaryTitle           originalTitle  \\\n",
       "0  tt0000001     short              Carmencita              Carmencita   \n",
       "1  tt0000002     short  Le clown et ses chiens  Le clown et ses chiens   \n",
       "2  tt0000003     short          Pauvre Pierrot          Pauvre Pierrot   \n",
       "3  tt0000004     short             Un bon bock             Un bon bock   \n",
       "4  tt0000005     short        Blacksmith Scene        Blacksmith Scene   \n",
       "\n",
       "  isAdult startYear endYear runtimeMinutes                    genres  \n",
       "0       0      1894      \\N              1         Documentary,Short  \n",
       "1       0      1892      \\N              5           Animation,Short  \n",
       "2       0      1892      \\N              4  Animation,Comedy,Romance  \n",
       "3       0      1892      \\N             12           Animation,Short  \n",
       "4       0      1893      \\N              1              Comedy,Short  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "basics_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "73ea15b5",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Replace \"\\N\" with np.nan\n",
    "basics_df.replace({'\\\\N':np.nan},inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "c8f9c55b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Eliminate movies that are missing values for runtimeMinutes, genres, startYear\n",
    "basics_df = basics_df.dropna(subset = ['runtimeMinutes', 'genres', 'startYear'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "7bc7710e",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Keep titleType movie\n",
    "basics_df = basics_df.loc[basics_df['titleType'] == 'movie']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "e08146ef",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Keep startYear 2000-2022\n",
    "basics_df['startYear'] = basics_df['startYear'].astype(int)\n",
    "basics_df = basics_df.loc[(basics_df['startYear'] >= 2000) & (basics_df['startYear'] <=2021)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "1af27b54",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Eliminate movies that include \"Documentary\" in genre\n",
    "documentary_filter = basics_df['genres'].str.contains('documentary', case=False)\n",
    "basics_df = basics_df[~documentary_filter]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "b5300402",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Make directory\n",
    "os.makedirs('Data/',exist_ok=True) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "f91ea680",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['.ipynb_checkpoints',\n",
       " 'final_tmdb_data_2000.csv.gz',\n",
       " 'final_tmdb_data_2001.csv.gz',\n",
       " 'final_tmdb_data_2002.csv.gz',\n",
       " 'final_tmdb_data_2003.csv.gz',\n",
       " 'final_tmdb_data_2004.csv.gz',\n",
       " 'final_tmdb_data_2005.csv.gz',\n",
       " 'final_tmdb_data_2006.csv.gz',\n",
       " 'final_tmdb_data_2007.csv.gz',\n",
       " 'final_tmdb_data_2008.csv.gz',\n",
       " 'final_tmdb_data_2009.csv.gz',\n",
       " 'final_tmdb_data_2010.csv.gz',\n",
       " 'final_tmdb_data_2011.csv.gz',\n",
       " 'final_tmdb_data_2012.csv.gz',\n",
       " 'final_tmdb_data_2013.csv.gz',\n",
       " 'final_tmdb_data_2014.csv.gz',\n",
       " 'final_tmdb_data_2015.csv.gz',\n",
       " 'final_tmdb_data_2016.csv.gz',\n",
       " 'final_tmdb_data_2017.csv.gz',\n",
       " 'final_tmdb_data_2018.csv.gz',\n",
       " 'final_tmdb_data_2019.csv.gz',\n",
       " 'final_tmdb_data_2020.csv.gz',\n",
       " 'final_tmdb_data_2021.csv.gz',\n",
       " 'title_akas.csv.gz',\n",
       " 'title_basics.csv.gz',\n",
       " 'title_ratings.csv.gz',\n",
       " 'tmdb_api_results_2000.json',\n",
       " 'tmdb_api_results_2001.json',\n",
       " 'tmdb_api_results_2002.json',\n",
       " 'tmdb_api_results_2003.json',\n",
       " 'tmdb_api_results_2004.json',\n",
       " 'tmdb_api_results_2005.json',\n",
       " 'tmdb_api_results_2006.json',\n",
       " 'tmdb_api_results_2007.json',\n",
       " 'tmdb_api_results_2008.json',\n",
       " 'tmdb_api_results_2009.json',\n",
       " 'tmdb_api_results_2010.json',\n",
       " 'tmdb_api_results_2011.json',\n",
       " 'tmdb_api_results_2012.json',\n",
       " 'tmdb_api_results_2013.json',\n",
       " 'tmdb_api_results_2014.json',\n",
       " 'tmdb_api_results_2015.json',\n",
       " 'tmdb_api_results_2016.json',\n",
       " 'tmdb_api_results_2017.json',\n",
       " 'tmdb_api_results_2018.json',\n",
       " 'tmdb_api_results_2019.json',\n",
       " 'tmdb_api_results_2020.json',\n",
       " 'tmdb_api_results_2021.json',\n",
       " 'tmdb_results_combined.csv.gz',\n",
       " 'tmdb_results_combined_final_df.csv.gz']"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Confirm directory created\n",
    "os.listdir(\"Data/\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "d5a583e2",
   "metadata": {},
   "outputs": [],
   "source": [
    "## Save dataframe to file\n",
    "basics_df.to_csv(\"Data/title_basics.csv.gz\", compression='gzip', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "f12293d1",
   "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>tconst</th>\n",
       "      <th>titleType</th>\n",
       "      <th>primaryTitle</th>\n",
       "      <th>originalTitle</th>\n",
       "      <th>isAdult</th>\n",
       "      <th>startYear</th>\n",
       "      <th>endYear</th>\n",
       "      <th>runtimeMinutes</th>\n",
       "      <th>genres</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>tt0035423</td>\n",
       "      <td>movie</td>\n",
       "      <td>Kate &amp; Leopold</td>\n",
       "      <td>Kate &amp; Leopold</td>\n",
       "      <td>0</td>\n",
       "      <td>2001</td>\n",
       "      <td>NaN</td>\n",
       "      <td>118</td>\n",
       "      <td>Comedy,Fantasy,Romance</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>tt0062336</td>\n",
       "      <td>movie</td>\n",
       "      <td>The Tango of the Widower and Its Distorting Mi...</td>\n",
       "      <td>El Tango del Viudo y Su Espejo Deformante</td>\n",
       "      <td>0</td>\n",
       "      <td>2020</td>\n",
       "      <td>NaN</td>\n",
       "      <td>70</td>\n",
       "      <td>Drama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>tt0069049</td>\n",
       "      <td>movie</td>\n",
       "      <td>The Other Side of the Wind</td>\n",
       "      <td>The Other Side of the Wind</td>\n",
       "      <td>0</td>\n",
       "      <td>2018</td>\n",
       "      <td>NaN</td>\n",
       "      <td>122</td>\n",
       "      <td>Drama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>tt0079644</td>\n",
       "      <td>movie</td>\n",
       "      <td>November 1828</td>\n",
       "      <td>November 1828</td>\n",
       "      <td>0</td>\n",
       "      <td>2001</td>\n",
       "      <td>NaN</td>\n",
       "      <td>140</td>\n",
       "      <td>Drama,War</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>tt0088751</td>\n",
       "      <td>movie</td>\n",
       "      <td>The Naked Monster</td>\n",
       "      <td>The Naked Monster</td>\n",
       "      <td>0</td>\n",
       "      <td>2005</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100</td>\n",
       "      <td>Comedy,Horror,Sci-Fi</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      tconst titleType                                       primaryTitle  \\\n",
       "0  tt0035423     movie                                     Kate & Leopold   \n",
       "1  tt0062336     movie  The Tango of the Widower and Its Distorting Mi...   \n",
       "2  tt0069049     movie                         The Other Side of the Wind   \n",
       "3  tt0079644     movie                                      November 1828   \n",
       "4  tt0088751     movie                                  The Naked Monster   \n",
       "\n",
       "                               originalTitle  isAdult  startYear  endYear  \\\n",
       "0                             Kate & Leopold        0       2001      NaN   \n",
       "1  El Tango del Viudo y Su Espejo Deformante        0       2020      NaN   \n",
       "2                 The Other Side of the Wind        0       2018      NaN   \n",
       "3                              November 1828        0       2001      NaN   \n",
       "4                          The Naked Monster        0       2005      NaN   \n",
       "\n",
       "   runtimeMinutes                  genres  \n",
       "0             118  Comedy,Fantasy,Romance  \n",
       "1              70                   Drama  \n",
       "2             122                   Drama  \n",
       "3             140               Drama,War  \n",
       "4             100    Comedy,Horror,Sci-Fi  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Open saved file and preview again\n",
    "basics_df = pd.read_csv(\"Data/title_basics.csv.gz\", low_memory=False)\n",
    "basics_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "2c08e1ac",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 135783 entries, 0 to 135782\n",
      "Data columns (total 9 columns):\n",
      " #   Column          Non-Null Count   Dtype  \n",
      "---  ------          --------------   -----  \n",
      " 0   tconst          135783 non-null  object \n",
      " 1   titleType       135783 non-null  object \n",
      " 2   primaryTitle    135783 non-null  object \n",
      " 3   originalTitle   135783 non-null  object \n",
      " 4   isAdult         135783 non-null  int64  \n",
      " 5   startYear       135783 non-null  int64  \n",
      " 6   endYear         0 non-null       float64\n",
      " 7   runtimeMinutes  135783 non-null  int64  \n",
      " 8   genres          135783 non-null  object \n",
      "dtypes: float64(1), int64(3), object(5)\n",
      "memory usage: 9.3+ MB\n"
     ]
    }
   ],
   "source": [
    "basics_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "96597141",
   "metadata": {},
   "outputs": [],
   "source": [
    "del basics_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "277fdca6",
   "metadata": {},
   "source": [
    "#### **Title Ratings**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a14e83d4",
   "metadata": {},
   "source": [
    "- Keep only US entries.\n",
    "- Replace \"\\N\" with np.nan"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "3ba1969c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create dataframe\n",
    "ratings_df = pd.read_csv(ratings_url,sep='\\t', low_memory=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "ce534fa3",
   "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>tconst</th>\n",
       "      <th>averageRating</th>\n",
       "      <th>numVotes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>tt0000001</td>\n",
       "      <td>5.7</td>\n",
       "      <td>1879</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>tt0000002</td>\n",
       "      <td>5.9</td>\n",
       "      <td>249</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>tt0000003</td>\n",
       "      <td>6.5</td>\n",
       "      <td>1654</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>tt0000004</td>\n",
       "      <td>5.8</td>\n",
       "      <td>162</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>tt0000005</td>\n",
       "      <td>6.2</td>\n",
       "      <td>2479</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      tconst  averageRating  numVotes\n",
       "0  tt0000001            5.7      1879\n",
       "1  tt0000002            5.9       249\n",
       "2  tt0000003            6.5      1654\n",
       "3  tt0000004            5.8       162\n",
       "4  tt0000005            6.2      2479"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ratings_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "662df5ce",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Replace \"\\N\" with np.nan\n",
    "ratings_df.replace({'\\\\N':np.nan},inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "76be5a08",
   "metadata": {},
   "outputs": [],
   "source": [
    "## Save dataframe to file\n",
    "ratings_df.to_csv(\"Data/title_ratings.csv.gz\", compression='gzip', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "92cde7d9",
   "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>tconst</th>\n",
       "      <th>averageRating</th>\n",
       "      <th>numVotes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>tt0000001</td>\n",
       "      <td>5.7</td>\n",
       "      <td>1879</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>tt0000002</td>\n",
       "      <td>5.9</td>\n",
       "      <td>249</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>tt0000003</td>\n",
       "      <td>6.5</td>\n",
       "      <td>1654</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>tt0000004</td>\n",
       "      <td>5.8</td>\n",
       "      <td>162</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>tt0000005</td>\n",
       "      <td>6.2</td>\n",
       "      <td>2479</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      tconst  averageRating  numVotes\n",
       "0  tt0000001            5.7      1879\n",
       "1  tt0000002            5.9       249\n",
       "2  tt0000003            6.5      1654\n",
       "3  tt0000004            5.8       162\n",
       "4  tt0000005            6.2      2479"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Open saved file and preview again\n",
    "ratings_df = pd.read_csv(\"Data/title_ratings.csv.gz\", low_memory=False)\n",
    "ratings_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "83815537",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1244712 entries, 0 to 1244711\n",
      "Data columns (total 3 columns):\n",
      " #   Column         Non-Null Count    Dtype  \n",
      "---  ------         --------------    -----  \n",
      " 0   tconst         1244712 non-null  object \n",
      " 1   averageRating  1244712 non-null  float64\n",
      " 2   numVotes       1244712 non-null  int64  \n",
      "dtypes: float64(1), int64(1), object(1)\n",
      "memory usage: 28.5+ MB\n"
     ]
    }
   ],
   "source": [
    "ratings_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "057948f2",
   "metadata": {},
   "outputs": [],
   "source": [
    "del ratings_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a8f1cc26",
   "metadata": {},
   "source": [
    "#### **Title Akas**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2686fb9c",
   "metadata": {},
   "source": [
    "- Replace \"\\N\" with np.nan (if any)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "878cbedb",
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\DELL\\anaconda3\\envs\\dojo-env\\lib\\site-packages\\IPython\\core\\interactiveshell.py:3524: DtypeWarning: Columns (7) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  exec(code_obj, self.user_global_ns, self.user_ns)\n"
     ]
    }
   ],
   "source": [
    "# Create dataframe # chunksize creates an iterator\n",
    "dl_file = \"C:/Users/DELL/Downloads/title.akas.tsv.gz\"\n",
    "akas_df = pd.read_csv(dl_file,sep='\\t', low_memory=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "7620f816",
   "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>titleId</th>\n",
       "      <th>ordering</th>\n",
       "      <th>title</th>\n",
       "      <th>region</th>\n",
       "      <th>language</th>\n",
       "      <th>types</th>\n",
       "      <th>attributes</th>\n",
       "      <th>isOriginalTitle</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>tt0000001</td>\n",
       "      <td>1</td>\n",
       "      <td>Карменсіта</td>\n",
       "      <td>UA</td>\n",
       "      <td>\\N</td>\n",
       "      <td>imdbDisplay</td>\n",
       "      <td>\\N</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>tt0000001</td>\n",
       "      <td>2</td>\n",
       "      <td>Carmencita</td>\n",
       "      <td>DE</td>\n",
       "      <td>\\N</td>\n",
       "      <td>\\N</td>\n",
       "      <td>literal title</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>tt0000001</td>\n",
       "      <td>3</td>\n",
       "      <td>Carmencita - spanyol tánc</td>\n",
       "      <td>HU</td>\n",
       "      <td>\\N</td>\n",
       "      <td>imdbDisplay</td>\n",
       "      <td>\\N</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>tt0000001</td>\n",
       "      <td>4</td>\n",
       "      <td>Καρμενσίτα</td>\n",
       "      <td>GR</td>\n",
       "      <td>\\N</td>\n",
       "      <td>imdbDisplay</td>\n",
       "      <td>\\N</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>tt0000001</td>\n",
       "      <td>5</td>\n",
       "      <td>Карменсита</td>\n",
       "      <td>RU</td>\n",
       "      <td>\\N</td>\n",
       "      <td>imdbDisplay</td>\n",
       "      <td>\\N</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     titleId  ordering                      title region language  \\\n",
       "0  tt0000001         1                 Карменсіта     UA       \\N   \n",
       "1  tt0000001         2                 Carmencita     DE       \\N   \n",
       "2  tt0000001         3  Carmencita - spanyol tánc     HU       \\N   \n",
       "3  tt0000001         4                 Καρμενσίτα     GR       \\N   \n",
       "4  tt0000001         5                 Карменсита     RU       \\N   \n",
       "\n",
       "         types     attributes isOriginalTitle  \n",
       "0  imdbDisplay             \\N               0  \n",
       "1           \\N  literal title               0  \n",
       "2  imdbDisplay             \\N               0  \n",
       "3  imdbDisplay             \\N               0  \n",
       "4  imdbDisplay             \\N               0  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "akas_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "3ec6be63",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Replace \"\\N\" with np.nan\n",
    "akas_df.replace({'\\\\N':np.nan},inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "9daa1c05",
   "metadata": {},
   "outputs": [],
   "source": [
    "akas_df = akas_df.loc[akas_df['region'] =='US']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "9eddc2ba",
   "metadata": {},
   "outputs": [],
   "source": [
    "## Save dataframe to file\n",
    "akas_df.to_csv(\"Data/title_akas.csv.gz\", compression='gzip', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "7a65b0bf",
   "metadata": {
    "scrolled": false
   },
   "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>titleId</th>\n",
       "      <th>ordering</th>\n",
       "      <th>title</th>\n",
       "      <th>region</th>\n",
       "      <th>language</th>\n",
       "      <th>types</th>\n",
       "      <th>attributes</th>\n",
       "      <th>isOriginalTitle</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>tt0000001</td>\n",
       "      <td>6</td>\n",
       "      <td>Carmencita</td>\n",
       "      <td>US</td>\n",
       "      <td>NaN</td>\n",
       "      <td>imdbDisplay</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>tt0000002</td>\n",
       "      <td>7</td>\n",
       "      <td>The Clown and His Dogs</td>\n",
       "      <td>US</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>literal English title</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>tt0000005</td>\n",
       "      <td>10</td>\n",
       "      <td>Blacksmith Scene</td>\n",
       "      <td>US</td>\n",
       "      <td>NaN</td>\n",
       "      <td>imdbDisplay</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>tt0000005</td>\n",
       "      <td>1</td>\n",
       "      <td>Blacksmithing Scene</td>\n",
       "      <td>US</td>\n",
       "      <td>NaN</td>\n",
       "      <td>alternative</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>tt0000005</td>\n",
       "      <td>6</td>\n",
       "      <td>Blacksmith Scene #1</td>\n",
       "      <td>US</td>\n",
       "      <td>NaN</td>\n",
       "      <td>alternative</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     titleId  ordering                   title region language        types  \\\n",
       "0  tt0000001         6              Carmencita     US      NaN  imdbDisplay   \n",
       "1  tt0000002         7  The Clown and His Dogs     US      NaN          NaN   \n",
       "2  tt0000005        10        Blacksmith Scene     US      NaN  imdbDisplay   \n",
       "3  tt0000005         1     Blacksmithing Scene     US      NaN  alternative   \n",
       "4  tt0000005         6     Blacksmith Scene #1     US      NaN  alternative   \n",
       "\n",
       "              attributes  isOriginalTitle  \n",
       "0                    NaN              0.0  \n",
       "1  literal English title              0.0  \n",
       "2                    NaN              0.0  \n",
       "3                    NaN              0.0  \n",
       "4                    NaN              0.0  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Open saved file and preview again\n",
    "akas_df = pd.read_csv(\"Data/title_akas.csv.gz\", low_memory=True)\n",
    "akas_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "6679217b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1316613 entries, 0 to 1316612\n",
      "Data columns (total 8 columns):\n",
      " #   Column           Non-Null Count    Dtype  \n",
      "---  ------           --------------    -----  \n",
      " 0   titleId          1316613 non-null  object \n",
      " 1   ordering         1316613 non-null  int64  \n",
      " 2   title            1316613 non-null  object \n",
      " 3   region           1316613 non-null  object \n",
      " 4   language         3508 non-null     object \n",
      " 5   types            1023499 non-null  object \n",
      " 6   attributes       43847 non-null    object \n",
      " 7   isOriginalTitle  1315238 non-null  float64\n",
      "dtypes: float64(1), int64(1), object(6)\n",
      "memory usage: 80.4+ MB\n"
     ]
    }
   ],
   "source": [
    "akas_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "81873e49",
   "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>tconst</th>\n",
       "      <th>titleType</th>\n",
       "      <th>primaryTitle</th>\n",
       "      <th>originalTitle</th>\n",
       "      <th>isAdult</th>\n",
       "      <th>startYear</th>\n",
       "      <th>endYear</th>\n",
       "      <th>runtimeMinutes</th>\n",
       "      <th>genres</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>tt0035423</td>\n",
       "      <td>movie</td>\n",
       "      <td>Kate &amp; Leopold</td>\n",
       "      <td>Kate &amp; Leopold</td>\n",
       "      <td>0</td>\n",
       "      <td>2001</td>\n",
       "      <td>NaN</td>\n",
       "      <td>118</td>\n",
       "      <td>Comedy,Fantasy,Romance</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>tt0062336</td>\n",
       "      <td>movie</td>\n",
       "      <td>The Tango of the Widower and Its Distorting Mi...</td>\n",
       "      <td>El Tango del Viudo y Su Espejo Deformante</td>\n",
       "      <td>0</td>\n",
       "      <td>2020</td>\n",
       "      <td>NaN</td>\n",
       "      <td>70</td>\n",
       "      <td>Drama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>tt0069049</td>\n",
       "      <td>movie</td>\n",
       "      <td>The Other Side of the Wind</td>\n",
       "      <td>The Other Side of the Wind</td>\n",
       "      <td>0</td>\n",
       "      <td>2018</td>\n",
       "      <td>NaN</td>\n",
       "      <td>122</td>\n",
       "      <td>Drama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>tt0079644</td>\n",
       "      <td>movie</td>\n",
       "      <td>November 1828</td>\n",
       "      <td>November 1828</td>\n",
       "      <td>0</td>\n",
       "      <td>2001</td>\n",
       "      <td>NaN</td>\n",
       "      <td>140</td>\n",
       "      <td>Drama,War</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>tt0088751</td>\n",
       "      <td>movie</td>\n",
       "      <td>The Naked Monster</td>\n",
       "      <td>The Naked Monster</td>\n",
       "      <td>0</td>\n",
       "      <td>2005</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100</td>\n",
       "      <td>Comedy,Horror,Sci-Fi</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      tconst titleType                                       primaryTitle  \\\n",
       "0  tt0035423     movie                                     Kate & Leopold   \n",
       "1  tt0062336     movie  The Tango of the Widower and Its Distorting Mi...   \n",
       "2  tt0069049     movie                         The Other Side of the Wind   \n",
       "3  tt0079644     movie                                      November 1828   \n",
       "4  tt0088751     movie                                  The Naked Monster   \n",
       "\n",
       "                               originalTitle  isAdult  startYear  endYear  \\\n",
       "0                             Kate & Leopold        0       2001      NaN   \n",
       "1  El Tango del Viudo y Su Espejo Deformante        0       2020      NaN   \n",
       "2                 The Other Side of the Wind        0       2018      NaN   \n",
       "3                              November 1828        0       2001      NaN   \n",
       "4                          The Naked Monster        0       2005      NaN   \n",
       "\n",
       "   runtimeMinutes                  genres  \n",
       "0             118  Comedy,Fantasy,Romance  \n",
       "1              70                   Drama  \n",
       "2             122                   Drama  \n",
       "3             140               Drama,War  \n",
       "4             100    Comedy,Horror,Sci-Fi  "
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Open saved file and preview again\n",
    "basics_df = pd.read_csv(\"Data/title_basics.csv.gz\", low_memory=False)\n",
    "basics_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "9bb2815e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0          True\n",
       "1          True\n",
       "2          True\n",
       "3         False\n",
       "4          True\n",
       "          ...  \n",
       "135778     True\n",
       "135779     True\n",
       "135780    False\n",
       "135781     True\n",
       "135782    False\n",
       "Name: tconst, Length: 135783, dtype: bool"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Filter the basics table down to only include the US by using the filter akas dataframe\n",
    "us_filter = basics_df['tconst'].isin(akas_df['titleId'])\n",
    "us_filter"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "247a14fe",
   "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>tconst</th>\n",
       "      <th>titleType</th>\n",
       "      <th>primaryTitle</th>\n",
       "      <th>originalTitle</th>\n",
       "      <th>isAdult</th>\n",
       "      <th>startYear</th>\n",
       "      <th>endYear</th>\n",
       "      <th>runtimeMinutes</th>\n",
       "      <th>genres</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>tt0035423</td>\n",
       "      <td>movie</td>\n",
       "      <td>Kate &amp; Leopold</td>\n",
       "      <td>Kate &amp; Leopold</td>\n",
       "      <td>0</td>\n",
       "      <td>2001</td>\n",
       "      <td>NaN</td>\n",
       "      <td>118</td>\n",
       "      <td>Comedy,Fantasy,Romance</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>tt0062336</td>\n",
       "      <td>movie</td>\n",
       "      <td>The Tango of the Widower and Its Distorting Mi...</td>\n",
       "      <td>El Tango del Viudo y Su Espejo Deformante</td>\n",
       "      <td>0</td>\n",
       "      <td>2020</td>\n",
       "      <td>NaN</td>\n",
       "      <td>70</td>\n",
       "      <td>Drama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>tt0069049</td>\n",
       "      <td>movie</td>\n",
       "      <td>The Other Side of the Wind</td>\n",
       "      <td>The Other Side of the Wind</td>\n",
       "      <td>0</td>\n",
       "      <td>2018</td>\n",
       "      <td>NaN</td>\n",
       "      <td>122</td>\n",
       "      <td>Drama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>tt0088751</td>\n",
       "      <td>movie</td>\n",
       "      <td>The Naked Monster</td>\n",
       "      <td>The Naked Monster</td>\n",
       "      <td>0</td>\n",
       "      <td>2005</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100</td>\n",
       "      <td>Comedy,Horror,Sci-Fi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>tt0093119</td>\n",
       "      <td>movie</td>\n",
       "      <td>Grizzly II: Revenge</td>\n",
       "      <td>Grizzly II: The Predator</td>\n",
       "      <td>0</td>\n",
       "      <td>2020</td>\n",
       "      <td>NaN</td>\n",
       "      <td>74</td>\n",
       "      <td>Horror,Music,Thriller</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>135776</th>\n",
       "      <td>tt9914942</td>\n",
       "      <td>movie</td>\n",
       "      <td>Life Without Sara Amat</td>\n",
       "      <td>La vida sense la Sara Amat</td>\n",
       "      <td>0</td>\n",
       "      <td>2019</td>\n",
       "      <td>NaN</td>\n",
       "      <td>74</td>\n",
       "      <td>Drama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>135777</th>\n",
       "      <td>tt9915872</td>\n",
       "      <td>movie</td>\n",
       "      <td>The Last White Witch</td>\n",
       "      <td>My Girlfriend is a Wizard</td>\n",
       "      <td>0</td>\n",
       "      <td>2019</td>\n",
       "      <td>NaN</td>\n",
       "      <td>97</td>\n",
       "      <td>Comedy,Drama,Fantasy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>135778</th>\n",
       "      <td>tt9916170</td>\n",
       "      <td>movie</td>\n",
       "      <td>The Rehearsal</td>\n",
       "      <td>O Ensaio</td>\n",
       "      <td>0</td>\n",
       "      <td>2019</td>\n",
       "      <td>NaN</td>\n",
       "      <td>51</td>\n",
       "      <td>Drama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>135779</th>\n",
       "      <td>tt9916190</td>\n",
       "      <td>movie</td>\n",
       "      <td>Safeguard</td>\n",
       "      <td>Safeguard</td>\n",
       "      <td>0</td>\n",
       "      <td>2020</td>\n",
       "      <td>NaN</td>\n",
       "      <td>90</td>\n",
       "      <td>Action,Adventure,Thriller</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>135781</th>\n",
       "      <td>tt9916362</td>\n",
       "      <td>movie</td>\n",
       "      <td>Coven</td>\n",
       "      <td>Akelarre</td>\n",
       "      <td>0</td>\n",
       "      <td>2020</td>\n",
       "      <td>NaN</td>\n",
       "      <td>92</td>\n",
       "      <td>Drama,History</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>79187 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           tconst titleType  \\\n",
       "0       tt0035423     movie   \n",
       "1       tt0062336     movie   \n",
       "2       tt0069049     movie   \n",
       "4       tt0088751     movie   \n",
       "7       tt0093119     movie   \n",
       "...           ...       ...   \n",
       "135776  tt9914942     movie   \n",
       "135777  tt9915872     movie   \n",
       "135778  tt9916170     movie   \n",
       "135779  tt9916190     movie   \n",
       "135781  tt9916362     movie   \n",
       "\n",
       "                                             primaryTitle  \\\n",
       "0                                          Kate & Leopold   \n",
       "1       The Tango of the Widower and Its Distorting Mi...   \n",
       "2                              The Other Side of the Wind   \n",
       "4                                       The Naked Monster   \n",
       "7                                     Grizzly II: Revenge   \n",
       "...                                                   ...   \n",
       "135776                             Life Without Sara Amat   \n",
       "135777                               The Last White Witch   \n",
       "135778                                      The Rehearsal   \n",
       "135779                                          Safeguard   \n",
       "135781                                              Coven   \n",
       "\n",
       "                                    originalTitle  isAdult  startYear  \\\n",
       "0                                  Kate & Leopold        0       2001   \n",
       "1       El Tango del Viudo y Su Espejo Deformante        0       2020   \n",
       "2                      The Other Side of the Wind        0       2018   \n",
       "4                               The Naked Monster        0       2005   \n",
       "7                        Grizzly II: The Predator        0       2020   \n",
       "...                                           ...      ...        ...   \n",
       "135776                 La vida sense la Sara Amat        0       2019   \n",
       "135777                  My Girlfriend is a Wizard        0       2019   \n",
       "135778                                   O Ensaio        0       2019   \n",
       "135779                                  Safeguard        0       2020   \n",
       "135781                                   Akelarre        0       2020   \n",
       "\n",
       "        endYear  runtimeMinutes                     genres  \n",
       "0           NaN             118     Comedy,Fantasy,Romance  \n",
       "1           NaN              70                      Drama  \n",
       "2           NaN             122                      Drama  \n",
       "4           NaN             100       Comedy,Horror,Sci-Fi  \n",
       "7           NaN              74      Horror,Music,Thriller  \n",
       "...         ...             ...                        ...  \n",
       "135776      NaN              74                      Drama  \n",
       "135777      NaN              97       Comedy,Drama,Fantasy  \n",
       "135778      NaN              51                      Drama  \n",
       "135779      NaN              90  Action,Adventure,Thriller  \n",
       "135781      NaN              92              Drama,History  \n",
       "\n",
       "[79187 rows x 9 columns]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "basics_df = basics_df[us_filter]\n",
    "basics_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "00a7e49b",
   "metadata": {},
   "outputs": [],
   "source": [
    "## Save dataframe to file\n",
    "basics_df.to_csv(\"Data/title_basics.csv.gz\", compression='gzip', index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python (dojo-env)",
   "language": "python",
   "name": "dojo-env"
  },
  "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.7.12"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {
    "height": "calc(100% - 180px)",
    "left": "10px",
    "top": "150px",
    "width": "273.188px"
   },
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}