etl.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import ast\n",
    "\n",
    "from datetime import datetime"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# ETL to movies dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_492878/3456366765.py:3: DtypeWarning: Columns (10) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  df_movies = pd.read_csv(url)\n"
     ]
    }
   ],
   "source": [
    "url='https://drive.google.com/file/d/1yqB8ahhhdiONfZLu8-xuU9qsWOgSf5Oo/view?usp=sharing'\n",
    "url='https://drive.google.com/uc?id=' + url.split('/')[-2]\n",
    "df_movies = pd.read_csv(url)"
   ]
  },
  {
   "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>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",
       "    <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>1995-12-22</td>\n",
       "      <td>0.0</td>\n",
       "      <td>101.0</td>\n",
       "      <td>[{'iso_639_1': 'en', 'name': 'English'}]</td>\n",
       "      <td>Released</td>\n",
       "      <td>Still Yelling. Still Fighting. Still Ready for...</td>\n",
       "      <td>Grumpier Old Men</td>\n",
       "      <td>False</td>\n",
       "      <td>6.5</td>\n",
       "      <td>92.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>16000000</td>\n",
       "      <td>[{'id': 35, 'name': 'Comedy'}, {'id': 18, 'nam...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31357</td>\n",
       "      <td>tt0114885</td>\n",
       "      <td>en</td>\n",
       "      <td>Waiting to Exhale</td>\n",
       "      <td>Cheated on, mistreated and stepped on, the wom...</td>\n",
       "      <td>...</td>\n",
       "      <td>1995-12-22</td>\n",
       "      <td>81452156.0</td>\n",
       "      <td>127.0</td>\n",
       "      <td>[{'iso_639_1': 'en', 'name': 'English'}]</td>\n",
       "      <td>Released</td>\n",
       "      <td>Friends are the people who let you be yourself...</td>\n",
       "      <td>Waiting to Exhale</td>\n",
       "      <td>False</td>\n",
       "      <td>6.1</td>\n",
       "      <td>34.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>False</td>\n",
       "      <td>{'id': 96871, 'name': 'Father of the Bride Col...</td>\n",
       "      <td>0</td>\n",
       "      <td>[{'id': 35, 'name': 'Comedy'}]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11862</td>\n",
       "      <td>tt0113041</td>\n",
       "      <td>en</td>\n",
       "      <td>Father of the Bride Part II</td>\n",
       "      <td>Just when George Banks has recovered from his ...</td>\n",
       "      <td>...</td>\n",
       "      <td>1995-02-10</td>\n",
       "      <td>76578911.0</td>\n",
       "      <td>106.0</td>\n",
       "      <td>[{'iso_639_1': 'en', 'name': 'English'}]</td>\n",
       "      <td>Released</td>\n",
       "      <td>Just When His World Is Back To Normal... He's ...</td>\n",
       "      <td>Father of the Bride Part II</td>\n",
       "      <td>False</td>\n",
       "      <td>5.7</td>\n",
       "      <td>173.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 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",
       "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",
       "                                              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",
       "                               homepage     id    imdb_id original_language  \\\n",
       "0  http://toystory.disney.com/toy-story    862  tt0114709                en   \n",
       "1                                   NaN   8844  tt0113497                en   \n",
       "2                                   NaN  15602  tt0113228                en   \n",
       "3                                   NaN  31357  tt0114885                en   \n",
       "4                                   NaN  11862  tt0113041                en   \n",
       "\n",
       "                original_title  \\\n",
       "0                    Toy Story   \n",
       "1                      Jumanji   \n",
       "2             Grumpier Old Men   \n",
       "3            Waiting to Exhale   \n",
       "4  Father of the Bride Part II   \n",
       "\n",
       "                                            overview  ... release_date  \\\n",
       "0  Led by Woody, Andy's toys live happily in his ...  ...   1995-10-30   \n",
       "1  When siblings Judy and Peter discover an encha...  ...   1995-12-15   \n",
       "2  A family wedding reignites the ancient feud be...  ...   1995-12-22   \n",
       "3  Cheated on, mistreated and stepped on, the wom...  ...   1995-12-22   \n",
       "4  Just when George Banks has recovered from his ...  ...   1995-02-10   \n",
       "\n",
       "       revenue runtime                                   spoken_languages  \\\n",
       "0  373554033.0    81.0           [{'iso_639_1': 'en', 'name': 'English'}]   \n",
       "1  262797249.0   104.0  [{'iso_639_1': 'en', 'name': 'English'}, {'iso...   \n",
       "2          0.0   101.0           [{'iso_639_1': 'en', 'name': 'English'}]   \n",
       "3   81452156.0   127.0           [{'iso_639_1': 'en', 'name': 'English'}]   \n",
       "4   76578911.0   106.0           [{'iso_639_1': 'en', 'name': 'English'}]   \n",
       "\n",
       "     status                                            tagline  \\\n",
       "0  Released                                                NaN   \n",
       "1  Released          Roll the dice and unleash the excitement!   \n",
       "2  Released  Still Yelling. Still Fighting. Still Ready for...   \n",
       "3  Released  Friends are the people who let you be yourself...   \n",
       "4  Released  Just When His World Is Back To Normal... He's ...   \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",
       "[5 rows x 24 columns]"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_movies.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(45466, 24)"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_movies.shape"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Drop unuseful columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_movies.drop(columns=['video', 'imdb_id', 'adult', 'original_title', 'poster_path', 'homepage', 'tagline'], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 45466 entries, 0 to 45465\n",
      "Data columns (total 17 columns):\n",
      " #   Column                 Non-Null Count  Dtype  \n",
      "---  ------                 --------------  -----  \n",
      " 0   belongs_to_collection  4494 non-null   object \n",
      " 1   budget                 45466 non-null  object \n",
      " 2   genres                 45466 non-null  object \n",
      " 3   id                     45466 non-null  object \n",
      " 4   original_language      45455 non-null  object \n",
      " 5   overview               44512 non-null  object \n",
      " 6   popularity             45461 non-null  object \n",
      " 7   production_companies   45463 non-null  object \n",
      " 8   production_countries   45463 non-null  object \n",
      " 9   release_date           45379 non-null  object \n",
      " 10  revenue                45460 non-null  float64\n",
      " 11  runtime                45203 non-null  float64\n",
      " 12  spoken_languages       45460 non-null  object \n",
      " 13  status                 45379 non-null  object \n",
      " 14  title                  45460 non-null  object \n",
      " 15  vote_average           45460 non-null  float64\n",
      " 16  vote_count             45460 non-null  float64\n",
      "dtypes: float64(4), object(13)\n",
      "memory usage: 5.9+ MB\n"
     ]
    }
   ],
   "source": [
    "df_movies.info()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Fill NaN value with 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_movies['revenue'] = df_movies['revenue'].fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_movies['budget'] = pd.to_numeric(df_movies['budget'], errors='coerce').fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 45466 entries, 0 to 45465\n",
      "Data columns (total 17 columns):\n",
      " #   Column                 Non-Null Count  Dtype  \n",
      "---  ------                 --------------  -----  \n",
      " 0   belongs_to_collection  4494 non-null   object \n",
      " 1   budget                 45466 non-null  float64\n",
      " 2   genres                 45466 non-null  object \n",
      " 3   id                     45466 non-null  object \n",
      " 4   original_language      45455 non-null  object \n",
      " 5   overview               44512 non-null  object \n",
      " 6   popularity             45461 non-null  object \n",
      " 7   production_companies   45463 non-null  object \n",
      " 8   production_countries   45463 non-null  object \n",
      " 9   release_date           45379 non-null  object \n",
      " 10  revenue                45466 non-null  float64\n",
      " 11  runtime                45203 non-null  float64\n",
      " 12  spoken_languages       45460 non-null  object \n",
      " 13  status                 45379 non-null  object \n",
      " 14  title                  45460 non-null  object \n",
      " 15  vote_average           45460 non-null  float64\n",
      " 16  vote_count             45460 non-null  float64\n",
      "dtypes: float64(5), object(12)\n",
      "memory usage: 5.9+ MB\n"
     ]
    }
   ],
   "source": [
    "df_movies.info()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create `return` column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_movies['return'] = df_movies.apply(lambda x: x['revenue']/x['budget'] if x['budget'] != 0 else 0, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 45466 entries, 0 to 45465\n",
      "Data columns (total 18 columns):\n",
      " #   Column                 Non-Null Count  Dtype  \n",
      "---  ------                 --------------  -----  \n",
      " 0   belongs_to_collection  4494 non-null   object \n",
      " 1   budget                 45466 non-null  float64\n",
      " 2   genres                 45466 non-null  object \n",
      " 3   id                     45466 non-null  object \n",
      " 4   original_language      45455 non-null  object \n",
      " 5   overview               44512 non-null  object \n",
      " 6   popularity             45461 non-null  object \n",
      " 7   production_companies   45463 non-null  object \n",
      " 8   production_countries   45463 non-null  object \n",
      " 9   release_date           45379 non-null  object \n",
      " 10  revenue                45466 non-null  float64\n",
      " 11  runtime                45203 non-null  float64\n",
      " 12  spoken_languages       45460 non-null  object \n",
      " 13  status                 45379 non-null  object \n",
      " 14  title                  45460 non-null  object \n",
      " 15  vote_average           45460 non-null  float64\n",
      " 16  vote_count             45460 non-null  float64\n",
      " 17  return                 45466 non-null  float64\n",
      "dtypes: float64(6), object(12)\n",
      "memory usage: 6.2+ MB\n"
     ]
    }
   ],
   "source": [
    "df_movies.info()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create funtion to convert string to list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "def str_to_list(value, ret_val=[]):\n",
    "    \"\"\"Convert string to dictionary or list depending on value parameter.\n",
    "\n",
    "    Args:\n",
    "        value (str): String valu to be converted to dictionary.\n",
    "        ret_val (list): Value call depending, could be a dictionary or None.\n",
    "\n",
    "    Returns:\n",
    "        None : Return None/empty list if NaN value is present otherwise return a list or dict.\n",
    "    \"\"\"\n",
    "    if pd.isna(value):\n",
    "        return ret_val\n",
    "    else:\n",
    "        value = ast.literal_eval(value)\n",
    "        if isinstance(value, bool) or isinstance(value, float):\n",
    "            return ret_val\n",
    "        return value"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Function to get elements of list of list of dictionaries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_list(ret_val=[], dataframe='', column='', key_name=''):\n",
    "    \"\"\"Get the list of key elements from a dictionary.\n",
    "\n",
    "    Args:\n",
    "        ret_val (list): Value call depending, could be a dictionary or None.\n",
    "        dataframe (str, optional): Dataframe to extract information. Defaults to ''.\n",
    "        column (str, optional): Column of the dataframe to get information. Defaults to ''.\n",
    "        key_name (str, optional): Dictionary key to get value. Defaults to ''. \n",
    "\n",
    "    Returns:\n",
    "        List: List of list of elements of the dictionary\n",
    "    \"\"\"\n",
    "    dataframe[column] = dataframe[column].apply(str_to_list, ret_val=ret_val)\n",
    "    column_list = dataframe[column].to_list()\n",
    "\n",
    "    return_list = [[data[key_name] for data in inter_list if key_name in data] for inter_list in column_list]\n",
    "\n",
    "    return return_list"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Unnest columns with dictionaries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_movies['belongs_to_collection'] = df_movies['belongs_to_collection'].apply(str_to_list, ret_val=None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "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>id</th>\n",
       "      <th>name</th>\n",
       "      <th>poster_path</th>\n",
       "      <th>backdrop_path</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10194.0</td>\n",
       "      <td>Toy Story Collection</td>\n",
       "      <td>/7G9915LfUQ2lVfwMEEhDsn3kT4B.jpg</td>\n",
       "      <td>/9FBwqcd9IRruEDUrTdcaafOMKUq.jpg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>119050.0</td>\n",
       "      <td>Grumpy Old Men Collection</td>\n",
       "      <td>/nLvUdqgPgm3F85NMCii9gVFUcet.jpg</td>\n",
       "      <td>/hypTnLot2z8wpFS7qwsQHW1uV8u.jpg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>96871.0</td>\n",
       "      <td>Father of the Bride Collection</td>\n",
       "      <td>/nts4iOmNnq7GNicycMJ9pSAn204.jpg</td>\n",
       "      <td>/7qwE57OVZmMJChBpLEbJEmzUydk.jpg</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         id                            name                       poster_path  \\\n",
       "0   10194.0            Toy Story Collection  /7G9915LfUQ2lVfwMEEhDsn3kT4B.jpg   \n",
       "1                                                                               \n",
       "2  119050.0       Grumpy Old Men Collection  /nLvUdqgPgm3F85NMCii9gVFUcet.jpg   \n",
       "3                                                                               \n",
       "4   96871.0  Father of the Bride Collection  /nts4iOmNnq7GNicycMJ9pSAn204.jpg   \n",
       "\n",
       "                      backdrop_path  \n",
       "0  /9FBwqcd9IRruEDUrTdcaafOMKUq.jpg  \n",
       "1                                    \n",
       "2  /hypTnLot2z8wpFS7qwsQHW1uV8u.jpg  \n",
       "3                                    \n",
       "4  /7qwE57OVZmMJChBpLEbJEmzUydk.jpg  "
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_nested = pd.json_normalize(df_movies['belongs_to_collection']).fillna('')\n",
    "df_nested.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "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>belongs_to_collection_id</th>\n",
       "      <th>belongs_to_collection_name</th>\n",
       "      <th>belongs_to_collection_poster_path</th>\n",
       "      <th>belongs_to_collection_backdrop_path</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10194.0</td>\n",
       "      <td>Toy Story Collection</td>\n",
       "      <td>/7G9915LfUQ2lVfwMEEhDsn3kT4B.jpg</td>\n",
       "      <td>/9FBwqcd9IRruEDUrTdcaafOMKUq.jpg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>119050.0</td>\n",
       "      <td>Grumpy Old Men Collection</td>\n",
       "      <td>/nLvUdqgPgm3F85NMCii9gVFUcet.jpg</td>\n",
       "      <td>/hypTnLot2z8wpFS7qwsQHW1uV8u.jpg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>96871.0</td>\n",
       "      <td>Father of the Bride Collection</td>\n",
       "      <td>/nts4iOmNnq7GNicycMJ9pSAn204.jpg</td>\n",
       "      <td>/7qwE57OVZmMJChBpLEbJEmzUydk.jpg</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  belongs_to_collection_id      belongs_to_collection_name  \\\n",
       "0                  10194.0            Toy Story Collection   \n",
       "1                                                            \n",
       "2                 119050.0       Grumpy Old Men Collection   \n",
       "3                                                            \n",
       "4                  96871.0  Father of the Bride Collection   \n",
       "\n",
       "  belongs_to_collection_poster_path belongs_to_collection_backdrop_path  \n",
       "0  /7G9915LfUQ2lVfwMEEhDsn3kT4B.jpg    /9FBwqcd9IRruEDUrTdcaafOMKUq.jpg  \n",
       "1                                                                        \n",
       "2  /nLvUdqgPgm3F85NMCii9gVFUcet.jpg    /hypTnLot2z8wpFS7qwsQHW1uV8u.jpg  \n",
       "3                                                                        \n",
       "4  /nts4iOmNnq7GNicycMJ9pSAn204.jpg    /7qwE57OVZmMJChBpLEbJEmzUydk.jpg  "
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "columns = df_nested.columns\n",
    "new_columns_names = {column : f'belongs_to_collection_{column}' for column in columns}\n",
    "df_nested.rename(columns=new_columns_names, inplace=True)\n",
    "df_nested.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(45466, 4)"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_nested.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_nested.drop(columns=['belongs_to_collection_id', 'belongs_to_collection_poster_path', 'belongs_to_collection_backdrop_path'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "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>genres</th>\n",
       "      <th>id</th>\n",
       "      <th>original_language</th>\n",
       "      <th>overview</th>\n",
       "      <th>popularity</th>\n",
       "      <th>production_companies</th>\n",
       "      <th>production_countries</th>\n",
       "      <th>release_date</th>\n",
       "      <th>revenue</th>\n",
       "      <th>runtime</th>\n",
       "      <th>spoken_languages</th>\n",
       "      <th>status</th>\n",
       "      <th>title</th>\n",
       "      <th>vote_average</th>\n",
       "      <th>vote_count</th>\n",
       "      <th>return</th>\n",
       "      <th>belongs_to_collection_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>30000000.0</td>\n",
       "      <td>[{'id': 16, 'name': 'Animation'}, {'id': 35, '...</td>\n",
       "      <td>862</td>\n",
       "      <td>en</td>\n",
       "      <td>Led by Woody, Andy's toys live happily in his ...</td>\n",
       "      <td>21.946943</td>\n",
       "      <td>[{'name': 'Pixar Animation Studios', 'id': 3}]</td>\n",
       "      <td>[{'iso_3166_1': 'US', 'name': 'United States o...</td>\n",
       "      <td>1995-10-30</td>\n",
       "      <td>373554033.0</td>\n",
       "      <td>81.0</td>\n",
       "      <td>[{'iso_639_1': 'en', 'name': 'English'}]</td>\n",
       "      <td>Released</td>\n",
       "      <td>Toy Story</td>\n",
       "      <td>7.7</td>\n",
       "      <td>5415.0</td>\n",
       "      <td>12.451801</td>\n",
       "      <td>Toy Story Collection</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>65000000.0</td>\n",
       "      <td>[{'id': 12, 'name': 'Adventure'}, {'id': 14, '...</td>\n",
       "      <td>8844</td>\n",
       "      <td>en</td>\n",
       "      <td>When siblings Judy and Peter discover an encha...</td>\n",
       "      <td>17.015539</td>\n",
       "      <td>[{'name': 'TriStar Pictures', 'id': 559}, {'na...</td>\n",
       "      <td>[{'iso_3166_1': 'US', 'name': 'United States o...</td>\n",
       "      <td>1995-12-15</td>\n",
       "      <td>262797249.0</td>\n",
       "      <td>104.0</td>\n",
       "      <td>[{'iso_639_1': 'en', 'name': 'English'}, {'iso...</td>\n",
       "      <td>Released</td>\n",
       "      <td>Jumanji</td>\n",
       "      <td>6.9</td>\n",
       "      <td>2413.0</td>\n",
       "      <td>4.043035</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.0</td>\n",
       "      <td>[{'id': 10749, 'name': 'Romance'}, {'id': 35, ...</td>\n",
       "      <td>15602</td>\n",
       "      <td>en</td>\n",
       "      <td>A family wedding reignites the ancient feud be...</td>\n",
       "      <td>11.7129</td>\n",
       "      <td>[{'name': 'Warner Bros.', 'id': 6194}, {'name'...</td>\n",
       "      <td>[{'iso_3166_1': 'US', 'name': 'United States o...</td>\n",
       "      <td>1995-12-22</td>\n",
       "      <td>0.0</td>\n",
       "      <td>101.0</td>\n",
       "      <td>[{'iso_639_1': 'en', 'name': 'English'}]</td>\n",
       "      <td>Released</td>\n",
       "      <td>Grumpier Old Men</td>\n",
       "      <td>6.5</td>\n",
       "      <td>92.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>Grumpy Old Men Collection</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       budget                                             genres     id  \\\n",
       "0  30000000.0  [{'id': 16, 'name': 'Animation'}, {'id': 35, '...    862   \n",
       "1  65000000.0  [{'id': 12, 'name': 'Adventure'}, {'id': 14, '...   8844   \n",
       "2         0.0  [{'id': 10749, 'name': 'Romance'}, {'id': 35, ...  15602   \n",
       "\n",
       "  original_language                                           overview  \\\n",
       "0                en  Led by Woody, Andy's toys live happily in his ...   \n",
       "1                en  When siblings Judy and Peter discover an encha...   \n",
       "2                en  A family wedding reignites the ancient feud be...   \n",
       "\n",
       "  popularity                               production_companies  \\\n",
       "0  21.946943     [{'name': 'Pixar Animation Studios', 'id': 3}]   \n",
       "1  17.015539  [{'name': 'TriStar Pictures', 'id': 559}, {'na...   \n",
       "2    11.7129  [{'name': 'Warner Bros.', 'id': 6194}, {'name'...   \n",
       "\n",
       "                                production_countries release_date  \\\n",
       "0  [{'iso_3166_1': 'US', 'name': 'United States o...   1995-10-30   \n",
       "1  [{'iso_3166_1': 'US', 'name': 'United States o...   1995-12-15   \n",
       "2  [{'iso_3166_1': 'US', 'name': 'United States o...   1995-12-22   \n",
       "\n",
       "       revenue  runtime                                   spoken_languages  \\\n",
       "0  373554033.0     81.0           [{'iso_639_1': 'en', 'name': 'English'}]   \n",
       "1  262797249.0    104.0  [{'iso_639_1': 'en', 'name': 'English'}, {'iso...   \n",
       "2          0.0    101.0           [{'iso_639_1': 'en', 'name': 'English'}]   \n",
       "\n",
       "     status             title  vote_average  vote_count     return  \\\n",
       "0  Released         Toy Story           7.7      5415.0  12.451801   \n",
       "1  Released           Jumanji           6.9      2413.0   4.043035   \n",
       "2  Released  Grumpier Old Men           6.5        92.0   0.000000   \n",
       "\n",
       "  belongs_to_collection_name  \n",
       "0       Toy Story Collection  \n",
       "1                             \n",
       "2  Grumpy Old Men Collection  "
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_movies = pd.concat([df_movies, df_nested], axis=1)\n",
    "df_movies.drop(columns='belongs_to_collection', inplace=True)\n",
    "df_movies.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 45466 entries, 0 to 45465\n",
      "Data columns (total 18 columns):\n",
      " #   Column                      Non-Null Count  Dtype  \n",
      "---  ------                      --------------  -----  \n",
      " 0   budget                      45466 non-null  float64\n",
      " 1   genres                      45466 non-null  object \n",
      " 2   id                          45466 non-null  object \n",
      " 3   original_language           45455 non-null  object \n",
      " 4   overview                    44512 non-null  object \n",
      " 5   popularity                  45461 non-null  object \n",
      " 6   production_companies        45463 non-null  object \n",
      " 7   production_countries        45463 non-null  object \n",
      " 8   release_date                45379 non-null  object \n",
      " 9   revenue                     45466 non-null  float64\n",
      " 10  runtime                     45203 non-null  float64\n",
      " 11  spoken_languages            45460 non-null  object \n",
      " 12  status                      45379 non-null  object \n",
      " 13  title                       45460 non-null  object \n",
      " 14  vote_average                45460 non-null  float64\n",
      " 15  vote_count                  45460 non-null  float64\n",
      " 16  return                      45466 non-null  float64\n",
      " 17  belongs_to_collection_name  45466 non-null  object \n",
      "dtypes: float64(6), object(12)\n",
      "memory usage: 6.2+ MB\n"
     ]
    }
   ],
   "source": [
    "df_movies.info()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Unnest columns with list of dictionaries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [],
   "source": [
    "columns = ['genres', 'production_companies', 'production_countries', 'spoken_languages']\n",
    "key = 'name'\n",
    "df = pd.DataFrame()\n",
    "for _, column in enumerate(columns):\n",
    "    elements_list = []\n",
    "    elements_list = get_list(dataframe=df_movies, column=column, key_name=key)\n",
    "\n",
    "    df_aux = pd.DataFrame({column: elements_list})\n",
    "    df = pd.concat([df, df_aux], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_movies.drop(columns=columns, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "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>genres</th>\n",
       "      <th>production_companies</th>\n",
       "      <th>production_countries</th>\n",
       "      <th>spoken_languages</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>[Animation, Comedy, Family]</td>\n",
       "      <td>[Pixar Animation Studios]</td>\n",
       "      <td>[United States of America]</td>\n",
       "      <td>[English]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>[Adventure, Fantasy, Family]</td>\n",
       "      <td>[TriStar Pictures, Teitler Film, Interscope Co...</td>\n",
       "      <td>[United States of America]</td>\n",
       "      <td>[English, Français]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>[Romance, Comedy]</td>\n",
       "      <td>[Warner Bros., Lancaster Gate]</td>\n",
       "      <td>[United States of America]</td>\n",
       "      <td>[English]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>[Comedy, Drama, Romance]</td>\n",
       "      <td>[Twentieth Century Fox Film Corporation]</td>\n",
       "      <td>[United States of America]</td>\n",
       "      <td>[English]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>[Comedy]</td>\n",
       "      <td>[Sandollar Productions, Touchstone Pictures]</td>\n",
       "      <td>[United States of America]</td>\n",
       "      <td>[English]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         genres  \\\n",
       "0   [Animation, Comedy, Family]   \n",
       "1  [Adventure, Fantasy, Family]   \n",
       "2             [Romance, Comedy]   \n",
       "3      [Comedy, Drama, Romance]   \n",
       "4                      [Comedy]   \n",
       "\n",
       "                                production_companies  \\\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",
       "         production_countries     spoken_languages  \n",
       "0  [United States of America]            [English]  \n",
       "1  [United States of America]  [English, Français]  \n",
       "2  [United States of America]            [English]  \n",
       "3  [United States of America]            [English]  \n",
       "4  [United States of America]            [English]  "
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "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>status</th>\n",
       "      <th>title</th>\n",
       "      <th>vote_average</th>\n",
       "      <th>vote_count</th>\n",
       "      <th>return</th>\n",
       "      <th>belongs_to_collection_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>30000000.0</td>\n",
       "      <td>862</td>\n",
       "      <td>en</td>\n",
       "      <td>Led by Woody, Andy's toys live happily in his ...</td>\n",
       "      <td>21.946943</td>\n",
       "      <td>1995-10-30</td>\n",
       "      <td>373554033.0</td>\n",
       "      <td>81.0</td>\n",
       "      <td>Released</td>\n",
       "      <td>Toy Story</td>\n",
       "      <td>7.7</td>\n",
       "      <td>5415.0</td>\n",
       "      <td>12.451801</td>\n",
       "      <td>Toy Story Collection</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>65000000.0</td>\n",
       "      <td>8844</td>\n",
       "      <td>en</td>\n",
       "      <td>When siblings Judy and Peter discover an encha...</td>\n",
       "      <td>17.015539</td>\n",
       "      <td>1995-12-15</td>\n",
       "      <td>262797249.0</td>\n",
       "      <td>104.0</td>\n",
       "      <td>Released</td>\n",
       "      <td>Jumanji</td>\n",
       "      <td>6.9</td>\n",
       "      <td>2413.0</td>\n",
       "      <td>4.043035</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.0</td>\n",
       "      <td>15602</td>\n",
       "      <td>en</td>\n",
       "      <td>A family wedding reignites the ancient feud be...</td>\n",
       "      <td>11.7129</td>\n",
       "      <td>1995-12-22</td>\n",
       "      <td>0.0</td>\n",
       "      <td>101.0</td>\n",
       "      <td>Released</td>\n",
       "      <td>Grumpier Old Men</td>\n",
       "      <td>6.5</td>\n",
       "      <td>92.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>Grumpy Old Men Collection</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>16000000.0</td>\n",
       "      <td>31357</td>\n",
       "      <td>en</td>\n",
       "      <td>Cheated on, mistreated and stepped on, the wom...</td>\n",
       "      <td>3.859495</td>\n",
       "      <td>1995-12-22</td>\n",
       "      <td>81452156.0</td>\n",
       "      <td>127.0</td>\n",
       "      <td>Released</td>\n",
       "      <td>Waiting to Exhale</td>\n",
       "      <td>6.1</td>\n",
       "      <td>34.0</td>\n",
       "      <td>5.090760</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.0</td>\n",
       "      <td>11862</td>\n",
       "      <td>en</td>\n",
       "      <td>Just when George Banks has recovered from his ...</td>\n",
       "      <td>8.387519</td>\n",
       "      <td>1995-02-10</td>\n",
       "      <td>76578911.0</td>\n",
       "      <td>106.0</td>\n",
       "      <td>Released</td>\n",
       "      <td>Father of the Bride Part II</td>\n",
       "      <td>5.7</td>\n",
       "      <td>173.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>Father of the Bride Collection</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       budget     id original_language  \\\n",
       "0  30000000.0    862                en   \n",
       "1  65000000.0   8844                en   \n",
       "2         0.0  15602                en   \n",
       "3  16000000.0  31357                en   \n",
       "4         0.0  11862                en   \n",
       "\n",
       "                                            overview popularity release_date  \\\n",
       "0  Led by Woody, Andy's toys live happily in his ...  21.946943   1995-10-30   \n",
       "1  When siblings Judy and Peter discover an encha...  17.015539   1995-12-15   \n",
       "2  A family wedding reignites the ancient feud be...    11.7129   1995-12-22   \n",
       "3  Cheated on, mistreated and stepped on, the wom...   3.859495   1995-12-22   \n",
       "4  Just when George Banks has recovered from his ...   8.387519   1995-02-10   \n",
       "\n",
       "       revenue  runtime    status                        title  vote_average  \\\n",
       "0  373554033.0     81.0  Released                    Toy Story           7.7   \n",
       "1  262797249.0    104.0  Released                      Jumanji           6.9   \n",
       "2          0.0    101.0  Released             Grumpier Old Men           6.5   \n",
       "3   81452156.0    127.0  Released            Waiting to Exhale           6.1   \n",
       "4   76578911.0    106.0  Released  Father of the Bride Part II           5.7   \n",
       "\n",
       "   vote_count     return      belongs_to_collection_name  \n",
       "0      5415.0  12.451801            Toy Story Collection  \n",
       "1      2413.0   4.043035                                  \n",
       "2        92.0   0.000000       Grumpy Old Men Collection  \n",
       "3        34.0   5.090760                                  \n",
       "4       173.0   0.000000  Father of the Bride Collection  "
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_movies.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "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>status</th>\n",
       "      <th>title</th>\n",
       "      <th>vote_average</th>\n",
       "      <th>vote_count</th>\n",
       "      <th>return</th>\n",
       "      <th>belongs_to_collection_name</th>\n",
       "      <th>genres</th>\n",
       "      <th>production_companies</th>\n",
       "      <th>production_countries</th>\n",
       "      <th>spoken_languages</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>30000000.0</td>\n",
       "      <td>862</td>\n",
       "      <td>en</td>\n",
       "      <td>Led by Woody, Andy's toys live happily in his ...</td>\n",
       "      <td>21.946943</td>\n",
       "      <td>1995-10-30</td>\n",
       "      <td>373554033.0</td>\n",
       "      <td>81.0</td>\n",
       "      <td>Released</td>\n",
       "      <td>Toy Story</td>\n",
       "      <td>7.7</td>\n",
       "      <td>5415.0</td>\n",
       "      <td>12.451801</td>\n",
       "      <td>Toy Story Collection</td>\n",
       "      <td>[Animation, Comedy, Family]</td>\n",
       "      <td>[Pixar Animation Studios]</td>\n",
       "      <td>[United States of America]</td>\n",
       "      <td>[English]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>65000000.0</td>\n",
       "      <td>8844</td>\n",
       "      <td>en</td>\n",
       "      <td>When siblings Judy and Peter discover an encha...</td>\n",
       "      <td>17.015539</td>\n",
       "      <td>1995-12-15</td>\n",
       "      <td>262797249.0</td>\n",
       "      <td>104.0</td>\n",
       "      <td>Released</td>\n",
       "      <td>Jumanji</td>\n",
       "      <td>6.9</td>\n",
       "      <td>2413.0</td>\n",
       "      <td>4.043035</td>\n",
       "      <td></td>\n",
       "      <td>[Adventure, Fantasy, Family]</td>\n",
       "      <td>[TriStar Pictures, Teitler Film, Interscope Co...</td>\n",
       "      <td>[United States of America]</td>\n",
       "      <td>[English, Français]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.0</td>\n",
       "      <td>15602</td>\n",
       "      <td>en</td>\n",
       "      <td>A family wedding reignites the ancient feud be...</td>\n",
       "      <td>11.7129</td>\n",
       "      <td>1995-12-22</td>\n",
       "      <td>0.0</td>\n",
       "      <td>101.0</td>\n",
       "      <td>Released</td>\n",
       "      <td>Grumpier Old Men</td>\n",
       "      <td>6.5</td>\n",
       "      <td>92.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>Grumpy Old Men Collection</td>\n",
       "      <td>[Romance, Comedy]</td>\n",
       "      <td>[Warner Bros., Lancaster Gate]</td>\n",
       "      <td>[United States of America]</td>\n",
       "      <td>[English]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>16000000.0</td>\n",
       "      <td>31357</td>\n",
       "      <td>en</td>\n",
       "      <td>Cheated on, mistreated and stepped on, the wom...</td>\n",
       "      <td>3.859495</td>\n",
       "      <td>1995-12-22</td>\n",
       "      <td>81452156.0</td>\n",
       "      <td>127.0</td>\n",
       "      <td>Released</td>\n",
       "      <td>Waiting to Exhale</td>\n",
       "      <td>6.1</td>\n",
       "      <td>34.0</td>\n",
       "      <td>5.090760</td>\n",
       "      <td></td>\n",
       "      <td>[Comedy, Drama, Romance]</td>\n",
       "      <td>[Twentieth Century Fox Film Corporation]</td>\n",
       "      <td>[United States of America]</td>\n",
       "      <td>[English]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.0</td>\n",
       "      <td>11862</td>\n",
       "      <td>en</td>\n",
       "      <td>Just when George Banks has recovered from his ...</td>\n",
       "      <td>8.387519</td>\n",
       "      <td>1995-02-10</td>\n",
       "      <td>76578911.0</td>\n",
       "      <td>106.0</td>\n",
       "      <td>Released</td>\n",
       "      <td>Father of the Bride Part II</td>\n",
       "      <td>5.7</td>\n",
       "      <td>173.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>Father of the Bride Collection</td>\n",
       "      <td>[Comedy]</td>\n",
       "      <td>[Sandollar Productions, Touchstone Pictures]</td>\n",
       "      <td>[United States of America]</td>\n",
       "      <td>[English]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       budget     id original_language  \\\n",
       "0  30000000.0    862                en   \n",
       "1  65000000.0   8844                en   \n",
       "2         0.0  15602                en   \n",
       "3  16000000.0  31357                en   \n",
       "4         0.0  11862                en   \n",
       "\n",
       "                                            overview popularity release_date  \\\n",
       "0  Led by Woody, Andy's toys live happily in his ...  21.946943   1995-10-30   \n",
       "1  When siblings Judy and Peter discover an encha...  17.015539   1995-12-15   \n",
       "2  A family wedding reignites the ancient feud be...    11.7129   1995-12-22   \n",
       "3  Cheated on, mistreated and stepped on, the wom...   3.859495   1995-12-22   \n",
       "4  Just when George Banks has recovered from his ...   8.387519   1995-02-10   \n",
       "\n",
       "       revenue  runtime    status                        title  vote_average  \\\n",
       "0  373554033.0     81.0  Released                    Toy Story           7.7   \n",
       "1  262797249.0    104.0  Released                      Jumanji           6.9   \n",
       "2          0.0    101.0  Released             Grumpier Old Men           6.5   \n",
       "3   81452156.0    127.0  Released            Waiting to Exhale           6.1   \n",
       "4   76578911.0    106.0  Released  Father of the Bride Part II           5.7   \n",
       "\n",
       "   vote_count     return      belongs_to_collection_name  \\\n",
       "0      5415.0  12.451801            Toy Story Collection   \n",
       "1      2413.0   4.043035                                   \n",
       "2        92.0   0.000000       Grumpy Old Men Collection   \n",
       "3        34.0   5.090760                                   \n",
       "4       173.0   0.000000  Father of the Bride Collection   \n",
       "\n",
       "                         genres  \\\n",
       "0   [Animation, Comedy, Family]   \n",
       "1  [Adventure, Fantasy, Family]   \n",
       "2             [Romance, Comedy]   \n",
       "3      [Comedy, Drama, Romance]   \n",
       "4                      [Comedy]   \n",
       "\n",
       "                                production_companies  \\\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",
       "         production_countries     spoken_languages  \n",
       "0  [United States of America]            [English]  \n",
       "1  [United States of America]  [English, Français]  \n",
       "2  [United States of America]            [English]  \n",
       "3  [United States of America]            [English]  \n",
       "4  [United States of America]            [English]  "
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_movies = pd.concat([df_movies, df], axis=1)\n",
    "df_movies.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(45466, 18)"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_movies.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 45466 entries, 0 to 45465\n",
      "Data columns (total 18 columns):\n",
      " #   Column                      Non-Null Count  Dtype  \n",
      "---  ------                      --------------  -----  \n",
      " 0   budget                      45466 non-null  float64\n",
      " 1   id                          45466 non-null  object \n",
      " 2   original_language           45455 non-null  object \n",
      " 3   overview                    44512 non-null  object \n",
      " 4   popularity                  45461 non-null  object \n",
      " 5   release_date                45379 non-null  object \n",
      " 6   revenue                     45466 non-null  float64\n",
      " 7   runtime                     45203 non-null  float64\n",
      " 8   status                      45379 non-null  object \n",
      " 9   title                       45460 non-null  object \n",
      " 10  vote_average                45460 non-null  float64\n",
      " 11  vote_count                  45460 non-null  float64\n",
      " 12  return                      45466 non-null  float64\n",
      " 13  belongs_to_collection_name  45466 non-null  object \n",
      " 14  genres                      45466 non-null  object \n",
      " 15  production_companies        45466 non-null  object \n",
      " 16  production_countries        45466 non-null  object \n",
      " 17  spoken_languages            45466 non-null  object \n",
      "dtypes: float64(6), object(12)\n",
      "memory usage: 6.2+ MB\n"
     ]
    }
   ],
   "source": [
    "df_movies.info()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Delete null values of `release_date` and `id` column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 45379 entries, 0 to 45465\n",
      "Data columns (total 18 columns):\n",
      " #   Column                      Non-Null Count  Dtype  \n",
      "---  ------                      --------------  -----  \n",
      " 0   budget                      45379 non-null  float64\n",
      " 1   id                          45379 non-null  object \n",
      " 2   original_language           45368 non-null  object \n",
      " 3   overview                    44438 non-null  object \n",
      " 4   popularity                  45377 non-null  object \n",
      " 5   release_date                45379 non-null  object \n",
      " 6   revenue                     45379 non-null  float64\n",
      " 7   runtime                     45130 non-null  float64\n",
      " 8   status                      45296 non-null  object \n",
      " 9   title                       45376 non-null  object \n",
      " 10  vote_average                45376 non-null  float64\n",
      " 11  vote_count                  45376 non-null  float64\n",
      " 12  return                      45379 non-null  float64\n",
      " 13  belongs_to_collection_name  45379 non-null  object \n",
      " 14  genres                      45379 non-null  object \n",
      " 15  production_companies        45379 non-null  object \n",
      " 16  production_countries        45379 non-null  object \n",
      " 17  spoken_languages            45379 non-null  object \n",
      "dtypes: float64(6), object(12)\n",
      "memory usage: 6.6+ MB\n"
     ]
    }
   ],
   "source": [
    "df_movies.dropna(subset=['release_date'], inplace=True)\n",
    "df_movies.info()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Change `release_date` to `AAAA-mm-dd` format and keep year field in a new column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_492878/235543016.py:2: UserWarning: This pattern is interpreted as a regular expression, and has match groups. To actually get the groups, use str.extract.\n",
      "  filter = df_movies['release_date'].str.contains(regex_filter)\n"
     ]
    }
   ],
   "source": [
    "regex_filter = '^\\d{4}\\-(0?[1-9]|1[012])\\-(0?[1-9]|[12][0-9]|3[01])$'\n",
    "filter = df_movies['release_date'].str.contains(regex_filter)\n",
    "df_movies = df_movies[filter]\n",
    "df_movies['release_year'] = df_movies['release_date'].apply(lambda x: datetime.strptime(x, '%Y-%m-%d').date().year)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Convert `release_date` to date format"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_movies['release_date'] = pd.to_datetime(df_movies['release_date'], format='%Y-%m-%d')"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reset index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_movies.reset_index(drop=True, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "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>status</th>\n",
       "      <th>title</th>\n",
       "      <th>vote_average</th>\n",
       "      <th>vote_count</th>\n",
       "      <th>return</th>\n",
       "      <th>belongs_to_collection_name</th>\n",
       "      <th>genres</th>\n",
       "      <th>production_companies</th>\n",
       "      <th>production_countries</th>\n",
       "      <th>spoken_languages</th>\n",
       "      <th>release_year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>30000000.0</td>\n",
       "      <td>862</td>\n",
       "      <td>en</td>\n",
       "      <td>Led by Woody, Andy's toys live happily in his ...</td>\n",
       "      <td>21.946943</td>\n",
       "      <td>1995-10-30</td>\n",
       "      <td>373554033.0</td>\n",
       "      <td>81.0</td>\n",
       "      <td>Released</td>\n",
       "      <td>Toy Story</td>\n",
       "      <td>7.7</td>\n",
       "      <td>5415.0</td>\n",
       "      <td>12.451801</td>\n",
       "      <td>Toy Story Collection</td>\n",
       "      <td>[Animation, Comedy, Family]</td>\n",
       "      <td>[Pixar Animation Studios]</td>\n",
       "      <td>[United States of America]</td>\n",
       "      <td>[English]</td>\n",
       "      <td>1995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>65000000.0</td>\n",
       "      <td>8844</td>\n",
       "      <td>en</td>\n",
       "      <td>When siblings Judy and Peter discover an encha...</td>\n",
       "      <td>17.015539</td>\n",
       "      <td>1995-12-15</td>\n",
       "      <td>262797249.0</td>\n",
       "      <td>104.0</td>\n",
       "      <td>Released</td>\n",
       "      <td>Jumanji</td>\n",
       "      <td>6.9</td>\n",
       "      <td>2413.0</td>\n",
       "      <td>4.043035</td>\n",
       "      <td></td>\n",
       "      <td>[Adventure, Fantasy, Family]</td>\n",
       "      <td>[TriStar Pictures, Teitler Film, Interscope Co...</td>\n",
       "      <td>[United States of America]</td>\n",
       "      <td>[English, Français]</td>\n",
       "      <td>1995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.0</td>\n",
       "      <td>15602</td>\n",
       "      <td>en</td>\n",
       "      <td>A family wedding reignites the ancient feud be...</td>\n",
       "      <td>11.7129</td>\n",
       "      <td>1995-12-22</td>\n",
       "      <td>0.0</td>\n",
       "      <td>101.0</td>\n",
       "      <td>Released</td>\n",
       "      <td>Grumpier Old Men</td>\n",
       "      <td>6.5</td>\n",
       "      <td>92.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>Grumpy Old Men Collection</td>\n",
       "      <td>[Romance, Comedy]</td>\n",
       "      <td>[Warner Bros., Lancaster Gate]</td>\n",
       "      <td>[United States of America]</td>\n",
       "      <td>[English]</td>\n",
       "      <td>1995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>16000000.0</td>\n",
       "      <td>31357</td>\n",
       "      <td>en</td>\n",
       "      <td>Cheated on, mistreated and stepped on, the wom...</td>\n",
       "      <td>3.859495</td>\n",
       "      <td>1995-12-22</td>\n",
       "      <td>81452156.0</td>\n",
       "      <td>127.0</td>\n",
       "      <td>Released</td>\n",
       "      <td>Waiting to Exhale</td>\n",
       "      <td>6.1</td>\n",
       "      <td>34.0</td>\n",
       "      <td>5.090760</td>\n",
       "      <td></td>\n",
       "      <td>[Comedy, Drama, Romance]</td>\n",
       "      <td>[Twentieth Century Fox Film Corporation]</td>\n",
       "      <td>[United States of America]</td>\n",
       "      <td>[English]</td>\n",
       "      <td>1995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.0</td>\n",
       "      <td>11862</td>\n",
       "      <td>en</td>\n",
       "      <td>Just when George Banks has recovered from his ...</td>\n",
       "      <td>8.387519</td>\n",
       "      <td>1995-02-10</td>\n",
       "      <td>76578911.0</td>\n",
       "      <td>106.0</td>\n",
       "      <td>Released</td>\n",
       "      <td>Father of the Bride Part II</td>\n",
       "      <td>5.7</td>\n",
       "      <td>173.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>Father of the Bride Collection</td>\n",
       "      <td>[Comedy]</td>\n",
       "      <td>[Sandollar Productions, Touchstone Pictures]</td>\n",
       "      <td>[United States of America]</td>\n",
       "      <td>[English]</td>\n",
       "      <td>1995</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       budget     id original_language  \\\n",
       "0  30000000.0    862                en   \n",
       "1  65000000.0   8844                en   \n",
       "2         0.0  15602                en   \n",
       "3  16000000.0  31357                en   \n",
       "4         0.0  11862                en   \n",
       "\n",
       "                                            overview popularity release_date  \\\n",
       "0  Led by Woody, Andy's toys live happily in his ...  21.946943   1995-10-30   \n",
       "1  When siblings Judy and Peter discover an encha...  17.015539   1995-12-15   \n",
       "2  A family wedding reignites the ancient feud be...    11.7129   1995-12-22   \n",
       "3  Cheated on, mistreated and stepped on, the wom...   3.859495   1995-12-22   \n",
       "4  Just when George Banks has recovered from his ...   8.387519   1995-02-10   \n",
       "\n",
       "       revenue  runtime    status                        title  vote_average  \\\n",
       "0  373554033.0     81.0  Released                    Toy Story           7.7   \n",
       "1  262797249.0    104.0  Released                      Jumanji           6.9   \n",
       "2          0.0    101.0  Released             Grumpier Old Men           6.5   \n",
       "3   81452156.0    127.0  Released            Waiting to Exhale           6.1   \n",
       "4   76578911.0    106.0  Released  Father of the Bride Part II           5.7   \n",
       "\n",
       "   vote_count     return      belongs_to_collection_name  \\\n",
       "0      5415.0  12.451801            Toy Story Collection   \n",
       "1      2413.0   4.043035                                   \n",
       "2        92.0   0.000000       Grumpy Old Men Collection   \n",
       "3        34.0   5.090760                                   \n",
       "4       173.0   0.000000  Father of the Bride Collection   \n",
       "\n",
       "                         genres  \\\n",
       "0   [Animation, Comedy, Family]   \n",
       "1  [Adventure, Fantasy, Family]   \n",
       "2             [Romance, Comedy]   \n",
       "3      [Comedy, Drama, Romance]   \n",
       "4                      [Comedy]   \n",
       "\n",
       "                                production_companies  \\\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",
       "         production_countries     spoken_languages  release_year  \n",
       "0  [United States of America]            [English]          1995  \n",
       "1  [United States of America]  [English, Français]          1995  \n",
       "2  [United States of America]            [English]          1995  \n",
       "3  [United States of America]            [English]          1995  \n",
       "4  [United States of America]            [English]          1995  "
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_movies.head()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Convert `id` to int"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_movies['id'] = df_movies['id'].astype(int)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Drop duplicated `id` values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "141971    3\n",
       "97995     2\n",
       "10991     2\n",
       "109962    2\n",
       "119916    2\n",
       "         ..\n",
       "40594     1\n",
       "23104     1\n",
       "37605     1\n",
       "91727     1\n",
       "461257    1\n",
       "Name: id, Length: 45346, dtype: int64"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_movies['id'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_movies.drop_duplicates(subset='id', keep='first', inplace=True, ignore_index=True)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Check duplicated `id`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Int64Index([], dtype='int64')"
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_movies['id'].value_counts().index[df_movies['id'].value_counts().values > 1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "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>revenue</th>\n",
       "      <th>runtime</th>\n",
       "      <th>vote_average</th>\n",
       "      <th>vote_count</th>\n",
       "      <th>return</th>\n",
       "      <th>release_year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>4.534600e+04</td>\n",
       "      <td>45346.000000</td>\n",
       "      <td>4.534600e+04</td>\n",
       "      <td>45100.000000</td>\n",
       "      <td>45346.000000</td>\n",
       "      <td>45346.000000</td>\n",
       "      <td>4.534600e+04</td>\n",
       "      <td>45346.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>4.232580e+06</td>\n",
       "      <td>108042.219645</td>\n",
       "      <td>1.123366e+07</td>\n",
       "      <td>94.177805</td>\n",
       "      <td>5.624196</td>\n",
       "      <td>110.135293</td>\n",
       "      <td>6.604792e+02</td>\n",
       "      <td>1991.882834</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>1.744373e+07</td>\n",
       "      <td>112187.326746</td>\n",
       "      <td>6.440990e+07</td>\n",
       "      <td>38.348775</td>\n",
       "      <td>1.915339</td>\n",
       "      <td>491.899276</td>\n",
       "      <td>7.471800e+04</td>\n",
       "      <td>24.053040</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>1874.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>26390.250000</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>85.000000</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>1978.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>59852.500000</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>95.000000</td>\n",
       "      <td>6.000000</td>\n",
       "      <td>10.000000</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>2001.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>156601.500000</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>107.000000</td>\n",
       "      <td>6.800000</td>\n",
       "      <td>34.000000</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>2010.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>3.800000e+08</td>\n",
       "      <td>469172.000000</td>\n",
       "      <td>2.787965e+09</td>\n",
       "      <td>1256.000000</td>\n",
       "      <td>10.000000</td>\n",
       "      <td>14075.000000</td>\n",
       "      <td>1.239638e+07</td>\n",
       "      <td>2020.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             budget             id       revenue       runtime  vote_average  \\\n",
       "count  4.534600e+04   45346.000000  4.534600e+04  45100.000000  45346.000000   \n",
       "mean   4.232580e+06  108042.219645  1.123366e+07     94.177805      5.624196   \n",
       "std    1.744373e+07  112187.326746  6.440990e+07     38.348775      1.915339   \n",
       "min    0.000000e+00       2.000000  0.000000e+00      0.000000      0.000000   \n",
       "25%    0.000000e+00   26390.250000  0.000000e+00     85.000000      5.000000   \n",
       "50%    0.000000e+00   59852.500000  0.000000e+00     95.000000      6.000000   \n",
       "75%    0.000000e+00  156601.500000  0.000000e+00    107.000000      6.800000   \n",
       "max    3.800000e+08  469172.000000  2.787965e+09   1256.000000     10.000000   \n",
       "\n",
       "         vote_count        return  release_year  \n",
       "count  45346.000000  4.534600e+04  45346.000000  \n",
       "mean     110.135293  6.604792e+02   1991.882834  \n",
       "std      491.899276  7.471800e+04     24.053040  \n",
       "min        0.000000  0.000000e+00   1874.000000  \n",
       "25%        3.000000  0.000000e+00   1978.000000  \n",
       "50%       10.000000  0.000000e+00   2001.000000  \n",
       "75%       34.000000  0.000000e+00   2010.000000  \n",
       "max    14075.000000  1.239638e+07   2020.000000  "
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_movies.describe()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# ETL to credits dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [],
   "source": [
    "url='https://drive.google.com/file/d/1iyhs4e65lu0tg1gVih_ATUP6wGz1KG2k/view?usp=sharing'\n",
    "url='https://drive.google.com/uc?id=' + url.split('/')[-2]\n",
    "df_credits = pd.read_csv('RawData/credits.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "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": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_credits.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 45476 entries, 0 to 45475\n",
      "Data columns (total 3 columns):\n",
      " #   Column  Non-Null Count  Dtype \n",
      "---  ------  --------------  ----- \n",
      " 0   cast    45476 non-null  object\n",
      " 1   crew    45476 non-null  object\n",
      " 2   id      45476 non-null  int64 \n",
      "dtypes: int64(1), object(2)\n",
      "memory usage: 1.0+ MB\n"
     ]
    }
   ],
   "source": [
    "df_credits.info()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create a new dataframe with the actors and director"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "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>[Tom Hanks, Tim Allen, Don Rickles, Jim Varney...</td>\n",
       "      <td>John Lasseter</td>\n",
       "      <td>862</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>[Robin Williams, Jonathan Hyde, Kirsten Dunst,...</td>\n",
       "      <td>Larry J. Franco</td>\n",
       "      <td>8844</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>[Walter Matthau, Jack Lemmon, Ann-Margret, Sop...</td>\n",
       "      <td>Howard Deutch</td>\n",
       "      <td>15602</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>[Whitney Houston, Angela Bassett, Loretta Devi...</td>\n",
       "      <td>Forest Whitaker</td>\n",
       "      <td>31357</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>[Steve Martin, Diane Keaton, Martin Short, Kim...</td>\n",
       "      <td>Alan Silvestri</td>\n",
       "      <td>11862</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                cast             crew     id\n",
       "0  [Tom Hanks, Tim Allen, Don Rickles, Jim Varney...    John Lasseter    862\n",
       "1  [Robin Williams, Jonathan Hyde, Kirsten Dunst,...  Larry J. Franco   8844\n",
       "2  [Walter Matthau, Jack Lemmon, Ann-Margret, Sop...    Howard Deutch  15602\n",
       "3  [Whitney Houston, Angela Bassett, Loretta Devi...  Forest Whitaker  31357\n",
       "4  [Steve Martin, Diane Keaton, Martin Short, Kim...   Alan Silvestri  11862"
      ]
     },
     "execution_count": 94,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "columns = ['cast', 'crew']\n",
    "key_list = ['name', 'name']\n",
    "df_actors = pd.DataFrame()\n",
    "for _, (column, key) in enumerate(zip(columns, key_list)):\n",
    "    elements_list = []\n",
    "    elements_list = get_list(ret_val=None, dataframe=df_credits, column=column, key_name=key)\n",
    "    if 'crew' in column:\n",
    "        new_list = [element[0] if len(element) > 0 else 'No Director' for element in elements_list]\n",
    "        elements_list = new_list.copy()\n",
    "    df_aux = pd.DataFrame({column: elements_list})\n",
    "    df_actors = pd.concat([df_actors, df_aux], axis=1)\n",
    "df_actors = pd.concat([df_actors, df_credits['id']], axis=1)\n",
    "df_actors.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "141971    3\n",
       "298721    2\n",
       "9755      2\n",
       "10991     2\n",
       "99080     2\n",
       "         ..\n",
       "55135     1\n",
       "15877     1\n",
       "72272     1\n",
       "1549      1\n",
       "461257    1\n",
       "Name: id, Length: 45432, dtype: int64"
      ]
     },
     "execution_count": 95,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_actors['id'].value_counts()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Drop duplicated `id` values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_actors.drop_duplicates(subset='id', keep='first', inplace=True, ignore_index=True)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Check duplicated `id`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Int64Index([], dtype='int64')"
      ]
     },
     "execution_count": 97,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_actors['id'].value_counts().index[df_actors['id'].value_counts().values > 1]"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Rename column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_actors.rename(columns={'crew': 'director'}, inplace=True)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reset index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_actors.reset_index(drop=True, inplace=True)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Merge dataframes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_movies_new = df_movies.copy()\n",
    "df_actors_new = df_actors.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_movies_new = pd.merge(df_movies_new, df_actors_new, how='left', on='id')"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Check if a film contains NaN values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "42783    NaN\n",
       "Name: cast, dtype: object"
      ]
     },
     "execution_count": 102,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_aux = df_movies_new['cast']\n",
    "df_aux[df_aux.isna()]"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Delete value with NaN"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "budget                                                                      0.0\n",
       "id                                                                       401840\n",
       "original_language                                                            es\n",
       "overview                      Two high school kids mentored by a nightclub o...\n",
       "popularity                                                             0.207775\n",
       "release_date                                                2017-05-30 00:00:00\n",
       "revenue                                                                     0.0\n",
       "runtime                                                                     0.0\n",
       "status                                                                 Released\n",
       "title                                                              School's out\n",
       "vote_average                                                                0.0\n",
       "vote_count                                                                  0.0\n",
       "return                                                                      0.0\n",
       "belongs_to_collection_name                                                     \n",
       "genres                                                                       []\n",
       "production_companies                                                         []\n",
       "production_countries                                                         []\n",
       "spoken_languages                                                             []\n",
       "release_year                                                               2017\n",
       "cast                                                                        NaN\n",
       "director                                                                    NaN\n",
       "Name: 42783, dtype: object"
      ]
     },
     "execution_count": 103,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_movies_new.iloc[42783]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_movies_new.drop(index=42783, inplace=True)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reset index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_movies_new.reset_index(drop=True, inplace=True)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create a new `CSV` file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_movies_new.to_csv('ProcessedData/proc_movies.csv', index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "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.9.13"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}