<jupyter_start><jupyter_text>Customize Your Lineup - Trial CodeGet the python/pandas logic working for the customize page before incorporating it into the flask app/js/html.<jupyter_code>import pandas as pd
import numpy as np
from scipy import stats

# #try using plotly for graphing - should help transition to later javascript graphs
# import plotly
# import plotly.plotly as py
# import plotly.figure_factory as ff
# import plotly.graph_objs as go
# #need to use Plotly API Key in order to get it to run
# plotly.tools.set_credentials_file(username='mtsuomi', api_key='<KEY>')

from sqlalchemy.orm import Session
from sqlalchemy import create_engine, inspect, func
from sqlalchemy.ext.automap import automap_base
import json
#create engine to the database
db = create_engine('sqlite:///../fantasy_football_2018.db')

#create inspector so can see schema of database visually
inspector = inspect(db)

#Database Schema - verify the database has the info we are expecting by printing out all the schema visually
for table_name in inspector.get_table_names():
    for column in inspector.get_columns(table_name):
        print(table_name, column.get('name'), column.get('type'))
    print()

#other options for using SQLAlchemy sort of coding instead of traditional SQL queries
# engine = create_engine('sqlite:///raw_data/hawaii.sqlite')
# Base = automap_base()
# Base.prepare(engine, reflect=True)
# session = Session(bind=engine)
# inspector = inspect(engine)<jupyter_output>def_scores Unnamed: 0 BIGINT
def_scores PLAYER, TEAM POS TEXT
def_scores team_abbr TEXT
def_scores week_2_opp TEXT
def_scores week_2_proj FLOAT
def_scores week_2_actual BIGINT
def_scores week_3_opp TEXT
def_scores week_3_proj FLOAT
def_scores binned TEXT
def_scores score_sentiment TEXT

week1_ppr_actuals PLAYER TEXT
week1_ppr_actuals POS TEXT
week1_ppr_actuals TEAM TEXT
week1_ppr_actuals FPTS_PPR_ACTUAL FLOAT
week1_ppr_actuals WEEK BIGINT
week1_ppr_actuals PK INTEGER

week1_ppr_projections PLAYER TEXT
week1_ppr_projections POS TEXT
week1_ppr_projections TEAM TEXT
week1_ppr_projections FPTS_PPR_ESPN FLOAT
week1_ppr_projections FPTS_PPR_CBS FLOAT
week1_ppr_projections FPTS_PPR_SHARKS FLOAT
week1_ppr_projections FPTS_PPR_SCOUT FLOAT
week1_ppr_projections FPTS_PPR_PRVS_WK_ACTUAL FLOAT
week1_ppr_projections PK INTEGER

week1_ppr_projections_all_scraped PLAYER TEXT
week1_ppr_projections_all_scraped POS TEXT
week1_ppr_projections_all_scraped TEAM TEXT
week1_ppr_projections_all_scraped FPTS_PP[...]<jupyter_text>Customize Projection Average<jupyter_code>week = 3
espn_wt = 15
cbs_wt = 40
sharks_wt = 15
scout_wt = 0
prior_wt = 30
def_boost_wt = 'Full'
overunder_boost_wt = 'Partial'
twitter_boost_wt = 'None'

#convert weights to percentages
espn_pct = espn_wt/100
cbs_pct = cbs_wt/100
sharks_pct = sharks_wt/100
scout_pct = scout_wt/100
prior_pct = prior_wt/100
#get the projections for your week selected
stmt = f"""
        SELECT *
        FROM week{week}_ppr_projections
       """

#need to input db.session.bind in flask app due to different initalization process
df_proj = pd.read_sql_query(stmt, db)
print(df_proj.shape)
df_proj.head()
df_proj.columns
#create initial custom weighted average of the five projection sources based on user input
df_proj['FPTS_PPR_CUSTOM_AVG'] = df_proj.apply(lambda row: 
                                    (row['FPTS_PPR_ESPN']*espn_pct +
                                    row['FPTS_PPR_CBS']*cbs_pct +
                                    row['FPTS_PPR_SHARKS']*sharks_pct +
                                    row['FPTS_PPR_SCOUT']*scout_pct +
                                    row['FPTS_PPR_PRVS_WK_ACTUAL']*prior_pct),
                                    axis='columns')
df_proj.head()<jupyter_output><empty_output><jupyter_text>Boost Factor Logic - Import Data (later DB connection), Merge Data, then Do Logic<jupyter_code># ### FIGURE OUT UPDATED DB CONNECTION LATER ###
# #import the data for now
# df_opp_defense = pd.read_excel('../def_scoring_proj.xlsx')
# df_overunder = pd.read_csv('../week_2_over_unders.csv')
# df_tweets = pd.read_csv('../expert_tweets_week_3.csv')
#get the defense data for your week selected - all defense data for each week is one database,
#select only columns you want
#NOTE - currently it is pulling in defense sentiment always for week 3 - no sentiments for other weeks
stmt_def = f"""
        SELECT team_abbr, week_{week}_opp, week_{week}_proj, score_sentiment
        FROM def_scores
       """

#need to input db.session.bind in flask app due to different initalization process
df_defense = pd.read_sql_query(stmt_def, db)

#currently score_sentiment is for the offensive opponenet perspective so Min's really good Defense
#against Buf has score sentiment of 'Terrible' which is terrible for Buf, so all Buf players
#would get negative points weighting - rename column
#rename team_abbr to def_team_abbr
df_defense.rename(columns={'score_sentiment': 'offense_opp_def_impact',
                           'team_abbr': 'def_team_abbr'}, inplace=True)

print(df_defense.shape)
df_defense.head()
#get the overunder data for your week selected
#select only columns you want
stmt_overunder = f"""
        SELECT *
        FROM week_{week}_over_unders
       """

#need to input db.session.bind in flask app due to different initalization process
df_overunder = pd.read_sql_query(stmt_overunder, db)
print(df_overunder.shape)
df_overunder.head()
#get the tweet data for your week selected
#select only columns you want
stmt_tweets = f"""
        SELECT *
        FROM week_{week}_tweets
       """

#need to input db.session.bind in flask app due to different initalization process
df_tweets = pd.read_sql_query(stmt_tweets, db)
print(df_tweets.shape)
df_tweets.head()
# #Connor's code for binning the over-unders
# high = df_overunder['over_under'].max()
# print(high)
# low = df_overunder['over_under'].min()
# print(low)
# mean = df_overunder['over_under'].mean()
# print(mean)

# #create bins from range of over unders
# bins = [(low-.5), ((low+low+mean)/3), ((low+mean+mean)/3), ((high+mean+mean)/3), ((high+high+mean)/3), (high+.5)]
# df_overunder['binned'] = pd.cut(df_overunder['over_under'], bins)
# df_overunder.head()

# #convert binned values to string so we can categorize with adjectives
# df_overunder['binned'] = df_overunder['binned'].map(lambda x: str(x))

# #create adjectives from bins
# score_bins = {
#     '(42.0, 43.521]': 'Terrible',
#     '(43.521, 44.542]': 'Concerned',
#     '(44.542, 48.208]': 'Neutral',
#     '(48.208, 50.854]': 'Good',
#     '(50.854, 54.0]': 'Awesome'
# }

# df_overunder['score_sentiment'] = df_overunder['binned'].map(score_bins)

# color_bins = {
#     'Terrible': 'red',
#     'Concerned': 'yellow',
#     'Neutral': 'grey',
#     'Good': 'blue',
#     'Awesome': 'green'
# }

# df_overunder['color'] = df_overunder['score_sentiment'].map(color_bins)
# df_overunder
#merge on the boost factor data to players
#data: opposing defense, opposing defense bin, twitter sentiment, twitter sentiment bin, vegas overunder, vegas overunder bin

#check size of starting df
print(df_proj.shape)

#first merge in the twitter sentiment - always do left merge (rather have Nans for boost factors than lose players)
df_boosts = pd.merge(df_proj, df_tweets[['player', 'compound_score', 'expert_sentiment']],
                     how='left', left_on='PLAYER', right_on='player')
df_boosts.drop('player', axis='columns', inplace=True) #don't need to keep this added player name column
print(df_boosts.shape)

#second merge in the overunder - always do left merge (rather have Nans for boost factors than lose players)
df_boosts = pd.merge(df_boosts, df_overunder[['team_abbr', 'over_under', 'score_sentiment']],
                     how='left', left_on='TEAM', right_on='team_abbr')
df_boosts.drop('team_abbr', axis='columns', inplace=True) #don't need to keep this added team abbr column
print(df_boosts.shape)

#third merge in the defense projections - always do left merge (rather have Nans for boost factors than lose players)
#will merge by the team of the datframe and in defense looking for the opponent (that is offensive opponent of the defense listed as team)
df_boosts = pd.merge(df_boosts, df_defense[['def_team_abbr', f"week_{week}_opp", f"week_{week}_proj", 'offense_opp_def_impact']],
                     how='left', left_on='TEAM', right_on=f"week_{week}_opp")
df_boosts.drop(f"week_{week}_opp", axis='columns', inplace=True) #don't need to keep this added team abbr column
print(df_boosts.shape)

#convert any of the Nans in our binned sentiment columns to Neutral (that will just apply a zero boost to those players)
df_boosts.fillna('Neutral', inplace=True)

df_boosts.head(30)
# #apply defensive boost factor
# # def_boost = 'Full'
# # overunder_boost = 'Partial'
# # twitter_boost = 'None'

# player = '<NAME>'
# team = 'NO'
# custom_points = 20.16
# expert_sentiment = 'Concerned'
# score_sentiment = 'Good'
# defense_sentiment = 'Terrible'
def over_under_boost_calculator(row, user_boost_weight):
    #define the user's boost multiplier based on their selection of how much of the factor to apply
    if user_boost_weight == 'Full':
        user_boost_multiplier = 1.0
    elif user_boost_weight == 'Partial':
        user_boost_multiplier = 0.5
    else:
        user_boost_multiplier = 0
        
    #these are our custom boost factors based on our o/u score sentiments (can change these on the backend however we want)
    over_under_boost_values = {'Awesome': 0.2, 'Good': 0.1, 'Neutral': 0, 'Concerned': -0.1, 'Terrible': -0.2}
    
    #get the starting custom weighted average fantasy points projections by the user
    custom_points = row['FPTS_PPR_CUSTOM_AVG']
    
    #get boost multiplier based on the overunder binned sentiments and our dictionary of boost factors
    boost_multiplier = over_under_boost_values.get(row['score_sentiment'], 0)
    
    #calculate the boost points
    over_under_boost_points = custom_points * boost_multiplier * user_boost_multiplier
    
    return over_under_boost_points
def twitter_boost_calculator(row, user_boost_weight):
    #define the user's boost multiplier based on their selection of how much of the factor to apply
    if user_boost_weight == 'Full':
        user_boost_multiplier = 1.0
    elif user_boost_weight == 'Partial':
        user_boost_multiplier = 0.5
    else:
        user_boost_multiplier = 0
        
    #these are our custom boost factors based on our twitter sentiments (can change these on the backend however we want)
    twitter_boost_values = {'Awesome': 0.2, 'Good': 0.1, 'Neutral': 0, 'Concerned': -0.1, 'Terrible': -0.2}
    
    #get the starting custom weighted average fantasy points projections by the user
    custom_points = row['FPTS_PPR_CUSTOM_AVG']
    
    #get boost multiplier based on the overunder binned sentiments and our dictionary of boost factors
    boost_multiplier = twitter_boost_values.get(row['expert_sentiment'], 0)
    
    #calculate the boost points
    twitter_boost_points = custom_points * boost_multiplier * user_boost_multiplier
    
    return twitter_boost_points
def defense_boost_calculator(row, user_boost_weight):
    #if the player looking it as a Defense, we will want to skip and not boost the defense projection
    if row['POS'] == 'D/ST':
        return 0.0
    
    #otherwise apply the defense boost to all other positions (may in future want to consider removing kickers as
    #they are often unpredictable, and maybe the correlation of a harder defense is even positive on kicker points b/c they kick more FGs than TDs)
    else:
        #define the user's boost multiplier based on their selection of how much of the factor to apply
        if user_boost_weight == 'Full':
            user_boost_multiplier = 1.0
        elif user_boost_weight == 'Partial':
            user_boost_multiplier = 0.5
        else:
            user_boost_multiplier = 0

        #these are our custom boost factors based on our opposing defenses(can change these on the backend however we want)
        #the sentiment stored is from the offensive perspective of the opp def impact, so 'Good' means they 
        #are facing an easy defense and will get a positive boost, 'Terrible' means they are facing a hard 
        #defense and will get negative boost
        opp_defense_boost_values = {'Awesome': 0.2, 'Good': 0.1, 'Neutral': 0, 'Concerned': -0.1, 'Terrible': -0.2}

        #get the starting custom weighted average fantasy points projections by the user
        custom_points = row['FPTS_PPR_CUSTOM_AVG']

        #get boost multiplier based on the overunder binned sentiments and our dictionary of boost factors
        boost_multiplier = opp_defense_boost_values.get(row['offense_opp_def_impact'], 0)

        #calculate the boost points
        defense_boost_points = custom_points * boost_multiplier * user_boost_multiplier

        return defense_boost_points
print(overunder_boost_wt)
print(twitter_boost_wt)
print(def_boost_wt)
df_boosts['OVER_UNDER_BOOST'] = df_boosts.apply(lambda row: 
                                    over_under_boost_calculator(row, overunder_boost_wt),
                                    axis='columns')
df_boosts['TWITTER_BOOST'] = df_boosts.apply(lambda row: 
                                    twitter_boost_calculator(row, twitter_boost_wt),
                                    axis='columns')
df_boosts['OPPOSING_DEFENSE_BOOST'] = df_boosts.apply(lambda row: 
                                        defense_boost_calculator(row, def_boost_wt),
                                        axis='columns')
df_boosts.head()
df_boosts.columns
#calculate final FPTS ADDING UP ALL BOOST PTS TO THE CUSTOM AVG

df_boosts['FPTS_PPR_CUSTOM_AVG_BOOST'] = df_boosts[['FPTS_PPR_CUSTOM_AVG', 'OPPOSING_DEFENSE_BOOST',
                                                    'OVER_UNDER_BOOST', 'TWITTER_BOOST']].sum(axis='columns') 
df_boosts
#reorder dataframe and limits columns for the final output data and sort descending by custom poitns
df_boosts_output = df_boosts[['PLAYER', 'POS', 'TEAM', 'FPTS_PPR_CUSTOM_AVG_BOOST',
                              'FPTS_PPR_ESPN', 'FPTS_PPR_CBS', 'FPTS_PPR_SHARKS', 'FPTS_PPR_SCOUT',
                              'FPTS_PPR_PRVS_WK_ACTUAL', 'FPTS_PPR_CUSTOM_AVG', 
                              'OVER_UNDER_BOOST', 'TWITTER_BOOST',
                              'OPPOSING_DEFENSE_BOOST']].sort_values('FPTS_PPR_CUSTOM_AVG_BOOST',
                                                                    ascending=False)
df_boosts_output
df_tweets.columns
df_overunder2 = pd.DataFrame(columns=['Unnamed: 0', 'team', 'over_under', 'team_abbr', 'binned',
       'score_sentiment'])
df_overunder2<jupyter_output><empty_output><jupyter_text>Converting Our DF to a JSON File !!!Don't use .to_json it makes it all a string, better way is use .to_dict('records')Note: when you return the json in flask, you'll also want to jsonify it to make sure it is formatted well.  return jsonify(json)<jupyter_code>#create json that is a list of dictionaries, where each row is its own dictionary with column:value pairs
df_week1_ppr_projections.head(10).to_json('filename_test', orient='records')

json_test = df_week1_ppr_projections.head(10).to_json(orient='records')
jsonfiles = json.loads(json_test)

jsonfiles
jsonify(json)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08827936673398919, 'fraction_numerical': 0.011140790931005277, 'mean_word_length': 3.8560582094273963, 'pattern_counts': {'":': 0, '<': 18, '<?xml version=': 0, '>': 18, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 16, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '223211', 'license': 'MIT', 'n_tokens_mistral': 5187, 'n_tokens_neox': 4841, 'n_words': 1426, 'path': 'data/exploratory_analysis/Customize Your Lineup - Trial Code.ipynb', 'repo_name': 'zgscherrer/Project-Fantasy-Football', 'size': '15370'}