<jupyter_start><jupyter_text>Data Preparation<jupyter_code>def load_data(in_path, name):
    df = pd.read_csv(in_path, low_memory=False)
    #print(f"{name}: shape is {df.shape}")
    #print(df.info())
    #display(df.head(5))
    return df<jupyter_output><empty_output><jupyter_text>Load Roster Data<jupyter_code>DATA_DIR = "C:/Users/travis/Dropbox (Arthroscopy Associa)/Travis/Desktop/IU/Final Project"
datasets={}
pre_roster_2009 = load_data(os.path.join(DATA_DIR, f'pre_roster_2009.csv'), 'pre_roster_2009')
pre_roster_2009['Made_Roster'] = 0

reg_roster_2009 = load_data(os.path.join(DATA_DIR, f'reg_roster_2009.csv'), 'reg_roster_2009')
reg_roster_2009['Made_Roster'] = 1

roster_2009 = reg_roster_2009.append(pre_roster_2009)
years = [2010, 2011, 2012, 2013, 2014, 2015, 2016, 2017, 2018]
roster_all = roster_2009.copy()

for ii in years:
    pre_name = 'pre_roster_' + str(ii)
    pre_df = load_data(os.path.join(DATA_DIR, f'{pre_name}.csv'), pre_name)
    #print(pre_df.shape)
    
    reg_name = 'reg_roster_' + str(ii)
    reg_df = load_data(os.path.join(DATA_DIR, f'{reg_name}.csv'), reg_name)
    
    roster_df = pre_df.assign(Made_Roster=pre_df.gsis_id.isin(reg_df.gsis_id).astype(int))
    
    roster_all = roster_all.append(roster_df)
    #print(roster_all.shape)
    
roster_all = roster_all.drop(['season_type'], axis=1)<jupyter_output><empty_output><jupyter_text>Transform Play-By-Play Data<jupyter_code>years.append(2009)
columns = ['game_id', 'yardline_100', 'half_seconds_remaining', 'game_seconds_remaining', 'game_half', 'drive', 'qtr', 'down', 
           'goal_to_go', 'ydstogo', 'ydsnet', 'play_type', 'yards_gained','air_yards', 'yards_after_catch', 
           'two_point_converted', 'two_point_failed', 'third_down_converted', 'third_down_failed',
           'fourth_down_converted', 'fourth_down_failed', 'incomplete_pass', 'interception', 'penalty', 'fumble_lost',
           'touchdown', 'fumble', 'complete_pass', 'passer_player_id', 'receiver_player_id', 'rusher_player_id',
           'fumbled_1_player_id', 'penalty_player_id', 'penalty_yards',
           'unique_id', 'season', 'Two_Min', 'Redzone', 'Per_Yards_Gained', 'Success_Play']
pbp_all = pd.DataFrame(columns = columns)

for ii in years:
    pbp_name = 'pre_pbp_' + str(ii)
    pbp_df = load_data(os.path.join(DATA_DIR, f'{pbp_name}.csv'), pbp_name)
    
    #get unique ID for each play
    pbp_df['unique_id'] = pbp_df['play_id'].astype(str) + pbp_df['game_id'].astype(str)
    
    #add year of game
    pbp_df['season'] = ii
    
    #keep plays we are concerned with
    pbp_df = pbp_df[(pbp_df.play_type == 'pass') | (pbp_df.play_type == 'run')]
    
    #encode two point conversions
    two_conv_df = pbp_df[(pbp_df.two_point_conv_result == 'success')]
    pbp_df = pbp_df.assign(two_point_converted=pbp_df.unique_id.isin(two_conv_df.unique_id).astype(int))
    
    two_fail_df = pbp_df[(pbp_df.two_point_conv_result == 'failure')]
    pbp_df = pbp_df.assign(two_point_failed=pbp_df.unique_id.isin(two_fail_df.unique_id).astype(int))
    
    #flag plays in last two mins of a half
    two_min_df = pbp_df[(pbp_df.half_seconds_remaining <= 120)]
    pbp_df = pbp_df.assign(Two_Min=pbp_df.unique_id.isin(two_min_df.unique_id).astype(int))
    
    #flag red zone plays
    red_df = pbp_df[(pbp_df.yardline_100 <= 20)]
    pbp_df = pbp_df.assign(Redzone=pbp_df.unique_id.isin(red_df.unique_id).astype(int))
    
    
    # flag success rate using Football Outsider's varsity formula
    # https://www.footballoutsiders.com/info/glossary
    pbp_df['Per_Yards_Gained'] = pbp_df.yards_gained / pbp_df.ydstogo
    
    suc_fir_df = pbp_df[(pbp_df.down == 1) & (pbp_df.Per_Yards_Gained >= .5)]
    suc_sec_df = pbp_df[(pbp_df.down == 2) & (pbp_df.Per_Yards_Gained >= .7)]
    suc_thi_df = pbp_df[(pbp_df.down == 3) & (pbp_df.Per_Yards_Gained >= 1)]
    suc_fou_df = pbp_df[(pbp_df.down == 4) & (pbp_df.Per_Yards_Gained >= 1)]
    
    suc_all_df = pd.concat([suc_fir_df, suc_sec_df, suc_thi_df, suc_fou_df], axis=0, ignore_index=True)
    
    pbp_df = pbp_df.assign(Success_Play=pbp_df.unique_id.isin(suc_all_df.unique_id).astype(int))
    
    
    #prepare final dataframe
    pbp_df = pbp_df.fillna(0)
    
    pbp_all = pbp_all.append(pbp_df[columns])<jupyter_output><empty_output><jupyter_text>Combine All Data<jupyter_code>agg_cols = ['yards_gained', 'two_point_converted', 'two_point_failed', 'third_down_converted', 'third_down_failed',
            'fourth_down_converted', 'fourth_down_failed', 'touchdown', 'Two_Min', 'Redzone', 'Success_Play', 'Total_Plays',
            'Q1_Plays', 'Q2_Plays', 'Q3_Plays', 'Q4_Plays']

pass_cols = ['interception', 'incomplete_pass', 'complete_pass', 'air_yards', 'pass_yards', 'pass_touchdown', 
             'Success_Pass', 'Passer_Plays'] + agg_cols

rec_cols = ['yards_after_catch', 'receiving_yards', 'receiving_touchdown', 'Success_Receive', 'Receiver_Plays'] + agg_cols

rush_cols = ['rush_yards', 'rush_touchdown', 'Success_Rush', 'Run_Plays'] + agg_cols

pen_cols = ['penalty', 'penalty_yards']

fumb_cols = ['fumble_lost', 'fumble']
all_cols = pass_cols + rec_cols + rush_cols + pen_cols + fumb_cols
all_cols = list(set(all_cols))
all_cols = sorted(all_cols, key=str.lower)
stats_df = pd.DataFrame(columns = all_cols)

for ii in years:
    year_df = pbp_all[(pbp_all.season == ii)].copy()
    year_df['Total_Plays'] = 1
    
    #Add Plays in quarter    
    q1_df = year_df[(year_df.qtr == 1)]
    year_df = year_df.assign(Q1_Plays=year_df.unique_id.isin(q1_df.unique_id).astype(int))
    
    q2_df = year_df[(year_df.qtr == 2)]
    year_df = year_df.assign(Q2_Plays=year_df.unique_id.isin(q2_df.unique_id).astype(int))
    
    q3_df = year_df[(year_df.qtr == 3)]
    year_df = year_df.assign(Q3_Plays=year_df.unique_id.isin(q3_df.unique_id).astype(int))
    
    q4_df = year_df[(year_df.qtr == 4)]
    year_df = year_df.assign(Q4_Plays=year_df.unique_id.isin(q4_df.unique_id).astype(int))
    
    #assemble passing stats
    pass_df = year_df[(year_df.play_type == 'pass')]
    pass_df = pass_df.infer_objects()  
    pass_df['pass_yards'] = pass_df['yards_gained']
    pass_df['pass_touchdown'] = pass_df['touchdown']
    pass_df['Success_Pass'] = pass_df['Success_Play']
    pass_df['Passer_Plays'] = 1
    
    pass_df = pass_df.groupby('passer_player_id').sum()    
    pass_df = pass_df[pass_cols]
    pass_df.index.name = 'player_id'
    
    
    #assemble receiving stats
    rec_df = year_df[(year_df.play_type == 'pass')]
    rec_df = rec_df.infer_objects()
    rec_df['receiving_yards'] = rec_df['yards_gained']
    rec_df['receiving_touchdown'] = rec_df['touchdown']
    rec_df['Success_Receive'] = rec_df['Success_Play']
    rec_df['Receiver_Plays'] = 1
    
    rec_df = rec_df.groupby('receiver_player_id').sum()    
    rec_df = rec_df[rec_cols]
    
    rec_df.index.name = 'player_id'
    rec_df = rec_df.drop(rec_df.index[0])
    
    
    #assemble rushing stats
    rush_df = year_df[(year_df.play_type == 'run')]
    rush_df = rush_df.infer_objects()
    rush_df['rush_yards'] = rush_df['yards_gained']
    rush_df['rush_touchdown'] = rush_df['touchdown']
    rush_df['Success_Rush'] = rush_df['Success_Play']
    rush_df['Run_Plays'] = 1
    
    rush_df = rush_df.groupby('rusher_player_id').sum()    
    rush_df = rush_df[rush_cols]
    
    rush_df.index.name = 'player_id'
    
    
    #assemble penalty stats
    pen_df = year_df[(year_df.penalty == 1)]
    pen_df = pen_df.infer_objects()
    pen_df = pen_df.groupby('penalty_player_id').sum()    
    pen_df = pen_df[pen_cols]
    
    pen_df.index.name = 'player_id'
    pen_df = pen_df.drop(pen_df.index[0])
    
    
    #assemble fumble stats
    fumb_df = year_df[(year_df.fumble == 1)]
    fumb_df = fumb_df.infer_objects()
    
    fumb_df = fumb_df.groupby('fumbled_1_player_id').sum()    
    fumb_df = fumb_df[fumb_cols]
    
    fumb_df.index.name = 'player_id'
    
    
    #combine stats
    comb_df = pd.concat([pass_df, rec_df, rush_df, pen_df, fumb_df]).groupby(['player_id']).sum()
    
    
    #join in player information
    player_df = roster_all[(roster_all.season == ii)]
    
    player_df = player_df.set_index('gsis_id')
    player_df.index.name = 'player_id'
    
    comb_df = comb_df.join(player_df)    
    comb_df = comb_df.dropna(subset=['Made_Roster'])
    
    
    #join all data into final dataframe
    comb_df = comb_df.fillna(0)
   
    stats_df = stats_df.append(comb_df)
#change the display formats on numbers for simplicity
pd.options.display.float_format = '{:,.0f}'.format
stats_df['season'] = pd.to_numeric(stats_df['season'], downcast='integer')
print(len(stats_df))
stats_df.head()<jupyter_output>6555<jupyter_text>Visualizations<jupyter_code>ax = stats_df.groupby(['position', 'Made_Roster']).size().unstack().plot(kind='bar', stacked=True)
ax.set_ylabel('Player Count')
ax.set_xlabel('Position')
ax.set_title('Preseason to Week 1 NFL Roster Analysis by Position (2009-2018)')
ax.legend(['waived', 'signed'])
sns.countplot(x="season", data=stats_df);
sns.countplot(x="season", data=stats_df[stats_df['Made_Roster'] == 1]);<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14410671331729719, 'fraction_numerical': 0.017384649026897004, 'mean_word_length': 4.447885646217987, 'pattern_counts': {'":': 0, '<': 22, '<?xml version=': 0, '>': 24, 'https://': 1, 'lorem ipsum': 0, 'www.': 1, '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': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '53529', 'license': 'Apache-2.0', 'n_tokens_mistral': 3863, 'n_tokens_neox': 3672, 'n_words': 505, 'path': 'docs/report/fa20-523-308/HW7/Task_2_HW7_Project_Whitaker.ipynb', 'repo_name': 'aporlowski/cybertraining-dsc.github.io', 'size': '9146'}