{
 "metadata": {
  "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.2"
  },
  "orig_nbformat": 2,
  "kernelspec": {
   "name": "python392jvsc74a57bd0a27d3f2bf68df5402465348834a2195030d3fc5bfc8e594e2a17c8c7e2447c85",
   "display_name": "Python 3.9.2 64-bit ('ds-3.9': conda)"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2,
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "from pathlib import Path\n",
    "import re"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# define some needed variables, lists, and the like\n",
    "events = {'wmpo': 't003', 'api': 't009', 'tpc': 'to11', 'rbch': 't012', 'masters': 't014', 'pga': 't033'}\n",
    "stats = {'drv': '101', 'gir': '103', 't2g': '02674', 'scramble': '130'}\n",
    "st_cols = {'drv': ['PLAYER NAME', 'AVG.', 'TOTAL DISTANCE', 'TOTAL DRIVES'],\n",
    "           'gir': ['PLAYER NAME', '%', 'GREENS HIT', '# HOLES', 'RELATIVE/PAR'],\n",
    "           't2g': ['PLAYER NAME', 'AVERAGE', 'SG:OTT', 'SG:APR', 'SG:ARG'],\n",
    "           'scramble': ['PLAYER NAME', '%', 'PAR OR BETTER', 'MISSED GIR']\n",
    "}\n",
    "# data directory and stats, tournaments and years to process\n",
    "d_dir = \"./data/\"\n",
    "p_sids = ['drv', 'gir']\n",
    "p_tids = ['pga', 'rbch']\n",
    "p_yrs = ['2020', '2021']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# function: take year, tounament id and stat id, return CSV file path (relative) \n",
    "def get_csv_nm(t_yr, t_id, p_st):\n",
    "    global d_dir\n",
    "    return f\"{d_dir}{t_id}_{t_yr}_{p_st}_2.test.csv\"\n",
    "\n",
    "# function: will take a year, tournament and stat, read csv, return suitable DataFrame\n",
    "def csv_2_df(t_yr, t_id, p_st):\n",
    "    \"\"\" Read appropriate CSV file into DataFrame. Modify Dataframe to employ multi-indices.\n",
    "        Return modified DataFrame.\n",
    "        Useage: csv_2_df(t_yr, t_id, p_st)\n",
    "          where t_yr = tournament year\n",
    "                t_id = tournament id (e.g. 'pga')\n",
    "                p_st = player stat (e.g. 'drv', 'gir')\n",
    "    \"\"\"\n",
    "    global st_cols\n",
    "    col_nms = {'drv': {'AVG.': 'drv'},\n",
    "                'gir': {'%': 'gir'},\n",
    "                'scramble': {'%': 'scramble'}\n",
    "    }\n",
    "\n",
    "    csv_fl = get_csv_nm(t_yr, t_id, p_st)\n",
    "    ty_m_idx = pd.MultiIndex.from_tuples([(t_yr, t_id)])\n",
    "    s_col = st_cols[p_st][1]\n",
    "    df_stats = pd.read_csv(csv_fl, index_col=['PLAYER NAME'], usecols=[s_col, 'PLAYER NAME'])\n",
    "    df_stats.rename(columns=col_nms[p_st], inplace=True)\n",
    "    s_tmp = df_stats.stack()\n",
    "    ts_df = pd.DataFrame(s_tmp, columns=ty_m_idx)\n",
    "    ts_df.rename_axis(['player', 'stat'], inplace=True)\n",
    "    return ts_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/plain": "                         2020\n                          pga\nplayer            stat       \nCameron Champ     drv   321.1\nBryson DeChambeau drv   318.1\nRory McIlroy      drv   312.5\nSepp Straka       drv   305.8\nTommy Fleetwood   drv   305.5\n...                       ...\nCharl Schwartzel  drv   276.0\nChez Reavie       drv   274.5\nBrendon Todd      drv   272.3\nPatrick Reed      drv   271.6\nMark Hubbard      drv   268.8\n\n[79 rows x 1 columns]",
      "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 tr th {\n        text-align: left;\n    }\n\n    .dataframe thead tr:last-of-type th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr>\n      <th></th>\n      <th></th>\n      <th>2020</th>\n    </tr>\n    <tr>\n      <th></th>\n      <th></th>\n      <th>pga</th>\n    </tr>\n    <tr>\n      <th>player</th>\n      <th>stat</th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>Cameron Champ</th>\n      <th>drv</th>\n      <td>321.1</td>\n    </tr>\n    <tr>\n      <th>Bryson DeChambeau</th>\n      <th>drv</th>\n      <td>318.1</td>\n    </tr>\n    <tr>\n      <th>Rory McIlroy</th>\n      <th>drv</th>\n      <td>312.5</td>\n    </tr>\n    <tr>\n      <th>Sepp Straka</th>\n      <th>drv</th>\n      <td>305.8</td>\n    </tr>\n    <tr>\n      <th>Tommy Fleetwood</th>\n      <th>drv</th>\n      <td>305.5</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <th>...</th>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>Charl Schwartzel</th>\n      <th>drv</th>\n      <td>276.0</td>\n    </tr>\n    <tr>\n      <th>Chez Reavie</th>\n      <th>drv</th>\n      <td>274.5</td>\n    </tr>\n    <tr>\n      <th>Brendon Todd</th>\n      <th>drv</th>\n      <td>272.3</td>\n    </tr>\n    <tr>\n      <th>Patrick Reed</th>\n      <th>drv</th>\n      <td>271.6</td>\n    </tr>\n    <tr>\n      <th>Mark Hubbard</th>\n      <th>drv</th>\n      <td>268.8</td>\n    </tr>\n  </tbody>\n</table>\n<p>79 rows × 1 columns</p>\n</div>"
     },
     "metadata": {}
    },
    {
     "output_type": "display_data",
     "data": {
      "text/plain": "                        2020\n                         pga\nplayer           stat       \nMatthew Wolff    gir   77.78\nPaul Casey       gir   76.39\nJason Day        gir   76.39\nLouis Oosthuizen gir   73.61\nCameron Champ    gir   73.61\n...                      ...\nDenny McCarthy   gir   54.17\nHarris English   gir   52.78\nBrian Harman     gir   51.39\nBrandt Snedeker  gir   51.39\nJ.T. Poston      gir   50.00\n\n[79 rows x 1 columns]",
      "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 tr th {\n        text-align: left;\n    }\n\n    .dataframe thead tr:last-of-type th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr>\n      <th></th>\n      <th></th>\n      <th>2020</th>\n    </tr>\n    <tr>\n      <th></th>\n      <th></th>\n      <th>pga</th>\n    </tr>\n    <tr>\n      <th>player</th>\n      <th>stat</th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>Matthew Wolff</th>\n      <th>gir</th>\n      <td>77.78</td>\n    </tr>\n    <tr>\n      <th>Paul Casey</th>\n      <th>gir</th>\n      <td>76.39</td>\n    </tr>\n    <tr>\n      <th>Jason Day</th>\n      <th>gir</th>\n      <td>76.39</td>\n    </tr>\n    <tr>\n      <th>Louis Oosthuizen</th>\n      <th>gir</th>\n      <td>73.61</td>\n    </tr>\n    <tr>\n      <th>Cameron Champ</th>\n      <th>gir</th>\n      <td>73.61</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <th>...</th>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>Denny McCarthy</th>\n      <th>gir</th>\n      <td>54.17</td>\n    </tr>\n    <tr>\n      <th>Harris English</th>\n      <th>gir</th>\n      <td>52.78</td>\n    </tr>\n    <tr>\n      <th>Brian Harman</th>\n      <th>gir</th>\n      <td>51.39</td>\n    </tr>\n    <tr>\n      <th>Brandt Snedeker</th>\n      <th>gir</th>\n      <td>51.39</td>\n    </tr>\n    <tr>\n      <th>J.T. Poston</th>\n      <th>gir</th>\n      <td>50.00</td>\n    </tr>\n  </tbody>\n</table>\n<p>79 rows × 1 columns</p>\n</div>"
     },
     "metadata": {}
    }
   ],
   "source": [
    "# quick test\n",
    "df1 = csv_2_df('2020', 'pga', 'drv')\n",
    "display(df1)\n",
    "df2 = csv_2_df('2020', 'pga', 'gir')\n",
    "display(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "# function: will take a year, a tournament and a list of stats, generate a DataFrame for that tournament and year\n",
    "def tourney_2_df(t_yr, t_id, p_sts):\n",
    "    \"\"\" Combine all requested stats for a given tournament and year into a single DataFrame. \n",
    "        Return DataFrame.\n",
    "        Useage: tourney_2_df(t_yr, t_id, p_sts)\n",
    "          where t_yr = tournament year\n",
    "                t_id = tournament id (e.g. 'pga')\n",
    "                p_sts = list of player stat (e.g. ['drv', 'gir'])\n",
    "    \"\"\"\n",
    "    df1 = csv_2_df(t_yr, t_id, p_sts[0])\n",
    "    if len(p_sts) > 1:\n",
    "        df2 = csv_2_df(t_yr, t_id, p_sts[1])\n",
    "        df_tourney = pd.concat([df1, df2])\n",
    "    else:\n",
    "        return df1\n",
    "    if len(p_sts) > 2:\n",
    "        passs\n",
    "    \n",
    "    ndx_sort2 = sorted(df_tourney.index,key=lambda x: re.split(r'\\W+', x[0])[-1])\n",
    "    df_tourney = df_tourney.reindex(ndx_sort2)\n",
    "    return df_tourney\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/plain": "                      2020\n                       pga\nplayer        stat        \nByeong Hun An drv   286.60\n              gir    62.50\nAbraham Ancer drv   295.60\n              gir    63.89\nDaniel Berger drv   291.90\n...                    ...\nMatthew Wolff gir    77.78\nGary Woodland drv   293.00\n              gir    65.28\nTiger Woods   drv   304.00\n              gir    62.50\n\n[158 rows x 1 columns]",
      "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 tr th {\n        text-align: left;\n    }\n\n    .dataframe thead tr:last-of-type th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr>\n      <th></th>\n      <th></th>\n      <th>2020</th>\n    </tr>\n    <tr>\n      <th></th>\n      <th></th>\n      <th>pga</th>\n    </tr>\n    <tr>\n      <th>player</th>\n      <th>stat</th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Byeong Hun An</th>\n      <th>drv</th>\n      <td>286.60</td>\n    </tr>\n    <tr>\n      <th>gir</th>\n      <td>62.50</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Abraham Ancer</th>\n      <th>drv</th>\n      <td>295.60</td>\n    </tr>\n    <tr>\n      <th>gir</th>\n      <td>63.89</td>\n    </tr>\n    <tr>\n      <th>Daniel Berger</th>\n      <th>drv</th>\n      <td>291.90</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <th>...</th>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>Matthew Wolff</th>\n      <th>gir</th>\n      <td>77.78</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Gary Woodland</th>\n      <th>drv</th>\n      <td>293.00</td>\n    </tr>\n    <tr>\n      <th>gir</th>\n      <td>65.28</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Tiger Woods</th>\n      <th>drv</th>\n      <td>304.00</td>\n    </tr>\n    <tr>\n      <th>gir</th>\n      <td>62.50</td>\n    </tr>\n  </tbody>\n</table>\n<p>158 rows × 1 columns</p>\n</div>"
     },
     "metadata": {}
    }
   ],
   "source": [
    "# test\n",
    "pga_2020 = tourney_2_df('2020', 'pga', ['drv', 'gir'])\n",
    "display(pga_2020)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "# function: will take a year, list of tournaments and list of stats and generate a DataFrame for that year\n",
    "def year_2_df(t_yr, t_ids, p_sts):\n",
    "    df1 = tourney_2_df(t_yr, t_ids[0], p_sts)\n",
    "    if len(t_ids) == 1:\n",
    "        return df1\n",
    "    df2 = tourney_2_df(t_yr, t_ids[1], p_sts)\n",
    "    df_comb = pd.merge(df1, df2, how='outer', on=['player', 'stat'])\n",
    "    if len(t_ids) > 2:\n",
    "        pass\n",
    "    ndx_sort2 = sorted(df_comb.index,key=lambda x: re.split(r'\\W+', x[0])[-1])\n",
    "    df_comb = df_comb.reindex(ndx_sort2)\n",
    "\n",
    "    return df_comb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/plain": "                      2020        \n                       pga    rbch\nplayer        stat                \nByeong Hun An drv   286.60     NaN\n              gir    62.50     NaN\nAbraham Ancer drv   295.60  278.10\n              gir    63.89   90.28\nRyan Armour   drv      NaN  275.00\n...                    ...     ...\nMatthew Wolff gir    77.78     NaN\nGary Woodland drv   293.00  287.00\n              gir    65.28   73.61\nTiger Woods   drv   304.00     NaN\n              gir    62.50     NaN\n\n[234 rows x 2 columns]",
      "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 tr th {\n        text-align: left;\n    }\n\n    .dataframe thead tr:last-of-type th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr>\n      <th></th>\n      <th></th>\n      <th colspan=\"2\" halign=\"left\">2020</th>\n    </tr>\n    <tr>\n      <th></th>\n      <th></th>\n      <th>pga</th>\n      <th>rbch</th>\n    </tr>\n    <tr>\n      <th>player</th>\n      <th>stat</th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Byeong Hun An</th>\n      <th>drv</th>\n      <td>286.60</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>gir</th>\n      <td>62.50</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Abraham Ancer</th>\n      <th>drv</th>\n      <td>295.60</td>\n      <td>278.10</td>\n    </tr>\n    <tr>\n      <th>gir</th>\n      <td>63.89</td>\n      <td>90.28</td>\n    </tr>\n    <tr>\n      <th>Ryan Armour</th>\n      <th>drv</th>\n      <td>NaN</td>\n      <td>275.00</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <th>...</th>\n      <td>...</td>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>Matthew Wolff</th>\n      <th>gir</th>\n      <td>77.78</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Gary Woodland</th>\n      <th>drv</th>\n      <td>293.00</td>\n      <td>287.00</td>\n    </tr>\n    <tr>\n      <th>gir</th>\n      <td>65.28</td>\n      <td>73.61</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Tiger Woods</th>\n      <th>drv</th>\n      <td>304.00</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>gir</th>\n      <td>62.50</td>\n      <td>NaN</td>\n    </tr>\n  </tbody>\n</table>\n<p>234 rows × 2 columns</p>\n</div>"
     },
     "metadata": {}
    }
   ],
   "source": [
    "# test year_2_df\n",
    "df_2020 = year_2_df('2020', ['pga', 'rbch'], ['drv', 'gir'])\n",
    "display(df_2020)\n",
    "# want to see what the csv looks like\n",
    "golf_csv = f'{d_dir}golf_play_8.test.csv'\n",
    "df_2020.to_csv(golf_csv)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/plain": "                       2021        \n                        pga    rbch\nplayer         stat                \nByeong Hun An  drv   306.50     NaN\n               gir    48.61     NaN\nAbraham Ancer  drv   301.80  299.90\n               gir    61.11   70.83\nDaniel Berger  drv   298.40  299.60\n...                     ...     ...\nAaron Wise     gir    54.17     NaN\nGary Woodland  drv   311.10     NaN\n               gir    58.33     NaN\nWill Zalatoris drv   306.50  312.00\n               gir    61.11   58.33\n\n[226 rows x 2 columns]",
      "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 tr th {\n        text-align: left;\n    }\n\n    .dataframe thead tr:last-of-type th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr>\n      <th></th>\n      <th></th>\n      <th colspan=\"2\" halign=\"left\">2021</th>\n    </tr>\n    <tr>\n      <th></th>\n      <th></th>\n      <th>pga</th>\n      <th>rbch</th>\n    </tr>\n    <tr>\n      <th>player</th>\n      <th>stat</th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Byeong Hun An</th>\n      <th>drv</th>\n      <td>306.50</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>gir</th>\n      <td>48.61</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Abraham Ancer</th>\n      <th>drv</th>\n      <td>301.80</td>\n      <td>299.90</td>\n    </tr>\n    <tr>\n      <th>gir</th>\n      <td>61.11</td>\n      <td>70.83</td>\n    </tr>\n    <tr>\n      <th>Daniel Berger</th>\n      <th>drv</th>\n      <td>298.40</td>\n      <td>299.60</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <th>...</th>\n      <td>...</td>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>Aaron Wise</th>\n      <th>gir</th>\n      <td>54.17</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Gary Woodland</th>\n      <th>drv</th>\n      <td>311.10</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>gir</th>\n      <td>58.33</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Will Zalatoris</th>\n      <th>drv</th>\n      <td>306.50</td>\n      <td>312.00</td>\n    </tr>\n    <tr>\n      <th>gir</th>\n      <td>61.11</td>\n      <td>58.33</td>\n    </tr>\n  </tbody>\n</table>\n<p>226 rows × 2 columns</p>\n</div>"
     },
     "metadata": {}
    },
    {
     "output_type": "display_data",
     "data": {
      "text/plain": "                       2020            2021        \n                        pga    rbch     pga    rbch\nplayer         stat                                \nByeong Hun An  drv   286.60     NaN  306.50     NaN\n               gir    62.50     NaN   48.61     NaN\nAbraham Ancer  drv   295.60  278.10  301.80  299.90\n               gir    63.89   90.28   61.11   70.83\nRyan Armour    drv      NaN  275.00     NaN     NaN\n...                     ...     ...     ...     ...\nGary Woodland  gir    65.28   73.61   58.33     NaN\nTiger Woods    drv   304.00     NaN     NaN     NaN\n               gir    62.50     NaN     NaN     NaN\nWill Zalatoris drv      NaN     NaN  306.50  312.00\n               gir      NaN     NaN   61.11   58.33\n\n[318 rows x 4 columns]",
      "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 tr th {\n        text-align: left;\n    }\n\n    .dataframe thead tr:last-of-type th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr>\n      <th></th>\n      <th></th>\n      <th colspan=\"2\" halign=\"left\">2020</th>\n      <th colspan=\"2\" halign=\"left\">2021</th>\n    </tr>\n    <tr>\n      <th></th>\n      <th></th>\n      <th>pga</th>\n      <th>rbch</th>\n      <th>pga</th>\n      <th>rbch</th>\n    </tr>\n    <tr>\n      <th>player</th>\n      <th>stat</th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Byeong Hun An</th>\n      <th>drv</th>\n      <td>286.60</td>\n      <td>NaN</td>\n      <td>306.50</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>gir</th>\n      <td>62.50</td>\n      <td>NaN</td>\n      <td>48.61</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Abraham Ancer</th>\n      <th>drv</th>\n      <td>295.60</td>\n      <td>278.10</td>\n      <td>301.80</td>\n      <td>299.90</td>\n    </tr>\n    <tr>\n      <th>gir</th>\n      <td>63.89</td>\n      <td>90.28</td>\n      <td>61.11</td>\n      <td>70.83</td>\n    </tr>\n    <tr>\n      <th>Ryan Armour</th>\n      <th>drv</th>\n      <td>NaN</td>\n      <td>275.00</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <th>...</th>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>Gary Woodland</th>\n      <th>gir</th>\n      <td>65.28</td>\n      <td>73.61</td>\n      <td>58.33</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Tiger Woods</th>\n      <th>drv</th>\n      <td>304.00</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>gir</th>\n      <td>62.50</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Will Zalatoris</th>\n      <th>drv</th>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>306.50</td>\n      <td>312.00</td>\n    </tr>\n    <tr>\n      <th>gir</th>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>61.11</td>\n      <td>58.33</td>\n    </tr>\n  </tbody>\n</table>\n<p>318 rows × 4 columns</p>\n</div>"
     },
     "metadata": {}
    }
   ],
   "source": [
    "# test merging two years\n",
    "df_2021 = year_2_df('2021', ['pga', 'rbch'], ['drv', 'gir'])\n",
    "display(df_2021)\n",
    "df_comb = pd.merge(df_2020, df_2021, how='outer', on=['player', 'stat'])\n",
    "ndx_sort2 = sorted(df_comb.index,key=lambda x: re.split(r'\\W+', x[0])[-1])\n",
    "df_comb = df_comb.reindex(ndx_sort2)\n",
    "display(df_comb)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "# let's try that with 3 different stats\n",
    "# will need to redefine tourney_2_df\n",
    "# you may have noticed the extra condition with only a 'pass'\n",
    "def tourney_2_df(t_yr, t_id, p_sts):\n",
    "    \"\"\" Combine all requested stats for a given tournament and year into a single DataFrame. \n",
    "        Return DataFrame.\n",
    "        Useage: tourney_2_df(t_yr, t_id, p_sts)\n",
    "          where t_yr = tournament year\n",
    "                t_id = tournament id (e.g. 'pga')\n",
    "                p_sts = list of player stat (e.g. ['drv', 'gir'])\n",
    "    \"\"\"\n",
    "    df1 = csv_2_df(t_yr, t_id, p_sts[0])\n",
    "    if len(p_sts) > 1:\n",
    "        df2 = csv_2_df(t_yr, t_id, p_sts[1])\n",
    "        df_tourney = pd.concat([df1, df2])\n",
    "    else:\n",
    "        return df1\n",
    "    if len(p_sts) > 2:\n",
    "        all_dfs = [df_tourney]\n",
    "        for p_st in p_sts[2:]:\n",
    "            df_tmp = csv_2_df(t_yr, t_id, p_st)\n",
    "            all_dfs.append(df_tmp)\n",
    "        df_tourney = pd.concat(all_dfs)\n",
    "    \n",
    "    ndx_sort2 = sorted(df_tourney.index,key=lambda x: re.split(r'\\W+', x[0])[-1])\n",
    "    df_tourney = df_tourney.reindex(ndx_sort2)\n",
    "    return df_tourney"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/plain": "                          2020\n                           pga\nplayer        stat            \nByeong Hun An drv       286.60\n              gir        62.50\n              scramble   62.96\nAbraham Ancer drv       295.60\n              gir        63.89\n...                        ...\nGary Woodland gir        65.28\n              scramble   64.00\nTiger Woods   drv       304.00\n              gir        62.50\n              scramble   59.26\n\n[237 rows x 1 columns]",
      "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 tr th {\n        text-align: left;\n    }\n\n    .dataframe thead tr:last-of-type th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr>\n      <th></th>\n      <th></th>\n      <th>2020</th>\n    </tr>\n    <tr>\n      <th></th>\n      <th></th>\n      <th>pga</th>\n    </tr>\n    <tr>\n      <th>player</th>\n      <th>stat</th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th rowspan=\"3\" valign=\"top\">Byeong Hun An</th>\n      <th>drv</th>\n      <td>286.60</td>\n    </tr>\n    <tr>\n      <th>gir</th>\n      <td>62.50</td>\n    </tr>\n    <tr>\n      <th>scramble</th>\n      <td>62.96</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Abraham Ancer</th>\n      <th>drv</th>\n      <td>295.60</td>\n    </tr>\n    <tr>\n      <th>gir</th>\n      <td>63.89</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <th>...</th>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Gary Woodland</th>\n      <th>gir</th>\n      <td>65.28</td>\n    </tr>\n    <tr>\n      <th>scramble</th>\n      <td>64.00</td>\n    </tr>\n    <tr>\n      <th rowspan=\"3\" valign=\"top\">Tiger Woods</th>\n      <th>drv</th>\n      <td>304.00</td>\n    </tr>\n    <tr>\n      <th>gir</th>\n      <td>62.50</td>\n    </tr>\n    <tr>\n      <th>scramble</th>\n      <td>59.26</td>\n    </tr>\n  </tbody>\n</table>\n<p>237 rows × 1 columns</p>\n</div>"
     },
     "metadata": {}
    }
   ],
   "source": [
    "# test time\n",
    "p_sts = ['drv', 'gir', 'scramble']\n",
    "pga_2020 = tourney_2_df('2020', 'pga', p_sts)\n",
    "display(pga_2020)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/plain": "                          2020        \n                           pga    rbch\nplayer        stat                    \nByeong Hun An drv       286.60     NaN\n              gir        62.50     NaN\n              scramble   62.96     NaN\nAbraham Ancer drv       295.60  278.10\n              gir        63.89   90.28\n...                        ...     ...\nGary Woodland gir        65.28   73.61\n              scramble   64.00   42.11\nTiger Woods   drv       304.00     NaN\n              gir        62.50     NaN\n              scramble   59.26     NaN\n\n[351 rows x 2 columns]",
      "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 tr th {\n        text-align: left;\n    }\n\n    .dataframe thead tr:last-of-type th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr>\n      <th></th>\n      <th></th>\n      <th colspan=\"2\" halign=\"left\">2020</th>\n    </tr>\n    <tr>\n      <th></th>\n      <th></th>\n      <th>pga</th>\n      <th>rbch</th>\n    </tr>\n    <tr>\n      <th>player</th>\n      <th>stat</th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th rowspan=\"3\" valign=\"top\">Byeong Hun An</th>\n      <th>drv</th>\n      <td>286.60</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>gir</th>\n      <td>62.50</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>scramble</th>\n      <td>62.96</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Abraham Ancer</th>\n      <th>drv</th>\n      <td>295.60</td>\n      <td>278.10</td>\n    </tr>\n    <tr>\n      <th>gir</th>\n      <td>63.89</td>\n      <td>90.28</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <th>...</th>\n      <td>...</td>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Gary Woodland</th>\n      <th>gir</th>\n      <td>65.28</td>\n      <td>73.61</td>\n    </tr>\n    <tr>\n      <th>scramble</th>\n      <td>64.00</td>\n      <td>42.11</td>\n    </tr>\n    <tr>\n      <th rowspan=\"3\" valign=\"top\">Tiger Woods</th>\n      <th>drv</th>\n      <td>304.00</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>gir</th>\n      <td>62.50</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>scramble</th>\n      <td>59.26</td>\n      <td>NaN</td>\n    </tr>\n  </tbody>\n</table>\n<p>351 rows × 2 columns</p>\n</div>"
     },
     "metadata": {}
    }
   ],
   "source": [
    "# that seemed to work, so let's do a second tourney and merge\n",
    "df_2020 = year_2_df('2020', ['pga', 'rbch'], ['drv', 'gir', 'scramble'])\n",
    "display(df_2020)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/plain": "                           2021        \n                            pga    rbch\nplayer         stat                    \nByeong Hun An  drv       306.50     NaN\n               gir        48.61     NaN\n               scramble   67.57     NaN\nAbraham Ancer  drv       301.80  299.90\n               gir        61.11   70.83\n...                         ...     ...\nGary Woodland  gir        58.33     NaN\n               scramble   60.00     NaN\nWill Zalatoris drv       306.50  312.00\n               gir        61.11   58.33\n               scramble   64.29   56.67\n\n[339 rows x 2 columns]",
      "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 tr th {\n        text-align: left;\n    }\n\n    .dataframe thead tr:last-of-type th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr>\n      <th></th>\n      <th></th>\n      <th colspan=\"2\" halign=\"left\">2021</th>\n    </tr>\n    <tr>\n      <th></th>\n      <th></th>\n      <th>pga</th>\n      <th>rbch</th>\n    </tr>\n    <tr>\n      <th>player</th>\n      <th>stat</th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th rowspan=\"3\" valign=\"top\">Byeong Hun An</th>\n      <th>drv</th>\n      <td>306.50</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>gir</th>\n      <td>48.61</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>scramble</th>\n      <td>67.57</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Abraham Ancer</th>\n      <th>drv</th>\n      <td>301.80</td>\n      <td>299.90</td>\n    </tr>\n    <tr>\n      <th>gir</th>\n      <td>61.11</td>\n      <td>70.83</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <th>...</th>\n      <td>...</td>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Gary Woodland</th>\n      <th>gir</th>\n      <td>58.33</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>scramble</th>\n      <td>60.00</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th rowspan=\"3\" valign=\"top\">Will Zalatoris</th>\n      <th>drv</th>\n      <td>306.50</td>\n      <td>312.00</td>\n    </tr>\n    <tr>\n      <th>gir</th>\n      <td>61.11</td>\n      <td>58.33</td>\n    </tr>\n    <tr>\n      <th>scramble</th>\n      <td>64.29</td>\n      <td>56.67</td>\n    </tr>\n  </tbody>\n</table>\n<p>339 rows × 2 columns</p>\n</div>"
     },
     "metadata": {}
    },
    {
     "output_type": "display_data",
     "data": {
      "text/plain": "                           2020            2021        \n                            pga    rbch     pga    rbch\nplayer         stat                                    \nByeong Hun An  drv       286.60     NaN  306.50     NaN\n               gir        62.50     NaN   48.61     NaN\n               scramble   62.96     NaN   67.57     NaN\nAbraham Ancer  drv       295.60  278.10  301.80  299.90\n               gir        63.89   90.28   61.11   70.83\n...                         ...     ...     ...     ...\nTiger Woods    gir        62.50     NaN     NaN     NaN\n               scramble   59.26     NaN     NaN     NaN\nWill Zalatoris drv          NaN     NaN  306.50  312.00\n               gir          NaN     NaN   61.11   58.33\n               scramble     NaN     NaN   64.29   56.67\n\n[477 rows x 4 columns]",
      "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 tr th {\n        text-align: left;\n    }\n\n    .dataframe thead tr:last-of-type th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr>\n      <th></th>\n      <th></th>\n      <th colspan=\"2\" halign=\"left\">2020</th>\n      <th colspan=\"2\" halign=\"left\">2021</th>\n    </tr>\n    <tr>\n      <th></th>\n      <th></th>\n      <th>pga</th>\n      <th>rbch</th>\n      <th>pga</th>\n      <th>rbch</th>\n    </tr>\n    <tr>\n      <th>player</th>\n      <th>stat</th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th rowspan=\"3\" valign=\"top\">Byeong Hun An</th>\n      <th>drv</th>\n      <td>286.60</td>\n      <td>NaN</td>\n      <td>306.50</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>gir</th>\n      <td>62.50</td>\n      <td>NaN</td>\n      <td>48.61</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>scramble</th>\n      <td>62.96</td>\n      <td>NaN</td>\n      <td>67.57</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Abraham Ancer</th>\n      <th>drv</th>\n      <td>295.60</td>\n      <td>278.10</td>\n      <td>301.80</td>\n      <td>299.90</td>\n    </tr>\n    <tr>\n      <th>gir</th>\n      <td>63.89</td>\n      <td>90.28</td>\n      <td>61.11</td>\n      <td>70.83</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <th>...</th>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Tiger Woods</th>\n      <th>gir</th>\n      <td>62.50</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>scramble</th>\n      <td>59.26</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th rowspan=\"3\" valign=\"top\">Will Zalatoris</th>\n      <th>drv</th>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>306.50</td>\n      <td>312.00</td>\n    </tr>\n    <tr>\n      <th>gir</th>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>61.11</td>\n      <td>58.33</td>\n    </tr>\n    <tr>\n      <th>scramble</th>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>64.29</td>\n      <td>56.67</td>\n    </tr>\n  </tbody>\n</table>\n<p>477 rows × 4 columns</p>\n</div>"
     },
     "metadata": {}
    }
   ],
   "source": [
    "# okay, and now a 2nd year\n",
    "df_2021 = year_2_df('2021', ['pga', 'rbch'], ['drv', 'gir', 'scramble'])\n",
    "display(df_2021)\n",
    "df_comb = pd.merge(df_2020, df_2021, how='outer', on=['player', 'stat'])\n",
    "ndx_sort2 = sorted(df_comb.index,key=lambda x: re.split(r'\\W+', x[0])[-1])\n",
    "df_comb = df_comb.reindex(ndx_sort2)\n",
    "display(df_comb)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "# now let's add a 3rd tournament\n",
    "# and of course we need to redefine year_2_df\n",
    "def year_2_df(t_yr, t_ids, p_sts):\n",
    "    df1 = tourney_2_df(t_yr, t_ids[0], p_sts)\n",
    "    if len(t_ids) == 1:\n",
    "        return df1\n",
    "    df2 = tourney_2_df(t_yr, t_ids[1], p_sts)\n",
    "    df_comb = pd.merge(df1, df2, how='outer', on=['player', 'stat'])\n",
    "    if len(t_ids) > 2:\n",
    "        for t_id in t_ids[2:]:\n",
    "            df_tmp = tourney_2_df(t_yr, t_id, p_sts)\n",
    "            df_comb = pd.merge(df_comb, df_tmp, how='outer', on=['player', 'stat'])\n",
    "\n",
    "    ndx_sort2 = sorted(df_comb.index,key=lambda x: re.split(r'\\W+', x[0])[-1])\n",
    "    df_comb = df_comb.reindex(ndx_sort2)\n",
    "\n",
    "    return df_comb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/plain": "                          2020                \n                           api     pga    rbch\nplayer        stat                            \nByeong Hun An drv       291.80  286.60     NaN\n              gir        66.67   62.50     NaN\n              scramble   54.17   62.96     NaN\nAbraham Ancer drv       281.90  295.60  278.10\n              gir        48.61   63.89   90.28\n...                        ...     ...     ...\nTiger Woods   gir          NaN   62.50     NaN\n              scramble     NaN   59.26     NaN\nXinjun Zhang  drv       288.80     NaN     NaN\n              gir        50.00     NaN     NaN\n              scramble   52.78     NaN     NaN\n\n[441 rows x 3 columns]",
      "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 tr th {\n        text-align: left;\n    }\n\n    .dataframe thead tr:last-of-type th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr>\n      <th></th>\n      <th></th>\n      <th colspan=\"3\" halign=\"left\">2020</th>\n    </tr>\n    <tr>\n      <th></th>\n      <th></th>\n      <th>api</th>\n      <th>pga</th>\n      <th>rbch</th>\n    </tr>\n    <tr>\n      <th>player</th>\n      <th>stat</th>\n      <th></th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th rowspan=\"3\" valign=\"top\">Byeong Hun An</th>\n      <th>drv</th>\n      <td>291.80</td>\n      <td>286.60</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>gir</th>\n      <td>66.67</td>\n      <td>62.50</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>scramble</th>\n      <td>54.17</td>\n      <td>62.96</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Abraham Ancer</th>\n      <th>drv</th>\n      <td>281.90</td>\n      <td>295.60</td>\n      <td>278.10</td>\n    </tr>\n    <tr>\n      <th>gir</th>\n      <td>48.61</td>\n      <td>63.89</td>\n      <td>90.28</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <th>...</th>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Tiger Woods</th>\n      <th>gir</th>\n      <td>NaN</td>\n      <td>62.50</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>scramble</th>\n      <td>NaN</td>\n      <td>59.26</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th rowspan=\"3\" valign=\"top\">Xinjun Zhang</th>\n      <th>drv</th>\n      <td>288.80</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>gir</th>\n      <td>50.00</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>scramble</th>\n      <td>52.78</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n  </tbody>\n</table>\n<p>441 rows × 3 columns</p>\n</div>"
     },
     "metadata": {}
    }
   ],
   "source": [
    "# testing 1 2 3\n",
    "df_2020 = year_2_df('2020', ['api', 'pga', 'rbch'], ['drv', 'gir', 'scramble'])\n",
    "display(df_2020)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/plain": "                           2020                    2021                \n                            api     pga    rbch     api     pga    rbch\nplayer         stat                                                    \nByeong Hun An  drv       291.80  286.60     NaN  290.60  306.50     NaN\n               gir        66.67   62.50     NaN   56.94   48.61     NaN\n               scramble   54.17   62.96     NaN   64.52   67.57     NaN\nAbraham Ancer  drv       281.90  295.60  278.10     NaN  301.80  299.90\n               gir        48.61   63.89   90.28     NaN   61.11   70.83\n...                         ...     ...     ...     ...     ...     ...\nWill Zalatoris gir          NaN     NaN     NaN   69.44   61.11   58.33\n               scramble     NaN     NaN     NaN   59.09   64.29   56.67\nXinjun Zhang   drv       288.80     NaN     NaN     NaN     NaN     NaN\n               gir        50.00     NaN     NaN     NaN     NaN     NaN\n               scramble   52.78     NaN     NaN     NaN     NaN     NaN\n\n[558 rows x 6 columns]",
      "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 tr th {\n        text-align: left;\n    }\n\n    .dataframe thead tr:last-of-type th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr>\n      <th></th>\n      <th></th>\n      <th colspan=\"3\" halign=\"left\">2020</th>\n      <th colspan=\"3\" halign=\"left\">2021</th>\n    </tr>\n    <tr>\n      <th></th>\n      <th></th>\n      <th>api</th>\n      <th>pga</th>\n      <th>rbch</th>\n      <th>api</th>\n      <th>pga</th>\n      <th>rbch</th>\n    </tr>\n    <tr>\n      <th>player</th>\n      <th>stat</th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th rowspan=\"3\" valign=\"top\">Byeong Hun An</th>\n      <th>drv</th>\n      <td>291.80</td>\n      <td>286.60</td>\n      <td>NaN</td>\n      <td>290.60</td>\n      <td>306.50</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>gir</th>\n      <td>66.67</td>\n      <td>62.50</td>\n      <td>NaN</td>\n      <td>56.94</td>\n      <td>48.61</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>scramble</th>\n      <td>54.17</td>\n      <td>62.96</td>\n      <td>NaN</td>\n      <td>64.52</td>\n      <td>67.57</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Abraham Ancer</th>\n      <th>drv</th>\n      <td>281.90</td>\n      <td>295.60</td>\n      <td>278.10</td>\n      <td>NaN</td>\n      <td>301.80</td>\n      <td>299.90</td>\n    </tr>\n    <tr>\n      <th>gir</th>\n      <td>48.61</td>\n      <td>63.89</td>\n      <td>90.28</td>\n      <td>NaN</td>\n      <td>61.11</td>\n      <td>70.83</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <th>...</th>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Will Zalatoris</th>\n      <th>gir</th>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>69.44</td>\n      <td>61.11</td>\n      <td>58.33</td>\n    </tr>\n    <tr>\n      <th>scramble</th>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>59.09</td>\n      <td>64.29</td>\n      <td>56.67</td>\n    </tr>\n    <tr>\n      <th rowspan=\"3\" valign=\"top\">Xinjun Zhang</th>\n      <th>drv</th>\n      <td>288.80</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>gir</th>\n      <td>50.00</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>scramble</th>\n      <td>52.78</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n  </tbody>\n</table>\n<p>558 rows × 6 columns</p>\n</div>"
     },
     "metadata": {}
    }
   ],
   "source": [
    "# add the second year\n",
    "df_2021 = year_2_df('2021', ['api', 'pga', 'rbch'], ['drv', 'gir', 'scramble'])\n",
    "df_comb = pd.merge(df_2020, df_2021, how='outer', on=['player', 'stat'])\n",
    "ndx_sort2 = sorted(df_comb.index,key=lambda x: re.split(r'\\W+', x[0])[-1])\n",
    "df_comb = df_comb.reindex(ndx_sort2)\n",
    "display(df_comb)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "# decided to test using an additional year, 2019\n",
    "# so need to get some more stats into csv files\n",
    "def stats_2_csv(tyrs, tids, psts):\n",
    "    global d_dir, events, stats, st_cols\n",
    "    for tid in tids:\n",
    "        eid = events[tid]\n",
    "        for tyr in tyrs:\n",
    "            for pst in psts:\n",
    "                print(f\"\\n{tid}, {tyr}, {pst} ->\", end='')\n",
    "                stid = stats[pst]\n",
    "                tlnk = f'https://www.pgatour.com/content/pgatour/stats/stat.{stid}.y{tyr}.eon.{eid}.html'\n",
    "                f_out = get_csv_nm(tyr, tid, pst)\n",
    "                if Path(f_out).is_file():\n",
    "                    print(\" already exists, not downloaded again\")\n",
    "                else:\n",
    "                    tmp_stats = pd.read_html(tlnk)\n",
    "                    if len(tmp_stats) <= 1:\n",
    "                        print(\" not found on site\")\n",
    "                        break\n",
    "                    df_stats = tmp_stats[1][st_cols[pst]]\n",
    "                    df_stats.to_csv(f_out, index=False)\n",
    "                    print(\" downloaded and saved to CSV\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "\napi, 2019, drv -> already exists, not downloaded again\n\napi, 2019, gir -> already exists, not downloaded again\n\napi, 2019, scramble -> already exists, not downloaded again\n\npga, 2019, drv -> already exists, not downloaded again\n\npga, 2019, gir -> already exists, not downloaded again\n\npga, 2019, scramble -> already exists, not downloaded again\n\nrbch, 2019, drv -> already exists, not downloaded again\n\nrbch, 2019, gir -> already exists, not downloaded again\n\nrbch, 2019, scramble -> already exists, not downloaded again\n"
     ]
    }
   ],
   "source": [
    "p_sts = ['drv', 'gir', 'scramble']\n",
    "t_ids = ['api', 'pga', 'rbch']\n",
    "t_yrs = ['2019']\n",
    "stats_2_csv(t_yrs, t_ids, p_sts)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "# now a new function to build the final df by combining the ones for each year\n",
    "def golf_stats_2_df(tyrs, tids, psts):\n",
    "    if len(tyrs) < 1:\n",
    "        return None\n",
    "    else:    \n",
    "        df1 = year_2_df(tyrs[0], tids, psts)\n",
    "        if len(tyrs) == 1:\n",
    "            return df1\n",
    "        else:\n",
    "            df2 = year_2_df(tyrs[1], tids, psts)\n",
    "            df_comb = pd.merge(df1, df2, how='outer', on=['player', 'stat'])\n",
    "            if len(tyrs) > 2:\n",
    "                for tyr in tyrs[2:]:\n",
    "                    df_tmp = year_2_df(tyr, tids, psts)\n",
    "                    df_comb = pd.merge(df_comb, df_tmp, how='outer', on=['player', 'stat'])\n",
    "\n",
    "        ndx_sort2 = sorted(df_comb.index,key=lambda x: re.split(r'\\W+', x[0])[-1])\n",
    "        df_comb = df_comb.reindex(ndx_sort2)\n",
    "\n",
    "        return df_comb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/plain": "                           2019                 2020                    2021  \\\n                            api     pga rbch     api     pga    rbch     api   \nplayer         stat                                                            \nByeong Hun An  drv       312.90     NaN  NaN  291.80  286.60     NaN  290.60   \n               gir        63.89     NaN  NaN   66.67   62.50     NaN   56.94   \n               scramble   69.23     NaN  NaN   54.17   62.96     NaN   64.52   \nAbraham Ancer  drv          NaN  285.60  NaN  281.90  295.60  278.10     NaN   \n               gir          NaN   56.94  NaN   48.61   63.89   90.28     NaN   \n...                         ...     ...  ...     ...     ...     ...     ...   \nWill Zalatoris gir          NaN     NaN  NaN     NaN     NaN     NaN   69.44   \n               scramble     NaN     NaN  NaN     NaN     NaN     NaN   59.09   \nXinjun Zhang   drv          NaN     NaN  NaN  288.80     NaN     NaN     NaN   \n               gir          NaN     NaN  NaN   50.00     NaN     NaN     NaN   \n               scramble     NaN     NaN  NaN   52.78     NaN     NaN     NaN   \n\n                                         \n                            pga    rbch  \nplayer         stat                      \nByeong Hun An  drv       306.50     NaN  \n               gir        48.61     NaN  \n               scramble   67.57     NaN  \nAbraham Ancer  drv       301.80  299.90  \n               gir        61.11   70.83  \n...                         ...     ...  \nWill Zalatoris gir        61.11   58.33  \n               scramble   64.29   56.67  \nXinjun Zhang   drv          NaN     NaN  \n               gir          NaN     NaN  \n               scramble     NaN     NaN  \n\n[678 rows x 9 columns]",
      "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 tr th {\n        text-align: left;\n    }\n\n    .dataframe thead tr:last-of-type th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr>\n      <th></th>\n      <th></th>\n      <th colspan=\"3\" halign=\"left\">2019</th>\n      <th colspan=\"3\" halign=\"left\">2020</th>\n      <th colspan=\"3\" halign=\"left\">2021</th>\n    </tr>\n    <tr>\n      <th></th>\n      <th></th>\n      <th>api</th>\n      <th>pga</th>\n      <th>rbch</th>\n      <th>api</th>\n      <th>pga</th>\n      <th>rbch</th>\n      <th>api</th>\n      <th>pga</th>\n      <th>rbch</th>\n    </tr>\n    <tr>\n      <th>player</th>\n      <th>stat</th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th rowspan=\"3\" valign=\"top\">Byeong Hun An</th>\n      <th>drv</th>\n      <td>312.90</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>291.80</td>\n      <td>286.60</td>\n      <td>NaN</td>\n      <td>290.60</td>\n      <td>306.50</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>gir</th>\n      <td>63.89</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>66.67</td>\n      <td>62.50</td>\n      <td>NaN</td>\n      <td>56.94</td>\n      <td>48.61</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>scramble</th>\n      <td>69.23</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>54.17</td>\n      <td>62.96</td>\n      <td>NaN</td>\n      <td>64.52</td>\n      <td>67.57</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Abraham Ancer</th>\n      <th>drv</th>\n      <td>NaN</td>\n      <td>285.60</td>\n      <td>NaN</td>\n      <td>281.90</td>\n      <td>295.60</td>\n      <td>278.10</td>\n      <td>NaN</td>\n      <td>301.80</td>\n      <td>299.90</td>\n    </tr>\n    <tr>\n      <th>gir</th>\n      <td>NaN</td>\n      <td>56.94</td>\n      <td>NaN</td>\n      <td>48.61</td>\n      <td>63.89</td>\n      <td>90.28</td>\n      <td>NaN</td>\n      <td>61.11</td>\n      <td>70.83</td>\n    </tr>\n    <tr>\n      <th>...</th>\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    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Will Zalatoris</th>\n      <th>gir</th>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>69.44</td>\n      <td>61.11</td>\n      <td>58.33</td>\n    </tr>\n    <tr>\n      <th>scramble</th>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>59.09</td>\n      <td>64.29</td>\n      <td>56.67</td>\n    </tr>\n    <tr>\n      <th rowspan=\"3\" valign=\"top\">Xinjun Zhang</th>\n      <th>drv</th>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>288.80</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>gir</th>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>50.00</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>scramble</th>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>52.78</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n  </tbody>\n</table>\n<p>678 rows × 9 columns</p>\n</div>"
     },
     "metadata": {}
    }
   ],
   "source": [
    "# test time\n",
    "# manually edidted the appropriate csv files and removed the ', Jr.' for the Ted Potter row\n",
    "all_yrs = ['2019', '2020', '2021']\n",
    "golf_stats = golf_stats_2_df(all_yrs, t_ids, p_sts)\n",
    "display(golf_stats)\n",
    "# want to see what the csv looks like\n",
    "golf_csv = f'{d_dir}golf_stats.test.csv'\n",
    "golf_stats.to_csv(golf_csv)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "# all that's left is to figure out how to use this data set in some meaningful fashion"
   ]
  }
 ]
}