<jupyter_start><jupyter_text>Final Project SubmissionPlease fill out:* Student name: <NAME>* Student pace: Self Paced* Scheduled project review date/time: Friday, September 25. 5:30PM* Instructor name: <NAME>* Blog post URL: https://princess-1.github.io/why_did_i_decide_to_study_data_science  Data Cleaning and EDA<jupyter_code># Import libraries
import sqlite3

import pandas as pd
import numpy as np

import seaborn as sns

import matplotlib.pyplot as plt
%matplotlib inline
# Connect to database and create cursor
conn = sqlite3.connect('movies_db.sqlite')
cur = conn.cursor()
# Print out table names
cur.execute('''SELECT name
               FROM sqlite_master
               WHERE type='table';
               ''').fetchall()<jupyter_output><empty_output><jupyter_text>tmdb_movies_df - Data Cleaning and EDA<jupyter_code># I'm choosing to convert all my tables to Dataframes because I feel it will be easier to work with
tmdb_movies_df = pd.read_sql('''SELECT *
                                FROM tmdb_movies''', conn)
tmdb_movies_df.head()
# Set 'Index' as the datasets index
tmdb_movies_df.set_index('index', inplace=True)
tmdb_movies_df.head()
# Check df shape to see how many rows and columns we have
print(tmdb_movies_df.shape)

# View info for each column
tmdb_movies_df.info()
# Check for null values if any
print(tmdb_movies_df.isnull().sum())
# It appears we don't have any null value -not any we can see right now at least-
# We'll explore the dataset further by checking out the Unique values
print('Number of Unique values:\n', tmdb_movies_df.nunique())
# Going off the original_title and title columns its safe to assume there are some duplicate
tmdb_movies_df[tmdb_movies_df.duplicated(keep=False)].sort_values(by='title')
# We'll delete the duplicates as part of our data cleaning process
tmdb_movies_df.drop_duplicates(subset=['title'], inplace=True)
# Check to make sure changes were made to the dataset
tmdb_movies_df[tmdb_movies_df.duplicated(keep=False)].sort_values(by='title')
# Now that our data is clean we can proceed to some light EDA
# Going back to the columns datatype we can see that release_date is stored as an object
print(tmdb_movies_df.dtypes)

# That can be fixed by changing it to a datetime dtype
# We can clearly see the before and after below
tmdb_movies_df['release_date'] = pd.to_datetime(tmdb_movies_df['release_date'])
tmdb_movies_df.info()
# Create a two new column release_month and release_day
# release_month
tmdb_movies_df['release_month'] = tmdb_movies_df['release_date'].dt.strftime('%B')
tmdb_movies_df.head()

# release_day
tmdb_movies_df['release_day'] = tmdb_movies_df['release_date'].dt.strftime('%A')
tmdb_movies_df.head()<jupyter_output><empty_output><jupyter_text>budget_df Cleaning and EDA<jupyter_code># Convert sql table to pandas dataframe
budget_df = pd.read_sql('''SELECT *
                                FROM tn_movie_budgets''', conn)
budget_df.head()
# Set 'id' column as index
budget_df.set_index('id', inplace=True)
# Check for null values
budget_df.isna().sum()
# Check for duplicates
budget_df[budget_df.duplicated(keep=False)].sort_values(by='movie')
# Check data structure
budget_df.info()
# Looking at the datatypes the last 3 columns are stored as an object datatype 
# We'll be converting the last 3 columns to Integer datatypes

# production_budget
budget_df['production_budget'] = budget_df['production_budget'].str.replace('$','').str.replace(',','').astype('int')

# domestic_gross
budget_df['domestic_gross'] = budget_df['domestic_gross'].str.replace('$','').str.replace(',','').astype('int')

# worldwide_gross
budget_df['worldwide_gross'] = budget_df['worldwide_gross'].str.replace('$','').str.replace(',','').astype('int')
# Confirm changes
print(budget_df.info())
budget_df.head()
# Convert release_date from object dtype to datetime dtype
budget_df['release_date'] = pd.to_datetime(budget_df['release_date'])
# Confirm change
budget_df.info()
# Create column for release_year
from datetime import datetime
budget_df['release_year'] = budget_df['release_date'].dt.year
budget_df.head()
# Create another column for movie profit
budget_df['profit'] = ((budget_df['worldwide_gross'] + budget_df['domestic_gross']) - budget_df['production_budget'])
budget_df.head()<jupyter_output><empty_output><jupyter_text>Has the profits on movies increased or decreased over the years?<jupyter_code># Use a visulization to answer the question
with plt.style.context('fivethirtyeight'):
    plt.figure(figsize=(12, 7))
    sns.lineplot(x='release_year', y='profit', data=budget_df)
    plt.ylabel('Profit', fontsize=17)
    plt.xlabel('Year Released', fontsize=17)
    plt.title('Profit Over The Years',fontsize=12)
    plt.show()<jupyter_output><empty_output><jupyter_text>**It seems that the profit made on movies over the years is not as great as you would expect.**  Does it cost more to produce movies now?<jupyter_code># Visualize data
with plt.style.context('fivethirtyeight'):
    plt.figure(figsize=(12, 7))
    sns.lineplot(x='release_year', y='production_budget', data=budget_df)
    plt.ylabel('Budget', fontsize=17)
    plt.xlabel('Year Released', fontsize=17)
    plt.title('Cost of Production over the years',fontsize=12)
    plt.show()<jupyter_output><empty_output><jupyter_text>**The cost of producing a movie increased steadily over the past half-century.**  Was it worth it?<jupyter_code># Write a function to help answer the question
def P(budget_df):
    if budget_df['profit'] > 0:
        val = 'Yes'
    else:
        val = 'No'
    return val
# Apply function to dataframe
budget_df['Worth_it?'] = budget_df.apply(P, axis=1)
budget_df.head()
# Visualize answer
with plt.style.context('ggplot'):
    plt.figure(figsize=(10, 7))
    sns.countplot(x='Worth_it?', data=budget_df)
    plt.xlabel('Was it Worth it?', fontsize= 15)
    plt.ylabel('Count', fontsize=15)
    plt.title('Was a profit made?')
    plt.show()<jupyter_output><empty_output><jupyter_text>title_df Data Cleaning and EDA<jupyter_code># Convert sql table to pandas dataframe
title_df = pd.read_sql('''SELECT *
                                FROM imdb_title_basics''', conn)
title_df.head()
# View data structure
title_df.info()
# Check for null values
title_df.isna().sum()
# Fill null values in 'runtime_minutes' with median to preserve data
title_df['runtime_minutes'].fillna(value=title_df['runtime_minutes'].median(), inplace=True)
# Drop all other null values
title_df.dropna(inplace=True)
# Confirm changes
title_df.isna().sum()
# Set primary_title as index
title_df.set_index('tconst', inplace=True)
title_df.head()
title_df[title_df.duplicated(keep=False)].sort_values(by='tconst')
# Delete duplicates
title_df.drop_duplicates(subset=['primary_title'], inplace=True)

# Confirm changes
title_df[title_df.duplicated(keep=False)].sort_values(by='tconst')<jupyter_output><empty_output><jupyter_text>Has the average 'runtime_minutes' for movies increased over the years?<jupyter_code>with plt.style.context('ggplot'):
    title_df.groupby(['start_year']).mean().plot(figsize=(10,7))<jupyter_output><empty_output><jupyter_text>**The average runtime seems to have stayed constant over the years.**  title_ratings_df Data Cleaning and EDA<jupyter_code># Convert ratings_df from sql table to pandas dataframe
ratings_df = pd.read_sql('''SELECT *
                                FROM title_ratings''', conn)
ratings_df.head()
# Create new dataframe by merging title_df and ratings_df
title_ratings_df = pd.merge(title_df, ratings_df, on='tconst')
title_ratings_df.head()
# Check for null values
title_ratings_df.isna().sum()<jupyter_output><empty_output><jupyter_text>studio_df Data Cleaning and EDA<jupyter_code># Convert bom_movie_gross to pandas dataframe
studio_df = pd.read_sql('''SELECT *
                                FROM bom_movie_gross''', conn)
studio_df.head()
# View data structure
studio_df.info()
studio_df.isna().sum()
# I want to convert the foreign_gross column to a float dtype but it has a lot of null data I do not wish to drop
# To make the transition smooth I will be filling all the null values with 0 temporarily
studio_df['foreign_gross'] = studio_df['foreign_gross'].fillna(value=0)
# Convert foreign_gross from object to int dtype
studio_df['foreign_gross'] = studio_df['foreign_gross'].str.replace('$','').str.replace(',','').astype('float')
# Confirm changes 
studio_df.info()
# Fill place holder with the the columns median value
studio_df['foreign_gross'] = studio_df['foreign_gross'].fillna(value=(studio_df['foreign_gross'].median()))
studio_df.isna().sum()
# Drop null values in other columns
studio_df.dropna(inplace=True)

# Confirm changes
studio_df.isna().sum()
# Create a column for total_gross
studio_df['total_gross'] = (studio_df['domestic_gross'] + studio_df['foreign_gross'])
studio_df.head()<jupyter_output><empty_output><jupyter_text>Now that the Data is clean we can get to answering our questions.  Question 1: What Genre(s) should we pay the most attention to based on Popularity and Profit?<jupyter_code># We'll start by merging tmdb_movies_df, budget_df, and title_df
genre_df = pd.merge(pd.merge(tmdb_movies_df, title_df, on='original_title'),budget_df,left_on='original_title', right_on='movie')
genre_df.head()
genre_df.info()
# There are a lot of duplicate columns we can go ahead and drop
genre_df.drop(['original_title', 'primary_title', 'movie', 'release_date_y', 'start_year', 'id'], axis=1, inplace=True)
genre_df.head()
# Set 'title' as index
genre_df.set_index('title', inplace=True)
genre_df.head()<jupyter_output><empty_output><jupyter_text>Top selling Genre(s) based on Popularity<jupyter_code>df = genre_df.sort_values('popularity', ascending=False)
df.head()

# We want to see the most popular genre so we'll be limiting the amount of data to explore
popularity_df = df.iloc[:300].copy()
popularity_df.head()
# Next we'll be working on extracting individual genres from dataframe
popularity_df['genres'] = popularity_df['genres'].map(lambda x: x.split(",") if x else x)
# Write a function to store genres in a list
genre_list =[]
none_counter = 0
for genre in popularity_df['genres']:
    genre_list += genre
# list all unique genres
unique_genre = set(genre_list)
unique_genre
# Get a count of how many movies in the dataset belongs to each genre
genre_count = {}
for g in unique_genre:
    genre_count[g] = genre_list.count(g)
genre_count
# Plot a vertical histogram to better understand the information

Genre = []
Count = []

for genre, count in genre_count.items():
    Genre.append(genre)
    Count.append(count)

# Plot graph
with plt.style.context('fivethirtyeight'):
    plt.figure(figsize=(12, 7))
    sns.barplot(Count, Genre)
    plt.title('Top Genres based on Popularity',fontsize=12)
    plt.xlabel('Count', fontsize=17)
    plt.ylabel('Genre', fontsize=17)
    plt.show()<jupyter_output><empty_output><jupyter_text>**Looking at the graph it is obvious that Action takes the lead with Adventure right on it's heel. You can also tell that Westerns, Musicals, and War movies don't seem to go over well with the masses.**  What genre makes the most profit?<jupyter_code>df = genre_df.sort_values('profit', ascending=False)
df.head()

# We want to see the most popular genre based on profit so we'll be limiting the amount of data to explore
profit_df = df.iloc[:300].copy()
profit_df.head()
# In the next few block we'll be repeating the same codes used in the popularity anaysis
profit_df['genres'] = profit_df['genres'].map(lambda x: x.split(",") if x else x)
# Write a function to store genres in a list
p_list =[]
none_counters = 0
for genre in profit_df['genres']:
    p_list += genre
    
    
# list all unique genres
profit_genres = set(p_list)
profit_genres
# Get a count of how many movies in the dataset belongs to each genre
profit_count = {}
for g in profit_genres:
    profit_count[g] = p_list.count(g)
profit_count
# Plot a vertical histogram to show results

profit_Genre = []
profit_Count = []

for genre, count in profit_count.items():
    profit_Genre.append(genre)
    profit_Count.append(count)

# Plot graph
with plt.style.context('fivethirtyeight'):
    plt.figure(figsize=(12, 7))
    sns.barplot(profit_Count, profit_Genre)
    plt.title('Top Genres based on Profit',fontsize=12)
    plt.xlabel('Count', fontsize=17)
    plt.ylabel('Genre', fontsize=17)
    plt.show()<jupyter_output><empty_output><jupyter_text>**Once again Adventure and Action take point as the 2 most profitable genre.**  Question 1: ConclusionIt's quite obvious that Drama is the genre to go to based on Popularity and Profit**Further Analysis**What is the most popular Genre based on Popularity AND Budget.  Question 2: What release month(s) and day(s) have the most popular movies  Are movies released in certain months more popular?<jupyter_code># Categorize the months 
months = ['January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December']
tmdb_movies_df['release_month'] = pd.Categorical(tmdb_movies_df['release_month'], categories=months, ordered=True)
# Visualize the popularity of movies based on month released
with plt.style.context('dark_background'):
    plt.figure(figsize=(12, 7))
    sns.barplot(x='release_month', y='popularity', data=tmdb_movies_df)
    plt.xlabel('Month', fontsize= 15)
    plt.ylabel('Popularity', fontsize=15)
    plt.title('Best month(s) for movie release')
    plt.show()<jupyter_output><empty_output><jupyter_text>**_July, August, and December_ look like the highest points on the graph. Based on the graph I would not conclude that the month released had a huge impact on the popularity of the movie.**  What's the best day to release a movie?<jupyter_code># Categorize the days 
days = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday']
tmdb_movies_df['release_day'] = pd.Categorical(tmdb_movies_df['release_day'], categories=days, ordered=True)
# Visualize the popularity of movies based on month released
with plt.style.context('dark_background'):
    plt.figure(figsize=(12, 7))
    sns.barplot(x='release_day', y='popularity', data=tmdb_movies_df)
    plt.xlabel('Day of Week', fontsize= 15)
    plt.ylabel('Popularity', fontsize=15)
    plt.title('Best day(s) for movie release')
    plt.show()<jupyter_output><empty_output><jupyter_text>**Based on the graph it's very clear that movies released on Friday's are very popular**  Question 2: ConclusionRelease month does not seem to have a lot of impact on the popularity of movies, but it seems the day of the week does.  Question 3: What are the top grossing production studios?<jupyter_code># View studio_df
studio_df.head()
# Sort values from highest grossing
studio_df = studio_df.sort_values('total_gross', ascending=False)
studio_df.head()
# Make a list for the top 100 grossing studios
studio_name = []
for i in studio_df['studio'].head(100):
    studio_name.append(i)
studio_name
# Display all unique values
unique_studio = set(studio_name)
unique_studio
# Create a dictionary for unique values and counts
studio_count = {}
for s in studio_name:
    studio_count[s] = studio_name.count(s)
studio_count

# Split data for visualization purposes
studio_names = []
s_count = []

for name, count in studio_count.items():
    studio_names.append(name)
    s_count.append(count)


# Visualize data
plt.figure(figsize=(12, 7))
sns.barplot(studio_names, s_count)
plt.title('Top Production Studios',fontsize=12)
plt.xlabel('Studio Name', fontsize=17)
plt.ylabel('Count', fontsize=17)
plt.show()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1147045586060567, 'fraction_numerical': 0.006686812833536939, 'mean_word_length': 5.615908124202467, 'pattern_counts': {'":': 0, '<': 67, '<?xml version=': 0, '>': 68, 'https://': 1, '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': '16', 'dir': 'jupyter-structured-clean-dedup', 'id': '100825', 'license': 'Apache-2.0', 'n_tokens_mistral': 5298, 'n_tokens_neox': 5093, 'n_words': 1456, 'path': '.ipynb_checkpoints/Final_Project-checkpoint.ipynb', 'repo_name': 'Princess-1/1_Module_Project', 'size': '15574'}