CHG_SQL_Table_Creation.ipynb

{
 "metadata": {
  "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.6-final"
  },
  "orig_nbformat": 2,
  "kernelspec": {
   "name": "python_defaultSpec_1600562278429",
   "display_name": "Python 3.7.6 64-bit ('base': conda)"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2,
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Bring in my Dependencies\n",
    "\n",
    "import pandas as pd\n",
    "import os, csv\n",
    "from sqlalchemy import create_engine\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Bring in my csv that I made in the 'CHG_Netflix_SAG_merged_data.' notebook, and create a dataframe\n",
    "# from it. Additionally, we drop the Unnamed: 0 column that gets created in the process as it is\n",
    "# unneeded.\n",
    "\n",
    "data = os.path.join('Resources','cleaned_merged','cleaned_netflix_sag_data.csv')\n",
    "data1 = pd.read_csv(data)\n",
    "data_df = pd.DataFrame(data1).drop(columns='Unnamed: 0')\n",
    "data_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Below, I create a dataframe whose name and data match the SQL table that I have set up in pgadmin4\n",
    "\n",
    "# The actors table needed to have the id and full_name variables. I dropped the duplicate values as I need each\n",
    "# entry to be a unique identifier. I then set the index to 'id' which will be the primary key for the SQL table.\n",
    "# Copy() is used to ensure I am not slicing the original data\n",
    "\n",
    "actors = data_df[['id','full_name']].set_index('id').drop_duplicates().copy()\n",
    "\n",
    "# For the titles dataframe, I followed the exact same method used to create my actors dataframe only using different\n",
    "# variables \n",
    "\n",
    "titles = data_df[['show_id','title','type','release_year','description']].set_index('show_id').drop_duplicates().copy()\n",
    "\n",
    "# And again for my act_ttl dataframe\n",
    "\n",
    "act_ttl = data_df[['id','show_id']].set_index('id').drop_duplicates().copy()\n",
    "\n",
    "# this table was a bit tricker, as I had to create a new unique id for the directors_names variable\n",
    "# In order to accomplish this, I created the column director_id and set it to a number starting\n",
    "# at 0 for each director name in the list. Once I set my index to director_id, I will have my primary key\n",
    "#\n",
    "# *NOTE* it is important to notice that I have not set the index for this table yet -\n",
    "# that is because we need to reference this series later to build junction tables\n",
    "# if I set the index now - I would have to reference it as an index, and I was struggling to \n",
    "# figure that one out. *NOTE*\n",
    "\n",
    "directors = data_df[['director']].rename(columns={'director':'directors_names'}).drop_duplicates().copy()\n",
    "directors['director_id'] = range(len(directors['directors_names']))\n",
    "\n",
    "\n",
    "# Let's kick it up a notch. Now I need to create my dir_ttl table, and it will leverage data from \n",
    "# two of my created dataframes rather than the source data. To start, I took the show_id from the source\n",
    "# data, drop_dups, copy - now I create a new series in the dir_ttl dataframe called director_id that is taken \n",
    "# from the directors table. I then drop any NULL values. Now, if I did not do the next line of code, my\n",
    "# director_id series would all by float values, and I need them to be integers as they are primary keys.\n",
    "# \n",
    "# To address this, I write a linear expression that converts every value of that series to an integer - then I set \n",
    "# the index to show_id, as it is the first primary key in the composite key\n",
    "#\n",
    "# *hint* this will be a junction table, i.e. both series are the primary keys of two other tables which make up this\n",
    "# table's COMPOSITE KEY *hint*\n",
    "\n",
    "dir_ttl = data_df[['show_id']].drop_duplicates().copy()\n",
    "dir_ttl['director_id'] = directors['director_id']\n",
    "dir_ttl = dir_ttl.dropna()\n",
    "dir_ttl['director_id'] = [int(value) for value in directors['director_id']]\n",
    "dir_ttl = dir_ttl.set_index('show_id')\n",
    "\n",
    "# This process was relatively similar to the directors dataframe creation - I had to create a unique key by\n",
    "# referencing a range of numbers that was equally as long as the dataframe so that it could be appended. Also\n",
    "# like the directors table, I have held off on setting the index for this dataframe, as the series\n",
    "# will need to be referenced later for junction table creation.\n",
    "\n",
    "awards = data_df[['category','won']].copy()\n",
    "awards['award_id'] = range(len(awards['won']))\n",
    "\n",
    "# actors and awards junction table - this table carries the primary keys from the mentioned tables as its\n",
    "# COMPOSITE key. \n",
    "\n",
    "act_award = data_df[['id']].copy()\n",
    "act_award['award_id'] = awards['award_id']\n",
    "act_award = act_award.set_index('id')\n",
    "\n",
    "# directors and awards junction table - this table carries the primary keys from the mentioned tables as its\n",
    "# COMPOSITE key. \n",
    "\n",
    "dir_award = directors[['director_id']].copy()\n",
    "dir_award['award_id'] = awards['award_id']\n",
    "dir_award = dir_award.set_index('director_id')\n",
    "\n",
    "# With all of my junction tables created, I can go ahead and set the index for the awards and \n",
    "# directors dataframes.\n",
    "\n",
    "awards = awards.set_index('award_id')\n",
    "directors = directors.set_index('director_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# my data had a bunch of strings that were used for the various award categories\n",
    "# so I wrote this code in order to have a cleaner result in my queries by combining\n",
    "# results that were similiar in nature \n",
    "\n",
    "awards['category'] = awards['category'].replace(\n",
    "    {'MALE SUPPORT':'MALE SUPPORTING ROLE',\n",
    "    ' MALE ACTOR IN A SUPPORTING ROLE':'MALE SUPPORTING ROLE',\n",
    "    'FEMALE SUPPORT':'FEMALE SUPPORTING ROLE',\n",
    "    ' FEMALE ACTOR IN A SUPPORTING ROLE':'FEMALE SUPPORTING ROLE',\n",
    "    'FEMALE LEAD':'FEMALE LEAD ROLE',\n",
    "    'FEMALE ACTOR IN A LEADING ROLE':'FEMALE LEAD ROLE',\n",
    "    ' FEMALE ACTOR IN A LEADING ROLE':'FEMALE LEAD ROLE',\n",
    "    'FEMALE LEAD IN A MOTION PICTURE':'FEMALE LEAD ROLE',\n",
    "    'MALE LEAD':'MALE LEAD ROLE',\n",
    "    ' MALE ACTOR IN A LEADING ROLE':'MALE LEAD ROLE',\n",
    "    'MALE ACTOR IN A LEADING ROLE':'MALE LEAD ROLE'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# establish connection string, and create engine\n",
    "\n",
    "conn = \"postgres:postgres@localhost:5432/netflix_sag_db\"\n",
    "engine = create_engine(f'postgresql://{conn}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# inspect my database's tables to verify my connection is functioning\n",
    "\n",
    "engine.table_names()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# with my connection established - I can now insert my dataframes into my various sql database tables\n",
    "# it is important to run the lines in the below order so that all of the tables connect properly\n",
    "\n",
    "actors.to_sql(name='actors', con=engine, if_exists='append', index=True)\n",
    "titles.to_sql(name='titles', con=engine, if_exists='append', index=True)\n",
    "act_ttl.to_sql(name='act_ttl', con=engine, if_exists='append', index=True)\n",
    "directors.to_sql(name='directors', con=engine, if_exists='append', index=True)\n",
    "dir_ttl.to_sql(name='dir_ttl', con=engine, if_exists='append', index=True)\n",
    "awards.to_sql(name='awards', con=engine, if_exists='append', index=True)\n",
    "act_award.to_sql(name='act_award', con=engine, if_exists='append', index=True)\n",
    "dir_award.to_sql(name='dir_award', con=engine, if_exists='append', index=True)"
   ]
  }
 ]
}