clean_the_numbers.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import sys\n",
    "import csv\n",
    "from time import strptime\n",
    "from datetime import datetime as dt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 221,
   "metadata": {},
   "outputs": [],
   "source": [
    "csv.field_size_limit(sys.maxsize)\n",
    "np.set_printoptions(threshold=sys.maxsize)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 222,
   "metadata": {},
   "outputs": [],
   "source": [
    "names_full = pd.read_csv('data/numbers_budget.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 223,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(5928, 5)"
      ]
     },
     "execution_count": 223,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "names_full.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 224,
   "metadata": {},
   "outputs": [],
   "source": [
    "tmdb = pd.read_csv('data/tmdb/tmdb_5000_movies.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 225,
   "metadata": {},
   "outputs": [],
   "source": [
    "full_data = pd.read_csv('data/tmdb/fulltmdb.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 226,
   "metadata": {},
   "outputs": [],
   "source": [
    "def format_date(date):\n",
    "    try:\n",
    "        date_obj = dt.strptime(date, '%Y-%m-%d')\n",
    "        return dt.strftime(date_obj, '%b %d, %Y')\n",
    "    except:\n",
    "        return 'False'\n",
    "\n",
    "def has_day_month_year(date):\n",
    "    date = date.replace(u'\\xa0', u' ')\n",
    "    return len(date.split(' ')) == 3\n",
    "\n",
    "def format_tmdb_date(row):\n",
    "    date = row.release_date.to_string(index=False).strip()\n",
    "    return format_date(date) if date != 'NaN' else 'False'\n",
    "\n",
    "def get_tmdb_date(name):\n",
    "    item = tmdb.loc[tmdb['original_title'] == name]\n",
    "    if item.shape[0]:\n",
    "        return format_tmdb_date(item)\n",
    "    item = full_data.loc[full_data['original_title'] == name]\n",
    "    if item.shape[0]:\n",
    "        return format_tmdb_date(item)\n",
    "\n",
    "def replace_date(row):\n",
    "    date = row['ReleaseDate']\n",
    "    if not has_day_month_year(date):\n",
    "        row['ReleaseDate'] = get_tmdb_date(row['Movie'])\n",
    "    return row"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 227,
   "metadata": {},
   "outputs": [],
   "source": [
    "added_dates = names_full.apply(replace_date, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 228,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(5928, 5)"
      ]
     },
     "execution_count": 228,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "added_dates.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 229,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(49, 5)"
      ]
     },
     "execution_count": 229,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# movies without dates, skip them\n",
    "added_dates[(added_dates['ReleaseDate'] == 'False') | (pd.isna(added_dates['ReleaseDate']))].shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 230,
   "metadata": {},
   "outputs": [],
   "source": [
    "with_dates = added_dates[(added_dates['ReleaseDate'] != 'False') & (~pd.isna(added_dates['ReleaseDate']))]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 231,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(5879, 5)"
      ]
     },
     "execution_count": 231,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "with_dates.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 232,
   "metadata": {},
   "outputs": [],
   "source": [
    "zero_worldwide_gross = with_dates.loc[with_dates['WorldwideGross'] == 0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 233,
   "metadata": {},
   "outputs": [],
   "source": [
    "zero_budget = with_dates.loc[with_dates['ProductionBudget'] == 0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 234,
   "metadata": {},
   "outputs": [],
   "source": [
    "gross_movies = with_dates.loc[(with_dates.WorldwideGross > 0) & (with_dates.ProductionBudget > 0)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 235,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((348, 5), (0, 5), (5531, 5))"
      ]
     },
     "execution_count": 235,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "zero_worldwide_gross.shape, zero_budget.shape, gross_movies.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 238,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['ReleaseDate', 'Movie', 'ProductionBudget', 'DomesticGross',\n",
       "       'WorldwideGross'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 238,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gross_movies.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 244,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_month(date):\n",
    "    return strptime(date.split(' ')[0], '%b').tm_mon\n",
    "\n",
    "def get_day(date):\n",
    "    return date.split(' ')[1].split(',')[0]\n",
    "\n",
    "def get_year(date):\n",
    "    return date.split(' ')[2]\n",
    "\n",
    "def date_if_weekend(date):\n",
    "    return str(dt.strptime(date, '%b %d, %Y').weekday() > 4)\n",
    "\n",
    "def apply_func_on_dataframe(df, col, func_list):\n",
    "    return [[func(row[col]) for i, row in df.iterrows()] for func in func_list]\n",
    "\n",
    "def get_date_lists(df):\n",
    "    return apply_func_on_dataframe(df, 'ReleaseDate', [get_year, get_month, get_day, date_if_weekend])\n",
    "\n",
    "def calculate_profitability(revenue, budget):\n",
    "    revenue, budget = float(revenue), float(budget)\n",
    "    profit = revenue - budget\n",
    "    return profit/budget if profit > 0 else profit/revenue \n",
    "\n",
    "def get_profitability_df(df, col_revenue, col_budget):\n",
    "    return [calculate_profitability(row[col_revenue], row[col_budget]) for i, row in df.iterrows()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 245,
   "metadata": {},
   "outputs": [],
   "source": [
    "list_profitability = get_profitability_df(gross_movies, 'WorldwideGross', 'ProductionBudget')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 246,
   "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>ReleaseDate</th>\n",
       "      <th>Movie</th>\n",
       "      <th>ProductionBudget</th>\n",
       "      <th>WorldwideGross</th>\n",
       "      <th>profitability</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>Apr 23, 2019</td>\n",
       "      <td>Avengers: Endgame</td>\n",
       "      <td>400000000</td>\n",
       "      <td>2797800564</td>\n",
       "      <td>5.9945014100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>May 20, 2011</td>\n",
       "      <td>Pirates of the Caribbean: On Stranger Tides</td>\n",
       "      <td>379000000</td>\n",
       "      <td>1045663875</td>\n",
       "      <td>1.7590075858</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Apr 22, 2015</td>\n",
       "      <td>Avengers: Age of Ultron</td>\n",
       "      <td>365000000</td>\n",
       "      <td>1403013963</td>\n",
       "      <td>2.8438738712</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>Dec 16, 2015</td>\n",
       "      <td>Star Wars Ep. VII: The Force Awakens</td>\n",
       "      <td>306000000</td>\n",
       "      <td>2068223624</td>\n",
       "      <td>5.7589007320</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>Apr 25, 2018</td>\n",
       "      <td>Avengers: Infinity War</td>\n",
       "      <td>300000000</td>\n",
       "      <td>2048359754</td>\n",
       "      <td>5.8278658467</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5920</td>\n",
       "      <td>Oct 8, 2004</td>\n",
       "      <td>Primer</td>\n",
       "      <td>7000</td>\n",
       "      <td>841926</td>\n",
       "      <td>119.2751428571</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5921</td>\n",
       "      <td>May 26, 2006</td>\n",
       "      <td>Cavite</td>\n",
       "      <td>7000</td>\n",
       "      <td>71644</td>\n",
       "      <td>9.2348571429</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5924</td>\n",
       "      <td>Apr 2, 1999</td>\n",
       "      <td>Following</td>\n",
       "      <td>6000</td>\n",
       "      <td>240495</td>\n",
       "      <td>39.0825000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5925</td>\n",
       "      <td>Jul 13, 2005</td>\n",
       "      <td>Return to the Land of Wonders</td>\n",
       "      <td>5000</td>\n",
       "      <td>1338</td>\n",
       "      <td>-2.7369207773</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5927</td>\n",
       "      <td>Aug 5, 2005</td>\n",
       "      <td>My Date With Drew</td>\n",
       "      <td>1100</td>\n",
       "      <td>181041</td>\n",
       "      <td>163.5827272727</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5531 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       ReleaseDate                                        Movie  \\\n",
       "0     Apr 23, 2019                            Avengers: Endgame   \n",
       "1     May 20, 2011  Pirates of the Caribbean: On Stranger Tides   \n",
       "2     Apr 22, 2015                      Avengers: Age of Ultron   \n",
       "3     Dec 16, 2015         Star Wars Ep. VII: The Force Awakens   \n",
       "4     Apr 25, 2018                       Avengers: Infinity War   \n",
       "...            ...                                          ...   \n",
       "5920   Oct 8, 2004                                       Primer   \n",
       "5921  May 26, 2006                                       Cavite   \n",
       "5924   Apr 2, 1999                                    Following   \n",
       "5925  Jul 13, 2005                Return to the Land of Wonders   \n",
       "5927   Aug 5, 2005                            My Date With Drew   \n",
       "\n",
       "      ProductionBudget  WorldwideGross  profitability  \n",
       "0            400000000      2797800564   5.9945014100  \n",
       "1            379000000      1045663875   1.7590075858  \n",
       "2            365000000      1403013963   2.8438738712  \n",
       "3            306000000      2068223624   5.7589007320  \n",
       "4            300000000      2048359754   5.8278658467  \n",
       "...                ...             ...            ...  \n",
       "5920              7000          841926 119.2751428571  \n",
       "5921              7000           71644   9.2348571429  \n",
       "5924              6000          240495  39.0825000000  \n",
       "5925              5000            1338  -2.7369207773  \n",
       "5927              1100          181041 163.5827272727  \n",
       "\n",
       "[5531 rows x 5 columns]"
      ]
     },
     "execution_count": 246,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = gross_movies.copy()\n",
    "df['profitability'] = list_profitability\n",
    "df = df.drop('DomesticGross', axis=1)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 247,
   "metadata": {},
   "outputs": [],
   "source": [
    "deleted_least_profitable = df.sort_values('profitability')[50:-1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 248,
   "metadata": {},
   "outputs": [],
   "source": [
    "list_year, list_month, list_day, list_if_weekend = get_date_lists(deleted_least_profitable)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 249,
   "metadata": {},
   "outputs": [],
   "source": [
    "deleted_least_profitable['year'] = list_year\n",
    "deleted_least_profitable['month'] = list_month\n",
    "deleted_least_profitable['day'] = list_day\n",
    "deleted_least_profitable['weekend'] = list_if_weekend\n",
    "deleted_least_profitable = deleted_least_profitable.drop('ReleaseDate', axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 250,
   "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>Movie</th>\n",
       "      <th>ProductionBudget</th>\n",
       "      <th>WorldwideGross</th>\n",
       "      <th>profitability</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "      <th>weekend</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>4692</td>\n",
       "      <td>Hevi reissu</td>\n",
       "      <td>3800000</td>\n",
       "      <td>10153</td>\n",
       "      <td>-373.2736137102</td>\n",
       "      <td>2018</td>\n",
       "      <td>10</td>\n",
       "      <td>5</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5098</td>\n",
       "      <td>Say Uncle</td>\n",
       "      <td>2000000</td>\n",
       "      <td>5361</td>\n",
       "      <td>-372.0647267301</td>\n",
       "      <td>2006</td>\n",
       "      <td>6</td>\n",
       "      <td>23</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4745</td>\n",
       "      <td>Taxman</td>\n",
       "      <td>3500000</td>\n",
       "      <td>9871</td>\n",
       "      <td>-353.5740046601</td>\n",
       "      <td>1999</td>\n",
       "      <td>9</td>\n",
       "      <td>17</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4674</td>\n",
       "      <td>The Loved Ones</td>\n",
       "      <td>4000000</td>\n",
       "      <td>12302</td>\n",
       "      <td>-324.1503820517</td>\n",
       "      <td>2012</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4519</td>\n",
       "      <td>All Hat</td>\n",
       "      <td>4900000</td>\n",
       "      <td>15198</td>\n",
       "      <td>-321.4108435320</td>\n",
       "      <td>2008</td>\n",
       "      <td>1</td>\n",
       "      <td>18</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5851</td>\n",
       "      <td>Super Size Me</td>\n",
       "      <td>65000</td>\n",
       "      <td>22233808</td>\n",
       "      <td>341.0585846154</td>\n",
       "      <td>2004</td>\n",
       "      <td>5</td>\n",
       "      <td>7</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5542</td>\n",
       "      <td>The Blair Witch Project</td>\n",
       "      <td>600000</td>\n",
       "      <td>248300000</td>\n",
       "      <td>412.8333333333</td>\n",
       "      <td>1999</td>\n",
       "      <td>7</td>\n",
       "      <td>14</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5820</td>\n",
       "      <td>The Gallows</td>\n",
       "      <td>100000</td>\n",
       "      <td>41656474</td>\n",
       "      <td>415.5647400000</td>\n",
       "      <td>2015</td>\n",
       "      <td>7</td>\n",
       "      <td>9</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5629</td>\n",
       "      <td>Paranormal Activity</td>\n",
       "      <td>450000</td>\n",
       "      <td>194183034</td>\n",
       "      <td>430.5178533333</td>\n",
       "      <td>2009</td>\n",
       "      <td>9</td>\n",
       "      <td>25</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5755</td>\n",
       "      <td>Mad Max</td>\n",
       "      <td>200000</td>\n",
       "      <td>99750000</td>\n",
       "      <td>497.7500000000</td>\n",
       "      <td>1980</td>\n",
       "      <td>3</td>\n",
       "      <td>21</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5480 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                        Movie  ProductionBudget  WorldwideGross  \\\n",
       "4692              Hevi reissu           3800000           10153   \n",
       "5098                Say Uncle           2000000            5361   \n",
       "4745                   Taxman           3500000            9871   \n",
       "4674           The Loved Ones           4000000           12302   \n",
       "4519                  All Hat           4900000           15198   \n",
       "...                       ...               ...             ...   \n",
       "5851            Super Size Me             65000        22233808   \n",
       "5542  The Blair Witch Project            600000       248300000   \n",
       "5820              The Gallows            100000        41656474   \n",
       "5629      Paranormal Activity            450000       194183034   \n",
       "5755                  Mad Max            200000        99750000   \n",
       "\n",
       "       profitability  year  month day weekend  \n",
       "4692 -373.2736137102  2018     10   5   False  \n",
       "5098 -372.0647267301  2006      6  23   False  \n",
       "4745 -353.5740046601  1999      9  17   False  \n",
       "4674 -324.1503820517  2012      6   1   False  \n",
       "4519 -321.4108435320  2008      1  18   False  \n",
       "...              ...   ...    ...  ..     ...  \n",
       "5851  341.0585846154  2004      5   7   False  \n",
       "5542  412.8333333333  1999      7  14   False  \n",
       "5820  415.5647400000  2015      7   9   False  \n",
       "5629  430.5178533333  2009      9  25   False  \n",
       "5755  497.7500000000  1980      3  21   False  \n",
       "\n",
       "[5480 rows x 8 columns]"
      ]
     },
     "execution_count": 250,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "deleted_least_profitable"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 252,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.2500000000   -0.3783815046\n",
       "0.5000000000    0.9208483357\n",
       "0.7500000000    3.0160915000\n",
       "Name: profitability, dtype: float64"
      ]
     },
     "execution_count": 252,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "deleted_least_profitable.profitability.quantile(np.linspace(.25, 1, 3, 0))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 254,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(5480, 8)"
      ]
     },
     "execution_count": 254,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "deleted_least_profitable.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 253,
   "metadata": {},
   "outputs": [],
   "source": [
    "deleted_least_profitable.to_csv('data/cleaned_the_numbers.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}