<jupyter_start><jupyter_text>Cumulative Average code  - Opponent Stats<jupyter_code>def convert_forwards_bigs(x):
    if x=='C' or x=='F' or x=='SF' or x=='PF':
        return 'Big_man'
    elif x=='G' or x=='PG'or x=='SG':
        return 'Guard'
    else:
        ### Some missing positions (ATH - Athlete OR NAN)
        return x




### Shows all the columns in a dataframe
pd.set_option('max_columns', None)
#### Disables chained assingments without the need to create a copy
pd.options.mode.chained_assignment = None


#### Individual games points to starters and reserves 
opponents_points_to_starters_and_reserves= pd.DataFrame()
opponents_points_to_starters_and_reserves_position = pd.DataFrame()
### Use to append the opponent stats allowed to starters and reserves
Rolling_average_grouped_starters_reserves = pd.DataFrame()
### Stores total points by opponent for each game
total_opponent_points_allowed = pd.DataFrame()
#### players ppg 
player_box_with_points_avergae= pd.DataFrame()
### team-players stats
all_teams_box_score_df = pd.DataFrame()

merged_team_opp_stats = pd.DataFrame()
merged_team_opp_stats_with_pos = pd.DataFrame()


# for file in tqdm(file_names[:4]):### Limit number of seasons 
#   if len((pd.read_csv('{}/{}'.format(teams_path,file))))>0:
#     df= pd.read_csv('{}/{}'.format(teams_path,file),index_col=0)
    # display(df.head())
    # print(df.columns)

for year in np.arange(2019,2023):
    df = pd.read_csv("..\\..\\src\\team_player_boxscore_merged\\{}.csv".format(year),index_col = 0)
    players_list = list(df.athlete_display_name.unique())

    # ############################################ Team statistics #########################################################################

    # ### gets the cumulative average points and shifts it to the next game to use for predicting (does not include game in the current row.)  
    df['points_per_min']=df['pts']/df['min']
    df=df.assign(prev_player_ppg=df.groupby('athlete_display_name', sort=False)['pts'].transform(lambda x: x.expanding().mean().round(2).shift(1)))
    df=df.assign(prev_player_points_per_min=df.groupby('athlete_display_name', sort=False)['points_per_min'].transform(lambda x: x.expanding().mean().round(3).shift(1)))
    player_box_with_points_avergae=player_box_with_points_avergae.append(df)

    list_of_teams = list(df.team_abbreviation.unique())
    for team in list_of_teams: 

        team_df = df[df.team_abbreviation==team]
        date_total_points = team_df.groupby('game_date')['pts'].sum().reset_index().rename(columns = {'pts':'game_total_points'})
        team_df = pd.merge(team_df,date_total_points,on='game_date')

        ### Gets the game number for each team.
        games_list = list(team_df[team_df.team_abbreviation==team]["game_id"].unique())
        game_num = list(np.arange(1,len(games_list)+1))
        game_number_df = (pd.DataFrame((list(zip(games_list,game_num))),columns = ['game_id','game_number']))
        game_number_df["team_abbreviation"]=team

        # game_num_dates_all_teams=game_num_dates_all_teams.append(game_number_df)
        team_df = pd.merge(team_df,game_number_df,on=['team_abbreviation','game_id'])
        team_df["Cleaned_position_bigs_guards"] = team_df['athlete_position_abbreviation'].apply(convert_forwards_bigs)

        ### Append to dataframe with all teams 
        ## Add cleaned_position_bigs_guards to all_teams_box_score_df
        all_teams_box_score_df = all_teams_box_score_df.append(team_df)
    
    
      ######################################### Stats against opponents ######################################################################################

      #### Get a rolling average for points allowed by opponents for each team split into starters and reserves 
      ### Create a function for the desired rolling average window. 
        rolling_average_widow = 3
        opponent_df = df[df.opponent_abbrev==team].sort_values(by = "game_date")

        opponent_df["Cleaned_position_bigs_guards"] = opponent_df['athlete_position_abbreviation'].apply(convert_forwards_bigs)


        ##### For starters/reserves and position

        grouped_position_opponent_df = opponent_df.loc[:,['season','min','largest_lead','pts']+["game_date","game_id",'opponent_abbrev','starter','Cleaned_position_bigs_guards']].groupby(
          ["game_date","game_id",'opponent_abbrev','starter','Cleaned_position_bigs_guards']).agg(
          {'season':'mean','min':'mean','pts':'sum'}
        )

        grouped_position_opponent_df = grouped_position_opponent_df.rename(columns={"pts":"pts_allowed"})
        grouped_position_opponent_df['season'] = grouped_position_opponent_df.season.astype(int)
        opponents_points_to_starters_and_reserves_position = opponents_points_to_starters_and_reserves_position.append(grouped_position_opponent_df)
        grouped_position_opponent_df = grouped_position_opponent_df.reset_index()
        #### Starting bigs
        grouped_bigs_starters =  grouped_position_opponent_df[(grouped_position_opponent_df['Cleaned_position_bigs_guards']=='Big_man') & (grouped_position_opponent_df['starter']==True)]
        grouped_bigs_starters["cum_average_opp_points_to_role_position"]=(grouped_bigs_starters['pts_allowed'].cumsum(axis=0) / (grouped_bigs_starters['pts_allowed'] != 0).cumsum(axis=0)).round(3).shift(1) 
        grouped_bigs_starters["cum_average_opp_min_allowed_to_role_position"]=(grouped_bigs_starters['min'].cumsum(axis=0) / (grouped_bigs_starters['min'] != 0).cumsum(axis=0)).round(3).shift(1)
        cumulative_averages_grouped_bigs_starters = grouped_bigs_starters.loc[:, ~grouped_bigs_starters.columns.isin(["min"	,"pts_allowed"])]


        #### Starting guards
        grouped_guards_starters =  grouped_position_opponent_df[(grouped_position_opponent_df['Cleaned_position_bigs_guards']=='Guard') & (grouped_position_opponent_df['starter']==True)]
        grouped_guards_starters["cum_average_opp_points_to_role_position"]=(grouped_guards_starters['pts_allowed'].cumsum(axis=0) / (grouped_guards_starters['pts_allowed'] != 0).cumsum(axis=0)).round(3).shift(1) 
        grouped_guards_starters["cum_average_opp_min_allowed_to_role_position"]=(grouped_guards_starters['min'].cumsum(axis=0) / (grouped_guards_starters['min'] != 0).cumsum(axis=0)).round(3).shift(1)
        cumulative_averages_grouped_guards_starters = grouped_guards_starters.loc[:, ~grouped_guards_starters.columns.isin(["min"	,"pts_allowed"])]

        #### Reserve bigs 
        grouped_bigs_reserves =  grouped_position_opponent_df[(grouped_position_opponent_df['Cleaned_position_bigs_guards']=='Big_man') & (grouped_position_opponent_df['starter']==False)]
        grouped_bigs_reserves["cum_average_opp_points_to_role_position"]=(grouped_bigs_reserves['pts_allowed'].cumsum(axis=0) / (grouped_bigs_reserves['pts_allowed'] != 0).cumsum(axis=0)).round(3).shift(1) 
        grouped_bigs_reserves["cum_average_opp_min_allowed_to_role_position"]=(grouped_bigs_reserves['min'].cumsum(axis=0) / (grouped_bigs_reserves['min'] != 0).cumsum(axis=0)).round(3).shift(1)
        cumulative_averages_grouped_bigs_reserves = grouped_bigs_reserves.loc[:, ~grouped_bigs_reserves.columns.isin(["min"	,"pts_allowed"])]


        #### Reserve guards
        grouped_guards_reserves =  grouped_position_opponent_df[(grouped_position_opponent_df['Cleaned_position_bigs_guards']=='Guard') & (grouped_position_opponent_df['starter']==False)]
        grouped_guards_reserves["cum_average_opp_points_to_role_position"]=(grouped_guards_reserves['pts_allowed'].cumsum(axis=0) / (grouped_guards_reserves['pts_allowed'] != 0).cumsum(axis=0)).round(3).shift(1) 
        grouped_guards_reserves["cum_average_opp_min_allowed_to_role_position"]=(grouped_guards_reserves['min'].cumsum(axis=0) / (grouped_guards_reserves['min'] != 0).cumsum(axis=0)).round(3).shift(1)
        cumulative_averages_grouped_guards_reserves = grouped_guards_reserves.loc[:, ~grouped_guards_reserves.columns.isin(["min"	,"pts_allowed"])]

        starters_reserves__position_cumulative_averages= cumulative_averages_grouped_bigs_starters.append([cumulative_averages_grouped_guards_starters,
                                                                                                         cumulative_averages_grouped_bigs_reserves,
                                                                                                         cumulative_averages_grouped_guards_reserves
                                                                                                         ]).sort_values(by='game_date')



        #### For only starters and reserves 
        grouped_opponent_df = opponent_df.loc[:,['season','min','largest_lead','pts']+["game_date","game_id",'opponent_abbrev','starter']].groupby(
          ["game_date","game_id",'opponent_abbrev','starter']).agg(
          {'season':'mean','min':'mean','largest_lead':'mean','pts':'sum'}
        )

        grouped_opponent_df = grouped_opponent_df.rename(columns={"largest_lead":"largest_lead_entire_game","pts":"pts_allowed"})
        ### individual games - points to starters and reserves in grouped_opponent_df
        grouped_opponent_df['season'] = grouped_opponent_df.season.astype(int)
        ### Append to big dataframe with all opponents_points allowed
        opponents_points_to_starters_and_reserves = opponents_points_to_starters_and_reserves.append(grouped_opponent_df)

        #### Get total points for each opponent. 
        grouped_opponent_df_all_roles = opponent_df.loc[:,['season','pts']+["game_date","game_id",'opponent_abbrev']].groupby(
          ["game_date","game_id",'opponent_abbrev']).agg(
          {'season':'mean','pts':'sum'}
        )
        grouped_opponent_df_all_roles = grouped_opponent_df_all_roles.rename(columns={"pts":"total_pts_allowed"})
        grouped_opponent_df_all_roles['season'] = grouped_opponent_df_all_roles.season.astype(int)
        ### Looks at prev games average
        grouped_opponent_df_all_roles['prev_total_opp_ppg'] = (grouped_opponent_df_all_roles['total_pts_allowed'].cumsum(axis=0) / (grouped_opponent_df_all_roles['total_pts_allowed'] != 0).cumsum(axis=0)).round(3).shift(1) 

        total_opponent_points_allowed = total_opponent_points_allowed.append(grouped_opponent_df_all_roles)

       ###################################################### starters ################################################################
        grouped_starters = grouped_opponent_df.reset_index()[grouped_opponent_df.reset_index().starter == True]
        ### Cumulative Averages - Starters
        grouped_starters["cum_average_opp_points_to_role"]=(grouped_starters['pts_allowed'].cumsum(axis=0) / (grouped_starters['pts_allowed'] != 0).cumsum(axis=0)).round(3).shift(1) 
        grouped_starters["cum_average_opp_min_allowed_to_role"]=(grouped_starters['min'].cumsum(axis=0) / (grouped_starters['min'] != 0).cumsum(axis=0)).round(3).shift(1)


        ### Rolling Averages - starters
        grouped_starters["prev_{}_games_opp_min_allowed_to_role".format(rolling_average_widow)]=grouped_starters['min'].rolling(rolling_average_widow).mean().round(3).shift(1)
        grouped_starters["prev_{}_games__opp_points_allowed_to_role".format(rolling_average_widow)]=grouped_starters['pts_allowed'].rolling(rolling_average_widow).mean().round(3).shift(1)

        ### does not include totals, only rolling averages for starters
        rolling_averages_grouped_starters = grouped_starters.loc[:, ~grouped_starters.columns.isin(["min"	,"largest_lead_entire_game"	,"pts_allowed"])]

        ############################################################################ Reserves ###########################################################
        grouped_reserves = grouped_opponent_df.reset_index()[grouped_opponent_df.reset_index().starter == False]
        ### Cumulative Averages - reserves
        grouped_reserves["cum_average_opp_points_to_role"]=(grouped_reserves['pts_allowed'].cumsum(axis=0) / (grouped_reserves['pts_allowed'] != 0).cumsum(axis=0)).round(3).shift(1)
        grouped_reserves["cum_average_opp_min_allowed_to_role"]=(grouped_reserves['min'].cumsum(axis=0) / (grouped_reserves['min'] != 0).cumsum(axis=0)).round(3).shift(1)

        ### Rolling Averages - reserves
        grouped_reserves["prev_{}_games_opp_min_allowed_to_role".format(rolling_average_widow)]=grouped_reserves['min'].rolling(rolling_average_widow).mean().round(3).shift(1)
        grouped_reserves["prev_{}_games__opp_points_allowed_to_role".format(rolling_average_widow)]=grouped_reserves['pts_allowed'].rolling(rolling_average_widow).mean().round(3).shift(1)

        ### Gets rid of the columns we took the averages from 
        rolling_averages_grouped_reserves = grouped_reserves.loc[:, ~grouped_reserves.columns.isin(["min"	,"largest_lead_entire_game"	,"pts_allowed"])]


        starters_reserves_rolling_averages = rolling_averages_grouped_starters.append(rolling_averages_grouped_reserves).sort_values(by='game_date')

        ### Merge total opponent points with the rolling and cumulative averages
        starters_reserves_rolling_averages = pd.merge(starters_reserves_rolling_averages,total_opponent_points_allowed,on =['game_date','game_id','opponent_abbrev','season'])

        ### Append to larger df for all teams starters and reserves rolling average 
        Rolling_average_grouped_starters_reserves = Rolling_average_grouped_starters_reserves.append(starters_reserves_rolling_averages)


        #### Merge cumulative average with starters_reserves_rolling_averages
        starters_reserves_pos_rolling_averages = pd.merge(starters_reserves__position_cumulative_averages,Rolling_average_grouped_starters_reserves,on =['game_date','game_id','opponent_abbrev','season','starter'])

    ### Uncomment first one if you do not want to include position (which is missing some values in many teams and seasons) - and limit the files
    merged_team_opp_stats = merged_team_opp_stats.append(pd.merge(all_teams_box_score_df,Rolling_average_grouped_starters_reserves, on =['game_date','game_id','opponent_abbrev','starter','season']))
#     merged_team_opp_stats_with_pos = merged_team_opp_stats_with_pos.append(pd.merge(all_teams_box_score_df,starters_reserves_pos_rolling_averages, on =['game_date','game_id','opponent_abbrev','starter','season','Cleaned_position_bigs_guards']))



merged_team_opp_stats



# merged_team_opp_stats.to_csv("/drive/My Drive/Capstone Project College Basketball Data/ESPN_data/Features_and_boxscores/player_and_opp_stats.csv")
### File used for prediction model
# merged_team_opp_stats = pd.read_csv("/drive/My Drive/Capstone Project College Basketball Data/ESPN_data/Features_and_boxscores/player_and_opp_stats.csv",index_col=0)
### Drop duplicates
merged_team_opp_stats = merged_team_opp_stats.drop_duplicates()
merged_team_opp_stats
merged_team_opp_stats['Home']=pd.get_dummies(merged_team_opp_stats.home_away, drop_first=True)
merged_team_opp_stats.loc[:, merged_team_opp_stats.columns!='home_away']
merged_team_opp_stats["Starter_numeric"] = pd.get_dummies(merged_team_opp_stats.starter, drop_first=True)
merged_team_opp_stats["Cleaned_position_bigs_guards"] = merged_team_opp_stats['athlete_position_abbreviation'].apply(convert_forwards_bigs)
merged_team_opp_stats

total_points_allowed_to_position = merged_team_opp_stats.groupby(["game_id",'game_date',"team_short_display_name","team_id","opponent_abbrev","Starter_numeric","Cleaned_position_bigs_guards"]).agg(
          {'pts':'sum','min':'mean'}
      ).round(2).reset_index()
##### Remove teams that have ATH position players 
teams_with_ath_pos = (list(total_points_allowed_to_position[(total_points_allowed_to_position['Cleaned_position_bigs_guards']=="ATH")].team_short_display_name.unique()))
total_points_allowed_to_position = total_points_allowed_to_position[~total_points_allowed_to_position['team_short_display_name'].isin(teams_with_ath_pos)]
### Clean previous code for starters using .apply(lambda x: x.expanding().mean() for cumulative average and cleaner code - only need a few lines of code (instead of filtering and using cumsum())
### Runs faster too do the same thing for role(starter/reserve but do not have to include cleaned_position_bigs_guards - and merge with "merged_team_opp_stats" df instead of total_points_allowed_to_position.
cum_avg_pos =total_points_allowed_to_position.groupby(["opponent_abbrev",'Starter_numeric','Cleaned_position_bigs_guards'])['pts','min'].apply(lambda x: x.expanding().mean().round(3).shift())
cum_avg_pos = cum_avg_pos.rename(columns = {'pts':"prev_avg_pts_to_role_pos",'min':'prev_avg_min_to_role_position'})
merged_opp_stats_allowed= total_points_allowed_to_position.merge(cum_avg_pos,left_index=True, right_index = True)

# merged_opp_stats_allowed#### includes points and minutes to each position and starting/reserve role
cleaned_merged_opp_stats_allowed = merged_opp_stats_allowed.loc[:, ~merged_opp_stats_allowed.columns.isin(['pts','min'])]
cleaned_merged_opp_stats_allowed
box_scores_and_features = pd.merge(merged_team_opp_stats,cleaned_merged_opp_stats_allowed, on = ['game_id',	"game_date","team_short_display_name","team_id","opponent_abbrev","Starter_numeric","Cleaned_position_bigs_guards"]
                                   ,how = "left")
positions_encoding = pd.get_dummies(box_scores_and_features['Cleaned_position_bigs_guards'])
box_scores_and_features = box_scores_and_features.join(positions_encoding)
box_scores_and_features

# For Saving
# box_scores_and_features.to_csv("src\\Features_and_boxscores\\limited_sampled_player_and_opp_stats_with_position.csv")<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14700215346254109, 'fraction_numerical': 0.005383656352714496, 'mean_word_length': 6.132983023443816, 'pattern_counts': {'":': 4, '<': 5, '<?xml version=': 0, '>': 6, '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': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '319525', 'license': 'MIT', 'n_tokens_mistral': 5939, 'n_tokens_neox': 5566, 'n_words': 655, 'path': 'notebooks/Feature Engineering/Feature_engineering.ipynb', 'repo_name': 'btatkinson/NCAA-basketball-Capstone', 'size': '17646'}