{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "# Data Aggregation and Group Operations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "PREVIOUS_MAX_ROWS = pd.options.display.max_rows\n",
    "pd.options.display.max_rows = 20\n",
    "np.random.seed(12345)\n",
    "import matplotlib.pyplot as plt\n",
    "plt.rc('figure', figsize=(10, 6))\n",
    "np.set_printoptions(precision=4, suppress=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "## GroupBy Mechanics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "df = pd.DataFrame({'key1' : ['a', 'a', 'b', 'b', 'a'],\n",
    "                   'key2' : ['one', 'two', 'one', 'two', 'one'],\n",
    "                   'data1' : np.random.randn(5),\n",
    "                   'data2' : np.random.randn(5)})\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "grouped = df['data1'].groupby(df['key1'])\n",
    "grouped"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "grouped.mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "means = df['data1'].groupby([df['key1'], df['key2']]).mean()\n",
    "means"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "means.unstack()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "states = np.array(['Ohio', 'California', 'California', 'Ohio', 'Ohio'])\n",
    "years = np.array([2005, 2005, 2006, 2005, 2006])\n",
    "df['data1'].groupby([states, years]).mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "df.groupby('key1').mean()\n",
    "df.groupby(['key1', 'key2']).mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "df.groupby(['key1', 'key2']).size()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Iterating Over Groups"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "for name, group in df.groupby('key1'):\n",
    "    print(name)\n",
    "    print(group)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "for (k1, k2), group in df.groupby(['key1', 'key2']):\n",
    "    print((k1, k2))\n",
    "    print(group)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "pieces = dict(list(df.groupby('key1')))\n",
    "pieces['b']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "df.dtypes\n",
    "grouped = df.groupby(df.dtypes, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "for dtype, group in grouped:\n",
    "    print(dtype)\n",
    "    print(group)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Selecting a Column or Subset of Columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "df.groupby('key1')['data1']\n",
    "df.groupby('key1')[['data2']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "df['data1'].groupby(df['key1'])\n",
    "df[['data2']].groupby(df['key1'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "df.groupby(['key1', 'key2'])[['data2']].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "s_grouped = df.groupby(['key1', 'key2'])['data2']\n",
    "s_grouped\n",
    "s_grouped.mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Grouping with Dicts and Series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "people = pd.DataFrame(np.random.randn(5, 5),\n",
    "                      columns=['a', 'b', 'c', 'd', 'e'],\n",
    "                      index=['Joe', 'Steve', 'Wes', 'Jim', 'Travis'])\n",
    "people.iloc[2:3, [1, 2]] = np.nan # Add a few NA values\n",
    "people"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "mapping = {'a': 'red', 'b': 'red', 'c': 'blue',\n",
    "           'd': 'blue', 'e': 'red', 'f' : 'orange'}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "by_column = people.groupby(mapping, axis=1)\n",
    "by_column.sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "map_series = pd.Series(mapping)\n",
    "map_series\n",
    "people.groupby(map_series, axis=1).count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Grouping with Functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "people.groupby(len).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "key_list = ['one', 'one', 'one', 'two', 'two']\n",
    "people.groupby([len, key_list]).min()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Grouping by Index Levels"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "columns = pd.MultiIndex.from_arrays([['US', 'US', 'US', 'JP', 'JP'],\n",
    "                                    [1, 3, 5, 1, 3]],\n",
    "                                    names=['cty', 'tenor'])\n",
    "hier_df = pd.DataFrame(np.random.randn(4, 5), columns=columns)\n",
    "hier_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "hier_df.groupby(level='cty', axis=1).count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "## Data Aggregation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "df\n",
    "grouped = df.groupby('key1')\n",
    "grouped['data1'].quantile(0.9)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "def peak_to_peak(arr):\n",
    "    return arr.max() - arr.min()\n",
    "grouped.agg(peak_to_peak)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "grouped.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Column-Wise and Multiple Function Application"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "tips = pd.read_csv('examples/tips.csv')\n",
    "# Add tip percentage of total bill\n",
    "tips['tip_pct'] = tips['tip'] / tips['total_bill']\n",
    "tips[:6]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "grouped = tips.groupby(['day', 'smoker'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "grouped_pct = grouped['tip_pct']\n",
    "grouped_pct.agg('mean')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "grouped_pct.agg(['mean', 'std', peak_to_peak])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "grouped_pct.agg([('foo', 'mean'), ('bar', np.std)])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "functions = ['count', 'mean', 'max']\n",
    "result = grouped['tip_pct', 'total_bill'].agg(functions)\n",
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "result['tip_pct']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "ftuples = [('Durchschnitt', 'mean'), ('Abweichung', np.var)]\n",
    "grouped['tip_pct', 'total_bill'].agg(ftuples)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "grouped.agg({'tip' : np.max, 'size' : 'sum'})\n",
    "grouped.agg({'tip_pct' : ['min', 'max', 'mean', 'std'],\n",
    "             'size' : 'sum'})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Returning Aggregated Data Without Row Indexes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "tips.groupby(['day', 'smoker'], as_index=False).mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "## Apply: General split-apply-combine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "def top(df, n=5, column='tip_pct'):\n",
    "    return df.sort_values(by=column)[-n:]\n",
    "top(tips, n=6)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "tips.groupby('smoker').apply(top)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "tips.groupby(['smoker', 'day']).apply(top, n=1, column='total_bill')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "result = tips.groupby('smoker')['tip_pct'].describe()\n",
    "result\n",
    "result.unstack('smoker')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "f = lambda x: x.describe()\n",
    "grouped.apply(f)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Suppressing the Group Keys"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "tips.groupby('smoker', group_keys=False).apply(top)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Quantile and Bucket Analysis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "frame = pd.DataFrame({'data1': np.random.randn(1000),\n",
    "                      'data2': np.random.randn(1000)})\n",
    "quartiles = pd.cut(frame.data1, 4)\n",
    "quartiles[:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "def get_stats(group):\n",
    "    return {'min': group.min(), 'max': group.max(),\n",
    "            'count': group.count(), 'mean': group.mean()}\n",
    "grouped = frame.data2.groupby(quartiles)\n",
    "grouped.apply(get_stats).unstack()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# Return quantile numbers\n",
    "grouping = pd.qcut(frame.data1, 10, labels=False)\n",
    "grouped = frame.data2.groupby(grouping)\n",
    "grouped.apply(get_stats).unstack()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Example: Filling Missing Values with Group-Specific       Values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "s = pd.Series(np.random.randn(6))\n",
    "s[::2] = np.nan\n",
    "s\n",
    "s.fillna(s.mean())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "states = ['Ohio', 'New York', 'Vermont', 'Florida',\n",
    "          'Oregon', 'Nevada', 'California', 'Idaho']\n",
    "group_key = ['East'] * 4 + ['West'] * 4\n",
    "data = pd.Series(np.random.randn(8), index=states)\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "data[['Vermont', 'Nevada', 'Idaho']] = np.nan\n",
    "data\n",
    "data.groupby(group_key).mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "fill_mean = lambda g: g.fillna(g.mean())\n",
    "data.groupby(group_key).apply(fill_mean)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "fill_values = {'East': 0.5, 'West': -1}\n",
    "fill_func = lambda g: g.fillna(fill_values[g.name])\n",
    "data.groupby(group_key).apply(fill_func)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Example: Random Sampling and Permutation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# Hearts, Spades, Clubs, Diamonds\n",
    "suits = ['H', 'S', 'C', 'D']\n",
    "card_val = (list(range(1, 11)) + [10] * 3) * 4\n",
    "base_names = ['A'] + list(range(2, 11)) + ['J', 'K', 'Q']\n",
    "cards = []\n",
    "for suit in ['H', 'S', 'C', 'D']:\n",
    "    cards.extend(str(num) + suit for num in base_names)\n",
    "\n",
    "deck = pd.Series(card_val, index=cards)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "deck[:13]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "def draw(deck, n=5):\n",
    "    return deck.sample(n)\n",
    "draw(deck)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "get_suit = lambda card: card[-1] # last letter is suit\n",
    "deck.groupby(get_suit).apply(draw, n=2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "deck.groupby(get_suit, group_keys=False).apply(draw, n=2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Example: Group Weighted Average and Correlation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "df = pd.DataFrame({'category': ['a', 'a', 'a', 'a',\n",
    "                                'b', 'b', 'b', 'b'],\n",
    "                   'data': np.random.randn(8),\n",
    "                   'weights': np.random.rand(8)})\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "grouped = df.groupby('category')\n",
    "get_wavg = lambda g: np.average(g['data'], weights=g['weights'])\n",
    "grouped.apply(get_wavg)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "close_px = pd.read_csv('examples/stock_px_2.csv', parse_dates=True,\n",
    "                       index_col=0)\n",
    "close_px.info()\n",
    "close_px[-4:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "spx_corr = lambda x: x.corrwith(x['SPX'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "rets = close_px.pct_change().dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "get_year = lambda x: x.year\n",
    "by_year = rets.groupby(get_year)\n",
    "by_year.apply(spx_corr)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "by_year.apply(lambda g: g['AAPL'].corr(g['MSFT']))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Example: Group-Wise Linear Regression"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "import statsmodels.api as sm\n",
    "def regress(data, yvar, xvars):\n",
    "    Y = data[yvar]\n",
    "    X = data[xvars]\n",
    "    X['intercept'] = 1.\n",
    "    result = sm.OLS(Y, X).fit()\n",
    "    return result.params"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "by_year.apply(regress, 'AAPL', ['SPX'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "## Pivot Tables and Cross-Tabulation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "tips.pivot_table(index=['day', 'smoker'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "tips.pivot_table(['tip_pct', 'size'], index=['time', 'day'],\n",
    "                 columns='smoker')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "tips.pivot_table(['tip_pct', 'size'], index=['time', 'day'],\n",
    "                 columns='smoker', margins=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "tips.pivot_table('tip_pct', index=['time', 'smoker'], columns='day',\n",
    "                 aggfunc=len, margins=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "tips.pivot_table('tip_pct', index=['time', 'size', 'smoker'],\n",
    "                 columns='day', aggfunc='mean', fill_value=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Cross-Tabulations: Crosstab"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "from io import StringIO\n",
    "data = \"\"\"\\\n",
    "Sample  Nationality  Handedness\n",
    "1   USA  Right-handed\n",
    "2   Japan    Left-handed\n",
    "3   USA  Right-handed\n",
    "4   Japan    Right-handed\n",
    "5   Japan    Left-handed\n",
    "6   Japan    Right-handed\n",
    "7   USA  Right-handed\n",
    "8   USA  Left-handed\n",
    "9   Japan    Right-handed\n",
    "10  USA  Right-handed\"\"\"\n",
    "data = pd.read_table(StringIO(data), sep='\\s+')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "pd.crosstab(data.Nationality, data.Handedness, margins=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "pd.crosstab([tips.time, tips.day], tips.smoker, margins=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "pd.options.display.max_rows = PREVIOUS_MAX_ROWS"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "## Conclusion"
   ]
  }
 ],
 "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.6.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}