# This Python 3 environment comes with many helpful analytics libraries installed
# It is defined by the kaggle/python Docker image: https://github.com/kaggle/docker-python
# For example, here's several helpful packages to load

import numpy as np # linear algebra
import pandas # data processing, CSV file I/O (e.g. pd.read_csv)
from pprint import pprint
#import zipfile
import time
#import json
import io
g = 0

eventdir = '../input/baseball-events-from-retrosheetorg/'
gamedir = '../input/baseball-events-from-retrosheetorg/gl1871_2020/'

# Input data files are available in the read-only "../input/" directory
# For example, running this (by clicking run or pressing Shift+Enter) will list all files under the input directory

roster_columns_names = ['playerID', 'nameLast', 'nameFirst', 'batting_hand', 'throwing_hand', 'team', 'position']


import os
# for dirname, _, filenames in os.walk('/kaggle/input'):
#     for filename in filenames:
#         print(os.path.join(dirname, filename))
print('Packages loaded!')
# You can write up to 20GB to the current directory (/kaggle/working/) that gets preserved as output when you create a version using "Save & Run All" 
# You can also write temporary files to /kaggle/temp/, but they won't be saved outside of the current session

"""
# Read ancillary data
"""
s = time.time()
#[x for x in os.listdir(eventdir) if x[-4:] == '.csv']
teams_df = pandas.read_csv(eventdir+'teamids.csv', header=None)
teams_df.columns = ['teamID','league','city','nickname','yearFirst','yearLast']
teams_df = teams_df.drop(0)
teams_df.loc[:, 'yearFirst'] = pandas.to_numeric(teams_df['yearFirst'], errors='coerce', downcast='integer')
teams_df.loc[:, 'yearLast'] = pandas.to_numeric(teams_df['yearLast'], errors='coerce', downcast='integer')
#teams_df = teams_df.set_index('teamID')

teams_nicknames_df = pandas.read_csv(eventdir+'teams_nicknames.csv')
teams_nicknames_df.index.name = 'nicknameID'

ballparks_df = pandas.read_csv(eventdir+'ballparks.csv')
ballparks_df.columns = [x.lower() for x in ballparks_df.columns]
ballparks_df = ballparks_df.rename(columns={'parkid': 'parkID'})
ballparks_df = ballparks_df.set_index('parkID')

biodata_df = pandas.read_csv(eventdir+'biodata.csv')
biodata_df.columns = [x.lower().replace(' ','_') for x in biodata_df.columns]

print('\tfixing columns and data types...')
biodata_df = biodata_df.rename(columns={'playerid': 'playerID', 'last': 'nameLast', 'first':'nameFirstMiddle', 'play_debut': 'player_debut', 'play_lastgame': 'player_lastgame'})
for x in [x for x in biodata_df.columns if (('debut' in x) or ('lastgame') in x)]:
    biodata_df.loc[:, x] = pandas.to_datetime(biodata_df[x], errors='coerce')
biodata_df = biodata_df.assign(nameFirst = biodata_df['nameFirstMiddle'].apply(lambda x: str(x).split(' ')[0]))
biodata_df = biodata_df.assign(nameMiddle = biodata_df[biodata_df['nameFirstMiddle'].apply(lambda x: str(x).find(" ")) > -1]['nameFirstMiddle'].apply(lambda x: str(x).split(" ")[1]))
biodata_df.loc[:, 'nameMiddle'] = biodata_df['nameMiddle'].fillna('')
biodata_df = biodata_df.assign(height_inches = biodata_df['height'].dropna().apply(lambda x: float(x.split('-')[0])*12+float(x.split('-')[1])))
biodata_df.loc[biodata_df['hof'] == 'HOF', 'hof'] = True
biodata_df.loc[biodata_df['hof'] == 'NOT', 'hof'] = False
biodata_df.loc[:, 'HOF'] = biodata_df['hof'].astype('bool')

print('\tfinding players vs. non-players...')
biodata_df = biodata_df.assign(is_player = np.nan)
biodata_df.loc[biodata_df['player_debut'].notnull(), 'is_player'] = True
biodata_df.loc[biodata_df['player_debut'].isnull(), 'is_player'] = False
print('fixing duplicate(s)...')
biodata_df.loc[(biodata_df['playerID'] == 'leppd101') & (biodata_df['nameFirstMiddle'] == 'Donald George'), 'playerID'] = 'leppd102'
#biodata_df

print('saving output data...')
teams_df.to_csv('teams.csv', encoding='utf-8')
teams_nicknames_df.to_csv('teams_nicknames.csv', encoding='utf-8')
ballparks_df.to_csv('ballparks.csv', encoding='utf-8')
biodata_df.to_csv('biodata.csv', encoding='utf-8')

e = time.time()

print('Read and saved:')
print('\t{0:,.0f} teams'.format(len(teams_df)))
print('\t{0:,.0f} nicknames'.format(len(teams_nicknames_df)))
print('\t{0:,.0f} ballparks'.format(len(ballparks_df)))
print('\t{0:,.0f} biographies'.format(len(biodata_df)))
print('in {0:,.1f} seconds!'.format(e-s))
#teams_df
#teams_df[teams_df['yearLast'].apply(lambda x: int(x) == np.nan)]#.dtypes
"""
# Parse roster data
"""
s = time.time()
firstyear = 1903
finalyear = 2020

roster_df = pandas.DataFrame(data=None, columns=roster_columns_names)
unsorted_rosterfiles = []
sorted_rosterfiles = []
for dirname, _, filenames in os.walk('/kaggle/input'):
    for filename in filenames:
        if (filename[-4:] == '.ROS'):
            unsorted_rosterfiles.append('{0:}/{1:}'.format(dirname,filename))
#        print([x for x in os.path.join(dirname, filename) if x[-4:] == '.ROS'])

yearset = sorted(set([int(x[-8:-4]) for x in unsorted_rosterfiles if x[-8] in ['1', '2']]))


for thisyear in yearset:
    sorted_rosterfiles += sorted([x for x in unsorted_rosterfiles if ((x[-8:-4] == str(thisyear)) & (x.split('/')[-2] not in ['allas', 'allpost']))])
    sorted_rosterfiles += sorted([x for x in unsorted_rosterfiles if ((x[-8:-4] == str(thisyear)) & (x.split('/')[-2] == 'allas'))])
    sorted_rosterfiles += sorted([x for x in unsorted_rosterfiles if ((x[-8:-4] == str(thisyear)) & (x.split('/')[-2] == 'allpost'))])
e = time.time()
g += e-s

print('Found {0:,.0f} files in {1:.1f} seconds!'.format(len(sorted_rosterfiles), e-s))


s = time.time()
rosters_df = pandas.DataFrame()

current_year = firstyear

for thisfile in sorted_rosterfiles:
    #print(thisfile)
    this_roster_df = pandas.read_csv(thisfile, names=roster_columns_names, header=None)
    this_roster_df = this_roster_df[(this_roster_df['playerID'] != '\x1a')]
#     this_roster_df = this_roster_df.assign(allstargame = False)
#     this_roster_df = this_roster_df.assign(postseason = False)
    thisyear = int(thisfile[-8:-4])
    #print(thisyear)
    if (thisyear != current_year):
        if (np.mod(thisyear, 10) == 0):
            print('Reading roster files from {0:.0f}...'.format(thisyear))
        current_year = thisyear
#     if (current_year > 1960):
#         break
    this_roster_df = this_roster_df.assign(theyear = thisyear)
    if (thisyear == firstyear):
        this_roster_df.loc[:, 'team'] = thisfile[-11:-8]
        rosters_df = pandas.concat((rosters_df, this_roster_df), axis=0)
    else:
        if ('allas' in thisfile):
#            this_roster_df.loc[:, 'allstargame'] = True
            if (len(this_roster_df[(~this_roster_df['playerID'].isin(rosters_df[(rosters_df['theyear'] == thisyear)]['playerID'].tolist()))]) > 0):
                #print(thisfile)
                if (thisyear == 1949):
                    roster_df = pandas.concat((roster_df, this_roster_df[(~this_roster_df['playerID'].isin(rosters_df[(rosters_df['theyear'] == thisyear)]['playerID'].tolist()))]), axis=0)
                else:
                    print('\t{0:.0f}: Found {1:.0f} players in all-star game not on regular season roster!'.format(thisyear, len(this_roster_df[(~this_roster_df['playerID'].isin(rosters_df[(rosters_df['theyear'] == thisyear)]['playerID'].tolist()))])))
                    print(this_roster_df[(~this_roster_df['playerID'].isin(rosters_df[(rosters_df['theyear'] == thisyear)]['playerID'].tolist()))])
        elif ('allpost' in thisfile):
#            this_roster_df.loc[:, 'postseason'] = True
            if (len(this_roster_df[(~this_roster_df['playerID'].isin(rosters_df[(rosters_df['theyear'] == thisyear)]['playerID'].tolist()))]) > 0):
#                print(thisfile[-11:-8])
                if (thisyear >= 1915):
                    print('\t{0:.0f}: Found {1:.0f} players in postseason not on regular season roster!'.format(thisyear, len(this_roster_df[(~this_roster_df['playerID'].isin(rosters_df[(rosters_df['theyear'] == thisyear)]['playerID'].tolist()))])))
                this_roster_df.loc[:, 'team'] = thisfile[-11:-8]
                if (thisyear == 1945):
                    this_roster_df.loc[(this_roster_df['playerID'] == 'mccuc101'), 'position'] = 'PH'
                    rosters_df = pandas.concat((rosters_df, this_roster_df[(~this_roster_df['playerID'].isin(rosters_df[(rosters_df['theyear'] == thisyear)]['playerID'].tolist()))]), axis=0)
                elif ((thisyear < 1915) | (thisyear == 1949)):
                    rosters_df = pandas.concat((rosters_df, this_roster_df[(~this_roster_df['playerID'].isin(rosters_df[(rosters_df['theyear'] == thisyear)]['playerID'].tolist()))]), axis=0)
                else:
                    print(this_roster_df[(~this_roster_df['playerID'].isin(rosters_df[(rosters_df['theyear'] == thisyear)]['playerID'].tolist()))])
                    #print(this_roster_df[(~this_roster_df['playerID'].isin(rosters_df[(rosters_df['theyear'] == thisyear)]['playerID'].tolist()))])
#                 rosters_df = pandas.concat((rosters_df, this_roster_df), axis=0)
        else:
            rosters_df = pandas.concat((rosters_df, this_roster_df), axis=0)

print('dropping weird duplicates...')
rosters_df = rosters_df.drop(rosters_df[(rosters_df['playerID'] == 'rackm101') & (rosters_df['theyear'] == 1949)].tail(1).index)
rosters_df = rosters_df.drop(rosters_df[(rosters_df['playerID'] == 'donap101') & (rosters_df['theyear'] == 1910)].tail(1).index)
rosters_df = rosters_df.drop(rosters_df[(rosters_df['playerID'] == 'robed004') & (rosters_df['theyear'] == 2019)].tail(1).index)
rosters_df = rosters_df.drop(rosters_df[(rosters_df['playerID'] == 'powem102') & (rosters_df['theyear'] == 1905)].tail(1).index)
rosters_df = rosters_df.drop(rosters_df[(rosters_df['playerID'] == 'crand101') & (rosters_df['theyear'] == 1913)].tail(1).index)

#rosters_df.groupby(['playerID', 'theyear', 'team']).size().sort_values(ascending=False)[0:5]

rosters_df = rosters_df.reset_index(drop=True)
rosters_df.index.name = 'rownumber'

print('saving rosters...')
rosters_df.to_csv('rosters.csv', encoding='utf-8')
e = time.time()
print('Parsed and saved {0:,.0f} roster rows in {1:,.1f} seconds!'.format(len(rosters_df), e-s))
#rosters_df.sample(5)

#rosters_df.groupby(['playerID', 'theyear', 'team']).size().sort_values(ascending=False)
#rosters_df[rosters_df['theyear'] == 1903]
"""
# Get game data for use in events
"""
s = time.time()

firstyear = 1874
finalyear = 2020


filenames = []
print('Finding filenames...')
# for thisyear in years:
#     filenames.append('GL'+str(thisyear)+'.TXT')

gamedata_df = pandas.DataFrame()

print(gamedir)
filenames = sorted([gamedir+x for x in os.listdir(gamedir)])

print('Found {0:,.0f} files...'.format(len(filenames)))
for thisfile in filenames:
    thisyear = int(thisfile[-8:-4])
    if (np.mod(thisyear, 20) == 0):
        print('\tParsing game logs from {0:.0f}...'.format(thisyear))
    df = pandas.read_csv(thisfile, header=None, dtype={19: str, 20:str})
    gamedata_df = pandas.concat((gamedata_df, pandas.read_csv(thisfile, header=None)), axis=0)

# Columns given at http://www.retrosheet.org/gamelogs/glfields.txt
colnames = ['date','gameno','dayofweek']
colnames += ['visitor','visitor_league','visitor_gameno']
colnames += ['home','home_league','home_gameno']
colnames += ['visitor_score','home_score']
colnames += ['nOuts','day_night','completed','forfeit','protest','parkID','attendance','length']
colnames += ['visitor_linescore','home_linescore']
colnames += ['visitor_ab','visitor_h','visitor_2b','visitor_3b','visitor_hr']
colnames += ['visitor_rbi','visitor_sac','visitor_sacfly','visitor_hbp']
colnames += ['visitor_bb','visitor_ibb','visitor_k','visitor_sb','visitor_cs']
colnames += ['visitor_gidp','visitor_ci','visitor_lob','visitor_nPitchers']
colnames += ['visitor_er_ind','visitor_er_team','visitor_wp','visitor_balk']
colnames += ['visitor_putout','visitor_assist','visitor_e','visitor_pb']
colnames += ['visitor_dp','visitor_tp','home_ab','home_h','home_2b','home_3b']
colnames += ['home_hr','home_rbi','home_sac','home_sacfly','home_hbp','home_bb']
colnames += ['home_ibb','home_k','home_sb','home_cs','home_gidp','home_ci']
colnames += ['home_lob','home_nPitchers','home_er_ind','home_er_team','home_wp']
colnames += ['home_balk','home_putout','home_assist','home_e','home_pb']
colnames += ['home_dp','home_tp']
colnames += ['ump_home_id','ump_home_name','ump_1b_id']
colnames += ['ump_1b_name','ump_2b_id','ump_2b_name','ump_3b_id','ump_3b_name']
colnames += ['ump_lf_id','ump_lf_name','ump_rf_id','ump_rf_name']
colnames += ['visitor_manager_id','visitor_manager_name','home_manager_id','home_manager_name']
colnames += ['winning_pitcher_id','winning_pitcher_name','losing_pitcher_id']
colnames += ['losing_pitcher_name','saving_pitcher_id','saving_pitcher_name']
colnames += ['gw_rbi_batter_id','gw_rbi_batter_name','visitor_starting_pitcher_id']
colnames += ['visitor_starting_pitcher_name','home_starting_pitcher_id']
colnames += ['home_starting_pitcher_name']
colnames += ['visitor_batter1_id','visitor_batter1_name','visitor_batter1_pos']
colnames += ['visitor_batter2_id','visitor_batter2_name','visitor_batter2_pos']
colnames += ['visitor_batter3_id','visitor_batter3_name','visitor_batter3_pos']
colnames += ['visitor_batter4_id','visitor_batter4_name','visitor_batter4_pos']
colnames += ['visitor_batter5_id','visitor_batter5_name','visitor_batter5_pos']
colnames += ['visitor_batter6_id','visitor_batter6_name','visitor_batter6_pos']
colnames += ['visitor_batter7_id','visitor_batter7_name','visitor_batter7_pos']
colnames += ['visitor_batter8_id','visitor_batter8_name','visitor_batter8_pos']
colnames += ['visitor_batter9_id','visitor_batter9_name','visitor_batter9_pos']
colnames += ['home_batter1_id','home_batter1_name','home_batter1_pos']
colnames += ['home_batter2_id','home_batter2_name','home_batter2_pos']
colnames += ['home_batter3_id','home_batter3_name','home_batter3_pos']
colnames += ['home_batter4_id','home_batter4_name','home_batter4_pos']
colnames += ['home_batter5_id','home_batter5_name','home_batter5_pos']
colnames += ['home_batter6_id','home_batter6_name','home_batter6_pos']
colnames += ['home_batter7_id','home_batter7_name','home_batter7_pos']
colnames += ['home_batter8_id','home_batter8_name','home_batter8_pos']
colnames += ['home_batter9_id','home_batter9_name','home_batter9_pos']
colnames += ['misc','acquisition']

gamedata_df.columns = colnames

print('Calculating gameID...')
gamedata_df = gamedata_df.assign(gameID = gamedata_df.apply(lambda row: row['home']+str(row['date'])+str(row['gameno']), axis=1))

print('Fixing data types...')
gamedata_df['date'] = pandas.to_datetime(gamedata_df['date'], format='%Y%m%d', errors='coerce')
gamedata_df['visitor_linescore'] = '"'+gamedata_df['visitor_linescore'].astype('str')+'"'
gamedata_df['home_linescore'] = '"'+gamedata_df['home_linescore'].astype('str')+'"'
gamedata_df.loc[gamedata_df['visitor_linescore'] == 'nan', 'visitor_linescore'] = 'n/a'
gamedata_df.loc[gamedata_df['home_linescore'] == 'nan', 'home_linescore'] = 'n/a'

gamedata_df = gamedata_df.set_index('gameID')

print('Saving games...')
gamedata_df.to_csv('games.csv', encoding='utf-8')

e = time.time()
print('\nParsed and saved {0:,.0f} game logs in {1:,.0f} seconds!'.format(len(gamedata_df), e-s))
    
gamedata_df.sample(1).T[0:32]