notebooks/01a-data-processing-fixing-names.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "fe359b5e-d7d6-49fe-8942-a794756476e3",
   "metadata": {},
   "source": [
    "# Helper Notebook for Initial Investigation\n",
    "After collecting supplemental information on pitcher performance (see [00-data-scrape-example.ipynb](./00-data-scrape-example.ipynb) and `data/supplemental-stats.csv`), this data needs to be aggregated with the provided data (`data/k.csv`).\n",
    "\n",
    "Since the player information will be used for joining data together, we need to ensure that the naming conventions of the provided data (`k.csv`) and the supplemental data (`supplemental-stats.csv`) are consistent.\n",
    "\n",
    "This notebook is for R&D purposes and eventually source code was created to clean and processes the data (See `bullpen.data_utils.load_data()`)\n",
    "\n",
    "**NOTE**: All functions defined in this notebook are defined in source code at `bullpen.data_utils`. They are shown here for convenience and to show original development and intent, but are stored in source code for future use and ability to unit test.\n",
    "\n",
    "\n",
    "# Helper Notebook for Initial Data Investigation\n",
    "\n",
    "After collecting supplemental pitcher performance data (see [00-data-scrape-example.ipynb](./00-data-scrape-example.ipynb) and `data/supplemental-stats.csv`), the next step is to aggregate this information with the provided dataset (`data/k.csv`).  \n",
    "\n",
    "To ensure successful data merging, it is essential to standardize player naming conventions between the two datasets:  \n",
    "- **`k.csv`**: Original dataset with core statistics.  \n",
    "- **`supplemental-stats.csv`**: Dataset with additional, scraped information.  \n",
    "\n",
    "This notebook serves as a research and development (R&D) tool to prototype the data cleaning and aggregation processes. The finalized procedures have been incorporated into the source code as a reusable utility:  \n",
    "- **`bullpen.data_utils.load_data()`**: Cleans, processes, and combines the datasets for downstream analysis.\n",
    "\n",
    "---\n",
    "\n",
    "## Development Workflow\n",
    "\n",
    "All functions demonstrated in this notebook are defined in the `bullpen.data_utils` module for clarity, reusability, and unit testing. While this notebook retains the initial development and intent of these functions, their inclusion here is primarily for transparency and ease of reference.  \n",
    "\n",
    "For production usage, refer to the source code in the `bullpen.data_utils` module.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "81dd0130-f437-4d5a-ae38-4a566d2218e7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DATA_DIR=PosixPath('/Users/logan/Desktop/repos/mlb-pitcher-xK/data')\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "from bullpen.data_utils import DATA_DIR, PlayerLookup\n",
    "\n",
    "print(f\"{DATA_DIR=}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "8faa1a72-8049-4291-9c2d-4cd760bf5116",
   "metadata": {},
   "outputs": [],
   "source": [
    "provided_data = pd.read_csv(DATA_DIR.joinpath(\"k.csv\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "ac8f52f7-72f4-44b4-8433-39f467ea2ee9",
   "metadata": {},
   "outputs": [],
   "source": [
    "suppl_data = pd.read_csv(DATA_DIR.joinpath(\"supplemental-stats.csv\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5efa2ef6-7c56-491f-9f78-46d64eeeb315",
   "metadata": {},
   "source": [
    "## Standardizing Naming Conventions\n",
    "\n",
    "While pattern matching and fuzzy searching could provide a more efficient approach for standardizing naming conventions, the limited number of discrepancies allowed for a quick manual adjustment in this instance.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "77b1b97a-f6d1-4219-921c-431e1dd4d110",
   "metadata": {},
   "outputs": [],
   "source": [
    "suppl_data.Name = suppl_data.Name.replace(\n",
    "    {\n",
    "        \"Manny Banuelos\": \"Manny Bañuelos\",\n",
    "        \"Ralph Garza\": \"Ralph Garza Jr.\",\n",
    "        \"Luis Ortiz\": \"Luis L. Ortiz\",\n",
    "        \"Jose Hernandez\": \"Jose E. Hernandez\",\n",
    "        \"Hyeon-jong Yang\": \"Hyeon-Jong Yang\",\n",
    "        \"Adrián Martinez\": \"Adrián Martínez\",\n",
    "    }\n",
    ")\n",
    "\n",
    "\n",
    "provided_data.Name = provided_data.Name.replace(\n",
    "    {\n",
    "        \"Eduardo Rodriguez\": \"Eduardo Rodríguez\",\n",
    "        \"Jose Alvarez\": \"José Álvarez\",\n",
    "        \"Sandy Alcantara\": \"Sandy Alcántara\",\n",
    "        \"Carlos Martinez\": \"Carlos Martínez\",\n",
    "        \"Phillips Valdez\": \"Phillips Valdéz\",\n",
    "        \"Jovani Moran\": \"Jovani Morán\",\n",
    "        \"Jose Cuas\": \"José Cuas\",\n",
    "        \"Jorge Alcala\": \"Jorge Alcalá\",\n",
    "        \"Jhoan Duran\": \"Jhoan Durán\",\n",
    "        \"Jesus Tinoco\": \"Jesús Tinoco\",\n",
    "        \"Brent Honeywell\": \"Brent Honeywell Jr.\",\n",
    "        \"Adrian Morejon\": \"Adrián Morejón\",\n",
    "    }\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "49565ee9-ef83-48f4-a303-73f75dd60326",
   "metadata": {},
   "outputs": [],
   "source": [
    "def compare_datasets(provided_df, supplemental_df):\n",
    "    provided = set(provided_df.Name)\n",
    "    suppl = set(supplemental_df.Name)\n",
    "\n",
    "    # Only care about what is in provided as that's what is used in prediction exercise.\n",
    "    diffs = provided - suppl\n",
    "    return diffs\n",
    "\n",
    "\n",
    "def lookup_player(dataframe, name, source=\"provided\", show_data=False):\n",
    "    mask = (\n",
    "        dataframe.Name.str.startswith(name)\n",
    "        if source == \"supplemental\"\n",
    "        else dataframe.Name == name\n",
    "    )\n",
    "    if show_data:\n",
    "        print(dataframe[mask])\n",
    "    return dataframe.loc[mask, \"Name\"].unique().tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "ff0296e3-d73c-4847-bd5d-e00694d73d87",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "set()"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "compare_datasets(provided_data, suppl_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "fb159080-c8d1-4839-91a9-970c22e2eec3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Empty DataFrame\n",
      "Columns: [MLBAMID, PlayerId, Name, Team, Age, Season, TBF, K%]\n",
      "Index: []\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lookup_player(provided_data, \"Jhoan Duran\", source=\"provided\", show_data=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "76ea032e-40ec-4c97-a306-a341048b0668",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       Rk             Name  Age   Tm    IP   PA   Pit  Pit/PA  Str   Str%  \\\n",
      "707   708   Adrián Morejón   22  SDP   4.2   20    80    4.00   51  0.638   \n",
      "1750  592  Adrián Martínez   25  OAK  57.2  260  1015    3.90  647  0.637   \n",
      "1826  668   Adrián Morejón   23  SDP  34.0  141   540    3.83  358  0.663   \n",
      "2836  597  Adrián Martínez   26  OAK  55.0  242   943    3.90  570  0.604   \n",
      "2908  669   Adrián Morejón   24  SDP   9.0   44   161    3.66   98  0.609   \n",
      "3993  690   Adrián Morejón   25  SDP  63.2  272  1064    3.91  714  0.671   \n",
      "\n",
      "      ...  02h  L/SO  S/SO  L/SO%  3pK  4pW  PAu  Pitu  Stru  Season  \n",
      "707   ...    0     0     3  0.000    0    1    0     0     0    2021  \n",
      "1750  ...    4    14    39  0.264    9    2    0     0     0    2022  \n",
      "1826  ...    2     3    23  0.107    5    2    0     0     0    2022  \n",
      "2836  ...    2    15    32  0.319    6    2    0     0     0    2023  \n",
      "2908  ...    0     5     3  0.625    0    0    0     0     0    2023  \n",
      "3993  ...    5    18    53  0.254    9    3    0     0     0    2024  \n",
      "\n",
      "[6 rows x 35 columns]\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "['Adrián Morejón', 'Adrián Martínez']"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lookup_player(suppl_data, \"Adrián\", source=\"supplemental\", show_data=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "346d9c32-ef1c-4ed5-a5f1-d852a75abb73",
   "metadata": {},
   "source": [
    "## Save player names to id mappings \n",
    "See `bullpen.data_utils.PlayerLookup()` for source code implementation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "4bcc71e7-a589-4a69-8d38-772a7ae310e3",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "tmp = (\n",
    "    provided_data[[\"MLBAMID\", \"PlayerId\", \"Name\"]].drop_duplicates().to_dict(\"records\")\n",
    ")\n",
    "# tmp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "c1d3e8f0-cc9a-40ed-8a18-b3e35fef35cf",
   "metadata": {},
   "outputs": [],
   "source": [
    "# import json\n",
    "\n",
    "# with open(DATA_DIR.joinpath(\"player_ids.json\"), \"w\") as fp:\n",
    "#     json.dump(tmp, fp)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "59f9e5cc-9ffb-43ec-b294-475fccd4cc96",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "loading player ids from /Users/logan/Desktop/repos/mlb-pitcher-xK/data/player_ids.json...\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>MLBAMID</th>\n",
       "      <th>PlayerId</th>\n",
       "      <th>Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>695243</td>\n",
       "      <td>31757</td>\n",
       "      <td>Mason Miller</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>621242</td>\n",
       "      <td>14710</td>\n",
       "      <td>Edwin Díaz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>518585</td>\n",
       "      <td>7048</td>\n",
       "      <td>Fernando Cruz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>623352</td>\n",
       "      <td>14212</td>\n",
       "      <td>Josh Hader</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>663574</td>\n",
       "      <td>19926</td>\n",
       "      <td>Tony Santillan</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   MLBAMID  PlayerId            Name\n",
       "0   695243     31757    Mason Miller\n",
       "1   621242     14710      Edwin Díaz\n",
       "2   518585      7048   Fernando Cruz\n",
       "3   623352     14212      Josh Hader\n",
       "4   663574     19926  Tony Santillan"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lookup = PlayerLookup()\n",
    "\n",
    "lookup.mapping.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "e96fcd19-4906-4df8-9bb5-a688cf2a5eca",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "670970"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lookup.get_id_from_name(\"Adrián Morejón\", source=\"mlb\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "0ff210cf-22ec-4585-8936-f386b471dccb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "20039"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lookup.get_id_from_name(\"Adrián Morejón\", source=\"fangraphs\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "9b9310d0-0575-4b11-935b-6855d3b0473b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Adrián Morejón'"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lookup.get_name_from_id(670970)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "26b22169-8a8f-4ecb-9d34-16d1f38a38a9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Adrián Morejón'"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lookup.get_name_from_id(20039, source=\"fangraphs\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "03135c41-2472-4498-83bd-be2f98be6854",
   "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>Name</th>\n",
       "      <th>PlayerId</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Logan Allen</td>\n",
       "      <td>27589</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Logan Allen</td>\n",
       "      <td>18555</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Name  PlayerId\n",
       "0  Logan Allen     27589\n",
       "1  Logan Allen     18555"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lookup.get_id_from_name(\"Logan Allen\", source=\"fangraphs\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "7a3ebb00-62b1-47e4-8567-3edda1b899bb",
   "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>Name</th>\n",
       "      <th>MLBAMID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Logan Allen</td>\n",
       "      <td>671106</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Logan Allen</td>\n",
       "      <td>663531</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Name  MLBAMID\n",
       "0  Logan Allen   671106\n",
       "1  Logan Allen   663531"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lookup.get_id_from_name(\"Logan Allen\", source=\"mlb\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "de89b1f1-3db1-4f21-a0b4-5c8a73225ba9",
   "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>Name</th>\n",
       "      <th>MLBAMID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [Name, MLBAMID]\n",
       "Index: []"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lookup.get_id_from_name(\"Logan Alln\", source=\"mlb\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "ee36c35f-784d-4122-8dda-23c4ae26ebd1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Logan Allen'"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lookup.get_name_from_id(671106, source=\"mlb\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0c17d733-6e4b-4ddd-adc7-4c536c302474",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.11.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}