def structure_constraints(structureid, platform, description): str_qry = """select structureid from structureConstraints where structureid = ?""" qry_df = dbMgr.query(str_qry, (structureid,)) constraint_df = None if len(qry_df['structureid']) == 0: if platform == 'fanduel': if description == 'all week without kicker': list_constraints = [("bound", "<=", None, 1), ("bound", ">=", None, 0), ("dot", "==", 'DST', 1), ("dot", "==", 'QB', 1), ("dot", ">=", 'RB', 2), ("dot", "<=", 'RB', 3), ("dot", ">=", 'WR', 3), ("dot", "<=", 'WR', 4), ("dot", ">=", 'TE', 1), ("dot", "<=", 'TE', 2), ("dot", "==", 'Flex', 7), ("dot", "<=", 'salary', 60000)] elif description == 'all week with kicker': list_constraints = [("bound", "<=", None, 1), ("bound", ">=", None, 0), ("dot", "==", 'DST', 1), ("dot", "==", 'QB', 1), ("dot", "==", 'RB', 2), ("dot", "==", 'WR', 3), ("dot", "==", 'TE', 1), ("dot", "<=", 'salary', 60000), ("dot", "==", 'K', 1)] elif platform == 'draftkings': list_constraints = [("bound", "<=", None, 1), ("bound", ">=", None, 0), ("dot", "==", 'DST', 1), ("dot", "==", 'QB', 1), ("dot", ">=", 'RB', 2), ("dot", "<=", 'RB', 3), ("dot", ">=", 'WR', 3), ("dot", "<=", 'WR', 4), ("dot", ">=", 'TE', 1), ("dot", "<=", 'TE', 2), ("dot", "==", 'Flex', 7), ("dot", "<=", 'salary', 50000)] elif platform == 'yahoo': list_constraints = [("bound", "<=", None, 1), ("bound", ">=", None, 0), ("dot", "==", 'DST', 1), ("dot", "==", 'QB', 1), ("dot", ">=", 'RB', 2), ("dot", "<=", 'RB', 3), ("dot", ">=", 'WR', 3), ("dot", "<=", 'WR', 4), ("dot", ">=", 'TE', 1), ("dot", "<=", 'TE', 2), ("dot", "==", 'Flex', 7), ("dot", "<=", 'salary', 200)] constraint_df = pd.DataFrame(list_constraints) if constraint_df is not None: constraint_df.columns = ['type', 'operator', 'vec', 'bound'] constraint_df['structureid'] = structureid dbMgr.df_insert(constraint_df, 'structureConstraints', True) return None
def load_projections(historical=False): for proj_source, proj_class in projections_sources.items(): if historical: all_data = pd.DataFrame() for year in years: for week in weeks: exist_df = dbMgr.query("""select playerid from projections proj join time t on proj.timeid = t.timeid where season = {} and week = {} and source = '{}'""".format(year, week, proj_source)) if len(exist_df) == 0: platform_df = proj_class.get_projections(year, week) if platform_df is not None: platform_df['timeid'] = exp.time_search(year, week) platform_df['source'] = proj_source all_data = all_data.append(platform_df, ignore_index=True, sort=False) else: 1 + 1 if len(all_data) > 0: all_data['playerid'] = all_data.apply(lambda x: exp.player_search(x.PLAYER, dst_mode=(x.position == 'DST'), filters={'position': x.position, 'year': x.year, 'team': x.TEAM}, auto_insert=True, source=proj_source, sourceid=x.SOURCE, allow_missing=True), axis=1) all_data = all_data[all_data['playerid'].notnull()] stats = list(set(sum(proj_class.positions.values(), [])).union({'FPTS_HALF', 'FPTS_STD', 'FPTS_FULL'})) stacked_df = all_data.groupby(['timeid', 'playerid', 'source'])[stats].mean().stack().dropna().reset_index() stacked_df.columns = ['timeid', 'playerid', 'source', 'statistic', 'value'] dbMgr.df_insert(stacked_df, 'projections', True) return None
def load_ownership(historical=False): if historical: dates = dbMgr.query("""select distinct date, season from games g join time t on g.timeid = t.timeid order by date""") all_dates = dates[dates['date'] > '2016-09-10'].set_index('date', verify_integrity=True)['season'] import pickle with open('ownership.pickle', 'rb') as handle: test_data = pickle.load(handle) day_df = {} for key in test_data.keys(): if (test_data[key][0] is not None) and (pd.to_datetime(key).day_name() == 'Sunday'): possible_keys = [x for x in test_data[key][0].keys() if ('Classic' in x or 'SalaryCap' in x) and '1:00 pm' in x] if len(possible_keys) > 0: temp_max = 0 temp_contest = None for pk in possible_keys: if test_data[key][0][pk].shape[0] > temp_max: temp_max = test_data[key][0][pk].shape[0] temp_contest = pk if temp_contest is not None: if len(test_data[key][1][temp_contest]) > 100: day_df[key] = temp_contest for dd, val in day_df.items(): contest_df = test_data[dd][0][val] ownership_df = test_data[dd][1][val] ownership_df.columns = [x.replace('$', '').replace(',', '') for x in ownership_df.columns] exclude_contests = contest_df['Name'].value_counts()[contest_df['Name'].value_counts() > 1].index filtered_df = ownership_df[ownership_df['Avg'] > 0.0001].drop(exclude_contests, axis=1, errors='ignore') valid_contests = [x for x in filtered_df.columns if x not in ['Player', 'Pos', 'Avg', 'Fpts']] valid_df = contest_df[ contest_df['Name'].isin(valid_contests) & -contest_df['Name'].isin(exclude_contests)].drop_duplicates() timeid = exp.time_search(date=dd) structureid = exp.structure_search('draftkings', 'FULL', 'all week without kicker') valid_df['contestid'] = valid_df.apply(lambda x: exp.contest_insert(x.Link, timeid, structureid), axis=1) link_map = valid_df.set_index('Name')['contestid'] pivoted_df = valid_df.set_index('contestid').drop(['Name', 'Link', 'Winner'], axis=1).unstack().reset_index() pivoted_df.columns = ['stat', 'contestid', 'value'] dbMgr.df_insert(pivoted_df[['contestid', 'stat', 'value']], 'contestStats', True) filtered_df = filtered_df[['Player', 'Pos', 'Avg', 'Fpts']+list(link_map.keys())] filtered_df.columns = [link_map[x] if x in valid_contests else x for x in filtered_df.columns] filtered_df['playerid'] = filtered_df.apply( lambda x: exp.player_search(x.Player, dst_mode=(x.Pos == 'DST'), filters={'position': x.Pos, 'year': all_dates[dd], 'points': x.Fpts, 'platform': 'dk', 'date': dd}, auto_insert=True, allow_missing=True), axis=1) player_df = filtered_df.set_index('playerid').drop(['Player', 'Pos', 'Avg', 'Fpts'], axis=1) stacked_df = player_df.replace(0.0, np.nan).unstack().dropna().reset_index().drop_duplicates() stacked_df.columns = ['contestid', 'playerid', 'value'] dbMgr.df_insert(stacked_df[['contestid', 'playerid', 'value']], 'contestOwnership', True) else: 1 + 1 return None
def load_game_logs(historical=False): if historical: players_df = dbMgr.query("""select playerid, link, name from players where playerid not in ( select distinct playerid from playsForTeam)""") for ix, row in players_df.iterrows(): team = False if row['link'].startswith('https://www.pro-football-reference.com/teams/'): team = True gamelog_df, position = pfr.get_game_log(row['link']) if len(gamelog_df) > 0: gamelog_df['playerid'] = row['playerid'] gamelog_df['timeid'] = gamelog_df.apply(lambda x: exp.time_search(x.year_id, x.week_num), axis=1) gamelog_df['teamid'] = gamelog_df['team'].apply(lambda x: exp.team_search(x)) gamelog_df['oppid'] = gamelog_df['opp'].apply(lambda x: exp.team_search(x)) gamelog_df['hometeamid'] = gamelog_df.apply(lambda x: exp.team_search(x.opp) if x.game_location == '@' else exp.team_search(x.team), axis=1) gamelog_df['awayteamid'] = gamelog_df.apply(lambda x: exp.team_search(x.team) if x.game_location == '@' else exp.team_search(x.opp), axis=1) if team: gamelog_df['gameid'] = gamelog_df.apply(lambda x: exp.game_insert(x.boxscore_word_link, x.timeid, x.hometeamid, x.awayteamid, x.game_date), axis=1) else: gamelog_df['gameid'] = gamelog_df.apply(lambda x: exp.game_insert(x.game_result_link, x.timeid, x.hometeamid, x.awayteamid, x.game_date), axis=1) dbMgr.df_insert(gamelog_df[['gameid', 'playerid', 'teamid', 'oppid']], 'playsForTeam', True) if not team: stats = ['age', 'fumbles', 'fumbles_forced', 'fumbles_lost', 'fumbles_rec_td', 'fumbles_rec_yds', 'pass_att', 'pass_cmp', 'pass_int', 'pass_rating', 'pass_sacked', 'pass_sacked_yds', 'pass_td', 'pass_yds', 'rec', 'rec_td', 'rec_yds', 'rush_att', 'rush_td', 'rush_yds', 'targets'] indexer = ['gameid', 'playerid'] stacked_df = gamelog_df.reindex(columns=(stats + indexer)).astype(float).groupby( ['gameid', 'playerid']).mean().stack().dropna().reset_index() stacked_df.columns = ['gameid', 'playerid', 'stat', 'value'] dbMgr.df_insert(stacked_df, 'gameLog', True) else: 1 + 1 return None
def load_salaries(historical=False): if historical: all_data = pd.DataFrame() for year in years: for week in weeks: for plat in platforms: platform_df = rg.get_platform_data(plat, year, week) if platform_df is not None: platform_df['time_id'] = exp.time_search(year, week) if plat == 'draftkings': scoring_mode = 'FULL' elif plat in ('yahoo', 'fanduel'): scoring_mode = 'HALF' if (year < 2018) and (plat == 'fanduel'): platform_df['structureid'] = exp.structure_search(plat, scoring_mode, 'all week with kicker') else: platform_df['structureid'] = exp.structure_search(plat, scoring_mode, 'all week without kicker') all_data = all_data.append(platform_df, ignore_index=True, sort=False) else: 1 + 1 loop_df = {} for ix, row in all_data.iterrows(): row_df = {} dst_mode = row['Pos'] == 'DST' filters = {'year': row['Year'], 'team': row['Team'], 'position': row['Pos']} row_df['playerid'] = exp.player_search(row['Name'], dst_mode=dst_mode, filters=filters, auto_insert=True) row_df['structureid'] = row['structureid'] row_df['timeid'] = row['time_id'] row_df['position'] = row['Pos'] row_df['salary'] = row['salary'] row_df['points'] = row['points'] loop_df[ix] = row_df loop_df = pd.DataFrame(loop_df).T dbMgr.df_insert(loop_df, 'contestPlayers', True) return None