code/data_manipulation/Data_Clean.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "e2f33c78",
   "metadata": {},
   "outputs": [],
   "source": [
    "from re import T\n",
    "import pandas as pd\n",
    "import os\n",
    "import numpy as np\n",
    "import math\n",
    "\n",
    "# Matching dictionary for team names between events and tracking data\n",
    "TEAM_NAMES = {'Canada': 'Olympic (Women) - Canada', \n",
    "              'USA': 'Olympic (Women) - United States',\n",
    "              'Finland': 'Olympic (Women) - Finland',\n",
    "              'ROC': 'Olympic (Women) - Olympic Athletes from Russia',\n",
    "              'Switzerland': 'Olympic (Women) - Switzerland'}\n",
    "\n",
    "# Max number of frames to skip in velocity calculations\n",
    "MAX_FRAME_JUMP = 5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "ec9a760a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# This is for convenience to match our wd just in case the notebook is located elsewhere\n",
    "#os.chdir('/mnt/c/Users/thepi/OneDrive/Documents/Python/Hockey/Big-Data-Cup-2022')\n",
    "os.chdir('C:/Users/Paula/Desktop/Big-Data-Cup-2022-Private')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "0bf5960a",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_names_date(game):\n",
    "    # get's team names and date from the game file name\n",
    "    spl = game.split(' ')\n",
    "    date_comps = spl[0].split('-')\n",
    "    date = '{}/{}/{}'.format(*[dt.strip('0') for dt in date_comps[::-1]])\n",
    "    team_1 = TEAM_NAMES[spl[1]]\n",
    "    team_2 = TEAM_NAMES[spl[3]]\n",
    "    \n",
    "    return team_1, team_2, date\n",
    "    \n",
    "def get_speed(track):\n",
    "    # calculate velocity using displacement between frames\n",
    "    assert (track['track_id'] == track['track_id'].iloc[0]).all()\n",
    "    try:\n",
    "        frame_diff = np.diff(track['frame_id'])\n",
    "        nonvalid_speed = np.concatenate([[True],frame_diff>MAX_FRAME_JUMP])\n",
    "        next_valid_speed = np.concatenate([frame_diff<=MAX_FRAME_JUMP,[False]])\n",
    "        repl = nonvalid_speed & next_valid_speed\n",
    "        idx = np.where(repl)[0]+1\n",
    "        \n",
    "        x_spd = np.concatenate([[np.nan],np.diff(track['x_ft'])/frame_diff])*30\n",
    "        y_spd = np.concatenate([[np.nan],np.diff(track['y_ft'])/frame_diff])*30\n",
    "        \n",
    "        x_spd[repl] = x_spd[idx]\n",
    "        y_spd[repl] = y_spd[idx]\n",
    "\n",
    "    except Exception as e:\n",
    "        print(np.diff(track['frame_id']))\n",
    "        raise\n",
    "\n",
    "    return(x_spd, y_spd)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "6d4899df",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_pp_tracking(power_play_info_file = 'pp_info.csv',\n",
    "    play_by_play_data_file = 'pxp_womens_oly_2022_v2.csv',\n",
    "    tracking_dir = 'data',\n",
    "    pbp_dir = 'data'):\n",
    "    '''\n",
    "    Function for reading tracking and event data and matching relevant tracks to events\n",
    "    '''\n",
    "    play_by_play_data = pd.read_csv(os.path.join(tracking_dir,play_by_play_data_file))\n",
    "     # Adjusting clock seconds to include period so the game starts at 3600 and ends at 0 \n",
    "    # (there are no overtime periods in this data but if there will be in the future this will require handeling)\n",
    "    play_by_play_data.loc[:,'clock_seconds'] = (3-play_by_play_data.period) * 1200 + play_by_play_data.clock_seconds\n",
    "    \n",
    "    power_play_info = pd.read_csv(os.path.join(pbp_dir,power_play_info_file))\n",
    "    power_play_info.loc[:,'start_game_clock_seconds'] +=  (3-power_play_info.start_period)*1200\n",
    "    power_play_info.loc[:,'end_game_clock_seconds'] +=  (3-power_play_info.end_period)*1200\n",
    "    \n",
    "    power_play_info['relevant_events'] = np.nan\n",
    "\n",
    "    # Big data frame for all games and power plays\n",
    "    relevant_eventses = []\n",
    "\n",
    "    # Run through all the games and powerplays\n",
    "    for ix, pp in power_play_info.iterrows():\n",
    "        relevant_events = None\n",
    "        game = pp['game_name']\n",
    "        # print(game)\n",
    "        #if game!='2022-02-14 Finland at USA':\n",
    "        \n",
    "        # Read relevant files\n",
    "        game_files = os.listdir(os.path.join(tracking_dir,game))\n",
    "        tracking_data_name = os.path.join(tracking_dir,game,f'{game} P{pp[\"end_period\"]} PP{pp[\"penalty_number\"]}.csv')\n",
    "        tracking_info_name = os.path.join(tracking_dir,game,f'videoShotsInfo_{game} P{pp[\"end_period\"]} PP{pp[\"penalty_number\"]}.csv')\n",
    "        roster_info_name = os.path.join(tracking_dir, game, f'{game} roster.csv')\n",
    "        roster_info = pd.read_csv(roster_info_name, index_col = 0)\n",
    "        \n",
    "        # Get team names\n",
    "        away, _ , home = game.split(' ')[-3:]\n",
    "        roster_info.loc[roster_info['team']=='away','team'] = away\n",
    "        roster_info.loc[roster_info['team']=='home','team'] = home\n",
    "\n",
    "        # mark goalies for each team that is playing (used later)\n",
    "        goalie = {f'{rf[\"team\"]}_{rf[\"jn\"]}':rf['position']=='Goalie' for _,rf in roster_info.iterrows()}\n",
    "\n",
    "        # read entire tracking file and calculate speeds and skating directions\n",
    "        if os.path.split(tracking_data_name)[1] in game_files:\n",
    "            print(tracking_data_name)\n",
    "            tracking_data = pd.read_csv(tracking_data_name)\n",
    "            # add column identifiyng whether each track belongs to a goal keeper\n",
    "            tracking_data['goalie'] = [goalie[f'{tr[\"team_name\"]}_{tr[\"jersey_number\"]}'] if f'{tr[\"team_name\"]}_{tr[\"jersey_number\"]}' in goalie.keys() else False for _,tr in tracking_data.iterrows()]\n",
    "\n",
    "\n",
    "            tracking_data['vel_x'] = np.nan\n",
    "            tracking_data['vel_y'] = np.nan\n",
    "            tracking_data['angle'] = np.nan\n",
    "            tr_ids = tracking_data.track_id.unique()\n",
    "            # Calculate velocity and skating angle\n",
    "            for tr_id in tr_ids:\n",
    "                idxs = tracking_data.track_id==tr_id\n",
    "                track = tracking_data.loc[idxs]\n",
    "                if len(track) <= 5:\n",
    "                    continue\n",
    "                x_spd,y_spd = get_speed(track)\n",
    "                tracking_data.loc[idxs,'vel_x'] = x_spd\n",
    "                tracking_data.loc[idxs,'vel_y'] = y_spd\n",
    "                tracking_data['angle'] = np.arctan2(tracking_data['vel_y'],tracking_data['vel_x'])\n",
    "            tracking_info = pd.read_csv(tracking_info_name)\n",
    "        else:\n",
    "            # Can't file this tracking file\n",
    "            print('Tracking file missing:\\n{}'.format(tracking_data_name))\n",
    "            relevant_eventses.append(None)\n",
    "            continue\n",
    "        team_1, team_2, date = get_names_date(game)\n",
    "       \n",
    "        # filter relevant plays for current period and power play\n",
    "        relevant_events = play_by_play_data.loc[(play_by_play_data['game_date'] == date) & \n",
    "                                    ((play_by_play_data['team_name'] == team_1) |\n",
    "                                        (play_by_play_data['team_name'] == team_2))]\n",
    "\n",
    "        # Because of the time adjustment for the play-by-play and power play data we only need to check the time, not the period\n",
    "        relevant_events = relevant_events.loc[(relevant_events['clock_seconds'] <= pp['start_game_clock_seconds']) &\n",
    "                                        (relevant_events['clock_seconds'] >= pp['end_game_clock_seconds']) ]\n",
    "                                        \n",
    "        # Placeholders for time to next shot and time to next goal\n",
    "        relevant_events.index = np.arange(0, len(relevant_events.index), dtype = 'int')\n",
    "        relevant_events['time_to_shot'] = np.ones(len(relevant_events)) * np.inf\n",
    "        relevant_events['time_to_goal'] = np.ones(len(relevant_events)) * np.inf\n",
    "        shots = relevant_events.loc[relevant_events.event == 'Shot']\n",
    "        goals = shots.loc[shots.event_successful == 't']\n",
    "        # Calculating the time to next shot, goal\n",
    "        while len(shots):\n",
    "            ix = (relevant_events['clock_seconds']>=shots.iloc[0].clock_seconds) & (relevant_events['time_to_shot'] > 100000)\n",
    "            relevant_events.loc[ix,'time_to_shot'] = relevant_events.loc[ix,'clock_seconds'] - shots.iloc[0].clock_seconds\n",
    "            shots = shots.iloc[1:,]\n",
    "        while len(goals):\n",
    "            ix = (relevant_events['clock_seconds']>=goals.iloc[0].clock_seconds) & (relevant_events['time_to_goal'] > 100000)\n",
    "            relevant_events.loc[ix,'time_to_goal'] = relevant_events.loc[ix,'clock_seconds'] - goals.iloc[0].clock_seconds\n",
    "            goals = shots.iloc[1:,]\n",
    "        \n",
    "        # Matching names and jersey numbers\n",
    "        pl1_jn = roster_info.loc[relevant_events['player_name'],'jn']\n",
    "        pl1_jn.index = relevant_events.index\n",
    "\n",
    "        pl2_exists = list(relevant_events['player_name_2'].notna())\n",
    "        pl2_jn = roster_info.loc[relevant_events['player_name_2'][pl2_exists],'jn']\n",
    "\n",
    "        pl2_jn.index = relevant_events.index[pl2_exists]\n",
    "        relevant_events['Player_1_num'] = pl1_jn\n",
    "        relevant_events['Player_2_num'] = pl2_jn\n",
    "        \n",
    "        tracks = []\n",
    "        \n",
    "        # Giving each event the tracks between the beginning and end of the event (if we have the end, otherwise just the frame of the event)\n",
    "        for rw, event in relevant_events.iterrows():\n",
    "            fr_id_1 = event['frame_id_1']\n",
    "            if pd.isna(fr_id_1):\n",
    "                tracks.append(None)\n",
    "                continue\n",
    "            fr_id_2 = event['frame_id_2']\n",
    "            if pd.notna(fr_id_2):\n",
    "                tracks.append(tracking_data.loc[(tracking_data['frame_id']>=fr_id_1) &\n",
    "                                                     (tracking_data['frame_id']<=fr_id_2)])\n",
    "            else:\n",
    "                tracks.append(tracking_data.loc[tracking_data['frame_id']==fr_id_1])\n",
    "\n",
    "        # Assign track to the relevant event            \n",
    "        relevant_events['tracks'] = tracks\n",
    "\n",
    "        # Add the current event to the final dataframe for this pp\n",
    "        relevant_eventses.append(relevant_events)\n",
    "    # Add a column of relevant events to the power play infor dataframe\n",
    "    power_play_info['relevant_events'] = relevant_eventses\n",
    "    return power_play_info\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "81f4132c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "data\\2022-02-08 Canada at USA\\2022-02-08 Canada at USA P1 PP1.csv\n",
      "data\\2022-02-08 Canada at USA\\2022-02-08 Canada at USA P1 PP2.csv\n",
      "data\\2022-02-08 Canada at USA\\2022-02-08 Canada at USA P2 PP3.csv\n",
      "Tracking file missing:\n",
      "data\\2022-02-08 Canada at USA\\2022-02-08 Canada at USA P2 PP4.csv\n",
      "data\\2022-02-08 Canada at USA\\2022-02-08 Canada at USA P2 PP5.csv\n",
      "data\\2022-02-08 Canada at USA\\2022-02-08 Canada at USA P3 PP6.csv\n",
      "data\\2022-02-08 Canada at USA\\2022-02-08 Canada at USA P3 PP7.csv\n",
      "data\\2022-02-08 ROC at Finland\\2022-02-08 ROC at Finland P1 PP1.csv\n",
      "data\\2022-02-08 ROC at Finland\\2022-02-08 ROC at Finland P2 PP2.csv\n",
      "Tracking file missing:\n",
      "data\\2022-02-08 ROC at Finland\\2022-02-08 ROC at Finland P2 PP3.csv\n",
      "Tracking file missing:\n",
      "data\\2022-02-08 ROC at Finland\\2022-02-08 ROC at Finland P2 PP4.csv\n",
      "data\\2022-02-08 ROC at Finland\\2022-02-08 ROC at Finland P2 PP5.csv\n",
      "data\\2022-02-08 ROC at Finland\\2022-02-08 ROC at Finland P3 PP6.csv\n",
      "data\\2022-02-12 Switzerland at ROC\\2022-02-12 Switzerland at ROC P1 PP1.csv\n",
      "data\\2022-02-12 Switzerland at ROC\\2022-02-12 Switzerland at ROC P1 PP2.csv\n",
      "data\\2022-02-12 Switzerland at ROC\\2022-02-12 Switzerland at ROC P3 PP3.csv\n",
      "Tracking file missing:\n",
      "data\\2022-02-12 Switzerland at ROC\\2022-02-12 Switzerland at ROC P3 PP4.csv\n",
      "data\\2022-02-12 Switzerland at ROC\\2022-02-12 Switzerland at ROC P3 PP5.csv\n",
      "data\\2022-02-14 Switzerland at Canada\\2022-02-14 Switzerland at Canada P1 PP1.csv\n",
      "data\\2022-02-14 Switzerland at Canada\\2022-02-14 Switzerland at Canada P1 PP2.csv\n",
      "Tracking file missing:\n",
      "data\\2022-02-14 Switzerland at Canada\\2022-02-14 Switzerland at Canada P2 PP3.csv\n",
      "data\\2022-02-14 Switzerland at Canada\\2022-02-14 Switzerland at Canada P2 PP4.csv\n",
      "data\\2022-02-14 Switzerland at Canada\\2022-02-14 Switzerland at Canada P3 PP5.csv\n",
      "data\\2022-02-14 USA at Finland\\2022-02-14 USA at Finland P2 PP1.csv\n",
      "Tracking file missing:\n",
      "data\\2022-02-14 USA at Finland\\2022-02-14 USA at Finland P2 PP2.csv\n",
      "data\\2022-02-14 USA at Finland\\2022-02-14 USA at Finland P2 PP3.csv\n",
      "data\\2022-02-14 USA at Finland\\2022-02-14 USA at Finland P3 PP4.csv\n",
      "data\\2022-02-14 USA at Finland\\2022-02-14 USA at Finland P3 PP5.csv\n",
      "Tracking file missing:\n",
      "data\\2022-02-14 USA at Finland\\2022-02-14 USA at Finland P3 PP6.csv\n",
      "data\\2022-02-16 Switzerland at Finland\\2022-02-16 Switzerland at Finland P1 PP1.csv\n",
      "data\\2022-02-16 Switzerland at Finland\\2022-02-16 Switzerland at Finland P1 PP2.csv\n",
      "data\\2022-02-16 Switzerland at Finland\\2022-02-16 Switzerland at Finland P2 PP3.csv\n",
      "data\\2022-02-16 Switzerland at Finland\\2022-02-16 Switzerland at Finland P2 PP4.csv\n",
      "data\\2022-02-16 Switzerland at Finland\\2022-02-16 Switzerland at Finland P2 PP5.csv\n",
      "data\\2022-02-16 Switzerland at Finland\\2022-02-16 Switzerland at Finland P3 PP6.csv\n",
      "data\\2022-02-16 Switzerland at Finland\\2022-02-16 Switzerland at Finland P3 PP7.csv\n",
      "data\\2022-02-16 Switzerland at Finland\\2022-02-16 Switzerland at Finland P3 PP8.csv\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>game_name</th>\n",
       "      <th>penalty_number</th>\n",
       "      <th>start_video_clock_seconds</th>\n",
       "      <th>end_video_clock_seconds</th>\n",
       "      <th>start_period</th>\n",
       "      <th>end_period</th>\n",
       "      <th>start_game_clock_seconds</th>\n",
       "      <th>end_game_clock_seconds</th>\n",
       "      <th>relevant_events</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-02-08 Canada at USA</td>\n",
       "      <td>1</td>\n",
       "      <td>1278</td>\n",
       "      <td>1314</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2786</td>\n",
       "      <td>2750</td>\n",
       "      <td>game_date  season_year                     ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022-02-08 Canada at USA</td>\n",
       "      <td>2</td>\n",
       "      <td>1624</td>\n",
       "      <td>1744</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2616</td>\n",
       "      <td>2496</td>\n",
       "      <td>game_date  season_year                     ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022-02-08 Canada at USA</td>\n",
       "      <td>3</td>\n",
       "      <td>409</td>\n",
       "      <td>562</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2191</td>\n",
       "      <td>2071</td>\n",
       "      <td>game_date  season_year                     ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022-02-08 Canada at USA</td>\n",
       "      <td>4</td>\n",
       "      <td>898</td>\n",
       "      <td>898</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1813</td>\n",
       "      <td>1813</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022-02-08 Canada at USA</td>\n",
       "      <td>5</td>\n",
       "      <td>1690</td>\n",
       "      <td>1927</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1472</td>\n",
       "      <td>1352</td>\n",
       "      <td>game_date  season_year                     ...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  game_name  penalty_number  start_video_clock_seconds  \\\n",
       "0  2022-02-08 Canada at USA               1                       1278   \n",
       "1  2022-02-08 Canada at USA               2                       1624   \n",
       "2  2022-02-08 Canada at USA               3                        409   \n",
       "3  2022-02-08 Canada at USA               4                        898   \n",
       "4  2022-02-08 Canada at USA               5                       1690   \n",
       "\n",
       "   end_video_clock_seconds  start_period  end_period  \\\n",
       "0                     1314             1           1   \n",
       "1                     1744             1           1   \n",
       "2                      562             2           2   \n",
       "3                      898             2           2   \n",
       "4                     1927             2           2   \n",
       "\n",
       "   start_game_clock_seconds  end_game_clock_seconds  \\\n",
       "0                      2786                    2750   \n",
       "1                      2616                    2496   \n",
       "2                      2191                    2071   \n",
       "3                      1813                    1813   \n",
       "4                      1472                    1352   \n",
       "\n",
       "                                     relevant_events  \n",
       "0     game_date  season_year                     ...  \n",
       "1     game_date  season_year                     ...  \n",
       "2     game_date  season_year                     ...  \n",
       "3                                               None  \n",
       "4     game_date  season_year                     ...  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Read tracking and event data\n",
    "relevant_events = get_pp_tracking()\n",
    "relevant_events.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "3ef3732a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>frame_id</th>\n",
       "      <th>period</th>\n",
       "      <th>track_id</th>\n",
       "      <th>team_id</th>\n",
       "      <th>team_name</th>\n",
       "      <th>jersey_number</th>\n",
       "      <th>x_ft</th>\n",
       "      <th>y_ft</th>\n",
       "      <th>video_shot</th>\n",
       "      <th>game_seconds</th>\n",
       "      <th>goalie</th>\n",
       "      <th>vel_x</th>\n",
       "      <th>vel_y</th>\n",
       "      <th>angle</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>301</th>\n",
       "      <td>344</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>Dark</td>\n",
       "      <td>USA</td>\n",
       "      <td>35</td>\n",
       "      <td>185.378708</td>\n",
       "      <td>44.201831</td>\n",
       "      <td>1</td>\n",
       "      <td>375</td>\n",
       "      <td>True</td>\n",
       "      <td>-3.084396</td>\n",
       "      <td>1.223622</td>\n",
       "      <td>2.763923</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>302</th>\n",
       "      <td>344</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>Dark</td>\n",
       "      <td>USA</td>\n",
       "      <td>2</td>\n",
       "      <td>178.061495</td>\n",
       "      <td>47.371741</td>\n",
       "      <td>1</td>\n",
       "      <td>375</td>\n",
       "      <td>False</td>\n",
       "      <td>-6.897018</td>\n",
       "      <td>7.126913</td>\n",
       "      <td>2.339803</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>303</th>\n",
       "      <td>344</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>Light</td>\n",
       "      <td>Canada</td>\n",
       "      <td>19</td>\n",
       "      <td>190.091633</td>\n",
       "      <td>69.393926</td>\n",
       "      <td>1</td>\n",
       "      <td>375</td>\n",
       "      <td>False</td>\n",
       "      <td>-9.628467</td>\n",
       "      <td>7.603144</td>\n",
       "      <td>2.473193</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>304</th>\n",
       "      <td>344</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Light</td>\n",
       "      <td>Canada</td>\n",
       "      <td>6</td>\n",
       "      <td>169.637361</td>\n",
       "      <td>54.290513</td>\n",
       "      <td>1</td>\n",
       "      <td>375</td>\n",
       "      <td>False</td>\n",
       "      <td>-0.641787</td>\n",
       "      <td>7.721428</td>\n",
       "      <td>1.653723</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>305</th>\n",
       "      <td>344</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>Dark</td>\n",
       "      <td>USA</td>\n",
       "      <td>5</td>\n",
       "      <td>185.094211</td>\n",
       "      <td>61.938154</td>\n",
       "      <td>1</td>\n",
       "      <td>375</td>\n",
       "      <td>False</td>\n",
       "      <td>-12.411864</td>\n",
       "      <td>3.411144</td>\n",
       "      <td>2.873385</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>509</th>\n",
       "      <td>370</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>Dark</td>\n",
       "      <td>USA</td>\n",
       "      <td>5</td>\n",
       "      <td>176.362805</td>\n",
       "      <td>61.602780</td>\n",
       "      <td>1</td>\n",
       "      <td>374</td>\n",
       "      <td>False</td>\n",
       "      <td>-0.583101</td>\n",
       "      <td>-7.261438</td>\n",
       "      <td>-1.650925</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>510</th>\n",
       "      <td>370</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>Light</td>\n",
       "      <td>Canada</td>\n",
       "      <td>23</td>\n",
       "      <td>131.460731</td>\n",
       "      <td>30.983528</td>\n",
       "      <td>1</td>\n",
       "      <td>374</td>\n",
       "      <td>False</td>\n",
       "      <td>-7.497990</td>\n",
       "      <td>0.437055</td>\n",
       "      <td>3.083369</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>511</th>\n",
       "      <td>370</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>Dark</td>\n",
       "      <td>USA</td>\n",
       "      <td>35</td>\n",
       "      <td>181.717333</td>\n",
       "      <td>45.429456</td>\n",
       "      <td>1</td>\n",
       "      <td>374</td>\n",
       "      <td>True</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>512</th>\n",
       "      <td>370</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>Light</td>\n",
       "      <td>Canada</td>\n",
       "      <td>29</td>\n",
       "      <td>136.055357</td>\n",
       "      <td>68.309436</td>\n",
       "      <td>1</td>\n",
       "      <td>374</td>\n",
       "      <td>False</td>\n",
       "      <td>22.730955</td>\n",
       "      <td>4.214396</td>\n",
       "      <td>0.183322</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>513</th>\n",
       "      <td>370</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>Dark</td>\n",
       "      <td>USA</td>\n",
       "      <td>25</td>\n",
       "      <td>145.369410</td>\n",
       "      <td>55.302033</td>\n",
       "      <td>1</td>\n",
       "      <td>374</td>\n",
       "      <td>False</td>\n",
       "      <td>-3.280671</td>\n",
       "      <td>9.047462</td>\n",
       "      <td>1.918658</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>213 rows × 14 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     frame_id  period  track_id team_id team_name  jersey_number        x_ft  \\\n",
       "301       344       1         3    Dark       USA             35  185.378708   \n",
       "302       344       1         2    Dark       USA              2  178.061495   \n",
       "303       344       1         4   Light    Canada             19  190.091633   \n",
       "304       344       1         1   Light    Canada              6  169.637361   \n",
       "305       344       1         7    Dark       USA              5  185.094211   \n",
       "..        ...     ...       ...     ...       ...            ...         ...   \n",
       "509       370       1         7    Dark       USA              5  176.362805   \n",
       "510       370       1         9   Light    Canada             23  131.460731   \n",
       "511       370       1         3    Dark       USA             35  181.717333   \n",
       "512       370       1        13   Light    Canada             29  136.055357   \n",
       "513       370       1         8    Dark       USA             25  145.369410   \n",
       "\n",
       "          y_ft  video_shot  game_seconds  goalie      vel_x     vel_y  \\\n",
       "301  44.201831           1           375    True  -3.084396  1.223622   \n",
       "302  47.371741           1           375   False  -6.897018  7.126913   \n",
       "303  69.393926           1           375   False  -9.628467  7.603144   \n",
       "304  54.290513           1           375   False  -0.641787  7.721428   \n",
       "305  61.938154           1           375   False -12.411864  3.411144   \n",
       "..         ...         ...           ...     ...        ...       ...   \n",
       "509  61.602780           1           374   False  -0.583101 -7.261438   \n",
       "510  30.983528           1           374   False  -7.497990  0.437055   \n",
       "511  45.429456           1           374    True   0.000000  0.000000   \n",
       "512  68.309436           1           374   False  22.730955  4.214396   \n",
       "513  55.302033           1           374   False  -3.280671  9.047462   \n",
       "\n",
       "        angle  \n",
       "301  2.763923  \n",
       "302  2.339803  \n",
       "303  2.473193  \n",
       "304  1.653723  \n",
       "305  2.873385  \n",
       "..        ...  \n",
       "509 -1.650925  \n",
       "510  3.083369  \n",
       "511  0.000000  \n",
       "512  0.183322  \n",
       "513  1.918658  \n",
       "\n",
       "[213 rows x 14 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Example of tracking data for one event\n",
    "relevant_events.loc[0,'relevant_events'].tracks.iloc[6]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "26b3d35c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save to JSON to read from R\n",
    "relevant_events.to_json(r'data/BDC_2022_all_data.json')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "9932b6f5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(425, 29)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Filter for direct passes only\n",
    "\n",
    "relevant_events2 = relevant_events.loc[~relevant_events['relevant_events'].isna()]\n",
    "the_truth = (relevant_events2.loc[0,'relevant_events']['event_type'] == 'Direct')&(~relevant_events2.loc[0,'relevant_events']['tracks'].isna())\n",
    "r0_direct = relevant_events2.loc[0,'relevant_events'].loc[the_truth]\n",
    "\n",
    "for r in relevant_events2.index[1:]:\n",
    "    r_the_truth = (relevant_events2.loc[r,'relevant_events']['event_type'] == 'Direct')&(~relevant_events2.loc[r,'relevant_events']['tracks'].isna())\n",
    "    r_new_direct = relevant_events2.loc[r,'relevant_events'].loc[r_the_truth]\n",
    "    r0_direct=pd.concat([r0_direct, r_new_direct])\n",
    "r0_direct = r0_direct.reset_index(drop=True)\n",
    "r0_direct.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "e522dd8c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save direct passes to R to read later\n",
    "r0_direct.to_json(r'data/BDC_2022_all_direct_wFrames.json')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "fd54bb7a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>game_date</th>\n",
       "      <th>season_year</th>\n",
       "      <th>team_name</th>\n",
       "      <th>opp_team_name</th>\n",
       "      <th>venue</th>\n",
       "      <th>period</th>\n",
       "      <th>clock_seconds</th>\n",
       "      <th>situation_type</th>\n",
       "      <th>goals_for</th>\n",
       "      <th>goals_against</th>\n",
       "      <th>...</th>\n",
       "      <th>event_detail_1</th>\n",
       "      <th>event_detail_2</th>\n",
       "      <th>event_detail_3</th>\n",
       "      <th>frame_id_1</th>\n",
       "      <th>frame_id_2</th>\n",
       "      <th>time_to_shot</th>\n",
       "      <th>time_to_goal</th>\n",
       "      <th>Player_1_num</th>\n",
       "      <th>Player_2_num</th>\n",
       "      <th>tracks</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>8/2/2022</td>\n",
       "      <td>2021</td>\n",
       "      <td>Olympic (Women) - Canada</td>\n",
       "      <td>Olympic (Women) - United States</td>\n",
       "      <td>away</td>\n",
       "      <td>1</td>\n",
       "      <td>2775</td>\n",
       "      <td>5 on 4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>344.0</td>\n",
       "      <td>370.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>19</td>\n",
       "      <td>29.0</td>\n",
       "      <td>frame_id  period  track_id team_id team_n...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>8/2/2022</td>\n",
       "      <td>2021</td>\n",
       "      <td>Olympic (Women) - Canada</td>\n",
       "      <td>Olympic (Women) - United States</td>\n",
       "      <td>away</td>\n",
       "      <td>1</td>\n",
       "      <td>2773</td>\n",
       "      <td>5 on 4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>420.0</td>\n",
       "      <td>443.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>29</td>\n",
       "      <td>6.0</td>\n",
       "      <td>frame_id  period  track_id team_id team_n...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8/2/2022</td>\n",
       "      <td>2021</td>\n",
       "      <td>Olympic (Women) - Canada</td>\n",
       "      <td>Olympic (Women) - United States</td>\n",
       "      <td>away</td>\n",
       "      <td>1</td>\n",
       "      <td>2766</td>\n",
       "      <td>5 on 4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>605.0</td>\n",
       "      <td>639.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>6</td>\n",
       "      <td>29.0</td>\n",
       "      <td>frame_id  period  track_id team_id team_...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8/2/2022</td>\n",
       "      <td>2021</td>\n",
       "      <td>Olympic (Women) - Canada</td>\n",
       "      <td>Olympic (Women) - United States</td>\n",
       "      <td>away</td>\n",
       "      <td>1</td>\n",
       "      <td>2765</td>\n",
       "      <td>5 on 4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>650.0</td>\n",
       "      <td>670.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>29</td>\n",
       "      <td>23.0</td>\n",
       "      <td>frame_id  period  track_id team_id team_...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8/2/2022</td>\n",
       "      <td>2021</td>\n",
       "      <td>Olympic (Women) - Canada</td>\n",
       "      <td>Olympic (Women) - United States</td>\n",
       "      <td>away</td>\n",
       "      <td>1</td>\n",
       "      <td>2763</td>\n",
       "      <td>5 on 4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>701.0</td>\n",
       "      <td>718.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>23</td>\n",
       "      <td>19.0</td>\n",
       "      <td>frame_id  period  track_id team_id team_...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 29 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  game_date  season_year                 team_name  \\\n",
       "0  8/2/2022         2021  Olympic (Women) - Canada   \n",
       "1  8/2/2022         2021  Olympic (Women) - Canada   \n",
       "2  8/2/2022         2021  Olympic (Women) - Canada   \n",
       "3  8/2/2022         2021  Olympic (Women) - Canada   \n",
       "4  8/2/2022         2021  Olympic (Women) - Canada   \n",
       "\n",
       "                     opp_team_name venue  period  clock_seconds  \\\n",
       "0  Olympic (Women) - United States  away       1           2775   \n",
       "1  Olympic (Women) - United States  away       1           2773   \n",
       "2  Olympic (Women) - United States  away       1           2766   \n",
       "3  Olympic (Women) - United States  away       1           2765   \n",
       "4  Olympic (Women) - United States  away       1           2763   \n",
       "\n",
       "  situation_type  goals_for  goals_against  ... event_detail_1 event_detail_2  \\\n",
       "0         5 on 4          0              0  ...            NaN            NaN   \n",
       "1         5 on 4          0              0  ...            NaN            NaN   \n",
       "2         5 on 4          0              0  ...            NaN            NaN   \n",
       "3         5 on 4          0              0  ...            NaN            NaN   \n",
       "4         5 on 4          0              0  ...            NaN            NaN   \n",
       "\n",
       "  event_detail_3  frame_id_1  frame_id_2 time_to_shot time_to_goal  \\\n",
       "0            NaN       344.0       370.0         23.0         23.0   \n",
       "1            NaN       420.0       443.0         21.0         21.0   \n",
       "2            NaN       605.0       639.0         14.0         14.0   \n",
       "3            NaN       650.0       670.0         13.0         13.0   \n",
       "4            NaN       701.0       718.0         11.0         11.0   \n",
       "\n",
       "   Player_1_num  Player_2_num  \\\n",
       "0            19          29.0   \n",
       "1            29           6.0   \n",
       "2             6          29.0   \n",
       "3            29          23.0   \n",
       "4            23          19.0   \n",
       "\n",
       "                                              tracks  \n",
       "0       frame_id  period  track_id team_id team_n...  \n",
       "1       frame_id  period  track_id team_id team_n...  \n",
       "2        frame_id  period  track_id team_id team_...  \n",
       "3        frame_id  period  track_id team_id team_...  \n",
       "4        frame_id  period  track_id team_id team_...  \n",
       "\n",
       "[5 rows x 29 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Filter to keep only one frame (makes JSON smaller for further analysis)\n",
    "truth2 = r0_direct.loc[0,'tracks']['frame_id']==r0_direct.frame_id_1[0]\n",
    "r_frame_direct = r0_direct.loc[0,'tracks'].loc[truth2]\n",
    "r_direct_oneFrame = []\n",
    "\n",
    "for r in r0_direct.index:\n",
    "    r_truth2 = r0_direct.loc[r,'tracks']['frame_id']==r0_direct.frame_id_1[r]\n",
    "    r2_frame_direct = r0_direct.loc[r,'tracks'].loc[r_truth2]\n",
    "    r_direct_oneFrame.append(r2_frame_direct)\n",
    "\n",
    "r0_direct['tracks']=r_direct_oneFrame\n",
    "r0_direct = r0_direct.reset_index(drop=True)\n",
    "\n",
    "# Save to JSON\n",
    "r0_direct.to_json(r'data/BDC_2022_passOnly.json')\n",
    "r0_direct.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "24f8c1ae",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(425, 29)"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "r0_direct.shape"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}