ETL_create_database.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "import re\n",
    "\n",
    "from sqlalchemy import create_engine\n",
    "import psycopg2\n",
    "\n",
    "from config import db_password\n",
    "import time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "#  Add the clean movie function that takes in the argument, \"movie\".\n",
    "def clean_movie(movie):\n",
    "    movie = dict(movie) #create a non-destructive copy\n",
    "    alt_titles = {}\n",
    "    # combine alternate titles into one list\n",
    "    for key in ['Also known as','Arabic','Cantonese','Chinese','French',\n",
    "                'Hangul','Hebrew','Hepburn','Japanese','Literally',\n",
    "                'Mandarin','McCune-Reischauer','Original title','Polish',\n",
    "                'Revised Romanization','Romanized','Russian',\n",
    "                'Simplified','Traditional','Yiddish']:\n",
    "        if key in movie:\n",
    "            alt_titles[key] = movie[key]\n",
    "            movie.pop(key)\n",
    "    if len(alt_titles) > 0:\n",
    "        movie['alt_titles'] = alt_titles\n",
    "\n",
    "    # merge column names\n",
    "    def change_column_name(old_name, new_name):\n",
    "        if old_name in movie:\n",
    "            movie[new_name] = movie.pop(old_name)\n",
    "    change_column_name('Adaptation by', 'Writer(s)')\n",
    "    change_column_name('Country of origin', 'Country')\n",
    "    change_column_name('Directed by', 'Director')\n",
    "    change_column_name('Distributed by', 'Distributor')\n",
    "    change_column_name('Edited by', 'Editor(s)')\n",
    "    change_column_name('Length', 'Running time')\n",
    "    change_column_name('Original release', 'Release date')\n",
    "    change_column_name('Music by', 'Composer(s)')\n",
    "    change_column_name('Produced by', 'Producer(s)')\n",
    "    change_column_name('Producer', 'Producer(s)')\n",
    "    change_column_name('Productioncompanies ', 'Production company(s)')\n",
    "    change_column_name('Productioncompany ', 'Production company(s)')\n",
    "    change_column_name('Released', 'Release Date')\n",
    "    change_column_name('Release Date', 'Release date')\n",
    "    change_column_name('Screen story by', 'Writer(s)')\n",
    "    change_column_name('Screenplay by', 'Writer(s)')\n",
    "    change_column_name('Story by', 'Writer(s)')\n",
    "    change_column_name('Theme music composer', 'Composer(s)')\n",
    "    change_column_name('Written by', 'Writer(s)')\n",
    "\n",
    "    return movie"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 1 Add the function that takes in three arguments;\n",
    "# Wikipedia data, Kaggle metadata, and MovieLens rating data (from Kaggle)\n",
    "\n",
    "def Deep_Clean(kaggle_file, ratings_file, wiki_file):\n",
    "    \n",
    "    # Read in the kaggle metadata and MovieLens ratings CSV files as Pandas DataFrames.\n",
    "    kaggle_metadata = pd.read_csv(kaggle_file)\n",
    "    ratings = pd.read_csv(ratings_file, low_memory=False)\n",
    "    \n",
    "    # Open and read the Wikipedia data JSON file.\n",
    "    with open((wiki_file),mode='r') as file:\n",
    "        wiki_movies_raw = json.load(file) \n",
    "        \n",
    "    # Write a list comprehension to filter out TV shows.\n",
    "    cleaned_wiki_movies_raw = [movie for movie in wiki_movies_raw if 'No. of episodes' not in movie]\n",
    "\n",
    "    # Write a list comprehension to iterate through the cleaned wiki movies list\n",
    "    # and call the clean_movie function on each movie.\n",
    "    clean_wikiraw_for_df = [clean_movie(movie) for movie in cleaned_wiki_movies_raw ]\n",
    "\n",
    "    # Read in the cleaned movies list from Step 4 as a DataFrame.\n",
    "    wiki_movies_df = pd.DataFrame(clean_wikiraw_for_df)\n",
    "\n",
    "    # Write a try-except block to catch errors while extracting the IMDb ID using a regular expression string and\n",
    "    #  dropping any imdb_id duplicates. If there is an error, capture and print the exception.\n",
    "    try:\n",
    "        wiki_movies_df['imdb_id'] = wiki_movies_df['imdb_link'].str.extract(r'(tt\\d{7})')\n",
    "        wiki_movies_df.drop_duplicates(subset='imdb_id', inplace=True)\n",
    "    except Exception as e:\n",
    "        print(f'An error ocurred: ' + str(e))\n",
    "\n",
    "    # Write a list comprehension to keep the columns that don't have null values from the wiki_movies_df DataFrame.\n",
    "    wiki_columns_to_keep = [column for column in wiki_movies_df.columns if wiki_movies_df[column].isnull().sum() < len(wiki_movies_df) * 0.9]\n",
    "    wiki_movies_df = wiki_movies_df[wiki_columns_to_keep]\n",
    "    \n",
    "    # Create a variable that will hold the non-null values from the “Box office” column.\n",
    "    box_office = wiki_movies_df['Box office'].dropna()\n",
    "    \n",
    "    # Convert the box office data created in Step 8 to string values using the lambda and join functions.\n",
    "    box_office = box_office.apply(lambda x: ' '.join(x) if type(x) == list else x)\n",
    "\n",
    "    # Write a regular expression to match the six elements of \"form_one\" of the box office data.\n",
    "    form_one = r'\\$\\s*\\d+\\.?\\d*\\s*[mb]illi?on'\n",
    "    # Write a regular expression to match the three elements of \"form_two\" of the box office data.\n",
    "    form_two = r'\\$\\s*\\d{1,3}(?:[,\\.]\\d{3})+(?!\\s[mb]illion)'\n",
    "\n",
    "    # Add the parse_dollars function.\n",
    "    def parse_dollars(s):\n",
    "        # if s is not a string, return NaN\n",
    "        if type(s) != str:\n",
    "            return np.nan\n",
    "        # if input is of the form $###.# million\n",
    "        if re.match(r'\\$\\s*\\d+\\.?\\d*\\s*milli?on', s, flags=re.IGNORECASE):\n",
    "            # remove dollar sign and \" million\"\n",
    "            s = re.sub('\\$|\\s|[a-zA-Z]','', s)\n",
    "            # convert to float and multiply by a million\n",
    "            value = float(s) * 10**6\n",
    "            # return value\n",
    "            return value\n",
    "        # if input is of the form $###.# billion\n",
    "        elif re.match(r'\\$\\s*\\d+\\.?\\d*\\s*billi?on', s, flags=re.IGNORECASE):\n",
    "            # remove dollar sign and \" billion\"\n",
    "            s = re.sub('\\$|\\s|[a-zA-Z]','', s)\n",
    "            # convert to float and multiply by a billion\n",
    "            value = float(s) * 10**9\n",
    "            # return value\n",
    "            return value\n",
    "        # if input is of the form $###,###,###\n",
    "        elif re.match(r'\\$\\s*\\d{1,3}(?:[,\\.]\\d{3})+(?!\\s[mb]illion)', s, flags=re.IGNORECASE):\n",
    "            # remove dollar sign and commas\n",
    "            s = re.sub('\\$|,','', s)\n",
    "            # convert to float\n",
    "            value = float(s)\n",
    "            # return value\n",
    "            return value\n",
    "        # otherwise, return NaN\n",
    "        else:\n",
    "            return np.nan\n",
    "    # Clean the box office column in the wiki_movies_df DataFrame.\n",
    "    wiki_movies_df['box_office'] = box_office.str.extract(f'({form_one}|{form_two})',\n",
    "                                                          flags=re.IGNORECASE)[0].apply(parse_dollars)\n",
    "    wiki_movies_df = wiki_movies_df.drop('Box office', axis='columns')\n",
    "    # Clean the budget column in the wiki_movies_df DataFrame.\n",
    "    budget = wiki_movies_df['Budget'].dropna()\n",
    "    budget = budget.map(lambda x: ' '.join(x) if type(x) == list else x)\n",
    "    budget = budget.str.replace(r'\\$.*[-—–](?![a-z])', '$', regex=True)\n",
    "    budget = budget.str.replace(r'\\[\\d+\\]\\s*', '')\n",
    "    wiki_movies_df['budget'] = budget.str.extract(f'({form_one}|{form_two})',\n",
    "                                                  flags=re.IGNORECASE)[0].apply(parse_dollars)\n",
    "    # Clean the release date column in the wiki_movies_df DataFrame.\n",
    "    release_date = wiki_movies_df['Release date'].dropna().apply(lambda x: ' '.join(x) if type(x) == list else x)\n",
    "    date_form_one = r'(?:January|February|March|April|May|June|July|August|September|October|November|December)\\s[123]\\d,\\s\\d{4}'\n",
    "    date_form_two = r'\\d{4}.[01]\\d.[123]\\d'\n",
    "    date_form_three = r'(?:January|February|March|April|May|June|July|August|September|October|November|December)\\s\\d{4}'\n",
    "    date_form_four = r'\\d{4}'\n",
    "    wiki_movies_df['release_date'] = pd.to_datetime(release_date.str.extract(f'({date_form_one}|{date_form_two}|{date_form_three}|{date_form_four})')[0],\n",
    "                                                    infer_datetime_format=True)\n",
    "    # Clean the running time column in the wiki_movies_df DataFrame.\n",
    "    running_time = wiki_movies_df['Running time'].dropna().apply(lambda x: ' '.join(x) if type(x) == list else x)\n",
    "    running_time_extract = running_time.str.extract(r'(\\d+)\\s*ho?u?r?s?\\s*(\\d*)|(\\d+)\\s*m')\n",
    "    running_time_extract = running_time_extract.apply(lambda col: pd.to_numeric(col, errors='coerce')).fillna(0)\n",
    "    wiki_movies_df['running_time'] = running_time_extract.apply(lambda row: row[0]*60 + row[1] if row[2] == 0 else row[2], axis=1)\n",
    "    \n",
    "    wiki_movies_df = wiki_movies_df.drop('Running time', axis='columns')\n",
    "    \n",
    "    \n",
    "    # 2. Clean the Kaggle metadata.\n",
    "    # Exclude adult movies\n",
    "    kaggle_metadata = kaggle_metadata[kaggle_metadata['adult'] == 'False'].drop('adult',axis='columns')\n",
    "    \n",
    "    # Convert video column to boolean dtype\n",
    "    kaggle_metadata['video'] = kaggle_metadata['video'] == 'True'\n",
    "    \n",
    "    # Convert budget, id, and popularity columns into numeric dtype\n",
    "    kaggle_metadata['budget'] = kaggle_metadata['budget'].astype(int)\n",
    "    kaggle_metadata['id'] = pd.to_numeric(kaggle_metadata['id'], errors='raise')\n",
    "    kaggle_metadata['popularity'] = pd.to_numeric(kaggle_metadata['popularity'], errors='raise')\n",
    "    \n",
    "    # Convert the release date column into datetime dtype\n",
    "    kaggle_metadata['release_date'] = pd.to_datetime(kaggle_metadata['release_date'])\n",
    "        \n",
    "    \n",
    "    # 3. Merged the two DataFrames into the movies DataFrame.\n",
    "    movies_df = pd.merge(wiki_movies_df, kaggle_metadata, on='imdb_id', suffixes=['_wiki','_kaggle'])\n",
    "    # 4. Drop unnecessary columns from the merged DataFrame.\n",
    "    movies_df = movies_df.drop(columns=['title_wiki','release_date_wiki','Language','Production company(s)'])\n",
    "    # 5. Add in the function to fill in the missing Kaggle data.\n",
    "    def fill_missing_kaggle_data(df, kaggle_column, wiki_column):\n",
    "        df[kaggle_column] = df.apply(lambda row: row[wiki_column] if row[kaggle_column] == 0 else row[kaggle_column]\n",
    "        , axis=1)\n",
    "        df.drop(columns=wiki_column, inplace=True)\n",
    "        \n",
    "    # 6. Call the function in Step 5 with the DataFrame and columns as the arguments.\n",
    "    fill_missing_kaggle_data(movies_df, 'runtime', 'running_time')\n",
    "    fill_missing_kaggle_data(movies_df, 'budget_kaggle', 'budget_wiki')\n",
    "    fill_missing_kaggle_data(movies_df, 'revenue', 'box_office')\n",
    "\n",
    "    # 7. Filter the movies DataFrame for specific columns.\n",
    "    for col in movies_df.columns:\n",
    "        lists_to_tuples = lambda x: tuple(x) if type(x) == list else x\n",
    "        value_counts = movies_df[col].apply(lists_to_tuples).value_counts(dropna=False)\n",
    "        num_values = len(value_counts)\n",
    "        if num_values == 1:\n",
    "            movies_df = movies_df.drop(col, axis='columns')\n",
    "\n",
    "    # 8. Rename the columns in the movies DataFrame.\n",
    "    movies_df = movies_df.loc[:, ['imdb_id','id','title_kaggle','original_title','tagline','belongs_to_collection','url','imdb_link',\n",
    "                       'runtime','budget_kaggle','revenue','release_date_kaggle','popularity','vote_average','vote_count',\n",
    "                       'genres','original_language','overview','spoken_languages','Country',\n",
    "                       'production_companies','production_countries','Distributor',\n",
    "                       'Producer(s)','Director','Starring','Cinematography','Editor(s)','Writer(s)','Composer(s)','Based on'\n",
    "                      ]]\n",
    "    movies_df.rename({'id':'kaggle_id',\n",
    "                  'title_kaggle':'title',\n",
    "                  'url':'wikipedia_url',\n",
    "                  'budget_kaggle':'budget',\n",
    "                  'release_date_kaggle':'release_date',\n",
    "                  'Country':'country',\n",
    "                  'Distributor':'distributor',\n",
    "                  'Producer(s)':'producers',\n",
    "                  'Director':'director',\n",
    "                  'Starring':'starring',\n",
    "                  'Cinematography':'cinematography',\n",
    "                  'Editor(s)':'editors',\n",
    "                  'Writer(s)':'writers',\n",
    "                  'Composer(s)':'composers',\n",
    "                  'Based on':'based_on'\n",
    "                 }, axis='columns', inplace=True)\n",
    "\n",
    "    # 9. Transform and merge the ratings DataFrame.\n",
    "    # Transform ratings into pivot table\n",
    "    rating_counts = ratings.groupby(['movieId','rating'], as_index=False).count() \\\n",
    "                .rename({'userId':'count'}, axis=1) \\\n",
    "                .pivot(index='movieId',columns='rating', values='count')\n",
    "    # Rename top columns to know they are ratings\n",
    "    rating_counts.columns = ['rating_' + str(col) for col in rating_counts.columns]\n",
    "    \n",
    "    # Merge with movies df\n",
    "    movies_with_ratings_df = pd.merge(movies_df, rating_counts, left_on='kaggle_id', right_index=True, how='left')\n",
    "    # Fill NaNs with 0 for the ratings for better analysis\n",
    "    movies_with_ratings_df[rating_counts.columns] = movies_with_ratings_df[rating_counts.columns].fillna(0)\n",
    "    \n",
    "    return wiki_movies_df, movies_with_ratings_df, movies_df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 10. Create the path to your file directory and variables for the three files.\n",
    "file_dir = 'Original_Datasets'\n",
    "# The Wikipedia data\n",
    "wiki_file = f'{file_dir}/wikipedia-movies.json'\n",
    "# The Kaggle metadata\n",
    "kaggle_file = f'{file_dir}/movies_metadata.csv'\n",
    "# The MovieLens rating data.\n",
    "ratings_file = f'{file_dir}/ratings.csv'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/anaconda3/lib/python3.8/site-packages/IPython/core/interactiveshell.py:3338: DtypeWarning: Columns (10) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  if (await self.run_code(code, result,  async_=asy)):\n"
     ]
    }
   ],
   "source": [
    "# 11. Set the three variables equal to the function created in D1.\n",
    "wiki_movies, movies_with_ratings, merged_movies = Deep_Clean(kaggle_file, ratings_file, wiki_file)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 12. Set the DataFrames from the return statement equal to the file names in Step 11. \n",
    "wiki_movies_df = wiki_movies\n",
    "movies_with_ratings_df = movies_with_ratings\n",
    "movies_df = merged_movies"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>url</th>\n",
       "      <th>year</th>\n",
       "      <th>imdb_link</th>\n",
       "      <th>title</th>\n",
       "      <th>Based on</th>\n",
       "      <th>Starring</th>\n",
       "      <th>Cinematography</th>\n",
       "      <th>Release date</th>\n",
       "      <th>Country</th>\n",
       "      <th>Language</th>\n",
       "      <th>...</th>\n",
       "      <th>Editor(s)</th>\n",
       "      <th>Composer(s)</th>\n",
       "      <th>Producer(s)</th>\n",
       "      <th>Production company(s)</th>\n",
       "      <th>Writer(s)</th>\n",
       "      <th>imdb_id</th>\n",
       "      <th>box_office</th>\n",
       "      <th>budget</th>\n",
       "      <th>release_date</th>\n",
       "      <th>running_time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>https://en.wikipedia.org/wiki/The_Adventures_o...</td>\n",
       "      <td>1990.0</td>\n",
       "      <td>https://www.imdb.com/title/tt0098987/</td>\n",
       "      <td>The Adventures of Ford Fairlane</td>\n",
       "      <td>[Characters, by Rex Weiner]</td>\n",
       "      <td>[Andrew Dice Clay, Wayne Newton, Priscilla Pre...</td>\n",
       "      <td>Oliver Wood</td>\n",
       "      <td>[July 11, 1990, (, 1990-07-11, )]</td>\n",
       "      <td>United States</td>\n",
       "      <td>English</td>\n",
       "      <td>...</td>\n",
       "      <td>Michael Tronick</td>\n",
       "      <td>[Cliff Eidelman, Yello]</td>\n",
       "      <td>[Steve Perry, Joel Silver]</td>\n",
       "      <td>Silver Pictures</td>\n",
       "      <td>[David Arnott, James Cappe]</td>\n",
       "      <td>tt0098987</td>\n",
       "      <td>21400000.0</td>\n",
       "      <td>20000000.0</td>\n",
       "      <td>1990-07-11</td>\n",
       "      <td>102.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>https://en.wikipedia.org/wiki/After_Dark,_My_S...</td>\n",
       "      <td>1990.0</td>\n",
       "      <td>https://www.imdb.com/title/tt0098994/</td>\n",
       "      <td>After Dark, My Sweet</td>\n",
       "      <td>[the novel, After Dark, My Sweet, by, Jim Thom...</td>\n",
       "      <td>[Jason Patric, Rachel Ward, Bruce Dern, George...</td>\n",
       "      <td>Mark Plummer</td>\n",
       "      <td>[May 17, 1990, (, 1990-05-17, ), (Cannes Film ...</td>\n",
       "      <td>United States</td>\n",
       "      <td>English</td>\n",
       "      <td>...</td>\n",
       "      <td>Howard E. Smith</td>\n",
       "      <td>Maurice Jarre</td>\n",
       "      <td>[Ric Kidney, Robert Redlin]</td>\n",
       "      <td>Avenue Pictures</td>\n",
       "      <td>[James Foley, Robert Redlin]</td>\n",
       "      <td>tt0098994</td>\n",
       "      <td>2700000.0</td>\n",
       "      <td>6000000.0</td>\n",
       "      <td>1990-05-17</td>\n",
       "      <td>114.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>https://en.wikipedia.org/wiki/Air_America_(film)</td>\n",
       "      <td>1990.0</td>\n",
       "      <td>https://www.imdb.com/title/tt0099005/</td>\n",
       "      <td>Air America</td>\n",
       "      <td>[Air America, by, Christopher Robbins]</td>\n",
       "      <td>[Mel Gibson, Robert Downey Jr., Nancy Travis, ...</td>\n",
       "      <td>Roger Deakins</td>\n",
       "      <td>[August 10, 1990, (, 1990-08-10, )]</td>\n",
       "      <td>United States</td>\n",
       "      <td>[English, Lao]</td>\n",
       "      <td>...</td>\n",
       "      <td>[John Bloom, Lois Freeman-Fox]</td>\n",
       "      <td>Charles Gross</td>\n",
       "      <td>Daniel Melnick</td>\n",
       "      <td>[Carolco Pictures, IndieProd Company]</td>\n",
       "      <td>[John Eskow, Richard Rush]</td>\n",
       "      <td>tt0099005</td>\n",
       "      <td>57718089.0</td>\n",
       "      <td>35000000.0</td>\n",
       "      <td>1990-08-10</td>\n",
       "      <td>113.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>https://en.wikipedia.org/wiki/Alice_(1990_film)</td>\n",
       "      <td>1990.0</td>\n",
       "      <td>https://www.imdb.com/title/tt0099012/</td>\n",
       "      <td>Alice</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[Alec Baldwin, Blythe Danner, Judy Davis, Mia ...</td>\n",
       "      <td>Carlo Di Palma</td>\n",
       "      <td>[December 25, 1990, (, 1990-12-25, )]</td>\n",
       "      <td>United States</td>\n",
       "      <td>English</td>\n",
       "      <td>...</td>\n",
       "      <td>Susan E. Morse</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Robert Greenhut</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Woody Allen</td>\n",
       "      <td>tt0099012</td>\n",
       "      <td>7331647.0</td>\n",
       "      <td>12000000.0</td>\n",
       "      <td>1990-12-25</td>\n",
       "      <td>106.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>https://en.wikipedia.org/wiki/Almost_an_Angel</td>\n",
       "      <td>1990.0</td>\n",
       "      <td>https://www.imdb.com/title/tt0099018/</td>\n",
       "      <td>Almost an Angel</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[Paul Hogan, Elias Koteas, Linda Kozlowski]</td>\n",
       "      <td>Russell Boyd</td>\n",
       "      <td>December 19, 1990</td>\n",
       "      <td>US</td>\n",
       "      <td>English</td>\n",
       "      <td>...</td>\n",
       "      <td>David Stiven</td>\n",
       "      <td>Maurice Jarre</td>\n",
       "      <td>John Cornell</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Paul Hogan</td>\n",
       "      <td>tt0099018</td>\n",
       "      <td>6939946.0</td>\n",
       "      <td>25000000.0</td>\n",
       "      <td>1990-12-19</td>\n",
       "      <td>95.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 url    year  \\\n",
       "0  https://en.wikipedia.org/wiki/The_Adventures_o...  1990.0   \n",
       "1  https://en.wikipedia.org/wiki/After_Dark,_My_S...  1990.0   \n",
       "2   https://en.wikipedia.org/wiki/Air_America_(film)  1990.0   \n",
       "3    https://en.wikipedia.org/wiki/Alice_(1990_film)  1990.0   \n",
       "4      https://en.wikipedia.org/wiki/Almost_an_Angel  1990.0   \n",
       "\n",
       "                               imdb_link                            title  \\\n",
       "0  https://www.imdb.com/title/tt0098987/  The Adventures of Ford Fairlane   \n",
       "1  https://www.imdb.com/title/tt0098994/             After Dark, My Sweet   \n",
       "2  https://www.imdb.com/title/tt0099005/                      Air America   \n",
       "3  https://www.imdb.com/title/tt0099012/                            Alice   \n",
       "4  https://www.imdb.com/title/tt0099018/                  Almost an Angel   \n",
       "\n",
       "                                            Based on  \\\n",
       "0                        [Characters, by Rex Weiner]   \n",
       "1  [the novel, After Dark, My Sweet, by, Jim Thom...   \n",
       "2             [Air America, by, Christopher Robbins]   \n",
       "3                                                NaN   \n",
       "4                                                NaN   \n",
       "\n",
       "                                            Starring  Cinematography  \\\n",
       "0  [Andrew Dice Clay, Wayne Newton, Priscilla Pre...     Oliver Wood   \n",
       "1  [Jason Patric, Rachel Ward, Bruce Dern, George...    Mark Plummer   \n",
       "2  [Mel Gibson, Robert Downey Jr., Nancy Travis, ...   Roger Deakins   \n",
       "3  [Alec Baldwin, Blythe Danner, Judy Davis, Mia ...  Carlo Di Palma   \n",
       "4        [Paul Hogan, Elias Koteas, Linda Kozlowski]    Russell Boyd   \n",
       "\n",
       "                                        Release date        Country  \\\n",
       "0                  [July 11, 1990, (, 1990-07-11, )]  United States   \n",
       "1  [May 17, 1990, (, 1990-05-17, ), (Cannes Film ...  United States   \n",
       "2                [August 10, 1990, (, 1990-08-10, )]  United States   \n",
       "3              [December 25, 1990, (, 1990-12-25, )]  United States   \n",
       "4                                  December 19, 1990             US   \n",
       "\n",
       "         Language  ...                       Editor(s)  \\\n",
       "0         English  ...                 Michael Tronick   \n",
       "1         English  ...                 Howard E. Smith   \n",
       "2  [English, Lao]  ...  [John Bloom, Lois Freeman-Fox]   \n",
       "3         English  ...                  Susan E. Morse   \n",
       "4         English  ...                    David Stiven   \n",
       "\n",
       "               Composer(s)                  Producer(s)  \\\n",
       "0  [Cliff Eidelman, Yello]   [Steve Perry, Joel Silver]   \n",
       "1            Maurice Jarre  [Ric Kidney, Robert Redlin]   \n",
       "2            Charles Gross               Daniel Melnick   \n",
       "3                      NaN              Robert Greenhut   \n",
       "4            Maurice Jarre                 John Cornell   \n",
       "\n",
       "                   Production company(s)                     Writer(s)  \\\n",
       "0                        Silver Pictures   [David Arnott, James Cappe]   \n",
       "1                        Avenue Pictures  [James Foley, Robert Redlin]   \n",
       "2  [Carolco Pictures, IndieProd Company]    [John Eskow, Richard Rush]   \n",
       "3                                    NaN                   Woody Allen   \n",
       "4                                    NaN                    Paul Hogan   \n",
       "\n",
       "     imdb_id  box_office      budget release_date  running_time  \n",
       "0  tt0098987  21400000.0  20000000.0   1990-07-11         102.0  \n",
       "1  tt0098994   2700000.0   6000000.0   1990-05-17         114.0  \n",
       "2  tt0099005  57718089.0  35000000.0   1990-08-10         113.0  \n",
       "3  tt0099012   7331647.0  12000000.0   1990-12-25         106.0  \n",
       "4  tt0099018   6939946.0  25000000.0   1990-12-19          95.0  \n",
       "\n",
       "[5 rows x 23 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 13. Check the wiki_movies_df DataFrame. \n",
    "wiki_movies_df.head()"
   ]
  },
  {
   "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>imdb_id</th>\n",
       "      <th>kaggle_id</th>\n",
       "      <th>title</th>\n",
       "      <th>original_title</th>\n",
       "      <th>tagline</th>\n",
       "      <th>belongs_to_collection</th>\n",
       "      <th>wikipedia_url</th>\n",
       "      <th>imdb_link</th>\n",
       "      <th>runtime</th>\n",
       "      <th>budget</th>\n",
       "      <th>...</th>\n",
       "      <th>rating_0.5</th>\n",
       "      <th>rating_1.0</th>\n",
       "      <th>rating_1.5</th>\n",
       "      <th>rating_2.0</th>\n",
       "      <th>rating_2.5</th>\n",
       "      <th>rating_3.0</th>\n",
       "      <th>rating_3.5</th>\n",
       "      <th>rating_4.0</th>\n",
       "      <th>rating_4.5</th>\n",
       "      <th>rating_5.0</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>tt0098987</td>\n",
       "      <td>9548</td>\n",
       "      <td>The Adventures of Ford Fairlane</td>\n",
       "      <td>The Adventures of Ford Fairlane</td>\n",
       "      <td>Kojak. Columbo. Dirty Harry. Wimps.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://en.wikipedia.org/wiki/The_Adventures_o...</td>\n",
       "      <td>https://www.imdb.com/title/tt0098987/</td>\n",
       "      <td>104.0</td>\n",
       "      <td>49000000.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>tt0098994</td>\n",
       "      <td>25501</td>\n",
       "      <td>After Dark, My Sweet</td>\n",
       "      <td>After Dark, My Sweet</td>\n",
       "      <td>All they risked was everything.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://en.wikipedia.org/wiki/After_Dark,_My_S...</td>\n",
       "      <td>https://www.imdb.com/title/tt0098994/</td>\n",
       "      <td>114.0</td>\n",
       "      <td>6000000.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>tt0099005</td>\n",
       "      <td>11856</td>\n",
       "      <td>Air America</td>\n",
       "      <td>Air America</td>\n",
       "      <td>The few. The proud. The totally insane.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://en.wikipedia.org/wiki/Air_America_(film)</td>\n",
       "      <td>https://www.imdb.com/title/tt0099005/</td>\n",
       "      <td>112.0</td>\n",
       "      <td>35000000.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>tt0099012</td>\n",
       "      <td>8217</td>\n",
       "      <td>Alice</td>\n",
       "      <td>Alice</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://en.wikipedia.org/wiki/Alice_(1990_film)</td>\n",
       "      <td>https://www.imdb.com/title/tt0099012/</td>\n",
       "      <td>102.0</td>\n",
       "      <td>12000000.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>tt0099018</td>\n",
       "      <td>25943</td>\n",
       "      <td>Almost an Angel</td>\n",
       "      <td>Almost an Angel</td>\n",
       "      <td>Who does he think he is?</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://en.wikipedia.org/wiki/Almost_an_Angel</td>\n",
       "      <td>https://www.imdb.com/title/tt0099018/</td>\n",
       "      <td>95.0</td>\n",
       "      <td>25000000.0</td>\n",
       "      <td>...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>37.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>11.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 41 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     imdb_id  kaggle_id                            title  \\\n",
       "0  tt0098987       9548  The Adventures of Ford Fairlane   \n",
       "1  tt0098994      25501             After Dark, My Sweet   \n",
       "2  tt0099005      11856                      Air America   \n",
       "3  tt0099012       8217                            Alice   \n",
       "4  tt0099018      25943                  Almost an Angel   \n",
       "\n",
       "                    original_title                                  tagline  \\\n",
       "0  The Adventures of Ford Fairlane      Kojak. Columbo. Dirty Harry. Wimps.   \n",
       "1             After Dark, My Sweet          All they risked was everything.   \n",
       "2                      Air America  The few. The proud. The totally insane.   \n",
       "3                            Alice                                      NaN   \n",
       "4                  Almost an Angel                 Who does he think he is?   \n",
       "\n",
       "  belongs_to_collection                                      wikipedia_url  \\\n",
       "0                   NaN  https://en.wikipedia.org/wiki/The_Adventures_o...   \n",
       "1                   NaN  https://en.wikipedia.org/wiki/After_Dark,_My_S...   \n",
       "2                   NaN   https://en.wikipedia.org/wiki/Air_America_(film)   \n",
       "3                   NaN    https://en.wikipedia.org/wiki/Alice_(1990_film)   \n",
       "4                   NaN      https://en.wikipedia.org/wiki/Almost_an_Angel   \n",
       "\n",
       "                               imdb_link  runtime      budget  ...  \\\n",
       "0  https://www.imdb.com/title/tt0098987/    104.0  49000000.0  ...   \n",
       "1  https://www.imdb.com/title/tt0098994/    114.0   6000000.0  ...   \n",
       "2  https://www.imdb.com/title/tt0099005/    112.0  35000000.0  ...   \n",
       "3  https://www.imdb.com/title/tt0099012/    102.0  12000000.0  ...   \n",
       "4  https://www.imdb.com/title/tt0099018/     95.0  25000000.0  ...   \n",
       "\n",
       "   rating_0.5 rating_1.0  rating_1.5  rating_2.0  rating_2.5 rating_3.0  \\\n",
       "0         0.0        0.0         0.0         0.0         0.0        0.0   \n",
       "1         0.0        0.0         0.0         0.0         0.0        0.0   \n",
       "2         0.0        0.0         0.0         0.0         0.0        0.0   \n",
       "3         0.0        0.0         0.0         0.0         0.0        0.0   \n",
       "4         3.0        0.0         3.0         2.0         5.0       26.0   \n",
       "\n",
       "  rating_3.5 rating_4.0 rating_4.5 rating_5.0  \n",
       "0        0.0        0.0        0.0        0.0  \n",
       "1        0.0        0.0        0.0        0.0  \n",
       "2        0.0        0.0        0.0        0.0  \n",
       "3        0.0        0.0        0.0        0.0  \n",
       "4       37.0       46.0       16.0       11.0  \n",
       "\n",
       "[5 rows x 41 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 14. Check the movies_with_ratings_df DataFrame.\n",
    "movies_with_ratings_df.head()"
   ]
  },
  {
   "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>imdb_id</th>\n",
       "      <th>kaggle_id</th>\n",
       "      <th>title</th>\n",
       "      <th>original_title</th>\n",
       "      <th>tagline</th>\n",
       "      <th>belongs_to_collection</th>\n",
       "      <th>wikipedia_url</th>\n",
       "      <th>imdb_link</th>\n",
       "      <th>runtime</th>\n",
       "      <th>budget</th>\n",
       "      <th>...</th>\n",
       "      <th>production_countries</th>\n",
       "      <th>distributor</th>\n",
       "      <th>producers</th>\n",
       "      <th>director</th>\n",
       "      <th>starring</th>\n",
       "      <th>cinematography</th>\n",
       "      <th>editors</th>\n",
       "      <th>writers</th>\n",
       "      <th>composers</th>\n",
       "      <th>based_on</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>tt0098987</td>\n",
       "      <td>9548</td>\n",
       "      <td>The Adventures of Ford Fairlane</td>\n",
       "      <td>The Adventures of Ford Fairlane</td>\n",
       "      <td>Kojak. Columbo. Dirty Harry. Wimps.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://en.wikipedia.org/wiki/The_Adventures_o...</td>\n",
       "      <td>https://www.imdb.com/title/tt0098987/</td>\n",
       "      <td>104.0</td>\n",
       "      <td>49000000.0</td>\n",
       "      <td>...</td>\n",
       "      <td>[{'iso_3166_1': 'US', 'name': 'United States o...</td>\n",
       "      <td>20th Century Fox</td>\n",
       "      <td>[Steve Perry, Joel Silver]</td>\n",
       "      <td>Renny Harlin</td>\n",
       "      <td>[Andrew Dice Clay, Wayne Newton, Priscilla Pre...</td>\n",
       "      <td>Oliver Wood</td>\n",
       "      <td>Michael Tronick</td>\n",
       "      <td>[David Arnott, James Cappe]</td>\n",
       "      <td>[Cliff Eidelman, Yello]</td>\n",
       "      <td>[Characters, by Rex Weiner]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>tt0098994</td>\n",
       "      <td>25501</td>\n",
       "      <td>After Dark, My Sweet</td>\n",
       "      <td>After Dark, My Sweet</td>\n",
       "      <td>All they risked was everything.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://en.wikipedia.org/wiki/After_Dark,_My_S...</td>\n",
       "      <td>https://www.imdb.com/title/tt0098994/</td>\n",
       "      <td>114.0</td>\n",
       "      <td>6000000.0</td>\n",
       "      <td>...</td>\n",
       "      <td>[{'iso_3166_1': 'US', 'name': 'United States o...</td>\n",
       "      <td>Avenue Pictures</td>\n",
       "      <td>[Ric Kidney, Robert Redlin]</td>\n",
       "      <td>James Foley</td>\n",
       "      <td>[Jason Patric, Rachel Ward, Bruce Dern, George...</td>\n",
       "      <td>Mark Plummer</td>\n",
       "      <td>Howard E. Smith</td>\n",
       "      <td>[James Foley, Robert Redlin]</td>\n",
       "      <td>Maurice Jarre</td>\n",
       "      <td>[the novel, After Dark, My Sweet, by, Jim Thom...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>tt0099005</td>\n",
       "      <td>11856</td>\n",
       "      <td>Air America</td>\n",
       "      <td>Air America</td>\n",
       "      <td>The few. The proud. The totally insane.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://en.wikipedia.org/wiki/Air_America_(film)</td>\n",
       "      <td>https://www.imdb.com/title/tt0099005/</td>\n",
       "      <td>112.0</td>\n",
       "      <td>35000000.0</td>\n",
       "      <td>...</td>\n",
       "      <td>[{'iso_3166_1': 'US', 'name': 'United States o...</td>\n",
       "      <td>TriStar Pictures</td>\n",
       "      <td>Daniel Melnick</td>\n",
       "      <td>Roger Spottiswoode</td>\n",
       "      <td>[Mel Gibson, Robert Downey Jr., Nancy Travis, ...</td>\n",
       "      <td>Roger Deakins</td>\n",
       "      <td>[John Bloom, Lois Freeman-Fox]</td>\n",
       "      <td>[John Eskow, Richard Rush]</td>\n",
       "      <td>Charles Gross</td>\n",
       "      <td>[Air America, by, Christopher Robbins]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>tt0099012</td>\n",
       "      <td>8217</td>\n",
       "      <td>Alice</td>\n",
       "      <td>Alice</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://en.wikipedia.org/wiki/Alice_(1990_film)</td>\n",
       "      <td>https://www.imdb.com/title/tt0099012/</td>\n",
       "      <td>102.0</td>\n",
       "      <td>12000000.0</td>\n",
       "      <td>...</td>\n",
       "      <td>[{'iso_3166_1': 'US', 'name': 'United States o...</td>\n",
       "      <td>Orion Pictures</td>\n",
       "      <td>Robert Greenhut</td>\n",
       "      <td>Woody Allen</td>\n",
       "      <td>[Alec Baldwin, Blythe Danner, Judy Davis, Mia ...</td>\n",
       "      <td>Carlo Di Palma</td>\n",
       "      <td>Susan E. Morse</td>\n",
       "      <td>Woody Allen</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>tt0099018</td>\n",
       "      <td>25943</td>\n",
       "      <td>Almost an Angel</td>\n",
       "      <td>Almost an Angel</td>\n",
       "      <td>Who does he think he is?</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://en.wikipedia.org/wiki/Almost_an_Angel</td>\n",
       "      <td>https://www.imdb.com/title/tt0099018/</td>\n",
       "      <td>95.0</td>\n",
       "      <td>25000000.0</td>\n",
       "      <td>...</td>\n",
       "      <td>[{'iso_3166_1': 'US', 'name': 'United States o...</td>\n",
       "      <td>Paramount Pictures</td>\n",
       "      <td>John Cornell</td>\n",
       "      <td>John Cornell</td>\n",
       "      <td>[Paul Hogan, Elias Koteas, Linda Kozlowski]</td>\n",
       "      <td>Russell Boyd</td>\n",
       "      <td>David Stiven</td>\n",
       "      <td>Paul Hogan</td>\n",
       "      <td>Maurice Jarre</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 31 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     imdb_id  kaggle_id                            title  \\\n",
       "0  tt0098987       9548  The Adventures of Ford Fairlane   \n",
       "1  tt0098994      25501             After Dark, My Sweet   \n",
       "2  tt0099005      11856                      Air America   \n",
       "3  tt0099012       8217                            Alice   \n",
       "4  tt0099018      25943                  Almost an Angel   \n",
       "\n",
       "                    original_title                                  tagline  \\\n",
       "0  The Adventures of Ford Fairlane      Kojak. Columbo. Dirty Harry. Wimps.   \n",
       "1             After Dark, My Sweet          All they risked was everything.   \n",
       "2                      Air America  The few. The proud. The totally insane.   \n",
       "3                            Alice                                      NaN   \n",
       "4                  Almost an Angel                 Who does he think he is?   \n",
       "\n",
       "  belongs_to_collection                                      wikipedia_url  \\\n",
       "0                   NaN  https://en.wikipedia.org/wiki/The_Adventures_o...   \n",
       "1                   NaN  https://en.wikipedia.org/wiki/After_Dark,_My_S...   \n",
       "2                   NaN   https://en.wikipedia.org/wiki/Air_America_(film)   \n",
       "3                   NaN    https://en.wikipedia.org/wiki/Alice_(1990_film)   \n",
       "4                   NaN      https://en.wikipedia.org/wiki/Almost_an_Angel   \n",
       "\n",
       "                               imdb_link  runtime      budget  ...  \\\n",
       "0  https://www.imdb.com/title/tt0098987/    104.0  49000000.0  ...   \n",
       "1  https://www.imdb.com/title/tt0098994/    114.0   6000000.0  ...   \n",
       "2  https://www.imdb.com/title/tt0099005/    112.0  35000000.0  ...   \n",
       "3  https://www.imdb.com/title/tt0099012/    102.0  12000000.0  ...   \n",
       "4  https://www.imdb.com/title/tt0099018/     95.0  25000000.0  ...   \n",
       "\n",
       "                                production_countries         distributor  \\\n",
       "0  [{'iso_3166_1': 'US', 'name': 'United States o...    20th Century Fox   \n",
       "1  [{'iso_3166_1': 'US', 'name': 'United States o...     Avenue Pictures   \n",
       "2  [{'iso_3166_1': 'US', 'name': 'United States o...    TriStar Pictures   \n",
       "3  [{'iso_3166_1': 'US', 'name': 'United States o...      Orion Pictures   \n",
       "4  [{'iso_3166_1': 'US', 'name': 'United States o...  Paramount Pictures   \n",
       "\n",
       "                     producers            director  \\\n",
       "0   [Steve Perry, Joel Silver]        Renny Harlin   \n",
       "1  [Ric Kidney, Robert Redlin]         James Foley   \n",
       "2               Daniel Melnick  Roger Spottiswoode   \n",
       "3              Robert Greenhut         Woody Allen   \n",
       "4                 John Cornell        John Cornell   \n",
       "\n",
       "                                            starring  cinematography  \\\n",
       "0  [Andrew Dice Clay, Wayne Newton, Priscilla Pre...     Oliver Wood   \n",
       "1  [Jason Patric, Rachel Ward, Bruce Dern, George...    Mark Plummer   \n",
       "2  [Mel Gibson, Robert Downey Jr., Nancy Travis, ...   Roger Deakins   \n",
       "3  [Alec Baldwin, Blythe Danner, Judy Davis, Mia ...  Carlo Di Palma   \n",
       "4        [Paul Hogan, Elias Koteas, Linda Kozlowski]    Russell Boyd   \n",
       "\n",
       "                          editors                       writers  \\\n",
       "0                 Michael Tronick   [David Arnott, James Cappe]   \n",
       "1                 Howard E. Smith  [James Foley, Robert Redlin]   \n",
       "2  [John Bloom, Lois Freeman-Fox]    [John Eskow, Richard Rush]   \n",
       "3                  Susan E. Morse                   Woody Allen   \n",
       "4                    David Stiven                    Paul Hogan   \n",
       "\n",
       "                 composers                                           based_on  \n",
       "0  [Cliff Eidelman, Yello]                        [Characters, by Rex Weiner]  \n",
       "1            Maurice Jarre  [the novel, After Dark, My Sweet, by, Jim Thom...  \n",
       "2            Charles Gross             [Air America, by, Christopher Robbins]  \n",
       "3                      NaN                                                NaN  \n",
       "4            Maurice Jarre                                                NaN  \n",
       "\n",
       "[5 rows x 31 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 15. Check the movies_df DataFrame. \n",
    "movies_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Load"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "db_string = f\"postgres://postgres:{db_password}@127.0.0.1:5432/movie_data\"\n",
    "engine = create_engine(db_string)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "movies_df.to_sql(name='movies', con=engine, if_exists='replace')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import time\n",
    "\n",
    "rows_imported = 0\n",
    "# get the start_time from time.time()\n",
    "start_time = time.time()\n",
    "for data in pd.read_csv('Original_Datasets/ratings.csv', chunksize=1000000):\n",
    "    print(f'importing rows {rows_imported} to {rows_imported + len(data)}...', end='')\n",
    "    data.to_sql(name='ratings', con=engine, if_exists='append')\n",
    "    rows_imported += len(data)\n",
    "\n",
    "    # add elapsed time to final print out\n",
    "    print(f'Done. {time.time() - start_time} total seconds elapsed')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "PythonData",
   "language": "python",
   "name": "pythondata"
  },
  "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.5"
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}