1. Getting, cleaning and loading data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "__author__ = \"Victor Xu\"\n",
    "__email__ = \"victor.c.xu@gmail.com\"\n",
    "__website__ = \"victorxu.me\"\n",
    "\n",
    "__copyright__ = \"Copyright 2019, Victor Xu\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Problem Definition\n",
    "\n",
    "The goal of this analysis is to predict NBA player positions given performance data such the player shot count, shot location, and player defense etc... Each player in the league is assigned a position label such as point guard, shooting guard, and center.\n",
    "\n",
    "### Why is predicting player position important?\n",
    "If you are basketball fan, you probably realized that the player positions are inconsistent across data sources. For instance ESPN and the League even uses different position categories altogether.\n",
    "\n",
    "\n",
    "| ESPN | NBA   |\n",
    "|------|------|\n",
    "|Center|Center|\n",
    "|Point Guard|Guard|\n",
    "|Shooting Guard|Guard|\n",
    "|Small Forward|Forward|\n",
    "|Power Forward|Forward|\n",
    "|No Direct Translation|Guard-Forward|\n",
    "|No Direct Translation|Center-Forward|\n",
    "\n",
    "Though we will not be examining the difference between NBA and ESPN player labeling methodologies, never the less by looking at how ML algos approach classifying player into ESPN categories, we could gain insights into the structure of player labels. Specifically, allowing us to examine if player labels are well separated or not."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-31T20:44:06.991477Z",
     "start_time": "2019-05-31T20:44:06.987976Z"
    },
    "init_cell": true,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "import re\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "from src.espn_scraper import espn_player_scraper\n",
    "from src.player_stat import get_player_stat, NoDataError\n",
    "\n",
    "from sqlalchemy import create_engine, Integer\n",
    "from tqdm import tqdm_notebook, tqdm\n",
    "from nba_py import player\n",
    "from time import sleep"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data Acqusition\n",
    "#### Scrape ESPN for player name and position label /w our scraper"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-31T16:24:56.348598Z",
     "start_time": "2019-05-31T16:24:42.664468Z"
    }
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 30/30 [01:58<00:00,  3.67s/it]\n"
     ]
    }
   ],
   "source": [
    "# Scrape ESPN pages\n",
    "teams_overview_url = \"http://www.espn.com/nba/players\"\n",
    "scraper = espn_player_scraper()\n",
    "espn_player_list = scraper.scrape_all_players(teams_overview_url)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>name</th>\n",
       "      <th>position</th>\n",
       "      <th>espn_player_id</th>\n",
       "      <th>url</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Jaylen Brown</td>\n",
       "      <td>SG</td>\n",
       "      <td>3917376</td>\n",
       "      <td>http://www.espn.com/nba/player/_/id/3917376/ja...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Carsen Edwards</td>\n",
       "      <td>PG</td>\n",
       "      <td>4066407</td>\n",
       "      <td>http://www.espn.com/nba/player/_/id/4066407/ca...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Tacko Fall</td>\n",
       "      <td>C</td>\n",
       "      <td>3904625</td>\n",
       "      <td>http://www.espn.com/nba/player/_/id/3904625/ta...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Jonathan Gibson</td>\n",
       "      <td>PG</td>\n",
       "      <td>2234666</td>\n",
       "      <td>http://www.espn.com/nba/player/_/id/2234666/jo...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Javonte Green</td>\n",
       "      <td>SG</td>\n",
       "      <td>2596112</td>\n",
       "      <td>http://www.espn.com/nba/player/_/id/2596112/ja...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              name position espn_player_id  \\\n",
       "0     Jaylen Brown       SG        3917376   \n",
       "1   Carsen Edwards       PG        4066407   \n",
       "2       Tacko Fall        C        3904625   \n",
       "3  Jonathan Gibson       PG        2234666   \n",
       "4    Javonte Green       SG        2596112   \n",
       "\n",
       "                                                 url  \n",
       "0  http://www.espn.com/nba/player/_/id/3917376/ja...  \n",
       "1  http://www.espn.com/nba/player/_/id/4066407/ca...  \n",
       "2  http://www.espn.com/nba/player/_/id/3904625/ta...  \n",
       "3  http://www.espn.com/nba/player/_/id/2234666/jo...  \n",
       "4  http://www.espn.com/nba/player/_/id/2596112/ja...  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "espn_player_list.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Getting player name and NBA ID data from the Official NBA API\n",
    "Here we will use nba_py by seemethere, a Python wrapper for the offical but unpublished NBA API.\n",
    "\n",
    "We will be using the player performance data from the NBA API and using it to predict player's ESPN position label.\n",
    "\n",
    "The official NBA API uses a different set of Player IDs, so we will have to join ESPN and NBA data by cross referencing player names. Also, the NBA API returns some players in the NBA Delevelopment league, which we are not interested in. We will thus use a left join on the ESPN table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-31T16:25:04.548370Z",
     "start_time": "2019-05-31T16:25:04.329028Z"
    },
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "NBA roster has 1016 players over past 3 seasons\n",
      "ESPN roster has 564 players in the current season.\n"
     ]
    }
   ],
   "source": [
    "# Getting players who were active in the last 3 seasons for the NBA database\n",
    "nba_player_list_17 = player.PlayerList(season='2017-18').info()\n",
    "nba_player_list_18 = player.PlayerList(season='2018-19').info()\n",
    "nba_player_list_19 = player.PlayerList(season='2019-20').info()\n",
    "\n",
    "# Concat the 3 dfs together\n",
    "nba_player_list = pd.concat([nba_player_list_17,nba_player_list_18,nba_player_list_19])\n",
    "nba_player_list = nba_player_list.drop_duplicates()\n",
    "\n",
    "# Convert upper case letter to upper case letter\n",
    "nba_player_list.columns = [col.lower() for col in nba_player_list.columns]\n",
    "\n",
    "# Check if number of players are the same\n",
    "print(\"NBA roster has {} players over past 3 seasons\".format(nba_player_list.shape[0]))\n",
    "print(\"ESPN roster has {} players in the current season.\".format(espn_player_list.shape[0]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Examine the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-31T16:25:09.942406Z",
     "start_time": "2019-05-31T16:25:09.933214Z"
    }
   },
   "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>person_id</th>\n",
       "      <th>display_first_last</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>203518</td>\n",
       "      <td>alex abrines</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>203112</td>\n",
       "      <td>quincy acy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>201167</td>\n",
       "      <td>arron afflalo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>201582</td>\n",
       "      <td>alexis ajinca</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>202332</td>\n",
       "      <td>cole aldrich</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   person_id display_first_last\n",
       "0     203518       alex abrines\n",
       "1     203112         quincy acy\n",
       "2     201167      arron afflalo\n",
       "3     201582      alexis ajinca\n",
       "4     202332       cole aldrich"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Selecting columns of interest\n",
    "nba_player_list = nba_player_list[['person_id','display_first_last']]\n",
    "nba_player_list.display_first_last = nba_player_list.display_first_last.str.lower()\n",
    "nba_player_list.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 1016 entries, 0 to 514\n",
      "Data columns (total 2 columns):\n",
      "person_id             1016 non-null int64\n",
      "display_first_last    1016 non-null object\n",
      "dtypes: int64(1), object(1)\n",
      "memory usage: 23.8+ KB\n"
     ]
    }
   ],
   "source": [
    "nba_player_list.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-31T16:25:10.067966Z",
     "start_time": "2019-05-31T16:25:10.056232Z"
    },
    "scrolled": false
   },
   "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>name</th>\n",
       "      <th>position</th>\n",
       "      <th>espn_player_id</th>\n",
       "      <th>url</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Jaylen Brown</td>\n",
       "      <td>SG</td>\n",
       "      <td>3917376</td>\n",
       "      <td>http://www.espn.com/nba/player/_/id/3917376/ja...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Carsen Edwards</td>\n",
       "      <td>PG</td>\n",
       "      <td>4066407</td>\n",
       "      <td>http://www.espn.com/nba/player/_/id/4066407/ca...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Tacko Fall</td>\n",
       "      <td>C</td>\n",
       "      <td>3904625</td>\n",
       "      <td>http://www.espn.com/nba/player/_/id/3904625/ta...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Jonathan Gibson</td>\n",
       "      <td>PG</td>\n",
       "      <td>2234666</td>\n",
       "      <td>http://www.espn.com/nba/player/_/id/2234666/jo...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Javonte Green</td>\n",
       "      <td>SG</td>\n",
       "      <td>2596112</td>\n",
       "      <td>http://www.espn.com/nba/player/_/id/2596112/ja...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              name position espn_player_id  \\\n",
       "0     Jaylen Brown       SG        3917376   \n",
       "1   Carsen Edwards       PG        4066407   \n",
       "2       Tacko Fall        C        3904625   \n",
       "3  Jonathan Gibson       PG        2234666   \n",
       "4    Javonte Green       SG        2596112   \n",
       "\n",
       "                                                 url  \n",
       "0  http://www.espn.com/nba/player/_/id/3917376/ja...  \n",
       "1  http://www.espn.com/nba/player/_/id/4066407/ca...  \n",
       "2  http://www.espn.com/nba/player/_/id/3904625/ta...  \n",
       "3  http://www.espn.com/nba/player/_/id/2234666/jo...  \n",
       "4  http://www.espn.com/nba/player/_/id/2596112/ja...  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "espn_player_list.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 564 entries, 0 to 563\n",
      "Data columns (total 4 columns):\n",
      "name              564 non-null object\n",
      "position          561 non-null object\n",
      "espn_player_id    564 non-null object\n",
      "url               564 non-null object\n",
      "dtypes: object(4)\n",
      "memory usage: 17.8+ KB\n"
     ]
    }
   ],
   "source": [
    "espn_player_list.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Check duplicates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "236"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nba_player_list.duplicated().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "espn_player_list.duplicated().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>name</th>\n",
       "      <th>position</th>\n",
       "      <th>espn_player_id</th>\n",
       "      <th>url</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>124</th>\n",
       "      <td>Rodney McGruder</td>\n",
       "      <td>SF</td>\n",
       "      <td>2488826</td>\n",
       "      <td>http://www.espn.com/nba/player/_/id/2488826/ro...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157</th>\n",
       "      <td>Jawun Evans</td>\n",
       "      <td>PG</td>\n",
       "      <td>3912854</td>\n",
       "      <td>http://www.espn.com/nba/player/_/id/3912854/ja...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>386</th>\n",
       "      <td>Brandon Goodwin</td>\n",
       "      <td>PG</td>\n",
       "      <td>3057198</td>\n",
       "      <td>http://www.espn.com/nba/player/_/id/3057198/br...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>429</th>\n",
       "      <td>Rodney McGruder</td>\n",
       "      <td>SF</td>\n",
       "      <td>2488826</td>\n",
       "      <td>http://www.espn.com/nba/player/_/id/2488826/ro...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>483</th>\n",
       "      <td>Brandon Goodwin</td>\n",
       "      <td>PG</td>\n",
       "      <td>3057198</td>\n",
       "      <td>http://www.espn.com/nba/player/_/id/3057198/br...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>518</th>\n",
       "      <td>Jawun Evans</td>\n",
       "      <td>PG</td>\n",
       "      <td>3912854</td>\n",
       "      <td>http://www.espn.com/nba/player/_/id/3912854/ja...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                name position espn_player_id  \\\n",
       "124  Rodney McGruder       SF        2488826   \n",
       "157      Jawun Evans       PG        3912854   \n",
       "386  Brandon Goodwin       PG        3057198   \n",
       "429  Rodney McGruder       SF        2488826   \n",
       "483  Brandon Goodwin       PG        3057198   \n",
       "518      Jawun Evans       PG        3912854   \n",
       "\n",
       "                                                   url  \n",
       "124  http://www.espn.com/nba/player/_/id/2488826/ro...  \n",
       "157  http://www.espn.com/nba/player/_/id/3912854/ja...  \n",
       "386  http://www.espn.com/nba/player/_/id/3057198/br...  \n",
       "429  http://www.espn.com/nba/player/_/id/2488826/ro...  \n",
       "483  http://www.espn.com/nba/player/_/id/3057198/br...  \n",
       "518  http://www.espn.com/nba/player/_/id/3912854/ja...  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "espn_player_list[espn_player_list.duplicated(keep=False)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "espn_player_list = espn_player_list.drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "espn_player_list.duplicated().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Check N/A\n",
    "\n",
    "A small number of newly drafted rookies will not have a position assigned to them yet. Their positions will be N/As"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "person_id             0\n",
       "display_first_last    0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nba_player_list.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "name              0\n",
       "position          3\n",
       "espn_player_id    0\n",
       "url               0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "espn_player_list.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "espn_player_list = espn_player_list.dropna()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Joining 2 datasets together\n",
    "\n",
    "We need the ESPN player position label which lives in the ESPN table, and the NBA player ID, which lives in the NBA table.\n",
    "\n",
    "We will later use NBA player ID to call the official NBA API to retrieve player performence data, which is used to train our models.\n",
    "\n",
    "| name | position | \n",
    "|------|------|\n",
    "|Center|Center|\n",
    "|Point Guard|Guard|"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Cleaning before joining 2 data scources\n",
    "\n",
    "We will be joining on player names, which are different across ESPN and NBA records. As such, cleaning is required\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-31T16:25:10.683248Z",
     "start_time": "2019-05-31T16:25:10.678950Z"
    }
   },
   "outputs": [],
   "source": [
    "def sanitize_name(name_str):\n",
    "    \"\"\"Remove all special characters and player name suffix\"\"\"\n",
    "    sanitized = name_str.lower()\n",
    "    sanitized = sanitized.replace('-', ' ')\n",
    "    \n",
    "    to_remove = [\".\", \"'\", \"jr\", 'sr', 'iii']\n",
    "    \n",
    "    for pattern in to_remove:\n",
    "        sanitized = sanitized.replace(pattern, '')\n",
    "        \n",
    "    # Remove special characters and trailing spaces\n",
    "    sanitized = re.sub(re.compile(\"\\s*$\"), '', sanitized)\n",
    "        \n",
    "    return sanitized"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-31T16:25:10.859584Z",
     "start_time": "2019-05-31T16:25:10.849229Z"
    }
   },
   "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>name</th>\n",
       "      <th>position</th>\n",
       "      <th>espn_player_id</th>\n",
       "      <th>url</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Jaylen Brown</td>\n",
       "      <td>SG</td>\n",
       "      <td>3917376</td>\n",
       "      <td>http://www.espn.com/nba/player/_/id/3917376/ja...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Carsen Edwards</td>\n",
       "      <td>PG</td>\n",
       "      <td>4066407</td>\n",
       "      <td>http://www.espn.com/nba/player/_/id/4066407/ca...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Tacko Fall</td>\n",
       "      <td>C</td>\n",
       "      <td>3904625</td>\n",
       "      <td>http://www.espn.com/nba/player/_/id/3904625/ta...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Jonathan Gibson</td>\n",
       "      <td>PG</td>\n",
       "      <td>2234666</td>\n",
       "      <td>http://www.espn.com/nba/player/_/id/2234666/jo...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Javonte Green</td>\n",
       "      <td>SG</td>\n",
       "      <td>2596112</td>\n",
       "      <td>http://www.espn.com/nba/player/_/id/2596112/ja...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              name position espn_player_id  \\\n",
       "0     Jaylen Brown       SG        3917376   \n",
       "1   Carsen Edwards       PG        4066407   \n",
       "2       Tacko Fall        C        3904625   \n",
       "3  Jonathan Gibson       PG        2234666   \n",
       "4    Javonte Green       SG        2596112   \n",
       "\n",
       "                                                 url  \n",
       "0  http://www.espn.com/nba/player/_/id/3917376/ja...  \n",
       "1  http://www.espn.com/nba/player/_/id/4066407/ca...  \n",
       "2  http://www.espn.com/nba/player/_/id/3904625/ta...  \n",
       "3  http://www.espn.com/nba/player/_/id/2234666/jo...  \n",
       "4  http://www.espn.com/nba/player/_/id/2596112/ja...  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Getting a list of ESPN player names not in NBA player name list\n",
    "espn_player_list[~espn_player_list.name.isin(nba_player_list.display_first_last)].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-31T16:25:11.073142Z",
     "start_time": "2019-05-31T16:25:11.006869Z"
    }
   },
   "outputs": [],
   "source": [
    "# Sanitize player name for both dfs so they can later be used to join the tables\n",
    "nba_player_list.display_first_last = nba_player_list.display_first_last.astype('str')\n",
    "espn_player_list.name = espn_player_list.name.astype('str')\n",
    "\n",
    "espn_player_list.name = espn_player_list.name.apply(sanitize_name)\n",
    "nba_player_list.display_first_last = nba_player_list.display_first_last.apply(sanitize_name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-31T16:25:11.162786Z",
     "start_time": "2019-05-31T16:25:11.142826Z"
    }
   },
   "outputs": [],
   "source": [
    "# Handle a few special cases where names are different across two data sources\n",
    "espn_player_list.loc[espn_player_list.espn_player_id == '1713', 'name'] = 'nene'\n",
    "espn_player_list.loc[espn_player_list.espn_player_id == '4017839', 'name'] = 'juancho hernangomez'\n",
    "espn_player_list.loc[espn_player_list.espn_player_id == '3056247', 'name'] = 'kendrick nunn'\n",
    "espn_player_list.loc[espn_player_list.espn_player_id == '2528586', 'name'] = 'walter lemon'\n",
    "espn_player_list.loc[espn_player_list.espn_player_id == '3133602', 'name'] = 'svi mykhailiuk'\n",
    "espn_player_list.loc[espn_player_list.espn_player_id == '4066508', 'name'] = 'charles brown'\n",
    "espn_player_list.loc[espn_player_list.espn_player_id == '4395627', 'name'] = 'cameron reddish'\n",
    "espn_player_list.loc[espn_player_list.espn_player_id == '4395627', 'name'] = 'cameron reddish'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-31T16:25:11.295740Z",
     "start_time": "2019-05-31T16:25:11.288549Z"
    }
   },
   "outputs": [],
   "source": [
    "merged_player_list = espn_player_list.join(nba_player_list.set_index(\"display_first_last\", drop=True),\n",
    "                                    on='name')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-31T16:25:11.444723Z",
     "start_time": "2019-05-31T16:25:11.431942Z"
    },
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>position</th>\n",
       "      <th>espn_player_id</th>\n",
       "      <th>url</th>\n",
       "      <th>person_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>175</th>\n",
       "      <td>cody demps</td>\n",
       "      <td>F</td>\n",
       "      <td>4028211</td>\n",
       "      <td>http://www.espn.com/nba/player/_/id/4028211/co...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>481</th>\n",
       "      <td>tyler cook</td>\n",
       "      <td>F</td>\n",
       "      <td>4066367</td>\n",
       "      <td>http://www.espn.com/nba/player/_/id/4066367/ty...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           name position espn_player_id  \\\n",
       "175  cody demps        F        4028211   \n",
       "481  tyler cook        F        4066367   \n",
       "\n",
       "                                                   url  person_id  \n",
       "175  http://www.espn.com/nba/player/_/id/4028211/co...        NaN  \n",
       "481  http://www.espn.com/nba/player/_/id/4066367/ty...        NaN  "
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check for rows that didn't join correctly\n",
    "merged_player_list[merged_player_list.isnull().any(axis=1)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-31T16:25:11.594092Z",
     "start_time": "2019-05-31T16:25:11.583925Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_player_list.position.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-31T16:25:11.729650Z",
     "start_time": "2019-05-31T16:25:11.721169Z"
    }
   },
   "outputs": [],
   "source": [
    "# Tyler Cook and Cody Demps just got traded and \n",
    "# has not played any games in his career, so we're dropping them\n",
    "merged_player_list = merged_player_list.dropna()\n",
    "merged_player_list = merged_player_list.rename(columns={\"person_id\":\"nba_id\", 'position':'espn_position'})\n",
    "merged_player_list.nba_id = merged_player_list.nba_id.astype('int')\n",
    "merged_player_list = merged_player_list[['name','espn_position','nba_id']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-31T16:25:12.010850Z",
     "start_time": "2019-05-31T16:25:12.000116Z"
    },
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>espn_position</th>\n",
       "      <th>nba_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>jaylen brown</td>\n",
       "      <td>SG</td>\n",
       "      <td>1627759</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>carsen edwards</td>\n",
       "      <td>PG</td>\n",
       "      <td>1629035</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>tacko fall</td>\n",
       "      <td>C</td>\n",
       "      <td>1629605</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>jonathan gibson</td>\n",
       "      <td>PG</td>\n",
       "      <td>1626780</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>javonte green</td>\n",
       "      <td>SG</td>\n",
       "      <td>1629750</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              name espn_position   nba_id\n",
       "0     jaylen brown            SG  1627759\n",
       "1   carsen edwards            PG  1629035\n",
       "2       tacko fall             C  1629605\n",
       "3  jonathan gibson            PG  1626780\n",
       "4    javonte green            SG  1629750"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_player_list.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Check NA for final player information df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "name             0\n",
       "espn_position    0\n",
       "nba_id           0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_player_list.isna().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Getting Player Performmance Data\n",
    "\n",
    "Data source: NBA official API\n",
    "\n",
    "The data includes the following:\n",
    "    - Position on the court where the shot was take\n",
    "    - Shooting accuracy and frequency stats\n",
    "    - Blocking accuracy and frequency stats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total of 557 unique IDs\n"
     ]
    }
   ],
   "source": [
    "# Getting unique set of player_ids to iterate over\n",
    "nba_ids = merged_player_list.nba_id.unique()\n",
    "\n",
    "print(\"Total of {} unique IDs\".format(nba_ids.shape[0]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-31T21:15:37.082423Z",
     "start_time": "2019-05-31T20:44:55.410704Z"
    },
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 557/557 [37:15<00:00,  3.42s/it]\n"
     ]
    }
   ],
   "source": [
    "dfs_to_concat = []\n",
    "for player_id in tqdm(nba_ids):\n",
    "    player_id = str(int(player_id))\n",
    "    sleep(1) # Prevents from being banned\n",
    "    try: \n",
    "        player_stat = get_player_stat(player_id)\n",
    "        dfs_to_concat.append(player_stat)\n",
    "    except NoDataError:\n",
    "        # This is when the player has no data and returns an empty df\n",
    "        # print(\"No data for player id\", idx)\n",
    "        continue"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-31T21:43:04.814959Z",
     "start_time": "2019-05-31T21:43:04.138711Z"
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "player_performance = pd.concat(dfs_to_concat, sort=False)\n",
    "\n",
    "player_performance.index.name = \"nba_id\"\n",
    "player_performance.index = player_performance.index.astype('str') \n",
    "\n",
    "merged_player_list = merged_player_list.set_index('nba_id')\n",
    "merged_player_list.index = merged_player_list.index.astype('str')\n",
    "\n",
    "player_performance = player_performance.join(merged_player_list2[['espn_position']])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-31T21:57:00.010664Z",
     "start_time": "2019-05-31T21:56:59.986222Z"
    },
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>shot_res</th>\n",
       "      <th>shot_in_paint</th>\n",
       "      <th>shot_mid_range</th>\n",
       "      <th>shot_lcorner_3</th>\n",
       "      <th>shot_rcorner_3</th>\n",
       "      <th>shot_above_3</th>\n",
       "      <th>fga</th>\n",
       "      <th>block_res</th>\n",
       "      <th>block_in_paint</th>\n",
       "      <th>block_mid_range</th>\n",
       "      <th>block_lcorner_3</th>\n",
       "      <th>block_rcorner_3</th>\n",
       "      <th>block_above_3</th>\n",
       "      <th>blka</th>\n",
       "      <th>oreb</th>\n",
       "      <th>dreb</th>\n",
       "      <th>ast</th>\n",
       "      <th>stl</th>\n",
       "      <th>min</th>\n",
       "      <th>espn_position</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>nba_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>101106</th>\n",
       "      <td>0.666666</td>\n",
       "      <td>0.277778</td>\n",
       "      <td>0.0555555</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>36</td>\n",
       "      <td>0</td>\n",
       "      <td>0.99999</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>10.1</td>\n",
       "      <td>26.9</td>\n",
       "      <td>7.2</td>\n",
       "      <td>2.0</td>\n",
       "      <td>350.758333</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101107</th>\n",
       "      <td>0.18894</td>\n",
       "      <td>0.173579</td>\n",
       "      <td>0.0522273</td>\n",
       "      <td>0.132104</td>\n",
       "      <td>0.06298</td>\n",
       "      <td>0.390169</td>\n",
       "      <td>651</td>\n",
       "      <td>0.619047</td>\n",
       "      <td>0.238095</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.047619</td>\n",
       "      <td>0.095238</td>\n",
       "      <td>21</td>\n",
       "      <td>3.5</td>\n",
       "      <td>14.5</td>\n",
       "      <td>4.4</td>\n",
       "      <td>2.9</td>\n",
       "      <td>4139.115000</td>\n",
       "      <td>PF</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101108</th>\n",
       "      <td>0.100418</td>\n",
       "      <td>0.211994</td>\n",
       "      <td>0.199442</td>\n",
       "      <td>0.0306834</td>\n",
       "      <td>0.0516039</td>\n",
       "      <td>0.405858</td>\n",
       "      <td>717</td>\n",
       "      <td>0.533333</td>\n",
       "      <td>0.333333</td>\n",
       "      <td>0.0666666</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0666666</td>\n",
       "      <td>15</td>\n",
       "      <td>1.9</td>\n",
       "      <td>13.1</td>\n",
       "      <td>24.1</td>\n",
       "      <td>5.4</td>\n",
       "      <td>3703.331667</td>\n",
       "      <td>PG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101108</th>\n",
       "      <td>0.100418</td>\n",
       "      <td>0.211994</td>\n",
       "      <td>0.199442</td>\n",
       "      <td>0.0306834</td>\n",
       "      <td>0.0516039</td>\n",
       "      <td>0.405858</td>\n",
       "      <td>717</td>\n",
       "      <td>0.533333</td>\n",
       "      <td>0.333333</td>\n",
       "      <td>0.0666666</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0666666</td>\n",
       "      <td>15</td>\n",
       "      <td>1.9</td>\n",
       "      <td>13.1</td>\n",
       "      <td>24.1</td>\n",
       "      <td>5.4</td>\n",
       "      <td>3703.331667</td>\n",
       "      <td>PG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101109</th>\n",
       "      <td>0.134328</td>\n",
       "      <td>0.179104</td>\n",
       "      <td>0.238806</td>\n",
       "      <td>0.0373134</td>\n",
       "      <td>0.0447761</td>\n",
       "      <td>0.365672</td>\n",
       "      <td>134</td>\n",
       "      <td>0.333332</td>\n",
       "      <td>0.333332</td>\n",
       "      <td>0.333332</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>1.4</td>\n",
       "      <td>8.4</td>\n",
       "      <td>13.7</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1744.088333</td>\n",
       "      <td>PG</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        shot_res shot_in_paint shot_mid_range shot_lcorner_3 shot_rcorner_3  \\\n",
       "nba_id                                                                        \n",
       "101106  0.666666      0.277778      0.0555555              0              0   \n",
       "101107   0.18894      0.173579      0.0522273       0.132104        0.06298   \n",
       "101108  0.100418      0.211994       0.199442      0.0306834      0.0516039   \n",
       "101108  0.100418      0.211994       0.199442      0.0306834      0.0516039   \n",
       "101109  0.134328      0.179104       0.238806      0.0373134      0.0447761   \n",
       "\n",
       "       shot_above_3  fga block_res block_in_paint block_mid_range  \\\n",
       "nba_id                                                              \n",
       "101106            0   36         0        0.99999               0   \n",
       "101107     0.390169  651  0.619047       0.238095               0   \n",
       "101108     0.405858  717  0.533333       0.333333       0.0666666   \n",
       "101108     0.405858  717  0.533333       0.333333       0.0666666   \n",
       "101109     0.365672  134  0.333332       0.333332        0.333332   \n",
       "\n",
       "       block_lcorner_3 block_rcorner_3 block_above_3  blka  oreb  dreb   ast  \\\n",
       "nba_id                                                                         \n",
       "101106               0               0             0     1  10.1  26.9   7.2   \n",
       "101107               0        0.047619      0.095238    21   3.5  14.5   4.4   \n",
       "101108               0               0     0.0666666    15   1.9  13.1  24.1   \n",
       "101108               0               0     0.0666666    15   1.9  13.1  24.1   \n",
       "101109               0               0             0     3   1.4   8.4  13.7   \n",
       "\n",
       "        stl          min espn_position  \n",
       "nba_id                                  \n",
       "101106  2.0   350.758333             C  \n",
       "101107  2.9  4139.115000            PF  \n",
       "101108  5.4  3703.331667            PG  \n",
       "101108  5.4  3703.331667            PG  \n",
       "101109  3.0  1744.088333            PG  "
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "player_performance.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Checking dtypes, length with .info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Index: 647 entries, 101106 to 2772\n",
      "Data columns (total 20 columns):\n",
      "shot_res           647 non-null object\n",
      "shot_in_paint      646 non-null object\n",
      "shot_mid_range     647 non-null object\n",
      "shot_lcorner_3     640 non-null object\n",
      "shot_rcorner_3     643 non-null object\n",
      "shot_above_3       647 non-null object\n",
      "fga                647 non-null int64\n",
      "block_res          647 non-null object\n",
      "block_in_paint     646 non-null object\n",
      "block_mid_range    647 non-null object\n",
      "block_lcorner_3    640 non-null object\n",
      "block_rcorner_3    643 non-null object\n",
      "block_above_3      647 non-null object\n",
      "blka               647 non-null int64\n",
      "oreb               647 non-null float64\n",
      "dreb               647 non-null float64\n",
      "ast                647 non-null float64\n",
      "stl                647 non-null float64\n",
      "min                647 non-null float64\n",
      "espn_position      647 non-null object\n",
      "dtypes: float64(5), int64(2), object(13)\n",
      "memory usage: 106.1+ KB\n"
     ]
    }
   ],
   "source": [
    "player_performance.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [],
   "source": [
    "#player_performance.index = player_performance.index.astype('int')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Check Missing Values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "shot_res           False\n",
       "shot_in_paint       True\n",
       "shot_mid_range     False\n",
       "shot_lcorner_3      True\n",
       "shot_rcorner_3      True\n",
       "shot_above_3       False\n",
       "fga                False\n",
       "block_res          False\n",
       "block_in_paint      True\n",
       "block_mid_range    False\n",
       "block_lcorner_3     True\n",
       "block_rcorner_3     True\n",
       "block_above_3      False\n",
       "blka               False\n",
       "oreb               False\n",
       "dreb               False\n",
       "ast                False\n",
       "stl                False\n",
       "min                False\n",
       "espn_position      False\n",
       "dtype: bool"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "player_performance.isna().any()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For players that have NA values, lets look at how many field goals (shots) they made so far this season"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {
    "scrolled": false
   },
   "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>fga</th>\n",
       "      <th>name</th>\n",
       "      <th>espn_position</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>nba_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1628505</th>\n",
       "      <td>8</td>\n",
       "      <td>troy caupain</td>\n",
       "      <td>PG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1628961</th>\n",
       "      <td>3</td>\n",
       "      <td>kostas antetokounmpo</td>\n",
       "      <td>PF</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1628961</th>\n",
       "      <td>3</td>\n",
       "      <td>kostas antetokounmpo</td>\n",
       "      <td>PF</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1628961</th>\n",
       "      <td>3</td>\n",
       "      <td>kostas antetokounmpo</td>\n",
       "      <td>PF</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1628961</th>\n",
       "      <td>3</td>\n",
       "      <td>kostas antetokounmpo</td>\n",
       "      <td>PF</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1628994</th>\n",
       "      <td>0</td>\n",
       "      <td>george king</td>\n",
       "      <td>SF</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1629055</th>\n",
       "      <td>2</td>\n",
       "      <td>donte grantham</td>\n",
       "      <td>SF</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1629116</th>\n",
       "      <td>5</td>\n",
       "      <td>angel delgado</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1629147</th>\n",
       "      <td>3</td>\n",
       "      <td>joe chealey</td>\n",
       "      <td>PG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1629147</th>\n",
       "      <td>3</td>\n",
       "      <td>joe chealey</td>\n",
       "      <td>PG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1629147</th>\n",
       "      <td>3</td>\n",
       "      <td>joe chealey</td>\n",
       "      <td>PG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1629147</th>\n",
       "      <td>3</td>\n",
       "      <td>joe chealey</td>\n",
       "      <td>PG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202700</th>\n",
       "      <td>6</td>\n",
       "      <td>donatas motiejunas</td>\n",
       "      <td>PF</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         fga                  name espn_position\n",
       "nba_id                                          \n",
       "1628505    8          troy caupain            PG\n",
       "1628961    3  kostas antetokounmpo            PF\n",
       "1628961    3  kostas antetokounmpo            PF\n",
       "1628961    3  kostas antetokounmpo            PF\n",
       "1628961    3  kostas antetokounmpo            PF\n",
       "1628994    0           george king            SF\n",
       "1629055    2        donte grantham            SF\n",
       "1629116    5         angel delgado             C\n",
       "1629147    3           joe chealey            PG\n",
       "1629147    3           joe chealey            PG\n",
       "1629147    3           joe chealey            PG\n",
       "1629147    3           joe chealey            PG\n",
       "202700     6    donatas motiejunas            PF"
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "player_performance.loc[player_performance.isnull().any(axis=1),['fga']].join(merged_player_list, how='left')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Evidently these are rookies that all took less than 10 shots so far this season. This is not enough data to make predictions. We will be dropping these players"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [],
   "source": [
    "player_performance = player_performance.dropna()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Check Duplicates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "193"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "player_performance.duplicated().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Loading all data into SQLite"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-31T16:24:56.478074Z",
     "start_time": "2019-05-31T16:24:56.438348Z"
    }
   },
   "outputs": [],
   "source": [
    "def load_data_to_db(merged_player_list, player_performance):\n",
    "    \"\"\"Loads eplayer name and position data to db\"\"\"\n",
    "    \n",
    "    \n",
    "    engine = create_engine(\"sqlite:///db/nba.db\", echo=False)\n",
    "    \n",
    "    with engine.connect() as conn:\n",
    "        merged_player_list.to_sql('players', \n",
    "                                conn, \n",
    "                                dtype={\"player_id\":Integer}, \n",
    "                                if_exists=\"replace\")  \n",
    "        \n",
    "        player_performance.to_sql('player_stats', \n",
    "                                  conn,\n",
    "                                 if_exists='replace')\n",
    "        \n",
    "        print(\"Successfully loaded.\")\n",
    "        \n",
    "        \n",
    "def read_data_from_db():\n",
    "    \"\"\"Retrieves player name and position data from db\n",
    "    \n",
    "    Returns:\n",
    "        merged_player_list: pd.DataFrame\n",
    "            df containing espn player info including \n",
    "                -  player name, \n",
    "                -  player position, \n",
    "                -  espn player id,\n",
    "                -  url to player profile\n",
    "                -  nba player id\n",
    "                \n",
    "        player_performance: pd.DataFrame\n",
    "            df containing player shooting and blocking stats\n",
    "    \"\"\"\n",
    "    \n",
    "    engine = create_engine(\"sqlite:///db/nba.db\", echo=False)\n",
    "    with engine.connect() as conn:\n",
    "        merged_player_list = pd.read_sql('players', conn)\n",
    "        player_performance = pd.read_sql('player_stats', conn)\n",
    "        \n",
    "        print(\"Successfully read in players data into dataframes\")\n",
    "        \n",
    "        return merged_player_list, player_performance"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Successfully loaded.\n"
     ]
    }
   ],
   "source": [
    "load_data_to_db(merged_player_list, player_performance)"
   ]
  },
  {
   "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.3"
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}