<jupyter_start><jupyter_text>Drafting functions/workflow for data cleaning + feature engineering, to be used on full data  Imports<jupyter_code>import os
import numpy as np
import pandas as pd

pd.set_option('display.max_rows', 100)
pd.set_option('display.max_columns', 100)
pd.set_option('display.width', 100)
os.getcwd();
info_file = '/Users/ryanrappa/Desktop/dsi/film-profit-prediction/csv_files/for_draft_cleaning/info_10k.csv'
credits_file = '/Users/ryanrappa/Desktop/dsi/film-profit-prediction/csv_files/for_draft_cleaning/credits_10k.csv'
releases_file = '/Users/ryanrappa/Desktop/dsi/film-profit-prediction/csv_files/for_draft_cleaning/releases_10k.csv'
#info table contains basic film details like genre, budget, revenue, language, runtime, etc.
info_df = pd.read_csv(info_file)

#credits table contains cast and crew
credits_df = pd.read_csv(credits_file)

#releases table contains info on which countries each film was released in
releases_df = pd.read_csv(releases_file)<jupyter_output><empty_output><jupyter_text>Cleaning + new features  Because of the way data was collected, last row of each table has the headers. Fixing that:<jupyter_code>info_df.columns = info_df.iloc[-1:, :].squeeze()
info_df = info_df.iloc[:-1, :]
credits_df.columns = credits_df.iloc[-1:, :].squeeze()
credits_df = credits_df.iloc[:-1, :]
releases_df.columns = releases_df.iloc[-1:, :].squeeze()
releases_df = releases_df.iloc[:-1, :]<jupyter_output><empty_output><jupyter_text>Cleaning info table  Getting each film's genre:<jupyter_code>def get_first_genre(lod_str):
    '''
    Function for use with pandas apply method.
    --------
    INPUT
    lod_str (str): a list of dictionaries in the form of a string
    --------
    OUTPUT
    (str): name of the first genre from the list of dicts
    '''
    lst_of_dicts = eval(lod_str)
    if len(lst_of_dicts) > 0:
        for i in range(len(lst_of_dicts)):
            return lst_of_dicts[i]['name']
    else:
        pass
info_df['genre'] = info_df['genres'].apply(get_first_genre)  #func in cell above<jupyter_output><empty_output><jupyter_text>Dropping unnecessary rows and columns from info table:<jupyter_code>#making sure relevant numerical columns are int/float type
info_df = info_df.astype({"budget": float, "id": int, "revenue": float, "runtime": float})
#filtering for only non-adult films,
# with revenue & budget > 0,
# original language english,
# runtime >= 80 minutes,
# and genre is not documentary.

info_df = info_df[(info_df['adult'] == 'False') & \
        (info_df['budget'] > 0) & \
        (info_df['revenue'] > 0) & \
        (info_df['original_language'] == 'en') & \
        (info_df['runtime'] >= 80.0) & \
        (info_df['genre'] != 'Documentary')]
#dropping columns from info_df that won't be needed for EDA
drop_cols = ['adult', 'backdrop_path', 'belongs_to_collection', 
             'genres', 'homepage', 'imdb_id', 'original_language', 'original_title', 
             'overview', 'popularity', 'poster_path', 'production_companies', 
             'production_countries', 'spoken_languages', 'status', 'tagline', 'video', 
             'vote_average', 'vote_count']

info_df = info_df.drop(drop_cols, axis=1)

### a few more cols to be dropped later for modelling, after EDA, feat. engineering, & joins: 
### ['id', 'release_date', 'runtime' (possibly), 'title', 'genre' (possibly)]<jupyter_output><empty_output><jupyter_text>Creating columns for (a) amount of profit or loss (revenue minus budget) and (b) whether each movie profited (binary, whether revenue > budget):<jupyter_code>info_df['profit'] = info_df['revenue'] - info_df['budget']
info_df['made_money'] = info_df['profit'] > 0
info_df['made_money'] = info_df['made_money'].astype('int')
#converting release date column from object type to datetime type
info_df['release_date'] = pd.to_datetime(info_df['release_date'])<jupyter_output><empty_output><jupyter_text>Cleaning credits table  Appending columns with top billed actors to the credits table:<jupyter_code>def get_top_cast(df, lod_col, billings):
    '''
    INPUT
    df: pandas dataframe w/ stringified lists of dictionaries containing cast
    lod_col (str): name of col w/ stringified lists of dictionaries
    billings (int): number of cast members to extract from each lod
    --------
    OUTPUT
    original df w/ new columns having actor names corresponding to each billing 
    number in this function (lower billing number = more prominent
    role; 0 = star of the movie, 1 = costar, etc.)
    '''
    for c in range(billings):
        billing = c
        for i in range(len(df)):
            lst_of_dicts = eval(df.at[i, lod_col]) #lod's are strings at first, hence eval
            if len(lst_of_dicts) > billing:
                dict_of_interest = lst_of_dicts[billing]
                actor = dict_of_interest['name']
                df.at[i, 'cast_'+str(billing)] = actor
            else:
                continue
    return df
credits_df = get_top_cast(credits_df, 'cast', 5)  #func in cell above<jupyter_output><empty_output><jupyter_text>Appending columns with director and screenwriters to the credits table:<jupyter_code>def get_director(lod_str):
    '''
    Function for use with pandas apply method.
    --------
    INPUT
    lod_str (str): a list of dictionaries in the form of a string
    --------
    OUTPUT
    (str): name of the first director from the list of dicts
    '''
    lst_of_dicts = eval(lod_str)
    if len(lst_of_dicts) > 0:
        for i in range(len(lst_of_dicts)):
            if lst_of_dicts[i]['job'] == 'Director':
                return lst_of_dicts[i]['name']
    else:
        pass
credits_df['director'] = credits_df['crew'].apply(get_director)  #func in cell above
def get_first_writer(lod_str):
    '''
    Function for use with pandas apply method.
    --------
    INPUT
    lod_str (str): a list of dictionaries in the form of a string
    --------
    OUTPUT
    (str): name of the first screenwriter from the list of dicts
    '''
    lst_of_dicts = eval(lod_str)
    if len(lst_of_dicts) > 0:
        for i in range(len(lst_of_dicts)):
            if lst_of_dicts[i]['job'] == 'Screenplay' or \
            lst_of_dicts[i]['job'] == 'Writer' or \
            lst_of_dicts[i]['job'] == 'Author':
                return lst_of_dicts[i]['name']
    else:
        pass
credits_df['writer'] = credits_df['crew'].apply(get_first_writer)  #func in cell above
#decided not to get second writer becuse relatively few films have one in the dataset
creds_drop_cols = ['cast', 'crew']
credits_df = credits_df.drop(creds_drop_cols, axis=1)
#making sure id column is int so dataframes can be merged later
credits_df = credits_df.astype({"id": int})<jupyter_output><empty_output><jupyter_text>Dropping rows without at least 5 actors, or missing director, or missing writer:<jupyter_code>credits_df = credits_df.dropna(subset=['cast_4', 'director', 'writer'])<jupyter_output><empty_output><jupyter_text>Cleaning releases table  Getting number of countries each film was released in and dropping nulls:<jupyter_code>def get_releases_count(lod_str):
    '''
    Function for use with pandas apply method.
    --------
    INPUT
    lod_str (str): a list of dictionaries in the form of a string
    --------
    OUTPUT
    (int): number of countries/territories the film was released in
    '''
    lst_of_dicts = eval(lod_str)
    if len(lst_of_dicts) > 0:
        return len(lst_of_dicts)
    else:
        pass
releases_df['releases'] = releases_df['countries'].apply(get_releases_count)  #func in cell above
releases_df = releases_df.dropna()
releases_df = releases_df.drop('countries', axis=1)
#making sure id column is int so dataframes can be merged later
releases_df = releases_df.astype({"id": int})<jupyter_output><empty_output><jupyter_text>Joining the tables  Note: when the keys to join on are pd columns, not indices, better to use merge method<jupyter_code>step1 = info_df.merge(credits_df, on='id', how='inner')
df = step1.merge(releases_df, on='id', how='inner')<jupyter_output><empty_output><jupyter_text>Checking for outliers/faulty data (HAVE TO REEXAMINE THIS ON FULL DATA)<jupyter_code>pd.set_option('float_format', '{:f}'.format)  #so that floats will print w/o scientific notat.
df.describe()  #checking for outliers/faulty data, looks like we have an issue in budget/revenue
df = df[df['budget'] > 1000] #dropping rows with faulty budget numbers
df = df[df['revenue'] > 10300] #dropping rows with faulty revenue numbers
df.describe()  #much better (the 12k revenue and 12k budget are not mistakes)
# resetting index for easier for-looping later
df = df.reset_index().drop('index', axis=1)<jupyter_output><empty_output><jupyter_text>Getting cast & crew lifetime revenue, profits,  of films,  of profitable films<jupyter_code>def get_cast_metrics(df, cast_cols):
    '''
    Iterates over columns of cast members
    and generates new columns with the total
    revenue, profit, number of movies, and number of
    profitable movies that cast collectively has been in
    up until the date of release in each row.
    --------
    INPUT
    df: dataframe with cast columns
    cast_cols: list of column names (str) with the cast
    '''
    rev_cols = []
    prof_cols = []
    prof_count_cols = []
    tot_count_cols = []
    for col_name in cast_cols:
        for i in range(len(df)):
            #1. getting a dataframe with actor's movies to date only
            name = df.at[i, col_name]
            release_date = df.at[i, 'release_date']
            df_to_sum = df[(df['release_date'] < release_date) & \
                           ((df['cast_0'] == name) | (df['cast_1'] == name) | \
                            (df['cast_2'] == name) | (df['cast_3'] == name) | \
                            (df['cast_4'] == name))]
            #2. creating new cols for each actor's lifetime rev, prof, etc. to date
            df.at[i, str(col_name)+'_rev'] = df_to_sum['revenue'].sum()
            df.at[i, str(col_name)+'_prof'] = df_to_sum['profit'].sum()
            df.at[i, str(col_name)+'_films'] = df_to_sum['made_money'].count()
            df.at[i, str(col_name)+'_prof_films'] = df_to_sum['made_money'].sum()
        
        #2.1 appending those new col names to a list for subsequent summing and dropping
        rev_cols.append(str(col_name)+'_rev')
        prof_cols.append(str(col_name)+'_prof')
        prof_count_cols.append(str(col_name)+'_prof_films')
        tot_count_cols.append(str(col_name)+'_films')

    #3. summing/dropping those cols so we just have cols representing top5 cast as a whole
    
    rev_to_sum = df[rev_cols].copy()
    prof_to_sum = df[prof_cols].copy()
    prof_count_to_sum = df[prof_count_cols].copy()
    tot_count_to_sum = df[tot_count_cols].copy()
    
    df['cast_rev'] = rev_to_sum.sum(axis=1)
    df['cast_prof'] = prof_to_sum.sum(axis=1)
    df['cast_films'] = tot_count_to_sum.sum(axis=1)
    df['cast_prof_films'] = prof_count_to_sum.sum(axis=1)
    
    df = df.drop(rev_cols, axis=1)
    df = df.drop(prof_cols, axis=1)
    df = df.drop(prof_count_cols, axis=1)
    df = df.drop(tot_count_cols, axis=1)
    
    return df
cast_cols = ['cast_0', 'cast_1', 'cast_2', 'cast_3', 'cast_4']

#this may take a while to run, reason to use Spark on full data:
df = get_cast_metrics(df, cast_cols)  #func from cell above
def get_director_metrics(df, director_col):
    '''
    Iterates over column with director
    and generates new columns with the total
    revenue, profit, number of movies, and number of
    profitable movies the director has been in
    up until the date of release for each row.
    --------
    INPUT
    df: dataframe with director columns
    director_col: column name (str) with director
    '''
    for i in range(len(df)):
        #1. getting a dataframe with director's movies to date only
        name = df.at[i, director_col]
        release_date = df.at[i, 'release_date']
        df_to_sum = df[(df['release_date'] < release_date) & (df[director_col] == name)]
        
        #2. creating new cols for each director's lifetime rev, prof, etc. to date
        df.at[i, 'dir_rev'] = df_to_sum['revenue'].sum()
        df.at[i, 'dir_prof'] = df_to_sum['profit'].sum()
        df.at[i, 'dir_films'] = df_to_sum['made_money'].count()
        df.at[i, 'dir_prof_films'] = df_to_sum['made_money'].sum()
    
    return df
df = get_director_metrics(df, 'director')  #func from cell above
def get_writer_metrics(df, writer_col):
    '''
    Iterates over column with writer
    and generates new columns with the total
    revenue, profit, number of movies, and number of
    profitable movies the writer has been in
    up until the date of release for each row.
    --------
    INPUT
    df: dataframe with writer columns
    writer_col: column name (str) with writer
    '''
    for i in range(len(df)):
        #1. getting a dataframe with writer's movies to date only
        name = df.at[i, writer_col]
        release_date = df.at[i, 'release_date']
        df_to_sum = df[(df['release_date'] < release_date) & (df[writer_col] == name)]
        
        #2. creating new cols for each writer's lifetime rev, prof, etc. to date
        df.at[i, 'writ_rev'] = df_to_sum['revenue'].sum()
        df.at[i, 'writ_prof'] = df_to_sum['profit'].sum()
        df.at[i, 'writ_films'] = df_to_sum['made_money'].count()
        df.at[i, 'writ_prof_films'] = df_to_sum['made_money'].sum()
    
    return df
df = get_writer_metrics(df, 'writer')  #func from cell above
df.head()<jupyter_output><empty_output><jupyter_text>Getting average of cast & crew revenue, profit, etc. for each film's competitors (i.e. all other films released with +/- 2 weeks of each film):<jupyter_code>def get_competitor_metrics(df, id_col):
    '''
    Iterates over each film (each row)
    and generates new columns with the average
    revenue, profit, number of movies, and number of
    profitable movies of the casts/crews from competing movies
    (i.e. those released within +/- 2 weeks) have been in.
    --------
    INPUT
    df: dataframe with cast columns
    id_col: column name (str) with film id
    '''
    for i in range(len(df)):
        #1. getting a dataframe with competing films
        id_ = df.at[i, id_col]
        release_date = df.at[i, 'release_date']
        df_to_avg = df[(df['release_date'] >= (release_date - pd.to_timedelta(14, unit='d'))) & \
               (df['release_date'] <= (release_date + pd.to_timedelta(14, unit='d'))) & \
               (df['id'] != id_)]
        
        #2. creating new cols for competing cast/crews' avg lifetime rev, prof, etc.
        df.at[i, 'compet_cast_rev'] = df_to_avg['cast_rev'].mean()
        df.at[i, 'compet_cast_prof'] = df_to_avg['cast_prof'].mean()
        df.at[i, 'compet_cast_films'] = df_to_avg['cast_films'].mean()
        df.at[i, 'compet_cast_prof_films'] = df_to_avg['cast_prof_films'].mean()
        
        df.at[i, 'compet_dir_rev'] = df_to_avg['dir_rev'].mean()
        df.at[i, 'compet_dir_prof'] = df_to_avg['dir_prof'].mean()
        df.at[i, 'compet_dir_films'] = df_to_avg['dir_films'].mean()
        df.at[i, 'compet_dir_prof_films'] = df_to_avg['dir_prof_films'].mean()
        
        df.at[i, 'compet_writ_rev'] = df_to_avg['writ_rev'].mean()
        df.at[i, 'compet_writ_prof'] = df_to_avg['writ_prof'].mean()
        df.at[i, 'compet_writ_films'] = df_to_avg['writ_films'].mean()
        df.at[i, 'compet_writ_prof_films'] = df_to_avg['writ_prof_films'].mean()
    
    return df
df = get_competitor_metrics(df, 'id')
df.head()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12705444273240885, 'fraction_numerical': 0.0060349255264509506, 'mean_word_length': 3.4543894766943093, 'pattern_counts': {'":': 6, '<': 57, '<?xml version=': 0, '>': 68, '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': '13', 'dir': 'jupyter-structured-clean-dedup', 'id': '124727', 'license': 'MIT', 'n_tokens_mistral': 5362, 'n_tokens_neox': 5151, 'n_words': 1389, 'path': 'notebooks/3_draft_data_cleaning.ipynb', 'repo_name': 'ryanlrappa/film-profit-prediction', 'size': '15576'}