<jupyter_start><jupyter_text>This notebook outlines the steps in downloading the raw data and then formatting and cleaning it. To reproduce the steps in this notebook:1. download the code repository1. run "make data" in a terminal opened at the location of the repository    * this will first download and install all code dependencies, so it is recommended to run this in a virtual environment    * then it will download and run the data formatting and cleaning steps carried out in this notebook    The data will then be downloaded and processed ready to move on to the next steps.<jupyter_code># common notebook config
%run notebook-config.ipynb
from src.data import make_dataset
raw_filepath = "../data/raw"
interim_filepath = "../data/interim"<jupyter_output><empty_output><jupyter_text>Download data* premier league and championship matches from the 2000/01 season onwards from football-data.co.uk* previous seasons have less information<jupyter_code>make_dataset.download_match_data(raw_filepath)<jupyter_output>Downloading data...
Downloaded file: ../data/raw/2000-E0.csv
Downloaded file: ../data/raw/2000-E1.csv
Downloaded file: ../data/raw/2001-E0.csv
Downloaded file: ../data/raw/2001-E1.csv
Downloaded file: ../data/raw/2002-E0.csv
Downloaded file: ../data/raw/2002-E1.csv
Downloaded file: ../data/raw/2003-E0.csv
Downloaded file: ../data/raw/2003-E1.csv
Downloaded file: ../data/raw/2004-E0.csv
Downloaded file: ../data/raw/2004-E1.csv
Downloaded file: ../data/raw/2005-E0.csv
Downloaded file: ../data/raw/2005-E1.csv
Downloaded file: ../data/raw/2006-E0.csv
Downloaded file: ../data/raw/2006-E1.csv
Downloaded file: ../data/raw/2007-E0.csv
Downloaded file: ../data/raw/2007-E1.csv
Downloaded file: ../data/raw/2008-E0.csv
Downloaded file: ../data/raw/2008-E1.csv
Downloaded file: ../data/raw/2009-E0.csv
Downloaded file: ../data/raw/2009-E1.csv
Downloaded file: ../data/raw/2010-E0.csv
Downloaded file: ../data/raw/2010-E1.csv
Downloaded file: ../data/raw/2011-E0.csv
Downloaded file: ../data/raw/2011-E1.[...]<jupyter_text>Reformat data* changes column headings to be human readable. For example, HST will be renamed to home_shotsOnTarget* the date format will be standardised as the CSV files have a mixture of formats* they will be combined in a single CSV file which will be easier to manipulate<jupyter_code>make_dataset.standardise_data_format(raw_filepath, interim_filepath)<jupyter_output>Standardising data...
Loading: ../data/raw/2000-E0.csv
Loading: ../data/raw/2000-E1.csv
Loading: ../data/raw/2001-E0.csv
Loading: ../data/raw/2001-E1.csv
Loading: ../data/raw/2002-E0.csv
Loading: ../data/raw/2002-E1.csv
Loading: ../data/raw/2003-E0.csv
Loading: ../data/raw/2003-E1.csv
Loading: ../data/raw/2004-E0.csv
Loading: ../data/raw/2004-E1.csv
Loading: ../data/raw/2005-E0.csv
Loading: ../data/raw/2005-E1.csv
Loading: ../data/raw/2006-E0.csv
Loading: ../data/raw/2006-E1.csv
Loading: ../data/raw/2007-E0.csv
Loading: ../data/raw/2007-E1.csv
Loading: ../data/raw/2008-E0.csv
Loading: ../data/raw/2008-E1.csv
Loading: ../data/raw/2009-E0.csv
Loading: ../data/raw/2009-E1.csv
Loading: ../data/raw/2010-E0.csv
Loading: ../data/raw/2010-E1.csv
Loading: ../data/raw/2011-E0.csv
Loading: ../data/raw/2011-E1.csv
Loading: ../data/raw/2012-E0.csv
Loading: ../data/raw/2012-E1.csv
Loading: ../data/raw/2013-E0.csv
Loading: ../data/raw/2013-E1.csv
Loading: ../data/raw/2014-E0.csv
Loading: ../data/raw/[...]<jupyter_text>Analysis to identify data cleaning steps<jupyter_code>matches = pd.read_csv("{}/matches_raw.csv".format(interim_filepath))

# Show random sample of records for quick check
matches.sample(10)
print("Matches rows and columns: {}".format(matches.shape))
matches.describe()<jupyter_output>Matches rows and columns: (19172, 27)<jupyter_text>We can see that some features do not have the expected count as given by the matches row number above. This means that there are some null values.<jupyter_code># Check null values in any of the following columns
columns_to_check = [
    "league",
    "season",
    "division",
    "home_goals",
    "away_goals"
]

# These are important values, so these should not be null

# Show records with any null values
matches[matches[columns_to_check].isnull().any(axis=1)]
# Check null values in any of the following columns
columns_to_check = [
    "odds_interwetten_homeWin",
    "odds_interwetten_draw",
    "odds_interwetten_awayWin",
    "odds_williamHill_homeWin",
    "odds_williamHill_draw",
    "odds_williamHill_awayWin"
]

# These may be used later, but we can handle null values if needed

# Show records with any null values
matches[matches[columns_to_check].isnull().any(axis=1)]
numeric_features = matches.dtypes[matches.dtypes != "object"].index
columns_already_checked = [
    "league",
    "season",
    "division",
    "home_goals",
    "away_goals",
    "odds_interwetten_homeWin",
    "odds_interwetten_draw",
    "odds_interwetten_awayWin",
    "odds_williamHill_homeWin",
    "odds_williamHill_draw",
    "odds_williamHill_awayWin"
]

# Check null values in any of the remaining numeric columns
columns_to_check = [col for col in numeric_features if col not in columns_already_checked]

# These are important values, so these should not be null

# Show records with any null values
matches[matches[columns_to_check].isnull().any(axis=1)]<jupyter_output><empty_output><jupyter_text>There is a match (Bolton vs Brentford in 2019) with nan values for all the match stats apart from the goals scored.This match was initially postponed but then Brentford were given the 0-1 away win without the match being played* https://en.wikipedia.org/wiki/2018%E2%80%9319_Brentford_F.C._season  Impute missing valuesWe will impute the values for the 2019 Bolton/Brentford match using median values from other matches.  We will take the median values from all previous matches in the same division with the same number of home goals and away goals.Using median ensures that the imputed values are less influenced by any extreme values.Grouping by league, division, home goals and away goals ensures realistic values for that result. Calculating the median from previous matches prevents leakage of data that was not available at the time.<jupyter_code>make_dataset.impute_missing_values(interim_filepath)
# load match data following imputation
matches = pd.read_csv("{}/matches_imputed_missing.csv".format(interim_filepath))
# Check the record
matches[
    (matches.home_team == "Bolton") &
    (matches.away_team == "Brentford") &
    (matches.season == 2018)
]<jupyter_output><empty_output><jupyter_text>Now we will sanity check some feature values.  Shots should always be greater than shots on target.  If this is not true, then it is most likely caused by a data entry mistake.Unfortunately it is not clear how own goals or penalties are handled in the raw data, so we cannot be sure that goals should always be greater than shots on target.Players could receive a card without committing a foul, so we shouldn't enforce fouls being greater than cards.<jupyter_code># Sanity check shots
display(matches.loc[matches["home_shotsOnTarget"] > matches["home_shots"]])
display(matches.loc[matches["away_shotsOnTarget"] > matches["away_shots"]])<jupyter_output><empty_output><jupyter_text>Only one of these matches took place outside of the 2000 season - Aston Villa vs West Ham in 2015.We can see from the following link that the home_shots should have been 15 rather than 5:* https://www.premierleague.com/match/12285Note: this match has been corrected on the football-data website now.It looks like this was a data entry mistake and the 1 was missed.  It would make sense that the others were caused by entry mistakes as well, so we can fix them by adding 10.  Correct data entry mistakesSeveral matches have been identified which have fewer shots than shots on target.  These are most likely due to a missing 1 during data entry, so we will add 10 to each of them.<jupyter_code>make_dataset.correct_data_entry_mistakes(interim_filepath)
# load match data following correction
matches = pd.read_csv("{}/matches_corrected.csv".format(interim_filepath))
# Ensure there are no longer matches with more shots on target than shots
display(matches.loc[matches["home_shotsOnTarget"] > matches["home_shots"]])
display(matches.loc[matches["away_shotsOnTarget"] > matches["away_shots"]])<jupyter_output><empty_output><jupyter_text>The correction has worked, so let's move on to checking the distribution of values for each feature.  This will help us identify if there are any extreme outliers.  Check data distribution for outliers<jupyter_code># Using boxplots to look for outliers
exclude_features = [
    "league",
    "season",
    "division",
    "odds_interwetten_homeWin",
    "odds_interwetten_draw",
    "odds_interwetten_awayWin",
    "odds_williamHill_homeWin",
    "odds_williamHill_draw",
    "odds_williamHill_awayWin"
]
feature_columns = [col for col in numeric_features if col not in exclude_features]
matches[feature_columns].boxplot(rot=60, figsize=(20,10))
plt.show()<jupyter_output><empty_output><jupyter_text>These plots look reasonable and there are no clear incorrect values. There are quite a few circles representing outliers. It looks like this is due to the distributions being skewed rather than because the data is wrong.The following extreme values are unlikely but still possible. A match where a team:* scores 9 goals* has no shots* has 43 shots* commits no fouls* commits 35 fouls* gets 11 yellow cards  Expand features to for and against featuresNow we will expand the features in to for and against values.  For example, home goals will be expanded in to home goals for as well as away goals against.  This will make for and against aggregations easier to calculate later.<jupyter_code>make_dataset.expand_for_against_values(interim_filepath)
# load match data following expansion of features and view a sample
matches = pd.read_csv("{}/matches_expanded_features.csv".format(interim_filepath))

matches.sample(10)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09885401096163428, 'fraction_numerical': 0.03457897359242651, 'mean_word_length': 4.725042783799202, 'pattern_counts': {'":': 0, '<': 38, '<?xml version=': 0, '>': 42, 'https://': 2, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 32, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '649353', 'license': 'MIT', 'n_tokens_mistral': 3373, 'n_tokens_neox': 3011, 'n_words': 1042, 'path': 'notebooks/001-jah-download-and-clean-data.ipynb', 'repo_name': 'JaHicks/premier_league_prediction', 'size': '10035'}