merge_espn_and_fp.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "a777682b-13b5-4296-912a-d3971d002d1c",
   "metadata": {},
   "source": [
    "# Merge ESPN and FantasyPros"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "c77d181d-5467-45ea-aa63-de8aba236e84",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pathlib import Path\n",
    "\n",
    "import pandas as pd\n",
    "from rapidfuzz import fuzz\n",
    "\n",
    "pd.set_option('max_rows', 100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "66dc697e-5dd5-4991-a141-4328e6e232a2",
   "metadata": {},
   "outputs": [],
   "source": [
    "DATA_DIR = Path.cwd() / 'data'\n",
    "\n",
    "# manual map created bc they are the most different\n",
    "FP_TO_ESPN = {\n",
    "    'Tampa Bay Buccaneers': 'Buccaneers D/ST',\n",
    "    'Pittsburgh Steelers': 'Steelers D/ST',\n",
    "    'Baltimore Ravens': 'Ravens D/ST',\n",
    "    'Washington Football Team': 'Washington D/ST',\n",
    "    'San Francisco 49ers': '49ers D/ST',\n",
    "    'Buffalo Bills': 'Bills D/ST',\n",
    "    'Denver Broncos': 'Broncos D/ST',\n",
    "    'New England Patriots': 'Patriots D/ST',\n",
    "    'Los Angeles Rams': 'Rams D/ST',\n",
    "    'Cleveland Browns': 'Browns D/ST',\n",
    "    'Indianapolis Colts': 'Colts D/ST',\n",
    "    'Minnesota Vikings': 'Vikings D/ST',\n",
    "    'Miami Dolphins': 'Dolphins D/ST',\n",
    "    'New Orleans Saints': 'Saints D/ST',\n",
    "    'Green Bay Packers': 'Packers D/ST',\n",
    "    'Chicago Bears': 'Bears D/ST',\n",
    "    'New York Giants': 'Giants D/ST',\n",
    "    'Arizona Cardinals': 'Cardinals D/ST',\n",
    "    'New York Jets': 'Jets D/ST',\n",
    "    'Kansas City Chiefs': 'Chiefs D/ST',\n",
    "    'Tennessee Titans': 'Titans D/ST',\n",
    "    'Atlanta Falcons': 'Falcons D/ST',\n",
    "    'Carolina Panthers': 'Panthers D/ST',\n",
    "    'Seattle Seahawks': 'Seahawks D/ST',\n",
    "    'Los Angeles Chargers': 'Chargers D/ST',\n",
    "    'Philadelphia Eagles': 'Eagles D/ST',\n",
    "    'Cincinnati Bengals': 'Bengals D/ST',\n",
    "    'Las Vegas Raiders': 'Raiders D/ST',\n",
    "    'Dallas Cowboys': 'Cowboys D/ST',\n",
    "    'Jacksonville Jaguars': 'Jaguars D/ST',\n",
    "    'Detroit Lions': 'Lions D/ST',\n",
    "    'Houston Texans': 'Texans D/ST',\n",
    "\n",
    "    # extras\n",
    "    'Phillip Walker': 'P.J. Walker',\n",
    "    'Patrick Mahomes II': 'Patrick Mahomes',\n",
    "    'D.K. Metcalf': 'DK Metcalf',\n",
    "    'D.J. Moore': 'DJ Moore',\n",
    "    'Darrell Henderson': 'Darrell Henderson Jr.',\n",
    "    'Will Fuller V': 'William Fuller V',\n",
    "    'D.J. Chark Jr.': 'DJ Chark Jr.',\n",
    "    'Chris Herndon IV': 'Chris Herndon',\n",
    "    'Phillip Walker': 'Philip Rivers',\n",
    "    'Keelan Cole Sr.': 'Keelan Cole',\n",
    "    \"D'Wayne Eskridge\": 'Dee Eskridge',\n",
    "    'Josh Palmer': 'Joshua Palmer',\n",
    "    'Mohamed Sanu': 'Mohamed Sanu Sr.',\n",
    "}\n",
    "\n",
    "SKIP = set(['Jose Borregales', 'Chris Naggar', 'Andrew Jacas', 'Riley Patterson', 'Sammis Reyes',\n",
    "            'Tanner Hudson', 'Bryce Perkins', 'Drew Stanton'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "56154d90-6519-4a67-829a-a23f02417892",
   "metadata": {},
   "outputs": [],
   "source": [
    "def top_match(item: str, search_list: list[str]) -> str:\n",
    "    ans, max_ratio = '', 0\n",
    "    for s in search_list:\n",
    "            r = fuzz.ratio(item, s, score_cutoff=0.5)\n",
    "            if r > max_ratio:\n",
    "                max_ratio = r\n",
    "                ans = s\n",
    "\n",
    "    if max_ratio < 0.5:\n",
    "        return None\n",
    "    return ans"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "4f9163df-e57b-4dd3-8151-9060ca2f6fee",
   "metadata": {},
   "outputs": [],
   "source": [
    "fp = pd.read_csv(DATA_DIR / 'fantasypros_2021_draft_rankings_all.csv')\n",
    "fp = fp[~fp['name'].isin(SKIP)].copy()\n",
    "\n",
    "# change some FP names to match ESPN names\n",
    "for fp_name, espn_name in FP_TO_ESPN.items():\n",
    "    fp.loc[fp['name'] == fp_name, 'name'] = espn_name\n",
    "\n",
    "espn = pd.read_csv(DATA_DIR / 'espn_draft_trends_2021.csv')\n",
    "espn = espn[~espn['name'].isin(SKIP)].copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "df60551a-7dea-4c82-9996-63820345f294",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "29"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged = pd.merge(fp, espn, on=['name', 'pos'], how='left')\n",
    "\n",
    "espn_pos_to_names_dict = espn.groupby('pos')['name'].apply(lambda v: list(v)).to_dict()\n",
    "missing_names = merged[merged['player_id'].isnull()][['name', 'pos']].set_index('name').to_dict('dict').get('pos')\n",
    "\n",
    "len(missing_names)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "dcf2682e-b838-4e60-847a-fb02068112ef",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'Tommy Hudson': 'Tommy Sweeney',\n",
       " 'Cordarrelle Patterson': 'Andre Patton',\n",
       " 'J.P. Holtz': 'Jacob Hollister',\n",
       " 'Nick Bowers': 'Nick Boyle',\n",
       " 'Ryan Becker': 'Nate Becker',\n",
       " 'Mitchell Wilcox': 'Will Dissly',\n",
       " 'Ty Montgomery': 'David Montgomery',\n",
       " 'Demetric Felton': 'Darius Slayton',\n",
       " 'Eli Wolf': 'Clive Walford',\n",
       " 'Jake Breeland': 'Blake Bell',\n",
       " 'Andrew Beck': 'Nate Becker',\n",
       " 'Giovanni Ricci': 'Jonnu Smith',\n",
       " 'Jack Stoll': 'Jack Doyle',\n",
       " 'Mekhi Sargent': 'Chris Warren',\n",
       " 'Tre Harbison': 'Tra Carson',\n",
       " 'K.J. Hill Jr.': 'KJ Hill Jr.',\n",
       " 'James Proche': 'James Proche II',\n",
       " 'Josh Hokit': 'Josh Jacobs',\n",
       " 'Hakeem Butler': 'Emmanuel Butler',\n",
       " 'Chris Finke': 'Chris Godwin',\n",
       " 'Victor Bolden Jr.': 'Victor Bolden',\n",
       " 'Stanley Morgan Jr.': 'Stanley Morgan',\n",
       " 'Juwan Johnson': 'Cade Johnson',\n",
       " 'T.J. Vasher': 'C.J. Board',\n",
       " 'Austin Trammell': 'Austin Proehl',\n",
       " 'Tyler Simmons': 'Tyler Johnson',\n",
       " 'Rysen John': 'Tyler Johnson',\n",
       " 'Stephen Guidry': 'Steven Sims',\n",
       " 'Warren Jackson': 'DeSean Jackson'}"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# good enough\n",
    "best_match = {}\n",
    "for name, pos in missing_names.items():\n",
    "    if pos != 'DST':\n",
    "        espn_names = espn_pos_to_names_dict.get(pos)\n",
    "        best_match[name] = top_match(name, espn_names)\n",
    "\n",
    "best_match"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b72d1949-8a74-4cd2-bb8a-8f1ed9b0bb4f",
   "metadata": {},
   "source": [
    "## Export cheatsheet"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "cf97a975-1c76-40b2-ada6-029450078ea3",
   "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>tiers</th>\n",
       "      <th>pos</th>\n",
       "      <th>name</th>\n",
       "      <th>bye</th>\n",
       "      <th>rk</th>\n",
       "      <th>team</th>\n",
       "      <th>sos</th>\n",
       "      <th>ecr_vs_adp</th>\n",
       "      <th>player_id</th>\n",
       "      <th>ppr_rank</th>\n",
       "      <th>avg_draft_pos</th>\n",
       "      <th>pos_diff</th>\n",
       "      <th>ppr_auc_value</th>\n",
       "      <th>avg_auc_value</th>\n",
       "      <th>auc_diff</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>WR</td>\n",
       "      <td>Davante Adams</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "      <td>GB</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>16800</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>51</td>\n",
       "      <td>56</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>WR</td>\n",
       "      <td>Tyreek Hill</td>\n",
       "      <td>12</td>\n",
       "      <td>2</td>\n",
       "      <td>KC</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>3116406</td>\n",
       "      <td>13</td>\n",
       "      <td>11</td>\n",
       "      <td>-2</td>\n",
       "      <td>44</td>\n",
       "      <td>51</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>WR</td>\n",
       "      <td>Stefon Diggs</td>\n",
       "      <td>7</td>\n",
       "      <td>3</td>\n",
       "      <td>BUF</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>2976212</td>\n",
       "      <td>16</td>\n",
       "      <td>16</td>\n",
       "      <td>0</td>\n",
       "      <td>40</td>\n",
       "      <td>45</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>TE</td>\n",
       "      <td>Mark Andrews</td>\n",
       "      <td>8</td>\n",
       "      <td>4</td>\n",
       "      <td>BAL</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>3116365</td>\n",
       "      <td>63</td>\n",
       "      <td>48</td>\n",
       "      <td>-15</td>\n",
       "      <td>9</td>\n",
       "      <td>11</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>TE</td>\n",
       "      <td>T.J. Hockenson</td>\n",
       "      <td>9</td>\n",
       "      <td>5</td>\n",
       "      <td>DET</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>4036133</td>\n",
       "      <td>64</td>\n",
       "      <td>62</td>\n",
       "      <td>-2</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1</td>\n",
       "      <td>TE</td>\n",
       "      <td>Travis Kelce</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>KC</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>15847</td>\n",
       "      <td>6</td>\n",
       "      <td>8</td>\n",
       "      <td>2</td>\n",
       "      <td>53</td>\n",
       "      <td>55</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1</td>\n",
       "      <td>TE</td>\n",
       "      <td>Darren Waller</td>\n",
       "      <td>8</td>\n",
       "      <td>2</td>\n",
       "      <td>LV</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>2576925</td>\n",
       "      <td>24</td>\n",
       "      <td>25</td>\n",
       "      <td>1</td>\n",
       "      <td>32</td>\n",
       "      <td>32</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1</td>\n",
       "      <td>TE</td>\n",
       "      <td>George Kittle</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>SF</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>3040151</td>\n",
       "      <td>29</td>\n",
       "      <td>27</td>\n",
       "      <td>-2</td>\n",
       "      <td>28</td>\n",
       "      <td>30</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1</td>\n",
       "      <td>TE</td>\n",
       "      <td>Kyle Pitts</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>ATL</td>\n",
       "      <td>2</td>\n",
       "      <td>-2</td>\n",
       "      <td>4360248</td>\n",
       "      <td>65</td>\n",
       "      <td>52</td>\n",
       "      <td>-13</td>\n",
       "      <td>8</td>\n",
       "      <td>13</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1</td>\n",
       "      <td>RB</td>\n",
       "      <td>Alvin Kamara</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>NO</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>3054850</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>58</td>\n",
       "      <td>65</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   tiers pos            name  bye  rk team  sos  ecr_vs_adp  player_id  \\\n",
       "0      1  WR   Davante Adams   13   1   GB    1           0      16800   \n",
       "1      1  WR     Tyreek Hill   12   2   KC    3           0    3116406   \n",
       "2      1  WR    Stefon Diggs    7   3  BUF    3           0    2976212   \n",
       "3      1  TE    Mark Andrews    8   4  BAL    4           1    3116365   \n",
       "4      1  TE  T.J. Hockenson    9   5  DET    4           1    4036133   \n",
       "5      1  TE    Travis Kelce   12   1   KC    3           0      15847   \n",
       "6      1  TE   Darren Waller    8   2   LV    4           0    2576925   \n",
       "7      1  TE   George Kittle    6   3   SF    4           0    3040151   \n",
       "8      1  TE      Kyle Pitts    6   6  ATL    2          -2    4360248   \n",
       "9      1  RB    Alvin Kamara    6   3   NO    2           1    3054850   \n",
       "\n",
       "   ppr_rank  avg_draft_pos  pos_diff  ppr_auc_value  avg_auc_value  auc_diff  \n",
       "0         7              7         0             51             56         5  \n",
       "1        13             11        -2             44             51         7  \n",
       "2        16             16         0             40             45         5  \n",
       "3        63             48       -15              9             11         2  \n",
       "4        64             62        -2              8              8         0  \n",
       "5         6              8         2             53             55         2  \n",
       "6        24             25         1             32             32         0  \n",
       "7        29             27        -2             28             30         2  \n",
       "8        65             52       -13              8             13         5  \n",
       "9         4              4         0             58             65         7  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "for col in merged.columns[7:]:\n",
    "    merged[col] = merged[col].astype('Int64')  # nullable int\n",
    "\n",
    "merged.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "16291c16-3e43-40c4-a5dd-f85ce403903e",
   "metadata": {},
   "outputs": [],
   "source": [
    "merged.to_csv(DATA_DIR / 'cheatsheet_2021.csv', index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}