merge_tracking_data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "tracking = pd.read_csv(\"NFL_Turf/PlayerTrackData.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tracking.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "injuries = pd.read_csv(\"NFL_Turf/injuryrecord.csv\")\n",
    "playlist = pd.read_csv(\"NFL_Turf/playlist.csv\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "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>PlayerKey</th>\n",
       "      <th>GameID</th>\n",
       "      <th>PlayKey</th>\n",
       "      <th>RosterPosition</th>\n",
       "      <th>PlayerDay</th>\n",
       "      <th>PlayerGame</th>\n",
       "      <th>StadiumType</th>\n",
       "      <th>FieldType</th>\n",
       "      <th>Temperature</th>\n",
       "      <th>Weather</th>\n",
       "      <th>PlayType</th>\n",
       "      <th>PlayerGamePlay</th>\n",
       "      <th>Position</th>\n",
       "      <th>PositionGroup</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>26624</td>\n",
       "      <td>26624-1</td>\n",
       "      <td>26624-1-1</td>\n",
       "      <td>Quarterback</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Outdoor</td>\n",
       "      <td>Synthetic</td>\n",
       "      <td>63</td>\n",
       "      <td>Clear and warm</td>\n",
       "      <td>Pass</td>\n",
       "      <td>1</td>\n",
       "      <td>QB</td>\n",
       "      <td>QB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>26624</td>\n",
       "      <td>26624-1</td>\n",
       "      <td>26624-1-2</td>\n",
       "      <td>Quarterback</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Outdoor</td>\n",
       "      <td>Synthetic</td>\n",
       "      <td>63</td>\n",
       "      <td>Clear and warm</td>\n",
       "      <td>Pass</td>\n",
       "      <td>2</td>\n",
       "      <td>QB</td>\n",
       "      <td>QB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>26624</td>\n",
       "      <td>26624-1</td>\n",
       "      <td>26624-1-3</td>\n",
       "      <td>Quarterback</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Outdoor</td>\n",
       "      <td>Synthetic</td>\n",
       "      <td>63</td>\n",
       "      <td>Clear and warm</td>\n",
       "      <td>Rush</td>\n",
       "      <td>3</td>\n",
       "      <td>QB</td>\n",
       "      <td>QB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>26624</td>\n",
       "      <td>26624-1</td>\n",
       "      <td>26624-1-4</td>\n",
       "      <td>Quarterback</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Outdoor</td>\n",
       "      <td>Synthetic</td>\n",
       "      <td>63</td>\n",
       "      <td>Clear and warm</td>\n",
       "      <td>Rush</td>\n",
       "      <td>4</td>\n",
       "      <td>QB</td>\n",
       "      <td>QB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>26624</td>\n",
       "      <td>26624-1</td>\n",
       "      <td>26624-1-5</td>\n",
       "      <td>Quarterback</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Outdoor</td>\n",
       "      <td>Synthetic</td>\n",
       "      <td>63</td>\n",
       "      <td>Clear and warm</td>\n",
       "      <td>Pass</td>\n",
       "      <td>5</td>\n",
       "      <td>QB</td>\n",
       "      <td>QB</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   PlayerKey   GameID    PlayKey RosterPosition  PlayerDay  PlayerGame  \\\n",
       "0      26624  26624-1  26624-1-1    Quarterback          1           1   \n",
       "1      26624  26624-1  26624-1-2    Quarterback          1           1   \n",
       "2      26624  26624-1  26624-1-3    Quarterback          1           1   \n",
       "3      26624  26624-1  26624-1-4    Quarterback          1           1   \n",
       "4      26624  26624-1  26624-1-5    Quarterback          1           1   \n",
       "\n",
       "  StadiumType  FieldType  Temperature         Weather PlayType  \\\n",
       "0     Outdoor  Synthetic           63  Clear and warm     Pass   \n",
       "1     Outdoor  Synthetic           63  Clear and warm     Pass   \n",
       "2     Outdoor  Synthetic           63  Clear and warm     Rush   \n",
       "3     Outdoor  Synthetic           63  Clear and warm     Rush   \n",
       "4     Outdoor  Synthetic           63  Clear and warm     Pass   \n",
       "\n",
       "   PlayerGamePlay Position PositionGroup  \n",
       "0               1       QB            QB  \n",
       "1               2       QB            QB  \n",
       "2               3       QB            QB  \n",
       "3               4       QB            QB  \n",
       "4               5       QB            QB  "
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "playlist.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "injuries.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "inj = injuries.drop(columns=['PlayerKey', 'GameID', 'Surface', 'DM_M1', 'DM_M7', 'DM_M28', 'DM_M42'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "inj.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_inj = pd.merge(tracking, inj, how='inner', on = 'PlayKey')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "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>PlayKey</th>\n",
       "      <th>x</th>\n",
       "      <th>y</th>\n",
       "      <th>dir</th>\n",
       "      <th>o</th>\n",
       "      <th>BodyPart</th>\n",
       "      <th>Position</th>\n",
       "      <th>Twist</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>31070-3-7</td>\n",
       "      <td>44.07</td>\n",
       "      <td>32.14</td>\n",
       "      <td>23.31</td>\n",
       "      <td>174.83</td>\n",
       "      <td>Knee</td>\n",
       "      <td>RB</td>\n",
       "      <td>151.52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>31070-3-7</td>\n",
       "      <td>44.08</td>\n",
       "      <td>32.14</td>\n",
       "      <td>20.18</td>\n",
       "      <td>175.09</td>\n",
       "      <td>Knee</td>\n",
       "      <td>RB</td>\n",
       "      <td>154.91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>31070-3-7</td>\n",
       "      <td>44.08</td>\n",
       "      <td>32.14</td>\n",
       "      <td>16.53</td>\n",
       "      <td>175.35</td>\n",
       "      <td>Knee</td>\n",
       "      <td>RB</td>\n",
       "      <td>158.82</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>31070-3-7</td>\n",
       "      <td>44.08</td>\n",
       "      <td>32.14</td>\n",
       "      <td>13.23</td>\n",
       "      <td>175.60</td>\n",
       "      <td>Knee</td>\n",
       "      <td>RB</td>\n",
       "      <td>162.37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>31070-3-7</td>\n",
       "      <td>44.08</td>\n",
       "      <td>32.14</td>\n",
       "      <td>9.78</td>\n",
       "      <td>175.82</td>\n",
       "      <td>Knee</td>\n",
       "      <td>RB</td>\n",
       "      <td>166.04</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     PlayKey      x      y    dir       o BodyPart Position   Twist\n",
       "0  31070-3-7  44.07  32.14  23.31  174.83     Knee       RB  151.52\n",
       "1  31070-3-7  44.08  32.14  20.18  175.09     Knee       RB  154.91\n",
       "2  31070-3-7  44.08  32.14  16.53  175.35     Knee       RB  158.82\n",
       "3  31070-3-7  44.08  32.14  13.23  175.60     Knee       RB  162.37\n",
       "4  31070-3-7  44.08  32.14   9.78  175.82     Knee       RB  166.04"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_inj.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>PlayerKey</th>\n",
       "      <th>GameID</th>\n",
       "      <th>PlayKey</th>\n",
       "      <th>RosterPosition</th>\n",
       "      <th>PlayerDay</th>\n",
       "      <th>PlayerGame</th>\n",
       "      <th>StadiumType</th>\n",
       "      <th>FieldType</th>\n",
       "      <th>Temperature</th>\n",
       "      <th>Weather</th>\n",
       "      <th>PlayType</th>\n",
       "      <th>PlayerGamePlay</th>\n",
       "      <th>Position</th>\n",
       "      <th>PositionGroup</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>26624</td>\n",
       "      <td>26624-1</td>\n",
       "      <td>26624-1-1</td>\n",
       "      <td>Quarterback</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Outdoor</td>\n",
       "      <td>Synthetic</td>\n",
       "      <td>63</td>\n",
       "      <td>Clear and warm</td>\n",
       "      <td>Pass</td>\n",
       "      <td>1</td>\n",
       "      <td>QB</td>\n",
       "      <td>QB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>26624</td>\n",
       "      <td>26624-1</td>\n",
       "      <td>26624-1-2</td>\n",
       "      <td>Quarterback</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Outdoor</td>\n",
       "      <td>Synthetic</td>\n",
       "      <td>63</td>\n",
       "      <td>Clear and warm</td>\n",
       "      <td>Pass</td>\n",
       "      <td>2</td>\n",
       "      <td>QB</td>\n",
       "      <td>QB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>26624</td>\n",
       "      <td>26624-1</td>\n",
       "      <td>26624-1-3</td>\n",
       "      <td>Quarterback</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Outdoor</td>\n",
       "      <td>Synthetic</td>\n",
       "      <td>63</td>\n",
       "      <td>Clear and warm</td>\n",
       "      <td>Rush</td>\n",
       "      <td>3</td>\n",
       "      <td>QB</td>\n",
       "      <td>QB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>26624</td>\n",
       "      <td>26624-1</td>\n",
       "      <td>26624-1-4</td>\n",
       "      <td>Quarterback</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Outdoor</td>\n",
       "      <td>Synthetic</td>\n",
       "      <td>63</td>\n",
       "      <td>Clear and warm</td>\n",
       "      <td>Rush</td>\n",
       "      <td>4</td>\n",
       "      <td>QB</td>\n",
       "      <td>QB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>26624</td>\n",
       "      <td>26624-1</td>\n",
       "      <td>26624-1-5</td>\n",
       "      <td>Quarterback</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Outdoor</td>\n",
       "      <td>Synthetic</td>\n",
       "      <td>63</td>\n",
       "      <td>Clear and warm</td>\n",
       "      <td>Pass</td>\n",
       "      <td>5</td>\n",
       "      <td>QB</td>\n",
       "      <td>QB</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   PlayerKey   GameID    PlayKey RosterPosition  PlayerDay  PlayerGame  \\\n",
       "0      26624  26624-1  26624-1-1    Quarterback          1           1   \n",
       "1      26624  26624-1  26624-1-2    Quarterback          1           1   \n",
       "2      26624  26624-1  26624-1-3    Quarterback          1           1   \n",
       "3      26624  26624-1  26624-1-4    Quarterback          1           1   \n",
       "4      26624  26624-1  26624-1-5    Quarterback          1           1   \n",
       "\n",
       "  StadiumType  FieldType  Temperature         Weather PlayType  \\\n",
       "0     Outdoor  Synthetic           63  Clear and warm     Pass   \n",
       "1     Outdoor  Synthetic           63  Clear and warm     Pass   \n",
       "2     Outdoor  Synthetic           63  Clear and warm     Rush   \n",
       "3     Outdoor  Synthetic           63  Clear and warm     Rush   \n",
       "4     Outdoor  Synthetic           63  Clear and warm     Pass   \n",
       "\n",
       "   PlayerGamePlay Position PositionGroup  \n",
       "0               1       QB            QB  \n",
       "1               2       QB            QB  \n",
       "2               3       QB            QB  \n",
       "3               4       QB            QB  \n",
       "4               5       QB            QB  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "playlist.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_inj = pd.merge(merged_inj, playlist, how='left', on = 'PlayKey')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "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>PlayKey</th>\n",
       "      <th>x</th>\n",
       "      <th>y</th>\n",
       "      <th>dir</th>\n",
       "      <th>o</th>\n",
       "      <th>BodyPart</th>\n",
       "      <th>Position_x</th>\n",
       "      <th>Twist</th>\n",
       "      <th>PlayerKey</th>\n",
       "      <th>GameID</th>\n",
       "      <th>...</th>\n",
       "      <th>PlayerDay</th>\n",
       "      <th>PlayerGame</th>\n",
       "      <th>StadiumType</th>\n",
       "      <th>FieldType</th>\n",
       "      <th>Temperature</th>\n",
       "      <th>Weather</th>\n",
       "      <th>PlayType</th>\n",
       "      <th>PlayerGamePlay</th>\n",
       "      <th>Position_y</th>\n",
       "      <th>PositionGroup</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>31070-3-7</td>\n",
       "      <td>44.07</td>\n",
       "      <td>32.14</td>\n",
       "      <td>23.31</td>\n",
       "      <td>174.83</td>\n",
       "      <td>Knee</td>\n",
       "      <td>RB</td>\n",
       "      <td>151.52</td>\n",
       "      <td>31070</td>\n",
       "      <td>31070-3</td>\n",
       "      <td>...</td>\n",
       "      <td>15</td>\n",
       "      <td>3</td>\n",
       "      <td>Outdoor</td>\n",
       "      <td>Natural</td>\n",
       "      <td>89</td>\n",
       "      <td>Sunny</td>\n",
       "      <td>Rush</td>\n",
       "      <td>7</td>\n",
       "      <td>RB</td>\n",
       "      <td>RB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>31070-3-7</td>\n",
       "      <td>44.08</td>\n",
       "      <td>32.14</td>\n",
       "      <td>20.18</td>\n",
       "      <td>175.09</td>\n",
       "      <td>Knee</td>\n",
       "      <td>RB</td>\n",
       "      <td>154.91</td>\n",
       "      <td>31070</td>\n",
       "      <td>31070-3</td>\n",
       "      <td>...</td>\n",
       "      <td>15</td>\n",
       "      <td>3</td>\n",
       "      <td>Outdoor</td>\n",
       "      <td>Natural</td>\n",
       "      <td>89</td>\n",
       "      <td>Sunny</td>\n",
       "      <td>Rush</td>\n",
       "      <td>7</td>\n",
       "      <td>RB</td>\n",
       "      <td>RB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>31070-3-7</td>\n",
       "      <td>44.08</td>\n",
       "      <td>32.14</td>\n",
       "      <td>16.53</td>\n",
       "      <td>175.35</td>\n",
       "      <td>Knee</td>\n",
       "      <td>RB</td>\n",
       "      <td>158.82</td>\n",
       "      <td>31070</td>\n",
       "      <td>31070-3</td>\n",
       "      <td>...</td>\n",
       "      <td>15</td>\n",
       "      <td>3</td>\n",
       "      <td>Outdoor</td>\n",
       "      <td>Natural</td>\n",
       "      <td>89</td>\n",
       "      <td>Sunny</td>\n",
       "      <td>Rush</td>\n",
       "      <td>7</td>\n",
       "      <td>RB</td>\n",
       "      <td>RB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>31070-3-7</td>\n",
       "      <td>44.08</td>\n",
       "      <td>32.14</td>\n",
       "      <td>13.23</td>\n",
       "      <td>175.60</td>\n",
       "      <td>Knee</td>\n",
       "      <td>RB</td>\n",
       "      <td>162.37</td>\n",
       "      <td>31070</td>\n",
       "      <td>31070-3</td>\n",
       "      <td>...</td>\n",
       "      <td>15</td>\n",
       "      <td>3</td>\n",
       "      <td>Outdoor</td>\n",
       "      <td>Natural</td>\n",
       "      <td>89</td>\n",
       "      <td>Sunny</td>\n",
       "      <td>Rush</td>\n",
       "      <td>7</td>\n",
       "      <td>RB</td>\n",
       "      <td>RB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>31070-3-7</td>\n",
       "      <td>44.08</td>\n",
       "      <td>32.14</td>\n",
       "      <td>9.78</td>\n",
       "      <td>175.82</td>\n",
       "      <td>Knee</td>\n",
       "      <td>RB</td>\n",
       "      <td>166.04</td>\n",
       "      <td>31070</td>\n",
       "      <td>31070-3</td>\n",
       "      <td>...</td>\n",
       "      <td>15</td>\n",
       "      <td>3</td>\n",
       "      <td>Outdoor</td>\n",
       "      <td>Natural</td>\n",
       "      <td>89</td>\n",
       "      <td>Sunny</td>\n",
       "      <td>Rush</td>\n",
       "      <td>7</td>\n",
       "      <td>RB</td>\n",
       "      <td>RB</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     PlayKey      x      y    dir       o BodyPart Position_x   Twist  \\\n",
       "0  31070-3-7  44.07  32.14  23.31  174.83     Knee         RB  151.52   \n",
       "1  31070-3-7  44.08  32.14  20.18  175.09     Knee         RB  154.91   \n",
       "2  31070-3-7  44.08  32.14  16.53  175.35     Knee         RB  158.82   \n",
       "3  31070-3-7  44.08  32.14  13.23  175.60     Knee         RB  162.37   \n",
       "4  31070-3-7  44.08  32.14   9.78  175.82     Knee         RB  166.04   \n",
       "\n",
       "   PlayerKey   GameID  ... PlayerDay  PlayerGame  StadiumType FieldType  \\\n",
       "0      31070  31070-3  ...        15           3      Outdoor   Natural   \n",
       "1      31070  31070-3  ...        15           3      Outdoor   Natural   \n",
       "2      31070  31070-3  ...        15           3      Outdoor   Natural   \n",
       "3      31070  31070-3  ...        15           3      Outdoor   Natural   \n",
       "4      31070  31070-3  ...        15           3      Outdoor   Natural   \n",
       "\n",
       "  Temperature  Weather PlayType PlayerGamePlay  Position_y PositionGroup  \n",
       "0          89    Sunny     Rush              7          RB            RB  \n",
       "1          89    Sunny     Rush              7          RB            RB  \n",
       "2          89    Sunny     Rush              7          RB            RB  \n",
       "3          89    Sunny     Rush              7          RB            RB  \n",
       "4          89    Sunny     Rush              7          RB            RB  \n",
       "\n",
       "[5 rows x 21 columns]"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_inj.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "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>PlayKey</th>\n",
       "      <th>x</th>\n",
       "      <th>y</th>\n",
       "      <th>dir</th>\n",
       "      <th>o</th>\n",
       "      <th>BodyPart</th>\n",
       "      <th>Position_x</th>\n",
       "      <th>Twist</th>\n",
       "      <th>Position_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>31070-3-7</td>\n",
       "      <td>44.07</td>\n",
       "      <td>32.14</td>\n",
       "      <td>23.31</td>\n",
       "      <td>174.83</td>\n",
       "      <td>Knee</td>\n",
       "      <td>RB</td>\n",
       "      <td>151.52</td>\n",
       "      <td>RB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>31070-3-7</td>\n",
       "      <td>44.08</td>\n",
       "      <td>32.14</td>\n",
       "      <td>20.18</td>\n",
       "      <td>175.09</td>\n",
       "      <td>Knee</td>\n",
       "      <td>RB</td>\n",
       "      <td>154.91</td>\n",
       "      <td>RB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>31070-3-7</td>\n",
       "      <td>44.08</td>\n",
       "      <td>32.14</td>\n",
       "      <td>16.53</td>\n",
       "      <td>175.35</td>\n",
       "      <td>Knee</td>\n",
       "      <td>RB</td>\n",
       "      <td>158.82</td>\n",
       "      <td>RB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>31070-3-7</td>\n",
       "      <td>44.08</td>\n",
       "      <td>32.14</td>\n",
       "      <td>13.23</td>\n",
       "      <td>175.60</td>\n",
       "      <td>Knee</td>\n",
       "      <td>RB</td>\n",
       "      <td>162.37</td>\n",
       "      <td>RB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>31070-3-7</td>\n",
       "      <td>44.08</td>\n",
       "      <td>32.14</td>\n",
       "      <td>9.78</td>\n",
       "      <td>175.82</td>\n",
       "      <td>Knee</td>\n",
       "      <td>RB</td>\n",
       "      <td>166.04</td>\n",
       "      <td>RB</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     PlayKey      x      y    dir       o BodyPart Position_x   Twist  \\\n",
       "0  31070-3-7  44.07  32.14  23.31  174.83     Knee         RB  151.52   \n",
       "1  31070-3-7  44.08  32.14  20.18  175.09     Knee         RB  154.91   \n",
       "2  31070-3-7  44.08  32.14  16.53  175.35     Knee         RB  158.82   \n",
       "3  31070-3-7  44.08  32.14  13.23  175.60     Knee         RB  162.37   \n",
       "4  31070-3-7  44.08  32.14   9.78  175.82     Knee         RB  166.04   \n",
       "\n",
       "  Position_y  \n",
       "0         RB  \n",
       "1         RB  \n",
       "2         RB  \n",
       "3         RB  \n",
       "4         RB  "
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_inj.drop(columns=['event', 'time', 'dis', 's', 'PlayerKey', 'GameID', 'RosterPosition', 'PlayerGame', 'StadiumType',\n",
    "                'FieldType', 'Temperature', 'Weather', 'PlayType', 'PlayerGamePlay', 'PositionGroup', 'PlayerDay'], inplace=True)\n",
    "merged_inj['Twist'] = abs(merged_inj.dir - merged_inj.o)\n",
    "merged_inj.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_inj = merged_inj.rename(columns={'Position_y': 'Position', 'BodyPart': 'Injury'})[something]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "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>PlayKey</th>\n",
       "      <th>Position</th>\n",
       "      <th>Injury</th>\n",
       "      <th>x</th>\n",
       "      <th>y</th>\n",
       "      <th>o</th>\n",
       "      <th>dir</th>\n",
       "      <th>Twist</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>31070-3-7</td>\n",
       "      <td>RB</td>\n",
       "      <td>Knee</td>\n",
       "      <td>44.07</td>\n",
       "      <td>32.14</td>\n",
       "      <td>174.83</td>\n",
       "      <td>23.31</td>\n",
       "      <td>151.52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>31070-3-7</td>\n",
       "      <td>RB</td>\n",
       "      <td>Knee</td>\n",
       "      <td>44.08</td>\n",
       "      <td>32.14</td>\n",
       "      <td>175.09</td>\n",
       "      <td>20.18</td>\n",
       "      <td>154.91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>31070-3-7</td>\n",
       "      <td>RB</td>\n",
       "      <td>Knee</td>\n",
       "      <td>44.08</td>\n",
       "      <td>32.14</td>\n",
       "      <td>175.35</td>\n",
       "      <td>16.53</td>\n",
       "      <td>158.82</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>31070-3-7</td>\n",
       "      <td>RB</td>\n",
       "      <td>Knee</td>\n",
       "      <td>44.08</td>\n",
       "      <td>32.14</td>\n",
       "      <td>175.60</td>\n",
       "      <td>13.23</td>\n",
       "      <td>162.37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>31070-3-7</td>\n",
       "      <td>RB</td>\n",
       "      <td>Knee</td>\n",
       "      <td>44.08</td>\n",
       "      <td>32.14</td>\n",
       "      <td>175.82</td>\n",
       "      <td>9.78</td>\n",
       "      <td>166.04</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     PlayKey Position Injury      x      y       o    dir   Twist\n",
       "0  31070-3-7       RB   Knee  44.07  32.14  174.83  23.31  151.52\n",
       "1  31070-3-7       RB   Knee  44.08  32.14  175.09  20.18  154.91\n",
       "2  31070-3-7       RB   Knee  44.08  32.14  175.35  16.53  158.82\n",
       "3  31070-3-7       RB   Knee  44.08  32.14  175.60  13.23  162.37\n",
       "4  31070-3-7       RB   Knee  44.08  32.14  175.82   9.78  166.04"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_inj.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "c:\\Users\\justi\\anaconda3\\envs\\mlenv\\lib\\site-packages\\IPython\\core\\interactiveshell.py:3457: DtypeWarning: Columns (10) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  exec(code_obj, self.user_global_ns, self.user_ns)\n"
     ]
    }
   ],
   "source": [
    "ngs = pd.read_csv(\"NFL_Punt/NGS-2016-reg-wk13-17.csv\")\n",
    "\n",
    "# Remove NaN in necessary rows\n",
    "ngs = ngs.loc[ngs.GameKey.isna() == False]\n",
    "ngs = ngs.loc[ngs.PlayID.isna() == False]\n",
    "ngs = ngs.loc[ngs.GSISID.isna() == False]\n",
    "\n",
    "# Change the GSISID to Int type - it is currently float\n",
    "ngs.GSISID = ngs.GSISID.astype('int')\n",
    "\n",
    "# Add the Game_Play and GamePlay with GSISID - this will create a unique identifier for the play by each player\n",
    "ngs['Game_Play_ID'] = ngs[['GameKey', 'PlayID', 'GSISID']].apply(\n",
    "    lambda row: '-'.join(row.values.astype(str)), axis=1)\n",
    "\n",
    "# Create a new column that shows the player's Twist - the difference\n",
    "# between the angles that the player is running (dir) and face orientation while running (o)\n",
    "ngs['Twist'] = abs(ngs.dir - ngs.o)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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>Season_Year</th>\n",
       "      <th>GameKey</th>\n",
       "      <th>PlayID</th>\n",
       "      <th>GSISID</th>\n",
       "      <th>Time</th>\n",
       "      <th>x</th>\n",
       "      <th>y</th>\n",
       "      <th>dis</th>\n",
       "      <th>o</th>\n",
       "      <th>dir</th>\n",
       "      <th>Event</th>\n",
       "      <th>Game_Play_ID</th>\n",
       "      <th>Twist</th>\n",
       "      <th>Number</th>\n",
       "      <th>Position</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016</td>\n",
       "      <td>252</td>\n",
       "      <td>231</td>\n",
       "      <td>30982</td>\n",
       "      <td>2016-12-04 21:11:57.500</td>\n",
       "      <td>46.209999</td>\n",
       "      <td>45.230000</td>\n",
       "      <td>0.03</td>\n",
       "      <td>147.750000</td>\n",
       "      <td>315.339996</td>\n",
       "      <td>NaN</td>\n",
       "      <td>252-231-30982</td>\n",
       "      <td>167.589996</td>\n",
       "      <td>39</td>\n",
       "      <td>CB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016</td>\n",
       "      <td>252</td>\n",
       "      <td>231</td>\n",
       "      <td>30982</td>\n",
       "      <td>2016-12-04 21:11:57.500</td>\n",
       "      <td>46.209999</td>\n",
       "      <td>45.230000</td>\n",
       "      <td>0.03</td>\n",
       "      <td>147.750000</td>\n",
       "      <td>315.339996</td>\n",
       "      <td>NaN</td>\n",
       "      <td>252-231-30982</td>\n",
       "      <td>167.589996</td>\n",
       "      <td>47</td>\n",
       "      <td>CB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2016</td>\n",
       "      <td>252</td>\n",
       "      <td>231</td>\n",
       "      <td>30982</td>\n",
       "      <td>2016-12-04 21:11:57.500</td>\n",
       "      <td>46.209999</td>\n",
       "      <td>45.230000</td>\n",
       "      <td>0.03</td>\n",
       "      <td>147.750000</td>\n",
       "      <td>315.339996</td>\n",
       "      <td>NaN</td>\n",
       "      <td>252-231-30982</td>\n",
       "      <td>167.589996</td>\n",
       "      <td>38</td>\n",
       "      <td>CB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2016</td>\n",
       "      <td>252</td>\n",
       "      <td>231</td>\n",
       "      <td>32070</td>\n",
       "      <td>2016-12-04 21:11:57.800</td>\n",
       "      <td>46.259998</td>\n",
       "      <td>24.850000</td>\n",
       "      <td>0.08</td>\n",
       "      <td>206.080002</td>\n",
       "      <td>264.890015</td>\n",
       "      <td>NaN</td>\n",
       "      <td>252-231-32070</td>\n",
       "      <td>58.810013</td>\n",
       "      <td>13</td>\n",
       "      <td>WR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2016</td>\n",
       "      <td>252</td>\n",
       "      <td>231</td>\n",
       "      <td>30084</td>\n",
       "      <td>2016-12-04 21:11:57.800</td>\n",
       "      <td>45.740002</td>\n",
       "      <td>7.090000</td>\n",
       "      <td>0.05</td>\n",
       "      <td>153.160004</td>\n",
       "      <td>69.709999</td>\n",
       "      <td>NaN</td>\n",
       "      <td>252-231-30084</td>\n",
       "      <td>83.450005</td>\n",
       "      <td>36</td>\n",
       "      <td>SS</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",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10660176</th>\n",
       "      <td>2016</td>\n",
       "      <td>244</td>\n",
       "      <td>2263</td>\n",
       "      <td>31570</td>\n",
       "      <td>2016-12-04 19:51:16.200</td>\n",
       "      <td>63.680000</td>\n",
       "      <td>34.520000</td>\n",
       "      <td>0.03</td>\n",
       "      <td>96.599998</td>\n",
       "      <td>162.630005</td>\n",
       "      <td>NaN</td>\n",
       "      <td>244-2263-31570</td>\n",
       "      <td>66.030006</td>\n",
       "      <td>14</td>\n",
       "      <td>WR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10660177</th>\n",
       "      <td>2016</td>\n",
       "      <td>244</td>\n",
       "      <td>2263</td>\n",
       "      <td>31570</td>\n",
       "      <td>2016-12-04 19:51:16.200</td>\n",
       "      <td>63.680000</td>\n",
       "      <td>34.520000</td>\n",
       "      <td>0.03</td>\n",
       "      <td>96.599998</td>\n",
       "      <td>162.630005</td>\n",
       "      <td>NaN</td>\n",
       "      <td>244-2263-31570</td>\n",
       "      <td>66.030006</td>\n",
       "      <td>16</td>\n",
       "      <td>WR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10660178</th>\n",
       "      <td>2016</td>\n",
       "      <td>244</td>\n",
       "      <td>2263</td>\n",
       "      <td>32599</td>\n",
       "      <td>2016-12-04 19:51:16.200</td>\n",
       "      <td>62.270000</td>\n",
       "      <td>28.709999</td>\n",
       "      <td>0.00</td>\n",
       "      <td>288.359985</td>\n",
       "      <td>280.600006</td>\n",
       "      <td>NaN</td>\n",
       "      <td>244-2263-32599</td>\n",
       "      <td>7.759979</td>\n",
       "      <td>82</td>\n",
       "      <td>TE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10660179</th>\n",
       "      <td>2016</td>\n",
       "      <td>244</td>\n",
       "      <td>2263</td>\n",
       "      <td>32596</td>\n",
       "      <td>2016-12-04 19:51:16.200</td>\n",
       "      <td>60.930000</td>\n",
       "      <td>28.980000</td>\n",
       "      <td>0.04</td>\n",
       "      <td>358.690002</td>\n",
       "      <td>36.840000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>244-2263-32596</td>\n",
       "      <td>321.850002</td>\n",
       "      <td>34</td>\n",
       "      <td>CB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10660180</th>\n",
       "      <td>2016</td>\n",
       "      <td>244</td>\n",
       "      <td>2263</td>\n",
       "      <td>24535</td>\n",
       "      <td>2016-12-04 19:51:16.200</td>\n",
       "      <td>23.020000</td>\n",
       "      <td>29.530001</td>\n",
       "      <td>0.06</td>\n",
       "      <td>3.930000</td>\n",
       "      <td>96.279999</td>\n",
       "      <td>NaN</td>\n",
       "      <td>244-2263-24535</td>\n",
       "      <td>92.349999</td>\n",
       "      <td>14</td>\n",
       "      <td>WR</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10660181 rows × 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          Season_Year  GameKey  PlayID  GSISID                     Time  \\\n",
       "0                2016      252     231   30982  2016-12-04 21:11:57.500   \n",
       "1                2016      252     231   30982  2016-12-04 21:11:57.500   \n",
       "2                2016      252     231   30982  2016-12-04 21:11:57.500   \n",
       "3                2016      252     231   32070  2016-12-04 21:11:57.800   \n",
       "4                2016      252     231   30084  2016-12-04 21:11:57.800   \n",
       "...               ...      ...     ...     ...                      ...   \n",
       "10660176         2016      244    2263   31570  2016-12-04 19:51:16.200   \n",
       "10660177         2016      244    2263   31570  2016-12-04 19:51:16.200   \n",
       "10660178         2016      244    2263   32599  2016-12-04 19:51:16.200   \n",
       "10660179         2016      244    2263   32596  2016-12-04 19:51:16.200   \n",
       "10660180         2016      244    2263   24535  2016-12-04 19:51:16.200   \n",
       "\n",
       "                  x          y   dis           o         dir Event  \\\n",
       "0         46.209999  45.230000  0.03  147.750000  315.339996   NaN   \n",
       "1         46.209999  45.230000  0.03  147.750000  315.339996   NaN   \n",
       "2         46.209999  45.230000  0.03  147.750000  315.339996   NaN   \n",
       "3         46.259998  24.850000  0.08  206.080002  264.890015   NaN   \n",
       "4         45.740002   7.090000  0.05  153.160004   69.709999   NaN   \n",
       "...             ...        ...   ...         ...         ...   ...   \n",
       "10660176  63.680000  34.520000  0.03   96.599998  162.630005   NaN   \n",
       "10660177  63.680000  34.520000  0.03   96.599998  162.630005   NaN   \n",
       "10660178  62.270000  28.709999  0.00  288.359985  280.600006   NaN   \n",
       "10660179  60.930000  28.980000  0.04  358.690002   36.840000   NaN   \n",
       "10660180  23.020000  29.530001  0.06    3.930000   96.279999   NaN   \n",
       "\n",
       "            Game_Play_ID       Twist Number Position  \n",
       "0          252-231-30982  167.589996     39       CB  \n",
       "1          252-231-30982  167.589996     47       CB  \n",
       "2          252-231-30982  167.589996     38       CB  \n",
       "3          252-231-32070   58.810013     13       WR  \n",
       "4          252-231-30084   83.450005     36       SS  \n",
       "...                  ...         ...    ...      ...  \n",
       "10660176  244-2263-31570   66.030006     14       WR  \n",
       "10660177  244-2263-31570   66.030006     16       WR  \n",
       "10660178  244-2263-32599    7.759979     82       TE  \n",
       "10660179  244-2263-32596  321.850002     34       CB  \n",
       "10660180  244-2263-24535   92.349999     14       WR  \n",
       "\n",
       "[10660181 rows x 15 columns]"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "punt = pd.read_csv('NFL_Punt/player_punt_data.csv')\n",
    "\n",
    "ngs_merge = pd.merge(ngs, punt, how='left', on='GSISID')\n",
    "ngs_merge"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "ngs_merge.drop(columns=['Season_Year', 'GameKey', 'PlayID', 'GSISID', 'Time', 'dis', 'Event', 'Number'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "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>x</th>\n",
       "      <th>y</th>\n",
       "      <th>o</th>\n",
       "      <th>dir</th>\n",
       "      <th>Game_Play_ID</th>\n",
       "      <th>Twist</th>\n",
       "      <th>Position</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>46.209999</td>\n",
       "      <td>45.23</td>\n",
       "      <td>147.750000</td>\n",
       "      <td>315.339996</td>\n",
       "      <td>252-231-30982</td>\n",
       "      <td>167.589996</td>\n",
       "      <td>CB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>46.209999</td>\n",
       "      <td>45.23</td>\n",
       "      <td>147.750000</td>\n",
       "      <td>315.339996</td>\n",
       "      <td>252-231-30982</td>\n",
       "      <td>167.589996</td>\n",
       "      <td>CB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>46.209999</td>\n",
       "      <td>45.23</td>\n",
       "      <td>147.750000</td>\n",
       "      <td>315.339996</td>\n",
       "      <td>252-231-30982</td>\n",
       "      <td>167.589996</td>\n",
       "      <td>CB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>46.259998</td>\n",
       "      <td>24.85</td>\n",
       "      <td>206.080002</td>\n",
       "      <td>264.890015</td>\n",
       "      <td>252-231-32070</td>\n",
       "      <td>58.810013</td>\n",
       "      <td>WR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>45.740002</td>\n",
       "      <td>7.09</td>\n",
       "      <td>153.160004</td>\n",
       "      <td>69.709999</td>\n",
       "      <td>252-231-30084</td>\n",
       "      <td>83.450005</td>\n",
       "      <td>SS</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           x      y           o         dir   Game_Play_ID       Twist  \\\n",
       "0  46.209999  45.23  147.750000  315.339996  252-231-30982  167.589996   \n",
       "1  46.209999  45.23  147.750000  315.339996  252-231-30982  167.589996   \n",
       "2  46.209999  45.23  147.750000  315.339996  252-231-30982  167.589996   \n",
       "3  46.259998  24.85  206.080002  264.890015  252-231-32070   58.810013   \n",
       "4  45.740002   7.09  153.160004   69.709999  252-231-30084   83.450005   \n",
       "\n",
       "  Position  \n",
       "0       CB  \n",
       "1       CB  \n",
       "2       CB  \n",
       "3       WR  \n",
       "4       SS  "
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ngs_merge.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "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>x</th>\n",
       "      <th>y</th>\n",
       "      <th>o</th>\n",
       "      <th>dir</th>\n",
       "      <th>Game_Play_ID</th>\n",
       "      <th>Twist</th>\n",
       "      <th>Position</th>\n",
       "      <th>Injury</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>46.209999</td>\n",
       "      <td>45.23</td>\n",
       "      <td>147.750000</td>\n",
       "      <td>315.339996</td>\n",
       "      <td>252-231-30982</td>\n",
       "      <td>167.589996</td>\n",
       "      <td>CB</td>\n",
       "      <td>Concussion</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>46.209999</td>\n",
       "      <td>45.23</td>\n",
       "      <td>147.750000</td>\n",
       "      <td>315.339996</td>\n",
       "      <td>252-231-30982</td>\n",
       "      <td>167.589996</td>\n",
       "      <td>CB</td>\n",
       "      <td>Concussion</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>46.209999</td>\n",
       "      <td>45.23</td>\n",
       "      <td>147.750000</td>\n",
       "      <td>315.339996</td>\n",
       "      <td>252-231-30982</td>\n",
       "      <td>167.589996</td>\n",
       "      <td>CB</td>\n",
       "      <td>Concussion</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>46.259998</td>\n",
       "      <td>24.85</td>\n",
       "      <td>206.080002</td>\n",
       "      <td>264.890015</td>\n",
       "      <td>252-231-32070</td>\n",
       "      <td>58.810013</td>\n",
       "      <td>WR</td>\n",
       "      <td>Concussion</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>45.740002</td>\n",
       "      <td>7.09</td>\n",
       "      <td>153.160004</td>\n",
       "      <td>69.709999</td>\n",
       "      <td>252-231-30084</td>\n",
       "      <td>83.450005</td>\n",
       "      <td>SS</td>\n",
       "      <td>Concussion</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           x      y           o         dir   Game_Play_ID       Twist  \\\n",
       "0  46.209999  45.23  147.750000  315.339996  252-231-30982  167.589996   \n",
       "1  46.209999  45.23  147.750000  315.339996  252-231-30982  167.589996   \n",
       "2  46.209999  45.23  147.750000  315.339996  252-231-30982  167.589996   \n",
       "3  46.259998  24.85  206.080002  264.890015  252-231-32070   58.810013   \n",
       "4  45.740002   7.09  153.160004   69.709999  252-231-30084   83.450005   \n",
       "\n",
       "  Position      Injury  \n",
       "0       CB  Concussion  \n",
       "1       CB  Concussion  \n",
       "2       CB  Concussion  \n",
       "3       WR  Concussion  \n",
       "4       SS  Concussion  "
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ngs_merge['Injury'] = 'Concussion'\n",
    "ngs_merge.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# columns1 = [\"PlayID\", 'Injury', 'x', 'y', 'o', 'dir', 'Twist']\n",
    "# columns2 = [\"Game_Play_ID\", 'Injury', 'x', 'y', 'o', 'dir', 'Twist']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "something = ['PlayKey', 'Position', 'Injury', 'x', 'y', 'o', 'dir', 'Twist']\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "ngs_merge = ngs_merge.rename(columns={'Game_Play_ID': 'PlayKey'})[something]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "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>PlayKey</th>\n",
       "      <th>Position</th>\n",
       "      <th>Injury</th>\n",
       "      <th>x</th>\n",
       "      <th>y</th>\n",
       "      <th>o</th>\n",
       "      <th>dir</th>\n",
       "      <th>Twist</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>252-231-30982</td>\n",
       "      <td>CB</td>\n",
       "      <td>Concussion</td>\n",
       "      <td>46.209999</td>\n",
       "      <td>45.23</td>\n",
       "      <td>147.750000</td>\n",
       "      <td>315.339996</td>\n",
       "      <td>167.589996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>252-231-30982</td>\n",
       "      <td>CB</td>\n",
       "      <td>Concussion</td>\n",
       "      <td>46.209999</td>\n",
       "      <td>45.23</td>\n",
       "      <td>147.750000</td>\n",
       "      <td>315.339996</td>\n",
       "      <td>167.589996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>252-231-30982</td>\n",
       "      <td>CB</td>\n",
       "      <td>Concussion</td>\n",
       "      <td>46.209999</td>\n",
       "      <td>45.23</td>\n",
       "      <td>147.750000</td>\n",
       "      <td>315.339996</td>\n",
       "      <td>167.589996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>252-231-32070</td>\n",
       "      <td>WR</td>\n",
       "      <td>Concussion</td>\n",
       "      <td>46.259998</td>\n",
       "      <td>24.85</td>\n",
       "      <td>206.080002</td>\n",
       "      <td>264.890015</td>\n",
       "      <td>58.810013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>252-231-30084</td>\n",
       "      <td>SS</td>\n",
       "      <td>Concussion</td>\n",
       "      <td>45.740002</td>\n",
       "      <td>7.09</td>\n",
       "      <td>153.160004</td>\n",
       "      <td>69.709999</td>\n",
       "      <td>83.450005</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         PlayKey Position      Injury          x      y           o  \\\n",
       "0  252-231-30982       CB  Concussion  46.209999  45.23  147.750000   \n",
       "1  252-231-30982       CB  Concussion  46.209999  45.23  147.750000   \n",
       "2  252-231-30982       CB  Concussion  46.209999  45.23  147.750000   \n",
       "3  252-231-32070       WR  Concussion  46.259998  24.85  206.080002   \n",
       "4  252-231-30084       SS  Concussion  45.740002   7.09  153.160004   \n",
       "\n",
       "          dir       Twist  \n",
       "0  315.339996  167.589996  \n",
       "1  315.339996  167.589996  \n",
       "2  315.339996  167.589996  \n",
       "3  264.890015   58.810013  \n",
       "4   69.709999   83.450005  "
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ngs_merge.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "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>PlayKey</th>\n",
       "      <th>Position</th>\n",
       "      <th>Injury</th>\n",
       "      <th>x</th>\n",
       "      <th>y</th>\n",
       "      <th>o</th>\n",
       "      <th>dir</th>\n",
       "      <th>Twist</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>31070-3-7</td>\n",
       "      <td>RB</td>\n",
       "      <td>Knee</td>\n",
       "      <td>44.07</td>\n",
       "      <td>32.14</td>\n",
       "      <td>174.83</td>\n",
       "      <td>23.31</td>\n",
       "      <td>151.52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>31070-3-7</td>\n",
       "      <td>RB</td>\n",
       "      <td>Knee</td>\n",
       "      <td>44.08</td>\n",
       "      <td>32.14</td>\n",
       "      <td>175.09</td>\n",
       "      <td>20.18</td>\n",
       "      <td>154.91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>31070-3-7</td>\n",
       "      <td>RB</td>\n",
       "      <td>Knee</td>\n",
       "      <td>44.08</td>\n",
       "      <td>32.14</td>\n",
       "      <td>175.35</td>\n",
       "      <td>16.53</td>\n",
       "      <td>158.82</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>31070-3-7</td>\n",
       "      <td>RB</td>\n",
       "      <td>Knee</td>\n",
       "      <td>44.08</td>\n",
       "      <td>32.14</td>\n",
       "      <td>175.60</td>\n",
       "      <td>13.23</td>\n",
       "      <td>162.37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>31070-3-7</td>\n",
       "      <td>RB</td>\n",
       "      <td>Knee</td>\n",
       "      <td>44.08</td>\n",
       "      <td>32.14</td>\n",
       "      <td>175.82</td>\n",
       "      <td>9.78</td>\n",
       "      <td>166.04</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     PlayKey Position Injury      x      y       o    dir   Twist\n",
       "0  31070-3-7       RB   Knee  44.07  32.14  174.83  23.31  151.52\n",
       "1  31070-3-7       RB   Knee  44.08  32.14  175.09  20.18  154.91\n",
       "2  31070-3-7       RB   Knee  44.08  32.14  175.35  16.53  158.82\n",
       "3  31070-3-7       RB   Knee  44.08  32.14  175.60  13.23  162.37\n",
       "4  31070-3-7       RB   Knee  44.08  32.14  175.82   9.78  166.04"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_inj.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "All_Injuries = ngs_merge.append(merged_inj)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(10682376, 8)"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "All_Injuries.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "PlayKey          0\n",
       "Position    790245\n",
       "Injury           0\n",
       "x                0\n",
       "y                0\n",
       "o                0\n",
       "dir              0\n",
       "Twist            0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "All_Injuries.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "All_Injuries = All_Injuries.loc[All_Injuries.Position.isna() == False]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(9892131, 8)"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "All_Injuries.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "PlayKey     0\n",
       "Position    0\n",
       "Injury      0\n",
       "x           0\n",
       "y           0\n",
       "o           0\n",
       "dir         0\n",
       "Twist       0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "All_Injuries.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "All_Injuries.to_csv(\"NFL_Punt/All_Injuries.csv\", index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.7.13 ('mlenv')",
   "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.7.13"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "55a128abeef29120eb7bd22a6a2e883184ae9f9178564f0df9c7c7d3d676105f"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}