<jupyter_start><jupyter_text>9. Data pipeline extracting data from CSV, building datasets and storing them in parquet files ---**To be able to execute this notebook, please download your own local version of the data from https://www.kaggle.com/martinellis/nhl-game-data and store it into the folder "data/nhl/nhl_stats/"****In addition, please download your own local version of the data from https://www.kaggle.com/camnugent/predict-nhl-player-salaries and store it into the folder "data/nhl/nhl_salaries/"**--- This notebook provides a data pipeline to extract data from divers CSV files, merge it and model new features out of it.<jupyter_code>import pandas as pd
import numpy as np
import numpy as np
import math
import os

from utils import create_dummy_df
from utils import balance_binary_target

from models import run_logistic_regression
from models import run_lightgbm_classifier
from models import run_kneighbors_classifier
def dist(df): 
    '''
    Function calculates distance to the goal based on the location
    INPUT:
    df - data frame with location
    
    OUTPUT:
    distance to the goal
    ''' 
    middle_goal_x = 100 
    middle_goal_y = 0
    return math.hypot(middle_goal_x - df.st_x, middle_goal_y - df.st_y)


def angle(df):   
    '''
    Function calculated angle of the shot measured to the longitude axis of the hockey field 
    INPUT:
    df - data frame with location
    
    OUTPUT:
    angle of the shot
    '''    
    middle_goal_x = 100 
    return math.fabs(math.degrees(math.atan2(df.st_x - middle_goal_x, df.st_y)) + 90)


    
def extract_dataset(force=False):
    '''
    Function extract all data from csv files and store experimental datasets as parquet files 
    INPUT:
    force - flag if the data should be extract and replaced even if the parquet files do exist
        
    OUTPUT: no
    '''       
    
    # if not forced and all parquet files do exist, skip data extraction
    if ((force==False) and 
        (os.path.isfile('data/_1_first_dataset.parquet') and
        os.path.isfile('data/_2_balanced_target.parquet') and 
        os.path.isfile('data/_3_with_distance.parquet') and 
        os.path.isfile('data/_4_with_distance_angle.parquet') and 
        os.path.isfile('data/_5_with_player_ids.parquet') and 
        os.path.isfile('data/_6_with_player_stats.parquet') and 
        os.path.isfile('data/_7_with_player_salary.parquet') and 
        os.path.isfile('data/_8_short_dist.parquet') and 
        os.path.isfile('data/_9_long_dist.parquet'))):
        print('Data extraction skipped')
        return 
    
    print('Started data extraction...')
    
    # read data
    df_game_plays = pd.read_csv('data/nhl/nhl_stats/game_plays.csv')
    df_game_plays.drop_duplicates(inplace=True)

    # drop NaNs, select relevant columns only
    df = df_game_plays[['play_id', 'event', 'secondaryType', 'st_x', 'st_y', 'period', 'periodTime']][(df_game_plays.event=='Goal') | (df_game_plays.event=='Shot')]
    df.dropna(inplace=True)
    df.secondaryType = df.secondaryType.str.replace(" ", "")
    df.secondaryType = df.secondaryType.str.replace("-", "")

    # Prepare target. Convert categorical event values 'Goal' to a numerical 0/1 value indicating goal
    df['goal'] = np.where(df.event=='Goal', 1, 0)
    df.drop(columns='event', inplace=True)

    # Convert categorical 'secondaryType' column to multiple numerical columns
    cat_cols = ['secondaryType']
    df = create_dummy_df(df=df, cat_cols=cat_cols, dummy_na=True)
    df.drop(columns=['secondaryType_nan'], inplace=True)

    #####################
    df.to_parquet('data/_1_first_dataset.parquet', compression='brotli')
    #####################


    ###############################################################
    #balance targets
    df = balance_binary_target(df, target='goal')

    #####################
    df.to_parquet('data/_2_balanced_target.parquet', compression='brotli')
    #####################


    ###############################################################
    # adding feature distance
    df['distance'] = df.apply(dist, axis=1)

    # cut outliers first
    df = df[df.distance<100]

    #####################
    df.to_parquet('data/_3_with_distance.parquet', compression='brotli')
    #####################


    ###############################################################
    # adding feature angle
    df['angle'] = df.apply(angle, axis=1)

    # cutting outliers
    df= df[df.angle<70]

    #####################
    df.to_parquet('data/_4_with_distance_angle.parquet', compression='brotli')
    #####################


    ###############################################################
    # adding players to the game
    df_player = pd.read_csv('data/nhl/nhl_stats/game_plays_players.csv')
    df_player.drop_duplicates(inplace=True)

    #skater
    df_skater = df_player[df_player.playerType.isin(['Shooter', 'Scorer'])].copy()
    df_skater.rename(columns={'player_id':'skater_id'}, inplace=True)
    df_skater.drop(columns=['game_id', 'playerType'], inplace=True)

    #goalies
    df_goalie = df_player[df_player.playerType.isin(['Goalie'])].copy()
    df_goalie.rename(columns={'player_id':'goalie_id'}, inplace=True)
    df_goalie.drop(columns=['game_id', 'playerType'], inplace=True)

    # created new table and merge skaters and golies to the game events
    df = df.merge(df_skater, how='left', on='play_id')
    df = df.merge(df_goalie, how='left', on='play_id')

    # Apparently not all shots were on goal. No golie then. Keeping this rows anyway but replacing goalie by Zero
    df.goalie_id.fillna(0, inplace=True)


    #####################
    df.to_parquet('data/_5_with_player_ids.parquet', compression='brotli')
    #####################

    ###############################################################
    # adding player statistics
    df_player_info = pd.read_csv('data/nhl/nhl_stats/player_info.csv')

    # calculating overall savePercentage for each goalie
    df_goalie = pd.read_csv('data/nhl/nhl_stats/game_goalie_stats.csv')
    df_goalie = df_goalie.groupby('player_id').agg({'savePercentage':'mean'}).reset_index()
    df_goalie.rename(columns={'player_id':'goalie_id'}, inplace=True)

    # calculating overall statistics for each skater
    df_skater = pd.read_csv('data/nhl/nhl_stats/game_skater_stats.csv')
    df_skater = df_skater.groupby('player_id').agg({'goals':'sum', 'shots':'sum', 'assists':'sum', 'timeOnIce':'sum'}).reset_index()
    df_skater.rename(columns={'player_id':'skater_id'}, inplace=True)

    # merging it together in the training dataset
    df = df.merge(df_skater, how='left', on='skater_id')
    df = df.merge(df_goalie, how='left', on='goalie_id')

    # filling savePercentage
    df.savePercentage.fillna(df.savePercentage.median(), inplace=True)
    # drop all remaining rows with NaNs
    df.dropna(inplace=True)

    #####################
    df.to_parquet('data/_6_with_player_stats.parquet', compression='BROTLI')
    #####################

    ###############################################################
    # adding player salaries
    # Data salaries from another dataset
    df_players_train = pd.read_csv('data/nhl/nhl_salaries/train.csv')
    df_players_test = pd.read_csv('data/nhl/nhl_salaries/test.csv')
    df_player_stats = pd.concat([df_players_train, df_players_test])

    df_player_stats = df_player_stats[['First Name', 'Last Name', 'Nat', 'Salary']].rename(columns={'First Name':'firstName', 'Last Name':'lastName', 'Nat':'nationality', 'Salary':'salary'})

    # prepare player_info for merging
    df_player_info.rename(columns={'player_id':'skater_id'}, inplace=True)

    #add name and nationality of the skater to the main dataset
    df = df.merge(df_player_info[['skater_id', 'firstName', 'lastName', 'nationality']], how='left', on='skater_id')

    # add salary based on first name, last name, and the nationality
    df = df.merge(df_player_stats, how='left', on=['firstName', 'lastName', 'nationality'])

    # replace missing salary
    df.salary.fillna(df.salary.median(), inplace=True)
    df.savePercentage.fillna(df.savePercentage.median(), inplace=True)

    # cut off outliers
    df = df[df.goals<400]
    df = df[df.shots<4000]
    df = df[df.assists<700]
    df = df[df.salary<10000000]

    #####################
    df.to_parquet('data/_7_with_player_salary.parquet', compression='BROTLI')
    #####################

    ###############################################################
    # selecting short distance shots
    df_short_dist = df[df.distance<30]

    #####################
    df_short_dist.to_parquet('data/_8_short_dist.parquet', compression='BROTLI')
    #####################


    ###############################################################
    # selecting long distance shots
    df_long_dist = df[df.distance>=30]

    #####################
    df_long_dist.to_parquet('data/_9_long_dist.parquet', compression='BROTLI')
    #####################
    
    print('Data extraction done!')<jupyter_output><empty_output><jupyter_text>--- Extract datasets:<jupyter_code>extract_dataset(force=False)<jupyter_output>Started data extraction...
Data extraction done!<jupyter_text>---Testing dataset in training<jupyter_code>df = pd.read_parquet('data/_6_with_player_stats.parquet')
# starting training 
run_logistic_regression(df, c_matrix=False, r_curve=False);
run_lightgbm_classifier(df, c_matrix=False, r_curve=False);<jupyter_output>========== Logistic Regression ==========
Accuracy: 0.5516
F1 Score: 0.4954
AUC-ROC Score: 0.5870
-----------------------------------------
Precision: 0.5687
Recall: 0.4389
=========================================
========== LightGBM Classifier ==========
Accuracy: 0.6974
F1 Score: 0.7119
AUC-ROC Score: 0.7595
-----------------------------------------
Precision: 0.6812
Recall: 0.7455
=========================================
{'fraction_non_alphanumeric': 0.2168723447299211, 'fraction_numerical': 0.011733764920089014, 'mean_word_length': 4.284339925173704, 'pattern_counts': {'":': 0, '<': 18, '<?xml version=': 0, '>': 12, 'https://': 2, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 1, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '249230', 'license': 'MIT', 'n_tokens_mistral': 3265, 'n_tokens_neox': 3023, 'n_words': 635, 'path': '9_nhl_data_pipeline.ipynb', 'repo_name': 'rmnng/dsnanocapstone', 'size': '9886'}