# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

# ## Capstone 2: Data Wrangling
#

# ### Overview
#
# How can we use data from 2015-2018 to predict/assess what type of pitch should be thrown in a given at bat?
#
# This can be looked at from both the pitching team’s perspective (what is the ideal pitch for a given situation), and from the batting team (what pitch should I expect, assuming that the pitcher will choose the optimal pitch). Using the ab_id, we can link the data in the pitches csv to the data in the atbats csv to look at the outcomes of at bats along with the exact type and order of pitches thrown. We can add to this by using the batter_id and pitcher_id to gather specific data for a given hitter or pitcher by linking with the player_names csv. 
#
# The data will be acquired from https://www.kaggle.com/pschale/mlb-pitch-data-20152018?select=pitches.csv
# which was scraped from  http://gd2.mlb.com/components/game/mlb/.
#

import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
import os
import pandas_profiling
from library.sb_utils import save_file
# %matplotlib inline

from pathlib import Path
import pandas_profiling
import requests
from pandas_profiling.utils.cache import cache_file
from pandas_profiling import ProfileReport

import datetime as dt
from datetime import datetime


# ### Because I found myself repeatedly using this technique, I decided to define the following function to show missing values

#missing values function
def missing(df):
    '''Takes dataframe as argument, 
    returns count and % of missing values for each column'''
    import pandas as pd
    miss = pd.concat([df.isnull().sum(), 100 * df.isnull().mean()], axis = 1)
    miss.columns = ['count','%']
    return miss.sort_values(by = ['count'], ascending = False)


# ### Import the data
# #### First we will do a small file (player_names), and then do the rest

#

#the CSV data files are in the data/raw directory
#player names
names = pd.read_csv('../data/raw/player_names.csv')

names.info()

names.head()

# ## Import Games and At-Bats Data

#the CSV data files are in the data/raw directory
#at bat info
atbats = pd.read_csv('../data/raw/atbats.csv')
#games info 
games = pd.read_csv('../data/raw/games.csv')


atbats.info()

games.info()

# ## Import Pitch Data (800 mb)

#the CSV data files are in the data/raw directory
pitches = pd.read_csv('../data/raw/pitches.csv')

pitches.info()

pitches.head()

# ## Check missing values for at bats, players, pitches

#missing values for each column in pitches
missingP = pd.concat([pitches.isnull().sum(), 100 * pitches.isnull().mean()], axis=1)
missingP.columns=['count', '%']
missingP.sort_values(by=['count'], ascending = False)

#missing values for each column in atbats
missingAB = pd.concat([atbats.isnull().sum(), 100 * atbats.isnull().mean()], axis=1)
missingAB.columns=['count', '%']
missingAB.sort_values(by=['count'], ascending = False)

# +
#missing values for players

missingPlayers = pd.concat([names.isnull().sum(), 100 * names.isnull().mean()], axis=1)
missingPlayers.columns=['count', '%']
missingPlayers.sort_values(by=['count'], ascending = False)
# -

#missing values for games
missingG = pd.concat([games.isnull().sum(), 100 * games.isnull().mean()], axis=1)
missingG.columns=['count', '%']
missingG.sort_values(by=['count'], ascending = False)

# ### There are no missing values in the AB and Names dataframes
#
# ### Because there are no columns with more than 0.5% of values missing (i.e. no large chunks missing) in the pitches and games dataframes, I am just going to remove rows with missing values.
#
# ### I think I want to concat the df's first (link the abs, pitches, and games) before removing though. 

#maybe do groupby ab id for pitches first. 
pitches.ab_id.unique().shape

atbats.ab_id.unique().shape

# ### It appears that there are 159 at bats missing from the pitches df. Ill start by trying to groupy abid on the pitches df and then decide how I want to join the dfs
#

# Pitch Type Definitions
# CH - Changeup
#
# CU - Curveball
#
# EP - Eephus*
#
# FC - Cutter
#
# FF - Four-seam Fastball
#
# FO - Pitchout (also PO)*
#
# FS - Splitter
#
# FT - Two-seam Fastball
#
# IN - Intentional ball
#
# KC - Knuckle curve
#
# KN - Knuckeball
#
# PO - Pitchout (also FO)*
#
# SC - Screwball*
#
# SI - Sinker
#
# SL - Slider
#
# UN - Unknown*
#
# these pitch types occur rarely
# Code Definitions #
# While these aren't spelled out anywhere, play descriptions allowed confident identification of these codes
#
# B - Ball
#
# *B - Ball in dirt
#
# S - Swinging Strike
#
# C - Called Strike
#
# F - Foul
#
# T - Foul Tip
#
# L - Foul Bunt
#
# I - Intentional Ball
#
# W - Swinging Strike (Blocked)
#
# M - Missed Bunt
#
# P - Pitchout
#
# Q - Swinging pitchout
#
# R - Foul pitchout
#
# Values that only occur on last pitch of at-bat:
#
# X - In play, out(s)
#
# D - In play, no out
#
# E - In play, runs
#
# H - Hit by pitch
#
# Note: all codes, except for H, come directly from the XML files. All at-bats with code H were given no code in the XMLs.

pitches.columns; atbats.columns


#at bats with names
names.head()

atbats.head()

atbatNames = names.merge(atbats, how = 'right', left_on = 'id', right_on = 'pitcher_id' )

atbatNames.head()

missingTest = pd.concat([atbatNames.isnull().sum(), 100 * atbatNames.isnull().mean()], axis=1)
missingTest.columns=['count', '%']
missingTest.sort_values(by=['count'], ascending = False)

names[names['first_name']=='Clayton']

# ### Start with <NAME>, id 477132

KershawAB = atbatNames[atbatNames['id']==477132]

KershawAB.head()

KershawPitches = pitches[pitches['ab_id'].isin(KershawAB['ab_id'])]

KershawPitches.head()

KershawPitches.info()

Kershaw = KershawAB.merge(KershawPitches, how = 'right', left_on = 'ab_id', right_on = 'ab_id' )

Kershaw.head()

Kershaw.info()

Kershaw.describe()

# ### Now include game ID

CKdata = Kershaw.merge(games, on = 'g_id', how = 'left' )

CKdata.columns


#missing values for each column
missingCK = pd.concat([CKdata.isnull().sum(), 100 * CKdata.isnull().mean()], axis=1)
missingCK.columns=['count', '%']
missingCK.sort_values(by=['count'], ascending = False).head(len(CKdata.columns))

#     #missing values function
#     def missing(df):
#         '''Takes dataframe as argument, 
#         returns count and % of missing values for each column'''
#         import pandas as pd
#         miss = pd.concat([df.isnull().sum(), 100 * df.isnull().mean()], axis = 1)
#         miss.columns = ['count','%']
#         return miss.sort_values(by = ['count'], ascending = False)

missing(CKdata)

#     report = df.profile_report(sort=None, html={'style':{'full_width': True}}, progress_bar=False)
#     report
#     profile_report.to_file("tmp/example.html")
#     profile = ProfileReport(df, minimal=True)
#     
#     profile.to_file(output_file="output_min.html")

CKdata['date'].head(10)

CKdata['date'] = pd.to_datetime(CKdata['date'], infer_datetime_format=True)

CKdata.dtypes

kersh2015 = CKdata[CKdata.date < datetime.fromisoformat('2016-01-01')]

kersh2015.shape

#     # save the data to a new csv file
#     datapath = '../data/interim'
#     save_file(kersh2015, 'kersh2015.csv', datapath)

# +
#results2015 = kersh2015.groupby('ab_id')

# +
#https://stackoverflow.com/questions/46140609/one-to-many-left-outer-join-with-pandas-python
# -

kersh2015.columns

kersh2015.stand.value_counts()

kershR2015 = kersh2015[kersh2015.stand == 'R']
kershL2015 = kersh2015[kersh2015.stand == 'L']

type(kershR2015.pitch_type.value_counts())

kershR2015.pitch_type.value_counts().plot(kind='bar')
plt.xlabel('pitch type')
plt.ylabel('Count')
plt.title('Distribution of pitches against right handed hitters (Clayton Kershaw 2015)')
#plt.plot()

kershL2015.pitch_type.value_counts().plot(kind='bar')
plt.xlabel('pitch type')
plt.ylabel('Count')
plt.title('Distribution of pitches against left handed hitters (Clayton Kershaw 2015)')
#plt.plot()

#     #Code task 16#
#     #Create a seaborn boxplot of the ticket price dataframe we created above,
#     #with 'state' on the x-axis, 'Price' as the y-value, and a hue that indicates 'Ticket'
#     #This will use boxplot's x, y, hue, and data arguments.
#     plt.subplots(figsize=(12, 8))
#     sns.boxplot(x='state', y='Price', hue='Ticket', data=ticket_prices)
#     plt.xticks(rotation='vertical')
#     plt.ylabel('Price ($)')
#     plt.xlabel('State');

#pitch thrown in each count for righties and lefties. 
possible_counts = [(x,y) for x in range(4) for y in range(3)]
print(possible_counts)


bs = zip(kersh2015.loc[:,['b_count']], kersh2015.loc[:,['s_count']])

BS = kersh2015.groupby(by=['b_count','s_count'])

BS.plot()

# +
profile1 = ProfileReport(kersh2015, minimal=True)
    
#profile1.to_file(output_file="output1_min.html")
profile1.to_file("output.html")
# -

kershR2015.pitch_type.value_counts().plot(kind='bar')
plt.xlabel('pitch type')
plt.ylabel('Count')
plt.title('Distribution of pitches against right handed hitters (<NAME> 2015)')
#plt.plot()

StarcoderdataJupyterScriptsDedupFiltered