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",
    "import os\n",
    "import re\n",
    "\n",
    "from sqlalchemy import create_engine\n",
    "import psycopg2\n",
    "\n",
    "# 1 uncomment the # from config import db_password so this code is working.\n",
    "from config import db_password\n",
    "\n",
    "import time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "gobucks1\n"
     ]
    }
   ],
   "source": [
    "print(db_password)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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": 4,
   "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 extract_transform_load(wiki_file, kaggle_file, ratings_file):\n",
    "    # Read in the kaggle metadata and MovieLens ratings CSV files as Pandas DataFrames.    \n",
    "    kaggle_metadata = pd.read_csv(os.path.join('Archive','movies_metadata.csv'), low_memory=False)\n",
    "    ratings = pd.read_csv(os.path.join('Archive','ratings.csv'))      \n",
    "\n",
    "    # Open and read the Wikipedia data JSON file.\n",
    "    with open(os.path.join('Archive','wikipedia-movies.json'), mode='r') as file:\n",
    "        wiki_movies_raw = json.load(file)         \n",
    "    \n",
    "    # Write a list comprehension to filter out TV shows.\n",
    "    wiki_tv = [tvshows for tvshows in wiki_movies_raw \n",
    "        if 'Television series' in tvshows]   \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_movies = [clean_movie(movie) for movie in wiki_movies_raw]       \n",
    "\n",
    "    # Read in the cleaned movies list from Step 4 as a DataFrame.\n",
    "    wiki_movies_df = pd.DataFrame(clean_movies)\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: \n",
    "        print(\"No link to extract\")        \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'\\$\\d+\\.?\\d*\\s*[mb]illion'\n",
    "    \n",
    "    # Write a regular expression to match the three elements of \"form_two\" of the box office data.\n",
    "    form_two = r'\\$\\d{1,3}(?:,\\d{3})+'\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",
    "\n",
    "        # if input is of the form $###.# million\n",
    "        if re.match(r'\\$\\s*\\d+\\.?\\d*\\s*milli?on', s, flags=re.IGNORECASE):\n",
    "\n",
    "            # remove dollar sign and \" million\"\n",
    "            s = re.sub('\\$|\\s|[a-zA-Z]','', s)\n",
    "\n",
    "            # convert to float and multiply by a million\n",
    "            value = float(s) * 10**6\n",
    "\n",
    "            # return value\n",
    "            return value\n",
    "\n",
    "        # if input is of the form $###.# billion\n",
    "        elif re.match(r'\\$\\s*\\d+\\.?\\d*\\s*billi?on', s, flags=re.IGNORECASE):\n",
    "\n",
    "            # remove dollar sign and \" billion\"\n",
    "            s = re.sub('\\$|\\s|[a-zA-Z]','', s)\n",
    "\n",
    "            # convert to float and multiply by a billion\n",
    "            value = float(s) * 10**9\n",
    "\n",
    "            # return value\n",
    "            return value\n",
    "\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",
    "\n",
    "            # remove dollar sign and commas\n",
    "            s = re.sub('\\$|,','', s)\n",
    "\n",
    "            # convert to float\n",
    "            value = float(s)\n",
    "\n",
    "            # return value\n",
    "            return value\n",
    "\n",
    "        # otherwise, return NaN\n",
    "        else:\n",
    "            return np.nan       \n",
    "        \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})', flags=re.IGNORECASE)[0].apply(parse_dollars)\n",
    "    wiki_movies_df.drop('Box office', axis=1, inplace=True)\n",
    "    \n",
    "    # Clean the budget column in the wiki_movies_df DataFrame.\n",
    "    # First, we need to preprocess the budget data. Create a budget variable\n",
    "    budget = wiki_movies_df['Budget'].dropna()\n",
    "    \n",
    "    # Convert any lists to strings:\n",
    "    budget = budget.map(lambda x: ' '.join(x) if type(x) == list else x)\n",
    "    \n",
    "    # Then remove any values between a dollar sign and a hyphen (for budgets given in ranges):\n",
    "    budget = budget.str.replace(r'\\$.*[-—–](?![a-z])', '$', regex=True)\n",
    "    \n",
    "    # Use the same pattern matches that you created to parse the box office data, and apply them without modifications \n",
    "    # to the budget data. Then, look at what's left.\n",
    "    matches_form_one = budget.str.contains(form_one, flags=re.IGNORECASE, na=False)\n",
    "    matches_form_two = budget.str.contains(form_two, flags=re.IGNORECASE, na=False)\n",
    "    budget[~matches_form_one & ~matches_form_two]\n",
    "    \n",
    "    # Another issue is discovered: citation references (the numbers in square brackets).\n",
    "    # Remove those with a regular expression.\n",
    "    budget = budget.str.replace(r'\\[\\d+\\]\\s*', '')\n",
    "    budget[~matches_form_one & ~matches_form_two]\n",
    "    \n",
    "    # Ready to parse the budget values\n",
    "    wiki_movies_df['budget'] = budget.str.extract(f'({form_one}|{form_two})', flags=re.IGNORECASE)[0].apply(parse_dollars)\n",
    "    \n",
    "    # Drop the original Budget column\n",
    "    wiki_movies_df.drop('Budget', axis=1, inplace=True)    \n",
    "\n",
    "    # Clean the release date column in the wiki_movies_df DataFrame.\n",
    "    # Parse Release Date\n",
    "    # Parsing the release date will follow a similar pattern to parsing box office and budget, but with different forms.\n",
    "\n",
    "    # First, make a variable that holds the non-null values of Release date in the DataFrame, converting lists to strings:\n",
    "    release_date = wiki_movies_df['Release date'].dropna().apply(lambda x: ' '.join(x) if type(x) == list else x)\n",
    "    \n",
    "    # The forms we'll be parsing are:\n",
    "\n",
    "    # 1 Full month name, one- to two-digit day, four-digit year (i.e., January 1, 2000)\n",
    "    # 2 Four-digit year, two-digit month, two-digit day, with any separator (i.e., 2000-01-01)\n",
    "    # 3 Full month name, four-digit year (i.e., January 2000)\n",
    "    # 4 Four-digit year\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.[0123]\\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",
    "    \n",
    "    # And then we can extract the dates with:\n",
    "    release_date.str.extract(f'({date_form_one}|{date_form_two}|{date_form_three}|{date_form_four})', flags=re.IGNORECASE)\n",
    "    \n",
    "    # Instead of creating our own function to parse the dates, we'll use the built-in to_datetime() method in Pandas. Since \n",
    "    # there are different date formats, set the infer_datetime_format option to True. The date formats we've targeted are \n",
    "    # among those that the to_datetime() function can recognize, which explains the infer_datetime_format=True argument below.\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], infer_datetime_format=True)  \n",
    "    \n",
    "    # Clean the running time column in the wiki_movies_df DataFrame.\n",
    "    # Parse Running Time\n",
    "    # First, make a variable that holds the non-null values of Release date in the DataFrame, converting lists to strings:\n",
    "    running_time = wiki_movies_df['Running time'].dropna().apply(lambda x: ' '.join(x) if type(x) == list else x)\n",
    "    running_time\n",
    "    \n",
    "    # It looks like most of the entries just look like \"100 minutes.\" Let's see how many running times look exactly like \n",
    "    # that by using string boundaries.\n",
    "    running_time.str.contains(r'^\\d*\\s*minutes$', flags=re.IGNORECASE, na=False).sum()\n",
    "    \n",
    "    # The above code returns 6,528 entries. Let's get a sense of what the other 366 entries look like.\n",
    "    running_time[running_time.str.contains(r'^\\d*\\s*minutes$', flags=re.IGNORECASE, na=False) != True]\n",
    "    \n",
    "    # Let's make this more general by only marking the beginning of the string, and accepting other abbreviations of \n",
    "    # \"minutes\" by only searching up to the letter \"m.\n",
    "    running_time.str.contains(r'^\\d*\\s*m', flags=re.IGNORECASE, na=False).sum()\n",
    "    \n",
    "    # That accounts for 6,877 entries. The remaining 17 follow:\n",
    "    running_time[running_time.str.contains(r'^\\d*\\s*m', flags=re.IGNORECASE, na=False) != True]\n",
    "    \n",
    "    # We can match all of the hour + minute patterns with one regular expression pattern. Our pattern follows:\n",
    "    # 1 Start with one or more digits.\n",
    "    # 2 Have an optional space after the digit and before the letter \"h.\"\n",
    "    # 3 Capture all the possible abbreviations of \"hour(s).\" To do this, we'll make every letter in \"hours\" optional except \n",
    "    #   the \"h.\"\n",
    "    # 4 Have an optional space after the \"hours\" marker.\n",
    "    # 5 Have an optional number of digits for minutes.\n",
    "    # As a pattern, this looks like \"\\d+\\s*ho?u?r?s?\\s*\\d*\".\n",
    "\n",
    "    # We only want to extract digits, and we want to allow for both possible patterns. Therefore, we'll add capture \n",
    "    # groups around the \\d instances as well as add an alternating character. Our code will look like the following.\n",
    "    running_time_extract = running_time.str.extract(r'(\\d+)\\s*ho?u?r?s?\\s*(\\d*)|(\\d+)\\s*m')\n",
    "    \n",
    "    # Unfortunately, this new DataFrame is all strings, we'll need to convert them to numeric values. Because we may have \n",
    "    # captured empty strings, we'll use the to_numeric() method and set the errors argument to 'coerce'. Coercing the \n",
    "    # errors will turn the empty strings into Not a Number (NaN), then we can use fillna() to change all the NaNs to zeros.\n",
    "    running_time_extract = running_time_extract.apply(lambda col: pd.to_numeric(col, errors='coerce')).fillna(0)\n",
    "    \n",
    "    # Now we can apply a function that will convert the hour capture groups and minute capture groups to minutes if the \n",
    "    # pure minutes capture group is zero, and save the output to wiki_movies_df:\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",
    "    # Finally, we can drop Running time from the dataset with the following code:\n",
    "    wiki_movies_df.drop('Running time', axis=1, inplace=True)\n",
    "    \n",
    "    # Return three variables. The first is the wiki_movies_df DataFrame\n",
    "    # return wiki_movies_df, kaggle_metadata, ratings     \n",
    "     \n",
    "    # 2. Clean the Kaggle metadata.\n",
    "    \n",
    "    # Remove bad data\n",
    "    kaggle_metadata[~kaggle_metadata['adult'].isin(['True','False'])]\n",
    "    \n",
    "    # Since imbd_id is missing for adult films, only keep rows where adult is False, and then drop the \"adult\" column.\n",
    "    # The following code will keep rows where the adult column is False, and then drop the adult column.\n",
    "    kaggle_metadata = kaggle_metadata[kaggle_metadata['adult'] == 'False'].drop('adult',axis='columns')\n",
    "    \n",
    "    # Convert video by changing data type.\n",
    "    # kaggle_metadata_df['video'] == 'True'\n",
    "    # The above code creates the Boolean column we want. We just need to assign it back to video\n",
    "    kaggle_metadata['video'] = kaggle_metadata['video'] == 'True'\n",
    "    \n",
    "    # For the numeric columns, we can just use the to_numeric() method from Pandas. We'll make sure the errors= argument \n",
    "    # is set to 'raise', so we'll know if there's any data that can't be converted to numbers.\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",
    "    # Finally, we need to convert release_date to datetime. Luckily, Pandas has a built-in function for that as well: \n",
    "    # to_datetime(). \n",
    "    # Since release_date is in a standard format, to_datetime() will convert it without any fuss.\n",
    "    kaggle_metadata['release_date'] = pd.to_datetime(kaggle_metadata['release_date'])\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",
    "\n",
    "    # 4. Drop unnecessary columns from the merged DataFrame.\n",
    "    # use the dataframe.drop() function\n",
    "    movies_df.drop(columns=['title_wiki'])\n",
    "\n",
    "    # 5. Add in the function to fill in the missing Kaggle data.\n",
    "    # Add the fill_missing_kaggle_data() function that fills in the missing Kaggle data on the movies_df DataFrame. \n",
    "    # Used reference code from M_Fullerton \"ETL_clean_kaggle_data.ipynb.\" Not familiar with function\n",
    "    def fill_missing_kaggle_data(df, kaggle_column, wiki_column):\n",
    "        df[kaggle_column] = df.apply(\n",
    "            lambda row: row[wiki_column] if row[kaggle_column] == 0 else row[kaggle_column], 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",
    "    # Used reference code from M_Fullerton \"ETL_clean_kaggle_data.ipynb.\" Not familiar with function.\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",
    "    movies_df\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",
    "        print(col)\n",
    "\n",
    "    # 8. Rename the columns in the movies DataFrame for consistency.\n",
    "    # Reference M_Fullerton \"ETL_clean_wiki_movies.ipynb\" from GitHub. Did not have complete code, missing column_names and\n",
    "    # the reindex function.\n",
    "    column_names = ['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 = movies_df.reindex(columns=column_names)\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",
    "    # use a groupby on the \"movieId\" and \"rating\" columns and take the \n",
    "    # count for each group.\n",
    "    rating_counts = ratings.groupby(['movieId','rating'], as_index=False).count()\n",
    "    \n",
    "    # Rename the \"userId\" column to \"count.\"\n",
    "    rating_counts = ratings.groupby(['movieId','rating'], as_index=False).count() \\\n",
    "                .rename({'userId':'count'}, axis=1)\n",
    "    \n",
    "    # pivot this data so that movieId is the index, the columns will be \n",
    "    # all the rating values, and the rows will be the counts for each \n",
    "    # rating value.\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",
    "    \n",
    "    #  rename the columns so they're easier to understand. We'll prepend \n",
    "    # rating_ to each column with a list comprehension\n",
    "    rating_counts.columns = ['rating_' + str(col) for col in rating_counts.columns]\n",
    "    \n",
    "    # We need to use a left merge, since we want to keep everything in \n",
    "    # movies_df:\n",
    "    movies_with_ratings_df = pd.merge(movies_df, rating_counts, left_on='kaggle_id', right_index=True, how='left')\n",
    "    \n",
    "    # Not every movie got a rating for each rating level, there will be \n",
    "    # missing values instead of zeros. We have to fill those in ourselves, \n",
    "    # like this:\n",
    "    movies_with_ratings_df[rating_counts.columns] = movies_with_ratings_df[rating_counts.columns].fillna(0)\n",
    "\n",
    "    # 2 Remove the return statement, return wiki_movies_df, movies_with_ratings_df, movies_df.\n",
    "    # return wiki_movies_df, movies_with_ratings_df, movies_df   \n",
    "    \n",
    "    # 3 After Step 9, Transform and merge the ratings DataFrame, add the code to create the connection to the PostgreSQL \n",
    "    # database, \n",
    "    \n",
    "    # Create the Database Engine - local server, the connection string will be as follows:\n",
    "    # Used reference code from Raquely44 (Raquel Yates) \"ETL_create_database.ipynb\" on GitHub for code to complete db_string.\n",
    "    # Instructor Nick Meyer helped with this string\n",
    "    # db_string = f\"postgresql://postgres:{db_password}@127.0.0.1:5432/movie_data\"\n",
    "    db_string = f\"postgresql://postgres:{db_password}@localhost:5432/movies_db\"\n",
    "\n",
    "    # Create the database engine (to the PostgreSQL database)\n",
    "    engine = create_engine(db_string)\n",
    "    \n",
    "    # Then add the movies_df DataFrame to a SQL database.\n",
    "    # Use 'replace' for the if_exists parameter so that the movies_df DataFrame data won't be added to the table again.\n",
    "    movies_df.to_sql(name='movies', if_exists='replace',con=engine)\n",
    "    \n",
    "    # 4 Before reading in the MovieLens rating CSV data, drop the ratings table in pgAdmin. \n",
    "    # data.to_sql(name='ratings', con=engine, if_exists='replace', index=False)\n",
    "      \n",
    "    # 5 Add the code that prints out the elapsed time to import each row. \n",
    "    # create a variable for the number of rows imported\n",
    "    rows_imported = 0\n",
    "    # get the start_time from time.time()\n",
    "    # Used reference code from Raquely44 (Raquel Yates) \"ETL_create_database.ipynb\" on GitHub to complete for loop including\n",
    "    # chunksize parameter and data.to_sql function con=engine parameter\n",
    "    start_time = time.time()\n",
    "    for data in pd.read_csv(ratings_file, chunksize=1000000):\n",
    "        data.to_sql(name='ratings', con=engine, if_exists='replace', index=False)\n",
    "    # print out the range of rows that are being imported\n",
    "    # Used reference code from Raquely44 (Raquel Yates) \"ETL_create_database.ipynb\" on GitHub for code to complete print\n",
    "    # function\n",
    "        print(f'importing rows {rows_imported} to {rows_imported + len(data)}...', end='')\n",
    "\n",
    "    # increment the number of rows imported by the size of 'data'\n",
    "        rows_imported += len(data)\n",
    "\n",
    "    # print that the rows have finished importing\n",
    "        print(f'Done. {time.time() - start_time} total seconds elapsed')\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 10. Create the path to your file directory and variables for the three files.\n",
    "file_dir = '/Users/rfnichol/OneDrive - COOPER TIRE & RUBBER COMPANY/Personal/Data Analytics Boot Camp/Module_8_ETL/Module_8_files/Movies_ETL/archive'\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": 6,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\rfnichol\\Anaconda3\\envs\\PythonData\\lib\\site-packages\\ipykernel_launcher.py:116: FutureWarning: The default value of regex will change from True to False in a future version.\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "importing rows 0 to 1000000...Done. 17.62169599533081 total seconds elapsed\n",
      "importing rows 1000000 to 2000000...Done. 34.32362413406372 total seconds elapsed\n",
      "importing rows 2000000 to 3000000...Done. 51.4937047958374 total seconds elapsed\n",
      "importing rows 3000000 to 4000000...Done. 70.24476718902588 total seconds elapsed\n",
      "importing rows 4000000 to 5000000...Done. 88.34394955635071 total seconds elapsed\n",
      "importing rows 5000000 to 6000000...Done. 106.40009140968323 total seconds elapsed\n",
      "importing rows 6000000 to 7000000...Done. 124.37980651855469 total seconds elapsed\n",
      "importing rows 7000000 to 8000000...Done. 142.28150463104248 total seconds elapsed\n",
      "importing rows 8000000 to 9000000...Done. 160.44074630737305 total seconds elapsed\n",
      "importing rows 9000000 to 10000000...Done. 178.63142204284668 total seconds elapsed\n",
      "importing rows 10000000 to 11000000...Done. 196.91586923599243 total seconds elapsed\n",
      "importing rows 11000000 to 12000000...Done. 214.7011513710022 total seconds elapsed\n",
      "importing rows 12000000 to 13000000...Done. 232.61549997329712 total seconds elapsed\n",
      "importing rows 13000000 to 14000000...Done. 250.4377477169037 total seconds elapsed\n",
      "importing rows 14000000 to 15000000...Done. 268.5344521999359 total seconds elapsed\n",
      "importing rows 15000000 to 16000000...Done. 286.24274158477783 total seconds elapsed\n",
      "importing rows 16000000 to 17000000...Done. 304.46315026283264 total seconds elapsed\n",
      "importing rows 17000000 to 18000000...Done. 323.0362756252289 total seconds elapsed\n",
      "importing rows 18000000 to 19000000...Done. 341.2707223892212 total seconds elapsed\n",
      "importing rows 19000000 to 20000000...Done. 359.25341629981995 total seconds elapsed\n",
      "importing rows 20000000 to 21000000...Done. 377.2683711051941 total seconds elapsed\n",
      "importing rows 21000000 to 22000000...Done. 395.2890291213989 total seconds elapsed\n",
      "importing rows 22000000 to 23000000...Done. 413.54301381111145 total seconds elapsed\n",
      "importing rows 23000000 to 24000000...Done. 432.111209154129 total seconds elapsed\n",
      "importing rows 24000000 to 25000000...Done. 450.2098693847656 total seconds elapsed\n",
      "importing rows 25000000 to 26000000...Done. 468.2249274253845 total seconds elapsed\n",
      "importing rows 26000000 to 26024289...Done. 468.6397337913513 total seconds elapsed\n"
     ]
    }
   ],
   "source": [
    "# 11. Delivrable 4 step 6, set the three variables equal to the function created in D1.\n",
    "# wiki_file, kaggle_file, ratings_file = \n",
    "extract_transform_load(wiki_file, kaggle_file, ratings_file)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Run a query on the PostgreSQL database that retreives the number of rows for the movies and ratings tables.\n",
    "# Steps - see example code in Data Analytics Notes\n",
    "# 1 Connect to PostgreSQL from Python\n",
    "#     db_string = f\"postgresql://postgre:{db_password}@127.0.0.1:5432/movie_data\"\n",
    "\n",
    "    # Create the database engine (to the PostgreSQL database)\n",
    "#    engine = create_engine(db_string)\n",
    "# 2 Define a PostgreSQL SELECT Query\n",
    "# SELECT col1, col2,…colnN FROM postgresql_table WHERE id = 5\n",
    "# 3 Get Cursor Object from Connection \n",
    "# Next, use a connection.cursor() method to create a Psycopg2 cursor object. This method creates a new \n",
    "# psycopg2.extensions.cursor object.\n",
    "# 4 Execute the SELECT query using a execute() method\n",
    "# Execute the select query using the cursor.execute() method.\n",
    "\n",
    "# 5 Extract all rows from a result\n",
    "# Use the fetchall() method of a cursor object to get all rows from a query result. it returns a list of row\n",
    "# 6 Iterate each row\n",
    "# Iterate a row list using a for loop and access each row individually (Access each row’s column data using a column \n",
    "# name or index number.)\n",
    "# 7 Close the cursor object and database connection object\n",
    "# use cursor.clsoe() and connection.clsoe() method to close open connections after your work completes.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.7.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}