- Use Python to gather data from internet sources, parse it and load it into..
- MongoDB where I store data in document format before...
- Analysing the data using R and...
- Presenting the results for you lucky people in Blogger!
So it was time to gather some data, load it and analyse it!
A quick look around the internet showed me a variety of sources, the first of which was:
http://www.football-data.co.uk/
This is a betting focussed website but has a set of free CSV files showing results from multiple European leagues going back to the early '90s, (older data is more sparse than more recent data). As I say, they offer their data for free but I urge you to say thanks to them as an ad funded website in the only way you can if you see what I mean.
Grabbing a file and looking at it in LibreOffice Calc shows there to be all sorts of interesting data available. Here's a screenshot showing data for the Belgian Pro league.
So much nice data to play with!!
Step 1- Getting All the Data
Looking at an example URL for one of the CSV files on football-data.co.uk shows it to be:
http://www.football-data.co.uk/mmz4281/1516/E0.csv
So here we see:
- A base URL - http://www.football-data.co.uk/mmz4281/
- Four digits indicating the year. Here 1516 means the season 2015-16
- The league in question. Here E0 means English Premier League
Hence it's pretty easy to write a Python script to iterate through the years (9394 to 1516) and leagues to grab all the CSV files. The script is below. The comments should explain all but in simple terms it iterates through a list of years (YearList) and for each year a list of leagues (LeagueList), forms a URL for a wget command and then renames the file so we can have all the leagues for all the years in the same folder.
#Downloading a bulk load of Football CSV files from http://www.football-data.co.
#Example URL is http://www.football-data.co.
import os
import time
#Constants
BaseURL = "http://www.football-data.co.
DirForFiles = "/home/pi/Documents/
#Next part is a set of digits that represent the year. Do these as a list
YearList = ['9394','9495','9596','9697','
1415','1516']
#Then the values that are used to represent leagues. Another List
LeagueList = ['E0','E1','E2','E3','EC','
#Iterate through the years
for TheYear in YearList:
#Now iterate through the leagues, forming the command to get
for TheLeague in LeagueList:
GetCommand = "sudo wget " + BaseURL + TheYear + "/" + TheLeague + ".csv"
#Also form the name the file will take when downloaded
FileWhenDownloaded = DirForFiles + TheLeague + ".csv"
#And the file name to rename to
RenameFileTo = DirForFiles + TheLeague + "_" + TheYear + ".csv"
#Run the wget command#
os.system(GetCommand)
time.sleep(0.5)
#Rename the file
RenameCommand = "sudo mv " + FileWhenDownloaded + " " + RenameFileTo
os.system(RenameCommand)
time.sleep(0.5)
#print (GetCommand + "|" + FileWhenDownloaded + "|" + RenameFileTo)
A quick ls command shows all the lovely files ready for analysing!
Step 2 - Loading into MongoDB
In my last post I covered the basics of loading data into MongoDB using Python. Now it was time to load data downloaded from the football-data site. I decided to just load one league for one season to have an initial play with the data.
It seems that there is different fields for different leagues for different years. Field names are common, it's just that they're absent or present from file to file. The full list of field names is here.
I decided to model the data as one simple JSON document per match with each record as a key value pair. So for example a simplified document for the first match of the Belgian league file shown above would be:
{
"Div":"B1",
"Date":"31/07/09",
"HomeTeam":"Standard",
"AwayTeam":"St Truiden"
}
There may well be more elegant ways of modelling the data. As I explore and learn more I'll work this out!
To prepare MongoDB I created a database called "Footie" and a collection called "Results" using these commands in the Mongo utility:
> use Footie
> db.createCollection("Results")
I wrote the script below to load data for one season and one league (English League 2, 2015/16).
In the script I:
- Create an object to access MongoDB
- Open a CSV file to read it
- Read the first line, remove trailing non-printing characters, split it and use this to form a list (HeaderList) that will make up the key element of the JSON document.
- Then for each subsequent line, read it, split it into another list (LineList)
- I then iterate through each list, forming key value pairs and writing them to a Python dictionary, (which is required to write a JSON document to MongoDB).
- I then write the document to MongoDB!
from pymongo import MongoClient
import sys
#Constants
DirPath = "/home/pi/Documents/
#Connect to the Footie database
client = MongoClient()
db = client.Footie
#Get a collection
collection = db.Results
#Open the file to process
MyFile = open(DirPath + "E3_1516.csv",'r')
#Read the first lines which is the header line, remove the \r\n at the end and turn it into a list
LineOne = MyFile.readline()
LineOne = LineOne[:-2]
HeaderList = LineOne.split(',')
#Now loop through the file reading lines, creating JSONs and writing them
FileLine = MyFile.readline()
while len(FileLine) > 0:
print(FileLine)
#Get rid of last two characters and put in a list
FileLine = FileLine[:-2]
LineList = FileLine.split(',')
#Form a JSON from these lists; needs to be a Python Dictionary
JSONDict = dict()
#Loop through both lists and add to the JSON
for i in range(len(HeaderList)):
#Interestingly field names in MongoDB can't contain a "." so turn it into a European style ","
HeaderList[i] = HeaderList[i].replace(".",",")
JSONDict[HeaderList[i]] = LineList[i]
#Write the document to the collection
print JSONDict
collection.insert_one(
#Setup for next loop
FileLine = MyFile.readline()
print ("Finished writing JSONs")
#Close the file
MyFile.close()
The net result from the Mongo tool being (abridged):
> db.Results.find()
{ "_id" : ObjectId("
{ "_id" : ObjectId("
Step 3 - Some Analysis in R
Using R I did some initial analysis of the data. I thought it would be interesting to ask the all-important question "who is the naughtiest team in League 2?". The data can help as the following fields are present:
HF = Home Team Fouls Committed
AF = Away Team Fouls Committed
HY = Home Team Yellow Cards
AY = Away Team Yellow Cards HR = Home Team Red Cards AR = Away Team Red Cards
Before using I, I practised the query I wanted to run in the MongoDB shell. The query is:
> use Footie
switched to db Footie
> db.Results.find({},{HomeTeam: 1, AwayTeam:1,HF:1,AF:1,HY:1,AY:1,HR:1,AR:1,_id: 0})
Which breaks down as:
> db.Results.find({}, means run a query on the Results collection and provide no filter parameters, i.e. give everything.
...and...
{HomeTeam: 1, AwayTeam:1,HF:1,AF:1,HY:1,AY:1,HR:1,AR:1,_id: 0})
means turn all the fields with a "1" on in the output and turn the _id (which is on by default) off.
...and this yields (abridged):
> db.Results.find({},{HomeTeam: 1, AwayTeam:1,HF:1,AF:1,HY:1,AY:1,HR:1,AR:1,_id: 0})
{ "HY" : "1", "HR" : "0", "AwayTeam" : "Luton", "HF" : "12", "AF" : "15", "AR" : "0", "AY" : "0", "HomeTeam" : "Accrington" }
{ "HY" : "1", "HR" : "0", "AwayTeam" : "Luton", "HF" : "12", "AF" : "15", "AR" : "0", "AY" : "0", "HomeTeam" : "Accrington" }
{ "HY" : "1", "HR" : "0", "AwayTeam" : "Plymouth", "HF" : "9", "AF" : "7", "AR" : "0", "AY" : "0", "HomeTeam" : "AFC Wimbledon" }
{ "HY" : "1", "HR" : "0", "AwayTeam" : "Northampton", "HF" : "11", "AF" : "8", "AR" : "0", "AY" : "1", "HomeTeam" : "Bristol Rvs" }
{ "HY" : "1", "HR" : "0", "AwayTeam" : "Newport County", "HF" : "9", "AF" : "12", "AR" : "0", "AY" : "2", "HomeTeam" : "Cambridge" }
To get the same data into a R data frame I did the following to set things up:
> library(RMongo)
Loading required package: rJava
> mg1 <- mongoDbConnect('Footie')
> print(dbShowCollections(mg1))
[1] "Results" "system.indexes"
...then this to run the query.
> query <- dbGetQueryForKeys(mg1, 'Results', "{}","{HomeTeam: 1, AwayTeam:1,HF:1,AF:1,HY:1,AY:1,HR:1,AR:1,_id:0}")
> data1 <- query
(Note the use of the dbGetQueryForKeys method which splits the Mongo shell query shown above into two parts).
Which gives this output (abridged):
> data1
HomeTeam AwayTeam HF AF HY AY HR AR X_id
1 Accrington Luton 12 15 1 0 0 0 NA
2 AFC Wimbledon Plymouth 9 7 1 0 0 0 NA
3 Bristol Rvs Northampton 11 8 1 1 0 0 NA
4 Cambridge Newport County 9 12 1 2 0 0 NA
5 Exeter Yeovil 5 10 1 0 0 0 NA
...am not sure why I get the X_id put I'm sure I can deal with it!
I now need to get this side-by-side data (so home and away team on the same row) into a data frame where there's one row per match per team.
To do this I created one data frame for home teams, one for away teams then merged them.
First for the home team. Get the home team data (columns 1, 3, 5 and 7) and then rename them to make them consistent when we combine home and away data frames:
> data3 <- data1[,c(2,4,6,8)]
Now you can see this is split over two rows of our data frame:
I now need to get this side-by-side data (so home and away team on the same row) into a data frame where there's one row per match per team.
To do this I created one data frame for home teams, one for away teams then merged them.
First for the home team. Get the home team data (columns 1, 3, 5 and 7) and then rename them to make them consistent when we combine home and away data frames:
> data2 <- data1[,c(1,3,5,7)
> colnames(data2) <- c("Team","Fouls","Yellows","
> head(data2)
Team Fouls Yellows Reds
1 Accrington 12 1 0
2 AFC Wimbledon 9 1 0
3 Bristol Rvs 11 1 0
4 Cambridge 9 1 0
5 Exeter 5 1 0
6 Hartlepool 12 4 0
> head(data2)
Team Fouls Yellows Reds
1 Accrington 12 1 0
2 AFC Wimbledon 9 1 0
3 Bristol Rvs 11 1 0
4 Cambridge 9 1 0
5 Exeter 5 1 0
6 Hartlepool 12 4 0
Get away team data and rename:
> data3 <- data1[,c(2,4,6,8)]
> colnames(data3) <- c("Team","Fouls","Yellows","
Team Fouls Yellows Reds
1 Luton 15 0 0
2 Plymouth 7 0 0
3 Northampton 8 1 0
4 Newport County 12 2 0
5 Yeovil 10 0 0
6 Morecambe 14 2 0
1 Luton 15 0 0
2 Plymouth 7 0 0
3 Northampton 8 1 0
4 Newport County 12 2 0
5 Yeovil 10 0 0
6 Morecambe 14 2 0
data4 < rbind(data2, data3)
> head(data4)
Team Fouls Yellows Reds
1 Accrington 12 1 0
2 AFC Wimbledon 9 1 0
3 Bristol Rvs 11 1 0
4 Cambridge 9 1 0
5 Exeter 5 1 0
6 Hartlepool 12 4 0
So as a quick check, in the first data frame we had this as the bottom result:
> data1[371,]
HomeTeam AwayTeam HF AF HY AY HR AR X_id
371 Yeovil Portsmouth 7 10 1 0 0 1 NA
Now you can see this is split over two rows of our data frame:
> data4[c(371,742),]
Team Fouls Yellows Reds
371 Yeovil 7 1 0
3711 Portsmouth 10 0 1
Now aggregate to get the count per team across the season so far. Here you first list the values you're aggregating, then what to group them by, the the function (sum in this case):
> data_agg <-aggregate(list(Fouls=data4$
Which gives us (abridged):
Which gives us (abridged):
> data_agg
Team Fouls Yellows Reds
1 Accrington 302 57 3
2 AFC Wimbledon 377 40 3
3 Barnet 331 46 3
4 Bristol Rvs 299 39 2
Not much use so time to order the data using the "order" function. There are many ways to order, I selected to order by fouls first, then yellow cards, then red cars.
> agg_sort <- data_agg[order(-data_agg$
> agg_sort
Team Fouls Yellows Reds
22 Wycombe 420 48 2
13 Mansfield 417 62 5
2 AFC Wimbledon 377 40 3
8 Dag and Red 373 42 2
21 Stevenage 351 56 3
12 Luton 351 53 3
7 Crawley Town 348 52 4
16 Northampton 347 44 5
11 Leyton Orient 338 45 3
19 Plymouth 332 53 0
3 Barnet 331 46 3
17 Notts County 329 56 3
5 Cambridge 329 38 3
23 Yeovil 317 34 3
24 York 316 50 3
18 Oxford 310 46 2
15 Newport County 306 37 2
1 Accrington 302 57 3
9 Exeter 301 38 0
4 Bristol Rvs 299 39 2
14 Morecambe 294 55 2
6 Carlisle 284 39 3
20 Portsmouth 264 28 5
10 Hartlepool 252 45 2
So there, it's official*, Wycombe Wanderers are the naughtiest team in English League 2.
(*Apologies for fans of Wycombe. It;s just stats geekery and no reflection on your fine football team!).
