<jupyter_start><jupyter_text>Tidy Data> Structuring datasets to facilitate analysis [(Wickham 2014)](http://www.jstatsoft.org/v59/i10/paper)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. The RulesIn a tidy dataset...1. Each variable forms a column2. Each observation forms a rowConsistently following these rules when writing your data-processing functions makes the whole experience smoother.We'll cover a few methods that help you tidy your data.<jupyter_code>import numpy as np
import pandas as pd
import seaborn as sns
import matplotlib.pyplot as plt

sns.set(style="white", context="talk")
plt.rcParams['figure.figsize'] = 12, 8
pd.options.display.max_rows = 10
%matplotlib inline<jupyter_output><empty_output><jupyter_text>Earlier, I fetched some data```pythontables = pd.read_html("http://www.basketball-reference.com/leagues/NBA_2016_games.html")games = tables[0]games.to_csv('data/games.csv', index=False)```<jupyter_code>pd.read_html?
!head -n  5 data/games.csv<jupyter_output><empty_output><jupyter_text>So the data is roughly like| Date        | Visitor Team | Visitor Points | Home Team | Home Points || ----------- | ------------ | -------------- | --------- | ----------- || 2015-10-07  | Detroit      | 106            | Atlanta   | 94          || ...         | ...          | ...            | ...       | ...         |Plus some extra junk. [The Question](http://stackoverflow.com/questions/22695680/python-pandas-timedelta-specific-rows):> **How many days of rest did each team get between each game?**Whether or not your dataset is tidy depends on your question. Given our question, what is an observation?---Show AnswerAn observation is a (team, game) pair. So no, we don't have a tidy dataset.A tidy dataset would be likeDateTeamHome / AwayPoints2015-10-07DetroitAway1062015-10-07AtlantaHome94............<jupyter_code>column_names = {'Date': 'date', 'Start (ET)': 'start',
                'Unamed: 2': 'box', 'Visitor/Neutral': 'away_team', 
                'PTS': 'away_points', 'Home/Neutral': 'home_team',
                'PTS.1': 'home_points', 'Unamed: 7': 'n_ot'}

games = (
    pd.read_csv("data/games.csv")
      .rename(columns=column_names)
      .dropna(thresh=4)
      [['date', 'away_team', 'away_points', 'home_team', 'home_points']]
      .assign(date=lambda x: pd.to_datetime(x['date'], format='%a, %b %d, %Y'))
      .set_index('date', append=True)
      .rename_axis(["game_id", "date"])
      .sort_index()
)
games.head()<jupyter_output><empty_output><jupyter_text>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.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.<jupyter_code>tidy = pd.melt(games.reset_index(),
               id_vars=['game_id', 'date'], value_vars=['away_team', 'home_team'],
               value_name='team').sort_values(['game_id', 'date'])

tidy.head()<jupyter_output><empty_output><jupyter_text>Now the translation from question to operation is direct:<jupyter_code># How many days of rest for each team
# For each team...  get number of days between games
tidy.groupby('team')['date'].diff().dt.days - 1
tidy['rest'] = tidy.groupby('team').date.diff().dt.days - 1
tidy.dropna().head()<jupyter_output><empty_output><jupyter_text>You can "invert" a `melt` with `pd.pivot_table`<jupyter_code>by_game = pd.pivot_table(tidy, values='rest',
                         index=['game_id', 'date'],
                         columns='variable').rename(
    columns={'away_team': 'away_rest', 'home_team': 'home_rest'}
)
by_game.columns.name = None

by_game.dropna().head()<jupyter_output><empty_output><jupyter_text>`concat` will merge two dataframes, expanding an `axis`, while aligning on the other axis.<jupyter_code>df = pd.concat([games, by_game], axis='columns')
df.head()
g = sns.FacetGrid(data=tidy.dropna(), col='team', col_wrap=5, hue='team')
g.map(sns.barplot, "variable", "rest");
delta = (df['home_rest'] - df['away_rest']).dropna().astype(int)
(delta.value_counts()
    .reindex(np.arange(delta.min(), delta.max() + 1), fill_value=0)
    .sort_index().plot(kind='bar', color='k', width=.9, rot=0, figsize=(12, 6)))
sns.despine()
plt.xlabel("Difference in Rest (home - away)")
plt.grid(axis='y');<jupyter_output><empty_output><jupyter_text>Stack / Unstack Not all APIs expect tidy data, so you need to convert between "wide" and "long" form data.<jupyter_code>rest = (tidy.groupby(['date', 'variable'])
            .rest.mean()
            .dropna())
rest.head()<jupyter_output><empty_output><jupyter_text>`rest` is in "long" form. `DataFrame.plot` for example, expects wide form data.<jupyter_code>rest.unstack().head()
rest.unstack().rolling(14).mean().plot()<jupyter_output><empty_output><jupyter_text>Mini Project: Home Court Advantage?What's the effect (in terms of probability to win) of beingthe home team?What are the components of the advantage?We'll fit a Logistic regression like`home_win ~ home_strength + away_strength + home_rest + away_rest`Our final dataframe will have one row per game (like `df`).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).  Step 0: Outcome variable<jupyter_code>df['home_win'] = df.home_points > df.away_points<jupyter_output><empty_output><jupyter_text>Step 1: Calculate Win %*Get each team's win percent as home and away*- name the resulting DataFrame `wins`- The output should be a DataFrame were  * The index is a MultiIndex of `team`, `is_home` pairs  * The columns are `win_pct`, `n_wins`, `n_games` This is our final goal, but we have a few intermediate stages:                        n_wins      n_games      win_pct              team      is_home                                    Atlanta Hawks      away_team      21.0      41      0.512195              home_team      27.0      41      0.658537              Boston Celtics      away_team      20.0      41      0.487805              home_team      28.0      41      0.682927              Brooklyn Nets      away_team      7.0      41      0.170732        1.1: `pd.melt` `df` like beforeBefore we melted down for reset, this time we have home_win.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`.<jupyter_code>id_vars = ...  # this changed, we have an extra column
value_vars = ...
value_name = 'team'
var_name = 'home_or_away'
games = pd.melt(df.reset_index(), id_vars=id_vars, value_vars=value_vars,
                var_name=var_name, value_name=value_name)
games.head()
%load -r 4:14 solutions/solutions_tidy.py<jupyter_output><empty_output><jupyter_text>1.2: Assign a new column indicating whether the team in that row won.Hint: Must either have- `games.home_win` be True and `games.home_or_away == 'home_team'`- `games.home_win` be False and `games.home_or_away == 'away_team'` Goal:                  game_id      date      home_win      home_or_away      team      win                  0      1      2015-10-27      False      home_team      Atlanta Hawks      False              1      2      2015-10-27      True      home_team      Chicago Bulls      True              2      3      2015-10-27      True      home_team      Golden State Warriors      True              3      4      2015-10-28      True      home_team      Boston Celtics      True              4      5      2015-10-28      False      home_team      Brooklyn Nets      False<jupyter_code>games['win'] = ...
%load -r 15:18 solutions/solutions_tidy.py<jupyter_output><empty_output><jupyter_text>1.3: AggregateUse a `groupby` to get the- number of wins- number of total games- win percentFor each team at home and away.Hint: You can control the output of `.agg()` like```pythongroupby['column'].agg({    output_name1: aggfunc1,    output_name2: aggfunc2,})```For example `{'n_wins': 'sum', 'n_games': 'count', ... }`<jupyter_code># Your solution
wins = games.groupby(...)['win'].agg(...)
%load -r 19:25 solutions/solutions_tidy.py<jupyter_output><empty_output><jupyter_text>Quick vis<jupyter_code>(wins.win_pct
    .unstack()
    .assign(**{'Home Win % - Away %': lambda x: x.home_team - x.away_team,
               'Overall %': lambda x: (x.home_team + x.away_team) / 2})
     .pipe((sns.regplot, 'data'), x='Overall %', y='Home Win % - Away %')
)
sns.despine()
g = sns.FacetGrid(wins.reset_index(), hue='team', size=10, aspect=.5, palette=['k'])
g.map(sns.pointplot, 'home_or_away', 'win_pct').set(ylim=(0, 1));<jupyter_output><empty_output><jupyter_text>Step 2: Calculate the actual win percent.Use `wins` to get `win_percent`.Output should be a `Series` where the index is the team name and the values is the win percent (home or away).                  win_percent              team                        Atlanta Hawks      0.585366              Boston Celtics      0.585366              Brooklyn Nets      0.256098              Charlotte Hornets      0.585366              Chicago Bulls      0.512195<jupyter_code># Your code here
win_percent = ...
%load -r 26:35 solutions/solutions_tidy.py<jupyter_output><empty_output><jupyter_text>Step 3: Incorporate the `win_percent`  values Bring the `strength` valuess into `df` for each team, for each game. Assign them to `away_strength` and `home_strength` in the `df` DataFrame.Hint: Lookup `pd.Series.map?`Also calculate `point_diff` (home - away) and `rest_diff` (home - away).<jupyter_code>win_percent
# Your code here

%load -r 36:43 solutions/solutions_tidy.py<jupyter_output><empty_output><jupyter_text>Now we can fit the model<jupyter_code>import statsmodels.formula.api as sm
mod = sm.logit('home_win ~ home_strength + away_strength + home_rest + away_rest', df)
res = mod.fit()
res.summary()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12916913360963095, 'fraction_numerical': 0.02585356226564042, 'mean_word_length': 3.2926725963574754, 'pattern_counts': {'":': 0, '<': 69, '<?xml version=': 0, '>': 72, 'https://': 0, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 5, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '17', 'dir': 'jupyter-structured-clean-dedup', 'id': '198746', 'license': 'CC-BY-3.0', 'n_tokens_mistral': 3461, 'n_tokens_neox': 3218, 'n_words': 924, 'path': '5-TidyData.ipynb', 'repo_name': 'upeshpv/pydata-pandas-chi-h2t', 'size': '10134'}