<jupyter_start><jupyter_text><jupyter_code>import matplotlib.pyplot as plt
import pandas as pd
import numpy as np
from google.cloud import bigquery
from google.cloud import bigquery_storage
from datetime import datetime
import os
import pyarrow
import firebase_admin
from firebase_admin import credentials as cred
from firebase_admin import firestore
# from google.colab import auth
# auth.authenticate_user()
# print('Authenticated')
my_project_id = "nba-predictions-dev"
raw_game_data_table = 'nba.raw_basketballreference_game'
raw_player_data_table = 'nba.raw_basketballreference_playerbox'
%env GOOGLE_APPLICATION_CREDENTIALS C:\GCP\nba-predictions-dev-d76075f0a064.json
%env GOOGLE_CLOUD_PROJECT nba-predictions-dev
credentials = os.environ.get("GOOGLE_APPLICATION_CREDENTIALS")
client = bigquery.Client(project=my_project_id)
# %%bigquery --project nba-predictions-dev game
#   SELECT * from `nba.raw_basketballreference_game`
# %%bigquery --project nba-predictions-dev player
#   SELECT * from `nba.raw_basketballreference_playerbox`
game = client.query('''
  SELECT
    *
  FROM
    `%s`
''' % (raw_game_data_table)).to_dataframe()
player = client.query('''
  SELECT
    *
  FROM
    `%s`
''' % (raw_player_data_table)).to_dataframe()
game_original = game.copy()
player_original = player.copy()
game[game['game_date']==game['game_date'].max()]
game.head()
player.head()
game.describe()
player.describe()
player.info()
game['home_spread'] = game['home_pts'].astype(int) - game['visitor_pts'].astype(int)
game['home_spread'].hist()
game['season'] = ''
for i in range(len(game)):
  if ((game['game_date'][i].year != 2020 and game['game_date'][i].month < 7) or (game['game_date'][i].year == 2020 and game['game_date'][i].month < 11)):
    game.loc[i,'season'] = game['game_date'][i].year
  else:
    game.loc[i,'season'] = game['game_date'][i].year + 1

game[game['season']=='']
game.head()
game.info()
games_by_team_home = pd.DataFrame()
games_by_team_home['season'] = game['season']
games_by_team_home['game_key'] = game['game_key'] + 'h'
games_by_team_home['game_date'] = pd.to_datetime(game['game_date'])
games_by_team_home['team'] = game['home_team_name']
games_by_team_home['opponent'] = game['visitor_team_name']
games_by_team_home['is_home_team'] = 1
games_by_team_home['spread'] = game['home_spread']
games_by_team_home['pace'] = game['h_ff_pace']
games_by_team_home['efg_pct'] = game['h_ff_efg_pct']
games_by_team_home['tov_pct'] = game['h_ff_tov_pct']
games_by_team_home['orb_pct'] = game['h_ff_tov_pct']
games_by_team_home['ft_rate'] = game['h_ff_ft_rate']
games_by_team_home['off_rtg'] = game['h_ff_off_rtg']
games_by_team_home['opponent_efg_pct'] = game['a_ff_efg_pct']
games_by_team_home['opponent_tov_pct'] = game['a_ff_tov_pct']
games_by_team_home['opponent_orb_pct'] = game['a_ff_orb_pct']
games_by_team_home['opponent_ft_rate'] = game['a_ff_ft_rate']
games_by_team_home['opponent_off_rtg'] = game['a_ff_off_rtg']


games_by_team_home['is_win'] = ''
for i in range(len(game)):
  games_by_team_home.loc[i,'is_win'] = 1 if game['home_spread'][i].astype(int) > 0 else 0
games_by_team_home.info()
games_by_team_visitor = pd.DataFrame()
games_by_team_visitor ['season'] = game['season']
games_by_team_visitor ['game_key'] = game['game_key'] + 'a'
games_by_team_visitor ['game_date'] = pd.to_datetime(game['game_date'])
games_by_team_visitor ['team'] = game['visitor_team_name']
games_by_team_visitor ['opponent'] = game['home_team_name']
games_by_team_visitor ['is_home_team'] = 0
games_by_team_visitor ['spread'] = game['home_spread']*-1
games_by_team_visitor ['pace'] = game['a_ff_pace']
games_by_team_visitor['efg_pct'] = game['a_ff_efg_pct']
games_by_team_visitor['tov_pct'] = game['a_ff_tov_pct']
games_by_team_visitor['orb_pct'] = game['a_ff_tov_pct']
games_by_team_visitor['ft_rate'] = game['a_ff_ft_rate']
games_by_team_visitor['off_rtg'] = game['a_ff_off_rtg']
games_by_team_visitor['opponent_efg_pct'] = game['h_ff_efg_pct']
games_by_team_visitor['opponent_tov_pct'] = game['h_ff_tov_pct']
games_by_team_visitor['opponent_orb_pct'] = game['h_ff_orb_pct']
games_by_team_visitor['opponent_ft_rate'] = game['h_ff_ft_rate']
games_by_team_visitor['opponent_off_rtg'] = game['h_ff_off_rtg']

games_by_team_visitor['is_win'] = ''
for i in range(len(game)):
  games_by_team_visitor.loc[i,'is_win'] = 1 if game['home_spread'][i].astype(int) < 0 else 0
games_by_team_visitor.info()
games_by_team = pd.concat([games_by_team_home,games_by_team_visitor])
games_by_team['is_win'].unique()
games_by_team.info()
games_by_team[games_by_team['is_win']=='']
player.head()
player['game_key'] = player['game_key'] + player['h_or_a']
player = player.dropna(subset=['mp', 'plus_minus']).reset_index(drop=True)
player['plus_minus'] = player['plus_minus'].astype(int)
def convert_to_seconds(x):
  sp = int(x.split(':')[0]) * 60 + int(x.split(':')[1])
  return sp
player['seconds_played'] = player['mp'].apply(convert_to_seconds)
player['seconds_played'] = player['seconds_played'].astype(int)
game_player_stats = pd.DataFrame()
game_player_stats['game_key'] = player['game_key'].unique()
game_player_stats.head()
a = player.groupby(['game_key'])['seconds_played'].sum()
b = player[player['starter_flag']==True].groupby(['game_key'])['seconds_played'].sum()
seconds = pd.merge(a, b, left_index=True, right_index=True, how='inner')
seconds['starter_minutes_played_proportion'] = seconds['seconds_played_y']/seconds['seconds_played_x']
seconds.head()
game_player_stats.set_index('game_key',inplace=True)
game_player_stats = pd.merge(game_player_stats,seconds['starter_minutes_played_proportion'],left_index=True,right_index=True,how='inner')
game_player_stats.head()
bench_pl_min = player[player['starter_flag']==False].groupby(['game_key'])['plus_minus'].sum()
game_player_stats = pd.merge(game_player_stats,bench_pl_min, left_index=True, right_index=True, how='inner')
game_player_stats = game_player_stats.rename(columns={'plus_minus':'bench_plus_minus'})
game_player_stats.head()
games_by_team.info()
games_by_team.set_index('game_key', inplace=True)
games_by_team = pd.merge(games_by_team,game_player_stats, left_index=True, right_index=True,how='inner')
games_by_team.head()
game_player_stats
game_player_stats_opponent = game_player_stats.copy()
game_player_stats_opponent.reset_index(drop=False, inplace=True)
def switch_key(key):
  new_key = key[:-1] + ('h' if key[-1] == 'a' else 'a')
  return new_key
game_player_stats_opponent['game_key'] = game_player_stats_opponent['game_key'].apply(switch_key)
game_player_stats_opponent = game_player_stats_opponent.rename(columns={'starter_minutes_played_proportion':'opponnent_starter_minutes_played_proportion','bench_plus_minus':'opponent_bench_plus_minus'})
game_player_stats_opponent.set_index('game_key', inplace=True)
games_by_team = pd.merge(games_by_team,game_player_stats_opponent,left_index=True,right_index=True,how='inner')
games_by_team.head()
games_by_team.loc[(games_by_team['team']=='Atlanta Hawks') & (games_by_team['season']==2019)]
def generate_streak_info(data,column):
    """
    Parameters
    ----------
    data:
      Dataframe with a specific column to generate streak data

    column:
      Stirng with specific column name to generate streak info

    Returns
    -------

    data_with_streak_counter:
        The original dataframe with a new column
        `streak_counter_[column]` containing integers with 
        counts for each streak.
    """
    
    data['start_of_streak'] = data[column].ne(data[column].shift())
    data['streak_id'] = data.start_of_streak.cumsum()
    data[f'streak_counter_{column}'] = data.groupby('streak_id').cumcount() + 1
    data_with_streak_counter = data.drop(columns = ['start_of_streak','streak_id'] )
    return data_with_streak_counter
testing = games_by_team.loc[(games_by_team['team']=='Atlanta Hawks') & (games_by_team['season']==2019)].sort_values(by=['game_date'])
testing2 = games_by_team.loc[(games_by_team['team']=='Washington Wizards') & (games_by_team['season']==2019)].sort_values(by=['game_date'])
testing = generate_streak_info(testing,'is_win')
testing2 = generate_streak_info(testing2,'is_win')
# Make negative streak if losing streak, positive if winning streak
testing['streak_counter_is_win'] = [x * -1 if y == 0 else x for x,y in zip(testing['streak_counter_is_win'],testing['is_win'])]
testing['incoming_is_win_streak'] = testing['streak_counter_is_win'].shift(fill_value=0)
testing2['streak_counter_is_win'] = [x * -1 if y == 0 else x for x,y in zip(testing2['streak_counter_is_win'],testing2['is_win'])]
testing2['incoming_is_win_streak'] = testing2['streak_counter_is_win'].shift(fill_value=0)

test = pd.concat([testing, testing2])
test.head()
test.loc[(test['team']=='Washington Wizards') & (test['season']==2019)]
testing = games_by_team.loc[(games_by_team['team']=='Atlanta Hawks') & (games_by_team['season']==2019)].sort_values(by=['game_date'])
testing['pace'].unique()
testing['pace'] = testing['pace'].astype(float)
# Set W to number of days desired for moving average
W = 10
weights = np.arange(1,W+1)
testing['pace'].rolling(10).apply(lambda pace: np.dot(pace, weights)/weights.sum(), raw=True)
def create_linear_weighted_moving_average(data,column,W):
    """
    Parameters
    ----------
    data:
      Dataframe with a specific column to generate weighted moving average.

    column:
      Stirng with specific column name to generate weighted moving average info.
      Column must be ready to be converted to float data type.

    Returns
    -------

    data_with_moving_average:
        The original dataframe with a new column
        `wma_[W]_[column]` containing float values with weighted moving average
        values for the provided value with a weight of W.
    """  
    data_with_moving_average = data.copy()
    data_with_moving_average[column] = data_with_moving_average[column].astype(float)
    weights = np.arange(1,W+1) 
    data_with_moving_average[f'wma_{W}_{column}'] = data_with_moving_average[column].rolling(W).apply(lambda col: np.dot(col, weights)/weights.sum(), raw=True)
    return data_with_moving_average
testing4 = create_linear_weighted_moving_average(testing,'efg_pct',10)
testing4 = create_linear_weighted_moving_average(testing4,'bench_plus_minus',10)
testing4.head()
games_by_team_with_streak = pd.DataFrame()
wma_columns = ['pace',
       'efg_pct', 'tov_pct', 'ft_rate', 'off_rtg',
       'opponent_efg_pct', 'opponent_tov_pct', 'opponent_ft_rate',
       'opponent_off_rtg', 'starter_minutes_played_proportion',
       'bench_plus_minus', 'opponnent_starter_minutes_played_proportion',
       'opponent_bench_plus_minus']
W = 10
for team in games_by_team['team'].unique():
  team_games = games_by_team.loc[games_by_team['team']==team].sort_values(by='game_date')
  team_games = generate_streak_info(team_games,'is_win')
  team_games['streak_counter_is_win'] = [x * -1 if y == 0 else x for x,y in zip(team_games['streak_counter_is_win'],team_games['is_win'])]
  team_games['incoming_is_win_streak'] = team_games['streak_counter_is_win'].shift()
  for col in wma_columns:
    team_games = create_linear_weighted_moving_average(team_games,col,W)
    team_games[f'incoming_wma_{W}_{col}'] = team_games[f'wma_{W}_{col}'].shift()
  games_by_team_with_streak = pd.concat([games_by_team_with_streak, team_games])
games_by_team = games_by_team_with_streak.copy()
games_by_team.head(12)
games_by_team.columns
model_game_data = games_by_team.copy()
model_game_data.head()
model_game_data = games_by_team.dropna(subset=['incoming_wma_10_pace'])
model_game_data.info()
model_game_data['is_win'].unique()
model_game_data['season'].unique()
model_game_data = model_game_data.astype({'season': int, 'is_win': int})
model_game_data.reset_index(drop=False,inplace=True)
model_game_data.head()
model_game_data.columns
most_recent_game = model_game_data.sort_values('game_date').drop_duplicates(['team'],keep='last')
most_recent_game = most_recent_game[['season', 'game_date', 'team','streak_counter_is_win',
       'wma_10_pace', 'wma_10_efg_pct', 'wma_10_tov_pct', 'wma_10_ft_rate',
       'wma_10_off_rtg', 'wma_10_opponent_efg_pct', 'wma_10_opponent_tov_pct',
       'wma_10_opponent_ft_rate', 'wma_10_opponent_off_rtg',
       'wma_10_starter_minutes_played_proportion', 'wma_10_bench_plus_minus',
       'wma_10_opponnent_starter_minutes_played_proportion',
       'wma_10_opponent_bench_plus_minus']]
most_recent_game.reset_index(drop=True, inplace=True)
most_recent_game.set_index('team', inplace=True)
docs = most_recent_game.to_dict(orient='index')
most_recent_game.index.unique()
docs['<NAME>']
#!pip install google-cloud-secret-manager
credentials
default_app = firebase_admin.initialize_app()
db = firestore.client()
for team in most_recent_game.index.unique():
    doc_ref = db.collection('team_model_data').document(team)
    doc_ref.set(docs[team])
    print(docs[team])

docs = db.collection('team_model_data').stream()
teams = []
for doc in docs:
    teams.append(doc.id)
    #print(f'{doc.id} => {doc.to_dict()}')
teams
doc_ref = db.collection('team_model_data').document('<NAME>')

doc = doc_ref.get().to_dict()
doc

if doc.exists:
    print(f'Document data: {doc.to_dict()}')
else:
    print(u'No such document!')
client = bigquery.Client(project=my_project_id)
model_table_name = 'nba.model_game'

#Publish model data
job_config = bigquery.LoadJobConfig()
job_config.autodetect='True'
job_config.create_disposition = 'CREATE_IF_NEEDED'
#Truncate for now 
job_config.write_disposition = 'WRITE_TRUNCATE'
job_config.time_partitioning = bigquery.TimePartitioning(
    type_=bigquery.TimePartitioningType.DAY,
    field="game_date")
## Set schema for specific columns where more information is needed (e.g. not NULLABLE or specific date/time)
job_config.schema = [
    bigquery.SchemaField('game_key','STRING', 'REQUIRED'),
    bigquery.SchemaField('team','STRING', 'REQUIRED'),
    bigquery.SchemaField('opponent','STRING', 'REQUIRED'),
    bigquery.SchemaField('game_date','DATE'),
]
job_model = client.load_table_from_dataframe(model_game_data, model_table_name, job_config=job_config)

model_result = job_model.result()
model_message = (
    f'Job ID: {model_result.job_id} '
    f'was started {model_result.started} '
    f'and ended {model_result.ended} '
    f'loading {model_result.output_rows} row(s) '
    f'to {model_result.destination}')

print(model_message)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14682401943769524, 'fraction_numerical': 0.008469281499479347, 'mean_word_length': 7.678313253012048, 'pattern_counts': {'":': 0, '<': 10, '<?xml version=': 0, '>': 9, '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': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '237130', 'license': 'MIT', 'n_tokens_mistral': 5538, 'n_tokens_neox': 5254, 'n_words': 759, 'path': 'notebooks/model_data_creation_sandbox.ipynb', 'repo_name': 'cwilbar04/nba_predictions', 'size': '14425'}