Data Engineering of Opta City Analytics Data¶

Notebook to engineer the aggregated match-by-match performance Excel data and F24 Event data available from Opta as part of the #mcfcanalytics initiative¶

By Edd Webster¶

Notebook first written: 02/12/2020
Notebook last updated: 07/12/2020

Click here to jump straight to the Exploratory Data Analysis section and skip the Task Brief, Data Sources, and Data Engineering sections. Or click here to jump straight to the Conclusion.

Introduction¶

This notebook engineers the aggregated performance Excel data available from Opta as part of the #mcfcanalytics initiative for the 11/12 Premier League season using pandas for data manipulation through DataFrames.

For more information about this notebook and the author, I'm available through all the following channels:

• eddwebster.com;
• edd.j.webster@gmail.com;
• @eddwebster;
• linkedin.com/in/eddwebster;
• github/eddwebster;
• public.tableau.com/profile/edd.webster;
• kaggle.com/eddwebster; and
• hackerrank.com/eddwebster.

The accompanying GitHub repository for this notebook can be found here and a static version of this notebook can be found here.

Notebook Contents¶

1. Notebook Dependencies
   
2. Project Brief
   
3. Data Sources
   
   1. Introduction
      
   2. Data Dictionary
      
   3. Creating the DataFrame
      
   4. Initial Data Handling
      
   5. Export the Raw DataFrame
      
4. Data Engineering
   
   1. Introduction
      
   2. Columns of Interest
      
   3. String Cleaning
      
   4. Converting Data Types
      
   5. Export the Engineered DataFrame
      
5. Exploratory Data Analysis (EDA)
   
   1. ...
      
   2. ...
      
   3. ...
      
6. Summary
   
7. Next Steps
   
8. Bibliography
   

1. Notebook Dependencies¶

This notebook was written using Python 3 and requires the following libraries:

• Jupyter notebooks for this notebook environment with which this project is presented;
• NumPy for multidimensional array computing;
• pandas for data analysis and manipulation; and
• matplotlib for data visualisations;

All packages used for this notebook except for BeautifulSoup can be obtained by downloading and installing the Conda distribution, available on all platforms (Windows, Linux and Mac OSX). Step-by-step guides on how to install Anaconda can be found for Windows here and Mac here, as well as in the Anaconda documentation itself here.

Import Libraries and Modules¶

Defined Variables¶

Defined Filepaths¶

Notebook Settings¶

2. Project Brief¶

This Jupyter notebook explores how to parse publicly available Event data from Opta using pandas for data manipulation through DataFrames.

In this analysis, we're looking specifically at Premier League for the 11/12 season.

The engineered event data roduced in this notebook is exported to CSV. This data can be further analysed in Python, joined to other datasets, or explored using Tableau, PowerBI, Microsoft Excel.

3. Data Sources¶

3.1. Introduction¶

Opta are a football analytics and data company.

The data needs to be imported as a DataFrame in the Data Sources section Section 3 and cleaned in the Data Engineering section Section 4.

The Opta data made available for the #mcfcanalytics initiative is available in two sources:

• Match-by-Match aggregated performance data for all players during the 11/12 season
• Opta F24 Event data for Manchester City vs. Bolton Wanders on 21/08/2011 [link]

The data import is divided into these two sections.

We'll be using the pandas library to import our data to this workbook as a DataFrame.

3.2. Match-by-Match Data¶

3.2.1. Data Dictionary¶

The Opta dataset has one hundred and fourteen features (columns) with the following definitions and data types:

For a full list of definitions, see the official documentation [link].

3.2.2. Import Data¶

3.2.3. Initial Data Handling¶

Let's quality of the dataset by looking first and last rows in pandas using the head() and tail() methods.

The dataset has two hundred and ten features (columns). Full details of these attributes can be found in the Data Dictionary.

All six of the columns have the object data type. Full details of these attributes and their data types can be found in the Data Dictionary.

The visualisation shows us that the only field with missing values is the Player Forename attribute. This is something that will be fixed in the Data Engineering section.

3.2. F24 Event Data¶

See: https://fcpython.com/blog/parsing-opta-f24-files-introduction-xml-python and https://github.com/imrankhan17/Parsing-Opta-files/blob/master/Parsing%20Opta%20F24%20files.ipynb

3.2.1. Data Dictionary¶

The Opta dataset has one hundred and fourteen features (columns) with the following definitions and data types:

For a full list of definitions, see the official documentation [link].

3.2.2. Import Data¶

Method 1: Open CSV prepared in R¶

R code for managing the F24 dataset [link].

Method 2: Parse XML file in Python¶

See: https://fcpython.com/blog/parsing-opta-f24-files-introduction-xml-python

First import the XML file using the imported XML module.

The following two lines below will take an XML file and parse it into something that we could navigate just like we do with an object by using square brackets:

Next take a look at what information we are given about the game itself with the ‘.attrib’ method from the XML module imported earlier.

We get loads of information about the match in a dictionary, where the data is laid out with a key, then the value. For example, we can see the team names and also their Opta IDs.

Print the match details:

Tthe structure of the file that the match events lie within the game details tags. The first event is the following:

We can see that there are event keys like min, sec, x and y – these are quite easy to understand.

The values, like outcome: 1 and event_id: 1, don’t really make much sense by themselves. This is particularly important when it comes to teams, as we only have their id and not their name. This is tidied up in the next steps.

The Opta XML uses lots of IDs rather than names that are detailed in the Opta documentation link

The events contained qualifiers. Let’s again use square brackets to pull the first one out for the event above:

We are going to loop through each of the events that we have identified above and take the details that we want from each. These details will go into different lists for different data categories (player, team, success, etc.). We will then put these lists into a table which is then ready for analysis, plotting or exporting.

Firstly though, we'll make the team names come through to make this a bit more readible, while than the team ID. The events only carry the team ID, so let’s create a dictionary that will allow us to later swap the ID for the team name:

For this tutorial, we’re simply going to take the x/y locations of the pass origin and destination, the time of the pass, the team and whether or not it was successful.

There’s so much more that we could take, such as the players, the pass length or any other details that you spot in the XML. If you’d like to pull those out too, doing so will make a great extension to this tutorial!

We’re going to start by creating the empty lists for our data:

The main part of the tutorial sees us going event-by-event and adding our desired details only when the event is a pass. To do this, we will use a for loop on each event, and when the event is a pass (id = 1), we will append the correct attribute to our lists created above. Some of these details are hidden in the qualifiers, so we’ll also iterate over those to get the information needed there.

This tutorial probably isn’t the best place to go through the intricacies of the feed, so take a look at the docs if you’re interested.

Follow the code below with comments on each of the above steps:

If this has worked correctly, we should have 8 lists populated. Let’s check out the minutes list:

You can check out each list in more detail, but they should work just fine.

Our final task is to create a table for our data from our lists. To do this, we just need to create a list of our column headers, then assign the list to each one. We’ll then flip our table to make it long, rather than wide – just like you would want to see in a spreadsheet. Let’s take a look:

So this is great for passes, and the same logic would apply for shots, fouls or even all events at the same time – just expand on the above with the relevant IDs from the Opta docs. And analysts, if you’re still struggling to get it done, the emergency loan window is always open!

Now that we’ve taken a complex XML and parsed the passes into a table, there’s a number of things that we can do. We could put the table into a wider dataset, do some analysis of these passes, visualise straight away or just export our new table to a csv:

3.2.3. Initial Data Handling¶

Let's quality of the dataset by looking first and last rows in pandas using the head() and tail() methods.

The dataset has two hundred and ten features (columns). Full details of these attributes can be found in the Data Dictionary.

All six of the columns have the object data type. Full details of these attributes and their data types can be found in the Data Dictionary.