module-4-review-scratchpad.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "560e303a-c884-4520-9020-4093973ce5f4",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "45ed2876-6767-4bd5-8cfa-387df4997f18",
   "metadata": {},
   "outputs": [],
   "source": [
    "ncaa = pd.read_csv(\"data/ncaa-team-data-cleanCoachNames.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "8b472a46-f34c-4d2a-bb2f-ab739723a721",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>school</th>\n",
       "      <th>conf</th>\n",
       "      <th>rk</th>\n",
       "      <th>w</th>\n",
       "      <th>l</th>\n",
       "      <th>srs</th>\n",
       "      <th>sos</th>\n",
       "      <th>pts_for</th>\n",
       "      <th>pts_vs</th>\n",
       "      <th>pts_total</th>\n",
       "      <th>ap_pre</th>\n",
       "      <th>ap_high</th>\n",
       "      <th>ap_final</th>\n",
       "      <th>pts_diff</th>\n",
       "      <th>ncaa_result</th>\n",
       "      <th>ncaa_numeric</th>\n",
       "      <th>season</th>\n",
       "      <th>year</th>\n",
       "      <th>coach</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>9962</th>\n",
       "      <td>9962</td>\n",
       "      <td>louisiana-lafayette</td>\n",
       "      <td>Southland</td>\n",
       "      <td>43</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>30</td>\n",
       "      <td>30</td>\n",
       "      <td>30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>1974-75</td>\n",
       "      <td>1974</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9963</th>\n",
       "      <td>9963</td>\n",
       "      <td>louisiana-lafayette</td>\n",
       "      <td>Southland</td>\n",
       "      <td>44</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>30</td>\n",
       "      <td>30</td>\n",
       "      <td>30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>1973-74</td>\n",
       "      <td>1973</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Unnamed: 0               school       conf  rk  w  l  srs  sos  pts_for  \\\n",
       "9962        9962  louisiana-lafayette  Southland  43  0  0  NaN  NaN      NaN   \n",
       "9963        9963  louisiana-lafayette  Southland  44  0  0  NaN  NaN      NaN   \n",
       "\n",
       "      pts_vs  pts_total  ap_pre  ap_high  ap_final  pts_diff ncaa_result  \\\n",
       "9962     NaN        NaN      30       30        30       NaN         NaN   \n",
       "9963     NaN        NaN      30       30        30       NaN         NaN   \n",
       "\n",
       "      ncaa_numeric   season  year coach  \n",
       "9962             0  1974-75  1974  None  \n",
       "9963             0  1973-74  1973  None  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ncaa[ncaa['coach'] == \"None\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "6b59f325-833b-47cb-a3c0-e5c172bab578",
   "metadata": {},
   "outputs": [],
   "source": [
    "courses = pd.read_csv(\"data/testudo_fall2020.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "1cbcd537-e46d-4145-b1e8-8ff0d4ba9360",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['code', 'title', 'description', 'prereqs', 'credits', 'prereq_type',\n",
       "       'area'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "courses.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "8770edfb-b224-4644-9325-839ea03031aa",
   "metadata": {},
   "outputs": [],
   "source": [
    "def winrate(row):\n",
    "    return row['w'] / (row['w'] + row['l'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "bd8e27a9-41d3-434d-938f-dffdba7c9c38",
   "metadata": {},
   "outputs": [],
   "source": [
    "ncaa = ncaa[ncaa['coach'] != \"None\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "92a12707-515a-4423-b3d8-3294ab227a15",
   "metadata": {},
   "outputs": [],
   "source": [
    "ncaa['winrate'] = ncaa.apply(winrate, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "aaecb88d-5c1e-4585-9d3c-788d231efdc0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>school</th>\n",
       "      <th>conf</th>\n",
       "      <th>rk</th>\n",
       "      <th>w</th>\n",
       "      <th>l</th>\n",
       "      <th>srs</th>\n",
       "      <th>sos</th>\n",
       "      <th>pts_for</th>\n",
       "      <th>pts_vs</th>\n",
       "      <th>...</th>\n",
       "      <th>ap_pre</th>\n",
       "      <th>ap_high</th>\n",
       "      <th>ap_final</th>\n",
       "      <th>pts_diff</th>\n",
       "      <th>ncaa_result</th>\n",
       "      <th>ncaa_numeric</th>\n",
       "      <th>season</th>\n",
       "      <th>year</th>\n",
       "      <th>coach</th>\n",
       "      <th>winrate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>air-force</td>\n",
       "      <td>MWC</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>21</td>\n",
       "      <td>-2.99</td>\n",
       "      <td>1.08</td>\n",
       "      <td>73.1</td>\n",
       "      <td>75.1</td>\n",
       "      <td>...</td>\n",
       "      <td>30</td>\n",
       "      <td>30</td>\n",
       "      <td>30</td>\n",
       "      <td>-2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>2016-17</td>\n",
       "      <td>2016</td>\n",
       "      <td>Dave Pilipovich</td>\n",
       "      <td>0.363636</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>air-force</td>\n",
       "      <td>MWC</td>\n",
       "      <td>2</td>\n",
       "      <td>14</td>\n",
       "      <td>18</td>\n",
       "      <td>-5.51</td>\n",
       "      <td>0.66</td>\n",
       "      <td>68.4</td>\n",
       "      <td>72.8</td>\n",
       "      <td>...</td>\n",
       "      <td>30</td>\n",
       "      <td>30</td>\n",
       "      <td>30</td>\n",
       "      <td>-4.4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>2015-16</td>\n",
       "      <td>2015</td>\n",
       "      <td>Dave Pilipovich</td>\n",
       "      <td>0.437500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>air-force</td>\n",
       "      <td>MWC</td>\n",
       "      <td>3</td>\n",
       "      <td>14</td>\n",
       "      <td>17</td>\n",
       "      <td>-1.85</td>\n",
       "      <td>-0.71</td>\n",
       "      <td>65.7</td>\n",
       "      <td>65.1</td>\n",
       "      <td>...</td>\n",
       "      <td>30</td>\n",
       "      <td>30</td>\n",
       "      <td>30</td>\n",
       "      <td>0.6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>2014-15</td>\n",
       "      <td>2014</td>\n",
       "      <td>Dave Pilipovich</td>\n",
       "      <td>0.451613</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>air-force</td>\n",
       "      <td>MWC</td>\n",
       "      <td>4</td>\n",
       "      <td>12</td>\n",
       "      <td>18</td>\n",
       "      <td>-4.08</td>\n",
       "      <td>1.71</td>\n",
       "      <td>66.0</td>\n",
       "      <td>69.1</td>\n",
       "      <td>...</td>\n",
       "      <td>30</td>\n",
       "      <td>30</td>\n",
       "      <td>30</td>\n",
       "      <td>-3.1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>2013-14</td>\n",
       "      <td>2013</td>\n",
       "      <td>Dave Pilipovich</td>\n",
       "      <td>0.400000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>air-force</td>\n",
       "      <td>MWC</td>\n",
       "      <td>5</td>\n",
       "      <td>18</td>\n",
       "      <td>14</td>\n",
       "      <td>4.18</td>\n",
       "      <td>4.28</td>\n",
       "      <td>70.0</td>\n",
       "      <td>67.8</td>\n",
       "      <td>...</td>\n",
       "      <td>30</td>\n",
       "      <td>30</td>\n",
       "      <td>30</td>\n",
       "      <td>2.2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>2012-13</td>\n",
       "      <td>2012</td>\n",
       "      <td>Dave Pilipovich</td>\n",
       "      <td>0.562500</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0     school conf  rk   w   l   srs   sos  pts_for  pts_vs  ...  \\\n",
       "0           0  air-force  MWC   1  12  21 -2.99  1.08     73.1    75.1  ...   \n",
       "1           1  air-force  MWC   2  14  18 -5.51  0.66     68.4    72.8  ...   \n",
       "2           2  air-force  MWC   3  14  17 -1.85 -0.71     65.7    65.1  ...   \n",
       "3           3  air-force  MWC   4  12  18 -4.08  1.71     66.0    69.1  ...   \n",
       "4           4  air-force  MWC   5  18  14  4.18  4.28     70.0    67.8  ...   \n",
       "\n",
       "   ap_pre  ap_high  ap_final  pts_diff  ncaa_result ncaa_numeric   season  \\\n",
       "0      30       30        30      -2.0          NaN            0  2016-17   \n",
       "1      30       30        30      -4.4          NaN            0  2015-16   \n",
       "2      30       30        30       0.6          NaN            0  2014-15   \n",
       "3      30       30        30      -3.1          NaN            0  2013-14   \n",
       "4      30       30        30       2.2          NaN            0  2012-13   \n",
       "\n",
       "   year            coach   winrate  \n",
       "0  2016  Dave Pilipovich  0.363636  \n",
       "1  2015  Dave Pilipovich  0.437500  \n",
       "2  2014  Dave Pilipovich  0.451613  \n",
       "3  2013  Dave Pilipovich  0.400000  \n",
       "4  2012  Dave Pilipovich  0.562500  \n",
       "\n",
       "[5 rows x 21 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ncaa.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "9ddbf552-3f95-4b47-baeb-26c6f16893ef",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>coach</th>\n",
       "      <th>winrate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A. V. Barrett</td>\n",
       "      <td>0.540097</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A.A. Gillian</td>\n",
       "      <td>0.190476</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A.A. Mason</td>\n",
       "      <td>0.700000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A.B. Williamson</td>\n",
       "      <td>0.573192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A.D. Browne</td>\n",
       "      <td>0.538462</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3366</th>\n",
       "      <td>Yancey Goodall</td>\n",
       "      <td>0.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3367</th>\n",
       "      <td>Zac Roman</td>\n",
       "      <td>0.100000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3368</th>\n",
       "      <td>Zach Spiker</td>\n",
       "      <td>0.450804</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3369</th>\n",
       "      <td>Zeke Martin</td>\n",
       "      <td>0.475000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3370</th>\n",
       "      <td>Zora Clevenger</td>\n",
       "      <td>0.692140</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3371 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                coach   winrate\n",
       "0       A. V. Barrett  0.540097\n",
       "1        A.A. Gillian  0.190476\n",
       "2          A.A. Mason  0.700000\n",
       "3     A.B. Williamson  0.573192\n",
       "4         A.D. Browne  0.538462\n",
       "...               ...       ...\n",
       "3366   Yancey Goodall  0.500000\n",
       "3367        Zac Roman  0.100000\n",
       "3368      Zach Spiker  0.450804\n",
       "3369      Zeke Martin  0.475000\n",
       "3370   Zora Clevenger  0.692140\n",
       "\n",
       "[3371 rows x 2 columns]"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "coach_winrates = ncaa.groupby(\"coach\", as_index=False).agg(\n",
    "    winrate=(\"winrate\", \"mean\"),\n",
    "    # totalwins=(\"w\", \"sum\"),\n",
    "    # totallossess=(\"l\", \"sum\")\n",
    ")\n",
    "coach_winrates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "b1c0f40d-a1b7-42d0-87a1-d54422aa1593",
   "metadata": {},
   "outputs": [],
   "source": [
    "top5 = coach_winrates.sort_values(by=\"winrate\", ascending=False, kind=\"mergesort\")[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "857cd10b-9ade-4d33-8993-f920bb0052c1",
   "metadata": {},
   "outputs": [],
   "source": [
    "bottom5 = coach_winrates.sort_values(by=\"winrate\", ascending=True, kind=\"mergesort\")[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "ffc5fa63-5992-4085-b0ce-08f9b247068b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['C.I. Freeman',\n",
       " 'George Denman',\n",
       " 'George Spaulding',\n",
       " 'Grinnell Jones',\n",
       " \"Gus O'Conner\",\n",
       " 'Henry Rose',\n",
       " 'Howard Ray',\n",
       " 'Jerry Desmond',\n",
       " 'Jim Flanagan',\n",
       " 'Laurence Wild',\n",
       " 'Louis Wenzell',\n",
       " 'M.P. Burroughs',\n",
       " 'Orin Kates',\n",
       " 'R.E. Edmonds',\n",
       " 'S.A. Tennison',\n",
       " 'T.P. Hewitt']"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(coach_winrates[coach_winrates['winrate'] == 1.0]['coach'].values)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "a3bf5ca5-17ea-43e1-aec0-310cf06b00d9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['C.C. Childs',\n",
       " 'Charles Burden',\n",
       " 'Dean Cromwell',\n",
       " 'Doc Fletcher',\n",
       " 'Dudy Noble',\n",
       " 'Elwood Romney',\n",
       " 'F. Knight',\n",
       " 'F.E. Scofield',\n",
       " 'F.G. Carter',\n",
       " 'George Walker',\n",
       " 'Harry Gilligan',\n",
       " 'Harry Rhodes',\n",
       " 'Henry Pratt',\n",
       " 'J.B. Andrews',\n",
       " 'J.H. Brown',\n",
       " 'Jeff Mangold',\n",
       " 'Jim Browning',\n",
       " 'Peggy Swope',\n",
       " 'Squint Hunter',\n",
       " 'W.S. Milligan',\n",
       " 'W.T. Cook',\n",
       " 'Walter Winslow',\n",
       " 'Will Bryan',\n",
       " 'William Gardiner']"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(coach_winrates[coach_winrates['winrate'] == 0.0]['coach'].values)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "96be559a-b977-480e-bd8b-c0951ecedf69",
   "metadata": {},
   "outputs": [],
   "source": [
    "with open('data/ncaa-report.txt', 'w') as f:\n",
    "    f.write(\"Top 5 coaches by winrate:\")\n",
    "    f.write(\"\\n\")\n",
    "    f.write(top5.to_string())\n",
    "    f.write(\"\\n\\n\")\n",
    "    f.write(\"Bottom 5 coaches by winrate:\")\n",
    "    f.write(\"\\n\")\n",
    "    f.write(bottom5.to_string())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8d450e98-f35a-4cae-8e17-f7afbe899d84",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}