<jupyter_start><jupyter_text>Cleaning the Kaggle metadata<jupyter_code>kaggle_metadata.dtypes
kaggle_metadata['adult'].value_counts()
kaggle_metadata[~kaggle_metadata['adult'].isin(['True','False'])]
kaggle_metadata = kaggle_metadata[kaggle_metadata['adult'] == 'False'].drop('adult',axis='columns')
kaggle_metadata['video'].value_counts()
kaggle_metadata['video'] = kaggle_metadata['video'] == 'True'
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')
kaggle_metadata['release_date'] = pd.to_datetime(kaggle_metadata['release_date'])<jupyter_output><empty_output><jupyter_text>Ratings Data<jupyter_code>ratings.info(null_counts=True)
pd.to_datetime(ratings['timestamp'], unit='s')
ratings['timestamp'] = pd.to_datetime(ratings['timestamp'], unit='s')
pd.options.display.float_format = '{:20,.2f}'.format
ratings['rating'].plot(kind='hist')
ratings['rating'].describe()<jupyter_output><empty_output><jupyter_text>Merging wiki_movies and kaggle_metadata<jupyter_code>movies_df = pd.merge(wiki_movies_df, kaggle_metadata, on='imdb_id', suffixes=['_wiki','_kaggle'])
movies_df[['title_wiki','title_kaggle']]
movies_df[movies_df['title_wiki'] != movies_df['title_kaggle']][['title_wiki','title_kaggle']]
# Show any rows where title_kaggle is empty
movies_df[(movies_df['title_kaggle'] == '') | (movies_df['title_kaggle'].isnull())]
movies_df.fillna(0).plot(x='running_time', y='runtime', kind='scatter')
movies_df.fillna(0).plot(x='budget_wiki',y='budget_kaggle', kind='scatter')
movies_df.fillna(0).plot(x='box_office', y='revenue', kind='scatter')
movies_df.fillna(0)[movies_df['box_office'] < 10**9].plot(x='box_office', y='revenue', kind='scatter')
movies_df[['release_date_wiki','release_date_kaggle']].plot(x='release_date_wiki', y='release_date_kaggle', style='.')
movies_df[(movies_df['release_date_wiki'] > '1996-01-01') & (movies_df['release_date_kaggle'] < '1965-01-01')]
movies_df[(movies_df['release_date_wiki'] > '1996-01-01') & (movies_df['release_date_kaggle'] < '1965-01-01')].index
movies_df = movies_df.drop(movies_df[(movies_df['release_date_wiki'] > '1996-01-01') & (movies_df['release_date_kaggle'] < '1965-01-01')].index)
movies_df[movies_df['release_date_wiki'].isnull()]
movies_df[movies_df['release_date_wiki'].isnull()]
movies_df['Language'].value_counts()
movies_df['Language'].apply(lambda x: tuple(x) == list if type(x) == list else x).value_counts(dropna=False)
movies_df['original_language'].value_counts(dropna=False)
movies_df[['Production company(s)','production_companies']]
movies_df.drop(columns=['title_wiki','release_date_wiki','Language','Production company(s)'], inplace=True)
def fill_missing_kaggle_data(df, kaggle_column, wiki_column):
    df[kaggle_column] = df.apply(
        lambda row: row[wiki_column] if row[kaggle_column] == 0 else row[kaggle_column]
        , axis=1)
    df.drop(columns=wiki_column, inplace=True)
fill_missing_kaggle_data(movies_df, 'runtime', 'running_time')
fill_missing_kaggle_data(movies_df, 'budget_kaggle', 'budget_wiki')
fill_missing_kaggle_data(movies_df, 'revenue', 'box_office')
movies_df
for col in movies_df.columns:
    lists_to_tuples = lambda x: tuple(x) if type(x) == list else x
    value_counts = movies_df[col].apply(lists_to_tuples).value_counts(dropna=False)
    num_values = len(value_counts)
    if num_values == 1:
        print(col)
movies_df['video'].value_counts(dropna=False)
movies_df = movies_df.loc[:, ['imdb_id','id','title_kaggle','original_title','tagline','belongs_to_collection','url','imdb_link',
                       'runtime','budget_kaggle','revenue','release_date_kaggle','popularity','vote_average','vote_count',
                       'genres','original_language','overview','spoken_languages','Country',
                       'production_companies','production_countries','Distributor',
                       'Producer(s)','Director','Starring','Cinematography','Editor(s)','Writer(s)','Composer(s)','Based on'
                      ]]
movies_df.rename({'id':'kaggle_id',
                  'title_kaggle':'title',
                  'url':'wikipedia_url',
                  'budget_kaggle':'budget',
                  'release_date_kaggle':'release_date',
                  'Country':'country',
                  'Distributor':'distributor',
                  'Producer(s)':'producers',
                  'Director':'director',
                  'Starring':'starring',
                  'Cinematography':'cinematography',
                  'Editor(s)':'editors',
                  'Writer(s)':'writers',
                  'Composer(s)':'composers',
                  'Based on':'based_on'
                 }, axis='columns', inplace=True)<jupyter_output><empty_output><jupyter_text>Transform and Merge Rating Data<jupyter_code>rating_counts = ratings.groupby(['movieId','rating'], as_index=False).count()\
                .rename({'userId':'count'}, axis=1)\
                .pivot(index='movieId',columns='rating', values='count')
rating_counts.columns = ['rating_' + str(col) for col in rating_counts.columns]
movies_with_ratings_df = pd.merge(movies_df, rating_counts, left_on='kaggle_id', right_index=True, how='left')
movies_with_ratings_df[rating_counts.columns] = movies_with_ratings_df[rating_counts.columns].fillna(0)<jupyter_output><empty_output><jupyter_text>Connect to SQL<jupyter_code>from config import db_password
db_string = f"postgresql://postgres:{db_password}@127.0.0.1:5432/movie_data"
engine = create_engine(db_string)
movies_df.to_sql(name='movies', con=engine)
rows_imported = 0
# get the start_time from time.time()
start_time = time.time()
for data in pd.read_csv(f'{file_dir}ratings.csv', chunksize=1000000):
    
    print(f'importing rows {rows_imported} to {rows_imported + len(data)}...', end='')
    data.to_sql(name='ratings', con=engine, if_exists='append')
    rows_imported += len(data)

    # add elapsed time to final print out
    print(f'Done. {time.time() - start_time} total seconds elapsed')<jupyter_output>importing rows 0 to 1000000...Done. 19.193996906280518 total seconds elapsed
importing rows 1000000 to 2000000...Done. 37.53322792053223 total seconds elapsed
importing rows 2000000 to 3000000...Done. 55.702571868896484 total seconds elapsed
importing rows 3000000 to 4000000...Done. 74.64302706718445 total seconds elapsed
importing rows 4000000 to 5000000...Done. 93.22685503959656 total seconds elapsed
importing rows 5000000 to 6000000...Done. 112.19110894203186 total seconds elapsed
importing rows 6000000 to 7000000...Done. 130.57939910888672 total seconds elapsed
importing rows 7000000 to 8000000...Done. 149.4053180217743 total seconds elapsed
importing rows 8000000 to 9000000...Done. 167.94342684745789 total seconds elapsed
importing rows 9000000 to 10000000...Done. 186.58401012420654 total seconds elapsed
importing rows 10000000 to 11000000...Done. 205.44590091705322 total seconds elapsed
importing rows 11000000 to 12000000...Done. 224.07003092765808 total seconds elapsed
importing[...]
{'fraction_non_alphanumeric': 0.15364182860338318, 'fraction_numerical': 0.06263106388927722, 'mean_word_length': 5.793922127255461, 'pattern_counts': {'":': 0, '<': 24, '<?xml version=': 0, '>': 23, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 1, 'substrings_counts': 2, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '498408', 'license': 'MIT', 'n_tokens_mistral': 2842, 'n_tokens_neox': 2438, 'n_words': 356, 'path': 'ETL_wiki_movies.ipynb', 'repo_name': 'rykiprince/Movies-ETL', 'size': '7153'}