<jupyter_start><jupyter_text>Project: The Movie Database Table of ContentsIntroductionData WranglingExploratory Data AnalysisConclusions  IntroductionWhat can we say about the success of a movie before it is released? Are there certain companies that have found a consistent formula? Given that major films costing over $100 million to produce can still flop, this question is more important than ever to the industry. Film aficionados might have different interests. Can we predict which films will be highly rated, whether or not they are a commercial success?In this project I will apply data analytics to answer these questions and many others.This data set contains information about 10,000 movies collected fromThe Movie Database (TMDb),including user ratings and revenue.The data set can be downloaded [here](https://d17h27t6h515a5.cloudfront.net/topher/2017/October/59dd1c4c_tmdb-movies/tmdb-movies.csv) and it is a modified version from the one provided by [Kaggle](https://www.kaggle.com/tmdb/tmdb-movie-metadata?select=tmdb_5000_movies.csv).<jupyter_code># import all necessary libraries
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
from IPython.display import display

# change style for better visualization
sns.set_style("darkgrid")

# display plots in the notebook
%matplotlib inline

# force visualization of all columns
pd.set_option('display.max_columns', None)<jupyter_output><empty_output><jupyter_text>Data Wrangling General PropertiesAs a first step I will read the csv file and get some general information about the structure of the data set and its content.<jupyter_code># load the data
df = pd.read_csv("tmdb-movies.csv")
df.head(1)<jupyter_output><empty_output><jupyter_text>First of all, let's describe what each column stands for:- id - A unique id.- imbd_id - Undefined id.- popularity - A numeric quantity specifying the movie popularity.- budget - The budget in which the movie was made in US dollars.- revenue - The worldwide revenue generated by the movie US dollars.- original_title - The title of the movie before translation or adaptation.- cast - The cast in the movie.- homepage - A link to the homepage of the movie.- director - The director of the movie.- tagline - Movie's tagline.- keywords - The keywords or tags related to the movie.- overview - A brief description of the movie.- runtime - The running time of the movie in minutes.- genres - The genre of the movie, Action, Comedy ,Thriller etc.- production_companies - The production house of the movie.- release_date - The date on which it was released.- vote_count - The count of votes recieved.- vote_average - Average ratings the movie recieved.- release_year - Year in which the movie was released.- budget_adj - The budget in which the movie was made accounting for inflation overtime.- revenue_adj - The worldwide revenue generated by the movie accounting for inflation overtime.We can already see that some columns, like 'cast' and 'keywords', contain multiple values separated by pipe (|)characters.<jupyter_code>df.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 10866 entries, 0 to 10865
Data columns (total 21 columns):
 #   Column                Non-Null Count  Dtype  
---  ------                --------------  -----  
 0   id                    10866 non-null  int64  
 1   imdb_id               10856 non-null  object 
 2   popularity            10866 non-null  float64
 3   budget                10866 non-null  int64  
 4   revenue               10866 non-null  int64  
 5   original_title        10866 non-null  object 
 6   cast                  10790 non-null  object 
 7   homepage              2936 non-null   object 
 8   director              10822 non-null  object 
 9   tagline               8042 non-null   object 
 10  keywords              9373 non-null   object 
 11  overview              10862 non-null  object 
 12  runtime               10866 non-null  int64  
 13  genres                10843 non-null  object 
 14  production_companies  9836 non-null   object 
 15  release_date       [...]<jupyter_text>From the information above we can see that there are many missing values in many of the columns, especially the column 'homepage'. In addition we can see that the column 'imdb_id' also contains 10 missing values.<jupyter_code>df[df.imdb_id.isnull()]<jupyter_output><empty_output><jupyter_text>Most likely these 10 missing points are a result of an error during the dataset creation, since this is just an id, it shouldn't bring any relevant information, therefor we can ignore this column. Now we can look for other general information about the dataset.<jupyter_code>df.describe()<jupyter_output><empty_output><jupyter_text>The table above shows some intresting information, apparently some movies had 0 budget, 0 revenue or even a runtime of 0 minutes. This doesn't seem possible, let's analyze these cases further. budget_adj and revenue_adj are of course also 0 since the value is the same but adjusted due to inflation. We can also notice the range in the year of release, it goes from 1960 till 2015, and by looking at the mean we can notice that there are much recent movies instead of old ones.<jupyter_code>df.query("budget == 0 or revenue == 0 or runtime == 0")<jupyter_output><empty_output><jupyter_text>There are many rows with such values, then maybe the value 0 was saved as default when ther was no data available. Let's analyze each case more specifically.<jupyter_code>print(f"budget has {len(df.query('budget == 0'))} rows with value 0 in it.")
print(f"revenue has {len(df.query('revenue == 0'))} rows with value 0 in it.")
print(f"runtime has {len(df.query('runtime == 0'))} rows with value 0 in it.")<jupyter_output>budget has 5696 rows with value 0 in it.
revenue has 6016 rows with value 0 in it.
runtime has 31 rows with value 0 in it.<jupyter_text>Exactly as expected, the value 0 was use just to replace missing data, we will handle this in the next session.  Data Cleaning process Removing columns with redundant informationAs we saw before there are some columns which have redundant or non relevant information:- budget - is better to use the column budget_idj since take inflation into account- revenue - is better to use the column ravenue_idj since take inflation into account- release_date - year of release is already available in another column- imbd_id - not relevant information since is just an id- homepage - it contains mostly missing data and of course very old movies won't have a webpage<jupyter_code>df.drop(["imdb_id", "budget", "revenue", "homepage", "release_date"], axis=1, inplace=True)<jupyter_output><empty_output><jupyter_text>Create a new feature to simply the code<jupyter_code>df["profitability"] = df["revenue_adj"] - df["budget_adj"]<jupyter_output><empty_output><jupyter_text>Remove missing values and duplicatesNow we should remove all rows which contains missing data as well as remove duplicates.<jupyter_code>df.dropna(inplace=True)
df.drop_duplicates(inplace=True)
df.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 7031 entries, 0 to 10865
Data columns (total 17 columns):
 #   Column                Non-Null Count  Dtype  
---  ------                --------------  -----  
 0   id                    7031 non-null   int64  
 1   popularity            7031 non-null   float64
 2   original_title        7031 non-null   object 
 3   cast                  7031 non-null   object 
 4   director              7031 non-null   object 
 5   tagline               7031 non-null   object 
 6   keywords              7031 non-null   object 
 7   overview              7031 non-null   object 
 8   runtime               7031 non-null   int64  
 9   genres                7031 non-null   object 
 10  production_companies  7031 non-null   object 
 11  vote_count            7031 non-null   int64  
 12  vote_average          7031 non-null   float64
 13  release_year          7031 non-null   int64  
 14  budget_adj            7031 non-null   float64
 15  revenue_adj         [...]<jupyter_text>Removing outliers<jupyter_code>df.describe()<jupyter_output><empty_output><jupyter_text>From the table above we can notice that the maximum value of runtime, popularity and vote_count is way above the 75 percentile, indicating outliers. Since this can still be a useful information, I will not remove it for time being.  Creating 2 data setsAs we saw before budget_adj,revenue_adaj and runtime dont have technically any missing value, but they are just filled with zeros. For this reason we will remove also these rows, but instead of removing them completely, we will create 2 distinct data sets, 1 in the current status and 1 without those rows.<jupyter_code>df_with_zeros = df.copy()
clean_df = df.query("budget_adj != 0 and revenue_adj != 0 and runtime != 0")
del df<jupyter_output><empty_output><jupyter_text>Get reference wordsLast, we saw at the beginning of the notebook, some columns contain multiple values separated by pipe (|)characters.We could duplicate these rows and have only 1 word for each column, but this will create too much redundant data since we have to make every single combination. A better way would be to collect all unique names, keywords etc. and later on use these words to filter out the data.<jupyter_code>unique_cast = tuple(set(df_with_zeros["cast"].dropna().str.split("|").sum()))
unique_keywords = tuple(set(df_with_zeros["keywords"].dropna().str.split("|").sum()))
unique_genres = tuple(set(df_with_zeros["genres"].dropna().str.split("|").sum()))
unique_production_companies = tuple(set(df_with_zeros["production_companies"].dropna().str.split("|").sum()))

print("unique cast: ", len(unique_cast))
print("unique keywords: ", len(unique_keywords))
print("unique genres: ", len(unique_genres))
print("unique production companies: ", len(unique_production_companies))
clean_df.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 3445 entries, 0 to 10848
Data columns (total 17 columns):
 #   Column                Non-Null Count  Dtype  
---  ------                --------------  -----  
 0   id                    3445 non-null   int64  
 1   popularity            3445 non-null   float64
 2   original_title        3445 non-null   object 
 3   cast                  3445 non-null   object 
 4   director              3445 non-null   object 
 5   tagline               3445 non-null   object 
 6   keywords              3445 non-null   object 
 7   overview              3445 non-null   object 
 8   runtime               3445 non-null   int64  
 9   genres                3445 non-null   object 
 10  production_companies  3445 non-null   object 
 11  vote_count            3445 non-null   int64  
 12  vote_average          3445 non-null   float64
 13  release_year          3445 non-null   int64  
 14  budget_adj            3445 non-null   float64
 15  revenue_adj         [...]<jupyter_text>Exploratory Data AnalysisNow that the data has been cleaned and properly formatted we can start the data analysis exploration. Is the revenue increasing if the budget is also increasing?<jupyter_code>plt.rcParams.update({'font.size': 14})

clean_df.plot.scatter(
    "budget_adj", "revenue_adj",
    figsize=(11, 11),
    xlim=(0, 3e8),
    ylim=(0, 1e9),
    xticks=np.arange(0, 3.1e8, .5e8),
    yticks=np.arange(0, 1e9, 1e8),
    title="Correlation between budget and revenue",
);<jupyter_output><empty_output><jupyter_text>From the graph above we can see that there is no correlation between the money spent and the revenue, this was in fact expected. Is there any correlation between popularity, profitability, average vote and number of votes?<jupyter_code>plt.rcParams.update({'font.size': 14})

pd.plotting.scatter_matrix(
    clean_df[["popularity", "profitability", "vote_average", "vote_count"]],
    figsize=(11, 11)
);<jupyter_output><empty_output><jupyter_text>From the graph above we can derive a couple of information:- When the number of votes passes ~1000 we see a jump in average vote.- When the average vote increases the profitability also increases but non linearly.- We the above 2 statements we can derive that the higher the vote, the more people vote, meaning more people watching the movie and increase the profits.- Popularity and number of votes have a positive correlation. From all highly rated movies which genres are included?<jupyter_code>df_with_zeros.query("vote_average > 7.0")["genres"].value_counts()[:5]<jupyter_output><empty_output><jupyter_text>Drama is definetly on top, documentary comes as second, comedy, romance and crime are about the same level. What about highly rated modern movies (above 2010)?<jupyter_code>df_with_zeros.query("vote_average > 7.0 and release_year >= 2010")["genres"].value_counts()[:5]<jupyter_output><empty_output><jupyter_text>Drama, documentary and Comedy are still in the highest rated. From all genres, which one is in average the most profitable?<jupyter_code>profits = dict()

# iterate over all available genres
for g in unique_genres:
    # filter data by genre g
    filtered_df = clean_df[clean_df['genres'].str.contains(g)]
    # calculate mean profitability
    profits[g] = filtered_df.profitability.mean()

# plot barchart with some parameters
fig, ax = plt.subplots(figsize=(11, 11))
plt.rcParams.update({'font.size': 14})

ax.bar(x=tuple(profits.keys()),height=profits.values())

ax.set_yticks(np.arange(-1e7, 23e7, 1e7))
ax.set_title("Average profits per genre")
ax.set_xlabel("Genres")
ax.set_ylabel("Average profits (x10M $)")
plt.xticks(rotation=90)
plt.show()<jupyter_output><empty_output><jupyter_text>Apparently the 4 most profitable genres on average are Animation, Fantasy, Adventure and Family. Documentary seems quite strangly, maybe this is related to high costs.<jupyter_code>profits = dict()

# iterate over all available genres
for g in unique_genres:
    # filter data by genre g
    filtered_df = clean_df[clean_df['genres'].str.contains(g)]
    # calculate mean costs
    profits[g] = filtered_df.budget_adj.mean()

# plot barchart with some parameters
fig, ax = plt.subplots(figsize=(11, 11))
plt.rcParams.update({'font.size': 14})

ax.bar(x=tuple(profits.keys()),height=profits.values())

ax.set_yticks(np.arange(-1e7, 11e7, 1e7))
ax.set_title("Average costs per genre")
ax.set_xlabel("Genres")
ax.set_ylabel("Average costs (x10M $)")
plt.xticks(rotation=90)
plt.show()<jupyter_output><empty_output><jupyter_text>Animation, Adventure and Fantasy are the most expensive but also the most profitable then. Drama has costs in the average, like its profitability, but has best ratings. Documentary seems to have too less data to verify its costs and profitability, let's verify that.<jupyter_code># filter all documentary movies
filtered_df = df_with_zeros[df_with_zeros['genres'].str.contains("Documentary")]

print("revenue above 0: ", (filtered_df.revenue_adj > 0).sum())
print("revenue equal to 0: ", (filtered_df.revenue_adj == 0).sum())
print("budget above 0: ", (filtered_df.budget_adj > 0).sum())
print("budget equal to 0: ", (filtered_df.budget_adj == 0).sum())<jupyter_output>revenue above 0:  57
revenue equal to 0:  101
budget above 0:  29
budget equal to 0:  129<jupyter_text>As expected, most of the data contains 0, so this genre can't give us much information about profitability. How did all these genres performed in comparison over the years?<jupyter_code># plot parameters
fig, ax = plt.subplots(figsize=(13, 7))
plt.rcParams.update({'font.size': 14})

# create a range for the years
start_year = clean_df.release_year.min()
year_range = tuple(range(start_year+5, clean_df.release_year.max()+1, 5))

# the label locations
x = np.arange(len(year_range))

# genres to consider
genres = ("Animation", "Fantasy", "Adventure", "Drama")

profits = {k: [] for k in genres}
for y in year_range:
    for g in genres:
        # filter data within year range
        filtered_df = clean_df[start_year <= clean_df.release_year]
        filtered_df = filtered_df[filtered_df.release_year < y]
        # filter data by genre g
        filtered_df = filtered_df[filtered_df['genres'].str.contains(g)]
        # calculate mean profitability
        profits[g].append(filtered_df.profitability.mean())

# plot data
for k, v in profits.items():
    ax.plot(x, v, label=k)

# other plot parameters
ax.set_ylabel('Average profits (x10M $)')
ax.set_title("Average profits over time")
ax.set_xticks(x)
ax.set_xticklabels([f"<{y_}" for y_ in year_range])
ax.legend()
fig.tight_layout()
plt.show()<jupyter_output><empty_output><jupyter_text>This graph explains well the current situation. Drama movies were more profitable in the past and even if the trend is decreasing, fantasy movies seem increasing their profits since 2000. From all available directors in this dataset, which one are the most profitable in fantasy movies since 2010?<jupyter_code># filter fantasy movies from 2010
filtered_df = clean_df[clean_df['genres'].str.contains("Fantasy")].query("release_year >= 2010")

# group profitability of each director
profitability = filtered_df.groupby("director")["profitability"]

print("Highest sum\n", "-"*45, sep="")
display(profitability.sum().sort_values(ascending=False)[:10])
print("Highest average\n", "-"*45, sep="")
display(profitability.mean().sort_values(ascending=False)[:10])
print("Number of profitable movies\n", "-"*45, sep="")
display(filtered_df.query("profitability > 0")["director"].value_counts().sort_values(ascending=False)[:10])<jupyter_output>Highest sum
---------------------------------------------
{'fraction_non_alphanumeric': 0.09100221956633088, 'fraction_numerical': 0.031984519947640996, 'mean_word_length': 3.8421052631578947, 'pattern_counts': {'":': 0, '<': 89, '<?xml version=': 0, '>': 93, 'https://': 2, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '22', 'dir': 'jupyter-structured-clean-dedup', 'id': '200341', 'license': 'MIT', 'n_tokens_mistral': 5546, 'n_tokens_neox': 4994, 'n_words': 1906, 'path': 'The_Movie_Database_Investigation/investigate_a_dataset.ipynb', 'repo_name': 'DavidMachineLearning/data-analyst-udacity', 'size': '17571'}