Cleaning Data.ipynb

{"nbformat":4,"nbformat_minor":0,"metadata":{"colab":{"name":"Cleaning Data.ipynb","provenance":[],"collapsed_sections":[],"toc_visible":true},"kernelspec":{"name":"python3","display_name":"Python 3"}},"cells":[{"cell_type":"markdown","metadata":{"id":"jm81aHqz5TPZ","colab_type":"text"},"source":["# Predicting Movie Genres: Data Cleaning\n","\n","In this notebook, we clean the data by converting the datasets we fetched in Notebook 1 in CSV format to a DataFrame, clean the data by transforming columns to be useful for our purposes, and finally merge the DataFrames to produce one with all the features that define a movie (inclusing genres). "]},{"cell_type":"markdown","metadata":{"id":"_Ip0sRsS5HDG","colab_type":"text"},"source":["## Make all neccessary imports and setup the environment"]},{"cell_type":"code","metadata":{"id":"VOoXAZ1eg6Gi","colab_type":"code","colab":{}},"source":["import pandas as pd\n","from google.colab import drive"],"execution_count":0,"outputs":[]},{"cell_type":"code","metadata":{"id":"5RoSmA9R6PxV","colab_type":"code","outputId":"cd065b5d-b711-4b04-faec-55db4bdb235e","executionInfo":{"status":"ok","timestamp":1584577148643,"user_tz":420,"elapsed":29239,"user":{"displayName":"Rupal Totale","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14GgNnZACG8nsOCQJw12ujN1wTFuaoxhbRCW36hT9aoo=s64","userId":"09681754785651351739"}},"colab":{"base_uri":"https://localhost:8080/","height":122}},"source":["drive.mount('drive')"],"execution_count":0,"outputs":[{"output_type":"stream","text":["Go to this URL in a browser: https://accounts.google.com/o/oauth2/auth?client_id=947318989803-6bn6qk8qdgf4n4g3pfee6491hc0brc4i.apps.googleusercontent.com&redirect_uri=urn%3aietf%3awg%3aoauth%3a2.0%3aoob&response_type=code&scope=email%20https%3a%2f%2fwww.googleapis.com%2fauth%2fdocs.test%20https%3a%2f%2fwww.googleapis.com%2fauth%2fdrive%20https%3a%2f%2fwww.googleapis.com%2fauth%2fdrive.photos.readonly%20https%3a%2f%2fwww.googleapis.com%2fauth%2fpeopleapi.readonly\n","\n","Enter your authorization code:\n","··········\n","Mounted at drive\n"],"name":"stdout"}]},{"cell_type":"code","metadata":{"id":"zxPN4ptv7aS0","colab_type":"code","colab":{}},"source":["data_dir = \"drive/My Drive/301 Project/Data/\""],"execution_count":0,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"llrwuqMdGMbp","colab_type":"text"},"source":["## Clean and convert Kaggle data set to DataFrame"]},{"cell_type":"code","metadata":{"id":"VLg847NkGtfj","colab_type":"code","outputId":"372e4541-c7bc-41dd-f417-8fb0cb9c239d","executionInfo":{"status":"ok","timestamp":1584515530672,"user_tz":420,"elapsed":36705,"user":{"displayName":"Rupal Totale","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14GgNnZACG8nsOCQJw12ujN1wTFuaoxhbRCW36hT9aoo=s64","userId":"09681754785651351739"}},"colab":{"base_uri":"https://localhost:8080/","height":187}},"source":["print(\"Files for Kaggle: \\n\")\n","!ls \"drive/My Drive/301 Project/Data/\" | grep .csv"],"execution_count":0,"outputs":[{"output_type":"stream","text":["Files for Kaggle: \n","\n","cleaned_dataframe.csv\n","credits.csv\n","keywords.csv\n","links.csv\n","links_small.csv\n","movies_metadata.csv\n","ratings.csv\n","ratings_small.csv\n"],"name":"stdout"}]},{"cell_type":"markdown","metadata":{"id":"2PZ6ifWV9feB","colab_type":"text"},"source":["### Convert user_ratings.csv to a DataFrame, clean it, and extract movie ids from it"]},{"cell_type":"code","metadata":{"id":"q_PuAYSyVnIs","colab_type":"code","outputId":"a7b001e8-a6a1-4acd-d63f-4c99779513e5","executionInfo":{"status":"ok","timestamp":1584515543226,"user_tz":420,"elapsed":49250,"user":{"displayName":"Rupal Totale","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14GgNnZACG8nsOCQJw12ujN1wTFuaoxhbRCW36hT9aoo=s64","userId":"09681754785651351739"}},"colab":{"base_uri":"https://localhost:8080/","height":204}},"source":["df_ratings = pd.read_csv(data_dir + \"ratings.csv\")\n","df_ratings.head()"],"execution_count":0,"outputs":[{"output_type":"execute_result","data":{"text/html":["<div>\n","<style scoped>\n","    .dataframe tbody tr th:only-of-type {\n","        vertical-align: middle;\n","    }\n","\n","    .dataframe tbody tr th {\n","        vertical-align: top;\n","    }\n","\n","    .dataframe thead th {\n","        text-align: right;\n","    }\n","</style>\n","<table border=\"1\" class=\"dataframe\">\n","  <thead>\n","    <tr style=\"text-align: right;\">\n","      <th></th>\n","      <th>userId</th>\n","      <th>movieId</th>\n","      <th>rating</th>\n","      <th>timestamp</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>0</th>\n","      <td>1</td>\n","      <td>110</td>\n","      <td>1.0</td>\n","      <td>1425941529</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>1</td>\n","      <td>147</td>\n","      <td>4.5</td>\n","      <td>1425942435</td>\n","    </tr>\n","    <tr>\n","      <th>2</th>\n","      <td>1</td>\n","      <td>858</td>\n","      <td>5.0</td>\n","      <td>1425941523</td>\n","    </tr>\n","    <tr>\n","      <th>3</th>\n","      <td>1</td>\n","      <td>1221</td>\n","      <td>5.0</td>\n","      <td>1425941546</td>\n","    </tr>\n","    <tr>\n","      <th>4</th>\n","      <td>1</td>\n","      <td>1246</td>\n","      <td>5.0</td>\n","      <td>1425941556</td>\n","    </tr>\n","  </tbody>\n","</table>\n","</div>"],"text/plain":["   userId  movieId  rating   timestamp\n","0       1      110     1.0  1425941529\n","1       1      147     4.5  1425942435\n","2       1      858     5.0  1425941523\n","3       1     1221     5.0  1425941546\n","4       1     1246     5.0  1425941556"]},"metadata":{"tags":[]},"execution_count":6}]},{"cell_type":"code","metadata":{"id":"KoQl6S_ZuzrS","colab_type":"code","outputId":"0aced766-61cc-4f19-a886-6f5d446194a8","executionInfo":{"status":"ok","timestamp":1584515564229,"user_tz":420,"elapsed":70245,"user":{"displayName":"Rupal Totale","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14GgNnZACG8nsOCQJw12ujN1wTFuaoxhbRCW36hT9aoo=s64","userId":"09681754785651351739"}},"colab":{"base_uri":"https://localhost:8080/","height":419}},"source":["# Add average user rating as a column\n","df_ratings_clean = df_ratings.join(\n","    df_ratings.groupby(\"movieId\")[\"rating\"].mean(), \n","    on='movieId', \n","    rsuffix='_average'\n",")\n","\n","# Setup: Add users who like a given movie as a column\n","def convert_to_string(lst):\n","  ids = \"\"\n","  for i in lst:\n","    ids += str(i) + \" \"\n","  return ids\n","\n","good_user_ids = (df_ratings_clean[df_ratings_clean[\"rating\"] > 3]\n","                 .groupby(\"movieId\")[\"userId\"]\n","                 .apply(list))\n","\n","df_ratings_clean = (\n","    # Add users who like a given movie as a column\n","    df_ratings_clean.join(\n","      good_user_ids.apply(convert_to_string), \n","      on='movieId', \n","      rsuffix='_who_like'\n","    )\n","    # Drop userId column and rating column as they are no longer needed\n","    .drop(columns=[\"userId\", \"rating\", \"timestamp\"], axis = 1)\n","    # Movie ids should be unique now as each row is a distinct movie\n","    .drop_duplicates(subset='movieId', keep='first')\n","    # To be consistent with other datasets, rename movieId\n","    .rename(columns={'movieId': 'id'})\n",")\n","df_ratings_clean"],"execution_count":0,"outputs":[{"output_type":"execute_result","data":{"text/html":["<div>\n","<style scoped>\n","    .dataframe tbody tr th:only-of-type {\n","        vertical-align: middle;\n","    }\n","\n","    .dataframe tbody tr th {\n","        vertical-align: top;\n","    }\n","\n","    .dataframe thead th {\n","        text-align: right;\n","    }\n","</style>\n","<table border=\"1\" class=\"dataframe\">\n","  <thead>\n","    <tr style=\"text-align: right;\">\n","      <th></th>\n","      <th>id</th>\n","      <th>rating_average</th>\n","      <th>userId_who_like</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>0</th>\n","      <td>110</td>\n","      <td>4.016057</td>\n","      <td>11 22 24 30 34 49 56 58 63 64 65 68 80 82 88 8...</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>147</td>\n","      <td>3.595933</td>\n","      <td>1 24 70 142 463 638 690 726 778 831 882 884 88...</td>\n","    </tr>\n","    <tr>\n","      <th>2</th>\n","      <td>858</td>\n","      <td>4.339811</td>\n","      <td>1 3 5 12 20 24 27 28 30 37 47 49 50 56 62 63 6...</td>\n","    </tr>\n","    <tr>\n","      <th>3</th>\n","      <td>1221</td>\n","      <td>4.263475</td>\n","      <td>1 4 12 20 24 27 28 34 47 49 50 56 63 65 89 120...</td>\n","    </tr>\n","    <tr>\n","      <th>4</th>\n","      <td>1246</td>\n","      <td>3.912803</td>\n","      <td>1 9 12 24 49 62 64 68 82 97 98 106 115 120 132...</td>\n","    </tr>\n","    <tr>\n","      <th>...</th>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","    </tr>\n","    <tr>\n","      <th>26023429</th>\n","      <td>159050</td>\n","      <td>5.000000</td>\n","      <td>270887</td>\n","    </tr>\n","    <tr>\n","      <th>26023430</th>\n","      <td>159053</td>\n","      <td>5.000000</td>\n","      <td>270887</td>\n","    </tr>\n","    <tr>\n","      <th>26023482</th>\n","      <td>165649</td>\n","      <td>4.000000</td>\n","      <td>270887</td>\n","    </tr>\n","    <tr>\n","      <th>26023515</th>\n","      <td>171051</td>\n","      <td>4.000000</td>\n","      <td>270887</td>\n","    </tr>\n","    <tr>\n","      <th>26023516</th>\n","      <td>171221</td>\n","      <td>5.000000</td>\n","      <td>270887</td>\n","    </tr>\n","  </tbody>\n","</table>\n","<p>45115 rows × 3 columns</p>\n","</div>"],"text/plain":["              id  ...                                    userId_who_like\n","0            110  ...  11 22 24 30 34 49 56 58 63 64 65 68 80 82 88 8...\n","1            147  ...  1 24 70 142 463 638 690 726 778 831 882 884 88...\n","2            858  ...  1 3 5 12 20 24 27 28 30 37 47 49 50 56 62 63 6...\n","3           1221  ...  1 4 12 20 24 27 28 34 47 49 50 56 63 65 89 120...\n","4           1246  ...  1 9 12 24 49 62 64 68 82 97 98 106 115 120 132...\n","...          ...  ...                                                ...\n","26023429  159050  ...                                            270887 \n","26023430  159053  ...                                            270887 \n","26023482  165649  ...                                            270887 \n","26023515  171051  ...                                            270887 \n","26023516  171221  ...                                            270887 \n","\n","[45115 rows x 3 columns]"]},"metadata":{"tags":[]},"execution_count":7}]},{"cell_type":"code","metadata":{"id":"WjmWhfo0JQjw","colab_type":"code","outputId":"b1e91ff6-c369-421b-ae57-d87152cc0d48","executionInfo":{"status":"ok","timestamp":1584515564230,"user_tz":420,"elapsed":70236,"user":{"displayName":"Rupal Totale","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14GgNnZACG8nsOCQJw12ujN1wTFuaoxhbRCW36hT9aoo=s64","userId":"09681754785651351739"}},"colab":{"base_uri":"https://localhost:8080/","height":34}},"source":["movie_ids_dict = set(df_ratings_clean[\"id\"].value_counts().index)\n","print(\"Number of movies being considered so far:\", len(movie_ids_dict))"],"execution_count":0,"outputs":[{"output_type":"stream","text":["Number of movies being considered so far: 45115\n"],"name":"stdout"}]},{"cell_type":"markdown","metadata":{"id":"60IMwKsIGFWn","colab_type":"text"},"source":["### Convert credits.csv to a data frame and clean it"]},{"cell_type":"code","metadata":{"id":"OXX3tR9a6cqV","colab_type":"code","outputId":"29568fab-9949-4e36-ad54-95149f3bb558","executionInfo":{"status":"ok","timestamp":1584515568003,"user_tz":420,"elapsed":74000,"user":{"displayName":"Rupal Totale","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14GgNnZACG8nsOCQJw12ujN1wTFuaoxhbRCW36hT9aoo=s64","userId":"09681754785651351739"}},"colab":{"base_uri":"https://localhost:8080/","height":204}},"source":["df_credits = pd.read_csv(data_dir + \"credits.csv\")\n","df_credits.head()"],"execution_count":0,"outputs":[{"output_type":"execute_result","data":{"text/html":["<div>\n","<style scoped>\n","    .dataframe tbody tr th:only-of-type {\n","        vertical-align: middle;\n","    }\n","\n","    .dataframe tbody tr th {\n","        vertical-align: top;\n","    }\n","\n","    .dataframe thead th {\n","        text-align: right;\n","    }\n","</style>\n","<table border=\"1\" class=\"dataframe\">\n","  <thead>\n","    <tr style=\"text-align: right;\">\n","      <th></th>\n","      <th>cast</th>\n","      <th>crew</th>\n","      <th>id</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>0</th>\n","      <td>[{'cast_id': 14, 'character': 'Woody (voice)',...</td>\n","      <td>[{'credit_id': '52fe4284c3a36847f8024f49', 'de...</td>\n","      <td>862</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>[{'cast_id': 1, 'character': 'Alan Parrish', '...</td>\n","      <td>[{'credit_id': '52fe44bfc3a36847f80a7cd1', 'de...</td>\n","      <td>8844</td>\n","    </tr>\n","    <tr>\n","      <th>2</th>\n","      <td>[{'cast_id': 2, 'character': 'Max Goldman', 'c...</td>\n","      <td>[{'credit_id': '52fe466a9251416c75077a89', 'de...</td>\n","      <td>15602</td>\n","    </tr>\n","    <tr>\n","      <th>3</th>\n","      <td>[{'cast_id': 1, 'character': \"Savannah 'Vannah...</td>\n","      <td>[{'credit_id': '52fe44779251416c91011acb', 'de...</td>\n","      <td>31357</td>\n","    </tr>\n","    <tr>\n","      <th>4</th>\n","      <td>[{'cast_id': 1, 'character': 'George Banks', '...</td>\n","      <td>[{'credit_id': '52fe44959251416c75039ed7', 'de...</td>\n","      <td>11862</td>\n","    </tr>\n","  </tbody>\n","</table>\n","</div>"],"text/plain":["                                                cast  ...     id\n","0  [{'cast_id': 14, 'character': 'Woody (voice)',...  ...    862\n","1  [{'cast_id': 1, 'character': 'Alan Parrish', '...  ...   8844\n","2  [{'cast_id': 2, 'character': 'Max Goldman', 'c...  ...  15602\n","3  [{'cast_id': 1, 'character': \"Savannah 'Vannah...  ...  31357\n","4  [{'cast_id': 1, 'character': 'George Banks', '...  ...  11862\n","\n","[5 rows x 3 columns]"]},"metadata":{"tags":[]},"execution_count":9}]},{"cell_type":"code","metadata":{"id":"cnCOx6IL_zuN","colab_type":"code","outputId":"a8745d46-d909-4a1d-9f3f-db5c97661c21","executionInfo":{"status":"ok","timestamp":1584515587850,"user_tz":420,"elapsed":93839,"user":{"displayName":"Rupal Totale","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14GgNnZACG8nsOCQJw12ujN1wTFuaoxhbRCW36hT9aoo=s64","userId":"09681754785651351739"}},"colab":{"base_uri":"https://localhost:8080/","height":419}},"source":["\"\"\"\n","Crew ==> Get names of Screenplay, Director, Composer\n","Cast ==> Get all names, underscore instead of space for each member's full name,\n","         space seperated\n","\"\"\"\n","def clean_credits(obs):\n","  # Crew\n","  crew_members = eval(obs[\"crew\"])\n","  for person in crew_members:\n","    if person[\"job\"] == \"Screenplay\":\n","      obs[\"Screenplay\"] = person[\"name\"].replace(\" \", \"_\")\n","    elif person[\"job\"] == \"Director\":\n","      obs[\"Director\"] =  person[\"name\"].replace(\" \", \"_\")\n","    elif person[\"job\"] == \"Original Music Composer\":\n","      obs[\"Composer\"] =  person[\"name\"].replace(\" \", \"_\")\n","  # Cast\n","  cast = eval(obs[\"cast\"])\n","  names = \"\"\n","  for person in cast:\n","    names += person[\"name\"].replace(\" \", \"_\") + \" \"\n","  obs[\"cast\"] = names\n","  return obs\n","\n","df_credits_clean = (\n","    df_credits[df_credits[\"id\"].isin(movie_ids_dict)]\n","               .apply(clean_credits, axis=1)\n","               # Crew is no longer needed\n","               .drop(\n","                  columns=[\"crew\"], \n","                  axis=1)\n","               )\n","df_credits_clean"],"execution_count":0,"outputs":[{"output_type":"execute_result","data":{"text/html":["<div>\n","<style scoped>\n","    .dataframe tbody tr th:only-of-type {\n","        vertical-align: middle;\n","    }\n","\n","    .dataframe tbody tr th {\n","        vertical-align: top;\n","    }\n","\n","    .dataframe thead th {\n","        text-align: right;\n","    }\n","</style>\n","<table border=\"1\" class=\"dataframe\">\n","  <thead>\n","    <tr style=\"text-align: right;\">\n","      <th></th>\n","      <th>Composer</th>\n","      <th>Director</th>\n","      <th>Screenplay</th>\n","      <th>cast</th>\n","      <th>id</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>0</th>\n","      <td>NaN</td>\n","      <td>John_Lasseter</td>\n","      <td>Alec_Sokolow</td>\n","      <td>Tom_Hanks Tim_Allen Don_Rickles Jim_Varney Wal...</td>\n","      <td>862</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>James_Horner</td>\n","      <td>Joe_Johnston</td>\n","      <td>Jim_Strain</td>\n","      <td>Robin_Williams Jonathan_Hyde Kirsten_Dunst Bra...</td>\n","      <td>8844</td>\n","    </tr>\n","    <tr>\n","      <th>5</th>\n","      <td>Elliot_Goldenthal</td>\n","      <td>Michael_Mann</td>\n","      <td>Michael_Mann</td>\n","      <td>Al_Pacino Robert_De_Niro Val_Kilmer Jon_Voight...</td>\n","      <td>949</td>\n","    </tr>\n","    <tr>\n","      <th>9</th>\n","      <td>Eric_Serra</td>\n","      <td>Martin_Campbell</td>\n","      <td>Bruce_Feirstein</td>\n","      <td>Pierce_Brosnan Sean_Bean Izabella_Scorupco Fam...</td>\n","      <td>710</td>\n","    </tr>\n","    <tr>\n","      <th>14</th>\n","      <td>NaN</td>\n","      <td>Renny_Harlin</td>\n","      <td>Robert_King</td>\n","      <td>Geena_Davis Matthew_Modine Frank_Langella Maur...</td>\n","      <td>1408</td>\n","    </tr>\n","    <tr>\n","      <th>...</th>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","    </tr>\n","    <tr>\n","      <th>45416</th>\n","      <td>NaN</td>\n","      <td>Sergey_Ivanov</td>\n","      <td>NaN</td>\n","      <td>Kristina_Asmus Nikita_Efremov Artyom_Tkachenko...</td>\n","      <td>98604</td>\n","    </tr>\n","    <tr>\n","      <th>45443</th>\n","      <td>NaN</td>\n","      <td>Bořivoj_Zeman</td>\n","      <td>František_Vlček</td>\n","      <td>Helena_Vondráčková Václav_Neckář Jaroslav_Marv...</td>\n","      <td>5589</td>\n","    </tr>\n","    <tr>\n","      <th>45446</th>\n","      <td>NaN</td>\n","      <td>Uwe_Boll</td>\n","      <td>NaN</td>\n","      <td>Lauren_Holly Luke_Perry Steve_Bacic Cole_Heppe...</td>\n","      <td>45527</td>\n","    </tr>\n","    <tr>\n","      <th>45460</th>\n","      <td>NaN</td>\n","      <td>Georges_Méliès</td>\n","      <td>NaN</td>\n","      <td>Georges_Méliès</td>\n","      <td>49280</td>\n","    </tr>\n","    <tr>\n","      <th>45472</th>\n","      <td>NaN</td>\n","      <td>Lav_Diaz</td>\n","      <td>NaN</td>\n","      <td>Angel_Aquino Perry_Dizon Hazel_Orencio Joel_To...</td>\n","      <td>111109</td>\n","    </tr>\n","  </tbody>\n","</table>\n","<p>7571 rows × 5 columns</p>\n","</div>"],"text/plain":["                Composer  ...      id\n","0                    NaN  ...     862\n","1           James_Horner  ...    8844\n","5      Elliot_Goldenthal  ...     949\n","9             Eric_Serra  ...     710\n","14                   NaN  ...    1408\n","...                  ...  ...     ...\n","45416                NaN  ...   98604\n","45443                NaN  ...    5589\n","45446                NaN  ...   45527\n","45460                NaN  ...   49280\n","45472                NaN  ...  111109\n","\n","[7571 rows x 5 columns]"]},"metadata":{"tags":[]},"execution_count":10}]},{"cell_type":"markdown","metadata":{"id":"k5bGxE7vqX3E","colab_type":"text"},"source":["**Note**: The reason this DataFrame only consists of ~7571 observations even though we were considering around 45,000 movies in the previous section is because not all movie ids in user_ratings appear (or are defined) in other data sets. Same logic applies for the next two DataSets."]},{"cell_type":"markdown","metadata":{"id":"g_gf52VVLPqN","colab_type":"text"},"source":["### Convert keywords.csv to a data frame and clean it"]},{"cell_type":"code","metadata":{"id":"j8B7TWKmGRYX","colab_type":"code","outputId":"6dc2d566-c299-4e98-c7c4-f60db5bcd50b","executionInfo":{"status":"ok","timestamp":1584515588487,"user_tz":420,"elapsed":94469,"user":{"displayName":"Rupal Totale","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14GgNnZACG8nsOCQJw12ujN1wTFuaoxhbRCW36hT9aoo=s64","userId":"09681754785651351739"}},"colab":{"base_uri":"https://localhost:8080/","height":204}},"source":["df_keywords = pd.read_csv(data_dir + \"keywords.csv\")\n","df_keywords.head()"],"execution_count":0,"outputs":[{"output_type":"execute_result","data":{"text/html":["<div>\n","<style scoped>\n","    .dataframe tbody tr th:only-of-type {\n","        vertical-align: middle;\n","    }\n","\n","    .dataframe tbody tr th {\n","        vertical-align: top;\n","    }\n","\n","    .dataframe thead th {\n","        text-align: right;\n","    }\n","</style>\n","<table border=\"1\" class=\"dataframe\">\n","  <thead>\n","    <tr style=\"text-align: right;\">\n","      <th></th>\n","      <th>id</th>\n","      <th>keywords</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>0</th>\n","      <td>862</td>\n","      <td>[{'id': 931, 'name': 'jealousy'}, {'id': 4290,...</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>8844</td>\n","      <td>[{'id': 10090, 'name': 'board game'}, {'id': 1...</td>\n","    </tr>\n","    <tr>\n","      <th>2</th>\n","      <td>15602</td>\n","      <td>[{'id': 1495, 'name': 'fishing'}, {'id': 12392...</td>\n","    </tr>\n","    <tr>\n","      <th>3</th>\n","      <td>31357</td>\n","      <td>[{'id': 818, 'name': 'based on novel'}, {'id':...</td>\n","    </tr>\n","    <tr>\n","      <th>4</th>\n","      <td>11862</td>\n","      <td>[{'id': 1009, 'name': 'baby'}, {'id': 1599, 'n...</td>\n","    </tr>\n","  </tbody>\n","</table>\n","</div>"],"text/plain":["      id                                           keywords\n","0    862  [{'id': 931, 'name': 'jealousy'}, {'id': 4290,...\n","1   8844  [{'id': 10090, 'name': 'board game'}, {'id': 1...\n","2  15602  [{'id': 1495, 'name': 'fishing'}, {'id': 12392...\n","3  31357  [{'id': 818, 'name': 'based on novel'}, {'id':...\n","4  11862  [{'id': 1009, 'name': 'baby'}, {'id': 1599, 'n..."]},"metadata":{"tags":[]},"execution_count":11}]},{"cell_type":"code","metadata":{"id":"fcDFIOYKHTjE","colab_type":"code","outputId":"b566566a-ab5a-476f-fb90-f973e84f51cd","executionInfo":{"status":"ok","timestamp":1584515590142,"user_tz":420,"elapsed":96117,"user":{"displayName":"Rupal Totale","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14GgNnZACG8nsOCQJw12ujN1wTFuaoxhbRCW36hT9aoo=s64","userId":"09681754785651351739"}},"colab":{"base_uri":"https://localhost:8080/","height":204}},"source":["\"\"\"\n","Clean columns:\n","  keywords ==> keywords.name with underscore replacing space in each keyword,\n","               space seperated\n","\"\"\"\n","def clean_keywords(obs):\n","  keywords_as_string = \"\"\n","  for keyword in eval(obs[\"keywords\"]):\n","    keywords_as_string += keyword[\"name\"].replace(\" \", \"_\") + \" \"\n","  obs[\"keywords\"] = keywords_as_string\n","  return obs\n","  \n","df_keywords_clean = (\n","    df_keywords[df_keywords[\"id\"].isin(movie_ids_dict)]\n","               .apply(clean_keywords, axis=1)\n",")\n","df_keywords_clean.head()"],"execution_count":0,"outputs":[{"output_type":"execute_result","data":{"text/html":["<div>\n","<style scoped>\n","    .dataframe tbody tr th:only-of-type {\n","        vertical-align: middle;\n","    }\n","\n","    .dataframe tbody tr th {\n","        vertical-align: top;\n","    }\n","\n","    .dataframe thead th {\n","        text-align: right;\n","    }\n","</style>\n","<table border=\"1\" class=\"dataframe\">\n","  <thead>\n","    <tr style=\"text-align: right;\">\n","      <th></th>\n","      <th>id</th>\n","      <th>keywords</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>0</th>\n","      <td>862</td>\n","      <td>jealousy toy boy friendship friends rivalry bo...</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>8844</td>\n","      <td>board_game disappearance based_on_children's_b...</td>\n","    </tr>\n","    <tr>\n","      <th>5</th>\n","      <td>949</td>\n","      <td>robbery detective bank obsession chase shootin...</td>\n","    </tr>\n","    <tr>\n","      <th>9</th>\n","      <td>710</td>\n","      <td>cuba falsely_accused secret_identity computer_...</td>\n","    </tr>\n","    <tr>\n","      <th>14</th>\n","      <td>1408</td>\n","      <td>exotic_island treasure map ship scalp pirate</td>\n","    </tr>\n","  </tbody>\n","</table>\n","</div>"],"text/plain":["      id                                           keywords\n","0    862  jealousy toy boy friendship friends rivalry bo...\n","1   8844  board_game disappearance based_on_children's_b...\n","5    949  robbery detective bank obsession chase shootin...\n","9    710  cuba falsely_accused secret_identity computer_...\n","14  1408      exotic_island treasure map ship scalp pirate "]},"metadata":{"tags":[]},"execution_count":12}]},{"cell_type":"markdown","metadata":{"id":"u-7KIQkGWIif","colab_type":"text"},"source":["### Convert movies_metadata.csv to a DataFrame and clean it"]},{"cell_type":"code","metadata":{"id":"d5QwEMumWYwb","colab_type":"code","outputId":"77c935e9-2505-4863-ca88-3211609063c8","executionInfo":{"status":"ok","timestamp":1584515591642,"user_tz":420,"elapsed":97608,"user":{"displayName":"Rupal Totale","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14GgNnZACG8nsOCQJw12ujN1wTFuaoxhbRCW36hT9aoo=s64","userId":"09681754785651351739"}},"colab":{"base_uri":"https://localhost:8080/","height":666}},"source":["df_meta = pd.read_csv(data_dir + \"movies_metadata.csv\", low_memory=False)\n","# This are corrupt observation (where movie id is not an integer), \n","# so they need to be weeded out\n","df_meta = df_meta[pd.to_numeric(df_meta['id'], errors='coerce').notnull()]\n","df_meta[\"id\"] = df_meta[\"id\"].astype(int)\n","df_meta.head()"],"execution_count":0,"outputs":[{"output_type":"execute_result","data":{"text/html":["<div>\n","<style scoped>\n","    .dataframe tbody tr th:only-of-type {\n","        vertical-align: middle;\n","    }\n","\n","    .dataframe tbody tr th {\n","        vertical-align: top;\n","    }\n","\n","    .dataframe thead th {\n","        text-align: right;\n","    }\n","</style>\n","<table border=\"1\" class=\"dataframe\">\n","  <thead>\n","    <tr style=\"text-align: right;\">\n","      <th></th>\n","      <th>adult</th>\n","      <th>belongs_to_collection</th>\n","      <th>budget</th>\n","      <th>genres</th>\n","      <th>homepage</th>\n","      <th>id</th>\n","      <th>imdb_id</th>\n","      <th>original_language</th>\n","      <th>original_title</th>\n","      <th>overview</th>\n","      <th>popularity</th>\n","      <th>poster_path</th>\n","      <th>production_companies</th>\n","      <th>production_countries</th>\n","      <th>release_date</th>\n","      <th>revenue</th>\n","      <th>runtime</th>\n","      <th>spoken_languages</th>\n","      <th>status</th>\n","      <th>tagline</th>\n","      <th>title</th>\n","      <th>video</th>\n","      <th>vote_average</th>\n","      <th>vote_count</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>0</th>\n","      <td>False</td>\n","      <td>{'id': 10194, 'name': 'Toy Story Collection', ...</td>\n","      <td>30000000</td>\n","      <td>[{'id': 16, 'name': 'Animation'}, {'id': 35, '...</td>\n","      <td>http://toystory.disney.com/toy-story</td>\n","      <td>862</td>\n","      <td>tt0114709</td>\n","      <td>en</td>\n","      <td>Toy Story</td>\n","      <td>Led by Woody, Andy's toys live happily in his ...</td>\n","      <td>21.946943</td>\n","      <td>/rhIRbceoE9lR4veEXuwCC2wARtG.jpg</td>\n","      <td>[{'name': 'Pixar Animation Studios', 'id': 3}]</td>\n","      <td>[{'iso_3166_1': 'US', 'name': 'United States o...</td>\n","      <td>1995-10-30</td>\n","      <td>373554033.0</td>\n","      <td>81.0</td>\n","      <td>[{'iso_639_1': 'en', 'name': 'English'}]</td>\n","      <td>Released</td>\n","      <td>NaN</td>\n","      <td>Toy Story</td>\n","      <td>False</td>\n","      <td>7.7</td>\n","      <td>5415.0</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>False</td>\n","      <td>NaN</td>\n","      <td>65000000</td>\n","      <td>[{'id': 12, 'name': 'Adventure'}, {'id': 14, '...</td>\n","      <td>NaN</td>\n","      <td>8844</td>\n","      <td>tt0113497</td>\n","      <td>en</td>\n","      <td>Jumanji</td>\n","      <td>When siblings Judy and Peter discover an encha...</td>\n","      <td>17.015539</td>\n","      <td>/vzmL6fP7aPKNKPRTFnZmiUfciyV.jpg</td>\n","      <td>[{'name': 'TriStar Pictures', 'id': 559}, {'na...</td>\n","      <td>[{'iso_3166_1': 'US', 'name': 'United States o...</td>\n","      <td>1995-12-15</td>\n","      <td>262797249.0</td>\n","      <td>104.0</td>\n","      <td>[{'iso_639_1': 'en', 'name': 'English'}, {'iso...</td>\n","      <td>Released</td>\n","      <td>Roll the dice and unleash the excitement!</td>\n","      <td>Jumanji</td>\n","      <td>False</td>\n","      <td>6.9</td>\n","      <td>2413.0</td>\n","    </tr>\n","    <tr>\n","      <th>2</th>\n","      <td>False</td>\n","      <td>{'id': 119050, 'name': 'Grumpy Old Men Collect...</td>\n","      <td>0</td>\n","      <td>[{'id': 10749, 'name': 'Romance'}, {'id': 35, ...</td>\n","      <td>NaN</td>\n","      <td>15602</td>\n","      <td>tt0113228</td>\n","      <td>en</td>\n","      <td>Grumpier Old Men</td>\n","      <td>A family wedding reignites the ancient feud be...</td>\n","      <td>11.7129</td>\n","      <td>/6ksm1sjKMFLbO7UY2i6G1ju9SML.jpg</td>\n","      <td>[{'name': 'Warner Bros.', 'id': 6194}, {'name'...</td>\n","      <td>[{'iso_3166_1': 'US', 'name': 'United States o...</td>\n","      <td>1995-12-22</td>\n","      <td>0.0</td>\n","      <td>101.0</td>\n","      <td>[{'iso_639_1': 'en', 'name': 'English'}]</td>\n","      <td>Released</td>\n","      <td>Still Yelling. Still Fighting. Still Ready for...</td>\n","      <td>Grumpier Old Men</td>\n","      <td>False</td>\n","      <td>6.5</td>\n","      <td>92.0</td>\n","    </tr>\n","    <tr>\n","      <th>3</th>\n","      <td>False</td>\n","      <td>NaN</td>\n","      <td>16000000</td>\n","      <td>[{'id': 35, 'name': 'Comedy'}, {'id': 18, 'nam...</td>\n","      <td>NaN</td>\n","      <td>31357</td>\n","      <td>tt0114885</td>\n","      <td>en</td>\n","      <td>Waiting to Exhale</td>\n","      <td>Cheated on, mistreated and stepped on, the wom...</td>\n","      <td>3.859495</td>\n","      <td>/16XOMpEaLWkrcPqSQqhTmeJuqQl.jpg</td>\n","      <td>[{'name': 'Twentieth Century Fox Film Corporat...</td>\n","      <td>[{'iso_3166_1': 'US', 'name': 'United States o...</td>\n","      <td>1995-12-22</td>\n","      <td>81452156.0</td>\n","      <td>127.0</td>\n","      <td>[{'iso_639_1': 'en', 'name': 'English'}]</td>\n","      <td>Released</td>\n","      <td>Friends are the people who let you be yourself...</td>\n","      <td>Waiting to Exhale</td>\n","      <td>False</td>\n","      <td>6.1</td>\n","      <td>34.0</td>\n","    </tr>\n","    <tr>\n","      <th>4</th>\n","      <td>False</td>\n","      <td>{'id': 96871, 'name': 'Father of the Bride Col...</td>\n","      <td>0</td>\n","      <td>[{'id': 35, 'name': 'Comedy'}]</td>\n","      <td>NaN</td>\n","      <td>11862</td>\n","      <td>tt0113041</td>\n","      <td>en</td>\n","      <td>Father of the Bride Part II</td>\n","      <td>Just when George Banks has recovered from his ...</td>\n","      <td>8.387519</td>\n","      <td>/e64sOI48hQXyru7naBFyssKFxVd.jpg</td>\n","      <td>[{'name': 'Sandollar Productions', 'id': 5842}...</td>\n","      <td>[{'iso_3166_1': 'US', 'name': 'United States o...</td>\n","      <td>1995-02-10</td>\n","      <td>76578911.0</td>\n","      <td>106.0</td>\n","      <td>[{'iso_639_1': 'en', 'name': 'English'}]</td>\n","      <td>Released</td>\n","      <td>Just When His World Is Back To Normal... He's ...</td>\n","      <td>Father of the Bride Part II</td>\n","      <td>False</td>\n","      <td>5.7</td>\n","      <td>173.0</td>\n","    </tr>\n","  </tbody>\n","</table>\n","</div>"],"text/plain":["   adult  ... vote_count\n","0  False  ...     5415.0\n","1  False  ...     2413.0\n","2  False  ...       92.0\n","3  False  ...       34.0\n","4  False  ...      173.0\n","\n","[5 rows x 24 columns]"]},"metadata":{"tags":[]},"execution_count":13}]},{"cell_type":"markdown","metadata":{"id":"ukDqvku27QXD","colab_type":"text"},"source":["#### Get all genres from genres.csv"]},{"cell_type":"code","metadata":{"id":"ox1GDhfval5J","colab_type":"code","outputId":"052b58eb-fabe-4aba-b506-68efc3e2be54","executionInfo":{"status":"ok","timestamp":1584577214015,"user_tz":420,"elapsed":574,"user":{"displayName":"Rupal Totale","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14GgNnZACG8nsOCQJw12ujN1wTFuaoxhbRCW36hT9aoo=s64","userId":"09681754785651351739"}},"colab":{"base_uri":"https://localhost:8080/","height":340}},"source":["df_genres = pd.read_csv(data_dir + \"genres.csv\").drop(columns=[\"Unnamed: 0\"])\n","all_genres = list(df_genres[\"name\"])\n","all_genres"],"execution_count":0,"outputs":[{"output_type":"execute_result","data":{"text/plain":["['Action',\n"," 'Adventure',\n"," 'Animation',\n"," 'Comedy',\n"," 'Crime',\n"," 'Documentary',\n"," 'Drama',\n"," 'Family',\n"," 'Fantasy',\n"," 'History',\n"," 'Horror',\n"," 'Music',\n"," 'Mystery',\n"," 'Romance',\n"," 'Science Fiction',\n"," 'TV Movie',\n"," 'Thriller',\n"," 'War',\n"," 'Western']"]},"metadata":{"tags":[]},"execution_count":9}]},{"cell_type":"markdown","metadata":{"id":"QNGG1z697V8H","colab_type":"text"},"source":["#### Clean df_meta\n","The code cell below creates new columns in `df_meta` for each genre. "]},{"cell_type":"code","metadata":{"id":"9xM8SyhUdjdq","colab_type":"code","outputId":"46598c72-4b66-4af7-ca99-113ec1ad460d","executionInfo":{"status":"ok","timestamp":1584515592421,"user_tz":420,"elapsed":98370,"user":{"displayName":"Rupal Totale","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14GgNnZACG8nsOCQJw12ujN1wTFuaoxhbRCW36hT9aoo=s64","userId":"09681754785651351739"}},"colab":{"base_uri":"https://localhost:8080/","height":187}},"source":["for genre in all_genres:\n","  df_meta[genre] = 0\n","print(\"All columns of df_meta_clean\", df_meta.columns)"],"execution_count":0,"outputs":[{"output_type":"stream","text":["All columns of df_meta_clean Index(['adult', 'belongs_to_collection', 'budget', 'genres', 'homepage', 'id',\n","       'imdb_id', 'original_language', 'original_title', 'overview',\n","       'popularity', 'poster_path', 'production_companies',\n","       'production_countries', 'release_date', 'revenue', 'runtime',\n","       'spoken_languages', 'status', 'tagline', 'title', 'video',\n","       'vote_average', 'vote_count', 'Animation', 'Horror', 'Mystery',\n","       'Fantasy', 'Romance', 'TV Movie', 'Family', 'Documentary', 'Western',\n","       'Science Fiction', 'Foreign', 'Crime', 'Adventure', 'Thriller',\n","       'Comedy', 'War', 'Drama', 'History', 'Action', 'Music'],\n","      dtype='object')\n"],"name":"stdout"}]},{"cell_type":"markdown","metadata":{"id":"vEk8vw467_fF","colab_type":"text"},"source":["The code cell below cleans each column of df_meta_clean as described in the comment block. "]},{"cell_type":"code","metadata":{"id":"lkzrY2wiW8KI","colab_type":"code","outputId":"a618931e-62a4-4bfd-f8df-85caf1bad9c8","executionInfo":{"status":"ok","timestamp":1584518228871,"user_tz":420,"elapsed":5244,"user":{"displayName":"Rupal Totale","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14GgNnZACG8nsOCQJw12ujN1wTFuaoxhbRCW36hT9aoo=s64","userId":"09681754785651351739"}},"colab":{"base_uri":"https://localhost:8080/","height":683}},"source":["\"\"\"\n","Clean columns:\n","  belongs_to_collection ==> collection.name | NaN\n","  production_companies ==> space seperated, underscored instead of space for \n","    distinct companies. \n","  Genres ==> Seperate columns\n","    If movie is of the genre type, column value is 1, else 0.\n","\n","Keep columns: budget, id, original_title,\toverview,\tpopularity, \n","    production_companies, revenue, runtime, tagline, vote_average, vote_count,\n","    [genres]\n","\"\"\"\n","def clean_meta(obs):\n","  # Genre columns\n","  genres = eval(obs[\"genres\"])\n","  for genre in genres:\n","    obs[genre[\"name\"]] = 1\n","  # Production companies\n","  production_companies = \"\"\n","  for company in eval(obs[\"production_companies\"]):\n","    production_companies += company[\"name\"].replace(\" \", \"_\") + \" \"\n","  obs[\"production_companies\"] = production_companies\n","  # Collections\n","  if not pd.isna(obs[\"belongs_to_collection\"]):\n","    obs[\"collection\"] = eval(\n","          obs[\"belongs_to_collection\"]\n","        )[\"name\"].replace(\" \", \"_\")\n","  return obs\n","\n","# Define what columns to keep in df_meta_clean\n","keep_columns = all_genres + [\"id\", \"original_title\", \"budget\", \n","                             \"overview\",\t\"popularity\", \"production_companies\", \n","                             \"revenue\", \"runtime\", \"tagline\", \"vote_average\", \n","                             \"vote_count\", \"collection\"]\n","\n","df_meta_clean = (\n","    df_meta[df_meta[\"id\"].isin(movie_ids_dict)]\n","      .apply(clean_meta, axis=1)\n","      # The dataset seems to have duplicate observations that need to be dropped\n","      .drop_duplicates(subset='id', keep='first')\n","      [keep_columns]\n",")\n","df_meta_clean.head()"],"execution_count":0,"outputs":[{"output_type":"execute_result","data":{"text/html":["<div>\n","<style scoped>\n","    .dataframe tbody tr th:only-of-type {\n","        vertical-align: middle;\n","    }\n","\n","    .dataframe tbody tr th {\n","        vertical-align: top;\n","    }\n","\n","    .dataframe thead th {\n","        text-align: right;\n","    }\n","</style>\n","<table border=\"1\" class=\"dataframe\">\n","  <thead>\n","    <tr style=\"text-align: right;\">\n","      <th></th>\n","      <th>Animation</th>\n","      <th>Horror</th>\n","      <th>Mystery</th>\n","      <th>Fantasy</th>\n","      <th>Romance</th>\n","      <th>TV Movie</th>\n","      <th>Family</th>\n","      <th>Documentary</th>\n","      <th>Western</th>\n","      <th>Science Fiction</th>\n","      <th>Foreign</th>\n","      <th>Crime</th>\n","      <th>Adventure</th>\n","      <th>Thriller</th>\n","      <th>Comedy</th>\n","      <th>War</th>\n","      <th>Drama</th>\n","      <th>History</th>\n","      <th>Action</th>\n","      <th>Music</th>\n","      <th>id</th>\n","      <th>original_title</th>\n","      <th>budget</th>\n","      <th>overview</th>\n","      <th>popularity</th>\n","      <th>production_companies</th>\n","      <th>revenue</th>\n","      <th>runtime</th>\n","      <th>tagline</th>\n","      <th>vote_average</th>\n","      <th>vote_count</th>\n","      <th>collection</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>0</th>\n","      <td>1</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>1</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>1</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>862</td>\n","      <td>Toy Story</td>\n","      <td>30000000</td>\n","      <td>Led by Woody, Andy's toys live happily in his ...</td>\n","      <td>21.946943</td>\n","      <td>Pixar_Animation_Studios</td>\n","      <td>373554033.0</td>\n","      <td>81.0</td>\n","      <td>NaN</td>\n","      <td>7.7</td>\n","      <td>5415.0</td>\n","      <td>Toy_Story_Collection</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>1</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>1</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>1</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>8844</td>\n","      <td>Jumanji</td>\n","      <td>65000000</td>\n","      <td>When siblings Judy and Peter discover an encha...</td>\n","      <td>17.015539</td>\n","      <td>TriStar_Pictures Teitler_Film Interscope_Commu...</td>\n","      <td>262797249.0</td>\n","      <td>104.0</td>\n","      <td>Roll the dice and unleash the excitement!</td>\n","      <td>6.9</td>\n","      <td>2413.0</td>\n","      <td>NaN</td>\n","    </tr>\n","    <tr>\n","      <th>5</th>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>1</td>\n","      <td>0</td>\n","      <td>1</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>1</td>\n","      <td>0</td>\n","      <td>1</td>\n","      <td>0</td>\n","      <td>949</td>\n","      <td>Heat</td>\n","      <td>60000000</td>\n","      <td>Obsessive master thief, Neil McCauley leads a ...</td>\n","      <td>17.924927</td>\n","      <td>Regency_Enterprises Forward_Pass Warner_Bros.</td>\n","      <td>187436818.0</td>\n","      <td>170.0</td>\n","      <td>A Los Angeles Crime Saga</td>\n","      <td>7.7</td>\n","      <td>1886.0</td>\n","      <td>NaN</td>\n","    </tr>\n","    <tr>\n","      <th>9</th>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>1</td>\n","      <td>1</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>1</td>\n","      <td>0</td>\n","      <td>710</td>\n","      <td>GoldenEye</td>\n","      <td>58000000</td>\n","      <td>James Bond must unmask the mysterious head of ...</td>\n","      <td>14.686036</td>\n","      <td>United_Artists Eon_Productions</td>\n","      <td>352194034.0</td>\n","      <td>130.0</td>\n","      <td>No limits. No fears. No substitutes.</td>\n","      <td>6.6</td>\n","      <td>1194.0</td>\n","      <td>James_Bond_Collection</td>\n","    </tr>\n","    <tr>\n","      <th>14</th>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>1</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>1</td>\n","      <td>0</td>\n","      <td>1408</td>\n","      <td>Cutthroat Island</td>\n","      <td>98000000</td>\n","      <td>Morgan Adams and her slave, William Shaw, are ...</td>\n","      <td>7.284477</td>\n","      <td>Le_Studio_Canal+ Laurence_Mark_Productions Met...</td>\n","      <td>10017322.0</td>\n","      <td>119.0</td>\n","      <td>The Course Has Been Set. There Is No Turning B...</td>\n","      <td>5.7</td>\n","      <td>137.0</td>\n","      <td>NaN</td>\n","    </tr>\n","  </tbody>\n","</table>\n","</div>"],"text/plain":["    Animation  Horror  Mystery  ...  vote_average  vote_count             collection\n","0           1       0        0  ...           7.7      5415.0   Toy_Story_Collection\n","1           0       0        0  ...           6.9      2413.0                    NaN\n","5           0       0        0  ...           7.7      1886.0                    NaN\n","9           0       0        0  ...           6.6      1194.0  James_Bond_Collection\n","14          0       0        0  ...           5.7       137.0                    NaN\n","\n","[5 rows x 32 columns]"]},"metadata":{"tags":[]},"execution_count":27}]},{"cell_type":"markdown","metadata":{"id":"TDABbpfhjDVV","colab_type":"text"},"source":["### Merge all the DataFrames into a single DataFrame, and save (as csv) to drive"]},{"cell_type":"code","metadata":{"id":"XeMAH0AfjwO6","colab_type":"code","outputId":"bb6d2216-9bf8-4722-ddb7-1bc392a4258e","executionInfo":{"status":"ok","timestamp":1584518243119,"user_tz":420,"elapsed":435,"user":{"displayName":"Rupal Totale","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14GgNnZACG8nsOCQJw12ujN1wTFuaoxhbRCW36hT9aoo=s64","userId":"09681754785651351739"}},"colab":{"base_uri":"https://localhost:8080/","height":836}},"source":["from functools import reduce\n","data_frames = [df_meta_clean, \n","               df_credits_clean, \n","               df_keywords_clean, \n","               df_ratings_clean]\n","df_merged = reduce(lambda left, right: pd.merge(left, right, on=['id'], \n","                                                how='inner'), \n","                   data_frames)\n","\n","print(\"Number of movies:\", len(df_merged))\n","print(\"Columns of merged DataFrame:\", df_merged.columns)\n","df_merged.head()"],"execution_count":0,"outputs":[{"output_type":"stream","text":["Number of movies: 7679\n","Columns of merged DataFrame: Index(['Animation', 'Horror', 'Mystery', 'Fantasy', 'Romance', 'TV Movie',\n","       'Family', 'Documentary', 'Western', 'Science Fiction', 'Foreign',\n","       'Crime', 'Adventure', 'Thriller', 'Comedy', 'War', 'Drama', 'History',\n","       'Action', 'Music', 'id', 'original_title', 'budget', 'overview',\n","       'popularity', 'production_companies', 'revenue', 'runtime', 'tagline',\n","       'vote_average', 'vote_count', 'collection', 'Composer', 'Director',\n","       'Screenplay', 'cast', 'keywords', 'rating_average', 'userId_who_like'],\n","      dtype='object')\n"],"name":"stdout"},{"output_type":"execute_result","data":{"text/html":["<div>\n","<style scoped>\n","    .dataframe tbody tr th:only-of-type {\n","        vertical-align: middle;\n","    }\n","\n","    .dataframe tbody tr th {\n","        vertical-align: top;\n","    }\n","\n","    .dataframe thead th {\n","        text-align: right;\n","    }\n","</style>\n","<table border=\"1\" class=\"dataframe\">\n","  <thead>\n","    <tr style=\"text-align: right;\">\n","      <th></th>\n","      <th>Animation</th>\n","      <th>Horror</th>\n","      <th>Mystery</th>\n","      <th>Fantasy</th>\n","      <th>Romance</th>\n","      <th>TV Movie</th>\n","      <th>Family</th>\n","      <th>Documentary</th>\n","      <th>Western</th>\n","      <th>Science Fiction</th>\n","      <th>Foreign</th>\n","      <th>Crime</th>\n","      <th>Adventure</th>\n","      <th>Thriller</th>\n","      <th>Comedy</th>\n","      <th>War</th>\n","      <th>Drama</th>\n","      <th>History</th>\n","      <th>Action</th>\n","      <th>Music</th>\n","      <th>id</th>\n","      <th>original_title</th>\n","      <th>budget</th>\n","      <th>overview</th>\n","      <th>popularity</th>\n","      <th>production_companies</th>\n","      <th>revenue</th>\n","      <th>runtime</th>\n","      <th>tagline</th>\n","      <th>vote_average</th>\n","      <th>vote_count</th>\n","      <th>collection</th>\n","      <th>Composer</th>\n","      <th>Director</th>\n","      <th>Screenplay</th>\n","      <th>cast</th>\n","      <th>keywords</th>\n","      <th>rating_average</th>\n","      <th>userId_who_like</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>0</th>\n","      <td>1</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>1</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>1</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>862</td>\n","      <td>Toy Story</td>\n","      <td>30000000</td>\n","      <td>Led by Woody, Andy's toys live happily in his ...</td>\n","      <td>21.946943</td>\n","      <td>Pixar_Animation_Studios</td>\n","      <td>373554033.0</td>\n","      <td>81.0</td>\n","      <td>NaN</td>\n","      <td>7.7</td>\n","      <td>5415.0</td>\n","      <td>Toy_Story_Collection</td>\n","      <td>NaN</td>\n","      <td>John_Lasseter</td>\n","      <td>Alec_Sokolow</td>\n","      <td>Tom_Hanks Tim_Allen Don_Rickles Jim_Varney Wal...</td>\n","      <td>jealousy toy boy friendship friends rivalry bo...</td>\n","      <td>3.598930</td>\n","      <td>2103 6177 6525 8659 9328 9682 11214 13839 1523...</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>1</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>1</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>1</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>8844</td>\n","      <td>Jumanji</td>\n","      <td>65000000</td>\n","      <td>When siblings Judy and Peter discover an encha...</td>\n","      <td>17.015539</td>\n","      <td>TriStar_Pictures Teitler_Film Interscope_Commu...</td>\n","      <td>262797249.0</td>\n","      <td>104.0</td>\n","      <td>Roll the dice and unleash the excitement!</td>\n","      <td>6.9</td>\n","      <td>2413.0</td>\n","      <td>NaN</td>\n","      <td>James_Horner</td>\n","      <td>Joe_Johnston</td>\n","      <td>Jim_Strain</td>\n","      <td>Robin_Williams Jonathan_Hyde Kirsten_Dunst Bra...</td>\n","      <td>board_game disappearance based_on_children's_b...</td>\n","      <td>3.760163</td>\n","      <td>7016 7144 8659 9547 13399 17808 24266 25209 26...</td>\n","    </tr>\n","    <tr>\n","      <th>2</th>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>1</td>\n","      <td>0</td>\n","      <td>1</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>1</td>\n","      <td>0</td>\n","      <td>1</td>\n","      <td>0</td>\n","      <td>949</td>\n","      <td>Heat</td>\n","      <td>60000000</td>\n","      <td>Obsessive master thief, Neil McCauley leads a ...</td>\n","      <td>17.924927</td>\n","      <td>Regency_Enterprises Forward_Pass Warner_Bros.</td>\n","      <td>187436818.0</td>\n","      <td>170.0</td>\n","      <td>A Los Angeles Crime Saga</td>\n","      <td>7.7</td>\n","      <td>1886.0</td>\n","      <td>NaN</td>\n","      <td>Elliot_Goldenthal</td>\n","      <td>Michael_Mann</td>\n","      <td>Michael_Mann</td>\n","      <td>Al_Pacino Robert_De_Niro Val_Kilmer Jon_Voight...</td>\n","      <td>robbery detective bank obsession chase shootin...</td>\n","      <td>3.905544</td>\n","      <td>174 346 362 384 523 557 609 624 773 825 959 11...</td>\n","    </tr>\n","    <tr>\n","      <th>3</th>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>1</td>\n","      <td>1</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>1</td>\n","      <td>0</td>\n","      <td>710</td>\n","      <td>GoldenEye</td>\n","      <td>58000000</td>\n","      <td>James Bond must unmask the mysterious head of ...</td>\n","      <td>14.686036</td>\n","      <td>United_Artists Eon_Productions</td>\n","      <td>352194034.0</td>\n","      <td>130.0</td>\n","      <td>No limits. No fears. No substitutes.</td>\n","      <td>6.6</td>\n","      <td>1194.0</td>\n","      <td>James_Bond_Collection</td>\n","      <td>Eric_Serra</td>\n","      <td>Martin_Campbell</td>\n","      <td>Bruce_Feirstein</td>\n","      <td>Pierce_Brosnan Sean_Bean Izabella_Scorupco Fam...</td>\n","      <td>cuba falsely_accused secret_identity computer_...</td>\n","      <td>2.740334</td>\n","      <td>2073 2709 2967 4241 4389 4916 5463 5811 5980 8...</td>\n","    </tr>\n","    <tr>\n","      <th>4</th>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>1</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>1</td>\n","      <td>0</td>\n","      <td>1408</td>\n","      <td>Cutthroat Island</td>\n","      <td>98000000</td>\n","      <td>Morgan Adams and her slave, William Shaw, are ...</td>\n","      <td>7.284477</td>\n","      <td>Le_Studio_Canal+ Laurence_Mark_Productions Met...</td>\n","      <td>10017322.0</td>\n","      <td>119.0</td>\n","      <td>The Course Has Been Set. There Is No Turning B...</td>\n","      <td>5.7</td>\n","      <td>137.0</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>Renny_Harlin</td>\n","      <td>Robert_King</td>\n","      <td>Geena_Davis Matthew_Modine Frank_Langella Maur...</td>\n","      <td>exotic_island treasure map ship scalp pirate</td>\n","      <td>3.710181</td>\n","      <td>15 65 68 98 115 151 153 193 231 288 326 340 34...</td>\n","    </tr>\n","  </tbody>\n","</table>\n","</div>"],"text/plain":["   Animation  ...                                    userId_who_like\n","0          1  ...  2103 6177 6525 8659 9328 9682 11214 13839 1523...\n","1          0  ...  7016 7144 8659 9547 13399 17808 24266 25209 26...\n","2          0  ...  174 346 362 384 523 557 609 624 773 825 959 11...\n","3          0  ...  2073 2709 2967 4241 4389 4916 5463 5811 5980 8...\n","4          0  ...  15 65 68 98 115 151 153 193 231 288 326 340 34...\n","\n","[5 rows x 39 columns]"]},"metadata":{"tags":[]},"execution_count":28}]},{"cell_type":"markdown","metadata":{"id":"KlJPw5sX97I5","colab_type":"text"},"source":["#### Convert merged data to CSV and save to google drive"]},{"cell_type":"code","metadata":{"id":"2jJaV_2Z9sRR","colab_type":"code","outputId":"220ac098-f2f8-4e8f-df1d-7e475522b200","executionInfo":{"status":"ok","timestamp":1584518277990,"user_tz":420,"elapsed":3739,"user":{"displayName":"Rupal Totale","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14GgNnZACG8nsOCQJw12ujN1wTFuaoxhbRCW36hT9aoo=s64","userId":"09681754785651351739"}},"colab":{"base_uri":"https://localhost:8080/","height":34}},"source":["df_merged.to_csv(\"cleaned_dataframe.csv\")\n","!ls"],"execution_count":0,"outputs":[{"output_type":"stream","text":["cleaned_dataframe.csv  drive  sample_data\n"],"name":"stdout"}]},{"cell_type":"code","metadata":{"id":"zJmCyKpGpzpc","colab_type":"code","colab":{}},"source":["!cp -r cleaned_dataframe.csv \"drive/My Drive/301 Project/Data\""],"execution_count":0,"outputs":[]}]}