5-TidyData.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Tidy Data\n",
    "\n",
    "> Structuring datasets to facilitate analysis [(Wickham 2014)](http://www.jstatsoft.org/v59/i10/paper)\n",
    "\n",
    "If there's one maxim I can impart it's that your tools shouldn't get in the way of your analysis. Your problem is already difficult enough, don't let the data or your tools make it any harder.\n",
    "\n",
    "## The Rules\n",
    "\n",
    "In a tidy dataset...\n",
    "\n",
    "1. Each variable forms a column\n",
    "2. Each observation forms a row\n",
    "\n",
    "Consistently following these rules when writing your data-processing functions makes the whole experience smoother.\n",
    "We'll cover a few methods that help you tidy your data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "sns.set(style=\"white\", context=\"talk\")\n",
    "plt.rcParams['figure.figsize'] = 12, 8\n",
    "pd.options.display.max_rows = 10\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Earlier, I fetched some data\n",
    "\n",
    "```python\n",
    "tables = pd.read_html(\"http://www.basketball-reference.com/leagues/NBA_2016_games.html\")\n",
    "games = tables[0]\n",
    "games.to_csv('data/games.csv', index=False)\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "pd.read_html?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "!head -n  5 data/games.csv"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So the data is roughly like\n",
    "\n",
    "| Date        | Visitor Team | Visitor Points | Home Team | Home Points |\n",
    "| ----------- | ------------ | -------------- | --------- | ----------- |\n",
    "| 2015-10-07  | Detroit      | 106            | Atlanta   | 94          |\n",
    "| ...         | ...          | ...            | ...       | ...         |\n",
    "\n",
    "Plus some extra junk."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[The Question](http://stackoverflow.com/questions/22695680/python-pandas-timedelta-specific-rows):\n",
    "> **How many days of rest did each team get between each game?**\n",
    "\n",
    "Whether or not your dataset is tidy depends on your question. Given our question, what is an observation?\n",
    "\n",
    "---\n",
    "<a href=\"#answer\" class=\"btn btn-default\" data-toggle=\"collapse\">Show Answer</a>\n",
    "<div id=\"answer\" class=\"collapse\">\n",
    "An observation is a (team, game) pair. So no, we don't have a tidy dataset.\n",
    "A tidy dataset would be like\n",
    "\n",
    "<table>\n",
    "<thead>\n",
    "<tr class=\"header\">\n",
    "<th>Date</th>\n",
    "<th>Team</th>\n",
    "<th>Home / Away</th>\n",
    "<th>Points</th>\n",
    "</tr>\n",
    "</thead>\n",
    "<tbody>\n",
    "<tr class=\"odd\">\n",
    "<td>2015-10-07</td>\n",
    "<td>Detroit</td>\n",
    "<td>Away</td>\n",
    "<td>106</td>\n",
    "</tr>\n",
    "<tr class=\"even\">\n",
    "<td>2015-10-07</td>\n",
    "<td>Atlanta</td>\n",
    "<td>Home</td>\n",
    "<td>94</td>\n",
    "</tr>\n",
    "<tr class=\"odd\">\n",
    "<td>...</td>\n",
    "<td>...</td>\n",
    "<td>...</td>\n",
    "<td>...</td>\n",
    "</tr>\n",
    "</tbody>\n",
    "</table>\n",
    "\n",
    "</div>\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "column_names = {'Date': 'date', 'Start (ET)': 'start',\n",
    "                'Unamed: 2': 'box', 'Visitor/Neutral': 'away_team', \n",
    "                'PTS': 'away_points', 'Home/Neutral': 'home_team',\n",
    "                'PTS.1': 'home_points', 'Unamed: 7': 'n_ot'}\n",
    "\n",
    "games = (\n",
    "    pd.read_csv(\"data/games.csv\")\n",
    "      .rename(columns=column_names)\n",
    "      .dropna(thresh=4)\n",
    "      [['date', 'away_team', 'away_points', 'home_team', 'home_points']]\n",
    "      .assign(date=lambda x: pd.to_datetime(x['date'], format='%a, %b %d, %Y'))\n",
    "      .set_index('date', append=True)\n",
    "      .rename_axis([\"game_id\", \"date\"])\n",
    "      .sort_index()\n",
    ")\n",
    "games.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Above, we saw that we need to collapse the away / home teams down to two columns: one identifier and one for the value. Likewise with the points.\n",
    "We'll also need to repeat the metadata fields, like the date and `game_id`, so that each observation is matched with the correct date. `pd.melt` does all this for us."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "tidy = pd.melt(games.reset_index(),\n",
    "               id_vars=['game_id', 'date'], value_vars=['away_team', 'home_team'],\n",
    "               value_name='team').sort_values(['game_id', 'date'])\n",
    "\n",
    "tidy.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now the translation from question to operation is direct:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# How many days of rest for each team\n",
    "# For each team...  get number of days between games\n",
    "tidy.groupby('team')['date'].diff().dt.days - 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "tidy['rest'] = tidy.groupby('team').date.diff().dt.days - 1\n",
    "tidy.dropna().head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can \"invert\" a `melt` with `pd.pivot_table`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "by_game = pd.pivot_table(tidy, values='rest',\n",
    "                         index=['game_id', 'date'],\n",
    "                         columns='variable').rename(\n",
    "    columns={'away_team': 'away_rest', 'home_team': 'home_rest'}\n",
    ")\n",
    "by_game.columns.name = None\n",
    "\n",
    "by_game.dropna().head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`concat` will merge two dataframes, expanding an `axis`, while aligning on the other axis."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df = pd.concat([games, by_game], axis='columns')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "g = sns.FacetGrid(data=tidy.dropna(), col='team', col_wrap=5, hue='team')\n",
    "g.map(sns.barplot, \"variable\", \"rest\");"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "delta = (df['home_rest'] - df['away_rest']).dropna().astype(int)\n",
    "(delta.value_counts()\n",
    "    .reindex(np.arange(delta.min(), delta.max() + 1), fill_value=0)\n",
    "    .sort_index().plot(kind='bar', color='k', width=.9, rot=0, figsize=(12, 6)))\n",
    "sns.despine()\n",
    "plt.xlabel(\"Difference in Rest (home - away)\")\n",
    "plt.grid(axis='y');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "# Stack / Unstack"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Not all APIs expect tidy data, so you need to convert between \"wide\" and \"long\" form data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "rest = (tidy.groupby(['date', 'variable'])\n",
    "            .rest.mean()\n",
    "            .dropna())\n",
    "rest.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`rest` is in \"long\" form. `DataFrame.plot` for example, expects wide form data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "rest.unstack().head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "rest.unstack().rolling(14).mean().plot()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-success\">\n",
    "  <h1>Mini Project: Home Court Advantage?</h1>\n",
    "</div>\n",
    "\n",
    "What's the effect (in terms of probability to win) of being\n",
    "the home team?\n",
    "What are the components of the advantage?\n",
    "\n",
    "We'll fit a Logistic regression like\n",
    "\n",
    "`home_win ~ home_strength + away_strength + home_rest + away_rest`\n",
    "\n",
    "Our final dataframe will have one row per game (like `df`).\n",
    "Most examples I've seen use a \"team strength\" variable in their regression estimating the home court advantage. We'll use the team's win percent as a proxy for team strength (which is cheating, but oh well)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Step 0: Outcome variable"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df['home_win'] = df.home_points > df.away_points"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Step 1: Calculate Win %\n",
    "\n",
    "\n",
    "*Get each team's win percent as home and away*\n",
    "\n",
    "- name the resulting DataFrame `wins`\n",
    "- The output should be a DataFrame were\n",
    "  * The index is a MultiIndex of `team`, `is_home` pairs\n",
    "  * The columns are `win_pct`, `n_wins`, `n_games`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is our final goal, but we have a few intermediate stages:\n",
    "\n",
    "<table border=\"1\" class=\"dataframe\">\n",
    "  <thead>\n",
    "    <tr style=\"text-align: right;\">\n",
    "      <th></th>\n",
    "      <th></th>\n",
    "      <th>n_wins</th>\n",
    "      <th>n_games</th>\n",
    "      <th>win_pct</th>\n",
    "    </tr>\n",
    "    <tr>\n",
    "      <th>team</th>\n",
    "      <th>is_home</th>\n",
    "      <th></th>\n",
    "      <th></th>\n",
    "      <th></th>\n",
    "    </tr>\n",
    "  </thead>\n",
    "  <tbody>\n",
    "    <tr>\n",
    "      <th rowspan=\"2\" valign=\"top\">Atlanta Hawks</th>\n",
    "      <th>away_team</th>\n",
    "      <td>21.0</td>\n",
    "      <td>41</td>\n",
    "      <td>0.512195</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "      <th>home_team</th>\n",
    "      <td>27.0</td>\n",
    "      <td>41</td>\n",
    "      <td>0.658537</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "      <th rowspan=\"2\" valign=\"top\">Boston Celtics</th>\n",
    "      <th>away_team</th>\n",
    "      <td>20.0</td>\n",
    "      <td>41</td>\n",
    "      <td>0.487805</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "      <th>home_team</th>\n",
    "      <td>28.0</td>\n",
    "      <td>41</td>\n",
    "      <td>0.682927</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "      <th>Brooklyn Nets</th>\n",
    "      <th>away_team</th>\n",
    "      <td>7.0</td>\n",
    "      <td>41</td>\n",
    "      <td>0.170732</td>\n",
    "    </tr>\n",
    "  </tbody>\n",
    "</table>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.1: `pd.melt` `df` like before\n",
    "\n",
    "Before we melted down for reset, this time we have home_win.\n",
    "Get a DataFrame with one row per `(game, team)` pair that includes the boolean `home_win` and whether that team was `home_team` or `away_team`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "id_vars = ...  # this changed, we have an extra column\n",
    "value_vars = ...\n",
    "value_name = 'team'\n",
    "var_name = 'home_or_away'\n",
    "games = pd.melt(df.reset_index(), id_vars=id_vars, value_vars=value_vars,\n",
    "                var_name=var_name, value_name=value_name)\n",
    "games.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%load -r 4:14 solutions/solutions_tidy.py"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.2: Assign a new column indicating whether the team in that row won.\n",
    "\n",
    "Hint: Must either have\n",
    "\n",
    "- `games.home_win` be True and `games.home_or_away == 'home_team'`\n",
    "- `games.home_win` be False and `games.home_or_away == 'away_team'`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Goal:\n",
    "\n",
    "<table border=\"1\" class=\"dataframe\">\n",
    "  <thead>\n",
    "    <tr style=\"text-align: right;\">\n",
    "      <th></th>\n",
    "      <th>game_id</th>\n",
    "      <th>date</th>\n",
    "      <th>home_win</th>\n",
    "      <th>home_or_away</th>\n",
    "      <th>team</th>\n",
    "      <th>win</th>\n",
    "    </tr>\n",
    "  </thead>\n",
    "  <tbody>\n",
    "    <tr>\n",
    "      <th>0</th>\n",
    "      <td>1</td>\n",
    "      <td>2015-10-27</td>\n",
    "      <td>False</td>\n",
    "      <td>home_team</td>\n",
    "      <td>Atlanta Hawks</td>\n",
    "      <td>False</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "      <th>1</th>\n",
    "      <td>2</td>\n",
    "      <td>2015-10-27</td>\n",
    "      <td>True</td>\n",
    "      <td>home_team</td>\n",
    "      <td>Chicago Bulls</td>\n",
    "      <td>True</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "      <th>2</th>\n",
    "      <td>3</td>\n",
    "      <td>2015-10-27</td>\n",
    "      <td>True</td>\n",
    "      <td>home_team</td>\n",
    "      <td>Golden State Warriors</td>\n",
    "      <td>True</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "      <th>3</th>\n",
    "      <td>4</td>\n",
    "      <td>2015-10-28</td>\n",
    "      <td>True</td>\n",
    "      <td>home_team</td>\n",
    "      <td>Boston Celtics</td>\n",
    "      <td>True</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "      <th>4</th>\n",
    "      <td>5</td>\n",
    "      <td>2015-10-28</td>\n",
    "      <td>False</td>\n",
    "      <td>home_team</td>\n",
    "      <td>Brooklyn Nets</td>\n",
    "      <td>False</td>\n",
    "    </tr>\n",
    "  </tbody>\n",
    "</table>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "games['win'] = ..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%load -r 15:18 solutions/solutions_tidy.py"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.3: Aggregate\n",
    "\n",
    "Use a `groupby` to get the\n",
    "\n",
    "- number of wins\n",
    "- number of total games\n",
    "- win percent\n",
    "\n",
    "For each team at home and away.\n",
    "\n",
    "Hint: You can control the output of `.agg()` like\n",
    "\n",
    "```python\n",
    "groupby['column'].agg({\n",
    "    output_name1: aggfunc1,\n",
    "    output_name2: aggfunc2,\n",
    "})\n",
    "```\n",
    "\n",
    "For example `{'n_wins': 'sum', 'n_games': 'count', ... }`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Your solution\n",
    "wins = games.groupby(...)['win'].agg(...)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%load -r 19:25 solutions/solutions_tidy.py"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Quick vis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "(wins.win_pct\n",
    "    .unstack()\n",
    "    .assign(**{'Home Win % - Away %': lambda x: x.home_team - x.away_team,\n",
    "               'Overall %': lambda x: (x.home_team + x.away_team) / 2})\n",
    "     .pipe((sns.regplot, 'data'), x='Overall %', y='Home Win % - Away %')\n",
    ")\n",
    "sns.despine()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "g = sns.FacetGrid(wins.reset_index(), hue='team', size=10, aspect=.5, palette=['k'])\n",
    "g.map(sns.pointplot, 'home_or_away', 'win_pct').set(ylim=(0, 1));"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Step 2: Calculate the actual win percent.\n",
    "\n",
    "Use `wins` to get `win_percent`.\n",
    "\n",
    "Output should be a `Series` where the index is the team name and the values is the win percent (home or away).\n",
    "\n",
    "<table border=\"1\" class=\"dataframe\">\n",
    "  <thead>\n",
    "    <tr style=\"text-align: right;\">\n",
    "      <th></th>\n",
    "      <th>win_percent</th>\n",
    "    </tr>\n",
    "    <tr>\n",
    "      <th>team</th>\n",
    "      <th></th>\n",
    "    </tr>\n",
    "  </thead>\n",
    "  <tbody>\n",
    "    <tr>\n",
    "      <th>Atlanta Hawks</th>\n",
    "      <td>0.585366</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "      <th>Boston Celtics</th>\n",
    "      <td>0.585366</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "      <th>Brooklyn Nets</th>\n",
    "      <td>0.256098</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "      <th>Charlotte Hornets</th>\n",
    "      <td>0.585366</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "      <th>Chicago Bulls</th>\n",
    "      <td>0.512195</td>\n",
    "    </tr>\n",
    "  </tbody>\n",
    "</table>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Your code here\n",
    "win_percent = ..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%load -r 26:35 solutions/solutions_tidy.py"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Step 3: Incorporate the `win_percent`  values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Bring the `strength` valuess into `df` for each team, for each game. Assign them to `away_strength` and `home_strength` in the `df` DataFrame.\n",
    "\n",
    "Hint: Lookup `pd.Series.map?`\n",
    "\n",
    "Also calculate `point_diff` (home - away) and `rest_diff` (home - away)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "win_percent"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Your code here\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%load -r 36:43 solutions/solutions_tidy.py"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can fit the model"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import statsmodels.formula.api as sm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "mod = sm.logit('home_win ~ home_strength + away_strength + home_rest + away_rest', df)\n",
    "res = mod.fit()\n",
    "res.summary()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "# Recap\n",
    "\n",
    "- Tidy data: one row per observation\n",
    "    - melt / stack: wide to long\n",
    "    - pivot_table / unstack: long to wide"
   ]
  }
 ],
 "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.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}