<jupyter_start><jupyter_text>Table CreationLet's get some data into some SQL tables so we can use it.<jupyter_code># imports
import pandas as pd
import sqlite3 as sql

import os
import nflgame as nfl

from collections import defaultdict
con = sql.Connection("/Users/tstuessi/Dev/ff-analysis/fantasyfootball")
files = [
    "CBS_YEAR_2014",
    "CBS_YEAR_2015",
    "CBS_YEAR_2016",
    "CBS_YEAR_2017",
    "FLEAFLICKER_YEAR_2014",
    "FLEAFLICKER_YEAR_2015",
    "FLEAFLICKER_YEAR_2016",
    "FLEAFLICKER_YEAR_2017"
]
con = sql.Connection("/Users/tstuessi/Dev/ff-analysis/fantasyfootball.db")
for file in files:
    df = pd.read_csv("{}.csv".format(file), index_col="playerid")
    df["total"] = df.sum(axis=1)
    df.reset_index().to_sql(file, con, if_exists="replace",index=False)
con.close()<jupyter_output><empty_output><jupyter_text>Create the player statsThe db is strange in that it cannot show trades between teams and only updates to the most recent roster; we might have to do some manual investigation on that front.<jupyter_code>con = sql.Connection("/Users/tstuessi/Dev/ff-analysis/fantasyfootball.db")
for year in range(2014, 2018, 1):
    year_df = pd.DataFrame()
    i = 0
    for week in range(1,18, 1):
        games = nfl.games(year, week=week)
        players = nfl.combine_game_stats(games)
        
        dict_list = {}
        for p in players:
            tmp_dict = p.__dict__
            tmp_dict["position"] = p.__dict__["player"].position
            tmp_dict.pop("_stats", None)
            tmp_dict.pop("player", None)
            dict_list[i] = tmp_dict
            i += 1
        
        df = pd.DataFrame.from_dict(dict_list, orient="index")
        df["week"] = week
        year_df = year_df.append(df)
    
    year_df.fillna(0).to_sql("PLAYER_WEEKLY_STATS_YEAR_{}".format(year), con, if_exists="replace", index=False)
con.close()<jupyter_output>/Users/tstuessi/anaconda3/envs/ff_analysis/lib/python3.6/site-packages/pandas/core/frame.py:6211: FutureWarning: Sorting because non-concatenation axis is not aligned. A future version
of pandas will change to not sort by default.

To accept the future behavior, pass 'sort=False'.

To retain the current behavior and silence the warning, pass 'sort=True'.

  sort=sort)<jupyter_text>Create team position stats<jupyter_code>con = sql.Connection("/Users/tstuessi/Dev/ff-analysis/fantasyfootball.db")
for year in range(2014, 2018, 1):
    week_by_week_df = pd.read_sql("SELECT * FROM PLAYER_WEEKLY_STATS_YEAR_{}".format(year), con, index_col="playerid")
    grouped = week_by_week_df.drop("week", axis=1).groupby(["team", "position"]).sum()
    unstacked = grouped.unstack()
    unstacked.columns = unstacked.columns.map("{0[1]}_{0[0]}".format)
    s_un = unstacked.reindex_axis(sorted(unstacked.columns),axis=1)
    s_un.fillna(0).to_sql("TEAM_POSITION_STATS_{}".format(year), con, index=True, index_label="team", if_exists="replace")
con.close()
# I bet we can do a bit better than just summing up the total things -- that's not a good measurement.
# let's do stats sums for each position for each player; this will be easier to do in pandas.
con = sql.Connection("/Users/tstuessi/Dev/ff-analysis/fantasyfootball.db")
for year in range(2014, 2018, 1):
    week_by_week_df = pd.read_sql("SELECT * FROM PLAYER_WEEKLY_STATS_YEAR_{}".format(year), con, index_col="playerid")
    per_player_totals_df = week_by_week_df.drop(["week"], axis=1).groupby(["playerid", "team", "position"]).sum().reset_index()

        # grab the team stats
    team_stats = week_by_week_df.drop("week", axis=1).groupby(["team", "position"]).sum()
    unstacked = team_stats.unstack()
    unstacked.columns = unstacked.columns.map("{0[1]}_{0[0]}".format)
    s_un = unstacked.reindex_axis(sorted(unstacked.columns),axis=1)
        # join these team stats
    joined_df = per_player_totals_df.merge(s_un, left_on="team", right_index=True)
    joined_df.fillna(0, inplace=True)

    for i, row in joined_df.iterrows():
        team = row["team"]
        position = row["position"]

        column_list = [x for x in row.index.values if x.startswith("{}_".format(position))]
        if len(column_list) == 0:
            continue
        orig_list = [x.replace("{}_".format(position), "") for x in column_list]

        players_stats = row[orig_list]
        team_stats = row[column_list]
        #print((team_stats - players_stats))

        row.loc[column_list] = team_stats - players_stats
    joined_df.to_sql("FULL_YEAR_PLAYER_AND_TEAM_STATS_{}".format(year), con, index=False)

con.close()
joined_df.loc[0, :]
#con.close()<jupyter_output>/Users/tstuessi/anaconda3/envs/ff_analysis/lib/python3.6/site-packages/ipykernel_launcher.py:12: FutureWarning: '.reindex_axis' is deprecated and will be removed in a future version. Use '.reindex' instead.
  if sys.path[0] == '':
/Users/tstuessi/anaconda3/envs/ff_analysis/lib/python3.6/site-packages/pandas/core/series.py:851: FutureWarning: 
Passing list-likes to .loc or [] with any missing label will raise
KeyError in the future, you can use .reindex() as an alternative.

See the documentation here:
https://pandas.pydata.org/pandas-docs/stable/indexing.html#deprecate-loc-reindex-listlike
  return self.loc[key]<jupyter_text>Create Schedule Data<jupyter_code>con = sql.Connection("/Users/tstuessi/Dev/ff-analysis/fantasyfootball.db")
for year in range(2014, 2019, 1):
    games_dict = {}
    games = nfl.games(year)
    for i in range(0, len(games)*2, 2):
        game = games[i // 2]
        games_dict[i] = {"team": game.home,
                         "week": game.schedule["week"],
                         "is_home": True,
                         "opp": game.away,
                         "team_score": game.score_home,
                         "opp_score": game.score_away,
                         "month": game.schedule["month"],
                         "wday": game.schedule["wday"]
                        }
        games_dict[i+1] = {"team": game.away,
                           "week": game.schedule["week"],
                           "is_home": False,
                           "opp": game.home,
                           "team_score": game.score_away,
                           "opp_score": game.score_home,
                           "month": game.schedule["month"],
                           "wday": game.schedule["wday"]
                          }
                           
    df = pd.DataFrame.from_dict(games_dict, orient="index")
    df.to_sql("SCHED_YEAR_{}".format(year), con, index=False)
    
    
con.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13200668388272824, 'fraction_numerical': 0.017924958225732947, 'mean_word_length': 3.0717377860235002, 'pattern_counts': {'":': 16, '<': 15, '<?xml version=': 0, '>': 15, 'https://': 1, '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': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '231824', 'license': 'MIT', 'n_tokens_mistral': 2324, 'n_tokens_neox': 2205, 'n_words': 447, 'path': 'FF Table Experimentation.ipynb', 'repo_name': 'tstuessi/ff-analysis', 'size': '6583'}