{"cells":[{"metadata":{},"cell_type":"markdown","source":"# Missing and duplicated event data\n\nIt turns out that the current score columns in the event files are mostly 0, except for some entries that look like junk\nI need the current score columns to identify garbage time, so I'll have to supply current score columns.\n\nWhile I'm complaining, I might as well point out that I've found a few games that contain duplicated events,\nwith different event ids. I found a way to eliminate duplicate events, but it had a side effect of removing legitimate\nfree throws (which can produce legitimate duplicate events - same player, type of event, and same elapsed seconds,\nsince the clock doesn't run during three throws) This doesn't happen often (a fraction of a percent of games),\nso it doesn't seem worth the effort to work on a more complex way to identify and fix games with duplicated events.\n\nThis notebook's output will be fixed versions of the regular season event files."},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"# This Python 3 environment comes with many helpful analytics libraries installed\n# It is defined by the kaggle/python docker image: https://github.com/kaggle/docker-python\n# For example, here's several helpful packages to load in \n\nimport numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\n\n# Input data files are available in the \"../input/\" directory.\n# For example, running this (by clicking run or pressing Shift+Enter) will list all files under the input directory\n\nimport os\nfor dirname, _, filenames in os.walk('/kaggle/input'):\n    for filename in filenames:\n        print(os.path.join(dirname, filename))\n\n# Any results you write to the current directory are saved as output.","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","trusted":true},"cell_type":"code","source":"import re\nimport time","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"home = '/kaggle/input/google-cloud-ncaa-march-madness-2020-division-1-mens-tournament'","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"raw","source":"# this is the duplicate-event fix that also deleted legitimate free throws\n# df should be a DataFrame with an EventID column\ndef eliminate_dup_events(df):\n    return df[['EventID']].join(df.drop('EventID',axis=1).drop_duplicates(),how='right').reset_index(drop=True)"},{"metadata":{"trusted":true},"cell_type":"code","source":"# this regular expression recognizes the point-scoring events, including the number of points scored\npoint_pat = re.compile('^made([1-3])$')\n\n# to help in computing the current scores, this function determines whether an event provides points for a given team\n# the row is expected to have three columns: WTeamID or LTeamID, EventTeamID, and EventType\ndef event_points(row):\n    if row[0] == row[1]:\n        made = point_pat.match(str(row[2]))\n        if made is None:\n            points = 0\n        else:\n            points = int(made.groups()[0])\n    else:\n        points = 0\n    return points\n\n# df should be a DataFrame with WTeamID, LTeamID, EventTeamID and EventType columns\n# returns two Series, one for the winner's event points and the other for the loser's\ndef compute_event_points(df):\n    return (df.loc[:,['WTeamID','EventTeamID','EventType']].apply(event_points,axis=1),\n            df.loc[:,['LTeamID','EventTeamID','EventType']].apply(event_points,axis=1))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# df should be a DataFrame with Season, DayNum, WTeamId, LTeamID, EventTeamID, and EventType columns as described\n# in the Data section of this competition\n# this function adds columns for the winner's and loser's current score\n# the column names are optional parameters\ndef add_current_scores(df,WCurScore='WCurScore',LCurScore='LCurScore'):\n    # compute the event points - note that this can be done without regard to game boundaries\n    WEventPoints, LEventPoints = compute_event_points(df)\n    \n    # next, find the game boundaries\n    # iterrows() returns a 2-tuple consisting of the index and the column values\n    # because of drop_duplicates(), the index is the index of the first event of the game \n    games = df.loc[:,['Season','DayNum','WTeamID','LTeamID']].drop_duplicates()\n    starts = [game[0] for game in games.iterrows()]\n    ends = [game[0]-1 for game in games.iterrows()][1:] + [df.shape[0]-1] \n    \n    # now make the current score columns, going one game at a time\n    df.loc[:,'WCurScore'] = pd.concat([WEventPoints.loc[starts[n]:ends[n]].cumsum() for n in range(games.shape[0])]).to_numpy()\n    df.loc[:,'LCurScore'] = pd.concat([LEventPoints.loc[starts[n]:ends[n]].cumsum() for n in range(games.shape[0])]).to_numpy()\n    \n# a small number of games yield a mismatch between their final score and the final values of their current scores\n# for example, there are 11 such games in 2019 and 29 in 2018\n# df should be an events DataFrame\ndef find_score_issue_games(df):\n    games = df.loc[:,['Season','DayNum','WTeamID','LTeamID']].drop_duplicates()\n    starts = [game[0] for game in games.iterrows()]\n    ends = [game[0]-1 for game in games.iterrows()][1:] + [df.shape[0]-1] \n    endgames = df.loc[ends,:]\n    oops = endgames[(endgames['WFinalScore']!=endgames['WCurScore'])|(endgames['LFinalScore']!=endgames['LCurScore'])]\n    print(f'{oops.shape[0]} games with final score/event consistency issues ({oops.shape[0]*100/games.shape[0]:.2f}%)')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"for year in [2015, 2016, 2017, 2018, 2019]:\n    print(f'working on {year}')\n    \n    # read an event dataset\n    read_start = time.time()\n    events = pd.read_csv(f'{home}/MEvents{year}.csv')\n    read_end = time.time()\n    read_elapsed = read_end - read_start\n    print(f'{read_elapsed:.2f} sec to read {events.shape[0]} rows ({events.shape[0]/read_elapsed:.2f} rows/sec)')\n    \n    # fill in the current score columns\n    fill_start = time.time()\n    add_current_scores(events)\n    fill_end = time.time()\n    fill_elapsed = fill_end - fill_start\n    print(f'{fill_elapsed:.2f} sec to process {events.shape[0]} events ({events.shape[0]/fill_elapsed:.2f} events/sec)')\n    \n    # write the results\n    write_start = time.time()\n    events.to_csv(f'Mevents_reg_season_{year}.csv',header=True,index=False)\n    write_end = time.time()\n    write_elapsed = write_end - write_start\n    print(f'{write_elapsed:.2f} sec to write {events.shape[0]} rows ({events.shape[0]/write_elapsed:.2f} rows/sec)')\n    \n    find_score_issue_games(events)\n    print('')","execution_count":null,"outputs":[]}],"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat":4,"nbformat_minor":4}