<jupyter_start><jupyter_text>Feature engineering on NCAA dataDomain knowledge is critical to getting the best out of data analysis and machine learning.In the case of basketball, <NAME> identified four factors that are critical to success:* Shooting* Turnovers* Rebounding* Free ThrowsOf course, it is not enough to identify factors, you need a way to measure them.Read [this article](https://www.basketball-reference.com/about/factors.html) about the four factors and how they are measured. In this notebook, we will compute them from the box score data. The numbers are slightly different from that of the article because the article is about the NBA, but these numbers are Dean Oliver's variants for NCAA games.  Shooting efficiencyShooting is measured as the fraction of field goal attempts made, weighting 3 points higher:$(FG + 0.5 * 3P) / FGA$Let's compute the offensive and defensive shooting efficiency and see how correlated they are to winning teams.See [%%bigquery documentation](https://googleapis.github.io/google-cloud-python/latest/bigquery/magics.html) for how to use it.<jupyter_code>%%bigquery df1
SELECT 
  team_code,
  AVG(SAFE_DIVIDE(fgm + 0.5 * fgm3,fga)) AS offensive_shooting_efficiency,
  AVG(SAFE_DIVIDE(opp_fgm + 0.5 * opp_fgm3,opp_fga)) AS opponents_shooting_efficiency,
  AVG(win) AS win_rate,
  COUNT(win) AS num_games
FROM lab_dev.team_box
WHERE fga IS NOT NULL
GROUP BY team_code<jupyter_output><empty_output><jupyter_text>Let's remove the entries corresponding to teams that played fewer than 100 games, and then plot it.<jupyter_code>df1 = df1[df1['num_games'] > 100]
df1.plot(x='offensive_shooting_efficiency', y='win_rate', style='o');
df1.plot(x='opponents_shooting_efficiency', y='win_rate', style='o');<jupyter_output><empty_output><jupyter_text>Does the relationship make sense? Do you think offensive and defensive efficiency are good predictors of a team's performance?  Turnover PercentageTurnover percentage is measured as:$TOV / (FGA + 0.475 * FTA + TOV - OREB)$As before, let's compute this, and see whether it is a good predictor. For simplicity, we will compute only offensive turnover percentage, although we should really compute both sides as we did for scoring efficiency.<jupyter_code>%%bigquery df2
SELECT 
  team_code,
  AVG(SAFE_DIVIDE(tov,fga+0.475*fta+tov-oreb)) AS turnover_percent,
  AVG(win) AS win_rate,
  COUNT(win) AS num_games
FROM lab_dev.team_box
WHERE fga IS NOT NULL
GROUP BY team_code
HAVING num_games > 100
df2.plot(x='turnover_percent', y='win_rate', style='o');<jupyter_output><empty_output><jupyter_text>ReboundingAgain, we'd have to measure both sides, but for simplicity, we'll do only the offensive rebounds.$ORB / (ORB + Opp DRB)$<jupyter_code>%%bigquery df3
SELECT 
  team_code,
  AVG(SAFE_DIVIDE(oreb,oreb + opp_dreb)) AS rebounding,
  AVG(win) AS win_rate,
  COUNT(win) AS num_games
FROM lab_dev.team_box
WHERE fga IS NOT NULL
GROUP BY team_code
HAVING num_games > 100
df3.plot(x='rebounding', y='win_rate', style='o');<jupyter_output><empty_output><jupyter_text>The relationship doesn't seem all that strong here. One way to measure the strength of the relationship is through the correlation. Numbers near 0 mean not correlated and numbers near +/- 1 indicate high correlation:<jupyter_code>df3.corr()['win_rate']<jupyter_output><empty_output><jupyter_text>The correlation between rebounding and win_rate is 0.38.  Compare that to the first data frame:<jupyter_code>df1.corr()['win_rate']<jupyter_output><empty_output><jupyter_text>Notice that the offensive and opponents efficiency have correlation of 0.67 and -0.66, which are higher.<jupyter_code>df2.corr()['win_rate']<jupyter_output><empty_output><jupyter_text>Free throw factorThis is a measure of both how often a team gets to the line and how often they make them:$FT / FGA$<jupyter_code>%%bigquery df3
SELECT 
  team_code,
  AVG(SAFE_DIVIDE(ftm,fga)) AS freethrows,
  AVG(win) AS win_rate,
  COUNT(win) AS num_games
FROM lab_dev.team_box
WHERE fga IS NOT NULL
GROUP BY team_code
HAVING num_games > 100
df3.plot(x='freethrows', y='win_rate', style='o');
df3.corr()['win_rate']<jupyter_output><empty_output><jupyter_text>Machine LearningLet's use these factors to create a simple ML model<jupyter_code>%%bigquery
SELECT 
  team_code,
  is_home,
  SAFE_DIVIDE(fgm + 0.5 * fgm3,fga) AS offensive_shooting_efficiency,
  SAFE_DIVIDE(opp_fgm + 0.5 * opp_fgm3,opp_fga) AS opponents_shooting_efficiency,
  SAFE_DIVIDE(tov,fga+0.475*fta+tov-oreb) AS turnover_percent,
  SAFE_DIVIDE(opp_tov,opp_fga+0.475*opp_fta+opp_tov-opp_oreb) AS opponents_turnover_percent,
  SAFE_DIVIDE(oreb,oreb + opp_dreb) AS rebounding,
  SAFE_DIVIDE(opp_oreb,opp_oreb + dreb) AS opponents_rebounding,
  SAFE_DIVIDE(ftm,fga) AS freethrows,
  SAFE_DIVIDE(opp_ftm,opp_fga) AS opponents_freethrows,
  win
FROM lab_dev.team_box
WHERE fga IS NOT NULL and win IS NOT NULL
LIMIT 10
%%bigquery
CREATE OR REPLACE MODEL lab_dev.four_factors_model
OPTIONS(model_type='logistic_reg', input_label_cols=['win'])
AS

SELECT 
  team_code,
  is_home,
  SAFE_DIVIDE(fgm + 0.5 * fgm3,fga) AS offensive_shooting_efficiency,
  SAFE_DIVIDE(opp_fgm + 0.5 * opp_fgm3,opp_fga) AS opponents_shooting_efficiency,
  SAFE_DIVIDE(tov,fga+0.475*fta+tov-oreb) AS turnover_percent,
  SAFE_DIVIDE(opp_tov,opp_fga+0.475*opp_fta+opp_tov-opp_oreb) AS opponents_turnover_percent,
  SAFE_DIVIDE(oreb,oreb + opp_dreb) AS rebounding,
  SAFE_DIVIDE(opp_oreb,opp_oreb + dreb) AS opponents_rebounding,
  SAFE_DIVIDE(ftm,fga) AS freethrows,
  SAFE_DIVIDE(opp_ftm,opp_fga) AS opponents_freethrows,
  win
FROM lab_dev.team_box
WHERE fga IS NOT NULL and win IS NOT NULL
%%bigquery
SELECT * FROM ML.EVALUATE(MODEL lab_dev.four_factors_model)<jupyter_output><empty_output><jupyter_text>87% isn't bad, but ... there is a *huge* problem with the above approach.How are we supposed to know Team A's free throw shooting percentage against Team B before the game is played?What we could do is to get the free throw shooting percentage of Team A in the 3 games prior to this one and use that. This requires analytic functions in SQL. If you are not familar with these, make a copy of the select statement and modify it in stages until you grasp what is happening.<jupyter_code>%%bigquery
CREATE OR REPLACE MODEL lab_dev.four_factors_model
OPTIONS(model_type='logistic_reg', input_label_cols=['win'])
AS

WITH all_games AS (
SELECT 
  game_date,
  team_code,
  is_home,
  SAFE_DIVIDE(fgm + 0.5 * fgm3,fga) AS offensive_shooting_efficiency,
  SAFE_DIVIDE(opp_fgm + 0.5 * opp_fgm3,opp_fga) AS opponents_shooting_efficiency,
  SAFE_DIVIDE(tov,fga+0.475*fta+tov-oreb) AS turnover_percent,
  SAFE_DIVIDE(opp_tov,opp_fga+0.475*opp_fta+opp_tov-opp_oreb) AS opponents_turnover_percent,
  SAFE_DIVIDE(oreb,oreb + opp_dreb) AS rebounding,
  SAFE_DIVIDE(opp_oreb,opp_oreb + dreb) AS opponents_rebounding,
  SAFE_DIVIDE(ftm,fga) AS freethrows,
  SAFE_DIVIDE(opp_ftm,opp_fga) AS opponents_freethrows,
  win
FROM lab_dev.team_box
WHERE fga IS NOT NULL and win IS NOT NULL
)

, prevgames AS (
SELECT 
  is_home,
  AVG(offensive_shooting_efficiency) 
       OVER(PARTITION BY team_code ORDER BY game_date ASC ROWS BETWEEN 4 PRECEDING AND 1 PRECEDING) AS offensive_shooting_efficiency,
  AVG(opponents_shooting_efficiency) 
       OVER(PARTITION BY team_code ORDER BY game_date ASC ROWS BETWEEN 4 PRECEDING AND 1 PRECEDING)AS opponents_shooting_efficiency,
  AVG(turnover_percent)
       OVER(PARTITION BY team_code ORDER BY game_date ASC ROWS BETWEEN 4 PRECEDING AND 1 PRECEDING) AS turnover_percent,
  AVG(opponents_turnover_percent)
       OVER(PARTITION BY team_code ORDER BY game_date ASC ROWS BETWEEN 4 PRECEDING AND 1 PRECEDING) AS opponents_turnover_percent,
  AVG(rebounding)
       OVER(PARTITION BY team_code ORDER BY game_date ASC ROWS BETWEEN 4 PRECEDING AND 1 PRECEDING) AS rebounding,
  AVG(opponents_rebounding) 
       OVER(PARTITION BY team_code ORDER BY game_date ASC ROWS BETWEEN 4 PRECEDING AND 1 PRECEDING) AS opponents_rebounding,
  AVG(freethrows) 
       OVER(PARTITION BY team_code ORDER BY game_date ASC ROWS BETWEEN 4 PRECEDING AND 1 PRECEDING) AS freethrows,
  AVG(opponents_freethrows) 
       OVER(PARTITION BY team_code ORDER BY game_date ASC ROWS BETWEEN 4 PRECEDING AND 1 PRECEDING) AS oppponents_freethrows,
  win
FROM all_games
)

SELECT * FROM prevgames
WHERE offensive_shooting_efficiency IS NOT NULL
%%bigquery 
SELECT * FROM ML.EVALUATE(MODEL lab_dev.four_factors_model)<jupyter_output><empty_output><jupyter_text>Based on just the teams' performance coming in, we can predict the outcome of games with a 69.4% accuracy.  More complex ML modelWe can write a more complex ML model using Keras and a deep neural network.The code is not that hard but you'll have to do a lot more work (scaling, hyperparameter tuning)to get better performance than you did with the BigQuery ML model.<jupyter_code>%%bigquery games
WITH all_games AS (
SELECT 
  game_date,
  team_code,
  is_home,
  SAFE_DIVIDE(fgm + 0.5 * fgm3,fga) AS offensive_shooting_efficiency,
  SAFE_DIVIDE(opp_fgm + 0.5 * opp_fgm3,opp_fga) AS opponents_shooting_efficiency,
  SAFE_DIVIDE(tov,fga+0.475*fta+tov-oreb) AS turnover_percent,
  SAFE_DIVIDE(opp_tov,opp_fga+0.475*opp_fta+opp_tov-opp_oreb) AS opponents_turnover_percent,
  SAFE_DIVIDE(oreb,oreb + opp_dreb) AS rebounding,
  SAFE_DIVIDE(opp_oreb,opp_oreb + dreb) AS opponents_rebounding,
  SAFE_DIVIDE(ftm,fga) AS freethrows,
  SAFE_DIVIDE(opp_ftm,opp_fga) AS opponents_freethrows,
  win
FROM lab_dev.team_box
WHERE fga IS NOT NULL and win IS NOT NULL
)

, prevgames AS (
SELECT 
  is_home,
  AVG(offensive_shooting_efficiency) 
       OVER(PARTITION BY team_code ORDER BY game_date ASC ROWS BETWEEN 4 PRECEDING AND 1 PRECEDING) AS offensive_shooting_efficiency,
  AVG(opponents_shooting_efficiency) 
       OVER(PARTITION BY team_code ORDER BY game_date ASC ROWS BETWEEN 4 PRECEDING AND 1 PRECEDING)AS opponents_shooting_efficiency,
  AVG(turnover_percent)
       OVER(PARTITION BY team_code ORDER BY game_date ASC ROWS BETWEEN 4 PRECEDING AND 1 PRECEDING) AS turnover_percent,
  AVG(opponents_turnover_percent)
       OVER(PARTITION BY team_code ORDER BY game_date ASC ROWS BETWEEN 4 PRECEDING AND 1 PRECEDING) AS opponents_turnover_percent,
  AVG(rebounding)
       OVER(PARTITION BY team_code ORDER BY game_date ASC ROWS BETWEEN 4 PRECEDING AND 1 PRECEDING) AS rebounding,
  AVG(opponents_rebounding) 
       OVER(PARTITION BY team_code ORDER BY game_date ASC ROWS BETWEEN 4 PRECEDING AND 1 PRECEDING) AS opponents_rebounding,
  AVG(freethrows) 
       OVER(PARTITION BY team_code ORDER BY game_date ASC ROWS BETWEEN 4 PRECEDING AND 1 PRECEDING) AS freethrows,
  AVG(opponents_freethrows) 
       OVER(PARTITION BY team_code ORDER BY game_date ASC ROWS BETWEEN 4 PRECEDING AND 1 PRECEDING) AS oppponents_freethrows,
  win
FROM all_games
)

SELECT * FROM prevgames
WHERE offensive_shooting_efficiency IS NOT NULL
import tensorflow as tf
import tensorflow.keras as keras
nrows = len(games)
ncols = len(games.iloc[0])
ntrain = (nrows * 7) // 10
print(nrows, ncols, ntrain)
# 0:ntrain are the training data; remaining rows are testing
# last col is the label
train_x = games.iloc[:ntrain, 0:(ncols-1)]
train_y = games.iloc[:ntrain, ncols-1]
test_x = games.iloc[ntrain:, 0:(ncols-1)]
test_y = games.iloc[ntrain:, ncols-1]
model = keras.models.Sequential()
model.add(keras.layers.Dense(5, input_dim=ncols-1, activation='relu'))
model.add(keras.layers.Dense(1, activation='sigmoid'))
model.compile(loss='binary_crossentropy', optimizer='adam', metrics=['accuracy'])
history = model.fit(train_x, train_y, epochs=5, batch_size=32)
score = model.evaluate(test_x, test_y, batch_size=512)
print(score)<jupyter_output>WARNING:tensorflow:From /usr/local/lib/python3.5/dist-packages/tensorflow/python/ops/math_ops.py:3066: to_int32 (from tensorflow.python.ops.math_ops) is deprecated and will be removed in a future version.
Instructions for updating:
Use tf.cast instead.
Epoch 1/5
169820/169820 [==============================] - 11s 65us/sample - loss: 0.6281 - acc: 0.6539
Epoch 2/5
169820/169820 [==============================] - 9s 53us/sample - loss: 0.5976 - acc: 0.6771
Epoch 3/5
169820/169820 [==============================] - 10s 56us/sample - loss: 0.5907 - acc: 0.6841
Epoch 4/5
169820/169820 [==============================] - 10s 58us/sample - loss: 0.5886 - acc: 0.6861
Epoch 5/5
169820/169820 [==============================] - 9s 52us/sample - loss: 0.5877 - acc: 0.6860
72780/72780 [==============================] - 0s 4us/sample - loss: 0.5494 - acc: 0.7151
[0.5493521325746779, 0.71512777]<jupyter_text>With a deep neural network, we are able to get 71.5% accuracy using the four factors model.<jupyter_code># Copyright 2019 Google Inc. All Rights Reserved.
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#     http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.0924092409240924, 'fraction_numerical': 0.02857785778577858, 'mean_word_length': 5.630034808552959, 'pattern_counts': {'":': 0, '<': 49, '<?xml version=': 0, '>': 53, 'https://': 2, 'lorem ipsum': 0, 'www.': 2, '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': '12', 'dir': 'jupyter-structured-clean-dedup', 'id': '166335', 'license': 'Apache-2.0', 'n_tokens_mistral': 5096, 'n_tokens_neox': 4582, 'n_words': 1371, 'path': 'blogs/ncaa/ncaa_feateng_solution.ipynb', 'repo_name': 'Glairly/introduction_to_tensorflow', 'size': '13337'}