Transform.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import dependencies\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Reading data in Pandas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### OMDb API data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Title</th>\n",
       "      <th>Year</th>\n",
       "      <th>Rated</th>\n",
       "      <th>Released</th>\n",
       "      <th>Runtime</th>\n",
       "      <th>Genre</th>\n",
       "      <th>Director</th>\n",
       "      <th>Writer</th>\n",
       "      <th>Actors</th>\n",
       "      <th>Plot</th>\n",
       "      <th>...</th>\n",
       "      <th>imdbRating</th>\n",
       "      <th>imdbVotes</th>\n",
       "      <th>imdbID</th>\n",
       "      <th>Type</th>\n",
       "      <th>DVD</th>\n",
       "      <th>BoxOffice</th>\n",
       "      <th>Production</th>\n",
       "      <th>Website</th>\n",
       "      <th>Response</th>\n",
       "      <th>Error</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>The Shawshank Redemption</td>\n",
       "      <td>1994.0</td>\n",
       "      <td>R</td>\n",
       "      <td>14 Oct 1994</td>\n",
       "      <td>142 min</td>\n",
       "      <td>Drama</td>\n",
       "      <td>Frank Darabont</td>\n",
       "      <td>Stephen King (short story \"Rita Hayworth and S...</td>\n",
       "      <td>Tim Robbins, Morgan Freeman, Bob Gunton, Willi...</td>\n",
       "      <td>Two imprisoned men bond over a number of years...</td>\n",
       "      <td>...</td>\n",
       "      <td>9.3</td>\n",
       "      <td>2,217,195</td>\n",
       "      <td>tt0111161</td>\n",
       "      <td>movie</td>\n",
       "      <td>27 Jan 1998</td>\n",
       "      <td>N/A</td>\n",
       "      <td>Columbia Pictures</td>\n",
       "      <td>N/A</td>\n",
       "      <td>True</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>The Godfather</td>\n",
       "      <td>1972.0</td>\n",
       "      <td>R</td>\n",
       "      <td>24 Mar 1972</td>\n",
       "      <td>175 min</td>\n",
       "      <td>Crime, Drama</td>\n",
       "      <td>Francis Ford Coppola</td>\n",
       "      <td>Mario Puzo (screenplay by), Francis Ford Coppo...</td>\n",
       "      <td>Marlon Brando, Al Pacino, James Caan, Richard ...</td>\n",
       "      <td>The aging patriarch of an organized crime dyna...</td>\n",
       "      <td>...</td>\n",
       "      <td>9.2</td>\n",
       "      <td>1,532,092</td>\n",
       "      <td>tt0068646</td>\n",
       "      <td>movie</td>\n",
       "      <td>09 Oct 2001</td>\n",
       "      <td>N/A</td>\n",
       "      <td>Paramount Pictures</td>\n",
       "      <td>N/A</td>\n",
       "      <td>True</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 26 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                      Title    Year Rated     Released  Runtime         Genre  \\\n",
       "0  The Shawshank Redemption  1994.0     R  14 Oct 1994  142 min         Drama   \n",
       "1             The Godfather  1972.0     R  24 Mar 1972  175 min  Crime, Drama   \n",
       "\n",
       "               Director                                             Writer  \\\n",
       "0        Frank Darabont  Stephen King (short story \"Rita Hayworth and S...   \n",
       "1  Francis Ford Coppola  Mario Puzo (screenplay by), Francis Ford Coppo...   \n",
       "\n",
       "                                              Actors  \\\n",
       "0  Tim Robbins, Morgan Freeman, Bob Gunton, Willi...   \n",
       "1  Marlon Brando, Al Pacino, James Caan, Richard ...   \n",
       "\n",
       "                                                Plot  ... imdbRating  \\\n",
       "0  Two imprisoned men bond over a number of years...  ...        9.3   \n",
       "1  The aging patriarch of an organized crime dyna...  ...        9.2   \n",
       "\n",
       "   imdbVotes     imdbID   Type          DVD BoxOffice          Production  \\\n",
       "0  2,217,195  tt0111161  movie  27 Jan 1998       N/A   Columbia Pictures   \n",
       "1  1,532,092  tt0068646  movie  09 Oct 2001       N/A  Paramount Pictures   \n",
       "\n",
       "  Website Response Error  \n",
       "0     N/A     True   NaN  \n",
       "1     N/A     True   NaN  \n",
       "\n",
       "[2 rows x 26 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Reading json file from raw data to pandas\n",
    "df_raw = pd.read_json('Output/OMDb_250.json')\n",
    "# df_raw.head()\n",
    "\n",
    "# Cleaning data by removing rows with no data available\n",
    "omdb_df = df_raw.loc[df_raw['Error'].isna()]\n",
    "\n",
    "omdb_df.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Utelly API data"
   ]
  },
  {
   "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>IMDb ID</th>\n",
       "      <th>Title</th>\n",
       "      <th>Streaming Service</th>\n",
       "      <th>Streaming URL</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>tt0111161</td>\n",
       "      <td>The Shawshank Redemption</td>\n",
       "      <td>Netflix</td>\n",
       "      <td>https://www.netflix.com/title/70005379</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>tt0111161</td>\n",
       "      <td>The Shawshank Redemption</td>\n",
       "      <td>Google Play</td>\n",
       "      <td>https://play.google.com/store/movies/details/T...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     IMDb ID                     Title Streaming Service  \\\n",
       "0  tt0111161  The Shawshank Redemption           Netflix   \n",
       "1  tt0111161  The Shawshank Redemption       Google Play   \n",
       "\n",
       "                                       Streaming URL  \n",
       "0             https://www.netflix.com/title/70005379  \n",
       "1  https://play.google.com/store/movies/details/T...  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Reading csv file from raw data to pandas\n",
    "df_raw_service = pd.read_csv('Output/streaming_df.csv')\n",
    "df_raw_service.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Cleaning data by removing rows with no data available\n",
    "df_raw_service = df_raw_service.dropna(how = 'any')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Rapid API data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>netflixid</th>\n",
       "      <th>title</th>\n",
       "      <th>image</th>\n",
       "      <th>synopsis</th>\n",
       "      <th>rating</th>\n",
       "      <th>type</th>\n",
       "      <th>released</th>\n",
       "      <th>runtime</th>\n",
       "      <th>largeimage</th>\n",
       "      <th>unogsdate</th>\n",
       "      <th>imdbid</th>\n",
       "      <th>download</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>80192064</td>\n",
       "      <td>Luciano Mellera: Infantiloide</td>\n",
       "      <td>http://occ-0-2851-38.1.nflxso.net/dnm/api/v6/e...</td>\n",
       "      <td>Argentina&amp;#39;s Luciano Mellera emphasizes the...</td>\n",
       "      <td>9.6</td>\n",
       "      <td>movie</td>\n",
       "      <td>2018</td>\n",
       "      <td>1h6m</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2018-07-07</td>\n",
       "      <td>tt7607400</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>81206389</td>\n",
       "      <td>Oththa Seruppu Size 7</td>\n",
       "      <td>https://occ-0-2851-38.1.nflxso.net/dnm/api/v6/...</td>\n",
       "      <td>Taken into custody, a murder suspect&amp;#39;s the...</td>\n",
       "      <td>9.4</td>\n",
       "      <td>movie</td>\n",
       "      <td>2019</td>\n",
       "      <td>1h43m</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2019-11-03</td>\n",
       "      <td>tt10370116</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0  netflixid                          title  \\\n",
       "0           0   80192064  Luciano Mellera: Infantiloide   \n",
       "1           1   81206389          Oththa Seruppu Size 7   \n",
       "\n",
       "                                               image  \\\n",
       "0  http://occ-0-2851-38.1.nflxso.net/dnm/api/v6/e...   \n",
       "1  https://occ-0-2851-38.1.nflxso.net/dnm/api/v6/...   \n",
       "\n",
       "                                            synopsis  rating   type  released  \\\n",
       "0  Argentina&#39;s Luciano Mellera emphasizes the...     9.6  movie      2018   \n",
       "1  Taken into custody, a murder suspect&#39;s the...     9.4  movie      2019   \n",
       "\n",
       "  runtime largeimage   unogsdate      imdbid  download  \n",
       "0    1h6m        NaN  2018-07-07   tt7607400       0.0  \n",
       "1   1h43m        NaN  2019-11-03  tt10370116       0.0  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Reading csv file from raw data to pandas\n",
    "df_raw_netflix = pd.read_csv('Output/netflix_all_pages.csv')\n",
    "df_raw_netflix.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Cleaning data by removing rows with no data available\n",
    "df_raw_netflix = df_raw_netflix.dropna(how = 'any')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Google Webscraping data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>Unnamed: 0</th>\n",
       "      <th>Title</th>\n",
       "      <th>Streaming On</th>\n",
       "      <th>Price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>The Shawshank Redemption</td>\n",
       "      <td>YouTube</td>\n",
       "      <td>3.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>The Shawshank Redemption</td>\n",
       "      <td>iTunes</td>\n",
       "      <td>3.99</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0                     Title Streaming On Price\n",
       "0           0  The Shawshank Redemption      YouTube  3.99\n",
       "1           1  The Shawshank Redemption       iTunes  3.99"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Reading csv file from raw data to pandas\n",
    "df_raw_google = pd.read_csv('Output/Complete_Google_Scraping.csv')\n",
    "df_raw_google.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Cleaning data by removing rows with no data available\n",
    "df_raw_google = df_raw_google.dropna(how = 'any')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Transform"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Creating top_imdb  - 1NF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>imdb_id</th>\n",
       "      <th>title</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>tt0111161</td>\n",
       "      <td>The Shawshank Redemption</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>tt0068646</td>\n",
       "      <td>The Godfather</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>tt0071562</td>\n",
       "      <td>The Godfather: Part II</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>tt0468569</td>\n",
       "      <td>The Dark Knight</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>tt0050083</td>\n",
       "      <td>12 Angry Men</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>245</td>\n",
       "      <td>tt0064115</td>\n",
       "      <td>Butch Cassidy and the Sundance Kid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>246</td>\n",
       "      <td>tt0103639</td>\n",
       "      <td>Aladdin</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>247</td>\n",
       "      <td>tt2338151</td>\n",
       "      <td>PK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>248</td>\n",
       "      <td>tt0094625</td>\n",
       "      <td>Akira</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>249</td>\n",
       "      <td>tt0050613</td>\n",
       "      <td>Throne of Blood</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>245 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       imdb_id                               title\n",
       "0    tt0111161            The Shawshank Redemption\n",
       "1    tt0068646                       The Godfather\n",
       "2    tt0071562              The Godfather: Part II\n",
       "3    tt0468569                     The Dark Knight\n",
       "4    tt0050083                        12 Angry Men\n",
       "..         ...                                 ...\n",
       "245  tt0064115  Butch Cassidy and the Sundance Kid\n",
       "246  tt0103639                             Aladdin\n",
       "247  tt2338151                                  PK\n",
       "248  tt0094625                               Akira\n",
       "249  tt0050613                     Throne of Blood\n",
       "\n",
       "[245 rows x 2 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Show IMDB ID, Title\n",
    "df_title = omdb_df[['imdbID', 'Title']].copy()\n",
    "\n",
    "# Renaming column headers\n",
    "df_title = df_title.rename(columns = {'imdbID': 'imdb_id',\n",
    "                                     'Title': 'title'})\n",
    "df_title"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Saving DataFrame to csv file\n",
    "df_title.to_csv('Output/csv_files/top_imdb.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Creating movie - 1NF"
   ]
  },
  {
   "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>imdb_id</th>\n",
       "      <th>imdb_rank</th>\n",
       "      <th>title</th>\n",
       "      <th>year</th>\n",
       "      <th>runtime</th>\n",
       "      <th>rated</th>\n",
       "      <th>imdb_rating</th>\n",
       "      <th>production</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>tt0111161</td>\n",
       "      <td>0</td>\n",
       "      <td>The Shawshank Redemption</td>\n",
       "      <td>1994</td>\n",
       "      <td>142</td>\n",
       "      <td>R</td>\n",
       "      <td>9.3</td>\n",
       "      <td>Columbia Pictures</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>tt0068646</td>\n",
       "      <td>1</td>\n",
       "      <td>The Godfather</td>\n",
       "      <td>1972</td>\n",
       "      <td>175</td>\n",
       "      <td>R</td>\n",
       "      <td>9.2</td>\n",
       "      <td>Paramount Pictures</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>tt0071562</td>\n",
       "      <td>2</td>\n",
       "      <td>The Godfather: Part II</td>\n",
       "      <td>1974</td>\n",
       "      <td>202</td>\n",
       "      <td>R</td>\n",
       "      <td>9.0</td>\n",
       "      <td>Paramount Pictures</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>tt0468569</td>\n",
       "      <td>3</td>\n",
       "      <td>The Dark Knight</td>\n",
       "      <td>2008</td>\n",
       "      <td>152</td>\n",
       "      <td>PG-13</td>\n",
       "      <td>9.0</td>\n",
       "      <td>Warner Bros. Pictures/Legendary</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>tt0050083</td>\n",
       "      <td>4</td>\n",
       "      <td>12 Angry Men</td>\n",
       "      <td>1957</td>\n",
       "      <td>96</td>\n",
       "      <td>Approved</td>\n",
       "      <td>8.9</td>\n",
       "      <td>Criterion Collection</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>240</td>\n",
       "      <td>tt0064115</td>\n",
       "      <td>245</td>\n",
       "      <td>Butch Cassidy and the Sundance Kid</td>\n",
       "      <td>1969</td>\n",
       "      <td>110</td>\n",
       "      <td>PG</td>\n",
       "      <td>8.0</td>\n",
       "      <td>20th Century Fox</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>241</td>\n",
       "      <td>tt0103639</td>\n",
       "      <td>246</td>\n",
       "      <td>Aladdin</td>\n",
       "      <td>1992</td>\n",
       "      <td>90</td>\n",
       "      <td>G</td>\n",
       "      <td>8.0</td>\n",
       "      <td>Walt Disney Pictures</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>242</td>\n",
       "      <td>tt2338151</td>\n",
       "      <td>247</td>\n",
       "      <td>PK</td>\n",
       "      <td>2014</td>\n",
       "      <td>153</td>\n",
       "      <td>Not Rated</td>\n",
       "      <td>8.1</td>\n",
       "      <td>UTV Communications</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>243</td>\n",
       "      <td>tt0094625</td>\n",
       "      <td>248</td>\n",
       "      <td>Akira</td>\n",
       "      <td>1988</td>\n",
       "      <td>124</td>\n",
       "      <td>R</td>\n",
       "      <td>8.0</td>\n",
       "      <td>Streamline Pictures</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>244</td>\n",
       "      <td>tt0050613</td>\n",
       "      <td>249</td>\n",
       "      <td>Throne of Blood</td>\n",
       "      <td>1957</td>\n",
       "      <td>110</td>\n",
       "      <td>Not Rated</td>\n",
       "      <td>8.1</td>\n",
       "      <td>Media Home Entertainment</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>245 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       imdb_id  imdb_rank                               title  year runtime  \\\n",
       "0    tt0111161          0            The Shawshank Redemption  1994     142   \n",
       "1    tt0068646          1                       The Godfather  1972     175   \n",
       "2    tt0071562          2              The Godfather: Part II  1974     202   \n",
       "3    tt0468569          3                     The Dark Knight  2008     152   \n",
       "4    tt0050083          4                        12 Angry Men  1957      96   \n",
       "..         ...        ...                                 ...   ...     ...   \n",
       "240  tt0064115        245  Butch Cassidy and the Sundance Kid  1969     110   \n",
       "241  tt0103639        246                             Aladdin  1992      90   \n",
       "242  tt2338151        247                                  PK  2014     153   \n",
       "243  tt0094625        248                               Akira  1988     124   \n",
       "244  tt0050613        249                     Throne of Blood  1957     110   \n",
       "\n",
       "         rated imdb_rating                       production  \n",
       "0            R         9.3                Columbia Pictures  \n",
       "1            R         9.2               Paramount Pictures  \n",
       "2            R         9.0               Paramount Pictures  \n",
       "3        PG-13         9.0  Warner Bros. Pictures/Legendary  \n",
       "4     Approved         8.9             Criterion Collection  \n",
       "..         ...         ...                              ...  \n",
       "240         PG         8.0                 20th Century Fox  \n",
       "241          G         8.0             Walt Disney Pictures  \n",
       "242  Not Rated         8.1               UTV Communications  \n",
       "243          R         8.0              Streamline Pictures  \n",
       "244  Not Rated         8.1         Media Home Entertainment  \n",
       "\n",
       "[245 rows x 8 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Show IMDB ID, Title, release year, runtime, movie rating, imdb rating, production\n",
    "df_movie = omdb_df[['imdbID', 'Title', 'Year', 'Runtime', 'Rated', 'imdbRating', 'Production']].copy()\n",
    "\n",
    "# Setting index as imdb_rank column\n",
    "df_movie.reset_index(level=0, inplace=True)\n",
    "\n",
    "# Tranforming float number from year.0 to year\n",
    "year_df = df_movie['Year'].astype(float).map(\"{:.0f}\".format)\n",
    "year_df = pd.DataFrame(year_df)\n",
    "\n",
    "# Tranforming runtime column by splitting string \n",
    "runtime_df = df_movie['Runtime'].str.split(\" \", expand=True)\n",
    "\n",
    "# Naming columns\n",
    "runtime_df.columns = ['number', 'string']\n",
    "\n",
    "# Dropping minutes\n",
    "runtime_df = runtime_df.drop(columns = 'string')\n",
    "\n",
    "# Renaming column headers\n",
    "df_movie = df_movie.rename(columns = {'index': 'imdb_rank',\n",
    "                                     'imdbID': 'imdb_id',\n",
    "                                     'Title': 'title',\n",
    "                                     'Year': 'year-old',\n",
    "                                     'Runtime': 'runtime-old',\n",
    "                                     'Rated': 'rated',\n",
    "                                     'imdbRating': 'imdb_rating',\n",
    "                                     'Production': 'production'\n",
    "                                    })\n",
    "\n",
    "# Combining dataframes by assign function\n",
    "df_combined_movie = df_movie.assign(year = year_df)\n",
    "df_combined_movie = df_combined_movie.assign(runtime = runtime_df)\n",
    "\n",
    "# Show IMDB ID, Title, release year, runtime, movie rating, imdb rating, production\n",
    "df_combined_movie = df_combined_movie [['imdb_id','imdb_rank', 'title', 'year', 'runtime', 'rated', 'imdb_rating', 'production']]\n",
    "\n",
    "# Cleaning data by removing rows with no data available\n",
    "df_combined_movie = df_combined_movie.dropna(how = 'any')\n",
    "df_combined_movie"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Saving DataFrame to csv file\n",
    "df_combined_movie.to_csv('Output/csv_files/movie.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Creating movie_actor  - 1NF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>imdb_id</th>\n",
       "      <th>actor</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>tt0111161</td>\n",
       "      <td>Tim Robbins</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>tt0111161</td>\n",
       "      <td>Morgan Freeman</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>tt0111161</td>\n",
       "      <td>Bob Gunton</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>tt0111161</td>\n",
       "      <td>William Sadler</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>tt0068646</td>\n",
       "      <td>Marlon Brando</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>972</td>\n",
       "      <td>tt0094625</td>\n",
       "      <td>Tesshô Genda</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>973</td>\n",
       "      <td>tt0050613</td>\n",
       "      <td>Toshirô Mifune</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>974</td>\n",
       "      <td>tt0050613</td>\n",
       "      <td>Isuzu Yamada</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>975</td>\n",
       "      <td>tt0050613</td>\n",
       "      <td>Takashi Shimura</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>976</td>\n",
       "      <td>tt0050613</td>\n",
       "      <td>Akira Kubo</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>977 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       imdb_id            actor\n",
       "0    tt0111161      Tim Robbins\n",
       "1    tt0111161   Morgan Freeman\n",
       "2    tt0111161       Bob Gunton\n",
       "3    tt0111161   William Sadler\n",
       "4    tt0068646    Marlon Brando\n",
       "..         ...              ...\n",
       "972  tt0094625     Tesshô Genda\n",
       "973  tt0050613   Toshirô Mifune\n",
       "974  tt0050613     Isuzu Yamada\n",
       "975  tt0050613  Takashi Shimura\n",
       "976  tt0050613       Akira Kubo\n",
       "\n",
       "[977 rows x 2 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Show IMDB ID, Actors\n",
    "df_actor = omdb_df[['imdbID', 'Actors']].copy()\n",
    "\n",
    "# Dropping an N/A values\n",
    "df_actor = df_actor.dropna(how = 'any')\n",
    "\n",
    "# Creating an actor list to set up a conversion into a DataFrame\n",
    "movie_actor_list = []\n",
    "\n",
    "# Iterate through actors dataframe to clean up data into a dictionary\n",
    "for index, row in df_actor.iterrows():\n",
    "\n",
    "    all_actors = df_actor.loc[index,'Actors']\n",
    "    actors_list = all_actors.split(', ')\n",
    "\n",
    "    for actor in actors_list:\n",
    "        \n",
    "        movie_actor_dict = {}\n",
    "        \n",
    "        movie_actor_dict['imdb_id'] = df_actor.loc[index,'imdbID']\n",
    "        movie_actor_dict['actor'] = actor\n",
    "        \n",
    "        movie_actor_list.append(movie_actor_dict)\n",
    "\n",
    "# Convert actor list into a Dataframe\n",
    "movie_actor_df = pd.DataFrame(movie_actor_list)\n",
    "\n",
    "# Cleaning data by removing rows with no data available\n",
    "movie_actor_df = movie_actor_df.dropna(how = 'any')\n",
    "movie_actor_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Saving DataFrame to csv file\n",
    "movie_actor_df.to_csv('Output/csv_files/movie_actor.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Creating movie_director  - 1NF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>imdb_id</th>\n",
       "      <th>director</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>tt0111161</td>\n",
       "      <td>Frank Darabont</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>tt0068646</td>\n",
       "      <td>Francis Ford Coppola</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>tt0071562</td>\n",
       "      <td>Francis Ford Coppola</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>tt0468569</td>\n",
       "      <td>Christopher Nolan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>tt0050083</td>\n",
       "      <td>Sidney Lumet</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>265</td>\n",
       "      <td>tt0103639</td>\n",
       "      <td>Ron Clements</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>266</td>\n",
       "      <td>tt0103639</td>\n",
       "      <td>John Musker</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>267</td>\n",
       "      <td>tt2338151</td>\n",
       "      <td>Rajkumar Hirani</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>268</td>\n",
       "      <td>tt0094625</td>\n",
       "      <td>Katsuhiro Ôtomo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>269</td>\n",
       "      <td>tt0050613</td>\n",
       "      <td>Akira Kurosawa</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>270 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       imdb_id              director\n",
       "0    tt0111161        Frank Darabont\n",
       "1    tt0068646  Francis Ford Coppola\n",
       "2    tt0071562  Francis Ford Coppola\n",
       "3    tt0468569     Christopher Nolan\n",
       "4    tt0050083          Sidney Lumet\n",
       "..         ...                   ...\n",
       "265  tt0103639          Ron Clements\n",
       "266  tt0103639           John Musker\n",
       "267  tt2338151       Rajkumar Hirani\n",
       "268  tt0094625       Katsuhiro Ôtomo\n",
       "269  tt0050613        Akira Kurosawa\n",
       "\n",
       "[270 rows x 2 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Show IMDB ID, Directors\n",
    "df_director = omdb_df[['imdbID', 'Director']].copy()\n",
    "\n",
    "# Dropping any N/A values\n",
    "df_director = df_director.dropna(how = 'any')\n",
    "\n",
    "# Creating a director list to set up a conversion into a DataFrame\n",
    "movie_director_list = []\n",
    "\n",
    "# Iterate through directors dataframe to clean up data into a dictionary\n",
    "for index, row in df_director.iterrows():\n",
    "\n",
    "    all_directors = df_director.loc[index,'Director']\n",
    "    directors_list = all_directors.split(', ')\n",
    "\n",
    "    for director in directors_list:\n",
    "        \n",
    "        movie_director_dict = {}\n",
    "        \n",
    "        movie_director_dict['imdb_id'] = df_director.loc[index,'imdbID']\n",
    "        movie_director_dict['director'] = director\n",
    "        \n",
    "        movie_director_list.append(movie_director_dict)\n",
    "\n",
    "# Convert director list into a Dataframe\n",
    "movie_director_df = pd.DataFrame(movie_director_list)\n",
    "\n",
    "# Cleaning data by removing rows with no data available\n",
    "movie_director_df = movie_director_df.dropna(how = 'any')\n",
    "movie_director_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Saving DataFrame to csv file\n",
    "movie_director_df.to_csv('Output/csv_files/movie_director.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Creating Streaming Service Utelly  - 1NF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Netflix', 'Google Play', 'Amazon Instant Video', 'iTunes',\n",
       "       'FandangoMoviesIVAUS', 'Amazon Prime Video', 'HBO',\n",
       "       'DisneyPlusIVAUS', 'Hulu', 'AtomTicketsIVAUS'], dtype=object)"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Show Streaming service\n",
    "df_service = df_raw_service['Streaming Service']\n",
    "df_service = pd.DataFrame(df_service)\n",
    "\n",
    "# Renaming column header\n",
    "df_service = df_service.rename(columns= {'Streaming Service': 'service_name'})\n",
    "\n",
    "# To see unique values in Streaming Service column of df_raw_service\n",
    "df_service.service_name.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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>service_id</th>\n",
       "      <th>service_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>ss_1</td>\n",
       "      <td>Amazon Instant Video</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>ss_2</td>\n",
       "      <td>Amazon Prime Video</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>ss_3</td>\n",
       "      <td>AtomTickets</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>ss_4</td>\n",
       "      <td>DisneyPlus</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>ss_5</td>\n",
       "      <td>FandangoMovies</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>ss_6</td>\n",
       "      <td>Google Play</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>ss_7</td>\n",
       "      <td>HBO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>ss_8</td>\n",
       "      <td>Hulu</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>ss_9</td>\n",
       "      <td>Netflix</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>ss_10</td>\n",
       "      <td>iTunes</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  service_id          service_name\n",
       "0       ss_1  Amazon Instant Video\n",
       "1       ss_2    Amazon Prime Video\n",
       "2       ss_3           AtomTickets\n",
       "3       ss_4            DisneyPlus\n",
       "4       ss_5        FandangoMovies\n",
       "5       ss_6           Google Play\n",
       "6       ss_7                   HBO\n",
       "7       ss_8                  Hulu\n",
       "8       ss_9               Netflix\n",
       "9      ss_10                iTunes"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Spliting service name column by splitting string\n",
    "df_service = df_service['service_name'].str.split(\"IVAUS\", expand=True)\n",
    "\n",
    "# Renaming columns\n",
    "df_service.columns = ['service_name', 'info']\n",
    "\n",
    "# Group Streaming services\n",
    "grp_service = df_service.groupby('service_name')\n",
    "df_service = pd.DataFrame(grp_service)\n",
    "\n",
    "# Renaming columns\n",
    "df_service.columns = ['service_name', 'info']\n",
    "\n",
    "# Dropping info column\n",
    "df_service = df_service.drop(columns='info')\n",
    "\n",
    "# Declaring a list for unqiue service id column\n",
    "service_id = ['ss_1', 'ss_2', 'ss_3', 'ss_4', 'ss_5',\n",
    "             'ss_6', 'ss_7', 'ss_8', 'ss_9', 'ss_10']\n",
    "\n",
    "# Creating a service id column and adding the service_id list\n",
    "df_service['service_id']= service_id\n",
    "\n",
    "# Show service_id and service_name\n",
    "df_service = df_service[['service_id', 'service_name']]\n",
    "\n",
    "# Cleaning data by removing rows with no data available\n",
    "df_service = df_service.dropna(how = 'any')\n",
    "df_service"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Saving DataFrame to csv file\n",
    "df_service.to_csv('Output/csv_files/streaming_service.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Creating Streaming Service Google  - 1NF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['YouTube', 'iTunes', 'Google Play Movies & TV', 'Vudu',\n",
       "       'Amazon Prime Video', 'Philo', 'Hulu', 'Netflix', 'HBO Now',\n",
       "       'Sling TV', 'Tubi', 'Disney+', 'Crackle', 'fuboTV', 'Showtime',\n",
       "       'Cinemax', 'Starz'], dtype=object)"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Show Streaming service\n",
    "df_service_google = df_raw_google['Streaming On']\n",
    "df_service_google = pd.DataFrame(df_service_google)\n",
    "\n",
    "# Renaming column header\n",
    "df_service_google = df_service_google.rename(columns= {'Streaming On': 'service_name'})\n",
    "\n",
    "# To see unique values in Streaming Service column of df_raw_service\n",
    "df_service_google.service_name.unique()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "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>service_id</th>\n",
       "      <th>service_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>gg_1</td>\n",
       "      <td>Amazon Prime Video</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>gg_2</td>\n",
       "      <td>Cinemax</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>gg_3</td>\n",
       "      <td>Crackle</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>gg_4</td>\n",
       "      <td>Disney+</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>gg_5</td>\n",
       "      <td>Google Play Movies &amp; TV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>gg_6</td>\n",
       "      <td>HBO Now</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>gg_7</td>\n",
       "      <td>Hulu</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>gg_8</td>\n",
       "      <td>Netflix</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>gg_9</td>\n",
       "      <td>Philo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>gg_10</td>\n",
       "      <td>Showtime</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>10</td>\n",
       "      <td>gg_11</td>\n",
       "      <td>Sling TV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>11</td>\n",
       "      <td>gg_12</td>\n",
       "      <td>Starz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>12</td>\n",
       "      <td>gg_13</td>\n",
       "      <td>Tubi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>13</td>\n",
       "      <td>gg_14</td>\n",
       "      <td>Vudu</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>14</td>\n",
       "      <td>gg_15</td>\n",
       "      <td>YouTube</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>15</td>\n",
       "      <td>gg_16</td>\n",
       "      <td>fuboTV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>16</td>\n",
       "      <td>gg_17</td>\n",
       "      <td>iTunes</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   service_id             service_name\n",
       "0        gg_1       Amazon Prime Video\n",
       "1        gg_2                  Cinemax\n",
       "2        gg_3                  Crackle\n",
       "3        gg_4                  Disney+\n",
       "4        gg_5  Google Play Movies & TV\n",
       "5        gg_6                  HBO Now\n",
       "6        gg_7                     Hulu\n",
       "7        gg_8                  Netflix\n",
       "8        gg_9                    Philo\n",
       "9       gg_10                 Showtime\n",
       "10      gg_11                 Sling TV\n",
       "11      gg_12                    Starz\n",
       "12      gg_13                     Tubi\n",
       "13      gg_14                     Vudu\n",
       "14      gg_15                  YouTube\n",
       "15      gg_16                   fuboTV\n",
       "16      gg_17                   iTunes"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Group Streaming services\n",
    "grp_service = df_service_google.groupby('service_name')\n",
    "df_service_google= pd.DataFrame(grp_service)\n",
    "df_service_google\n",
    "\n",
    "# Renaming columns\n",
    "df_service_google.columns = ['service_name', 'info']\n",
    "\n",
    "# Dropping info column\n",
    "df_service_google = df_service_google.drop(columns='info')\n",
    "\n",
    "df_service_google \n",
    "\n",
    "# Declaring a list for unqiue service id column\n",
    "service_google_id = ['gg_1', 'gg_2', 'gg_3', 'gg_4', 'gg_5',\n",
    "             'gg_6', 'gg_7', 'gg_8', 'gg_9', 'gg_10', 'gg_11',\n",
    "             'gg_12', 'gg_13', 'gg_14', 'gg_15', 'gg_16', 'gg_17']\n",
    "\n",
    "# Creating a service id column and adding the service_id list\n",
    "df_service_google['service_id']= service_google_id\n",
    "\n",
    "# Show service_id and service_name\n",
    "df_service_google = df_service_google[['service_id', 'service_name']]\n",
    "df_service_google"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Saving DataFrame to csv file\n",
    "df_service_google.to_csv('Output/csv_files/google_streaming_service.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Creating streaming_service_utelly - 1NF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>imdb_id</th>\n",
       "      <th>service_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>tt0111161</td>\n",
       "      <td>ss_9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>tt0111161</td>\n",
       "      <td>ss_6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>tt0111161</td>\n",
       "      <td>ss_1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>tt0111161</td>\n",
       "      <td>ss_10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>tt0068646</td>\n",
       "      <td>ss_6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>694</td>\n",
       "      <td>tt0103639</td>\n",
       "      <td>ss_6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>695</td>\n",
       "      <td>tt0103639</td>\n",
       "      <td>ss_4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>696</td>\n",
       "      <td>tt2338151</td>\n",
       "      <td>ss_9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>697</td>\n",
       "      <td>tt0094625</td>\n",
       "      <td>ss_8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>698</td>\n",
       "      <td>tt0050613</td>\n",
       "      <td>ss_10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>699 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       imdb_id service_id\n",
       "0    tt0111161       ss_9\n",
       "1    tt0111161       ss_6\n",
       "2    tt0111161       ss_1\n",
       "3    tt0111161      ss_10\n",
       "4    tt0068646       ss_6\n",
       "..         ...        ...\n",
       "694  tt0103639       ss_6\n",
       "695  tt0103639       ss_4\n",
       "696  tt2338151       ss_9\n",
       "697  tt0094625       ss_8\n",
       "698  tt0050613      ss_10\n",
       "\n",
       "[699 rows x 2 columns]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Show IMDB id, Title, Streaming service\n",
    "df_streaming = df_raw_service[['IMDb ID', 'Title', 'Streaming Service']].copy()\n",
    "\n",
    "# Tranforming streaming service column by splitting string \n",
    "service_df = df_streaming['Streaming Service'].str.split(\"IVAUS\", expand=True)\n",
    "\n",
    "# Naming columns\n",
    "service_df.columns = ['number', 'string']\n",
    "\n",
    "# Dropping string column\n",
    "service_df = service_df.drop(columns = 'string')\n",
    "\n",
    "# Renaming column headers\n",
    "df_streaming = df_streaming.rename(columns = {'IMDb ID': 'imdb_id',\n",
    "                                     'Title': 'title',\n",
    "                                     'Streaming Service': 'service_name'\n",
    "                                    })\n",
    "\n",
    "# Combining dataframes by assign function\n",
    "df_combined_streaming = df_streaming.assign(service_name = service_df)\n",
    "\n",
    "# Creating a blank series for the service_id column\n",
    "service_id = pd.Series([])\n",
    "\n",
    "# Running for loop and assigning values to service_id series\n",
    "for i in range(len(df_combined_streaming)):\n",
    "    if df_combined_streaming['service_name'][i] == 'Amazon Instant Video':\n",
    "        service_id[i] = \"ss_1\"\n",
    "    elif df_combined_streaming['service_name'][i] == 'Amazon Prime Video':\n",
    "        service_id[i] = \"ss_2\"\n",
    "    elif df_combined_streaming['service_name'][i] == 'AtomTickets':\n",
    "        service_id[i] = \"ss_3\"  \n",
    "    elif df_combined_streaming['service_name'][i] == 'DisneyPlus':\n",
    "        service_id[i] = \"ss_4\" \n",
    "    elif df_combined_streaming['service_name'][i] == 'FandangoMovies':\n",
    "        service_id[i] = \"ss_5\"      \n",
    "    elif df_combined_streaming['service_name'][i] == 'Google Play':\n",
    "        service_id[i] = \"ss_6\"  \n",
    "    elif df_combined_streaming['service_name'][i] == 'HBO':\n",
    "        service_id[i] = \"ss_7\" \n",
    "    elif df_combined_streaming['service_name'][i] == 'Hulu':\n",
    "        service_id[i] = \"ss_8\"       \n",
    "    elif df_combined_streaming['service_name'][i] == 'Netflix':\n",
    "        service_id[i] = \"ss_9\" \n",
    "    elif df_combined_streaming['service_name'][i] == 'iTunes':\n",
    "        service_id[i] = \"ss_10\"    \n",
    "        \n",
    "# Inserting new column from the service_id values\n",
    "df_combined_streaming.insert(3, \"service_id\", service_id)\n",
    "\n",
    "# Show service id and IMDB id\n",
    "df_streaming_id = df_combined_streaming [['imdb_id','service_id']]\n",
    "df_streaming_id\n",
    "\n",
    "# Cleaning data by removing rows with no data available\n",
    "df_service_utelly = df_streaming_id.dropna(how = 'any')\n",
    "df_service_utelly"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Saving DataFrame to csv file\n",
    "df_streaming_id.to_csv('Output/csv_files/utelly_movie_streaming.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Creating streaming_service_google - 1NF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['YouTube', 'iTunes', 'Google Play Movies & TV', 'Vudu',\n",
       "       'Amazon Prime Video', 'Philo', 'Hulu', 'Netflix', 'HBO Now',\n",
       "       'Sling TV', 'Tubi', 'Disney+', 'Crackle', 'fuboTV', 'Showtime',\n",
       "       'Cinemax', 'Starz'], dtype=object)"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Show Title, Streaming On, Price\n",
    "df_service_google = df_raw_google[['Title', 'Streaming On', 'Price' ]].copy()\n",
    "\n",
    "# Renaming column headers\n",
    "df_service_google= df_service_google.rename(columns = {'Title': 'title',\n",
    "                                     'Streaming On': 'service_name',\n",
    "                                     'Price': 'price'\n",
    "                                    })\n",
    "\n",
    "# To see unique values in Streaming Service column of df_raw_service\n",
    "df_service_google.service_name.unique()"
   ]
  },
  {
   "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>title</th>\n",
       "      <th>google_service_id</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>The Shawshank Redemption</td>\n",
       "      <td>gg_17</td>\n",
       "      <td>3.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>The Shawshank Redemption</td>\n",
       "      <td>gg_9</td>\n",
       "      <td>3.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>The Shawshank Redemption</td>\n",
       "      <td>gg_6</td>\n",
       "      <td>3.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>The Shawshank Redemption</td>\n",
       "      <td>gg_16</td>\n",
       "      <td>3.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>The Shawshank Redemption</td>\n",
       "      <td>gg_1</td>\n",
       "      <td>3.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1171</td>\n",
       "      <td>Akira</td>\n",
       "      <td>gg_16</td>\n",
       "      <td>2.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1172</td>\n",
       "      <td>Akira</td>\n",
       "      <td>gg_9</td>\n",
       "      <td>5.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1173</td>\n",
       "      <td>Akira</td>\n",
       "      <td>gg_7</td>\n",
       "      <td>Subscription</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1174</td>\n",
       "      <td>Throne Of Blood</td>\n",
       "      <td>gg_9</td>\n",
       "      <td>3.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1175</td>\n",
       "      <td>Throne Of Blood</td>\n",
       "      <td>gg_1</td>\n",
       "      <td>3.99</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1176 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                         title google_service_id         price\n",
       "0     The Shawshank Redemption             gg_17          3.99\n",
       "1     The Shawshank Redemption              gg_9          3.99\n",
       "2     The Shawshank Redemption              gg_6          3.99\n",
       "3     The Shawshank Redemption             gg_16          3.99\n",
       "4     The Shawshank Redemption              gg_1          3.99\n",
       "...                        ...               ...           ...\n",
       "1171                     Akira             gg_16          2.99\n",
       "1172                     Akira              gg_9          5.99\n",
       "1173                     Akira              gg_7  Subscription\n",
       "1174           Throne Of Blood              gg_9          3.99\n",
       "1175           Throne Of Blood              gg_1          3.99\n",
       "\n",
       "[1176 rows x 3 columns]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Creating a blank series for the service_id column\n",
    "google_service_id = pd.Series([])\n",
    "\n",
    "# Running for loop and assigning values to service_id series\n",
    "for i in range(len(df_service_google)):\n",
    "    if df_service_google['service_name'][i] == 'Amazon Prime Video':\n",
    "        google_service_id[i] = \"gg_1\"\n",
    "    elif df_service_google['service_name'][i] == 'Cinemax':\n",
    "        google_service_id[i] = \"gg_2\"\n",
    "    elif df_service_google['service_name'][i] == 'Crackle':\n",
    "        google_service_id[i] = \"gg_3\"  \n",
    "    elif df_service_google['service_name'][i] == 'Disney+':\n",
    "        google_service_id[i] = \"gg_4\" \n",
    "    elif df_service_google['service_name'][i] == 'fuboTV':\n",
    "        google_service_id[i] = \"gg_5\"      \n",
    "    elif df_service_google['service_name'][i] == 'Google Play Movies & TV':\n",
    "        google_service_id[i] = \"gg_6\"  \n",
    "    elif df_service_google['service_name'][i] == 'Hulu':\n",
    "        google_service_id[i] = \"gg_7\" \n",
    "    elif df_service_google['service_name'][i] == 'HBO Now':\n",
    "        google_service_id[i] = \"gg_8\"       \n",
    "    elif df_service_google['service_name'][i] == 'iTunes':\n",
    "        google_service_id[i] = \"gg_9\" \n",
    "    elif df_service_google['service_name'][i] == 'Netflix':\n",
    "        google_service_id[i] = \"gg_10\"    \n",
    "    elif df_service_google['service_name'][i] == 'Philo':\n",
    "        google_service_id[i] = \"gg_11\"    \n",
    "    elif df_service_google['service_name'][i] == 'Showtime':\n",
    "        google_service_id[i] = \"gg_12\"   \n",
    "    elif df_service_google['service_name'][i] == 'Sling TV':\n",
    "        google_service_id[i] = \"gg_13\"   \n",
    "    elif df_service_google['service_name'][i] == 'Starz':\n",
    "        google_service_id[i] = \"gg_14\"\n",
    "    elif df_service_google['service_name'][i] == 'Tubi':\n",
    "        google_service_id[i] = \"gg_15\"\n",
    "    elif df_service_google['service_name'][i] == 'Vudu':\n",
    "        google_service_id[i] = \"gg_16\"\n",
    "    elif df_service_google['service_name'][i] == 'YouTube':\n",
    "        google_service_id[i] = \"gg_17\"\n",
    "        \n",
    "# Inserting new column from the service_id values\n",
    "df_service_google.insert(3, \"google_service_id\", google_service_id)\n",
    "\n",
    "# Show title, google service id, service name, price\n",
    "df_service_google = df_service_google [['title','google_service_id', 'price']]\n",
    "\n",
    "# Cleaning data by removing rows with no data available\n",
    "df_service_google = df_service_google.dropna(how = 'any')\n",
    "df_service_google"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Saving DataFrame to csv file\n",
    "df_service_google.to_csv('Output/csv_files/google_movie_streaming.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Creating netflix_movie - 1NF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>netflix_id</th>\n",
       "      <th>imdb_id</th>\n",
       "      <th>title</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>553500</td>\n",
       "      <td>tt0060196</td>\n",
       "      <td>The Good, the Bad and the Ugly</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>70131314</td>\n",
       "      <td>tt1375666</td>\n",
       "      <td>Inception</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>20557937</td>\n",
       "      <td>tt0133093</td>\n",
       "      <td>The Matrix</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>17</td>\n",
       "      <td>60010110</td>\n",
       "      <td>tt0088763</td>\n",
       "      <td>Back to the Future</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>18</td>\n",
       "      <td>60031884</td>\n",
       "      <td>tt0064116</td>\n",
       "      <td>Once Upon a Time in the West</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>863</td>\n",
       "      <td>70114021</td>\n",
       "      <td>tt1019452</td>\n",
       "      <td>A Serious Man</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>864</td>\n",
       "      <td>80017021</td>\n",
       "      <td>tt2937898</td>\n",
       "      <td>A Most Violent Year</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>866</td>\n",
       "      <td>70130432</td>\n",
       "      <td>tt0081494</td>\n",
       "      <td>Return To The 36th Chamber</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>867</td>\n",
       "      <td>80025390</td>\n",
       "      <td>tt3007512</td>\n",
       "      <td>The Water Diviner</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>868</td>\n",
       "      <td>80046694</td>\n",
       "      <td>tt4178092</td>\n",
       "      <td>The Gift</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>237 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     netflix_id    imdb_id                           title\n",
       "2        553500  tt0060196  The Good, the Bad and the Ugly\n",
       "3      70131314  tt1375666                       Inception\n",
       "8      20557937  tt0133093                      The Matrix\n",
       "17     60010110  tt0088763              Back to the Future\n",
       "18     60031884  tt0064116    Once Upon a Time in the West\n",
       "..          ...        ...                             ...\n",
       "863    70114021  tt1019452                   A Serious Man\n",
       "864    80017021  tt2937898             A Most Violent Year\n",
       "866    70130432  tt0081494      Return To The 36th Chamber\n",
       "867    80025390  tt3007512               The Water Diviner\n",
       "868    80046694  tt4178092                        The Gift\n",
       "\n",
       "[237 rows x 3 columns]"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Show Netflix ID, IMDB ID, Title\n",
    "df_netflix = df_raw_netflix[['netflixid', 'imdbid', 'title' ]].copy()\n",
    "\n",
    "# Renaming column headers\n",
    "df_netflix = df_netflix.rename(columns = {'netflixid': 'netflix_id',\n",
    "                                     'imdbid': 'imdb_id',\n",
    "                                    })\n",
    "\n",
    "# Cleaning data by removing rows with no data available\n",
    "df_netflix = df_netflix.dropna(how = 'any')\n",
    "df_netflix"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Saving DataFrame to csv file\n",
    "df_netflix.to_csv('Output/csv_files/netflix_movie.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}