<jupyter_start><jupyter_text>Movie Data ETL Pipeline - TransformThis notebook will continue the transform step of the pipeline by merging the Wikipedia data and the Kaggle data. Some additional cleaning and transformations will have to be done on the combined data before it is ready to be loaded into a SQL database. Dependencies and data<jupyter_code># Dependencies
import os
import numpy as np
import pandas as pd
import matplotlib as mpl
import matplotlib.pyplot as plt
%matplotlib inline
# Plot settings
plot_styles = mpl.style.available
mpl.style.use(plot_styles[0])
mpl.rcParams['figure.figsize'] = (12, 4)
mpl.rcParams['font.size'] = 15
# Path to data directory
data_path = os.path.join('..', 'data')

# Paths to data files
wmovies_path = os.path.join(data_path, 'wiki_movies.pkl')
kmovies_path = os.path.join(data_path, 'kaggle_movies.pkl')
print(wmovies_path)
print(kmovies_path)
# Clean Wikipedia movie data
wmovies_df = pd.read_pickle(wmovies_path)
wmovies_df.info()
# Clean Kaggle movie data
kmovies_df = pd.read_pickle(kmovies_path)
kmovies_df.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 45408 entries, 0 to 45465
Data columns (total 18 columns):
 #   Column                Non-Null Count  Dtype         
---  ------                --------------  -----         
 0   budget                45408 non-null  int64         
 1   genres                45408 non-null  object        
 2   id                    45408 non-null  int64         
 3   imdb_id               45407 non-null  object        
 4   original_language     45397 non-null  object        
 5   overview              44456 non-null  object        
 6   popularity            45405 non-null  float64       
 7   poster_path           45027 non-null  object        
 8   production_companies  45405 non-null  object        
 9   production_countries  45405 non-null  object        
 10  release_date          45325 non-null  datetime64[ns]
 11  revenue               45405 non-null  float64       
 12  runtime               45150 non-null  float64       
 13  spoken_languages[...]<jupyter_text>Join movie data from both sources<jupyter_code># Merge data
movies_df = pd.merge(wmovies_df, kmovies_df, how='inner', on='imdb_id', suffixes=['_wiki', '_kaggle'])
movies_df.head(2)<jupyter_output><empty_output><jupyter_text>Find redundant columnsSince the 2 dataframes being joined contain similar data, they will have several of the same columns. For these columns, it would be redundant to keep both, so we will examine each pair to see what is the best course of action to take. In most cases, we will likely just keep the one with more consistent data. A table will be created to keep track of the action we take on each pair.<jupyter_code># List cols
movies_df.columns.sort_values()
# List of redundant col name pairs (wikipedia col, kaggle col)
redundant_cols = [
    ('box_office', 'revenue'),
    ('budget_wiki', 'budget_kaggle'),
    ('duration', 'runtime'),
    ('languages', 'original_language'),
    ('production_companies_wiki', 'production_companies_kaggle'),
    ('release_date_wiki', 'release_date_kaggle'),
    ('title_wiki', 'title_kaggle')
]

# Create a table to keep track of the action taken for each pair
redundant_cols = pd.DataFrame(redundant_cols, columns=['wikipedia', 'kaggle'])
redundant_cols['action'] = ''
redundant_cols<jupyter_output><empty_output><jupyter_text>Inspect title columns<jupyter_code># Extract title cols
title = movies_df[['title_wiki', 'title_kaggle']]

# Rows where titles are different between the 2 cols
print(title.query('title_wiki != title_kaggle').shape[0])
title.query('title_wiki != title_kaggle').head(4)<jupyter_output>429<jupyter_text>It looks like the Kaggle data has more complete `title`s. If it contains no missing `title`s, or at least less than the Wikipedia data, then we will drop the Wikipedia `title`s and keep the Kaggle `title`s.<jupyter_code># Check for null titles in the Kaggle col
title[(title['title_kaggle'] == '') | (title['title_kaggle'].isnull())].shape[0]
# Add action for title - drop the Wikipedia column
redundant_cols.loc[6, 'action'] = 'drop wiki col'
redundant_cols<jupyter_output><empty_output><jupyter_text>Inspect language columns<jupyter_code># Extract language cols
lang = movies_df[['languages', 'original_language']]

# Rows where languages are different between the 2 cols
print(lang.query('languages != original_language').shape[0])
lang.query('languages != original_language').head(4)<jupyter_output>5983<jupyter_text>The Wikipedia `languages` column contains lists, so it would be easier to keep Kaggle's `original_language` column, assuming there are no missing or strange values.<jupyter_code># Check for null languages the Kaggle col
print(lang[(lang['original_language'] == '') | (lang['original_language'].isnull())].shape[0])

# Check Kaggle column vals
lang['original_language'].unique()
# Add action for language - drop the Wikipedia col
redundant_cols.loc[3, 'action'] = 'drop wiki col'
redundant_cols<jupyter_output><empty_output><jupyter_text>Inspect production company columns<jupyter_code># Extract production company cols
production = movies_df[['production_companies_wiki', 'production_companies_kaggle']]

# Rows where values are different between the 2 cols
print(production.query('production_companies_wiki != production_companies_kaggle').shape[0])
production.query('production_companies_wiki != production_companies_kaggle').head(4)
# Check for null vals in the Kaggle col
print(production[(production['production_companies_kaggle'] == '') | 
                 (production['production_companies_kaggle'].isnull())].shape[0])

# Check Kaggle col vals
production['production_companies_kaggle'].unique()[:4]<jupyter_output>0<jupyter_text>We will be keeping the Kaggle column because it's more descriptive than the Wikipedia values.<jupyter_code># Add action to production company - drop the Wikipedia column
redundant_cols.loc[4, 'action'] = 'drop wiki col'
redundant_cols<jupyter_output><empty_output><jupyter_text>Inspect budget columns<jupyter_code># Extract budget cols
budget = movies_df[['budget_wiki', 'budget_kaggle']]

# Scatterplot
budget.plot(kind='scatter', x='budget_wiki', y='budget_kaggle', title='Budget Data Comparison')
plt.show()
# Num of 0 vals
print('Number of 0s in Wikipedia `budget`:', (budget['budget_wiki'] == 0).sum())
print('Number of 0s in Kaggle `budget`:', (budget['budget_kaggle'] == 0).sum())

# Check null vals
budget.isnull().sum()<jupyter_output>Number of 0s in Wikipedia `budget`: 0
Number of 0s in Kaggle `budget`: 2350<jupyter_text>The Wikipedia data has 1589 missing `budget` values, but the Kaggle data has 2350 `budget` values of 0, which are probably missing values. We will use `budget_kaggle` to fill any missing `budget_wiki` and then drop `budget_kaggle`.<jupyter_code># Add action to budget - fill Wikipedia's missing vals with Kaggle's budget vals, then drop the Kaggle col
redundant_cols.loc[1, 'action'] = 'fill wiki missing, drop kaggle col'
redundant_cols<jupyter_output><empty_output><jupyter_text>Inspect revenue columns<jupyter_code># Extract budget cols
revenue = movies_df[['box_office', 'revenue']]

# Scatterplot
revenue.plot(kind='scatter', x='box_office', y='revenue', title='Revenue Data Comparison')
plt.show()
# Number of 0 vals
print('Number of 0s in Wikipedia `box_office`:', (revenue['box_office'] == 0).sum())
print('Number of 0s in Kaggle `revenue`:', (revenue['revenue'] == 0).sum())

# Check null vals
revenue.isnull().sum()<jupyter_output>Number of 0s in Wikipedia `box_office`: 0
Number of 0s in Kaggle `revenue`: 2326<jupyter_text>This is the same case as `budget` so we will take the same action for `revenue`.<jupyter_code># Add action to revenue - same as budget
redundant_cols.loc[0, 'action'] = 'fill wiki missing, drop kaggle col'
redundant_cols<jupyter_output><empty_output><jupyter_text>Inspect runtime columns<jupyter_code># Extract budget cols
run = movies_df[['duration', 'runtime']]

# Scatterplot
run.plot(kind='scatter', x='duration', y='runtime', title='Runtime Data Comparison')
plt.show()
# Number of 0 vals
print('Number of 0s in Wikipedia `duration`:', (run['duration'] == 0).sum())
print('Number of 0s in Kaggle `runtime`:', (run['runtime'] == 0).sum())

# Check null vals
run.isnull().sum()<jupyter_output>Number of 0s in Wikipedia `duration`: 0
Number of 0s in Kaggle `runtime`: 14<jupyter_text>Even though the Wikipedia data (`duration`) contains less missing/0 values, it has some extreme outliers, so we will actually use it to fill in the Kaggle data's (`runtime`) zero values and then drop it (drop `duration`).<jupyter_code># Add action to runtime - fill the zero vals in the Kaggle col, then drop the Wikipedia col
redundant_cols.loc[2, 'action'] = 'fill kaggle missing, drop wiki col'
redundant_cols<jupyter_output><empty_output><jupyter_text>Inspect release date column<jupyter_code># Extract budget cols
release = movies_df[['release_date_wiki', 'release_date_kaggle']]

# Scatterplot
release.plot(x='release_date_wiki', y='release_date_kaggle', style='.', 
             legend=False, ylabel='release_date_kaggle', title='Release Date Data Comparison')
plt.xticks(rotation=0, ha='center')
plt.show()<jupyter_output><empty_output><jupyter_text>There is a strange outlier with a `release_date` before 1960 in the Kaggle data but after 2000 in the Wikipedia data. Let's inspect this movie.<jupyter_code># Inspect outlier
release_date_cols = ['title_wiki', 'title_kaggle', 'release_date_wiki', 'release_date_kaggle']
release_outlier = movies_df.loc[(movies_df['release_date_wiki'] > '2000') & 
                                (movies_df['release_date_kaggle'] < '1960'), release_date_cols]
release_outlier
# Check null vals
release.isnull().sum()<jupyter_output><empty_output><jupyter_text>It looks like 2 different movies shared an `imdb_id` so they were merged together. We will be dropping this single movie and the Wikipedia `release_date` since it has some missing values.<jupyter_code># Add action to release date - drop outlier and drop the Wikipedia col
redundant_cols.loc[5, 'action'] = 'drop outlier, drop wiki col'
redundant_cols<jupyter_output><empty_output><jupyter_text>Drop redundant columnsWith a plan defined for each pair of redundant columns in the table above, we will now be executing these actions.<jupyter_code># Drop `title_wiki`, `languages`, and `production_companies_wiki`
movies_df.drop(['title_wiki', 'languages', 'production_companies_wiki'], axis=1, inplace=True)

# Drop the `release_date` outlier then drop `release_date_wiki`
movies_df.drop(release_outlier.index, inplace=True)
movies_df.drop('release_date_wiki', axis=1, inplace=True)
movies_df.shape
def filla_dropb(cola, colb, data):
    
    """ 
    Fill missing values in column a (keep) using values in column b (drop), 
    then drop column b.
    
    Parameters
    ----------
    cola : str
        Name of column to keep
    colb : str
        Name of column to drop
    data : pandas.core.frame.DataFrame
        Data with redundant columns
        
    Returns
    -------
    pandas.core.frame.DataFrame
        Data with redundant column dropped
    """
    
    # Copy df
    df = data.copy()
    
    def filla(row):
        cond1 = pd.isnull(row[cola]) or row[cola] == 0 # col a val is null
        cond2 = pd.notnull(row[colb]) and row[colb] != 0 # col b val is not null
        return row[colb] if cond1 and cond2 else row[cola]
            
    df[cola] = df.apply(filla, axis=1)
    df.drop(colb, axis=1, inplace=True)
    return df
    

# Fill `budget`, `revenue`, and `runtime` missing vals, then drop `Budget`, `Box office`, `Duration`
movies_df = filla_dropb('box_office', 'revenue', data=movies_df)
movies_df = filla_dropb('budget_wiki', 'budget_kaggle', data=movies_df)
movies_df = filla_dropb('runtime', 'duration', data=movies_df)
movies_df.head(2)<jupyter_output><empty_output><jupyter_text>Rename and reorder columns<jupyter_code># List cols
movies_df.columns
# Col order
col_order = ['id', 'imdb_id', 'imdb_link', 'url', 'poster_path', 'title_kaggle', 'overview',
             'release_date_kaggle', 'year', 'runtime', 'budget_wiki', 'box_office', 
             'genres', 'country', 'original_language', 'spoken_languages',
             'status', 'popularity', 'vote_average', 'vote_count', 
             'producers', 'writers', 'director', 'cinematographers', 'editors', 'composers', 'stars',
             'production_companies_kaggle', 'production_countries', 'distributor']

# New col names
col_names = ['movie_id', 'imdb_id', 'imdb_link', 'url', 'poster_path', 'title', 'overview',
             'release_date', 'year', 'runtime', 'budget', 'revenue', 
             'genres', 'country', 'language', 'spoken_languages',
             'status', 'popularity', 'vote_average', 'vote_count', 
             'producers', 'writers', 'director', 'cinematographers', 'editors', 'composers', 'stars',
             'production_companies', 'production_countries', 'distributor']

# Reorder and rename cols
movies_df = movies_df[col_order]
movies_df = movies_df.rename(dict(zip(col_order, col_names)), axis=1)
movies_df.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 5982 entries, 0 to 5982
Data columns (total 30 columns):
 #   Column                Non-Null Count  Dtype         
---  ------                --------------  -----         
 0   movie_id              5982 non-null   int64         
 1   imdb_id               5982 non-null   object        
 2   imdb_link             5982 non-null   object        
 3   url                   5982 non-null   object        
 4   poster_path           5981 non-null   object        
 5   title                 5982 non-null   object        
 6   overview              5977 non-null   object        
 7   release_date          5982 non-null   datetime64[ns]
 8   year                  5982 non-null   int64         
 9   runtime               5982 non-null   float64       
 10  budget                4600 non-null   float64       
 11  revenue               5149 non-null   float64       
 12  genres                5982 non-null   object        
 13  country           [...]<jupyter_text>Save movie data<jupyter_code># Save data
movies_path = os.path.join(data_path, 'movies.pkl')
movies_df.to_pickle(movies_path)
pd.read_pickle(movies_path).head(2)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11186086956521739, 'fraction_numerical': 0.021982608695652175, 'mean_word_length': 4.024816497728067, 'pattern_counts': {'":': 0, '<': 80, '<?xml version=': 0, '>': 80, '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': '21', 'dir': 'jupyter-structured-clean-dedup', 'id': '204559', 'license': 'MIT', 'n_tokens_mistral': 4910, 'n_tokens_neox': 4565, 'n_words': 1320, 'path': 'movie-etl/notebooks/2-transform.ipynb', 'repo_name': 'tri-bui/sandbox-dbs', 'size': '14375'}