02_feature_engineering.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Feature Engineering"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "IMDb, acronym for Internet Movie Database, is a website owned by Amazon.com where users can look for details about movies and TV shows: plot summaries, users reviews, genre, director and cast are just some of the attributes stored on IMDb. \n",
    "\n",
    "The goal of this project is to predict the rating of a given movie using properly trained Machine Learning models and evaluate the goodness of the predictions.\n",
    "The chosen dataset was made available on the Kaggle platform and it contains the top 100 movies between 2003 - 2022. \n",
    "\n",
    "Dataset: https://www.kaggle.com/datasets/georgescutelnicu/top-100-popular-movies-from-2003-to-2022-imdb "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Understanding the Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>Title</th>\n",
       "      <th>Rating</th>\n",
       "      <th>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>Certificate</th>\n",
       "      <th>Runtime</th>\n",
       "      <th>Directors</th>\n",
       "      <th>Stars</th>\n",
       "      <th>Genre</th>\n",
       "      <th>Filming_location</th>\n",
       "      <th>Budget</th>\n",
       "      <th>Income</th>\n",
       "      <th>Country_of_origin</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Avatar: The Way of Water</td>\n",
       "      <td>7.8</td>\n",
       "      <td>2022</td>\n",
       "      <td>December</td>\n",
       "      <td>PG-13</td>\n",
       "      <td>192</td>\n",
       "      <td>James Cameron</td>\n",
       "      <td>Sam Worthington, Zoe Saldana, Sigourney Weaver...</td>\n",
       "      <td>Action, Adventure, Fantasy</td>\n",
       "      <td>New Zealand</td>\n",
       "      <td>$350,000,000</td>\n",
       "      <td>$2,267,946,983</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Guillermo del Toro's Pinocchio</td>\n",
       "      <td>7.6</td>\n",
       "      <td>2022</td>\n",
       "      <td>December</td>\n",
       "      <td>PG</td>\n",
       "      <td>117</td>\n",
       "      <td>Guillermo del Toro, Mark Gustafson</td>\n",
       "      <td>Ewan McGregor, David Bradley, Gregory Mann, Bu...</td>\n",
       "      <td>Animation, Drama, Family</td>\n",
       "      <td>USA</td>\n",
       "      <td>$35,000,000</td>\n",
       "      <td>$108,967</td>\n",
       "      <td>United States, Mexico, France</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            Title  Rating  Year     Month Certificate Runtime  \\\n",
       "0        Avatar: The Way of Water     7.8  2022  December       PG-13     192   \n",
       "1  Guillermo del Toro's Pinocchio     7.6  2022  December          PG     117   \n",
       "\n",
       "                            Directors  \\\n",
       "0                       James Cameron   \n",
       "1  Guillermo del Toro, Mark Gustafson   \n",
       "\n",
       "                                               Stars  \\\n",
       "0  Sam Worthington, Zoe Saldana, Sigourney Weaver...   \n",
       "1  Ewan McGregor, David Bradley, Gregory Mann, Bu...   \n",
       "\n",
       "                        Genre Filming_location         Budget  \\\n",
       "0  Action, Adventure, Fantasy      New Zealand  $350,000,000    \n",
       "1    Animation, Drama, Family              USA   $35,000,000    \n",
       "\n",
       "            Income              Country_of_origin  \n",
       "0  $2,267,946,983                   United States  \n",
       "1        $108,967   United States, Mexico, France  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dataset_raw = pd.read_csv('./data/movies.csv')\n",
    "dataset_raw.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Dataset\n",
    "\n",
    "The dataset consists of 13 different features (columns) with 2000 movies (rows). Summary of all the variables in the dataset. \n",
    "\n",
    "| Variable | Description | Type | Remove | Processing |\n",
    "| --- | --- | --- | --- | --- |\n",
    "| Title | Movie title | str |  |  |\n",
    "| Rating | Movie rating | float |  |  |\n",
    "| Year | Release year | int |  |  |\n",
    "| Month | Release month | str |  | Categorical variable |\n",
    "| Certificate |  Parental guidence categorisation | str | X |  |\n",
    "| Runtime | Movie duration in minutes | int |  | Convert to int |\n",
    "| Directors | Movie directors (can be more than one) | str |  | Replace by IMDb scoring/popularity score |\n",
    "| Stars | Actors and Actresses (can be more than one) | str |  | Replace by IMDb scoring/popularity score |\n",
    "| Genre | Type of movie (always more than one) | str |  | One-hot encoding |\n",
    "| Filming_location | Where the movie was filmed | str | X |  |\n",
    "| Budget | Amount of money spend on production (different currencies) | str |  | Common currency (USD) and convert to int |\n",
    "| Income | Movie profit (different currencies) | str |  | Common currency (USD) and convert to int |\n",
    "| Country_of_origin | Where the movie is produced | str | X |  |"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data problems\n",
    "\n",
    "The dataset requires some processing and feature engineering before POC can be proceeded as most of the data is str type. Following problems must be solved:\n",
    "1. Some variables will affect the prediction less then others and given that they are str values it's easier to remove them than spend time on processing their values. (*2. Primary clean-up of the dataset*)\n",
    "2. Variable **Month** can be turned into a categorical value by replacing the names of the months by their corresponding numbers (*3.1 Month*)\n",
    "3. The **Runtime** will be simply converted to int instead of current str (*3.2 Runtime*)\n",
    "4. Currently each value of the **Genre** variable contains multiple genres. These will be handled with one-hot encoding (*3.3 Genre*)\n",
    "5. The **Budget** and **Income** variables are string values with varying currencies. Thess will be converted to USD and simultaneously to int (*3.4 Budget and Income*)\n",
    "6. The **Directors** and **Stars** variables are similar to the genre, where multuple names are listed for each value. For the director only the first listed name will be kept. The stars column on the other hand will be split into 'Lead' (first name) and 'Supporting' (second name). Finally, for the now three columns - 'Director', 'Lead', and 'Supporting' - the names will be replaced by their rankings based on IMDb data (*4. Feature Engineering*)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Primary clean-up of the dataset\n",
    "\n",
    "1. Removing the 3 columns (not so significant for the analysis)\n",
    "2. Removing the movie that doesn't have a rating"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "-----------------------------\n",
      "Current number of columns: 10\n",
      "Current number of rows: 2000\n"
     ]
    }
   ],
   "source": [
    "# Removing unwanted columns\n",
    "dataset_pruned = dataset_raw.drop(['Certificate', 'Filming_location', 'Country_of_origin'], axis = 1)\n",
    "\n",
    "# Checking the number of rows\n",
    "print('-----------------------------')\n",
    "print(f'Current number of columns: {dataset_pruned.shape[1]}')\n",
    "print(f'Current number of rows: {dataset_pruned.shape[0]}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " Movie without rating: 85    A Man Called Otto\n",
      "Name: Title, dtype: object\n",
      "-----------------------------\n",
      "Current number of columns: 10\n",
      "Current number of rows: 1999\n"
     ]
    }
   ],
   "source": [
    "# Missing values\n",
    "dataset_pruned.isna().any()\n",
    "\n",
    "# Which doesn't have a rating\n",
    "movie_wo_rating = dataset_pruned[dataset_pruned['Rating'].isna()]['Title']\n",
    "print(f' Movie without rating: {movie_wo_rating}')\n",
    "\n",
    "# Removing movie from dataset\n",
    "delete_row = dataset_pruned[dataset_pruned['Title']=='A Man Called Otto'].index\n",
    "dataset_pruned = dataset_pruned.drop(delete_row)\n",
    "\n",
    "# Checking the number of rows\n",
    "print('-----------------------------')\n",
    "print(f'Current number of columns: {dataset_pruned.shape[1]}')\n",
    "print(f'Current number of rows: {dataset_pruned.shape[0]}')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Variable Processing\n",
    "\n",
    "1. Turning 'Month' into a categorical variable i.e. Jan = 1, Feb = 2 etc.\n",
    "2. Converting 'Runtime' into type integer \n",
    "3. Turning 'Genre' into one-hot encoding \n",
    "4. Converting 'Budget' and 'Income' to USD currency\n",
    "\n",
    "'Directors' and 'Stars' are processed in the next part "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.1. Month: Converting to categorical variable "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unique values: ['December' 'August' 'November' 'October' 'March' 'September' 'May'\n",
      " 'April' 'January' 'July' 'June' 'February' '2014' '2008']\n",
      "Number of times 2008 appears: 1\n",
      "Number of times 2014 appears: 1\n"
     ]
    }
   ],
   "source": [
    "# Checking how many unique values there are for column Month\n",
    "month_values = dataset_pruned['Month'].unique()\n",
    "odd_values_2008 = dataset_pruned['Month'].value_counts()['2008']\n",
    "odd_values_2014 = dataset_pruned['Month'].value_counts()['2014']\n",
    "\n",
    "print(f'Unique values: {month_values}')  # Here are some odd values\n",
    "print(f'Number of times 2008 appears: {odd_values_2008}')\n",
    "print(f'Number of times 2014 appears: {odd_values_2014}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0       12\n",
      "1       12\n",
      "2        8\n",
      "3       11\n",
      "4       12\n",
      "        ..\n",
      "1995     6\n",
      "1996     7\n",
      "1997    11\n",
      "1998     2\n",
      "1999     8\n",
      "Name: Month, Length: 1997, dtype: int64\n",
      "-----------------------------\n",
      "Current number of columns: 10\n",
      "Current number of rows: 1997\n"
     ]
    }
   ],
   "source": [
    "# 1. Drop the 2008 and 2014 containing row\n",
    "dataset_processed_month = dataset_pruned.drop(dataset_pruned[dataset_pruned['Month'].isin(['2008', '2014'])].index)\n",
    "\n",
    "# 2. Maping names to integers\n",
    "month_map = {\n",
    "    'January': 1, \n",
    "    'February': 2, \n",
    "    'March': 3, \n",
    "    'April': 4, \n",
    "    'May': 5,\n",
    "    'June': 6,\n",
    "    'July': 7,\n",
    "    'August': 8,\n",
    "    'September': 9,\n",
    "    'October': 10,\n",
    "    'November': 11,\n",
    "    'December': 12\n",
    "}\n",
    "\n",
    "# 3. Replacing the names (str) with integers\n",
    "dataset_processed_month['Month'] = dataset_processed_month['Month'].replace(month_map)\n",
    "print(dataset_processed_month['Month'])\n",
    "\n",
    "# 4. Checking the dataset shape\n",
    "print('-----------------------------')\n",
    "print(f'Current number of columns: {dataset_processed_month.shape[1]}')\n",
    "print(f'Current number of rows: {dataset_processed_month.shape[0]}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.2. Runtime: Convering values into integer"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of movies with unknown runtime: 1\n",
      "-----------------------------\n",
      "Current number of columns: 10\n",
      "Current number of rows: 1996\n"
     ]
    }
   ],
   "source": [
    "# 1. Checking for non-numerical values\n",
    "dataset_processed_month['Runtime'].unique()  # Movie with unknown duration\n",
    "\n",
    "# 2. Getting number of movies with uknown runtime\n",
    "unknown_runtime = dataset_processed_month['Runtime'].value_counts()['Unknown']\n",
    "print(f'Number of movies with unknown runtime: {unknown_runtime}')\n",
    "\n",
    "# 3. Removing these movies from the dataset\n",
    "dataset_processed_runtime = dataset_processed_month.drop(dataset_processed_month[dataset_processed_month['Runtime'].isin(['Unknown'])].index)\n",
    "\n",
    "# 4. Converting to int values\n",
    "dataset_processed_runtime['Runtime'] = dataset_processed_runtime['Runtime'].astype(int)\n",
    "\n",
    "# 5. Checking the dataset shape\n",
    "print('-----------------------------')\n",
    "print(f'Current number of columns: {dataset_processed_runtime.shape[1]}')\n",
    "print(f'Current number of rows: {dataset_processed_runtime.shape[0]}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.3. Genre: One-Hot Encoding"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are 20\n",
      "        possible genres and they are: ['Sport', 'History', 'Thriller', 'Animation', 'Drama', 'Family', 'Horror', 'Comedy', 'Musical', 'Fantasy', 'Sci-Fi', 'War', 'Action', 'Music', 'Mystery', 'Adventure', 'Crime', 'Western', 'Romance', 'Biography']\n"
     ]
    }
   ],
   "source": [
    "dataset_processed_genre = dataset_processed_runtime\n",
    "\n",
    "# 1. Creating a list with all genres mentioned in the dataset\n",
    "genre_list = []\n",
    "for genres in dataset_processed_genre['Genre']:\n",
    "    genre_list.append(genres.split(', '))\n",
    "\n",
    "# 2. Creating a list with genre categories\n",
    "unique_g= set()\n",
    "for genres in genre_list:\n",
    "    for genre in genres:\n",
    "        unique_g.add(genre)\n",
    "\n",
    "unique_genres = list(unique_g)\n",
    "print(f'''There are {len(unique_genres)}\n",
    "        possible genres and they are: {unique_genres}''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                            Title Sport History Thriller Animation Drama  \\\n",
      "0        Avatar: The Way of Water     0       0        0         0     0   \n",
      "1  Guillermo del Toro's Pinocchio     0       0        0         1     1   \n",
      "\n",
      "  Family Horror Comedy Musical  ... Sci-Fi War Action Music Mystery Adventure  \\\n",
      "0      0      0      0       0  ...      0   0      1     0       0         1   \n",
      "1      1      0      0       0  ...      0   0      0     0       0         0   \n",
      "\n",
      "  Crime Western Romance Biography  \n",
      "0     0       0       0         0  \n",
      "1     0       0       0         0  \n",
      "\n",
      "[2 rows x 21 columns]\n"
     ]
    }
   ],
   "source": [
    "# 3. Creating a subtable with title as the first column followed by one \n",
    "# column per genre\n",
    "genre_subtable = pd.DataFrame(columns=['Title'] + unique_genres)\n",
    "\n",
    "# 4. For each movie, assign 1 to those genres it has been assigned \n",
    "for i, row in dataset_processed_genre.iterrows():\n",
    "    new_row = {'Title': row['Title']}\n",
    "    for genre in unique_genres:\n",
    "        new_row[genre] = 0\n",
    "    for genre in row['Genre'].split(', '):\n",
    "        new_row[genre] = 1\n",
    "    genre_subtable = genre_subtable.append(new_row, ignore_index=True)\n",
    "\n",
    "print(genre_subtable.head(2))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "-----------------------------\n",
      "Current number of columns: 29\n",
      "Current number of rows: 1996\n"
     ]
    }
   ],
   "source": [
    "# 5. Merging the subtable with the main dataset\n",
    "pivot_table = genre_subtable.melt(id_vars='Title', var_name='Genre')\n",
    "pivot_table = pivot_table[pivot_table['value'] == 1]\n",
    "pivot_table = pivot_table.drop(columns=['value'])\n",
    "pivot_table = pivot_table.pivot_table(index='Title', columns='Genre', aggfunc='size', fill_value=0).reset_index()\n",
    "\n",
    "# 7. Merge the pivot table with the original DataFrame\n",
    "dataset_processed_genre = pd.merge(dataset_processed_genre, pivot_table, on='Title', how='left')\n",
    "dataset_processed_genre.drop(columns=['Genre'], inplace=True)\n",
    "\n",
    "# 8. Checking the dataset shape\n",
    "print('-----------------------------')\n",
    "print(f'Current number of columns: {dataset_processed_genre.shape[1]}')\n",
    "print(f'Current number of rows: {dataset_processed_genre.shape[0]}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.4. Budget & Income: Turning into USD currency"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of movies with unknown budget: 303\n",
      "Number of movies with empty budget: 0\n",
      "Number of movies with unknown income: 142\n",
      "Number of movies with empty budget: 0\n"
     ]
    }
   ],
   "source": [
    "dataset_processed_finance = dataset_processed_genre\n",
    "\n",
    "# Checking for how the finance data looks\n",
    "budget_values = dataset_processed_finance['Budget'].unique()\n",
    "income_values = dataset_processed_finance['Income'].unique()\n",
    "\n",
    "#print(f'Budget values: {budget_values}')\n",
    "#print(f'Income values: {income_values}')\n",
    "\n",
    "'''There are unconsistencies in how the values are writen, extra \n",
    "# spaces etc. Also presence of 'Unknown'''\n",
    "\n",
    "# 2. Getting number of movies with uknown runtime\n",
    "unknown_budget_currency = dataset_processed_finance['Budget'].value_counts()['Unknown']\n",
    "unknown_income_currency = dataset_processed_finance['Income'].value_counts()['Unknown']\n",
    "\n",
    "missing_budget_values = dataset_processed_finance['Budget'].isnull().sum()\n",
    "missing_income_values = dataset_processed_finance['Income'].isnull().sum()\n",
    "\n",
    "print(f'Number of movies with unknown budget: {unknown_budget_currency}')\n",
    "print(f'Number of movies with empty budget: {missing_budget_values}')\n",
    "print(f'Number of movies with unknown income: {unknown_income_currency}')\n",
    "print(f'Number of movies with empty budget: {missing_income_values}')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "-----------------------------\n",
      "Current number of columns (Budget): 29\n",
      "Current number of rows (Budget): 1693\n",
      "-----------------------------\n",
      "Current number of columns (Income): 29\n",
      "Current number of rows (Income): 1650\n"
     ]
    }
   ],
   "source": [
    "# 3. Dropping all movies without a budget\n",
    "dataset_processed_finance = dataset_processed_finance.drop(dataset_processed_finance[dataset_processed_finance['Budget'].isin(['Unknown'])].index)\n",
    "\n",
    "# 4. Checking the dataset shape\n",
    "print('-----------------------------')\n",
    "print(f'Current number of columns (Budget): {dataset_processed_finance.shape[1]}')\n",
    "print(f'Current number of rows (Budget): {dataset_processed_finance.shape[0]}')\n",
    "\n",
    "\n",
    "# 5. Dropping all movies without a income\n",
    "dataset_processed_finance = dataset_processed_finance.drop(dataset_processed_finance[dataset_processed_finance['Income'].isin(['Unknown'])].index)\n",
    "\n",
    "# 6. Checking the dataset shape\n",
    "print('-----------------------------')\n",
    "print(f'Current number of columns (Income): {dataset_processed_finance.shape[1]}')\n",
    "print(f'Current number of rows (Income): {dataset_processed_finance.shape[0]}')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Conversion function (converts currency and to float)\n",
    "def convert_to_usd(amount):\n",
    "    amount.replace(' ', '')\n",
    "    amount.replace('\\xa0', '')\n",
    "    if amount.startswith('$'):\n",
    "        amount = amount.strip('$').replace(',', '')   # must remove commas\n",
    "        return float(amount)   # convert str into float\n",
    "    elif amount.startswith('€'):\n",
    "        # Exchange rate for EUR to USD\n",
    "        amount = amount.strip('€').replace(',', '')\n",
    "        return float(amount) * 1.06 \n",
    "    elif amount.startswith('¥'):\n",
    "        # Exchange rate for YEN to USD\n",
    "        amount = amount.strip('¥').replace(',', '')\n",
    "        return float(amount) * 0.0075\n",
    "    elif amount.startswith('₹'):\n",
    "        # Exchange rate for RPL to USD\n",
    "        amount = amount.strip('₹').replace(',', '')\n",
    "        return float(amount) * 0.012 \n",
    "    elif amount.startswith('SEK'):\n",
    "        # Exchange rate for SEK to USD\n",
    "        amount = amount.strip('SEK').replace(',', '')\n",
    "        return float(amount) * 0.094\n",
    "    elif amount.startswith('DKK'):\n",
    "        # Exchange rate for RPL to USD\n",
    "        amount = amount.strip('DKK').replace(',', '')\n",
    "        return float(amount) * 0.14\n",
    "    elif amount.startswith('£'):\n",
    "        # Exchange rate for RPL to USD\n",
    "        amount = amount.strip('£').replace(',', '')\n",
    "        return float(amount) * 1.21  \n",
    "    else:\n",
    "        return None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 1650 entries, 0 to 1995\n",
      "Data columns (total 29 columns):\n",
      " #   Column     Non-Null Count  Dtype  \n",
      "---  ------     --------------  -----  \n",
      " 0   Title      1650 non-null   object \n",
      " 1   Rating     1650 non-null   float64\n",
      " 2   Year       1650 non-null   int64  \n",
      " 3   Month      1650 non-null   int64  \n",
      " 4   Runtime    1650 non-null   int32  \n",
      " 5   Directors  1650 non-null   object \n",
      " 6   Stars      1650 non-null   object \n",
      " 7   Budget     1641 non-null   float64\n",
      " 8   Income     1650 non-null   float64\n",
      " 9   Action     1650 non-null   int64  \n",
      " 10  Adventure  1650 non-null   int64  \n",
      " 11  Animation  1650 non-null   int64  \n",
      " 12  Biography  1650 non-null   int64  \n",
      " 13  Comedy     1650 non-null   int64  \n",
      " 14  Crime      1650 non-null   int64  \n",
      " 15  Drama      1650 non-null   int64  \n",
      " 16  Family     1650 non-null   int64  \n",
      " 17  Fantasy    1650 non-null   int64  \n",
      " 18  History    1650 non-null   int64  \n",
      " 19  Horror     1650 non-null   int64  \n",
      " 20  Music      1650 non-null   int64  \n",
      " 21  Musical    1650 non-null   int64  \n",
      " 22  Mystery    1650 non-null   int64  \n",
      " 23  Romance    1650 non-null   int64  \n",
      " 24  Sci-Fi     1650 non-null   int64  \n",
      " 25  Sport      1650 non-null   int64  \n",
      " 26  Thriller   1650 non-null   int64  \n",
      " 27  War        1650 non-null   int64  \n",
      " 28  Western    1650 non-null   int64  \n",
      "dtypes: float64(3), int32(1), int64(22), object(3)\n",
      "memory usage: 380.3+ KB\n"
     ]
    }
   ],
   "source": [
    "# 7. Applying the conversion function\n",
    "dataset_processed_finance['Budget'] = dataset_processed_finance['Budget'].apply(convert_to_usd)\n",
    "dataset_processed_finance['Income'] = dataset_processed_finance['Income'].apply(convert_to_usd)\n",
    "\n",
    "dataset_processed_finance.info()  # 9 missing values in Budget"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "-----------------------------\n",
      "Current number of columns: 29\n",
      "Current number of rows: 1641\n"
     ]
    }
   ],
   "source": [
    "# 8. Removing 9 titles with empty budget values \n",
    "dataset_processed_finance = dataset_processed_finance.dropna(axis=0, subset=['Budget'])\n",
    "\n",
    "# 9. Checking the dataset shape\n",
    "print('-----------------------------')\n",
    "print(f'Current number of columns: {dataset_processed_finance.shape[1]}')\n",
    "print(f'Current number of rows: {dataset_processed_finance.shape[0]}')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Feature Engineering: 'Directors' and 'Stars'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This part needs more work and additional database. I think these columns will have an effect on the scoring of the movie, but they are strings right now. I think we should:\n",
    "1. Directors column should only contain the first director and ignore all other\n",
    "2. Stars column should be split into 'Lead' and 'Supporting' where the first actor is added to the lead and the second to the supporting\n",
    "3. Replace the directors and actors with their imdb score of how good they are or some popularity score"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4.1. Directors\n",
    "\n",
    "1. Only one director/film, only the first director will be kept\n",
    "2. Replace director with top 50 direcor = 1 or not top 50 director = 0"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### One director per movie"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "dataset_processed_1 = dataset_processed_finance\n",
    "\n",
    "# 1. Only keeping the first director\n",
    "dataset_processed_1['Top_50_Director'] = dataset_processed_1['Directors'].str.split(',').str[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Replacing the director's name with ranking"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 1. Importing top directors dataset\n",
    "dataset_directors = pd.read_csv('./data/top_50_directors.csv')\n",
    "\n",
    "# 2. If Director is in dataset_directors, replace name with 1\n",
    "# else replace name with 0\n",
    "#replace = dataset_processed_1['Director_Top'].isin(dataset_directors['Name'])\n",
    "#dataset_processed_1['Director_Top'] = replace.astype(int)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 3. Check the updated table\n",
    "#dataset_processed_1.head(2)\n",
    "#print(f'Number of top 50 directors in dataset: {(dataset_processed_1[\"Director_Top\"] == 1).astype(int).sum()}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4.2. Actors\n",
    "\n",
    "1. Splitting 'Stars' column into two; (1) 'Lead' (the first value of original column) and (2) 'Supporting' (the second value of the original column)\n",
    "2. Replace the name in these two columns by their rating"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Splitting Stars into Lead and Supporting"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 1. Creating a new column 'Lead' with the first value of 'Stars'\n",
    "dataset_processed_1['Lead'] = dataset_processed_1['Stars'].fillna('').str.split(',', expand=True)[0]\n",
    "\n",
    "# 2. Creating a new column 'Supporting' with the second value of 'Stars'\n",
    "dataset_processed_1['Supporting'] = dataset_processed_1['Stars'].fillna('').str.split(',', expand=True)[1]\n",
    "\n",
    "# 3. Dropping the original 'Stars' column\n",
    "dataset_processed_1.drop(columns=['Stars'], inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Replacing name with rating"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 1. Importing top a list of 1000 top actors and actresses\n",
    "dataset_top_actors = pd.read_csv('./data/top_1000_actors.csv', usecols=['Position', 'Name'])\n",
    "\n",
    "# 2. If star is in dataset_directors, replace name with 1\n",
    "# else replace name with 0\n",
    "replace_lead = dataset_processed_1['Lead'].isin(dataset_top_actors['Name'])\n",
    "dataset_processed_1['Lead_Top'] = replace_lead.astype(int)\n",
    "\n",
    "replace_support = dataset_processed_1['Supporting'].isin(dataset_top_actors['Name'])\n",
    "dataset_processed_1['Supporting_Top'] = replace_support.astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of top 1000 actors in dataset: 480\n"
     ]
    }
   ],
   "source": [
    "# 3. Check the updated table\n",
    "dataset_processed_1.head(2)\n",
    "print(f'Number of top 1000 actors in dataset: {(dataset_processed_1[\"Lead_Top\"] == 0).astype(int).sum()}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 1641 entries, 0 to 1995\n",
      "Data columns (total 33 columns):\n",
      " #   Column           Non-Null Count  Dtype  \n",
      "---  ------           --------------  -----  \n",
      " 0   Title            1641 non-null   object \n",
      " 1   Rating           1641 non-null   float64\n",
      " 2   Year             1641 non-null   int64  \n",
      " 3   Month            1641 non-null   int64  \n",
      " 4   Runtime          1641 non-null   int32  \n",
      " 5   Directors        1641 non-null   object \n",
      " 6   Budget           1641 non-null   float64\n",
      " 7   Income           1641 non-null   float64\n",
      " 8   Action           1641 non-null   int64  \n",
      " 9   Adventure        1641 non-null   int64  \n",
      " 10  Animation        1641 non-null   int64  \n",
      " 11  Biography        1641 non-null   int64  \n",
      " 12  Comedy           1641 non-null   int64  \n",
      " 13  Crime            1641 non-null   int64  \n",
      " 14  Drama            1641 non-null   int64  \n",
      " 15  Family           1641 non-null   int64  \n",
      " 16  Fantasy          1641 non-null   int64  \n",
      " 17  History          1641 non-null   int64  \n",
      " 18  Horror           1641 non-null   int64  \n",
      " 19  Music            1641 non-null   int64  \n",
      " 20  Musical          1641 non-null   int64  \n",
      " 21  Mystery          1641 non-null   int64  \n",
      " 22  Romance          1641 non-null   int64  \n",
      " 23  Sci-Fi           1641 non-null   int64  \n",
      " 24  Sport            1641 non-null   int64  \n",
      " 25  Thriller         1641 non-null   int64  \n",
      " 26  War              1641 non-null   int64  \n",
      " 27  Western          1641 non-null   int64  \n",
      " 28  Top_50_Director  1641 non-null   object \n",
      " 29  Lead             1641 non-null   object \n",
      " 30  Supporting       1641 non-null   object \n",
      " 31  Lead_Top         1641 non-null   int32  \n",
      " 32  Supporting_Top   1641 non-null   int32  \n",
      "dtypes: float64(3), int32(3), int64(22), object(5)\n",
      "memory usage: 416.7+ KB\n"
     ]
    }
   ],
   "source": [
    "dataset_processed_1.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Ridley Scott        13\n",
       "Steven Spielberg    12\n",
       "Shawn Levy          10\n",
       "Clint Eastwood      10\n",
       "Michael Bay          9\n",
       "                    ..\n",
       "Michael Engler       1\n",
       "May el Toukhy        1\n",
       "David Yarovesky      1\n",
       "Josh Cooley          1\n",
       "Chris Kentis         1\n",
       "Name: Directors, Length: 845, dtype: int64"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dataset_processed_1['Directors'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. Multiple rating of the same movie\n",
    "\n",
    "Another problem is when a movie appears more than once in the list. Therefore, only the top rating for the movie will be kept and all others removed from the dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 1. Keeping only the highest score for each name\n",
    "dataset_final = dataset_processed_1.sort_values(by=['Rating'], ascending=False)\n",
    "dataset_final = dataset_processed_1.drop_duplicates(subset=['Title'], keep='first')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 1633 entries, 0 to 1995\n",
      "Data columns (total 33 columns):\n",
      " #   Column           Non-Null Count  Dtype  \n",
      "---  ------           --------------  -----  \n",
      " 0   Title            1633 non-null   object \n",
      " 1   Rating           1633 non-null   float64\n",
      " 2   Year             1633 non-null   int64  \n",
      " 3   Month            1633 non-null   int64  \n",
      " 4   Runtime          1633 non-null   int32  \n",
      " 5   Directors        1633 non-null   object \n",
      " 6   Budget           1633 non-null   float64\n",
      " 7   Income           1633 non-null   float64\n",
      " 8   Action           1633 non-null   int64  \n",
      " 9   Adventure        1633 non-null   int64  \n",
      " 10  Animation        1633 non-null   int64  \n",
      " 11  Biography        1633 non-null   int64  \n",
      " 12  Comedy           1633 non-null   int64  \n",
      " 13  Crime            1633 non-null   int64  \n",
      " 14  Drama            1633 non-null   int64  \n",
      " 15  Family           1633 non-null   int64  \n",
      " 16  Fantasy          1633 non-null   int64  \n",
      " 17  History          1633 non-null   int64  \n",
      " 18  Horror           1633 non-null   int64  \n",
      " 19  Music            1633 non-null   int64  \n",
      " 20  Musical          1633 non-null   int64  \n",
      " 21  Mystery          1633 non-null   int64  \n",
      " 22  Romance          1633 non-null   int64  \n",
      " 23  Sci-Fi           1633 non-null   int64  \n",
      " 24  Sport            1633 non-null   int64  \n",
      " 25  Thriller         1633 non-null   int64  \n",
      " 26  War              1633 non-null   int64  \n",
      " 27  Western          1633 non-null   int64  \n",
      " 28  Top_50_Director  1633 non-null   object \n",
      " 29  Lead             1633 non-null   object \n",
      " 30  Supporting       1633 non-null   object \n",
      " 31  Lead_Top         1633 non-null   int32  \n",
      " 32  Supporting_Top   1633 non-null   int32  \n",
      "dtypes: float64(3), int32(3), int64(22), object(5)\n",
      "memory usage: 414.6+ KB\n"
     ]
    }
   ],
   "source": [
    "dataset_final.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6. Summary of the Dataprocessing and Feature Engineering"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 6.1. The new Dataset \n",
    "\n",
    "| Variable | Processing description | Type |\n",
    "| --- | --- | --- |\n",
    "| Title | Movie title | str |\n",
    "| Rating | Movie rating | float |\n",
    "| Year | Release year | int |\n",
    "| Month | Release month | int | \n",
    "| Runtime | Movie duration in minutes | int |  | Convert to int |\n",
    "| Genre | 20 columns with differnt genres with One-Hot Encoding (1-0) | int |\n",
    "| Budget | Set to USD | int |\n",
    "| Income | Set to USD | int |\n",
    "| Director | Director name | str |\n",
    "| Director_Top | If director is a top 50 director or not (1-0) | int |\n",
    "| Lead | Lead star name | str |\n",
    "| Lead_Top | Name replaced by actor's rating (1-1001, 1001 given to those that are not in the list) | int |\n",
    "| Supporting | Supporting star name | str |\n",
    "| Supporting_Top | Name replaced by actor's rating (1-1001, 1001 given to those that are not in the list)| int |"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "def create_table(dataframes, step):\n",
    "    num_dataframes = len(dataframes)\n",
    "    num_rows = []\n",
    "    num_cols = []\n",
    "    for df in dataframes:\n",
    "        num_rows.append(df.shape[0])\n",
    "        num_cols.append(df.shape[1])\n",
    "    indices = range(0, num_dataframes, step)\n",
    "    df_dict = {'Rows': num_rows, 'Columns': num_cols}\n",
    "    table = pd.DataFrame(df_dict, index=range(1, num_dataframes+1))\n",
    "    table.index.name = 'Processing step'\n",
    "    table = table.iloc[indices, :]\n",
    "    return table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 6.2. Data loss due to processing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "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>Rows</th>\n",
       "      <th>Columns</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Processing step</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2000</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1999</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1997</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1996</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1996</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1641</td>\n",
       "      <td>33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1641</td>\n",
       "      <td>33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1633</td>\n",
       "      <td>33</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 Rows  Columns\n",
       "Processing step               \n",
       "1                2000       13\n",
       "2                1999       10\n",
       "3                1997       10\n",
       "4                1996       10\n",
       "5                1996       29\n",
       "6                1641       33\n",
       "7                1641       33\n",
       "8                1633       33"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_list = [\n",
    "    dataset_raw, \n",
    "    dataset_pruned, \n",
    "    dataset_processed_month,\n",
    "    dataset_processed_runtime,\n",
    "    dataset_processed_genre,\n",
    "    dataset_processed_finance,\n",
    "    dataset_processed_1,\n",
    "    dataset_final]\n",
    "step = 1\n",
    "create_table(df_list, step)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7. Exporting the new dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "#dataset_processed_1.to_csv('new_dataset.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.7.15"
  },
  "vscode": {
   "interpreter": {
    "hash": "c0de92a10c1f65e53f9acff1f0bafd09b320a4aed9d91aa143eea6431dc87452"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}