Wyscout Data Engineering¶

%load_ext autoreload
%autoreload 2

# Python ≥3.5 (ideally)
import platform
import sys, getopt
assert sys.version_info >= (3, 5)
import csv

# Import Dependencies
%matplotlib inline

# Math Operations
import numpy as np
import math
from math import pi

# Datetime
import datetime
from datetime import date
import time

# Data Preprocessing
import pandas as pd
import os
import re
import random
from io import BytesIO
from pathlib import Path

# Reading directories
import glob
import os
from os.path import basename

# Working with JSON
import json
from pandas.io.json import json_normalize

# Data Visualisation
import matplotlib as mpl
import matplotlib.pyplot as plt
from matplotlib import patches
from matplotlib.patches import Arc
import seaborn as sns
plt.style.use('seaborn-whitegrid')
import missingno as msno

# Downloading data sources
from urllib.parse import urlparse
from urllib.request import urlopen, urlretrieve
from zipfile import ZipFile, is_zipfile
from tqdm import tqdm    # Progress Bar

# Display in Jupyter
from IPython.display import Image, Video, YouTubeVideo
from IPython.core.display import HTML

# Ignore Warnings
import warnings
warnings.filterwarnings(action="ignore", message="^internal gelsd")

print('Setup Complete')

# Python / module versions used here for reference
print('Python: {}'.format(platform.python_version()))
print('NumPy: {}'.format(np.__version__))
print('pandas: {}'.format(pd.__version__))
print('matplotlib: {}'.format(mpl.__version__))
print('Seaborn: {}'.format(sns.__version__))

# Read in preparsed Wyscout Events DataFrame
df_wyscout_raw = pd.read_csv(os.path.join(data_dir_wyscout, 'raw', 'csv', 'combined', 'wyscout_big5_combined.csv'))

# Display the first 5 rows of the raw DataFrame, df_wyscout_raw
df_wyscout_raw.head()

# Display the last 5 rows of the raw DataFrame, df_wyscout_raw
df_wyscout_raw.tail()

# Print the shape of the raw DataFrame, ddf_wyscout_raw
print(df_wyscout_raw.shape)

# Print the column names of the raw DataFrame, df_wyscout_raw
print(df_wyscout_raw.columns)

# Data types of the features of the raw DataFrame, df_wyscout_raw
df_wyscout_raw.dtypes

# Info for the raw DataFrame, df_wyscout_raw
df_wyscout_raw.info()

# Description of the raw DataFrame, df_wyscout_raw, showing some summary statistics for each numberical column in the DataFrame
df_wyscout_raw.describe()

# Plot visualisation of the missing values for each feature of the raw DataFrame, df_wyscout_raw
msno.matrix(df_wyscout_raw, figsize = (30, 7))

# Counts of missing values
null_value_stats = df_wyscout_raw.isnull().sum(axis=0)
null_value_stats[null_value_stats != 0]

# Select columns of interest

## Define columns
cols = ['season',
        'date_time_timestamp',
        'fixture',
        'team_home',
        'team_away',
        'teamName',
        'goals_home',
        'goals_away',
        'eventName',
        'subEventName'
       ]

## Streamline DataFrame with columns of interest
df_wyscout_select = df_wyscout[cols]

## 
df_wyscout_select['Opponent'] = np.where(df_wyscout_select['team_home'] == df_wyscout_select['teamName'], df_wyscout_select['team_away'], df_wyscout_select['team_home'])

#

## Group DataFrame and Aggregate on 'eventName'
df_wyscout_fixture_grouped = (df_wyscout_select
                                  .groupby(['season', 'date_time_timestamp', 'fixture', 'teamName', 'Opponent', 'goals_home', 'goals_away', 'eventName'])
                                  .agg({'eventName': ['count']})
                             )

## Drop level
df_wyscout_fixture_grouped.columns = df_wyscout_fixture_grouped.columns.droplevel(level=0)


## Reset index
df_wyscout_fixture_grouped = df_wyscout_fixture_grouped.reset_index()

## Rename columns
df_wyscout_fixture_grouped = df_wyscout_fixture_grouped.rename(columns={'season': 'Season',
                                                                        'date_time_timestamp': 'Date',
                                                                        'fixture': 'Fixture',
                                                                        'teamName': 'Team',
                                                                        'Opponent': 'Opponent',
                                                                        'goals_home': 'Goals_Home',
                                                                        'goals_away': 'Goals_Away',
                                                                        'eventName': 'Event',
                                                                        'count': 'Team_Value'
                                                                       }
                                                              )

## Display DataFrame
df_wyscout_fixture_grouped.head()

# Clean Data

## Drop columns
df_wyscout_fixture_grouped = df_wyscout_fixture_grouped.drop(columns=['Team_y', 'Opponent_y', 'Goals_Home_y', 'Goals_Away_y'])

## Rename columns
df_wyscout_fixture_grouped = df_wyscout_fixture_grouped.rename(columns={'Season_x': 'Season',
                                                                        'Team_x': 'Team',
                                                                        'Opponent_x': 'Opponent',
                                                                        'Goals_Home_x': 'Goals_Home',
                                                                        'Goals_Away_x': 'Goals_Away',
                                                                        'Team_Value_x': 'Team_Value',
                                                                        'Team_Value_y': 'Opponent_Value',
                                                                       }
                                                                  )

## Replace null values with zeros
df_wyscout_fixture_grouped['Team_Value'] = df_wyscout_fixture_grouped['Team_Value'].replace(np.nan, 0)
df_wyscout_fixture_grouped['Opponent_Value'] = df_wyscout_fixture_grouped['Opponent_Value'].replace(np.nan, 0)

## Convert Opponent_Value' from Float64 to Int64 type
df_wyscout_fixture_grouped['Opponent_Value'] = df_wyscout_fixture_grouped['Opponent_Value'].astype('Int64')

## Display DataFrame
df_wyscout_fixture_grouped.head()

#

##
df_fixture_gw = (df_wyscout_fixture_grouped
                     .groupby(['Date', 'Team'])
                     .agg({'Team': ['nunique']})
                )

##
df_fixture_gw.columns = df_fixture_gw.columns.droplevel(level=0)


##
df_fixture_gw = df_fixture_gw.reset_index()

##
df_fixture_gw = df_fixture_gw.rename(columns={'Date': 'Date',
                                              'nunique': 'Gameweek',
                                             }
                                    )

## Groupby. See: https://stackoverflow.com/questions/18554920/pandas-aggregate-count-distinct
df_fixture_gw = (df_fixture_gw.groupby(['Team', 'Date']).sum()
                              .groupby(level=0).cumsum().reset_index()
                )

## Display DataFrame
df_fixture_gw.head()

# Join DataFrame 
df_wyscout_fixture_grouped = pd.merge(df_wyscout_fixture_grouped, df_fixture_gw, how='left', left_on=['Date', 'Team'], right_on = ['Date', 'Team'])

# Display DataFrame
df_wyscout_fixture_grouped.head(50)

# Group DataFrame by Team

##
df_wyscout_team_grouped = (df_wyscout_fixture_grouped
                               .groupby(['Team', 'Event'])
                               .agg({'Team_Value': ['sum'],
                                     'Opponent_Value': ['sum']
                                    }
                                   )
                          )

##
df_wyscout_team_grouped.columns = df_wyscout_team_grouped.columns.droplevel(level=0)

##
df_wyscout_team_grouped = df_wyscout_team_grouped.reset_index()

## Rename columns
df_wyscout_team_grouped.columns = ['Team', 'Event', 'Team_Value', 'Opponent_Value']

## Display columns
df_wyscout_team_grouped.head()

df_wyscout_fixture_grouped_big5 = df_wyscout_fixture_grouped[df_wyscout_fixture_grouped['competitionId'].isin(lst_big5_leagues)]