ग्रेट अजगर स्पोर्ट्स डेटा प्रोसेसिंग ट्यूटोरियल

भाग 1. स्क्रैपिंग और पार्सिंग

import requests

r = requests.get(url='http://tristats.ru/')
print(r.content)

from selenium import webdriver
from selenium.webdriver.chrome.service import Service

service = Service(r'C:\ChromeDriver\chromedriver.exe')
service.start()
driver = webdriver.Remote(service.service_url)
driver.get('http://www.tristats.ru/')
print(driver.page_source)
driver.quit()

driver.execute_script("window.scrollTo(0, document.body.scrollHeight);")

from selenium import webdriver
from selenium.webdriver.chrome.service import Service
import time

service = Service(r'C:\ChromeDriver\chromedriver.exe')
service.start()
driver = webdriver.Remote(service.service_url)
driver.get('http://www.tristats.ru/')
prev_html = ''
scroll_attempt = 0

while scroll_attempt < 10:
    driver.execute_script("window.scrollTo(0, document.body.scrollHeight);")
    time.sleep(1)

    if prev_html == driver.page_source:
        scroll_attempt += 1
    else:
        prev_html = driver.page_source
        scroll_attempt = 0 

with open(r'D:\tri\summary.txt', 'w') as f:
    f.write(prev_html)

driver.quit()

from lxml import html

with open(r'D:\tri\summary.txt', 'r') as f:
    sum_html = f.read()

tree = html.fromstring(sum_html)
rows = tree.findall(".//*[@ng-repeat='r in racesData']")

import pandas as pd

rs = pd.DataFrame(columns=['date','name','link','males','females','rus','total'], index=range(len(rows))) #rs – races summary

<tr ng-repeat="r in racesData" class="ng-scope">
  <td class="ng-binding">2015-04-26</td>
    <td>
      <img src="/Images/flags/24/USA.png" class="flag">
      <a href="/rus/result/ironman/texas/half/2015" target="_self" class="ng-binding">Ironman Texas 70.3 2015</a>
    </td>
    <td>
      <a href="/rus/result/ironman/texas/half/2015?sex=F" target="_self" class="ng-binding">605</a>
      <i class="fas fa-venus fa-lg" style="color:Pink"></i>
      /
      <a href="/rus/result/ironman/texas/half/2015?sex=M" target="_self" class="ng-binding">1539</a>
      <i class="fas fa-mars fa-lg" style="color:LightBlue"></i>
    </td>
    <td class="ng-binding">
      <img src="/Images/flags/24/rus.png" class="flag">
      <!-- ngIf: r.CountryCount == 0 -->
      <!-- ngIf: r.CountryCount > 0 --><a ng-if="r.CountryCount > 0" href="/rus/result/ironman/texas/half/2015?country=rus" target="_self" class="ng-binding ng-scope">2</a>
      <!-- end ngIf: r.CountryCount > 0 -->
      / 2144
  </td>
</tr>

for i in range(len(rows)):
    rs.loc[i,'date'] = rows[i].getchildren()[0].text.strip()
    rs.loc[i,'name'] = rows[i].getchildren()[1].getchildren()[1].text.strip()
    rs.loc[i,'link'] = rows[i].getchildren()[1].getchildren()[1].attrib['href'].strip()
    rs.loc[i,'males'] = rows[i].getchildren()[2].getchildren()[2].text.strip()
    rs.loc[i,'females'] = rows[i].getchildren()[2].getchildren()[0].text.strip()
    rs.loc[i,'rus'] = rows[i].getchildren()[3].getchildren()[3].text.strip()
    rs.loc[i,'total'] = rows[i].getchildren()[3].text_content().split('/')[1].strip()

import pickle as pkl

with open(r'D:\tri\summary.pkl', 'wb') as f:
    pkl.dump(df,f)

df[df.duplicated(subset = 'event', keep=False)]

service.start()
driver = webdriver.Remote(service.service_url)
timeout = 60

for index, row in df.iterrows():
    try:
        driver.get('http://www.tristats.ru' + row['link'])     
        start = time.time()

        while True:
            driver.execute_script("window.scrollTo(0, document.body.scrollHeight);")
            time.sleep(1)
            race_html = driver.page_source
            tree = html.fromstring(race_html)
            race_rows = tree.findall(".//*[@ng-repeat='r in resultsData']")

            if len(race_rows) == int(row['total']):
                break
            if time.time() - start > timeout:
                print('timeout')
                break

        with open(os.path.join(r'D:\tri\races', row['event'] +  '.txt'), 'w') as f:
            f.write(race_html)

    except:
        traceback.print_exc()

    time.sleep(1)
    
driver.quit()

rd = {} #rd – race details

for e in rs['event']:
    place = []
    ... sex = [], name=..., country, group, place_in_group, swim, t1, bike, t2, run
    result = []

    with open(os.path.join(r'D:\tri\races', e + '.txt'), 'r')
        race_html = f.read()

    tree = html.fromstring(race_html)
    rows = tree.findall(".//*[@ng-repeat='r in resultsData']")

    for j in range(len(rows)):
        row = rows[j]
        parts = row.text_content().split('\n')
        parts = [r.strip() for r in parts if r.strip() != '']
        place.append(parts[0])

        if len([a for a in row.findall('.//i')]) > 0:
            sex.append([a for a in row.findall('.//i')][0].attrib['ng-if'][10:-1])
        else:
            sex.append('')

        name.append(parts[1])

        if len(parts) > 10:
            country.append(parts[2].strip())         
            k=0
        else:
            country.append('')
            k=1

        group.append(parts[3-k])
        ... place_in_group.append(...), swim.append ..., t1, bike, t2, run
        result.append(parts[10-k])

    race = pd.DataFrame()
    race['place'] = place
    ... race['sex'] = sex, race['name'] = ..., 'country', 'group', 'place_in_group', 'swim', ' t1', 'bike', 't2', 'run'
    race['result'] = result

    rd[e] = race

with open(r'D:\tri\details.pkl', 'wb') as f:
    pkl.dump(rd,f)

for index, row in rs.iterrows():
    e = row['event']       
    with open(os.path.join(r'D:\tri\races', e + '.txt'), 'r') as f:
        race_html = f.read()

    tree = html.fromstring(race_html)
    header_elem = [tb for tb in tree.findall('.//tbody') if tb.getchildren()[0].getchildren()[0].text == ''][0]
    location = header_elem.getchildren()[1].getchildren()[1].text.strip()
    rs.loc[index, 'loc'] = location

with open(r'D:\tri\summary1.pkl', 'wb') as f:
    pkl.dump(df,f)

भाग 2. टाइप कास्टिंग और स्वरूपण

दिनांक और समय

rs['date'] = pd.to_datetime(rs['date'])

cols = ['males', 'females', 'rus', 'total']
rs[cols] = rs[cols].astype(int)

with open(r'D:\tri\summary2.pkl', 'wb') as f:
    pkl.dump(rs, f)

ar = pd.concat(rd) 

ar[['place', 'place in group']] = ar[['place', 'place in group']].astype(int))

for col in ['swim', 't1', 'bike', 't2', 'run', 'result']:
    strlen = ar[col].str.len()
    ar.loc[strlen==5, col] = '0:' + ar.loc[strlen==5, col]
    ar.loc[strlen==4, col] = '0:0' + ar.loc[strlen==4, col]

for col in ['swim', 't1', 'bike', 't2', 'run', 'result']:
    ar[col] = pd.to_timedelta(ar[col])

मंज़िल

ar['sex'].unique() 

len(ar[ar['sex'] == '']) 

for event in ar.index.get_level_values(0).unique():
    rd[event] = ar.loc[event]

with open(r'D:\tri\details1.pkl', 'wb') as f:
    pkl.dump(rd,f)

देश कोड

ar['country'].unique()

import pycountry as pyco

pyco.countries.get(alpha_3 = 'RUS')

valid_a3 = [c for c in ar['country'].unique() if pyco.countries.get(alpha_3 = c.upper()) != None or pyco.historic_countries.get(alpha_3 = c.upper()) != None])

tofix = list(set(ar['country'].unique()) - set(valid_a3))

for icc in tofix: #icc -invalid country code
    if pyco.countries.get(alpha_2 = icc.upper()) != None:
        fix[icc] = pyco.countries.get(alpha_2 = icc.upper()).alpha_3
    else:
        if pyco.historic_countries.get(alpha_2 = icc.upper()) != None:
            fix[icc] = pyco.historic_countries.get(alpha_2 = icc.upper()).alpha_3

for icc in tofix:
    if pyco.countries.get(common_name = icc.title()) != None:
        fix[icc] = pyco.countries.get(common_name = icc.title()).alpha_3
    else:
        if pyco.countries.get(name = icc.title()) != None:
            fix[icc] = pyco.countries.get(name = icc.title()).alpha_3
        else:
            if pyco.historic_countries.get(name = icc.title()) != None:
                fix[icc] = pyco.historic_countries.get(name = icc.title()).alpha_3

len(([x for x in tofix if x.upper() in list(cdf['ioc'])]))

for icc in tofix:
    if icc.upper() in list(cdf['ioc']):
        ind = cdf[cdf['ioc'] == icc.upper()].index[0]
        fix[icc] = cdf.loc[ind, 'iso3']

unfixed = [x for x in tofix if x not in fix.keys()]

for cc in unfixed:
    fix[cc] = ''

for cc in valid_a3:
    if cc.upper() != cc:
        fix[cc] = cc.upper()

for cc in fix:
    ind = ar[ar['country'] == cc].index
    ar.loc[ind,'country'] = fix[cc]

len(ar[ar['country'] != ar['country raw']])

ar[ar['country'] != ar['country raw']].sample(10)

len(ar[ar['country'] == ''])

स्थान

from geopy.geocoders import Nominatim

geolocator = Nominatim(user_agent='triathlon results researcher')
geolocator.geocode(' , , ', language='en')

दूरी

sprints = rs.loc[[i for i in rs.index if 'sprint' in rs.loc[i, 'event'].lower()]]
olympics1 = rs.loc[[i for i in rs.index if 'olympic' in rs.loc[i, 'event'].lower()]]
olympics2 = rs.loc[[i for i in rs.index if '5150' in rs.loc[i, 'event'].lower()]]
olympics = pd.concat([olympics1, olympics2])
#…   

rsd = pd.concat([sprints, olympics, halfs, fulls]) 

rsd[rsd.duplicated(keep=False)]['event'].sort_index()

olympics.drop(65)

fulls.drop(85)

rs['dist'] = ''

rs.loc[sprints.index,'dist'] = 'sprint'
rs.loc[olympics.index,'dist'] = 'olympic'
rs.loc[halfs.index,'dist'] = 'half'
rs.loc[fulls.index,'dist'] = 'full'

rs.sample(10)

len(rs[rs['dist'] == ''])

rs.loc[[38,65,82],['event','dist']]

pkl.dump(rs, open(r'D:\tri\summary5.pkl', 'wb'))

आयु समूह

rd = pkl.load(open(r'D:\tri\details2.pkl', 'rb'))
ar = pd.concat(rd)
ar['group'].unique()

ar['group'].value_counts()[:30]

ag = ['MPRO', 'M18-24', 'M25-29', 'M30-34', 'M35-39', 'M40-44', 'M45-49', 'M50-54', 'M55-59', 'M60-64',  'M65-69', 'M70-74', 'M75-79', 'M80-84', 'M85-90', 'FPRO', 'F18-24', 'F25-29', 'F30-34', 'F35-39', 'F40-44',   'F45-49', 'F50-54', 'F55-59', 'F60-64', 'F65-69', 'F70-74', 'F75-79', 'F80-84', 'F85-90']
#ag – age group

fix = { 'F25-29F': 'F25-29', 'F30-34F' : 'F30-34', 'F30-34-34': 'F30-34', 'F35-39F': 'F35-39', 'F40-44F': 'F40-44', 'F45-49F': 'F45-49', 'F50-54F': 'F50-54', 'F55-59F': 'F55-59', 'FAG:FPRO': 'FPRO', 'FK30-34': 'F30-34',      'FK35-39': 'F35-39', 'FK40-44': 'F40-44', 'FK45-49': 'F45-49', 'FOPEN50-54': 'F50-54', 'FOPEN60-64': 'F60-64', 'MAG:MPRO': 'MPRO', 'MK30-34': 'M30-34', 'MK30-39': 'M30-39', 'MK35-39': 'M35-39', 'MK40-44': 'M40-44', 'MK40-49': 'M40-49', 'MK50-59': 'M50-59', 'M40-44': 'M40-44', 'MM85-89': 'M85-89', 'MOPEN25-29': 'M25-29', 'MOPEN30-34': 'M30-34', 'MOPEN35-39': 'M35-39', 'MOPEN40-44': 'M40-44', 'MOPEN45-49': 'M45-49', 'MOPEN50-54': 'M50-54', 'MOPEN70-74': 'M70- 74', 'MPRO:' :'MPRO', 'MPROM': 'MPRO', 'M0-44"' : 'M40-44'}

ar['group raw'] = ar['group']

len(ar[(ar['group'] != ar['group raw'])&(ar['group']!='')])

pkl.dump(rd, open(r'D:\tri\details3.pkl', 'wb'))

नाम

list(ar['name'].sample(100))

ar['name raw'] = ar['name']

set( ''.join(ar['name'].unique()))

bs = [s for s in symbols if not (s.isalpha() or s in " . - ' ? ,")] #bs – bad symbols

bs

for s in bs:
    ar['name'] = ar['name'].str.replace(s, '')

corr = ar[ar['name'] != ar['name raw']]

list(corr['name raw'].sample(100))

ar['name'] = ar['name raw']

for s in symbols:
    if s.isalpha() or s in " - ? '":        
        continue        
    if s in ".,\xa0":
        ar['name'] = ar['name'].str.replace(s, ' ')       
    else:
        ar['name'] = ar['name'].str.replace(s, '')

ar['name'] = ar['name'].str.split().str.join(' ')
ar['name'] = ar['name'].str.strip() #   

ar.loc[corr.index].sample(10)

qmon = ar[(ar['name'].str.replace('?', '').str.strip() == '')&(ar['name']!='')] #qmon – question mark only names

ar.loc[qmon.index, 'name'] = ''

ar['name'] = ar['name'].str.lower()

trans = {'':'a', '':'b', '':'v', '':'g', '':'d', '':'e', '':'e', '':'zh', '':'z',  '':'i', '':'y', '':'k', '':'l', '':'m', '':'n', '':'o', '':'p', '':'r', '':'s', '':'t', '':'u', '':'f', '':'kh', '':'ts', '':'ch', '':'sh', '':'shch', '':'', '':'y', '':'', '':'e', '':'yu', '':'ya', 'є':'e', 'і': 'i','ў':'w','µ':'m'}

for s in trans:
    ar['name'] = ar['name'].str.replace(s, trans[s])

ar['name'] = ar['name'].str.title()

ar[ar['name raw'].str.lower().str[0].isin(trans.keys())].sample(10)

set( ''.join(ar['name'].unique()))

pkl.dump(rd, open(r'D:\tri\details4.pkl', 'wb'))

wp = {} #wp – with patronymic 

for e in rd:
    nvich = (''.join(rd[e]['name'])).count('vich') 
    nvna = (''.join(rd[e]['name'])).count('vna')
    if nvich + nvna > 0.5*len(rd[e]):
        wp[e] = rd[e]

sum_n3w = 0 # sum name of 3 words
sum_nnot3w = 0 # sum name not of 3 words

for e in wp: 
    sum_n3w += len([n for n in wp[e]['name'] if len(n.split()) == 3])
    sum_nnot3w += len(wp[e]) - n3w

for e in wp:
    ind3 = [i for i in rd[e].index if len(rd[e].loc[i,'name'].split()) == 3]
    rd[e]['name0'] = ''
    rd[e]['name1'] = ''
    rd[e]['name2'] = ''
    rd[e].loc[ind3, 'name0'] = rd[e].loc[ind3,'name'].str.split().str[0]
    rd[e].loc[ind3, 'name1'] = rd[e].loc[ind3,'name'].str.split().str[1]
    rd[e].loc[ind3, 'name2'] = rd[e].loc[ind3,'name'].str.split().str[2]

for e in wp:    
    n1=(''.join(rd[e]['name1'])).count('vich')+(''.join(rd[e]['name1'])).count('vna')
    n2=(''.join(rd[e]['name2'])).count('vich')+(''.join(rd[e]['name2'])).count('vna')

    if (n1 > n2):
        rd[e]['new name'] = rd[e]['name0'] + ' ' + rd[e]['name2']
    else:
        rd[e]['new name'] = rd[e]['name0'] + ' ' + rd[e]['name1']

for e in wp:
    ind = rd[e][rd[e]['new name'].str.strip() != ''].index
    rd[e].loc[ind, 'name'] = rd[e].loc[ind, 'new name']
    rd[e] = rd[e].drop(columns = ['name0','name1','name2','new name'])

pkl.dump(rd, open(r'D:\tri\details5.pkl', 'wb'))

ar['nwin'] = ar['name'].str.count(' ') + 1 # nwin – number of words in name
ar.loc[ar['name'] == '','nwin'] = 0
100*ar['nwin'].value_counts()/len(ar)

ar[ar['nwin'] >= 3]['country'].value_counts()[:12]

ar['new name'] = ar['name']
ind = ar[ar['nwin'] < 2].index
ar.loc[ind, 'new name'] = '. .' #  ,   str.split()   
ar['wfin'] = ar['new name'].str.split().str[0] #fwin  – first word in name
ar['lwin'] = ar['new name'].str.split().str[-1]#lfin – last word in name

name_surname = {}
surname_name = {}

for e in rd:
    d = rd[e][rd[e]['nwin'] == 2]

    if len(d['fwin'].unique()) < len(d['lwin'].unique()) and len(''.join(d['fwin'])) < len(''.join(d['lwin'])):
        name_surname[e] = d
        rd[e]['first name'] = rd[e]['fwin']

    if len(d['fwin'].unique()) > len(d['lwin'].unique()) and len(''.join(d['fwin'])) > len(''.join(d['lwin'])):
        surname_name[e] = d
        rd[e]['first name'] = rd[e]['lwin']

vc = ar['first name'].value_counts()

pfn=vc[vc>100] #pfn – popular first names

sbpn = pd.DataFrame(columns = ['event', 'num pop names'], index=range(len(rd))) # sbpn - sorted by popular names

for i in range(len(rd)):
    e = list(rd.keys())[i]
    sbpn.loc[i, 'event'] = e
    sbpn.loc[i, 'num pop names'] = len(set(pfn).intersection(rd[e]['first name']))

sbnp=sbnp.sort_values(by = 'num pop names',ascending=False)
sbnp = sbnp.reset_index(drop=True) 

tofix = []

for i in range(len(rd)):
    e = sbpn.loc[i, 'event']

    if len(set(list(rd[e]['fwin'])).intersection(pfn)) > len(set(list(rd[e]['lwin'])).intersection(pfn)):
        tofix.append(e)
        pfn = list(set(pfn + list(rd[e]['fwin'])))
    else:
        pfn = list(set(pfn + list(rd[e]['lwin'])))

for e in tofix:
    ind = rd[e][rd[e]['nwin'] > 1].index
    rd[e].loc[ind,'name'] = rd[e].loc[ind,'name'].str.split(n=1).str[1] + ' ' +                   rd[e].loc[ind,'name'].str.split(n=1).str[0]

for e in rd:
    rd[e] = rd[e].drop(columns = ['new name', 'first name', 'fwin','lwin', 'nwin'])

pkl.dump(rd, open(r'D:\tri\details6.pkl', 'wb'))

भाग 3. अधूरे डेटा की वसूली

देश

arnc = ar[ar['country'] == ''] #arnc – all records with no country

nnc = arnc['name'].unique() #nnc - names with no country

arwc = ar[ar['country'] != ''] #arwc – all records with country
nwc = arwc['name'].unique() #nwc – names with country
tofix = set(nnc).intersection(nwc)

fix = {}

for n in tofix:
    nr = arwc[arwc['name'] == n] 
	
    if len(nr['country'].unique()) == 1:
        fix[n] = nr['country'].iloc[0]

if n not in fix:
    nr = arwc[arwc['name'] == n]
    vc = nr['country'].value_counts()    
	
    if vc[0] > vc[1]:
        fix[n] = vc.index[0]

for n in fix:
    ind = arnc[arnc['name'] == n].index
    ar.loc[ind, 'country'] = fix[n]

pkl.dump(rd, open(r'D:\tri\details7.pkl', 'wb'))

मंज़िल

ar[ar['sex'] == '']

ar['sex raw'] =ar['sex']

arws = ar[(ar['sex'] != '')&(ar['name'] != '')] #arws – all records with sex
snds = arws[arws.duplicated(subset='name',keep=False)]#snds–single name different sex
snds = snds.drop_duplicates(subset=['name','sex'], keep = 'first')
snds = snds.sort_values(by='name')
snds = snds[snds.duplicated(subset = 'name', keep=False)]

snds

rss = [rd[e] for e in rd if len(rd[e][rd[e]['sex'] != '']['sex'].unique()) == 1] #rss – races with single sex

ar['grflc'] = ar['group raw'].str.upper().str[0] #grflc – group raw first letter capital
grncs = ar[(ar['grflc'].isin(['M','F']))&(ar['sex']!=ar['grflc'])] #grncs – group raw not consistent with sex

ar.loc[grncs.index, 'sex'] = grncs['grflc']

ar[(ar['sex'] == '')&(ar['name'] != '')]

ar.loc[arns.index, 'sex'] = 'F'

if len(arns) == 1 and arns['name'].iloc[0] == 'Stather Emily':
    ar.loc[arns.index, 'sex'] = 'F'
else:
    raise Exception('Different scenario!')

ar['fn'] = ar['name'].str.split().str[-1] #fn – first name
mfn = list(ar[ar['sex'] == 'M']['fn'].unique()) #mfn – male first names

ffn = list(ar[ar['sex'] == 'F']['fn'].unique()) #ffn – female first names

mffn = set(mfn).intersection(ffn) #mffn – male-female first names

armfn = ar[ar['fn'].isin(mffn)] #armfn – all records with male-female names

df = pd.DataFrame(armfn['fn'].value_counts())
df = df.rename(columns={'fn':'total'})

df['M'] = armfn[armfn['sex'] == 'M']['fn'].value_counts()
df['F'] = armfn[armfn['sex'] == 'F']['fn'].value_counts()

df.sort_values(by = 'F', ascending=False)

import gender_guesser.detector as gg

d = gg.Detector()
d.get_gender(u'Oleg')

d.get_gender(u'Evgeniya')

d.names['Andrea']