How To Build a Soccer Data Web Scraper with Django and Fauna
Tags: django, fauna, beginners, python
Published: 2021-09-17 16:27:25

In this tutorial, we will be extracting data from the top 5 soccer leagues globally: the EPL, La Liga, Serie A, Bundesliga, and Ligue 1. However, to obtain this data, we will be extracting it from an official sports website ([understat.com](https://understat.com/)). We will then learn how to store and manage these data with Fauna.

# Prerequisites

To fully understand this tutorial, you are required to have the following in place:

* Python 3.7 or newer.
* Basic understanding of [Fauna](https://dashboard.fauna.com/accounts/register?utm_source%3DDevTo%26utm_medium%3Dreferral%26utm_campaign%3DWritewithFauna_SoccerData_Ochuko).
* Basic knowledge of Django.
* A text editor.

With the above prerequisites out of the way, we can now begin building our web scraper application.

# Introduction to Fauna

Fauna is a client-side serverless database that uses GraphQL and the Fauna Query Language (FQL) to support various data types and relational databases in a serverless API. You can learn more about Fauna in their official documentation [here](https://docs.fauna.com/fauna/current/). If this is the first time you have heard about Fauna, visit my previous article [here](https://dev.to/chukslord1/building-an-appointment-scheduler-app-with-django-and-fauna-2n9o) for a brief introduction.

## Installing the required libraries

The libraries required for this tutorial are as follows:

* numpy — fundamental package for scientific computing with Python
* pandas — library providing high-performance, easy-to-use data structures, and data analysis tools
* requests — is the only Non-GMO HTTP library for Python, safe for human consumption. (love this line from official docs :D)
* BeautifulSoup — a python library for pulling data out of HTML and XML files.

To install the libraries required for this tutorial, run the following commands below:

```bash

pip install numpy

pip install pandas

pip install requests 

pip install bs4

```

# Building the Python Web Scraper

Now that we have all the required libraries installed, let’s get to building our web scraper. 

## Importing the Python libraries

```python

import numpy as np

import pandas as pd

import requests

from bs4 import BeautifulSoup

import json

```

## Carrying out Site Research

The first step in any web scraping project is researching the web page you want to scrape and learn how it works. That is critical to finding where to get the data from the site; therefore, this is where we’ll begin.

![](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cucmcfwxw1a7rgt415x1.png)

We can see on the home page that the site has data for six European leagues. However, we will be extracting data for just the top 5 leagues(teams excluding RFPL).

![](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/d57i90txy5qhkqousf1k.png)

We can also notice that data on the site starts from 2014/2015 to 2020/2021. Let’s create variables to handle only the information we require.

```python

# create urls for all seasons of all leagues

base_url = 'https://understat.com/league'

leagues = ['La_liga', 'EPL', 'Bundesliga', 'Serie_A', 'Ligue_1']

seasons = ['2016', '2017', '2018', '2019', '2020']

```

The next step is to figure out where the data on the web page is stored. To do so, open Developer Tools in Chrome, navigate to the Network tab, locate the data file (in this example, 2018), and select the “Response” tab. After executing requests, this is what we'll get. get(URL)

![](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cdkziedpmhs10e3q7bme.png)

After looking through the web page's content, we discovered that the data is saved beneath the "script" element in the `teamsData` variable and is JSON encoded. As a result, we'll need to track down this tag, extract JSON from it, and convert it to a Python-readable data structure.

![](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/tguldfeas5wrifz3igts.png)

## Decoding the JSON data

```python

  season_data = dict()

  for season in seasons:

    url = base_url+'/'+league+'/'+season

    res = requests.get(url)

    soup = BeautifulSoup(res.content, "lxml")

    # Based on the structure of the webpage, I found that data is in the JSON variable, under <script> tags

    scripts = soup.find_all('script')

    string_with_json_obj = ''

    # Find data for teams

    for el in scripts:

        if 'teamsData' in str(el):

          string_with_json_obj = str(el).strip()

    # strip unnecessary symbols and get only JSON data

    ind_start = string_with_json_obj.index("('")+2

    ind_end = string_with_json_obj.index("')")

    json_data = string_with_json_obj[ind_start:ind_end]

    json_data = json_data.encode('utf8').decode('unicode_escape')

   #print(json_data)
```

After running the python code above, you should get data that we’ve cleaned up.

## Understanding the Data

When we start looking at the data, we realise it's a dictionary of dictionaries with three keys: id, title, and history. Ids are also used as keys in the dictionary's initial layer.

Therefore, we can deduce that history has information on every match a team has played in its league (League Cup or Champions League games are omitted). 

After reviewing the first layer dictionary, we can begin to compile a list of team names.

```python

# Get teams and their relevant ids and put them into separate dictionary

teams = {}

for id in data.keys():

    teams[id] = data[id]['title']

```

We see that column names frequently appear; therefore, we put them in a separate list. Also, look at how the sample values appear.

```python

columns = []

# Check the sample of values per each column

values = []

for id in data.keys():

  columns = list(data[id]['history'][0].keys())

  values = list(data[id]['history'][0].values())

  break

```

Now let’s get data for all teams. Uncomment the print statement in the code below to print the data to your console.

```python

# Getting data for all teams

    dataframes = {}

    for id, team in teams.items():

      teams_data = []

      for row in data[id]['history']:

        teams_data.append(list(row.values()))

      df = pd.DataFrame(teams_data, columns=columns)

      dataframes[team] = df

      # print('Added data for {}.'.format(team))

```

After you have completed this code, we will have a dictionary of DataFrames with the key being the team's name and the value being the DataFrame containing all of the team's games.

## Manipulating Data to Table

When we look at the DataFrame content, we can see that metrics like PPDA and OPPDA (ppda and ppda allowed) are represented as total sums of attacking/defensive actions.

However, they are shown as coefficients in the original table. Let's clean that up.

```python

for team, df in dataframes.items():

      dataframes[team]['ppda_coef'] = dataframes[team]['ppda'].apply(lambda x: x['att']/x['def'] if x['def'] != 0 else 0)

      dataframes[team]['oppda_coef'] = dataframes[team]['ppda_allowed'].apply(lambda x: x['att']/x['def'] if x['def'] != 0 else 0)

```

We now have all of our numbers, but for every game. The totals for the team are what we require. Let's look at the columns we need to add up. To do so, we returned to the original table on the website and discovered that all measures should be added together, with only PPDA and OPPDA remaining as means in the end. First, let’s define the columns we need to sum and mean.

```python

cols_to_sum = ['xG', 'xGA', 'npxG', 'npxGA', 'deep', 'deep_allowed', 'scored', 'missed', 'xpts', 'wins', 'draws', 'loses', 'pts', 'npxGD']

cols_to_mean = ['ppda_coef', 'oppda_coef']

```

Finally, let’s calculate the totals and means.

```python

for team, df in dataframes.items():

      sum_data = pd.DataFrame(df[cols_to_sum].sum()).transpose()

      mean_data = pd.DataFrame(df[cols_to_mean].mean()).transpose()

      final_df = sum_data.join(mean_data)

      final_df['team'] = team

      final_df['matches'] = len(df)

      frames.append(final_df)

    full_stat = pd.concat(frames)

    full_stat = full_stat[['team', 'matches', 'wins', 'draws', 'loses', 'scored', 'missed', 'pts', 'xG', 'npxG', 'xGA', 'npxGA', 'npxGD', 'ppda_coef', 'oppda_coef', 'deep', 'deep_allowed', 'xpts']]

    full_stat.sort_values('pts', ascending=False, inplace=True)

    full_stat.reset_index(inplace=True, drop=True)

    full_stat['position'] = range(1,len(full_stat)+1)

    full_stat['xG_diff'] = full_stat['xG'] - full_stat['scored']

    full_stat['xGA_diff'] = full_stat['xGA'] - full_stat['missed']

    full_stat['xpts_diff'] = full_stat['xpts'] - full_stat['pts']

    cols_to_int = ['wins', 'draws', 'loses', 'scored', 'missed', 'pts', 'deep', 'deep_allowed']

    full_stat[cols_to_int] = full_stat[cols_to_int].astype(int)

```

In the code above, we reordered columns for better readability, sorted rows based on points, reset the index, and added column ‘position’.

We also added the differences between the expected metrics and real metrics.

Lastly, we converted the floats to integers where appropriate.

## Beautifying the Final Output of the Dataframe

![](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xkv5jrucvy2fiqbiqajp.png)

Finally, let’s beautify our data to become similar to the site data in the image above. To do this, run the python code below.

```python

col_order = ['position', 'team', 'matches', 'wins', 'draws', 'loses', 'scored', 'missed', 'pts', 'xG', 'xG_diff', 'npxG', 'xGA', 'xGA_diff', 'npxGA', 'npxGD', 'ppda_coef', 'oppda_coef', 'deep', 'deep_allowed', 'xpts', 'xpts_diff']

 full_stat = full_stat[col_order]

 full_stat = full_stat.set_index('position')

 # print(full_stat.head(20))

```

To print a part of the beautified data, uncomment the print statement in the code above.

## Compiling the Final Code

To get all the data, we need to loop through all the leagues and seasons then manipulate it to be exportable as a CSV file.

```python

import numpy as np

import pandas as pd

import requests

from bs4 import BeautifulSoup

import json

# create urls for all seasons of all leagues

base_url = 'https://understat.com/league'

leagues = ['La_liga', 'EPL', 'Bundesliga', 'Serie_A', 'Ligue_1']

seasons = ['2016', '2017', '2018', '2019', '2020']

full_data = dict()

for league in leagues:

  season_data = dict()

  for season in seasons:

    url = base_url+'/'+league+'/'+season

    res = requests.get(url)

    soup = BeautifulSoup(res.content, "lxml")

    # Based on the structure of the webpage, I found that data is in the JSON variable, under <script> tags

    scripts = soup.find_all('script')

    string_with_json_obj = ''

    # Find data for teams

    for el in scripts:

        if 'teamsData' in str(el):

          string_with_json_obj = str(el).strip()

    # print(string_with_json_obj)

    # strip unnecessary symbols and get only JSON data

    ind_start = string_with_json_obj.index("('")+2

    ind_end = string_with_json_obj.index("')")

    json_data = string_with_json_obj[ind_start:ind_end]

    json_data = json_data.encode('utf8').decode('unicode_escape')

    # convert JSON data into Python dictionary

    data = json.loads(json_data)

    # Get teams and their relevant ids and put them into separate dictionary

    teams = {}

    for id in data.keys():

      teams[id] = data[id]['title']

    # EDA to get a feeling of how the JSON is structured

    # Column names are all the same, so we just use first element

    columns = []

    # Check the sample of values per each column

    values = []

    for id in data.keys():

      columns = list(data[id]['history'][0].keys())

      values = list(data[id]['history'][0].values())

      break

    # Getting data for all teams

    dataframes = {}

    for id, team in teams.items():

      teams_data = []

      for row in data[id]['history']:

        teams_data.append(list(row.values()))

      df = pd.DataFrame(teams_data, columns=columns)

      dataframes[team] = df

      # print('Added data for {}.'.format(team))

    for team, df in dataframes.items():

      dataframes[team]['ppda_coef'] = dataframes[team]['ppda'].apply(lambda x: x['att']/x['def'] if x['def'] != 0 else 0)

      dataframes[team]['oppda_coef'] = dataframes[team]['ppda_allowed'].apply(lambda x: x['att']/x['def'] if x['def'] != 0 else 0)

    cols_to_sum = ['xG', 'xGA', 'npxG', 'npxGA', 'deep', 'deep_allowed', 'scored', 'missed', 'xpts', 'wins', 'draws', 'loses', 'pts', 'npxGD']

    cols_to_mean = ['ppda_coef', 'oppda_coef']

    frames = []

    for team, df in dataframes.items():

      sum_data = pd.DataFrame(df[cols_to_sum].sum()).transpose()

      mean_data = pd.DataFrame(df[cols_to_mean].mean()).transpose()

      final_df = sum_data.join(mean_data)

      final_df['team'] = team

      final_df['matches'] = len(df)

      frames.append(final_df)

    full_stat = pd.concat(frames)

    full_stat = full_stat[['team', 'matches', 'wins', 'draws', 'loses', 'scored', 'missed', 'pts', 'xG', 'npxG', 'xGA', 'npxGA', 'npxGD', 'ppda_coef', 'oppda_coef', 'deep', 'deep_allowed', 'xpts']]

    full_stat.sort_values('pts', ascending=False, inplace=True)

    full_stat.reset_index(inplace=True, drop=True)

    full_stat['position'] = range(1,len(full_stat)+1)

    full_stat['xG_diff'] = full_stat['xG'] - full_stat['scored']

    full_stat['xGA_diff'] = full_stat['xGA'] - full_stat['missed']

    full_stat['xpts_diff'] = full_stat['xpts'] - full_stat['pts']

    cols_to_int = ['wins', 'draws', 'loses', 'scored', 'missed', 'pts', 'deep', 'deep_allowed']

    full_stat[cols_to_int] = full_stat[cols_to_int].astype(int)

    col_order = [‘league’,’year’,'position', 'team', 'matches', 'wins', 'draws', 'loses', 'scored', 'missed', 'pts', 'xG', 'xG_diff', 'npxG', 'xGA', 'xGA_diff', 'npxGA', 'npxGD', 'ppda_coef', 'oppda_coef', 'deep', 'deep_allowed', 'xpts', 'xpts_diff']

    full_stat = full_stat[col_order]

    full_stat = full_stat.set_index('position')

    # print(full_stat.head(20))

    season_data[season] = full_stat

  df_season = pd.concat(season_data)

  full_data[league] = df_season

```

To analyse our data with Django, we need to export the data to a CSV file. To do this, copy and paste the code below.

```python

data = pd.concat(full_data)

data.to_csv('understat.com.csv')

```

# Setting up the Fauna Database

You must first [establish an account](https://dashboard.fauna.com/accounts/register?utm_source%3DDevTo%26utm_medium%3Dreferral%26utm_campaign%3DWritewithFauna_SoccerData_Ochuko) before you can build a Fauna database. You may create another after you've created one by clicking the CREATE DATABASE button on the dashboard.

![](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ndsb1jv2xjmtr4s3gqsy.png)

## Creating the Database Collections

Create one collection in your database, the `SOCCER_DATA` collection. The `SOCCER_DATA` collection will store the data we extract to our CSV file in the database. For the `History days` and `TTL`, use the default values provided and save.

![](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bi8ybwj1b8hl5h09aikm.png)

![](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3tjeu4asev4trnhpesfn.png)

## Creating the Collection Indexes

Create two indexes for your collections; `check_name` and `get_all_teams_data`. The  `get_all_teams_data` index will allow you to scroll through data in the `SOCCER_DATA` collection. It has one term, which is the `matches` field. This term will enable you to match data with the matches for easy querying.

The `check_name` will allow you to scroll through data in the `SOCCER_DATA` collection. This index will enable matching with the `team` field to perform queries.

![](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qoc6zhussj5lo7ggq0to.png)

## Generating the Database API Key

To generate an API key, go to the security tab on the left side of your dashboard, then click `New Key` to create a key. You will then be required to provide a database to connect to the key. After providing the information required, click the `SAVE` button.

![](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/b9rudyel6edkml6le9ai.png)

After saving your key, Fauna will present you with your secret key in the image above (hidden here for privacy). Make sure to copy your secret key from the dashboard and save it somewhere you can easily retrieve it for later use.

![](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/m80w1dz40gvhgzuqr8l0.png)

# Setting Up the Django Application

The Django application will collect data from the CSV file, store it in the Fauna database and display it on the homepage user interface. To create a Django project, run the commands below:

```bash

django-admin startproject FAUNA_WEBSCRAPER

Django-admin startapp APP

```

After creating the Django project, we need to create another application before diving into the main code logic. Follow the steps below to set up your Django application.

1. Add `APP` to installed apps in the settings.py file as seen in the code below:

```python

INSTALLED_APPS = [

    'django.contrib.admin',

    'django.contrib.auth',

    'django.contrib.contenttypes',

    'django.contrib.sessions',

    'django.contrib.messages',

    'django.contrib.staticfiles',

    'APP',

]

```

2.  Update the project `urls.py` file with python code below:

```python

from django.contrib import admin

from django.urls import path, include

from django.conf import settings

from django.contrib.staticfiles.urls import staticfiles_urlpatterns

from django.contrib.staticfiles.urls import static

urlpatterns = [

    path('admin/', admin.site.urls),

    path('', include("APP.urls")),

]

urlpatterns += staticfiles_urlpatterns()

urlpatterns += static(settings.MEDIA_URL, document_root=settings.MEDIA_ROOT)

```

3. Create a new `urls.py` file in the `APP` folder, then paste the python code below.

```python

from django.conf import settings

from django.conf.urls.static import static

from django.urls import path, include

from . import views

app_name = "App"

urlpatterns = [

    path("", views.index, name="index"),

]

```

4. Create two new folders in your `APP` directory to handle the application user interface(i.e. `templates` and `static` folder).
5. Create two new files in the `templates` folder, an HTML file and a CSS file. The HTML should be `index.html`, and the CSS file should be `style.css`. Copy and paste the code below in their respective files.
```html

{% load static %}

<!DOCTYPE html>

<html>

<head>

  <link href="{% static 'style.css' %}" rel="stylesheet">

  <h1>Fauna Sport Aggregator</h1>

</head>

<body>

  <table class="content-table">

    <thead>

      <tr>

        <th>Position</th>

        <th>Team</th>

        <th>Matches</th>

        <th>Wins</th>

        <th>Draws</th>

        <th>Loses</th>

        <th>Points</th>

        </tr>

        </thead>

        <tbody>

        {% for team in team_list_data %}

          <tr>

            <td>{{team.position}}</td>

            <td>{{team.team}}</td>

            <td>{{team.matches}}</td>

            <td>{{team.wins}}</td>

            <td>{{team.draws}}</td>

            <td>{{team.loses}}</td>

            <td>{{team.points}}</td>

            </tr>

        {% endfor %}

        </tbody>

         </table>

       </body>

    </html>

```

```CSS
#style.css
h1 {

    color: #0000A0;

    font-family: sans-serif;

   }

   .content-table {

    border-collapse: collapse;

      margin: 25px 0;

        font-size: 1.0em;

        font-weight: bold;

        font-family: sans-serif;

         min-width: 400px;

          border-radius: 5px 5px 0 0;

            overflow-hidden: hidden;

          box-shadow: 0 0 20px rgba(0, 0, 0, 0.15);

        }

   

        .content-table thead tr {

          background-color: #0000A0;

          color: #FFFFFF;

          text-align: left;

          font-weight: bold;

        }

   

        .content-table th,

        .content-table td {

        padding: 15px 25px;

      }

   

      .content-table tbody tr {

        border-bottom: 1px solid #dddddd;

     }

   

      .content-table tbody tr:nth-of-type(even) {

        background-color: #f3f3f3;

        color: #0000A0;

        font-weight: bold

      }

   

      .content-table tbody tr:last-of-type {

        border-bottom: 2px solid #0000A0;

      }

   

        .content-table tbody tr:hover {

          background-color: #D3D3D3;

        }

```

6. Copy the CSV file generated earlier to the `APP` folder.
7. Copy and paste the python code below in the `views.py` file.

```python

from django.shortcuts import render

import csv

from faunadb import query as q

from faunadb.objects import Ref

from faunadb.client import FaunaClient

# Create your views here.

client = FaunaClient(secret="fauna_secret_key", domain="db.eu.fauna.com",

  # NOTE: Use the correct domain for your database's Region Group.

  port=443,

  scheme="https")

def index(request):

team_data_list = []

headers_list = []

    paginate_data=client.query(q.paginate(q.match(q.index("get_all_teams_data"), "38")))

    all_data = [

        q.get(

            q.ref(q.collection("SOCCER_DATA"), paginate.id())

        ) for paginate in paginate_data["data"]

    ]

    team_list_data = [i["data"] for i in  client.query(all_data)]

    context={"team_list_data":client.query(team_list_data)}

    index = 0

    with open("understat.com.csv", 'r') as data:

        for line in csv.reader(data):

            index += 1

            if index > 1:

                team_dict = {}

                for i, elem in enumerate(headers_list):

                    team_dict[elem] = line[i]

                team_data_list.append(team_dict)

            else:

                headers_list = list(line)

    for record in team_data_list[:20]:

        try:

            team_data_check = client.query(q.get(q.match(q.index('check_name'),record["team"])))

        except:

            team_record = client.query(q.create(q.collection("SOCCER_DATA"), {

                    "data": {

                        "matches":record["matches"],

                        "position":record["position"],

                        "team":record["team"],

                        "wins": record["wins"],

                        "draws": record["draws"],

                        "loses":record["loses"],

                        "points":record["pts"]

                    }

                }))

    return render(request,"index.html",context)

```

8. Run the application with the following commands.
9. 
```bash
python manage.py migrate

python manage.py runserver

```

After following the steps listed above, your `SOCCER_DATA` collection should be like the one in the image below.

![](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/okxdjnm0uddk4kc9ueuc.png)

Go to http://127:0.0.1.8000 to see your Django application. Your homepage should be similar to the one in the image below.

![](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/y3ljnfe1l6uphkj9zumo.png)

# The Views File.

This file is where we built the logic for the backend. We started by importing the required modules as seen in the python code below:

```python

from django.shortcuts import render

import csv

from faunadb import query as q

from faunadb.objects import Ref

from faunadb.client import FaunaClient

```

Next, we initialised the Fauna client using the secret key we generated earlier.

```python

client = FaunaClient(secret="fnAETH3xELAAxp99WYA-8_xMLqFM1uMTfAwZYmZO", domain="db.eu.fauna.com",

  # NOTE: Use the correct domain for your database's Region Group.

  port=443,

  scheme="https")

```

We then needed to extract the data from our excel sheet before saving it into our `SOCCER_DATA` collection. We did this by reading and saving the data in the CSV file to a list variable `team_data_list`  in our program. 

```python

team_data_list = []

headers_list = []

index = 0

    with open("understat.com.csv", 'r') as data:

        for line in csv.reader(data):

            index += 1

            if index > 1:

                team_dict = {}

                for i, elem in enumerate(headers_list):

                    team_dict[elem] = line[i]

                team_data_list.append(team_dict)

            else:

                headers_list = list(line)

```

Next, we needed to extract data from our list `team_data_list` variable, then stored them in the `SOCCER_DATA` collection. To do this, we iterated every data in the list and created a request with the Fauna client to save the data passed in the dictionary we defined to the collection. We only saved the first 20 data from the list for this tutorial, so we don’t exceed our free account rate limit.

```python

for record in team_data_list[:20]:

        try:

            team_data_check = client.query(q.get(q.match(q.index('check_name'),record["team"])))

        except:

            team_record = client.query(q.create(q.collection("SOCCER_DATA"), {

                    "data": {

                        "matches":record["matches"],

                        "position":record["position"],

                        "team":record["team"],

                        "wins": record["wins"],

                        "draws": record["draws"],

                        "loses":record["loses"],

                        "points":record["pts"]

                    }

                }))

```

Next, we queried the data in the collection using the `get_all_team_data1` Fauna index to match data for all teams with 38 matches. We paginated the queried data using Fauna’s `paginate` method, then created a list variable called `team_list_data` from it. Finally, we passed the list’s data to the front end user interface for display on a table.  

# Conclusion

In this article, we built a python web scraper to extract soccer data from a website. We also learned how to utilise this data in a Django application with [Fauna's serverless database](https://fauna.com/?utm_source%3DDevTo%26utm_medium%3Dreferral%26utm_campaign%3DWritewithFauna_SoccerData_Ochuko). We saw how easy it is to integrate Fauna into a Python application and got the chance to explore some of its core features and functionalities.

If you have any questions, don't hesitate to contact me on Twitter: [@](https://twitter.com/LordChuks3)[LordChuks3](https://twitter.com/LordChuks3).

Written in connection with the [Write with Fauna Program](https://fauna.com/blog/write-with-fauna).