<jupyter_start><jupyter_text>Extract Data<jupyter_code># A comprehensive list of movies available on various streaming platforms (IMDb dataset)
streaming_csv = "Resources/MoviesOnStreamingPlatforms_updated.csv"
streaming_df_orig = pd.read_csv(streaming_csv)
streaming_df_orig.head()
# The CSV file contains 85,855 movies with 22 attributes
movies_csv = "Resources/imdb_movies.csv"
movies_df_orig = pd.read_csv(movies_csv, low_memory=False)
movies_df_orig.head()
# The CSV file contains 297,705 cast members with 20 attributes
names_csv = "Resources/imdb_names.csv"
names_df_orig = pd.read_csv(names_csv)
names_df_orig.head()
# This CSV file contains 835,513 cast members roles in movies with 6 attributes
titles_csv = "Resources/imdb_title_principals.csv"
titles_df_orig = pd.read_csv(titles_csv)
titles_df_orig.head()<jupyter_output><empty_output><jupyter_text>Clean Data  Streaming DataFrame<jupyter_code># Format Streaming data column names
streaming_df_1 = streaming_df_orig.copy()
streaming_df_1.columns = streaming_df_1.columns.str.lower()
streaming_df_1.head()
# Clean Streaming data column names
streaming_df_2 = streaming_df_1.rename(columns={
    'age': 'target_age',
    'imdb': 'imdb_rating',
    'rotten tomatoes': 'rotten_tomatoes_converted',
    'prime video': 'prime_video'
})

streaming_df_3 = streaming_df_2.drop(columns='unnamed: 0')
streaming_df_3.head()
# streaming_df_3.dtypes
# Clean 'rotten_tomatoes' rating to comparable unit as 'imdb' rating to allow aggregation and comparison
# Rotten Tomatoes rating was initially percentage (max 100%) while IMDb Metascore rating is absolute value (max 10).

streaming_df_4 = streaming_df_3.copy()
streaming_df_4['rotten_tomatoes_converted'] = streaming_df_4['rotten_tomatoes_converted'].str.replace(r'%', r'.0').astype('float') / 10.0
# Divide by 10 instead of 100 to convert percentage to absolue value with max rating of 10

streaming_df_4.head()
# streaming_df_4.dtypes<jupyter_output><empty_output><jupyter_text>Movies DataFrame<jupyter_code># Clean column data since many datapoints overlap with Streaming DataFrame
movies_df_1 = movies_df_orig[['imdb_title_id', 'title', 'year', 'description']].copy()
movies_df_1.head()
# movies_df_1.dtypes
# Locate non-integer year from Movies df preventing series reclass to integer necessary to merge
movies_df_1.loc[movies_df_1['year'] == 'TV Movie 2019']
# Clean year value preventing merge by replacing with year value
movies_df_2 = movies_df_1.replace(to_replace='TV Movie 2019', value='2019')
movies_df_2.loc[movies_df_2['title'] == 'Bad Education']
# Reclass year value to integer
movies_df_2['year'] = movies_df_2['year'].astype('int64')
movies_df_2.dtypes
# Inner merge to only view movies available on streaming platforms
streaming_df_merged = streaming_df_4.merge(movies_df_2, how='inner', on=['title','year'])
streaming_df_merged.set_index('imdb_title_id', inplace=True)
streaming_df_clean = streaming_df_merged.drop('id', 1)
streaming_df_clean<jupyter_output><empty_output><jupyter_text>Names DataFrame<jupyter_code>names_df_1 = names_df_orig.copy()

# Split spouses_string to find most recent spouse
print(f"spouses max value: {names_df_1['spouses'].max()}")
names_df_1.head()
# Split out most recent spouse name
names_df_2 = names_df_1.copy()
names_df_2['spouse_last'] = names_df_2['spouses_string'].str.split('(', expand=True)[0]
names_df_2.head()
names_df_3 = names_df_2.copy()
names_df_3['place_of_birth_1'] = names_df_2['place_of_birth'].str.split(',', expand=True)[0]
names_df_3['place_of_birth_2'] = names_df_3['place_of_birth'].str.split(',', expand=True)[1]
names_df_3['place_of_birth_3'] = names_df_3['place_of_birth'].str.split(',', expand=True)[2]
names_df_3['place_of_birth_4'] = names_df_3['place_of_birth'].str.split(',', expand=True)[3]
names_df_3.head()
names_df_4 = names_df_3.copy()
names_df_4['place_of_death_1'] = names_df_3['place_of_death'].str.split(',', expand=True)[0]
names_df_4['place_of_death_2'] = names_df_3['place_of_death'].str.split(',', expand=True)[1]
names_df_4['place_of_death_3'] = names_df_3['place_of_death'].str.split(',', expand=True)[2]
names_df_4['place_of_death_4'] = names_df_3['place_of_death'].str.split(',', expand=True)[3]
names_df_4.head()
# Rename to clarify column unit of measure
names_df_5 = names_df_4.rename(columns={
    'height': 'height_cm'
})

# Drop unnecessary columns and reorder
names_df_5 = names_df_5[['imdb_name_id',
                         'name',
                         'birth_name',
                         'height_cm',
                         'bio',
                         'date_of_birth',
                         'place_of_birth_1',
                         'place_of_birth_2',
                         'place_of_birth_3',
                         'place_of_birth_4',
                         'date_of_death',
                         'place_of_death_1',
                         'place_of_death_2',
                         'place_of_death_3',
                         'place_of_death_4',
                         'reason_of_death',
                         'spouse_last',
                         'spouses',
                         'divorces',
                         'spouses_with_children',
                         'children'
                        ]]

names_df_5.set_index('imdb_name_id', inplace=True)
names_df_5<jupyter_output><empty_output><jupyter_text>Titles DataFrame<jupyter_code>titles_df_1 = titles_df_orig.copy()
titles_df_1.set_index('imdb_title_id', inplace=True)
titles_df_1<jupyter_output><empty_output><jupyter_text>Load DataFrames into Database<jupyter_code># Password config.py hidden using .gitignore
from config import pw
# Create connection to local database
db = 'streaming_db'
rds_connection_string = (f'postgres:{pw}@localhost:5432/{db}')

engine = create_engine(f'postgresql://{rds_connection_string}')
# Check for existing table names
engine.table_names()
# Load DataFrame into database
streaming_df_clean.to_sql(name='movies', con=engine, if_exists='append', index=True)
# Load DataFrame into database
names_df_5.to_sql(name='names', con=engine, if_exists='append', index=True)
# Load DataFrame into database
titles_df_1.to_sql(name='titles', con=engine, if_exists='append', index=True)
# Confirm data was added by querying each table
pd.read_sql_query('select * from movies', con=engine)
# Confirm data was added by querying each table
pd.read_sql_query('select * from names', con=engine)
# Confirm data was added by querying each table
pd.read_sql_query('select * from titles', con=engine)<jupyter_output><empty_output><jupyter_text>Aggregation<jupyter_code>print("Average IMDb rating of streaming movies: ")
pd.read_sql_query('select avg(imdb_rating) from movies', con=engine)
print("Average Rotten Tomatoes Converted rating of streaming movies: ")
pd.read_sql_query('select avg(rotten_tomatoes_converted) from movies', con=engine)
# Actors/actresses/personnel born in Chicago
pd.read_sql_query("select * from names where place_of_birth_1='Chicago'", con=engine)
# Actors/actresses/personnel whose place of death is Chicago
pd.read_sql_query("select * from names where place_of_death_1='Chicago'", con=engine)
# Movies streaming on Netflix with IMDb rating greater than or equal to 7
pd.read_sql_query('select * from movies where netflix=1 and imdb_rating>=7', con=engine)
# Movies streaming on Hulu with IMDb rating greater than or equal to 7
pd.read_sql_query('select * from movies where hulu=1 and imdb_rating>=7', con=engine)
# Movies streaming on Amazon Prime with IMDb rating greater than or equal to 7
pd.read_sql_query('select * from movies where prime_video=1 and imdb_rating>=7', con=engine)
# Movies streaming on Disney+ with IMDb rating greater than or equal to 7
pd.read_sql_query('select * from movies where "disney+"=1 and imdb_rating>=7', con=engine)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11113946663264004, 'fraction_numerical': 0.02143677427587087, 'mean_word_length': 5.0107361963190185, 'pattern_counts': {'":': 0, '<': 29, '<?xml version=': 0, '>': 33, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '559905', 'license': 'MIT', 'n_tokens_mistral': 2749, 'n_tokens_neox': 2624, 'n_words': 562, 'path': 'etl-project.ipynb', 'repo_name': 'junettel/ETL-project', 'size': '7837'}