in-progress-data-preprocessing.ipynb

{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"# Imports","metadata":{}},{"cell_type":"code","source":"from collections import defaultdict\n\nimport pandas as pd \nimport seaborn as sns\nimport matplotlib.pyplot as plt\nimport warnings\n\nwarnings.filterwarnings(\"ignore\")\n%matplotlib inline","metadata":{"execution":{"iopub.status.busy":"2023-09-23T17:43:50.959502Z","iopub.execute_input":"2023-09-23T17:43:50.959846Z","iopub.status.idle":"2023-09-23T17:43:52.733239Z","shell.execute_reply.started":"2023-09-23T17:43:50.959818Z","shell.execute_reply":"2023-09-23T17:43:52.732136Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Constants","metadata":{}},{"cell_type":"code","source":"DATASET_PATH = '/kaggle/input/movies-dataset-for-feature-extracion-prediction/movies.csv'\nREGEX_MOVIE_YEAR = r'\\(\\d{4}\\)|\\([MDCLXVI]+(?:M{0,3}D?C{0,3}L?X{0,3}V?I{0,3})\\)\\(\\d{4}\\)|\\([MDCLXVI]+(?:M{0,3}D?C{0,3}L?X{0,3}V?I{0,3})\\)'\n\nGENRE_WEIGHT_WRT_RATING = 0.4\nDIRECTOR_WEIGHT_WRT_RATING = 0.3\nCAST_WEIGHT_WRT_RATING = 0.3\n\nGENRE_WEIGHT_WRT_VOTES = 0.6\nDIRECTOR_WEIGHT_WRT_VOTES = 0.1\nCAST_WEIGHT_WRT_VOTES = 0.3","metadata":{"execution":{"iopub.status.busy":"2023-09-23T17:43:52.734951Z","iopub.execute_input":"2023-09-23T17:43:52.735407Z","iopub.status.idle":"2023-09-23T17:43:52.740894Z","shell.execute_reply.started":"2023-09-23T17:43:52.735376Z","shell.execute_reply":"2023-09-23T17:43:52.739659Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Helpers","metadata":{}},{"cell_type":"code","source":"def get_data_frame_quick_insights(dataframe):\n    \"\"\"\n    Check basic insights, which includes:\n    1. Column Type\n    2. Null Count\n    3. Null Percentage\n    \"\"\"\n    # Calculate null percentages and counts\n    null_percentage = (dataframe.isnull().mean() * 100).round(1)\n    null_count =  dataframe.isna().sum()\n\n    # Create a new DataFrame to display the results\n    result_df = pd.DataFrame({\n        'Column Type': dataframe.dtypes,\n        'Null Count': null_count, \n        'Null Percentage': null_percentage.values,\n    })\n\n    # Reset the index for a cleaner DataFrame\n    result_df.reset_index(inplace=True)\n    result_df.rename(columns={'index': 'Column Name'}, inplace=True)\n\n    # Return result DataFrame\n    return result_df\n\n\ndef extract_director_and_stars_info(row):\n    parts = row.split('|')\n    director = 'Unknown'\n    stars = 'Unknown'\n\n    for part in parts:\n        if part.startswith('Director:'):\n            director = part.replace('Director:', '').strip()\n        if part.startswith('Stars:'):\n            stars = part.replace('Stars:', '').strip()\n    \n    return director, stars\n\n\ndef get_directors_count(dataframe):\n    director_counts = {}\n\n    # Iterate through the 'Director' column and count unique values\n    for director in dataframe:\n        if director in director_counts:\n            director_counts[director] += 1\n        else:\n            director_counts[director] = 1   \n    return director_counts\n\n\ndef get_casts_count(movies_cast):\n    occurrence_dict = defaultdict(int)\n\n    for movie_cast in movies_cast:\n        cast_list = movie_cast.split(',')\n        for name in cast_list:\n            occurrence_dict[name] += 1\n\n    return dict(occurrence_dict)\n\n\ndef get_genre_count(dataframe):\n    genre_count = {}\n\n    # Split the 'GENRE' column by ',' and stack the resulting Series\n    genre_series = dataframe.str.split(', ').explode()\n\n    # Use value_counts to get the genre counts and convert it to a dictionary\n    genre_count = genre_series.value_counts().to_dict()\n    \n    return genre_count\n\n\ndef cal_movie_rating(genre_rating, director_rating, cast_rating):\n    movie_rating = (\n        GENRE_WEIGHT_WRT_RATING * genre_rating +\n        DIRECTOR_WEIGHT_WRT_RATING * director_rating +\n        CAST_WEIGHT_WRT_RATING * cast_rating\n    ) / (GENRE_WEIGHT_WRT_RATING + DIRECTOR_WEIGHT_WRT_RATING + CAST_WEIGHT_WRT_RATING)\n\n    # Ensure the movie rating is within the range of 1-10\n    movie_rating = max(1, min(10, movie_rating))\n    \n    return max(1, min(10, movie_rating))\n\ndef cal_movie_votes(genre_votes, director_votes, cast_votes):\n    movie_vote = (\n        GENRE_WEIGHT_WRT_VOTES * genre_votes +\n        DIRECTOR_WEIGHT_WRT_VOTES * director_votes +\n        CAST_WEIGHT_WRT_VOTES * cast_votes\n    ) / (GENRE_WEIGHT_WRT_VOTES + DIRECTOR_WEIGHT_WRT_VOTES + CAST_WEIGHT_WRT_VOTES)\n    \n    return movie_vote","metadata":{"execution":{"iopub.status.busy":"2023-09-23T17:43:52.742080Z","iopub.execute_input":"2023-09-23T17:43:52.742788Z","iopub.status.idle":"2023-09-23T17:43:52.756137Z","shell.execute_reply.started":"2023-09-23T17:43:52.742758Z","shell.execute_reply":"2023-09-23T17:43:52.754966Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Gather Data","metadata":{}},{"cell_type":"code","source":"# Load the dataset\noriginal_df = pd.read_csv(DATASET_PATH)\n\n# Show first 10 values\noriginal_df.head(10)","metadata":{"execution":{"iopub.status.busy":"2023-09-23T17:43:52.760454Z","iopub.execute_input":"2023-09-23T17:43:52.760792Z","iopub.status.idle":"2023-09-23T17:43:52.896929Z","shell.execute_reply.started":"2023-09-23T17:43:52.760763Z","shell.execute_reply":"2023-09-23T17:43:52.895862Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"original_df.shape","metadata":{"execution":{"iopub.status.busy":"2023-09-23T17:43:52.898555Z","iopub.execute_input":"2023-09-23T17:43:52.899199Z","iopub.status.idle":"2023-09-23T17:43:52.905022Z","shell.execute_reply.started":"2023-09-23T17:43:52.899144Z","shell.execute_reply":"2023-09-23T17:43:52.904357Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"#### Check for attributes type and null values in dataset","metadata":{}},{"cell_type":"code","source":"get_data_frame_quick_insights(original_df)","metadata":{"execution":{"iopub.status.busy":"2023-09-23T17:43:52.906195Z","iopub.execute_input":"2023-09-23T17:43:52.908389Z","iopub.status.idle":"2023-09-23T17:43:52.941237Z","shell.execute_reply.started":"2023-09-23T17:43:52.908358Z","shell.execute_reply":"2023-09-23T17:43:52.940201Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## 💡Findings:\n* There are a few columns with **inconsistent data types** (i.e. some columns have the \"object\" data type).\n* **Stars** column contains hidden info of **Director** and **Cast**.\n* There are a few columns with **inconsistent column names**.\n* There are a few columns with **inconsistent column values**.\n* The **Gross** column has **95%** of its values missing.\n* There is a chance for **duplicate entries**.","metadata":{}},{"cell_type":"markdown","source":"***Let's work on the above findings first***\n\n# Data Cleaning","metadata":{}},{"cell_type":"code","source":"# Make a copy from our original dataframe\ndata = original_df","metadata":{"execution":{"iopub.status.busy":"2023-09-23T17:43:52.942471Z","iopub.execute_input":"2023-09-23T17:43:52.942737Z","iopub.status.idle":"2023-09-23T17:43:52.947232Z","shell.execute_reply.started":"2023-09-23T17:43:52.942714Z","shell.execute_reply":"2023-09-23T17:43:52.946193Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Check for duplicates\ndata.duplicated().sum()","metadata":{"execution":{"iopub.status.busy":"2023-09-23T17:43:52.948644Z","iopub.execute_input":"2023-09-23T17:43:52.949064Z","iopub.status.idle":"2023-09-23T17:43:52.982655Z","shell.execute_reply.started":"2023-09-23T17:43:52.949015Z","shell.execute_reply":"2023-09-23T17:43:52.981967Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Dropping the duplication\ndata.drop_duplicates(inplace=True)\ndata.shape","metadata":{"execution":{"iopub.status.busy":"2023-09-23T17:43:52.983964Z","iopub.execute_input":"2023-09-23T17:43:52.984570Z","iopub.status.idle":"2023-09-23T17:43:53.009436Z","shell.execute_reply.started":"2023-09-23T17:43:52.984532Z","shell.execute_reply":"2023-09-23T17:43:53.008460Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Rename columns\ndata.rename(columns={'RunTime': 'RUNTIME', 'Gross': 'GROSS'}, inplace=True)","metadata":{"execution":{"iopub.status.busy":"2023-09-23T17:43:53.013952Z","iopub.execute_input":"2023-09-23T17:43:53.014485Z","iopub.status.idle":"2023-09-23T17:43:53.019747Z","shell.execute_reply.started":"2023-09-23T17:43:53.014456Z","shell.execute_reply":"2023-09-23T17:43:53.018713Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Remove 'Gross' due to its significance absence\nremove_columns = ['GROSS']\ndata = data.drop(columns=remove_columns, axis=1)\ndata.head(5)","metadata":{"execution":{"iopub.status.busy":"2023-09-23T17:43:53.021066Z","iopub.execute_input":"2023-09-23T17:43:53.021410Z","iopub.status.idle":"2023-09-23T17:43:53.044898Z","shell.execute_reply.started":"2023-09-23T17:43:53.021375Z","shell.execute_reply":"2023-09-23T17:43:53.043860Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Remove rows where YEAR, GENRE, RATING, VOTES, RUNTIME is NaN\ndf_removed_na_attributes_movies = data[\n    (data['YEAR'].isna()) & \\\n    (data['RATING'].isna()) & \\\n    (data['VOTES'].isna()) & \\\n    (data['RUNTIME'].isna())\n]\ndf_removed_na_attributes_movies.shape","metadata":{"execution":{"iopub.status.busy":"2023-09-23T17:43:53.046676Z","iopub.execute_input":"2023-09-23T17:43:53.047058Z","iopub.status.idle":"2023-09-23T17:43:53.057947Z","shell.execute_reply.started":"2023-09-23T17:43:53.047021Z","shell.execute_reply":"2023-09-23T17:43:53.057006Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_removed_na_attributes_movies.head()","metadata":{"execution":{"iopub.status.busy":"2023-09-23T17:43:53.059029Z","iopub.execute_input":"2023-09-23T17:43:53.059749Z","iopub.status.idle":"2023-09-23T17:43:53.074783Z","shell.execute_reply.started":"2023-09-23T17:43:53.059719Z","shell.execute_reply":"2023-09-23T17:43:53.073670Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"*As it can be observed, the entries with missing values in **YEAR**, **RATING**, **RUNTIME**, and **VOTES** account for approximately **5%** of our dataset after removing duplicate entries. It would be better to remove them for now.*","metadata":{}},{"cell_type":"code","source":"# Keeping reset of the entries by taking (~) bitwise NOT of NAN entries\ndata =  data[~\n    ((data['YEAR'].isna()) & \\\n    (data['RATING'].isna()) & \\\n    (data['VOTES'].isna()) & \\\n    (data['RUNTIME'].isna()))\n]\n\ndata.shape","metadata":{"execution":{"iopub.status.busy":"2023-09-23T17:43:53.076475Z","iopub.execute_input":"2023-09-23T17:43:53.076852Z","iopub.status.idle":"2023-09-23T17:43:53.089452Z","shell.execute_reply.started":"2023-09-23T17:43:53.076818Z","shell.execute_reply":"2023-09-23T17:43:53.088332Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"#### Fill Missing values and change column data types","metadata":{}},{"cell_type":"code","source":"# [FOR 'GENRE'] Remove '\\n' and empty spaces.\n# Fill missing values with 'Unknown' as only 0.8% of it is missing\ndata['GENRE'] = data['GENRE'].str.replace('\\n', '').str.strip()\ndata['GENRE'].fillna('Unknown', inplace=True)\ndata['GENRE'] = data['GENRE'].astype(str)","metadata":{"execution":{"iopub.status.busy":"2023-09-23T17:43:53.090500Z","iopub.execute_input":"2023-09-23T17:43:53.090875Z","iopub.status.idle":"2023-09-23T17:43:53.108118Z","shell.execute_reply.started":"2023-09-23T17:43:53.090840Z","shell.execute_reply":"2023-09-23T17:43:53.107378Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Change type and remove '\\n' \ndata['STARS'] = data['STARS'].astype(str).str.replace('\\n', '').str.replace(' ', '').str.strip()\n\n# Expand 'STARS' to add two new attributes into the dataframe\ndata[['DIRECTOR', 'CAST']] = data['STARS'].apply(extract_director_and_stars_info).tolist()\n\n# Drop 'STARS' column\ndata = data.drop('STARS', axis=1)\ndata.head(3)","metadata":{"execution":{"iopub.status.busy":"2023-09-23T17:43:53.109324Z","iopub.execute_input":"2023-09-23T17:43:53.110041Z","iopub.status.idle":"2023-09-23T17:43:53.180913Z","shell.execute_reply.started":"2023-09-23T17:43:53.110008Z","shell.execute_reply":"2023-09-23T17:43:53.179912Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# [FOR 'YEAR'] fill missing values\ndata['YEAR'].fillna('Unknown', inplace=True)\ndata['YEAR'] = data['YEAR'].astype(str)\n\n# [FOR 'ONE-LINE'] Change type and remove '\\n' \ndata['ONE-LINE'] = data['ONE-LINE'].astype(str).str.replace('\\n', '')\ndata['ONE-LINE'] = data['ONE-LINE'].astype(str)","metadata":{"execution":{"iopub.status.busy":"2023-09-23T17:43:53.182249Z","iopub.execute_input":"2023-09-23T17:43:53.182553Z","iopub.status.idle":"2023-09-23T17:43:53.198712Z","shell.execute_reply.started":"2023-09-23T17:43:53.182527Z","shell.execute_reply":"2023-09-23T17:43:53.197863Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"For now, just fill **VOTES**, **RUNTIME** and **RATING** with 0. Later on we will impute them w.r.t to **DIRECTOR** and **CAST** mean values","metadata":{}},{"cell_type":"code","source":"# [FOR 'VOTES'] Remove ',' and NAN\ndata['VOTES'] = data['VOTES'].str.replace(',', '', regex=True)\ndata['VOTES'].fillna('0.0', inplace=True)\ndata['VOTES'] = data['VOTES'].astype(float)\n\n# [FOR 'RUNTIME'] fill missing values\ndata['RUNTIME'].fillna(0.0, inplace=True)\n\n# [FOR 'RATING'] fill missing values\ndata['RATING'].fillna(0.0, inplace=True)","metadata":{"execution":{"iopub.status.busy":"2023-09-23T17:43:53.200114Z","iopub.execute_input":"2023-09-23T17:43:53.200428Z","iopub.status.idle":"2023-09-23T17:43:53.216238Z","shell.execute_reply.started":"2023-09-23T17:43:53.200402Z","shell.execute_reply":"2023-09-23T17:43:53.215147Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Find mean of movies RUNTIME, RATING and VOTES w.r.t each **DIRECTOR**","metadata":{}},{"cell_type":"code","source":"# Group by 'Director' and calculate the mean\ndf_director_mean = data[['DIRECTOR', 'RUNTIME', 'RATING', 'VOTES']].groupby('DIRECTOR').mean().reset_index()\ndf_director_mean.head(2)","metadata":{"execution":{"iopub.status.busy":"2023-09-23T17:43:53.217711Z","iopub.execute_input":"2023-09-23T17:43:53.218232Z","iopub.status.idle":"2023-09-23T17:43:53.240988Z","shell.execute_reply.started":"2023-09-23T17:43:53.218202Z","shell.execute_reply":"2023-09-23T17:43:53.240018Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Find mean of movies RUNTIME, RATING and VOTES w.r.t each **CAST**","metadata":{}},{"cell_type":"code","source":"# Create a dataframe to store absolute values w.r.t to casts of each movies\nmovie_casts = []\nfor index, row in data.iterrows():\n    cast_list = row['CAST'].split(',')\n    for cast_member in cast_list:\n        movie_casts.append({\n            'CAST': cast_member, \n            'RUNTIME': row['RUNTIME'], \n            'RATING': row['RATING'], \n            'VOTES': row['VOTES']\n        })\n\ndf_cast_wise_insigts = pd.DataFrame(movie_casts)\n\n# Group by 'CAST' and calculate the mean\ndf_cast_mean = df_cast_wise_insigts.groupby('CAST').mean().reset_index()\ndf_cast_mean.head(2)","metadata":{"execution":{"iopub.status.busy":"2023-09-23T17:43:53.242627Z","iopub.execute_input":"2023-09-23T17:43:53.243260Z","iopub.status.idle":"2023-09-23T17:43:54.215119Z","shell.execute_reply.started":"2023-09-23T17:43:53.243222Z","shell.execute_reply":"2023-09-23T17:43:54.214330Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Find mean of movies RUNTIME, RATING and VOTES w.r.t each **GENRE**","metadata":{}},{"cell_type":"code","source":"# Create a dataframe to store absolute values w.r.t to genre of each movies\nmovie_genre = []\nfor index, row in data.iterrows():\n    genre_list = row['GENRE'].split(',')\n    for genre in genre_list:\n        movie_genre.append({\n            'GENRE': genre, \n            'RUNTIME': row['RUNTIME'], \n            'RATING': row['RATING'], \n            'VOTES': row['VOTES']\n        })\ndf_genre_wise_insigts = pd.DataFrame(movie_genre)\n\n# Group by 'CAST' and calculate the mean\ndf_genre_mean = df_genre_wise_insigts.groupby('GENRE').mean().reset_index()\ndf_genre_mean.head(2)","metadata":{"execution":{"iopub.status.busy":"2023-09-23T17:43:54.216215Z","iopub.execute_input":"2023-09-23T17:43:54.216639Z","iopub.status.idle":"2023-09-23T17:43:55.023028Z","shell.execute_reply.started":"2023-09-23T17:43:54.216612Z","shell.execute_reply":"2023-09-23T17:43:55.022018Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"***Impute RATING values where 0.0:*** [ADD NOTE]","metadata":{}},{"cell_type":"code","source":"# Extract row where 'RATING' is equal to 0.0 to impute them\nrows_to_update = data[data['RATING'] == 0.0]\n\nfor _, row in rows_to_update.iterrows():\n    # Initialize values\n    director_rating = df_director_mean[df_director_mean['DIRECTOR'] == row['DIRECTOR']].iloc[0]['RATING']\n    genre_rating_mean = 0\n    cast_rating_mean = 0\n    \n    # Iterate genre and cast lookup table to extract values\n    genre_list = [element.strip() for element in row['GENRE'].split(',')]\n    cast_list = [element.strip() for element in row['CAST'].split(',')]\n    \n    for genre in genre_list:\n        genre_rating_mean += df_genre_mean[df_genre_mean['GENRE'] == genre].iloc[0]['RATING']\n    for cast in cast_list:\n        cast_rating_mean += df_cast_mean[df_cast_mean['CAST'] == cast].iloc[0]['RATING']\n    \n    # Take mean w.r.t their count\n    genre_rating_mean = (genre_rating_mean / len(genre_list)).round(1)\n    cast_rating_mean = (cast_rating_mean / len(cast_list)).round(1)\n    \n    # Update movie 'RATING' attribute value\n    rows_to_update['RATING'] = cal_movie_rating(genre_rating_mean, director_rating, cast_rating_mean)\n    data.update(rows_to_update)","metadata":{"execution":{"iopub.status.busy":"2023-09-23T17:43:55.024118Z","iopub.execute_input":"2023-09-23T17:43:55.024397Z","iopub.status.idle":"2023-09-23T17:44:09.460374Z","shell.execute_reply.started":"2023-09-23T17:43:55.024373Z","shell.execute_reply":"2023-09-23T17:44:09.459179Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"***Impute VOTES values where 0.0:*** [ADD NOTE]","metadata":{}},{"cell_type":"code","source":"# Extract row where 'VOTES' is equal to 0.0 to impute them\nrows_to_update = data[data['VOTES'] == 0.0]\n\nfor _, row in rows_to_update.iterrows():\n    # Initialize values\n    director_votes = df_director_mean[df_director_mean['DIRECTOR'] == row['DIRECTOR']].iloc[0]['VOTES']\n    genre_votes_mean = 0\n    cast_votes_mean = 0\n    \n    # Iterate genre and cast lookup table to extract values\n    genre_list = [element.strip() for element in row['GENRE'].split(',')]\n    cast_list = [element.strip() for element in row['CAST'].split(',')]\n    \n    for genre in genre_list:\n        genre_votes_mean += df_genre_mean[df_genre_mean['GENRE'] == genre].iloc[0]['VOTES']\n    for cast in cast_list:\n        cast_votes_mean += df_cast_mean[df_cast_mean['CAST'] == cast].iloc[0]['VOTES']\n    \n    # Take mean w.r.t their count\n    genre_votes_mean = (genre_votes_mean / len(genre_list)).round(1)\n    cast_votes_mean = (cast_votes_mean / len(cast_list)).round(1)\n    \n    #Update movie 'VOTES' attribute value\n    rows_to_update['VOTES'] = cal_movie_votes(genre_votes_mean, director_votes, cast_votes_mean)\n    data.update(rows_to_update)","metadata":{"execution":{"iopub.status.busy":"2023-09-23T17:44:09.461733Z","iopub.execute_input":"2023-09-23T17:44:09.462019Z","iopub.status.idle":"2023-09-23T17:44:23.710053Z","shell.execute_reply.started":"2023-09-23T17:44:09.461994Z","shell.execute_reply":"2023-09-23T17:44:23.709209Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"***Impute RUNTIME values where 0.0:*** [ADD NOTE]","metadata":{}},{"cell_type":"code","source":"# Extract row where 'RUNTIME' is equal to 0.0 to impute them\nrows_to_update = data[data['RUNTIME'] == 0.0]\n\nfor _, row in rows_to_update.iterrows():\n    # Initialize values\n    genre_runtime_mean = 0\n    \n    # Iterate genre and cast lookup table to extract values\n    genre_list = [element.strip() for element in row['GENRE'].split(',')]\n    \n    for genre in genre_list:\n        genre_runtime_mean += df_genre_mean[df_genre_mean['GENRE'] == genre].iloc[0]['RUNTIME']\n    \n    # Take mean w.r.t their count\n    genre_runtime_mean = (genre_runtime_mean / len(genre_list)).round(1)\n    \n    #Update movie 'RUNTIME' attribute value\n    rows_to_update['RUNTIME'] = genre_runtime_mean\n    data.update(rows_to_update)","metadata":{"execution":{"iopub.status.busy":"2023-09-23T17:44:23.711141Z","iopub.execute_input":"2023-09-23T17:44:23.711922Z","iopub.status.idle":"2023-09-23T17:44:39.970779Z","shell.execute_reply.started":"2023-09-23T17:44:23.711892Z","shell.execute_reply":"2023-09-23T17:44:39.969685Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"data.head(10)","metadata":{"execution":{"iopub.status.busy":"2023-09-23T17:44:39.972233Z","iopub.execute_input":"2023-09-23T17:44:39.972620Z","iopub.status.idle":"2023-09-23T17:44:39.989881Z","shell.execute_reply.started":"2023-09-23T17:44:39.972587Z","shell.execute_reply":"2023-09-23T17:44:39.988728Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## TODO:\n* Maintain directors count and cast count in a dict\n* Change type","metadata":{}},{"cell_type":"markdown","source":"# Data Transformation","metadata":{}},{"cell_type":"markdown","source":"#### Add conditional column to distinguish between Movie and Series","metadata":{}},{"cell_type":"code","source":"# Create a conditional column \"Type\"\ndata['Type'] = data['YEAR'].apply(lambda x: 'Movie' if pd.notna(x) and pd.Series(x).str.match(REGEX_MOVIE_YEAR).any() else 'Series')\ndata.head(5)","metadata":{"execution":{"iopub.status.busy":"2023-09-23T17:44:39.991367Z","iopub.execute_input":"2023-09-23T17:44:39.991843Z","iopub.status.idle":"2023-09-23T17:44:43.011291Z","shell.execute_reply.started":"2023-09-23T17:44:39.991806Z","shell.execute_reply":"2023-09-23T17:44:43.010448Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"genre_count = get_genre_count(data['GENRE'])\ngenre_count","metadata":{"execution":{"iopub.status.busy":"2023-09-23T17:44:43.012446Z","iopub.execute_input":"2023-09-23T17:44:43.012795Z","iopub.status.idle":"2023-09-23T17:44:43.035881Z","shell.execute_reply.started":"2023-09-23T17:44:43.012769Z","shell.execute_reply":"2023-09-23T17:44:43.035185Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Check unique values\ndata.nunique()","metadata":{"execution":{"iopub.status.busy":"2023-09-23T17:44:43.039784Z","iopub.execute_input":"2023-09-23T17:44:43.041497Z","iopub.status.idle":"2023-09-23T17:44:43.063618Z","shell.execute_reply.started":"2023-09-23T17:44:43.041458Z","shell.execute_reply":"2023-09-23T17:44:43.062694Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Check insights, correlation and covariance","metadata":{}},{"cell_type":"code","source":"df_insights = data[['RATING', 'VOTES', 'RUNTIME']]\ndf_insights.describe()","metadata":{"execution":{"iopub.status.busy":"2023-09-23T17:44:43.064716Z","iopub.execute_input":"2023-09-23T17:44:43.064994Z","iopub.status.idle":"2023-09-23T17:44:43.088367Z","shell.execute_reply.started":"2023-09-23T17:44:43.064970Z","shell.execute_reply":"2023-09-23T17:44:43.087542Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_insights.corr()","metadata":{"execution":{"iopub.status.busy":"2023-09-23T17:44:43.089574Z","iopub.execute_input":"2023-09-23T17:44:43.089840Z","iopub.status.idle":"2023-09-23T17:44:43.101367Z","shell.execute_reply.started":"2023-09-23T17:44:43.089816Z","shell.execute_reply":"2023-09-23T17:44:43.100605Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_insights.cov()","metadata":{"execution":{"iopub.status.busy":"2023-09-23T17:44:43.102473Z","iopub.execute_input":"2023-09-23T17:44:43.102948Z","iopub.status.idle":"2023-09-23T17:44:43.115623Z","shell.execute_reply.started":"2023-09-23T17:44:43.102921Z","shell.execute_reply":"2023-09-23T17:44:43.114721Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Data Visualization","metadata":{}},{"cell_type":"code","source":"# [HEAT MAP] Plotting Correlation between the variables\n# Setting plot size\nplt.figure(figsize=(5,5))\nsns.heatmap(df_insights.corr(), annot=True, cbar=True, annot_kws={'size': 12}, cmap=\"Blues\")\nplt.show","metadata":{"execution":{"iopub.status.busy":"2023-09-23T17:44:43.117035Z","iopub.execute_input":"2023-09-23T17:44:43.117349Z","iopub.status.idle":"2023-09-23T17:44:43.418511Z","shell.execute_reply.started":"2023-09-23T17:44:43.117323Z","shell.execute_reply":"2023-09-23T17:44:43.417212Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sns.pairplot(data, hue='Type')","metadata":{"execution":{"iopub.status.busy":"2023-09-23T17:44:43.419787Z","iopub.execute_input":"2023-09-23T17:44:43.420106Z","iopub.status.idle":"2023-09-23T17:44:51.489765Z","shell.execute_reply.started":"2023-09-23T17:44:43.420076Z","shell.execute_reply":"2023-09-23T17:44:51.488687Z"},"trusted":true},"execution_count":null,"outputs":[]}]}