<jupyter_start><jupyter_text>Extracting player info and saving into SQL Database This file creates a database in sqlite by pulling out information for female professional tennis players from 2010 - 2020. More specifically, this database largely relies on data from www.tennisexplorer.com to extract match, injury, and rank history. In addition, basic demographic data was recorded for each player when present. Data was pulled specifically from the top 200 players from 2010 - 2020.__The following tables will be created in this database:__* __Players__ (name, birthday, current/highest singles and doubles ranking, hand, sex, height, and weight)* __Injury_Record__ (name_id,start_date,end_date, tournament,reason )* __Match_Record__ (name_id, year, tournament, match_type, date, opponent, round, result, h ,a)* __Ranks__ (name_id, year, ranktype, rank* __Court_Type__ (Tournament, Court Type)<jupyter_code># Loading Required Packages
import pprint
from bs4 import BeautifulSoup
import requests
from urllib import request, response, error, parse
from urllib.request import urlopen
import time
import pandas as pd
import random
import os
import sqlite3
import random
# Change the Working Directory
os.chdir("/Users/Owner/Desktop/InsightFellows/Daniella_Patton_Insight_Project/Raw_Data")
# connecting to sqlite
conn = sqlite3.connect('tennis_players.sqlite')
cur = conn.cursor()<jupyter_output><empty_output><jupyter_text>Basic Data Table<jupyter_code># Makeing the Players Table
cur.executescript('''
DROP TABLE IF EXISTS Players;

CREATE TABLE Players (
    id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    name    TEXT UNIQUE,
    birthday    TEXT,
    country    TEXT,
    singles_curr_rank    INTEGER,
    singles_high_rank    INTEGER,
    doubles_curr_rank    INTEGER,
    doubles_high_rank    INTEGER,
    hand    TEXT,
    sex    TEXT,
    weight    TEXT,
    height    TEXT
);

''')<jupyter_output><empty_output><jupyter_text>Basic Data Function: For each player, the following basic information is saved:Age, height, weight, birthday, country, singles/doubles ranking, year __Input__ - The web-page from www.tennisexplorer.com and year of interest__Output__ - Adding rows to the Player sqlite table<jupyter_code>def basic_data(soup, year):
    x = 0
    name = soup.title # name
    name = name.text[:-18]
    #names.append(name.text[:-18])
    for i, table in enumerate(soup.select("table")):
        # print(i, table['class'])
        # Basic Data
        if table["class"] == ["plDetail"]:
            rows = table.findAll("div", {"class": "date"})
            for row in rows:
                row = row.text
                x = x + 1
                #print(x)
                # Country
                if "Country" in row:
                    country = row[9:] # country
                # Birthday
                if "Age" in row:
                    birthday = row
                    birthday = birthday[birthday.find("(") + 1 : birthday.find(")")] # birthday
                # Singles Rankings
                if "Current/Highest rank - singles" in row:
                    sin_ranks = row
                    cur_rank = sin_ranks.split(" / ")[0]
                    cur_rank_singles = cur_rank.split("singles: ")[1] # singles_curr_rank
                    if cur_rank_singles == '-':
                        cur_rank_singles = None
                    else: 
                        cur_rank_singles = int(float(cur_rank_singles))    
                    hi_rank_singles = float(sin_ranks.split(" / ")[1]) # singles_high_rank
                # Doubles Ranking
                if "Current/Highest rank - doubles" in row:
                    dub_ranks = row
                    cur_rank = dub_ranks.split(" / ")[0]
                    cur_rank_dubs = cur_rank.split("doubles: ")[1] # doubles_curr_rank
                    if cur_rank_dubs == '-':
                        cur_rank_dubs = None
                    else: 
                        cur_rank_dubs = int(float(cur_rank_dubs))  
                    hi_rank_dubs = int(float(dub_ranks.split(" / ")[1])) # doubles_high_rank
                # Sex
                if "Sex" in row:
                    s = row.split("Sex: ")
                    sex = s[1] # sex
                # Handed
                if "Plays" in row:
                    h = row
                    hand = h.split("Plays: ")[1] # hand
                # Height & Weight
                if "Height / Weight" in row:
                    hw = row[17:]
                    height = str(hw.split(" / ")[0]) # height
                    weight = str(hw.split(" / ")[1]) # weight       
                elif "Weight" in row:
                    weight = str(row[8:]) # weight
              
            try:
                height
            except NameError:
                height = None
            try:
                weight
            except NameError:
                weight = None
            try:
                cur_rank_dubs
            except NameError:
                cur_rank_dubs = None
            try:
                hi_rank_dubs
            except NameError:
                hi_rank_dubs = None 

    cur.execute('''INSERT OR IGNORE INTO Players 
    ( name, birthday, country, singles_curr_rank, singles_high_rank, doubles_curr_rank, doubles_high_rank,
        hand, sex, weight, height)
        VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )''', 
                ( str(name), str(birthday), str(country),
                 cur_rank_singles, int(hi_rank_singles),
                 cur_rank_dubs, hi_rank_dubs,
                 str(hand), str(sex), weight, height ))
    conn.commit()<jupyter_output><empty_output><jupyter_text>LoopThis loop pulls out player-specific information from various years.<jupyter_code># Creating SQLite table for Basic Data
for i in range(0, 11):
    """Basic Data Loop
    For each player, the following basic information is saved:
    Age, height, weight, birthday, country, singles/doubles ranking, year
    """
    year = 2020 - i
    url_b = "https://www.tennisexplorer.com/ranking/wta-women/" + str(year)

    for i in range(1, 5):
        url = url_b + "/?page=" + str(i)
        with requests.get(url) as r:
            soup = BeautifulSoup(r.content, features="html")
            sl = random.randint(1, 3)
            time.sleep(sl)

        data = soup.find("tbody", {"class": "flags"})
        rows = data.find_all("td", {"class": "t-name"})

        for t in rows:
            player = t.find("a").attrs["href"]
            player_url = "https://www.tennisexplorer.com/" + player[1:]
            print(player_url)

            with requests.get(player_url) as r:
                soup = BeautifulSoup(r.content, features="html")
                basic_data(soup, year)
                sl = random.randint(1, 3)
                time.sleep(sl)<jupyter_output>https://www.tennisexplorer.com/player/barty/
https://www.tennisexplorer.com/player/halep/
https://www.tennisexplorer.com/player/pliskova/
https://www.tennisexplorer.com/player/kenin-5a3a8/
https://www.tennisexplorer.com/player/svitolina/
https://www.tennisexplorer.com/player/andreescu-281c7/
https://www.tennisexplorer.com/player/bertens/
https://www.tennisexplorer.com/player/bencic/
https://www.tennisexplorer.com/player/williams-3d2a9/
https://www.tennisexplorer.com/player/osaka-fa603/
https://www.tennisexplorer.com/player/sabalenka/
https://www.tennisexplorer.com/player/kvitova/
https://www.tennisexplorer.com/player/keys/
https://www.tennisexplorer.com/player/konta/
https://www.tennisexplorer.com/player/martic/
https://www.tennisexplorer.com/player/muguruza-33acd/
https://www.tennisexplorer.com/player/rybakina/
https://www.tennisexplorer.com/player/vondrousova/
https://www.tennisexplorer.com/player/riske-69d46/
https://www.tennisexplorer.com/player/sakkari/
https://www.tennisexplorer.[...]<jupyter_text>Injury_Record and Match_Record Table<jupyter_code># Make some fresh tables using executescript()
cur.executescript('''
DROP TABLE IF EXISTS Injury_Record;


CREATE TABLE Injury_Record (
    id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    Players_id  INTEGER,
    start_date  TEXT,
    end_date   TEXT UNIQUE,
    tournament    TEXT,
    reason    TEXT
);

''')
        
cur.executescript('''
DROP TABLE IF EXISTS Match_Record;

CREATE TABLE Match_Record (
    id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    Players_id  INTEGER,
    year INTEGER,
    tournament TEXT,
    match_type TEXT,
    date TEXT,
    opponent TEXT,
    round TEXT,
    result  TEXT,
    h FLOAT, 
    a FLOAT
);
''')<jupyter_output><empty_output><jupyter_text>injury_data Function: For each injury a player experiences, the following basic information is saved:start date, end date, tournament, reason __Input__ - The web-page from www.tennisexplorer.com__Output__ - Adding rows to the Injury_Record sqlite table<jupyter_code>def injury_data(soup):
    for i, table in enumerate(soup.select("table")):
        # Injury Data
        if table["class"] == ["result", "flags", "injured"]:
            rows = table.find_all("tr")
            for row in rows:
                cols = row.find_all("td")
                cols = [x.text.strip() for x in cols]
                
                if len(cols) == 3:
                    dates = cols[0]
                    start_injury_date = dates.split(" - ")[0]
                    end_injury_date = dates.split(" - ")[1]
                    tournament = cols[1]
                    reason = cols[2]
                    name = soup.title # name
                    name = name.text[:-18]
                    cur.execute('SELECT id FROM Players WHERE name = ? ', (name, ))
                    Players_id = cur.fetchone()[0]
            
                    cur.execute('''INSERT OR IGNORE INTO Injury_Record 
                     ( Players_id, start_date, end_date, tournament, reason)
                     VALUES ( ?, ?, ?, ?, ?)''', 
                    (Players_id, str(start_injury_date), str(end_injury_date),
                    str(tournament), str(reason)))
                    conn.commit()
                else:
                    print("length is not 3")<jupyter_output><empty_output><jupyter_text>Match Results (tourn_res) Function: For each match a player plays, the following basic information is saved:year, tournament, match_type, date, opponent, round, result, h, a __Input__ - The web-page from tennisexplorer.com and year of interest__Output__ - Adding rows to the Match_Record sqlite table<jupyter_code># Extracting the Player Tournament Results
def tourn_res(soup):
    # iterate through different years of play
    for j in range(0, 13):
        year = 2020 - j
        # need to make sure that play took play that year
        # if exists, collect data, else == skip
        id_type = "matches-" + str(year) + "-1-data"
        test_exists = soup.find("div", {"id": id_type})
        if test_exists is None:
            print(str(year) + " Does not Exist!")
        else:
            # to iterate through all sing/dubs/mixed dub play in year
            for i in range(1, 4):
                id_type = "matches-" + str(year) + "-" + str(i) + "-data"
                table = soup.find("div", {"id": id_type})
                if i == 1:
                    mt = "singles"
                if i == 2:
                    mt = "doubles"
                if i == 3:
                    mt = "mixed doubles"

                rows = table.find_all("tr")
                for row in rows:
                    cols = row.find_all("td")
                    cols = [x.text.strip() for x in cols]
                    if len(cols) == 1:
                        tourn = cols[0]
                    else:
                        tournament = tourn
                        date = cols[0]
                        opponent = cols[2]
                        rounds = cols[3]
                        results = cols[4]
                        
                        h = cols[5]
                        try:
                            float(h)
                        except ValueError:
                            h = 0.0
                            
                        
                        a = cols[6]
                        try:
                            float(a)
                        except ValueError:
                            a = 0.0
                            
                        match_type = mt
                        year = year
                        # Saving info to table
                        name = soup.title # name
                        name = name.text[:-18]
                        cur.execute('SELECT id FROM Players WHERE name = ? ', (name, ))
                        Players_id = cur.fetchone()[0]

                        cur.execute('''INSERT OR IGNORE INTO Match_Record 
                         ( Players_id, year, tournament, match_type, date,
                         opponent, round, result, h, a)
                         VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)''', 
                            (Players_id, int(year), str(tournament),
                             str(match_type), str(date),opponent,str(rounds),str(results),float(h),float(a)))
                        conn.commit()<jupyter_output><empty_output><jupyter_text>LoopThis loop pulls out match and injury information from 2010 - 2020<jupyter_code>proxies = ['http://192.168.127.12:80', 'http://172.16.58.3:45169',
           'http://172.16.58.3:8080', 'http://194.182.64.67:3128', 
           'http://192.168.127.12:8080', 'http://172.16.31.10:3128',
           'http://172.16.58.3:8080']

for i in range(0, 11):
    '''
    Basic Data to save the match history for every player from 2010 - 2020 
    '''
    year = 2020 - i
    url_b = "https://www.tennisexplorer.com/ranking/wta-women/" + str(year)
    print(url_b)

    for i in range(1, 5):
        url = url_b + "/?page=" + str(i)
        print(url)
        proxy = {'http': random.choice(proxies)}
        
        with requests.get(url, proxies = proxy) as r:
            soup = BeautifulSoup(r.content, features="html")
            sl = random.randint(2, 4)
            time.sleep(sl)

        data = soup.find("tbody", {"class": "flags"})
        rows = data.find_all("td", {"class": "t-name"})

        for t in rows:
            player = t.find("a").attrs["href"]
            player_url = "https://www.tennisexplorer.com/" + player[1:]
            print(player_url)

            with requests.get(player_url, proxies = proxy) as r:
                soup = BeautifulSoup(r.content, features="html")
                sl = random.randint(2, 4)
                time.sleep(sl)

            # Find the summary hyperlink and pull out the injury
            # and sets/games played
            summary = soup.find("tr", {"class": "summary"})
            player = summary.find("a").attrs["href"]
            player_sum_url = "https://www.tennisexplorer.com/" + player[1:]
            print(player_sum_url)

            with requests.get(player_sum_url, proxies = proxy) as r:
                soup = BeautifulSoup(r.content, features="html")
                sl = random.randint(2, 4)
                time.sleep(sl)

            name_long = soup.title
            name = name_long.text[:-18]
            if name not in names:
                names.append(name)
                injury_data(soup)
                tourn_res(soup)<jupyter_output>https://www.tennisexplorer.com/ranking/wta-women/2020
https://www.tennisexplorer.com/ranking/wta-women/2020/?page=1
https://www.tennisexplorer.com/player/barty/
https://www.tennisexplorer.com/player/barty/?annual=all
https://www.tennisexplorer.com/player/halep/
https://www.tennisexplorer.com/player/halep/?annual=all
https://www.tennisexplorer.com/player/pliskova/
https://www.tennisexplorer.com/player/pliskova/?annual=all
https://www.tennisexplorer.com/player/kenin-5a3a8/
https://www.tennisexplorer.com/player/kenin-5a3a8/?annual=all
https://www.tennisexplorer.com/player/svitolina/
https://www.tennisexplorer.com/player/svitolina/?annual=all
https://www.tennisexplorer.com/player/andreescu-281c7/
https://www.tennisexplorer.com/player/andreescu-281c7/?annual=all
https://www.tennisexplorer.com/player/bertens/
https://www.tennisexplorer.com/player/bertens/?annual=all
https://www.tennisexplorer.com/player/bencic/
https://www.tennisexplorer.com/player/bencic/?annual=all
https://www.tennisexplor[...]<jupyter_text>Rank Table<jupyter_code>cur.executescript('''
DROP TABLE IF EXISTS Rank;

CREATE TABLE Rank (
    id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    Players_id  INTEGER,
    year INTEGER,
    ranktype TEXT,
    rank INTEGER
);

''')<jupyter_output><empty_output><jupyter_text>Ranking by Year (Rank) Function: The top 200 players from 2010 - 2020.Players_id, year, p_rank_type, rank __Input__ - The web-page from tennisexplorer.com__Output__ - Adding rows to the Rank sqlite table<jupyter_code># Replaced 0 
for i in range(0, 11):
    year = 2020 - i
    url_b = "https://www.tennisexplorer.com/ranking/wta-women/" + str(year)
    print(url_b)

    # Singles Rankings
    for i in range(1, 5):
        p_rank_type = "Singles"
        url = url_b + "/?page=" + str(i)
        print(url)
        proxy = {'http': random.choice(proxies)}
        with requests.get(url, proxies = proxy) as r:
            soup = BeautifulSoup(r.content, features="html")
            sl = random.randint(3, 5)
            time.sleep(sl)
            data = soup.find("tbody", {"class": "flags"})
            rows_name = data.find_all("td", {"class": "t-name"})
            rows_rank = data.find_all("td", {"class": "rank first"})

            for i in range(0, len(rows_rank)):
                player_row = rows_name[i]
                player = player_row.string
                
                # Edge Cases (18 total)
                if player == '<NAME>':
                    player = '<NAME>'                
                elif player == '<NAME>':
                    player = '<NAME>'                
                elif player == '<NAME>':
                    player = '<NAME>'                
                elif player == '<NAME>':
                    player = '<NAME>'                
                elif player == '<NAME>':
                    player = '<NAME>'                
                elif player == '<NAME>':
                    player = '<NAME>'                
                elif player == '<NAME>':
                    player = '<NAME>'                
                elif player == '<NAME>':
                    player  = '<NAME>'                    
                elif player == '<NAME>':
                    player = '<NAME>'                    
                elif player == '<NAME>':
                    player = '<NAME>'                    
                elif player == '<NAME>':
                    player = '<NAME>'                    
                elif player == '<NAME>':
                    player = '<NAME>'                    
                elif player == '<NAME>':
                    player = '<NAME>'                    
                elif player == '<NAME>':
                    player = '<NAME>'                    
                elif player == '<NAME>':
                    player = '<NAME>'                    
                elif player == '<NAME>':
                    player = '<NAME>'
                elif player == '<NAME>':
                    player = '<NAME>' 
                elif player == '<NAME>':
                    player = '<NAME>'
                
                # Otherwise use normal seperator
                else:
                    # Need to flip for first and last name
                    f_name = player.split()[1:]
                    f_name_f = ' '.join(f_name)
                    l_name = player.split()[0]
                    player = f_name_f + ' ' + l_name
                

                ranking_row = rows_rank[i]
                rank = int(float(ranking_row.string))
                
                # Adding Row of information to the table
                cur.execute('SELECT id FROM Players WHERE name = ? ', (player, ))
                Players_id = cur.fetchone()[0]
                
                cur.execute('''INSERT OR IGNORE INTO Rank 
                ( Players_id, year, ranktype, rank)
                VALUES ( ?, ?, ?, ?)''', 
                (Players_id, year, p_rank_type, rank))
                conn.commit()   
            
    # Doubles Rankings
    for i in range(1, 5):
        url = url_b + "/?t=doubles&page=" + str(i)
        print(url)
        p_rank_type = "Doubles"
        proxy = {'http': random.choice(proxies)}

        with requests.get(url,proxies = proxy) as r:
            soup = BeautifulSoup(r.content, features="html")
            sl = random.randint(3, 5)
            time.sleep(sl)
            data = soup.find("tbody", {"class": "flags"})
            rows_name = data.find_all("td", {"class": "t-name"})
            rows_rank = data.find_all("td", {"class": "rank first"})


            for i in range(0, len(rows_rank)):
                player_row = rows_name[i]
                player = player_row.string


                # Edge Cases (18 total)
                if player == '<NAME>':
                    player = '<NAME>'                
                elif player == '<NAME>':
                    player = '<NAME>'                
                elif player == '<NAME>':
                    player = '<NAME>'                
                elif player == '<NAME>':
                    player = '<NAME>'                
                elif player == '<NAME>':
                    player = '<NAME>'                
                elif player == '<NAME>':
                    player = '<NAME>'                
                elif player == '<NAME>':
                    player = '<NAME>'                
                elif player == '<NAME>':
                    player  = '<NAME>'                    
                elif player == '<NAME>':
                    player = '<NAME>'                    
                elif player == '<NAME>':
                    player = '<NAME>'                    
                elif player == '<NAME>':
                    player = '<NAME>'                    
                elif player == '<NAME>':
                    player = '<NAME>'                    
                elif player == '<NAME>':
                    player = '<NAME>'                    
                elif player == '<NAME>':
                    player = '<NAME>'                    
                elif player == '<NAME>':
                    player = '<NAME>'                    
                elif player == '<NAME>':
                    player = '<NAME>'
                elif player == '<NAME>':
                    player = '<NAME>' 
                elif player == '<NAME>':
                    player = '<NAME>'
                
                
                # Otherwise use normal seperator
                else:
                    # Need to flip for first and last name
                    f_name = player.split()[1:]
                    f_name_f = ' '.join(f_name)
                    l_name = player.split()[0]
                    player = f_name_f + ' ' + l_name
                    
                ranking_row = rows_rank[i]
                rank = int(float(ranking_row.string))

                cur.execute('SELECT id FROM Players WHERE name = ? ', (player, ))

                x = cur.fetchone()
                
                if not x:
                    Players_id = None
                else:
                    Players_id = x[0]

                
                cur.execute('''INSERT OR IGNORE INTO Rank 
                ( Players_id, year, ranktype, rank)
                VALUES ( ?, ?, ?, ?)''', 
                (Players_id, year, p_rank_type, rank))
                conn.commit()<jupyter_output>https://www.tennisexplorer.com/ranking/wta-women/2020
https://www.tennisexplorer.com/ranking/wta-women/2020/?page=1
https://www.tennisexplorer.com/ranking/wta-women/2020/?page=2
https://www.tennisexplorer.com/ranking/wta-women/2020/?page=3
https://www.tennisexplorer.com/ranking/wta-women/2020/?page=4
https://www.tennisexplorer.com/ranking/wta-women/2020/?t=doubles&page=1
https://www.tennisexplorer.com/ranking/wta-women/2020/?t=doubles&page=2
https://www.tennisexplorer.com/ranking/wta-women/2020/?t=doubles&page=3
https://www.tennisexplorer.com/ranking/wta-women/2020/?t=doubles&page=4
https://www.tennisexplorer.com/ranking/wta-women/2019
https://www.tennisexplorer.com/ranking/wta-women/2019/?page=1
https://www.tennisexplorer.com/ranking/wta-women/2019/?page=2
https://www.tennisexplorer.com/ranking/wta-women/2019/?page=3
https://www.tennisexplorer.com/ranking/wta-women/2019/?page=4
https://www.tennisexplorer.com/ranking/wta-women/2019/?t=doubles&page=1
https://www.tennisexplorer.com/ranki[...]<jupyter_text>Cout_Type Table* Unique id* Tournament* Court Type__Based off of database__Database information is downloaded as csv files<jupyter_code>cur.executescript('''
DROP TABLE IF EXISTS Cout_Type;

CREATE TABLE Cout_Type (
    id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    tournament  TEXT UNIQUE
    surface     TEXT
);

''')
from os import listdir

# Change the Working Directory
os.chdir("/Users/Owner/Desktop/InsightFellows/Daniella_Patton_Insight_Project/tennis_wta")

def find_csv_filenames( path_to_dir, suffix=".csv" ):
    filenames = listdir(path_to_dir)
    return [ filename for filename in filenames if filename.endswith( suffix ) ]


filenames = find_csv_filenames("my/directory")

for file in filenames:
    print(file)
    df = pd.read_csv(file)
    tournaments = df.tourney_name
    surfaces = df.surface
    
    for i in range(0, len(df)):
        tournament = tournaments[i]
        surface = surfaces[i]
        
        cur.execute('''INSERT OR IGNORE INTO Cout_Type 
                ( tournament, surface)
                VALUES ( ?, ?)''', 
                (tournament, surface))
        conn.commit()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11702494539602253, 'fraction_numerical': 0.014446104916273902, 'mean_word_length': 1.6260817065808009, 'pattern_counts': {'":': 14, '<': 114, '<?xml version=': 0, '>': 114, 'https://': 62, 'lorem ipsum': 0, 'www.': 65, 'xml': 0}, 'pii_count': 7, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 3, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '11', 'dir': 'jupyter-structured-clean-dedup', 'id': '634688', 'license': 'MIT', 'n_tokens_mistral': 8091, 'n_tokens_neox': 7393, 'n_words': 1716, 'path': 'Extracting_Files-Sqlite.ipynb', 'repo_name': 'daniella-patton/TennisDoctor', 'size': '27169'}