{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# IPython notebook for ultimateposeur.com article" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "This contains the code for an ultimateposeur.com article: Is Carlos Carballo The Most Ruthless Referee Of Euro 2016?.\n", "\n", "(The article has also been republished by an Indian national newspaper The Hindu in their web-exclusive thREAD section.)\n", "\n", "For the github repository click here.\n", "\n", "Each of the code blocks begins with a commented out section explaining what it does.\n", "\n", "To run this notebook, install Jupyter/IPython notebook first.\n", "\n", "Then download this notebook, open it with Jupyter/IPython and press SHIFT+ENTER in each code block to execute it." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": true }, "outputs": [], "source": [ "#Coming from the journalism world, i've always been surprised by how programming/coding is such a \n", "#collaborative effort and how people have no issues building off others' ideas and exchanging info, \n", "#in fact they welcome it.\n", "#but if i want to sell this as journalism, i'll have to do some 'full disclosure' preliminaries.\n", "#so bits and pieces of the code are from stackoverflow, github, udacity, \n", "#pythonlearn.com, pycon and scipy videos on youtube, sundry google results etc.\n", "#these are all too numerous to recount, so while the individual parts of this aren't original, \n", "#the assembly and piecing together of it is original and my own. If anyone sees \n", "#a snippet of their code and they want credit for it in the code blocks below\n", "#let me know (ultimateposeur@gmail.com) and i'll put a line in.\n", "\n", "\n", "# the code for how i scraped everything won't make sense \n", "#unless you're familiar with the soccerway page structure,\n", "#but i'm putting this out there in the interest of transparency\n", "#and reproducibility. This way you can see that I kind of know\n", "#what I'm doing! So i've seen braindumps like this \n", "#on the net from others, and while i never really go through\n", "#the whole thing, there's usually a piece of code in there that\n", "#i can borrow, so hopefully there'll be some google result\n", "#that will lead someone to this page and they find \n", "#something in it useful!" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": true }, "outputs": [], "source": [ "#so this first part is about getting data from soccerway.com\n", "\n", "\n", "#First I noted down the urls of the referee pages I was interested in \n", "#and saved it in 'referee_pages.txt' (check github repo). This is where I \n", "#start my crawling from. The pages i'm downloading here have a list of \n", "#all the matches of a referee, in some cases going back 10 years\n", "\n", "#just realised i'm probably not using this notebook right\n", "#some notebooks are really gussied up with all sorts of \n", "#fancy bold headings etc., I think I'd rather just go \n", "#stream of consciousness right now.\n", "\n", "import re\n", "\n", "reflist = open('referee_pages.txt')\n", "refids = []\n", "for i in reflist:\n", " refid = (re.findall(r'\\d+', i))[0]\n", " refids.append(refid)\n", "print refids \n", "\n", "from random import randint\n", "import time\n", "import requests\n", "\n", "a = 'http://us.soccerway.com/a/block_player_matches?block_id=page_player_1_block_player_matches_4&callback_params=%7B%22page%22%3A%22%22%2C%22bookmaker_urls%22%3A%5B%5D%2C%22block_service_id%22%3A%22player_matches_block_playermatches%22%2C%22people_id%22%3A%22'\n", "c = '%22%2C%22type%22%3A%22%22%2C%22formats%22%3A%22%22%7D&action=changePage¶ms=%7B%22page%22%3A'\n", "e = '%7D'\n", "\n", "headersx = {\n", " 'Host':'us.soccerway.com',\n", " 'User-Agent':'Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:45.0) Gecko/20100101 Firefox/45.0',\n", " 'Accept':'text/javascript, text/html, application/xml, text/xml, */*',\n", " 'Accept-Language':'en-US,en;q=0.5',\n", " 'Accept-Encoding':'gzip, deflate',\n", " 'X-Requested-With':'XMLHttpRequest',\n", " 'X-Prototype-Version':'1.7',\n", " 'Referer':'http://us.soccerway.com/',\n", " 'Cookie':'__utma=254272778.1038598711.1464594149.1464631260.1464644537.7; __utmc=254272778; __utmz=254272778.1464603708.3.3.utmcsr=google|utmccn=(organic)|utmcmd=organic|utmctr=(not%20provided); s_cc=true; s_fid=0B0C5F8BE6B3EC61-1954A0A8D6FC41BE; s_nr=1464644536442-Repeat; s_ppv=-%2C45%2C45%2C671; s_sq=%5B%5BB%5D%5D; ev4=jonas%20erikkson; __utma=58539694.601279174.1464635680.1464635680.1464635680.1; __utmc=58539694; __utmz=58539694.1464635680.1.1.utmcsr=(direct)|utmccn=(direct)|utmcmd=(none); prevPage=Players%20%3A%20Pavel%20Kralovec%20%3A%20matches; __utmb=254272778.1.10.1464644537; __utmt=1',\n", " 'Connection':'keep-alive'\n", "}\n", "#if i had to do this differently, I'd put everything into a sub-directory, all the files were just downloaded into my working directory!\n", "\n", "for b in refids:\n", " d = 0\n", " while d > -21:\n", " urlx = a + b + c + str(d) + e\n", " responsex = requests.get(urlx, headers = headersx)\n", " if d == 0:\n", " dz = '-00'\n", " else:\n", " dz = str(d).zfill(3)\n", " fname = b + dz + '.html'\n", " with open(fname, \"w\") as filex:\n", " filex.write(responsex.content)\n", " d -= 1\n", " print fname\n", " delay = randint(10,20)\n", " time.sleep(delay)\n", " else:\n", " continue\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": true }, "outputs": [], "source": [ "#executing the command above will download a whole lot of files\n", "#that are lists of matches related to each referee\n", "#they'll get downloaded into your directory\n", "#as I mentioned in the code, it probably would have been better\n", "#to put it all into a sub-directory\n", "#this code block is just about me creating subdirectories for each \n", "#referee, so that I can download each match file and put it into\n", "#the directory for the respective referee\n", "\n", "#refdeux below is the list of referee ids in soccerway.com\n", "\n", "import os, sys\n", "\n", "refdeux = ['44515', '44615', '92755', '44516', '45012', '45089', '44971', '85292', '44678', '44486', '44867', '87294', '45554', '44902', '44754', '45059', '45511', '45077']\n", "\n", "for i in refdeux:\n", " pathx = '/media/C-drive/Users/shijithpk/Downloads/ipython/2016_05_30_referees/' + i + '-full'\n", " os.mkdir(pathx)\n", "\n", "#so the line above is the path to my working directory. You'll have to replace it with your own path.\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": true }, "outputs": [], "source": [ "#this code block is about me scraping the lists of match \n", "#documents from soccerway.com\n", "#'edict' the dictionary below has the fields that I noticed\n", "# each document has\n", "#but the only thing that matters in those documents are links\n", "#to the actual matches for each referee\n", "#what we've gotten so far from soccerway is just 'lists' of matches\n", "#and just to experiment with creating json files\n", "#all this data was dumped into json files in each\n", "#referee directory\n", "\n", "import json\n", "from lxml import html\n", "import sys\n", "import os\n", "\n", "refdeux = ['44515', '44615', '92755', '44516', '45012', '45089', '44971', '85292', '44678', '44486', '44867', '87294', '45554', '44902', '44754', '45059', '45511', '45077']\n", "\n", "for i in refdeux:\n", " a, b, c, d, e, f, g, h, l = ([] for m in range(9))\n", " edict = dict.fromkeys(['Referee', 'Date', 'Competition', 'Competition_link', 'Home_team', 'Score', 'Match_link', 'Away_team', 'Events'])\n", "\n", "\n", " for j in range(0,21):\n", " k = str(j).zfill(2)\n", " fnamex = i + '-' + k + '.html'\n", " try:\n", " fhandle = open(fnamex)\n", " sourcex = fhandle.read()\n", " jsource = json.loads(str(sourcex))\n", " contentx = jsource['commands'][0]['parameters']['content']\n", " treex = html.document_fromstring(contentx)\n", " trs = treex.cssselect('table tbody tr')\n", "\n", " for tr in trs:\n", " try:\n", " a.append(tr.cssselect('td:nth-child(2)')[0].text_content())\n", " b.append(tr.cssselect('td:nth-child(3)')[0].text_content())\n", " xy = 'http://us.soccerway.com' + tr.cssselect('td:nth-child(3) a')[0].attrib['href']\n", " c.append(xy)\n", " d.append(tr.cssselect('td:nth-child(4)')[0].text_content())\n", " e.append(tr.cssselect('td:nth-child(5)')[0].text_content())\n", " yz = 'http://us.soccerway.com' + tr.cssselect('td:nth-child(5) a')[0].attrib['href']\n", " f.append(yz)\n", " g.append(tr.cssselect('td:nth-child(6)')[0].text_content())\n", " h.append(tr.cssselect('td:nth-child(7)')[0].text_content())\n", " l.append(i)\n", " except:\n", " print \"A row wasnt scraped here.\"\n", " except:\n", " print fnamex + ' didnt work'\n", " print fnamex + ' done'\n", "\n", " \n", " edict['Referee'] = l\n", " edict['Date'] = a\n", " edict['Competition'] = b\n", " edict['Competition_link'] = c\n", " edict['Home_team'] = d\n", " edict['Score'] = e\n", " edict['Match_link'] = f\n", " edict['Away_team'] = g\n", " edict['Events'] = h\n", " filenamey = i + '-full/' + i + '-collated.json'\n", " fnamez = os.path.join(os.path.dirname(os.path.realpath('__file__')), filenamey)\n", " json.dump(edict, open(fnamez , 'w')) #used json here instead of csv's just so that I could practise creating json files\n", " print i + ' done'\n", "\n", "print 'done, done'" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": true }, "outputs": [], "source": [ "#get the values from each key\n", "#download each file, set headers, set timeout between requests\n", "#make sure files get downloaded into the right folders\n", "\n", "#so this is where the bulk of the downloading actuall took \n", "#place. Because of the 10-20 second delay i placed between requests, it took\n", "#around 20 hours or so to download all the files\n", "#so what's happening now is files for each match officiated \n", "#by each referee (or at least the matches that are there in \n", "#soccerway's database), these files are being downloaded\n", "\n", "#i decided it would be better to just download all the files\n", "#and do my scraping for the information i need on yellow cards\n", "#times of booking, etc. , do that scraping on my local archive\n", "\n", "#so that way even if I get my scraping code wrong, it wont\n", "#be an issue because i just start over\n", "#if i was doing that trial and error by scraping the information\n", "#off the internet directly, i would have just been \n", "#requesting files over and over again. This local archive would \n", "#avoid that\n", "\n", "#also note that the output can get pretty verbose for this\n", "#so if you want you can comment out the print statement\n", "#and make it so so that there's only something printed \n", "#if somehting goes wrong\n", "\n", "#all data downloaded on may 31 & june 1\n", "\n", "\n", "from random import randint\n", "import time\n", "import requests\n", "import json\n", "import sys\n", "import os\n", "\n", "#furl = '/44486-full/44486-collated.json'\n", "\n", "refdeux = ['44515', '44615', '92755', '44516', '45012', '45089', '44971', '85292', '44678', '44486', '44867', '87294', '45554', '44902', '44754', '45059', '45511', '45077']\n", "\n", "headerz = {\n", " 'Host':'us.soccerway.com',\n", " 'User-Agent':'Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:45.0) Gecko/20100101 Firefox/45.0',\n", " 'Accept':'text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8',\n", " 'Accept-Language':'en-US,en;q=0.5',\n", " 'Accept-Encoding':'gzip, deflate',\n", " 'Referer':'http://us.soccerway.com/',\n", " 'Cookie':'__utma=254272778.1038598711.1464594149.1464682768.1464701479.10; __utmc=254272778; __utmz=254272778.1464603708.3.3.utmcsr=google|utmccn=(organic)|utmcmd=organic|utmctr=(not%20provided); s_cc=true; s_fid=0B0C5F8BE6B3EC61-1954A0A8D6FC41BE; s_nr=1464701530831-Repeat; s_ppv=-%2C45%2C45%2C671; s_sq=%5B%5BB%5D%5D; ev4=jonas%20erikkson; __utma=58539694.601279174.1464635680.1464635680.1464635680.1; __utmc=58539694; __utmz=58539694.1464635680.1.1.utmcsr=(direct)|utmccn=(direct)|utmcmd=(none); prevPage=Players%20%3A%20William%20Collum%20%3A%20matches; __utmb=254272778.3.10.1464701480; __utmt=1',\n", " 'Connection':'keep-alive'\n", "}\n", "\n", "for i in refdeux:\n", " filenamey = i + '-full/' + i + '-collated.json'\n", " fnamez = os.path.join(os.path.dirname(os.path.realpath('__file__')), filenamey)\n", " with open(fnamez) as fx:\n", " a = json.load(fx)\n", " urllist = a['Match_link']\n", " print i + '-collated.json ' + ' len-urllist ' + str(len(urllist))\n", " d = 0\n", " for urlx in urllist:\n", " print 'The url in question: ' + urlx\n", " try:\n", " responsex = requests.get(urlx, headers = headerz)\n", " d += 1\n", " zrs = str(d).zfill(3)\n", " fnad = i + '-full/' + i + '-' + zrs + '.html'\n", " fnadr = os.path.join(os.path.dirname(os.path.realpath('__file__')), fnad)\n", " with open(fnadr, \"w\") as filex:\n", " filex.write(responsex.content)\n", " print fnad + ' successfully written'\n", " delay = randint(10,20)\n", " time.sleep(delay)\n", " except:\n", " print \"Something went wrong here.\"\n", " print i + ' done'\n", " \n", "print 'done and done'" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": true }, "outputs": [], "source": [ "#grr, have kind of forgotten what i've done a few things for!\n", "#this wasnt' a straightforward a to b to c thing\n", "# it was a to b, then back to a, then c, then back to a, \n", "#you get the idea, i've done so many things, that now i realize\n", "# i should comment out code whenever i do anything\n", "#so that if i have to remind myself why i did something\n", "#at least there's some note or something to help me out\n", "#i'm writing all these comments a week after\n", "#so anyway I figured out what this block was for \n", "# it was to get the no. of matches for each refeee that soccerwy\n", "#has recorded data for, just wanted to have a sense of what i was getting into\n", "\n", "\n", "import json\n", "import sys\n", "import os\n", "\n", "\n", "refdeux = ['44515', '44615', '92755', '44516', '45012', '45089', '44971', '85292', '44678', '44486', '44867', '87294', '45554', '44902', '44754', '45059', '45511', '45077']\n", "fdict = dict.fromkeys(['refid', 'matches'])\n", "a = []\n", "b = []\n", "\n", "for i in refdeux:\n", " a.append(i)\n", " filenamey = i + '-full/' + i + '-collated.json'\n", " fnamez = os.path.join(os.path.dirname(os.path.realpath('__file__')), filenamey)\n", " with open(fnamez) as fx:\n", " d = json.load(fx)\n", " urllist = d['Match_link']\n", " b.append(str(len(urllist)))\n", "fdict['refid'] = a\n", "fdict['matches'] = b" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": true }, "outputs": [], "source": [ "#so the code below is about creating the main dataset\n", "#with info on each card, what type it was - yellow, second yellow\n", "#or red, who was booked, what time they were booked, the \n", "# team they were playing for at the time etc.\n", "\n", "#again the output for this is verbose, so if you're \n", "#reproducing this, you might want to cut down on the \n", "#print statements\n", "\n", "import re\n", "from lxml import html\n", "import sys\n", "import os\n", "import csv\n", "\n", "reflist = open('referee_pages.txt')\n", "refid_list = []\n", "refname_list = []\n", "#am sure there's a shorter way to do this, couldnt figure it out, what i'm doing below is \n", " #taking an element out of one list and appending it to another list!\n", "for i in reflist: \n", " refidx = (re.findall(r'\\d+', i))[0]\n", " refid_list.append(refidx)\n", " refnamex = (re.findall(r'http://us.soccerway.com/referees/(.*)/.*/matches/', i))[0] \n", " #extract name without diacritics, there was a turkish referee name that python completely mangled, this is safer\n", " refname_list.append(refnamex)\n", "\n", " \n", "headings = ['file_name', 'ref_code', 'ref_name_in_url', 'ref_nationality','match_code', 'match_duration',\n", " 'home_team_in_url', 'away_team_in_url', 'date', 'competition','winning_team', \n", " 'card_type','minute_card_given', 'player_name_in_url', 'player_nationality_in_url']\n", "\n", "#write/stream to a csv file, don't hold this in memory\n", "\n", "with open(\"dataset_main.csv\", \"w\") as filex: #after this change mode to a for append, otherwise file gets overwritten\n", " wr = csv.writer(filex, delimiter = ',' , quotechar = '\"' )\n", " wr.writerow(headings)\n", "\n", "\n", "for i in refid_list:\n", " d = range(1,450)\n", " for x in d:\n", " zrs = str(x).zfill(3)\n", " fnad = i + '-full/' + i + '-' + zrs + '.html'\n", " fnadr = os.path.join(os.path.dirname(os.path.realpath('__file__')), fnad)\n", " try:\n", " with open(fnadr) as filey:\n", " row_list = []\n", " sourcex = filey.read()\n", " treex = html.document_fromstring(sourcex)\n", " #check if referee isn't assistant referee or fourth official\n", " if i not in treex.cssselect('dl.details > dd:nth-child(2) > a:nth-child(1)')[0].attrib['href']:\n", " continue\n", " #append to the row list in the same order\n", " row_list.append(fnad)\n", " ref_code = i\n", " row_list.append(ref_code)\n", " ref_pos = refid_list.index(i)\n", " ref_name_in_url = refname_list[ref_pos]\n", " row_list.append(ref_name_in_url)\n", " ref_nationality = treex.cssselect('dl.details > dd:nth-child(2) > a:nth-child(1)')[0].attrib['class']\n", " ref_natx = (re.findall(r'flag_16 left_16 (.+?)_16_left referee', ref_nationality))[0]\n", " row_list.append(ref_natx)\n", " match_code = treex.cssselect('#submenu > ul:nth-child(1) > li:nth-child(1) > a:nth-child(1)')[0].attrib['href']\n", " match_codex = (re.findall(r'/matches/.*/.*/.*/.*/.*/.*/.*/(.*)/', match_code))[0]\n", " row_list.append(match_codex)\n", " try:\n", " if (treex.cssselect('.scoretime > span:nth-child(1)')[0].text_content() or treex.cssselect('.scoretime > span:nth-child(2)')[0].text_content()) == ('E' or 'P'):\n", " match_duration = 120\n", " row_list.append(match_duration)\n", " except:\n", " match_duration = 90\n", " row_list.append(match_duration)\n", " home_team_in_url = treex.cssselect('#page_match_1_block_match_info_4 > div:nth-child(1) > div:nth-child(1) > h3:nth-child(1) > a:nth-child(1)')[0].attrib['href']\n", " home_teamx = (re.findall(r'/teams/(.*/.*)/.*/', home_team_in_url))[0]\n", " row_list.append(home_teamx)\n", " away_team_in_url = treex.cssselect('#page_match_1_block_match_info_4 > div:nth-child(1) > div:nth-child(3) > h3:nth-child(1) > a:nth-child(1)')[0].attrib['href']\n", " away_teamx = (re.findall(r'/teams/(.*/.*)/.*/', away_team_in_url))[0]\n", " row_list.append(away_teamx)\n", " date_match = treex.cssselect('div.details:nth-child(1) > dl:nth-child(1) > dd:nth-child(4) > a:nth-child(1)')[0].attrib['href']\n", " date_matchx = (re.findall(r'/.*/(.*/.*/.*)/', date_match))[0]\n", " row_list.append(date_matchx)\n", " competition = treex.cssselect('div.details:nth-child(1) > dl:nth-child(1) > dd:nth-child(2) > a:nth-child(1)')[0].attrib['href']\n", " compx = (re.findall(r'/(.*/.*/.*)/.*/.*/.*/', competition))[0]\n", " row_list.append(compx)\n", " scorex = treex.cssselect('.bidi')[0].text_content()\n", " score_list = scorex.split()\n", " if score_list[0] > score_list[2]:\n", " winning_team = home_teamx\n", " row_list.append(winning_team)\n", " elif score_list[0] < score_list[2]:\n", " winning_team = away_teamx\n", " row_list.append(winning_team)\n", " else:\n", " winning_team = 'draw'\n", " row_list.append(winning_team)\n", " cdx = treex.xpath(\"//img[contains(@src,'YC.png')]|//img[contains(@src,'RC.png')]|//img[contains(@src,'Y2C.png')]\")\n", " if cdx == []: #this is just a check for matches without any yellow or red cards\n", " row_final = list(row_list)\n", " row_expand = list()\n", " card_type = 'zero'\n", " row_expand.append(card_type)\n", " minute_card_given = ''\n", " row_expand.append(minute_card_given)\n", " player_name_in_url = ''\n", " row_expand.append(player_name_in_url)\n", " player_nationality_in_url = ''\n", " row_expand.append(player_nationality_in_url)\n", " row_final.extend(row_expand)\n", " with open(\"dataset_main.csv\", \"a\") as filez:\n", " wrz = csv.writer(filez, delimiter = ',' , quotechar = '\"' )\n", " wrz.writerow(row_final)\n", " else:\n", " for cd in cdx:\n", " row_final = list(row_list)\n", " row_expand = []\n", " #make sure youve taken care of all potential slip-ups - mentioned in excel sheet\n", " card_type = cd.xpath('@src')[0]\n", " card_typex = (re.findall(r'http://.*/.*/.*/.*/.*/(.*).png', card_type))[0]\n", " row_expand.append(card_typex)\n", " minute_card_given = cd.xpath('../text()')[0]\n", " row_expand.append(minute_card_given)\n", " #the values in this are weird, some leading white spaces, minute mark at end, 90 + 4 etc., will need further work\n", " player_name_in_url = cd.xpath('preceding::td[1]//a[1]/@href')[0]\n", " player_name_in_urlx = (re.findall(r'/.*/(.*)/.*/', player_name_in_url))[0]\n", " row_expand.append(player_name_in_urlx)\n", " player_nationality_in_url = cd.xpath('preceding::td[1]//a[1]/@class')[0]\n", " player_nationality_in_urlx = (re.findall(r'flag_16 left_16 (.+?)_16_left', player_nationality_in_url))[0]\n", " row_expand.append(player_nationality_in_urlx)\n", " row_final.extend(row_expand)\n", " with open(\"dataset_main.csv\", \"a\") as filez:\n", " wrz = csv.writer(filez, delimiter = ',' , quotechar = '\"' )\n", " wrz.writerow(row_final)\n", " except:\n", " print 'No file named ', fnadr, ' forget about it.'\n", "\n", "filez.close() #dont forget to close the csv file once you've finished appending everything\n", "\n", "print 'done and done'" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": true }, "outputs": [], "source": [ "#now that i have my main dataset, will be doing some cleaning on it\n", "#\n", "\n", "import pandas as pd\n", "\n", "dfg = pd.read_csv('dataset_main.csv')\n", "\n", "#so i load my csv into a dataframe\n", "\n", "dfg['card_type'].unique()\n", "dfg_filtered = dfg[dfg['card_type'].isin(['YC', 'zero', 'RC', 'Y2C'])]\n", "len(dfg_filtered)\n", "len(dfg)\n", "\n", "# did the commands above to check if everything is kosher\n", "#turns out some of the pages werent' formatted right\n", "#so the wrong info came into the card type column\n", "#\n", "\n", "import numpy as np\n", "\n", "np.sort(dfg_filtered['minute_card_given'].unique())\n", "\n", "#this command above will show you that again some rows\n", "#didn't record the time of booking properly, so will be \n", "#filtering those rows out in the line below\n", "\n", "\n", "dfg_for_A = dfg_filtered[dfg_filtered['minute_card_given'] != \" '\"]\n", "\n", "dfg_for_A.to_csv('dataset_for_A_v2.csv')\n", "dfg_filtered.to_csv('dataset_B_filelist.csv')\n", "\n", "#this dataset A covers around 50 fewer matches than dataset B\n", "#will be using dataset A for time-related analyses and maybe a few other things\n", "#And will be using dataset B to generate a list of files with \n", "#more or less proper formatting so they can be scraped\n", "#not having the proper minute data in dataset B wont matter given my plans for it.\n", "\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": true }, "outputs": [], "source": [ "#i ended up not using the data resulting from the code below\n", "#this data could help us look at players from which nationalities get penalised more by referees\n", "#will be using this data for later article (I call dibs! please dont steal my story idea)\n", "#only included this code because i used to create the first graphic in the real article on which\n", "#referees are the card happiest\n", "\n", "import re\n", "from lxml import html\n", "import sys\n", "import os\n", "import csv\n", "import pandas as pd\n", "\n", "df = pd.read_csv('dataset_B_filelist.csv')\n", "nats = df.player_nationality_in_url.unique().tolist()\n", "#if you see the results you'll see that the value between australia and cameroon is some weird value\n", "#nats.index('australia')\n", "#nats.index('cameroon')\n", "#nats[27]\n", "del nats[27]\n", "df_heads = list(df)\n", "df_heads_new = df_heads[:11]\n", "headingsx = df_heads_new + nats\n", "filenames_deux = df.file_name.unique().tolist()\n", "\n", "with open(\"dataset_B_cards.csv\", \"w\") as filex: #after this change mode to a for append, otherwise file gets overwritten\n", " wr = csv.writer(filex, delimiter = ',' , quotechar = '\"' )\n", " wr.writerow(headingsx)\n", "\n", "with open(\"dataset_B_total.csv\", \"w\") as filez: #after this change mode to a for append, otherwise file gets overwritten\n", " wr = csv.writer(filez, delimiter = ',' , quotechar = '\"' )\n", " wr.writerow(headingsx)\n", "\n", "for deux in filenames_deux:\n", " fnadr = os.path.join(os.path.dirname(os.path.realpath('__file__')), deux)\n", " with open(fnadr) as filey:\n", " row_listc = []\n", " row_listt = []\n", " sourcex = filey.read()\n", " treex = html.document_fromstring(sourcex)\n", " row_listc.append(fnad)\n", " row_listt.append(fnad)\n", " ref_code = fnad[0:5]\n", " row_listc.append(ref_code)\n", " row_listt.append(ref_code)\n", " ref_name_url = treex.cssselect('dl.details > dd:nth-child(2) > a:nth-child(1)')[0].attrib['href']\n", " ref_name_urlx = (re.findall(r'/referees/(.*)/.*/', ref_name_url))[0]\n", " row_listc.append(ref_name_urlx)\n", " row_listt.append(ref_name_urlx)\n", " ref_nationality = treex.cssselect('dl.details > dd:nth-child(2) > a:nth-child(1)')[0].attrib['class']\n", " ref_natx = (re.findall(r'flag_16 left_16 (.+?)_16_left referee', ref_nationality))[0]\n", " row_listc.append(ref_natx)\n", " row_listt.append(ref_natx)\n", " match_code = treex.cssselect('#submenu > ul:nth-child(1) > li:nth-child(1) > a:nth-child(1)')[0].attrib['href']\n", " match_codex = (re.findall(r'/matches/.*/.*/.*/.*/.*/.*/.*/(.*)/', match_code))[0]\n", " row_listc.append(match_codex)\n", " row_listt.append(match_codex)\n", " try:\n", " if (treex.cssselect('.scoretime > span:nth-child(1)')[0].text_content() or treex.cssselect('.scoretime > span:nth-child(2)')[0].text_content()) == ('E' or 'P'):\n", " match_duration = 120\n", " row_listc.append(match_duration)\n", " row_listt.append(match_duration)\n", " except:\n", " match_duration = 90\n", " row_listc.append(match_duration)\n", " row_listt.append(match_duration)\n", " home_team_in_url = treex.cssselect('#page_match_1_block_match_info_4 > div:nth-child(1) > div:nth-child(1) > h3:nth-child(1) > a:nth-child(1)')[0].attrib['href']\n", " home_teamx = (re.findall(r'/teams/(.*/.*)/.*/', home_team_in_url))[0]\n", " row_listc.append(home_teamx)\n", " row_listt.append(home_teamx)\n", " away_team_in_url = treex.cssselect('#page_match_1_block_match_info_4 > div:nth-child(1) > div:nth-child(3) > h3:nth-child(1) > a:nth-child(1)')[0].attrib['href']\n", " away_teamx = (re.findall(r'/teams/(.*/.*)/.*/', away_team_in_url))[0]\n", " row_listc.append(away_teamx)\n", " row_listt.append(away_teamx)\n", " date_match = treex.cssselect('div.details:nth-child(1) > dl:nth-child(1) > dd:nth-child(4) > a:nth-child(1)')[0].attrib['href']\n", " date_matchx = (re.findall(r'/.*/(.*/.*/.*)/', date_match))[0]\n", " row_listc.append(date_matchx)\n", " row_listt.append(date_matchx)\n", " competition = treex.cssselect('div.details:nth-child(1) > dl:nth-child(1) > dd:nth-child(2) > a:nth-child(1)')[0].attrib['href']\n", " compx = (re.findall(r'/(.*/.*/.*)/.*/.*/.*/', competition))[0]\n", " row_listc.append(compx)\n", " row_listt.append(compx)\n", " scorex = treex.cssselect('.bidi')[0].text_content()\n", " score_list = scorex.split()\n", " if score_list[0] > score_list[2]:\n", " winning_team = home_teamx\n", " row_listc.append(winning_team)\n", " row_listt.append(winning_team)\n", " elif score_list[0] < score_list[2]:\n", " winning_team = away_teamx\n", " row_listc.append(winning_team)\n", " row_listt.append(winning_team)\n", " else:\n", " winning_team = 'draw'\n", " row_listc.append(winning_team)\n", " row_listt.append(winning_team)\n", " #create player set using | operator\n", " players = treex.xpath(\"//*[@id='page_match_1_block_match_lineups_8']//a[contains(@href,'players')]|//*[@id='page_match_1_block_match_substitutes_9']//p[@class='substitute substitute-in']/a[contains(@href,'players')]/following::img[contains(@src,'SI.png')][1]/preceding::a[1]\")\n", " for nat in nats:\n", " cards = 0\n", " totalx = 0\n", " for player in players:\n", " player_class = player.xpath('@class')[0]\n", " if nat == re.findall(r'flag_16 left_16 (.+?)_16_left', player_class)[0]:\n", " #set up card finder\n", " if player.xpath(\"following::td[1]//img[contains(@src,'YC.png') or contains(@src,'RC.png') or contains(@src,'Y2C.png')]\") != []:\n", " cards += 1\n", " totalx += 1\n", " else:\n", " totalx += 1\n", " else:\n", " continue\n", " row_listc.append(cards)\n", " row_listt.append(totalx)\n", " \n", " with open(\"dataset_B_cards.csv\", \"a\") as filex: \n", " wr = csv.writer(filex, delimiter = ',' , quotechar = '\"' )\n", " wr.writerow(row_listc)\n", "\n", " with open(\"dataset_B_total.csv\", \"a\") as filez: \n", " wr = csv.writer(filez, delimiter = ',' , quotechar = '\"' )\n", " wr.writerow(row_listt)\n", "\n", "filex.close()\n", "filez.close()\n", "print 'done and done'\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": true }, "outputs": [], "source": [ "#code below was written to figure out which referees\n", "# are particularly trigger happy when it comes to bookings and cards\n", "#\n", "\n", "df_world_total = pd.read_csv('dataset_B_total.csv')\n", "df_world_cards = pd.read_csv('dataset_B_cards.csv')\n", "\n", "#dont remember why but for some reason, i wasnt happy with \n", "#the dataframe loaded from the csv, so i did some slicing\n", "#below to create a new one\n", "\n", "df_world_cards.columns.get_loc(\"northern-ireland\")\n", "df_world_cards.columns.get_loc(\"equatorial-guinea\")\n", "df_world_cards.columns.get_loc(\"ref_name_in_url\")\n", "df_world_cards.columns.get_loc(\"match_code\")\n", "df_world_cards.columns.get_loc(\"competition\")\n", "\n", "\n", "card_slices = [df_world_cards.ix[:,2], df_world_cards.ix[:,4],df_world_cards.ix[:,9],df_world_cards.ix[:,11:160]]\n", "df_world_cards_new = pd.concat(card_slices, axis=1)\n", "\n", "total_slices = [df_world_total.ix[:,2], df_world_total.ix[:,4],df_world_total.ix[:,9],df_world_total.ix[:,11:160]]\n", "df_world_total_new = pd.concat(total_slices, axis=1)\n", "\n", "#these four lines below sum up the number of appearances \n", "#the 'total new sum' dataframe and cards for all players \n", "#in all matches they have appeared in\n", "#note that this wont take into cards for players\n", "#whey they've been on the subs bench (that happens sometimes)\n", "\n", "df_world_total_new_sum = df_world_total_new.groupby('ref_name_in_url').sum()\n", "df_world_cards_new_sum = df_world_cards_new.groupby('ref_name_in_url').sum()\n", "\n", "#this is just a simple no. of cards for all players divided by\n", "#no of appearances for all players\n", "\n", "b = df_world_cards_new_sum.sum(axis=1)\n", "c = df_world_total_new_sum.sum(axis=1)\n", "df_world_ratio_new = (b/c)*100\n", "\n", "graphic_A = [e, c, b, df_world_ratio_new]\n", "df_for_graphic_A = pd.concat(graphic_A, axis=1)\n", "\n", "df_for_graphic_A.columns = ['no_of_matches', 'player_appearances', 'cards', 'cards_to_appearance_ratio']\n", "df_for_graphic_A.to_csv('graphic_A.csv')\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": true }, "outputs": [], "source": [] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": true }, "outputs": [], "source": [ "import pandas as pd\n", "\n", "dfg = pd.read_csv('dataset_for_A_v2.csv')\n", "del dfg['Unnamed: 0']\n", "#deleting the index that got added on as a column for some reason\n", "dfg.minute_card_given.fillna('',inplace=True)\n", "dfg.player_name_in_url.fillna('zero',inplace=True)\n", "dfg.player_nationality_in_url.fillna('zero',inplace=True)\n", "\n", "#did the commands above because there were a lot of null \n", "#values created because of the matches where there were no \n", "#cards, so just filled them up with 'zero'\n", "dfg['minute_card_given'] = dfg['minute_card_given'].str.strip(\" \") #remove whitespace at beginning\n", "dfg['minute_card_given'] = dfg['minute_card_given'].str.strip(\"'\") #remove minute mark (')\n", "dfg[dfg['minute_card_given'].str.contains(r\"\\+\")].groupby('minute_card_given').match_code.nunique()\n", "#checking how many cards are given in injury time\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": true }, "outputs": [], "source": [ "#so because there are a lot of cards given in injury time\n", "# there are a lot of cards where the minutes added on are denoted\n", "#by '+', so 45+3, 90+1 etc.\n", "#this was messing up with my calculations because they werent\n", "#being recognised as numbers, so i wrote the code below\n", "#to take care of this by, wait for it, extending each half by\n", "#five minutes! so the second half starts at minute 50 instead of \n", "#45, if there's extra time, extra time starts at minute 100\n", "#instead of 90 etc. I did all this because i was thinking\n", "#of doing something related to mean time between cards\n", "#but dropped that idea later\n", "\n", "fake_list4 = []\n", "for s in dfg['minute_card_given']:\n", " if '+' in s:\n", " t = s.split(\"+\")\n", " if int(t[0]) == 45:\n", " if int(t[1]) > 5:\n", " v = 50\n", " elif int(t[1]) <= 5:\n", " v = 45 + int(t[1])\n", " elif int(t[0]) == 90:\n", " if int(t[1]) > 5:\n", " v = 100\n", " elif int(t[1]) <= 5:\n", " v = 95 + int(t[1])\n", " elif int(t[0]) == 105:\n", " v = 115\n", " elif int(t[0]) == 120:\n", " v = 130\n", " fake_list4.append(v)\n", " else:\n", " try:\n", " t = int(s)\n", " if t > 90:\n", " v = t + 10\n", " elif t > 45:\n", " v = t + 5\n", " elif t > 0:\n", " v = t\n", " fake_list4.append(v)\n", " except:\n", " fake_list4.append('none') \n", "\n", "fake_list4\n", "\n", "df_02= pd.DataFrame({'min_card_new': fake_list4})\n", "#am adding this transformed times column named 'min_card_new'\n", "#to the dataframe\n", "df_card_analysis = pd.concat([dfg, df_02], axis=1)\n", "\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": true }, "outputs": [], "source": [ "#taking out matches where there was no card given\n", "df_first_card = df_card_analysis[df_card_analysis['min_card_new'] != 'none']\n", "\n", "#now the analysis\n", "\n", "#this code below is to calculate the mean time of the first yellow and red card\n", "df_first_yellow = df_first_card[df_first_card['card_type'] == 'YC']\n", "df_first_red = df_first_card[df_first_card['card_type'] == 'RC']\n", "df_first_yellow_mean = df_first_yellow.groupby(['ref_name_in_url','file_name'], as_index=False)['min_card_new'].min().groupby('ref_name_in_url')['min_card_new'].mean()\n", "df_first_red_mean = df_first_red.groupby(['ref_name_in_url','file_name'], as_index=False)['min_card_new'].min().groupby('ref_name_in_url')['min_card_new'].mean()\n", "\n", "first_combo = [df_first_yellow_mean, df_first_red_mean]\n", "df_first_combo = pd.concat(first_combo, axis=1) #putting the two dataframes together\n", "\n", "df_first_combo.columns = ['first_yellow_mean', 'first_red_mean']\n", "#giving this dataframe new column names above\n", "\n", "df_first_combo.to_csv('graphic_first_card.csv')\n", "#putting the data in a csv so that i can use it in something \n", "#like Tableau to create the visualisation. Wasnt happy\n", "#with the options within the Python world, every graphic\n", "#looks so science-y, which is understandable given their \n", "#origins in academia, but all of them -- matplotlib, seaborn,\n", "#bokeh, they all look so bad!" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": true }, "outputs": [], "source": [ "#so we are trying to figure out how many times a referee took out a 2nd yellow card for a player and sent \n", "#him off. so to do that we first find out how many yellow cards were given by each ref\n", "#we then divide the no. of matches where theres a Y2C, ie. 2nd yellow leading to a red, and so we get the \n", "#conversion ratio for yellows to reds for each referee\n", "\n", "#len(df_first_card) = 19559\n", "df_card_04 = df_first_card[df_first_card['card_type'] == 'YC']\n", "#len(df_card_04) = 18459\n", "df_card_05 = df_card_04.groupby('ref_name_in_url')['min_card_new'].count()\n", "#this line above gives us total no of yellow cards per referee (excluding second yellows and straight reds)\n", "\n", "#now to get the no of matches where a second yellow led to a red\n", "#you do this by restricting the frame to matches where there are second yellows\n", "\n", "df_card_06 = df_first_card[df_first_card['card_type'] == 'Y2C']\n", "#this line below gives us total no of second yellows per referee (and excludes straight reds)\n", "df_card_07 = df_card_06.groupby('ref_name_in_url')['min_card_new'].count()\n", "\n", "df_card_08 = (df_card_07/df_card_05)*100\n", "#above is the calculation of the percentage of \n", "#yellow cards converted into second yellows\n", "#this is all possible because soccerway helpfully\n", "#has a second yellow category of card in addition\n", "#to the normal yellow and red card\n", "\n", "#the lines below concatenate the frames together and saves\n", "#everything in a csv file\n", "card_conversion = [df_card_05, df_card_07, df_card_08]\n", "df_card_convert = pd.concat(card_conversion, axis=1)\n", "df_card_convert_v2 = df_card_convert.reset_index()\n", "df_card_convert_v2.columns = ['referee_name', 'total_yellow_cards', 'total_second_yellows', 'ratio']\n", "df_card_convert_v2.to_csv('graphic_y_to_r.csv', index=False)\n", "\n", "\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": true }, "outputs": [], "source": [ "#for the next graphic, we find out who has historically pulled card after card after card in a match\n", "df_card_total_v2 = df_first_card.groupby('ref_name_in_url')['min_card_new'].count()\n", "#this gives us no. of cards per referee (yellows, second yellows and reds)\n", "#this is the base that we'll be using to calulate percentages of first cards, second cards, thrid cards etc.\n", "\n", "#df_first_card is the dataframe where the matches without any cards shown have been excluded\n", "\n", "df_card_percent_first = df_first_card.sort_values('min_card_new').groupby(['ref_name_in_url','file_name']).head(1).groupby('ref_name_in_url')['min_card_new'].count()\n", "\n", "#code below will help us calculate the percentages and save\n", "#the dataframe as a csv\n", "#being a newbie, the code below took me a few hours to figure out\n", "#probably the first time I defined my own function too\n", "#outside of an online tutorial\n", "\n", "def card(t):\n", " return df_first_card.sort_values('min_card_new').groupby(['ref_name_in_url','file_name']).head(t).groupby('ref_name_in_url')['min_card_new'].count()\n", "\n", "tot = df_card_total_v2\n", "t = 1\n", "sr_concat = []\n", "label_list = []\n", "sr_tot = pd.Series(index=df_card_total_v2.index, dtype='float64') #create empty series\n", "sr_tot = sr_tot.fillna(0) # fill with 0s rather than NaNs , thank you stackoverlow!\n", "sr_100v = pd.Series(data=100,index=df_card_total_v2.index, dtype='float64')\n", "#sr_100v = sr_tot.fillna(100.0) this wasnt working for some reason, could be because of some inplace nonsense\n", "\n", "print sr_100v\n", "\n", "while t < 6:\n", " print \"t= \", t\n", " z = ((card(t) - card(t-1))/tot)*100\n", " if z.isnull().any():\n", " xy = (card(t)/tot)*100\n", " else:\n", " xy = z\n", " print 'xy'\n", " print xy\n", " sr_concat.append(xy)\n", " sr_tot += xy\n", " #print 'sr_tot'\n", " #print sr_tot\n", "\n", " #print 'sr_concat'\n", " #print sr_concat\n", " label_name = 'card_' + str(t)\n", " label_list.append(label_name)\n", " t += 1\n", "\n", "print sr_tot\n", "xy = sr_100v - sr_tot\n", "print \"t=6\", 6\n", "#print 'xy'\n", "print xy\n", "sr_concat.append(xy)\n", "#print 'sr_concat'\n", "#print sr_concat\n", "label_name = 'the_rest'\n", "label_list.append(label_name)\n", "\n", "df_card_percent = pd.concat(sr_concat, axis=1) #putting the two dataframes together\n", "#df_card_convert_v2 = df_card_convert.reset_index() \n", "df_card_percent.columns = label_list\n", "df_card_percent.to_csv('graphic_card_percent.csv')\n", "#sometimes \", index = False\" works better when saving to a csv and the first column doesnt have a label\n", "\n", "print 'done and done'\n", "\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": true }, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "Python 2", "language": "python", "name": "python2" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 2 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython2", "version": "2.7.11+" } }, "nbformat": 4, "nbformat_minor": 0 }