notebooks/1. Data Wrangling.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Importing Libraries and Data Loading"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import ast"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\ASUS X515\\AppData\\Local\\Temp\\ipykernel_15092\\648835311.py:1: DtypeWarning: Columns (10) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  df_movies = pd.read_csv('../raw_data/movies_dataset.csv')\n"
     ]
    }
   ],
   "source": [
    "df_movies = pd.read_csv('../raw_data/movies_dataset.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_credits = pd.read_csv('../raw_data/credits.csv')"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 1. Movies Dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 198,
   "metadata": {},
   "outputs": [
    {
     "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>...</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>...</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>...</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",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   adult                              belongs_to_collection    budget  \\\n",
       "0  False  {'id': 10194, 'name': 'Toy Story Collection', ...  30000000   \n",
       "1  False                                                NaN  65000000   \n",
       "\n",
       "                                              genres  \\\n",
       "0  [{'id': 16, 'name': 'Animation'}, {'id': 35, '...   \n",
       "1  [{'id': 12, 'name': 'Adventure'}, {'id': 14, '...   \n",
       "\n",
       "                               homepage    id    imdb_id original_language  \\\n",
       "0  http://toystory.disney.com/toy-story   862  tt0114709                en   \n",
       "1                                   NaN  8844  tt0113497                en   \n",
       "\n",
       "  original_title                                           overview  ...  \\\n",
       "0      Toy Story  Led by Woody, Andy's toys live happily in his ...  ...   \n",
       "1        Jumanji  When siblings Judy and Peter discover an encha...  ...   \n",
       "\n",
       "  release_date      revenue runtime  \\\n",
       "0   1995-10-30  373554033.0    81.0   \n",
       "1   1995-12-15  262797249.0   104.0   \n",
       "\n",
       "                                    spoken_languages    status  \\\n",
       "0           [{'iso_639_1': 'en', 'name': 'English'}]  Released   \n",
       "1  [{'iso_639_1': 'en', 'name': 'English'}, {'iso...  Released   \n",
       "\n",
       "                                     tagline      title  video vote_average  \\\n",
       "0                                        NaN  Toy Story  False          7.7   \n",
       "1  Roll the dice and unleash the excitement!    Jumanji  False          6.9   \n",
       "\n",
       "  vote_count  \n",
       "0     5415.0  \n",
       "1     2413.0  \n",
       "\n",
       "[2 rows x 24 columns]"
      ]
     },
     "execution_count": 198,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_movies.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "adult                     object\n",
       "belongs_to_collection     object\n",
       "budget                    object\n",
       "genres                    object\n",
       "homepage                  object\n",
       "id                        object\n",
       "imdb_id                   object\n",
       "original_language         object\n",
       "original_title            object\n",
       "overview                  object\n",
       "popularity                object\n",
       "poster_path               object\n",
       "production_companies      object\n",
       "production_countries      object\n",
       "release_date              object\n",
       "revenue                  float64\n",
       "runtime                  float64\n",
       "spoken_languages          object\n",
       "status                    object\n",
       "tagline                   object\n",
       "title                     object\n",
       "video                     object\n",
       "vote_average             float64\n",
       "vote_count               float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_movies.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 199,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(45466, 24)"
      ]
     },
     "execution_count": 199,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_movies.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 200,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "adult                    0.000000\n",
       "belongs_to_collection    0.901157\n",
       "budget                   0.000000\n",
       "genres                   0.000000\n",
       "homepage                 0.828839\n",
       "id                       0.000000\n",
       "imdb_id                  0.000374\n",
       "original_language        0.000242\n",
       "original_title           0.000000\n",
       "overview                 0.020983\n",
       "popularity               0.000110\n",
       "poster_path              0.008490\n",
       "production_companies     0.000066\n",
       "production_countries     0.000066\n",
       "release_date             0.001914\n",
       "revenue                  0.000132\n",
       "runtime                  0.005785\n",
       "spoken_languages         0.000132\n",
       "status                   0.001914\n",
       "tagline                  0.551049\n",
       "title                    0.000132\n",
       "video                    0.000132\n",
       "vote_average             0.000132\n",
       "vote_count               0.000132\n",
       "dtype: float64"
      ]
     },
     "execution_count": 200,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_movies.isna().mean()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## A. Transforming Columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "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>...</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>13375</th>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>[{'id': 53, 'name': 'Thriller'}, {'id': 9648, ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>141971</td>\n",
       "      <td>tt1180333</td>\n",
       "      <td>fi</td>\n",
       "      <td>Blackout</td>\n",
       "      <td>Recovering from a nail gun shot to the head an...</td>\n",
       "      <td>...</td>\n",
       "      <td>2008-12-26</td>\n",
       "      <td>0.0</td>\n",
       "      <td>108.0</td>\n",
       "      <td>[{'iso_639_1': 'fi', 'name': 'suomi'}]</td>\n",
       "      <td>Released</td>\n",
       "      <td>Which one is the first to return - memory or t...</td>\n",
       "      <td>Blackout</td>\n",
       "      <td>False</td>\n",
       "      <td>6.7</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16764</th>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>[{'id': 53, 'name': 'Thriller'}, {'id': 9648, ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>141971</td>\n",
       "      <td>tt1180333</td>\n",
       "      <td>fi</td>\n",
       "      <td>Blackout</td>\n",
       "      <td>Recovering from a nail gun shot to the head an...</td>\n",
       "      <td>...</td>\n",
       "      <td>2008-12-26</td>\n",
       "      <td>0.0</td>\n",
       "      <td>108.0</td>\n",
       "      <td>[{'iso_639_1': 'fi', 'name': 'suomi'}]</td>\n",
       "      <td>Released</td>\n",
       "      <td>Which one is the first to return - memory or t...</td>\n",
       "      <td>Blackout</td>\n",
       "      <td>False</td>\n",
       "      <td>6.7</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23044</th>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>[{'id': 18, 'name': 'Drama'}]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>25541</td>\n",
       "      <td>tt1327820</td>\n",
       "      <td>da</td>\n",
       "      <td>Broderskab</td>\n",
       "      <td>Former Danish servicemen Lars and Jimmy are th...</td>\n",
       "      <td>...</td>\n",
       "      <td>2009-10-21</td>\n",
       "      <td>0.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>[{'iso_639_1': 'da', 'name': 'Dansk'}]</td>\n",
       "      <td>Released</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Brotherhood</td>\n",
       "      <td>False</td>\n",
       "      <td>7.1</td>\n",
       "      <td>21.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40040</th>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>980000</td>\n",
       "      <td>[{'id': 18, 'name': 'Drama'}, {'id': 14, 'name...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>298721</td>\n",
       "      <td>tt2818654</td>\n",
       "      <td>th</td>\n",
       "      <td>รักที่ขอนแก่น</td>\n",
       "      <td>In a hospital, ten soldiers are being treated ...</td>\n",
       "      <td>...</td>\n",
       "      <td>2015-09-02</td>\n",
       "      <td>0.0</td>\n",
       "      <td>122.0</td>\n",
       "      <td>[{'iso_639_1': 'en', 'name': 'English'}, {'iso...</td>\n",
       "      <td>Released</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Cemetery of Splendour</td>\n",
       "      <td>False</td>\n",
       "      <td>4.4</td>\n",
       "      <td>50.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22151</th>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>[{'id': 28, 'name': 'Action'}, {'id': 27, 'nam...</td>\n",
       "      <td>http://www.daysofdarknessthemovie.com/</td>\n",
       "      <td>18440</td>\n",
       "      <td>tt0499456</td>\n",
       "      <td>en</td>\n",
       "      <td>Days of Darkness</td>\n",
       "      <td>When a comet strikes Earth and kicks up a clou...</td>\n",
       "      <td>...</td>\n",
       "      <td>2007-01-01</td>\n",
       "      <td>0.0</td>\n",
       "      <td>89.0</td>\n",
       "      <td>[{'iso_639_1': 'en', 'name': 'English'}]</td>\n",
       "      <td>Released</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Days of Darkness</td>\n",
       "      <td>False</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24844</th>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>[{'id': 35, 'name': 'Comedy'}, {'id': 18, 'nam...</td>\n",
       "      <td>http://www.dealthemovie.com/</td>\n",
       "      <td>11115</td>\n",
       "      <td>tt0446676</td>\n",
       "      <td>en</td>\n",
       "      <td>Deal</td>\n",
       "      <td>As an ex-gambler teaches a hot-shot college ki...</td>\n",
       "      <td>...</td>\n",
       "      <td>2008-01-29</td>\n",
       "      <td>0.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>[{'iso_639_1': 'en', 'name': 'English'}]</td>\n",
       "      <td>Released</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Deal</td>\n",
       "      <td>False</td>\n",
       "      <td>5.2</td>\n",
       "      <td>22.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29374</th>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>[{'id': 18, 'name': 'Drama'}, {'id': 10769, 'n...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>42495</td>\n",
       "      <td>tt0067306</td>\n",
       "      <td>en</td>\n",
       "      <td>King Lear</td>\n",
       "      <td>King Lear, old and tired, divides his kingdom ...</td>\n",
       "      <td>...</td>\n",
       "      <td>1971-02-04</td>\n",
       "      <td>0.0</td>\n",
       "      <td>137.0</td>\n",
       "      <td>[{'iso_639_1': 'en', 'name': 'English'}]</td>\n",
       "      <td>Rumored</td>\n",
       "      <td>NaN</td>\n",
       "      <td>King Lear</td>\n",
       "      <td>False</td>\n",
       "      <td>8.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9165</th>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>[{'id': 80, 'name': 'Crime'}, {'id': 18, 'name...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5511</td>\n",
       "      <td>tt0062229</td>\n",
       "      <td>fr</td>\n",
       "      <td>Le Samouraï</td>\n",
       "      <td>Hitman Jef Costello is a perfectionist who alw...</td>\n",
       "      <td>...</td>\n",
       "      <td>1967-10-25</td>\n",
       "      <td>39481.0</td>\n",
       "      <td>105.0</td>\n",
       "      <td>[{'iso_639_1': 'fr', 'name': 'Français'}]</td>\n",
       "      <td>Released</td>\n",
       "      <td>There is no solitude greater than that of the ...</td>\n",
       "      <td>Le Samouraï</td>\n",
       "      <td>False</td>\n",
       "      <td>7.9</td>\n",
       "      <td>187.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28860</th>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>[{'id': 18, 'name': 'Drama'}, {'id': 35, 'name...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>168538</td>\n",
       "      <td>tt0084387</td>\n",
       "      <td>en</td>\n",
       "      <td>Nana</td>\n",
       "      <td>In Zola's Paris, an ingenue arrives at a tony ...</td>\n",
       "      <td>...</td>\n",
       "      <td>1983-06-13</td>\n",
       "      <td>0.0</td>\n",
       "      <td>92.0</td>\n",
       "      <td>[]</td>\n",
       "      <td>Released</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Nana, the True Key of Pleasure</td>\n",
       "      <td>False</td>\n",
       "      <td>4.7</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21854</th>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>[{'id': 18, 'name': 'Drama'}, {'id': 878, 'nam...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>152795</td>\n",
       "      <td>tt1821641</td>\n",
       "      <td>en</td>\n",
       "      <td>The Congress</td>\n",
       "      <td>More than two decades after catapulting to sta...</td>\n",
       "      <td>...</td>\n",
       "      <td>2013-05-16</td>\n",
       "      <td>455815.0</td>\n",
       "      <td>122.0</td>\n",
       "      <td>[{'iso_639_1': 'en', 'name': 'English'}]</td>\n",
       "      <td>Released</td>\n",
       "      <td>NaN</td>\n",
       "      <td>The Congress</td>\n",
       "      <td>False</td>\n",
       "      <td>6.4</td>\n",
       "      <td>165.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1465</th>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>[{'id': 18, 'name': 'Drama'}, {'id': 10749, 'n...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>105045</td>\n",
       "      <td>tt0111613</td>\n",
       "      <td>de</td>\n",
       "      <td>Das Versprechen</td>\n",
       "      <td>East-Berlin, 1961, shortly after the erection ...</td>\n",
       "      <td>...</td>\n",
       "      <td>1995-02-16</td>\n",
       "      <td>0.0</td>\n",
       "      <td>115.0</td>\n",
       "      <td>[{'iso_639_1': 'de', 'name': 'Deutsch'}]</td>\n",
       "      <td>Released</td>\n",
       "      <td>A love, a hope, a wall.</td>\n",
       "      <td>The Promise</td>\n",
       "      <td>False</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21165</th>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>[{'id': 14, 'name': 'Fantasy'}, {'id': 18, 'na...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>119916</td>\n",
       "      <td>tt0080000</td>\n",
       "      <td>en</td>\n",
       "      <td>The Tempest</td>\n",
       "      <td>Prospero, the true Duke of Milan is now living...</td>\n",
       "      <td>...</td>\n",
       "      <td>1980-02-27</td>\n",
       "      <td>0.0</td>\n",
       "      <td>123.0</td>\n",
       "      <td>[]</td>\n",
       "      <td>Released</td>\n",
       "      <td>NaN</td>\n",
       "      <td>The Tempest</td>\n",
       "      <td>False</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9327</th>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>[{'id': 12, 'name': 'Adventure'}, {'id': 16, '...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>23305</td>\n",
       "      <td>tt0295682</td>\n",
       "      <td>en</td>\n",
       "      <td>The Warrior</td>\n",
       "      <td>In feudal India, a warrior (Khan) who renounce...</td>\n",
       "      <td>...</td>\n",
       "      <td>2001-09-23</td>\n",
       "      <td>0.0</td>\n",
       "      <td>86.0</td>\n",
       "      <td>[{'iso_639_1': 'hi', 'name': 'हिन्दी'}]</td>\n",
       "      <td>Released</td>\n",
       "      <td>NaN</td>\n",
       "      <td>The Warrior</td>\n",
       "      <td>False</td>\n",
       "      <td>6.3</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>13 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       adult belongs_to_collection  budget  \\\n",
       "13375  False                   NaN       0   \n",
       "16764  False                   NaN       0   \n",
       "23044  False                   NaN       0   \n",
       "40040  False                   NaN  980000   \n",
       "22151  False                   NaN       0   \n",
       "24844  False                   NaN       0   \n",
       "29374  False                   NaN       0   \n",
       "9165   False                   NaN       0   \n",
       "28860  False                   NaN       0   \n",
       "21854  False                   NaN       0   \n",
       "1465   False                   NaN       0   \n",
       "21165  False                   NaN       0   \n",
       "9327   False                   NaN       0   \n",
       "\n",
       "                                                  genres  \\\n",
       "13375  [{'id': 53, 'name': 'Thriller'}, {'id': 9648, ...   \n",
       "16764  [{'id': 53, 'name': 'Thriller'}, {'id': 9648, ...   \n",
       "23044                      [{'id': 18, 'name': 'Drama'}]   \n",
       "40040  [{'id': 18, 'name': 'Drama'}, {'id': 14, 'name...   \n",
       "22151  [{'id': 28, 'name': 'Action'}, {'id': 27, 'nam...   \n",
       "24844  [{'id': 35, 'name': 'Comedy'}, {'id': 18, 'nam...   \n",
       "29374  [{'id': 18, 'name': 'Drama'}, {'id': 10769, 'n...   \n",
       "9165   [{'id': 80, 'name': 'Crime'}, {'id': 18, 'name...   \n",
       "28860  [{'id': 18, 'name': 'Drama'}, {'id': 35, 'name...   \n",
       "21854  [{'id': 18, 'name': 'Drama'}, {'id': 878, 'nam...   \n",
       "1465   [{'id': 18, 'name': 'Drama'}, {'id': 10749, 'n...   \n",
       "21165  [{'id': 14, 'name': 'Fantasy'}, {'id': 18, 'na...   \n",
       "9327   [{'id': 12, 'name': 'Adventure'}, {'id': 16, '...   \n",
       "\n",
       "                                     homepage      id    imdb_id  \\\n",
       "13375                                     NaN  141971  tt1180333   \n",
       "16764                                     NaN  141971  tt1180333   \n",
       "23044                                     NaN   25541  tt1327820   \n",
       "40040                                     NaN  298721  tt2818654   \n",
       "22151  http://www.daysofdarknessthemovie.com/   18440  tt0499456   \n",
       "24844            http://www.dealthemovie.com/   11115  tt0446676   \n",
       "29374                                     NaN   42495  tt0067306   \n",
       "9165                                      NaN    5511  tt0062229   \n",
       "28860                                     NaN  168538  tt0084387   \n",
       "21854                                     NaN  152795  tt1821641   \n",
       "1465                                      NaN  105045  tt0111613   \n",
       "21165                                     NaN  119916  tt0080000   \n",
       "9327                                      NaN   23305  tt0295682   \n",
       "\n",
       "      original_language    original_title  \\\n",
       "13375                fi          Blackout   \n",
       "16764                fi          Blackout   \n",
       "23044                da        Broderskab   \n",
       "40040                th     รักที่ขอนแก่น   \n",
       "22151                en  Days of Darkness   \n",
       "24844                en              Deal   \n",
       "29374                en         King Lear   \n",
       "9165                 fr       Le Samouraï   \n",
       "28860                en              Nana   \n",
       "21854                en      The Congress   \n",
       "1465                 de   Das Versprechen   \n",
       "21165                en       The Tempest   \n",
       "9327                 en       The Warrior   \n",
       "\n",
       "                                                overview  ... release_date  \\\n",
       "13375  Recovering from a nail gun shot to the head an...  ...   2008-12-26   \n",
       "16764  Recovering from a nail gun shot to the head an...  ...   2008-12-26   \n",
       "23044  Former Danish servicemen Lars and Jimmy are th...  ...   2009-10-21   \n",
       "40040  In a hospital, ten soldiers are being treated ...  ...   2015-09-02   \n",
       "22151  When a comet strikes Earth and kicks up a clou...  ...   2007-01-01   \n",
       "24844  As an ex-gambler teaches a hot-shot college ki...  ...   2008-01-29   \n",
       "29374  King Lear, old and tired, divides his kingdom ...  ...   1971-02-04   \n",
       "9165   Hitman Jef Costello is a perfectionist who alw...  ...   1967-10-25   \n",
       "28860  In Zola's Paris, an ingenue arrives at a tony ...  ...   1983-06-13   \n",
       "21854  More than two decades after catapulting to sta...  ...   2013-05-16   \n",
       "1465   East-Berlin, 1961, shortly after the erection ...  ...   1995-02-16   \n",
       "21165  Prospero, the true Duke of Milan is now living...  ...   1980-02-27   \n",
       "9327   In feudal India, a warrior (Khan) who renounce...  ...   2001-09-23   \n",
       "\n",
       "        revenue runtime                                   spoken_languages  \\\n",
       "13375       0.0   108.0             [{'iso_639_1': 'fi', 'name': 'suomi'}]   \n",
       "16764       0.0   108.0             [{'iso_639_1': 'fi', 'name': 'suomi'}]   \n",
       "23044       0.0    90.0             [{'iso_639_1': 'da', 'name': 'Dansk'}]   \n",
       "40040       0.0   122.0  [{'iso_639_1': 'en', 'name': 'English'}, {'iso...   \n",
       "22151       0.0    89.0           [{'iso_639_1': 'en', 'name': 'English'}]   \n",
       "24844       0.0    85.0           [{'iso_639_1': 'en', 'name': 'English'}]   \n",
       "29374       0.0   137.0           [{'iso_639_1': 'en', 'name': 'English'}]   \n",
       "9165    39481.0   105.0          [{'iso_639_1': 'fr', 'name': 'Français'}]   \n",
       "28860       0.0    92.0                                                 []   \n",
       "21854  455815.0   122.0           [{'iso_639_1': 'en', 'name': 'English'}]   \n",
       "1465        0.0   115.0           [{'iso_639_1': 'de', 'name': 'Deutsch'}]   \n",
       "21165       0.0   123.0                                                 []   \n",
       "9327        0.0    86.0            [{'iso_639_1': 'hi', 'name': 'हिन्दी'}]   \n",
       "\n",
       "         status                                            tagline  \\\n",
       "13375  Released  Which one is the first to return - memory or t...   \n",
       "16764  Released  Which one is the first to return - memory or t...   \n",
       "23044  Released                                                NaN   \n",
       "40040  Released                                                NaN   \n",
       "22151  Released                                                NaN   \n",
       "24844  Released                                                NaN   \n",
       "29374   Rumored                                                NaN   \n",
       "9165   Released  There is no solitude greater than that of the ...   \n",
       "28860  Released                                                NaN   \n",
       "21854  Released                                                NaN   \n",
       "1465   Released                            A love, a hope, a wall.   \n",
       "21165  Released                                                NaN   \n",
       "9327   Released                                                NaN   \n",
       "\n",
       "                                title  video vote_average vote_count  \n",
       "13375                        Blackout  False          6.7        3.0  \n",
       "16764                        Blackout  False          6.7        3.0  \n",
       "23044                     Brotherhood  False          7.1       21.0  \n",
       "40040           Cemetery of Splendour  False          4.4       50.0  \n",
       "22151                Days of Darkness  False          5.0        5.0  \n",
       "24844                            Deal  False          5.2       22.0  \n",
       "29374                       King Lear  False          8.0        3.0  \n",
       "9165                      Le Samouraï  False          7.9      187.0  \n",
       "28860  Nana, the True Key of Pleasure  False          4.7        3.0  \n",
       "21854                    The Congress  False          6.4      165.0  \n",
       "1465                      The Promise  False          5.0        1.0  \n",
       "21165                     The Tempest  False          0.0        0.0  \n",
       "9327                      The Warrior  False          6.3       15.0  \n",
       "\n",
       "[13 rows x 24 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_movies[df_movies.duplicated()].sort_values(by=\"title\") # checking duplicates to be dropped"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_movies.drop_duplicates(inplace=True) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'id': 10194,\n",
       " 'name': 'Toy Story Collection',\n",
       " 'poster_path': '/7G9915LfUQ2lVfwMEEhDsn3kT4B.jpg',\n",
       " 'backdrop_path': '/9FBwqcd9IRruEDUrTdcaafOMKUq.jpg'}"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# convert string elements in this column to dictionaries\n",
    "\n",
    "ast.literal_eval(df_movies[\"belongs_to_collection\"][0]) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# converting all elements in \"belongs_to_collection\" column to dictionaries\n",
    "\n",
    "df_movies[\"collection\"] = [x if pd.isnull(x) else ast.literal_eval(x) for x in df_movies[\"belongs_to_collection\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Toy Story Collection'"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_movies[\"collection\"][0].get(\"name\") # get the value using the key"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# new column that will replace \"belong_to_collection\" only need the atribute \"name\"  of the collection\n",
    "\n",
    "df_movies[\"collection\"] = [x.get(\"name\") if type(x) == dict else np.nan for x in df_movies[\"collection\"]] "
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The following columns are lists of dictionaries, so they will need slightly different treatment."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_movies[\"genres_list\"] = [x if pd.isnull(x) else ast.literal_eval(x) for x in df_movies[\"genres\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'id': 16, 'name': 'Animation'},\n",
       " {'id': 35, 'name': 'Comedy'},\n",
       " {'id': 10751, 'name': 'Family'}]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_movies[\"genres_list\"][0] "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Animation'"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_movies[\"genres_list\"][0][0][\"name\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# for every element in these columns, we do a comprehension list inside of a comprehension list\n",
    "# every cell will have a list with genres\n",
    "\n",
    "df_movies[\"genres_list\"] = [[genre[\"name\"] for genre in element] for element in df_movies[\"genres_list\"]]"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Repeating this process for other columns with similar structure."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# converting strings to lists\n",
    "\n",
    "df_movies[\"spoken_languages_list\"] = [x if pd.isnull(x) else ast.literal_eval(x) for x in df_movies[\"spoken_languages\"]]\n",
    "df_movies[\"production_companies_list\"] = [x if pd.isnull(x) else ast.literal_eval(x) for x in df_movies[\"production_companies\"]]\n",
    "df_movies[\"production_countries_list\"] = [x if pd.isnull(x) else ast.literal_eval(x) for x in df_movies[\"production_countries\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "# this comprehension list check first if the element is a list (list of dictionaries), if it's not a list, it will be converted to null value\n",
    "\n",
    "df_movies[\"spoken_languages_list\"] = [[language[\"iso_639_1\"] for language in element] if type(element) == list else np.nan for element in df_movies[\"spoken_languages_list\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_movies[\"production_companies_list\"] = [[company[\"name\"] for company in element] if type(element) == list else np.nan for element in df_movies[\"production_companies_list\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_movies[\"production_countries_list\"] = [[country[\"iso_3166_1\"] for country in element] if type(element) == list else np.nan for element in df_movies[\"production_countries_list\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "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>...</th>\n",
       "      <th>tagline</th>\n",
       "      <th>title</th>\n",
       "      <th>video</th>\n",
       "      <th>vote_average</th>\n",
       "      <th>vote_count</th>\n",
       "      <th>collection</th>\n",
       "      <th>genres_list</th>\n",
       "      <th>spoken_languages_list</th>\n",
       "      <th>production_companies_list</th>\n",
       "      <th>production_countries_list</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>...</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",
       "      <td>Toy Story Collection</td>\n",
       "      <td>[Animation, Comedy, Family]</td>\n",
       "      <td>[en]</td>\n",
       "      <td>[Pixar Animation Studios]</td>\n",
       "      <td>[US]</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>...</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",
       "      <td>NaN</td>\n",
       "      <td>[Adventure, Fantasy, Family]</td>\n",
       "      <td>[en, fr]</td>\n",
       "      <td>[TriStar Pictures, Teitler Film, Interscope Co...</td>\n",
       "      <td>[US]</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>...</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",
       "      <td>Grumpy Old Men Collection</td>\n",
       "      <td>[Romance, Comedy]</td>\n",
       "      <td>[en]</td>\n",
       "      <td>[Warner Bros., Lancaster Gate]</td>\n",
       "      <td>[US]</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>...</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",
       "      <td>NaN</td>\n",
       "      <td>[Comedy, Drama, Romance]</td>\n",
       "      <td>[en]</td>\n",
       "      <td>[Twentieth Century Fox Film Corporation]</td>\n",
       "      <td>[US]</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>...</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",
       "      <td>Father of the Bride Collection</td>\n",
       "      <td>[Comedy]</td>\n",
       "      <td>[en]</td>\n",
       "      <td>[Sandollar Productions, Touchstone Pictures]</td>\n",
       "      <td>[US]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45461</th>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>[{'id': 18, 'name': 'Drama'}, {'id': 10751, 'n...</td>\n",
       "      <td>http://www.imdb.com/title/tt6209470/</td>\n",
       "      <td>439050</td>\n",
       "      <td>tt6209470</td>\n",
       "      <td>fa</td>\n",
       "      <td>رگ خواب</td>\n",
       "      <td>Rising and falling between a man and woman.</td>\n",
       "      <td>...</td>\n",
       "      <td>Rising and falling between a man and woman</td>\n",
       "      <td>Subdue</td>\n",
       "      <td>False</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[Drama, Family]</td>\n",
       "      <td>[fa]</td>\n",
       "      <td>[]</td>\n",
       "      <td>[IR]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45462</th>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>[{'id': 18, 'name': 'Drama'}]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>111109</td>\n",
       "      <td>tt2028550</td>\n",
       "      <td>tl</td>\n",
       "      <td>Siglo ng Pagluluwal</td>\n",
       "      <td>An artist struggles to finish his work while a...</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Century of Birthing</td>\n",
       "      <td>False</td>\n",
       "      <td>9.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[Drama]</td>\n",
       "      <td>[tl]</td>\n",
       "      <td>[Sine Olivia]</td>\n",
       "      <td>[PH]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45463</th>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>[{'id': 28, 'name': 'Action'}, {'id': 18, 'nam...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>67758</td>\n",
       "      <td>tt0303758</td>\n",
       "      <td>en</td>\n",
       "      <td>Betrayal</td>\n",
       "      <td>When one of her hits goes wrong, a professiona...</td>\n",
       "      <td>...</td>\n",
       "      <td>A deadly game of wits.</td>\n",
       "      <td>Betrayal</td>\n",
       "      <td>False</td>\n",
       "      <td>3.8</td>\n",
       "      <td>6.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[Action, Drama, Thriller]</td>\n",
       "      <td>[en]</td>\n",
       "      <td>[American World Pictures]</td>\n",
       "      <td>[US]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45464</th>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>[]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>227506</td>\n",
       "      <td>tt0008536</td>\n",
       "      <td>en</td>\n",
       "      <td>Satana likuyushchiy</td>\n",
       "      <td>In a small town live two brothers, one a minis...</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Satan Triumphant</td>\n",
       "      <td>False</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[]</td>\n",
       "      <td>[]</td>\n",
       "      <td>[Yermoliev]</td>\n",
       "      <td>[RU]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45465</th>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>[]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>461257</td>\n",
       "      <td>tt6980792</td>\n",
       "      <td>en</td>\n",
       "      <td>Queerama</td>\n",
       "      <td>50 years after decriminalisation of homosexual...</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Queerama</td>\n",
       "      <td>False</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[]</td>\n",
       "      <td>[en]</td>\n",
       "      <td>[]</td>\n",
       "      <td>[GB]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>45453 rows × 29 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       adult                              belongs_to_collection    budget  \\\n",
       "0      False  {'id': 10194, 'name': 'Toy Story Collection', ...  30000000   \n",
       "1      False                                                NaN  65000000   \n",
       "2      False  {'id': 119050, 'name': 'Grumpy Old Men Collect...         0   \n",
       "3      False                                                NaN  16000000   \n",
       "4      False  {'id': 96871, 'name': 'Father of the Bride Col...         0   \n",
       "...      ...                                                ...       ...   \n",
       "45461  False                                                NaN         0   \n",
       "45462  False                                                NaN         0   \n",
       "45463  False                                                NaN         0   \n",
       "45464  False                                                NaN         0   \n",
       "45465  False                                                NaN         0   \n",
       "\n",
       "                                                  genres  \\\n",
       "0      [{'id': 16, 'name': 'Animation'}, {'id': 35, '...   \n",
       "1      [{'id': 12, 'name': 'Adventure'}, {'id': 14, '...   \n",
       "2      [{'id': 10749, 'name': 'Romance'}, {'id': 35, ...   \n",
       "3      [{'id': 35, 'name': 'Comedy'}, {'id': 18, 'nam...   \n",
       "4                         [{'id': 35, 'name': 'Comedy'}]   \n",
       "...                                                  ...   \n",
       "45461  [{'id': 18, 'name': 'Drama'}, {'id': 10751, 'n...   \n",
       "45462                      [{'id': 18, 'name': 'Drama'}]   \n",
       "45463  [{'id': 28, 'name': 'Action'}, {'id': 18, 'nam...   \n",
       "45464                                                 []   \n",
       "45465                                                 []   \n",
       "\n",
       "                                   homepage      id    imdb_id  \\\n",
       "0      http://toystory.disney.com/toy-story     862  tt0114709   \n",
       "1                                       NaN    8844  tt0113497   \n",
       "2                                       NaN   15602  tt0113228   \n",
       "3                                       NaN   31357  tt0114885   \n",
       "4                                       NaN   11862  tt0113041   \n",
       "...                                     ...     ...        ...   \n",
       "45461  http://www.imdb.com/title/tt6209470/  439050  tt6209470   \n",
       "45462                                   NaN  111109  tt2028550   \n",
       "45463                                   NaN   67758  tt0303758   \n",
       "45464                                   NaN  227506  tt0008536   \n",
       "45465                                   NaN  461257  tt6980792   \n",
       "\n",
       "      original_language               original_title  \\\n",
       "0                    en                    Toy Story   \n",
       "1                    en                      Jumanji   \n",
       "2                    en             Grumpier Old Men   \n",
       "3                    en            Waiting to Exhale   \n",
       "4                    en  Father of the Bride Part II   \n",
       "...                 ...                          ...   \n",
       "45461                fa                      رگ خواب   \n",
       "45462                tl          Siglo ng Pagluluwal   \n",
       "45463                en                     Betrayal   \n",
       "45464                en          Satana likuyushchiy   \n",
       "45465                en                     Queerama   \n",
       "\n",
       "                                                overview  ...  \\\n",
       "0      Led by Woody, Andy's toys live happily in his ...  ...   \n",
       "1      When siblings Judy and Peter discover an encha...  ...   \n",
       "2      A family wedding reignites the ancient feud be...  ...   \n",
       "3      Cheated on, mistreated and stepped on, the wom...  ...   \n",
       "4      Just when George Banks has recovered from his ...  ...   \n",
       "...                                                  ...  ...   \n",
       "45461        Rising and falling between a man and woman.  ...   \n",
       "45462  An artist struggles to finish his work while a...  ...   \n",
       "45463  When one of her hits goes wrong, a professiona...  ...   \n",
       "45464  In a small town live two brothers, one a minis...  ...   \n",
       "45465  50 years after decriminalisation of homosexual...  ...   \n",
       "\n",
       "                                                 tagline  \\\n",
       "0                                                    NaN   \n",
       "1              Roll the dice and unleash the excitement!   \n",
       "2      Still Yelling. Still Fighting. Still Ready for...   \n",
       "3      Friends are the people who let you be yourself...   \n",
       "4      Just When His World Is Back To Normal... He's ...   \n",
       "...                                                  ...   \n",
       "45461         Rising and falling between a man and woman   \n",
       "45462                                                NaN   \n",
       "45463                             A deadly game of wits.   \n",
       "45464                                                NaN   \n",
       "45465                                                NaN   \n",
       "\n",
       "                             title  video vote_average vote_count  \\\n",
       "0                        Toy Story  False          7.7     5415.0   \n",
       "1                          Jumanji  False          6.9     2413.0   \n",
       "2                 Grumpier Old Men  False          6.5       92.0   \n",
       "3                Waiting to Exhale  False          6.1       34.0   \n",
       "4      Father of the Bride Part II  False          5.7      173.0   \n",
       "...                            ...    ...          ...        ...   \n",
       "45461                       Subdue  False          4.0        1.0   \n",
       "45462          Century of Birthing  False          9.0        3.0   \n",
       "45463                     Betrayal  False          3.8        6.0   \n",
       "45464             Satan Triumphant  False          0.0        0.0   \n",
       "45465                     Queerama  False          0.0        0.0   \n",
       "\n",
       "                           collection                   genres_list  \\\n",
       "0                Toy Story Collection   [Animation, Comedy, Family]   \n",
       "1                                 NaN  [Adventure, Fantasy, Family]   \n",
       "2           Grumpy Old Men Collection             [Romance, Comedy]   \n",
       "3                                 NaN      [Comedy, Drama, Romance]   \n",
       "4      Father of the Bride Collection                      [Comedy]   \n",
       "...                               ...                           ...   \n",
       "45461                             NaN               [Drama, Family]   \n",
       "45462                             NaN                       [Drama]   \n",
       "45463                             NaN     [Action, Drama, Thriller]   \n",
       "45464                             NaN                            []   \n",
       "45465                             NaN                            []   \n",
       "\n",
       "      spoken_languages_list  \\\n",
       "0                      [en]   \n",
       "1                  [en, fr]   \n",
       "2                      [en]   \n",
       "3                      [en]   \n",
       "4                      [en]   \n",
       "...                     ...   \n",
       "45461                  [fa]   \n",
       "45462                  [tl]   \n",
       "45463                  [en]   \n",
       "45464                    []   \n",
       "45465                  [en]   \n",
       "\n",
       "                               production_companies_list  \\\n",
       "0                              [Pixar Animation Studios]   \n",
       "1      [TriStar Pictures, Teitler Film, Interscope Co...   \n",
       "2                         [Warner Bros., Lancaster Gate]   \n",
       "3               [Twentieth Century Fox Film Corporation]   \n",
       "4           [Sandollar Productions, Touchstone Pictures]   \n",
       "...                                                  ...   \n",
       "45461                                                 []   \n",
       "45462                                      [Sine Olivia]   \n",
       "45463                          [American World Pictures]   \n",
       "45464                                        [Yermoliev]   \n",
       "45465                                                 []   \n",
       "\n",
       "      production_countries_list  \n",
       "0                          [US]  \n",
       "1                          [US]  \n",
       "2                          [US]  \n",
       "3                          [US]  \n",
       "4                          [US]  \n",
       "...                         ...  \n",
       "45461                      [IR]  \n",
       "45462                      [PH]  \n",
       "45463                      [US]  \n",
       "45464                      [RU]  \n",
       "45465                      [GB]  \n",
       "\n",
       "[45453 rows x 29 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_movies"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we drop the columns we won't goint to use."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "discard_columns = [\"video\",\"imdb_id\",\"adult\",\"original_title\",\"poster_path\", # these columns won't be used to feed the machine learning model\n",
    "                   \"homepage\",\"belongs_to_collection\", \"tagline\", # tagline can be missleading for the recommendation model\n",
    "                   \"status\", # status is irrelevant to the purpose of this project\n",
    "                   \"genres\", \"production_companies\",  # these columns were replaced for new ones, so they won't be needed\n",
    "                   \"production_countries\",\n",
    "                    \"spoken_languages\" ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_movies.drop(columns=discard_columns, inplace=True)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## B. Handling Missing Values and Adding Columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "budget                       0.000000\n",
       "id                           0.000000\n",
       "original_language            0.000242\n",
       "overview                     0.020989\n",
       "popularity                   0.000110\n",
       "release_date                 0.001914\n",
       "revenue                      0.000132\n",
       "runtime                      0.005786\n",
       "title                        0.000132\n",
       "vote_average                 0.000132\n",
       "vote_count                   0.000132\n",
       "collection                   0.901195\n",
       "genres_list                  0.000000\n",
       "spoken_languages_list        0.000132\n",
       "production_companies_list    0.000132\n",
       "production_countries_list    0.000132\n",
       "dtype: float64"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_movies.isna().mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "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>budget</th>\n",
       "      <th>id</th>\n",
       "      <th>original_language</th>\n",
       "      <th>overview</th>\n",
       "      <th>popularity</th>\n",
       "      <th>release_date</th>\n",
       "      <th>revenue</th>\n",
       "      <th>runtime</th>\n",
       "      <th>title</th>\n",
       "      <th>vote_average</th>\n",
       "      <th>vote_count</th>\n",
       "      <th>collection</th>\n",
       "      <th>genres_list</th>\n",
       "      <th>spoken_languages_list</th>\n",
       "      <th>production_companies_list</th>\n",
       "      <th>production_countries_list</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>19730</th>\n",
       "      <td>/ff9qCepilowshEtG2GYWwzt2bs4.jpg</td>\n",
       "      <td>1997-08-20</td>\n",
       "      <td>104.0</td>\n",
       "      <td>Released</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[Carousel Productions, Vision View Entertainme...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29503</th>\n",
       "      <td>/zV8bHuSL6WXoD6FWogP9j4x80bL.jpg</td>\n",
       "      <td>2012-09-29</td>\n",
       "      <td>68.0</td>\n",
       "      <td>Released</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[Aniplex, GoHands, BROSTA TV, Mardock Scramble...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35587</th>\n",
       "      <td>/zaSf5OG7V8X8gqFvly88zDdRm46.jpg</td>\n",
       "      <td>2014-01-01</td>\n",
       "      <td>82.0</td>\n",
       "      <td>Released</td>\n",
       "      <td>Beware Of Frost Bites</td>\n",
       "      <td>22</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[Odyssey Media, Pulser Productions, Rogue Stat...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 budget          id original_language  \\\n",
       "19730  /ff9qCepilowshEtG2GYWwzt2bs4.jpg  1997-08-20             104.0   \n",
       "29503  /zV8bHuSL6WXoD6FWogP9j4x80bL.jpg  2012-09-29              68.0   \n",
       "35587  /zaSf5OG7V8X8gqFvly88zDdRm46.jpg  2014-01-01              82.0   \n",
       "\n",
       "       overview             popularity release_date  revenue  runtime title  \\\n",
       "19730  Released                    NaN            1      NaN      NaN   NaN   \n",
       "29503  Released                    NaN           12      NaN      NaN   NaN   \n",
       "35587  Released  Beware Of Frost Bites           22      NaN      NaN   NaN   \n",
       "\n",
       "       vote_average  vote_count collection  \\\n",
       "19730           NaN         NaN        NaN   \n",
       "29503           NaN         NaN        NaN   \n",
       "35587           NaN         NaN        NaN   \n",
       "\n",
       "                                             genres_list  \\\n",
       "19730  [Carousel Productions, Vision View Entertainme...   \n",
       "29503  [Aniplex, GoHands, BROSTA TV, Mardock Scramble...   \n",
       "35587  [Odyssey Media, Pulser Productions, Rogue Stat...   \n",
       "\n",
       "      spoken_languages_list production_companies_list  \\\n",
       "19730                   NaN                       NaN   \n",
       "29503                   NaN                       NaN   \n",
       "35587                   NaN                       NaN   \n",
       "\n",
       "      production_countries_list  \n",
       "19730                       NaN  \n",
       "29503                       NaN  \n",
       "35587                       NaN  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# there are non numerical values in budget column so it can not be converted to float\n",
    "# also the release dates are in the id column and the id's are in the release_date column\n",
    "# popularity and genres_list are also missplaced\n",
    "\n",
    "df_movies[df_movies['budget'].str.contains(r'\\D')]  # regex to identify numerical rows in budget"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "def convert_to_float(value): \n",
    "    try:\n",
    "        return float(value) \n",
    "    except (ValueError, TypeError): # if there is an error, it will replace the strange values for null values\n",
    "        return np.nan"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_movies[\"budget\"] = [convert_to_float(x) for x in df_movies[\"budget\"]] # now every value here is float"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_movies[\"budget\"].fillna(0, inplace=True) # there is no missing values anymore on budget and revenue\n",
    "df_movies[\"revenue\"].fillna(0, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "19730             1\n",
       "29503            12\n",
       "34940    1874-12-09\n",
       "34937    1878-06-14\n",
       "41602    1883-11-19\n",
       "            ...    \n",
       "45148           NaN\n",
       "45203           NaN\n",
       "45338           NaN\n",
       "45410           NaN\n",
       "45461           NaN\n",
       "Name: release_date, Length: 45453, dtype: object"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_movies['release_date'].sort_values() # there are values that we can not convert in date type "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "def convert_to_datetime(value): \n",
    "    try:\n",
    "        return pd.to_datetime(value, format=\"%Y-%m-%d\") \n",
    "    except (ValueError, TypeError):  \n",
    "        return np.nan # the data that can not be converted to datetime will be converted to null"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_movies['release_date'] = [convert_to_datetime(x) for x in df_movies['release_date']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop rows only if there are null values in \"release_date\" column\n",
    "# dropping this rows, also delete the table above with values missplaced, so we don't need to treat them anymore\n",
    "# there were only three values of dozens of thousands, so it won't affect further analysis\n",
    "\n",
    "df_movies.dropna(subset=['release_date'], inplace=True) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0        1995\n",
       "1        1995\n",
       "2        1995\n",
       "3        1995\n",
       "4        1995\n",
       "         ... \n",
       "45460    1991\n",
       "45462    2011\n",
       "45463    2003\n",
       "45464    1917\n",
       "45465    2017\n",
       "Name: release_year, Length: 45363, dtype: int32"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_movies[\"release_year\"] = df_movies[\"release_date\"].dt.year\n",
    "df_movies[\"release_year\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "# if the budget is zero, the return is zero\n",
    "\n",
    "df_movies[\"return\"] = np.where(df_movies[\"budget\"] != 0, df_movies[\"revenue\"] / df_movies[\"budget\"], 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "budget                           0\n",
       "id                               0\n",
       "original_language               11\n",
       "overview                       941\n",
       "popularity                       0\n",
       "release_date                     0\n",
       "revenue                          0\n",
       "runtime                        246\n",
       "title                            0\n",
       "vote_average                     0\n",
       "vote_count                       0\n",
       "collection                   40875\n",
       "genres_list                      0\n",
       "spoken_languages_list            0\n",
       "production_companies_list        0\n",
       "production_countries_list        0\n",
       "release_year                     0\n",
       "return                           0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_movies.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(45363, 18)"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_movies.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "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>budget</th>\n",
       "      <th>id</th>\n",
       "      <th>original_language</th>\n",
       "      <th>overview</th>\n",
       "      <th>popularity</th>\n",
       "      <th>release_date</th>\n",
       "      <th>revenue</th>\n",
       "      <th>runtime</th>\n",
       "      <th>title</th>\n",
       "      <th>vote_average</th>\n",
       "      <th>vote_count</th>\n",
       "      <th>collection</th>\n",
       "      <th>genres_list</th>\n",
       "      <th>spoken_languages_list</th>\n",
       "      <th>production_companies_list</th>\n",
       "      <th>production_countries_list</th>\n",
       "      <th>release_year</th>\n",
       "      <th>return</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>44821</th>\n",
       "      <td>16000000.0</td>\n",
       "      <td>10991</td>\n",
       "      <td>ja</td>\n",
       "      <td>When Molly Hale's sadness of her father's disa...</td>\n",
       "      <td>6.480376</td>\n",
       "      <td>2000-07-08</td>\n",
       "      <td>68411275.0</td>\n",
       "      <td>93.0</td>\n",
       "      <td>Pokémon: Spell of the Unknown</td>\n",
       "      <td>6.0</td>\n",
       "      <td>144.0</td>\n",
       "      <td>Pokémon Collection</td>\n",
       "      <td>[Adventure, Fantasy, Animation, Action, Family]</td>\n",
       "      <td>[en]</td>\n",
       "      <td>[TV Tokyo, 4 Kids Entertainment, Nintendo, Pik...</td>\n",
       "      <td>[JP]</td>\n",
       "      <td>2000</td>\n",
       "      <td>4.275705</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4114</th>\n",
       "      <td>16000000.0</td>\n",
       "      <td>10991</td>\n",
       "      <td>ja</td>\n",
       "      <td>When Molly Hale's sadness of her father's disa...</td>\n",
       "      <td>10.264597</td>\n",
       "      <td>2000-07-08</td>\n",
       "      <td>68411275.0</td>\n",
       "      <td>93.0</td>\n",
       "      <td>Pokémon: Spell of the Unknown</td>\n",
       "      <td>6.0</td>\n",
       "      <td>143.0</td>\n",
       "      <td>Pokémon Collection</td>\n",
       "      <td>[Adventure, Fantasy, Animation, Action, Family]</td>\n",
       "      <td>[en]</td>\n",
       "      <td>[TV Tokyo, 4 Kids Entertainment, Nintendo, Pik...</td>\n",
       "      <td>[JP]</td>\n",
       "      <td>2000</td>\n",
       "      <td>4.275705</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20899</th>\n",
       "      <td>0.0</td>\n",
       "      <td>109962</td>\n",
       "      <td>en</td>\n",
       "      <td>Two literary women compete for 20 years: one w...</td>\n",
       "      <td>10.396878</td>\n",
       "      <td>1981-09-23</td>\n",
       "      <td>0.0</td>\n",
       "      <td>115.0</td>\n",
       "      <td>Rich and Famous</td>\n",
       "      <td>4.9</td>\n",
       "      <td>7.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[Drama]</td>\n",
       "      <td>[en]</td>\n",
       "      <td>[Metro-Goldwyn-Mayer (MGM), Jaquet]</td>\n",
       "      <td>[US]</td>\n",
       "      <td>1981</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5710</th>\n",
       "      <td>0.0</td>\n",
       "      <td>109962</td>\n",
       "      <td>en</td>\n",
       "      <td>Two literary women compete for 20 years: one w...</td>\n",
       "      <td>12.180836</td>\n",
       "      <td>1981-09-23</td>\n",
       "      <td>0.0</td>\n",
       "      <td>115.0</td>\n",
       "      <td>Rich and Famous</td>\n",
       "      <td>4.9</td>\n",
       "      <td>7.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[Drama]</td>\n",
       "      <td>[en]</td>\n",
       "      <td>[Metro-Goldwyn-Mayer (MGM), Jaquet]</td>\n",
       "      <td>[US]</td>\n",
       "      <td>1981</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4356</th>\n",
       "      <td>3512454.0</td>\n",
       "      <td>110428</td>\n",
       "      <td>fr</td>\n",
       "      <td>Winter, 1915. Confined by her family to an asy...</td>\n",
       "      <td>0.134014</td>\n",
       "      <td>2013-03-13</td>\n",
       "      <td>115860.0</td>\n",
       "      <td>95.0</td>\n",
       "      <td>Camille Claudel 1915</td>\n",
       "      <td>7.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[Drama]</td>\n",
       "      <td>[fr]</td>\n",
       "      <td>[Canal+, Arte France Cinéma, 3B Productions, C...</td>\n",
       "      <td>[FR]</td>\n",
       "      <td>2013</td>\n",
       "      <td>0.032985</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23534</th>\n",
       "      <td>3512454.0</td>\n",
       "      <td>110428</td>\n",
       "      <td>fr</td>\n",
       "      <td>Winter, 1915. Confined by her family to an asy...</td>\n",
       "      <td>0.110065</td>\n",
       "      <td>2013-03-13</td>\n",
       "      <td>115860.0</td>\n",
       "      <td>95.0</td>\n",
       "      <td>Camille Claudel 1915</td>\n",
       "      <td>7.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[Drama]</td>\n",
       "      <td>[fr]</td>\n",
       "      <td>[Canal+, Arte France Cinéma, 3B Productions, C...</td>\n",
       "      <td>[FR]</td>\n",
       "      <td>2013</td>\n",
       "      <td>0.032985</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5535</th>\n",
       "      <td>0.0</td>\n",
       "      <td>12600</td>\n",
       "      <td>ja</td>\n",
       "      <td>All your favorite Pokémon characters are back,...</td>\n",
       "      <td>7.072301</td>\n",
       "      <td>2001-07-06</td>\n",
       "      <td>28023563.0</td>\n",
       "      <td>75.0</td>\n",
       "      <td>Pokémon 4Ever: Celebi - Voice of the Forest</td>\n",
       "      <td>5.7</td>\n",
       "      <td>82.0</td>\n",
       "      <td>Pokémon Collection</td>\n",
       "      <td>[Adventure, Fantasy, Animation, Science Fictio...</td>\n",
       "      <td>[ja]</td>\n",
       "      <td>[]</td>\n",
       "      <td>[JP, US]</td>\n",
       "      <td>2001</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44826</th>\n",
       "      <td>0.0</td>\n",
       "      <td>12600</td>\n",
       "      <td>ja</td>\n",
       "      <td>All your favorite Pokémon characters are back,...</td>\n",
       "      <td>6.080108</td>\n",
       "      <td>2001-07-06</td>\n",
       "      <td>28023563.0</td>\n",
       "      <td>75.0</td>\n",
       "      <td>Pokémon 4Ever: Celebi - Voice of the Forest</td>\n",
       "      <td>5.7</td>\n",
       "      <td>82.0</td>\n",
       "      <td>Pokémon Collection</td>\n",
       "      <td>[Adventure, Fantasy, Animation, Science Fictio...</td>\n",
       "      <td>[ja]</td>\n",
       "      <td>[]</td>\n",
       "      <td>[JP, US]</td>\n",
       "      <td>2001</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15765</th>\n",
       "      <td>2500.0</td>\n",
       "      <td>13209</td>\n",
       "      <td>fa</td>\n",
       "      <td>Since women are banned from soccer matches, Ir...</td>\n",
       "      <td>1.529879</td>\n",
       "      <td>2006-05-26</td>\n",
       "      <td>0.0</td>\n",
       "      <td>93.0</td>\n",
       "      <td>Offside</td>\n",
       "      <td>6.7</td>\n",
       "      <td>27.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[Drama, Comedy, Foreign]</td>\n",
       "      <td>[fa]</td>\n",
       "      <td>[Jafar Panahi Film Productions]</td>\n",
       "      <td>[IR]</td>\n",
       "      <td>2006</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11342</th>\n",
       "      <td>2500.0</td>\n",
       "      <td>13209</td>\n",
       "      <td>fa</td>\n",
       "      <td>Since women are banned from soccer matches, Ir...</td>\n",
       "      <td>1.52896</td>\n",
       "      <td>2006-05-26</td>\n",
       "      <td>0.0</td>\n",
       "      <td>93.0</td>\n",
       "      <td>Offside</td>\n",
       "      <td>6.7</td>\n",
       "      <td>27.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[Drama, Comedy, Foreign]</td>\n",
       "      <td>[fa]</td>\n",
       "      <td>[Jafar Panahi Film Productions]</td>\n",
       "      <td>[IR]</td>\n",
       "      <td>2006</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>838</th>\n",
       "      <td>0.0</td>\n",
       "      <td>132641</td>\n",
       "      <td>ja</td>\n",
       "      <td>Ten years into a marriage, the wife is disappo...</td>\n",
       "      <td>0.096079</td>\n",
       "      <td>1953-04-29</td>\n",
       "      <td>0.0</td>\n",
       "      <td>89.0</td>\n",
       "      <td>Wife</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[Drama]</td>\n",
       "      <td>[ja]</td>\n",
       "      <td>[Toho Company]</td>\n",
       "      <td>[JP]</td>\n",
       "      <td>1953</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30001</th>\n",
       "      <td>0.0</td>\n",
       "      <td>132641</td>\n",
       "      <td>ja</td>\n",
       "      <td>Ten years into a marriage, the wife is disappo...</td>\n",
       "      <td>0.619388</td>\n",
       "      <td>1953-04-29</td>\n",
       "      <td>0.0</td>\n",
       "      <td>89.0</td>\n",
       "      <td>Wife</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[Drama]</td>\n",
       "      <td>[ja]</td>\n",
       "      <td>[Toho Company]</td>\n",
       "      <td>[JP]</td>\n",
       "      <td>1953</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12066</th>\n",
       "      <td>1600000.0</td>\n",
       "      <td>14788</td>\n",
       "      <td>en</td>\n",
       "      <td>Set against the backdrop of a decaying Midwest...</td>\n",
       "      <td>3.008299</td>\n",
       "      <td>2005-09-03</td>\n",
       "      <td>0.0</td>\n",
       "      <td>73.0</td>\n",
       "      <td>Bubble</td>\n",
       "      <td>6.4</td>\n",
       "      <td>36.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[Drama, Crime, Mystery]</td>\n",
       "      <td>[en]</td>\n",
       "      <td>[Magnolia Pictures, Extension 765]</td>\n",
       "      <td>[US]</td>\n",
       "      <td>2005</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10419</th>\n",
       "      <td>1600000.0</td>\n",
       "      <td>14788</td>\n",
       "      <td>en</td>\n",
       "      <td>Set against the backdrop of a decaying Midwest...</td>\n",
       "      <td>3.185256</td>\n",
       "      <td>2005-09-03</td>\n",
       "      <td>0.0</td>\n",
       "      <td>73.0</td>\n",
       "      <td>Bubble</td>\n",
       "      <td>6.4</td>\n",
       "      <td>36.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[Drama, Crime, Mystery]</td>\n",
       "      <td>[en]</td>\n",
       "      <td>[Magnolia Pictures, Extension 765]</td>\n",
       "      <td>[US]</td>\n",
       "      <td>2005</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5130</th>\n",
       "      <td>26000000.0</td>\n",
       "      <td>15028</td>\n",
       "      <td>en</td>\n",
       "      <td>Until now, Zak Gibbs' greatest challenge has b...</td>\n",
       "      <td>5.373623</td>\n",
       "      <td>2002-03-17</td>\n",
       "      <td>38793283.0</td>\n",
       "      <td>94.0</td>\n",
       "      <td>Clockstoppers</td>\n",
       "      <td>4.9</td>\n",
       "      <td>89.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[Adventure, Family, Science Fiction, Thriller]</td>\n",
       "      <td>[cs, en]</td>\n",
       "      <td>[Paramount Pictures, Nickelodeon Movies, Valha...</td>\n",
       "      <td>[US]</td>\n",
       "      <td>2002</td>\n",
       "      <td>1.492049</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33743</th>\n",
       "      <td>26000000.0</td>\n",
       "      <td>15028</td>\n",
       "      <td>en</td>\n",
       "      <td>Until now, Zak Gibbs' greatest challenge has b...</td>\n",
       "      <td>4.920175</td>\n",
       "      <td>2002-03-17</td>\n",
       "      <td>38793283.0</td>\n",
       "      <td>94.0</td>\n",
       "      <td>Clockstoppers</td>\n",
       "      <td>4.9</td>\n",
       "      <td>90.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[Adventure, Family, Science Fiction, Thriller]</td>\n",
       "      <td>[cs, en]</td>\n",
       "      <td>[Paramount Pictures, Nickelodeon Movies, Valha...</td>\n",
       "      <td>[US]</td>\n",
       "      <td>2002</td>\n",
       "      <td>1.492049</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13220</th>\n",
       "      <td>0.0</td>\n",
       "      <td>159849</td>\n",
       "      <td>en</td>\n",
       "      <td>The third film of Frank Capra's 'Why We Fight\"...</td>\n",
       "      <td>0.473322</td>\n",
       "      <td>1943-01-01</td>\n",
       "      <td>0.0</td>\n",
       "      <td>57.0</td>\n",
       "      <td>Why We Fight: Divide and Conquer</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Why We Fight</td>\n",
       "      <td>[Documentary]</td>\n",
       "      <td>[en]</td>\n",
       "      <td>[]</td>\n",
       "      <td>[US]</td>\n",
       "      <td>1943</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35798</th>\n",
       "      <td>0.0</td>\n",
       "      <td>159849</td>\n",
       "      <td>en</td>\n",
       "      <td>The third film of Frank Capra's 'Why We Fight\"...</td>\n",
       "      <td>0.473322</td>\n",
       "      <td>1943-01-01</td>\n",
       "      <td>0.0</td>\n",
       "      <td>57.0</td>\n",
       "      <td>Why We Fight: Divide and Conquer</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Why We Fight</td>\n",
       "      <td>[Documentary]</td>\n",
       "      <td>[en]</td>\n",
       "      <td>[]</td>\n",
       "      <td>[US]</td>\n",
       "      <td>1943</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15074</th>\n",
       "      <td>4.0</td>\n",
       "      <td>22649</td>\n",
       "      <td>en</td>\n",
       "      <td>British nurse Catherine Barkley (Helen Hayes) ...</td>\n",
       "      <td>2.411191</td>\n",
       "      <td>1932-12-08</td>\n",
       "      <td>25.0</td>\n",
       "      <td>89.0</td>\n",
       "      <td>A Farewell to Arms</td>\n",
       "      <td>6.2</td>\n",
       "      <td>29.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[Drama, Romance, War]</td>\n",
       "      <td>[en]</td>\n",
       "      <td>[Paramount Pictures]</td>\n",
       "      <td>[US]</td>\n",
       "      <td>1932</td>\n",
       "      <td>6.250000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>949</th>\n",
       "      <td>4.0</td>\n",
       "      <td>22649</td>\n",
       "      <td>en</td>\n",
       "      <td>British nurse Catherine Barkley (Helen Hayes) ...</td>\n",
       "      <td>1.914697</td>\n",
       "      <td>1932-12-08</td>\n",
       "      <td>25.0</td>\n",
       "      <td>89.0</td>\n",
       "      <td>A Farewell to Arms</td>\n",
       "      <td>6.2</td>\n",
       "      <td>29.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[Drama, Romance, War]</td>\n",
       "      <td>[en]</td>\n",
       "      <td>[Paramount Pictures]</td>\n",
       "      <td>[US]</td>\n",
       "      <td>1932</td>\n",
       "      <td>6.250000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24164</th>\n",
       "      <td>0.0</td>\n",
       "      <td>265189</td>\n",
       "      <td>sv</td>\n",
       "      <td>While holidaying in the French Alps, a Swedish...</td>\n",
       "      <td>12.165685</td>\n",
       "      <td>2014-08-15</td>\n",
       "      <td>1359497.0</td>\n",
       "      <td>118.0</td>\n",
       "      <td>Force Majeure</td>\n",
       "      <td>6.8</td>\n",
       "      <td>255.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[Comedy, Drama]</td>\n",
       "      <td>[fr, no, sv, en]</td>\n",
       "      <td>[Motlys, Coproduction Office, Film i Väst]</td>\n",
       "      <td>[NO, SE, FR]</td>\n",
       "      <td>2014</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45265</th>\n",
       "      <td>0.0</td>\n",
       "      <td>265189</td>\n",
       "      <td>sv</td>\n",
       "      <td>While holidaying in the French Alps, a Swedish...</td>\n",
       "      <td>12.165685</td>\n",
       "      <td>2014-08-15</td>\n",
       "      <td>1359497.0</td>\n",
       "      <td>118.0</td>\n",
       "      <td>Force Majeure</td>\n",
       "      <td>6.8</td>\n",
       "      <td>255.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[Comedy, Drama]</td>\n",
       "      <td>[fr, no, sv, en]</td>\n",
       "      <td>[Motlys, Coproduction Office, Film i Väst]</td>\n",
       "      <td>[NO, SE, FR]</td>\n",
       "      <td>2014</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5865</th>\n",
       "      <td>30000000.0</td>\n",
       "      <td>4912</td>\n",
       "      <td>en</td>\n",
       "      <td>Television made him famous, but his biggest hi...</td>\n",
       "      <td>11.331072</td>\n",
       "      <td>2002-12-30</td>\n",
       "      <td>33013805.0</td>\n",
       "      <td>113.0</td>\n",
       "      <td>Confessions of a Dangerous Mind</td>\n",
       "      <td>6.6</td>\n",
       "      <td>281.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[Comedy, Crime, Drama, Romance, Thriller]</td>\n",
       "      <td>[en]</td>\n",
       "      <td>[Miramax Films, Allied Filmmakers, Mad Chance]</td>\n",
       "      <td>[US]</td>\n",
       "      <td>2002</td>\n",
       "      <td>1.100460</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33826</th>\n",
       "      <td>30000000.0</td>\n",
       "      <td>4912</td>\n",
       "      <td>en</td>\n",
       "      <td>Television made him famous, but his biggest hi...</td>\n",
       "      <td>7.645827</td>\n",
       "      <td>2002-12-30</td>\n",
       "      <td>33013805.0</td>\n",
       "      <td>113.0</td>\n",
       "      <td>Confessions of a Dangerous Mind</td>\n",
       "      <td>6.6</td>\n",
       "      <td>281.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[Comedy, Crime, Drama, Romance, Thriller]</td>\n",
       "      <td>[en]</td>\n",
       "      <td>[Miramax Films, Allied Filmmakers, Mad Chance]</td>\n",
       "      <td>[US]</td>\n",
       "      <td>2002</td>\n",
       "      <td>1.100460</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26625</th>\n",
       "      <td>10000000.0</td>\n",
       "      <td>69234</td>\n",
       "      <td>en</td>\n",
       "      <td>Count de Chagnie has discovered Christine's si...</td>\n",
       "      <td>0.43849</td>\n",
       "      <td>1990-03-18</td>\n",
       "      <td>0.0</td>\n",
       "      <td>168.0</td>\n",
       "      <td>The Phantom of the Opera</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[Drama, Horror, Music, Romance, TV Movie]</td>\n",
       "      <td>[en, it]</td>\n",
       "      <td>[Beta Film, Reteitalia, TF1, Hexatel, Saban/Sc...</td>\n",
       "      <td>[FR, DE, IT, US]</td>\n",
       "      <td>1990</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9576</th>\n",
       "      <td>10000000.0</td>\n",
       "      <td>69234</td>\n",
       "      <td>en</td>\n",
       "      <td>Count de Chagnie has discovered Christine's si...</td>\n",
       "      <td>0.441872</td>\n",
       "      <td>1990-03-18</td>\n",
       "      <td>0.0</td>\n",
       "      <td>168.0</td>\n",
       "      <td>The Phantom of the Opera</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[Drama, Horror, Music, Romance, TV Movie]</td>\n",
       "      <td>[en, it]</td>\n",
       "      <td>[Beta Film, Reteitalia, TF1, Hexatel, Saban/Sc...</td>\n",
       "      <td>[FR, DE, IT, US]</td>\n",
       "      <td>1990</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20843</th>\n",
       "      <td>40000000.0</td>\n",
       "      <td>77221</td>\n",
       "      <td>en</td>\n",
       "      <td>On the Arabian Peninsula in the 1930s, two war...</td>\n",
       "      <td>6.475665</td>\n",
       "      <td>2011-12-21</td>\n",
       "      <td>5446000.0</td>\n",
       "      <td>130.0</td>\n",
       "      <td>Black Gold</td>\n",
       "      <td>5.9</td>\n",
       "      <td>77.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[Adventure, Drama]</td>\n",
       "      <td>[en]</td>\n",
       "      <td>[France 2 Cinéma, Quinta Communications, Carth...</td>\n",
       "      <td>[FR, IT, QA, TN]</td>\n",
       "      <td>2011</td>\n",
       "      <td>0.136150</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11155</th>\n",
       "      <td>40000000.0</td>\n",
       "      <td>77221</td>\n",
       "      <td>en</td>\n",
       "      <td>On the Arabian Peninsula in the 1930s, two war...</td>\n",
       "      <td>6.652197</td>\n",
       "      <td>2011-12-21</td>\n",
       "      <td>5446000.0</td>\n",
       "      <td>130.0</td>\n",
       "      <td>Black Gold</td>\n",
       "      <td>5.9</td>\n",
       "      <td>77.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[Adventure, Drama]</td>\n",
       "      <td>[en]</td>\n",
       "      <td>[France 2 Cinéma, Quinta Communications, Carth...</td>\n",
       "      <td>[FR, IT, QA, TN]</td>\n",
       "      <td>2011</td>\n",
       "      <td>0.136150</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21116</th>\n",
       "      <td>0.0</td>\n",
       "      <td>84198</td>\n",
       "      <td>en</td>\n",
       "      <td>Using personal stories, this powerful document...</td>\n",
       "      <td>1.673307</td>\n",
       "      <td>2012-03-22</td>\n",
       "      <td>0.0</td>\n",
       "      <td>84.0</td>\n",
       "      <td>A Place at the Table</td>\n",
       "      <td>6.9</td>\n",
       "      <td>7.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[Documentary]</td>\n",
       "      <td>[en]</td>\n",
       "      <td>[]</td>\n",
       "      <td>[US]</td>\n",
       "      <td>2012</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2564</th>\n",
       "      <td>0.0</td>\n",
       "      <td>84198</td>\n",
       "      <td>en</td>\n",
       "      <td>Using personal stories, this powerful document...</td>\n",
       "      <td>0.501046</td>\n",
       "      <td>2012-03-22</td>\n",
       "      <td>0.0</td>\n",
       "      <td>84.0</td>\n",
       "      <td>A Place at the Table</td>\n",
       "      <td>6.9</td>\n",
       "      <td>7.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[Documentary]</td>\n",
       "      <td>[en]</td>\n",
       "      <td>[]</td>\n",
       "      <td>[US]</td>\n",
       "      <td>2012</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13946</th>\n",
       "      <td>0.0</td>\n",
       "      <td>97995</td>\n",
       "      <td>en</td>\n",
       "      <td>After breaking a mirror in his home, superstit...</td>\n",
       "      <td>0.141558</td>\n",
       "      <td>1921-02-06</td>\n",
       "      <td>0.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>Seven Years Bad Luck</td>\n",
       "      <td>5.6</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[Comedy]</td>\n",
       "      <td>[en]</td>\n",
       "      <td>[Max Linder Productions]</td>\n",
       "      <td>[US]</td>\n",
       "      <td>1921</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40276</th>\n",
       "      <td>0.0</td>\n",
       "      <td>97995</td>\n",
       "      <td>en</td>\n",
       "      <td>After breaking a mirror in his home, superstit...</td>\n",
       "      <td>0.141558</td>\n",
       "      <td>1921-02-06</td>\n",
       "      <td>0.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>Seven Years Bad Luck</td>\n",
       "      <td>5.6</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[Comedy]</td>\n",
       "      <td>[en]</td>\n",
       "      <td>[Max Linder Productions]</td>\n",
       "      <td>[US]</td>\n",
       "      <td>1921</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38871</th>\n",
       "      <td>0.0</td>\n",
       "      <td>99080</td>\n",
       "      <td>en</td>\n",
       "      <td>Originally called White Thunder, American prod...</td>\n",
       "      <td>0.002362</td>\n",
       "      <td>1931-06-21</td>\n",
       "      <td>0.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>The Viking</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[Action, Drama, Romance, Adventure]</td>\n",
       "      <td>[en]</td>\n",
       "      <td>[]</td>\n",
       "      <td>[]</td>\n",
       "      <td>1931</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16167</th>\n",
       "      <td>0.0</td>\n",
       "      <td>99080</td>\n",
       "      <td>en</td>\n",
       "      <td>Originally called White Thunder, American prod...</td>\n",
       "      <td>0.002362</td>\n",
       "      <td>1931-06-21</td>\n",
       "      <td>0.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>The Viking</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[Action, Drama, Romance, Adventure]</td>\n",
       "      <td>[en]</td>\n",
       "      <td>[]</td>\n",
       "      <td>[]</td>\n",
       "      <td>1931</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           budget      id original_language  \\\n",
       "44821  16000000.0   10991                ja   \n",
       "4114   16000000.0   10991                ja   \n",
       "20899         0.0  109962                en   \n",
       "5710          0.0  109962                en   \n",
       "4356    3512454.0  110428                fr   \n",
       "23534   3512454.0  110428                fr   \n",
       "5535          0.0   12600                ja   \n",
       "44826         0.0   12600                ja   \n",
       "15765      2500.0   13209                fa   \n",
       "11342      2500.0   13209                fa   \n",
       "838           0.0  132641                ja   \n",
       "30001         0.0  132641                ja   \n",
       "12066   1600000.0   14788                en   \n",
       "10419   1600000.0   14788                en   \n",
       "5130   26000000.0   15028                en   \n",
       "33743  26000000.0   15028                en   \n",
       "13220         0.0  159849                en   \n",
       "35798         0.0  159849                en   \n",
       "15074         4.0   22649                en   \n",
       "949           4.0   22649                en   \n",
       "24164         0.0  265189                sv   \n",
       "45265         0.0  265189                sv   \n",
       "5865   30000000.0    4912                en   \n",
       "33826  30000000.0    4912                en   \n",
       "26625  10000000.0   69234                en   \n",
       "9576   10000000.0   69234                en   \n",
       "20843  40000000.0   77221                en   \n",
       "11155  40000000.0   77221                en   \n",
       "21116         0.0   84198                en   \n",
       "2564          0.0   84198                en   \n",
       "13946         0.0   97995                en   \n",
       "40276         0.0   97995                en   \n",
       "38871         0.0   99080                en   \n",
       "16167         0.0   99080                en   \n",
       "\n",
       "                                                overview popularity  \\\n",
       "44821  When Molly Hale's sadness of her father's disa...   6.480376   \n",
       "4114   When Molly Hale's sadness of her father's disa...  10.264597   \n",
       "20899  Two literary women compete for 20 years: one w...  10.396878   \n",
       "5710   Two literary women compete for 20 years: one w...  12.180836   \n",
       "4356   Winter, 1915. Confined by her family to an asy...   0.134014   \n",
       "23534  Winter, 1915. Confined by her family to an asy...   0.110065   \n",
       "5535   All your favorite Pokémon characters are back,...   7.072301   \n",
       "44826  All your favorite Pokémon characters are back,...   6.080108   \n",
       "15765  Since women are banned from soccer matches, Ir...   1.529879   \n",
       "11342  Since women are banned from soccer matches, Ir...    1.52896   \n",
       "838    Ten years into a marriage, the wife is disappo...   0.096079   \n",
       "30001  Ten years into a marriage, the wife is disappo...   0.619388   \n",
       "12066  Set against the backdrop of a decaying Midwest...   3.008299   \n",
       "10419  Set against the backdrop of a decaying Midwest...   3.185256   \n",
       "5130   Until now, Zak Gibbs' greatest challenge has b...   5.373623   \n",
       "33743  Until now, Zak Gibbs' greatest challenge has b...   4.920175   \n",
       "13220  The third film of Frank Capra's 'Why We Fight\"...   0.473322   \n",
       "35798  The third film of Frank Capra's 'Why We Fight\"...   0.473322   \n",
       "15074  British nurse Catherine Barkley (Helen Hayes) ...   2.411191   \n",
       "949    British nurse Catherine Barkley (Helen Hayes) ...   1.914697   \n",
       "24164  While holidaying in the French Alps, a Swedish...  12.165685   \n",
       "45265  While holidaying in the French Alps, a Swedish...  12.165685   \n",
       "5865   Television made him famous, but his biggest hi...  11.331072   \n",
       "33826  Television made him famous, but his biggest hi...   7.645827   \n",
       "26625  Count de Chagnie has discovered Christine's si...    0.43849   \n",
       "9576   Count de Chagnie has discovered Christine's si...   0.441872   \n",
       "20843  On the Arabian Peninsula in the 1930s, two war...   6.475665   \n",
       "11155  On the Arabian Peninsula in the 1930s, two war...   6.652197   \n",
       "21116  Using personal stories, this powerful document...   1.673307   \n",
       "2564   Using personal stories, this powerful document...   0.501046   \n",
       "13946  After breaking a mirror in his home, superstit...   0.141558   \n",
       "40276  After breaking a mirror in his home, superstit...   0.141558   \n",
       "38871  Originally called White Thunder, American prod...   0.002362   \n",
       "16167  Originally called White Thunder, American prod...   0.002362   \n",
       "\n",
       "      release_date     revenue  runtime  \\\n",
       "44821   2000-07-08  68411275.0     93.0   \n",
       "4114    2000-07-08  68411275.0     93.0   \n",
       "20899   1981-09-23         0.0    115.0   \n",
       "5710    1981-09-23         0.0    115.0   \n",
       "4356    2013-03-13    115860.0     95.0   \n",
       "23534   2013-03-13    115860.0     95.0   \n",
       "5535    2001-07-06  28023563.0     75.0   \n",
       "44826   2001-07-06  28023563.0     75.0   \n",
       "15765   2006-05-26         0.0     93.0   \n",
       "11342   2006-05-26         0.0     93.0   \n",
       "838     1953-04-29         0.0     89.0   \n",
       "30001   1953-04-29         0.0     89.0   \n",
       "12066   2005-09-03         0.0     73.0   \n",
       "10419   2005-09-03         0.0     73.0   \n",
       "5130    2002-03-17  38793283.0     94.0   \n",
       "33743   2002-03-17  38793283.0     94.0   \n",
       "13220   1943-01-01         0.0     57.0   \n",
       "35798   1943-01-01         0.0     57.0   \n",
       "15074   1932-12-08        25.0     89.0   \n",
       "949     1932-12-08        25.0     89.0   \n",
       "24164   2014-08-15   1359497.0    118.0   \n",
       "45265   2014-08-15   1359497.0    118.0   \n",
       "5865    2002-12-30  33013805.0    113.0   \n",
       "33826   2002-12-30  33013805.0    113.0   \n",
       "26625   1990-03-18         0.0    168.0   \n",
       "9576    1990-03-18         0.0    168.0   \n",
       "20843   2011-12-21   5446000.0    130.0   \n",
       "11155   2011-12-21   5446000.0    130.0   \n",
       "21116   2012-03-22         0.0     84.0   \n",
       "2564    2012-03-22         0.0     84.0   \n",
       "13946   1921-02-06         0.0     62.0   \n",
       "40276   1921-02-06         0.0     62.0   \n",
       "38871   1931-06-21         0.0     70.0   \n",
       "16167   1931-06-21         0.0     70.0   \n",
       "\n",
       "                                             title  vote_average  vote_count  \\\n",
       "44821                Pokémon: Spell of the Unknown           6.0       144.0   \n",
       "4114                 Pokémon: Spell of the Unknown           6.0       143.0   \n",
       "20899                              Rich and Famous           4.9         7.0   \n",
       "5710                               Rich and Famous           4.9         7.0   \n",
       "4356                          Camille Claudel 1915           7.0        20.0   \n",
       "23534                         Camille Claudel 1915           7.0        20.0   \n",
       "5535   Pokémon 4Ever: Celebi - Voice of the Forest           5.7        82.0   \n",
       "44826  Pokémon 4Ever: Celebi - Voice of the Forest           5.7        82.0   \n",
       "15765                                      Offside           6.7        27.0   \n",
       "11342                                      Offside           6.7        27.0   \n",
       "838                                           Wife           0.0         0.0   \n",
       "30001                                         Wife           0.0         0.0   \n",
       "12066                                       Bubble           6.4        36.0   \n",
       "10419                                       Bubble           6.4        36.0   \n",
       "5130                                 Clockstoppers           4.9        89.0   \n",
       "33743                                Clockstoppers           4.9        90.0   \n",
       "13220             Why We Fight: Divide and Conquer           5.0         1.0   \n",
       "35798             Why We Fight: Divide and Conquer           5.0         1.0   \n",
       "15074                           A Farewell to Arms           6.2        29.0   \n",
       "949                             A Farewell to Arms           6.2        29.0   \n",
       "24164                                Force Majeure           6.8       255.0   \n",
       "45265                                Force Majeure           6.8       255.0   \n",
       "5865               Confessions of a Dangerous Mind           6.6       281.0   \n",
       "33826              Confessions of a Dangerous Mind           6.6       281.0   \n",
       "26625                     The Phantom of the Opera           5.0         3.0   \n",
       "9576                      The Phantom of the Opera           5.0         3.0   \n",
       "20843                                   Black Gold           5.9        77.0   \n",
       "11155                                   Black Gold           5.9        77.0   \n",
       "21116                         A Place at the Table           6.9         7.0   \n",
       "2564                          A Place at the Table           6.9         7.0   \n",
       "13946                         Seven Years Bad Luck           5.6         4.0   \n",
       "40276                         Seven Years Bad Luck           5.6         4.0   \n",
       "38871                                   The Viking           0.0         0.0   \n",
       "16167                                   The Viking           0.0         0.0   \n",
       "\n",
       "               collection                                        genres_list  \\\n",
       "44821  Pokémon Collection    [Adventure, Fantasy, Animation, Action, Family]   \n",
       "4114   Pokémon Collection    [Adventure, Fantasy, Animation, Action, Family]   \n",
       "20899                 NaN                                            [Drama]   \n",
       "5710                  NaN                                            [Drama]   \n",
       "4356                  NaN                                            [Drama]   \n",
       "23534                 NaN                                            [Drama]   \n",
       "5535   Pokémon Collection  [Adventure, Fantasy, Animation, Science Fictio...   \n",
       "44826  Pokémon Collection  [Adventure, Fantasy, Animation, Science Fictio...   \n",
       "15765                 NaN                           [Drama, Comedy, Foreign]   \n",
       "11342                 NaN                           [Drama, Comedy, Foreign]   \n",
       "838                   NaN                                            [Drama]   \n",
       "30001                 NaN                                            [Drama]   \n",
       "12066                 NaN                            [Drama, Crime, Mystery]   \n",
       "10419                 NaN                            [Drama, Crime, Mystery]   \n",
       "5130                  NaN     [Adventure, Family, Science Fiction, Thriller]   \n",
       "33743                 NaN     [Adventure, Family, Science Fiction, Thriller]   \n",
       "13220        Why We Fight                                      [Documentary]   \n",
       "35798        Why We Fight                                      [Documentary]   \n",
       "15074                 NaN                              [Drama, Romance, War]   \n",
       "949                   NaN                              [Drama, Romance, War]   \n",
       "24164                 NaN                                    [Comedy, Drama]   \n",
       "45265                 NaN                                    [Comedy, Drama]   \n",
       "5865                  NaN          [Comedy, Crime, Drama, Romance, Thriller]   \n",
       "33826                 NaN          [Comedy, Crime, Drama, Romance, Thriller]   \n",
       "26625                 NaN          [Drama, Horror, Music, Romance, TV Movie]   \n",
       "9576                  NaN          [Drama, Horror, Music, Romance, TV Movie]   \n",
       "20843                 NaN                                 [Adventure, Drama]   \n",
       "11155                 NaN                                 [Adventure, Drama]   \n",
       "21116                 NaN                                      [Documentary]   \n",
       "2564                  NaN                                      [Documentary]   \n",
       "13946                 NaN                                           [Comedy]   \n",
       "40276                 NaN                                           [Comedy]   \n",
       "38871                 NaN                [Action, Drama, Romance, Adventure]   \n",
       "16167                 NaN                [Action, Drama, Romance, Adventure]   \n",
       "\n",
       "      spoken_languages_list  \\\n",
       "44821                  [en]   \n",
       "4114                   [en]   \n",
       "20899                  [en]   \n",
       "5710                   [en]   \n",
       "4356                   [fr]   \n",
       "23534                  [fr]   \n",
       "5535                   [ja]   \n",
       "44826                  [ja]   \n",
       "15765                  [fa]   \n",
       "11342                  [fa]   \n",
       "838                    [ja]   \n",
       "30001                  [ja]   \n",
       "12066                  [en]   \n",
       "10419                  [en]   \n",
       "5130               [cs, en]   \n",
       "33743              [cs, en]   \n",
       "13220                  [en]   \n",
       "35798                  [en]   \n",
       "15074                  [en]   \n",
       "949                    [en]   \n",
       "24164      [fr, no, sv, en]   \n",
       "45265      [fr, no, sv, en]   \n",
       "5865                   [en]   \n",
       "33826                  [en]   \n",
       "26625              [en, it]   \n",
       "9576               [en, it]   \n",
       "20843                  [en]   \n",
       "11155                  [en]   \n",
       "21116                  [en]   \n",
       "2564                   [en]   \n",
       "13946                  [en]   \n",
       "40276                  [en]   \n",
       "38871                  [en]   \n",
       "16167                  [en]   \n",
       "\n",
       "                               production_companies_list  \\\n",
       "44821  [TV Tokyo, 4 Kids Entertainment, Nintendo, Pik...   \n",
       "4114   [TV Tokyo, 4 Kids Entertainment, Nintendo, Pik...   \n",
       "20899                [Metro-Goldwyn-Mayer (MGM), Jaquet]   \n",
       "5710                 [Metro-Goldwyn-Mayer (MGM), Jaquet]   \n",
       "4356   [Canal+, Arte France Cinéma, 3B Productions, C...   \n",
       "23534  [Canal+, Arte France Cinéma, 3B Productions, C...   \n",
       "5535                                                  []   \n",
       "44826                                                 []   \n",
       "15765                    [Jafar Panahi Film Productions]   \n",
       "11342                    [Jafar Panahi Film Productions]   \n",
       "838                                       [Toho Company]   \n",
       "30001                                     [Toho Company]   \n",
       "12066                 [Magnolia Pictures, Extension 765]   \n",
       "10419                 [Magnolia Pictures, Extension 765]   \n",
       "5130   [Paramount Pictures, Nickelodeon Movies, Valha...   \n",
       "33743  [Paramount Pictures, Nickelodeon Movies, Valha...   \n",
       "13220                                                 []   \n",
       "35798                                                 []   \n",
       "15074                               [Paramount Pictures]   \n",
       "949                                 [Paramount Pictures]   \n",
       "24164         [Motlys, Coproduction Office, Film i Väst]   \n",
       "45265         [Motlys, Coproduction Office, Film i Väst]   \n",
       "5865      [Miramax Films, Allied Filmmakers, Mad Chance]   \n",
       "33826     [Miramax Films, Allied Filmmakers, Mad Chance]   \n",
       "26625  [Beta Film, Reteitalia, TF1, Hexatel, Saban/Sc...   \n",
       "9576   [Beta Film, Reteitalia, TF1, Hexatel, Saban/Sc...   \n",
       "20843  [France 2 Cinéma, Quinta Communications, Carth...   \n",
       "11155  [France 2 Cinéma, Quinta Communications, Carth...   \n",
       "21116                                                 []   \n",
       "2564                                                  []   \n",
       "13946                           [Max Linder Productions]   \n",
       "40276                           [Max Linder Productions]   \n",
       "38871                                                 []   \n",
       "16167                                                 []   \n",
       "\n",
       "      production_countries_list  release_year    return  \n",
       "44821                      [JP]          2000  4.275705  \n",
       "4114                       [JP]          2000  4.275705  \n",
       "20899                      [US]          1981  0.000000  \n",
       "5710                       [US]          1981  0.000000  \n",
       "4356                       [FR]          2013  0.032985  \n",
       "23534                      [FR]          2013  0.032985  \n",
       "5535                   [JP, US]          2001  0.000000  \n",
       "44826                  [JP, US]          2001  0.000000  \n",
       "15765                      [IR]          2006  0.000000  \n",
       "11342                      [IR]          2006  0.000000  \n",
       "838                        [JP]          1953  0.000000  \n",
       "30001                      [JP]          1953  0.000000  \n",
       "12066                      [US]          2005  0.000000  \n",
       "10419                      [US]          2005  0.000000  \n",
       "5130                       [US]          2002  1.492049  \n",
       "33743                      [US]          2002  1.492049  \n",
       "13220                      [US]          1943  0.000000  \n",
       "35798                      [US]          1943  0.000000  \n",
       "15074                      [US]          1932  6.250000  \n",
       "949                        [US]          1932  6.250000  \n",
       "24164              [NO, SE, FR]          2014  0.000000  \n",
       "45265              [NO, SE, FR]          2014  0.000000  \n",
       "5865                       [US]          2002  1.100460  \n",
       "33826                      [US]          2002  1.100460  \n",
       "26625          [FR, DE, IT, US]          1990  0.000000  \n",
       "9576           [FR, DE, IT, US]          1990  0.000000  \n",
       "20843          [FR, IT, QA, TN]          2011  0.136150  \n",
       "11155          [FR, IT, QA, TN]          2011  0.136150  \n",
       "21116                      [US]          2012  0.000000  \n",
       "2564                       [US]          2012  0.000000  \n",
       "13946                      [US]          1921  0.000000  \n",
       "40276                      [US]          1921  0.000000  \n",
       "38871                        []          1931  0.000000  \n",
       "16167                        []          1931  0.000000  "
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# applying duplicated() in the entire dataframe will pop-up an error because of columns like \"genres_list\" that are compound of lists\n",
    "# notice how these movies with duplicated id also have same  values in other columns\n",
    "\n",
    "df_movies[df_movies[\"id\"].duplicated(keep=False)].sort_values(by=\"id\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "# subset its important here because it will look for duplicates in column \"id\" and drop the rows.\n",
    "# using df_movies[\"id\"].drop_duplicates() would only delete duplicates inside this columns\n",
    "\n",
    "df_movies.drop_duplicates(subset=\"id\", inplace=True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(45346, 18)"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_movies.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "# saving this in a csv so we don't have to do this process again\n",
    "\n",
    "# Index False because we don't need the index in the csv file as an additional column\n",
    "df_movies.to_csv(\"../processed_data/movies.csv\", index=False) "
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2. Credits Dataset"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we execute a similar process with credits dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "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  \\\n",
       "0  [{'cast_id': 14, 'character': 'Woody (voice)',...   \n",
       "1  [{'cast_id': 1, 'character': 'Alan Parrish', '...   \n",
       "2  [{'cast_id': 2, 'character': 'Max Goldman', 'c...   \n",
       "3  [{'cast_id': 1, 'character': \"Savannah 'Vannah...   \n",
       "4  [{'cast_id': 1, 'character': 'George Banks', '...   \n",
       "\n",
       "                                                crew     id  \n",
       "0  [{'credit_id': '52fe4284c3a36847f8024f49', 'de...    862  \n",
       "1  [{'credit_id': '52fe44bfc3a36847f80a7cd1', 'de...   8844  \n",
       "2  [{'credit_id': '52fe466a9251416c75077a89', 'de...  15602  \n",
       "3  [{'credit_id': '52fe44779251416c91011acb', 'de...  31357  \n",
       "4  [{'credit_id': '52fe44959251416c75039ed7', 'de...  11862  "
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_credits.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "cast    object\n",
       "crew    object\n",
       "id       int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_credits.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(45476, 3)"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_credits.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "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>25950</th>\n",
       "      <td>[{'cast_id': 12, 'character': 'The Creature', ...</td>\n",
       "      <td>[{'credit_id': '52fe4380c3a36847f80590dd', 'de...</td>\n",
       "      <td>3057</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25885</th>\n",
       "      <td>[{'cast_id': 12, 'character': 'The Creature', ...</td>\n",
       "      <td>[{'credit_id': '52fe4380c3a36847f80590dd', 'de...</td>\n",
       "      <td>3057</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9165</th>\n",
       "      <td>[{'cast_id': 11, 'character': 'Jef Costello', ...</td>\n",
       "      <td>[{'credit_id': '52fe440ac3a36847f807ee01', 'de...</td>\n",
       "      <td>5511</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7345</th>\n",
       "      <td>[{'cast_id': 11, 'character': 'Jef Costello', ...</td>\n",
       "      <td>[{'credit_id': '52fe440ac3a36847f807ee01', 'de...</td>\n",
       "      <td>5511</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25895</th>\n",
       "      <td>[{'cast_id': 11, 'character': 'Mike Gaucher / ...</td>\n",
       "      <td>[{'credit_id': '52fe44b8c3a36847f80a6389', 'de...</td>\n",
       "      <td>8767</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25897</th>\n",
       "      <td>[]</td>\n",
       "      <td>[{'credit_id': '52fe4d119251416c7512ed39', 'de...</td>\n",
       "      <td>187156</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25954</th>\n",
       "      <td>[{'cast_id': 7, 'character': 'Hollander', 'cre...</td>\n",
       "      <td>[{'credit_id': '52fe4da29251416c9111ce5d', 'de...</td>\n",
       "      <td>199591</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25887</th>\n",
       "      <td>[{'cast_id': 7, 'character': 'Hollander', 'cre...</td>\n",
       "      <td>[{'credit_id': '52fe4da29251416c9111ce5d', 'de...</td>\n",
       "      <td>199591</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24163</th>\n",
       "      <td>[{'cast_id': 2, 'character': 'Ebba', 'credit_i...</td>\n",
       "      <td>[{'credit_id': '534fd1a80e0a267eb6000e32', 'de...</td>\n",
       "      <td>265189</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45275</th>\n",
       "      <td>[{'cast_id': 2, 'character': 'Ebba', 'credit_i...</td>\n",
       "      <td>[{'credit_id': '534fd1a80e0a267eb6000e32', 'de...</td>\n",
       "      <td>265189</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>73 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                    cast  \\\n",
       "25950  [{'cast_id': 12, 'character': 'The Creature', ...   \n",
       "25885  [{'cast_id': 12, 'character': 'The Creature', ...   \n",
       "9165   [{'cast_id': 11, 'character': 'Jef Costello', ...   \n",
       "7345   [{'cast_id': 11, 'character': 'Jef Costello', ...   \n",
       "25895  [{'cast_id': 11, 'character': 'Mike Gaucher / ...   \n",
       "...                                                  ...   \n",
       "25897                                                 []   \n",
       "25954  [{'cast_id': 7, 'character': 'Hollander', 'cre...   \n",
       "25887  [{'cast_id': 7, 'character': 'Hollander', 'cre...   \n",
       "24163  [{'cast_id': 2, 'character': 'Ebba', 'credit_i...   \n",
       "45275  [{'cast_id': 2, 'character': 'Ebba', 'credit_i...   \n",
       "\n",
       "                                                    crew      id  \n",
       "25950  [{'credit_id': '52fe4380c3a36847f80590dd', 'de...    3057  \n",
       "25885  [{'credit_id': '52fe4380c3a36847f80590dd', 'de...    3057  \n",
       "9165   [{'credit_id': '52fe440ac3a36847f807ee01', 'de...    5511  \n",
       "7345   [{'credit_id': '52fe440ac3a36847f807ee01', 'de...    5511  \n",
       "25895  [{'credit_id': '52fe44b8c3a36847f80a6389', 'de...    8767  \n",
       "...                                                  ...     ...  \n",
       "25897  [{'credit_id': '52fe4d119251416c7512ed39', 'de...  187156  \n",
       "25954  [{'credit_id': '52fe4da29251416c9111ce5d', 'de...  199591  \n",
       "25887  [{'credit_id': '52fe4da29251416c9111ce5d', 'de...  199591  \n",
       "24163  [{'credit_id': '534fd1a80e0a267eb6000e32', 'de...  265189  \n",
       "45275  [{'credit_id': '534fd1a80e0a267eb6000e32', 'de...  265189  \n",
       "\n",
       "[73 rows x 3 columns]"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_credits[df_credits.duplicated(keep=False)].sort_values(by=\"id\") # checking and droping duplicates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_credits.drop_duplicates(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "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>5865</th>\n",
       "      <td>[{'cast_id': 15, 'character': 'Chuck Barris', ...</td>\n",
       "      <td>[{'credit_id': '52fe43e2c3a36847f80760b5', 'de...</td>\n",
       "      <td>4912</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33838</th>\n",
       "      <td>[{'cast_id': 15, 'character': 'Chuck Barris', ...</td>\n",
       "      <td>[{'credit_id': '52fe43e2c3a36847f80760a9', 'de...</td>\n",
       "      <td>4912</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9576</th>\n",
       "      <td>[{'cast_id': 1, 'character': 'Gerard Carriere'...</td>\n",
       "      <td>[{'credit_id': '5468acec22136e68c9000d53', 'de...</td>\n",
       "      <td>69234</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26638</th>\n",
       "      <td>[{'cast_id': 1, 'character': 'Gerard Carriere'...</td>\n",
       "      <td>[{'credit_id': '52fe47bfc3a368484e0d77bf', 'de...</td>\n",
       "      <td>69234</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16167</th>\n",
       "      <td>[{'cast_id': 1004, 'character': 'Luke Oarum', ...</td>\n",
       "      <td>[{'credit_id': '52fe4a269251416c750df623', 'de...</td>\n",
       "      <td>99080</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38882</th>\n",
       "      <td>[{'cast_id': 1004, 'character': 'Luke Oarum', ...</td>\n",
       "      <td>[{'credit_id': '52fe4a269251416c750df61d', 'de...</td>\n",
       "      <td>99080</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4356</th>\n",
       "      <td>[{'cast_id': 3, 'character': 'Camille Claudel'...</td>\n",
       "      <td>[{'credit_id': '52fe4ad6c3a36847f81e461b', 'de...</td>\n",
       "      <td>110428</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23533</th>\n",
       "      <td>[{'cast_id': 3, 'character': 'Camille Claudel'...</td>\n",
       "      <td>[{'credit_id': '577ed5389251416976004432', 'de...</td>\n",
       "      <td>110428</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>838</th>\n",
       "      <td>[{'cast_id': 4, 'character': 'Mihoko Nakagawa'...</td>\n",
       "      <td>[{'credit_id': '52fe4b9ac3a368484e190d25', 'de...</td>\n",
       "      <td>132641</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30013</th>\n",
       "      <td>[{'cast_id': 4, 'character': 'Mihoko Nakagawa'...</td>\n",
       "      <td>[{'credit_id': '56365ed9925141285701b06e', 'de...</td>\n",
       "      <td>132641</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13220</th>\n",
       "      <td>[{'cast_id': 12, 'character': 'Narrator', 'cre...</td>\n",
       "      <td>[{'credit_id': '52fe4c229251416c910f10d5', 'de...</td>\n",
       "      <td>159849</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35809</th>\n",
       "      <td>[{'cast_id': 12, 'character': 'Narrator', 'cre...</td>\n",
       "      <td>[{'credit_id': '52fe4c229251416c910f10d5', 'de...</td>\n",
       "      <td>159849</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33196</th>\n",
       "      <td>[{'cast_id': 1, 'character': 'Jenjira', 'credi...</td>\n",
       "      <td>[{'credit_id': '5448c8efc3a3680fb4001582', 'de...</td>\n",
       "      <td>298721</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40051</th>\n",
       "      <td>[{'cast_id': 1, 'character': 'Jenjira', 'credi...</td>\n",
       "      <td>[{'credit_id': '5739ceeac3a3683c9d000c12', 'de...</td>\n",
       "      <td>298721</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                    cast  \\\n",
       "5865   [{'cast_id': 15, 'character': 'Chuck Barris', ...   \n",
       "33838  [{'cast_id': 15, 'character': 'Chuck Barris', ...   \n",
       "9576   [{'cast_id': 1, 'character': 'Gerard Carriere'...   \n",
       "26638  [{'cast_id': 1, 'character': 'Gerard Carriere'...   \n",
       "16167  [{'cast_id': 1004, 'character': 'Luke Oarum', ...   \n",
       "38882  [{'cast_id': 1004, 'character': 'Luke Oarum', ...   \n",
       "4356   [{'cast_id': 3, 'character': 'Camille Claudel'...   \n",
       "23533  [{'cast_id': 3, 'character': 'Camille Claudel'...   \n",
       "838    [{'cast_id': 4, 'character': 'Mihoko Nakagawa'...   \n",
       "30013  [{'cast_id': 4, 'character': 'Mihoko Nakagawa'...   \n",
       "13220  [{'cast_id': 12, 'character': 'Narrator', 'cre...   \n",
       "35809  [{'cast_id': 12, 'character': 'Narrator', 'cre...   \n",
       "33196  [{'cast_id': 1, 'character': 'Jenjira', 'credi...   \n",
       "40051  [{'cast_id': 1, 'character': 'Jenjira', 'credi...   \n",
       "\n",
       "                                                    crew      id  \n",
       "5865   [{'credit_id': '52fe43e2c3a36847f80760b5', 'de...    4912  \n",
       "33838  [{'credit_id': '52fe43e2c3a36847f80760a9', 'de...    4912  \n",
       "9576   [{'credit_id': '5468acec22136e68c9000d53', 'de...   69234  \n",
       "26638  [{'credit_id': '52fe47bfc3a368484e0d77bf', 'de...   69234  \n",
       "16167  [{'credit_id': '52fe4a269251416c750df623', 'de...   99080  \n",
       "38882  [{'credit_id': '52fe4a269251416c750df61d', 'de...   99080  \n",
       "4356   [{'credit_id': '52fe4ad6c3a36847f81e461b', 'de...  110428  \n",
       "23533  [{'credit_id': '577ed5389251416976004432', 'de...  110428  \n",
       "838    [{'credit_id': '52fe4b9ac3a368484e190d25', 'de...  132641  \n",
       "30013  [{'credit_id': '56365ed9925141285701b06e', 'de...  132641  \n",
       "13220  [{'credit_id': '52fe4c229251416c910f10d5', 'de...  159849  \n",
       "35809  [{'credit_id': '52fe4c229251416c910f10d5', 'de...  159849  \n",
       "33196  [{'credit_id': '5448c8efc3a3680fb4001582', 'de...  298721  \n",
       "40051  [{'credit_id': '5739ceeac3a3683c9d000c12', 'de...  298721  "
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# its very important to check id duplicated because we have to merge by id in the future\n",
    "\n",
    "df_credits[df_credits[\"id\"].duplicated(keep=False)].sort_values(by=\"id\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_credits.drop_duplicates(subset=\"id\", inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(45432, 3)"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_credits.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "# converting strings to dictionaries \n",
    "\n",
    "df_credits[\"cast\"] = [x if pd.isnull(x) else ast.literal_eval(x) for x in df_credits[\"cast\"]] \n",
    "df_credits[\"crew\"] = [x if pd.isnull(x) else ast.literal_eval(x) for x in df_credits[\"crew\"]]"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## A. Cast Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 251,
   "metadata": {},
   "outputs": [
    {
     "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_id</th>\n",
       "      <th>character</th>\n",
       "      <th>credit_id</th>\n",
       "      <th>gender</th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>order</th>\n",
       "      <th>profile_path</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>14</td>\n",
       "      <td>Woody (voice)</td>\n",
       "      <td>52fe4284c3a36847f8024f95</td>\n",
       "      <td>2</td>\n",
       "      <td>862</td>\n",
       "      <td>Tom Hanks</td>\n",
       "      <td>0</td>\n",
       "      <td>/pQFoyx7rp09CJTAb932F2g8Nlho.jpg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>15</td>\n",
       "      <td>Buzz Lightyear (voice)</td>\n",
       "      <td>52fe4284c3a36847f8024f99</td>\n",
       "      <td>2</td>\n",
       "      <td>862</td>\n",
       "      <td>Tim Allen</td>\n",
       "      <td>1</td>\n",
       "      <td>/uX2xVf6pMmPepxnvFWyBtjexzgY.jpg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>16</td>\n",
       "      <td>Mr. Potato Head (voice)</td>\n",
       "      <td>52fe4284c3a36847f8024f9d</td>\n",
       "      <td>2</td>\n",
       "      <td>862</td>\n",
       "      <td>Don Rickles</td>\n",
       "      <td>2</td>\n",
       "      <td>/h5BcaDMPRVLHLDzbQavec4xfSdt.jpg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>17</td>\n",
       "      <td>Slinky Dog (voice)</td>\n",
       "      <td>52fe4284c3a36847f8024fa1</td>\n",
       "      <td>2</td>\n",
       "      <td>862</td>\n",
       "      <td>Jim Varney</td>\n",
       "      <td>3</td>\n",
       "      <td>/eIo2jVVXYgjDtaHoF19Ll9vtW7h.jpg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>18</td>\n",
       "      <td>Rex (voice)</td>\n",
       "      <td>52fe4284c3a36847f8024fa5</td>\n",
       "      <td>2</td>\n",
       "      <td>862</td>\n",
       "      <td>Wallace Shawn</td>\n",
       "      <td>4</td>\n",
       "      <td>/oGE6JqPP2xH4tNORKNqxbNPYi7u.jpg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>19</td>\n",
       "      <td>Hamm (voice)</td>\n",
       "      <td>52fe4284c3a36847f8024fa9</td>\n",
       "      <td>2</td>\n",
       "      <td>862</td>\n",
       "      <td>John Ratzenberger</td>\n",
       "      <td>5</td>\n",
       "      <td>/yGechiKWL6TJDfVE2KPSJYqdMsY.jpg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>20</td>\n",
       "      <td>Bo Peep (voice)</td>\n",
       "      <td>52fe4284c3a36847f8024fad</td>\n",
       "      <td>1</td>\n",
       "      <td>862</td>\n",
       "      <td>Annie Potts</td>\n",
       "      <td>6</td>\n",
       "      <td>/eryXT84RL41jHSJcMy4kS3u9y6w.jpg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>26</td>\n",
       "      <td>Andy (voice)</td>\n",
       "      <td>52fe4284c3a36847f8024fc1</td>\n",
       "      <td>0</td>\n",
       "      <td>862</td>\n",
       "      <td>John Morris</td>\n",
       "      <td>7</td>\n",
       "      <td>/vYGyvK4LzeaUCoNSHtsuqJUY15M.jpg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>22</td>\n",
       "      <td>Sid (voice)</td>\n",
       "      <td>52fe4284c3a36847f8024fb1</td>\n",
       "      <td>2</td>\n",
       "      <td>862</td>\n",
       "      <td>Erik von Detten</td>\n",
       "      <td>8</td>\n",
       "      <td>/twnF1ZaJ1FUNUuo6xLXwcxjayBE.jpg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>23</td>\n",
       "      <td>Mrs. Davis (voice)</td>\n",
       "      <td>52fe4284c3a36847f8024fb5</td>\n",
       "      <td>1</td>\n",
       "      <td>862</td>\n",
       "      <td>Laurie Metcalf</td>\n",
       "      <td>9</td>\n",
       "      <td>/unMMIT60eoBM2sN2nyR7EZ2BvvD.jpg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>24</td>\n",
       "      <td>Sergeant (voice)</td>\n",
       "      <td>52fe4284c3a36847f8024fb9</td>\n",
       "      <td>2</td>\n",
       "      <td>862</td>\n",
       "      <td>R. Lee Ermey</td>\n",
       "      <td>10</td>\n",
       "      <td>/r8GBqFBjypLUP9VVqDqfZ7wYbSs.jpg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>25</td>\n",
       "      <td>Hannah (voice)</td>\n",
       "      <td>52fe4284c3a36847f8024fbd</td>\n",
       "      <td>1</td>\n",
       "      <td>862</td>\n",
       "      <td>Sarah Freeman</td>\n",
       "      <td>11</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>27</td>\n",
       "      <td>TV Announcer (voice)</td>\n",
       "      <td>52fe4284c3a36847f8024fc5</td>\n",
       "      <td>2</td>\n",
       "      <td>862</td>\n",
       "      <td>Penn Jillette</td>\n",
       "      <td>12</td>\n",
       "      <td>/zmAaXUdx12NRsssgHbk1T31j2x9.jpg</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    cast_id                character                 credit_id  gender   id  \\\n",
       "0        14            Woody (voice)  52fe4284c3a36847f8024f95       2  862   \n",
       "1        15   Buzz Lightyear (voice)  52fe4284c3a36847f8024f99       2  862   \n",
       "2        16  Mr. Potato Head (voice)  52fe4284c3a36847f8024f9d       2  862   \n",
       "3        17       Slinky Dog (voice)  52fe4284c3a36847f8024fa1       2  862   \n",
       "4        18              Rex (voice)  52fe4284c3a36847f8024fa5       2  862   \n",
       "5        19             Hamm (voice)  52fe4284c3a36847f8024fa9       2  862   \n",
       "6        20          Bo Peep (voice)  52fe4284c3a36847f8024fad       1  862   \n",
       "7        26             Andy (voice)  52fe4284c3a36847f8024fc1       0  862   \n",
       "8        22              Sid (voice)  52fe4284c3a36847f8024fb1       2  862   \n",
       "9        23       Mrs. Davis (voice)  52fe4284c3a36847f8024fb5       1  862   \n",
       "10       24         Sergeant (voice)  52fe4284c3a36847f8024fb9       2  862   \n",
       "11       25           Hannah (voice)  52fe4284c3a36847f8024fbd       1  862   \n",
       "12       27     TV Announcer (voice)  52fe4284c3a36847f8024fc5       2  862   \n",
       "\n",
       "                 name  order                      profile_path  \n",
       "0           Tom Hanks      0  /pQFoyx7rp09CJTAb932F2g8Nlho.jpg  \n",
       "1           Tim Allen      1  /uX2xVf6pMmPepxnvFWyBtjexzgY.jpg  \n",
       "2         Don Rickles      2  /h5BcaDMPRVLHLDzbQavec4xfSdt.jpg  \n",
       "3          Jim Varney      3  /eIo2jVVXYgjDtaHoF19Ll9vtW7h.jpg  \n",
       "4       Wallace Shawn      4  /oGE6JqPP2xH4tNORKNqxbNPYi7u.jpg  \n",
       "5   John Ratzenberger      5  /yGechiKWL6TJDfVE2KPSJYqdMsY.jpg  \n",
       "6         Annie Potts      6  /eryXT84RL41jHSJcMy4kS3u9y6w.jpg  \n",
       "7         John Morris      7  /vYGyvK4LzeaUCoNSHtsuqJUY15M.jpg  \n",
       "8     Erik von Detten      8  /twnF1ZaJ1FUNUuo6xLXwcxjayBE.jpg  \n",
       "9      Laurie Metcalf      9  /unMMIT60eoBM2sN2nyR7EZ2BvvD.jpg  \n",
       "10       R. Lee Ermey     10  /r8GBqFBjypLUP9VVqDqfZ7wYbSs.jpg  \n",
       "11      Sarah Freeman     11                              None  \n",
       "12      Penn Jillette     12  /zmAaXUdx12NRsssgHbk1T31j2x9.jpg  "
      ]
     },
     "execution_count": 251,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# every row of this df can be considered a table \n",
    "# we need to extract every of these tables from every column \n",
    "\n",
    "pd.DataFrame(df_credits[\"cast\"][0]).assign(id = df_credits[\"id\"][0]) # assign() will add a new column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 252,
   "metadata": {},
   "outputs": [],
   "source": [
    "# to use the next loop we need to reset the index\n",
    "\n",
    "df_credits = df_credits.reset_index(drop=True) # drop=True because we don't need the old index as a new column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 253,
   "metadata": {},
   "outputs": [],
   "source": [
    "list_of_df_cast = [] # list for cast column\n",
    "for i in range(len(df_credits)): # for every row\n",
    "    dfi = pd.DataFrame(df_credits[\"cast\"][i]).assign(id = df_credits[\"id\"][i]) # create a df\n",
    "    list_of_df_cast.append(dfi) # and every df will be stored in a list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 254,
   "metadata": {},
   "outputs": [],
   "source": [
    "# ignore_index will ignore the index of original df's, assign new ones and guarantee continuous index\n",
    "\n",
    "df_cast = pd.concat(list_of_df_cast, ignore_index=True) # union of df's in the list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 255,
   "metadata": {},
   "outputs": [
    {
     "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_id</th>\n",
       "      <th>character</th>\n",
       "      <th>credit_id</th>\n",
       "      <th>gender</th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>order</th>\n",
       "      <th>profile_path</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>14.0</td>\n",
       "      <td>Woody (voice)</td>\n",
       "      <td>52fe4284c3a36847f8024f95</td>\n",
       "      <td>2.0</td>\n",
       "      <td>862</td>\n",
       "      <td>Tom Hanks</td>\n",
       "      <td>0.0</td>\n",
       "      <td>/pQFoyx7rp09CJTAb932F2g8Nlho.jpg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>15.0</td>\n",
       "      <td>Buzz Lightyear (voice)</td>\n",
       "      <td>52fe4284c3a36847f8024f99</td>\n",
       "      <td>2.0</td>\n",
       "      <td>862</td>\n",
       "      <td>Tim Allen</td>\n",
       "      <td>1.0</td>\n",
       "      <td>/uX2xVf6pMmPepxnvFWyBtjexzgY.jpg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>16.0</td>\n",
       "      <td>Mr. Potato Head (voice)</td>\n",
       "      <td>52fe4284c3a36847f8024f9d</td>\n",
       "      <td>2.0</td>\n",
       "      <td>862</td>\n",
       "      <td>Don Rickles</td>\n",
       "      <td>2.0</td>\n",
       "      <td>/h5BcaDMPRVLHLDzbQavec4xfSdt.jpg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>17.0</td>\n",
       "      <td>Slinky Dog (voice)</td>\n",
       "      <td>52fe4284c3a36847f8024fa1</td>\n",
       "      <td>2.0</td>\n",
       "      <td>862</td>\n",
       "      <td>Jim Varney</td>\n",
       "      <td>3.0</td>\n",
       "      <td>/eIo2jVVXYgjDtaHoF19Ll9vtW7h.jpg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>18.0</td>\n",
       "      <td>Rex (voice)</td>\n",
       "      <td>52fe4284c3a36847f8024fa5</td>\n",
       "      <td>2.0</td>\n",
       "      <td>862</td>\n",
       "      <td>Wallace Shawn</td>\n",
       "      <td>4.0</td>\n",
       "      <td>/oGE6JqPP2xH4tNORKNqxbNPYi7u.jpg</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   cast_id                character                 credit_id  gender   id  \\\n",
       "0     14.0            Woody (voice)  52fe4284c3a36847f8024f95     2.0  862   \n",
       "1     15.0   Buzz Lightyear (voice)  52fe4284c3a36847f8024f99     2.0  862   \n",
       "2     16.0  Mr. Potato Head (voice)  52fe4284c3a36847f8024f9d     2.0  862   \n",
       "3     17.0       Slinky Dog (voice)  52fe4284c3a36847f8024fa1     2.0  862   \n",
       "4     18.0              Rex (voice)  52fe4284c3a36847f8024fa5     2.0  862   \n",
       "\n",
       "            name  order                      profile_path  \n",
       "0      Tom Hanks    0.0  /pQFoyx7rp09CJTAb932F2g8Nlho.jpg  \n",
       "1      Tim Allen    1.0  /uX2xVf6pMmPepxnvFWyBtjexzgY.jpg  \n",
       "2    Don Rickles    2.0  /h5BcaDMPRVLHLDzbQavec4xfSdt.jpg  \n",
       "3     Jim Varney    3.0  /eIo2jVVXYgjDtaHoF19Ll9vtW7h.jpg  \n",
       "4  Wallace Shawn    4.0  /oGE6JqPP2xH4tNORKNqxbNPYi7u.jpg  "
      ]
     },
     "execution_count": 255,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_cast.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 256,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_cast.drop(columns=[\"order\", \"profile_path\", \"cast_id\", \"credit_id\"], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 258,
   "metadata": {},
   "outputs": [
    {
     "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>character</th>\n",
       "      <th>gender</th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>194631</th>\n",
       "      <td></td>\n",
       "      <td>0.0</td>\n",
       "      <td>25755</td>\n",
       "      <td>Akranee Inyotha</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>194632</th>\n",
       "      <td></td>\n",
       "      <td>0.0</td>\n",
       "      <td>25755</td>\n",
       "      <td>Akranee Inyotha</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>341644</th>\n",
       "      <td>Himself - Interviewee</td>\n",
       "      <td>0.0</td>\n",
       "      <td>116495</td>\n",
       "      <td>Alexander Nemerov</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>341645</th>\n",
       "      <td>Himself - Interviewee</td>\n",
       "      <td>0.0</td>\n",
       "      <td>116495</td>\n",
       "      <td>Alexander Nemerov</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201170</th>\n",
       "      <td></td>\n",
       "      <td>0.0</td>\n",
       "      <td>15957</td>\n",
       "      <td>Anne Møen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>440181</th>\n",
       "      <td></td>\n",
       "      <td>0.0</td>\n",
       "      <td>163428</td>\n",
       "      <td>Yahyo Fayzullayev</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157447</th>\n",
       "      <td>Ebirah</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3115</td>\n",
       "      <td>Yû Sekita</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157448</th>\n",
       "      <td>Ebirah</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3115</td>\n",
       "      <td>Yû Sekita</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>461091</th>\n",
       "      <td>Veterinary assistant</td>\n",
       "      <td>0.0</td>\n",
       "      <td>336222</td>\n",
       "      <td>Þorgerður Sigurgeirsdóttir</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>461092</th>\n",
       "      <td>Veterinary assistant</td>\n",
       "      <td>0.0</td>\n",
       "      <td>336222</td>\n",
       "      <td>Þorgerður Sigurgeirsdóttir</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>183 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                    character  gender      id                        name\n",
       "194631                            0.0   25755             Akranee Inyotha\n",
       "194632                            0.0   25755             Akranee Inyotha\n",
       "341644  Himself - Interviewee     0.0  116495           Alexander Nemerov\n",
       "341645  Himself - Interviewee     0.0  116495           Alexander Nemerov\n",
       "201170                            0.0   15957                   Anne Møen\n",
       "...                       ...     ...     ...                         ...\n",
       "440181                            0.0  163428           Yahyo Fayzullayev\n",
       "157447                 Ebirah     0.0    3115                   Yû Sekita\n",
       "157448                 Ebirah     0.0    3115                   Yû Sekita\n",
       "461091   Veterinary assistant     0.0  336222  Þorgerður Sigurgeirsdóttir\n",
       "461092   Veterinary assistant     0.0  336222  Þorgerður Sigurgeirsdóttir\n",
       "\n",
       "[183 rows x 4 columns]"
      ]
     },
     "execution_count": 258,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_cast[df_cast.duplicated(keep=False)].sort_values(by=\"name\") # checking for more duplicates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 259,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_cast.drop_duplicates(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 260,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "character    0\n",
       "gender       0\n",
       "id           0\n",
       "name         0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 260,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_cast.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 261,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(561942, 4)"
      ]
     },
     "execution_count": 261,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_cast.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 262,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_cast.to_csv(\"../processed_data/cast.csv\", index=False)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Crew Table"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Repeat the task with the crew column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 263,
   "metadata": {},
   "outputs": [],
   "source": [
    "list_of_df_crew = []\n",
    "for i in range(len(df_credits)):\n",
    "    dfi = pd.DataFrame(df_credits[\"crew\"][i]).assign(id = df_credits[\"id\"][i])\n",
    "    list_of_df_crew.append(dfi)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 264,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_crew = pd.concat(list_of_df_crew, ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 265,
   "metadata": {},
   "outputs": [
    {
     "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>credit_id</th>\n",
       "      <th>department</th>\n",
       "      <th>gender</th>\n",
       "      <th>id</th>\n",
       "      <th>job</th>\n",
       "      <th>name</th>\n",
       "      <th>profile_path</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>52fe4284c3a36847f8024f49</td>\n",
       "      <td>Directing</td>\n",
       "      <td>2.0</td>\n",
       "      <td>862</td>\n",
       "      <td>Director</td>\n",
       "      <td>John Lasseter</td>\n",
       "      <td>/7EdqiNbr4FRjIhKHyPPdFfEEEFG.jpg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>52fe4284c3a36847f8024f4f</td>\n",
       "      <td>Writing</td>\n",
       "      <td>2.0</td>\n",
       "      <td>862</td>\n",
       "      <td>Screenplay</td>\n",
       "      <td>Joss Whedon</td>\n",
       "      <td>/dTiVsuaTVTeGmvkhcyJvKp2A5kr.jpg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>52fe4284c3a36847f8024f55</td>\n",
       "      <td>Writing</td>\n",
       "      <td>2.0</td>\n",
       "      <td>862</td>\n",
       "      <td>Screenplay</td>\n",
       "      <td>Andrew Stanton</td>\n",
       "      <td>/pvQWsu0qc8JFQhMVJkTHuexUAa1.jpg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>52fe4284c3a36847f8024f5b</td>\n",
       "      <td>Writing</td>\n",
       "      <td>2.0</td>\n",
       "      <td>862</td>\n",
       "      <td>Screenplay</td>\n",
       "      <td>Joel Cohen</td>\n",
       "      <td>/dAubAiZcvKFbboWlj7oXOkZnTSu.jpg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>52fe4284c3a36847f8024f61</td>\n",
       "      <td>Writing</td>\n",
       "      <td>0.0</td>\n",
       "      <td>862</td>\n",
       "      <td>Screenplay</td>\n",
       "      <td>Alec Sokolow</td>\n",
       "      <td>/v79vlRYi94BZUQnkkyznbGUZLjT.jpg</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  credit_id department  gender   id         job  \\\n",
       "0  52fe4284c3a36847f8024f49  Directing     2.0  862    Director   \n",
       "1  52fe4284c3a36847f8024f4f    Writing     2.0  862  Screenplay   \n",
       "2  52fe4284c3a36847f8024f55    Writing     2.0  862  Screenplay   \n",
       "3  52fe4284c3a36847f8024f5b    Writing     2.0  862  Screenplay   \n",
       "4  52fe4284c3a36847f8024f61    Writing     0.0  862  Screenplay   \n",
       "\n",
       "             name                      profile_path  \n",
       "0   John Lasseter  /7EdqiNbr4FRjIhKHyPPdFfEEEFG.jpg  \n",
       "1     Joss Whedon  /dTiVsuaTVTeGmvkhcyJvKp2A5kr.jpg  \n",
       "2  Andrew Stanton  /pvQWsu0qc8JFQhMVJkTHuexUAa1.jpg  \n",
       "3      Joel Cohen  /dAubAiZcvKFbboWlj7oXOkZnTSu.jpg  \n",
       "4    Alec Sokolow  /v79vlRYi94BZUQnkkyznbGUZLjT.jpg  "
      ]
     },
     "execution_count": 265,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_crew.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 266,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_crew.drop(columns=[\"profile_path\", \"credit_id\"], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 267,
   "metadata": {},
   "outputs": [
    {
     "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>department</th>\n",
       "      <th>gender</th>\n",
       "      <th>id</th>\n",
       "      <th>job</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>251497</th>\n",
       "      <td>Visual Effects</td>\n",
       "      <td>0.0</td>\n",
       "      <td>72105</td>\n",
       "      <td>Animation</td>\n",
       "      <td>Aaron Clement</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>251498</th>\n",
       "      <td>Visual Effects</td>\n",
       "      <td>0.0</td>\n",
       "      <td>72105</td>\n",
       "      <td>Animation</td>\n",
       "      <td>Aaron Clement</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>416833</th>\n",
       "      <td>Production</td>\n",
       "      <td>0.0</td>\n",
       "      <td>359641</td>\n",
       "      <td>Producer</td>\n",
       "      <td>Alejandro Israel</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>416834</th>\n",
       "      <td>Production</td>\n",
       "      <td>0.0</td>\n",
       "      <td>359641</td>\n",
       "      <td>Producer</td>\n",
       "      <td>Alejandro Israel</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>425212</th>\n",
       "      <td>Writing</td>\n",
       "      <td>0.0</td>\n",
       "      <td>405040</td>\n",
       "      <td>Writer</td>\n",
       "      <td>Andi Awwe Wijaya</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>274697</th>\n",
       "      <td>Art</td>\n",
       "      <td>0.0</td>\n",
       "      <td>48319</td>\n",
       "      <td>Production Design</td>\n",
       "      <td>Wendy Samuels</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>455458</th>\n",
       "      <td>Art</td>\n",
       "      <td>0.0</td>\n",
       "      <td>49379</td>\n",
       "      <td>Production Design</td>\n",
       "      <td>William Hoffman</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>455459</th>\n",
       "      <td>Art</td>\n",
       "      <td>0.0</td>\n",
       "      <td>49379</td>\n",
       "      <td>Production Design</td>\n",
       "      <td>William Hoffman</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>304831</th>\n",
       "      <td>Visual Effects</td>\n",
       "      <td>0.0</td>\n",
       "      <td>17337</td>\n",
       "      <td>Animation</td>\n",
       "      <td>Yoon Hyo Sook</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>304829</th>\n",
       "      <td>Visual Effects</td>\n",
       "      <td>0.0</td>\n",
       "      <td>17337</td>\n",
       "      <td>Animation</td>\n",
       "      <td>Yoon Hyo Sook</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>238 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            department  gender      id                job              name\n",
       "251497  Visual Effects     0.0   72105          Animation     Aaron Clement\n",
       "251498  Visual Effects     0.0   72105          Animation     Aaron Clement\n",
       "416833      Production     0.0  359641           Producer  Alejandro Israel\n",
       "416834      Production     0.0  359641           Producer  Alejandro Israel\n",
       "425212         Writing     0.0  405040             Writer  Andi Awwe Wijaya\n",
       "...                ...     ...     ...                ...               ...\n",
       "274697             Art     0.0   48319  Production Design     Wendy Samuels\n",
       "455458             Art     0.0   49379  Production Design   William Hoffman\n",
       "455459             Art     0.0   49379  Production Design   William Hoffman\n",
       "304831  Visual Effects     0.0   17337          Animation     Yoon Hyo Sook\n",
       "304829  Visual Effects     0.0   17337          Animation     Yoon Hyo Sook\n",
       "\n",
       "[238 rows x 5 columns]"
      ]
     },
     "execution_count": 267,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_crew[df_crew.duplicated(keep=False)].sort_values(by=\"name\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 268,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_crew.drop_duplicates(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 269,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "department    0\n",
       "gender        0\n",
       "id            0\n",
       "job           0\n",
       "name          0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 269,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_crew.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 270,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(463706, 5)"
      ]
     },
     "execution_count": 270,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_crew.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 271,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_crew.to_csv(\"../processed_data/crew.csv\", index=False)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Creating Extra Datasets"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To complete this project, movies, cast and crew are more than enough, but building the API functions will be a lot easier with two extra tables. The First one will be financial data per actor."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_movies[\"id\"] = [int(x) for x in df_movies[\"id\"]] # \"id\" is type object, needs to be int to merge"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 273,
   "metadata": {},
   "outputs": [
    {
     "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>character</th>\n",
       "      <th>gender</th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>title</th>\n",
       "      <th>budget</th>\n",
       "      <th>revenue</th>\n",
       "      <th>return</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Woody (voice)</td>\n",
       "      <td>2.0</td>\n",
       "      <td>862</td>\n",
       "      <td>Tom Hanks</td>\n",
       "      <td>Toy Story</td>\n",
       "      <td>30000000.0</td>\n",
       "      <td>373554033.0</td>\n",
       "      <td>12.451801</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Buzz Lightyear (voice)</td>\n",
       "      <td>2.0</td>\n",
       "      <td>862</td>\n",
       "      <td>Tim Allen</td>\n",
       "      <td>Toy Story</td>\n",
       "      <td>30000000.0</td>\n",
       "      <td>373554033.0</td>\n",
       "      <td>12.451801</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mr. Potato Head (voice)</td>\n",
       "      <td>2.0</td>\n",
       "      <td>862</td>\n",
       "      <td>Don Rickles</td>\n",
       "      <td>Toy Story</td>\n",
       "      <td>30000000.0</td>\n",
       "      <td>373554033.0</td>\n",
       "      <td>12.451801</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Slinky Dog (voice)</td>\n",
       "      <td>2.0</td>\n",
       "      <td>862</td>\n",
       "      <td>Jim Varney</td>\n",
       "      <td>Toy Story</td>\n",
       "      <td>30000000.0</td>\n",
       "      <td>373554033.0</td>\n",
       "      <td>12.451801</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Rex (voice)</td>\n",
       "      <td>2.0</td>\n",
       "      <td>862</td>\n",
       "      <td>Wallace Shawn</td>\n",
       "      <td>Toy Story</td>\n",
       "      <td>30000000.0</td>\n",
       "      <td>373554033.0</td>\n",
       "      <td>12.451801</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>561937</th>\n",
       "      <td></td>\n",
       "      <td>2.0</td>\n",
       "      <td>227506</td>\n",
       "      <td>Iwan Mosschuchin</td>\n",
       "      <td>Satan Triumphant</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>561938</th>\n",
       "      <td></td>\n",
       "      <td>1.0</td>\n",
       "      <td>227506</td>\n",
       "      <td>Nathalie Lissenko</td>\n",
       "      <td>Satan Triumphant</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>561939</th>\n",
       "      <td></td>\n",
       "      <td>2.0</td>\n",
       "      <td>227506</td>\n",
       "      <td>Pavel Pavlov</td>\n",
       "      <td>Satan Triumphant</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>561940</th>\n",
       "      <td></td>\n",
       "      <td>0.0</td>\n",
       "      <td>227506</td>\n",
       "      <td>Aleksandr Chabrov</td>\n",
       "      <td>Satan Triumphant</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>561941</th>\n",
       "      <td></td>\n",
       "      <td>1.0</td>\n",
       "      <td>227506</td>\n",
       "      <td>Vera Orlova</td>\n",
       "      <td>Satan Triumphant</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>561942 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                      character  gender      id               name  \\\n",
       "0                 Woody (voice)     2.0     862          Tom Hanks   \n",
       "1        Buzz Lightyear (voice)     2.0     862          Tim Allen   \n",
       "2       Mr. Potato Head (voice)     2.0     862        Don Rickles   \n",
       "3            Slinky Dog (voice)     2.0     862         Jim Varney   \n",
       "4                   Rex (voice)     2.0     862      Wallace Shawn   \n",
       "...                         ...     ...     ...                ...   \n",
       "561937                              2.0  227506   Iwan Mosschuchin   \n",
       "561938                              1.0  227506  Nathalie Lissenko   \n",
       "561939                              2.0  227506       Pavel Pavlov   \n",
       "561940                              0.0  227506  Aleksandr Chabrov   \n",
       "561941                              1.0  227506        Vera Orlova   \n",
       "\n",
       "                   title      budget      revenue     return  \n",
       "0              Toy Story  30000000.0  373554033.0  12.451801  \n",
       "1              Toy Story  30000000.0  373554033.0  12.451801  \n",
       "2              Toy Story  30000000.0  373554033.0  12.451801  \n",
       "3              Toy Story  30000000.0  373554033.0  12.451801  \n",
       "4              Toy Story  30000000.0  373554033.0  12.451801  \n",
       "...                  ...         ...          ...        ...  \n",
       "561937  Satan Triumphant         0.0          0.0   0.000000  \n",
       "561938  Satan Triumphant         0.0          0.0   0.000000  \n",
       "561939  Satan Triumphant         0.0          0.0   0.000000  \n",
       "561940  Satan Triumphant         0.0          0.0   0.000000  \n",
       "561941  Satan Triumphant         0.0          0.0   0.000000  \n",
       "\n",
       "[561942 rows x 8 columns]"
      ]
     },
     "execution_count": 273,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# merging the cast and movies dataset to get the financial information of every actor\n",
    "\n",
    "join_actor = df_cast.merge(df_movies[[\"id\", \"title\", \"budget\", \"revenue\", \"return\"]], how=\"left\", on=\"id\")  \n",
    "join_actor\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 274,
   "metadata": {},
   "outputs": [
    {
     "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>name</th>\n",
       "      <th>budget</th>\n",
       "      <th>revenue</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Stan Lee</td>\n",
       "      <td>4.889500e+09</td>\n",
       "      <td>1.941496e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Samuel L. Jackson</td>\n",
       "      <td>4.855665e+09</td>\n",
       "      <td>1.710962e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Johnny Depp</td>\n",
       "      <td>3.840269e+09</td>\n",
       "      <td>1.065376e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Morgan Freeman</td>\n",
       "      <td>3.331000e+09</td>\n",
       "      <td>9.537209e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Bruce Willis</td>\n",
       "      <td>3.304445e+09</td>\n",
       "      <td>8.372937e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202742</th>\n",
       "      <td>Jean-Pierre Jeunet</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202743</th>\n",
       "      <td>Jean-Pierre Klein</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202744</th>\n",
       "      <td>Jean-Pierre Lazzerini</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202745</th>\n",
       "      <td>Jean-Pierre Leclerc</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202746</th>\n",
       "      <td>조병만</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>202747 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                         name        budget       revenue\n",
       "0                    Stan Lee  4.889500e+09  1.941496e+10\n",
       "1           Samuel L. Jackson  4.855665e+09  1.710962e+10\n",
       "2                 Johnny Depp  3.840269e+09  1.065376e+10\n",
       "3              Morgan Freeman  3.331000e+09  9.537209e+09\n",
       "4                Bruce Willis  3.304445e+09  8.372937e+09\n",
       "...                       ...           ...           ...\n",
       "202742     Jean-Pierre Jeunet  0.000000e+00  0.000000e+00\n",
       "202743      Jean-Pierre Klein  0.000000e+00  0.000000e+00\n",
       "202744  Jean-Pierre Lazzerini  0.000000e+00  0.000000e+00\n",
       "202745    Jean-Pierre Leclerc  0.000000e+00  0.000000e+00\n",
       "202746                    조병만  0.000000e+00  0.000000e+00\n",
       "\n",
       "[202747 rows x 3 columns]"
      ]
     },
     "execution_count": 274,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# grouping the names of actors to get the total budget and total revenue\n",
    "\n",
    "budget_actor = join_actor.groupby(\"name\")[\"budget\"].sum().sort_values(ascending=False).reset_index() # needs reset_index() to convert it to pandas dataframe\n",
    "revenue_actor = join_actor.groupby(\"name\")[\"revenue\"].sum().sort_values(ascending=False).reset_index()\n",
    "\n",
    "# merge this two new df's so all the info is in one table \n",
    "actor_financial = budget_actor.merge(revenue_actor, how=\"left\", on=\"name\")\n",
    "actor_financial\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 275,
   "metadata": {},
   "outputs": [],
   "source": [
    "# total return = total revenue / total budget, except if budget is zero\n",
    "\n",
    "actor_financial[\"total_return\"] = np.where(actor_financial[\"budget\"] != 0, actor_financial[\"revenue\"] / actor_financial[\"budget\"], 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 276,
   "metadata": {},
   "outputs": [
    {
     "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>name</th>\n",
       "      <th>return</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Juana Sutton</td>\n",
       "      <td>8500000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Cecil Reynolds</td>\n",
       "      <td>4250020.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Steve Taschler</td>\n",
       "      <td>4197476.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Camila Risser</td>\n",
       "      <td>2755584.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Giovana Fuentes</td>\n",
       "      <td>2755584.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202742</th>\n",
       "      <td>Taylor Brahms</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202743</th>\n",
       "      <td>Virgil Riley Runnels, Jr.</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202744</th>\n",
       "      <td>Wambli BearRunner</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202745</th>\n",
       "      <td>Yellow Pony Pettibone</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202746</th>\n",
       "      <td>Zoe Bournelis</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>202747 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                             name      return\n",
       "0                    Juana Sutton  8500000.00\n",
       "1                  Cecil Reynolds  4250020.62\n",
       "2                  Steve Taschler  4197476.62\n",
       "3                   Camila Risser  2755584.00\n",
       "4                 Giovana Fuentes  2755584.00\n",
       "...                           ...         ...\n",
       "202742              Taylor Brahms         NaN\n",
       "202743  Virgil Riley Runnels, Jr.         NaN\n",
       "202744          Wambli BearRunner         NaN\n",
       "202745      Yellow Pony Pettibone         NaN\n",
       "202746              Zoe Bournelis         NaN\n",
       "\n",
       "[202747 rows x 2 columns]"
      ]
     },
     "execution_count": 276,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# grouping the actor's name but we calculate the mean return using every movie they've participated\n",
    "\n",
    "actor_avg_return = join_actor.groupby(\"name\")[\"return\"].mean().round(2).sort_values(ascending=False).reset_index()\n",
    "actor_avg_return"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 277,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merging the table with average return with the main table\n",
    "\n",
    "actor_financial = actor_financial.merge(actor_avg_return, how=\"left\", on=\"name\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 278,
   "metadata": {},
   "outputs": [
    {
     "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>name</th>\n",
       "      <th>title</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bess Flowers</td>\n",
       "      <td>241</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Christopher Lee</td>\n",
       "      <td>148</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>John Wayne</td>\n",
       "      <td>125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Samuel L. Jackson</td>\n",
       "      <td>123</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Gérard Depardieu</td>\n",
       "      <td>110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202742</th>\n",
       "      <td>Mahesh Elkunchwar</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202743</th>\n",
       "      <td>Elham Korda</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202744</th>\n",
       "      <td>Charlie Semine</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202745</th>\n",
       "      <td>Ben Gaule</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202746</th>\n",
       "      <td>Kourosh Tahami</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>202747 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     name  title\n",
       "0            Bess Flowers    241\n",
       "1         Christopher Lee    148\n",
       "2              John Wayne    125\n",
       "3       Samuel L. Jackson    123\n",
       "4        Gérard Depardieu    110\n",
       "...                   ...    ...\n",
       "202742  Mahesh Elkunchwar      0\n",
       "202743        Elham Korda      0\n",
       "202744     Charlie Semine      0\n",
       "202745          Ben Gaule      0\n",
       "202746     Kourosh Tahami      0\n",
       "\n",
       "[202747 rows x 2 columns]"
      ]
     },
     "execution_count": 278,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# grouping by name to get the number of films of every actor\n",
    "\n",
    "movies_actor = join_actor.groupby(\"name\")[\"title\"].count().sort_values(ascending=False).reset_index()\n",
    "movies_actor"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 279,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merging the table with number or movies with the main table\n",
    "\n",
    "actor_financial = actor_financial.merge(movies_actor, how=\"left\", on=\"name\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 280,
   "metadata": {},
   "outputs": [
    {
     "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>name</th>\n",
       "      <th>total_budget</th>\n",
       "      <th>total_revenue</th>\n",
       "      <th>total_return</th>\n",
       "      <th>average_return</th>\n",
       "      <th>films</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Stan Lee</td>\n",
       "      <td>4.889500e+09</td>\n",
       "      <td>1.941496e+10</td>\n",
       "      <td>3.970745</td>\n",
       "      <td>2.42</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Samuel L. Jackson</td>\n",
       "      <td>4.855665e+09</td>\n",
       "      <td>1.710962e+10</td>\n",
       "      <td>3.523641</td>\n",
       "      <td>1.94</td>\n",
       "      <td>123</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Johnny Depp</td>\n",
       "      <td>3.840269e+09</td>\n",
       "      <td>1.065376e+10</td>\n",
       "      <td>2.774222</td>\n",
       "      <td>2.02</td>\n",
       "      <td>69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Morgan Freeman</td>\n",
       "      <td>3.331000e+09</td>\n",
       "      <td>9.537209e+09</td>\n",
       "      <td>2.863167</td>\n",
       "      <td>2.08</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Bruce Willis</td>\n",
       "      <td>3.304445e+09</td>\n",
       "      <td>8.372937e+09</td>\n",
       "      <td>2.533841</td>\n",
       "      <td>2.58</td>\n",
       "      <td>91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202742</th>\n",
       "      <td>Jean-Pierre Jeunet</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202743</th>\n",
       "      <td>Jean-Pierre Klein</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202744</th>\n",
       "      <td>Jean-Pierre Lazzerini</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202745</th>\n",
       "      <td>Jean-Pierre Leclerc</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202746</th>\n",
       "      <td>조병만</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>202747 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                         name  total_budget  total_revenue  total_return  \\\n",
       "0                    Stan Lee  4.889500e+09   1.941496e+10      3.970745   \n",
       "1           Samuel L. Jackson  4.855665e+09   1.710962e+10      3.523641   \n",
       "2                 Johnny Depp  3.840269e+09   1.065376e+10      2.774222   \n",
       "3              Morgan Freeman  3.331000e+09   9.537209e+09      2.863167   \n",
       "4                Bruce Willis  3.304445e+09   8.372937e+09      2.533841   \n",
       "...                       ...           ...            ...           ...   \n",
       "202742     Jean-Pierre Jeunet  0.000000e+00   0.000000e+00      0.000000   \n",
       "202743      Jean-Pierre Klein  0.000000e+00   0.000000e+00      0.000000   \n",
       "202744  Jean-Pierre Lazzerini  0.000000e+00   0.000000e+00      0.000000   \n",
       "202745    Jean-Pierre Leclerc  0.000000e+00   0.000000e+00      0.000000   \n",
       "202746                    조병만  0.000000e+00   0.000000e+00      0.000000   \n",
       "\n",
       "        average_return  films  \n",
       "0                 2.42     49  \n",
       "1                 1.94    123  \n",
       "2                 2.02     69  \n",
       "3                 2.08     85  \n",
       "4                 2.58     91  \n",
       "...                ...    ...  \n",
       "202742            0.00      1  \n",
       "202743            0.00      1  \n",
       "202744            0.00      2  \n",
       "202745            0.00      2  \n",
       "202746            0.00      1  \n",
       "\n",
       "[202747 rows x 6 columns]"
      ]
     },
     "execution_count": 280,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# renaming columns\n",
    "\n",
    "actor_financial = actor_financial.rename(columns={'budget': 'total_budget', 'revenue': 'total_revenue', 'return': 'average_return', 'title': 'films'})\n",
    "actor_financial"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 281,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "name               0\n",
       "total_budget       0\n",
       "total_revenue      0\n",
       "total_return       0\n",
       "average_return    47\n",
       "films              0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 281,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "actor_financial.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 282,
   "metadata": {},
   "outputs": [],
   "source": [
    "actor_financial.fillna(0, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 283,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 283,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "actor_financial.duplicated().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 187,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(202747, 6)"
      ]
     },
     "execution_count": 187,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "actor_financial.shape"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now getting financial data per director, doing similar transformations like the previous ones."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 284,
   "metadata": {},
   "outputs": [],
   "source": [
    "director_df = df_crew[df_crew[\"job\"] == \"Director\"] # filtering jobs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 285,
   "metadata": {},
   "outputs": [],
   "source": [
    "join_director = director_df.merge(df_movies[[\"id\", \"title\", \"budget\", \"revenue\", \"return\"]], how=\"left\", on=\"id\") \n",
    "budget_director = join_director.groupby(\"name\")[\"budget\"].sum().sort_values(ascending=False).reset_index()\n",
    "revenue_director = join_director.groupby(\"name\")[\"revenue\"].sum().sort_values(ascending=False).reset_index()\n",
    "director_financial = budget_director.merge(revenue_director, how=\"left\", on=\"name\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 286,
   "metadata": {},
   "outputs": [
    {
     "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>name</th>\n",
       "      <th>total_budget</th>\n",
       "      <th>total_revenue</th>\n",
       "      <th>total_return</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Steven Spielberg</td>\n",
       "      <td>1.736950e+09</td>\n",
       "      <td>9.256621e+09</td>\n",
       "      <td>5.329239</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Michael Bay</td>\n",
       "      <td>1.670000e+09</td>\n",
       "      <td>6.437467e+09</td>\n",
       "      <td>3.854771</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Ridley Scott</td>\n",
       "      <td>1.641900e+09</td>\n",
       "      <td>3.917529e+09</td>\n",
       "      <td>2.385973</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Peter Jackson</td>\n",
       "      <td>1.327426e+09</td>\n",
       "      <td>6.528245e+09</td>\n",
       "      <td>4.917974</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Ron Howard</td>\n",
       "      <td>1.285000e+09</td>\n",
       "      <td>3.714152e+09</td>\n",
       "      <td>2.890391</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19735</th>\n",
       "      <td>Ivo Caprino</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19736</th>\n",
       "      <td>Ivo Trajkov</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19737</th>\n",
       "      <td>Ivona Juka</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19738</th>\n",
       "      <td>Iván Dariel Ortiz</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19739</th>\n",
       "      <td>진모영</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>19740 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                    name  total_budget  total_revenue  total_return\n",
       "0       Steven Spielberg  1.736950e+09   9.256621e+09      5.329239\n",
       "1            Michael Bay  1.670000e+09   6.437467e+09      3.854771\n",
       "2           Ridley Scott  1.641900e+09   3.917529e+09      2.385973\n",
       "3          Peter Jackson  1.327426e+09   6.528245e+09      4.917974\n",
       "4             Ron Howard  1.285000e+09   3.714152e+09      2.890391\n",
       "...                  ...           ...            ...           ...\n",
       "19735        Ivo Caprino  0.000000e+00   0.000000e+00      0.000000\n",
       "19736        Ivo Trajkov  0.000000e+00   0.000000e+00      0.000000\n",
       "19737         Ivona Juka  0.000000e+00   0.000000e+00      0.000000\n",
       "19738  Iván Dariel Ortiz  0.000000e+00   0.000000e+00      0.000000\n",
       "19739                진모영  0.000000e+00   0.000000e+00      0.000000\n",
       "\n",
       "[19740 rows x 4 columns]"
      ]
     },
     "execution_count": 286,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "director_financial[\"total_return\"] = np.where(director_financial[\"budget\"] != 0, director_financial[\"revenue\"] / director_financial[\"budget\"], 0)\n",
    "director_financial = director_financial.rename(columns={'budget': 'total_budget', 'revenue': 'total_revenue'})\n",
    "director_financial"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 287,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "name             0\n",
       "total_budget     0\n",
       "total_revenue    0\n",
       "total_return     0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 287,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "director_financial.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 288,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 288,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "director_financial.duplicated().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 192,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(19740, 4)"
      ]
     },
     "execution_count": 192,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "director_financial.shape"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Extra Transformations"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that we have the clean datasets, we can do some transformations to ease the building of functions later."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_movies[\"return\"] = df_movies[\"return\"].round(2) # more readable numbers"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Adding this datetime transformation avoid incompatibility between locale library and remote servers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'C'"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import locale\n",
    "locale.setlocale(locale.LC_TIME) # setting a local configuration for dates values, locale.LC_TIME, 'es_ES' for spanish"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0         October\n",
       "1        December\n",
       "2        December\n",
       "3        December\n",
       "4        February\n",
       "           ...   \n",
       "45340         May\n",
       "45341    November\n",
       "45342      August\n",
       "45343     October\n",
       "45344        June\n",
       "Name: release_month, Length: 45345, dtype: object"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# '%B' complete name of the month, strftime is string format time, it allows to format date data to a desirable representation\n",
    "# dt only works with pandas series that are datetime type\n",
    "\n",
    "df_movies[\"release_month\"] = pd.to_datetime(df_movies[\"release_date\"]).dt.strftime('%B')\n",
    "df_movies[\"release_month\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0          Monday\n",
       "1          Friday\n",
       "2          Friday\n",
       "3          Friday\n",
       "4          Friday\n",
       "           ...   \n",
       "45340      Monday\n",
       "45341    Thursday\n",
       "45342      Friday\n",
       "45343      Sunday\n",
       "45344      Friday\n",
       "Name: release_day, Length: 45345, dtype: object"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# dt only works with pandas series that are datetime type\n",
    "# '%A' complete name of the day, strftime is string format time, it allows to format date data to a desirable representation\n",
    "\n",
    "df_movies[\"release_day\"] = pd.to_datetime(df_movies[\"release_date\"]).dt.strftime('%A') \n",
    "df_movies[\"release_day\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(45345, 21)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_movies.shape"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Adding useful columns for the recommendation model"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_movies = df_movies.merge(df_credits[[\"crew\", \"id\"]], how=\"left\", on=\"id\") "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "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>budget</th>\n",
       "      <th>id</th>\n",
       "      <th>original_language</th>\n",
       "      <th>overview</th>\n",
       "      <th>popularity</th>\n",
       "      <th>release_date</th>\n",
       "      <th>revenue</th>\n",
       "      <th>runtime</th>\n",
       "      <th>title</th>\n",
       "      <th>vote_average</th>\n",
       "      <th>...</th>\n",
       "      <th>collection</th>\n",
       "      <th>genres_list</th>\n",
       "      <th>spoken_languages_list</th>\n",
       "      <th>production_companies_list</th>\n",
       "      <th>production_countries_list</th>\n",
       "      <th>release_year</th>\n",
       "      <th>return</th>\n",
       "      <th>release_month</th>\n",
       "      <th>release_day</th>\n",
       "      <th>crew</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>42783</th>\n",
       "      <td>0.0</td>\n",
       "      <td>401840</td>\n",
       "      <td>es</td>\n",
       "      <td>Two high school kids mentored by a nightclub o...</td>\n",
       "      <td>0.207775</td>\n",
       "      <td>2017-05-30</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>School's out</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[]</td>\n",
       "      <td>[]</td>\n",
       "      <td>[]</td>\n",
       "      <td>[]</td>\n",
       "      <td>2017</td>\n",
       "      <td>0.0</td>\n",
       "      <td>mayo</td>\n",
       "      <td>martes</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       budget      id original_language  \\\n",
       "42783     0.0  401840                es   \n",
       "\n",
       "                                                overview popularity  \\\n",
       "42783  Two high school kids mentored by a nightclub o...   0.207775   \n",
       "\n",
       "      release_date  revenue  runtime         title  vote_average  ...  \\\n",
       "42783   2017-05-30      0.0      0.0  School's out           0.0  ...   \n",
       "\n",
       "       collection genres_list spoken_languages_list production_companies_list  \\\n",
       "42783         NaN          []                    []                        []   \n",
       "\n",
       "      production_countries_list release_year  return  release_month  \\\n",
       "42783                        []         2017     0.0           mayo   \n",
       "\n",
       "      release_day crew  \n",
       "42783      martes  NaN  \n",
       "\n",
       "[1 rows x 21 columns]"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# no much info here, its safe to drop this row\n",
    "\n",
    "df_movies[df_movies[\"crew\"].isna()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_movies.dropna(subset=\"crew\", inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['John Lasseter']"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# function to get directors in a list for every row in the column\n",
    "def get_directors(crew):\n",
    "    directors = []\n",
    "    for member in crew:\n",
    "        if member['job'] == 'Director':\n",
    "            directors.append(member['name'])\n",
    "    return directors\n",
    "\n",
    "get_directors(df_movies[\"crew\"][0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_movies[\"directors\"] = [get_directors(x) for x in df_movies[\"crew\"]]\n",
    "df_movies.drop(columns=\"crew\", inplace=True)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Saving CSV Files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Index False because we don't need the index in the csv file as an additional column\n",
    "df_movies.to_csv(\"../processed_data/movies.csv\", index=False) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [],
   "source": [
    "actor_financial.to_csv(\"../processed_data/actor_financial.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [],
   "source": [
    "director_financial.to_csv(\"../processed_data/director_financial.csv\", index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.11"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}