# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: PythonData
#     language: python
#     name: pythondata
# ---

import json
import pandas as pd
import numpy as np
import os
import re
from sqlalchemy import create_engine
import time

os.path.abspath("Movielens_Extract.ipynb")

os.path.abspath("Movies-ETL/wikipedia-movies.json")

file_dir = '/Users/leora/Desktop/Class/Movies-ETL/'

f'{file_dir}wikipedia-movies.json'

with open(f'{file_dir}/wikipedia-movies.json', mode='r') as file:
    wiki_movies_raw = json.load(file)


# + active=""
# len(wiki_movies_raw)
# -

# First 5 records
wiki_movies_raw[:5]

# Last 5 records
wiki_movies_raw[-5:]

kaggle_metadata = pd.read_csv(f'{file_dir}/archive/movies_metadata.csv', low_memory = False)
ratings = pd.read_csv(f'{file_dir}/archive/ratings.csv')


kaggle_metadata.head()

kaggle_metadata.tail()

kaggle_metadata.sample(n=5)

ratings.head()

ratings.tail()

ratings.sample(n=5)

wiki_movies_df = pd.DataFrame(wiki_movies_raw)
wiki_movies_df.head()

wiki_movies_df.columns.tolist()

wiki_movies = [movie for movie in wiki_movies_raw
               if ('Director' in movie or 'Directed by' in movie)
               and 'imdb_link' in movie
              and 'No. of episodes' not in movie]
len(wiki_movies)

wiki_movies_df = pd.DataFrame(wiki_movies)
wiki_movies_df.head()

sorted(wiki_movies_df.columns.tolist())


# Create a function to clean the movie data
def clean_movie(movie):
    movie = dict(movie) #create a non-destructive copy
    return movie


wiki_movies_df[wiki_movies_df['Arabic'].notnull()]['url']


# Create a function to clean the movie data
def clean_movie(movie):
    movie = dict(movie) #Create a non-destructive copy
    alt_titles = {}
    #loop through the columns to check for alternate titles
    for key in ['Also known as', 'Arabic', 'Cantonese', 'Chinese', 'French',
               'Hangul', 'Hebrew', 'Hepburn', 'Japanese', 'Literally', 
                'Mandarin', 'McCune-Reischauer', 'Original title', 'Polish', 'Revised Romanization', 'Romanized', 'Russian', 
                'Simplified', 'Traditional', 'Yiddish']:
        #remove the key-value pair and add to the alternative titles dict
        if key in movie:
            alt_titles[key] = movie[key]
            movie.pop(key)
            
    #add the alternative titles dict to the movie object
    if len(alt_titles) > 0:
        movie['alt_titles'] = alt_titles
        
    #consolidate columns with the same data into one column    
    def change_column_name(old_name, new_name):
        if old_name in movie:
            movie[new_name] = movie.pop(old_name)
            
    return movie


#make a list of cleaned movies with a list comprehension.
clean_movies = [clean_movie(movie) for movie in wiki_movies]

#set wiki_movies_df to be the DF created from the clean_movies and 
#print out the list of columns
wiki_movies_df = pd.DataFrame(clean_movies)
sorted(wiki_movies_df.columns.tolist())


def clean_movie(movie):
    movie = dict(movie) #Create a non-destructive copy
    alt_titles = {}
    #loop through the columns to check for alternate titles
    for key in ['Also known as', 'Arabic', 'Cantonese', 'Chinese', 'French',
               'Hangul', 'Hebrew', 'Hepburn', 'Japanese', 'Literally', 
                'Mandarin', 'McCune-Reischauer', 'Original title', 'Polish', 'Revised Romanization', 'Romanized', 'Russian', 
                'Simplified', 'Traditional', 'Yiddish']:
        #remove the key-value pair and add to the alternative titles dict
        if key in movie:
            alt_titles[key] = movie[key]
            movie.pop(key)
            
    #add the alternative titles dict to the movie object
    if len(alt_titles) > 0:
        movie['alt_titles'] = alt_titles
        
    #consolidate columns with the same data into one column    
    def change_column_name(old_name, new_name):
        if old_name in movie:
            movie[new_name] = movie.pop(old_name)
            
    change_column_name('Adaptation by','Writer(s)')
    change_column_name('Country of origin','Country')
    change_column_name('Directed by','Director')
    change_column_name('Distributed by','Distributor')
    change_column_name('Edited by','Editor(s)')
    change_column_name('Produced by','Producer(s)')
    change_column_name('Producer','Producer(s)')
    change_column_name('Productioncompanies ','Production company(s)')
    change_column_name('Productioncompany ','Production company(s)')
    change_column_name('Release date','Released')
    change_column_name('Screen story by','Writer(s)')
    change_column_name('Screenplay by','Writer(s)')
    change_column_name('Story by','Writer(s)')
    change_column_name('Theme music composer','Composer(s)')
    change_column_name('Written by','Writer(s)')
    
    return movie


clean_movies = [clean_movie(movie) for movie in wiki_movies]
wiki_movies_df = pd.DataFrame(clean_movies)
sorted(wiki_movies_df.columns.tolist())

# Drop the duplicate rows from the wiki_movies_df
wiki_movies_df['imdb_id'] = wiki_movies_df['imdb_link'].str.extract(r'(tt\d{7})')
print(len(wiki_movies_df))
wiki_movies_df.drop_duplicates(subset='imdb_id', inplace=True)
print(len(wiki_movies_df))
wiki_movies_df.head()

# Determine the number of null values in each column
wiki_movies_df.isnull()

# Use a list comprehension to count the number of null values in each column
[[column,wiki_movies_df[column].isnull().sum()] for column in wiki_movies_df.columns]

# Identify the columns we want to keep (aka columns that have less than 90% null values)
[column for column in wiki_movies_df.columns if wiki_movies_df[column].isnull().sum() < len(wiki_movies_df) * 0.9]

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]
wiki_movies_df = wiki_movies_df[wiki_columns_to_keep]
wiki_movies_df.head()

# Make a data series that drops missing values from box office data
box_office = wiki_movies_df['Box office'].dropna()
len(box_office)


def is_not_a_string(x):
    return type(x) != str


box_office[box_office.map(is_not_a_string)]

# Use a lambda function so that we don't have to make a new function every time we use .map()
lambda x: type(x) != str

box_office[box_office.map(lambda x:type(x) != str)]

# Join the list items for box office from above into one string
box_office = box_office.apply(lambda x: ' '.join(x) if type(x) == list else x)
box_office

# Make the box office object a uniform format
# Use a regular expression to search for box office values in the format $1.23 million/billion
form_one = r"\$\d+\.?\d*\s*[mb]illion"
box_office.str.contains(form_one, flags=re.IGNORECASE).sum()

# Use a regular expression to search for box office values in the format $1,234,567 format
form_two = r"\$\d{1,3}(?:,\d{3})+"
box_office.str.contains(form_two, flags=re.IGNORECASE).sum()

# Find which box office values aren't either of the formats above
matches_form_one = box_office.str.contains(form_one, flags=re.IGNORECASE)
matches_form_two = box_office.str.contains(form_two, flags=re.IGNORECASE)
box_office[~matches_form_one & ~matches_form_two]
Out[44]:


# Update the box office object forms to account for spaces between dollar sign and number
form_one = r'\$\s*\d+\.?\d*\s*[mb]illion'
form_two = r'\$\s*\d{1,3}(?:,\d{3})+'
print(box_office.str.contains(form_one, flags=re.IGNORECASE).sum())
print(box_office.str.contains(form_two, flags=re.IGNORECASE).sum())

# Update the box office object forms to account for periods used as a thousands separator (and spaces)
form_two = r'\$\s*\d{1,3}(?:[,\.]\d{3})+'
box_office.str.contains(form_two, flags=re.IGNORECASE).sum()

# Update the box office object forms to account for periods used as a thousands separator (and spaces), but only for $1,234,567 values
form_two = r'\$\s*\d{1,3}(?:[,\.]\d{3})+(?!s[mb]illion)'
box_office.str.contains(form_two, flags=re.IGNORECASE).sum()

# Replace box office values that are given as a range
box_office = box_office.str.replace(r'\$.*[-—–](?![a-z])', '$', regex=True)

# Replace millon with million
form_one = r'\$\s*\d+\.?\d*\s*[mb]illi?on'

# Extract the values that match the formats specified (form_one or form_two)
box_office.str.extract(f'({form_one}|{form_two})')


# Create a funciton that extracts box office values into numeric values
def parse_dollars(s):
    # if s is not a string, return NaN
    if type(s) != str:
        return np.nan

    # if input is of the form $###.# million
    if re.match(r'\$\s*\d+\.?\d*\s*milli?on', s, flags=re.IGNORECASE):
        # remove dollar sign and " million"
        s = re.sub('\$|\s|[a-zA-Z]','', s)
        # convert to float and multiply by a million
        value = float(s) * 10**6
        # return value
        return value

    # if input is of the form $###.# billion
    elif re.match(r'\$\s*\d+\.?\d*\s*billi?on', s, flags=re.IGNORECASE):
        # remove dollar sign and " billion"
        s = re.sub('\$|\s|[a-zA-Z]','', s)
        # convert to float and multiply by a billion
        value = float(s) * 10**9
        # return value
        return value

    # if input is of the form $###,###,###
    elif re.match(r'\$\s*\d{1,3}(?:[,\.]\d{3})+(?!\s[mb]illion)', s, flags=re.IGNORECASE):
        # remove dollar sign and commas
        s = re.sub('\$|,','', s)
        # convert to float
        value = float(s)
        # return value
        return value

    # otherwise, return NaN
    else:
        return np.nan


# Parse box office values to numeric values using the parse_dollars function
wiki_movies_df['box_office'] = box_office.str.extract(f'({form_one}|{form_two})', flags=re.IGNORECASE)[0].apply(parse_dollars)
wiki_movies_df['box_office']

# Drop the box office column
wiki_movies_df.drop('Box office', axis=1, inplace=True)
wiki_movies_df.head()

budget = wiki_movies_df['Budget'].dropna()

budget = budget.map(lambda x: ' '.join(x) if type(x) == list else x)

budget = budget.str.replace(r'\$.*[-—–](?![a-z])', '$', regex=True)

matches_form_one = budget.str.contains(form_one, flags=re.IGNORECASE)
matches_form_two = budget.str.contains(form_two, flags=re.IGNORECASE)
budget[~matches_form_one & ~matches_form_two]

# Remove citation references from the budget column
budget = budget.str.replace(r'\[\d+\]\s*', '')
budget[~matches_form_one & ~matches_form_two]

# Parse the budget values and drop the original Budget column
wiki_movies_df['budget'] = budget.str.extract(f'({form_one}|{form_two})', flags=re.IGNORECASE)[0].apply(parse_dollars)
wiki_movies_df.drop('Budget', axis=1, inplace=True)
sorted(wiki_movies_df.columns.tolist())

# +
# Parse the release date data
# Create a variable to hold the non-null values of release dates
release_date = wiki_movies_df['Released'].dropna().apply(lambda x: ' '.join(x) if type(x) == list else x)

# Specify the different date forms
date_form_one = r'(?:January|Februrary|March|April|May|June|July|August|September|October|November|December)\s[123]\d,\s\d{4}'
date_form_two = r'\d{4}.[01]\d.[123]\d'
date_form_three = r'(?:January|Februrary|March|April|May|June|July|August|September|October|November|December)\s\d{4}'
date_form_four = r'\d{4}'

# Extract the dates
release_date.str.extract(f'({date_form_one}|{date_form_two}|{date_form_three}|{date_form_four})', flags=re.IGNORECASE)

# Use Pandas to_datetime() function to parse the dates
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)

# +
# Parse the running time
# Create a variable to hold the non-null values of running time
running_time = wiki_movies_df['Running time'].dropna().apply(lambda x: ' '.join(x) if type(x) == list else x)

# check the number of running times that match 100 minutes 
running_time.str.contains(r'^\d*\s*minutes$', flags=re.IGNORECASE).sum()
# -

# Check to see what the other running time entries look like
running_time[running_time.str.contains(r'^\d*\s*minutes$', flags=re.IGNORECASE) != True]

# make the search more general by marking on the beginning of "minutes"
running_time.str.contains(r'^\d*\s*m', flags=re.IGNORECASE).sum()

# Check to see what the entries from above look like
running_time[running_time.str.contains(r'^\d*\s*m', flags=re.IGNORECASE) != True]

# Relax the beginning of the string criteria (remove the caret)
running_time[running_time.str.contains(r'\d*\s*m', flags=re.IGNORECASE) != True]

running_time_extract = running_time.str.extract(r'(\d+)\s*ho?u?r?s?\s*(\d*)|(\d+)\s*m')
running_time_extract

# +
# Convert values in running time df to numeric values
running_time_extract = running_time_extract.apply(lambda col: pd.to_numeric(col, errors='coerce')).fillna(0)

# Apply a funciton to convert hour and minute capture groups to minutes 
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)

# Drop the running tiem from the dataset
wiki_movies_df.drop('Running time', axis=1, inplace=True)
sorted(wiki_movies_df.columns.tolist())
# -

# Clean the Kaggle movie data
kaggle_metadata.dtypes

# Check to see if the adult column are all boolean values
kaggle_metadata['adult'].value_counts()

# Remove the bad data from the adult column
kaggle_metadata[~kaggle_metadata['adult'].isin(['True', 'False'])]

# Drop the adult movies from the dataset and keep non-adult movies
kaggle_metadata = kaggle_metadata[kaggle_metadata['adult'] == 'False'].drop('adult',axis='columns')

kaggle_metadata['video'].value_counts()

# Convert the video data
kaggle_metadata['video'] == 'True'
kaggle_metadata['video'] = kaggle_metadata['video'] == 'True'


# Convert the numeric columns
kaggle_metadata['budget'] = kaggle_metadata['budget'].astype(int)
kaggle_metadata['id'] = pd.to_numeric(kaggle_metadata['id'], errors='raise')
kaggle_metadata['popularity'] = pd.to_numeric(kaggle_metadata['popularity'], errors='raise')

# Convert release date to datetime
kaggle_metadata['release_date'] = pd.to_datetime(kaggle_metadata['release_date'])

wiki_movies_df.dtypes

# +
# Reasonability Checks on Ratings Data
# -









StarcoderdataJupyterScriptsDedupFiltered