data_cleaning_sql.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Cleaning and preparing tables in SQL Nbadb\n",
    "\n",
    "**Mainly below actions will be followed for both columns:**\n",
    "* Check table data from information schema\n",
    "* Select correct data type for columns\n",
    "* Deal with missing data\n",
    "* Remove blank / duplicate columns\n",
    "* Create auto id column\n",
    "* Establish a foreign key and relationship between tables\n",
    "\n",
    "* Glossary - https://www.basketball-reference.com/about/glossary.html"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Load Sql Extension, Connect to DB and select schema"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "postgresql://postgres:postgres@localhost/nbadb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/nbadb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SET search_path TO public;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Inspect Schema"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/nbadb\n",
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>table_catalog</th>\n",
       "        <th>table_schema</th>\n",
       "        <th>table_name</th>\n",
       "        <th>table_type</th>\n",
       "        <th>self_referencing_column_name</th>\n",
       "        <th>reference_generation</th>\n",
       "        <th>user_defined_type_catalog</th>\n",
       "        <th>user_defined_type_schema</th>\n",
       "        <th>user_defined_type_name</th>\n",
       "        <th>is_insertable_into</th>\n",
       "        <th>is_typed</th>\n",
       "        <th>commit_action</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>nbadb</td>\n",
       "        <td>public</td>\n",
       "        <td>players</td>\n",
       "        <td>BASE TABLE</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>YES</td>\n",
       "        <td>NO</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>nbadb</td>\n",
       "        <td>public</td>\n",
       "        <td>seasons</td>\n",
       "        <td>BASE TABLE</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>YES</td>\n",
       "        <td>NO</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('nbadb', 'public', 'players', 'BASE TABLE', None, None, None, None, None, 'YES', 'NO', None),\n",
       " ('nbadb', 'public', 'seasons', 'BASE TABLE', None, None, None, None, None, 'YES', 'NO', None)]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * FROM INFORMATION_SCHEMA.TABLES\n",
    "WHERE table_schema = 'public';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### PLAYER TABLE"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Check Col names/dtypes and display the table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/nbadb\n",
      "8 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>column_name</th>\n",
       "        <th>data_type</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>birth_date</td>\n",
       "        <td>text</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>college</td>\n",
       "        <td>text</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>height</td>\n",
       "        <td>text</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>name</td>\n",
       "        <td>text</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>position</td>\n",
       "        <td>text</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>weight</td>\n",
       "        <td>double precision</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>year_end</td>\n",
       "        <td>bigint</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>year_start</td>\n",
       "        <td>bigint</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('birth_date', 'text'),\n",
       " ('college', 'text'),\n",
       " ('height', 'text'),\n",
       " ('name', 'text'),\n",
       " ('position', 'text'),\n",
       " ('weight', 'double precision'),\n",
       " ('year_end', 'bigint'),\n",
       " ('year_start', 'bigint')]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT column_name, data_type\n",
    "FROM INFORMATION_SCHEMA.COLUMNS\n",
    "WHERE table_schema = 'public'\n",
    "AND table_name = 'players'\n",
    "ORDER BY column_name;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/nbadb\n",
      "10 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>year_start</th>\n",
       "        <th>year_end</th>\n",
       "        <th>position</th>\n",
       "        <th>height</th>\n",
       "        <th>weight</th>\n",
       "        <th>birth_date</th>\n",
       "        <th>college</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Alaa Abdelnaby</td>\n",
       "        <td>1991</td>\n",
       "        <td>1995</td>\n",
       "        <td>F-C</td>\n",
       "        <td>6-10</td>\n",
       "        <td>240.0</td>\n",
       "        <td>June 24, 1968</td>\n",
       "        <td>Duke University</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Zaid Abdul-Aziz</td>\n",
       "        <td>1969</td>\n",
       "        <td>1978</td>\n",
       "        <td>C-F</td>\n",
       "        <td>6-9</td>\n",
       "        <td>235.0</td>\n",
       "        <td>April 7, 1946</td>\n",
       "        <td>Iowa State University</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Kareem Abdul-Jabbar</td>\n",
       "        <td>1970</td>\n",
       "        <td>1989</td>\n",
       "        <td>C</td>\n",
       "        <td>7-2</td>\n",
       "        <td>225.0</td>\n",
       "        <td>April 16, 1947</td>\n",
       "        <td>University of California, Los Angeles</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mahmoud Abdul-Rauf</td>\n",
       "        <td>1991</td>\n",
       "        <td>2001</td>\n",
       "        <td>G</td>\n",
       "        <td>6-1</td>\n",
       "        <td>162.0</td>\n",
       "        <td>March 9, 1969</td>\n",
       "        <td>Louisiana State University</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Tariq Abdul-Wahad</td>\n",
       "        <td>1998</td>\n",
       "        <td>2003</td>\n",
       "        <td>F</td>\n",
       "        <td>6-6</td>\n",
       "        <td>223.0</td>\n",
       "        <td>November 3, 1974</td>\n",
       "        <td>San Jose State University</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Shareef Abdur-Rahim</td>\n",
       "        <td>1997</td>\n",
       "        <td>2008</td>\n",
       "        <td>F</td>\n",
       "        <td>6-9</td>\n",
       "        <td>225.0</td>\n",
       "        <td>December 11, 1976</td>\n",
       "        <td>University of California</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Tom Abernethy</td>\n",
       "        <td>1977</td>\n",
       "        <td>1981</td>\n",
       "        <td>F</td>\n",
       "        <td>6-7</td>\n",
       "        <td>220.0</td>\n",
       "        <td>May 6, 1954</td>\n",
       "        <td>Indiana University</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Forest Able</td>\n",
       "        <td>1957</td>\n",
       "        <td>1957</td>\n",
       "        <td>G</td>\n",
       "        <td>6-3</td>\n",
       "        <td>180.0</td>\n",
       "        <td>July 27, 1932</td>\n",
       "        <td>Western Kentucky University</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>John Abramovic</td>\n",
       "        <td>1947</td>\n",
       "        <td>1948</td>\n",
       "        <td>F</td>\n",
       "        <td>6-3</td>\n",
       "        <td>195.0</td>\n",
       "        <td>February 9, 1919</td>\n",
       "        <td>Salem International University</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Alex Abrines</td>\n",
       "        <td>2017</td>\n",
       "        <td>2018</td>\n",
       "        <td>G-F</td>\n",
       "        <td>6-6</td>\n",
       "        <td>190.0</td>\n",
       "        <td>August 1, 1993</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Alaa Abdelnaby', 1991, 1995, 'F-C', '6-10', 240.0, 'June 24, 1968', 'Duke University'),\n",
       " ('Zaid Abdul-Aziz', 1969, 1978, 'C-F', '6-9', 235.0, 'April 7, 1946', 'Iowa State University'),\n",
       " ('Kareem Abdul-Jabbar', 1970, 1989, 'C', '7-2', 225.0, 'April 16, 1947', 'University of California, Los Angeles'),\n",
       " ('Mahmoud Abdul-Rauf', 1991, 2001, 'G', '6-1', 162.0, 'March 9, 1969', 'Louisiana State University'),\n",
       " ('Tariq Abdul-Wahad', 1998, 2003, 'F', '6-6', 223.0, 'November 3, 1974', 'San Jose State University'),\n",
       " ('Shareef Abdur-Rahim', 1997, 2008, 'F', '6-9', 225.0, 'December 11, 1976', 'University of California'),\n",
       " ('Tom Abernethy', 1977, 1981, 'F', '6-7', 220.0, 'May 6, 1954', 'Indiana University'),\n",
       " ('Forest Able', 1957, 1957, 'G', '6-3', 180.0, 'July 27, 1932', 'Western Kentucky University'),\n",
       " ('John Abramovic', 1947, 1948, 'F', '6-3', 195.0, 'February 9, 1919', 'Salem International University'),\n",
       " ('Alex Abrines', 2017, 2018, 'G-F', '6-6', 190.0, 'August 1, 1993', None)]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * FROM players LIMIT 10;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Birth date should be DATE, height should be numeric"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Update correct dtypes / Convert height to cm and weight to kgs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/nbadb\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>birth_date</th>\n",
       "        <th>birth_as_date</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>June 24, 1968</td>\n",
       "        <td>1968-06-24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>April 7, 1946</td>\n",
       "        <td>1946-04-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>April 16, 1947</td>\n",
       "        <td>1947-04-16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>March 9, 1969</td>\n",
       "        <td>1969-03-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>November 3, 1974</td>\n",
       "        <td>1974-11-03</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('June 24, 1968', datetime.date(1968, 6, 24)),\n",
       " ('April 7, 1946', datetime.date(1946, 4, 7)),\n",
       " ('April 16, 1947', datetime.date(1947, 4, 16)),\n",
       " ('March 9, 1969', datetime.date(1969, 3, 9)),\n",
       " ('November 3, 1974', datetime.date(1974, 11, 3))]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT birth_date, CAST(birth_date AS date) birth_as_date\n",
    "FROM players\n",
    "LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/nbadb\n",
      "4550 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "UPDATE\n",
    "    players\n",
    "SET\n",
    "    height = SPLIT_PART(height, '-', 1)::integer * 30.48 + SPLIT_PART(height, '-', 2)::integer * 2.54,\n",
    "    weight = weight::numeric * 0.453592;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/nbadb\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "ALTER TABLE players ALTER COLUMN birth_date SET DATA TYPE date USING CAST(birth_date AS date);\n",
    "ALTER TABLE players ALTER COLUMN college SET DATA TYPE varchar(255);\n",
    "ALTER TABLE players ALTER COLUMN weight SET DATA TYPE integer;\n",
    "ALTER TABLE players ALTER COLUMN name SET DATA TYPE varchar(255);\n",
    "ALTER TABLE players ALTER COLUMN position SET DATA TYPE varchar(5);\n",
    "ALTER TABLE players ALTER COLUMN height SET DATA TYPE integer USING height::numeric::integer;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Query to split name data to firstname and lastname. Not applied to dataset.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/nbadb\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>firstname</th>\n",
       "        <th>lastname</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Billy Ray Bates</td>\n",
       "        <td>Billy Ray</td>\n",
       "        <td>Bates</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>George Bon Salle</td>\n",
       "        <td>George Bon</td>\n",
       "        <td>Salle</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Joe Barry Carroll</td>\n",
       "        <td>Joe Barry</td>\n",
       "        <td>Carroll</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Nando De Colo</td>\n",
       "        <td>Nando De</td>\n",
       "        <td>Colo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Vinny Del Negro</td>\n",
       "        <td>Vinny Del</td>\n",
       "        <td>Negro</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Billy Ray Bates', 'Billy Ray', 'Bates'),\n",
       " ('George Bon Salle', 'George Bon', 'Salle'),\n",
       " ('Joe Barry Carroll', 'Joe Barry', 'Carroll'),\n",
       " ('Nando De Colo', 'Nando De', 'Colo'),\n",
       " ('Vinny Del Negro', 'Vinny Del', 'Negro')]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT\n",
    "    name,\n",
    "    SUBSTRING(name FROM 1 FOR (LENGTH(name) - STRPOS(REVERSE(name), ' '))) as firstname,\n",
    "    REVERSE(SPLIT_PART(REVERSE(name), ' ', 1)) lastname\n",
    "FROM players\n",
    "WHERE name ~ '\\w+\\s\\w+\\s'\n",
    "LIMIT 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Regex split to get new rows for position**\n",
    "- Instead of creating multiple player rows, it will be more effective to use pos data in seasons dataset, as players might change position in seasons/teams. \n",
    "- We will also keep this row unchanged in players table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/nbadb\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>year_start</th>\n",
       "        <th>year_end</th>\n",
       "        <th>position</th>\n",
       "        <th>height</th>\n",
       "        <th>weight</th>\n",
       "        <th>birth_date</th>\n",
       "        <th>college</th>\n",
       "        <th>regexp_split_to_table</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Al Jefferson</td>\n",
       "        <td>2005</td>\n",
       "        <td>2018</td>\n",
       "        <td>C-F</td>\n",
       "        <td>208</td>\n",
       "        <td>131</td>\n",
       "        <td>1985-01-04</td>\n",
       "        <td>None</td>\n",
       "        <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Al Jefferson</td>\n",
       "        <td>2005</td>\n",
       "        <td>2018</td>\n",
       "        <td>C-F</td>\n",
       "        <td>208</td>\n",
       "        <td>131</td>\n",
       "        <td>1985-01-04</td>\n",
       "        <td>None</td>\n",
       "        <td>F</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Furkan Korkmaz</td>\n",
       "        <td>2018</td>\n",
       "        <td>2018</td>\n",
       "        <td>G</td>\n",
       "        <td>201</td>\n",
       "        <td>84</td>\n",
       "        <td>1997-07-24</td>\n",
       "        <td>None</td>\n",
       "        <td>G</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Don May</td>\n",
       "        <td>1969</td>\n",
       "        <td>1975</td>\n",
       "        <td>F</td>\n",
       "        <td>193</td>\n",
       "        <td>91</td>\n",
       "        <td>1946-01-03</td>\n",
       "        <td>University of Dayton</td>\n",
       "        <td>F</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Alaa Abdelnaby</td>\n",
       "        <td>1991</td>\n",
       "        <td>1995</td>\n",
       "        <td>F-C</td>\n",
       "        <td>208</td>\n",
       "        <td>109</td>\n",
       "        <td>1968-06-24</td>\n",
       "        <td>Duke University</td>\n",
       "        <td>F</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Al Jefferson', 2005, 2018, 'C-F', 208, 131, datetime.date(1985, 1, 4), None, 'C'),\n",
       " ('Al Jefferson', 2005, 2018, 'C-F', 208, 131, datetime.date(1985, 1, 4), None, 'F'),\n",
       " ('Furkan Korkmaz', 2018, 2018, 'G', 201, 84, datetime.date(1997, 7, 24), None, 'G'),\n",
       " ('Don May', 1969, 1975, 'F', 193, 91, datetime.date(1946, 1, 3), 'University of Dayton', 'F'),\n",
       " ('Alaa Abdelnaby', 1991, 1995, 'F-C', 208, 109, datetime.date(1968, 6, 24), 'Duke University', 'F')]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT *, REGEXP_SPLIT_TO_TABLE(position,'-')\n",
    "FROM players\n",
    "LIMIT 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Check if there are null values from player name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/nbadb\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(0,)]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT COUNT(*)\n",
    "FROM players\n",
    "WHERE name IS NULL;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/nbadb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "ALTER TABLE players ALTER COLUMN name SET NOT NULL;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create a serial id for players"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/nbadb\n",
      "0 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>birth_date</th>\n",
       "        <th>count</th>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT\n",
    "    name,\n",
    "    birth_date,\n",
    "    COUNT(*)\n",
    "FROM\n",
    "    players\n",
    "GROUP BY\n",
    "    name, birth_date\n",
    "HAVING\n",
    "    COUNT(*) > 1;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- There are no rows with same player name and same birth date, which means every player is unique.\n",
    "- We can add a serial id key which autoincrements with new records"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/nbadb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "ALTER TABLE players ADD COLUMN id serial PRIMARY KEY;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### SEASONS TABLE"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Check out first 10 rows**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/nbadb\n",
      "10 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>year</th>\n",
       "        <th>player</th>\n",
       "        <th>pos</th>\n",
       "        <th>age</th>\n",
       "        <th>tm</th>\n",
       "        <th>g</th>\n",
       "        <th>gs</th>\n",
       "        <th>mp</th>\n",
       "        <th>per</th>\n",
       "        <th>ts_pct</th>\n",
       "        <th>3par</th>\n",
       "        <th>ftr</th>\n",
       "        <th>orb_pct</th>\n",
       "        <th>drb_pct</th>\n",
       "        <th>trb_pct</th>\n",
       "        <th>ast_pct</th>\n",
       "        <th>stl_pct</th>\n",
       "        <th>blk_pct</th>\n",
       "        <th>tov_pct</th>\n",
       "        <th>usg_pct</th>\n",
       "        <th>blanl</th>\n",
       "        <th>ows</th>\n",
       "        <th>dws</th>\n",
       "        <th>ws</th>\n",
       "        <th>ws/48</th>\n",
       "        <th>blank2</th>\n",
       "        <th>obpm</th>\n",
       "        <th>dbpm</th>\n",
       "        <th>bpm</th>\n",
       "        <th>vorp</th>\n",
       "        <th>fg</th>\n",
       "        <th>fga</th>\n",
       "        <th>fg_pct</th>\n",
       "        <th>3p</th>\n",
       "        <th>3pa</th>\n",
       "        <th>3p_pct</th>\n",
       "        <th>2p</th>\n",
       "        <th>2pa</th>\n",
       "        <th>2p_pct</th>\n",
       "        <th>efg_pct</th>\n",
       "        <th>ft</th>\n",
       "        <th>fta</th>\n",
       "        <th>ft_pct</th>\n",
       "        <th>orb</th>\n",
       "        <th>drb</th>\n",
       "        <th>trb</th>\n",
       "        <th>ast</th>\n",
       "        <th>stl</th>\n",
       "        <th>blk</th>\n",
       "        <th>tov</th>\n",
       "        <th>pf</th>\n",
       "        <th>pts</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1950.0</td>\n",
       "        <td>Curly Armstrong</td>\n",
       "        <td>G-F</td>\n",
       "        <td>31.0</td>\n",
       "        <td>FTW</td>\n",
       "        <td>63.0</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>0.368</td>\n",
       "        <td>None</td>\n",
       "        <td>0.467</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>-0.1</td>\n",
       "        <td>3.6</td>\n",
       "        <td>3.5</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>144.0</td>\n",
       "        <td>516.0</td>\n",
       "        <td>0.27899999999999997</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>144.0</td>\n",
       "        <td>516.0</td>\n",
       "        <td>0.27899999999999997</td>\n",
       "        <td>0.27899999999999997</td>\n",
       "        <td>170.0</td>\n",
       "        <td>241.0</td>\n",
       "        <td>0.705</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>176.0</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>217.0</td>\n",
       "        <td>458.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1950.0</td>\n",
       "        <td>Cliff Barker</td>\n",
       "        <td>SG</td>\n",
       "        <td>29.0</td>\n",
       "        <td>INO</td>\n",
       "        <td>49.0</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>0.435</td>\n",
       "        <td>None</td>\n",
       "        <td>0.387</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>1.6</td>\n",
       "        <td>0.6</td>\n",
       "        <td>2.2</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>102.0</td>\n",
       "        <td>274.0</td>\n",
       "        <td>0.37200000000000005</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>102.0</td>\n",
       "        <td>274.0</td>\n",
       "        <td>0.37200000000000005</td>\n",
       "        <td>0.37200000000000005</td>\n",
       "        <td>75.0</td>\n",
       "        <td>106.0</td>\n",
       "        <td>0.708</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>109.0</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>99.0</td>\n",
       "        <td>279.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1950.0</td>\n",
       "        <td>Leo Barnhorst</td>\n",
       "        <td>SF</td>\n",
       "        <td>25.0</td>\n",
       "        <td>CHS</td>\n",
       "        <td>67.0</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>0.39399999999999996</td>\n",
       "        <td>None</td>\n",
       "        <td>0.259</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>0.9</td>\n",
       "        <td>2.8</td>\n",
       "        <td>3.6</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>174.0</td>\n",
       "        <td>499.0</td>\n",
       "        <td>0.349</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>174.0</td>\n",
       "        <td>499.0</td>\n",
       "        <td>0.349</td>\n",
       "        <td>0.349</td>\n",
       "        <td>90.0</td>\n",
       "        <td>129.0</td>\n",
       "        <td>0.698</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>140.0</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>192.0</td>\n",
       "        <td>438.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1950.0</td>\n",
       "        <td>Ed Bartels</td>\n",
       "        <td>F</td>\n",
       "        <td>24.0</td>\n",
       "        <td>TOT</td>\n",
       "        <td>15.0</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>0.312</td>\n",
       "        <td>None</td>\n",
       "        <td>0.395</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>-0.5</td>\n",
       "        <td>-0.1</td>\n",
       "        <td>-0.6</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>22.0</td>\n",
       "        <td>86.0</td>\n",
       "        <td>0.256</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>22.0</td>\n",
       "        <td>86.0</td>\n",
       "        <td>0.256</td>\n",
       "        <td>0.256</td>\n",
       "        <td>19.0</td>\n",
       "        <td>34.0</td>\n",
       "        <td>0.5589999999999999</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>20.0</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>29.0</td>\n",
       "        <td>63.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1950.0</td>\n",
       "        <td>Ed Bartels</td>\n",
       "        <td>F</td>\n",
       "        <td>24.0</td>\n",
       "        <td>DNN</td>\n",
       "        <td>13.0</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>0.308</td>\n",
       "        <td>None</td>\n",
       "        <td>0.37799999999999995</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>-0.5</td>\n",
       "        <td>-0.1</td>\n",
       "        <td>-0.6</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>21.0</td>\n",
       "        <td>82.0</td>\n",
       "        <td>0.256</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>21.0</td>\n",
       "        <td>82.0</td>\n",
       "        <td>0.256</td>\n",
       "        <td>0.256</td>\n",
       "        <td>17.0</td>\n",
       "        <td>31.0</td>\n",
       "        <td>0.5479999999999999</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>20.0</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>27.0</td>\n",
       "        <td>59.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1950.0</td>\n",
       "        <td>Ed Bartels</td>\n",
       "        <td>F</td>\n",
       "        <td>24.0</td>\n",
       "        <td>NYK</td>\n",
       "        <td>2.0</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>0.376</td>\n",
       "        <td>None</td>\n",
       "        <td>0.75</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>1.0</td>\n",
       "        <td>4.0</td>\n",
       "        <td>0.25</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>1.0</td>\n",
       "        <td>4.0</td>\n",
       "        <td>0.25</td>\n",
       "        <td>0.25</td>\n",
       "        <td>2.0</td>\n",
       "        <td>3.0</td>\n",
       "        <td>0.667</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>0.0</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>2.0</td>\n",
       "        <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1950.0</td>\n",
       "        <td>Ralph Beard</td>\n",
       "        <td>G</td>\n",
       "        <td>22.0</td>\n",
       "        <td>INO</td>\n",
       "        <td>60.0</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>0.42200000000000004</td>\n",
       "        <td>None</td>\n",
       "        <td>0.301</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>3.6</td>\n",
       "        <td>1.2</td>\n",
       "        <td>4.8</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>340.0</td>\n",
       "        <td>936.0</td>\n",
       "        <td>0.363</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>340.0</td>\n",
       "        <td>936.0</td>\n",
       "        <td>0.363</td>\n",
       "        <td>0.363</td>\n",
       "        <td>215.0</td>\n",
       "        <td>282.0</td>\n",
       "        <td>0.762</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>233.0</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>132.0</td>\n",
       "        <td>895.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1950.0</td>\n",
       "        <td>Gene Berce</td>\n",
       "        <td>G-F</td>\n",
       "        <td>23.0</td>\n",
       "        <td>TRI</td>\n",
       "        <td>3.0</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>0.275</td>\n",
       "        <td>None</td>\n",
       "        <td>0.313</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>-0.1</td>\n",
       "        <td>0.0</td>\n",
       "        <td>-0.1</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>5.0</td>\n",
       "        <td>16.0</td>\n",
       "        <td>0.313</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>5.0</td>\n",
       "        <td>16.0</td>\n",
       "        <td>0.313</td>\n",
       "        <td>0.313</td>\n",
       "        <td>0.0</td>\n",
       "        <td>5.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>2.0</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>6.0</td>\n",
       "        <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1950.0</td>\n",
       "        <td>Charlie Black</td>\n",
       "        <td>F-C</td>\n",
       "        <td>28.0</td>\n",
       "        <td>TOT</td>\n",
       "        <td>65.0</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>0.34600000000000003</td>\n",
       "        <td>None</td>\n",
       "        <td>0.395</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>-2.2</td>\n",
       "        <td>5.0</td>\n",
       "        <td>2.8</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>226.0</td>\n",
       "        <td>813.0</td>\n",
       "        <td>0.278</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>226.0</td>\n",
       "        <td>813.0</td>\n",
       "        <td>0.278</td>\n",
       "        <td>0.278</td>\n",
       "        <td>209.0</td>\n",
       "        <td>321.0</td>\n",
       "        <td>0.6509999999999999</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>163.0</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>273.0</td>\n",
       "        <td>661.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1950.0</td>\n",
       "        <td>Charlie Black</td>\n",
       "        <td>F-C</td>\n",
       "        <td>28.0</td>\n",
       "        <td>FTW</td>\n",
       "        <td>36.0</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>0.36200000000000004</td>\n",
       "        <td>None</td>\n",
       "        <td>0.48</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>-0.7</td>\n",
       "        <td>2.2</td>\n",
       "        <td>1.5</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>125.0</td>\n",
       "        <td>435.0</td>\n",
       "        <td>0.287</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>125.0</td>\n",
       "        <td>435.0</td>\n",
       "        <td>0.287</td>\n",
       "        <td>0.287</td>\n",
       "        <td>132.0</td>\n",
       "        <td>209.0</td>\n",
       "        <td>0.632</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>75.0</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>140.0</td>\n",
       "        <td>382.0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1950.0, 'Curly Armstrong', 'G-F', 31.0, 'FTW', 63.0, None, None, None, 0.368, None, 0.467, None, None, None, None, None, None, None, None, None, -0.1, 3.6, 3.5, None, None, None, None, None, None, 144.0, 516.0, 0.27899999999999997, None, None, None, 144.0, 516.0, 0.27899999999999997, 0.27899999999999997, 170.0, 241.0, 0.705, None, None, None, 176.0, None, None, None, 217.0, 458.0),\n",
       " (1950.0, 'Cliff Barker', 'SG', 29.0, 'INO', 49.0, None, None, None, 0.435, None, 0.387, None, None, None, None, None, None, None, None, None, 1.6, 0.6, 2.2, None, None, None, None, None, None, 102.0, 274.0, 0.37200000000000005, None, None, None, 102.0, 274.0, 0.37200000000000005, 0.37200000000000005, 75.0, 106.0, 0.708, None, None, None, 109.0, None, None, None, 99.0, 279.0),\n",
       " (1950.0, 'Leo Barnhorst', 'SF', 25.0, 'CHS', 67.0, None, None, None, 0.39399999999999996, None, 0.259, None, None, None, None, None, None, None, None, None, 0.9, 2.8, 3.6, None, None, None, None, None, None, 174.0, 499.0, 0.349, None, None, None, 174.0, 499.0, 0.349, 0.349, 90.0, 129.0, 0.698, None, None, None, 140.0, None, None, None, 192.0, 438.0),\n",
       " (1950.0, 'Ed Bartels', 'F', 24.0, 'TOT', 15.0, None, None, None, 0.312, None, 0.395, None, None, None, None, None, None, None, None, None, -0.5, -0.1, -0.6, None, None, None, None, None, None, 22.0, 86.0, 0.256, None, None, None, 22.0, 86.0, 0.256, 0.256, 19.0, 34.0, 0.5589999999999999, None, None, None, 20.0, None, None, None, 29.0, 63.0),\n",
       " (1950.0, 'Ed Bartels', 'F', 24.0, 'DNN', 13.0, None, None, None, 0.308, None, 0.37799999999999995, None, None, None, None, None, None, None, None, None, -0.5, -0.1, -0.6, None, None, None, None, None, None, 21.0, 82.0, 0.256, None, None, None, 21.0, 82.0, 0.256, 0.256, 17.0, 31.0, 0.5479999999999999, None, None, None, 20.0, None, None, None, 27.0, 59.0),\n",
       " (1950.0, 'Ed Bartels', 'F', 24.0, 'NYK', 2.0, None, None, None, 0.376, None, 0.75, None, None, None, None, None, None, None, None, None, 0.0, 0.0, 0.0, None, None, None, None, None, None, 1.0, 4.0, 0.25, None, None, None, 1.0, 4.0, 0.25, 0.25, 2.0, 3.0, 0.667, None, None, None, 0.0, None, None, None, 2.0, 4.0),\n",
       " (1950.0, 'Ralph Beard', 'G', 22.0, 'INO', 60.0, None, None, None, 0.42200000000000004, None, 0.301, None, None, None, None, None, None, None, None, None, 3.6, 1.2, 4.8, None, None, None, None, None, None, 340.0, 936.0, 0.363, None, None, None, 340.0, 936.0, 0.363, 0.363, 215.0, 282.0, 0.762, None, None, None, 233.0, None, None, None, 132.0, 895.0),\n",
       " (1950.0, 'Gene Berce', 'G-F', 23.0, 'TRI', 3.0, None, None, None, 0.275, None, 0.313, None, None, None, None, None, None, None, None, None, -0.1, 0.0, -0.1, None, None, None, None, None, None, 5.0, 16.0, 0.313, None, None, None, 5.0, 16.0, 0.313, 0.313, 0.0, 5.0, 0.0, None, None, None, 2.0, None, None, None, 6.0, 10.0),\n",
       " (1950.0, 'Charlie Black', 'F-C', 28.0, 'TOT', 65.0, None, None, None, 0.34600000000000003, None, 0.395, None, None, None, None, None, None, None, None, None, -2.2, 5.0, 2.8, None, None, None, None, None, None, 226.0, 813.0, 0.278, None, None, None, 226.0, 813.0, 0.278, 0.278, 209.0, 321.0, 0.6509999999999999, None, None, None, 163.0, None, None, None, 273.0, 661.0),\n",
       " (1950.0, 'Charlie Black', 'F-C', 28.0, 'FTW', 36.0, None, None, None, 0.36200000000000004, None, 0.48, None, None, None, None, None, None, None, None, None, -0.7, 2.2, 1.5, None, None, None, None, None, None, 125.0, 435.0, 0.287, None, None, None, 125.0, 435.0, 0.287, 0.287, 132.0, 209.0, 0.632, None, None, None, 75.0, None, None, None, 140.0, 382.0)]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * FROM seasons LIMIT 10;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* There are rows indicating the season total where team name is represented with 'TOT'. \n",
    "* We will remove this line for data integrity. Yearly totals can be calculated with simple query. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/nbadb\n",
      "2123 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "DELETE FROM seasons\n",
    "WHERE tm = 'TOT';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Remove blank columns and set correct data types"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/nbadb\n",
      "0 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>count</th>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT COUNT(*)\n",
    "FROM seasons\n",
    "GROUP BY blanl, blank2\n",
    "HAVING blanl IS NOT NULL OR blank2 IS NOT NULL;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/nbadb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "ALTER TABLE seasons\n",
    "DROP COLUMN blanl, \n",
    "DROP COLUMN blank2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/nbadb\n",
      "Done.\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "ALTER TABLE seasons ALTER COLUMN pos SET DATA TYPE varchar(5);\n",
    "ALTER TABLE seasons ALTER COLUMN tm SET DATA TYPE varchar(5);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Check and remove null values (for player name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/nbadb\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>67</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(67,)]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT COUNT(*)\n",
    "FROM seasons\n",
    "WHERE player IS NULL;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/nbadb\n",
      "67 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "DELETE FROM seasons\n",
    "WHERE player IS NULL;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create a serial id for seasons"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Check if all values are unique**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/nbadb\n",
      "0 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>count</th>\n",
       "        <th>player</th>\n",
       "        <th>year</th>\n",
       "        <th>tm</th>\n",
       "        <th>age</th>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT COUNT(*), player, year, tm, age\n",
    "FROM seasons\n",
    "GROUP BY player, year, tm, age\n",
    "HAVING COUNT(*)>1;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* There are no duplicate values in seasons dataset for same player, year, team and age info"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/nbadb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "ALTER TABLE\n",
    "    seasons\n",
    "ADD COLUMN id serial PRIMARY KEY;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### CREATE A FOREIGN KEY\n",
    "\n",
    "* This will create a one to many relationship between players and season statistics tables\n",
    "* The items match on player names and season year between the start-end years of player career\n",
    "* Update player names with \"*\" to find a match on names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/nbadb\n",
      "1486 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "UPDATE\n",
    "    seasons\n",
    "SET \n",
    "    player = REPLACE(player, '*', '')\n",
    "WHERE\n",
    "    player LIKE '%*%';"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/nbadb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "ALTER TABLE seasons ADD COLUMN player_id integer;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/nbadb\n",
      "10000 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "UPDATE seasons\n",
    "SET player_id = temp_table.pid\n",
    "FROM ( SELECT s.player, p.name, p.id AS pid, s.id AS sid, p.year_start, p.year_end\n",
    "    FROM seasons AS s\n",
    "    LEFT JOIN players AS p\n",
    "    ON s.player = REGEXP_REPLACE(p.name, '(\\w+\\s+\\w+.?)\\s.+', '\\1')\n",
    ") AS temp_table\n",
    "WHERE\n",
    "    seasons.id = temp_table.sid\n",
    "    AND seasons.year BETWEEN temp_table.year_start AND temp_table.year_end\n",
    "    AND seasons.id BETWEEN 12500 AND 25000;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Check and remove if there are any players in season stats that are not existing in our players table.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/nbadb\n",
      "4 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>player</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "        <td>Johnny Macknowski</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "        <td>Johnny Macknowski</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "        <td>Walter Tavares</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "        <td>Sheldon McClellan</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(None, 'Johnny Macknowski'),\n",
       " (None, 'Johnny Macknowski'),\n",
       " (None, 'Walter Tavares'),\n",
       " (None, 'Sheldon McClellan')]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name, player\n",
    "FROM seasons AS s\n",
    "LEFT JOIN players AS p\n",
    "ON s.player_id = p.id\n",
    "WHERE name IS NULL;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/nbadb\n",
      "4 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "DELETE FROM seasons\n",
    "WHERE player IN ('Johnny Macknowski', 'Sheldon McClellan', 'Walter Tavares')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Set player id as foreign key**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/nbadb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "ALTER TABLE seasons\n",
    "ADD CONSTRAINT fkey_player FOREIGN KEY (player_id) REFERENCES players(id);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Since age and player name already exist in players db, we can drop these columns from seasons**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/nbadb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "ALTER TABLE\n",
    "    seasons\n",
    "DROP COLUMN age, \n",
    "DROP COLUMN player;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Command to create db \n",
    "psql -h localhost -U postgres -f nbadb.sql nbadb"
   ]
  },
  {
   "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.8.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}