<jupyter_start><jupyter_text>Web Scraping NBA Player Game and Shooting Stats Using Python and BeautifulSoup This is something that I hacked together so that I can teach my oldest son SQL.  Instead of teaching him SQL on data he has no interest in, I decided that I need to get data on something he'd enjoy looking at.  Since he loves basketball and <NAME>, I figured that I try to get data on NBA player statistics from ESPN's website using a technique called "web scraping".  Web scraping is using a computer program to obtain/extract/"scrape" data from a website.Here I am using Python 3 and [BeautifulSoup](https://www.crummy.com/software/BeautifulSoup/bs4/doc/) library to scrape the data from ESPN's website.  Visit ESPN's [website](http://espn.go.com/nba/teams) to get a feel of what's available.  Take a look at a specific team's stats.  For example, here is the [page](http://espn.go.com/nba/team/stats/_/name/cle/cleveland-cavaliers) for the Cleveland Cavaliers.I know there are better/faster libraries for scraping web data like lxml.  But with lxml, I didn't want to have to get up to speed with XPath and XSLT.  With BeautifulSoup, I can get up and running quickly since I didn't have to learn a few more different APIs and it is just "good enough" for my purpose dealing with relatively small data sets.I re-wrote these scripts using the [requests](http://docs.python-requests.org) library.  So you'll need to install requests and lxml since I am using lxml as the parsing engine with BeautifulSoup.Before you venture into the world of web scraping, I would recommend reading this [article](http://robertorocha.info/on-the-ethics-of-web-scraping/) on ethical web scraping.  Quick Links - [source code for scraping player game stats](game_stats)- [source code for scraping player shooting stats](shooting_stats)- [querying the NBA sqlite database using db.py](db_py)  Below are the SQL statements needed to create tables that will be used to store the player stats **In this example, I am using sqlite3 database.**<jupyter_code>CREATE TABLE "player_game_stats" (
    "id" INTEGER PRIMARY KEY NOT NULL,
    "name_pos" TEXT NOT NULL,
    "team_name" TEXT NOT NULL,
    "GP" INTEGER NOT NULL,
    "GS" INTEGER NOT NULL,
    "MIN" REAL NOT NULL,
    "PPG" REAL NOT NULL,
    "OFFR" REAL NOT NULL,
    "DEFR" REAL NOT NULL,
    "RPG" REAL NOT NULL,
    "APG" REAL NOT NULL,
    "SPG" REAL NOT NULL,
    "BPG" REAL NOT NULL,
    "TPG" REAL NOT NULL,
    "FPG" REAL NOT NULL,
    "A2TO" REAL NOT NULL,
    "PER" REAL NOT NULL
);

CREATE TABLE "player_shooting_stats" (
    "id" INTEGER PRIMARY KEY NOT NULL,
    "name_pos" TEXT NOT NULL,
    "team_name" TEXT NOT NULL,
    "FGM" REAL NOT NULL,
    "FGA" REAL NOT NULL,
    "FG_Perc" REAL NOT NULL,
    "3PM" REAL NOT NULL,
    "3PA" REAL NOT NULL,
    "3P_Perc" REAL NOT NULL,
    "FTM" REAL NOT NULL,
    "FTA" REAL NOT NULL,
    "FT_Perc" REAL NOT NULL,
    "2PM" REAL NOT NULL,
    "2PA" REAL NOT NULL,
    "2P_Perc" REAL NOT NULL,
    "PPS" REAL NOT NULL,
    "AFG_Perc" REAL NOT NULL
);<jupyter_output><empty_output><jupyter_text>Let's begin by examining what's available at ESPN's website<jupyter_code>import requests
from bs4 import BeautifulSoup
import re

base_url = 'http://espn.go.com'

teams_url = 'http://espn.go.com/nba/teams'
html_teams = requests.get(teams_url)

soup_teams = BeautifulSoup(html_teams.text,'lxml')<jupyter_output><empty_output><jupyter_text>Getting the URLs for all teams in the NBA<jupyter_code>urls = soup_teams.find_all(href=re.compile('/nba/teams/stats'))
urls<jupyter_output><empty_output><jupyter_text>But I want the full URLs and without the markup junk<jupyter_code>team_urls = [base_url+url['href'] for url in urls]
team_urls<jupyter_output><empty_output><jupyter_text>Ahhh much better now  The web site doesn't have team names readily available that I could easily scrape using beautifulsoup, but the team codes are available in the URLs.  But I rather get the full team names.  I'm sure I could've scraped the team names somehow, but using the dictionary was more straight-forward and easiest for me at the time.  Python dictionary to be used to create a team name column based on the team code at the end of the URLs above<jupyter_code>team_name_dict = {'bos':'Boston Celtics',
                  'bkn':'Brooklyn Nets',
                  'nyk':'New York Knicks',
                  'phi':'Philadelphia 76ers',
                  'tor':'Toronto Raptors',
                  'gsw':'Golden State Warriors',
                  'lac':'Los Angeles Clippers',
                  'lal':'Los Angeles Lakers',
                  'pho':'Phoenix Suns',
                  'sac':'Sacramento Kings',
                  'chi':'Chicago Bulls',
                  'cle':'Cleveland Cavaliers',
                  'det':'Detroit Pistons',
                  'ind':'Indiana Pacers',
                  'mil':'Milwaukee Bucks',
                  'dal':'Dallas Mavericks',
                  'hou':'Houston Rockets',
                  'mem':'Memphis Grizzlies',
                  'nor':'New Orleans Pelicans',
                  'sas':'San Antonio Spurs',
                  'atl':'Atlanta Hawks',
                  'cha':'Charlotte Hornets',
                  'mia':'Miami Heat',
                  'orl':'Orlando Magic',
                  'was':'Washington Wizards',
                  'den':'Denver Nuggets',
                  'min':'Minnesota Timberwolves',
                  'okc':'Oklahoma City Thunder',
                  'por':'Portland Trail Blazers',
                  'uth':'Utah Jazz'
                  }<jupyter_output><empty_output><jupyter_text>Getting game stats and shooting stats for a specific team (Cleveland Cavaliers)<jupyter_code>url_team = 'http://espn.go.com/nba/teams/stats?team=cle'
team_code = url_team[-3:]
html_team = requests.get(url_team)

soup_team = BeautifulSoup(html_team.text, 'lxml')

# Grab all HTML tr elements with class containing the word 'player'
roster = soup_team.find_all('tr', class_=re.compile('player'))<jupyter_output><empty_output><jupyter_text>First/top half of the web page contains the game statistics, the 2nd/bottom half contains the shooting statistics<jupyter_code># Grab the top half of the data, which contains the game stats
roster_game_stats = roster[:int(len(roster)/2)]

# Grap the bottom half of the data, which contains the shooting stats
roster_shooting_stats = roster[-int(len(roster)/2):]<jupyter_output><empty_output><jupyter_text>Let's examine the game statistics data:<jupyter_code>for player in roster_game_stats:
    print(player.get_text())<jupyter_output><NAME>, SF131340.932.50.87.28.07.02.151.383.92.31.8
<NAME>, PG131334.824.50.42.02.45.61.380.542.51.72.3
<NAME>, PF131332.117.21.49.010.41.90.770.852.02.51.0
<NAME>, C131333.09.24.25.19.31.00.460.771.12.00.9
<NAME>, PF11013.07.80.01.71.71.10.270.360.21.06.0
<NAME>, SG131326.192.168.3.11.60.80.770.310.62.11.4
<NAME>, SG13017.76.40.21.71.80.80.460.310.40.92.2
<NAME>, PG13015.55.60.21.01.22.50.620.080.91.62.7
<NAME>, SG12017.44.70.42.73.11.00.580.250.31.83.0
<NAME>, SF9010.62.90.60.91.40.60.110.220.41.41.3
<NAME>, PF505.62.80.20.20.40.60.000.200.60.01.0
<NAME>, SG703.31.00.00.40.40.10.000.140.11.01.0
<NAME>, SG504.20.40.20.40.60.00.000.000.40.60.0<jupyter_text>Let's examine the shooting statistics data:<jupyter_code>for player in roster_shooting_stats:
    print(player.get_text())<jupyter_output>LeBron James, SF11.620.5.5662.55.8.4216.89.60.719.114.7.6231.5840.63
<NAME>, PG8.819.0.4662.46.7.3564.54.90.916.412.3.5251.2910.53
<NAME>, PF5.311.6.4572.96.2.4753.64.20.852.45.4.4371.4770.58
<NAME>, C3.25.4.6000.00.0.0002.84.20.673.25.4.6001.7140.60
<NAME>, PF2.75.0.5451.83.5.5260.50.60.860.91.5.5881.5640.73
<NAME>, SG2.34.8.4841.73.8.4490.30.60.500.61.0.6151.3870.66
<NAME>, SG2.14.7.4431.74.1.4150.50.51.000.40.6.6251.3610.62
<NAME>, PG2.03.7.5420.81.7.5000.80.80.911.22.0.5771.5210.66
<NAME>, SG1.83.6.4880.71.4.4710.50.60.861.12.2.5001.3020.58
<NAME>, SF0.92.2.4000.41.1.4000.71.10.600.51.1.4001.3000.50
<NAME>, PF1.21.8.6670.40.6.6670.00.00.000.81.2.6671.5560.78
<NAME>, SG0.41.0.4290.00.0.0000.10.11.000.41.0.4291.0000.43
<NAME>, SG0.20.8.2500.00.4.0000.00.00.000.20.4.500.5000.25<jupyter_text>Now we're ready to create a list of the game stats.  I also noticed that player ID numbers are available also.<jupyter_code>players = []
for row in roster_game_stats:
    for data in row:
        players.append(data.get_text())
        
player_ids = [player.a['href'].split('/')[7] for player in roster_game_stats]<jupyter_output><empty_output><jupyter_text>Let's see what the list of player game stats looks like:<jupyter_code>for player in players:
    print(player)<jupyter_output>LeB<NAME>, SF
13
13
40.9
32.5
0.8
7.2
8.0
7.0
2.15
1.38
3.9
2.3
1.8
<NAME>, PG
13
13
34.8
24.5
0.4
2.0
2.4
5.6
1.38
0.54
2.5
1.7
2.3
<NAME>, PF
13
13
32.1
17.2
1.4
9.0
10.4
1.9
0.77
0.85
2.0
2.5
1.0
<NAME>, C
13
13
33.0
9.2
4.2
5.1
9.3
1.0
0.46
0.77
1.1
2.0
0.9
<NAME>, PF
11
0
13.0
7.8
0.0
1.7
1.7
1.1
0.27
0.36
0.2
1.0
6.0
<NAME>, SG
13
13
26.2
6.6
0.3
2.3
2.6
0.8
0.77
0.31
0.6
2.1
1.4
<NAME>, SG
13
0
17.7
6.4
0.2
1.7
1.8
0.8
0.46
0.31
0.4
0.9
2.2
<NAME>, PG
13
0
15.5
5.6
0.2
1.0
1.2
2.5
0.62
0.08
0.9
1.6
2.7
<NAME>, SG
12
0
17.4
4.7
0.4
2.7
3.1
1.0
0.58
0.25
0.3
1.8
3.0
<NAME>, SF
9
0
10.6
2.9
0.6
0.9
1.4
0.6
0.11
0.22
0.4
1.4
1.3
<NAME>, PF
5
0
5.6
2.8
0.2
0.2
0.4
0.6
0.00
0.20
0.6
0.0
1.0
<NAME>, SG
7
0
3.3
1.0
0.0
0.4
0.4
0.1
0.00
0.14
0.1
1.0
1.0
<NAME>, SG
5
0
4.2
0.4
0.2
0.4
0.6
0.0
0.00
0.00
0.4
0.6
0.0<jupyter_text>But I would like to add player ID to the list, so I will insert the ID for every 15th element:<jupyter_code>index = 0 # insert the player ID before the player's name
increment = 0
for id in player_ids:
    players.insert(index + increment, id)
    index = index + 15
    increment = increment + 1<jupyter_output><empty_output><jupyter_text>Let's see if the player IDs got added before each player's name<jupyter_code>for player in players:
    print(player)<jupyter_output>1966
<NAME>, SF
29
29
37.5
25.2
0.7
4.6
5.3
7.6
1.34
0.79
3.8
1.7
2.0
25.0
6442
<NAME>, PG
30
30
38.2
20.8
0.5
2.6
3.1
5.3
1.43
0.30
2.1
2.2
2.5
19.8
3449
<NAME>, PF
31
31
35.8
16.7
1.9
8.2
10.1
2.4
0.77
0.45
1.8
2.3
1.3
17.9
6628
<NAME>, SG
31
3
23.5
10.3
0.3
1.4
1.6
2.2
1.29
0.32
1.5
1.8
1.4
12.3
2419
<NAME>, C
26
26
24.5
9.8
2.2
4.3
6.5
1.3
0.73
0.62
1.3
2.2
1.0
17.6
6474
<NAME>, PF
32
5
27.6
9.3
3.7
4.1
7.8
0.6
0.41
0.84
1.0
2.5
0.6
16.4
510
<NAME>, SF
30
22
23.1
5.6
1.0
2.5
3.5
1.1
0.63
0.73
0.7
1.1
1.5
10.6
2489716
<NAME>, SG
17
5
20.9
4.2
0.6
1.2
1.9
2.8
0.35
0.12
1.1
2.5
2.5
6.1
2009
<NAME>, SF
15
0
10.5
4.1
0.1
1.3
1.4
0.7
0.27
0.20
0.1
1.1
11.0
14.7
558
<NAME>, SF
21
8
17.0
3.0
0.2
2.1
2.3
1.0
0.29
0.10
0.4
2.0
2.4
6.1
2528794
<NAME>, SG
27
0
10.7
2.8
0.2
0.8
1.0
0.6
0.11
0.04
0.4
1.3
1.3
6.4
1000
<NAME>, C
8
1
6.4
2.4
0.5
1.4
1.9
0.0
0.13
0.75
0.6
1.1
0.0
12.7
2489897
<NAME>, PG
8
0
8.6
[...]<jupyter_text>As you can see from the output above, the player IDs were added, but I would also like to add team name since I took the trouble making that dictionary earlier:<jupyter_code>index = 2  # insert the team name after the player's name
increment = 0
for id in player_ids:
    players.insert(index + increment, team_name_dict[team_code])
    index = index + 16  # since we added player ID, there is now a total of 16 columns. instead of 15
    increment = increment + 1<jupyter_output><empty_output><jupyter_text>Let's double-check to see team name was added<jupyter_code>for player in players:
    print(player)<jupyter_output>1966
<NAME>, SF
Cleveland Cavaliers
29
29
37.5
25.2
0.7
4.6
5.3
7.6
1.34
0.79
3.8
1.7
2.0
25.0
6442
<NAME>, PG
Cleveland Cavaliers
30
30
38.2
20.8
0.5
2.6
3.1
5.3
1.43
0.30
2.1
2.2
2.5
19.8
3449
K<NAME>, PF
Cleveland Cavaliers
31
31
35.8
16.7
1.9
8.2
10.1
2.4
0.77
0.45
1.8
2.3
1.3
17.9
6628
<NAME>, SG
Cleveland Cavaliers
31
3
23.5
10.3
0.3
1.4
1.6
2.2
1.29
0.32
1.5
1.8
1.4
12.3
2419
<NAME>, C
Cleveland Cavaliers
26
26
24.5
9.8
2.2
4.3
6.5
1.3
0.73
0.62
1.3
2.2
1.0
17.6
6474
<NAME>, PF
Cleveland Cavaliers
32
5
27.6
9.3
3.7
4.1
7.8
0.6
0.41
0.84
1.0
2.5
0.6
16.4
510
<NAME>, SF
Cleveland Cavaliers
30
22
23.1
5.6
1.0
2.5
3.5
1.1
0.63
0.73
0.7
1.1
1.5
10.6
2489716
<NAME>, SG
Cleveland Cavaliers
17
5
20.9
4.2
0.6
1.2
1.9
2.8
0.35
0.12
1.1
2.5
2.5
6.1
2009
<NAME>, SF
Cleveland Cavaliers
15
0
10.5
4.1
0.1
1.3
1.4
0.7
0.27
0.20
0.1
1.1
11.0
14.7
558
<NAME>, SF
Cleveland Cavaliers
21
8
17.0
3.0
0.2
2.1
2.3
1.0
0.29
0.10
0.4
2.[...]<jupyter_text>Great, looks like team name was added to the list.  But there is a problem.  I want to insert this data into a database.  I got this one huge list, but I need it to be structured so that the data for each player is grouped in its own list.  Searched stackoverflow for a [solution](http://stackoverflow.com/questions/312443/how-do-you-split-a-list-into-evenly-sized-chunks-in-python) and found one using a generator:<jupyter_code># http://stackoverflow.com/questions/312443/how-do-you-split-a-list-into-evenly-sized-chunks-in-python
def chunks(l, n):
    """ Yield successive n-sized chunks from l.
    """
    for i in range(0, len(l), n):
        yield l[i:i+n]<jupyter_output><empty_output><jupyter_text>Let's double-check if that worked:<jupyter_code>from pprint import pprint

for row in chunks(players,17):
    pprint(row)<jupyter_output>['1966',
 '<NAME>, SF',
 '<NAME>',
 '29',
 '29',
 '37.5',
 '25.2',
 '0.7',
 '4.6',
 '5.3',
 '7.6',
 '1.34',
 '0.79',
 '3.8',
 '1.7',
 '2.0',
 '25.0']
['6442',
 '<NAME>, PG',
 '<NAME>',
 '30',
 '30',
 '38.2',
 '20.8',
 '0.5',
 '2.6',
 '3.1',
 '5.3',
 '1.43',
 '0.30',
 '2.1',
 '2.2',
 '2.5',
 '19.8']
['3449',
 '<NAME>, PF',
 '<NAME>',
 '31',
 '31',
 '35.8',
 '16.7',
 '1.9',
 '8.2',
 '10.1',
 '2.4',
 '0.77',
 '0.45',
 '1.8',
 '2.3',
 '1.3',
 '17.9']
['6628',
 '<NAME>, SG',
 '<NAME>',
 '31',
 '3',
 '23.5',
 '10.3',
 '0.3',
 '1.4',
 '1.6',
 '2.2',
 '1.29',
 '0.32',
 '1.5',
 '1.8',
 '1.4',
 '12.3']
['2419',
 '<NAME>, C',
 '<NAME>',
 '26',
 '26',
 '24.5',
 '9.8',
 '2.2',
 '4.3',
 '6.5',
 '1.3',
 '0.73',
 '0.62',
 '1.3',
 '2.2',
 '1.0',
 '17.6']
['6474',
 '<NAME>, PF',
 '<NAME>',
 '32',
 '5',
 '27.6',
 '9.3',
 '3.7',
 '4.1',
 '7.8',
 '0.6',
 '0.41',
 '0.84',
 '1.0',
 '2.5',
 [...]<jupyter_text>Sweet!  As you can see from above, each player's data is in its own list.  Now we can just loop through the main list and insert the data from the nested lists into the sqlite database:<jupyter_code>import sqlite3

conn = sqlite3.connect('/home/pybokeh/databases/nba')
c = conn.cursor()

for row in chunks(players,17):
    try:
        c.execute('INSERT INTO player_game_stats VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', row)
    except:
        pass
    conn.commit()
conn.close()<jupyter_output><empty_output><jupyter_text>Below is all the code in one cell for getting player game stats: [[back to top](top)]<jupyter_code>import requests
from bs4 import BeautifulSoup
import sqlite3
import re

base_url = 'http://espn.go.com'

teams_url = 'http://espn.go.com/nba/teams'
html_teams = requests.get(teams_url)

soup_teams = BeautifulSoup(html_teams.text, 'lxml')

urls = soup_teams.find_all(href=re.compile('/nba/teams/stats'))

team_urls = [base_url+url['href'] for url in urls]

team_name_dict = {'bos':'Boston Celtics',
                  'bkn':'Brooklyn Nets',
                  'nyk':'New York Knicks',
                  'phi':'Philadelphia 76ers',
                  'tor':'Toronto Raptors',
                  'gsw':'Golden State Warriors',
                  'lac':'Los Angeles Clippers',
                  'lal':'Los Angeles Lakers',
                  'pho':'Phoenix Suns',
                  'sac':'Sacramento Kings',
                  'chi':'Chicago Bulls',
                  'cle':'Cleveland Cavaliers',
                  'det':'Detroit Pistons',
                  'ind':'Indiana Pacers',
                  'mil':'Milwaukee Bucks',
                  'dal':'Dallas Mavericks',
                  'hou':'Houston Rockets',
                  'mem':'Memphis Grizzlies',
                  'nor':'New Orleans Pelicans',
                  'sas':'San Antonio Spurs',
                  'atl':'Atlanta Hawks',
                  'cha':'Charlotte Hornets',
                  'mia':'Miami Heat',
                  'orl':'Orlando Magic',
                  'was':'Washington Wizards',
                  'den':'Denver Nuggets',
                  'min':'Minnesota Timberwolves',
                  'okc':'Oklahoma City Thunder',
                  'por':'Portland Trail Blazers',
                  'uth':'Utah Jazz'
                  }

# http://stackoverflow.com/questions/312443/how-do-you-split-a-list-into-evenly-sized-chunks-in-python
def chunks(l, n):
    """ Yield successive n-sized chunks from l.
    """
    for i in range(0, len(l), n):
        yield l[i:i+n]

for team in team_urls:
    team_code = team[-3:]
    html_team = requests.get(team)

    soup_team = BeautifulSoup(html_team.text, 'lxml')

    roster = soup_team.find_all('tr', class_=re.compile('player'))
    roster_game_stats = roster[:int(len(roster)/2)]
    #roster_shooting_stats = roster[-int(len(roster)/2):]
    
    players = []
    for row in roster_game_stats:
        for data in row:
            players.append(data.get_text())
        
    player_ids = [player.a['href'].split('/')[7] for player in roster_game_stats]
    
    index = 0
    increment = 0
    for id in player_ids:
        players.insert(index + increment, id)
        index = index + 15
        increment = increment + 1
        
    index = 2
    increment = 0
    for id in player_ids:
        players.insert(index + increment, team_name_dict[team_code])
        index = index + 16
        increment = increment + 1

    conn = sqlite3.connect('/home/pybokeh/databases/nba')
    c = conn.cursor()

    for row in chunks(players,17):
        try:
            c.execute('INSERT INTO player_game_stats VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', row)
        except:
            pass
        conn.commit()
    conn.close()<jupyter_output><empty_output><jupyter_text>Below is all the code in one cell for getting player shooting stats: [[back to top](top)]<jupyter_code>import requests
from bs4 import BeautifulSoup
import sqlite3
import re

base_url = 'http://espn.go.com'

teams_url = 'http://espn.go.com/nba/teams'
html_teams = requests.get(teams_url)

soup_teams = BeautifulSoup(html_teams.text, 'lxml')

urls = soup_teams.find_all(href=re.compile('/nba/teams/stats'))

team_urls = [base_url+url['href'] for url in urls]

team_name_dict = {'bos':'Boston Celtics',
                  'bkn':'Brooklyn Nets',
                  'nyk':'New York Knicks',
                  'phi':'Philadelphia 76ers',
                  'tor':'Toronto Raptors',
                  'gsw':'Golden State Warriors',
                  'lac':'Los Angeles Clippers',
                  'lal':'Los Angeles Lakers',
                  'pho':'Phoenix Suns',
                  'sac':'Sacramento Kings',
                  'chi':'Chicago Bulls',
                  'cle':'Cleveland Cavaliers',
                  'det':'Detroit Pistons',
                  'ind':'Indiana Pacers',
                  'mil':'Milwaukee Bucks',
                  'dal':'Dallas Mavericks',
                  'hou':'Houston Rockets',
                  'mem':'Memphis Grizzlies',
                  'nor':'New Orleans Pelicans',
                  'sas':'San Antonio Spurs',
                  'atl':'Atlanta Hawks',
                  'cha':'Charlotte Hornets',
                  'mia':'Miami Heat',
                  'orl':'Orlando Magic',
                  'was':'Washington Wizards',
                  'den':'Denver Nuggets',
                  'min':'Minnesota Timberwolves',
                  'okc':'Oklahoma City Thunder',
                  'por':'Portland Trail Blazers',
                  'uth':'Utah Jazz'
                  }

# http://stackoverflow.com/questions/312443/how-do-you-split-a-list-into-evenly-sized-chunks-in-python
def chunks(l, n):
    """ Yield successive n-sized chunks from l.
    """
    for i in range(0, len(l), n):
        yield l[i:i+n]

for team in team_urls:
    team_code = team[-3:]
    html_team = requests.get(team)

    soup_team = BeautifulSoup(html_team.text, 'lxml')

    roster = soup_team.find_all('tr', class_=re.compile('player'))
    #roster_game_stats = roster[:int(len(roster)/2)]
    roster_shooting_stats = roster[-int(len(roster)/2):]
    
    players = []
    for row in roster_shooting_stats:
        for data in row:
            players.append(data.get_text())
        
    player_ids = [player.a['href'].split('/')[7] for player in roster_shooting_stats]
    
    index = 0
    increment = 0
    for id in player_ids:
        players.insert(index + increment, id)
        index = index + 15
        increment = increment + 1
        
    index = 2
    increment = 0
    for id in player_ids:
        players.insert(index + increment, team_name_dict[team_code])
        index = index + 16
        increment = increment + 1

    conn = sqlite3.connect('/home/pybokeh/databases/nba')
    c = conn.cursor()

    for row in chunks(players,17):
        try:
            c.execute('INSERT INTO player_shooting_stats VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', row)
        except:
            pass
        conn.commit()
    conn.close()<jupyter_output><empty_output><jupyter_text>Below are the database table definitions used to create the player game stats table and player shooting stats table in sqlite: CREATE TABLE "player_game_stats" (    "id" INTEGER PRIMARY KEY NOT NULL,    "name_pos" TEXT NOT NULL,    "team_name" TEXT NOT NULL,    "GP" INTEGER NOT NULL,    "GS" INTEGER NOT NULL,    "MIN" REAL NOT NULL,    "PPG" REAL NOT NULL,    "OFFR" REAL NOT NULL,    "DEFR" REAL NOT NULL,    "RPG" REAL NOT NULL,    "APG" REAL NOT NULL,    "SPG" REAL NOT NULL,    "BPG" REAL NOT NULL,    "TPG" REAL NOT NULL,    "FPG" REAL NOT NULL,    "A2TO" REAL NOT NULL,    "PER" REAL NOT NULL)CREATE TABLE "player_shooting_stats" (    "id" INTEGER PRIMARY KEY NOT NULL,    "name_pos" TEXT NOT NULL,    "team_name" TEXT NOT NULL,    "FGM" REAL NOT NULL,    "FGA" REAL NOT NULL,    "FG_Perc" REAL NOT NULL,    "3PM" REAL NOT NULL,    "3PA" REAL NOT NULL,    "3P_Perc" REAL NOT NULL,    "FTM" REAL NOT NULL,    "FTA" REAL NOT NULL,    "FT_Perc" REAL NOT NULL,    "2PM" REAL NOT NULL,    "2PA" REAL NOT NULL,    "2P_Perc" REAL NOT NULL,    "PPS" REAL NOT NULL,    "AFG_Perc" REAL NOT NULL)  OK, now that I have the data in sqlite database.  Let's go dig in!  Using Yhat's [db.py](http://blog.yhathq.com/posts/introducing-db-py.html) to query the NBA sqlite database [[back to top](top)]  If you are a fan of the [pandas](http://pandas.pydata.org/) library, I would definitely check out db.py since it is tightly integrated with pandas.  Not only that, db.py's API is so nice and easy to use.<jupyter_code>from db import DB
import pandas as pd

db = DB(filename="/home/pybokeh/databases/nba", dbtype="sqlite")<jupyter_output>Indexing schema. This will take a second...finished!
Refreshing schema. Please wait...done!<jupyter_text>What if you have no idea what's in this sqlite database?  Like what tables does it contain? First, let's see what functions are available in db?  Let's use Python's built-in dir() method:<jupyter_code>dir(db)<jupyter_output><empty_output><jupyter_text>Hmmmm, "tables" look like something I may be interested in.  Let's check it out.<jupyter_code>help(db.tables)
db.tables<jupyter_output><empty_output><jupyter_text>Nice!  db.tables tells me what tables are inside the sqlite database.  Let's now query the database.  But first, let's configure IPython notebook display options so that we can make sure we can view our results.<jupyter_code>pd.set_option("display.max_columns",50)
pd.set_option("display.max_rows",999)<jupyter_output><empty_output><jupyter_text>Now, let's return rows from our game stats table for just Cleveland Cavaliers players and order the results by PPG (points per game) in descending order.  Check the ESPN [site](http://espn.go.com/nba/team/stats/_/name/cle/cleveland-cavaliers) and see if results are similar.  If they played games since I last ran the script, obviously things will be different.<jupyter_code>db.query("select * from player_game_stats where team_name like '%Cav%' order by PPG desc;")<jupyter_output><empty_output><jupyter_text>Now, let's do the same for the player shooting stats table and order the results by FG_Perc (field goal percentage) in descending order.<jupyter_code>db.query("select * from player_shooting_stats where team_name like '%Cav%' order by FG_Perc desc;")
type(db.query("select * from player_game_stats limit 5;"))<jupyter_output><empty_output><jupyter_text>The resultset of the query is a Pandas dataframe!  Very nice!<jupyter_code>df = db.query("select * from player_game_stats where team_name like '%Cav%' order by PPG desc;")
df
df.head() # grab just the top 5 rows<jupyter_output><empty_output><jupyter_text>On average, how many minutes do the Cav players play per game?<jupyter_code>df.MIN.mean()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1422415512907473, 'fraction_numerical': 0.09280612855603879, 'mean_word_length': 2.787246902387428, 'pattern_counts': {'":': 0, '<': 180, '<?xml version=': 0, '>': 180, 'https://': 1, 'lorem ipsum': 0, 'www.': 1, 'xml': 10}, 'pii_count': 82, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '28', 'dir': 'jupyter-structured-clean-dedup', 'id': '132553', 'license': 'MIT', 'n_tokens_mistral': 10944, 'n_tokens_neox': 9666, 'n_words': 2705, 'path': 'web_scraping/NBA_Player_Stats.ipynb', 'repo_name': 'manual123/Nacho-Jupyter-Notebooks', 'size': '25602'}