<jupyter_start><jupyter_text>Data Cleaning and Feature Engineering  Modeling Process**Cross-industry standard process for data mining**, known as **CRISP-DM**, is an open standard process model that describes common approaches used by data mining experts. It is the most widely-used analytics model.6 high level phases of the data mining process:- Business Understanding- Data Understanding- Data Preparation- Modeling- Evaluation- DeploymentThe sequence of the phases is not strict and moving back and forth between different phases as it is always required.  Dataset UnderstandingToday we are going to be working with the IMDB movie data set.  Our goal is to eventually create a linear regression model that will enable us to predict the box office gross of a movie based on characteristics of the movie.Before we can start to model, we need to make sure our data is clean and in a usable format.  Therefore we will go through several steps of data cleaning. The code below is not a fully exhaustive list, but includes many of the process you will go through to clean data.<jupyter_code>import numpy as np 
import pandas as pd
import seaborn as sns
from datetime import datetime
import matplotlib.pyplot as plt
%matplotlib inline
pd.set_option('display.max_columns', 100)<jupyter_output><empty_output><jupyter_text>Check Your Data … QuicklyThe first thing you want to do when you get a new dataset, is to quickly to verify the contents with the .head() method.<jupyter_code>df = pd.read_csv('movie_metadata.csv')
print(df.shape)
df.head()<jupyter_output>(5043, 28)<jupyter_text>Now let’s quickly see the names and types of the columns. Most of the time you’re going get data that is not quite what you expected, such as dates which are actually strings and other oddities. But to check upfront.<jupyter_code># Get column names
column_names = df.columns
print(column_names)
# Get column data types
df.dtypes<jupyter_output>Index(['color', 'director_name', 'num_critic_for_reviews', 'duration',
       'director_facebook_likes', 'actor_3_facebook_likes', 'actor_2_name',
       'actor_1_facebook_likes', 'gross', 'genres', 'actor_1_name',
       'movie_title', 'num_voted_users', 'cast_total_facebook_likes',
       'actor_3_name', 'facenumber_in_poster', 'plot_keywords',
       'movie_imdb_link', 'num_user_for_reviews', 'language', 'country',
       'content_rating', 'budget', 'title_year', 'actor_2_facebook_likes',
       'imdb_score', 'aspect_ratio', 'movie_facebook_likes'],
      dtype='object')<jupyter_text>Convert a column to a different data typeThe most common example of this is converting a string of number to an actual float or integer.  There are two ways you can achieve this.  1. astype(float) method`df['DataFrame Column'] = df['DataFrame Column'].astype(float)`2.  to_numeric method`df['DataFrame Column'] = pd.to_numeric(df['DataFrame Column'],errors='coerce')`What is the difference in these two methods?(1) For a column that contains numeric values stored as strings;(2) For a column that contains both numeric and non-numeric values. By setting errors=’coerce’, you’ll transform the non-numeric values into NaN.https://datatofish.com/convert-string-to-float-dataframe/<jupyter_code>df['title_year'][0]
"2009/12/30"
df['title_year']= pd.to_datetime(df['title_year'], format='%Y')
df.dtypes
df['title_year']<jupyter_output><empty_output><jupyter_text>Drop ColumnsIf you do not plan on using some data in your analysis, feel free to drop those columns.<jupyter_code>print(df.columns)
df.drop(columns=['aspect_ratio', 'plot_keywords', 'movie_facebook_likes', 'genres'], inplace=True)
df.shape
df.drop([0,1,2,3], axis=0)
df.head()
df[['gross','budget']]<jupyter_output><empty_output><jupyter_text>Investigate the data<jupyter_code>df['country'].value_counts()<jupyter_output><empty_output><jupyter_text>We have movies from many different countries in this data set. If our goal is to only predict movies that were released in theaters, we probably don't want to include foreign movies.  So let's remove them from the data set. Write code to only leave us with movies from the US.<jupyter_code>#your code here

df= df[df['country']=='USA']
df.shape<jupyter_output><empty_output><jupyter_text>Let's also look at `content_rating`.<jupyter_code>df.content_rating
df['content_rating'].value_counts()
df.groupby('content_rating')['gross'].mean()
#look at the unique values for ratings
ratings = list(df['content_rating'].unique())
ratings<jupyter_output><empty_output><jupyter_text>There are many unique values that don't have a high count or don't make sense to the common user.  How should we handle these?<jupyter_code>#create a list of the ratings we want to group
unrated = ['Unrated','Approved', 'Not Rated', 'TV-MA', 'M', 'GP', 'Passed', np.nan, 'X', 'NC-17','TV-14', 'TV-PG', 'TV-G', 'TV-Y', 'TV-Y7']
#create a list of the movie ratings we want to maintian
rated = [x for x in ratings if x not in unrated]
rated
#create a dictionary with keys of the 'unrated' values and the value being 'unrated'
unrated_dict = dict.fromkeys(unrated, 'unrated')
unrated_dict
rated
#create a dictionary of the rated values
rated_dict  = dict(zip(rated, rated))
rated_dict
#combine those ditionaries into 1
ratings_map = {**rated_dict,**unrated_dict}
ratings_map<jupyter_output><empty_output><jupyter_text>What does `**` do? It basically takes the dictionary passed through and unpacks it.  https://medium.com/understand-the-python/understanding-the-asterisk-of-python-8b9daaa4a558https://pynash.org/2013/03/13/unpacking/<jupyter_code># use the pandas map function to change the content_rating column
df['rating'] = df['content_rating'].map(ratings_map)
#compare the two columns
df[['rating', 'content_rating']].tail()
df['rating'].value_counts()<jupyter_output><empty_output><jupyter_text>Handling Missing Data:<jupyter_code>#creates a dataframe of booleans show where data is missing
df.isna()
# Find the Percentage of rows missing data
df.isna().mean()
#graphically see the missing data
sns.heatmap(df.isna(), cbar=False)<jupyter_output><empty_output><jupyter_text>Dropping missing rowsOne way to handle missing data is just to drop the observation from the data set. This is not always the ideal way since you will lose obseervations, but it might be unavoidable.  For example, we want to predict the gross earnings for each film, so we have to remove those that don't have value for gross.<jupyter_code>df.dropna(subset=['gross'], inplace=True)
df_dropped = df.dropna(subset=['gross'])
df.shape
sns.heatmap(df.isnull(), cbar=False)
#look at all the observations with at least one missing data point
df[df['budget'].isna()].head(10)
df['budget'].plot(kind='box')<jupyter_output><empty_output><jupyter_text>Quite a few films are still missing the values for budget. We do not want to drop this column because we believe it is an important variable, but we must have a value for each observation in order to use it.**Talk with a partner to think of different ways you can fill in the missing budget values?**<jupyter_code>#you can fill the missing values with the average value of the observations
df['budget'].fillna(df['budget'].mean(), inplace=False)<jupyter_output><empty_output><jupyter_text>Another way to fill the missing data<jupyter_code>df.groupby('rating')['budget'].mean().plot(kind='bar') #G, PG, PG-13 are similar, so run a statistical test (ANOVA)
budget_ratings = df.groupby('rating')['budget'].mean().round(1).to_dict()

budget_ratings
df['budget'].fillna(df['rating'].map(budget_ratings), inplace=True)

sns.heatmap(df.isnull(), cbar=False)<jupyter_output><empty_output><jupyter_text>What statistical test could we use to support our use of this method?  Handling Categorical Datahttps://towardsdatascience.com/the-dummys-guide-to-creating-dummy-variables-f21faddb1d40 For the column of `rating`, we can see that the values come through as strings that represent a category.  We can not put a string through as a value for a linear model. Instead we use dummy variables.<jupyter_code>df['rating'].value_counts()<jupyter_output><empty_output><jupyter_text>A **dummy variable** (aka, an indicator variable) is a numeric variable that represents categorical data, such as gender, race, political affiliation, etc.- Typically, 1 represents the presence of a qualitative attribute, and 0 represents the absence.<jupyter_code>pd.get_dummies(df['rating']).tail(10)<jupyter_output><empty_output><jupyter_text>By creating these dummy variables. We can now include them in the model.  **In our model should we include all of the columns created by `get_dummies`?** ___ Remember multi-collinearity?  If you have know the values of a movie for `G`, `PG`, `PG-13`, and `R`, then you already know the value for `unrated`.Multi-collinearity can exist in the combination of multiple columns, so we need to drop one of them.  Using all the dummy variables derived from a category is known as the dummy variable trap. Avoid this trap!- Either don't include in the model _or_ use the parameter `drop_first=True` (see below)<jupyter_code>df.head()
df_dummy = pd.get_dummies(df, columns=['rating'], drop_first=True)
df_dummy
df = pd.concat([df, pd.get_dummies(df['rating'])], 1)
df.head(10)<jupyter_output><empty_output><jupyter_text>Removing Outliershttps://towardsdatascience.com/ways-to-detect-and-remove-the-outliers-404d16608dba<jupyter_code>df.boxplot(['gross'])
df.sort_values('gross', ascending=False)
# Calculate gross amount that is 5 times above the standard deviation
above_5std = df.gross.mean()+(5*df.gross.std())
above_5std<jupyter_output><empty_output><jupyter_text>Use a conditional selection to only return values lower than 5 standard deviations above the mean<jupyter_code>df.shape
df[(df['gross']<=above_5std)&(df['gross']>1000000)].shape<jupyter_output><empty_output><jupyter_text>Feature Engineering  Creating New columns based on other columns<jupyter_code>df['actor_1_facebook_likes'].describe()
# Create a new column called df.superstar where the value is 1
# if df.actor_1_facebook_likes is greater th000 and 0 if not
df['superstar'] = np.where(df['actor_1_facebook_likes']>=25000, 1, 0)

df[['movie_title', 'actor_1_name','actor_1_facebook_likes', 'superstar']].head(10)
df['years_old'] = 2016- pd.DatetimeIndex(df['title_year']).year

df['years_old']<jupyter_output><empty_output><jupyter_text>**Create your own new column of data using the method above.**<jupyter_code>#your code here<jupyter_output><empty_output><jupyter_text>Another data cleaning Resource:https://medium.com/@rrfd/cleaning-and-prepping-data-with-python-for-data-science-best-practices-and-helpful-packages-af1edfbe2a3  Geospatial DataThis dataset currently comes with the latitude and longitude of each house. This is probably not that useful for us. So now we need  to think about how to transform this data into something more useful. Looking at the map below, which aparment might cost more to rent 1  or 2?Why? Looking at this map of East Harlem, how could  we transform lat and long into a more useful data point?  Time Aggregations:Imagine this situation. You are trying to predict sales for a bar in Williamsburg that has an outdoor patio. You know that weather impacts the sales and want to include it in your features.  Right now you have weather data at a hourly level, but your sales data is at a daily level. How should we aggregate/convert our hourly data into our daily data? **EX:** For temperature, I believe that what is most important is how warm it got that day, so I take my hourly data and `resample`  it to give me the max for the day.  ```df['temp'].resample('D').max()``` QuestionNow let's think about this for rain.  Talk with your partner and come up with 3 ways that you can take hourly rain measurements and aggregate it up to a daily level. ***Hint:*** The outdoor space is primarily used from 12-8pm.    Multiple Linear Regression AppliedNow let's fit a model to this data where we try to explain the variance in the box office gross of a movie, using differen variables.  Try first only including a few variables and see how your model performs according to the R^2 value.  Then add on more variables to see how it impacts the R^2 value.<jupyter_code>df = df[df['content_rating'].isin(['G', 'PG-13', 'PG', 'R'])]
df.head()<jupyter_output><empty_output><jupyter_text>Now that we have our data cleaned up, what should we do before putting all of the features into the model?<jupyter_code>from statsmodels.formula.api import ols
mlr_model = ols(formula='gross~duration+budget+years_old+superstar+G+PG+R+cast_total_facebook_likes', data=df).fit()
mlr_model.summary()<jupyter_output><empty_output><jupyter_text>Interpreting Dummy Coefficients<jupyter_code>mlr_model = ols(formula='gross~G+PG+R', data=df).fit()
mlr_model.summary()<jupyter_output><empty_output><jupyter_text>**The coefficient for a dummy variable tells you the unit increase for an observation in that category compared to the categories not included in the model.**In the above example, the category left out is PG-13. Therefore you can interpret the coefficent for the movie R to tell you the difference in the predicted gross of an R-rated movie compared to a PG-13 rated movie.<jupyter_code>df.groupby('rating')['gross'].mean()<jupyter_output><empty_output><jupyter_text>Scaling DataMost of the times, your dataset will contain features highly varying in magnitudes, units and range (ex: acceleration and range). For linear regression models, this makes it difficult to compare the sizes of the coefficients for different variables. We want to **get everything on the same scale so that nothing comes accross as more or less important because of the way it's measured**.Three common ways to scale the data are:1. **Standardization**: This redistributes the features with their mean μ = 0 and standard deviation σ =1 . `sklearn.preprocessing.scale` helps us implementing standardization in python. $$x' =\frac{x-\bar{x}}{\sigma}$$2. **Mean Normalization**: This distribution will have values between -1 and 1 with μ=0.$$x' =\frac{x-\bar{x}}{max(x)- min(x)}$$3. **Min-Max Scaling**: This scaling brings the value between 0 and 1.$$x' =\frac{x-min(x)}{max(x)- min(x)}$$https://medium.com/@swethalakshmanan14/how-when-and-why-should-you-normalize-standardize-rescale-your-data-3f083def38ff<jupyter_code>from sklearn.preprocessing import StandardScaler
scaler = StandardScaler()

subset= []
subset_scaled = []
for var in subset:
    new_col = var +"_scaled"
    df[new_col] = scaler.fit_transform(df[[var]])
    subset_scaled.append(new_col)
df.head(30)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10144728719390905, 'fraction_numerical': 0.010563138761231909, 'mean_word_length': 6.0673776054289865, 'pattern_counts': {'":': 0, '<': 104, '<?xml version=': 0, '>': 105, 'https://': 7, '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': '26', 'dir': 'jupyter-structured-clean-dedup', 'id': '548816', 'license': 'MIT', 'n_tokens_mistral': 4558, 'n_tokens_neox': 4360, 'n_words': 1622, 'path': 'Phase_2/data_preparation/Data_preparation_Feature_engineering.ipynb', 'repo_name': 'PipSaysHoot/ds-east-042621-lectures', 'size': '14579'}