Data_Cleaning.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Data Cleaning and Merging Dataframes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pickle\n",
    "import requests\n",
    "from bs4 import BeautifulSoup\n",
    "import re\n",
    "import pandas as pd\n",
    "import time\n",
    "import seaborn as sns\n",
    "import datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import all the dataframes\n",
    "mojog_df = pd.read_pickle('mojog_df.pkl')\n",
    "#unemp_df = pd.read_pickle(\"data/unemp_df.pkl\")\n",
    "#youtube_df = pd.read_pickle('data/youtube_df.pkl')\n",
    "omdb_df = pd.read_pickle('omdb_df.pkl')\n",
    "director_df = pd.read_pickle('director_df.pkl')\n",
    "actor_df = pd.read_pickle('actor_df.pkl')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 600 entries, 0 to 599\n",
      "Data columns (total 9 columns):\n",
      "movie_name    600 non-null object\n",
      "movie         600 non-null object\n",
      "opening       600 non-null int64\n",
      "date_time     600 non-null datetime64[ns]\n",
      "theaters      600 non-null int64\n",
      "tot_gross     600 non-null int64\n",
      "actors        578 non-null object\n",
      "directors     549 non-null object\n",
      "gtrend        600 non-null float64\n",
      "dtypes: datetime64[ns](1), float64(1), int64(3), object(4)\n",
      "memory usage: 46.9+ KB\n"
     ]
    }
   ],
   "source": [
    "#clean mojo df\n",
    "mojog_df.opening = mojog_df.opening.replace(\"\\$\",\"\", regex = True).replace(\",\",\"\", regex = True).str.strip()\n",
    "mojog_df.opening = pd.to_numeric(mojog_df.opening)\n",
    "mojog_df.tot_gross = mojog_df.tot_gross.replace(\"\\$\",\"\", regex = True).replace(\",\",\"\", regex = True).str.strip()\n",
    "mojog_df.tot_gross = pd.to_numeric(mojog_df.tot_gross)\n",
    "mojog_df.theaters = mojog_df.theaters.replace(\",\",\"\", regex = True).str.strip()\n",
    "mojog_df.theaters = pd.to_numeric(mojog_df.theaters)\n",
    "mojog_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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_name</th>\n",
       "      <th>movie</th>\n",
       "      <th>opening</th>\n",
       "      <th>date_time</th>\n",
       "      <th>theaters</th>\n",
       "      <th>tot_gross</th>\n",
       "      <th>actors</th>\n",
       "      <th>directors</th>\n",
       "      <th>gtrend</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>The Hunger Games: Catching Fire</td>\n",
       "      <td>The Hunger Games: Catching Fire</td>\n",
       "      <td>158074286</td>\n",
       "      <td>2013-11-22</td>\n",
       "      <td>4163</td>\n",
       "      <td>424668047</td>\n",
       "      <td>[Jennifer Lawrence, Josh Hutcherson, Liam Hems...</td>\n",
       "      <td>[Francis Lawrence]</td>\n",
       "      <td>0.564</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Iron Man 3</td>\n",
       "      <td>Iron Man 3</td>\n",
       "      <td>174144585</td>\n",
       "      <td>2013-05-03</td>\n",
       "      <td>4253</td>\n",
       "      <td>409013994</td>\n",
       "      <td>[Robert Downey, Jr., Gwyneth Paltrow, Don Chea...</td>\n",
       "      <td>[Shane Black]</td>\n",
       "      <td>0.707</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Frozen</td>\n",
       "      <td>Frozen</td>\n",
       "      <td>243390</td>\n",
       "      <td>2013-11-22</td>\n",
       "      <td>1</td>\n",
       "      <td>400738009</td>\n",
       "      <td>[Kristen Bell, Josh Gad, Alan Tudyk]</td>\n",
       "      <td>[Chris Buck, Jennifer Lee]</td>\n",
       "      <td>0.123</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Despicable Me 2</td>\n",
       "      <td>Despicable Me 2</td>\n",
       "      <td>83517315</td>\n",
       "      <td>2013-07-03</td>\n",
       "      <td>3997</td>\n",
       "      <td>368061265</td>\n",
       "      <td>[Steve Carell, Kristen Wiig, Benjamin Bratt, R...</td>\n",
       "      <td>[Pierre Coffin, Chris Renaud]</td>\n",
       "      <td>0.656</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Man of Steel</td>\n",
       "      <td>Man of Steel</td>\n",
       "      <td>116619362</td>\n",
       "      <td>2013-06-14</td>\n",
       "      <td>4207</td>\n",
       "      <td>291045518</td>\n",
       "      <td>[Henry Cavill, Amy Adams, Michael Shannon, Kev...</td>\n",
       "      <td>[Zack Snyder]</td>\n",
       "      <td>0.607</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        movie_name                            movie  \\\n",
       "0  The Hunger Games: Catching Fire  The Hunger Games: Catching Fire   \n",
       "1                       Iron Man 3                       Iron Man 3   \n",
       "2                           Frozen                           Frozen   \n",
       "3                  Despicable Me 2                  Despicable Me 2   \n",
       "4                     Man of Steel                     Man of Steel   \n",
       "\n",
       "     opening  date_time  theaters  tot_gross  \\\n",
       "0  158074286 2013-11-22      4163  424668047   \n",
       "1  174144585 2013-05-03      4253  409013994   \n",
       "2     243390 2013-11-22         1  400738009   \n",
       "3   83517315 2013-07-03      3997  368061265   \n",
       "4  116619362 2013-06-14      4207  291045518   \n",
       "\n",
       "                                              actors  \\\n",
       "0  [Jennifer Lawrence, Josh Hutcherson, Liam Hems...   \n",
       "1  [Robert Downey, Jr., Gwyneth Paltrow, Don Chea...   \n",
       "2               [Kristen Bell, Josh Gad, Alan Tudyk]   \n",
       "3  [Steve Carell, Kristen Wiig, Benjamin Bratt, R...   \n",
       "4  [Henry Cavill, Amy Adams, Michael Shannon, Kev...   \n",
       "\n",
       "                       directors  gtrend  \n",
       "0             [Francis Lawrence]   0.564  \n",
       "1                  [Shane Black]   0.707  \n",
       "2     [Chris Buck, Jennifer Lee]   0.123  \n",
       "3  [Pierre Coffin, Chris Renaud]   0.656  \n",
       "4                  [Zack Snyder]   0.607  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mojog_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 500 entries, 0 to 499\n",
      "Data columns (total 5 columns):\n",
      "movie_name    500 non-null object\n",
      "Yviews        500 non-null int64\n",
      "Ycomments     500 non-null int64\n",
      "Ydislikes     500 non-null int64\n",
      "Ylikes        500 non-null int64\n",
      "dtypes: int64(4), object(1)\n",
      "memory usage: 19.6+ KB\n"
     ]
    }
   ],
   "source": [
    "# Clean youtube df \n",
    "# select youtube df relevant data\n",
    "youtube_df = youtube_df[[\"movie_name\", \"viewCount\", \"commentCount\", \"dislikeCount\", \"likeCount\"]]\n",
    "\n",
    "#replace nulls with 0\n",
    "youtube_df = youtube_df.fillna(0)\n",
    "# convert numeric str values to int\n",
    "youtube_df.viewCount = pd.to_numeric(youtube_df.viewCount)\n",
    "youtube_df.dislikeCount = pd.to_numeric(youtube_df.dislikeCount)\n",
    "youtube_df.commentCount = pd.to_numeric(youtube_df.commentCount)\n",
    "youtube_df.likeCount = pd.to_numeric(youtube_df.likeCount)\n",
    "youtube_df = youtube_df.rename(columns ={\"viewCount\":\"Yviews\", \"commentCount\": \"Ycomments\", \n",
    "                                         \"dislikeCount\": \"Ydislikes\", \"likeCount\":\"Ylikes\"})\n",
    "youtube_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 559 entries, 0 to 573\n",
      "Data columns (total 7 columns):\n",
      "imdb               559 non-null float64\n",
      "metacritic         559 non-null int64\n",
      "movie              559 non-null object\n",
      "rated              559 non-null object\n",
      "rotten_tomatoes    559 non-null int64\n",
      "runtime            559 non-null int64\n",
      "year               559 non-null int64\n",
      "dtypes: float64(1), int64(4), object(2)\n",
      "memory usage: 34.9+ KB\n"
     ]
    }
   ],
   "source": [
    "# Clean omdb data\n",
    "#omdb_df.imdb = pd.to_numeric(omdb_df.imdb.replace(\"/10\",\"\", regex = True).str.strip())\n",
    "omdb_df.metacritic = pd.to_numeric(omdb_df.metacritic.replace(\"/100\",\"\", regex = True).str.strip())\n",
    "omdb_df.rotten_tomatoes = pd.to_numeric(omdb_df.rotten_tomatoes.replace(\"%\",\"\", regex = True).str.strip())\n",
    "omdb_df.runtime = pd.to_numeric(omdb_df.runtime.replace(\"min\",\"\", regex = True).str.strip())\n",
    "omdb_df.year = pd.to_numeric(omdb_df.year)\n",
    "# Remove 11 movies that have been wrongly identified on omdb (wrong year)\n",
    "year = [2013,2014,2015,2016,2017,2018]\n",
    "omdb_df = omdb_df[omdb_df.year.isin(year)]\n",
    "omdb_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>imdb</th>\n",
       "      <th>metacritic</th>\n",
       "      <th>movie</th>\n",
       "      <th>rated</th>\n",
       "      <th>rotten_tomatoes</th>\n",
       "      <th>runtime</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7.5</td>\n",
       "      <td>76</td>\n",
       "      <td>The Hunger Games: Catching Fire</td>\n",
       "      <td>PG-13</td>\n",
       "      <td>89</td>\n",
       "      <td>146</td>\n",
       "      <td>2013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7.2</td>\n",
       "      <td>62</td>\n",
       "      <td>Iron Man 3</td>\n",
       "      <td>PG-13</td>\n",
       "      <td>79</td>\n",
       "      <td>130</td>\n",
       "      <td>2013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7.5</td>\n",
       "      <td>74</td>\n",
       "      <td>Frozen</td>\n",
       "      <td>PG</td>\n",
       "      <td>90</td>\n",
       "      <td>102</td>\n",
       "      <td>2013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7.4</td>\n",
       "      <td>62</td>\n",
       "      <td>Despicable Me 2</td>\n",
       "      <td>PG</td>\n",
       "      <td>74</td>\n",
       "      <td>98</td>\n",
       "      <td>2013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7.1</td>\n",
       "      <td>55</td>\n",
       "      <td>Man of Steel</td>\n",
       "      <td>PG-13</td>\n",
       "      <td>56</td>\n",
       "      <td>143</td>\n",
       "      <td>2013</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   imdb  metacritic                            movie  rated  rotten_tomatoes  \\\n",
       "0   7.5          76  The Hunger Games: Catching Fire  PG-13               89   \n",
       "1   7.2          62                       Iron Man 3  PG-13               79   \n",
       "2   7.5          74                           Frozen     PG               90   \n",
       "3   7.4          62                  Despicable Me 2     PG               74   \n",
       "4   7.1          55                     Man of Steel  PG-13               56   \n",
       "\n",
       "   runtime  year  \n",
       "0      146  2013  \n",
       "1      130  2013  \n",
       "2      102  2013  \n",
       "3       98  2013  \n",
       "4      143  2013  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "omdb_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 500 entries, 0 to 499\n",
      "Data columns (total 4 columns):\n",
      "dir_agross     500 non-null float64\n",
      "dir_gross      500 non-null float64\n",
      "dir_nmovies    500 non-null int64\n",
      "director       500 non-null object\n",
      "dtypes: float64(2), int64(1), object(1)\n",
      "memory usage: 15.7+ KB\n"
     ]
    }
   ],
   "source": [
    "# Clean directors data\n",
    "# Convert str to numeric\n",
    "#director_df.dir_agross = pd.to_numeric(director_df.dir_agross.replace(\"\\$\",\"\", regex = True).str.strip())\n",
    "#director_df.dir_gross = pd.to_numeric(director_df.dir_gross.replace(\"\\$\",\"\", regex = True)\n",
    "#                                      .replace(\",\",\"\", regex = True).str.strip())\n",
    "director_df.dir_nmovies = pd.to_numeric(director_df.dir_nmovies)\n",
    "director_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>dir_agross</th>\n",
       "      <th>dir_gross</th>\n",
       "      <th>dir_nmovies</th>\n",
       "      <th>director</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>141.6</td>\n",
       "      <td>4672.2</td>\n",
       "      <td>33</td>\n",
       "      <td>Steven Spielberg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>179.0</td>\n",
       "      <td>2326.5</td>\n",
       "      <td>13</td>\n",
       "      <td>Michael Bay</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>165.6</td>\n",
       "      <td>2152.7</td>\n",
       "      <td>13</td>\n",
       "      <td>Peter Jackson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>84.4</td>\n",
       "      <td>2109.8</td>\n",
       "      <td>25</td>\n",
       "      <td>Ron Howard</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>110.8</td>\n",
       "      <td>2104.3</td>\n",
       "      <td>19</td>\n",
       "      <td>Robert Zemeckis</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   dir_agross  dir_gross  dir_nmovies          director\n",
       "0       141.6     4672.2           33  Steven Spielberg\n",
       "1       179.0     2326.5           13       Michael Bay\n",
       "2       165.6     2152.7           13     Peter Jackson\n",
       "3        84.4     2109.8           25        Ron Howard\n",
       "4       110.8     2104.3           19   Robert Zemeckis"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "director_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 500 entries, 0 to 499\n",
      "Data columns (total 4 columns):\n",
      "act_agross     500 non-null float64\n",
      "act_gross      500 non-null float64\n",
      "act_nmovies    500 non-null int64\n",
      "actor          500 non-null object\n",
      "dtypes: float64(2), int64(1), object(1)\n",
      "memory usage: 15.7+ KB\n"
     ]
    }
   ],
   "source": [
    "# Clean directors data\n",
    "# Convert str to numeric\n",
    "actor_df.act_agross = pd.to_numeric(actor_df.act_agross.replace(\"\\$\", \"\", regex = True)\n",
    "                                    .replace(\",\", \"\", regex = True).str.strip())\n",
    "actor_df.act_gross = pd.to_numeric(actor_df.act_gross.replace(\"\\$\", \"\", regex = True)\n",
    "                                      .replace(\",\", \"\", regex = True).str.strip())\n",
    "actor_df.act_nmovies = pd.to_numeric(actor_df.act_nmovies)\n",
    "actor_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>act_agross</th>\n",
       "      <th>act_gross</th>\n",
       "      <th>act_nmovies</th>\n",
       "      <th>actor</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>86.9</td>\n",
       "      <td>6693.5</td>\n",
       "      <td>77</td>\n",
       "      <td>Samuel L. Jackson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100.4</td>\n",
       "      <td>5620.8</td>\n",
       "      <td>56</td>\n",
       "      <td>Robert Downey Jr.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>117.4</td>\n",
       "      <td>5046.2</td>\n",
       "      <td>43</td>\n",
       "      <td>Scarlett Johansson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>118.2</td>\n",
       "      <td>4963.8</td>\n",
       "      <td>42</td>\n",
       "      <td>Harrison Ford</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>96.0</td>\n",
       "      <td>4606.8</td>\n",
       "      <td>48</td>\n",
       "      <td>Tom Hanks</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   act_agross  act_gross  act_nmovies               actor\n",
       "0        86.9     6693.5           77   Samuel L. Jackson\n",
       "1       100.4     5620.8           56   Robert Downey Jr.\n",
       "2       117.4     5046.2           43  Scarlett Johansson\n",
       "3       118.2     4963.8           42       Harrison Ford\n",
       "4        96.0     4606.8           48           Tom Hanks"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "actor_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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_name</th>\n",
       "      <th>movie</th>\n",
       "      <th>opening</th>\n",
       "      <th>date_time</th>\n",
       "      <th>theaters</th>\n",
       "      <th>tot_gross</th>\n",
       "      <th>actors</th>\n",
       "      <th>directors</th>\n",
       "      <th>gtrend</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>The Hunger Games: Catching Fire</td>\n",
       "      <td>The Hunger Games: Catching Fire</td>\n",
       "      <td>158074286</td>\n",
       "      <td>2013-11-22</td>\n",
       "      <td>4163</td>\n",
       "      <td>424668047</td>\n",
       "      <td>[Jennifer Lawrence, Josh Hutcherson, Liam Hems...</td>\n",
       "      <td>[Francis Lawrence]</td>\n",
       "      <td>0.456</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Iron Man 3</td>\n",
       "      <td>Iron Man 3</td>\n",
       "      <td>174144585</td>\n",
       "      <td>2013-05-03</td>\n",
       "      <td>4253</td>\n",
       "      <td>409013994</td>\n",
       "      <td>[Robert Downey, Jr., Gwyneth Paltrow, Don Chea...</td>\n",
       "      <td>[Shane Black]</td>\n",
       "      <td>0.792</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Frozen</td>\n",
       "      <td>Frozen</td>\n",
       "      <td>243390</td>\n",
       "      <td>2013-11-22</td>\n",
       "      <td>1</td>\n",
       "      <td>400738009</td>\n",
       "      <td>[Kristen Bell, Josh Gad, Alan Tudyk]</td>\n",
       "      <td>[Chris Buck, Jennifer Lee]</td>\n",
       "      <td>0.124</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Despicable Me 2</td>\n",
       "      <td>Despicable Me 2</td>\n",
       "      <td>83517315</td>\n",
       "      <td>2013-07-03</td>\n",
       "      <td>3997</td>\n",
       "      <td>368061265</td>\n",
       "      <td>[Steve Carell, Kristen Wiig, Benjamin Bratt, R...</td>\n",
       "      <td>[Pierre Coffin, Chris Renaud]</td>\n",
       "      <td>0.706</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Man of Steel</td>\n",
       "      <td>Man of Steel</td>\n",
       "      <td>116619362</td>\n",
       "      <td>2013-06-14</td>\n",
       "      <td>4207</td>\n",
       "      <td>291045518</td>\n",
       "      <td>[Henry Cavill, Amy Adams, Michael Shannon, Kev...</td>\n",
       "      <td>[Zack Snyder]</td>\n",
       "      <td>0.713</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        movie_name                            movie  \\\n",
       "0  The Hunger Games: Catching Fire  The Hunger Games: Catching Fire   \n",
       "1                       Iron Man 3                       Iron Man 3   \n",
       "2                           Frozen                           Frozen   \n",
       "3                  Despicable Me 2                  Despicable Me 2   \n",
       "4                     Man of Steel                     Man of Steel   \n",
       "\n",
       "     opening  date_time  theaters  tot_gross  \\\n",
       "0  158074286 2013-11-22      4163  424668047   \n",
       "1  174144585 2013-05-03      4253  409013994   \n",
       "2     243390 2013-11-22         1  400738009   \n",
       "3   83517315 2013-07-03      3997  368061265   \n",
       "4  116619362 2013-06-14      4207  291045518   \n",
       "\n",
       "                                              actors  \\\n",
       "0  [Jennifer Lawrence, Josh Hutcherson, Liam Hems...   \n",
       "1  [Robert Downey, Jr., Gwyneth Paltrow, Don Chea...   \n",
       "2               [Kristen Bell, Josh Gad, Alan Tudyk]   \n",
       "3  [Steve Carell, Kristen Wiig, Benjamin Bratt, R...   \n",
       "4  [Henry Cavill, Amy Adams, Michael Shannon, Kev...   \n",
       "\n",
       "                       directors  gtrend  \n",
       "0             [Francis Lawrence]   0.456  \n",
       "1                  [Shane Black]   0.792  \n",
       "2     [Chris Buck, Jennifer Lee]   0.124  \n",
       "3  [Pierre Coffin, Chris Renaud]   0.706  \n",
       "4                  [Zack Snyder]   0.713  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mojog_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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_name</th>\n",
       "      <th>movie</th>\n",
       "      <th>opening</th>\n",
       "      <th>date_time</th>\n",
       "      <th>theaters</th>\n",
       "      <th>tot_gross</th>\n",
       "      <th>actors</th>\n",
       "      <th>directors</th>\n",
       "      <th>gtrend</th>\n",
       "      <th>unemp_rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>The Hunger Games: Catching Fire</td>\n",
       "      <td>The Hunger Games: Catching Fire</td>\n",
       "      <td>158074286</td>\n",
       "      <td>2013-11-22</td>\n",
       "      <td>4163</td>\n",
       "      <td>424668047</td>\n",
       "      <td>[Jennifer Lawrence, Josh Hutcherson, Liam Hems...</td>\n",
       "      <td>[Francis Lawrence]</td>\n",
       "      <td>0.456</td>\n",
       "      <td>6.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Frozen</td>\n",
       "      <td>Frozen</td>\n",
       "      <td>243390</td>\n",
       "      <td>2013-11-22</td>\n",
       "      <td>1</td>\n",
       "      <td>400738009</td>\n",
       "      <td>[Kristen Bell, Josh Gad, Alan Tudyk]</td>\n",
       "      <td>[Chris Buck, Jennifer Lee]</td>\n",
       "      <td>0.124</td>\n",
       "      <td>6.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Thor: The Dark World</td>\n",
       "      <td>Thor: The Dark World</td>\n",
       "      <td>85737841</td>\n",
       "      <td>2013-11-08</td>\n",
       "      <td>3841</td>\n",
       "      <td>206362140</td>\n",
       "      <td>[Chris Hemsworth, Natalie Portman, Tom Hiddles...</td>\n",
       "      <td>[Alan Taylor]</td>\n",
       "      <td>0.474</td>\n",
       "      <td>6.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>The Best Man Holiday</td>\n",
       "      <td>The Best Man Holiday</td>\n",
       "      <td>30107555</td>\n",
       "      <td>2013-11-15</td>\n",
       "      <td>2024</td>\n",
       "      <td>70525195</td>\n",
       "      <td>[Morris Chestnut, Regina Hall, Terrence Howard...</td>\n",
       "      <td>[Malcolm D. Lee]</td>\n",
       "      <td>0.220</td>\n",
       "      <td>6.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Last Vegas</td>\n",
       "      <td>Last Vegas</td>\n",
       "      <td>16334566</td>\n",
       "      <td>2013-11-01</td>\n",
       "      <td>3065</td>\n",
       "      <td>63914167</td>\n",
       "      <td>[Michael Douglas, Robert DeNiro, Morgan Freema...</td>\n",
       "      <td>[Jon Turteltaub]</td>\n",
       "      <td>0.187</td>\n",
       "      <td>6.9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        movie_name                            movie  \\\n",
       "0  The Hunger Games: Catching Fire  The Hunger Games: Catching Fire   \n",
       "1                           Frozen                           Frozen   \n",
       "2             Thor: The Dark World             Thor: The Dark World   \n",
       "3             The Best Man Holiday             The Best Man Holiday   \n",
       "4                       Last Vegas                       Last Vegas   \n",
       "\n",
       "     opening  date_time  theaters  tot_gross  \\\n",
       "0  158074286 2013-11-22      4163  424668047   \n",
       "1     243390 2013-11-22         1  400738009   \n",
       "2   85737841 2013-11-08      3841  206362140   \n",
       "3   30107555 2013-11-15      2024   70525195   \n",
       "4   16334566 2013-11-01      3065   63914167   \n",
       "\n",
       "                                              actors  \\\n",
       "0  [Jennifer Lawrence, Josh Hutcherson, Liam Hems...   \n",
       "1               [Kristen Bell, Josh Gad, Alan Tudyk]   \n",
       "2  [Chris Hemsworth, Natalie Portman, Tom Hiddles...   \n",
       "3  [Morris Chestnut, Regina Hall, Terrence Howard...   \n",
       "4  [Michael Douglas, Robert DeNiro, Morgan Freema...   \n",
       "\n",
       "                    directors  gtrend  unemp_rate  \n",
       "0          [Francis Lawrence]   0.456         6.9  \n",
       "1  [Chris Buck, Jennifer Lee]   0.124         6.9  \n",
       "2               [Alan Taylor]   0.474         6.9  \n",
       "3            [Malcolm D. Lee]   0.220         6.9  \n",
       "4            [Jon Turteltaub]   0.187         6.9  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create a new column unemp_date, by dropping the datetime day to 1\n",
    "# This is done in order to merge the unemployment monthly data to the movies release dates\n",
    "#datetime.date(2008,1,1).replace(day=5)\n",
    "mojog_df[\"unemp_date\"] = mojog_df.date_time.apply(lambda x: x.replace(day=1))\n",
    "mojog_df = pd.merge(mojog_df,unemp_df,on=\"unemp_date\",how=\"inner\")\n",
    "mojog_df = mojog_df.drop([\"unemp_date\"],1)\n",
    "mojog_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Merge the dataframes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 559 entries, 0 to 558\n",
      "Data columns (total 15 columns):\n",
      "movie_name         559 non-null object\n",
      "movie              559 non-null object\n",
      "opening            559 non-null int64\n",
      "date_time          559 non-null datetime64[ns]\n",
      "theaters           559 non-null int64\n",
      "tot_gross          559 non-null int64\n",
      "actors             538 non-null object\n",
      "directors          510 non-null object\n",
      "gtrend             559 non-null float64\n",
      "imdb               559 non-null float64\n",
      "metacritic         559 non-null int64\n",
      "rated              559 non-null object\n",
      "rotten_tomatoes    559 non-null int64\n",
      "runtime            559 non-null int64\n",
      "year               559 non-null int64\n",
      "dtypes: datetime64[ns](1), float64(2), int64(7), object(5)\n",
      "memory usage: 69.9+ KB\n"
     ]
    }
   ],
   "source": [
    "merged2 = pd.DataFrame.merge(mojog_df, omdb_df,on='movie', how = 'inner')\n",
    "merged2.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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_name</th>\n",
       "      <th>movie</th>\n",
       "      <th>opening</th>\n",
       "      <th>date_time</th>\n",
       "      <th>theaters</th>\n",
       "      <th>tot_gross</th>\n",
       "      <th>actors</th>\n",
       "      <th>directors</th>\n",
       "      <th>gtrend</th>\n",
       "      <th>imdb</th>\n",
       "      <th>metacritic</th>\n",
       "      <th>rated</th>\n",
       "      <th>rotten_tomatoes</th>\n",
       "      <th>runtime</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>The Hunger Games: Catching Fire</td>\n",
       "      <td>The Hunger Games: Catching Fire</td>\n",
       "      <td>158074286</td>\n",
       "      <td>2013-11-22</td>\n",
       "      <td>4163</td>\n",
       "      <td>424668047</td>\n",
       "      <td>[Jennifer Lawrence, Josh Hutcherson, Liam Hems...</td>\n",
       "      <td>[Francis Lawrence]</td>\n",
       "      <td>0.564</td>\n",
       "      <td>7.5</td>\n",
       "      <td>76</td>\n",
       "      <td>PG-13</td>\n",
       "      <td>89</td>\n",
       "      <td>146</td>\n",
       "      <td>2013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Iron Man 3</td>\n",
       "      <td>Iron Man 3</td>\n",
       "      <td>174144585</td>\n",
       "      <td>2013-05-03</td>\n",
       "      <td>4253</td>\n",
       "      <td>409013994</td>\n",
       "      <td>[Robert Downey, Jr., Gwyneth Paltrow, Don Chea...</td>\n",
       "      <td>[Shane Black]</td>\n",
       "      <td>0.707</td>\n",
       "      <td>7.2</td>\n",
       "      <td>62</td>\n",
       "      <td>PG-13</td>\n",
       "      <td>79</td>\n",
       "      <td>130</td>\n",
       "      <td>2013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Frozen</td>\n",
       "      <td>Frozen</td>\n",
       "      <td>243390</td>\n",
       "      <td>2013-11-22</td>\n",
       "      <td>1</td>\n",
       "      <td>400738009</td>\n",
       "      <td>[Kristen Bell, Josh Gad, Alan Tudyk]</td>\n",
       "      <td>[Chris Buck, Jennifer Lee]</td>\n",
       "      <td>0.123</td>\n",
       "      <td>7.5</td>\n",
       "      <td>74</td>\n",
       "      <td>PG</td>\n",
       "      <td>90</td>\n",
       "      <td>102</td>\n",
       "      <td>2013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Despicable Me 2</td>\n",
       "      <td>Despicable Me 2</td>\n",
       "      <td>83517315</td>\n",
       "      <td>2013-07-03</td>\n",
       "      <td>3997</td>\n",
       "      <td>368061265</td>\n",
       "      <td>[Steve Carell, Kristen Wiig, Benjamin Bratt, R...</td>\n",
       "      <td>[Pierre Coffin, Chris Renaud]</td>\n",
       "      <td>0.656</td>\n",
       "      <td>7.4</td>\n",
       "      <td>62</td>\n",
       "      <td>PG</td>\n",
       "      <td>74</td>\n",
       "      <td>98</td>\n",
       "      <td>2013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Man of Steel</td>\n",
       "      <td>Man of Steel</td>\n",
       "      <td>116619362</td>\n",
       "      <td>2013-06-14</td>\n",
       "      <td>4207</td>\n",
       "      <td>291045518</td>\n",
       "      <td>[Henry Cavill, Amy Adams, Michael Shannon, Kev...</td>\n",
       "      <td>[Zack Snyder]</td>\n",
       "      <td>0.607</td>\n",
       "      <td>7.1</td>\n",
       "      <td>55</td>\n",
       "      <td>PG-13</td>\n",
       "      <td>56</td>\n",
       "      <td>143</td>\n",
       "      <td>2013</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        movie_name                            movie  \\\n",
       "0  The Hunger Games: Catching Fire  The Hunger Games: Catching Fire   \n",
       "1                       Iron Man 3                       Iron Man 3   \n",
       "2                           Frozen                           Frozen   \n",
       "3                  Despicable Me 2                  Despicable Me 2   \n",
       "4                     Man of Steel                     Man of Steel   \n",
       "\n",
       "     opening  date_time  theaters  tot_gross  \\\n",
       "0  158074286 2013-11-22      4163  424668047   \n",
       "1  174144585 2013-05-03      4253  409013994   \n",
       "2     243390 2013-11-22         1  400738009   \n",
       "3   83517315 2013-07-03      3997  368061265   \n",
       "4  116619362 2013-06-14      4207  291045518   \n",
       "\n",
       "                                              actors  \\\n",
       "0  [Jennifer Lawrence, Josh Hutcherson, Liam Hems...   \n",
       "1  [Robert Downey, Jr., Gwyneth Paltrow, Don Chea...   \n",
       "2               [Kristen Bell, Josh Gad, Alan Tudyk]   \n",
       "3  [Steve Carell, Kristen Wiig, Benjamin Bratt, R...   \n",
       "4  [Henry Cavill, Amy Adams, Michael Shannon, Kev...   \n",
       "\n",
       "                       directors  gtrend  imdb  metacritic  rated  \\\n",
       "0             [Francis Lawrence]   0.564   7.5          76  PG-13   \n",
       "1                  [Shane Black]   0.707   7.2          62  PG-13   \n",
       "2     [Chris Buck, Jennifer Lee]   0.123   7.5          74     PG   \n",
       "3  [Pierre Coffin, Chris Renaud]   0.656   7.4          62     PG   \n",
       "4                  [Zack Snyder]   0.607   7.1          55  PG-13   \n",
       "\n",
       "   rotten_tomatoes  runtime  year  \n",
       "0               89      146  2013  \n",
       "1               79      130  2013  \n",
       "2               90      102  2013  \n",
       "3               74       98  2013  \n",
       "4               56      143  2013  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged2 = merged2.fillna(0)\n",
    "merged2.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Generate a metric for actor and directors"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### actors\n",
    "I took the max total gross or total # movies featured for the actor/director for each given movie."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "\n",
    "act_list = []\n",
    "for i in range(len(merged2)):\n",
    "    #for each of the actors in a given movie, sum up their total gross & movies\n",
    "    gross_list = []\n",
    "    nmovies_list = []\n",
    "    if (merged2.actors[i] == 0) or (merged2.actors[i] == []):\n",
    "        act_gross, act_nmovies = 0, 0\n",
    "    else:\n",
    "        actor_count = len(merged2.actors[i])\n",
    "        for actoriter in merged2.actors[i]:\n",
    "            if actor_df[actor_df.actor == actoriter].empty:\n",
    "                actor_count -= 1\n",
    "            #Subtract the movie total gross (movie i want to predict) from actor total gross\n",
    "            else:\n",
    "                gross_list.append(actor_df[actor_df.actor == actoriter].act_gross.iloc[0] - \\\n",
    "                merged2.tot_gross[i]/(1e6))\n",
    "                nmovies_list.append(actor_df[actor_df.actor == actoriter].act_nmovies.iloc[0])\n",
    "    #take the sum and average over number of actors featured\n",
    "    if (actor_count <= 0) or (merged2.actors[i] == []) or (merged2.actors[i] == 0):\n",
    "        act_gross, act_nmovies = 0, 0\n",
    "    else:\n",
    "        act_gross = round(max(gross_list),1)\n",
    "        act_nmovies = round(max(nmovies_list),1)\n",
    "    act_dict = {'movie': merged2.movie[i], 'act_gross':act_gross, \n",
    "                'act_nmovies': act_nmovies}\n",
    "    act_list.append(act_dict)\n",
    "actor_metric = pd.DataFrame(act_list)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### directors"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "\n",
    "dir_list = []\n",
    "for i in range(len(merged2)):\n",
    "    #for each of the actors in a given movie, sum up their total gross & movies\n",
    "    gross_list = []\n",
    "    nmovies_list = []\n",
    "    if (merged2.directors[i] == 0) or (merged2.directors[i] == []):\n",
    "        dir_gross, dir_nmovies = 0, 0\n",
    "    else:\n",
    "        director_count = len(merged2.directors[i])\n",
    "        for directoriter in merged2.directors[i]:\n",
    "            if director_df[director_df.director == directoriter].empty:\n",
    "                director_count -= 1\n",
    "            #Subtract the movie total gross (movie i want to predict) from actor total gross\n",
    "            else:\n",
    "                gross_list.append(director_df[director_df.director == directoriter].\n",
    "                                  dir_gross.iloc[0] - \\\n",
    "                merged2.tot_gross[i]/(1e6))\n",
    "                nmovies_list.append(director_df[director_df.director == directoriter]\n",
    "                                    .dir_nmovies.iloc[0])\n",
    "    #take the sum and average over number of actors featured\n",
    "    if (director_count <= 0) or (merged2.directors[i] == []) or (merged2.directors[i] == 0):\n",
    "        dir_gross, dir_nmovies = 0, 0\n",
    "    else:\n",
    "        dir_gross = round(max(gross_list),1)\n",
    "        dir_nmovies = round(max(nmovies_list),1)\n",
    "    dir_dict = {'movie': merged2.movie[i], 'dir_gross':dir_gross, \n",
    "                'dir_nmovies': dir_nmovies}\n",
    "    dir_list.append(dir_dict)\n",
    "director_metric = pd.DataFrame(dir_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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>act_gross</th>\n",
       "      <th>act_nmovies</th>\n",
       "      <th>movie</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3353.5</td>\n",
       "      <td>59</td>\n",
       "      <td>The Hunger Games: Catching Fire</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3815.7</td>\n",
       "      <td>53</td>\n",
       "      <td>Iron Man 3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2098.8</td>\n",
       "      <td>19</td>\n",
       "      <td>Frozen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2686.0</td>\n",
       "      <td>33</td>\n",
       "      <td>Despicable Me 2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2441.1</td>\n",
       "      <td>43</td>\n",
       "      <td>Man of Steel</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   act_gross  act_nmovies                            movie\n",
       "0     3353.5           59  The Hunger Games: Catching Fire\n",
       "1     3815.7           53                       Iron Man 3\n",
       "2     2098.8           19                           Frozen\n",
       "3     2686.0           33                  Despicable Me 2\n",
       "4     2441.1           43                     Man of Steel"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "actor_metric.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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>dir_gross</th>\n",
       "      <th>dir_nmovies</th>\n",
       "      <th>movie</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1056.8</td>\n",
       "      <td>7</td>\n",
       "      <td>The Hunger Games: Catching Fire</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>91.5</td>\n",
       "      <td>4</td>\n",
       "      <td>Iron Man 3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>230.0</td>\n",
       "      <td>3</td>\n",
       "      <td>Frozen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>852.1</td>\n",
       "      <td>4</td>\n",
       "      <td>Despicable Me 2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1028.6</td>\n",
       "      <td>8</td>\n",
       "      <td>Man of Steel</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   dir_gross  dir_nmovies                            movie\n",
       "0     1056.8            7  The Hunger Games: Catching Fire\n",
       "1       91.5            4                       Iron Man 3\n",
       "2      230.0            3                           Frozen\n",
       "3      852.1            4                  Despicable Me 2\n",
       "4     1028.6            8                     Man of Steel"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "director_metric.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Merge the director & actor metrics to df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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_name</th>\n",
       "      <th>movie</th>\n",
       "      <th>opening</th>\n",
       "      <th>date_time</th>\n",
       "      <th>theaters</th>\n",
       "      <th>tot_gross</th>\n",
       "      <th>actors</th>\n",
       "      <th>directors</th>\n",
       "      <th>gtrend</th>\n",
       "      <th>imdb</th>\n",
       "      <th>metacritic</th>\n",
       "      <th>rated</th>\n",
       "      <th>rotten_tomatoes</th>\n",
       "      <th>runtime</th>\n",
       "      <th>year</th>\n",
       "      <th>act_gross</th>\n",
       "      <th>act_nmovies</th>\n",
       "      <th>dir_gross</th>\n",
       "      <th>dir_nmovies</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>The Hunger Games: Catching Fire</td>\n",
       "      <td>The Hunger Games: Catching Fire</td>\n",
       "      <td>158074286</td>\n",
       "      <td>2013-11-22</td>\n",
       "      <td>4163</td>\n",
       "      <td>424668047</td>\n",
       "      <td>[Jennifer Lawrence, Josh Hutcherson, Liam Hems...</td>\n",
       "      <td>[Francis Lawrence]</td>\n",
       "      <td>0.564</td>\n",
       "      <td>7.5</td>\n",
       "      <td>76</td>\n",
       "      <td>PG-13</td>\n",
       "      <td>89</td>\n",
       "      <td>146</td>\n",
       "      <td>2013</td>\n",
       "      <td>3353.5</td>\n",
       "      <td>59</td>\n",
       "      <td>1056.8</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Iron Man 3</td>\n",
       "      <td>Iron Man 3</td>\n",
       "      <td>174144585</td>\n",
       "      <td>2013-05-03</td>\n",
       "      <td>4253</td>\n",
       "      <td>409013994</td>\n",
       "      <td>[Robert Downey, Jr., Gwyneth Paltrow, Don Chea...</td>\n",
       "      <td>[Shane Black]</td>\n",
       "      <td>0.707</td>\n",
       "      <td>7.2</td>\n",
       "      <td>62</td>\n",
       "      <td>PG-13</td>\n",
       "      <td>79</td>\n",
       "      <td>130</td>\n",
       "      <td>2013</td>\n",
       "      <td>3815.7</td>\n",
       "      <td>53</td>\n",
       "      <td>91.5</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Frozen</td>\n",
       "      <td>Frozen</td>\n",
       "      <td>243390</td>\n",
       "      <td>2013-11-22</td>\n",
       "      <td>1</td>\n",
       "      <td>400738009</td>\n",
       "      <td>[Kristen Bell, Josh Gad, Alan Tudyk]</td>\n",
       "      <td>[Chris Buck, Jennifer Lee]</td>\n",
       "      <td>0.123</td>\n",
       "      <td>7.5</td>\n",
       "      <td>74</td>\n",
       "      <td>PG</td>\n",
       "      <td>90</td>\n",
       "      <td>102</td>\n",
       "      <td>2013</td>\n",
       "      <td>2098.8</td>\n",
       "      <td>19</td>\n",
       "      <td>230.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Despicable Me 2</td>\n",
       "      <td>Despicable Me 2</td>\n",
       "      <td>83517315</td>\n",
       "      <td>2013-07-03</td>\n",
       "      <td>3997</td>\n",
       "      <td>368061265</td>\n",
       "      <td>[Steve Carell, Kristen Wiig, Benjamin Bratt, R...</td>\n",
       "      <td>[Pierre Coffin, Chris Renaud]</td>\n",
       "      <td>0.656</td>\n",
       "      <td>7.4</td>\n",
       "      <td>62</td>\n",
       "      <td>PG</td>\n",
       "      <td>74</td>\n",
       "      <td>98</td>\n",
       "      <td>2013</td>\n",
       "      <td>2686.0</td>\n",
       "      <td>33</td>\n",
       "      <td>852.1</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Man of Steel</td>\n",
       "      <td>Man of Steel</td>\n",
       "      <td>116619362</td>\n",
       "      <td>2013-06-14</td>\n",
       "      <td>4207</td>\n",
       "      <td>291045518</td>\n",
       "      <td>[Henry Cavill, Amy Adams, Michael Shannon, Kev...</td>\n",
       "      <td>[Zack Snyder]</td>\n",
       "      <td>0.607</td>\n",
       "      <td>7.1</td>\n",
       "      <td>55</td>\n",
       "      <td>PG-13</td>\n",
       "      <td>56</td>\n",
       "      <td>143</td>\n",
       "      <td>2013</td>\n",
       "      <td>2441.1</td>\n",
       "      <td>43</td>\n",
       "      <td>1028.6</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        movie_name                            movie  \\\n",
       "0  The Hunger Games: Catching Fire  The Hunger Games: Catching Fire   \n",
       "1                       Iron Man 3                       Iron Man 3   \n",
       "2                           Frozen                           Frozen   \n",
       "3                  Despicable Me 2                  Despicable Me 2   \n",
       "4                     Man of Steel                     Man of Steel   \n",
       "\n",
       "     opening  date_time  theaters  tot_gross  \\\n",
       "0  158074286 2013-11-22      4163  424668047   \n",
       "1  174144585 2013-05-03      4253  409013994   \n",
       "2     243390 2013-11-22         1  400738009   \n",
       "3   83517315 2013-07-03      3997  368061265   \n",
       "4  116619362 2013-06-14      4207  291045518   \n",
       "\n",
       "                                              actors  \\\n",
       "0  [Jennifer Lawrence, Josh Hutcherson, Liam Hems...   \n",
       "1  [Robert Downey, Jr., Gwyneth Paltrow, Don Chea...   \n",
       "2               [Kristen Bell, Josh Gad, Alan Tudyk]   \n",
       "3  [Steve Carell, Kristen Wiig, Benjamin Bratt, R...   \n",
       "4  [Henry Cavill, Amy Adams, Michael Shannon, Kev...   \n",
       "\n",
       "                       directors  gtrend  imdb  metacritic  rated  \\\n",
       "0             [Francis Lawrence]   0.564   7.5          76  PG-13   \n",
       "1                  [Shane Black]   0.707   7.2          62  PG-13   \n",
       "2     [Chris Buck, Jennifer Lee]   0.123   7.5          74     PG   \n",
       "3  [Pierre Coffin, Chris Renaud]   0.656   7.4          62     PG   \n",
       "4                  [Zack Snyder]   0.607   7.1          55  PG-13   \n",
       "\n",
       "   rotten_tomatoes  runtime  year  act_gross  act_nmovies  dir_gross  \\\n",
       "0               89      146  2013     3353.5           59     1056.8   \n",
       "1               79      130  2013     3815.7           53       91.5   \n",
       "2               90      102  2013     2098.8           19      230.0   \n",
       "3               74       98  2013     2686.0           33      852.1   \n",
       "4               56      143  2013     2441.1           43     1028.6   \n",
       "\n",
       "   dir_nmovies  \n",
       "0            7  \n",
       "1            4  \n",
       "2            3  \n",
       "3            4  \n",
       "4            8  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged2 = pd.merge(merged2, actor_metric, on=\"movie\", how=\"inner\")\n",
    "merged2 = pd.merge(merged2, director_metric, on=\"movie\", how=\"inner\")\n",
    "#merged2 = merged2.drop([\"actors\",\"directors\"],1)\n",
    "merged2.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Save the merged dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "merged2.to_pickle(\"merged_df.pkl\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "149"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(merged2[(merged2.dir_nmovies == 0)])# | (merged2.act_gross == 0)])"
   ]
  }
 ],
 "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.5.5"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}