{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Data Aggregation and Group Operations" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "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)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "pd.options.display.notebook_repr_html = False" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "%matplotlib inline" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## GroupBy mechanics" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "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" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "grouped = df['data1'].groupby(df['key1'])\n", "grouped" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "grouped.mean()" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "means = df['data1'].groupby([df['key1'], df['key2']]).mean()\n", "means" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "means.unstack()" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "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 }, "outputs": [], "source": [ "df.groupby('key1').mean()" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "df.groupby(['key1', 'key2']).mean()" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "df.groupby(['key1', 'key2']).size()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Iterating over groups" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "for name, group in df.groupby('key1'):\n", " print(name)\n", " print(group)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "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 }, "outputs": [], "source": [ "pieces = dict(list(df.groupby('key1')))\n", "pieces['b']" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "df.dtypes" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "grouped = df.groupby(df.dtypes, axis=1)\n", "dict(list(grouped))" ] }, { "cell_type": "markdown", "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", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "df.groupby(['key1', 'key2'])[['data2']].mean()" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "s_grouped = df.groupby(['key1', 'key2'])['data2']\n", "s_grouped" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "s_grouped.mean()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Grouping with dicts and Series" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "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" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "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 }, "outputs": [], "source": [ "by_column = people.groupby(mapping, axis=1)\n", "by_column.sum()" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "map_series = Series(mapping)\n", "map_series" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "people.groupby(map_series, axis=1).count()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Grouping with functions" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "people.groupby(len).sum()" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "key_list = ['one', 'one', 'one', 'two', 'two']\n", "people.groupby([len, key_list]).min()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Grouping by index levels" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "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" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "hier_df.groupby(level='cty', axis=1).count()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Data aggregation" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "df" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "grouped = df.groupby('key1')\n", "grouped['data1'].quantile(0.9)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "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 }, "outputs": [], "source": [ "grouped.describe()" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "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]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Column-wise and multiple function application" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "grouped = tips.groupby(['sex', 'smoker'])" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "grouped_pct = grouped['tip_pct']\n", "grouped_pct.agg('mean')" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "grouped_pct.agg(['mean', 'std', peak_to_peak])" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "grouped_pct.agg([('foo', 'mean'), ('bar', np.std)])" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "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 }, "outputs": [], "source": [ "result['tip_pct']" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "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 }, "outputs": [], "source": [ "grouped.agg({'tip' : np.max, 'size' : 'sum'})" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "grouped.agg({'tip_pct' : ['min', 'max', 'mean', 'std'],\n", " 'size' : 'sum'})" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Returning aggregated data in \"unindexed\" form" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "tips.groupby(['sex', 'smoker'], as_index=False).mean()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Group-wise operations and transformations" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "df" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "k1_means = df.groupby('key1').mean().add_prefix('mean_')\n", "k1_means" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "pd.merge(df, k1_means, left_on='key1', right_index=True)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "key = ['one', 'two', 'one', 'two', 'one']\n", "people.groupby(key).mean()" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "people.groupby(key).transform(np.mean)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "def demean(arr):\n", " return arr - arr.mean()\n", "demeaned = people.groupby(key).transform(demean)\n", "demeaned" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "demeaned.groupby(key).mean()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Apply: General split-apply-combine" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "def top(df, n=5, column='tip_pct'):\n", " return df.sort_index(by=column)[-n:]\n", "top(tips, n=6)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "tips.groupby('smoker').apply(top)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "tips.groupby(['smoker', 'day']).apply(top, n=1, column='total_bill')" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "result = tips.groupby('smoker')['tip_pct'].describe()\n", "result" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "result.unstack('smoker')" ] }, { "cell_type": "raw", "metadata": {}, "source": [ "f = lambda x: x.describe()\n", "grouped.apply(f)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### Suppressing the group keys" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "tips.groupby('smoker', group_keys=False).apply(top)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Quantile and bucket analysis" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "frame = DataFrame({'data1': np.random.randn(1000),\n", " 'data2': np.random.randn(1000)})\n", "factor = pd.cut(frame.data1, 4)\n", "factor[:10]" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "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)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "# 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()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Example: Filling missing values with group-specific values" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "s = Series(np.random.randn(6))\n", "s[::2] = np.nan\n", "s" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "s.fillna(s.mean())" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "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" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "data.groupby(group_key).mean()" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "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 }, "outputs": [], "source": [ "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)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Example: Random sampling and permutation" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "# 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)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "deck[:13]" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "def draw(deck, n=5):\n", " return deck.take(np.random.permutation(len(deck))[:n])\n", "draw(deck)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "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 }, "outputs": [], "source": [ "# alternatively\n", "deck.groupby(get_suit, group_keys=False).apply(draw, n=2)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Example: Group weighted average and correlation" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "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" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "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 }, "outputs": [], "source": [ "close_px = pd.read_csv('ch09/stock_px.csv', parse_dates=True, index_col=0)\n", "close_px.info()" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "close_px[-4:]" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "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)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "# Annual correlation of Apple with Microsoft\n", "by_year.apply(lambda g: g['AAPL'].corr(g['MSFT']))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Example: Group-wise linear regression" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "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 }, "outputs": [], "source": [ "by_year.apply(regress, 'AAPL', ['SPX'])" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Pivot tables and Cross-tabulation" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "tips.pivot_table(index=['sex', 'smoker'])" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "tips.pivot_table(['tip_pct', 'size'], index=['sex', 'day'],\n", " columns='smoker')" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "tips.pivot_table(['tip_pct', 'size'], index=['sex', 'day'],\n", " columns='smoker', margins=True)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "tips.pivot_table('tip_pct', index=['sex', 'smoker'], columns='day',\n", " aggfunc=len, margins=True)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "tips.pivot_table('size', index=['time', 'sex', 'smoker'],\n", " columns='day', aggfunc='sum', fill_value=0)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Cross-tabulations: crosstab" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "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+')" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "data" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "pd.crosstab(data.Gender, data.Handedness, margins=True)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "pd.crosstab([tips.time, tips.day], tips.smoker, margins=True)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Example: 2012 Federal Election Commission Database" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "fec = pd.read_csv('ch09/P00000001-ALL.csv')" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "fec.info()" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "fec.ix[123456]" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "unique_cands = fec.cand_nm.unique()\n", "unique_cands" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "unique_cands[2]" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "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'}" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "fec.cand_nm[123456:123461]" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "fec.cand_nm[123456:123461].map(parties)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "# Add it as a column\n", "fec['party'] = fec.cand_nm.map(parties)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "fec['party'].value_counts()" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "(fec.contb_receipt_amt > 0).value_counts()" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "fec = fec[fec.contb_receipt_amt > 0]" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "fec_mrbo = fec[fec.cand_nm.isin(['Obama, Barack', 'Romney, Mitt'])]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Donation statistics by occupation and employer" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "fec.contbr_occupation.value_counts()[:10]" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "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)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "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)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "by_occupation = fec.pivot_table('contb_receipt_amt',\n", " index='contbr_occupation',\n", " columns='party', aggfunc='sum')" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "over_2mm = by_occupation[by_occupation.sum(1) > 2000000]\n", "over_2mm" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "over_2mm.plot(kind='barh')" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "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:]" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "grouped = fec_mrbo.groupby('cand_nm')\n", "grouped.apply(get_top_amounts, 'contbr_occupation', n=7)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "grouped.apply(get_top_amounts, 'contbr_employer', n=10)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Bucketing donation amounts" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "bins = np.array([0, 1, 10, 100, 1000, 10000, 100000, 1000000, 10000000])\n", "labels = pd.cut(fec_mrbo.contb_receipt_amt, bins)\n", "labels" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "grouped = fec_mrbo.groupby(['cand_nm', labels])\n", "grouped.size().unstack(0)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "bucket_sums = grouped.contb_receipt_amt.sum().unstack(0)\n", "bucket_sums" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "normed_sums = bucket_sums.div(bucket_sums.sum(axis=1), axis=0)\n", "normed_sums" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "normed_sums[:-2].plot(kind='barh', stacked=True)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Donation statistics by state" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "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]" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [ "percent = totals.div(totals.sum(1), axis=0)\n", "percent[:10]" ] } ], "metadata": { "kernelspec": { "display_name": "Python 2", "language": "python", "name": "python2" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 2 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython2", "version": "2.7.10" } }, "nbformat": 4, "nbformat_minor": 0 }