analysis_of_the_film_industry_cleaning.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# load the data as a pandas dataframe\n",
    "imdb_basic = pd.read_csv('zippedData/imdb.title.basics.csv.gz')\n",
    "imdb_crews = pd.read_csv('zippedData/imdb.title.crew.csv.gz')\n",
    "TN_budgets = pd.read_csv('zippedData/tn.movie_budgets.csv.gz')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>tconst</th>\n",
       "      <th>primary_title</th>\n",
       "      <th>original_title</th>\n",
       "      <th>start_year</th>\n",
       "      <th>runtime_minutes</th>\n",
       "      <th>genres</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>tt0063540</td>\n",
       "      <td>Sunghursh</td>\n",
       "      <td>Sunghursh</td>\n",
       "      <td>2013</td>\n",
       "      <td>175.0</td>\n",
       "      <td>Action,Crime,Drama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>tt0066787</td>\n",
       "      <td>One Day Before the Rainy Season</td>\n",
       "      <td>Ashad Ka Ek Din</td>\n",
       "      <td>2019</td>\n",
       "      <td>114.0</td>\n",
       "      <td>Biography,Drama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>tt0069049</td>\n",
       "      <td>The Other Side of the Wind</td>\n",
       "      <td>The Other Side of the Wind</td>\n",
       "      <td>2018</td>\n",
       "      <td>122.0</td>\n",
       "      <td>Drama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>tt0069204</td>\n",
       "      <td>Sabse Bada Sukh</td>\n",
       "      <td>Sabse Bada Sukh</td>\n",
       "      <td>2018</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Comedy,Drama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>tt0100275</td>\n",
       "      <td>The Wandering Soap Opera</td>\n",
       "      <td>La Telenovela Errante</td>\n",
       "      <td>2017</td>\n",
       "      <td>80.0</td>\n",
       "      <td>Comedy,Drama,Fantasy</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      tconst                    primary_title              original_title  \\\n",
       "0  tt0063540                        Sunghursh                   Sunghursh   \n",
       "1  tt0066787  One Day Before the Rainy Season             Ashad Ka Ek Din   \n",
       "2  tt0069049       The Other Side of the Wind  The Other Side of the Wind   \n",
       "3  tt0069204                  Sabse Bada Sukh             Sabse Bada Sukh   \n",
       "4  tt0100275         The Wandering Soap Opera       La Telenovela Errante   \n",
       "\n",
       "   start_year  runtime_minutes                genres  \n",
       "0        2013            175.0    Action,Crime,Drama  \n",
       "1        2019            114.0       Biography,Drama  \n",
       "2        2018            122.0                 Drama  \n",
       "3        2018              NaN          Comedy,Drama  \n",
       "4        2017             80.0  Comedy,Drama,Fantasy  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#peeking into the dataset\n",
    "imdb_basic.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "tconst                 0\n",
       "primary_title          0\n",
       "original_title        21\n",
       "start_year             0\n",
       "runtime_minutes    31739\n",
       "genres              5408\n",
       "dtype: int64"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# checking for any missing values\n",
    "imdb_basic.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(146144, 6)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# checking the shape of the data\n",
    "imdb_basic.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 146144 entries, 0 to 146143\n",
      "Data columns (total 6 columns):\n",
      " #   Column           Non-Null Count   Dtype  \n",
      "---  ------           --------------   -----  \n",
      " 0   tconst           146144 non-null  object \n",
      " 1   primary_title    146144 non-null  object \n",
      " 2   original_title   146123 non-null  object \n",
      " 3   start_year       146144 non-null  int64  \n",
      " 4   runtime_minutes  114405 non-null  float64\n",
      " 5   genres           140736 non-null  object \n",
      "dtypes: float64(1), int64(1), object(4)\n",
      "memory usage: 6.7+ MB\n"
     ]
    }
   ],
   "source": [
    "# more investigation into the data\n",
    "imdb_basic.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# dropping the rows with missing values in 'genres' column\n",
    "imdb_basic = imdb_basic.dropna(axis=0, subset = ['genres'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# dropping the rows with missing values in the 'original_title' column\n",
    "imdb_basic = imdb_basic.dropna(axis=0, subset = ['original_title'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 140734 entries, 0 to 146143\n",
      "Data columns (total 6 columns):\n",
      " #   Column           Non-Null Count   Dtype  \n",
      "---  ------           --------------   -----  \n",
      " 0   tconst           140734 non-null  object \n",
      " 1   primary_title    140734 non-null  object \n",
      " 2   original_title   140734 non-null  object \n",
      " 3   start_year       140734 non-null  int64  \n",
      " 4   runtime_minutes  112232 non-null  float64\n",
      " 5   genres           140734 non-null  object \n",
      "dtypes: float64(1), int64(1), object(4)\n",
      "memory usage: 7.5+ MB\n"
     ]
    }
   ],
   "source": [
    "# checking to see if those rows with missing values were successfully dropped\n",
    "imdb_basic.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "imdb_basic_new = imdb_basic.set_index(imdb_basic.columns.drop('genres',1).tolist()).genres.str.split(',', expand=True).stack().reset_index().rename(columns={0:'genres'}).loc[:, imdb_basic.columns]"
   ]
  },
  {
   "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>tconst</th>\n",
       "      <th>primary_title</th>\n",
       "      <th>original_title</th>\n",
       "      <th>start_year</th>\n",
       "      <th>runtime_minutes</th>\n",
       "      <th>genres</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>tt0063540</td>\n",
       "      <td>Sunghursh</td>\n",
       "      <td>Sunghursh</td>\n",
       "      <td>2013</td>\n",
       "      <td>175.0</td>\n",
       "      <td>Action</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>tt0063540</td>\n",
       "      <td>Sunghursh</td>\n",
       "      <td>Sunghursh</td>\n",
       "      <td>2013</td>\n",
       "      <td>175.0</td>\n",
       "      <td>Crime</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>tt0063540</td>\n",
       "      <td>Sunghursh</td>\n",
       "      <td>Sunghursh</td>\n",
       "      <td>2013</td>\n",
       "      <td>175.0</td>\n",
       "      <td>Drama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>tt0066787</td>\n",
       "      <td>One Day Before the Rainy Season</td>\n",
       "      <td>Ashad Ka Ek Din</td>\n",
       "      <td>2019</td>\n",
       "      <td>114.0</td>\n",
       "      <td>Biography</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>tt0066787</td>\n",
       "      <td>One Day Before the Rainy Season</td>\n",
       "      <td>Ashad Ka Ek Din</td>\n",
       "      <td>2019</td>\n",
       "      <td>114.0</td>\n",
       "      <td>Drama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>tt0069049</td>\n",
       "      <td>The Other Side of the Wind</td>\n",
       "      <td>The Other Side of the Wind</td>\n",
       "      <td>2018</td>\n",
       "      <td>122.0</td>\n",
       "      <td>Drama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>tt0069204</td>\n",
       "      <td>Sabse Bada Sukh</td>\n",
       "      <td>Sabse Bada Sukh</td>\n",
       "      <td>2018</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Comedy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>tt0069204</td>\n",
       "      <td>Sabse Bada Sukh</td>\n",
       "      <td>Sabse Bada Sukh</td>\n",
       "      <td>2018</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Drama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>tt0100275</td>\n",
       "      <td>The Wandering Soap Opera</td>\n",
       "      <td>La Telenovela Errante</td>\n",
       "      <td>2017</td>\n",
       "      <td>80.0</td>\n",
       "      <td>Comedy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>tt0100275</td>\n",
       "      <td>The Wandering Soap Opera</td>\n",
       "      <td>La Telenovela Errante</td>\n",
       "      <td>2017</td>\n",
       "      <td>80.0</td>\n",
       "      <td>Drama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>tt0100275</td>\n",
       "      <td>The Wandering Soap Opera</td>\n",
       "      <td>La Telenovela Errante</td>\n",
       "      <td>2017</td>\n",
       "      <td>80.0</td>\n",
       "      <td>Fantasy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>tt0111414</td>\n",
       "      <td>A Thin Life</td>\n",
       "      <td>A Thin Life</td>\n",
       "      <td>2018</td>\n",
       "      <td>75.0</td>\n",
       "      <td>Comedy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>tt0112502</td>\n",
       "      <td>Bigfoot</td>\n",
       "      <td>Bigfoot</td>\n",
       "      <td>2017</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Horror</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>tt0112502</td>\n",
       "      <td>Bigfoot</td>\n",
       "      <td>Bigfoot</td>\n",
       "      <td>2017</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Thriller</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>tt0137204</td>\n",
       "      <td>Joe Finds Grace</td>\n",
       "      <td>Joe Finds Grace</td>\n",
       "      <td>2017</td>\n",
       "      <td>83.0</td>\n",
       "      <td>Adventure</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>tt0137204</td>\n",
       "      <td>Joe Finds Grace</td>\n",
       "      <td>Joe Finds Grace</td>\n",
       "      <td>2017</td>\n",
       "      <td>83.0</td>\n",
       "      <td>Animation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>tt0137204</td>\n",
       "      <td>Joe Finds Grace</td>\n",
       "      <td>Joe Finds Grace</td>\n",
       "      <td>2017</td>\n",
       "      <td>83.0</td>\n",
       "      <td>Comedy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>tt0139613</td>\n",
       "      <td>O Silêncio</td>\n",
       "      <td>O Silêncio</td>\n",
       "      <td>2012</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Documentary</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>tt0139613</td>\n",
       "      <td>O Silêncio</td>\n",
       "      <td>O Silêncio</td>\n",
       "      <td>2012</td>\n",
       "      <td>NaN</td>\n",
       "      <td>History</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>tt0144449</td>\n",
       "      <td>Nema aviona za Zagreb</td>\n",
       "      <td>Nema aviona za Zagreb</td>\n",
       "      <td>2012</td>\n",
       "      <td>82.0</td>\n",
       "      <td>Biography</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       tconst                    primary_title              original_title  \\\n",
       "0   tt0063540                        Sunghursh                   Sunghursh   \n",
       "1   tt0063540                        Sunghursh                   Sunghursh   \n",
       "2   tt0063540                        Sunghursh                   Sunghursh   \n",
       "3   tt0066787  One Day Before the Rainy Season             Ashad Ka Ek Din   \n",
       "4   tt0066787  One Day Before the Rainy Season             Ashad Ka Ek Din   \n",
       "5   tt0069049       The Other Side of the Wind  The Other Side of the Wind   \n",
       "6   tt0069204                  Sabse Bada Sukh             Sabse Bada Sukh   \n",
       "7   tt0069204                  Sabse Bada Sukh             Sabse Bada Sukh   \n",
       "8   tt0100275         The Wandering Soap Opera       La Telenovela Errante   \n",
       "9   tt0100275         The Wandering Soap Opera       La Telenovela Errante   \n",
       "10  tt0100275         The Wandering Soap Opera       La Telenovela Errante   \n",
       "11  tt0111414                      A Thin Life                 A Thin Life   \n",
       "12  tt0112502                          Bigfoot                     Bigfoot   \n",
       "13  tt0112502                          Bigfoot                     Bigfoot   \n",
       "14  tt0137204                  Joe Finds Grace             Joe Finds Grace   \n",
       "15  tt0137204                  Joe Finds Grace             Joe Finds Grace   \n",
       "16  tt0137204                  Joe Finds Grace             Joe Finds Grace   \n",
       "17  tt0139613                       O Silêncio                  O Silêncio   \n",
       "18  tt0139613                       O Silêncio                  O Silêncio   \n",
       "19  tt0144449            Nema aviona za Zagreb       Nema aviona za Zagreb   \n",
       "\n",
       "    start_year  runtime_minutes       genres  \n",
       "0         2013            175.0       Action  \n",
       "1         2013            175.0        Crime  \n",
       "2         2013            175.0        Drama  \n",
       "3         2019            114.0    Biography  \n",
       "4         2019            114.0        Drama  \n",
       "5         2018            122.0        Drama  \n",
       "6         2018              NaN       Comedy  \n",
       "7         2018              NaN        Drama  \n",
       "8         2017             80.0       Comedy  \n",
       "9         2017             80.0        Drama  \n",
       "10        2017             80.0      Fantasy  \n",
       "11        2018             75.0       Comedy  \n",
       "12        2017              NaN       Horror  \n",
       "13        2017              NaN     Thriller  \n",
       "14        2017             83.0    Adventure  \n",
       "15        2017             83.0    Animation  \n",
       "16        2017             83.0       Comedy  \n",
       "17        2012              NaN  Documentary  \n",
       "18        2012              NaN      History  \n",
       "19        2012             82.0    Biography  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "imdb_basic_new.head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 146144 entries, 0 to 146143\n",
      "Data columns (total 3 columns):\n",
      " #   Column     Non-Null Count   Dtype \n",
      "---  ------     --------------   ----- \n",
      " 0   tconst     146144 non-null  object\n",
      " 1   directors  140417 non-null  object\n",
      " 2   writers    110261 non-null  object\n",
      "dtypes: object(3)\n",
      "memory usage: 3.3+ MB\n"
     ]
    }
   ],
   "source": [
    "# investigating the crews dataset\n",
    "imdb_crews.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# dropping rows with missing values in the 'directors' column\n",
    "imdb_crews = imdb_crews.dropna(axis=0, subset = ['directors'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(140417, 3)"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# confirming the new shape of the data\n",
    "imdb_crews.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 5782 entries, 0 to 5781\n",
      "Data columns (total 6 columns):\n",
      " #   Column             Non-Null Count  Dtype \n",
      "---  ------             --------------  ----- \n",
      " 0   id                 5782 non-null   int64 \n",
      " 1   release_date       5782 non-null   object\n",
      " 2   movie              5782 non-null   object\n",
      " 3   production_budget  5782 non-null   object\n",
      " 4   domestic_gross     5782 non-null   object\n",
      " 5   worldwide_gross    5782 non-null   object\n",
      "dtypes: int64(1), object(5)\n",
      "memory usage: 271.2+ KB\n"
     ]
    }
   ],
   "source": [
    "# investigating the TN budgets dataset\n",
    "TN_budgets.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0       425,000,000\n",
      "1       410,600,000\n",
      "2       350,000,000\n",
      "3       330,600,000\n",
      "4       317,000,000\n",
      "           ...     \n",
      "5777          7,000\n",
      "5778          6,000\n",
      "5779          5,000\n",
      "5780          1,400\n",
      "5781          1,100\n",
      "Name: production_budget, Length: 5782, dtype: object\n"
     ]
    }
   ],
   "source": [
    "#testing the code\n",
    "print(TN_budgets['production_budget'].str.strip('$'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "# stripping the dollar signs from the budget and gross columns\n",
    "TN_budgets['production_budget'] = TN_budgets['production_budget'].str.strip('$')\n",
    "TN_budgets['domestic_gross'] = TN_budgets['domestic_gross'].str.strip('$')\n",
    "TN_budgets['worldwide_gross'] = TN_budgets['worldwide_gross'].str.strip('$')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0       425000000\n",
      "1       410600000\n",
      "2       350000000\n",
      "3       330600000\n",
      "4       317000000\n",
      "          ...    \n",
      "5777         7000\n",
      "5778         6000\n",
      "5779         5000\n",
      "5780         1400\n",
      "5781         1100\n",
      "Name: production_budget, Length: 5782, dtype: object\n"
     ]
    }
   ],
   "source": [
    "# testing testing\n",
    "print(TN_budgets['production_budget'].str.replace(\",\", \"\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "# removing the commas from the budget and gross columns\n",
    "TN_budgets['production_budget'] = TN_budgets['production_budget'].str.replace(\",\", \"\")\n",
    "TN_budgets['domestic_gross'] = TN_budgets['domestic_gross'].str.replace(\",\", \"\")\n",
    "TN_budgets['worldwide_gross'] = TN_budgets['worldwide_gross'].str.replace(\",\", \"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "# changing the dtype of the budget and gross columns to float\n",
    "TN_budgets['production_budget'] = TN_budgets['production_budget'].astype(float)\n",
    "TN_budgets['domestic_gross'] = TN_budgets['domestic_gross'].astype(float)\n",
    "TN_budgets['worldwide_gross'] = TN_budgets['worldwide_gross'].astype(float)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[2009, 2011, 2019, 2015, 2017, 2015, 2018, 2007, 2017, 2015]\n"
     ]
    },
    {
     "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>TN_year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2011</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   TN_year\n",
       "0     2009\n",
       "1     2011\n",
       "2     2019\n",
       "3     2015\n",
       "4     2017"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# creating a list containing the release years and turning it into a dataframe\n",
    "TN_year = []\n",
    "# def budget_year():\n",
    "for date in list(TN_budgets.loc[:, 'release_date']):\n",
    "    TN_year.append(int(date[-4:]))\n",
    "print(TN_year[:10])\n",
    "TN_year = pd.DataFrame(TN_year)\n",
    "TN_year.columns = ['TN_year']\n",
    "TN_year.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Dec', 'May', 'Jun', 'May', 'Dec', 'Dec', 'Apr', 'May', 'Nov', 'Nov']\n"
     ]
    },
    {
     "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>TN_month</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Dec</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>May</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Jun</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>May</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Dec</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  TN_month\n",
       "0      Dec\n",
       "1      May\n",
       "2      Jun\n",
       "3      May\n",
       "4      Dec"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# creating a list containing the release months and turning it into a dataframe\n",
    "TN_month = []\n",
    "for dates in list(TN_budgets.loc[:, 'release_date']):\n",
    "    TN_month.append(dates[:3])\n",
    "print(TN_month[:10])\n",
    "TN_month = pd.DataFrame(TN_month)\n",
    "TN_month.columns = ['TN_month']\n",
    "TN_month.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>release_date</th>\n",
       "      <th>movie</th>\n",
       "      <th>production_budget</th>\n",
       "      <th>domestic_gross</th>\n",
       "      <th>worldwide_gross</th>\n",
       "      <th>TN_month</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Dec 18, 2009</td>\n",
       "      <td>Avatar</td>\n",
       "      <td>425000000.0</td>\n",
       "      <td>760507625.0</td>\n",
       "      <td>2.776345e+09</td>\n",
       "      <td>Dec</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>May 20, 2011</td>\n",
       "      <td>Pirates of the Caribbean: On Stranger Tides</td>\n",
       "      <td>410600000.0</td>\n",
       "      <td>241063875.0</td>\n",
       "      <td>1.045664e+09</td>\n",
       "      <td>May</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Jun 7, 2019</td>\n",
       "      <td>Dark Phoenix</td>\n",
       "      <td>350000000.0</td>\n",
       "      <td>42762350.0</td>\n",
       "      <td>1.497624e+08</td>\n",
       "      <td>Jun</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>May 1, 2015</td>\n",
       "      <td>Avengers: Age of Ultron</td>\n",
       "      <td>330600000.0</td>\n",
       "      <td>459005868.0</td>\n",
       "      <td>1.403014e+09</td>\n",
       "      <td>May</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Dec 15, 2017</td>\n",
       "      <td>Star Wars Ep. VIII: The Last Jedi</td>\n",
       "      <td>317000000.0</td>\n",
       "      <td>620181382.0</td>\n",
       "      <td>1.316722e+09</td>\n",
       "      <td>Dec</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id  release_date                                        movie  \\\n",
       "0   1  Dec 18, 2009                                       Avatar   \n",
       "1   2  May 20, 2011  Pirates of the Caribbean: On Stranger Tides   \n",
       "2   3   Jun 7, 2019                                 Dark Phoenix   \n",
       "3   4   May 1, 2015                      Avengers: Age of Ultron   \n",
       "4   5  Dec 15, 2017            Star Wars Ep. VIII: The Last Jedi   \n",
       "\n",
       "   production_budget  domestic_gross  worldwide_gross TN_month  \n",
       "0        425000000.0     760507625.0     2.776345e+09      Dec  \n",
       "1        410600000.0     241063875.0     1.045664e+09      May  \n",
       "2        350000000.0      42762350.0     1.497624e+08      Jun  \n",
       "3        330600000.0     459005868.0     1.403014e+09      May  \n",
       "4        317000000.0     620181382.0     1.316722e+09      Dec  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Joining the TN_month dataframe to the TN_budgets df\n",
    "TN_budgets = TN_budgets.join(TN_month)\n",
    "TN_budgets.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>release_date</th>\n",
       "      <th>movie</th>\n",
       "      <th>production_budget</th>\n",
       "      <th>domestic_gross</th>\n",
       "      <th>worldwide_gross</th>\n",
       "      <th>TN_month</th>\n",
       "      <th>TN_year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Dec 18, 2009</td>\n",
       "      <td>Avatar</td>\n",
       "      <td>425000000.0</td>\n",
       "      <td>760507625.0</td>\n",
       "      <td>2.776345e+09</td>\n",
       "      <td>Dec</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>May 20, 2011</td>\n",
       "      <td>Pirates of the Caribbean: On Stranger Tides</td>\n",
       "      <td>410600000.0</td>\n",
       "      <td>241063875.0</td>\n",
       "      <td>1.045664e+09</td>\n",
       "      <td>May</td>\n",
       "      <td>2011</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Jun 7, 2019</td>\n",
       "      <td>Dark Phoenix</td>\n",
       "      <td>350000000.0</td>\n",
       "      <td>42762350.0</td>\n",
       "      <td>1.497624e+08</td>\n",
       "      <td>Jun</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>May 1, 2015</td>\n",
       "      <td>Avengers: Age of Ultron</td>\n",
       "      <td>330600000.0</td>\n",
       "      <td>459005868.0</td>\n",
       "      <td>1.403014e+09</td>\n",
       "      <td>May</td>\n",
       "      <td>2015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Dec 15, 2017</td>\n",
       "      <td>Star Wars Ep. VIII: The Last Jedi</td>\n",
       "      <td>317000000.0</td>\n",
       "      <td>620181382.0</td>\n",
       "      <td>1.316722e+09</td>\n",
       "      <td>Dec</td>\n",
       "      <td>2017</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id  release_date                                        movie  \\\n",
       "0   1  Dec 18, 2009                                       Avatar   \n",
       "1   2  May 20, 2011  Pirates of the Caribbean: On Stranger Tides   \n",
       "2   3   Jun 7, 2019                                 Dark Phoenix   \n",
       "3   4   May 1, 2015                      Avengers: Age of Ultron   \n",
       "4   5  Dec 15, 2017            Star Wars Ep. VIII: The Last Jedi   \n",
       "\n",
       "   production_budget  domestic_gross  worldwide_gross TN_month  TN_year  \n",
       "0        425000000.0     760507625.0     2.776345e+09      Dec     2009  \n",
       "1        410600000.0     241063875.0     1.045664e+09      May     2011  \n",
       "2        350000000.0      42762350.0     1.497624e+08      Jun     2019  \n",
       "3        330600000.0     459005868.0     1.403014e+09      May     2015  \n",
       "4        317000000.0     620181382.0     1.316722e+09      Dec     2017  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Joining the TN_year dataframe to the TN_budgets df\n",
    "TN_budgets = TN_budgets.join(TN_year)\n",
    "TN_budgets.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "# creating a connection to aggregate db \n",
    "conn = sqlite3.connect('aggregate.db')\n",
    "cur = conn.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "#  converting the dfs into a SQL tables\n",
    "imdb_basic.to_sql('basic_table', con = conn)\n",
    "imdb_crews.to_sql('crews_table', con = conn)\n",
    "TN_budgets.to_sql('budgets_table', con = conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "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>tconst</th>\n",
       "      <th>original_title</th>\n",
       "      <th>genres</th>\n",
       "      <th>start_year</th>\n",
       "      <th>directors</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>tt0063540</td>\n",
       "      <td>Sunghursh</td>\n",
       "      <td>Action,Crime,Drama</td>\n",
       "      <td>2013</td>\n",
       "      <td>nm0712540</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>tt0066787</td>\n",
       "      <td>Ashad Ka Ek Din</td>\n",
       "      <td>Biography,Drama</td>\n",
       "      <td>2019</td>\n",
       "      <td>nm0002411</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>tt0069049</td>\n",
       "      <td>The Other Side of the Wind</td>\n",
       "      <td>Drama</td>\n",
       "      <td>2018</td>\n",
       "      <td>nm0000080</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>tt0069204</td>\n",
       "      <td>Sabse Bada Sukh</td>\n",
       "      <td>Comedy,Drama</td>\n",
       "      <td>2018</td>\n",
       "      <td>nm0611531</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>tt0100275</td>\n",
       "      <td>La Telenovela Errante</td>\n",
       "      <td>Comedy,Drama,Fantasy</td>\n",
       "      <td>2017</td>\n",
       "      <td>nm0765384,nm0749914</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      tconst              original_title                genres  start_year  \\\n",
       "0  tt0063540                   Sunghursh    Action,Crime,Drama        2013   \n",
       "1  tt0066787             Ashad Ka Ek Din       Biography,Drama        2019   \n",
       "2  tt0069049  The Other Side of the Wind                 Drama        2018   \n",
       "3  tt0069204             Sabse Bada Sukh          Comedy,Drama        2018   \n",
       "4  tt0100275       La Telenovela Errante  Comedy,Drama,Fantasy        2017   \n",
       "\n",
       "             directors  \n",
       "0            nm0712540  \n",
       "1            nm0002411  \n",
       "2            nm0000080  \n",
       "3            nm0611531  \n",
       "4  nm0765384,nm0749914  "
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# creating a new table and turning it into a dataframe\n",
    "cur.execute(\"\"\"SELECT b.tconst, b.original_title, b.genres, b.start_year, c.directors FROM basic_table b LEFT JOIN crews_table c ON b.tconst=c.tconst\"\"\")\n",
    "basic_crew_df = pd.DataFrame(cur.fetchall())\n",
    "basic_crew_df.columns = [x[0] for x in cur.description]\n",
    "basic_crew_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#  converting the df back into an SQL table\n",
    "basic_crew_df.to_sql('bc_table', con = conn)"
   ]
  },
  {
   "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>movie</th>\n",
       "      <th>original_title</th>\n",
       "      <th>genres</th>\n",
       "      <th>directors</th>\n",
       "      <th>production_budget</th>\n",
       "      <th>domestic_gross</th>\n",
       "      <th>worldwide_gross</th>\n",
       "      <th>TN_year</th>\n",
       "      <th>TN_month</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Pirates of the Caribbean: On Stranger Tides</td>\n",
       "      <td>Pirates of the Caribbean: On Stranger Tides</td>\n",
       "      <td>Action,Adventure,Fantasy</td>\n",
       "      <td>nm0551128</td>\n",
       "      <td>410600000.0</td>\n",
       "      <td>241063875.0</td>\n",
       "      <td>1.045664e+09</td>\n",
       "      <td>2011</td>\n",
       "      <td>May</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Dark Phoenix</td>\n",
       "      <td>Dark Phoenix</td>\n",
       "      <td>Action,Adventure,Sci-Fi</td>\n",
       "      <td>nm1334526</td>\n",
       "      <td>350000000.0</td>\n",
       "      <td>42762350.0</td>\n",
       "      <td>1.497624e+08</td>\n",
       "      <td>2019</td>\n",
       "      <td>Jun</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Avengers: Age of Ultron</td>\n",
       "      <td>Avengers: Age of Ultron</td>\n",
       "      <td>Action,Adventure,Sci-Fi</td>\n",
       "      <td>nm0923736</td>\n",
       "      <td>330600000.0</td>\n",
       "      <td>459005868.0</td>\n",
       "      <td>1.403014e+09</td>\n",
       "      <td>2015</td>\n",
       "      <td>May</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Avengers: Infinity War</td>\n",
       "      <td>Avengers: Infinity War</td>\n",
       "      <td>Action,Adventure,Sci-Fi</td>\n",
       "      <td>nm0751577,nm0751648</td>\n",
       "      <td>300000000.0</td>\n",
       "      <td>678815482.0</td>\n",
       "      <td>2.048134e+09</td>\n",
       "      <td>2018</td>\n",
       "      <td>Apr</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Justice League</td>\n",
       "      <td>Justice League</td>\n",
       "      <td>Action,Adventure,Fantasy</td>\n",
       "      <td>nm0811583</td>\n",
       "      <td>300000000.0</td>\n",
       "      <td>229024295.0</td>\n",
       "      <td>6.559452e+08</td>\n",
       "      <td>2017</td>\n",
       "      <td>Nov</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                         movie  \\\n",
       "0  Pirates of the Caribbean: On Stranger Tides   \n",
       "1                                 Dark Phoenix   \n",
       "2                      Avengers: Age of Ultron   \n",
       "3                       Avengers: Infinity War   \n",
       "4                               Justice League   \n",
       "\n",
       "                                original_title                    genres  \\\n",
       "0  Pirates of the Caribbean: On Stranger Tides  Action,Adventure,Fantasy   \n",
       "1                                 Dark Phoenix   Action,Adventure,Sci-Fi   \n",
       "2                      Avengers: Age of Ultron   Action,Adventure,Sci-Fi   \n",
       "3                       Avengers: Infinity War   Action,Adventure,Sci-Fi   \n",
       "4                               Justice League  Action,Adventure,Fantasy   \n",
       "\n",
       "             directors  production_budget  domestic_gross  worldwide_gross  \\\n",
       "0            nm0551128        410600000.0     241063875.0     1.045664e+09   \n",
       "1            nm1334526        350000000.0      42762350.0     1.497624e+08   \n",
       "2            nm0923736        330600000.0     459005868.0     1.403014e+09   \n",
       "3  nm0751577,nm0751648        300000000.0     678815482.0     2.048134e+09   \n",
       "4            nm0811583        300000000.0     229024295.0     6.559452e+08   \n",
       "\n",
       "   TN_year TN_month  \n",
       "0     2011      May  \n",
       "1     2019      Jun  \n",
       "2     2015      May  \n",
       "3     2018      Apr  \n",
       "4     2017      Nov  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Joining the bc_table to the production budget, domestic/worldwide gross, TN_month and TN_year from the budgets table to form a new table\n",
    "cur.execute(\"\"\"SELECT bd.movie, bc.original_title, bc.genres, bc.directors, bd.production_budget, bd.domestic_gross, bd.worldwide_gross, bd.TN_year, bd.TN_month \n",
    "               FROM budgets_table bd \n",
    "               JOIN bc_table bc \n",
    "               ON bd.movie = bc.original_title\"\"\")\n",
    "new_table = pd.DataFrame(cur.fetchall())\n",
    "new_table.columns = [x[0] for x in cur.description]\n",
    "new_table.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0       2011\n",
       "1       2019\n",
       "2       2015\n",
       "3       2018\n",
       "4       2017\n",
       "        ... \n",
       "3468    2006\n",
       "3469    2001\n",
       "3470    2012\n",
       "3471    2018\n",
       "3472    2015\n",
       "Name: TN_year, Length: 3473, dtype: int32"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# converting TN_year into type int\n",
    "new_table['TN_year'] = new_table['TN_year'].astype(int)\n",
    "new_table['TN_year']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "years range from 1915 to 2020\n"
     ]
    }
   ],
   "source": [
    "#checking the range of release years\n",
    "start = min(new_table['TN_year'])\n",
    "end = max(new_table['TN_year'])\n",
    "print('years range from {} to {}'.format(start, end))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "# filtering out the data to only include data from the year 2010 and beyond\n",
    "ten_yr_table = new_table.loc[new_table['TN_year'] >= 2010]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2482, 9)"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#just checking to see the shape of the data\n",
    "ten_yr_table.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "TN_year\n",
       "2010    4.630690e+07\n",
       "2011    3.383283e+07\n",
       "2012    4.185786e+07\n",
       "2013    4.050234e+07\n",
       "2014    4.265665e+07\n",
       "2015    3.777268e+07\n",
       "2016    4.365226e+07\n",
       "2017    5.741031e+07\n",
       "2018    6.793475e+07\n",
       "2019    4.677700e+07\n",
       "2020    0.000000e+00\n",
       "Name: domestic_gross, dtype: float64"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ten_yr_table.groupby('TN_year')['domestic_gross'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "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>original_title</th>\n",
       "      <th>genres</th>\n",
       "      <th>directors</th>\n",
       "      <th>production_budget</th>\n",
       "      <th>domestic_gross</th>\n",
       "      <th>worldwide_gross</th>\n",
       "      <th>TN_year</th>\n",
       "      <th>TN_month</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>348</th>\n",
       "      <td>Bright</td>\n",
       "      <td>Bright</td>\n",
       "      <td>Action,Crime,Fantasy</td>\n",
       "      <td>nm0043742</td>\n",
       "      <td>90000000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2017</td>\n",
       "      <td>Dec</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>374</th>\n",
       "      <td>Call of the Wild</td>\n",
       "      <td>Call of the Wild</td>\n",
       "      <td>Adventure,Animation,Family</td>\n",
       "      <td>nm0761498</td>\n",
       "      <td>82000000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2020</td>\n",
       "      <td>Feb</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>403</th>\n",
       "      <td>Renegades</td>\n",
       "      <td>Renegades</td>\n",
       "      <td>Action,Adventure,Crime</td>\n",
       "      <td>nm0702797</td>\n",
       "      <td>77500000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1521672.0</td>\n",
       "      <td>2019</td>\n",
       "      <td>Jan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>583</th>\n",
       "      <td>The Ridiculous 6</td>\n",
       "      <td>The Ridiculous 6</td>\n",
       "      <td>Comedy,Western</td>\n",
       "      <td>nm0178997</td>\n",
       "      <td>60000000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2015</td>\n",
       "      <td>Dec</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>584</th>\n",
       "      <td>Midway</td>\n",
       "      <td>Midway</td>\n",
       "      <td>Action,Drama,History</td>\n",
       "      <td>nm0000386</td>\n",
       "      <td>59500000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2019</td>\n",
       "      <td>Nov</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3465</th>\n",
       "      <td>The Front Man</td>\n",
       "      <td>The Front Man</td>\n",
       "      <td>Biography,Comedy,Documentary</td>\n",
       "      <td>nm0222816</td>\n",
       "      <td>20000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2015</td>\n",
       "      <td>Apr</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3466</th>\n",
       "      <td>The Ridges</td>\n",
       "      <td>The Ridges</td>\n",
       "      <td>Drama,Horror,Thriller</td>\n",
       "      <td>nm4192760</td>\n",
       "      <td>17300.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2011</td>\n",
       "      <td>Nov</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3467</th>\n",
       "      <td>Stories of Our Lives</td>\n",
       "      <td>Stories of Our Lives</td>\n",
       "      <td>Drama</td>\n",
       "      <td>nm5874888</td>\n",
       "      <td>15000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2014</td>\n",
       "      <td>Dec</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3471</th>\n",
       "      <td>Red 11</td>\n",
       "      <td>Red 11</td>\n",
       "      <td>Horror,Sci-Fi,Thriller</td>\n",
       "      <td>nm0001675</td>\n",
       "      <td>7000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2018</td>\n",
       "      <td>Dec</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3472</th>\n",
       "      <td>A Plague So Pleasant</td>\n",
       "      <td>A Plague So Pleasant</td>\n",
       "      <td>Drama,Horror,Thriller</td>\n",
       "      <td>nm4766951,nm5792320</td>\n",
       "      <td>1400.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2015</td>\n",
       "      <td>Sep</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>439 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     movie        original_title  \\\n",
       "348                 Bright                Bright   \n",
       "374       Call of the Wild      Call of the Wild   \n",
       "403              Renegades             Renegades   \n",
       "583       The Ridiculous 6      The Ridiculous 6   \n",
       "584                 Midway                Midway   \n",
       "...                    ...                   ...   \n",
       "3465         The Front Man         The Front Man   \n",
       "3466            The Ridges            The Ridges   \n",
       "3467  Stories of Our Lives  Stories of Our Lives   \n",
       "3471                Red 11                Red 11   \n",
       "3472  A Plague So Pleasant  A Plague So Pleasant   \n",
       "\n",
       "                            genres            directors  production_budget  \\\n",
       "348           Action,Crime,Fantasy            nm0043742         90000000.0   \n",
       "374     Adventure,Animation,Family            nm0761498         82000000.0   \n",
       "403         Action,Adventure,Crime            nm0702797         77500000.0   \n",
       "583                 Comedy,Western            nm0178997         60000000.0   \n",
       "584           Action,Drama,History            nm0000386         59500000.0   \n",
       "...                            ...                  ...                ...   \n",
       "3465  Biography,Comedy,Documentary            nm0222816            20000.0   \n",
       "3466         Drama,Horror,Thriller            nm4192760            17300.0   \n",
       "3467                         Drama            nm5874888            15000.0   \n",
       "3471        Horror,Sci-Fi,Thriller            nm0001675             7000.0   \n",
       "3472         Drama,Horror,Thriller  nm4766951,nm5792320             1400.0   \n",
       "\n",
       "      domestic_gross  worldwide_gross  TN_year TN_month  \n",
       "348              0.0              0.0     2017      Dec  \n",
       "374              0.0              0.0     2020      Feb  \n",
       "403              0.0        1521672.0     2019      Jan  \n",
       "583              0.0              0.0     2015      Dec  \n",
       "584              0.0              0.0     2019      Nov  \n",
       "...              ...              ...      ...      ...  \n",
       "3465             0.0              0.0     2015      Apr  \n",
       "3466             0.0              0.0     2011      Nov  \n",
       "3467             0.0              0.0     2014      Dec  \n",
       "3471             0.0              0.0     2018      Dec  \n",
       "3472             0.0              0.0     2015      Sep  \n",
       "\n",
       "[439 rows x 9 columns]"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# realized domestic_gr0ss for 2020 was 0 so decided to see if there were more rows with value 0f 0\n",
    "ten_yr_table.loc[(ten_yr_table['domestic_gross'] == 0) | (ten_yr_table['worldwide_gross'] == 0)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "cleaned_data = ten_yr_table.loc[(ten_yr_table['domestic_gross'] != 0) & (ten_yr_table['worldwide_gross'] != 0)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>movie</th>\n",
       "      <th>original_title</th>\n",
       "      <th>genres</th>\n",
       "      <th>directors</th>\n",
       "      <th>production_budget</th>\n",
       "      <th>domestic_gross</th>\n",
       "      <th>worldwide_gross</th>\n",
       "      <th>TN_year</th>\n",
       "      <th>TN_month</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Pirates of the Caribbean: On Stranger Tides</td>\n",
       "      <td>Pirates of the Caribbean: On Stranger Tides</td>\n",
       "      <td>Action,Adventure,Fantasy</td>\n",
       "      <td>nm0551128</td>\n",
       "      <td>410600000.0</td>\n",
       "      <td>241063875.0</td>\n",
       "      <td>1.045664e+09</td>\n",
       "      <td>2011</td>\n",
       "      <td>May</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Dark Phoenix</td>\n",
       "      <td>Dark Phoenix</td>\n",
       "      <td>Action,Adventure,Sci-Fi</td>\n",
       "      <td>nm1334526</td>\n",
       "      <td>350000000.0</td>\n",
       "      <td>42762350.0</td>\n",
       "      <td>1.497624e+08</td>\n",
       "      <td>2019</td>\n",
       "      <td>Jun</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Avengers: Age of Ultron</td>\n",
       "      <td>Avengers: Age of Ultron</td>\n",
       "      <td>Action,Adventure,Sci-Fi</td>\n",
       "      <td>nm0923736</td>\n",
       "      <td>330600000.0</td>\n",
       "      <td>459005868.0</td>\n",
       "      <td>1.403014e+09</td>\n",
       "      <td>2015</td>\n",
       "      <td>May</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Avengers: Infinity War</td>\n",
       "      <td>Avengers: Infinity War</td>\n",
       "      <td>Action,Adventure,Sci-Fi</td>\n",
       "      <td>nm0751577,nm0751648</td>\n",
       "      <td>300000000.0</td>\n",
       "      <td>678815482.0</td>\n",
       "      <td>2.048134e+09</td>\n",
       "      <td>2018</td>\n",
       "      <td>Apr</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Justice League</td>\n",
       "      <td>Justice League</td>\n",
       "      <td>Action,Adventure,Fantasy</td>\n",
       "      <td>nm0811583</td>\n",
       "      <td>300000000.0</td>\n",
       "      <td>229024295.0</td>\n",
       "      <td>6.559452e+08</td>\n",
       "      <td>2017</td>\n",
       "      <td>Nov</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3450</th>\n",
       "      <td>Krisha</td>\n",
       "      <td>Krisha</td>\n",
       "      <td>Drama</td>\n",
       "      <td>nm4099092</td>\n",
       "      <td>30000.0</td>\n",
       "      <td>144822.0</td>\n",
       "      <td>1.448220e+05</td>\n",
       "      <td>2016</td>\n",
       "      <td>Mar</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3452</th>\n",
       "      <td>Emily</td>\n",
       "      <td>Emily</td>\n",
       "      <td>Documentary</td>\n",
       "      <td>nm7470465</td>\n",
       "      <td>27000.0</td>\n",
       "      <td>3547.0</td>\n",
       "      <td>3.547000e+03</td>\n",
       "      <td>2017</td>\n",
       "      <td>Jan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3453</th>\n",
       "      <td>Emily</td>\n",
       "      <td>Emily</td>\n",
       "      <td>Drama</td>\n",
       "      <td>nm0573934</td>\n",
       "      <td>27000.0</td>\n",
       "      <td>3547.0</td>\n",
       "      <td>3.547000e+03</td>\n",
       "      <td>2017</td>\n",
       "      <td>Jan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3454</th>\n",
       "      <td>Emily</td>\n",
       "      <td>Emily</td>\n",
       "      <td>Drama</td>\n",
       "      <td>nm6254824</td>\n",
       "      <td>27000.0</td>\n",
       "      <td>3547.0</td>\n",
       "      <td>3.547000e+03</td>\n",
       "      <td>2017</td>\n",
       "      <td>Jan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3470</th>\n",
       "      <td>Newlyweds</td>\n",
       "      <td>Newlyweds</td>\n",
       "      <td>Comedy,Drama</td>\n",
       "      <td>nm0122653</td>\n",
       "      <td>9000.0</td>\n",
       "      <td>4584.0</td>\n",
       "      <td>4.584000e+03</td>\n",
       "      <td>2012</td>\n",
       "      <td>Jan</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2043 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                            movie  \\\n",
       "0     Pirates of the Caribbean: On Stranger Tides   \n",
       "1                                    Dark Phoenix   \n",
       "2                         Avengers: Age of Ultron   \n",
       "3                          Avengers: Infinity War   \n",
       "4                                  Justice League   \n",
       "...                                           ...   \n",
       "3450                                       Krisha   \n",
       "3452                                        Emily   \n",
       "3453                                        Emily   \n",
       "3454                                        Emily   \n",
       "3470                                    Newlyweds   \n",
       "\n",
       "                                   original_title                    genres  \\\n",
       "0     Pirates of the Caribbean: On Stranger Tides  Action,Adventure,Fantasy   \n",
       "1                                    Dark Phoenix   Action,Adventure,Sci-Fi   \n",
       "2                         Avengers: Age of Ultron   Action,Adventure,Sci-Fi   \n",
       "3                          Avengers: Infinity War   Action,Adventure,Sci-Fi   \n",
       "4                                  Justice League  Action,Adventure,Fantasy   \n",
       "...                                           ...                       ...   \n",
       "3450                                       Krisha                     Drama   \n",
       "3452                                        Emily               Documentary   \n",
       "3453                                        Emily                     Drama   \n",
       "3454                                        Emily                     Drama   \n",
       "3470                                    Newlyweds              Comedy,Drama   \n",
       "\n",
       "                directors  production_budget  domestic_gross  worldwide_gross  \\\n",
       "0               nm0551128        410600000.0     241063875.0     1.045664e+09   \n",
       "1               nm1334526        350000000.0      42762350.0     1.497624e+08   \n",
       "2               nm0923736        330600000.0     459005868.0     1.403014e+09   \n",
       "3     nm0751577,nm0751648        300000000.0     678815482.0     2.048134e+09   \n",
       "4               nm0811583        300000000.0     229024295.0     6.559452e+08   \n",
       "...                   ...                ...             ...              ...   \n",
       "3450            nm4099092            30000.0        144822.0     1.448220e+05   \n",
       "3452            nm7470465            27000.0          3547.0     3.547000e+03   \n",
       "3453            nm0573934            27000.0          3547.0     3.547000e+03   \n",
       "3454            nm6254824            27000.0          3547.0     3.547000e+03   \n",
       "3470            nm0122653             9000.0          4584.0     4.584000e+03   \n",
       "\n",
       "      TN_year TN_month  \n",
       "0        2011      May  \n",
       "1        2019      Jun  \n",
       "2        2015      May  \n",
       "3        2018      Apr  \n",
       "4        2017      Nov  \n",
       "...       ...      ...  \n",
       "3450     2016      Mar  \n",
       "3452     2017      Jan  \n",
       "3453     2017      Jan  \n",
       "3454     2017      Jan  \n",
       "3470     2012      Jan  \n",
       "\n",
       "[2043 rows x 9 columns]"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cleaned_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "cleaned_data.to_csv('clean_data.csv', index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "learn-env",
   "language": "python",
   "name": "learn-env"
  },
  "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.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}