ch09.ipynb

{
 "metadata": {
  "name": "",
  "signature": "sha256:884d45c0ec888c81923e3e89bc579b89c2b34c7a37578b0a064bb5dcfd9ffa29"
 },
 "nbformat": 3,
 "nbformat_minor": 0,
 "worksheets": [
  {
   "cells": [
    {
     "cell_type": "heading",
     "level": 1,
     "metadata": {},
     "source": [
      "Data Aggregation and Group Operations"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "from __future__ import division\n",
      "from numpy.random import randn\n",
      "import numpy as np\n",
      "import os\n",
      "import matplotlib.pyplot as plt\n",
      "np.random.seed(12345)\n",
      "plt.rc('figure', figsize=(10, 6))\n",
      "from pandas import Series, DataFrame\n",
      "import pandas as pd\n",
      "np.set_printoptions(precision=4)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "%cd ../book_scripts"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "pd.options.display.notebook_repr_html = False"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "%matplotlib inline"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "GroupBy mechanics"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df = 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"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "grouped = df['data1'].groupby(df['key1'])\n",
      "grouped"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "grouped.mean()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "means = df['data1'].groupby([df['key1'], df['key2']]).mean()\n",
      "means"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "means.unstack()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "states = np.array(['Ohio', 'California', 'California', 'Ohio', 'Ohio'])\n",
      "years = np.array([2005, 2005, 2006, 2005, 2006])\n",
      "df['data1'].groupby([states, years]).mean()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.groupby('key1').mean()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.groupby(['key1', 'key2']).mean()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.groupby(['key1', 'key2']).size()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "heading",
     "level": 3,
     "metadata": {},
     "source": [
      "Iterating over groups"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "for name, group in df.groupby('key1'):\n",
      "    print(name)\n",
      "    print(group)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "for (k1, k2), group in df.groupby(['key1', 'key2']):\n",
      "    print((k1, k2))\n",
      "    print(group)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "pieces = dict(list(df.groupby('key1')))\n",
      "pieces['b']"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.dtypes"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "grouped = df.groupby(df.dtypes, axis=1)\n",
      "dict(list(grouped))"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "heading",
     "level": 3,
     "metadata": {},
     "source": [
      "Selecting a column or subset of columns"
     ]
    },
    {
     "cell_type": "raw",
     "metadata": {},
     "source": [
      "df.groupby('key1')['data1']\n",
      "df.groupby('key1')[['data2']]"
     ]
    },
    {
     "cell_type": "raw",
     "metadata": {},
     "source": [
      "df['data1'].groupby(df['key1'])\n",
      "df[['data2']].groupby(df['key1'])"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.groupby(['key1', 'key2'])[['data2']].mean()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "s_grouped = df.groupby(['key1', 'key2'])['data2']\n",
      "s_grouped"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "s_grouped.mean()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "heading",
     "level": 3,
     "metadata": {},
     "source": [
      "Grouping with dicts and Series"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "people = DataFrame(np.random.randn(5, 5),\n",
      "                   columns=['a', 'b', 'c', 'd', 'e'],\n",
      "                   index=['Joe', 'Steve', 'Wes', 'Jim', 'Travis'])\n",
      "people.ix[2:3, ['b', 'c']] = np.nan # Add a few NA values\n",
      "people"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "mapping = {'a': 'red', 'b': 'red', 'c': 'blue',\n",
      "           'd': 'blue', 'e': 'red', 'f' : 'orange'}"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "by_column = people.groupby(mapping, axis=1)\n",
      "by_column.sum()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "map_series = Series(mapping)\n",
      "map_series"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "people.groupby(map_series, axis=1).count()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "heading",
     "level": 3,
     "metadata": {},
     "source": [
      "Grouping with functions"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "people.groupby(len).sum()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "key_list = ['one', 'one', 'one', 'two', 'two']\n",
      "people.groupby([len, key_list]).min()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "heading",
     "level": 3,
     "metadata": {},
     "source": [
      "Grouping by index levels"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "columns = pd.MultiIndex.from_arrays([['US', 'US', 'US', 'JP', 'JP'],\n",
      "                                    [1, 3, 5, 1, 3]], names=['cty', 'tenor'])\n",
      "hier_df = DataFrame(np.random.randn(4, 5), columns=columns)\n",
      "hier_df"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "hier_df.groupby(level='cty', axis=1).count()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "Data aggregation"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "grouped = df.groupby('key1')\n",
      "grouped['data1'].quantile(0.9)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "def peak_to_peak(arr):\n",
      "    return arr.max() - arr.min()\n",
      "grouped.agg(peak_to_peak)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "grouped.describe()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "tips = pd.read_csv('ch08/tips.csv')\n",
      "# Add tip percentage of total bill\n",
      "tips['tip_pct'] = tips['tip'] / tips['total_bill']\n",
      "tips[:6]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "heading",
     "level": 3,
     "metadata": {},
     "source": [
      "Column-wise and multiple function application"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "grouped = tips.groupby(['sex', 'smoker'])"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "grouped_pct = grouped['tip_pct']\n",
      "grouped_pct.agg('mean')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "grouped_pct.agg(['mean', 'std', peak_to_peak])"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "grouped_pct.agg([('foo', 'mean'), ('bar', np.std)])"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "functions = ['count', 'mean', 'max']\n",
      "result = grouped['tip_pct', 'total_bill'].agg(functions)\n",
      "result"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "result['tip_pct']"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "ftuples = [('Durchschnitt', 'mean'), ('Abweichung', np.var)]\n",
      "grouped['tip_pct', 'total_bill'].agg(ftuples)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "grouped.agg({'tip' : np.max, 'size' : 'sum'})"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "grouped.agg({'tip_pct' : ['min', 'max', 'mean', 'std'],\n",
      "             'size' : 'sum'})"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "heading",
     "level": 3,
     "metadata": {},
     "source": [
      "Returning aggregated data in \"unindexed\" form"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "tips.groupby(['sex', 'smoker'], as_index=False).mean()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "Group-wise operations and transformations"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "k1_means = df.groupby('key1').mean().add_prefix('mean_')\n",
      "k1_means"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "pd.merge(df, k1_means, left_on='key1', right_index=True)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "key = ['one', 'two', 'one', 'two', 'one']\n",
      "people.groupby(key).mean()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "people.groupby(key).transform(np.mean)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "def demean(arr):\n",
      "    return arr - arr.mean()\n",
      "demeaned = people.groupby(key).transform(demean)\n",
      "demeaned"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "demeaned.groupby(key).mean()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "heading",
     "level": 3,
     "metadata": {},
     "source": [
      "Apply: General split-apply-combine"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "def top(df, n=5, column='tip_pct'):\n",
      "    return df.sort_index(by=column)[-n:]\n",
      "top(tips, n=6)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "tips.groupby('smoker').apply(top)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "tips.groupby(['smoker', 'day']).apply(top, n=1, column='total_bill')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "result = tips.groupby('smoker')['tip_pct'].describe()\n",
      "result"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "result.unstack('smoker')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "raw",
     "metadata": {},
     "source": [
      "f = lambda x: x.describe()\n",
      "grouped.apply(f)"
     ]
    },
    {
     "cell_type": "heading",
     "level": 4,
     "metadata": {},
     "source": [
      "Suppressing the group keys"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "tips.groupby('smoker', group_keys=False).apply(top)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "heading",
     "level": 3,
     "metadata": {},
     "source": [
      "Quantile and bucket analysis"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "frame = DataFrame({'data1': np.random.randn(1000),\n",
      "                   'data2': np.random.randn(1000)})\n",
      "factor = pd.cut(frame.data1, 4)\n",
      "factor[:10]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "def get_stats(group):\n",
      "    return {'min': group.min(), 'max': group.max(),\n",
      "            'count': group.count(), 'mean': group.mean()}\n",
      "\n",
      "grouped = frame.data2.groupby(factor)\n",
      "grouped.apply(get_stats).unstack()\n",
      "\n",
      "#ADAPT the output is not sorted in the book while this is the case now (swap first two lines)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Return quantile numbers\n",
      "grouping = pd.qcut(frame.data1, 10, labels=False)\n",
      "\n",
      "grouped = frame.data2.groupby(grouping)\n",
      "grouped.apply(get_stats).unstack()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "heading",
     "level": 3,
     "metadata": {},
     "source": [
      "Example: Filling missing values with group-specific values"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "s = Series(np.random.randn(6))\n",
      "s[::2] = np.nan\n",
      "s"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "s.fillna(s.mean())"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "states = ['Ohio', 'New York', 'Vermont', 'Florida',\n",
      "          'Oregon', 'Nevada', 'California', 'Idaho']\n",
      "group_key = ['East'] * 4 + ['West'] * 4\n",
      "data = Series(np.random.randn(8), index=states)\n",
      "data[['Vermont', 'Nevada', 'Idaho']] = np.nan\n",
      "data"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "data.groupby(group_key).mean()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "fill_mean = lambda g: g.fillna(g.mean())\n",
      "data.groupby(group_key).apply(fill_mean)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "fill_values = {'East': 0.5, 'West': -1}\n",
      "fill_func = lambda g: g.fillna(fill_values[g.name])\n",
      "\n",
      "data.groupby(group_key).apply(fill_func)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "heading",
     "level": 3,
     "metadata": {},
     "source": [
      "Example: Random sampling and permutation"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Hearts, Spades, Clubs, Diamonds\n",
      "suits = ['H', 'S', 'C', 'D']\n",
      "card_val = (range(1, 11) + [10] * 3) * 4\n",
      "base_names = ['A'] + 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 = Series(card_val, index=cards)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "deck[:13]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "def draw(deck, n=5):\n",
      "    return deck.take(np.random.permutation(len(deck))[:n])\n",
      "draw(deck)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "get_suit = lambda card: card[-1] # last letter is suit\n",
      "deck.groupby(get_suit).apply(draw, n=2)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# alternatively\n",
      "deck.groupby(get_suit, group_keys=False).apply(draw, n=2)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "heading",
     "level": 3,
     "metadata": {},
     "source": [
      "Example: Group weighted average and correlation"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df = DataFrame({'category': ['a', 'a', 'a', 'a', 'b', 'b', 'b', 'b'],\n",
      "                'data': np.random.randn(8),\n",
      "                'weights': np.random.rand(8)})\n",
      "df"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "grouped = df.groupby('category')\n",
      "get_wavg = lambda g: np.average(g['data'], weights=g['weights'])\n",
      "grouped.apply(get_wavg)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "close_px = pd.read_csv('ch09/stock_px.csv', parse_dates=True, index_col=0)\n",
      "close_px.info()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "close_px[-4:]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "rets = close_px.pct_change().dropna()\n",
      "spx_corr = lambda x: x.corrwith(x['SPX'])\n",
      "by_year = rets.groupby(lambda x: x.year)\n",
      "by_year.apply(spx_corr)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Annual correlation of Apple with Microsoft\n",
      "by_year.apply(lambda g: g['AAPL'].corr(g['MSFT']))"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "heading",
     "level": 3,
     "metadata": {},
     "source": [
      "Example: Group-wise linear regression"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "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"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "by_year.apply(regress, 'AAPL', ['SPX'])"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "Pivot tables and Cross-tabulation"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "tips.pivot_table(index=['sex', 'smoker'])"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "tips.pivot_table(['tip_pct', 'size'], index=['sex', 'day'],\n",
      "                 columns='smoker')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "tips.pivot_table(['tip_pct', 'size'], index=['sex', 'day'],\n",
      "                 columns='smoker', margins=True)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "tips.pivot_table('tip_pct', index=['sex', 'smoker'], columns='day',\n",
      "                 aggfunc=len, margins=True)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "tips.pivot_table('size', index=['time', 'sex', 'smoker'],\n",
      "                 columns='day', aggfunc='sum', fill_value=0)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "heading",
     "level": 3,
     "metadata": {},
     "source": [
      "Cross-tabulations: crosstab"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "from StringIO import StringIO\n",
      "data = \"\"\"\\\n",
      "Sample    Gender    Handedness\n",
      "1    Female    Right-handed\n",
      "2    Male    Left-handed\n",
      "3    Female    Right-handed\n",
      "4    Male    Right-handed\n",
      "5    Male    Left-handed\n",
      "6    Male    Right-handed\n",
      "7    Female    Right-handed\n",
      "8    Female    Left-handed\n",
      "9    Male    Right-handed\n",
      "10    Female    Right-handed\"\"\"\n",
      "data = pd.read_table(StringIO(data), sep='\\s+')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "data"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "pd.crosstab(data.Gender, data.Handedness, margins=True)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "pd.crosstab([tips.time, tips.day], tips.smoker, margins=True)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "Example: 2012 Federal Election Commission Database"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "fec = pd.read_csv('ch09/P00000001-ALL.csv')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "fec.info()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "fec.ix[123456]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "unique_cands = fec.cand_nm.unique()\n",
      "unique_cands"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "unique_cands[2]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "parties = {'Bachmann, Michelle': 'Republican',\n",
      "           'Cain, Herman': 'Republican',\n",
      "           'Gingrich, Newt': 'Republican',\n",
      "           'Huntsman, Jon': 'Republican',\n",
      "           'Johnson, Gary Earl': 'Republican',\n",
      "           'McCotter, Thaddeus G': 'Republican',\n",
      "           'Obama, Barack': 'Democrat',\n",
      "           'Paul, Ron': 'Republican',\n",
      "           'Pawlenty, Timothy': 'Republican',\n",
      "           'Perry, Rick': 'Republican',\n",
      "           \"Roemer, Charles E. 'Buddy' III\": 'Republican',\n",
      "           'Romney, Mitt': 'Republican',\n",
      "           'Santorum, Rick': 'Republican'}"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "fec.cand_nm[123456:123461]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "fec.cand_nm[123456:123461].map(parties)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Add it as a column\n",
      "fec['party'] = fec.cand_nm.map(parties)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "fec['party'].value_counts()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "(fec.contb_receipt_amt > 0).value_counts()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "fec = fec[fec.contb_receipt_amt > 0]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "fec_mrbo = fec[fec.cand_nm.isin(['Obama, Barack', 'Romney, Mitt'])]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "heading",
     "level": 3,
     "metadata": {},
     "source": [
      "Donation statistics by occupation and employer"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "fec.contbr_occupation.value_counts()[:10]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "occ_mapping = {\n",
      "   'INFORMATION REQUESTED PER BEST EFFORTS' : 'NOT PROVIDED',\n",
      "   'INFORMATION REQUESTED' : 'NOT PROVIDED',\n",
      "   'INFORMATION REQUESTED (BEST EFFORTS)' : 'NOT PROVIDED',\n",
      "   'C.E.O.': 'CEO'\n",
      "}\n",
      "\n",
      "# If no mapping provided, return x\n",
      "f = lambda x: occ_mapping.get(x, x)\n",
      "fec.contbr_occupation = fec.contbr_occupation.map(f)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "emp_mapping = {\n",
      "   'INFORMATION REQUESTED PER BEST EFFORTS' : 'NOT PROVIDED',\n",
      "   'INFORMATION REQUESTED' : 'NOT PROVIDED',\n",
      "   'SELF' : 'SELF-EMPLOYED',\n",
      "   'SELF EMPLOYED' : 'SELF-EMPLOYED',\n",
      "}\n",
      "\n",
      "# If no mapping provided, return x\n",
      "f = lambda x: emp_mapping.get(x, x)\n",
      "fec.contbr_employer = fec.contbr_employer.map(f)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "by_occupation = fec.pivot_table('contb_receipt_amt',\n",
      "                                index='contbr_occupation',\n",
      "                                columns='party', aggfunc='sum')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "over_2mm = by_occupation[by_occupation.sum(1) > 2000000]\n",
      "over_2mm"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "over_2mm.plot(kind='barh')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "def get_top_amounts(group, key, n=5):\n",
      "    totals = group.groupby(key)['contb_receipt_amt'].sum()\n",
      "\n",
      "    # Order totals by key in descending order\n",
      "    return totals.order(ascending=False)[-n:]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "grouped = fec_mrbo.groupby('cand_nm')\n",
      "grouped.apply(get_top_amounts, 'contbr_occupation', n=7)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "grouped.apply(get_top_amounts, 'contbr_employer', n=10)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "heading",
     "level": 3,
     "metadata": {},
     "source": [
      "Bucketing donation amounts"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "bins = np.array([0, 1, 10, 100, 1000, 10000, 100000, 1000000, 10000000])\n",
      "labels = pd.cut(fec_mrbo.contb_receipt_amt, bins)\n",
      "labels"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "grouped = fec_mrbo.groupby(['cand_nm', labels])\n",
      "grouped.size().unstack(0)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "bucket_sums = grouped.contb_receipt_amt.sum().unstack(0)\n",
      "bucket_sums"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "normed_sums = bucket_sums.div(bucket_sums.sum(axis=1), axis=0)\n",
      "normed_sums"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "normed_sums[:-2].plot(kind='barh', stacked=True)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "heading",
     "level": 3,
     "metadata": {},
     "source": [
      "Donation statistics by state"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "grouped = fec_mrbo.groupby(['cand_nm', 'contbr_st'])\n",
      "totals = grouped.contb_receipt_amt.sum().unstack(0).fillna(0)\n",
      "totals = totals[totals.sum(1) > 100000]\n",
      "totals[:10]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "percent = totals.div(totals.sum(1), axis=0)\n",
      "percent[:10]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    }
   ],
   "metadata": {}
  }
 ]
}