boxscoreScrape.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Basketball-Reference Game Log Data Scraper\n",
    "_To run any of the sections of code, click into the section, and press [Ctrl]+[Enter]_  \n",
    "__Note: In order for this code to function correctly, it must be executed chronologically, starting at the top__"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Install/Import required Python Libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: requests in c:\\python37\\lib\\site-packages (2.25.1)"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "WARNING: You are using pip version 20.3.3; however, version 21.0.1 is available.\n",
      "You should consider upgrading via the 'c:\\python37\\python.exe -m pip install --upgrade pip' command.\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Requirement already satisfied: chardet<5,>=3.0.2 in c:\\python37\\lib\\site-packages (from requests) (4.0.0)\n",
      "Requirement already satisfied: certifi>=2017.4.17 in c:\\python37\\lib\\site-packages (from requests) (2020.12.5)\n",
      "Requirement already satisfied: idna<3,>=2.5 in c:\\python37\\lib\\site-packages (from requests) (2.10)\n",
      "Requirement already satisfied: urllib3<1.27,>=1.21.1 in c:\\python37\\lib\\site-packages (from requests) (1.26.2)\n",
      "Requirement already satisfied: BeautifulSoup4 in c:\\python37\\lib\\site-packages (4.9.3)\n",
      "Requirement already satisfied: soupsieve>1.2 in c:\\python37\\lib\\site-packages (from BeautifulSoup4) (2.1)\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "WARNING: You are using pip version 20.3.3; however, version 21.0.1 is available.\n",
      "You should consider upgrading via the 'c:\\python37\\python.exe -m pip install --upgrade pip' command.\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: pandas in c:\\python37\\lib\\site-packages (1.2.0)\n",
      "Requirement already satisfied: python-dateutil>=2.7.3 in c:\\python37\\lib\\site-packages (from pandas) (2.8.1)\n",
      "Requirement already satisfied: numpy>=1.16.5 in c:\\python37\\lib\\site-packages (from pandas) (1.19.5)\n",
      "Requirement already satisfied: pytz>=2017.3 in c:\\python37\\lib\\site-packages (from pandas) (2020.5)\n",
      "Requirement already satisfied: six>=1.5 in c:\\python37\\lib\\site-packages (from python-dateutil>=2.7.3->pandas) (1.15.0)\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "WARNING: You are using pip version 20.3.3; however, version 21.0.1 is available.\n",
      "You should consider upgrading via the 'c:\\python37\\python.exe -m pip install --upgrade pip' command.\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: tqdm in c:\\python37\\lib\\site-packages (4.55.0)"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "WARNING: You are using pip version 20.3.3; however, version 21.0.1 is available.\n",
      "You should consider upgrading via the 'c:\\python37\\python.exe -m pip install --upgrade pip' command.\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Requirement already satisfied: numpy in c:\\python37\\lib\\site-packages (1.19.5)\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "WARNING: You are using pip version 20.3.3; however, version 21.0.1 is available.\n",
      "You should consider upgrading via the 'c:\\python37\\python.exe -m pip install --upgrade pip' command.\n"
     ]
    }
   ],
   "source": [
    "#If necessary, install BeautifulSoup4 and Pandas Packages:\n",
    "!pip install requests\n",
    "!pip install BeautifulSoup4\n",
    "!pip install pandas\n",
    "!pip install tqdm\n",
    "!pip install numpy"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First use Kernel [3] to import all of the required python libraries for this project.  \n",
    "***\n",
    "If any library does not load, uncomment (remove the # before) the associated '!pip install' command in Kernel [1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "from bs4 import BeautifulSoup\n",
    "import pandas as pd\n",
    "import os\n",
    "import unicodedata\n",
    "import numpy\n",
    "from tqdm import tqdm\n",
    "from time import sleep"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Select the years/months that you want to pull game data from:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      "----Input the Start Year:   2021\n",
      "----Input the Final Year:   2021\n"
     ]
    }
   ],
   "source": [
    "#Select the year you want to pull data from - only limited to single years for the time being.\n",
    "start_year = input('----Input the Start Year:  ')\n",
    "stop_year = input('----Input the Final Year:  ')\n",
    "years = range(int(start_year),int(stop_year)+1)\n",
    "\n",
    "#Comment out any months you DO NOT want game data pulled from:\n",
    "months = [\n",
    "'october', \n",
    "'november',\n",
    "'december',\n",
    "'january',\n",
    "'february',\n",
    "'march',\n",
    "'april',\n",
    "'may',\n",
    "'june',\n",
    "'july',\n",
    "'august',\n",
    "'september'\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "def make_soup(url):\n",
    "    page = requests.get(url)\n",
    "    soup = BeautifulSoup(page.text, 'html.parser')\n",
    "    return soup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Link Creation Progress: 100%|██████████████████████████████████████████████████████████| 12/12 [00:03<00:00,  3.20it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Links successfully created\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    }
   ],
   "source": [
    "links = []\n",
    "links_tmp = []\n",
    "with tqdm(total=len(months)*len(years),desc='Link Creation Progress') as pbar:\n",
    "    for year in years:\n",
    "        for month in months:\n",
    "            soup = make_soup('https://www.basketball-reference.com/leagues/NBA_'+str(year)+'_games-'+month+'.html')\n",
    "            links_tmp = [\"https://www.basketball-reference.com\" + game['href'] for game in soup.find_all('a', text='Box Score')]\n",
    "            for link in links_tmp:\n",
    "                links.append(link)\n",
    "            pbar.update(1)\n",
    "\n",
    "print('Links successfully created')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Scraping the Box Score Data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Scraping Progress: 100%|█████████████████████████████████████████████████████████████| 399/399 [04:13<00:00,  1.58it/s]\n"
     ]
    }
   ],
   "source": [
    "stats = []\n",
    "with tqdm(total=len(links),desc='Scraping Progress') as pbar:\n",
    "    for link in range(len(links)):\n",
    "    #for link in range():\n",
    "        tmp = links[link]\n",
    "        page = requests.get(tmp)\n",
    "        soup = BeautifulSoup(page.text, 'html.parser')\n",
    "\n",
    "        teams = [game['href'] for game in soup.find_all('a', {'itemprop': 'name'})]\n",
    "        teams = [teams[0][7:10], teams[1][7:10]]\n",
    "\n",
    "        stats_tmp = []\n",
    "        for team in range(len(teams)):\n",
    "            basic_stats = soup.find('table',{'id': 'box-'+teams[team]+'-game-basic'})\n",
    "            adv_stats = soup.find('table',{'id': 'box-'+teams[team]+'-game-advanced'})\n",
    "\n",
    "            #Player Names\n",
    "            player_names = [[th.getText() for th in basic_stats.findAll('tr')[1:][i].findAll('th')] for i in range(len(basic_stats.findAll('tr')[1:]))]\n",
    "            player_names = player_names[1:6] + player_names[7:-1]\n",
    "            df_player_names = pd.DataFrame(player_names)\n",
    "\n",
    "            #Starters\n",
    "            starters = ['Y','Y','Y','Y','Y'] + ['N']*(len(player_names)-5)\n",
    "            df_starters = pd.DataFrame(starters)\n",
    "            \n",
    "            #Player Basic Stats\n",
    "            team_stats_basic = [[td.getText() for td in basic_stats.findAll('tr')[1:][i].findAll('td')]\n",
    "                    for i in range(len(basic_stats.findAll('tr')[1:]))]\n",
    "            team_stats_basic = team_stats_basic[1:6] + team_stats_basic[7:-1]\n",
    "            df_team_stats_basic = pd.DataFrame(team_stats_basic)\n",
    "\n",
    "            #Player Advanced Stats\n",
    "            team_stats_adv = [[td.getText() for td in adv_stats.findAll('tr')[1:][i].findAll('td')]\n",
    "                    for i in range(len(adv_stats.findAll('tr')[1:]))]\n",
    "            team_stats_adv = team_stats_adv[1:6] + team_stats_adv[7:-1]\n",
    "            df_team_stats_adv = pd.DataFrame(team_stats_adv)\n",
    "\n",
    "            #Game ID\n",
    "            game_id = links[link][47:59]\n",
    "            game_id = [game_id for i in range(len(player_names))]\n",
    "            df_game_id = pd.DataFrame(game_id)\n",
    "            \n",
    "            #Opponent ID\n",
    "            if team == 0:\n",
    "                opponent = teams[1]\n",
    "            else:\n",
    "                opponent = teams[0]\n",
    "            opp_name = [opponent for i in range(len(player_names))]\n",
    "            df_opp_name = pd.DataFrame(opp_name)\n",
    "            \n",
    "            #Team ID\n",
    "            team_name = [teams[team] for i in range(len(player_names))]\n",
    "            df_team_name = pd.DataFrame(team_name)\n",
    "\n",
    "            #Consolidating the Data:\n",
    "            stats_tmp = pd.concat([df_game_id,df_team_name,df_opp_name, df_player_names,df_starters,df_team_stats_basic,df_team_stats_adv],axis=1)\n",
    "            stats.append(stats_tmp)\n",
    "        pbar.update(1)\n",
    "    \n",
    "    output_raw = pd.concat(stats)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data Post-Processing:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "output = output_raw\n",
    "#Add a header to the Data\n",
    "header = ['Game_ID','Team','Opponent','Player','Starter','Min','FG','FGA','FG%','3P','3PA','3P%','FT','FTA','FT%','ORB','DRB','TRB','AST','STL','BLK','TOV','PF','PTS','P/M',\n",
    "          'del','TS%','eFG%','3PAr', 'FTr','ORB%','DRB%','TRB%','AST%','STL%','BLK%','TOV%','USG%','ORtg','DRtg','BPM']\n",
    "output.columns = header\n",
    "\n",
    "#Index Column - Reset IDs\n",
    "output = output.reset_index(drop=True)\n",
    "\n",
    "#Player Column - Replace any special characters with the 26 basic alphabetic characters.\n",
    "output['Player'] = output['Player'].str.normalize('NFKD').str.encode('ascii', errors='ignore').str.decode('utf-8')\n",
    "\n",
    "#MP - Convert to decimal\n",
    "for row in range(len(output['Min'])):\n",
    "    if \":\" in output['Min'][row]:\n",
    "        output['Min'][row] = round(float(output['Min'][row].split(':')[0]+str(int(output['Min'][row].split(':')[1])/60)[1:]),2)\n",
    "    else:\n",
    "        output['Min'][row] = 0\n",
    "\n",
    "#Delete redundant Minutes Played (del) column:\n",
    "output = output.drop(['del'],axis=1)\n",
    "\n",
    "#Convert statistical columns to float type in order to calculate fantasy points\n",
    "output['PTS'] = pd.to_numeric(output['PTS'],downcast='float')\n",
    "output['3P'] = pd.to_numeric(output['3P'],downcast='float')\n",
    "output['TRB'] = pd.to_numeric(output['TRB'],downcast='float')\n",
    "output['AST'] = pd.to_numeric(output['AST'],downcast='float')\n",
    "output['STL'] = pd.to_numeric(output['STL'],downcast='float')\n",
    "output['BLK'] = pd.to_numeric(output['BLK'],downcast='float')\n",
    "output['TOV'] = pd.to_numeric(output['TOV'],downcast='float')\n",
    "\n",
    "#Dealing with Double Doubles/Triple Doubles \n",
    "isTen = lambda x:int(x>=10)\n",
    "countTens = lambda row: numpy.clip(isTen(row['PTS']) + isTen(row['TRB']) + isTen(row['AST']) + isTen(row['STL']) + isTen(row['BLK']),1,3)-1\n",
    "output['DDBonus'] = output.apply(countTens,axis=1)\n",
    "\n",
    "#Calculating Fantasy Points\n",
    "output['Fp'] = 1*output['PTS'] + 0.5*output['3P'] + 1.25*output['TRB'] + 1.5*output['AST'] + 2*output['STL'] + 2*output['BLK'] - 0.5*output['TOV'] + 1.5*output['DDBonus']\n",
    "\n",
    "#Calculating Fantasy Points per Minute\n",
    "FpMin = []\n",
    "for row in range(len(output['Min'])):\n",
    "    if output['Min'][row] == 0:\n",
    "        FpMin.append(0)\n",
    "    else:\n",
    "        FpMin.append(round(output['Fp'][row] / output['Min'][row],3))\n",
    "output['Fp/Min'] = FpMin\n",
    "        \n",
    "#Replace any blank cells with 0\n",
    "output = output.fillna(0)\n",
    "output = output.replace('',0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If preferred, run the kernel below to create a temporary .csv file in Binder"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "output.to_csv('2020stats.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Autoexport to MySQL Database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: config in c:\\python37\\lib\\site-packages (0.5.0.post0)"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "WARNING: You are using pip version 20.3.3; however, version 21.0 is available.\n",
      "You should consider upgrading via the 'c:\\python37\\python.exe -m pip install --upgrade pip' command.\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Requirement already satisfied: mysql in c:\\python37\\lib\\site-packages (0.0.2)\n",
      "Requirement already satisfied: mysqlclient in c:\\python37\\lib\\site-packages (from mysql) (2.0.3)\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "WARNING: You are using pip version 20.3.3; however, version 21.0 is available.\n",
      "You should consider upgrading via the 'c:\\python37\\python.exe -m pip install --upgrade pip' command.\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Collecting mysql.connector\n",
      "  Downloading mysql-connector-2.2.9.tar.gz (11.9 MB)\n",
      "Using legacy 'setup.py install' for mysql.connector, since package 'wheel' is not installed.\n",
      "Installing collected packages: mysql.connector\n",
      "    Running setup.py install for mysql.connector: started\n",
      "    Running setup.py install for mysql.connector: finished with status 'done'\n",
      "Successfully installed mysql.connector\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "WARNING: You are using pip version 20.3.3; however, version 21.0 is available.\n",
      "You should consider upgrading via the 'c:\\python37\\python.exe -m pip install --upgrade pip' command.\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: sqlalchemy in c:\\python37\\lib\\site-packages (1.3.22)\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "WARNING: You are using pip version 20.3.3; however, version 21.0 is available.\n",
      "You should consider upgrading via the 'c:\\python37\\python.exe -m pip install --upgrade pip' command.\n"
     ]
    }
   ],
   "source": [
    "!pip install config\n",
    "!pip install mysql\n",
    "!pip install mysql.connector\n",
    "!pip install sqlalchemy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<mysql.connector.connection.MySQLConnection object at 0x0000019C8E2BE128>\n"
     ]
    }
   ],
   "source": [
    "import config\n",
    "import mysql.connector \n",
    "from mysql.connector import errorcode\n",
    "from sqlalchemy import create_engine\n",
    "\n",
    "cnx = mysql.connector.connect(\n",
    "        host = config.host,\n",
    "        user = config.user,\n",
    "        password = config.passwd)\n",
    "print(cnx)\n",
    "\n",
    "cursor = cnx.cursor()\n",
    "#insert Database Name\n",
    "db_name = 'tjprzzy-nba-stats'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Database tjprzzy-nba-stats does not exists.\n",
      "1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-nba-stats' at line 1\n"
     ]
    }
   ],
   "source": [
    "def create_database(cursor, database):\n",
    "    try:\n",
    "        cursor.execute(\n",
    "            \"CREATE DATABASE {} DEFAULT CHARACTER SET 'utf8'\".format(database))\n",
    "    except mysql.connector.Error as err:\n",
    "        print(\"Failed creating database: {}\".format(err))\n",
    "        exit(1)\n",
    "try:\n",
    "    cursor.execute(\"USE {}\".format(db_name))\n",
    "except mysql.connector.Error as err:\n",
    "    print(\"Database {} does not exists.\".format(db_name))\n",
    "    if err.errno == errorcode.ER_BAD_DB_ERROR:\n",
    "        create_database(cursor, db_name)\n",
    "        print(\"Database {} created successfully.\".format(db_name))\n",
    "        cnx.database = db_name\n",
    "    else:\n",
    "        print(err)\n",
    "        exit(1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Autoexport to Google Sheets"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In order to access Google Sheets, we need credentials access.\n",
    "\n",
    "Now the tricky part, getting credentials access. In order to use Python we need to get some form of key from Google. This key is mainly for security purposes and will be in the format of a JSON file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "#!pip install gspread\n",
    "#!pip install gspread_dataframe\n",
    "\n",
    "import gspread\n",
    "from gspread_dataframe import set_with_dataframe\n",
    "\n",
    "gc = gspread.oauth()\n",
    "sh = gc.open_by_key('1ZmvBC3wiXHROO-I2aovyW2zF9t_z-gan4oDqRP0wSHE')\n",
    "worksheet = sh.get_worksheet(0)\n",
    "\n",
    "range_of_cells = worksheet.range('A1:AN50000') #-> Select the range you want to clear\n",
    "for cell in range_of_cells:\n",
    "    cell.value = ''\n",
    "set_with_dataframe(worksheet, output)"
   ]
  }
 ],
 "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.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}