Week3 Challenge.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# note data is xlsx excel format not csv\n",
    "\n",
    "# i am going to do this in \"unclean\" way\n",
    "sheets_dict = pd.read_excel('PD - NBA Results.xlsx', sheet_name=None)\n",
    "full_table = pd.DataFrame()\n",
    "for name, sheet in sheets_dict.items():\n",
    "    if name != \"Team List\":\n",
    "        sheet['sheet'] = name\n",
    "        sheet = sheet.rename(columns=lambda x: x.split('\\n')[-1])\n",
    "        full_table = full_table.append(sheet)\n",
    "    else:\n",
    "        team_list = sheet\n",
    "\n",
    "full_table.reset_index(inplace=True, drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>Date</th>\n",
       "      <th>Start (ET)</th>\n",
       "      <th>Visitor/Neutral</th>\n",
       "      <th>PTS</th>\n",
       "      <th>Home/Neutral</th>\n",
       "      <th>PTS.1</th>\n",
       "      <th>Unnamed: 6</th>\n",
       "      <th>Unnamed: 7</th>\n",
       "      <th>Attend.</th>\n",
       "      <th>Notes</th>\n",
       "      <th>sheet</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Tue Oct 22 2019</td>\n",
       "      <td>8:00p</td>\n",
       "      <td>New Orleans Pelicans</td>\n",
       "      <td>122.0</td>\n",
       "      <td>Toronto Raptors</td>\n",
       "      <td>130.0</td>\n",
       "      <td>Box Score</td>\n",
       "      <td>OT</td>\n",
       "      <td>20787.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>October Results</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Tue Oct 22 2019</td>\n",
       "      <td>10:30p</td>\n",
       "      <td>Los Angeles Lakers</td>\n",
       "      <td>102.0</td>\n",
       "      <td>Los Angeles Clippers</td>\n",
       "      <td>112.0</td>\n",
       "      <td>Box Score</td>\n",
       "      <td>NaN</td>\n",
       "      <td>19068.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>October Results</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Wed Oct 23 2019</td>\n",
       "      <td>7:00p</td>\n",
       "      <td>Chicago Bulls</td>\n",
       "      <td>125.0</td>\n",
       "      <td>Charlotte Hornets</td>\n",
       "      <td>126.0</td>\n",
       "      <td>Box Score</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15424.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>October Results</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Wed Oct 23 2019</td>\n",
       "      <td>7:00p</td>\n",
       "      <td>Detroit Pistons</td>\n",
       "      <td>119.0</td>\n",
       "      <td>Indiana Pacers</td>\n",
       "      <td>110.0</td>\n",
       "      <td>Box Score</td>\n",
       "      <td>NaN</td>\n",
       "      <td>17923.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>October Results</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Wed Oct 23 2019</td>\n",
       "      <td>7:00p</td>\n",
       "      <td>Cleveland Cavaliers</td>\n",
       "      <td>85.0</td>\n",
       "      <td>Orlando Magic</td>\n",
       "      <td>94.0</td>\n",
       "      <td>Box Score</td>\n",
       "      <td>NaN</td>\n",
       "      <td>18846.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>October Results</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              Date Start (ET)       Visitor/Neutral    PTS  \\\n",
       "0  Tue Oct 22 2019      8:00p  New Orleans Pelicans  122.0   \n",
       "1  Tue Oct 22 2019     10:30p    Los Angeles Lakers  102.0   \n",
       "2  Wed Oct 23 2019      7:00p         Chicago Bulls  125.0   \n",
       "3  Wed Oct 23 2019      7:00p       Detroit Pistons  119.0   \n",
       "4  Wed Oct 23 2019      7:00p   Cleveland Cavaliers   85.0   \n",
       "\n",
       "           Home/Neutral  PTS.1 Unnamed: 6 Unnamed: 7  Attend. Notes  \\\n",
       "0       Toronto Raptors  130.0  Box Score         OT  20787.0   NaN   \n",
       "1  Los Angeles Clippers  112.0  Box Score        NaN  19068.0   NaN   \n",
       "2     Charlotte Hornets  126.0  Box Score        NaN  15424.0   NaN   \n",
       "3        Indiana Pacers  110.0  Box Score        NaN  17923.0   NaN   \n",
       "4         Orlando Magic   94.0  Box Score        NaN  18846.0   NaN   \n",
       "\n",
       "             sheet  \n",
       "0  October Results  \n",
       "1  October Results  \n",
       "2  October Results  \n",
       "3  October Results  \n",
       "4  October Results  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "full_table.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(726, 11)"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "full_table.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>Team</th>\n",
       "      <th>Divison</th>\n",
       "      <th>Conference</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Atlanta Hawks</td>\n",
       "      <td>Southeast</td>\n",
       "      <td>Eastern</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Boston Celtics</td>\n",
       "      <td>Atlantic</td>\n",
       "      <td>Eastern</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Brooklyn Nets</td>\n",
       "      <td>Atlantic</td>\n",
       "      <td>Eastern</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Charlotte Hornets</td>\n",
       "      <td>Southeast</td>\n",
       "      <td>Eastern</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Chicago Bulls</td>\n",
       "      <td>Central</td>\n",
       "      <td>Eastern</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                Team    Divison Conference\n",
       "0      Atlanta Hawks  Southeast    Eastern\n",
       "1     Boston Celtics   Atlantic    Eastern\n",
       "2      Brooklyn Nets   Atlantic    Eastern\n",
       "3  Charlotte Hornets  Southeast    Eastern\n",
       "4      Chicago Bulls    Central    Eastern"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "team_list.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "team_stats = {i:{\"W\":0, \"L\":0, \"Conf\":[0,0], \"Home\":[0,0], \"Away\":[0,0], \"streak\":[], \"conference\":j} for i,j in zip(team_list[\"Team\"].values, team_list[\"Conference\"].values)}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "# i will not do this in pandas dataframe level, but in iterative approach\n",
    "for index, row in full_table.iterrows():\n",
    "    visit, v_s, home, h_s = row[\"Visitor/Neutral\"], row[\"PTS\"], row[\"Home/Neutral\"], row[\"PTS.1\"]\n",
    "    #print(visit, v_s, home, h_s)\n",
    "    if v_s > h_s: # visitor won\n",
    "        team_stats[visit][\"W\"] += 1\n",
    "        team_stats[visit][\"Away\"][0] += 1\n",
    "        team_stats[visit][\"streak\"] += [\"W\"]\n",
    "        team_stats[home][\"L\"] += 1\n",
    "        team_stats[home][\"Home\"][1] += 1\n",
    "        team_stats[home][\"streak\"] += [\"L\"]\n",
    "        if team_stats[visit][\"conference\"] == team_stats[home][\"conference\"]:\n",
    "            team_stats[visit][\"Conf\"][0] += 1\n",
    "            team_stats[home][\"Conf\"][1] += 1\n",
    "    elif v_s < h_s: # home won\n",
    "        team_stats[visit][\"L\"] += 1\n",
    "        team_stats[visit][\"Away\"][1] += 1\n",
    "        team_stats[visit][\"streak\"] += [\"L\"]\n",
    "        team_stats[home][\"W\"] += 1\n",
    "        team_stats[home][\"Home\"][0] += 1\n",
    "        team_stats[home][\"streak\"] += [\"W\"]\n",
    "        if team_stats[visit][\"conference\"] == team_stats[home][\"conference\"]:\n",
    "            team_stats[visit][\"Conf\"][1] += 1\n",
    "            team_stats[home][\"Conf\"][0] += 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "from collections import Counter\n",
    "western = []\n",
    "for team in team_stats.keys():\n",
    "    if team_stats[team][\"conference\"] == \"Western\":\n",
    "        last10 = team_stats[team][\"streak\"][-10:]\n",
    "        c = Counter(last10)\n",
    "        streak, count = last10[-1], 1\n",
    "        for i in range(9):\n",
    "            if last10[-(i+2)] == streak:\n",
    "                count += 1\n",
    "            else:\n",
    "                break\n",
    "        western += [[team, team_stats[team][\"W\"], team_stats[team][\"L\"], \n",
    "                    round(team_stats[team][\"W\"]/(team_stats[team][\"W\"] + team_stats[team][\"L\"]), 3),\n",
    "                    \"-\".join(str(x) for x in team_stats[team][\"Conf\"]),\n",
    "                    \"-\".join(str(x) for x in team_stats[team][\"Home\"]),\n",
    "                    \"-\".join(str(x) for x in team_stats[team][\"Away\"]),\n",
    "                    \"-\".join(str(x) for x in [c[\"W\"], c[\"L\"]]),\n",
    "                    streak+str(count)\n",
    "                   ]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Dallas Mavericks', 22, 13, 0.629, '13-6', '10-8', '12-5', '5-5', 'L1']\n",
      "['Denver Nuggets', 24, 11, 0.686, '14-6', '15-4', '9-7', '7-3', 'L1']\n",
      "['Golden State Warriors', 9, 28, 0.243, '7-20', '6-12', '3-16', '4-6', 'L4']\n",
      "['Houston Rockets', 24, 11, 0.686, '14-7', '13-4', '11-7', '7-3', 'W2']\n",
      "['Los Angeles Clippers', 25, 12, 0.676, '16-9', '15-4', '10-8', '5-5', 'L1']\n",
      "['Los Angeles Lakers', 28, 7, 0.8, '20-4', '13-4', '15-3', '6-4', 'W4']\n",
      "['Memphis Grizzlies', 14, 22, 0.389, '9-14', '7-12', '7-10', '5-5', 'W1']\n",
      "['Minnesota Timberwolves', 13, 21, 0.382, '6-16', '5-11', '8-10', '3-7', 'W1']\n",
      "['New Orleans Pelicans', 12, 24, 0.333, '10-15', '6-11', '6-13', '6-4', 'W1']\n",
      "['Oklahoma City Thunder', 20, 15, 0.571, '14-11', '12-6', '8-9', '9-1', 'W5']\n",
      "['Phoenix Suns', 14, 21, 0.4, '9-17', '7-12', '7-9', '3-7', 'W1']\n",
      "['Portland Trail Blazers', 15, 21, 0.417, '9-15', '8-9', '7-12', '5-5', 'W1']\n",
      "['Sacramento Kings', 13, 23, 0.361, '9-14', '7-10', '6-13', '1-9', 'L1']\n",
      "['San Antonio Spurs', 14, 20, 0.412, '9-12', '10-9', '4-11', '5-5', 'L2']\n",
      "['Utah Jazz', 23, 12, 0.657, '14-7', '13-3', '10-9', '9-1', 'W5']\n"
     ]
    }
   ],
   "source": [
    "for i in western:\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "# column names\n",
    "cols = [\"Team\", \"W\", \"L\", \"Pct\", \"Conf\", \"Home\", \"Away\", \"L10\", \"Strk\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "Western = pd.DataFrame(data=western, columns=cols)\n",
    "Western['Rank'] = Western['Pct'].rank(method='max', ascending=False)\n",
    "Western = Western.astype({\"Rank\": \"int\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "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>Team</th>\n",
       "      <th>W</th>\n",
       "      <th>L</th>\n",
       "      <th>Pct</th>\n",
       "      <th>Conf</th>\n",
       "      <th>Home</th>\n",
       "      <th>Away</th>\n",
       "      <th>L10</th>\n",
       "      <th>Strk</th>\n",
       "      <th>Rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Dallas Mavericks</td>\n",
       "      <td>22</td>\n",
       "      <td>13</td>\n",
       "      <td>0.629</td>\n",
       "      <td>13-6</td>\n",
       "      <td>10-8</td>\n",
       "      <td>12-5</td>\n",
       "      <td>5-5</td>\n",
       "      <td>L1</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Denver Nuggets</td>\n",
       "      <td>24</td>\n",
       "      <td>11</td>\n",
       "      <td>0.686</td>\n",
       "      <td>14-6</td>\n",
       "      <td>15-4</td>\n",
       "      <td>9-7</td>\n",
       "      <td>7-3</td>\n",
       "      <td>L1</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Golden State Warriors</td>\n",
       "      <td>9</td>\n",
       "      <td>28</td>\n",
       "      <td>0.243</td>\n",
       "      <td>7-20</td>\n",
       "      <td>6-12</td>\n",
       "      <td>3-16</td>\n",
       "      <td>4-6</td>\n",
       "      <td>L4</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Houston Rockets</td>\n",
       "      <td>24</td>\n",
       "      <td>11</td>\n",
       "      <td>0.686</td>\n",
       "      <td>14-7</td>\n",
       "      <td>13-4</td>\n",
       "      <td>11-7</td>\n",
       "      <td>7-3</td>\n",
       "      <td>W2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Los Angeles Clippers</td>\n",
       "      <td>25</td>\n",
       "      <td>12</td>\n",
       "      <td>0.676</td>\n",
       "      <td>16-9</td>\n",
       "      <td>15-4</td>\n",
       "      <td>10-8</td>\n",
       "      <td>5-5</td>\n",
       "      <td>L1</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Los Angeles Lakers</td>\n",
       "      <td>28</td>\n",
       "      <td>7</td>\n",
       "      <td>0.800</td>\n",
       "      <td>20-4</td>\n",
       "      <td>13-4</td>\n",
       "      <td>15-3</td>\n",
       "      <td>6-4</td>\n",
       "      <td>W4</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Memphis Grizzlies</td>\n",
       "      <td>14</td>\n",
       "      <td>22</td>\n",
       "      <td>0.389</td>\n",
       "      <td>9-14</td>\n",
       "      <td>7-12</td>\n",
       "      <td>7-10</td>\n",
       "      <td>5-5</td>\n",
       "      <td>W1</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Minnesota Timberwolves</td>\n",
       "      <td>13</td>\n",
       "      <td>21</td>\n",
       "      <td>0.382</td>\n",
       "      <td>6-16</td>\n",
       "      <td>5-11</td>\n",
       "      <td>8-10</td>\n",
       "      <td>3-7</td>\n",
       "      <td>W1</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>New Orleans Pelicans</td>\n",
       "      <td>12</td>\n",
       "      <td>24</td>\n",
       "      <td>0.333</td>\n",
       "      <td>10-15</td>\n",
       "      <td>6-11</td>\n",
       "      <td>6-13</td>\n",
       "      <td>6-4</td>\n",
       "      <td>W1</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Oklahoma City Thunder</td>\n",
       "      <td>20</td>\n",
       "      <td>15</td>\n",
       "      <td>0.571</td>\n",
       "      <td>14-11</td>\n",
       "      <td>12-6</td>\n",
       "      <td>8-9</td>\n",
       "      <td>9-1</td>\n",
       "      <td>W5</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Phoenix Suns</td>\n",
       "      <td>14</td>\n",
       "      <td>21</td>\n",
       "      <td>0.400</td>\n",
       "      <td>9-17</td>\n",
       "      <td>7-12</td>\n",
       "      <td>7-9</td>\n",
       "      <td>3-7</td>\n",
       "      <td>W1</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Portland Trail Blazers</td>\n",
       "      <td>15</td>\n",
       "      <td>21</td>\n",
       "      <td>0.417</td>\n",
       "      <td>9-15</td>\n",
       "      <td>8-9</td>\n",
       "      <td>7-12</td>\n",
       "      <td>5-5</td>\n",
       "      <td>W1</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Sacramento Kings</td>\n",
       "      <td>13</td>\n",
       "      <td>23</td>\n",
       "      <td>0.361</td>\n",
       "      <td>9-14</td>\n",
       "      <td>7-10</td>\n",
       "      <td>6-13</td>\n",
       "      <td>1-9</td>\n",
       "      <td>L1</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>San Antonio Spurs</td>\n",
       "      <td>14</td>\n",
       "      <td>20</td>\n",
       "      <td>0.412</td>\n",
       "      <td>9-12</td>\n",
       "      <td>10-9</td>\n",
       "      <td>4-11</td>\n",
       "      <td>5-5</td>\n",
       "      <td>L2</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Utah Jazz</td>\n",
       "      <td>23</td>\n",
       "      <td>12</td>\n",
       "      <td>0.657</td>\n",
       "      <td>14-7</td>\n",
       "      <td>13-3</td>\n",
       "      <td>10-9</td>\n",
       "      <td>9-1</td>\n",
       "      <td>W5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      Team   W   L    Pct   Conf  Home  Away  L10 Strk  Rank\n",
       "0         Dallas Mavericks  22  13  0.629   13-6  10-8  12-5  5-5   L1     6\n",
       "1           Denver Nuggets  24  11  0.686   14-6  15-4   9-7  7-3   L1     3\n",
       "2    Golden State Warriors   9  28  0.243   7-20  6-12  3-16  4-6   L4    15\n",
       "3          Houston Rockets  24  11  0.686   14-7  13-4  11-7  7-3   W2     3\n",
       "4     Los Angeles Clippers  25  12  0.676   16-9  15-4  10-8  5-5   L1     4\n",
       "5       Los Angeles Lakers  28   7  0.800   20-4  13-4  15-3  6-4   W4     1\n",
       "6        Memphis Grizzlies  14  22  0.389   9-14  7-12  7-10  5-5   W1    11\n",
       "7   Minnesota Timberwolves  13  21  0.382   6-16  5-11  8-10  3-7   W1    12\n",
       "8     New Orleans Pelicans  12  24  0.333  10-15  6-11  6-13  6-4   W1    14\n",
       "9    Oklahoma City Thunder  20  15  0.571  14-11  12-6   8-9  9-1   W5     7\n",
       "10            Phoenix Suns  14  21  0.400   9-17  7-12   7-9  3-7   W1    10\n",
       "11  Portland Trail Blazers  15  21  0.417   9-15   8-9  7-12  5-5   W1     8\n",
       "12        Sacramento Kings  13  23  0.361   9-14  7-10  6-13  1-9   L1    13\n",
       "13       San Antonio Spurs  14  20  0.412   9-12  10-9  4-11  5-5   L2     9\n",
       "14               Utah Jazz  23  12  0.657   14-7  13-3  10-9  9-1   W5     5"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Western.head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "eastern = []\n",
    "for team in team_stats.keys():\n",
    "    if team_stats[team][\"conference\"] == \"Eastern\":\n",
    "        last10 = team_stats[team][\"streak\"][-10:]\n",
    "        c = Counter(last10)\n",
    "        streak, count = last10[-1], 1\n",
    "        for i in range(9):\n",
    "            if last10[-(i+2)] == streak:\n",
    "                count += 1\n",
    "            else:\n",
    "                break\n",
    "        eastern += [[team, team_stats[team][\"W\"], team_stats[team][\"L\"], \n",
    "                    round(team_stats[team][\"W\"]/(team_stats[team][\"W\"] + team_stats[team][\"L\"]), 3),\n",
    "                    \"-\".join(str(x) for x in team_stats[team][\"Conf\"]),\n",
    "                    \"-\".join(str(x) for x in team_stats[team][\"Home\"]),\n",
    "                    \"-\".join(str(x) for x in team_stats[team][\"Away\"]),\n",
    "                    \"-\".join(str(x) for x in [c[\"W\"], c[\"L\"]]),\n",
    "                    streak+str(count)\n",
    "                   ]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "Eastern = pd.DataFrame(data=eastern, columns=cols)\n",
    "Eastern['Rank'] = Eastern['Pct'].rank(method='max', ascending=False)\n",
    "Eastern = Eastern.astype({\"Rank\": \"int\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "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>Team</th>\n",
       "      <th>W</th>\n",
       "      <th>L</th>\n",
       "      <th>Pct</th>\n",
       "      <th>Conf</th>\n",
       "      <th>Home</th>\n",
       "      <th>Away</th>\n",
       "      <th>L10</th>\n",
       "      <th>Strk</th>\n",
       "      <th>Rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Atlanta Hawks</td>\n",
       "      <td>8</td>\n",
       "      <td>28</td>\n",
       "      <td>0.222</td>\n",
       "      <td>5-19</td>\n",
       "      <td>4-12</td>\n",
       "      <td>4-16</td>\n",
       "      <td>2-8</td>\n",
       "      <td>W1</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Boston Celtics</td>\n",
       "      <td>25</td>\n",
       "      <td>8</td>\n",
       "      <td>0.758</td>\n",
       "      <td>18-5</td>\n",
       "      <td>14-2</td>\n",
       "      <td>11-6</td>\n",
       "      <td>8-2</td>\n",
       "      <td>W3</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Brooklyn Nets</td>\n",
       "      <td>16</td>\n",
       "      <td>18</td>\n",
       "      <td>0.471</td>\n",
       "      <td>10-9</td>\n",
       "      <td>9-7</td>\n",
       "      <td>7-11</td>\n",
       "      <td>3-7</td>\n",
       "      <td>L5</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Charlotte Hornets</td>\n",
       "      <td>15</td>\n",
       "      <td>23</td>\n",
       "      <td>0.395</td>\n",
       "      <td>10-14</td>\n",
       "      <td>7-11</td>\n",
       "      <td>8-12</td>\n",
       "      <td>3-7</td>\n",
       "      <td>W2</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Chicago Bulls</td>\n",
       "      <td>13</td>\n",
       "      <td>23</td>\n",
       "      <td>0.361</td>\n",
       "      <td>9-15</td>\n",
       "      <td>7-13</td>\n",
       "      <td>6-10</td>\n",
       "      <td>4-6</td>\n",
       "      <td>L3</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Cleveland Cavaliers</td>\n",
       "      <td>10</td>\n",
       "      <td>25</td>\n",
       "      <td>0.286</td>\n",
       "      <td>6-21</td>\n",
       "      <td>6-12</td>\n",
       "      <td>4-13</td>\n",
       "      <td>4-6</td>\n",
       "      <td>L3</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Detroit Pistons</td>\n",
       "      <td>13</td>\n",
       "      <td>23</td>\n",
       "      <td>0.361</td>\n",
       "      <td>9-18</td>\n",
       "      <td>8-10</td>\n",
       "      <td>5-13</td>\n",
       "      <td>2-8</td>\n",
       "      <td>W1</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Indiana Pacers</td>\n",
       "      <td>22</td>\n",
       "      <td>14</td>\n",
       "      <td>0.611</td>\n",
       "      <td>15-10</td>\n",
       "      <td>15-4</td>\n",
       "      <td>7-10</td>\n",
       "      <td>5-5</td>\n",
       "      <td>L2</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Miami Heat</td>\n",
       "      <td>25</td>\n",
       "      <td>10</td>\n",
       "      <td>0.714</td>\n",
       "      <td>18-4</td>\n",
       "      <td>16-1</td>\n",
       "      <td>9-9</td>\n",
       "      <td>7-3</td>\n",
       "      <td>L1</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Milwaukee Bucks</td>\n",
       "      <td>32</td>\n",
       "      <td>5</td>\n",
       "      <td>0.865</td>\n",
       "      <td>20-3</td>\n",
       "      <td>18-2</td>\n",
       "      <td>14-3</td>\n",
       "      <td>8-2</td>\n",
       "      <td>W5</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>New York Knicks</td>\n",
       "      <td>10</td>\n",
       "      <td>25</td>\n",
       "      <td>0.286</td>\n",
       "      <td>5-18</td>\n",
       "      <td>5-12</td>\n",
       "      <td>5-13</td>\n",
       "      <td>5-5</td>\n",
       "      <td>L1</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Orlando Magic</td>\n",
       "      <td>16</td>\n",
       "      <td>20</td>\n",
       "      <td>0.444</td>\n",
       "      <td>11-11</td>\n",
       "      <td>11-8</td>\n",
       "      <td>5-12</td>\n",
       "      <td>4-6</td>\n",
       "      <td>L1</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Philadelphia 76ers</td>\n",
       "      <td>23</td>\n",
       "      <td>14</td>\n",
       "      <td>0.622</td>\n",
       "      <td>16-8</td>\n",
       "      <td>16-2</td>\n",
       "      <td>7-12</td>\n",
       "      <td>3-7</td>\n",
       "      <td>L4</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Toronto Raptors</td>\n",
       "      <td>24</td>\n",
       "      <td>12</td>\n",
       "      <td>0.667</td>\n",
       "      <td>17-7</td>\n",
       "      <td>14-5</td>\n",
       "      <td>10-7</td>\n",
       "      <td>6-4</td>\n",
       "      <td>W1</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Washington Wizards</td>\n",
       "      <td>11</td>\n",
       "      <td>24</td>\n",
       "      <td>0.314</td>\n",
       "      <td>6-13</td>\n",
       "      <td>6-10</td>\n",
       "      <td>5-14</td>\n",
       "      <td>3-7</td>\n",
       "      <td>W1</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   Team   W   L    Pct   Conf  Home  Away  L10 Strk  Rank\n",
       "0         Atlanta Hawks   8  28  0.222   5-19  4-12  4-16  2-8   W1    15\n",
       "1        Boston Celtics  25   8  0.758   18-5  14-2  11-6  8-2   W3     2\n",
       "2         Brooklyn Nets  16  18  0.471   10-9   9-7  7-11  3-7   L5     7\n",
       "3     Charlotte Hornets  15  23  0.395  10-14  7-11  8-12  3-7   W2     9\n",
       "4         Chicago Bulls  13  23  0.361   9-15  7-13  6-10  4-6   L3    11\n",
       "5   Cleveland Cavaliers  10  25  0.286   6-21  6-12  4-13  4-6   L3    14\n",
       "6       Detroit Pistons  13  23  0.361   9-18  8-10  5-13  2-8   W1    11\n",
       "7        Indiana Pacers  22  14  0.611  15-10  15-4  7-10  5-5   L2     6\n",
       "8            Miami Heat  25  10  0.714   18-4  16-1   9-9  7-3   L1     3\n",
       "9       Milwaukee Bucks  32   5  0.865   20-3  18-2  14-3  8-2   W5     1\n",
       "10      New York Knicks  10  25  0.286   5-18  5-12  5-13  5-5   L1    14\n",
       "11        Orlando Magic  16  20  0.444  11-11  11-8  5-12  4-6   L1     8\n",
       "12   Philadelphia 76ers  23  14  0.622   16-8  16-2  7-12  3-7   L4     5\n",
       "13      Toronto Raptors  24  12  0.667   17-7  14-5  10-7  6-4   W1     4\n",
       "14   Washington Wizards  11  24  0.314   6-13  6-10  5-14  3-7   W1    12"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Eastern.head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [],
   "source": [
    "# let's save to one excel file\n",
    "writer = pd.ExcelWriter('final_output.xlsx', engine='xlsxwriter')\n",
    "\n",
    "# Write each dataframe to a different worksheet.\n",
    "Western.to_excel(writer, sheet_name='WesternConferenceStandings')\n",
    "Eastern.to_excel(writer, sheet_name='EasternConferenceStandings')\n",
    "\n",
    "writer.save()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}