{
 "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"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2,
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import re"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "events = {'wmpo': 't003', 'tpc': 'to11', 'rbch': 't012', 'masters': 't014', 'pga': 't033'}\n",
    "stats = {'drv': '101', 'gir': '103', 't2g': '02674'}\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",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "df1: 79, df2: 79\n\n                    AVG.\nPLAYER NAME             \nCameron Champ      321.1\nBryson DeChambeau  318.1\nRory McIlroy       312.5\nSepp Straka        305.8\nTommy Fleetwood    305.5\n\n                      %\nPLAYER NAME            \nMatthew Wolff     77.78\nPaul Casey        76.39\nJason Day         76.39\nLouis Oosthuizen  73.61\nCameron Champ     73.61\n\n<class 'pandas.core.frame.DataFrame'>\n"
     ]
    }
   ],
   "source": [
    "d_dir = \"./data/\"\n",
    "csvs = ['pga_2020_drv_2.test.csv', 'pga_2020_gir_2.test.csv',\n",
    "        'pga_2021_drv_2.test.csv', 'pga_2021_gir_2.test.csv',\n",
    "        'rbch_2020_drv_2.test.csv', 'rbch_2020_gir_2.test.csv',\n",
    "        'rbch_2021_drv_2.test.csv', 'rbch_2021_gir_2.test.csv'\n",
    "]\n",
    "df1 = pd.read_csv(f\"{d_dir}{csvs[0]}\", index_col=['PLAYER NAME'], usecols=['AVG.', 'PLAYER NAME'])\n",
    "df2 = pd.read_csv(f\"{d_dir}{csvs[1]}\", index_col=['PLAYER NAME'], usecols=['%', 'PLAYER NAME'])\n",
    "print(f\"df1: {len(df1)}, df2: {len(df2)}\\n\")\n",
    "print(df1.head())\n",
    "print()\n",
    "print(df2.head())\n",
    "print()\n",
    "print(type(df2))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "                    AVG.   %\nPLAYER NAME                 \nCameron Champ      321.1 NaN\nBryson DeChambeau  318.1 NaN\nRory McIlroy       312.5 NaN\nSepp Straka        305.8 NaN\nTommy Fleetwood    305.5 NaN\n\n                 AVG.      %\nPLAYER NAME                 \nDenny McCarthy    NaN  54.17\nHarris English    NaN  52.78\nBrian Harman      NaN  51.39\nBrandt Snedeker   NaN  51.39\nJ.T. Poston       NaN  50.00\n\n158\n"
     ]
    }
   ],
   "source": [
    "combined = pd.concat([df1, df2])\n",
    "print(combined.head())\n",
    "print()\n",
    "print(combined.tail())\n",
    "print()\n",
    "print(len(combined))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "PLAYER NAME\n",
       "Cameron Champ         True\n",
       "Bryson DeChambeau     True\n",
       "Rory McIlroy          True\n",
       "Sepp Straka           True\n",
       "Tommy Fleetwood       True\n",
       "                     ...  \n",
       "Denny McCarthy       False\n",
       "Harris English       False\n",
       "Brian Harman         False\n",
       "Brandt Snedeker      False\n",
       "J.T. Poston          False\n",
       "Name: AVG., Length: 158, dtype: bool"
      ]
     },
     "metadata": {},
     "execution_count": 5
    }
   ],
   "source": [
    "pd.notna(combined[\"AVG.\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "                    AVG.      %\nPLAYER NAME                    \nCameron Champ      321.1  73.61\nBryson DeChambeau  318.1  66.67\nRory McIlroy       312.5  65.28\nSepp Straka        305.8  58.33\nTommy Fleetwood    305.5  63.89\n\n                   AVG.      %\nPLAYER NAME                   \nCharl Schwartzel  276.0  59.72\nChez Reavie       274.5  55.56\nBrendon Todd      272.3  62.50\nPatrick Reed      271.6  68.06\nMark Hubbard      268.8  61.11\n"
     ]
    }
   ],
   "source": [
    "combined = pd.concat([df1, df2], axis=1)\n",
    "print(combined.head())\n",
    "print()\n",
    "print(combined.tail())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "\n",
      "df1a ->\n"
     ]
    },
    {
     "output_type": "display_data",
     "data": {
      "text/plain": "                         2020\n                          pga\nPLAYER NAME                  \nCameron Champ     AVG.  321.1\nBryson DeChambeau AVG.  318.1\nRory McIlroy      AVG.  312.5\nSepp Straka       AVG.  305.8\nTommy Fleetwood   AVG.  305.5\n...                       ...\nCharl Schwartzel  AVG.  276.0\nChez Reavie       AVG.  274.5\nBrendon Todd      AVG.  272.3\nPatrick Reed      AVG.  271.6\nMark Hubbard      AVG.  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 NAME</th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>Cameron Champ</th>\n      <th>AVG.</th>\n      <td>321.1</td>\n    </tr>\n    <tr>\n      <th>Bryson DeChambeau</th>\n      <th>AVG.</th>\n      <td>318.1</td>\n    </tr>\n    <tr>\n      <th>Rory McIlroy</th>\n      <th>AVG.</th>\n      <td>312.5</td>\n    </tr>\n    <tr>\n      <th>Sepp Straka</th>\n      <th>AVG.</th>\n      <td>305.8</td>\n    </tr>\n    <tr>\n      <th>Tommy Fleetwood</th>\n      <th>AVG.</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>AVG.</th>\n      <td>276.0</td>\n    </tr>\n    <tr>\n      <th>Chez Reavie</th>\n      <th>AVG.</th>\n      <td>274.5</td>\n    </tr>\n    <tr>\n      <th>Brendon Todd</th>\n      <th>AVG.</th>\n      <td>272.3</td>\n    </tr>\n    <tr>\n      <th>Patrick Reed</th>\n      <th>AVG.</th>\n      <td>271.6</td>\n    </tr>\n    <tr>\n      <th>Mark Hubbard</th>\n      <th>AVG.</th>\n      <td>268.8</td>\n    </tr>\n  </tbody>\n</table>\n<p>79 rows × 1 columns</p>\n</div>"
     },
     "metadata": {}
    },
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "\ndf_tst.head(10) ->\n"
     ]
    },
    {
     "output_type": "display_data",
     "data": {
      "text/plain": "                         2020\n                          pga\nPLAYER NAME                  \nCameron Champ     AVG.  321.1\nBryson DeChambeau AVG.  318.1\nRory McIlroy      AVG.  312.5\nSepp Straka       AVG.  305.8\nTommy Fleetwood   AVG.  305.5\nTony Finau        AVG.  305.3\nDustin Johnson    AVG.  305.0\nVictor Perez      AVG.  304.4\nHaotong Li        AVG.  304.1\nTiger Woods       AVG.  304.0",
      "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 NAME</th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>Cameron Champ</th>\n      <th>AVG.</th>\n      <td>321.1</td>\n    </tr>\n    <tr>\n      <th>Bryson DeChambeau</th>\n      <th>AVG.</th>\n      <td>318.1</td>\n    </tr>\n    <tr>\n      <th>Rory McIlroy</th>\n      <th>AVG.</th>\n      <td>312.5</td>\n    </tr>\n    <tr>\n      <th>Sepp Straka</th>\n      <th>AVG.</th>\n      <td>305.8</td>\n    </tr>\n    <tr>\n      <th>Tommy Fleetwood</th>\n      <th>AVG.</th>\n      <td>305.5</td>\n    </tr>\n    <tr>\n      <th>Tony Finau</th>\n      <th>AVG.</th>\n      <td>305.3</td>\n    </tr>\n    <tr>\n      <th>Dustin Johnson</th>\n      <th>AVG.</th>\n      <td>305.0</td>\n    </tr>\n    <tr>\n      <th>Victor Perez</th>\n      <th>AVG.</th>\n      <td>304.4</td>\n    </tr>\n    <tr>\n      <th>Haotong Li</th>\n      <th>AVG.</th>\n      <td>304.1</td>\n    </tr>\n    <tr>\n      <th>Tiger Woods</th>\n      <th>AVG.</th>\n      <td>304.0</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {}
    },
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "\ndf_tst, after sorting index column ->\n"
     ]
    },
    {
     "output_type": "display_data",
     "data": {
      "text/plain": "                          2020\n                           pga\nPLAYER NAME                   \nAbraham Ancer     %      63.89\n                  AVG.  295.60\nAdam Hadwin       %      54.17\n                  AVG.  282.90\nAdam Long         %      59.72\n...                        ...\nViktor Hovland    AVG.  281.90\nWebb Simpson      %      62.50\n                  AVG.  281.10\nXander Schauffele %      68.06\n                  AVG.  302.30\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 NAME</th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Abraham Ancer</th>\n      <th>%</th>\n      <td>63.89</td>\n    </tr>\n    <tr>\n      <th>AVG.</th>\n      <td>295.60</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Adam Hadwin</th>\n      <th>%</th>\n      <td>54.17</td>\n    </tr>\n    <tr>\n      <th>AVG.</th>\n      <td>282.90</td>\n    </tr>\n    <tr>\n      <th>Adam Long</th>\n      <th>%</th>\n      <td>59.72</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <th>...</th>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>Viktor Hovland</th>\n      <th>AVG.</th>\n      <td>281.90</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Webb Simpson</th>\n      <th>%</th>\n      <td>62.50</td>\n    </tr>\n    <tr>\n      <th>AVG.</th>\n      <td>281.10</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Xander Schauffele</th>\n      <th>%</th>\n      <td>68.06</td>\n    </tr>\n    <tr>\n      <th>AVG.</th>\n      <td>302.30</td>\n    </tr>\n  </tbody>\n</table>\n<p>158 rows × 1 columns</p>\n</div>"
     },
     "metadata": {}
    },
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "\ndf_tst.loc['Bryson DeChambeau', :] ->\n        2020\n         pga\n%      66.67\nAVG.  318.10\n"
     ]
    }
   ],
   "source": [
    "s1 = df1.stack()\n",
    "cols1 = pd.MultiIndex.from_tuples([('2020', 'pga')])\n",
    "#print(s1.index)\n",
    "#print(s1.columns)\n",
    "df1a = pd.DataFrame(s1, columns=cols1)\n",
    "print('\\ndf1a ->')\n",
    "display(df1a)\n",
    "s2 = df2.stack()\n",
    "df2a = pd.DataFrame(s2, columns=cols1)\n",
    "df_tst = pd.concat([df1a, df2a])\n",
    "print('\\ndf_tst.head(10) ->')\n",
    "display(df_tst.head(10))\n",
    "df_tst = df_tst.sort_index()\n",
    "#print(f\"\\ntype(df_tst): {type(df_tst)}\\n\")\n",
    "print('\\ndf_tst, after sorting index column ->')\n",
    "display(df_tst)\n",
    "print(\"\\ndf_tst.loc['Bryson DeChambeau', :] ->\")\n",
    "print(df_tst.loc['Bryson DeChambeau', :])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "\n",
      "df1 with columns renamed ->\n",
      "\n"
     ]
    },
    {
     "output_type": "display_data",
     "data": {
      "text/plain": "                     drv\nplayer                  \nCameron Champ      321.1\nBryson DeChambeau  318.1\nRory McIlroy       312.5\nSepp Straka        305.8\nTommy Fleetwood    305.5",
      "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>drv</th>\n    </tr>\n    <tr>\n      <th>player</th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>Cameron Champ</th>\n      <td>321.1</td>\n    </tr>\n    <tr>\n      <th>Bryson DeChambeau</th>\n      <td>318.1</td>\n    </tr>\n    <tr>\n      <th>Rory McIlroy</th>\n      <td>312.5</td>\n    </tr>\n    <tr>\n      <th>Sepp Straka</th>\n      <td>305.8</td>\n    </tr>\n    <tr>\n      <th>Tommy Fleetwood</th>\n      <td>305.5</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {}
    },
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "\ndf1a (multi-indexed) ->\n\n"
     ]
    },
    {
     "output_type": "display_data",
     "data": {
      "text/plain": "                        2020\n                         pga\nplayer                      \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></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": "stream",
     "name": "stdout",
     "text": [
      "\ndf_tst (concat of df1a / df2a, sorted?) ->\n\n"
     ]
    },
    {
     "output_type": "display_data",
     "data": {
      "text/plain": "                         2020\n                          pga\nplayer                       \nAbraham Ancer     drv  295.60\n                  gir   63.89\nAdam Hadwin       drv  282.90\n                  gir   54.17\nAdam Long         drv  285.40\n...                       ...\nViktor Hovland    gir   65.28\nWebb Simpson      drv  281.10\n                  gir   62.50\nXander Schauffele drv  302.30\n                  gir   68.06\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></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\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 rowspan=\"2\" valign=\"top\">Adam Hadwin</th>\n      <th>drv</th>\n      <td>282.90</td>\n    </tr>\n    <tr>\n      <th>gir</th>\n      <td>54.17</td>\n    </tr>\n    <tr>\n      <th>Adam Long</th>\n      <th>drv</th>\n      <td>285.40</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <th>...</th>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>Viktor Hovland</th>\n      <th>gir</th>\n      <td>65.28</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Webb Simpson</th>\n      <th>drv</th>\n      <td>281.10</td>\n    </tr>\n    <tr>\n      <th>gir</th>\n      <td>62.50</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Xander Schauffele</th>\n      <th>drv</th>\n      <td>302.30</td>\n    </tr>\n    <tr>\n      <th>gir</th>\n      <td>68.06</td>\n    </tr>\n  </tbody>\n</table>\n<p>158 rows × 1 columns</p>\n</div>"
     },
     "metadata": {}
    }
   ],
   "source": [
    "df1.rename(columns={'AVG.': 'drv', '%': 'gir'}, inplace=True)\n",
    "df1.rename_axis(index={'PLAYER NAME': 'player'}, inplace=True)\n",
    "print('\\ndf1 with columns renamed ->\\n')\n",
    "display(df1.head())\n",
    "#cols = pd.MultiIndex.from_tuples([(\"lang\",), (\"2018\",\"height\"), (\"2018\",\"weight\"), (\"2019\",\"height\"), (\"2019\",\"weight\")])\n",
    "cols1 = pd.MultiIndex.from_tuples([('2020', 'pga')])\n",
    "s1 = df1.stack()\n",
    "#print(s1.index)\n",
    "#print(s1.columns)\n",
    "df1a = pd.DataFrame(s1, columns=cols1)\n",
    "print('\\ndf1a (multi-indexed) ->\\n')\n",
    "display(df1a)\n",
    "df2.rename(columns={'AVG.': 'drv', '%': 'gir'}, inplace=True)\n",
    "df2.rename_axis(index={'PLAYER NAME': 'player'}, inplace=True)\n",
    "s2 = df2.stack()\n",
    "df2a = pd.DataFrame(s2, columns=cols1)\n",
    "df_tst = pd.concat([df1a, df2a])\n",
    "df_tst = df_tst.sort_index()\n",
    "#print(f\"\\ntype(df_tst): {type(df_tst)}\\n\")\n",
    "print('\\ndf_tst (concat of df1a / df2a, sorted?) ->\\n')\n",
    "display(df_tst)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "\ndf_tst (added index column name) ->\n\n"
     ]
    },
    {
     "output_type": "display_data",
     "data": {
      "text/plain": "                          2020\n                           pga\nplayer            stat        \nAbraham Ancer     drv   295.60\n                  gir    63.89\nAdam Hadwin       drv   282.90\n                  gir    54.17\nAdam Long         drv   285.40\n...                        ...\nViktor Hovland    gir    65.28\nWebb Simpson      drv   281.10\n                  gir    62.50\nXander Schauffele drv   302.30\n                  gir    68.06\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\">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 rowspan=\"2\" valign=\"top\">Adam Hadwin</th>\n      <th>drv</th>\n      <td>282.90</td>\n    </tr>\n    <tr>\n      <th>gir</th>\n      <td>54.17</td>\n    </tr>\n    <tr>\n      <th>Adam Long</th>\n      <th>drv</th>\n      <td>285.40</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <th>...</th>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>Viktor Hovland</th>\n      <th>gir</th>\n      <td>65.28</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Webb Simpson</th>\n      <th>drv</th>\n      <td>281.10</td>\n    </tr>\n    <tr>\n      <th>gir</th>\n      <td>62.50</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Xander Schauffele</th>\n      <th>drv</th>\n      <td>302.30</td>\n    </tr>\n    <tr>\n      <th>gir</th>\n      <td>68.06</td>\n    </tr>\n  </tbody>\n</table>\n<p>158 rows × 1 columns</p>\n</div>"
     },
     "metadata": {}
    },
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "\ndf_tst.loc['Bryson DeChambeau', :] ->\n\n"
     ]
    },
    {
     "output_type": "display_data",
     "data": {
      "text/plain": "        2020\n         pga\nstat        \ndrv   318.10\ngir    66.67",
      "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>2020</th>\n    </tr>\n    <tr>\n      <th></th>\n      <th>pga</th>\n    </tr>\n    <tr>\n      <th>stat</th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>drv</th>\n      <td>318.10</td>\n    </tr>\n    <tr>\n      <th>gir</th>\n      <td>66.67</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {}
    }
   ],
   "source": [
    "# Add label for stat column of multi-index\n",
    "df_tst.rename_axis(['player', 'stat'], inplace=True)\n",
    "print('\\ndf_tst (added index column name) ->\\n')\n",
    "display(df_tst)\n",
    "print(\"\\ndf_tst.loc['Bryson DeChambeau', :] ->\\n\")\n",
    "display(df_tst.loc['Bryson DeChambeau', :])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "\ndf_tst (concat of df1a / df2a, index sorted on player last name) ->\n\n"
     ]
    },
    {
     "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": [
    "#print(df_tst.index)\n",
    "#print(re.split(r'\\W+', df_tst.index[0][0])[-1])\n",
    "ndx_sort = sorted(df_tst.index,key=lambda x: re.split(r'\\W+', x[0])[-1])\n",
    "#print(ndx_sort)\n",
    "#df_tst = df_tst.sort_index()\n",
    "df_tst = df_tst.reindex(ndx_sort)\n",
    "#print(f\"\\ntype(df_tst): {type(df_tst)}\\n\")\n",
    "print('\\ndf_tst (concat of df1a / df2a, index sorted on player last name) ->\\n')\n",
    "display(df_tst)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/plain": "                      2020\n                      rbch\nplayer        stat        \nAbraham Ancer drv   278.10\n              gir    90.28\nRyan Armour   drv   275.00\n              gir    56.94\nDaniel Berger drv   273.60\n...                    ...\nMatt Wallace  gir    65.28\nBubba Watson  drv   277.50\n              gir    65.28\nGary Woodland drv   287.00\n              gir    73.61\n\n[150 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>rbch</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\">Abraham Ancer</th>\n      <th>drv</th>\n      <td>278.10</td>\n    </tr>\n    <tr>\n      <th>gir</th>\n      <td>90.28</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Ryan Armour</th>\n      <th>drv</th>\n      <td>275.00</td>\n    </tr>\n    <tr>\n      <th>gir</th>\n      <td>56.94</td>\n    </tr>\n    <tr>\n      <th>Daniel Berger</th>\n      <th>drv</th>\n      <td>273.60</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <th>...</th>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>Matt Wallace</th>\n      <th>gir</th>\n      <td>65.28</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Bubba Watson</th>\n      <th>drv</th>\n      <td>277.50</td>\n    </tr>\n    <tr>\n      <th>gir</th>\n      <td>65.28</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Gary Woodland</th>\n      <th>drv</th>\n      <td>287.00</td>\n    </tr>\n    <tr>\n      <th>gir</th>\n      <td>73.61</td>\n    </tr>\n  </tbody>\n</table>\n<p>150 rows × 1 columns</p>\n</div>"
     },
     "metadata": {}
    }
   ],
   "source": [
    "# change order of csv files to reflect order I want in dataframe\n",
    "csvs = ['pga_2020_drv_2.test.csv', 'pga_2020_gir_2.test.csv',\n",
    "        'rbch_2020_drv_2.test.csv', 'rbch_2020_gir_2.test.csv',\n",
    "        'pga_2021_drv_2.test.csv', 'pga_2021_gir_2.test.csv',\n",
    "        'rbch_2021_drv_2.test.csv', 'rbch_2021_gir_2.test.csv'\n",
    "]\n",
    "df3 = pd.read_csv(f\"{d_dir}{csvs[2]}\", index_col=['PLAYER NAME'], usecols=['AVG.', 'PLAYER NAME'])\n",
    "df4 = pd.read_csv(f\"{d_dir}{csvs[3]}\", index_col=['PLAYER NAME'], usecols=['%', 'PLAYER NAME'])\n",
    "cols1 = pd.MultiIndex.from_tuples([('2020', 'rbch')])\n",
    "\n",
    "df3.rename(columns={'AVG.': 'drv', '%': 'gir'}, inplace=True)\n",
    "df3.rename_axis(index={'PLAYER NAME': 'player'}, inplace=True)\n",
    "s3 = df3.stack()\n",
    "df3a = pd.DataFrame(s3, columns=cols1)\n",
    "\n",
    "df4.rename(columns={'AVG.': 'drv', '%': 'gir'}, inplace=True)\n",
    "df4.rename_axis(index={'PLAYER NAME': 'player'}, inplace=True)\n",
    "s4 = df4.stack()\n",
    "df4a = pd.DataFrame(s4, columns=cols1)\n",
    "\n",
    "df_tst2 = pd.concat([df3a, df4a])\n",
    "df_tst2.rename_axis(['player', 'stat'], inplace=True)\n",
    "ndx_sort2 = sorted(df_tst2.index,key=lambda x: re.split(r'\\W+', x[0])[-1])\n",
    "df_tst2 = df_tst2.reindex(ndx_sort2)\n",
    "display(df_tst2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "\ndf_comb.head(10) ->\n\n"
     ]
    },
    {
     "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  NaN\n                gir    63.89  NaN\nDaniel Berger   drv   291.90  NaN\n                gir    65.28  NaN\nPatrick Cantlay drv   295.30  NaN\n                gir    59.72  NaN\nPaul Casey      drv   295.80  NaN\n                gir    76.39  NaN",
      "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>NaN</td>\n    </tr>\n    <tr>\n      <th>gir</th>\n      <td>63.89</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Daniel Berger</th>\n      <th>drv</th>\n      <td>291.90</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>gir</th>\n      <td>65.28</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Patrick Cantlay</th>\n      <th>drv</th>\n      <td>295.30</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>gir</th>\n      <td>59.72</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Paul Casey</th>\n      <th>drv</th>\n      <td>295.80</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>gir</th>\n      <td>76.39</td>\n      <td>NaN</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {}
    },
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "\nlen(df_comb): 308\n\ndf_comb.dropna() ->\n\nEmpty DataFrame\nColumns: [(2020, pga), (2020, rbch)]\nIndex: []\n"
     ]
    }
   ],
   "source": [
    "# now lets concatenate our two stats dfs\n",
    "df_comb = pd.concat([df_tst, df_tst2])\n",
    "print('\\ndf_comb.head(10) ->\\n')\n",
    "display(df_comb.head(10))\n",
    "print(f\"\\nlen(df_comb): {len(df_comb)}\")\n",
    "print('\\ndf_comb.dropna() ->\\n')\n",
    "print(df_comb.dropna())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "\ndf_comb.dropna(subset=[('2020', 'pga')]) ->\n\n"
     ]
    },
    {
     "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  NaN\n              gir    63.89  NaN\nDaniel Berger drv   291.90  NaN\n...                    ...  ...\nMatthew Wolff gir    77.78  NaN\nGary Woodland drv   293.00  NaN\n              gir    65.28  NaN\nTiger Woods   drv   304.00  NaN\n              gir    62.50  NaN\n\n[158 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>NaN</td>\n    </tr>\n    <tr>\n      <th>gir</th>\n      <td>63.89</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>Daniel Berger</th>\n      <th>drv</th>\n      <td>291.90</td>\n      <td>NaN</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>NaN</td>\n    </tr>\n    <tr>\n      <th>gir</th>\n      <td>65.28</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    </tr>\n    <tr>\n      <th>gir</th>\n      <td>62.50</td>\n      <td>NaN</td>\n    </tr>\n  </tbody>\n</table>\n<p>158 rows × 2 columns</p>\n</div>"
     },
     "metadata": {}
    },
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "\ndf_comb.dropna(subset=[('2020', 'rbch')]) ->\n\n"
     ]
    },
    {
     "output_type": "display_data",
     "data": {
      "text/plain": "                   2020        \n                    pga    rbch\nplayer        stat             \nAbraham Ancer drv   NaN  278.10\n              gir   NaN   90.28\nRyan Armour   drv   NaN  275.00\n              gir   NaN   56.94\nDaniel Berger drv   NaN  273.60\n...                 ...     ...\nMatt Wallace  gir   NaN   65.28\nBubba Watson  drv   NaN  277.50\n              gir   NaN   65.28\nGary Woodland drv   NaN  287.00\n              gir   NaN   73.61\n\n[150 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\">Abraham Ancer</th>\n      <th>drv</th>\n      <td>NaN</td>\n      <td>278.10</td>\n    </tr>\n    <tr>\n      <th>gir</th>\n      <td>NaN</td>\n      <td>90.28</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Ryan Armour</th>\n      <th>drv</th>\n      <td>NaN</td>\n      <td>275.00</td>\n    </tr>\n    <tr>\n      <th>gir</th>\n      <td>NaN</td>\n      <td>56.94</td>\n    </tr>\n    <tr>\n      <th>Daniel Berger</th>\n      <th>drv</th>\n      <td>NaN</td>\n      <td>273.60</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <th>...</th>\n      <td>...</td>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>Matt Wallace</th>\n      <th>gir</th>\n      <td>NaN</td>\n      <td>65.28</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Bubba Watson</th>\n      <th>drv</th>\n      <td>NaN</td>\n      <td>277.50</td>\n    </tr>\n    <tr>\n      <th>gir</th>\n      <td>NaN</td>\n      <td>65.28</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Gary Woodland</th>\n      <th>drv</th>\n      <td>NaN</td>\n      <td>287.00</td>\n    </tr>\n    <tr>\n      <th>gir</th>\n      <td>NaN</td>\n      <td>73.61</td>\n    </tr>\n  </tbody>\n</table>\n<p>150 rows × 2 columns</p>\n</div>"
     },
     "metadata": {}
    },
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "\ndf_comb.loc['Abraham Ancer'] ->\n\n        2020        \n         pga    rbch\nstat                \ndrv   295.60     NaN\ngir    63.89     NaN\ndrv      NaN  278.10\ngir      NaN   90.28\n"
     ]
    }
   ],
   "source": [
    "print(\"\\ndf_comb.dropna(subset=[('2020', 'pga')]) ->\\n\")\n",
    "display(df_comb.dropna(how='any', subset=[('2020', 'pga')]))\n",
    "\n",
    "print(\"\\ndf_comb.dropna(subset=[('2020', 'rbch')]) ->\\n\")\n",
    "cols= [(c0, c1) for (c0, c1) in df_comb.columns if c1 in ['rbch']]\n",
    "display(df_comb.dropna(how='any', subset=cols))\n",
    "# what's going on\n",
    "print(\"\\ndf_comb.loc['Abraham Ancer'] ->\\n\")\n",
    "print(df_comb.loc['Abraham Ancer'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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": [
    "df_comb = pd.merge(df_tst, df_tst2, how='outer', on=['player', 'stat'])\n",
    "ndx_sort3 = sorted(df_comb.index, key=lambda x: re.split(r'\\W+', x[0])[-1])\n",
    "df_comb = df_comb.reindex(ndx_sort3)\n",
    "\n",
    "display(df_comb)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/plain": "                      2020        \n                       pga    rbch\nplayer        stat                \nAbraham Ancer drv   295.60  278.10\n              gir    63.89   90.28\nDaniel Berger drv   291.90  273.60\n              gir    65.28   69.44\nJoel Dahmen   drv   289.50  270.30\n...                    ...     ...\nMatt Wallace  gir    65.28   65.28\nBubba Watson  drv   290.10  277.50\n              gir    70.83   65.28\nGary Woodland drv   293.00  287.00\n              gir    65.28   73.61\n\n[74 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\">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 rowspan=\"2\" valign=\"top\">Daniel Berger</th>\n      <th>drv</th>\n      <td>291.90</td>\n      <td>273.60</td>\n    </tr>\n    <tr>\n      <th>gir</th>\n      <td>65.28</td>\n      <td>69.44</td>\n    </tr>\n    <tr>\n      <th>Joel Dahmen</th>\n      <th>drv</th>\n      <td>289.50</td>\n      <td>270.30</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <th>...</th>\n      <td>...</td>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>Matt Wallace</th>\n      <th>gir</th>\n      <td>65.28</td>\n      <td>65.28</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">Bubba Watson</th>\n      <th>drv</th>\n      <td>290.10</td>\n      <td>277.50</td>\n    </tr>\n    <tr>\n      <th>gir</th>\n      <td>70.83</td>\n      <td>65.28</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  </tbody>\n</table>\n<p>74 rows × 2 columns</p>\n</div>"
     },
     "metadata": {}
    }
   ],
   "source": [
    "display(df_comb.dropna())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ]
}