{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# StatsBomb 360 Data Parsing\n", "##### Notebook to parse and engineer the JSON data from the [StatsBomb Open Data GitHub repository](https://github.com/statsbomb/open-data) using [pandas](http://pandas.pydata.org/).\n", "\n", "### By [Edd Webster](https://www.twitter.com/eddwebster)\n", "Notebook first written: 29/10/2021
\n", "Notebook last updated: 05/12/2021\n", "\n", "![StatsBomb](../../img/logos/stats-bomb-logo.png)\n", "\n", "![StatsBomb 360](../../img/logos/stats-bomb-360-logo.png)\n", "\n", "Click [here](#section5) to jump straight to the Exploratory Data Analysis section and skip the [Task Brief](#section2), [Data Sources](#section3), [Data Engineering](#section4), [Data Aggregation](#section5), and [Subsetted DataFrames](#section6) sections." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "___\n", "\n", "\n", "## Introduction\n", "This notebook parses pubicly available [StatsBomb](https://statsbomb.com/) Event data, using [pandas](http://pandas.pydata.org/) for data manipulation through DataFrames.\n", "\n", "For more information about this notebook and the author, I'm available through all the following channels:\n", "* [eddwebster.com](https://www.eddwebster.com/);\n", "* edd.j.webster@gmail.com;\n", "* [@eddwebster](https://www.twitter.com/eddwebster);\n", "* [linkedin.com/in/eddwebster](https://www.linkedin.com/in/eddwebster/);\n", "* [github/eddwebster](https://github.com/eddwebster/); and\n", "* [public.tableau.com/profile/edd.webster](https://public.tableau.com/profile/edd.webster).\n", "\n", "![Edd Webster](../../img/edd_webster/fifa21eddwebsterbanner.png)\n", "\n", "The accompanying GitHub repository for this notebook can be found [here](https://github.com/eddwebster/football_analytics) and a static version of this notebook can be found [here](https://nbviewer.org/github/eddwebster/football_analytics/blob/master/notebooks/2_data_parsing/Parma%20Calcio%201913%20-%20StatsBomb%20Data%20Parsing%20and%20Engineering.ipynb)." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "___\n", "\n", "## Notebook Contents\n", "1. [Notebook Dependencies](#section1)
\n", "2. [Project Brief](#section2)
\n", "3. [Data Sources](#section3)
\n", " 1. [Introduction](#section3.1)
\n", " 2. [Read in the Datasets](#section3.2)
\n", " 3. [Join the Datasets](#section3.3)
\n", " 4. [Initial Data Handling](#section3.4)
\n", "5. [Summary](#section5)
\n", "6. [Next Steps](#section6)
\n", "7. [References](#section7)
" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "___\n", "\n", "\n", "\n", "## 1. Notebook Dependencies\n", "\n", "This notebook was written using [Python 3](https://docs.python.org/3.7/) and requires the following libraries:\n", "* [`Jupyter notebooks`](https://jupyter.org/) for this notebook environment with which this project is presented;\n", "* [`NumPy`](http://www.numpy.org/) for multidimensional array computing; and\n", "* [`pandas`](http://pandas.pydata.org/) for data analysis and manipulation.\n", "\n", "All packages used for this notebook except for BeautifulSoup can be obtained by downloading and installing the [Conda](https://anaconda.org/anaconda/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](https://medium.com/@GalarnykMichael/install-python-on-windows-anaconda-c63c7c3d1444) and Mac [here](https://medium.com/@GalarnykMichael/install-python-on-mac-anaconda-ccd9f2014072), as well as in the Anaconda documentation itself [here](https://docs.anaconda.com/anaconda/install/)." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Import Libraries and Modules" ] }, { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Setup Complete\n" ] } ], "source": [ "# Python ≥3.5 (ideally)\n", "import platform\n", "import sys, getopt\n", "assert sys.version_info >= (3, 5)\n", "import csv\n", "\n", "# Import Dependencies\n", "%matplotlib inline\n", "\n", "# Math Operations\n", "import numpy as np\n", "from math import pi\n", "\n", "# Datetime\n", "import datetime\n", "from datetime import date\n", "import time\n", "\n", "# Data Preprocessing\n", "import pandas as pd\n", "import pandas_profiling as pp\n", "import os\n", "import re\n", "import chardet\n", "import random\n", "from io import BytesIO\n", "from pathlib import Path\n", "\n", "# Reading Directories\n", "import glob\n", "import os\n", "\n", "# Working with JSON\n", "import json\n", "from pandas.io.json import json_normalize\n", "\n", "# Data Visualisation\n", "import matplotlib as mpl\n", "import matplotlib.pyplot as plt\n", "import seaborn as sns\n", "import missingno as msno\n", "\n", "# Progress Bar\n", "from tqdm import tqdm\n", "\n", "# Display in Jupyter\n", "from IPython.display import Image, YouTubeVideo\n", "from IPython.core.display import HTML\n", "\n", "# Ignore Warnings\n", "import warnings\n", "warnings.filterwarnings(action=\"ignore\", message=\"^internal gelsd\")\n", "\n", "print(\"Setup Complete\")" ] }, { "cell_type": "code", "execution_count": 2, "metadata": { "scrolled": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Python: 3.7.6\n", "NumPy: 1.20.3\n", "pandas: 1.3.2\n", "matplotlib: 3.4.2\n" ] } ], "source": [ "# Python / module versions used here for reference\n", "print('Python: {}'.format(platform.python_version()))\n", "print('NumPy: {}'.format(np.__version__))\n", "print('pandas: {}'.format(pd.__version__))\n", "print('matplotlib: {}'.format(mpl.__version__))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Defined Variables" ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [], "source": [ "# Define today's date\n", "today = datetime.datetime.now().strftime('%d/%m/%Y').replace('/', '')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Defined Filepaths" ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [], "source": [ "# Set up initial paths to subfolders\n", "base_dir = os.path.join('..', '..')\n", "data_dir = os.path.join(base_dir, 'data')\n", "data_dir_sb = os.path.join(base_dir, 'data', 'sb')\n", "img_dir = os.path.join(base_dir, 'img')\n", "fig_dir = os.path.join(base_dir, 'img', 'fig')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Create Directory Structure" ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [], "source": [ "# make the directory structure\n", "for folder in ['combined', 'competitions', 'events', 'matches']:\n", " path = os.path.join(data_dir_sb, 'raw', folder)\n", " if not os.path.exists(path):\n", " os.mkdir(path)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Custom Functions" ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [], "source": [ "# Define custom functions for used in the notebook\n", "\n", "## Function to read JSON files that also handles the encoding of special characters e.g. accents in names of players and teams\n", "def read_json_file(filename):\n", " with open(filename, 'rb') as json_file:\n", " return BytesIO(json_file.read()).getvalue().decode('unicode_escape')\n", "\n", " \n", "## Function to flatten pandas DataFrames with nested JSON columns. Source: https://stackoverflow.com/questions/39899005/how-to-flatten-a-pandas-dataframe-with-some-columns-as-json\n", "def flatten_nested_json_df(df):\n", "\n", " df = df.reset_index()\n", "\n", " print(f\"original shape: {df.shape}\")\n", " print(f\"original columns: {df.columns}\")\n", "\n", "\n", " # search for columns to explode/flatten\n", " s = (df.applymap(type) == list).all()\n", " list_columns = s[s].index.tolist()\n", "\n", " s = (df.applymap(type) == dict).all()\n", " dict_columns = s[s].index.tolist()\n", "\n", " print(f\"lists: {list_columns}, dicts: {dict_columns}\")\n", " while len(list_columns) > 0 or len(dict_columns) > 0:\n", " new_columns = []\n", "\n", " for col in dict_columns:\n", " print(f\"flattening: {col}\")\n", " # explode dictionaries horizontally, adding new columns\n", " horiz_exploded = pd.json_normalize(df[col]).add_prefix(f'{col}.')\n", " horiz_exploded.index = df.index\n", " df = pd.concat([df, horiz_exploded], axis=1).drop(columns=[col])\n", " new_columns.extend(horiz_exploded.columns) # inplace\n", "\n", " for col in list_columns:\n", " print(f\"exploding: {col}\")\n", " # explode lists vertically, adding new columns\n", " df = df.drop(columns=[col]).join(df[col].explode().to_frame())\n", " new_columns.append(col)\n", "\n", " # check if there are still dict o list fields to flatten\n", " s = (df[new_columns].applymap(type) == list).all()\n", " list_columns = s[s].index.tolist()\n", "\n", " s = (df[new_columns].applymap(type) == dict).all()\n", " dict_columns = s[s].index.tolist()\n", "\n", " print(f\"lists: {list_columns}, dicts: {dict_columns}\")\n", "\n", " print(f\"final shape: {df.shape}\")\n", " print(f\"final columns: {df.columns}\")\n", " return df" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Notebook Settings" ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [], "source": [ "# Display all columns of displayed pandas DataFrames\n", "pd.set_option('display.max_columns', None)\n", "pd.options.mode.chained_assignment=None" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "---\n", "\n", "\n", "\n", "## 2. Notebook Brief\n", "This Jupyter notebook is part of a series of notebooks to parse and engineer StatsBomb Event data.\n", "\n", "This particular notebook is the **StatsBomb Data Parsing** notebook for 360 data, that takes raw JSON data downloaded from the StatsBomb Open Data GitHub Repository and converts this to event level data that is saved as a CSV file.\n", "\n", "Links to these notebooks in the [`football_analytics`](https://github.com/eddwebster/football_analytics) GitHub repository can be found at the following:\n", "* [Data Parsing](https://github.com/eddwebster/football_analytics/tree/master/notebooks/2_data_parsing)\n", " + [StatsBomb Data Parsing](https://github.com/eddwebster/football_analytics/blob/master/notebooks/2_data_parsing/ELO%20Team%20Ratings%20Data%20Parsing.ipynb)\n", "* [Data Engineering](https://github.com/eddwebster/football_analytics/tree/master/notebooks/3_data_engineering)\n", " + [StatsBomb Data Engineering](https://github.com/eddwebster/football_analytics/blob/master/notebooks/3_data_engineering/FBref%20Player%20Stats%20Data%20Engineering.ipynb)\n", "\n", "**Notebook Conventions**:
\n", "* Variables that refer a `DataFrame` object are prefixed with `df_`.\n", "* Variables that refer to a collection of `DataFrame` objects (e.g., a list, a set or a dict) are prefixed with `dfs_`." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "---\n", "\n", "\n", "\n", "## 3. Data Sources" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 3.1. Introduction" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### 3.1.1. About StatsBomb\n", "[StatsBomb](https://statsbomb.com/) are a football analytics and data company.\n", "\n", "![title](../../img/logos/stats-bomb-logo.png)\n", "\n", "Before conducting our EDA, the data needs to be imported as a DataFrame in the Data Sources section [Section 3](#section3) and Cleaned in the Data Engineering section [Section 4](#section4).\n", "\n", "We'll be using the [pandas](http://pandas.pydata.org/) library to import our data to this workbook as a DataFrame." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### 3.1.2. About the StatsBomb publicly available data\n", "The complete data set contains:\n", "- 7 competitions;\n", "- 879 matches;\n", "- 3,161,917 events; and\n", "- z players.\n", "\n", "The datasets we will be using are:\n", "- competitions;\n", "- matches;\n", "- events;\n", "- lineups; and\n", "- tactics;\n", "\n", "The data needs to be imported as a DataFrame in the Data Sources section [Section 3](#section3) and cleaned in the Data Engineering section [Section 4](#section4)." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 3.2. Downloading StatsBomb Data" ] }, { "cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [], "source": [ "# ADD CODE HERE" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 3.3. Reading In and Parsing the JSON Data\n", "The following cells read in the `JSON` files into a [pandas](https://pandas.pydata.org/) `DataFrame` object with some basic Data Engineering to flatten the data and select only the columns of interest ensuring that the Jupyter otebook does not crash on a standard laptop." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### 3.3.1. Competitions" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "##### Data dictionary" ] }, { "cell_type": "code", "execution_count": 9, "metadata": {}, "outputs": [], "source": [ "# ADD MARKDOWN TABLE OF DATA HERE" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "##### Read in JSON files" ] }, { "cell_type": "code", "execution_count": 10, "metadata": { "scrolled": true }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "['../../data/sb/raw/competitions/competitions_wc2018.csv', '../../data/sb/raw/competitions/competitions.csv', '../../data/sb/raw/competitions/competitions_male.csv']\n" ] } ], "source": [ "# Show files in directory\n", "print(glob.glob(os.path.join(data_dir_sb, 'raw', 'competitions/*')))" ] }, { "cell_type": "code", "execution_count": 13, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
competition_idseason_idcountry_namecompetition_namecompetition_gendercompetition_youthcompetition_internationalseason_namematch_updatedmatch_updated_360match_available_360match_available
0164EuropeChampions LeaguemaleFalseFalse2018/20192021-08-27T11:26:39.8028322021-06-13T16:17:31.694None2021-07-09T14:06:05.802
1161EuropeChampions LeaguemaleFalseFalse2017/20182021-08-27T11:26:39.8028322021-06-13T16:17:31.694None2021-01-23T21:55:30.425330
2162EuropeChampions LeaguemaleFalseFalse2016/20172021-08-27T11:26:39.8028322021-06-13T16:17:31.694None2020-07-29T05:00
31627EuropeChampions LeaguemaleFalseFalse2015/20162021-08-27T11:26:39.8028322021-06-13T16:17:31.694None2020-07-29T05:00
41626EuropeChampions LeaguemaleFalseFalse2014/20152021-08-27T11:26:39.8028322021-06-13T16:17:31.694None2020-07-29T05:00
51625EuropeChampions LeaguemaleFalseFalse2013/20142021-08-27T11:26:39.8028322021-06-13T16:17:31.694None2020-07-29T05:00
61624EuropeChampions LeaguemaleFalseFalse2012/20132021-08-27T11:26:39.8028322021-06-13T16:17:31.694None2021-07-10T13:41:45.751
71623EuropeChampions LeaguemaleFalseFalse2011/20122021-08-27T11:26:39.8028322021-06-13T16:17:31.694None2020-07-29T05:00
81622EuropeChampions LeaguemaleFalseFalse2010/20112021-06-22T21:17:46.3812021-06-13T16:17:31.694None2021-06-22T21:17:46.381
91621EuropeChampions LeaguemaleFalseFalse2009/20102021-06-22T21:24:20.5062021-06-13T16:17:31.694None2021-06-22T21:24:20.506
101641EuropeChampions LeaguemaleFalseFalse2008/20092021-11-07T14:20:01.6999932021-06-13T16:17:31.694None2021-11-07T14:20:01.699993
111639EuropeChampions LeaguemaleFalseFalse2006/20072021-03-31T04:18:30.4370602021-06-13T16:17:31.694None2021-03-31T04:18:30.437060
121637EuropeChampions LeaguemaleFalseFalse2004/20052021-04-01T06:18:57.4590322021-06-13T16:17:31.694None2021-04-01T06:18:57.459032
131644EuropeChampions LeaguemaleFalseFalse2003/20042021-04-01T00:34:59.4724852021-06-13T16:17:31.694None2021-04-01T00:34:59.472485
141676EuropeChampions LeaguemaleFalseFalse1999/20002020-07-29T05:002021-06-13T16:17:31.694None2020-07-29T05:00
153790EnglandFA Women's Super LeaguefemaleFalseFalse2020/20212021-07-01T18:14:40.7562021-06-13T16:17:31.694None2021-07-01T18:14:40.756
163742EnglandFA Women's Super LeaguefemaleFalseFalse2019/20202021-06-01T13:01:18.1882021-06-13T16:17:31.694None2021-06-01T13:01:18.188
17374EnglandFA Women's Super LeaguefemaleFalseFalse2018/20192021-12-02T12:09:35.5850462021-06-13T16:17:31.694None2021-12-02T12:09:35.585046
18433InternationalFIFA World CupmaleFalseTrue20182021-08-05T16:04:30.0812021-06-13T16:17:31.694None2021-08-05T16:04:30.081
191190SpainLa LigamaleFalseFalse2020/20212021-11-28T09:47:02.5051222021-10-30T04:19:36.1166002021-09-17T15:18:33.7877902021-11-28T09:47:02.505122
201142SpainLa LigamaleFalseFalse2019/20202021-06-15T15:35:02.6732021-06-13T16:17:31.694None2021-06-15T15:35:02.673
21114SpainLa LigamaleFalseFalse2018/20192021-11-02T17:53:14.5299522021-07-09T14:53:22.103024None2021-11-02T17:53:14.529952
22111SpainLa LigamaleFalseFalse2017/20182021-08-27T11:26:39.8028322021-06-13T16:17:31.694None2021-05-19T08:38:06.507959
23112SpainLa LigamaleFalseFalse2016/20172021-08-07T22:30:18.2422021-06-13T16:17:31.694None2021-08-07T22:30:18.242
241127SpainLa LigamaleFalseFalse2015/20162020-07-29T05:002021-06-13T16:17:31.694None2020-07-29T05:00
251126SpainLa LigamaleFalseFalse2014/20152020-07-29T05:002021-06-13T16:17:31.694None2020-07-29T05:00
261125SpainLa LigamaleFalseFalse2013/20142020-07-29T05:002021-06-13T16:17:31.694None2020-07-29T05:00
271124SpainLa LigamaleFalseFalse2012/20132021-10-27T15:44:43.9408622021-06-13T16:17:31.694None2021-10-27T15:44:43.940862
281123SpainLa LigamaleFalseFalse2011/20122020-07-29T05:002021-06-13T16:17:31.694None2020-07-29T05:00
291122SpainLa LigamaleFalseFalse2010/20112021-11-11T22:57:42.3619022021-06-13T16:17:31.694None2021-11-11T22:57:42.361902
301121SpainLa LigamaleFalseFalse2009/20102021-10-26T13:56:40.9892142021-06-13T16:17:31.694None2021-10-26T13:56:40.989214
311141SpainLa LigamaleFalseFalse2008/20092020-07-29T05:002021-06-13T16:17:31.694None2020-07-29T05:00
321140SpainLa LigamaleFalseFalse2007/20082021-10-26T13:13:56.1805892021-06-13T16:17:31.694None2021-10-26T13:13:56.180589
331139SpainLa LigamaleFalseFalse2006/20072020-07-29T05:002021-06-13T16:17:31.694None2020-07-29T05:00
341138SpainLa LigamaleFalseFalse2005/20062021-11-28T23:00:27.7473962021-06-13T16:17:31.694None2021-11-28T23:00:27.747396
351137SpainLa LigamaleFalseFalse2004/20052020-07-29T05:002021-06-13T16:17:31.694None2020-07-29T05:00
36493United States of AmericaNWSLfemaleFalseFalse20182021-11-06T05:53:29.4350162021-06-13T16:17:31.694None2021-11-06T05:53:29.435016
37244EnglandPremier LeaguemaleFalseFalse2003/20042021-11-14T22:29:00.6461202021-06-13T16:17:31.694None2021-11-14T22:29:00.646120
385543EuropeUEFA EuromaleFalseTrue20202021-11-11T14:00:16.1058092021-11-11T13:54:37.5073762021-11-11T13:54:37.5073762021-11-11T14:00:16.105809
397230InternationalWomen's World CupfemaleFalseTrue20192020-07-29T05:002021-06-13T16:17:31.694None2020-07-29T05:00
\n", "
" ], "text/plain": [ " competition_id season_id country_name \\\n", "0 16 4 Europe \n", "1 16 1 Europe \n", "2 16 2 Europe \n", "3 16 27 Europe \n", "4 16 26 Europe \n", "5 16 25 Europe \n", "6 16 24 Europe \n", "7 16 23 Europe \n", "8 16 22 Europe \n", "9 16 21 Europe \n", "10 16 41 Europe \n", "11 16 39 Europe \n", "12 16 37 Europe \n", "13 16 44 Europe \n", "14 16 76 Europe \n", "15 37 90 England \n", "16 37 42 England \n", "17 37 4 England \n", "18 43 3 International \n", "19 11 90 Spain \n", "20 11 42 Spain \n", "21 11 4 Spain \n", "22 11 1 Spain \n", "23 11 2 Spain \n", "24 11 27 Spain \n", "25 11 26 Spain \n", "26 11 25 Spain \n", "27 11 24 Spain \n", "28 11 23 Spain \n", "29 11 22 Spain \n", "30 11 21 Spain \n", "31 11 41 Spain \n", "32 11 40 Spain \n", "33 11 39 Spain \n", "34 11 38 Spain \n", "35 11 37 Spain \n", "36 49 3 United States of America \n", "37 2 44 England \n", "38 55 43 Europe \n", "39 72 30 International \n", "\n", " competition_name competition_gender competition_youth \\\n", "0 Champions League male False \n", "1 Champions League male False \n", "2 Champions League male False \n", "3 Champions League male False \n", "4 Champions League male False \n", "5 Champions League male False \n", "6 Champions League male False \n", "7 Champions League male False \n", "8 Champions League male False \n", "9 Champions League male False \n", "10 Champions League male False \n", "11 Champions League male False \n", "12 Champions League male False \n", "13 Champions League male False \n", "14 Champions League male False \n", "15 FA Women's Super League female False \n", "16 FA Women's Super League female False \n", "17 FA Women's Super League female False \n", "18 FIFA World Cup male False \n", "19 La Liga male False \n", "20 La Liga male False \n", "21 La Liga male False \n", "22 La Liga male False \n", "23 La Liga male False \n", "24 La Liga male False \n", "25 La Liga male False \n", "26 La Liga male False \n", "27 La Liga male False \n", "28 La Liga male False \n", "29 La Liga male False \n", "30 La Liga male False \n", "31 La Liga male False \n", "32 La Liga male False \n", "33 La Liga male False \n", "34 La Liga male False \n", "35 La Liga male False \n", "36 NWSL female False \n", "37 Premier League male False \n", "38 UEFA Euro male False \n", "39 Women's World Cup female False \n", "\n", " competition_international season_name match_updated \\\n", "0 False 2018/2019 2021-08-27T11:26:39.802832 \n", "1 False 2017/2018 2021-08-27T11:26:39.802832 \n", "2 False 2016/2017 2021-08-27T11:26:39.802832 \n", "3 False 2015/2016 2021-08-27T11:26:39.802832 \n", "4 False 2014/2015 2021-08-27T11:26:39.802832 \n", "5 False 2013/2014 2021-08-27T11:26:39.802832 \n", "6 False 2012/2013 2021-08-27T11:26:39.802832 \n", "7 False 2011/2012 2021-08-27T11:26:39.802832 \n", "8 False 2010/2011 2021-06-22T21:17:46.381 \n", "9 False 2009/2010 2021-06-22T21:24:20.506 \n", "10 False 2008/2009 2021-11-07T14:20:01.699993 \n", "11 False 2006/2007 2021-03-31T04:18:30.437060 \n", "12 False 2004/2005 2021-04-01T06:18:57.459032 \n", "13 False 2003/2004 2021-04-01T00:34:59.472485 \n", "14 False 1999/2000 2020-07-29T05:00 \n", "15 False 2020/2021 2021-07-01T18:14:40.756 \n", "16 False 2019/2020 2021-06-01T13:01:18.188 \n", "17 False 2018/2019 2021-12-02T12:09:35.585046 \n", "18 True 2018 2021-08-05T16:04:30.081 \n", "19 False 2020/2021 2021-11-28T09:47:02.505122 \n", "20 False 2019/2020 2021-06-15T15:35:02.673 \n", "21 False 2018/2019 2021-11-02T17:53:14.529952 \n", "22 False 2017/2018 2021-08-27T11:26:39.802832 \n", "23 False 2016/2017 2021-08-07T22:30:18.242 \n", "24 False 2015/2016 2020-07-29T05:00 \n", "25 False 2014/2015 2020-07-29T05:00 \n", "26 False 2013/2014 2020-07-29T05:00 \n", "27 False 2012/2013 2021-10-27T15:44:43.940862 \n", "28 False 2011/2012 2020-07-29T05:00 \n", "29 False 2010/2011 2021-11-11T22:57:42.361902 \n", "30 False 2009/2010 2021-10-26T13:56:40.989214 \n", "31 False 2008/2009 2020-07-29T05:00 \n", "32 False 2007/2008 2021-10-26T13:13:56.180589 \n", "33 False 2006/2007 2020-07-29T05:00 \n", "34 False 2005/2006 2021-11-28T23:00:27.747396 \n", "35 False 2004/2005 2020-07-29T05:00 \n", "36 False 2018 2021-11-06T05:53:29.435016 \n", "37 False 2003/2004 2021-11-14T22:29:00.646120 \n", "38 True 2020 2021-11-11T14:00:16.105809 \n", "39 True 2019 2020-07-29T05:00 \n", "\n", " match_updated_360 match_available_360 \\\n", "0 2021-06-13T16:17:31.694 None \n", "1 2021-06-13T16:17:31.694 None \n", "2 2021-06-13T16:17:31.694 None \n", "3 2021-06-13T16:17:31.694 None \n", "4 2021-06-13T16:17:31.694 None \n", "5 2021-06-13T16:17:31.694 None \n", "6 2021-06-13T16:17:31.694 None \n", "7 2021-06-13T16:17:31.694 None \n", "8 2021-06-13T16:17:31.694 None \n", "9 2021-06-13T16:17:31.694 None \n", "10 2021-06-13T16:17:31.694 None \n", "11 2021-06-13T16:17:31.694 None \n", "12 2021-06-13T16:17:31.694 None \n", "13 2021-06-13T16:17:31.694 None \n", "14 2021-06-13T16:17:31.694 None \n", "15 2021-06-13T16:17:31.694 None \n", "16 2021-06-13T16:17:31.694 None \n", "17 2021-06-13T16:17:31.694 None \n", "18 2021-06-13T16:17:31.694 None \n", "19 2021-10-30T04:19:36.116600 2021-09-17T15:18:33.787790 \n", "20 2021-06-13T16:17:31.694 None \n", "21 2021-07-09T14:53:22.103024 None \n", "22 2021-06-13T16:17:31.694 None \n", "23 2021-06-13T16:17:31.694 None \n", "24 2021-06-13T16:17:31.694 None \n", "25 2021-06-13T16:17:31.694 None \n", "26 2021-06-13T16:17:31.694 None \n", "27 2021-06-13T16:17:31.694 None \n", "28 2021-06-13T16:17:31.694 None \n", "29 2021-06-13T16:17:31.694 None \n", "30 2021-06-13T16:17:31.694 None \n", "31 2021-06-13T16:17:31.694 None \n", "32 2021-06-13T16:17:31.694 None \n", "33 2021-06-13T16:17:31.694 None \n", "34 2021-06-13T16:17:31.694 None \n", "35 2021-06-13T16:17:31.694 None \n", "36 2021-06-13T16:17:31.694 None \n", "37 2021-06-13T16:17:31.694 None \n", "38 2021-11-11T13:54:37.507376 2021-11-11T13:54:37.507376 \n", "39 2021-06-13T16:17:31.694 None \n", "\n", " match_available \n", "0 2021-07-09T14:06:05.802 \n", "1 2021-01-23T21:55:30.425330 \n", "2 2020-07-29T05:00 \n", "3 2020-07-29T05:00 \n", "4 2020-07-29T05:00 \n", "5 2020-07-29T05:00 \n", "6 2021-07-10T13:41:45.751 \n", "7 2020-07-29T05:00 \n", "8 2021-06-22T21:17:46.381 \n", "9 2021-06-22T21:24:20.506 \n", "10 2021-11-07T14:20:01.699993 \n", "11 2021-03-31T04:18:30.437060 \n", "12 2021-04-01T06:18:57.459032 \n", "13 2021-04-01T00:34:59.472485 \n", "14 2020-07-29T05:00 \n", "15 2021-07-01T18:14:40.756 \n", "16 2021-06-01T13:01:18.188 \n", "17 2021-12-02T12:09:35.585046 \n", "18 2021-08-05T16:04:30.081 \n", "19 2021-11-28T09:47:02.505122 \n", "20 2021-06-15T15:35:02.673 \n", "21 2021-11-02T17:53:14.529952 \n", "22 2021-05-19T08:38:06.507959 \n", "23 2021-08-07T22:30:18.242 \n", "24 2020-07-29T05:00 \n", "25 2020-07-29T05:00 \n", "26 2020-07-29T05:00 \n", "27 2021-10-27T15:44:43.940862 \n", "28 2020-07-29T05:00 \n", "29 2021-11-11T22:57:42.361902 \n", "30 2021-10-26T13:56:40.989214 \n", "31 2020-07-29T05:00 \n", "32 2021-10-26T13:13:56.180589 \n", "33 2020-07-29T05:00 \n", "34 2021-11-28T23:00:27.747396 \n", "35 2020-07-29T05:00 \n", "36 2021-11-06T05:53:29.435016 \n", "37 2021-11-14T22:29:00.646120 \n", "38 2021-11-11T14:00:16.105809 \n", "39 2020-07-29T05:00 " ] }, "execution_count": 13, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Read in exported CSV file if exists, if not, read in JSON file\n", "\n", "## \n", "if not os.path.exists(os.path.join(data_dir_sb, 'raw', 'competitions', 'competitions.csv')):\n", " json_competitions = read_json_file(os.path.join(data_dir_sb, 'open-data', 'data', 'competitions.json'))\n", " df_competitions_flat = pd.read_json(json_competitions)\n", "\n", "## \n", "else:\n", " df_competitions_flat = pd.read_csv(os.path.join(data_dir_sb, 'raw', 'competitions', 'competitions.csv')) \n", "\n", "\n", "# Display DataFrame\n", "df_competitions_flat" ] }, { "cell_type": "code", "execution_count": 14, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "(40, 12)" ] }, "execution_count": 14, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_competitions_flat.shape" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "##### Identify the Competition of Interest\n", "For our analysis, we only want to take the players that have played in the **male** competitions." ] }, { "cell_type": "code", "execution_count": 15, "metadata": {}, "outputs": [], "source": [ "# Filter DataFrame for rows where 'competition_gender' is equal to 'male'\n", "df_competitions_flat = df_competitions_flat.loc[(df_competitions_flat['competition_id'] == 55) & \n", " (df_competitions_flat['season_id'] == 43)\n", " ]" ] }, { "cell_type": "code", "execution_count": 16, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
competition_idseason_idcountry_namecompetition_namecompetition_gendercompetition_youthcompetition_internationalseason_namematch_updatedmatch_updated_360match_available_360match_available
385543EuropeUEFA EuromaleFalseTrue20202021-11-11T14:00:16.1058092021-11-11T13:54:37.5073762021-11-11T13:54:37.5073762021-11-11T14:00:16.105809
\n", "
" ], "text/plain": [ " competition_id season_id country_name competition_name \\\n", "38 55 43 Europe UEFA Euro \n", "\n", " competition_gender competition_youth competition_international \\\n", "38 male False True \n", "\n", " season_name match_updated match_updated_360 \\\n", "38 2020 2021-11-11T14:00:16.105809 2021-11-11T13:54:37.507376 \n", "\n", " match_available_360 match_available \n", "38 2021-11-11T13:54:37.507376 2021-11-11T14:00:16.105809 " ] }, "execution_count": 16, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_competitions_flat" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "##### Export DataFrame" ] }, { "cell_type": "code", "execution_count": 17, "metadata": {}, "outputs": [], "source": [ "# Export DataFrame as a CSV file\n", "\n", "##\n", "if not os.path.exists(os.path.join(data_dir_sb, 'raw', 'competitions', 'competitions_sb_360.csv')):\n", " df_competitions_flat.to_csv(os.path.join(data_dir_sb, 'raw', 'competitions', 'competitions_sb_360.csv'), index=None, header=True)\n", "\n", "## \n", "else:\n", " pass" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### 3.3.2. Matches" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "##### Data Dictionary" ] }, { "cell_type": "code", "execution_count": 18, "metadata": {}, "outputs": [], "source": [ "# ADD MARKDOWN TABLE OF DATA HERE" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "##### Define competitions\n", "The following cell lists the competitions to be included in the dataset. Dataset includes data for seven different competitions - 5 domestic and 2 international." ] }, { "cell_type": "code", "execution_count": 19, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "[55]" ] }, "execution_count": 19, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Define a list to select only the competitions of interest. \n", "\n", "# Flatmap all Competition IDs to use all available competitions\n", "lst_competitions = df_competitions_flat['competition_id'].unique().tolist()\n", "\n", "\"\"\"\n", "# Define list of competitions\n", "lst_competitions = [2, # Premier League\n", " 11, # La Liga\n", " 16, # Champions League\n", " #37, # FA Women's Super League\n", " 43, # FIFA World Cup\n", " #49, # NWSL\n", " #55, # UEFA Euro\n", " #72, # Women's World Cup\n", " ]\n", "\n", "\"\"\"\n", "\n", "# Display list of competitions\n", "lst_competitions" ] }, { "cell_type": "code", "execution_count": 20, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "1" ] }, "execution_count": 20, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Display the number of competitions\n", "len(lst_competitions)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "##### Read in JSON files" ] }, { "cell_type": "code", "execution_count": 21, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "['../../data/sb/raw/matches/matches.csv', '../../data/sb/raw/matches/matches_male.csv', '../../data/sb/raw/matches/matches_wc2018.csv']\n" ] } ], "source": [ "# Show files in directory\n", "print(glob.glob(os.path.join(data_dir_sb, 'raw', 'matches/*')))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Steps: \n", "* Loop through match files for the select competitions.\n", "* Take the separate JSON file each representing the matches for the selected competitions. This file is called {match_id}.json.\n", "* Read JSON file as a pandas DataFrame.\n", "* Append the DataFrames to a list.\n", "* Finally, concatenate all the separate DataFrames into one DataFrame of matches." ] }, { "cell_type": "code", "execution_count": 22, "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "/opt/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:28: FutureWarning: pandas.io.json.json_normalize is deprecated, use pandas.json_normalize instead\n" ] }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
match_idmatch_datekick_offhome_scoreaway_scorematch_statusmatch_status_360last_updatedlast_updated_360match_weekcompetition.competition_idcompetition.country_namecompetition.competition_nameseason.season_idseason.season_namehome_team.home_team_idhome_team.home_team_namehome_team.home_team_genderhome_team.home_team_grouphome_team.country.idhome_team.country.namehome_team.managersaway_team.away_team_idaway_team.away_team_nameaway_team.away_team_genderaway_team.away_team_groupaway_team.country.idaway_team.country.nameaway_team.managersmetadata.data_versionmetadata.shot_fidelity_versionmetadata.xy_fidelity_versioncompetition_stage.idcompetition_stage.namestadium.idstadium.namestadium.country.idstadium.country.namereferee.idreferee.namereferee.country.idreferee.country.name
037887532021-06-1615:00:00.00001availableavailable2021-11-11T14:00:16.1058092021-09-22T16:39:05.697512255EuropeUEFA Euro4320201835FinlandmaleGroup B77Finland[{'id': 3622, 'name': 'Markku Kanerva', 'nickn...796RussiamaleGroup B188Russia[{'id': 365, 'name': 'Stanislav Cherchesov', '...1.1.02210Group Stage4726Saint-Petersburg Stadium188Russia293Danny Desmond Makkelie160Netherlands
137887652021-06-2018:00:00.00031availableavailable2021-08-02T14:58:49.0572021-11-11T13:54:37.507376355EuropeUEFA Euro432020773SwitzerlandmaleGroup A221Switzerland[{'id': 492, 'name': 'Vladimir Petković', 'nic...909TurkeymaleGroup A233Turkey[{'id': 701, 'name': 'Şenol Güneş', 'nickname'...1.1.02210Group Stage4549Bakı Olimpiya Stadionu16Azerbaijan943Slavko Vinčić208Slovenia
237951072021-07-0221:00:00.00012availableavailable2021-07-19T12:41:55.8982021-09-23T00:02:51.495862555EuropeUEFA Euro432020782BelgiummaleNone22Belgium[{'id': 263, 'name': 'Roberto Martínez Montoli...914ItalymaleNone112Italy[{'id': 2997, 'name': 'Roberto Mancini', 'nick...1.1.02211Quarter-finals4867Allianz Arena (München)85Germany943Slavko Vinčić208Slovenia
337952212021-07-0721:00:00.00021availableavailable2021-07-09T12:38:23.4372021-09-22T22:33:37.494366655EuropeUEFA Euro432020768EnglandmaleNone68England[{'id': 277, 'name': 'Gareth Southgate', 'nick...776DenmarkmaleNone61Denmark[{'id': 255, 'name': 'Kasper Hjulmand', 'nickn...1.1.02215Semi-finals4666Wembley Stadium (London)68England293Danny Desmond Makkelie160Netherlands
437955062021-07-1121:00:00.00011availableavailable2021-07-12T12:27:50.6472021-09-22T22:40:31.690550755EuropeUEFA Euro432020914ItalymaleNone112Italy[{'id': 2997, 'name': 'Roberto Mancini', 'nick...768EnglandmaleNone68England[{'id': 277, 'name': 'Gareth Southgate', 'nick...1.1.02226Final4666Wembley Stadium (London)68England287Björn Kuipers160Netherlands
\n", "
" ], "text/plain": [ " match_id match_date kick_off home_score away_score match_status \\\n", "0 3788753 2021-06-16 15:00:00.000 0 1 available \n", "1 3788765 2021-06-20 18:00:00.000 3 1 available \n", "2 3795107 2021-07-02 21:00:00.000 1 2 available \n", "3 3795221 2021-07-07 21:00:00.000 2 1 available \n", "4 3795506 2021-07-11 21:00:00.000 1 1 available \n", "\n", " match_status_360 last_updated last_updated_360 \\\n", "0 available 2021-11-11T14:00:16.105809 2021-09-22T16:39:05.697512 \n", "1 available 2021-08-02T14:58:49.057 2021-11-11T13:54:37.507376 \n", "2 available 2021-07-19T12:41:55.898 2021-09-23T00:02:51.495862 \n", "3 available 2021-07-09T12:38:23.437 2021-09-22T22:33:37.494366 \n", "4 available 2021-07-12T12:27:50.647 2021-09-22T22:40:31.690550 \n", "\n", " match_week competition.competition_id competition.country_name \\\n", "0 2 55 Europe \n", "1 3 55 Europe \n", "2 5 55 Europe \n", "3 6 55 Europe \n", "4 7 55 Europe \n", "\n", " competition.competition_name season.season_id season.season_name \\\n", "0 UEFA Euro 43 2020 \n", "1 UEFA Euro 43 2020 \n", "2 UEFA Euro 43 2020 \n", "3 UEFA Euro 43 2020 \n", "4 UEFA Euro 43 2020 \n", "\n", " home_team.home_team_id home_team.home_team_name home_team.home_team_gender \\\n", "0 1835 Finland male \n", "1 773 Switzerland male \n", "2 782 Belgium male \n", "3 768 England male \n", "4 914 Italy male \n", "\n", " home_team.home_team_group home_team.country.id home_team.country.name \\\n", "0 Group B 77 Finland \n", "1 Group A 221 Switzerland \n", "2 None 22 Belgium \n", "3 None 68 England \n", "4 None 112 Italy \n", "\n", " home_team.managers away_team.away_team_id \\\n", "0 [{'id': 3622, 'name': 'Markku Kanerva', 'nickn... 796 \n", "1 [{'id': 492, 'name': 'Vladimir Petković', 'nic... 909 \n", "2 [{'id': 263, 'name': 'Roberto Martínez Montoli... 914 \n", "3 [{'id': 277, 'name': 'Gareth Southgate', 'nick... 776 \n", "4 [{'id': 2997, 'name': 'Roberto Mancini', 'nick... 768 \n", "\n", " away_team.away_team_name away_team.away_team_gender \\\n", "0 Russia male \n", "1 Turkey male \n", "2 Italy male \n", "3 Denmark male \n", "4 England male \n", "\n", " away_team.away_team_group away_team.country.id away_team.country.name \\\n", "0 Group B 188 Russia \n", "1 Group A 233 Turkey \n", "2 None 112 Italy \n", "3 None 61 Denmark \n", "4 None 68 England \n", "\n", " away_team.managers metadata.data_version \\\n", "0 [{'id': 365, 'name': 'Stanislav Cherchesov', '... 1.1.0 \n", "1 [{'id': 701, 'name': 'Şenol Güneş', 'nickname'... 1.1.0 \n", "2 [{'id': 2997, 'name': 'Roberto Mancini', 'nick... 1.1.0 \n", "3 [{'id': 255, 'name': 'Kasper Hjulmand', 'nickn... 1.1.0 \n", "4 [{'id': 277, 'name': 'Gareth Southgate', 'nick... 1.1.0 \n", "\n", " metadata.shot_fidelity_version metadata.xy_fidelity_version \\\n", "0 2 2 \n", "1 2 2 \n", "2 2 2 \n", "3 2 2 \n", "4 2 2 \n", "\n", " competition_stage.id competition_stage.name stadium.id \\\n", "0 10 Group Stage 4726 \n", "1 10 Group Stage 4549 \n", "2 11 Quarter-finals 4867 \n", "3 15 Semi-finals 4666 \n", "4 26 Final 4666 \n", "\n", " stadium.name stadium.country.id stadium.country.name \\\n", "0 Saint-Petersburg Stadium 188 Russia \n", "1 Bakı Olimpiya Stadionu 16 Azerbaijan \n", "2 Allianz Arena (München) 85 Germany \n", "3 Wembley Stadium (London) 68 England \n", "4 Wembley Stadium (London) 68 England \n", "\n", " referee.id referee.name referee.country.id referee.country.name \n", "0 293 Danny Desmond Makkelie 160 Netherlands \n", "1 943 Slavko Vinčić 208 Slovenia \n", "2 943 Slavko Vinčić 208 Slovenia \n", "3 293 Danny Desmond Makkelie 160 Netherlands \n", "4 287 Björn Kuipers 160 Netherlands " ] }, "execution_count": 22, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Read in selected matches\n", "\n", "## Read in exported CSV file if exists, if not, read in JSON file\n", "if not os.path.exists(os.path.join(data_dir_sb, 'raw', 'matches', 'matches_sb_360.csv')):\n", " \n", " ### Create empty list for DataFrames\n", " dfs_matches_all = []\n", " \n", " ### Loop through the selected competitions\n", " for competition in lst_competitions:\n", "\n", " ### Create empty list for DataFrames\n", " dfs_matches_competition = []\n", " \n", " #### Show files in directory\n", " lst_filepaths = list(glob.glob(data_dir_sb + '/open-data/data/matches/' + str(competition) + '/*'))\n", " \n", " for filepath in lst_filepaths:\n", " \n", " ##### Open the JSON filepath with defined Competition and Season IDs\n", " try:\n", "\n", " ###### Import all StatsBomb JSON Match data for the mens matches\n", " with open(filepath) as f:\n", " json_sb_match_data = json.load(f)\n", "\n", " ###### Flatten the JSON Match data\n", " df_matches_flat = json_normalize(json_sb_match_data)\n", "\n", " ###### Append each Match data to \n", " dfs_matches_competition.append(df_matches_flat)\n", "\n", " ## Concatenate DataFrames to one DataFrame\n", " df_matches_competition = pd.concat(dfs_matches_competition)\n", "\n", " #####\n", " except:\n", " pass\n", "\n", " ## Concatenate DataFrames to one DataFrame\n", " dfs_matches_all.append(df_matches_competition)\n", " \n", " ## Concatenate DataFrames to one DataFrame\n", " df_matches_flat = pd.concat(dfs_matches_all)\n", " \n", "##\n", "else: \n", " df_matches_flat = pd.read_csv(os.path.join(data_dir_sb, 'raw', 'matches', 'matches_sb_360.csv'))\n", " \n", " \n", "## Display DataFrame\n", "df_matches_flat.head()" ] }, { "cell_type": "code", "execution_count": 23, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "(51, 42)" ] }, "execution_count": 23, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_matches_flat.shape" ] }, { "cell_type": "code", "execution_count": 24, "metadata": { "scrolled": true }, "outputs": [ { "data": { "text/plain": [ "competition.competition_name season.season_name\n", "UEFA Euro 2020 51\n", "Name: match_id, dtype: int64" ] }, "execution_count": 24, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Shot outcomes types and their frequency\n", "df_matches_flat.groupby(['competition.competition_name', 'season.season_name']).match_id.count()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "There are 51 games in the UEFA Euro 2020 that can be used as part of the Expected Goals model." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "##### Convert `match_id` column to list\n", "List used as reference of matches to parse for Events, Lineups, and Tactics data - iteration through list comprehension." ] }, { "cell_type": "code", "execution_count": 25, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "51" ] }, "execution_count": 25, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Flatmap all Match IDs to use all available matches\n", "lst_matches = df_matches_flat['match_id'].tolist()\n", "\n", "# Display the number of matches\n", "len(lst_matches)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "##### Export DataFrame" ] }, { "cell_type": "code", "execution_count": 26, "metadata": {}, "outputs": [], "source": [ "# Export DataFrame as a CSV file\n", "\n", "##\n", "if not os.path.exists(os.path.join(data_dir_sb, 'raw', 'matches', 'matches_sb_360.csv')):\n", " df_matches_flat.to_csv(os.path.join(data_dir_sb, 'raw', 'matches', 'matches_sb_360.csv'), index=None, header=True)\n", "\n", "## \n", "else:\n", " pass" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### 3.3.3. Events" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "##### Data dictionary" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The [StatsBomb](https://statsbomb.com/) dataset has one hundred and fourteen features (columns) with the following definitions and data types:\n", "\n", "| Feature | Data type |\n", "|------|-----|\n", "| `id` | `object`\n", "| `index` | `object`\n", "| `period` | `object`\n", "| `timestamp` | `object`\n", "| `minute` | `object`\n", "| `second` | `object`\n", "| `possession` | `object`\n", "| `duration` | `object`\n", "| `type.id` | `object`\n", "| `type.name` | `object`\n", "| `possession_team.id` | `object`\n", "| `possession_team.name` | `object`\n", "| `play_pattern.id` | `object`\n", "| `play_pattern.name` | `object`\n", "| `team.id` | `object`\n", "| `team.name` | `object`\n", "| `tactics.formation` | `object`\n", "| `tactics.lineup` | `object`\n", "| `related_events` | `object`\n", "| `location` | `object`\n", "| `player.id` | `object`\n", "| `player.name` | `object`\n", "| `position.id` | `object`\n", "| `position.name` | `object`\n", "| `pass.recipient.id` | `object`\n", "| `pass.recipient.name` | `object`\n", "| `pass.length` | `object`\n", "| `pass.angle` | `object`\n", "| `pass.height.id` | `object`\n", "| `pass.height.name` | `object`\n", "| `pass.end_location` | `object`\n", "| `pass.type.id` | `object`\n", "| `pass.type.name` | `object`\n", "| `pass.body_part.id` | `object`\n", "| `pass.body_part.name` | `object`\n", "| `carry.end_location` | `object`\n", "| `under_pressure` | `object`\n", "| `duel.type.id` | `object`\n", "| `duel.type.name` | `object`\n", "| `out` | `object`\n", "| `miscontrol.aerial_won` | `object`\n", "| `pass.outcome.id` | `object`\n", "| `pass.outcome.name` | `object`\n", "| `ball_receipt.outcome.id` | `object`\n", "| `ball_receipt.outcome.name` | `object`\n", "| `pass.aerial_won` | `object`\n", "| `counterpress` | `object`\n", "| `off_camera` | `object`\n", "| `dribble.outcome.id` | `object`\n", "| `dribble.outcome.name` | `object`\n", "| `dribble.overrun` | `object`\n", "| `ball_recovery.offensive` | `object`\n", "| `shot.statsbomb_xg` | `object`\n", "| `shot.end_location` | `object`\n", "| `shot.outcome.id` | `object`\n", "| `shot.outcome.name` | `object`\n", "| `shot.type.id` | `object`\n", "| `shot.type.name` | `object`\n", "| `shot.body_part.id` | `object`\n", "| `shot.body_part.name` | `object`\n", "| `shot.technique.id` | `object`\n", "| `shot.technique.name` | `object`\n", "| `shot.freeze_frame` | `object`\n", "| `goalkeeper.end_location` | `object`\n", "| `goalkeeper.type.id` | `object`\n", "| `goalkeeper.type.name` | `object`\n", "| `goalkeeper.position.id` | `object`\n", "| `goalkeeper.position.name` | `object`\n", "| `pass.straight` | `object`\n", "| `pass.technique.id` | `object`\n", "| `pass.technique.name` | `object`\n", "| `clearance.head` | `object`\n", "| `clearance.body_part.id` | `object`\n", "| `clearance.body_part.name` | `object`\n", "| `pass.switch` | `object`\n", "| `duel.outcome.id` | `object`\n", "| `duel.outcome.name` | `object`\n", "| `foul_committed.advantage` | `object`\n", "| `foul_won.advantage` | `object`\n", "| `pass.cross` | `object`\n", "| `pass.assisted_shot_id` | `object`\n", "| `pass.shot_assist` | `object`\n", "| `shot.one_on_one` | `object`\n", "| `shot.key_pass_id` | `object`\n", "| `goalkeeper.body_part.id` | `object`\n", "| `goalkeeper.body_part.name` | `object`\n", "| `goalkeeper.technique.id` | `object`\n", "| `goalkeeper.technique.name` | `object`\n", "| `goalkeeper.outcome.id` | `object`\n", "| `goalkeeper.outcome.name` | `object`\n", "| `clearance.aerial_won` | `object`\n", "| `foul_committed.card.id` | `object`\n", "| `foul_committed.card.name` | `object`\n", "| `foul_won.defensive` | `object`\n", "| `clearance.right_foot` | `object`\n", "| `shot.first_time` | `object`\n", "| `pass.through_ball` | `object`\n", "| `interception.outcome.id` | `object`\n", "| `interception.outcome.name` | `object`\n", "| `clearance.left_foot` | `object`\n", "| `ball_recovery.recovery_failure` | `object`\n", "| `shot.aerial_won` | `object`\n", "| `pass.goal_assist` | `object`\n", "| `pass.cut_back` | `object`\n", "| `pass.deflected` | `object`\n", "| `clearance.other` | `object`\n", "| `pass.outswinging` | `object`\n", "| `substitution.outcome.id` | `object`\n", "| `substitution.outcome.name` | `object`\n", "| `substitution.replacement.id` | `object`\n", "| `substitution.replacement.name` | `object`\n", "| `block.deflection` | `object`\n", "| `block.offensive` | `object`\n", "| `injury_stoppage.in_chain` | `object`\n", "\n", "For a full list of definitions, see the official documentation [[link](https://statsbomb.com/stat-definitions/)]." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "##### Read in JSON files" ] }, { "cell_type": "code", "execution_count": 27, "metadata": { "scrolled": true }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "['../../data/sb/raw/events/events_male.csv', '../../data/sb/raw/events/events_wc2018.csv']\n" ] } ], "source": [ "# Show files in directory\n", "print(glob.glob(os.path.join(data_dir_sb, 'raw', 'events/*')))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Steps: \n", "* Loop through the matches files for the selected match(es)\n", "* Take the separate JSON file each representing theevents match for the selected matches. This file is called {match_id}.json.\n", "* Read the corresponding JSON matches files using the auxillary function\n", "* Read JSON file as a pandas DataFrame\n", "* Append the DataFrames to a list\n", "* Finally, concatenate all the separate DataFrames into one DataFrame" ] }, { "cell_type": "code", "execution_count": 28, "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "/opt/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:16: FutureWarning: pandas.io.json.json_normalize is deprecated, use pandas.json_normalize instead\n", " app.launch_new_instance()\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "original shape: (192686, 144)\n", "original columns: Index(['level_0', 'id', 'index', 'period', 'timestamp', 'minute', 'second',\n", " 'possession', 'duration', 'type.id',\n", " ...\n", " 'goalkeeper.shot_saved_to_post', 'shot.open_goal',\n", " 'goalkeeper.penalty_saved_to_post', 'dribble.no_touch',\n", " 'block.offensive', 'shot.follows_dribble', 'ball_recovery.offensive',\n", " 'shot.redirect', 'goalkeeper.lost_in_play',\n", " 'goalkeeper.success_in_play'],\n", " dtype='object', length=144)\n", "lists: [], dicts: []\n", "final shape: (192686, 144)\n", "final columns: Index(['level_0', 'id', 'index', 'period', 'timestamp', 'minute', 'second',\n", " 'possession', 'duration', 'type.id',\n", " ...\n", " 'goalkeeper.shot_saved_to_post', 'shot.open_goal',\n", " 'goalkeeper.penalty_saved_to_post', 'dribble.no_touch',\n", " 'block.offensive', 'shot.follows_dribble', 'ball_recovery.offensive',\n", " 'shot.redirect', 'goalkeeper.lost_in_play',\n", " 'goalkeeper.success_in_play'],\n", " dtype='object', length=144)\n" ] }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
level_0idindexperiodtimestampminutesecondpossessiondurationtype.idtype.namepossession_team.idpossession_team.nameplay_pattern.idplay_pattern.nameteam.idteam.nametactics.formationtactics.lineuprelated_eventslocationplayer.idplayer.nameposition.idposition.namepass.recipient.idpass.recipient.namepass.lengthpass.anglepass.height.idpass.height.namepass.end_locationpass.body_part.idpass.body_part.namepass.type.idpass.type.namecarry.end_locationunder_pressureduel.type.idduel.type.namepass.aerial_woncounterpressduel.outcome.idduel.outcome.namedribble.outcome.iddribble.outcome.namepass.outcome.idpass.outcome.nameball_receipt.outcome.idball_receipt.outcome.nameinterception.outcome.idinterception.outcome.nameshot.statsbomb_xgshot.end_locationshot.outcome.idshot.outcome.nameshot.type.idshot.type.nameshot.body_part.idshot.body_part.nameshot.technique.idshot.technique.nameshot.freeze_framegoalkeeper.end_locationgoalkeeper.type.idgoalkeeper.type.namegoalkeeper.position.idgoalkeeper.position.nameoutpass.outswingingpass.technique.idpass.technique.nameclearance.headclearance.body_part.idclearance.body_part.namepass.switchoff_camerapass.crossclearance.left_footdribble.overrundribble.nutmegclearance.right_footpass.no_touchfoul_committed.advantagefoul_won.advantagepass.assisted_shot_idpass.shot_assistshot.key_pass_idshot.first_timeclearance.otherpass.miscommunicationclearance.aerial_wonpass.through_ballball_recovery.recovery_failuregoalkeeper.outcome.idgoalkeeper.outcome.namegoalkeeper.body_part.idgoalkeeper.body_part.nameshot.aerial_wonfoul_committed.card.idfoul_committed.card.namefoul_committed.offensivefoul_won.defensivesubstitution.outcome.idsubstitution.outcome.namesubstitution.replacement.idsubstitution.replacement.name50_50.outcome.id50_50.outcome.namepass.goal_assistgoalkeeper.technique.idgoalkeeper.technique.namepass.cut_backmiscontrol.aerial_wonpass.straightfoul_committed.type.idfoul_committed.type.namematch_idpass.inswingingpass.deflectedinjury_stoppage.in_chainshot.one_on_onebad_behaviour.card.idbad_behaviour.card.nameshot.deflectedblock.deflectionfoul_committed.penaltyfoul_won.penaltyblock.save_blockgoalkeeper.punched_outplayer_off.permanentshot.saved_off_targetgoalkeeper.shot_saved_off_targetshot.saved_to_postgoalkeeper.shot_saved_to_postshot.open_goalgoalkeeper.penalty_saved_to_postdribble.no_touchblock.offensiveshot.follows_dribbleball_recovery.offensiveshot.redirectgoalkeeper.lost_in_playgoalkeeper.success_in_play
009427b18a-6b10-411f-90da-3d6240b80c711100:00:00.0000010.00000035Starting XI1835Finland1Regular Play1835Finland352.0[{'player': {'id': 8667, 'name': 'Lukáš Hrádec...NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN3788753NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN
11542c58bf-5c6c-43ca-9d8d-e086c7f08aaf2100:00:00.0000010.00000035Starting XI1835Finland1Regular Play796Russia3421.0[{'player': {'id': 21298, 'name': 'Matvey Safo...NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN3788753NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN
22a0dfe8a0-a0b9-443e-89e3-a8ba6596fa333100:00:00.0000010.00000018Half Start1835Finland1Regular Play1835FinlandNaNNaN[c7156352-f4b7-4140-aa51-6e26fd019a11]NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN3788753NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN
33c7156352-f4b7-4140-aa51-6e26fd019a114100:00:00.0000010.00000018Half Start1835Finland1Regular Play796RussiaNaNNaN[a0dfe8a0-a0b9-443e-89e3-a8ba6596fa33]NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN3788753NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN
4494dbc5c3-ef37-445e-9154-3d9f9ea9245d5100:00:00.4900021.37321530Pass796Russia9From Kick Off796RussiaNaNNaN[c0935bbe-3eb4-4a21-9eee-45f380d1f26d][60.0, 40.0]6299.0Aleksey Miranchuk18.0Right Attacking Midfield31917.0Igor Diveev22.3573253.0699671.0Ground Pass[37.7, 41.6]38.0Left Foot65.0Kick OffNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN3788753NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN
\n", "
" ], "text/plain": [ " level_0 id index period timestamp \\\n", "0 0 9427b18a-6b10-411f-90da-3d6240b80c71 1 1 00:00:00.000 \n", "1 1 542c58bf-5c6c-43ca-9d8d-e086c7f08aaf 2 1 00:00:00.000 \n", "2 2 a0dfe8a0-a0b9-443e-89e3-a8ba6596fa33 3 1 00:00:00.000 \n", "3 3 c7156352-f4b7-4140-aa51-6e26fd019a11 4 1 00:00:00.000 \n", "4 4 94dbc5c3-ef37-445e-9154-3d9f9ea9245d 5 1 00:00:00.490 \n", "\n", " minute second possession duration type.id type.name \\\n", "0 0 0 1 0.000000 35 Starting XI \n", "1 0 0 1 0.000000 35 Starting XI \n", "2 0 0 1 0.000000 18 Half Start \n", "3 0 0 1 0.000000 18 Half Start \n", "4 0 0 2 1.373215 30 Pass \n", "\n", " possession_team.id possession_team.name play_pattern.id play_pattern.name \\\n", "0 1835 Finland 1 Regular Play \n", "1 1835 Finland 1 Regular Play \n", "2 1835 Finland 1 Regular Play \n", "3 1835 Finland 1 Regular Play \n", "4 796 Russia 9 From Kick Off \n", "\n", " team.id team.name tactics.formation \\\n", "0 1835 Finland 352.0 \n", "1 796 Russia 3421.0 \n", "2 1835 Finland NaN \n", "3 796 Russia NaN \n", "4 796 Russia NaN \n", "\n", " tactics.lineup \\\n", "0 [{'player': {'id': 8667, 'name': 'Lukáš Hrádec... \n", "1 [{'player': {'id': 21298, 'name': 'Matvey Safo... \n", "2 NaN \n", "3 NaN \n", "4 NaN \n", "\n", " related_events location player.id \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 [c7156352-f4b7-4140-aa51-6e26fd019a11] NaN NaN \n", "3 [a0dfe8a0-a0b9-443e-89e3-a8ba6596fa33] NaN NaN \n", "4 [c0935bbe-3eb4-4a21-9eee-45f380d1f26d] [60.0, 40.0] 6299.0 \n", "\n", " player.name position.id position.name \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 Aleksey Miranchuk 18.0 Right Attacking Midfield \n", "\n", " pass.recipient.id pass.recipient.name pass.length pass.angle \\\n", "0 NaN NaN NaN NaN \n", "1 NaN NaN NaN NaN \n", "2 NaN NaN NaN NaN \n", "3 NaN NaN NaN NaN \n", "4 31917.0 Igor Diveev 22.357325 3.069967 \n", "\n", " pass.height.id pass.height.name pass.end_location pass.body_part.id \\\n", "0 NaN NaN NaN NaN \n", "1 NaN NaN NaN NaN \n", "2 NaN NaN NaN NaN \n", "3 NaN NaN NaN NaN \n", "4 1.0 Ground Pass [37.7, 41.6] 38.0 \n", "\n", " pass.body_part.name pass.type.id pass.type.name carry.end_location \\\n", "0 NaN NaN NaN NaN \n", "1 NaN NaN NaN NaN \n", "2 NaN NaN NaN NaN \n", "3 NaN NaN NaN NaN \n", "4 Left Foot 65.0 Kick Off NaN \n", "\n", " under_pressure duel.type.id duel.type.name pass.aerial_won counterpress \\\n", "0 NaN NaN NaN NaN NaN \n", "1 NaN NaN NaN NaN NaN \n", "2 NaN NaN NaN NaN NaN \n", "3 NaN NaN NaN NaN NaN \n", "4 NaN NaN NaN NaN NaN \n", "\n", " duel.outcome.id duel.outcome.name dribble.outcome.id dribble.outcome.name \\\n", "0 NaN NaN NaN NaN \n", "1 NaN NaN NaN NaN \n", "2 NaN NaN NaN NaN \n", "3 NaN NaN NaN NaN \n", "4 NaN NaN NaN NaN \n", "\n", " pass.outcome.id pass.outcome.name ball_receipt.outcome.id \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " ball_receipt.outcome.name interception.outcome.id \\\n", "0 NaN NaN \n", "1 NaN NaN \n", "2 NaN NaN \n", "3 NaN NaN \n", "4 NaN NaN \n", "\n", " interception.outcome.name shot.statsbomb_xg shot.end_location \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " shot.outcome.id shot.outcome.name shot.type.id shot.type.name \\\n", "0 NaN NaN NaN NaN \n", "1 NaN NaN NaN NaN \n", "2 NaN NaN NaN NaN \n", "3 NaN NaN NaN NaN \n", "4 NaN NaN NaN NaN \n", "\n", " shot.body_part.id shot.body_part.name shot.technique.id \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " shot.technique.name shot.freeze_frame goalkeeper.end_location \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " goalkeeper.type.id goalkeeper.type.name goalkeeper.position.id \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " goalkeeper.position.name out pass.outswinging pass.technique.id \\\n", "0 NaN NaN NaN NaN \n", "1 NaN NaN NaN NaN \n", "2 NaN NaN NaN NaN \n", "3 NaN NaN NaN NaN \n", "4 NaN NaN NaN NaN \n", "\n", " pass.technique.name clearance.head clearance.body_part.id \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " clearance.body_part.name pass.switch off_camera pass.cross \\\n", "0 NaN NaN NaN NaN \n", "1 NaN NaN NaN NaN \n", "2 NaN NaN NaN NaN \n", "3 NaN NaN NaN NaN \n", "4 NaN NaN NaN NaN \n", "\n", " clearance.left_foot dribble.overrun dribble.nutmeg clearance.right_foot \\\n", "0 NaN NaN NaN NaN \n", "1 NaN NaN NaN NaN \n", "2 NaN NaN NaN NaN \n", "3 NaN NaN NaN NaN \n", "4 NaN NaN NaN NaN \n", "\n", " pass.no_touch foul_committed.advantage foul_won.advantage \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " pass.assisted_shot_id pass.shot_assist shot.key_pass_id shot.first_time \\\n", "0 NaN NaN NaN NaN \n", "1 NaN NaN NaN NaN \n", "2 NaN NaN NaN NaN \n", "3 NaN NaN NaN NaN \n", "4 NaN NaN NaN NaN \n", "\n", " clearance.other pass.miscommunication clearance.aerial_won \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " pass.through_ball ball_recovery.recovery_failure goalkeeper.outcome.id \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " goalkeeper.outcome.name goalkeeper.body_part.id goalkeeper.body_part.name \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " shot.aerial_won foul_committed.card.id foul_committed.card.name \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " foul_committed.offensive foul_won.defensive substitution.outcome.id \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " substitution.outcome.name substitution.replacement.id \\\n", "0 NaN NaN \n", "1 NaN NaN \n", "2 NaN NaN \n", "3 NaN NaN \n", "4 NaN NaN \n", "\n", " substitution.replacement.name 50_50.outcome.id 50_50.outcome.name \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " pass.goal_assist goalkeeper.technique.id goalkeeper.technique.name \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " pass.cut_back miscontrol.aerial_won pass.straight foul_committed.type.id \\\n", "0 NaN NaN NaN NaN \n", "1 NaN NaN NaN NaN \n", "2 NaN NaN NaN NaN \n", "3 NaN NaN NaN NaN \n", "4 NaN NaN NaN NaN \n", "\n", " foul_committed.type.name match_id pass.inswinging pass.deflected \\\n", "0 NaN 3788753 NaN NaN \n", "1 NaN 3788753 NaN NaN \n", "2 NaN 3788753 NaN NaN \n", "3 NaN 3788753 NaN NaN \n", "4 NaN 3788753 NaN NaN \n", "\n", " injury_stoppage.in_chain shot.one_on_one bad_behaviour.card.id \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " bad_behaviour.card.name shot.deflected block.deflection \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " foul_committed.penalty foul_won.penalty block.save_block \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " goalkeeper.punched_out player_off.permanent shot.saved_off_target \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " goalkeeper.shot_saved_off_target shot.saved_to_post \\\n", "0 NaN NaN \n", "1 NaN NaN \n", "2 NaN NaN \n", "3 NaN NaN \n", "4 NaN NaN \n", "\n", " goalkeeper.shot_saved_to_post shot.open_goal \\\n", "0 NaN NaN \n", "1 NaN NaN \n", "2 NaN NaN \n", "3 NaN NaN \n", "4 NaN NaN \n", "\n", " goalkeeper.penalty_saved_to_post dribble.no_touch block.offensive \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " shot.follows_dribble ball_recovery.offensive shot.redirect \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " goalkeeper.lost_in_play goalkeeper.success_in_play \n", "0 NaN NaN \n", "1 NaN NaN \n", "2 NaN NaN \n", "3 NaN NaN \n", "4 NaN NaN " ] }, "execution_count": 28, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Read in exported CSV file if exists, if not, read in JSON file\n", "\n", "##\n", "if not os.path.exists(os.path.join(data_dir_sb, 'raw', 'events', 'events_sb_360.csv')):\n", "\n", " ### Create empty list for DataFrames\n", " dfs_events = []\n", "\n", " ### Loop through event files for the selected matches and append DataFrame to dfs_events list\n", " for match_id in lst_matches:\n", " \n", " #### \n", " with open(data_dir_sb + '/open-data/data/events/' + str(match_id) + '.json') as f:\n", " event = json.load(f)\n", " #match_id = str(match_id)\n", " df_event_flat = json_normalize(event)\n", " df_event_flat['match_id'] = match_id\n", " dfs_events.append(df_event_flat) \n", "\n", " ### Concatenate DataFrames to one DataFrame\n", " df_events = pd.concat(dfs_events)\n", " \n", " ### Flatten the nested columns\n", " df_events_flat = flatten_nested_json_df(df_events)\n", " \n", " \n", "##\n", "else: \n", " df_events_flat = pd.read_csv(os.path.join(data_dir_sb, 'raw', 'events', 'events_sb_360.csv'))\n", " \n", " \n", "## Display DataFrame\n", "df_events_flat.head()" ] }, { "cell_type": "code", "execution_count": 29, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "(192686, 144)" ] }, "execution_count": 29, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_events_flat.shape" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "##### Export DataFrame" ] }, { "cell_type": "code", "execution_count": 30, "metadata": {}, "outputs": [], "source": [ "# Export DataFrame as a CSV file\n", "\n", "## \n", "if not os.path.exists(os.path.join(data_dir_sb, 'raw', 'events', 'events_sb_360.csv')):\n", " df_events_flat.to_csv(os.path.join(data_dir_sb, 'raw', 'events', 'events_sb_360.csv'), index=None, header=True)\n", "\n", "## \n", "else:\n", " pass" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 3.4. Join the Datasets\n", "The final step of the data parsing is to join the `Matches` DataFrame and the `Competition` DataFrames to the `Events` DataFrame. The `Events` data is the base DataFrame in which we join the other tables via `match_id` and `competition.competition_id`." ] }, { "cell_type": "code", "execution_count": 31, "metadata": { "scrolled": true }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
level_0idindexperiodtimestampminutesecondpossessiondurationtype.idtype.namepossession_team.idpossession_team.nameplay_pattern.idplay_pattern.nameteam.idteam.nametactics.formationtactics.lineuprelated_eventslocationplayer.idplayer.nameposition.idposition.namepass.recipient.idpass.recipient.namepass.lengthpass.anglepass.height.idpass.height.namepass.end_locationpass.body_part.idpass.body_part.namepass.type.idpass.type.namecarry.end_locationunder_pressureduel.type.idduel.type.namepass.aerial_woncounterpressduel.outcome.idduel.outcome.namedribble.outcome.iddribble.outcome.namepass.outcome.idpass.outcome.nameball_receipt.outcome.idball_receipt.outcome.nameinterception.outcome.idinterception.outcome.nameshot.statsbomb_xgshot.end_locationshot.outcome.idshot.outcome.nameshot.type.idshot.type.nameshot.body_part.idshot.body_part.nameshot.technique.idshot.technique.nameshot.freeze_framegoalkeeper.end_locationgoalkeeper.type.idgoalkeeper.type.namegoalkeeper.position.idgoalkeeper.position.nameoutpass.outswingingpass.technique.idpass.technique.nameclearance.headclearance.body_part.idclearance.body_part.namepass.switchoff_camerapass.crossclearance.left_footdribble.overrundribble.nutmegclearance.right_footpass.no_touchfoul_committed.advantagefoul_won.advantagepass.assisted_shot_idpass.shot_assistshot.key_pass_idshot.first_timeclearance.otherpass.miscommunicationclearance.aerial_wonpass.through_ballball_recovery.recovery_failuregoalkeeper.outcome.idgoalkeeper.outcome.namegoalkeeper.body_part.idgoalkeeper.body_part.nameshot.aerial_wonfoul_committed.card.idfoul_committed.card.namefoul_committed.offensivefoul_won.defensivesubstitution.outcome.idsubstitution.outcome.namesubstitution.replacement.idsubstitution.replacement.name50_50.outcome.id50_50.outcome.namepass.goal_assistgoalkeeper.technique.idgoalkeeper.technique.namepass.cut_backmiscontrol.aerial_wonpass.straightfoul_committed.type.idfoul_committed.type.namematch_idpass.inswingingpass.deflectedinjury_stoppage.in_chainshot.one_on_onebad_behaviour.card.idbad_behaviour.card.nameshot.deflectedblock.deflectionfoul_committed.penaltyfoul_won.penaltyblock.save_blockgoalkeeper.punched_outplayer_off.permanentshot.saved_off_targetgoalkeeper.shot_saved_off_targetshot.saved_to_postgoalkeeper.shot_saved_to_postshot.open_goalgoalkeeper.penalty_saved_to_postdribble.no_touchblock.offensiveshot.follows_dribbleball_recovery.offensiveshot.redirectgoalkeeper.lost_in_playgoalkeeper.success_in_playmatch_datekick_offhome_scoreaway_scorematch_statusmatch_status_360last_updatedlast_updated_360match_weekcompetition.competition_idcompetition.country_namecompetition.competition_nameseason.season_idseason.season_namehome_team.home_team_idhome_team.home_team_namehome_team.home_team_genderhome_team.home_team_grouphome_team.country.idhome_team.country.namehome_team.managersaway_team.away_team_idaway_team.away_team_nameaway_team.away_team_genderaway_team.away_team_groupaway_team.country.idaway_team.country.nameaway_team.managersmetadata.data_versionmetadata.shot_fidelity_versionmetadata.xy_fidelity_versioncompetition_stage.idcompetition_stage.namestadium.idstadium.namestadium.country.idstadium.country.namereferee.idreferee.namereferee.country.idreferee.country.namecompetition_idseason_idcountry_namecompetition_namecompetition_gendercompetition_youthcompetition_internationalseason_namematch_updatedmatch_updated_360match_available_360match_available
009427b18a-6b10-411f-90da-3d6240b80c711100:00:00.0000010.00000035Starting XI1835Finland1Regular Play1835Finland352.0[{'player': {'id': 8667, 'name': 'Lukáš Hrádec...NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN3788753NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN2021-06-1615:00:00.00001availableavailable2021-11-11T14:00:16.1058092021-09-22T16:39:05.697512255EuropeUEFA Euro4320201835FinlandmaleGroup B77Finland[{'id': 3622, 'name': 'Markku Kanerva', 'nickn...796RussiamaleGroup B188Russia[{'id': 365, 'name': 'Stanislav Cherchesov', '...1.1.02210Group Stage4726Saint-Petersburg Stadium188Russia293Danny Desmond Makkelie160Netherlands5543EuropeUEFA EuromaleFalseTrue20202021-11-11T14:00:16.1058092021-11-11T13:54:37.5073762021-11-11T13:54:37.5073762021-11-11T14:00:16.105809
11542c58bf-5c6c-43ca-9d8d-e086c7f08aaf2100:00:00.0000010.00000035Starting XI1835Finland1Regular Play796Russia3421.0[{'player': {'id': 21298, 'name': 'Matvey Safo...NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN3788753NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN2021-06-1615:00:00.00001availableavailable2021-11-11T14:00:16.1058092021-09-22T16:39:05.697512255EuropeUEFA Euro4320201835FinlandmaleGroup B77Finland[{'id': 3622, 'name': 'Markku Kanerva', 'nickn...796RussiamaleGroup B188Russia[{'id': 365, 'name': 'Stanislav Cherchesov', '...1.1.02210Group Stage4726Saint-Petersburg Stadium188Russia293Danny Desmond Makkelie160Netherlands5543EuropeUEFA EuromaleFalseTrue20202021-11-11T14:00:16.1058092021-11-11T13:54:37.5073762021-11-11T13:54:37.5073762021-11-11T14:00:16.105809
22a0dfe8a0-a0b9-443e-89e3-a8ba6596fa333100:00:00.0000010.00000018Half Start1835Finland1Regular Play1835FinlandNaNNaN[c7156352-f4b7-4140-aa51-6e26fd019a11]NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN3788753NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN2021-06-1615:00:00.00001availableavailable2021-11-11T14:00:16.1058092021-09-22T16:39:05.697512255EuropeUEFA Euro4320201835FinlandmaleGroup B77Finland[{'id': 3622, 'name': 'Markku Kanerva', 'nickn...796RussiamaleGroup B188Russia[{'id': 365, 'name': 'Stanislav Cherchesov', '...1.1.02210Group Stage4726Saint-Petersburg Stadium188Russia293Danny Desmond Makkelie160Netherlands5543EuropeUEFA EuromaleFalseTrue20202021-11-11T14:00:16.1058092021-11-11T13:54:37.5073762021-11-11T13:54:37.5073762021-11-11T14:00:16.105809
33c7156352-f4b7-4140-aa51-6e26fd019a114100:00:00.0000010.00000018Half Start1835Finland1Regular Play796RussiaNaNNaN[a0dfe8a0-a0b9-443e-89e3-a8ba6596fa33]NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN3788753NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN2021-06-1615:00:00.00001availableavailable2021-11-11T14:00:16.1058092021-09-22T16:39:05.697512255EuropeUEFA Euro4320201835FinlandmaleGroup B77Finland[{'id': 3622, 'name': 'Markku Kanerva', 'nickn...796RussiamaleGroup B188Russia[{'id': 365, 'name': 'Stanislav Cherchesov', '...1.1.02210Group Stage4726Saint-Petersburg Stadium188Russia293Danny Desmond Makkelie160Netherlands5543EuropeUEFA EuromaleFalseTrue20202021-11-11T14:00:16.1058092021-11-11T13:54:37.5073762021-11-11T13:54:37.5073762021-11-11T14:00:16.105809
4494dbc5c3-ef37-445e-9154-3d9f9ea9245d5100:00:00.4900021.37321530Pass796Russia9From Kick Off796RussiaNaNNaN[c0935bbe-3eb4-4a21-9eee-45f380d1f26d][60.0, 40.0]6299.0Aleksey Miranchuk18.0Right Attacking Midfield31917.0Igor Diveev22.3573253.0699671.0Ground Pass[37.7, 41.6]38.0Left Foot65.0Kick OffNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN3788753NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN2021-06-1615:00:00.00001availableavailable2021-11-11T14:00:16.1058092021-09-22T16:39:05.697512255EuropeUEFA Euro4320201835FinlandmaleGroup B77Finland[{'id': 3622, 'name': 'Markku Kanerva', 'nickn...796RussiamaleGroup B188Russia[{'id': 365, 'name': 'Stanislav Cherchesov', '...1.1.02210Group Stage4726Saint-Petersburg Stadium188Russia293Danny Desmond Makkelie160Netherlands5543EuropeUEFA EuromaleFalseTrue20202021-11-11T14:00:16.1058092021-11-11T13:54:37.5073762021-11-11T13:54:37.5073762021-11-11T14:00:16.105809
\n", "
" ], "text/plain": [ " level_0 id index period timestamp \\\n", "0 0 9427b18a-6b10-411f-90da-3d6240b80c71 1 1 00:00:00.000 \n", "1 1 542c58bf-5c6c-43ca-9d8d-e086c7f08aaf 2 1 00:00:00.000 \n", "2 2 a0dfe8a0-a0b9-443e-89e3-a8ba6596fa33 3 1 00:00:00.000 \n", "3 3 c7156352-f4b7-4140-aa51-6e26fd019a11 4 1 00:00:00.000 \n", "4 4 94dbc5c3-ef37-445e-9154-3d9f9ea9245d 5 1 00:00:00.490 \n", "\n", " minute second possession duration type.id type.name \\\n", "0 0 0 1 0.000000 35 Starting XI \n", "1 0 0 1 0.000000 35 Starting XI \n", "2 0 0 1 0.000000 18 Half Start \n", "3 0 0 1 0.000000 18 Half Start \n", "4 0 0 2 1.373215 30 Pass \n", "\n", " possession_team.id possession_team.name play_pattern.id play_pattern.name \\\n", "0 1835 Finland 1 Regular Play \n", "1 1835 Finland 1 Regular Play \n", "2 1835 Finland 1 Regular Play \n", "3 1835 Finland 1 Regular Play \n", "4 796 Russia 9 From Kick Off \n", "\n", " team.id team.name tactics.formation \\\n", "0 1835 Finland 352.0 \n", "1 796 Russia 3421.0 \n", "2 1835 Finland NaN \n", "3 796 Russia NaN \n", "4 796 Russia NaN \n", "\n", " tactics.lineup \\\n", "0 [{'player': {'id': 8667, 'name': 'Lukáš Hrádec... \n", "1 [{'player': {'id': 21298, 'name': 'Matvey Safo... \n", "2 NaN \n", "3 NaN \n", "4 NaN \n", "\n", " related_events location player.id \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 [c7156352-f4b7-4140-aa51-6e26fd019a11] NaN NaN \n", "3 [a0dfe8a0-a0b9-443e-89e3-a8ba6596fa33] NaN NaN \n", "4 [c0935bbe-3eb4-4a21-9eee-45f380d1f26d] [60.0, 40.0] 6299.0 \n", "\n", " player.name position.id position.name \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 Aleksey Miranchuk 18.0 Right Attacking Midfield \n", "\n", " pass.recipient.id pass.recipient.name pass.length pass.angle \\\n", "0 NaN NaN NaN NaN \n", "1 NaN NaN NaN NaN \n", "2 NaN NaN NaN NaN \n", "3 NaN NaN NaN NaN \n", "4 31917.0 Igor Diveev 22.357325 3.069967 \n", "\n", " pass.height.id pass.height.name pass.end_location pass.body_part.id \\\n", "0 NaN NaN NaN NaN \n", "1 NaN NaN NaN NaN \n", "2 NaN NaN NaN NaN \n", "3 NaN NaN NaN NaN \n", "4 1.0 Ground Pass [37.7, 41.6] 38.0 \n", "\n", " pass.body_part.name pass.type.id pass.type.name carry.end_location \\\n", "0 NaN NaN NaN NaN \n", "1 NaN NaN NaN NaN \n", "2 NaN NaN NaN NaN \n", "3 NaN NaN NaN NaN \n", "4 Left Foot 65.0 Kick Off NaN \n", "\n", " under_pressure duel.type.id duel.type.name pass.aerial_won counterpress \\\n", "0 NaN NaN NaN NaN NaN \n", "1 NaN NaN NaN NaN NaN \n", "2 NaN NaN NaN NaN NaN \n", "3 NaN NaN NaN NaN NaN \n", "4 NaN NaN NaN NaN NaN \n", "\n", " duel.outcome.id duel.outcome.name dribble.outcome.id dribble.outcome.name \\\n", "0 NaN NaN NaN NaN \n", "1 NaN NaN NaN NaN \n", "2 NaN NaN NaN NaN \n", "3 NaN NaN NaN NaN \n", "4 NaN NaN NaN NaN \n", "\n", " pass.outcome.id pass.outcome.name ball_receipt.outcome.id \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " ball_receipt.outcome.name interception.outcome.id \\\n", "0 NaN NaN \n", "1 NaN NaN \n", "2 NaN NaN \n", "3 NaN NaN \n", "4 NaN NaN \n", "\n", " interception.outcome.name shot.statsbomb_xg shot.end_location \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " shot.outcome.id shot.outcome.name shot.type.id shot.type.name \\\n", "0 NaN NaN NaN NaN \n", "1 NaN NaN NaN NaN \n", "2 NaN NaN NaN NaN \n", "3 NaN NaN NaN NaN \n", "4 NaN NaN NaN NaN \n", "\n", " shot.body_part.id shot.body_part.name shot.technique.id \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " shot.technique.name shot.freeze_frame goalkeeper.end_location \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " goalkeeper.type.id goalkeeper.type.name goalkeeper.position.id \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " goalkeeper.position.name out pass.outswinging pass.technique.id \\\n", "0 NaN NaN NaN NaN \n", "1 NaN NaN NaN NaN \n", "2 NaN NaN NaN NaN \n", "3 NaN NaN NaN NaN \n", "4 NaN NaN NaN NaN \n", "\n", " pass.technique.name clearance.head clearance.body_part.id \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " clearance.body_part.name pass.switch off_camera pass.cross \\\n", "0 NaN NaN NaN NaN \n", "1 NaN NaN NaN NaN \n", "2 NaN NaN NaN NaN \n", "3 NaN NaN NaN NaN \n", "4 NaN NaN NaN NaN \n", "\n", " clearance.left_foot dribble.overrun dribble.nutmeg clearance.right_foot \\\n", "0 NaN NaN NaN NaN \n", "1 NaN NaN NaN NaN \n", "2 NaN NaN NaN NaN \n", "3 NaN NaN NaN NaN \n", "4 NaN NaN NaN NaN \n", "\n", " pass.no_touch foul_committed.advantage foul_won.advantage \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " pass.assisted_shot_id pass.shot_assist shot.key_pass_id shot.first_time \\\n", "0 NaN NaN NaN NaN \n", "1 NaN NaN NaN NaN \n", "2 NaN NaN NaN NaN \n", "3 NaN NaN NaN NaN \n", "4 NaN NaN NaN NaN \n", "\n", " clearance.other pass.miscommunication clearance.aerial_won \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " pass.through_ball ball_recovery.recovery_failure goalkeeper.outcome.id \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " goalkeeper.outcome.name goalkeeper.body_part.id goalkeeper.body_part.name \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " shot.aerial_won foul_committed.card.id foul_committed.card.name \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " foul_committed.offensive foul_won.defensive substitution.outcome.id \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " substitution.outcome.name substitution.replacement.id \\\n", "0 NaN NaN \n", "1 NaN NaN \n", "2 NaN NaN \n", "3 NaN NaN \n", "4 NaN NaN \n", "\n", " substitution.replacement.name 50_50.outcome.id 50_50.outcome.name \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " pass.goal_assist goalkeeper.technique.id goalkeeper.technique.name \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " pass.cut_back miscontrol.aerial_won pass.straight foul_committed.type.id \\\n", "0 NaN NaN NaN NaN \n", "1 NaN NaN NaN NaN \n", "2 NaN NaN NaN NaN \n", "3 NaN NaN NaN NaN \n", "4 NaN NaN NaN NaN \n", "\n", " foul_committed.type.name match_id pass.inswinging pass.deflected \\\n", "0 NaN 3788753 NaN NaN \n", "1 NaN 3788753 NaN NaN \n", "2 NaN 3788753 NaN NaN \n", "3 NaN 3788753 NaN NaN \n", "4 NaN 3788753 NaN NaN \n", "\n", " injury_stoppage.in_chain shot.one_on_one bad_behaviour.card.id \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " bad_behaviour.card.name shot.deflected block.deflection \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " foul_committed.penalty foul_won.penalty block.save_block \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " goalkeeper.punched_out player_off.permanent shot.saved_off_target \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " goalkeeper.shot_saved_off_target shot.saved_to_post \\\n", "0 NaN NaN \n", "1 NaN NaN \n", "2 NaN NaN \n", "3 NaN NaN \n", "4 NaN NaN \n", "\n", " goalkeeper.shot_saved_to_post shot.open_goal \\\n", "0 NaN NaN \n", "1 NaN NaN \n", "2 NaN NaN \n", "3 NaN NaN \n", "4 NaN NaN \n", "\n", " goalkeeper.penalty_saved_to_post dribble.no_touch block.offensive \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " shot.follows_dribble ball_recovery.offensive shot.redirect \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " goalkeeper.lost_in_play goalkeeper.success_in_play match_date \\\n", "0 NaN NaN 2021-06-16 \n", "1 NaN NaN 2021-06-16 \n", "2 NaN NaN 2021-06-16 \n", "3 NaN NaN 2021-06-16 \n", "4 NaN NaN 2021-06-16 \n", "\n", " kick_off home_score away_score match_status match_status_360 \\\n", "0 15:00:00.000 0 1 available available \n", "1 15:00:00.000 0 1 available available \n", "2 15:00:00.000 0 1 available available \n", "3 15:00:00.000 0 1 available available \n", "4 15:00:00.000 0 1 available available \n", "\n", " last_updated last_updated_360 match_week \\\n", "0 2021-11-11T14:00:16.105809 2021-09-22T16:39:05.697512 2 \n", "1 2021-11-11T14:00:16.105809 2021-09-22T16:39:05.697512 2 \n", "2 2021-11-11T14:00:16.105809 2021-09-22T16:39:05.697512 2 \n", "3 2021-11-11T14:00:16.105809 2021-09-22T16:39:05.697512 2 \n", "4 2021-11-11T14:00:16.105809 2021-09-22T16:39:05.697512 2 \n", "\n", " competition.competition_id competition.country_name \\\n", "0 55 Europe \n", "1 55 Europe \n", "2 55 Europe \n", "3 55 Europe \n", "4 55 Europe \n", "\n", " competition.competition_name season.season_id season.season_name \\\n", "0 UEFA Euro 43 2020 \n", "1 UEFA Euro 43 2020 \n", "2 UEFA Euro 43 2020 \n", "3 UEFA Euro 43 2020 \n", "4 UEFA Euro 43 2020 \n", "\n", " home_team.home_team_id home_team.home_team_name home_team.home_team_gender \\\n", "0 1835 Finland male \n", "1 1835 Finland male \n", "2 1835 Finland male \n", "3 1835 Finland male \n", "4 1835 Finland male \n", "\n", " home_team.home_team_group home_team.country.id home_team.country.name \\\n", "0 Group B 77 Finland \n", "1 Group B 77 Finland \n", "2 Group B 77 Finland \n", "3 Group B 77 Finland \n", "4 Group B 77 Finland \n", "\n", " home_team.managers away_team.away_team_id \\\n", "0 [{'id': 3622, 'name': 'Markku Kanerva', 'nickn... 796 \n", "1 [{'id': 3622, 'name': 'Markku Kanerva', 'nickn... 796 \n", "2 [{'id': 3622, 'name': 'Markku Kanerva', 'nickn... 796 \n", "3 [{'id': 3622, 'name': 'Markku Kanerva', 'nickn... 796 \n", "4 [{'id': 3622, 'name': 'Markku Kanerva', 'nickn... 796 \n", "\n", " away_team.away_team_name away_team.away_team_gender \\\n", "0 Russia male \n", "1 Russia male \n", "2 Russia male \n", "3 Russia male \n", "4 Russia male \n", "\n", " away_team.away_team_group away_team.country.id away_team.country.name \\\n", "0 Group B 188 Russia \n", "1 Group B 188 Russia \n", "2 Group B 188 Russia \n", "3 Group B 188 Russia \n", "4 Group B 188 Russia \n", "\n", " away_team.managers metadata.data_version \\\n", "0 [{'id': 365, 'name': 'Stanislav Cherchesov', '... 1.1.0 \n", "1 [{'id': 365, 'name': 'Stanislav Cherchesov', '... 1.1.0 \n", "2 [{'id': 365, 'name': 'Stanislav Cherchesov', '... 1.1.0 \n", "3 [{'id': 365, 'name': 'Stanislav Cherchesov', '... 1.1.0 \n", "4 [{'id': 365, 'name': 'Stanislav Cherchesov', '... 1.1.0 \n", "\n", " metadata.shot_fidelity_version metadata.xy_fidelity_version \\\n", "0 2 2 \n", "1 2 2 \n", "2 2 2 \n", "3 2 2 \n", "4 2 2 \n", "\n", " competition_stage.id competition_stage.name stadium.id \\\n", "0 10 Group Stage 4726 \n", "1 10 Group Stage 4726 \n", "2 10 Group Stage 4726 \n", "3 10 Group Stage 4726 \n", "4 10 Group Stage 4726 \n", "\n", " stadium.name stadium.country.id stadium.country.name \\\n", "0 Saint-Petersburg Stadium 188 Russia \n", "1 Saint-Petersburg Stadium 188 Russia \n", "2 Saint-Petersburg Stadium 188 Russia \n", "3 Saint-Petersburg Stadium 188 Russia \n", "4 Saint-Petersburg Stadium 188 Russia \n", "\n", " referee.id referee.name referee.country.id \\\n", "0 293 Danny Desmond Makkelie 160 \n", "1 293 Danny Desmond Makkelie 160 \n", "2 293 Danny Desmond Makkelie 160 \n", "3 293 Danny Desmond Makkelie 160 \n", "4 293 Danny Desmond Makkelie 160 \n", "\n", " referee.country.name competition_id season_id country_name \\\n", "0 Netherlands 55 43 Europe \n", "1 Netherlands 55 43 Europe \n", "2 Netherlands 55 43 Europe \n", "3 Netherlands 55 43 Europe \n", "4 Netherlands 55 43 Europe \n", "\n", " competition_name competition_gender competition_youth \\\n", "0 UEFA Euro male False \n", "1 UEFA Euro male False \n", "2 UEFA Euro male False \n", "3 UEFA Euro male False \n", "4 UEFA Euro male False \n", "\n", " competition_international season_name match_updated \\\n", "0 True 2020 2021-11-11T14:00:16.105809 \n", "1 True 2020 2021-11-11T14:00:16.105809 \n", "2 True 2020 2021-11-11T14:00:16.105809 \n", "3 True 2020 2021-11-11T14:00:16.105809 \n", "4 True 2020 2021-11-11T14:00:16.105809 \n", "\n", " match_updated_360 match_available_360 \\\n", "0 2021-11-11T13:54:37.507376 2021-11-11T13:54:37.507376 \n", "1 2021-11-11T13:54:37.507376 2021-11-11T13:54:37.507376 \n", "2 2021-11-11T13:54:37.507376 2021-11-11T13:54:37.507376 \n", "3 2021-11-11T13:54:37.507376 2021-11-11T13:54:37.507376 \n", "4 2021-11-11T13:54:37.507376 2021-11-11T13:54:37.507376 \n", "\n", " match_available \n", "0 2021-11-11T14:00:16.105809 \n", "1 2021-11-11T14:00:16.105809 \n", "2 2021-11-11T14:00:16.105809 \n", "3 2021-11-11T14:00:16.105809 \n", "4 2021-11-11T14:00:16.105809 " ] }, "execution_count": 31, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Read in exported CSV file if exists, if not, merge the individual DataFrames\n", "if not os.path.exists(os.path.join(data_dir_sb, 'raw', 'combined', 'combined_sb_360.csv')):\n", " \n", " # Join the Matches DataFrame to the Events DataFrame\n", " df_events_matches = pd.merge(df_events_flat, df_matches_flat, left_on=['match_id'], right_on=['match_id'])\n", "\n", " # Join the Competitions DataFrame to the Events-Matches DataFrame\n", " df_events_matches_competitions = pd.merge(df_events_matches, df_competitions_flat, left_on=['competition.competition_id', 'season.season_id'], right_on=['competition_id', 'season_id'])\n", " \n", "else: \n", " df_events_matches_competitions = pd.read_csv(os.path.join(data_dir_sb, 'raw', 'combined', 'combined_sb_360.csv'))\n", " \n", " \n", "# Display DataFrame\n", "df_events_matches_competitions.head()" ] }, { "cell_type": "code", "execution_count": 32, "metadata": { "scrolled": true }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "No. rows in Events DataFrame BEFORE join to Matches and Competitions DataFrames: 192686\n", "No. rows in DataFrame AFTER join: 192686\n", "\n", "----------\n", "\n", "Variance in rows before and after join: 0\n", "\n" ] } ], "source": [ "print('No. rows in Events DataFrame BEFORE join to Matches and Competitions DataFrames: {}'.format(len(df_events_flat)))\n", "print('No. rows in DataFrame AFTER join: {}\\n'.format(len(df_events_matches_competitions)))\n", "print('-'*10+'\\n')\n", "print('Variance in rows before and after join: {}\\n'.format(len(df_events_matches_competitions) - len(df_events_flat)))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "##### Export DataFrame" ] }, { "cell_type": "code", "execution_count": 33, "metadata": {}, "outputs": [], "source": [ "# Export DataFrame as a CSV file\n", "\n", "##\n", "if not os.path.exists(os.path.join(data_dir_sb, 'raw', 'combined', 'combined_sb_360.csv')):\n", " df_events_matches_competitions.to_csv(os.path.join(data_dir_sb, 'raw', 'combined', 'combined_sb_360.csv'), index=None, header=True)\n", "\n", "## \n", "else:\n", " pass" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 3.4. Initial Data Handling\n", "Let's quality of the dataset by looking first and last rows in pandas using the [head()](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.head.html) and [tail()](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.tail.html) methods." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### 3.4.1. Summary Report\n", "Initial step of the data handling and Exploratory Data Analysis (EDA) is to create a quick summary report of the dataset using [pandas Profiling Report](https://github.com/pandas-profiling/pandas-profiling)." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Summary of the data using pandas Profiling Report\n", "#pp.ProfileReport(df_events_matches_competitions)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### 3.4.2. Further Inspection\n", "The following commands go into more bespoke summary of the dataset. Some of the commands include content covered in the [pandas Profiling](https://github.com/pandas-profiling/pandas-profiling) summary above, but using the standard [pandas](https://pandas.pydata.org/) functions and methods that most peoplem will be more familiar with.\n", "\n", "First check the quality of the dataset by looking first and last rows in pandas using the [head()](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.head.html) and [tail()](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.tail.html) methods." ] }, { "cell_type": "code", "execution_count": 34, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
level_0idindexperiodtimestampminutesecondpossessiondurationtype.idtype.namepossession_team.idpossession_team.nameplay_pattern.idplay_pattern.nameteam.idteam.nametactics.formationtactics.lineuprelated_eventslocationplayer.idplayer.nameposition.idposition.namepass.recipient.idpass.recipient.namepass.lengthpass.anglepass.height.idpass.height.namepass.end_locationpass.body_part.idpass.body_part.namepass.type.idpass.type.namecarry.end_locationunder_pressureduel.type.idduel.type.namepass.aerial_woncounterpressduel.outcome.idduel.outcome.namedribble.outcome.iddribble.outcome.namepass.outcome.idpass.outcome.nameball_receipt.outcome.idball_receipt.outcome.nameinterception.outcome.idinterception.outcome.nameshot.statsbomb_xgshot.end_locationshot.outcome.idshot.outcome.nameshot.type.idshot.type.nameshot.body_part.idshot.body_part.nameshot.technique.idshot.technique.nameshot.freeze_framegoalkeeper.end_locationgoalkeeper.type.idgoalkeeper.type.namegoalkeeper.position.idgoalkeeper.position.nameoutpass.outswingingpass.technique.idpass.technique.nameclearance.headclearance.body_part.idclearance.body_part.namepass.switchoff_camerapass.crossclearance.left_footdribble.overrundribble.nutmegclearance.right_footpass.no_touchfoul_committed.advantagefoul_won.advantagepass.assisted_shot_idpass.shot_assistshot.key_pass_idshot.first_timeclearance.otherpass.miscommunicationclearance.aerial_wonpass.through_ballball_recovery.recovery_failuregoalkeeper.outcome.idgoalkeeper.outcome.namegoalkeeper.body_part.idgoalkeeper.body_part.nameshot.aerial_wonfoul_committed.card.idfoul_committed.card.namefoul_committed.offensivefoul_won.defensivesubstitution.outcome.idsubstitution.outcome.namesubstitution.replacement.idsubstitution.replacement.name50_50.outcome.id50_50.outcome.namepass.goal_assistgoalkeeper.technique.idgoalkeeper.technique.namepass.cut_backmiscontrol.aerial_wonpass.straightfoul_committed.type.idfoul_committed.type.namematch_idpass.inswingingpass.deflectedinjury_stoppage.in_chainshot.one_on_onebad_behaviour.card.idbad_behaviour.card.nameshot.deflectedblock.deflectionfoul_committed.penaltyfoul_won.penaltyblock.save_blockgoalkeeper.punched_outplayer_off.permanentshot.saved_off_targetgoalkeeper.shot_saved_off_targetshot.saved_to_postgoalkeeper.shot_saved_to_postshot.open_goalgoalkeeper.penalty_saved_to_postdribble.no_touchblock.offensiveshot.follows_dribbleball_recovery.offensiveshot.redirectgoalkeeper.lost_in_playgoalkeeper.success_in_playmatch_datekick_offhome_scoreaway_scorematch_statusmatch_status_360last_updatedlast_updated_360match_weekcompetition.competition_idcompetition.country_namecompetition.competition_nameseason.season_idseason.season_namehome_team.home_team_idhome_team.home_team_namehome_team.home_team_genderhome_team.home_team_grouphome_team.country.idhome_team.country.namehome_team.managersaway_team.away_team_idaway_team.away_team_nameaway_team.away_team_genderaway_team.away_team_groupaway_team.country.idaway_team.country.nameaway_team.managersmetadata.data_versionmetadata.shot_fidelity_versionmetadata.xy_fidelity_versioncompetition_stage.idcompetition_stage.namestadium.idstadium.namestadium.country.idstadium.country.namereferee.idreferee.namereferee.country.idreferee.country.namecompetition_idseason_idcountry_namecompetition_namecompetition_gendercompetition_youthcompetition_internationalseason_namematch_updatedmatch_updated_360match_available_360match_available
009427b18a-6b10-411f-90da-3d6240b80c711100:00:00.0000010.00000035Starting XI1835Finland1Regular Play1835Finland352.0[{'player': {'id': 8667, 'name': 'Lukáš Hrádec...NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN3788753NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN2021-06-1615:00:00.00001availableavailable2021-11-11T14:00:16.1058092021-09-22T16:39:05.697512255EuropeUEFA Euro4320201835FinlandmaleGroup B77Finland[{'id': 3622, 'name': 'Markku Kanerva', 'nickn...796RussiamaleGroup B188Russia[{'id': 365, 'name': 'Stanislav Cherchesov', '...1.1.02210Group Stage4726Saint-Petersburg Stadium188Russia293Danny Desmond Makkelie160Netherlands5543EuropeUEFA EuromaleFalseTrue20202021-11-11T14:00:16.1058092021-11-11T13:54:37.5073762021-11-11T13:54:37.5073762021-11-11T14:00:16.105809
11542c58bf-5c6c-43ca-9d8d-e086c7f08aaf2100:00:00.0000010.00000035Starting XI1835Finland1Regular Play796Russia3421.0[{'player': {'id': 21298, 'name': 'Matvey Safo...NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN3788753NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN2021-06-1615:00:00.00001availableavailable2021-11-11T14:00:16.1058092021-09-22T16:39:05.697512255EuropeUEFA Euro4320201835FinlandmaleGroup B77Finland[{'id': 3622, 'name': 'Markku Kanerva', 'nickn...796RussiamaleGroup B188Russia[{'id': 365, 'name': 'Stanislav Cherchesov', '...1.1.02210Group Stage4726Saint-Petersburg Stadium188Russia293Danny Desmond Makkelie160Netherlands5543EuropeUEFA EuromaleFalseTrue20202021-11-11T14:00:16.1058092021-11-11T13:54:37.5073762021-11-11T13:54:37.5073762021-11-11T14:00:16.105809
22a0dfe8a0-a0b9-443e-89e3-a8ba6596fa333100:00:00.0000010.00000018Half Start1835Finland1Regular Play1835FinlandNaNNaN[c7156352-f4b7-4140-aa51-6e26fd019a11]NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN3788753NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN2021-06-1615:00:00.00001availableavailable2021-11-11T14:00:16.1058092021-09-22T16:39:05.697512255EuropeUEFA Euro4320201835FinlandmaleGroup B77Finland[{'id': 3622, 'name': 'Markku Kanerva', 'nickn...796RussiamaleGroup B188Russia[{'id': 365, 'name': 'Stanislav Cherchesov', '...1.1.02210Group Stage4726Saint-Petersburg Stadium188Russia293Danny Desmond Makkelie160Netherlands5543EuropeUEFA EuromaleFalseTrue20202021-11-11T14:00:16.1058092021-11-11T13:54:37.5073762021-11-11T13:54:37.5073762021-11-11T14:00:16.105809
33c7156352-f4b7-4140-aa51-6e26fd019a114100:00:00.0000010.00000018Half Start1835Finland1Regular Play796RussiaNaNNaN[a0dfe8a0-a0b9-443e-89e3-a8ba6596fa33]NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN3788753NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN2021-06-1615:00:00.00001availableavailable2021-11-11T14:00:16.1058092021-09-22T16:39:05.697512255EuropeUEFA Euro4320201835FinlandmaleGroup B77Finland[{'id': 3622, 'name': 'Markku Kanerva', 'nickn...796RussiamaleGroup B188Russia[{'id': 365, 'name': 'Stanislav Cherchesov', '...1.1.02210Group Stage4726Saint-Petersburg Stadium188Russia293Danny Desmond Makkelie160Netherlands5543EuropeUEFA EuromaleFalseTrue20202021-11-11T14:00:16.1058092021-11-11T13:54:37.5073762021-11-11T13:54:37.5073762021-11-11T14:00:16.105809
4494dbc5c3-ef37-445e-9154-3d9f9ea9245d5100:00:00.4900021.37321530Pass796Russia9From Kick Off796RussiaNaNNaN[c0935bbe-3eb4-4a21-9eee-45f380d1f26d][60.0, 40.0]6299.0Aleksey Miranchuk18.0Right Attacking Midfield31917.0Igor Diveev22.3573253.0699671.0Ground Pass[37.7, 41.6]38.0Left Foot65.0Kick OffNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN3788753NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN2021-06-1615:00:00.00001availableavailable2021-11-11T14:00:16.1058092021-09-22T16:39:05.697512255EuropeUEFA Euro4320201835FinlandmaleGroup B77Finland[{'id': 3622, 'name': 'Markku Kanerva', 'nickn...796RussiamaleGroup B188Russia[{'id': 365, 'name': 'Stanislav Cherchesov', '...1.1.02210Group Stage4726Saint-Petersburg Stadium188Russia293Danny Desmond Makkelie160Netherlands5543EuropeUEFA EuromaleFalseTrue20202021-11-11T14:00:16.1058092021-11-11T13:54:37.5073762021-11-11T13:54:37.5073762021-11-11T14:00:16.105809
\n", "
" ], "text/plain": [ " level_0 id index period timestamp \\\n", "0 0 9427b18a-6b10-411f-90da-3d6240b80c71 1 1 00:00:00.000 \n", "1 1 542c58bf-5c6c-43ca-9d8d-e086c7f08aaf 2 1 00:00:00.000 \n", "2 2 a0dfe8a0-a0b9-443e-89e3-a8ba6596fa33 3 1 00:00:00.000 \n", "3 3 c7156352-f4b7-4140-aa51-6e26fd019a11 4 1 00:00:00.000 \n", "4 4 94dbc5c3-ef37-445e-9154-3d9f9ea9245d 5 1 00:00:00.490 \n", "\n", " minute second possession duration type.id type.name \\\n", "0 0 0 1 0.000000 35 Starting XI \n", "1 0 0 1 0.000000 35 Starting XI \n", "2 0 0 1 0.000000 18 Half Start \n", "3 0 0 1 0.000000 18 Half Start \n", "4 0 0 2 1.373215 30 Pass \n", "\n", " possession_team.id possession_team.name play_pattern.id play_pattern.name \\\n", "0 1835 Finland 1 Regular Play \n", "1 1835 Finland 1 Regular Play \n", "2 1835 Finland 1 Regular Play \n", "3 1835 Finland 1 Regular Play \n", "4 796 Russia 9 From Kick Off \n", "\n", " team.id team.name tactics.formation \\\n", "0 1835 Finland 352.0 \n", "1 796 Russia 3421.0 \n", "2 1835 Finland NaN \n", "3 796 Russia NaN \n", "4 796 Russia NaN \n", "\n", " tactics.lineup \\\n", "0 [{'player': {'id': 8667, 'name': 'Lukáš Hrádec... \n", "1 [{'player': {'id': 21298, 'name': 'Matvey Safo... \n", "2 NaN \n", "3 NaN \n", "4 NaN \n", "\n", " related_events location player.id \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 [c7156352-f4b7-4140-aa51-6e26fd019a11] NaN NaN \n", "3 [a0dfe8a0-a0b9-443e-89e3-a8ba6596fa33] NaN NaN \n", "4 [c0935bbe-3eb4-4a21-9eee-45f380d1f26d] [60.0, 40.0] 6299.0 \n", "\n", " player.name position.id position.name \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 Aleksey Miranchuk 18.0 Right Attacking Midfield \n", "\n", " pass.recipient.id pass.recipient.name pass.length pass.angle \\\n", "0 NaN NaN NaN NaN \n", "1 NaN NaN NaN NaN \n", "2 NaN NaN NaN NaN \n", "3 NaN NaN NaN NaN \n", "4 31917.0 Igor Diveev 22.357325 3.069967 \n", "\n", " pass.height.id pass.height.name pass.end_location pass.body_part.id \\\n", "0 NaN NaN NaN NaN \n", "1 NaN NaN NaN NaN \n", "2 NaN NaN NaN NaN \n", "3 NaN NaN NaN NaN \n", "4 1.0 Ground Pass [37.7, 41.6] 38.0 \n", "\n", " pass.body_part.name pass.type.id pass.type.name carry.end_location \\\n", "0 NaN NaN NaN NaN \n", "1 NaN NaN NaN NaN \n", "2 NaN NaN NaN NaN \n", "3 NaN NaN NaN NaN \n", "4 Left Foot 65.0 Kick Off NaN \n", "\n", " under_pressure duel.type.id duel.type.name pass.aerial_won counterpress \\\n", "0 NaN NaN NaN NaN NaN \n", "1 NaN NaN NaN NaN NaN \n", "2 NaN NaN NaN NaN NaN \n", "3 NaN NaN NaN NaN NaN \n", "4 NaN NaN NaN NaN NaN \n", "\n", " duel.outcome.id duel.outcome.name dribble.outcome.id dribble.outcome.name \\\n", "0 NaN NaN NaN NaN \n", "1 NaN NaN NaN NaN \n", "2 NaN NaN NaN NaN \n", "3 NaN NaN NaN NaN \n", "4 NaN NaN NaN NaN \n", "\n", " pass.outcome.id pass.outcome.name ball_receipt.outcome.id \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " ball_receipt.outcome.name interception.outcome.id \\\n", "0 NaN NaN \n", "1 NaN NaN \n", "2 NaN NaN \n", "3 NaN NaN \n", "4 NaN NaN \n", "\n", " interception.outcome.name shot.statsbomb_xg shot.end_location \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " shot.outcome.id shot.outcome.name shot.type.id shot.type.name \\\n", "0 NaN NaN NaN NaN \n", "1 NaN NaN NaN NaN \n", "2 NaN NaN NaN NaN \n", "3 NaN NaN NaN NaN \n", "4 NaN NaN NaN NaN \n", "\n", " shot.body_part.id shot.body_part.name shot.technique.id \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " shot.technique.name shot.freeze_frame goalkeeper.end_location \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " goalkeeper.type.id goalkeeper.type.name goalkeeper.position.id \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " goalkeeper.position.name out pass.outswinging pass.technique.id \\\n", "0 NaN NaN NaN NaN \n", "1 NaN NaN NaN NaN \n", "2 NaN NaN NaN NaN \n", "3 NaN NaN NaN NaN \n", "4 NaN NaN NaN NaN \n", "\n", " pass.technique.name clearance.head clearance.body_part.id \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " clearance.body_part.name pass.switch off_camera pass.cross \\\n", "0 NaN NaN NaN NaN \n", "1 NaN NaN NaN NaN \n", "2 NaN NaN NaN NaN \n", "3 NaN NaN NaN NaN \n", "4 NaN NaN NaN NaN \n", "\n", " clearance.left_foot dribble.overrun dribble.nutmeg clearance.right_foot \\\n", "0 NaN NaN NaN NaN \n", "1 NaN NaN NaN NaN \n", "2 NaN NaN NaN NaN \n", "3 NaN NaN NaN NaN \n", "4 NaN NaN NaN NaN \n", "\n", " pass.no_touch foul_committed.advantage foul_won.advantage \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " pass.assisted_shot_id pass.shot_assist shot.key_pass_id shot.first_time \\\n", "0 NaN NaN NaN NaN \n", "1 NaN NaN NaN NaN \n", "2 NaN NaN NaN NaN \n", "3 NaN NaN NaN NaN \n", "4 NaN NaN NaN NaN \n", "\n", " clearance.other pass.miscommunication clearance.aerial_won \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " pass.through_ball ball_recovery.recovery_failure goalkeeper.outcome.id \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " goalkeeper.outcome.name goalkeeper.body_part.id goalkeeper.body_part.name \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " shot.aerial_won foul_committed.card.id foul_committed.card.name \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " foul_committed.offensive foul_won.defensive substitution.outcome.id \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " substitution.outcome.name substitution.replacement.id \\\n", "0 NaN NaN \n", "1 NaN NaN \n", "2 NaN NaN \n", "3 NaN NaN \n", "4 NaN NaN \n", "\n", " substitution.replacement.name 50_50.outcome.id 50_50.outcome.name \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " pass.goal_assist goalkeeper.technique.id goalkeeper.technique.name \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " pass.cut_back miscontrol.aerial_won pass.straight foul_committed.type.id \\\n", "0 NaN NaN NaN NaN \n", "1 NaN NaN NaN NaN \n", "2 NaN NaN NaN NaN \n", "3 NaN NaN NaN NaN \n", "4 NaN NaN NaN NaN \n", "\n", " foul_committed.type.name match_id pass.inswinging pass.deflected \\\n", "0 NaN 3788753 NaN NaN \n", "1 NaN 3788753 NaN NaN \n", "2 NaN 3788753 NaN NaN \n", "3 NaN 3788753 NaN NaN \n", "4 NaN 3788753 NaN NaN \n", "\n", " injury_stoppage.in_chain shot.one_on_one bad_behaviour.card.id \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " bad_behaviour.card.name shot.deflected block.deflection \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " foul_committed.penalty foul_won.penalty block.save_block \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " goalkeeper.punched_out player_off.permanent shot.saved_off_target \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " goalkeeper.shot_saved_off_target shot.saved_to_post \\\n", "0 NaN NaN \n", "1 NaN NaN \n", "2 NaN NaN \n", "3 NaN NaN \n", "4 NaN NaN \n", "\n", " goalkeeper.shot_saved_to_post shot.open_goal \\\n", "0 NaN NaN \n", "1 NaN NaN \n", "2 NaN NaN \n", "3 NaN NaN \n", "4 NaN NaN \n", "\n", " goalkeeper.penalty_saved_to_post dribble.no_touch block.offensive \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " shot.follows_dribble ball_recovery.offensive shot.redirect \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " goalkeeper.lost_in_play goalkeeper.success_in_play match_date \\\n", "0 NaN NaN 2021-06-16 \n", "1 NaN NaN 2021-06-16 \n", "2 NaN NaN 2021-06-16 \n", "3 NaN NaN 2021-06-16 \n", "4 NaN NaN 2021-06-16 \n", "\n", " kick_off home_score away_score match_status match_status_360 \\\n", "0 15:00:00.000 0 1 available available \n", "1 15:00:00.000 0 1 available available \n", "2 15:00:00.000 0 1 available available \n", "3 15:00:00.000 0 1 available available \n", "4 15:00:00.000 0 1 available available \n", "\n", " last_updated last_updated_360 match_week \\\n", "0 2021-11-11T14:00:16.105809 2021-09-22T16:39:05.697512 2 \n", "1 2021-11-11T14:00:16.105809 2021-09-22T16:39:05.697512 2 \n", "2 2021-11-11T14:00:16.105809 2021-09-22T16:39:05.697512 2 \n", "3 2021-11-11T14:00:16.105809 2021-09-22T16:39:05.697512 2 \n", "4 2021-11-11T14:00:16.105809 2021-09-22T16:39:05.697512 2 \n", "\n", " competition.competition_id competition.country_name \\\n", "0 55 Europe \n", "1 55 Europe \n", "2 55 Europe \n", "3 55 Europe \n", "4 55 Europe \n", "\n", " competition.competition_name season.season_id season.season_name \\\n", "0 UEFA Euro 43 2020 \n", "1 UEFA Euro 43 2020 \n", "2 UEFA Euro 43 2020 \n", "3 UEFA Euro 43 2020 \n", "4 UEFA Euro 43 2020 \n", "\n", " home_team.home_team_id home_team.home_team_name home_team.home_team_gender \\\n", "0 1835 Finland male \n", "1 1835 Finland male \n", "2 1835 Finland male \n", "3 1835 Finland male \n", "4 1835 Finland male \n", "\n", " home_team.home_team_group home_team.country.id home_team.country.name \\\n", "0 Group B 77 Finland \n", "1 Group B 77 Finland \n", "2 Group B 77 Finland \n", "3 Group B 77 Finland \n", "4 Group B 77 Finland \n", "\n", " home_team.managers away_team.away_team_id \\\n", "0 [{'id': 3622, 'name': 'Markku Kanerva', 'nickn... 796 \n", "1 [{'id': 3622, 'name': 'Markku Kanerva', 'nickn... 796 \n", "2 [{'id': 3622, 'name': 'Markku Kanerva', 'nickn... 796 \n", "3 [{'id': 3622, 'name': 'Markku Kanerva', 'nickn... 796 \n", "4 [{'id': 3622, 'name': 'Markku Kanerva', 'nickn... 796 \n", "\n", " away_team.away_team_name away_team.away_team_gender \\\n", "0 Russia male \n", "1 Russia male \n", "2 Russia male \n", "3 Russia male \n", "4 Russia male \n", "\n", " away_team.away_team_group away_team.country.id away_team.country.name \\\n", "0 Group B 188 Russia \n", "1 Group B 188 Russia \n", "2 Group B 188 Russia \n", "3 Group B 188 Russia \n", "4 Group B 188 Russia \n", "\n", " away_team.managers metadata.data_version \\\n", "0 [{'id': 365, 'name': 'Stanislav Cherchesov', '... 1.1.0 \n", "1 [{'id': 365, 'name': 'Stanislav Cherchesov', '... 1.1.0 \n", "2 [{'id': 365, 'name': 'Stanislav Cherchesov', '... 1.1.0 \n", "3 [{'id': 365, 'name': 'Stanislav Cherchesov', '... 1.1.0 \n", "4 [{'id': 365, 'name': 'Stanislav Cherchesov', '... 1.1.0 \n", "\n", " metadata.shot_fidelity_version metadata.xy_fidelity_version \\\n", "0 2 2 \n", "1 2 2 \n", "2 2 2 \n", "3 2 2 \n", "4 2 2 \n", "\n", " competition_stage.id competition_stage.name stadium.id \\\n", "0 10 Group Stage 4726 \n", "1 10 Group Stage 4726 \n", "2 10 Group Stage 4726 \n", "3 10 Group Stage 4726 \n", "4 10 Group Stage 4726 \n", "\n", " stadium.name stadium.country.id stadium.country.name \\\n", "0 Saint-Petersburg Stadium 188 Russia \n", "1 Saint-Petersburg Stadium 188 Russia \n", "2 Saint-Petersburg Stadium 188 Russia \n", "3 Saint-Petersburg Stadium 188 Russia \n", "4 Saint-Petersburg Stadium 188 Russia \n", "\n", " referee.id referee.name referee.country.id \\\n", "0 293 Danny Desmond Makkelie 160 \n", "1 293 Danny Desmond Makkelie 160 \n", "2 293 Danny Desmond Makkelie 160 \n", "3 293 Danny Desmond Makkelie 160 \n", "4 293 Danny Desmond Makkelie 160 \n", "\n", " referee.country.name competition_id season_id country_name \\\n", "0 Netherlands 55 43 Europe \n", "1 Netherlands 55 43 Europe \n", "2 Netherlands 55 43 Europe \n", "3 Netherlands 55 43 Europe \n", "4 Netherlands 55 43 Europe \n", "\n", " competition_name competition_gender competition_youth \\\n", "0 UEFA Euro male False \n", "1 UEFA Euro male False \n", "2 UEFA Euro male False \n", "3 UEFA Euro male False \n", "4 UEFA Euro male False \n", "\n", " competition_international season_name match_updated \\\n", "0 True 2020 2021-11-11T14:00:16.105809 \n", "1 True 2020 2021-11-11T14:00:16.105809 \n", "2 True 2020 2021-11-11T14:00:16.105809 \n", "3 True 2020 2021-11-11T14:00:16.105809 \n", "4 True 2020 2021-11-11T14:00:16.105809 \n", "\n", " match_updated_360 match_available_360 \\\n", "0 2021-11-11T13:54:37.507376 2021-11-11T13:54:37.507376 \n", "1 2021-11-11T13:54:37.507376 2021-11-11T13:54:37.507376 \n", "2 2021-11-11T13:54:37.507376 2021-11-11T13:54:37.507376 \n", "3 2021-11-11T13:54:37.507376 2021-11-11T13:54:37.507376 \n", "4 2021-11-11T13:54:37.507376 2021-11-11T13:54:37.507376 \n", "\n", " match_available \n", "0 2021-11-11T14:00:16.105809 \n", "1 2021-11-11T14:00:16.105809 \n", "2 2021-11-11T14:00:16.105809 \n", "3 2021-11-11T14:00:16.105809 \n", "4 2021-11-11T14:00:16.105809 " ] }, "execution_count": 34, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Display the first five rows of the DataFrame, df_events_matches_competitions\n", "df_events_matches_competitions.head()" ] }, { "cell_type": "code", "execution_count": 35, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
level_0idindexperiodtimestampminutesecondpossessiondurationtype.idtype.namepossession_team.idpossession_team.nameplay_pattern.idplay_pattern.nameteam.idteam.nametactics.formationtactics.lineuprelated_eventslocationplayer.idplayer.nameposition.idposition.namepass.recipient.idpass.recipient.namepass.lengthpass.anglepass.height.idpass.height.namepass.end_locationpass.body_part.idpass.body_part.namepass.type.idpass.type.namecarry.end_locationunder_pressureduel.type.idduel.type.namepass.aerial_woncounterpressduel.outcome.idduel.outcome.namedribble.outcome.iddribble.outcome.namepass.outcome.idpass.outcome.nameball_receipt.outcome.idball_receipt.outcome.nameinterception.outcome.idinterception.outcome.nameshot.statsbomb_xgshot.end_locationshot.outcome.idshot.outcome.nameshot.type.idshot.type.nameshot.body_part.idshot.body_part.nameshot.technique.idshot.technique.nameshot.freeze_framegoalkeeper.end_locationgoalkeeper.type.idgoalkeeper.type.namegoalkeeper.position.idgoalkeeper.position.nameoutpass.outswingingpass.technique.idpass.technique.nameclearance.headclearance.body_part.idclearance.body_part.namepass.switchoff_camerapass.crossclearance.left_footdribble.overrundribble.nutmegclearance.right_footpass.no_touchfoul_committed.advantagefoul_won.advantagepass.assisted_shot_idpass.shot_assistshot.key_pass_idshot.first_timeclearance.otherpass.miscommunicationclearance.aerial_wonpass.through_ballball_recovery.recovery_failuregoalkeeper.outcome.idgoalkeeper.outcome.namegoalkeeper.body_part.idgoalkeeper.body_part.nameshot.aerial_wonfoul_committed.card.idfoul_committed.card.namefoul_committed.offensivefoul_won.defensivesubstitution.outcome.idsubstitution.outcome.namesubstitution.replacement.idsubstitution.replacement.name50_50.outcome.id50_50.outcome.namepass.goal_assistgoalkeeper.technique.idgoalkeeper.technique.namepass.cut_backmiscontrol.aerial_wonpass.straightfoul_committed.type.idfoul_committed.type.namematch_idpass.inswingingpass.deflectedinjury_stoppage.in_chainshot.one_on_onebad_behaviour.card.idbad_behaviour.card.nameshot.deflectedblock.deflectionfoul_committed.penaltyfoul_won.penaltyblock.save_blockgoalkeeper.punched_outplayer_off.permanentshot.saved_off_targetgoalkeeper.shot_saved_off_targetshot.saved_to_postgoalkeeper.shot_saved_to_postshot.open_goalgoalkeeper.penalty_saved_to_postdribble.no_touchblock.offensiveshot.follows_dribbleball_recovery.offensiveshot.redirectgoalkeeper.lost_in_playgoalkeeper.success_in_playmatch_datekick_offhome_scoreaway_scorematch_statusmatch_status_360last_updatedlast_updated_360match_weekcompetition.competition_idcompetition.country_namecompetition.competition_nameseason.season_idseason.season_namehome_team.home_team_idhome_team.home_team_namehome_team.home_team_genderhome_team.home_team_grouphome_team.country.idhome_team.country.namehome_team.managersaway_team.away_team_idaway_team.away_team_nameaway_team.away_team_genderaway_team.away_team_groupaway_team.country.idaway_team.country.nameaway_team.managersmetadata.data_versionmetadata.shot_fidelity_versionmetadata.xy_fidelity_versioncompetition_stage.idcompetition_stage.namestadium.idstadium.namestadium.country.idstadium.country.namereferee.idreferee.namereferee.country.idreferee.country.namecompetition_idseason_idcountry_namecompetition_namecompetition_gendercompetition_youthcompetition_internationalseason_namematch_updatedmatch_updated_360match_available_360match_available
19268129928aa68a18-79d2-4b8b-ba5f-9d3124f1cd202993200:50:23.15095231510.8709330Pass907Wales4From Throw In907WalesNaNNaN[a0bdd045-65d0-4601-9378-a5dacdba3257, dcbfc5e...[110.7, 0.1]3086.0Ben Davies6.0Left Back6399.0Gareth Frank Bale6.1032781.2542272.0Low Pass[112.6, 5.9]NaNNaN67.0Throw-inNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN9.0IncompleteNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN3788744NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN2021-06-1215:00:00.00011availableavailable2021-06-20T12:57:59.2582021-09-22T16:38:18.433799155EuropeUEFA Euro432020907WalesmaleGroup A249WalesNaN773SwitzerlandmaleGroup A221SwitzerlandNaN1.1.02210Group Stage4549Bakı Olimpiya Stadionu16Azerbaijan76Clément Turpin78France5543EuropeUEFA EuromaleFalseTrue20202021-11-11T14:00:16.1058092021-11-11T13:54:37.5073762021-11-11T13:54:37.5073762021-11-11T14:00:16.105809
1926822993a0bdd045-65d0-4601-9378-a5dacdba32572994200:50:24.0219524151NaN42Ball Receipt*907Wales4From Throw In907WalesNaNNaN[8aa68a18-79d2-4b8b-ba5f-9d3124f1cd20][113.7, 8.0]6399.0Gareth Frank Bale16.0Left MidfieldNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN9.0IncompleteNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN3788744NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN2021-06-1215:00:00.00011availableavailable2021-06-20T12:57:59.2582021-09-22T16:38:18.433799155EuropeUEFA Euro432020907WalesmaleGroup A249WalesNaN773SwitzerlandmaleGroup A221SwitzerlandNaN1.1.02210Group Stage4549Bakı Olimpiya Stadionu16Azerbaijan76Clément Turpin78France5543EuropeUEFA EuromaleFalseTrue20202021-11-11T14:00:16.1058092021-11-11T13:54:37.5073762021-11-11T13:54:37.5073762021-11-11T14:00:16.105809
1926832994dcbfc5e1-b22f-460f-8132-cf6fe7a590f82995200:50:24.02195241510.0000010Interception907Wales4From Throw In773SwitzerlandNaNNaN[8aa68a18-79d2-4b8b-ba5f-9d3124f1cd20][7.5, 74.2]8814.0Nico Elvedi3.0Right Center BackNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN4.0WonNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN3788744NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN2021-06-1215:00:00.00011availableavailable2021-06-20T12:57:59.2582021-09-22T16:38:18.433799155EuropeUEFA Euro432020907WalesmaleGroup A249WalesNaN773SwitzerlandmaleGroup A221SwitzerlandNaN1.1.02210Group Stage4549Bakı Olimpiya Stadionu16Azerbaijan76Clément Turpin78France5543EuropeUEFA EuromaleFalseTrue20202021-11-11T14:00:16.1058092021-11-11T13:54:37.5073762021-11-11T13:54:37.5073762021-11-11T14:00:16.105809
192684299597cb188e-82e3-49cb-9ccf-9a5346f042b12996200:50:25.40495251510.0000034Half End907Wales4From Throw In773SwitzerlandNaNNaN[edfa93d5-a744-41a4-a10d-f1d09017011d]NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN3788744NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN2021-06-1215:00:00.00011availableavailable2021-06-20T12:57:59.2582021-09-22T16:38:18.433799155EuropeUEFA Euro432020907WalesmaleGroup A249WalesNaN773SwitzerlandmaleGroup A221SwitzerlandNaN1.1.02210Group Stage4549Bakı Olimpiya Stadionu16Azerbaijan76Clément Turpin78France5543EuropeUEFA EuromaleFalseTrue20202021-11-11T14:00:16.1058092021-11-11T13:54:37.5073762021-11-11T13:54:37.5073762021-11-11T14:00:16.105809
1926852996edfa93d5-a744-41a4-a10d-f1d09017011d2997200:50:25.40495251510.0000034Half End907Wales4From Throw In907WalesNaNNaN[97cb188e-82e3-49cb-9ccf-9a5346f042b1]NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN3788744NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN2021-06-1215:00:00.00011availableavailable2021-06-20T12:57:59.2582021-09-22T16:38:18.433799155EuropeUEFA Euro432020907WalesmaleGroup A249WalesNaN773SwitzerlandmaleGroup A221SwitzerlandNaN1.1.02210Group Stage4549Bakı Olimpiya Stadionu16Azerbaijan76Clément Turpin78France5543EuropeUEFA EuromaleFalseTrue20202021-11-11T14:00:16.1058092021-11-11T13:54:37.5073762021-11-11T13:54:37.5073762021-11-11T14:00:16.105809
\n", "
" ], "text/plain": [ " level_0 id index period \\\n", "192681 2992 8aa68a18-79d2-4b8b-ba5f-9d3124f1cd20 2993 2 \n", "192682 2993 a0bdd045-65d0-4601-9378-a5dacdba3257 2994 2 \n", "192683 2994 dcbfc5e1-b22f-460f-8132-cf6fe7a590f8 2995 2 \n", "192684 2995 97cb188e-82e3-49cb-9ccf-9a5346f042b1 2996 2 \n", "192685 2996 edfa93d5-a744-41a4-a10d-f1d09017011d 2997 2 \n", "\n", " timestamp minute second possession duration type.id \\\n", "192681 00:50:23.150 95 23 151 0.87093 30 \n", "192682 00:50:24.021 95 24 151 NaN 42 \n", "192683 00:50:24.021 95 24 151 0.00000 10 \n", "192684 00:50:25.404 95 25 151 0.00000 34 \n", "192685 00:50:25.404 95 25 151 0.00000 34 \n", "\n", " type.name possession_team.id possession_team.name \\\n", "192681 Pass 907 Wales \n", "192682 Ball Receipt* 907 Wales \n", "192683 Interception 907 Wales \n", "192684 Half End 907 Wales \n", "192685 Half End 907 Wales \n", "\n", " play_pattern.id play_pattern.name team.id team.name \\\n", "192681 4 From Throw In 907 Wales \n", "192682 4 From Throw In 907 Wales \n", "192683 4 From Throw In 773 Switzerland \n", "192684 4 From Throw In 773 Switzerland \n", "192685 4 From Throw In 907 Wales \n", "\n", " tactics.formation tactics.lineup \\\n", "192681 NaN NaN \n", "192682 NaN NaN \n", "192683 NaN NaN \n", "192684 NaN NaN \n", "192685 NaN NaN \n", "\n", " related_events location \\\n", "192681 [a0bdd045-65d0-4601-9378-a5dacdba3257, dcbfc5e... [110.7, 0.1] \n", "192682 [8aa68a18-79d2-4b8b-ba5f-9d3124f1cd20] [113.7, 8.0] \n", "192683 [8aa68a18-79d2-4b8b-ba5f-9d3124f1cd20] [7.5, 74.2] \n", "192684 [edfa93d5-a744-41a4-a10d-f1d09017011d] NaN \n", "192685 [97cb188e-82e3-49cb-9ccf-9a5346f042b1] NaN \n", "\n", " player.id player.name position.id position.name \\\n", "192681 3086.0 Ben Davies 6.0 Left Back \n", "192682 6399.0 Gareth Frank Bale 16.0 Left Midfield \n", "192683 8814.0 Nico Elvedi 3.0 Right Center Back \n", "192684 NaN NaN NaN NaN \n", "192685 NaN NaN NaN NaN \n", "\n", " pass.recipient.id pass.recipient.name pass.length pass.angle \\\n", "192681 6399.0 Gareth Frank Bale 6.103278 1.254227 \n", "192682 NaN NaN NaN NaN \n", "192683 NaN NaN NaN NaN \n", "192684 NaN NaN NaN NaN \n", "192685 NaN NaN NaN NaN \n", "\n", " pass.height.id pass.height.name pass.end_location pass.body_part.id \\\n", "192681 2.0 Low Pass [112.6, 5.9] NaN \n", "192682 NaN NaN NaN NaN \n", "192683 NaN NaN NaN NaN \n", "192684 NaN NaN NaN NaN \n", "192685 NaN NaN NaN NaN \n", "\n", " pass.body_part.name pass.type.id pass.type.name carry.end_location \\\n", "192681 NaN 67.0 Throw-in NaN \n", "192682 NaN NaN NaN NaN \n", "192683 NaN NaN NaN NaN \n", "192684 NaN NaN NaN NaN \n", "192685 NaN NaN NaN NaN \n", "\n", " under_pressure duel.type.id duel.type.name pass.aerial_won \\\n", "192681 NaN NaN NaN NaN \n", "192682 NaN NaN NaN NaN \n", "192683 NaN NaN NaN NaN \n", "192684 NaN NaN NaN NaN \n", "192685 NaN NaN NaN NaN \n", "\n", " counterpress duel.outcome.id duel.outcome.name dribble.outcome.id \\\n", "192681 NaN NaN NaN NaN \n", "192682 NaN NaN NaN NaN \n", "192683 NaN NaN NaN NaN \n", "192684 NaN NaN NaN NaN \n", "192685 NaN NaN NaN NaN \n", "\n", " dribble.outcome.name pass.outcome.id pass.outcome.name \\\n", "192681 NaN 9.0 Incomplete \n", "192682 NaN NaN NaN \n", "192683 NaN NaN NaN \n", "192684 NaN NaN NaN \n", "192685 NaN NaN NaN \n", "\n", " ball_receipt.outcome.id ball_receipt.outcome.name \\\n", "192681 NaN NaN \n", "192682 9.0 Incomplete \n", "192683 NaN NaN \n", "192684 NaN NaN \n", "192685 NaN NaN \n", "\n", " interception.outcome.id interception.outcome.name shot.statsbomb_xg \\\n", "192681 NaN NaN NaN \n", "192682 NaN NaN NaN \n", "192683 4.0 Won NaN \n", "192684 NaN NaN NaN \n", "192685 NaN NaN NaN \n", "\n", " shot.end_location shot.outcome.id shot.outcome.name shot.type.id \\\n", "192681 NaN NaN NaN NaN \n", "192682 NaN NaN NaN NaN \n", "192683 NaN NaN NaN NaN \n", "192684 NaN NaN NaN NaN \n", "192685 NaN NaN NaN NaN \n", "\n", " shot.type.name shot.body_part.id shot.body_part.name \\\n", "192681 NaN NaN NaN \n", "192682 NaN NaN NaN \n", "192683 NaN NaN NaN \n", "192684 NaN NaN NaN \n", "192685 NaN NaN NaN \n", "\n", " shot.technique.id shot.technique.name shot.freeze_frame \\\n", "192681 NaN NaN NaN \n", "192682 NaN NaN NaN \n", "192683 NaN NaN NaN \n", "192684 NaN NaN NaN \n", "192685 NaN NaN NaN \n", "\n", " goalkeeper.end_location goalkeeper.type.id goalkeeper.type.name \\\n", "192681 NaN NaN NaN \n", "192682 NaN NaN NaN \n", "192683 NaN NaN NaN \n", "192684 NaN NaN NaN \n", "192685 NaN NaN NaN \n", "\n", " goalkeeper.position.id goalkeeper.position.name out pass.outswinging \\\n", "192681 NaN NaN NaN NaN \n", "192682 NaN NaN NaN NaN \n", "192683 NaN NaN NaN NaN \n", "192684 NaN NaN NaN NaN \n", "192685 NaN NaN NaN NaN \n", "\n", " pass.technique.id pass.technique.name clearance.head \\\n", "192681 NaN NaN NaN \n", "192682 NaN NaN NaN \n", "192683 NaN NaN NaN \n", "192684 NaN NaN NaN \n", "192685 NaN NaN NaN \n", "\n", " clearance.body_part.id clearance.body_part.name pass.switch \\\n", "192681 NaN NaN NaN \n", "192682 NaN NaN NaN \n", "192683 NaN NaN NaN \n", "192684 NaN NaN NaN \n", "192685 NaN NaN NaN \n", "\n", " off_camera pass.cross clearance.left_foot dribble.overrun \\\n", "192681 NaN NaN NaN NaN \n", "192682 NaN NaN NaN NaN \n", "192683 NaN NaN NaN NaN \n", "192684 NaN NaN NaN NaN \n", "192685 NaN NaN NaN NaN \n", "\n", " dribble.nutmeg clearance.right_foot pass.no_touch \\\n", "192681 NaN NaN NaN \n", "192682 NaN NaN NaN \n", "192683 NaN NaN NaN \n", "192684 NaN NaN NaN \n", "192685 NaN NaN NaN \n", "\n", " foul_committed.advantage foul_won.advantage pass.assisted_shot_id \\\n", "192681 NaN NaN NaN \n", "192682 NaN NaN NaN \n", "192683 NaN NaN NaN \n", "192684 NaN NaN NaN \n", "192685 NaN NaN NaN \n", "\n", " pass.shot_assist shot.key_pass_id shot.first_time clearance.other \\\n", "192681 NaN NaN NaN NaN \n", "192682 NaN NaN NaN NaN \n", "192683 NaN NaN NaN NaN \n", "192684 NaN NaN NaN NaN \n", "192685 NaN NaN NaN NaN \n", "\n", " pass.miscommunication clearance.aerial_won pass.through_ball \\\n", "192681 NaN NaN NaN \n", "192682 NaN NaN NaN \n", "192683 NaN NaN NaN \n", "192684 NaN NaN NaN \n", "192685 NaN NaN NaN \n", "\n", " ball_recovery.recovery_failure goalkeeper.outcome.id \\\n", "192681 NaN NaN \n", "192682 NaN NaN \n", "192683 NaN NaN \n", "192684 NaN NaN \n", "192685 NaN NaN \n", "\n", " goalkeeper.outcome.name goalkeeper.body_part.id \\\n", "192681 NaN NaN \n", "192682 NaN NaN \n", "192683 NaN NaN \n", "192684 NaN NaN \n", "192685 NaN NaN \n", "\n", " goalkeeper.body_part.name shot.aerial_won foul_committed.card.id \\\n", "192681 NaN NaN NaN \n", "192682 NaN NaN NaN \n", "192683 NaN NaN NaN \n", "192684 NaN NaN NaN \n", "192685 NaN NaN NaN \n", "\n", " foul_committed.card.name foul_committed.offensive foul_won.defensive \\\n", "192681 NaN NaN NaN \n", "192682 NaN NaN NaN \n", "192683 NaN NaN NaN \n", "192684 NaN NaN NaN \n", "192685 NaN NaN NaN \n", "\n", " substitution.outcome.id substitution.outcome.name \\\n", "192681 NaN NaN \n", "192682 NaN NaN \n", "192683 NaN NaN \n", "192684 NaN NaN \n", "192685 NaN NaN \n", "\n", " substitution.replacement.id substitution.replacement.name \\\n", "192681 NaN NaN \n", "192682 NaN NaN \n", "192683 NaN NaN \n", "192684 NaN NaN \n", "192685 NaN NaN \n", "\n", " 50_50.outcome.id 50_50.outcome.name pass.goal_assist \\\n", "192681 NaN NaN NaN \n", "192682 NaN NaN NaN \n", "192683 NaN NaN NaN \n", "192684 NaN NaN NaN \n", "192685 NaN NaN NaN \n", "\n", " goalkeeper.technique.id goalkeeper.technique.name pass.cut_back \\\n", "192681 NaN NaN NaN \n", "192682 NaN NaN NaN \n", "192683 NaN NaN NaN \n", "192684 NaN NaN NaN \n", "192685 NaN NaN NaN \n", "\n", " miscontrol.aerial_won pass.straight foul_committed.type.id \\\n", "192681 NaN NaN NaN \n", "192682 NaN NaN NaN \n", "192683 NaN NaN NaN \n", "192684 NaN NaN NaN \n", "192685 NaN NaN NaN \n", "\n", " foul_committed.type.name match_id pass.inswinging pass.deflected \\\n", "192681 NaN 3788744 NaN NaN \n", "192682 NaN 3788744 NaN NaN \n", "192683 NaN 3788744 NaN NaN \n", "192684 NaN 3788744 NaN NaN \n", "192685 NaN 3788744 NaN NaN \n", "\n", " injury_stoppage.in_chain shot.one_on_one bad_behaviour.card.id \\\n", "192681 NaN NaN NaN \n", "192682 NaN NaN NaN \n", "192683 NaN NaN NaN \n", "192684 NaN NaN NaN \n", "192685 NaN NaN NaN \n", "\n", " bad_behaviour.card.name shot.deflected block.deflection \\\n", "192681 NaN NaN NaN \n", "192682 NaN NaN NaN \n", "192683 NaN NaN NaN \n", "192684 NaN NaN NaN \n", "192685 NaN NaN NaN \n", "\n", " foul_committed.penalty foul_won.penalty block.save_block \\\n", "192681 NaN NaN NaN \n", "192682 NaN NaN NaN \n", "192683 NaN NaN NaN \n", "192684 NaN NaN NaN \n", "192685 NaN NaN NaN \n", "\n", " goalkeeper.punched_out player_off.permanent shot.saved_off_target \\\n", "192681 NaN NaN NaN \n", "192682 NaN NaN NaN \n", "192683 NaN NaN NaN \n", "192684 NaN NaN NaN \n", "192685 NaN NaN NaN \n", "\n", " goalkeeper.shot_saved_off_target shot.saved_to_post \\\n", "192681 NaN NaN \n", "192682 NaN NaN \n", "192683 NaN NaN \n", "192684 NaN NaN \n", "192685 NaN NaN \n", "\n", " goalkeeper.shot_saved_to_post shot.open_goal \\\n", "192681 NaN NaN \n", "192682 NaN NaN \n", "192683 NaN NaN \n", "192684 NaN NaN \n", "192685 NaN NaN \n", "\n", " goalkeeper.penalty_saved_to_post dribble.no_touch block.offensive \\\n", "192681 NaN NaN NaN \n", "192682 NaN NaN NaN \n", "192683 NaN NaN NaN \n", "192684 NaN NaN NaN \n", "192685 NaN NaN NaN \n", "\n", " shot.follows_dribble ball_recovery.offensive shot.redirect \\\n", "192681 NaN NaN NaN \n", "192682 NaN NaN NaN \n", "192683 NaN NaN NaN \n", "192684 NaN NaN NaN \n", "192685 NaN NaN NaN \n", "\n", " goalkeeper.lost_in_play goalkeeper.success_in_play match_date \\\n", "192681 NaN NaN 2021-06-12 \n", "192682 NaN NaN 2021-06-12 \n", "192683 NaN NaN 2021-06-12 \n", "192684 NaN NaN 2021-06-12 \n", "192685 NaN NaN 2021-06-12 \n", "\n", " kick_off home_score away_score match_status match_status_360 \\\n", "192681 15:00:00.000 1 1 available available \n", "192682 15:00:00.000 1 1 available available \n", "192683 15:00:00.000 1 1 available available \n", "192684 15:00:00.000 1 1 available available \n", "192685 15:00:00.000 1 1 available available \n", "\n", " last_updated last_updated_360 match_week \\\n", "192681 2021-06-20T12:57:59.258 2021-09-22T16:38:18.433799 1 \n", "192682 2021-06-20T12:57:59.258 2021-09-22T16:38:18.433799 1 \n", "192683 2021-06-20T12:57:59.258 2021-09-22T16:38:18.433799 1 \n", "192684 2021-06-20T12:57:59.258 2021-09-22T16:38:18.433799 1 \n", "192685 2021-06-20T12:57:59.258 2021-09-22T16:38:18.433799 1 \n", "\n", " competition.competition_id competition.country_name \\\n", "192681 55 Europe \n", "192682 55 Europe \n", "192683 55 Europe \n", "192684 55 Europe \n", "192685 55 Europe \n", "\n", " competition.competition_name season.season_id season.season_name \\\n", "192681 UEFA Euro 43 2020 \n", "192682 UEFA Euro 43 2020 \n", "192683 UEFA Euro 43 2020 \n", "192684 UEFA Euro 43 2020 \n", "192685 UEFA Euro 43 2020 \n", "\n", " home_team.home_team_id home_team.home_team_name \\\n", "192681 907 Wales \n", "192682 907 Wales \n", "192683 907 Wales \n", "192684 907 Wales \n", "192685 907 Wales \n", "\n", " home_team.home_team_gender home_team.home_team_group \\\n", "192681 male Group A \n", "192682 male Group A \n", "192683 male Group A \n", "192684 male Group A \n", "192685 male Group A \n", "\n", " home_team.country.id home_team.country.name home_team.managers \\\n", "192681 249 Wales NaN \n", "192682 249 Wales NaN \n", "192683 249 Wales NaN \n", "192684 249 Wales NaN \n", "192685 249 Wales NaN \n", "\n", " away_team.away_team_id away_team.away_team_name \\\n", "192681 773 Switzerland \n", "192682 773 Switzerland \n", "192683 773 Switzerland \n", "192684 773 Switzerland \n", "192685 773 Switzerland \n", "\n", " away_team.away_team_gender away_team.away_team_group \\\n", "192681 male Group A \n", "192682 male Group A \n", "192683 male Group A \n", "192684 male Group A \n", "192685 male Group A \n", "\n", " away_team.country.id away_team.country.name away_team.managers \\\n", "192681 221 Switzerland NaN \n", "192682 221 Switzerland NaN \n", "192683 221 Switzerland NaN \n", "192684 221 Switzerland NaN \n", "192685 221 Switzerland NaN \n", "\n", " metadata.data_version metadata.shot_fidelity_version \\\n", "192681 1.1.0 2 \n", "192682 1.1.0 2 \n", "192683 1.1.0 2 \n", "192684 1.1.0 2 \n", "192685 1.1.0 2 \n", "\n", " metadata.xy_fidelity_version competition_stage.id \\\n", "192681 2 10 \n", "192682 2 10 \n", "192683 2 10 \n", "192684 2 10 \n", "192685 2 10 \n", "\n", " competition_stage.name stadium.id stadium.name \\\n", "192681 Group Stage 4549 Bakı Olimpiya Stadionu \n", "192682 Group Stage 4549 Bakı Olimpiya Stadionu \n", "192683 Group Stage 4549 Bakı Olimpiya Stadionu \n", "192684 Group Stage 4549 Bakı Olimpiya Stadionu \n", "192685 Group Stage 4549 Bakı Olimpiya Stadionu \n", "\n", " stadium.country.id stadium.country.name referee.id referee.name \\\n", "192681 16 Azerbaijan 76 Clément Turpin \n", "192682 16 Azerbaijan 76 Clément Turpin \n", "192683 16 Azerbaijan 76 Clément Turpin \n", "192684 16 Azerbaijan 76 Clément Turpin \n", "192685 16 Azerbaijan 76 Clément Turpin \n", "\n", " referee.country.id referee.country.name competition_id season_id \\\n", "192681 78 France 55 43 \n", "192682 78 France 55 43 \n", "192683 78 France 55 43 \n", "192684 78 France 55 43 \n", "192685 78 France 55 43 \n", "\n", " country_name competition_name competition_gender competition_youth \\\n", "192681 Europe UEFA Euro male False \n", "192682 Europe UEFA Euro male False \n", "192683 Europe UEFA Euro male False \n", "192684 Europe UEFA Euro male False \n", "192685 Europe UEFA Euro male False \n", "\n", " competition_international season_name match_updated \\\n", "192681 True 2020 2021-11-11T14:00:16.105809 \n", "192682 True 2020 2021-11-11T14:00:16.105809 \n", "192683 True 2020 2021-11-11T14:00:16.105809 \n", "192684 True 2020 2021-11-11T14:00:16.105809 \n", "192685 True 2020 2021-11-11T14:00:16.105809 \n", "\n", " match_updated_360 match_available_360 \\\n", "192681 2021-11-11T13:54:37.507376 2021-11-11T13:54:37.507376 \n", "192682 2021-11-11T13:54:37.507376 2021-11-11T13:54:37.507376 \n", "192683 2021-11-11T13:54:37.507376 2021-11-11T13:54:37.507376 \n", "192684 2021-11-11T13:54:37.507376 2021-11-11T13:54:37.507376 \n", "192685 2021-11-11T13:54:37.507376 2021-11-11T13:54:37.507376 \n", "\n", " match_available \n", "192681 2021-11-11T14:00:16.105809 \n", "192682 2021-11-11T14:00:16.105809 \n", "192683 2021-11-11T14:00:16.105809 \n", "192684 2021-11-11T14:00:16.105809 \n", "192685 2021-11-11T14:00:16.105809 " ] }, "execution_count": 35, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Display the last five rows of the DataFrame, df_events_matches_competitions\n", "df_events_matches_competitions.tail()" ] }, { "cell_type": "code", "execution_count": 36, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "(192686, 197)\n" ] } ], "source": [ "# Print the shape of the DataFrame, df_events_matches_competitions\n", "print(df_events_matches_competitions.shape)" ] }, { "cell_type": "code", "execution_count": 37, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Index(['level_0', 'id', 'index', 'period', 'timestamp', 'minute', 'second',\n", " 'possession', 'duration', 'type.id',\n", " ...\n", " 'country_name', 'competition_name', 'competition_gender',\n", " 'competition_youth', 'competition_international', 'season_name',\n", " 'match_updated', 'match_updated_360', 'match_available_360',\n", " 'match_available'],\n", " dtype='object', length=197)\n" ] } ], "source": [ "# Print the column names of the DataFrame, df_events_matches_competitions\n", "print(df_events_matches_competitions.columns)" ] }, { "cell_type": "code", "execution_count": 38, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "level_0 int64\n", "id object\n", "index int64\n", "period int64\n", "timestamp object\n", " ... \n", "season_name object\n", "match_updated object\n", "match_updated_360 object\n", "match_available_360 object\n", "match_available object\n", "Length: 197, dtype: object" ] }, "execution_count": 38, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Data types of the features of the raw DataFrame, df_events_matches_competitions\n", "df_events_matches_competitions.dtypes" ] }, { "cell_type": "code", "execution_count": 39, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "level_0 int64\n", "id object\n", "index int64\n", "period int64\n", "timestamp object\n", "minute int64\n", "second int64\n", "possession int64\n", "duration float64\n", "type.id int64\n", "type.name object\n", "possession_team.id int64\n", "possession_team.name object\n", "play_pattern.id int64\n", "play_pattern.name object\n", "team.id int64\n", "team.name object\n", "tactics.formation float64\n", "tactics.lineup object\n", "related_events object\n", "location object\n", "player.id float64\n", "player.name object\n", "position.id float64\n", "position.name object\n", "pass.recipient.id float64\n", "pass.recipient.name object\n", "pass.length float64\n", "pass.angle float64\n", "pass.height.id float64\n", "pass.height.name object\n", "pass.end_location object\n", "pass.body_part.id float64\n", "pass.body_part.name object\n", "pass.type.id float64\n", "pass.type.name object\n", "carry.end_location object\n", "under_pressure object\n", "duel.type.id float64\n", "duel.type.name object\n", "pass.aerial_won object\n", "counterpress object\n", "duel.outcome.id float64\n", "duel.outcome.name object\n", "dribble.outcome.id float64\n", "dribble.outcome.name object\n", "pass.outcome.id float64\n", "pass.outcome.name object\n", "ball_receipt.outcome.id float64\n", "ball_receipt.outcome.name object\n", "interception.outcome.id float64\n", "interception.outcome.name object\n", "shot.statsbomb_xg float64\n", "shot.end_location object\n", "shot.outcome.id float64\n", "shot.outcome.name object\n", "shot.type.id float64\n", "shot.type.name object\n", "shot.body_part.id float64\n", "shot.body_part.name object\n", "shot.technique.id float64\n", "shot.technique.name object\n", "shot.freeze_frame object\n", "goalkeeper.end_location object\n", "goalkeeper.type.id float64\n", "goalkeeper.type.name object\n", "goalkeeper.position.id float64\n", "goalkeeper.position.name object\n", "out object\n", "pass.outswinging object\n", "pass.technique.id float64\n", "pass.technique.name object\n", "clearance.head object\n", "clearance.body_part.id float64\n", "clearance.body_part.name object\n", "pass.switch object\n", "off_camera object\n", "pass.cross object\n", "clearance.left_foot object\n", "dribble.overrun object\n", "dribble.nutmeg object\n", "clearance.right_foot object\n", "pass.no_touch object\n", "foul_committed.advantage object\n", "foul_won.advantage object\n", "pass.assisted_shot_id object\n", "pass.shot_assist object\n", "shot.key_pass_id object\n", "shot.first_time object\n", "clearance.other object\n", "pass.miscommunication object\n", "clearance.aerial_won object\n", "pass.through_ball object\n", "ball_recovery.recovery_failure object\n", "goalkeeper.outcome.id float64\n", "goalkeeper.outcome.name object\n", "goalkeeper.body_part.id float64\n", "goalkeeper.body_part.name object\n", "shot.aerial_won object\n", "foul_committed.card.id float64\n", "foul_committed.card.name object\n", "foul_committed.offensive object\n", "foul_won.defensive object\n", "substitution.outcome.id float64\n", "substitution.outcome.name object\n", "substitution.replacement.id float64\n", "substitution.replacement.name object\n", "50_50.outcome.id float64\n", "50_50.outcome.name object\n", "pass.goal_assist object\n", "goalkeeper.technique.id float64\n", "goalkeeper.technique.name object\n", "pass.cut_back object\n", "miscontrol.aerial_won object\n", "pass.straight object\n", "foul_committed.type.id float64\n", "foul_committed.type.name object\n", "match_id int64\n", "pass.inswinging object\n", "pass.deflected object\n", "injury_stoppage.in_chain object\n", "shot.one_on_one object\n", "bad_behaviour.card.id float64\n", "bad_behaviour.card.name object\n", "shot.deflected object\n", "block.deflection object\n", "foul_committed.penalty object\n", "foul_won.penalty object\n", "block.save_block object\n", "goalkeeper.punched_out object\n", "player_off.permanent object\n", "shot.saved_off_target object\n", "goalkeeper.shot_saved_off_target object\n", "shot.saved_to_post object\n", "goalkeeper.shot_saved_to_post object\n", "shot.open_goal object\n", "goalkeeper.penalty_saved_to_post object\n", "dribble.no_touch object\n", "block.offensive object\n", "shot.follows_dribble object\n", "ball_recovery.offensive object\n", "shot.redirect object\n", "goalkeeper.lost_in_play object\n", "goalkeeper.success_in_play object\n", "match_date object\n", "kick_off object\n", "home_score int64\n", "away_score int64\n", "match_status object\n", "match_status_360 object\n", "last_updated object\n", "last_updated_360 object\n", "match_week int64\n", "competition.competition_id int64\n", "competition.country_name object\n", "competition.competition_name object\n", "season.season_id int64\n", "season.season_name object\n", "home_team.home_team_id int64\n", "home_team.home_team_name object\n", "home_team.home_team_gender object\n", "home_team.home_team_group object\n", "home_team.country.id int64\n", "home_team.country.name object\n", "home_team.managers object\n", "away_team.away_team_id int64\n", "away_team.away_team_name object\n", "away_team.away_team_gender object\n", "away_team.away_team_group object\n", "away_team.country.id int64\n", "away_team.country.name object\n", "away_team.managers object\n", "metadata.data_version object\n", "metadata.shot_fidelity_version object\n", "metadata.xy_fidelity_version object\n", "competition_stage.id int64\n", "competition_stage.name object\n", "stadium.id int64\n", "stadium.name object\n", "stadium.country.id int64\n", "stadium.country.name object\n", "referee.id int64\n", "referee.name object\n", "referee.country.id int64\n", "referee.country.name object\n", "competition_id int64\n", "season_id int64\n", "country_name object\n", "competition_name object\n", "competition_gender object\n", "competition_youth bool\n", "competition_international bool\n", "season_name object\n", "match_updated object\n", "match_updated_360 object\n", "match_available_360 object\n", "match_available object\n", "dtype: object\n" ] } ], "source": [ "# Displays all columns\n", "with pd.option_context('display.max_rows', None, 'display.max_columns', None):\n", " print(df_events_matches_competitions.dtypes)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Full details of these attributes and their data types can be found in the [Data Dictionary](section3.3.1)." ] }, { "cell_type": "code", "execution_count": 40, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "duration 52722\n", "tactics.formation 192459\n", "tactics.lineup 192459\n", "related_events 7079\n", "location 1530\n", " ... \n", "goalkeeper.success_in_play 192684\n", "home_team.home_team_group 63094\n", "home_team.managers 12516\n", "away_team.away_team_group 63094\n", "away_team.managers 12516\n", "Length: 131, dtype: int64" ] }, "execution_count": 40, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Counts of missing values\n", "null_value_stats = df_events_matches_competitions.isnull().sum(axis=0)\n", "null_value_stats[null_value_stats != 0]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 4. Summary\n", "This notebook parses JSON data from the [StatsBomb Open Data GitHub repository](https://github.com/statsbomb/open-data) using [pandas](http://pandas.pydata.org/)." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 5. Next Steps\n", "The next stage is to engineer this DataFrame." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 5. References\n", "* [StatsBomb](https://statsbomb.com/) data\n", "* [StatsBomb Announce The Release Of Free StatsBomb 360 Data: Euro 2020 Available Now](https://statsbomb.com/2021/11/statsbomb-announce-the-release-of-free-statsbomb-360-data-euro-2020-available-now/)\n", "* [StatsBomb](https://github.com/statsbomb/open-data/tree/master/data) open data GitHub repository" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "---\n", "\n", "***Visit my website [eddwebster.com](https://www.eddwebster.com) or my [GitHub Repository](https://github.com/eddwebster) for more projects. If you'd like to get in contact, my Twitter handle is [@eddwebster](http://www.twitter.com/eddwebster) and my email is: edd.j.webster@gmail.com.***" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "[Back to the top](#top)" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3 (ipykernel)", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.7.6" }, "varInspector": { "cols": { "lenName": 16, "lenType": 16, "lenVar": 40 }, "kernels_config": { "python": { "delete_cmd_postfix": "", "delete_cmd_prefix": "del ", "library": "var_list.py", "varRefreshCmd": "print(var_dic_list())" }, "r": { "delete_cmd_postfix": ") ", "delete_cmd_prefix": "rm(", "library": "var_list.r", "varRefreshCmd": "cat(var_dic_list()) " } }, "oldHeight": 642, "position": { "height": "40px", "left": "1118px", "right": "20px", "top": "-7px", "width": "489px" }, "types_to_exclude": [ "module", "function", "builtin_function_or_method", "instance", "_Feature" ], "varInspector_section_display": "none", "window_display": true } }, "nbformat": 4, "nbformat_minor": 2 }