<jupyter_start><jupyter_text>Soccerstats Predictions v2.0 The changelog from v1.x:* Implement data cleaning pipeline for model predictions.* Load saved model from disk.* Use model to predict data points.  A. Data Preparation  1. Read csv file<jupyter_code># load csv data to predict
stat_df = sqlContext.read\
    .format("com.databricks.spark.csv")\
    .options(header = True)\
    .load("data/predFixture.csv")<jupyter_output><empty_output><jupyter_text>2. Filter-out column values<jupyter_code>from pyspark.sql.functions import udf
from pyspark.sql.types import StringType

# replace "-" values with null: HTS_teamAvgOpponentPPG, ATS_teamAvgOpponentPPG
nullify_hyphen_cols = udf(
    lambda row_value: None if row_value == "-" else row_value, 
    StringType()
)
stat_df = (stat_df.withColumn("HTS_teamAvgOpponentPPG", nullify_hyphen_cols(stat_df.HTS_teamAvgOpponentPPG))
           .withColumn("ATS_teamAvgOpponentPPG", nullify_hyphen_cols(stat_df.ATS_teamAvgOpponentPPG))
          )

# drop Null values
stat_df = stat_df.dropna()<jupyter_output><empty_output><jupyter_text>B. Deep Learning  1. Clean data<jupyter_code># drop unnecessary columns
ml_df = stat_df.drop(
    "gameID", "gamePlayDate", "gamePlayTime", "gameHomeTeamName",
    "gameAwayTeamName", "gameHomeTeamID","gameAwayTeamID", "leagueName", 
    "leagueDivisionName", "gameFtScore"
)
# separate col types: double & string
# double type features
dtype_features = [
    "leagueCompletion", "HTS_teamPosition", "HTS_teamGamesPlayed", "HTS_teamGamesWon", 
    "HTS_teamGamesDraw", "HTS_teamGamesLost", "HTS_teamGoalsScored", "HTS_teamGoalsConceded",
    "HTS_teamPoints", "HTS_teamPointsPerGame", "HTS_teamPPGlast8", "HTS_homeGamesWon",
    "HTS_homeGamesDraw", "HTS_homeGamesLost", "HTS_homeGamesPlayed", "HTS_awayGamesWon", 
    "HTS_awayGamesDraw", "HTS_awayGamesLost", "HTS_awayGamesPlayed", "HTS_teamPPGHome",
    "HTS_teamPPGAway", "HTS_teamAvgOpponentPPG", "HTS_homeGoalMargin_by1_wins",
    "HTS_homeGoalMargin_by1_losses", "HTS_homeGoalMargin_by2_wins", "HTS_homeGoalMargin_by2_losses", 
    "HTS_homeGoalMargin_by3_wins", "HTS_homeGoalMargin_by3_losses", "HTS_homeGoalMargin_by4p_wins", 
    "HTS_homeGoalMargin_by4p_losses", "HTS_awayGoalMargin_by1_wins", "HTS_awayGoalMargin_by1_losses", 
    "HTS_awayGoalMargin_by2_wins", "HTS_awayGoalMargin_by2_losses", "HTS_awayGoalMargin_by3_wins", 
    "HTS_awayGoalMargin_by3_losses", "HTS_awayGoalMargin_by4p_wins", "HTS_awayGoalMargin_by4p_losses", 
    "HTS_totalGoalMargin_by1_wins", "HTS_totalGoalMargin_by1_losses", "HTS_totalGoalMargin_by2_wins", 
    "HTS_totalGoalMargin_by2_losses", "HTS_totalGoalMargin_by3_wins", "HTS_totalGoalMargin_by3_losses", 
    "HTS_totalGoalMargin_by4p_wins", "HTS_totalGoalMargin_by4p_losses", "HTS_homeGoalsScored", 
    "HTS_homeGoalsConceded", "HTS_homeGoalsScoredPerMatch", "HTS_homeGoalsConcededPerMatch", 
    "HTS_homeScored_ConcededPerMatch", "HTS_awayGoalsScored", "HTS_awayGoalsConceded", 
    "HTS_awayGoalsScoredPerMatch", "HTS_awayGoalsConcededPerMatch", "HTS_awayScored_ConcededPerMatch", 
    "ATS_teamPosition", "ATS_teamGamesPlayed", "ATS_teamGamesWon", "ATS_teamGamesDraw", "ATS_teamGamesLost", 
    "ATS_teamGoalsScored", "ATS_teamGoalsConceded", "ATS_teamPoints", "ATS_teamPointsPerGame", 
    "ATS_teamPPGlast8", "ATS_homeGamesWon", "ATS_homeGamesDraw", "ATS_homeGamesLost", 
    "ATS_homeGamesPlayed", "ATS_awayGamesWon", "ATS_awayGamesDraw", "ATS_awayGamesLost", 
    "ATS_awayGamesPlayed", "ATS_teamPPGHome", "ATS_teamPPGAway", "ATS_teamAvgOpponentPPG", 
    "ATS_homeGoalMargin_by1_wins", "ATS_homeGoalMargin_by1_losses", "ATS_homeGoalMargin_by2_wins", 
    "ATS_homeGoalMargin_by2_losses", "ATS_homeGoalMargin_by3_wins", "ATS_homeGoalMargin_by3_losses", 
    "ATS_homeGoalMargin_by4p_wins", "ATS_homeGoalMargin_by4p_losses", "ATS_awayGoalMargin_by1_wins",
    "ATS_awayGoalMargin_by1_losses", "ATS_awayGoalMargin_by2_wins", "ATS_awayGoalMargin_by2_losses", 
    "ATS_awayGoalMargin_by3_wins", "ATS_awayGoalMargin_by3_losses", "ATS_awayGoalMargin_by4p_wins", 
    "ATS_awayGoalMargin_by4p_losses", "ATS_totalGoalMargin_by1_wins", "ATS_totalGoalMargin_by1_losses", 
    "ATS_totalGoalMargin_by2_wins", "ATS_totalGoalMargin_by2_losses", "ATS_totalGoalMargin_by3_wins", 
    "ATS_totalGoalMargin_by3_losses", "ATS_totalGoalMargin_by4p_wins", "ATS_totalGoalMargin_by4p_losses", 
    "ATS_homeGoalsScored", "ATS_homeGoalsConceded", "ATS_homeGoalsScoredPerMatch", "ATS_homeGoalsConcededPerMatch", 
    "ATS_homeScored_ConcededPerMatch", "ATS_awayGoalsScored", "ATS_awayGoalsConceded", "ATS_awayGoalsScoredPerMatch", 
    "ATS_awayGoalsConcededPerMatch", "ATS_awayScored_ConcededPerMatch"
]
# string type features
stype_features = [
    "HTS_teamCleanSheetPercent", "HTS_homeOver1_5GoalsPercent",
    "HTS_homeOver2_5GoalsPercent", "HTS_homeOver3_5GoalsPercent", "HTS_homeOver4_5GoalsPercent", 
    "HTS_awayOver1_5GoalsPercent", "HTS_awayOver2_5GoalsPercent", "HTS_awayOver3_5GoalsPercent", 
    "HTS_awayOver4_5GoalsPercent", "HTS_homeCleanSheets", "HTS_homeWonToNil", "HTS_homeBothTeamsScored", 
    "HTS_homeFailedToScore", "HTS_homeLostToNil", "HTS_awayCleanSheets", "HTS_awayWonToNil", 
    "HTS_awayBothTeamsScored", "HTS_awayFailedToScore", "HTS_awayLostToNil", "HTS_homeScored_ConcededBy_0",
    "HTS_homeScored_ConcededBy_1", "HTS_homeScored_ConcededBy_2", "HTS_homeScored_ConcededBy_3", 
    "HTS_homeScored_ConcededBy_4", "HTS_homeScored_ConcededBy_5p", "HTS_homeScored_ConcededBy_0_or_1", 
    "HTS_homeScored_ConcededBy_2_or_3", "HTS_homeScored_ConcededBy_4p", "HTS_awayScored_ConcededBy_0",
    "HTS_awayScored_ConcededBy_1", "HTS_awayScored_ConcededBy_2", "HTS_awayScored_ConcededBy_3", 
    "HTS_awayScored_ConcededBy_4", "HTS_awayScored_ConcededBy_5p", "HTS_awayScored_ConcededBy_0_or_1", 
    "HTS_awayScored_ConcededBy_2_or_3", "HTS_awayScored_ConcededBy_4p",
    "ATS_teamCleanSheetPercent", "ATS_homeOver1_5GoalsPercent", "ATS_homeOver2_5GoalsPercent", 
    "ATS_homeOver3_5GoalsPercent", "ATS_homeOver4_5GoalsPercent", "ATS_awayOver1_5GoalsPercent",
    "ATS_awayOver2_5GoalsPercent", "ATS_awayOver3_5GoalsPercent", "ATS_awayOver4_5GoalsPercent", 
    "ATS_homeCleanSheets", "ATS_homeWonToNil", "ATS_homeBothTeamsScored", "ATS_homeFailedToScore", 
    "ATS_homeLostToNil", "ATS_awayCleanSheets", "ATS_awayWonToNil", "ATS_awayBothTeamsScored", 
    "ATS_awayFailedToScore", "ATS_awayLostToNil", "ATS_homeScored_ConcededBy_0", "ATS_homeScored_ConcededBy_1", 
    "ATS_homeScored_ConcededBy_2", "ATS_homeScored_ConcededBy_3", "ATS_homeScored_ConcededBy_4", 
    "ATS_homeScored_ConcededBy_5p", "ATS_homeScored_ConcededBy_0_or_1", "ATS_homeScored_ConcededBy_2_or_3", 
    "ATS_homeScored_ConcededBy_4p", "ATS_awayScored_ConcededBy_0", "ATS_awayScored_ConcededBy_1", 
    "ATS_awayScored_ConcededBy_2", "ATS_awayScored_ConcededBy_3", "ATS_awayScored_ConcededBy_4", 
    "ATS_awayScored_ConcededBy_5p", "ATS_awayScored_ConcededBy_0_or_1", "ATS_awayScored_ConcededBy_2_or_3", 
    "ATS_awayScored_ConcededBy_4p" 
]
# integer type features
itype_features = ["HTS_teamGoalsDifference", "ATS_teamGoalsDifference"]
from pyspark.sql.types import DoubleType, IntegerType
from pyspark.sql.functions import col

# cast types to columns: doubles
ml_df = ml_df.select(*[col(c).cast("double").alias(c) for c in dtype_features] + stype_features + itype_features)

# convert "HTS_teamGoalsDifference" & "ATS_teamGoalsDifference" to integer
int_udf = udf(
    lambda r: int(r),
    IntegerType()
)
# cast types to columns: integers
ml_df = ml_df.select(*[int_udf(col(col_name)).name(col_name) for col_name in itype_features] + stype_features + dtype_features)

# convert percent cols to float
percent_udf = udf(
    lambda r: float(r.split("%")[0])/100, 
    DoubleType()
)
# cast types to columns: strings
ml_df = ml_df.select(*[percent_udf(col(col_name)).name(col_name) for col_name in stype_features] + itype_features + dtype_features)<jupyter_output><empty_output><jupyter_text>2. Some featurization<jupyter_code>import numpy as np

feature_cols = dtype_features + stype_features + itype_features
ml_df = ml_df[feature_cols]

# convert dataframe to ndarray
X_new = np.array(ml_df.select(feature_cols).collect())
print("New features shape: '{}'".format(X_new.shape))<jupyter_output>New features shape: '(2, 187)'<jupyter_text>3. Restore model from disk<jupyter_code>from keras.models import model_from_json

# model version to restore
MODEL_VERSION = 1.6
# load json and create model
json_file = open('models/model_({}).json'.format(MODEL_VERSION), 'r')
loaded_model_json = json_file.read()
json_file.close()
loaded_model = model_from_json(loaded_model_json)

# load weights into new model
loaded_model.load_weights("models/model_({}).h5".format(MODEL_VERSION))
print("Loaded model version '{}' from disk!".format(MODEL_VERSION))<jupyter_output>/Users/gilbert/Envs/pyspark/lib/python2.7/site-packages/h5py/__init__.py:36: FutureWarning: Conversion of the second argument of issubdtype from `float` to `np.floating` is deprecated. In future, it will be treated as `np.float64 == np.dtype(float).type`.
  from ._conv import register_converters as _register_converters
Using TensorFlow backend.<jupyter_text>4. Model predictions<jupyter_code>import numpy as np
from prettytable import PrettyTable

# evaluate loaded model on test data
loaded_model.compile(
    loss='binary_crossentropy', 
    optimizer='adagrad', 
    metrics=['accuracy'])

# make prediction: class prediction
y_new_class = loaded_model.predict_classes(X_new)
# make prediction: probability prediction
y_new_prob = loaded_model.predict_proba(X_new)

# create predictions table
predictions = PrettyTable()
predictions.field_names = [
    "gamePlayDate", "gameHomeTeamName", "gameAwayTeamName", "leagueName", 
    "leagueDivisionName", "predClass", "predProb", "predOutcome"
]

# populate prediction table
for val in range(len(X_new)):
    if y_new_class[val] == 0:
        pred = "Under 3.5"
    else:
        pred = "Over 3.5"

    # append values to predictions table
    predictions.add_row([
        "{}".format(stat_df.collect()[val]["gamePlayDate"]),
        "{}".format(stat_df.collect()[val]["gameHomeTeamName"]),
        "{}".format(stat_df.collect()[val]["gameAwayTeamName"]),
        "{}".format(stat_df.collect()[val]["leagueName"]),
        "{}".format(stat_df.collect()[val]["leagueDivisionName"]),
        "{}".format(y_new_class[val]),
        "{}".format(y_new_prob[val]),
        "{}".format(pred)
    ])

print(predictions)<jupyter_output>+--------------+------------------+------------------+------------+---------------------+-----------+-------------+-------------+
| gamePlayDate | gameHomeTeamName | gameAwayTeamName | leagueName |  leagueDivisionName | predClass |   predProb  | predOutcome |
+--------------+------------------+------------------+------------+---------------------+-----------+-------------+-------------+
|  2018-04-02  |  Guayaquil City  |      Aucas       |  Ecuador   | Serie A - 2nd stage |    [0]    | [0.0189554] |  Under 3.5  |
|  2018-03-10  |   Southern Utd   |  Hamilton Wand.  | NewZealand |     Premiership     |    [1]    | [0.5632164] |   Over 3.5  |
+--------------+------------------+------------------+------------+---------------------+-----------+-------------+-------------+
{'fraction_non_alphanumeric': 0.14170506912442396, 'fraction_numerical': 0.017458348103509394, 'mean_word_length': 5.626541397533764, 'pattern_counts': {'":': 0, '<': 22, '<?xml version=': 0, '>': 22, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '23695', 'license': 'MIT', 'n_tokens_mistral': 4492, 'n_tokens_neox': 4293, 'n_words': 575, 'path': '_archived/sstats/sstats-v2.0.ipynb', 'repo_name': 'Gilbertly/notebooks', 'size': '11284'}