02_Clean_Data_fix_duplicate_names.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "av_df = pd.read_csv('data/first_3_season_av_since_2000_RAW_2018-03-13.csv')\n",
    "combine_df = pd.read_csv('data/combine_data_RAW_2018-03-13.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 7870 entries, 0 to 7869\n",
      "Data columns (total 14 columns):\n",
      "Rk        7870 non-null int64\n",
      "Player    7870 non-null object\n",
      "From      7870 non-null int64\n",
      "To        7870 non-null int64\n",
      "Draft     4575 non-null object\n",
      "Tm        7870 non-null object\n",
      "Lg        7852 non-null object\n",
      "G         7715 non-null float64\n",
      "GS        7482 non-null float64\n",
      "Yrs       7870 non-null int64\n",
      "PB        7870 non-null int64\n",
      "AP1       7870 non-null int64\n",
      "AV        7664 non-null float64\n",
      "Pfr_ID    7870 non-null object\n",
      "dtypes: float64(3), int64(6), object(5)\n",
      "memory usage: 860.9+ KB\n"
     ]
    }
   ],
   "source": [
    "av_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>Rk</th>\n",
       "      <th>Player</th>\n",
       "      <th>From</th>\n",
       "      <th>To</th>\n",
       "      <th>Draft</th>\n",
       "      <th>Tm</th>\n",
       "      <th>Lg</th>\n",
       "      <th>G</th>\n",
       "      <th>GS</th>\n",
       "      <th>Yrs</th>\n",
       "      <th>PB</th>\n",
       "      <th>AP1</th>\n",
       "      <th>AV</th>\n",
       "      <th>Pfr_ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Cam Newton</td>\n",
       "      <td>2011</td>\n",
       "      <td>2013</td>\n",
       "      <td>1-1</td>\n",
       "      <td>CAR</td>\n",
       "      <td>NFL</td>\n",
       "      <td>48.0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>51.0</td>\n",
       "      <td>NewtCa00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Russell Wilson</td>\n",
       "      <td>2012</td>\n",
       "      <td>2014</td>\n",
       "      <td>3-75</td>\n",
       "      <td>SEA</td>\n",
       "      <td>NFL</td>\n",
       "      <td>48.0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>51.0</td>\n",
       "      <td>WilsRu00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Patrick Willis</td>\n",
       "      <td>2007</td>\n",
       "      <td>2009</td>\n",
       "      <td>1-11</td>\n",
       "      <td>SFO</td>\n",
       "      <td>NFL</td>\n",
       "      <td>48.0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>49.0</td>\n",
       "      <td>WillPa98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Patrick Peterson</td>\n",
       "      <td>2011</td>\n",
       "      <td>2013</td>\n",
       "      <td>1-5</td>\n",
       "      <td>CRD</td>\n",
       "      <td>NFL</td>\n",
       "      <td>48.0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>48.0</td>\n",
       "      <td>PetePa00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Aaron Donald</td>\n",
       "      <td>2014</td>\n",
       "      <td>2016</td>\n",
       "      <td>1-13</td>\n",
       "      <td>RAM</td>\n",
       "      <td>NFL</td>\n",
       "      <td>48.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>46.0</td>\n",
       "      <td>DonaAa00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Rk            Player  From    To Draft   Tm   Lg     G    GS  Yrs  PB  AP1  \\\n",
       "0   1        Cam Newton  2011  2013   1-1  CAR  NFL  48.0  48.0    3   2    0   \n",
       "1   2    Russell Wilson  2012  2014  3-75  SEA  NFL  48.0  48.0    3   2    0   \n",
       "2   3    Patrick Willis  2007  2009  1-11  SFO  NFL  48.0  48.0    3   3    2   \n",
       "3   4  Patrick Peterson  2011  2013   1-5  CRD  NFL  48.0  48.0    3   3    2   \n",
       "4   5      Aaron Donald  2014  2016  1-13  RAM  NFL  48.0  44.0    3   3    2   \n",
       "\n",
       "     AV    Pfr_ID  \n",
       "0  51.0  NewtCa00  \n",
       "1  51.0  WilsRu00  \n",
       "2  49.0  WillPa98  \n",
       "3  48.0  PetePa00  \n",
       "4  46.0  DonaAa00  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "av_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 6218 entries, 0 to 6217\n",
      "Data columns (total 15 columns):\n",
      "Player                 6218 non-null object\n",
      "Pos                    6218 non-null object\n",
      "School                 6218 non-null object\n",
      "College                4841 non-null object\n",
      "Ht                     6218 non-null object\n",
      "Wt                     6218 non-null int64\n",
      "40yd                   6046 non-null float64\n",
      "Vertical               4796 non-null float64\n",
      "BenchReps              4212 non-null float64\n",
      "BroadJump              4754 non-null float64\n",
      "3Cone                  3993 non-null float64\n",
      "Shuttle                4063 non-null float64\n",
      "Drafted (tm/rnd/yr)    3738 non-null object\n",
      "Year                   6218 non-null int64\n",
      "Pfr_ID                 4895 non-null object\n",
      "dtypes: float64(6), int64(2), object(7)\n",
      "memory usage: 728.8+ KB\n"
     ]
    }
   ],
   "source": [
    "combine_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>Player</th>\n",
       "      <th>Pos</th>\n",
       "      <th>School</th>\n",
       "      <th>College</th>\n",
       "      <th>Ht</th>\n",
       "      <th>Wt</th>\n",
       "      <th>40yd</th>\n",
       "      <th>Vertical</th>\n",
       "      <th>BenchReps</th>\n",
       "      <th>BroadJump</th>\n",
       "      <th>3Cone</th>\n",
       "      <th>Shuttle</th>\n",
       "      <th>Drafted (tm/rnd/yr)</th>\n",
       "      <th>Year</th>\n",
       "      <th>Pfr_ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>John Abraham</td>\n",
       "      <td>OLB</td>\n",
       "      <td>South Carolina</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6-4</td>\n",
       "      <td>252</td>\n",
       "      <td>4.55</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>New York Jets / 1st / 13th pick / 2000</td>\n",
       "      <td>2000</td>\n",
       "      <td>AbraJo00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Shaun Alexander</td>\n",
       "      <td>RB</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>College Stats</td>\n",
       "      <td>6-0</td>\n",
       "      <td>218</td>\n",
       "      <td>4.58</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Seattle Seahawks / 1st / 19th pick / 2000</td>\n",
       "      <td>2000</td>\n",
       "      <td>AlexSh00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Darnell Alford</td>\n",
       "      <td>OT</td>\n",
       "      <td>Boston College</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6-4</td>\n",
       "      <td>334</td>\n",
       "      <td>5.56</td>\n",
       "      <td>25.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>94.0</td>\n",
       "      <td>8.48</td>\n",
       "      <td>4.98</td>\n",
       "      <td>Kansas City Chiefs / 6th / 188th pick / 2000</td>\n",
       "      <td>2000</td>\n",
       "      <td>AlfoDa20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Kyle Allamon</td>\n",
       "      <td>TE</td>\n",
       "      <td>Texas Tech</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6-2</td>\n",
       "      <td>253</td>\n",
       "      <td>4.97</td>\n",
       "      <td>29.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>104.0</td>\n",
       "      <td>7.29</td>\n",
       "      <td>4.49</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Rashard Anderson</td>\n",
       "      <td>CB</td>\n",
       "      <td>Jackson State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6-2</td>\n",
       "      <td>206</td>\n",
       "      <td>4.55</td>\n",
       "      <td>34.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>123.0</td>\n",
       "      <td>7.18</td>\n",
       "      <td>4.15</td>\n",
       "      <td>Carolina Panthers / 1st / 23rd pick / 2000</td>\n",
       "      <td>2000</td>\n",
       "      <td>AndeRa21</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             Player  Pos          School        College   Ht   Wt  40yd  \\\n",
       "0      John Abraham  OLB  South Carolina            NaN  6-4  252  4.55   \n",
       "1   Shaun Alexander   RB         Alabama  College Stats  6-0  218  4.58   \n",
       "2    Darnell Alford   OT  Boston College            NaN  6-4  334  5.56   \n",
       "3      Kyle Allamon   TE      Texas Tech            NaN  6-2  253  4.97   \n",
       "4  Rashard Anderson   CB   Jackson State            NaN  6-2  206  4.55   \n",
       "\n",
       "   Vertical  BenchReps  BroadJump  3Cone  Shuttle  \\\n",
       "0       NaN        NaN        NaN    NaN      NaN   \n",
       "1       NaN        NaN        NaN    NaN      NaN   \n",
       "2      25.0       23.0       94.0   8.48     4.98   \n",
       "3      29.0        NaN      104.0   7.29     4.49   \n",
       "4      34.0        NaN      123.0   7.18     4.15   \n",
       "\n",
       "                            Drafted (tm/rnd/yr)  Year    Pfr_ID  \n",
       "0        New York Jets / 1st / 13th pick / 2000  2000  AbraJo00  \n",
       "1     Seattle Seahawks / 1st / 19th pick / 2000  2000  AlexSh00  \n",
       "2  Kansas City Chiefs / 6th / 188th pick / 2000  2000  AlfoDa20  \n",
       "3                                           NaN  2000       NaN  \n",
       "4    Carolina Panthers / 1st / 23rd pick / 2000  2000  AndeRa21  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "combine_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "combine_df = combine_df.merge(av_df[['Pfr_ID', 'AV']], how='left', on='Pfr_ID')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 6218 entries, 0 to 6217\n",
      "Data columns (total 16 columns):\n",
      "Player                 6218 non-null object\n",
      "Pos                    6218 non-null object\n",
      "School                 6218 non-null object\n",
      "College                4841 non-null object\n",
      "Ht                     6218 non-null object\n",
      "Wt                     6218 non-null int64\n",
      "40yd                   6046 non-null float64\n",
      "Vertical               4796 non-null float64\n",
      "BenchReps              4212 non-null float64\n",
      "BroadJump              4754 non-null float64\n",
      "3Cone                  3993 non-null float64\n",
      "Shuttle                4063 non-null float64\n",
      "Drafted (tm/rnd/yr)    3738 non-null object\n",
      "Year                   6218 non-null int64\n",
      "Pfr_ID                 4895 non-null object\n",
      "AV                     4240 non-null float64\n",
      "dtypes: float64(7), int64(2), object(7)\n",
      "memory usage: 825.8+ KB\n"
     ]
    }
   ],
   "source": [
    "combine_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>Player</th>\n",
       "      <th>Pos</th>\n",
       "      <th>School</th>\n",
       "      <th>College</th>\n",
       "      <th>Ht</th>\n",
       "      <th>Wt</th>\n",
       "      <th>40yd</th>\n",
       "      <th>Vertical</th>\n",
       "      <th>BenchReps</th>\n",
       "      <th>BroadJump</th>\n",
       "      <th>3Cone</th>\n",
       "      <th>Shuttle</th>\n",
       "      <th>Drafted (tm/rnd/yr)</th>\n",
       "      <th>Year</th>\n",
       "      <th>Pfr_ID</th>\n",
       "      <th>AV</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>John Abraham</td>\n",
       "      <td>OLB</td>\n",
       "      <td>South Carolina</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6-4</td>\n",
       "      <td>252</td>\n",
       "      <td>4.55</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>New York Jets / 1st / 13th pick / 2000</td>\n",
       "      <td>2000</td>\n",
       "      <td>AbraJo00</td>\n",
       "      <td>26.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Shaun Alexander</td>\n",
       "      <td>RB</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>College Stats</td>\n",
       "      <td>6-0</td>\n",
       "      <td>218</td>\n",
       "      <td>4.58</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Seattle Seahawks / 1st / 19th pick / 2000</td>\n",
       "      <td>2000</td>\n",
       "      <td>AlexSh00</td>\n",
       "      <td>26.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Darnell Alford</td>\n",
       "      <td>OT</td>\n",
       "      <td>Boston College</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6-4</td>\n",
       "      <td>334</td>\n",
       "      <td>5.56</td>\n",
       "      <td>25.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>94.0</td>\n",
       "      <td>8.48</td>\n",
       "      <td>4.98</td>\n",
       "      <td>Kansas City Chiefs / 6th / 188th pick / 2000</td>\n",
       "      <td>2000</td>\n",
       "      <td>AlfoDa20</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Kyle Allamon</td>\n",
       "      <td>TE</td>\n",
       "      <td>Texas Tech</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6-2</td>\n",
       "      <td>253</td>\n",
       "      <td>4.97</td>\n",
       "      <td>29.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>104.0</td>\n",
       "      <td>7.29</td>\n",
       "      <td>4.49</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Rashard Anderson</td>\n",
       "      <td>CB</td>\n",
       "      <td>Jackson State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6-2</td>\n",
       "      <td>206</td>\n",
       "      <td>4.55</td>\n",
       "      <td>34.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>123.0</td>\n",
       "      <td>7.18</td>\n",
       "      <td>4.15</td>\n",
       "      <td>Carolina Panthers / 1st / 23rd pick / 2000</td>\n",
       "      <td>2000</td>\n",
       "      <td>AndeRa21</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             Player  Pos          School        College   Ht   Wt  40yd  \\\n",
       "0      John Abraham  OLB  South Carolina            NaN  6-4  252  4.55   \n",
       "1   Shaun Alexander   RB         Alabama  College Stats  6-0  218  4.58   \n",
       "2    Darnell Alford   OT  Boston College            NaN  6-4  334  5.56   \n",
       "3      Kyle Allamon   TE      Texas Tech            NaN  6-2  253  4.97   \n",
       "4  Rashard Anderson   CB   Jackson State            NaN  6-2  206  4.55   \n",
       "\n",
       "   Vertical  BenchReps  BroadJump  3Cone  Shuttle  \\\n",
       "0       NaN        NaN        NaN    NaN      NaN   \n",
       "1       NaN        NaN        NaN    NaN      NaN   \n",
       "2      25.0       23.0       94.0   8.48     4.98   \n",
       "3      29.0        NaN      104.0   7.29     4.49   \n",
       "4      34.0        NaN      123.0   7.18     4.15   \n",
       "\n",
       "                            Drafted (tm/rnd/yr)  Year    Pfr_ID    AV  \n",
       "0        New York Jets / 1st / 13th pick / 2000  2000  AbraJo00  26.0  \n",
       "1     Seattle Seahawks / 1st / 19th pick / 2000  2000  AlexSh00  26.0  \n",
       "2  Kansas City Chiefs / 6th / 188th pick / 2000  2000  AlfoDa20   0.0  \n",
       "3                                           NaN  2000       NaN   NaN  \n",
       "4    Carolina Panthers / 1st / 23rd pick / 2000  2000  AndeRa21   6.0  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "combine_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Clean up Combine Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 6218 entries, 0 to 6217\n",
      "Data columns (total 16 columns):\n",
      "Player                 6218 non-null object\n",
      "Pos                    6218 non-null object\n",
      "School                 6218 non-null object\n",
      "College                4841 non-null object\n",
      "Ht                     6218 non-null object\n",
      "Wt                     6218 non-null int64\n",
      "40yd                   6046 non-null float64\n",
      "Vertical               4796 non-null float64\n",
      "BenchReps              4212 non-null float64\n",
      "BroadJump              4754 non-null float64\n",
      "3Cone                  3993 non-null float64\n",
      "Shuttle                4063 non-null float64\n",
      "Drafted (tm/rnd/yr)    3738 non-null object\n",
      "Year                   6218 non-null int64\n",
      "Pfr_ID                 4895 non-null object\n",
      "AV                     4240 non-null float64\n",
      "dtypes: float64(7), int64(2), object(7)\n",
      "memory usage: 825.8+ KB\n"
     ]
    }
   ],
   "source": [
    "combine_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "drafted_cols = ['Team', 'Round', 'Pick']\n",
    "combine_df[drafted_cols] = (combine_df['Drafted (tm/rnd/yr)']\n",
    "                                      .str.split(' / ', expand=True)\n",
    "                                      .iloc[:, :3])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "combine_df['Round'] = combine_df.Round.str.extract('(\\d+)', \n",
    "                                                   expand=False)\n",
    "combine_df['Pick'] = combine_df.Pick.str.extract('(\\d+)', \n",
    "                                                 expand=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# convert height to inches\n",
    "ht = combine_df.Ht.str.split('-', expand=True).astype(int)\n",
    "combine_df['Ht'] = ht[0]*12 + ht[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "combine_df.rename(columns={'40yd': 'Forty', '3Cone': 'Cone'},\n",
    "                  inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 6218 entries, 0 to 6217\n",
      "Data columns (total 19 columns):\n",
      "Player                 6218 non-null object\n",
      "Pos                    6218 non-null object\n",
      "School                 6218 non-null object\n",
      "College                4841 non-null object\n",
      "Ht                     6218 non-null int64\n",
      "Wt                     6218 non-null int64\n",
      "Forty                  6046 non-null float64\n",
      "Vertical               4796 non-null float64\n",
      "BenchReps              4212 non-null float64\n",
      "BroadJump              4754 non-null float64\n",
      "Cone                   3993 non-null float64\n",
      "Shuttle                4063 non-null float64\n",
      "Drafted (tm/rnd/yr)    3738 non-null object\n",
      "Year                   6218 non-null int64\n",
      "Pfr_ID                 4895 non-null object\n",
      "AV                     4240 non-null float64\n",
      "Team                   3738 non-null object\n",
      "Round                  3738 non-null object\n",
      "Pick                   3738 non-null object\n",
      "dtypes: float64(7), int64(3), object(9)\n",
      "memory usage: 971.6+ KB\n"
     ]
    }
   ],
   "source": [
    "combine_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>Player</th>\n",
       "      <th>Pos</th>\n",
       "      <th>School</th>\n",
       "      <th>College</th>\n",
       "      <th>Ht</th>\n",
       "      <th>Wt</th>\n",
       "      <th>Forty</th>\n",
       "      <th>Vertical</th>\n",
       "      <th>BenchReps</th>\n",
       "      <th>BroadJump</th>\n",
       "      <th>Cone</th>\n",
       "      <th>Shuttle</th>\n",
       "      <th>Drafted (tm/rnd/yr)</th>\n",
       "      <th>Year</th>\n",
       "      <th>Pfr_ID</th>\n",
       "      <th>AV</th>\n",
       "      <th>Team</th>\n",
       "      <th>Round</th>\n",
       "      <th>Pick</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>John Abraham</td>\n",
       "      <td>OLB</td>\n",
       "      <td>South Carolina</td>\n",
       "      <td>NaN</td>\n",
       "      <td>76</td>\n",
       "      <td>252</td>\n",
       "      <td>4.55</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>New York Jets / 1st / 13th pick / 2000</td>\n",
       "      <td>2000</td>\n",
       "      <td>AbraJo00</td>\n",
       "      <td>26.0</td>\n",
       "      <td>New York Jets</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Shaun Alexander</td>\n",
       "      <td>RB</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>College Stats</td>\n",
       "      <td>72</td>\n",
       "      <td>218</td>\n",
       "      <td>4.58</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Seattle Seahawks / 1st / 19th pick / 2000</td>\n",
       "      <td>2000</td>\n",
       "      <td>AlexSh00</td>\n",
       "      <td>26.0</td>\n",
       "      <td>Seattle Seahawks</td>\n",
       "      <td>1</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Darnell Alford</td>\n",
       "      <td>OT</td>\n",
       "      <td>Boston College</td>\n",
       "      <td>NaN</td>\n",
       "      <td>76</td>\n",
       "      <td>334</td>\n",
       "      <td>5.56</td>\n",
       "      <td>25.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>94.0</td>\n",
       "      <td>8.48</td>\n",
       "      <td>4.98</td>\n",
       "      <td>Kansas City Chiefs / 6th / 188th pick / 2000</td>\n",
       "      <td>2000</td>\n",
       "      <td>AlfoDa20</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Kansas City Chiefs</td>\n",
       "      <td>6</td>\n",
       "      <td>188</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Kyle Allamon</td>\n",
       "      <td>TE</td>\n",
       "      <td>Texas Tech</td>\n",
       "      <td>NaN</td>\n",
       "      <td>74</td>\n",
       "      <td>253</td>\n",
       "      <td>4.97</td>\n",
       "      <td>29.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>104.0</td>\n",
       "      <td>7.29</td>\n",
       "      <td>4.49</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Rashard Anderson</td>\n",
       "      <td>CB</td>\n",
       "      <td>Jackson State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>74</td>\n",
       "      <td>206</td>\n",
       "      <td>4.55</td>\n",
       "      <td>34.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>123.0</td>\n",
       "      <td>7.18</td>\n",
       "      <td>4.15</td>\n",
       "      <td>Carolina Panthers / 1st / 23rd pick / 2000</td>\n",
       "      <td>2000</td>\n",
       "      <td>AndeRa21</td>\n",
       "      <td>6.0</td>\n",
       "      <td>Carolina Panthers</td>\n",
       "      <td>1</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             Player  Pos          School        College  Ht   Wt  Forty  \\\n",
       "0      John Abraham  OLB  South Carolina            NaN  76  252   4.55   \n",
       "1   Shaun Alexander   RB         Alabama  College Stats  72  218   4.58   \n",
       "2    Darnell Alford   OT  Boston College            NaN  76  334   5.56   \n",
       "3      Kyle Allamon   TE      Texas Tech            NaN  74  253   4.97   \n",
       "4  Rashard Anderson   CB   Jackson State            NaN  74  206   4.55   \n",
       "\n",
       "   Vertical  BenchReps  BroadJump  Cone  Shuttle  \\\n",
       "0       NaN        NaN        NaN   NaN      NaN   \n",
       "1       NaN        NaN        NaN   NaN      NaN   \n",
       "2      25.0       23.0       94.0  8.48     4.98   \n",
       "3      29.0        NaN      104.0  7.29     4.49   \n",
       "4      34.0        NaN      123.0  7.18     4.15   \n",
       "\n",
       "                            Drafted (tm/rnd/yr)  Year    Pfr_ID    AV  \\\n",
       "0        New York Jets / 1st / 13th pick / 2000  2000  AbraJo00  26.0   \n",
       "1     Seattle Seahawks / 1st / 19th pick / 2000  2000  AlexSh00  26.0   \n",
       "2  Kansas City Chiefs / 6th / 188th pick / 2000  2000  AlfoDa20   0.0   \n",
       "3                                           NaN  2000       NaN   NaN   \n",
       "4    Carolina Panthers / 1st / 23rd pick / 2000  2000  AndeRa21   6.0   \n",
       "\n",
       "                 Team Round Pick  \n",
       "0       New York Jets     1   13  \n",
       "1    Seattle Seahawks     1   19  \n",
       "2  Kansas City Chiefs     6  188  \n",
       "3                 NaN   NaN  NaN  \n",
       "4   Carolina Panthers     1   23  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "combine_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "WR      857\n",
       "CB      630\n",
       "RB      540\n",
       "DE      487\n",
       "DT      463\n",
       "OT      460\n",
       "OLB     424\n",
       "OG      365\n",
       "QB      350\n",
       "TE      337\n",
       "ILB     276\n",
       "FS      229\n",
       "SS      213\n",
       "C       171\n",
       "P       120\n",
       "FB      117\n",
       "K        85\n",
       "S        27\n",
       "EDGE     23\n",
       "LS       20\n",
       "G        14\n",
       "LB        3\n",
       "NT        3\n",
       "DB        2\n",
       "OL        2\n",
       "Name: Pos, dtype: int64"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "combine_df.Pos.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "combine_df.AV.fillna(0, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "# duplicate names will be appended with a 3 digit number\n",
    "# so if John Smith shows up twice, then there\n",
    "# should be two names, John Smith 001 and John Smith 002\n",
    "# based my method from this answer\n",
    "# https://stackoverflow.com/questions/43015345/pandas-dataframe-count-duplicate-rows-and-fill-in-column?rq=1\n",
    "# https://stackoverflow.com/questions/37560548/how-to-change-value-of-second-duplicate-in-row\n",
    "combine_df['occ_num'] = (combine_df.groupby('Player').cumcount() + 1).astype(str).str.zfill(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "dupl_mask = combine_df.Player.duplicated(keep=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "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>Player</th>\n",
       "      <th>Pos</th>\n",
       "      <th>School</th>\n",
       "      <th>College</th>\n",
       "      <th>Ht</th>\n",
       "      <th>Wt</th>\n",
       "      <th>Forty</th>\n",
       "      <th>Vertical</th>\n",
       "      <th>BenchReps</th>\n",
       "      <th>BroadJump</th>\n",
       "      <th>Cone</th>\n",
       "      <th>Shuttle</th>\n",
       "      <th>Drafted (tm/rnd/yr)</th>\n",
       "      <th>Year</th>\n",
       "      <th>Pfr_ID</th>\n",
       "      <th>AV</th>\n",
       "      <th>Team</th>\n",
       "      <th>Round</th>\n",
       "      <th>Pick</th>\n",
       "      <th>occ_num</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>115</th>\n",
       "      <td>Mike Green</td>\n",
       "      <td>FS</td>\n",
       "      <td>Northwestern State</td>\n",
       "      <td>NaN</td>\n",
       "      <td>72</td>\n",
       "      <td>189</td>\n",
       "      <td>4.53</td>\n",
       "      <td>38.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>118.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.20</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>116</th>\n",
       "      <td>Mike Green</td>\n",
       "      <td>FB</td>\n",
       "      <td>Houston</td>\n",
       "      <td>College Stats</td>\n",
       "      <td>72</td>\n",
       "      <td>253</td>\n",
       "      <td>4.78</td>\n",
       "      <td>28.5</td>\n",
       "      <td>25.0</td>\n",
       "      <td>108.0</td>\n",
       "      <td>7.08</td>\n",
       "      <td>4.34</td>\n",
       "      <td>Tennessee Titans / 7th / 213th pick / 2000</td>\n",
       "      <td>2000</td>\n",
       "      <td>GreeMi00</td>\n",
       "      <td>2.0</td>\n",
       "      <td>Tennessee Titans</td>\n",
       "      <td>7</td>\n",
       "      <td>213</td>\n",
       "      <td>02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>849</th>\n",
       "      <td>Brandon Moore</td>\n",
       "      <td>OLB</td>\n",
       "      <td>Oklahoma</td>\n",
       "      <td>NaN</td>\n",
       "      <td>73</td>\n",
       "      <td>239</td>\n",
       "      <td>4.61</td>\n",
       "      <td>36.5</td>\n",
       "      <td>23.0</td>\n",
       "      <td>113.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2002</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>850</th>\n",
       "      <td>Brandon Moore</td>\n",
       "      <td>DT</td>\n",
       "      <td>Illinois</td>\n",
       "      <td>NaN</td>\n",
       "      <td>75</td>\n",
       "      <td>293</td>\n",
       "      <td>5.26</td>\n",
       "      <td>25.5</td>\n",
       "      <td>32.0</td>\n",
       "      <td>96.0</td>\n",
       "      <td>7.84</td>\n",
       "      <td>4.64</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2002</td>\n",
       "      <td>MoorBr20</td>\n",
       "      <td>13.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>02</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            Player  Pos              School        College  Ht   Wt  Forty  \\\n",
       "115     Mike Green   FS  Northwestern State            NaN  72  189   4.53   \n",
       "116     Mike Green   FB             Houston  College Stats  72  253   4.78   \n",
       "849  Brandon Moore  OLB            Oklahoma            NaN  73  239   4.61   \n",
       "850  Brandon Moore   DT            Illinois            NaN  75  293   5.26   \n",
       "\n",
       "     Vertical  BenchReps  BroadJump  Cone  Shuttle  \\\n",
       "115      38.5        NaN      118.0   NaN     4.20   \n",
       "116      28.5       25.0      108.0  7.08     4.34   \n",
       "849      36.5       23.0      113.0   NaN      NaN   \n",
       "850      25.5       32.0       96.0  7.84     4.64   \n",
       "\n",
       "                            Drafted (tm/rnd/yr)  Year    Pfr_ID    AV  \\\n",
       "115                                         NaN  2000       NaN   0.0   \n",
       "116  Tennessee Titans / 7th / 213th pick / 2000  2000  GreeMi00   2.0   \n",
       "849                                         NaN  2002       NaN   0.0   \n",
       "850                                         NaN  2002  MoorBr20  13.0   \n",
       "\n",
       "                 Team Round Pick occ_num  \n",
       "115               NaN   NaN  NaN      01  \n",
       "116  Tennessee Titans     7  213      02  \n",
       "849               NaN   NaN  NaN      01  \n",
       "850               NaN   NaN  NaN      02  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "combine_df.iloc[[115,116, 849, 850]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "combine_df.loc[dupl_mask,'Player'] = (combine_df.loc[dupl_mask, ['Player', 'occ_num']]\n",
    "                                                .apply(lambda x: '-'.join(x), axis=1))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "22       Marcus Bell-01\n",
       "29      Travis Brown-01\n",
       "115       Mike Green-01\n",
       "116       Mike Green-02\n",
       "147    Keith Jackson-01\n",
       "Name: Player, dtype: object"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "combine_df.loc[dupl_mask,'Player'].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "cols_to_drop = ['School', 'College', 'Drafted (tm/rnd/yr)', 'occ_num']\n",
    "combine_df.drop(cols_to_drop, axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 6218 entries, 0 to 6217\n",
      "Data columns (total 16 columns):\n",
      "Player       6218 non-null object\n",
      "Pos          6218 non-null object\n",
      "Ht           6218 non-null int64\n",
      "Wt           6218 non-null int64\n",
      "Forty        6046 non-null float64\n",
      "Vertical     4796 non-null float64\n",
      "BenchReps    4212 non-null float64\n",
      "BroadJump    4754 non-null float64\n",
      "Cone         3993 non-null float64\n",
      "Shuttle      4063 non-null float64\n",
      "Year         6218 non-null int64\n",
      "Pfr_ID       4895 non-null object\n",
      "AV           6218 non-null float64\n",
      "Team         3738 non-null object\n",
      "Round        3738 non-null object\n",
      "Pick         3738 non-null object\n",
      "dtypes: float64(7), int64(3), object(6)\n",
      "memory usage: 825.8+ KB\n"
     ]
    }
   ],
   "source": [
    "combine_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "combine_df.to_csv('data/combine_data_since_2000_PROCESSED_2018-04-26.csv',\n",
    "                  index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.6.4"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": false,
   "skip_h1_title": true,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": true,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}