Names_match_experiment.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from fuzzywuzzy import fuzz"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "stats = pd.read_csv(\"./data/yearly_stats_ipl.csv\")"
   ]
  },
  {
   "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>POS</th>\n",
       "      <th>PLAYER</th>\n",
       "      <th>Pts</th>\n",
       "      <th>Mat</th>\n",
       "      <th>Wkts</th>\n",
       "      <th>Dots</th>\n",
       "      <th>4s</th>\n",
       "      <th>6s</th>\n",
       "      <th>Catches</th>\n",
       "      <th>Stumpings</th>\n",
       "      <th>team_name</th>\n",
       "      <th>nationality</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>ShaneWatson</td>\n",
       "      <td>406.5</td>\n",
       "      <td>15</td>\n",
       "      <td>17</td>\n",
       "      <td>158</td>\n",
       "      <td>47</td>\n",
       "      <td>19</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>RR</td>\n",
       "      <td>Overseas</td>\n",
       "      <td>2008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>SanathJayasuriya</td>\n",
       "      <td>308.5</td>\n",
       "      <td>14</td>\n",
       "      <td>4</td>\n",
       "      <td>36</td>\n",
       "      <td>58</td>\n",
       "      <td>31</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>MI</td>\n",
       "      <td>Overseas</td>\n",
       "      <td>2008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>YusufPathan</td>\n",
       "      <td>290.5</td>\n",
       "      <td>16</td>\n",
       "      <td>8</td>\n",
       "      <td>60</td>\n",
       "      <td>43</td>\n",
       "      <td>25</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>RR</td>\n",
       "      <td>Indian</td>\n",
       "      <td>2008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>AlbieMorkel</td>\n",
       "      <td>261.0</td>\n",
       "      <td>13</td>\n",
       "      <td>17</td>\n",
       "      <td>100</td>\n",
       "      <td>18</td>\n",
       "      <td>14</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>CSK</td>\n",
       "      <td>Overseas</td>\n",
       "      <td>2008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>IrfanPathan</td>\n",
       "      <td>259.0</td>\n",
       "      <td>14</td>\n",
       "      <td>15</td>\n",
       "      <td>153</td>\n",
       "      <td>9</td>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>KXIP</td>\n",
       "      <td>Indian</td>\n",
       "      <td>2008</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   POS            PLAYER    Pts  Mat  Wkts  Dots  4s  6s  Catches  Stumpings  \\\n",
       "0    1       ShaneWatson  406.5   15    17   158  47  19        2          0   \n",
       "1    2  SanathJayasuriya  308.5   14     4    36  58  31        2          0   \n",
       "2    3       YusufPathan  290.5   16     8    60  43  25        3          0   \n",
       "3    4       AlbieMorkel  261.0   13    17   100  18  14        3          0   \n",
       "4    5       IrfanPathan  259.0   14    15   153   9   6        4          0   \n",
       "\n",
       "  team_name nationality  year  \n",
       "0        RR    Overseas  2008  \n",
       "1        MI    Overseas  2008  \n",
       "2        RR      Indian  2008  \n",
       "3       CSK    Overseas  2008  \n",
       "4      KXIP      Indian  2008  "
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stats.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "ipl_auction_list = pd.read_excel(\"./data/IplAuctionPlayersList.xlsx\", sheet_name=\"Sheet1\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "official_players = ipl_auction_list[\"PLAYER NAME\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "### Looks like the initials are causing an issue. Hence, we are throwing away the initials"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "# vaas has 5 letter initial. Lets handle his case separately.\n",
    "new_official = [player if len(player.split(\" \")[0]) > 3 else \" \".join(player.split(\" \")[1:]) for player in official_players]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Match names from the other list - Based on Levenshtein Distance"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 145,
   "metadata": {},
   "outputs": [],
   "source": [
    "def search_word(queryName, threshold):\n",
    "    ratios = [fuzz.partial_ratio(player, queryName) for player in new_official]\n",
    "    if max(ratios) < threshold:\n",
    "        return \"NA\"\n",
    "    return official_players[ratios.index(max(ratios))]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "metadata": {},
   "outputs": [],
   "source": [
    "stats[\"matched_player\"] = stats[\"PLAYER\"].apply(lambda x: search_word(x, 70))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Fix partial match threshold until we have only the names represented in IPL Auction list.\n",
    "1. 70 seems a good number to start with\n",
    "2. Length of Non- NA players need to be same as the official players length."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 147,
   "metadata": {},
   "outputs": [],
   "source": [
    "matched_players = stats[stats.matched_player!=\"NA\"][\"matched_player\"].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 148,
   "metadata": {},
   "outputs": [],
   "source": [
    "remaining_players = set(official_players).difference(set(matched_players))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Lets write it back to the file. And fix the rest manually."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 149,
   "metadata": {},
   "outputs": [],
   "source": [
    "matched = stats.drop_duplicates([\"matched_player\"]).reset_index(drop=False)[[\"PLAYER\", \"matched_player\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 150,
   "metadata": {},
   "outputs": [],
   "source": [
    "merged = pd.merge(ipl_auction_list, matched, how='left', left_on='PLAYER NAME', right_on='matched_player')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 153,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(195, 2)"
      ]
     },
     "execution_count": 153,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "matched.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 152,
   "metadata": {},
   "outputs": [],
   "source": [
    "merged.to_csv(\"./data/partial_cleaned_ipl.csv\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### After Manual Cleaning a bit"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 254,
   "metadata": {},
   "outputs": [],
   "source": [
    "manual_cleaned_names = pd.read_csv(\"./data/manual_cleaned_ipl.csv\")[[\"PLAYER NAME\", \"PLAYER\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 255,
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_stats = pd.merge(stats, manual_cleaned_names, on=\"PLAYER\", how=\"inner\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 256,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['POS', 'PLAYER', 'Pts', 'Mat', 'Wkts', 'Dots', '4s', '6s', 'Catches',\n",
       "       'Stumpings', 'team_name', 'nationality', 'year', 'matched_player',\n",
       "       'PLAYER NAME'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 256,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_stats.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Got the player roles"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 257,
   "metadata": {},
   "outputs": [],
   "source": [
    "player_roles = pd.read_excel(\"./data/IPLPLayerRoles.xlsx\", sheet_name=\"Sheet1\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 258,
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_stats = pd.merge(merged_stats, player_roles, on=\"PLAYER NAME\", how=\"inner\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 259,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['POS', 'PLAYER', 'Pts', 'Mat', 'Wkts', 'Dots', '4s', '6s', 'Catches',\n",
       "       'Stumpings', 'team_name', 'nationality', 'year', 'matched_player',\n",
       "       'PLAYER NAME', 'ROLE'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 259,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_stats.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 260,
   "metadata": {},
   "outputs": [],
   "source": [
    "### Renaming columns \n",
    "merged_stats.columns = ['year_position', 'player_name', 'year_points', 'matches', 'num_wkts', 'num_dots', \n",
    "                 'num_fours', 'num_six', 'num_catches', 'num_stumpings', 'team_name', 'overseas_or_indian',\n",
    "                 'ipl_year', 'remove_col1', 'remove_col2', 'position']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 261,
   "metadata": {},
   "outputs": [],
   "source": [
    "cols_to_select = [col for col in merged_stats.columns if \"remove\" not in col]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 262,
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_stats[cols_to_select].to_csv(\"./data/names-cleaned-stats.csv\", 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.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}