nettfli_etl(3).ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "a0c32c8b",
   "metadata": {},
   "source": [
    "# Netflix & Top Grossing Films ETL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "234bd6c2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: psycopg2 in c:\\users\\chisi\\anaconda3\\envs\\pythondata\\lib\\site-packages (2.9.3)\n"
     ]
    }
   ],
   "source": [
    "# Import Dependencies\n",
    "import pandas as pd\n",
    "from sqlalchemy import create_engine\n",
    "import numpy as np\n",
    "!pip install psycopg2"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c208fbf3",
   "metadata": {},
   "source": [
    "## Extract"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "7911569d",
   "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>show_id</th>\n",
       "      <th>type</th>\n",
       "      <th>title</th>\n",
       "      <th>director</th>\n",
       "      <th>cast</th>\n",
       "      <th>country</th>\n",
       "      <th>date_added</th>\n",
       "      <th>release_year</th>\n",
       "      <th>rating</th>\n",
       "      <th>duration</th>\n",
       "      <th>listed_in</th>\n",
       "      <th>description</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>s1</td>\n",
       "      <td>Movie</td>\n",
       "      <td>Dick Johnson Is Dead</td>\n",
       "      <td>Kirsten Johnson</td>\n",
       "      <td>NaN</td>\n",
       "      <td>United States</td>\n",
       "      <td>September 25, 2021</td>\n",
       "      <td>2020</td>\n",
       "      <td>PG-13</td>\n",
       "      <td>90 min</td>\n",
       "      <td>Documentaries</td>\n",
       "      <td>As her father nears the end of his life, filmm...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>s2</td>\n",
       "      <td>TV Show</td>\n",
       "      <td>Blood &amp; Water</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Ama Qamata, Khosi Ngema, Gail Mabalane, Thaban...</td>\n",
       "      <td>South Africa</td>\n",
       "      <td>September 24, 2021</td>\n",
       "      <td>2021</td>\n",
       "      <td>TV-MA</td>\n",
       "      <td>2 Seasons</td>\n",
       "      <td>International TV Shows, TV Dramas, TV Mysteries</td>\n",
       "      <td>After crossing paths at a party, a Cape Town t...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>s3</td>\n",
       "      <td>TV Show</td>\n",
       "      <td>Ganglands</td>\n",
       "      <td>Julien Leclercq</td>\n",
       "      <td>Sami Bouajila, Tracy Gotoas, Samuel Jouy, Nabi...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>September 24, 2021</td>\n",
       "      <td>2021</td>\n",
       "      <td>TV-MA</td>\n",
       "      <td>1 Season</td>\n",
       "      <td>Crime TV Shows, International TV Shows, TV Act...</td>\n",
       "      <td>To protect his family from a powerful drug lor...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>s4</td>\n",
       "      <td>TV Show</td>\n",
       "      <td>Jailbirds New Orleans</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>September 24, 2021</td>\n",
       "      <td>2021</td>\n",
       "      <td>TV-MA</td>\n",
       "      <td>1 Season</td>\n",
       "      <td>Docuseries, Reality TV</td>\n",
       "      <td>Feuds, flirtations and toilet talk go down amo...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>s5</td>\n",
       "      <td>TV Show</td>\n",
       "      <td>Kota Factory</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Mayur More, Jitendra Kumar, Ranjan Raj, Alam K...</td>\n",
       "      <td>India</td>\n",
       "      <td>September 24, 2021</td>\n",
       "      <td>2021</td>\n",
       "      <td>TV-MA</td>\n",
       "      <td>2 Seasons</td>\n",
       "      <td>International TV Shows, Romantic TV Shows, TV ...</td>\n",
       "      <td>In a city of coaching centers known to train I...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  show_id     type                  title         director  \\\n",
       "0      s1    Movie   Dick Johnson Is Dead  Kirsten Johnson   \n",
       "1      s2  TV Show          Blood & Water              NaN   \n",
       "2      s3  TV Show              Ganglands  Julien Leclercq   \n",
       "3      s4  TV Show  Jailbirds New Orleans              NaN   \n",
       "4      s5  TV Show           Kota Factory              NaN   \n",
       "\n",
       "                                                cast        country  \\\n",
       "0                                                NaN  United States   \n",
       "1  Ama Qamata, Khosi Ngema, Gail Mabalane, Thaban...   South Africa   \n",
       "2  Sami Bouajila, Tracy Gotoas, Samuel Jouy, Nabi...            NaN   \n",
       "3                                                NaN            NaN   \n",
       "4  Mayur More, Jitendra Kumar, Ranjan Raj, Alam K...          India   \n",
       "\n",
       "           date_added  release_year rating   duration  \\\n",
       "0  September 25, 2021          2020  PG-13     90 min   \n",
       "1  September 24, 2021          2021  TV-MA  2 Seasons   \n",
       "2  September 24, 2021          2021  TV-MA   1 Season   \n",
       "3  September 24, 2021          2021  TV-MA   1 Season   \n",
       "4  September 24, 2021          2021  TV-MA  2 Seasons   \n",
       "\n",
       "                                           listed_in  \\\n",
       "0                                      Documentaries   \n",
       "1    International TV Shows, TV Dramas, TV Mysteries   \n",
       "2  Crime TV Shows, International TV Shows, TV Act...   \n",
       "3                             Docuseries, Reality TV   \n",
       "4  International TV Shows, Romantic TV Shows, TV ...   \n",
       "\n",
       "                                         description  \n",
       "0  As her father nears the end of his life, filmm...  \n",
       "1  After crossing paths at a party, a Cape Town t...  \n",
       "2  To protect his family from a powerful drug lor...  \n",
       "3  Feuds, flirtations and toilet talk go down amo...  \n",
       "4  In a city of coaching centers known to train I...  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "csv_file = \"netflix_titles.csv\"\n",
    "netflix_df = pd.read_csv(csv_file)\n",
    "netflix_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "fcaef3be",
   "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>Poster_Link</th>\n",
       "      <th>Series_Title</th>\n",
       "      <th>Released_Year</th>\n",
       "      <th>Certificate</th>\n",
       "      <th>Runtime</th>\n",
       "      <th>Genre</th>\n",
       "      <th>IMDB_Rating</th>\n",
       "      <th>Overview</th>\n",
       "      <th>Meta_score</th>\n",
       "      <th>Director</th>\n",
       "      <th>Star1</th>\n",
       "      <th>Star2</th>\n",
       "      <th>Star3</th>\n",
       "      <th>Star4</th>\n",
       "      <th>No_of_Votes</th>\n",
       "      <th>Gross</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>https://m.media-amazon.com/images/M/MV5BMDFkYT...</td>\n",
       "      <td>The Shawshank Redemption</td>\n",
       "      <td>1994</td>\n",
       "      <td>A</td>\n",
       "      <td>142 min</td>\n",
       "      <td>Drama</td>\n",
       "      <td>9.3</td>\n",
       "      <td>Two imprisoned men bond over a number of years...</td>\n",
       "      <td>80.0</td>\n",
       "      <td>Frank Darabont</td>\n",
       "      <td>Tim Robbins</td>\n",
       "      <td>Morgan Freeman</td>\n",
       "      <td>Bob Gunton</td>\n",
       "      <td>William Sadler</td>\n",
       "      <td>2343110</td>\n",
       "      <td>28,341,469</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>https://m.media-amazon.com/images/M/MV5BM2MyNj...</td>\n",
       "      <td>The Godfather</td>\n",
       "      <td>1972</td>\n",
       "      <td>A</td>\n",
       "      <td>175 min</td>\n",
       "      <td>Crime, Drama</td>\n",
       "      <td>9.2</td>\n",
       "      <td>An organized crime dynasty's aging patriarch t...</td>\n",
       "      <td>100.0</td>\n",
       "      <td>Francis Ford Coppola</td>\n",
       "      <td>Marlon Brando</td>\n",
       "      <td>Al Pacino</td>\n",
       "      <td>James Caan</td>\n",
       "      <td>Diane Keaton</td>\n",
       "      <td>1620367</td>\n",
       "      <td>134,966,411</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>https://m.media-amazon.com/images/M/MV5BMTMxNT...</td>\n",
       "      <td>The Dark Knight</td>\n",
       "      <td>2008</td>\n",
       "      <td>UA</td>\n",
       "      <td>152 min</td>\n",
       "      <td>Action, Crime, Drama</td>\n",
       "      <td>9.0</td>\n",
       "      <td>When the menace known as the Joker wreaks havo...</td>\n",
       "      <td>84.0</td>\n",
       "      <td>Christopher Nolan</td>\n",
       "      <td>Christian Bale</td>\n",
       "      <td>Heath Ledger</td>\n",
       "      <td>Aaron Eckhart</td>\n",
       "      <td>Michael Caine</td>\n",
       "      <td>2303232</td>\n",
       "      <td>534,858,444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>https://m.media-amazon.com/images/M/MV5BMWMwMG...</td>\n",
       "      <td>The Godfather: Part II</td>\n",
       "      <td>1974</td>\n",
       "      <td>A</td>\n",
       "      <td>202 min</td>\n",
       "      <td>Crime, Drama</td>\n",
       "      <td>9.0</td>\n",
       "      <td>The early life and career of Vito Corleone in ...</td>\n",
       "      <td>90.0</td>\n",
       "      <td>Francis Ford Coppola</td>\n",
       "      <td>Al Pacino</td>\n",
       "      <td>Robert De Niro</td>\n",
       "      <td>Robert Duvall</td>\n",
       "      <td>Diane Keaton</td>\n",
       "      <td>1129952</td>\n",
       "      <td>57,300,000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>https://m.media-amazon.com/images/M/MV5BMWU4N2...</td>\n",
       "      <td>12 Angry Men</td>\n",
       "      <td>1957</td>\n",
       "      <td>U</td>\n",
       "      <td>96 min</td>\n",
       "      <td>Crime, Drama</td>\n",
       "      <td>9.0</td>\n",
       "      <td>A jury holdout attempts to prevent a miscarria...</td>\n",
       "      <td>96.0</td>\n",
       "      <td>Sidney Lumet</td>\n",
       "      <td>Henry Fonda</td>\n",
       "      <td>Lee J. Cobb</td>\n",
       "      <td>Martin Balsam</td>\n",
       "      <td>John Fiedler</td>\n",
       "      <td>689845</td>\n",
       "      <td>4,360,000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                         Poster_Link  \\\n",
       "0  https://m.media-amazon.com/images/M/MV5BMDFkYT...   \n",
       "1  https://m.media-amazon.com/images/M/MV5BM2MyNj...   \n",
       "2  https://m.media-amazon.com/images/M/MV5BMTMxNT...   \n",
       "3  https://m.media-amazon.com/images/M/MV5BMWMwMG...   \n",
       "4  https://m.media-amazon.com/images/M/MV5BMWU4N2...   \n",
       "\n",
       "               Series_Title Released_Year Certificate  Runtime  \\\n",
       "0  The Shawshank Redemption          1994           A  142 min   \n",
       "1             The Godfather          1972           A  175 min   \n",
       "2           The Dark Knight          2008          UA  152 min   \n",
       "3    The Godfather: Part II          1974           A  202 min   \n",
       "4              12 Angry Men          1957           U   96 min   \n",
       "\n",
       "                  Genre  IMDB_Rating  \\\n",
       "0                 Drama          9.3   \n",
       "1          Crime, Drama          9.2   \n",
       "2  Action, Crime, Drama          9.0   \n",
       "3          Crime, Drama          9.0   \n",
       "4          Crime, Drama          9.0   \n",
       "\n",
       "                                            Overview  Meta_score  \\\n",
       "0  Two imprisoned men bond over a number of years...        80.0   \n",
       "1  An organized crime dynasty's aging patriarch t...       100.0   \n",
       "2  When the menace known as the Joker wreaks havo...        84.0   \n",
       "3  The early life and career of Vito Corleone in ...        90.0   \n",
       "4  A jury holdout attempts to prevent a miscarria...        96.0   \n",
       "\n",
       "               Director           Star1           Star2          Star3  \\\n",
       "0        Frank Darabont     Tim Robbins  Morgan Freeman     Bob Gunton   \n",
       "1  Francis Ford Coppola   Marlon Brando       Al Pacino     James Caan   \n",
       "2     Christopher Nolan  Christian Bale    Heath Ledger  Aaron Eckhart   \n",
       "3  Francis Ford Coppola       Al Pacino  Robert De Niro  Robert Duvall   \n",
       "4          Sidney Lumet     Henry Fonda     Lee J. Cobb  Martin Balsam   \n",
       "\n",
       "            Star4  No_of_Votes        Gross  \n",
       "0  William Sadler      2343110   28,341,469  \n",
       "1    Diane Keaton      1620367  134,966,411  \n",
       "2   Michael Caine      2303232  534,858,444  \n",
       "3    Diane Keaton      1129952   57,300,000  \n",
       "4    John Fiedler       689845    4,360,000  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "csv_file_1 = \"imdb_top_1000.csv\"\n",
    "imdb_df = pd.read_csv(csv_file_1)\n",
    "imdb_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c03b91af",
   "metadata": {},
   "source": [
    "## Transform"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "8bbb3e8b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['show_id', 'type', 'title', 'director', 'cast', 'country', 'date_added',\n",
       "       'release_year', 'rating', 'duration', 'listed_in', 'description'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Clean netflix_df\n",
    "netflix_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "4e552b1e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# We want to drop show_id, cast, country, date_added\n",
    "del netflix_df['show_id']\n",
    "del netflix_df['cast']\n",
    "del netflix_df['country']\n",
    "del netflix_df['date_added']\n",
    "del netflix_df['rating']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "66ddc549",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Rename columns \n",
    "netflix_df = netflix_df.rename(columns={\"type\":\"genre\"})\n",
    "netflix_df = netflix_df.rename(columns={\"duration\":\"runtime\"})\n",
    "netflix_df = netflix_df.rename(columns={\"description\":\"overview\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "ba718ae9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Poster_Link', 'Series_Title', 'Released_Year', 'Certificate',\n",
       "       'Runtime', 'Genre', 'IMDB_Rating', 'Overview', 'Meta_score', 'Director',\n",
       "       'Star1', 'Star2', 'Star3', 'Star4', 'No_of_Votes', 'Gross'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Clean imdb_df\n",
    "imdb_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "452d3a07",
   "metadata": {},
   "outputs": [],
   "source": [
    "# We want to drop Poster_link, Star1, Star2, Star3, Star4\n",
    "del imdb_df['Poster_Link']\n",
    "del imdb_df['Star1']\n",
    "del imdb_df['Star2']\n",
    "del imdb_df['Star3']\n",
    "del imdb_df['Star4']\n",
    "del imdb_df['Certificate']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "0359f133",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Rename columns \n",
    "imdb_df = imdb_df.rename(columns={\"Series_Title\":\"title\"})\n",
    "imdb_df = imdb_df.rename(columns={\"Released_Year\":\"release_year\"})\n",
    "imdb_df = imdb_df.rename(columns={\"Runtime\":\"runtime\"})\n",
    "imdb_df = imdb_df.rename(columns={\"Genre\":\"genre\"})\n",
    "imdb_df = imdb_df.rename(columns={\"Overview\":\"overview\"})\n",
    "imdb_df = imdb_df.rename(columns={\"Director\":\"director\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "6f58c900",
   "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>genre</th>\n",
       "      <th>title</th>\n",
       "      <th>director</th>\n",
       "      <th>release_year</th>\n",
       "      <th>runtime</th>\n",
       "      <th>listed_in</th>\n",
       "      <th>overview</th>\n",
       "      <th>IMDB_Rating</th>\n",
       "      <th>Meta_score</th>\n",
       "      <th>No_of_Votes</th>\n",
       "      <th>Gross</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Movie</td>\n",
       "      <td>Dick Johnson Is Dead</td>\n",
       "      <td>Kirsten Johnson</td>\n",
       "      <td>2020</td>\n",
       "      <td>90 min</td>\n",
       "      <td>Documentaries</td>\n",
       "      <td>As her father nears the end of his life, filmm...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>TV Show</td>\n",
       "      <td>Blood &amp; Water</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2021</td>\n",
       "      <td>2 Seasons</td>\n",
       "      <td>International TV Shows, TV Dramas, TV Mysteries</td>\n",
       "      <td>After crossing paths at a party, a Cape Town t...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>TV Show</td>\n",
       "      <td>Ganglands</td>\n",
       "      <td>Julien Leclercq</td>\n",
       "      <td>2021</td>\n",
       "      <td>1 Season</td>\n",
       "      <td>Crime TV Shows, International TV Shows, TV Act...</td>\n",
       "      <td>To protect his family from a powerful drug lor...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>TV Show</td>\n",
       "      <td>Jailbirds New Orleans</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2021</td>\n",
       "      <td>1 Season</td>\n",
       "      <td>Docuseries, Reality TV</td>\n",
       "      <td>Feuds, flirtations and toilet talk go down amo...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>TV Show</td>\n",
       "      <td>Kota Factory</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2021</td>\n",
       "      <td>2 Seasons</td>\n",
       "      <td>International TV Shows, Romantic TV Shows, TV ...</td>\n",
       "      <td>In a city of coaching centers known to train I...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>995</th>\n",
       "      <td>Comedy, Drama, Romance</td>\n",
       "      <td>Breakfast at Tiffany's</td>\n",
       "      <td>Blake Edwards</td>\n",
       "      <td>1961</td>\n",
       "      <td>115 min</td>\n",
       "      <td>NaN</td>\n",
       "      <td>A young New York socialite becomes interested ...</td>\n",
       "      <td>7.6</td>\n",
       "      <td>76.0</td>\n",
       "      <td>166544.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>996</th>\n",
       "      <td>Drama, Western</td>\n",
       "      <td>Giant</td>\n",
       "      <td>George Stevens</td>\n",
       "      <td>1956</td>\n",
       "      <td>201 min</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Sprawling epic covering the life of a Texas ca...</td>\n",
       "      <td>7.6</td>\n",
       "      <td>84.0</td>\n",
       "      <td>34075.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>997</th>\n",
       "      <td>Drama, Romance, War</td>\n",
       "      <td>From Here to Eternity</td>\n",
       "      <td>Fred Zinnemann</td>\n",
       "      <td>1953</td>\n",
       "      <td>118 min</td>\n",
       "      <td>NaN</td>\n",
       "      <td>In Hawaii in 1941, a private is cruelly punish...</td>\n",
       "      <td>7.6</td>\n",
       "      <td>85.0</td>\n",
       "      <td>43374.0</td>\n",
       "      <td>30,500,000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>998</th>\n",
       "      <td>Drama, War</td>\n",
       "      <td>Lifeboat</td>\n",
       "      <td>Alfred Hitchcock</td>\n",
       "      <td>1944</td>\n",
       "      <td>97 min</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Several survivors of a torpedoed merchant ship...</td>\n",
       "      <td>7.6</td>\n",
       "      <td>78.0</td>\n",
       "      <td>26471.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>999</th>\n",
       "      <td>Crime, Mystery, Thriller</td>\n",
       "      <td>The 39 Steps</td>\n",
       "      <td>Alfred Hitchcock</td>\n",
       "      <td>1935</td>\n",
       "      <td>86 min</td>\n",
       "      <td>NaN</td>\n",
       "      <td>A man in London tries to help a counter-espion...</td>\n",
       "      <td>7.6</td>\n",
       "      <td>93.0</td>\n",
       "      <td>51853.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>9807 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                        genre                   title          director  \\\n",
       "0                       Movie    Dick Johnson Is Dead   Kirsten Johnson   \n",
       "1                     TV Show           Blood & Water               NaN   \n",
       "2                     TV Show               Ganglands   Julien Leclercq   \n",
       "3                     TV Show   Jailbirds New Orleans               NaN   \n",
       "4                     TV Show            Kota Factory               NaN   \n",
       "..                        ...                     ...               ...   \n",
       "995    Comedy, Drama, Romance  Breakfast at Tiffany's     Blake Edwards   \n",
       "996            Drama, Western                   Giant    George Stevens   \n",
       "997       Drama, Romance, War   From Here to Eternity    Fred Zinnemann   \n",
       "998                Drama, War                Lifeboat  Alfred Hitchcock   \n",
       "999  Crime, Mystery, Thriller            The 39 Steps  Alfred Hitchcock   \n",
       "\n",
       "    release_year    runtime  \\\n",
       "0           2020     90 min   \n",
       "1           2021  2 Seasons   \n",
       "2           2021   1 Season   \n",
       "3           2021   1 Season   \n",
       "4           2021  2 Seasons   \n",
       "..           ...        ...   \n",
       "995         1961    115 min   \n",
       "996         1956    201 min   \n",
       "997         1953    118 min   \n",
       "998         1944     97 min   \n",
       "999         1935     86 min   \n",
       "\n",
       "                                             listed_in  \\\n",
       "0                                        Documentaries   \n",
       "1      International TV Shows, TV Dramas, TV Mysteries   \n",
       "2    Crime TV Shows, International TV Shows, TV Act...   \n",
       "3                               Docuseries, Reality TV   \n",
       "4    International TV Shows, Romantic TV Shows, TV ...   \n",
       "..                                                 ...   \n",
       "995                                                NaN   \n",
       "996                                                NaN   \n",
       "997                                                NaN   \n",
       "998                                                NaN   \n",
       "999                                                NaN   \n",
       "\n",
       "                                              overview  IMDB_Rating  \\\n",
       "0    As her father nears the end of his life, filmm...          NaN   \n",
       "1    After crossing paths at a party, a Cape Town t...          NaN   \n",
       "2    To protect his family from a powerful drug lor...          NaN   \n",
       "3    Feuds, flirtations and toilet talk go down amo...          NaN   \n",
       "4    In a city of coaching centers known to train I...          NaN   \n",
       "..                                                 ...          ...   \n",
       "995  A young New York socialite becomes interested ...          7.6   \n",
       "996  Sprawling epic covering the life of a Texas ca...          7.6   \n",
       "997  In Hawaii in 1941, a private is cruelly punish...          7.6   \n",
       "998  Several survivors of a torpedoed merchant ship...          7.6   \n",
       "999  A man in London tries to help a counter-espion...          7.6   \n",
       "\n",
       "     Meta_score  No_of_Votes       Gross  \n",
       "0           NaN          NaN         NaN  \n",
       "1           NaN          NaN         NaN  \n",
       "2           NaN          NaN         NaN  \n",
       "3           NaN          NaN         NaN  \n",
       "4           NaN          NaN         NaN  \n",
       "..          ...          ...         ...  \n",
       "995        76.0     166544.0         NaN  \n",
       "996        84.0      34075.0         NaN  \n",
       "997        85.0      43374.0  30,500,000  \n",
       "998        78.0      26471.0         NaN  \n",
       "999        93.0      51853.0         NaN  \n",
       "\n",
       "[9807 rows x 11 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Merge dfs\n",
    "netflix_imdb1 = pd.concat([netflix_df, imdb_df])\n",
    "netflix_imdb1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "0229a936",
   "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>genre</th>\n",
       "      <th>title</th>\n",
       "      <th>director</th>\n",
       "      <th>release_year</th>\n",
       "      <th>runtime</th>\n",
       "      <th>listed_in</th>\n",
       "      <th>overview</th>\n",
       "      <th>IMDB_Rating</th>\n",
       "      <th>Meta_score</th>\n",
       "      <th>No_of_Votes</th>\n",
       "      <th>Gross</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>966</th>\n",
       "      <td>Adventure, Drama, History</td>\n",
       "      <td>Apollo 13</td>\n",
       "      <td>Ron Howard</td>\n",
       "      <td>PG</td>\n",
       "      <td>140 min</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NASA must devise a strategy to return Apollo 1...</td>\n",
       "      <td>7.6</td>\n",
       "      <td>77.0</td>\n",
       "      <td>269197.0</td>\n",
       "      <td>173,837,933</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         genre      title    director release_year  runtime  \\\n",
       "966  Adventure, Drama, History  Apollo 13  Ron Howard           PG  140 min   \n",
       "\n",
       "    listed_in                                           overview  IMDB_Rating  \\\n",
       "966       NaN  NASA must devise a strategy to return Apollo 1...          7.6   \n",
       "\n",
       "     Meta_score  No_of_Votes        Gross  \n",
       "966        77.0     269197.0  173,837,933  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "netflix_imdb1.loc[netflix_imdb1['release_year'] == 'PG']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "c29796f3",
   "metadata": {},
   "outputs": [],
   "source": [
    "netflix_imdb1.loc[966, 'release_year'] = 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "3a7e1565",
   "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>genre</th>\n",
       "      <th>title</th>\n",
       "      <th>director</th>\n",
       "      <th>release_year</th>\n",
       "      <th>runtime</th>\n",
       "      <th>listed_in</th>\n",
       "      <th>overview</th>\n",
       "      <th>IMDB_Rating</th>\n",
       "      <th>Meta_score</th>\n",
       "      <th>No_of_Votes</th>\n",
       "      <th>Gross</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Movie</td>\n",
       "      <td>Dick Johnson Is Dead</td>\n",
       "      <td>Kirsten Johnson</td>\n",
       "      <td>2020</td>\n",
       "      <td>90 min</td>\n",
       "      <td>Documentaries</td>\n",
       "      <td>As her father nears the end of his life, filmm...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>TV Show</td>\n",
       "      <td>Blood &amp; Water</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2021</td>\n",
       "      <td>2 Seasons</td>\n",
       "      <td>International TV Shows, TV Dramas, TV Mysteries</td>\n",
       "      <td>After crossing paths at a party, a Cape Town t...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>TV Show</td>\n",
       "      <td>Ganglands</td>\n",
       "      <td>Julien Leclercq</td>\n",
       "      <td>2021</td>\n",
       "      <td>1 Season</td>\n",
       "      <td>Crime TV Shows, International TV Shows, TV Act...</td>\n",
       "      <td>To protect his family from a powerful drug lor...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>TV Show</td>\n",
       "      <td>Jailbirds New Orleans</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2021</td>\n",
       "      <td>1 Season</td>\n",
       "      <td>Docuseries, Reality TV</td>\n",
       "      <td>Feuds, flirtations and toilet talk go down amo...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>TV Show</td>\n",
       "      <td>Kota Factory</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2021</td>\n",
       "      <td>2 Seasons</td>\n",
       "      <td>International TV Shows, Romantic TV Shows, TV ...</td>\n",
       "      <td>In a city of coaching centers known to train I...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>995</th>\n",
       "      <td>Comedy, Drama, Romance</td>\n",
       "      <td>Breakfast at Tiffany's</td>\n",
       "      <td>Blake Edwards</td>\n",
       "      <td>1961</td>\n",
       "      <td>115 min</td>\n",
       "      <td>NaN</td>\n",
       "      <td>A young New York socialite becomes interested ...</td>\n",
       "      <td>7.6</td>\n",
       "      <td>76.0</td>\n",
       "      <td>166544.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>996</th>\n",
       "      <td>Drama, Western</td>\n",
       "      <td>Giant</td>\n",
       "      <td>George Stevens</td>\n",
       "      <td>1956</td>\n",
       "      <td>201 min</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Sprawling epic covering the life of a Texas ca...</td>\n",
       "      <td>7.6</td>\n",
       "      <td>84.0</td>\n",
       "      <td>34075.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>997</th>\n",
       "      <td>Drama, Romance, War</td>\n",
       "      <td>From Here to Eternity</td>\n",
       "      <td>Fred Zinnemann</td>\n",
       "      <td>1953</td>\n",
       "      <td>118 min</td>\n",
       "      <td>NaN</td>\n",
       "      <td>In Hawaii in 1941, a private is cruelly punish...</td>\n",
       "      <td>7.6</td>\n",
       "      <td>85.0</td>\n",
       "      <td>43374.0</td>\n",
       "      <td>30,500,000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>998</th>\n",
       "      <td>Drama, War</td>\n",
       "      <td>Lifeboat</td>\n",
       "      <td>Alfred Hitchcock</td>\n",
       "      <td>1944</td>\n",
       "      <td>97 min</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Several survivors of a torpedoed merchant ship...</td>\n",
       "      <td>7.6</td>\n",
       "      <td>78.0</td>\n",
       "      <td>26471.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>999</th>\n",
       "      <td>Crime, Mystery, Thriller</td>\n",
       "      <td>The 39 Steps</td>\n",
       "      <td>Alfred Hitchcock</td>\n",
       "      <td>1935</td>\n",
       "      <td>86 min</td>\n",
       "      <td>NaN</td>\n",
       "      <td>A man in London tries to help a counter-espion...</td>\n",
       "      <td>7.6</td>\n",
       "      <td>93.0</td>\n",
       "      <td>51853.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>9807 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                        genre                   title          director  \\\n",
       "0                       Movie    Dick Johnson Is Dead   Kirsten Johnson   \n",
       "1                     TV Show           Blood & Water               NaN   \n",
       "2                     TV Show               Ganglands   Julien Leclercq   \n",
       "3                     TV Show   Jailbirds New Orleans               NaN   \n",
       "4                     TV Show            Kota Factory               NaN   \n",
       "..                        ...                     ...               ...   \n",
       "995    Comedy, Drama, Romance  Breakfast at Tiffany's     Blake Edwards   \n",
       "996            Drama, Western                   Giant    George Stevens   \n",
       "997       Drama, Romance, War   From Here to Eternity    Fred Zinnemann   \n",
       "998                Drama, War                Lifeboat  Alfred Hitchcock   \n",
       "999  Crime, Mystery, Thriller            The 39 Steps  Alfred Hitchcock   \n",
       "\n",
       "    release_year    runtime  \\\n",
       "0           2020     90 min   \n",
       "1           2021  2 Seasons   \n",
       "2           2021   1 Season   \n",
       "3           2021   1 Season   \n",
       "4           2021  2 Seasons   \n",
       "..           ...        ...   \n",
       "995         1961    115 min   \n",
       "996         1956    201 min   \n",
       "997         1953    118 min   \n",
       "998         1944     97 min   \n",
       "999         1935     86 min   \n",
       "\n",
       "                                             listed_in  \\\n",
       "0                                        Documentaries   \n",
       "1      International TV Shows, TV Dramas, TV Mysteries   \n",
       "2    Crime TV Shows, International TV Shows, TV Act...   \n",
       "3                               Docuseries, Reality TV   \n",
       "4    International TV Shows, Romantic TV Shows, TV ...   \n",
       "..                                                 ...   \n",
       "995                                                NaN   \n",
       "996                                                NaN   \n",
       "997                                                NaN   \n",
       "998                                                NaN   \n",
       "999                                                NaN   \n",
       "\n",
       "                                              overview  IMDB_Rating  \\\n",
       "0    As her father nears the end of his life, filmm...          NaN   \n",
       "1    After crossing paths at a party, a Cape Town t...          NaN   \n",
       "2    To protect his family from a powerful drug lor...          NaN   \n",
       "3    Feuds, flirtations and toilet talk go down amo...          NaN   \n",
       "4    In a city of coaching centers known to train I...          NaN   \n",
       "..                                                 ...          ...   \n",
       "995  A young New York socialite becomes interested ...          7.6   \n",
       "996  Sprawling epic covering the life of a Texas ca...          7.6   \n",
       "997  In Hawaii in 1941, a private is cruelly punish...          7.6   \n",
       "998  Several survivors of a torpedoed merchant ship...          7.6   \n",
       "999  A man in London tries to help a counter-espion...          7.6   \n",
       "\n",
       "     Meta_score  No_of_Votes       Gross  \n",
       "0           NaN          NaN         NaN  \n",
       "1           NaN          NaN         NaN  \n",
       "2           NaN          NaN         NaN  \n",
       "3           NaN          NaN         NaN  \n",
       "4           NaN          NaN         NaN  \n",
       "..          ...          ...         ...  \n",
       "995        76.0     166544.0         NaN  \n",
       "996        84.0      34075.0         NaN  \n",
       "997        85.0      43374.0  30,500,000  \n",
       "998        78.0      26471.0         NaN  \n",
       "999        93.0      51853.0         NaN  \n",
       "\n",
       "[9807 rows x 11 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "netflix_imdb1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "7a3a9a5c",
   "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>genre</th>\n",
       "      <th>title</th>\n",
       "      <th>director</th>\n",
       "      <th>release_year</th>\n",
       "      <th>runtime</th>\n",
       "      <th>listed_in</th>\n",
       "      <th>overview</th>\n",
       "      <th>IMDB_Rating</th>\n",
       "      <th>Meta_score</th>\n",
       "      <th>No_of_Votes</th>\n",
       "      <th>Gross</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>966</th>\n",
       "      <td>Adventure, Drama, History</td>\n",
       "      <td>Apollo 13</td>\n",
       "      <td>Ron Howard</td>\n",
       "      <td>0</td>\n",
       "      <td>140 min</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NASA must devise a strategy to return Apollo 1...</td>\n",
       "      <td>7.6</td>\n",
       "      <td>77.0</td>\n",
       "      <td>269197.0</td>\n",
       "      <td>173,837,933</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         genre      title    director release_year  runtime  \\\n",
       "966  Adventure, Drama, History  Apollo 13  Ron Howard            0  140 min   \n",
       "\n",
       "    listed_in                                           overview  IMDB_Rating  \\\n",
       "966       NaN  NASA must devise a strategy to return Apollo 1...          7.6   \n",
       "\n",
       "     Meta_score  No_of_Votes        Gross  \n",
       "966        77.0     269197.0  173,837,933  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "netflix_imdb1.loc[netflix_imdb1['title'] == 'Apollo 13']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "9f6c0e5e",
   "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>genre</th>\n",
       "      <th>title</th>\n",
       "      <th>director</th>\n",
       "      <th>release_year</th>\n",
       "      <th>runtime</th>\n",
       "      <th>listed_in</th>\n",
       "      <th>overview</th>\n",
       "      <th>IMDB_Rating</th>\n",
       "      <th>Meta_score</th>\n",
       "      <th>No_of_Votes</th>\n",
       "      <th>Gross</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Movie</td>\n",
       "      <td>Dick Johnson Is Dead</td>\n",
       "      <td>Kirsten Johnson</td>\n",
       "      <td>2020</td>\n",
       "      <td>90 min</td>\n",
       "      <td>Documentaries</td>\n",
       "      <td>As her father nears the end of his life, filmm...</td>\n",
       "      <td>not currently in IMDB top 1000</td>\n",
       "      <td>not currently in IMDB top 1000</td>\n",
       "      <td>not currently in IMDB top 1000</td>\n",
       "      <td>not currently in IMDB top 1000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>TV Show</td>\n",
       "      <td>Blood &amp; Water</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2021</td>\n",
       "      <td>2 Seasons</td>\n",
       "      <td>International TV Shows, TV Dramas, TV Mysteries</td>\n",
       "      <td>After crossing paths at a party, a Cape Town t...</td>\n",
       "      <td>not currently in IMDB top 1000</td>\n",
       "      <td>not currently in IMDB top 1000</td>\n",
       "      <td>not currently in IMDB top 1000</td>\n",
       "      <td>not currently in IMDB top 1000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>TV Show</td>\n",
       "      <td>Ganglands</td>\n",
       "      <td>Julien Leclercq</td>\n",
       "      <td>2021</td>\n",
       "      <td>1 Season</td>\n",
       "      <td>Crime TV Shows, International TV Shows, TV Act...</td>\n",
       "      <td>To protect his family from a powerful drug lor...</td>\n",
       "      <td>not currently in IMDB top 1000</td>\n",
       "      <td>not currently in IMDB top 1000</td>\n",
       "      <td>not currently in IMDB top 1000</td>\n",
       "      <td>not currently in IMDB top 1000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>TV Show</td>\n",
       "      <td>Jailbirds New Orleans</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2021</td>\n",
       "      <td>1 Season</td>\n",
       "      <td>Docuseries, Reality TV</td>\n",
       "      <td>Feuds, flirtations and toilet talk go down amo...</td>\n",
       "      <td>not currently in IMDB top 1000</td>\n",
       "      <td>not currently in IMDB top 1000</td>\n",
       "      <td>not currently in IMDB top 1000</td>\n",
       "      <td>not currently in IMDB top 1000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>TV Show</td>\n",
       "      <td>Kota Factory</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2021</td>\n",
       "      <td>2 Seasons</td>\n",
       "      <td>International TV Shows, Romantic TV Shows, TV ...</td>\n",
       "      <td>In a city of coaching centers known to train I...</td>\n",
       "      <td>not currently in IMDB top 1000</td>\n",
       "      <td>not currently in IMDB top 1000</td>\n",
       "      <td>not currently in IMDB top 1000</td>\n",
       "      <td>not currently in IMDB top 1000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     genre                  title         director release_year    runtime  \\\n",
       "0    Movie   Dick Johnson Is Dead  Kirsten Johnson         2020     90 min   \n",
       "1  TV Show          Blood & Water              NaN         2021  2 Seasons   \n",
       "2  TV Show              Ganglands  Julien Leclercq         2021   1 Season   \n",
       "3  TV Show  Jailbirds New Orleans              NaN         2021   1 Season   \n",
       "4  TV Show           Kota Factory              NaN         2021  2 Seasons   \n",
       "\n",
       "                                           listed_in  \\\n",
       "0                                      Documentaries   \n",
       "1    International TV Shows, TV Dramas, TV Mysteries   \n",
       "2  Crime TV Shows, International TV Shows, TV Act...   \n",
       "3                             Docuseries, Reality TV   \n",
       "4  International TV Shows, Romantic TV Shows, TV ...   \n",
       "\n",
       "                                            overview  \\\n",
       "0  As her father nears the end of his life, filmm...   \n",
       "1  After crossing paths at a party, a Cape Town t...   \n",
       "2  To protect his family from a powerful drug lor...   \n",
       "3  Feuds, flirtations and toilet talk go down amo...   \n",
       "4  In a city of coaching centers known to train I...   \n",
       "\n",
       "                      IMDB_Rating                      Meta_score  \\\n",
       "0  not currently in IMDB top 1000  not currently in IMDB top 1000   \n",
       "1  not currently in IMDB top 1000  not currently in IMDB top 1000   \n",
       "2  not currently in IMDB top 1000  not currently in IMDB top 1000   \n",
       "3  not currently in IMDB top 1000  not currently in IMDB top 1000   \n",
       "4  not currently in IMDB top 1000  not currently in IMDB top 1000   \n",
       "\n",
       "                      No_of_Votes                           Gross  \n",
       "0  not currently in IMDB top 1000  not currently in IMDB top 1000  \n",
       "1  not currently in IMDB top 1000  not currently in IMDB top 1000  \n",
       "2  not currently in IMDB top 1000  not currently in IMDB top 1000  \n",
       "3  not currently in IMDB top 1000  not currently in IMDB top 1000  \n",
       "4  not currently in IMDB top 1000  not currently in IMDB top 1000  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Change null values\n",
    "value = \"not currently in IMDB top 1000\"\n",
    "\n",
    "netflix_imdb1['IMDB_Rating'] = netflix_imdb1['IMDB_Rating'].replace(np.nan, value)\n",
    "netflix_imdb1['Meta_score'] = netflix_imdb1['Meta_score'].replace(np.nan, value)\n",
    "netflix_imdb1['No_of_Votes'] = netflix_imdb1['No_of_Votes'].replace(np.nan, value)\n",
    "netflix_imdb1['Gross'] = netflix_imdb1['Gross'].replace(np.nan, value)\n",
    "\n",
    "netflix_imdb1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "fbb10e1d",
   "metadata": {},
   "outputs": [],
   "source": [
    "from settings import key"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "c5693234",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load\n",
    "protocol = \"postgresql\"\n",
    "username = \"postgres\"\n",
    "host = 'localhost'\n",
    "port = 5432\n",
    "database_name = \"Netflixmovies_db\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "47840032",
   "metadata": {},
   "outputs": [],
   "source": [
    "rds_connection_string = f'{protocol}://{username}:{key}@{host}:{port}/{database_name}'\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "a7f10821",
   "metadata": {},
   "outputs": [],
   "source": [
    "engine = create_engine(rds_connection_string)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "0293aa7e",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\chisi\\AppData\\Local\\Temp/ipykernel_26692/509743474.py:1: SADeprecationWarning: The Engine.table_names() method is deprecated and will be removed in a future release.  Please refer to Inspector.get_table_names(). (deprecated since: 1.4)\n",
      "  engine.table_names()\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "['movies_db']"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "engine.table_names()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "37df78cb",
   "metadata": {},
   "source": [
    "##  Load DataFrames into database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "d62ff0dd",
   "metadata": {},
   "outputs": [],
   "source": [
    " netflix_imdb1.to_sql(name='movies_db', con=engine, if_exists='append', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6e14d8eb",
   "metadata": {},
   "source": [
    "## Confirming data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "dc3ccbbd",
   "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>genre</th>\n",
       "      <th>title</th>\n",
       "      <th>director</th>\n",
       "      <th>release_year</th>\n",
       "      <th>runtime</th>\n",
       "      <th>listed_in</th>\n",
       "      <th>overview</th>\n",
       "      <th>IMDB_Rating</th>\n",
       "      <th>Meta_score</th>\n",
       "      <th>No_of_Votes</th>\n",
       "      <th>Gross</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Movie</td>\n",
       "      <td>Dick Johnson Is Dead</td>\n",
       "      <td>Kirsten Johnson</td>\n",
       "      <td>2020</td>\n",
       "      <td>90 min</td>\n",
       "      <td>Documentaries</td>\n",
       "      <td>As her father nears the end of his life, filmm...</td>\n",
       "      <td>not currently in IMDB top 1000</td>\n",
       "      <td>not currently in IMDB top 1000</td>\n",
       "      <td>not currently in IMDB top 1000</td>\n",
       "      <td>not currently in IMDB top 1000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>TV Show</td>\n",
       "      <td>Blood &amp; Water</td>\n",
       "      <td>None</td>\n",
       "      <td>2021</td>\n",
       "      <td>2 Seasons</td>\n",
       "      <td>International TV Shows, TV Dramas, TV Mysteries</td>\n",
       "      <td>After crossing paths at a party, a Cape Town t...</td>\n",
       "      <td>not currently in IMDB top 1000</td>\n",
       "      <td>not currently in IMDB top 1000</td>\n",
       "      <td>not currently in IMDB top 1000</td>\n",
       "      <td>not currently in IMDB top 1000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>TV Show</td>\n",
       "      <td>Ganglands</td>\n",
       "      <td>Julien Leclercq</td>\n",
       "      <td>2021</td>\n",
       "      <td>1 Season</td>\n",
       "      <td>Crime TV Shows, International TV Shows, TV Act...</td>\n",
       "      <td>To protect his family from a powerful drug lor...</td>\n",
       "      <td>not currently in IMDB top 1000</td>\n",
       "      <td>not currently in IMDB top 1000</td>\n",
       "      <td>not currently in IMDB top 1000</td>\n",
       "      <td>not currently in IMDB top 1000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>TV Show</td>\n",
       "      <td>Jailbirds New Orleans</td>\n",
       "      <td>None</td>\n",
       "      <td>2021</td>\n",
       "      <td>1 Season</td>\n",
       "      <td>Docuseries, Reality TV</td>\n",
       "      <td>Feuds, flirtations and toilet talk go down amo...</td>\n",
       "      <td>not currently in IMDB top 1000</td>\n",
       "      <td>not currently in IMDB top 1000</td>\n",
       "      <td>not currently in IMDB top 1000</td>\n",
       "      <td>not currently in IMDB top 1000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>TV Show</td>\n",
       "      <td>Kota Factory</td>\n",
       "      <td>None</td>\n",
       "      <td>2021</td>\n",
       "      <td>2 Seasons</td>\n",
       "      <td>International TV Shows, Romantic TV Shows, TV ...</td>\n",
       "      <td>In a city of coaching centers known to train I...</td>\n",
       "      <td>not currently in IMDB top 1000</td>\n",
       "      <td>not currently in IMDB top 1000</td>\n",
       "      <td>not currently in IMDB top 1000</td>\n",
       "      <td>not currently in IMDB top 1000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     genre                  title         director  release_year    runtime  \\\n",
       "0    Movie   Dick Johnson Is Dead  Kirsten Johnson          2020     90 min   \n",
       "1  TV Show          Blood & Water             None          2021  2 Seasons   \n",
       "2  TV Show              Ganglands  Julien Leclercq          2021   1 Season   \n",
       "3  TV Show  Jailbirds New Orleans             None          2021   1 Season   \n",
       "4  TV Show           Kota Factory             None          2021  2 Seasons   \n",
       "\n",
       "                                           listed_in  \\\n",
       "0                                      Documentaries   \n",
       "1    International TV Shows, TV Dramas, TV Mysteries   \n",
       "2  Crime TV Shows, International TV Shows, TV Act...   \n",
       "3                             Docuseries, Reality TV   \n",
       "4  International TV Shows, Romantic TV Shows, TV ...   \n",
       "\n",
       "                                            overview  \\\n",
       "0  As her father nears the end of his life, filmm...   \n",
       "1  After crossing paths at a party, a Cape Town t...   \n",
       "2  To protect his family from a powerful drug lor...   \n",
       "3  Feuds, flirtations and toilet talk go down amo...   \n",
       "4  In a city of coaching centers known to train I...   \n",
       "\n",
       "                      IMDB_Rating                      Meta_score  \\\n",
       "0  not currently in IMDB top 1000  not currently in IMDB top 1000   \n",
       "1  not currently in IMDB top 1000  not currently in IMDB top 1000   \n",
       "2  not currently in IMDB top 1000  not currently in IMDB top 1000   \n",
       "3  not currently in IMDB top 1000  not currently in IMDB top 1000   \n",
       "4  not currently in IMDB top 1000  not currently in IMDB top 1000   \n",
       "\n",
       "                      No_of_Votes                           Gross  \n",
       "0  not currently in IMDB top 1000  not currently in IMDB top 1000  \n",
       "1  not currently in IMDB top 1000  not currently in IMDB top 1000  \n",
       "2  not currently in IMDB top 1000  not currently in IMDB top 1000  \n",
       "3  not currently in IMDB top 1000  not currently in IMDB top 1000  \n",
       "4  not currently in IMDB top 1000  not currently in IMDB top 1000  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('select * from movies_db', con=engine).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a991f293",
   "metadata": {},
   "source": [
    "## Analysis: Querying the data with SQL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ed05e3e0",
   "metadata": {},
   "outputs": [],
   "source": [
    "# show everything on Netflilx & IMDB\n",
    "pd.read_sql_query(\"SELECT * FROM movies_db WHERE IMDB_Rating <> 'not currently in IMDB top 1000'\", con=engine, if_exists=\"append\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "461c4174",
   "metadata": {},
   "outputs": [],
   "source": [
    "# show avg rating of the films and tv shows that are on both Nteflix and IMDB\n",
    "pd.read_sql_query(\"SELECT AVG(IMDB_Rating) FROM IMDB_Rating <> 'not currently in IMDB top 1000' GROUP BY IMDB_Rating ORDER BY IMDB_Rating desc LIMIT 5\", con=engine, if_exists=\"append\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d49a1967",
   "metadata": {},
   "outputs": [],
   "source": [
    "# show avg gross films and tv shows that are on both Nteflix and IMDB top 1000\n",
    "pd.read_sql_query(\"SELECT AVG(Gross) FROM IMDB_Rating <> 'not currently in IMDB top 1000' GROUP BY Gross ORDER BY Gross desc LIMIT 5\", con=engine, if_exists=\"append\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d2c9841d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# show films & tv shows that are on Netflix but not IMDB\n",
    "pd.read_sql_query(\"SELECT * FROM movies_db WHERE IMDB_Rating = 'not currently in IMDB top 1000'\", con=engine, if_exists=\"append\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "27ca80ff",
   "metadata": {},
   "outputs": [],
   "source": [
    "# show avg rating of the films and tv shows that are on Netflix and not IMDB\n",
    "pd.read_sql_query(\"SELECT AVG(IMDB_Rating) FROM IMDB_Rating = 'not currently in IMDB top 1000' GROUP BY IMDB_Rating ORDER BY IMDB_Rating desc LIMIT 5\", con=engine, if_exists=\"append\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "686ce453",
   "metadata": {},
   "outputs": [],
   "source": [
    "# show avg gross films the films and tv shows that are on Netflix and not IMDB\n",
    "pd.read_sql_query(\"SELECT AVG(Gross) FROM IMDB_Rating = 'not currently in IMDB top 1000' GROUP BY Gross ORDER BY Gross desc LIMIT 5\", con=engine, if_exists=\"append\", index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [conda env:PythonData]",
   "language": "python",
   "name": "conda-env-PythonData-py"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}