2016 Election Contribution Data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Federal Election Committee Data\n",
    "\n",
    "Performing exploratory data analysis on the FEC data of money exchanged during the 2016 election. I will be looking at the data found at the [FEC website](http://classic.fec.gov/finance/disclosure/ftpdet.shtml#a2015_2016). The specific files that I downloaded were the Committee Master file, Candidate Master file, and Contributions by Individuals. The Contributions by Individuals file is very large and therefore I will be looking specifically at contributions from individuals in Berkeley, CA. \n",
    "\n",
    "I have two goals for this project. \n",
    "\n",
    "- Answer questions by computing descriptive statistics on the data\n",
    "- Conduct a hypothesis test\n",
    "\n",
    "I will complete almost all of this project using SQL. A small portion will rely on the use of pandas dataframes in Python."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "%matplotlib inline\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "sns.set()\n",
    "import sqlalchemy"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Postgres\n",
    "\n",
    "I am hosting the data through Heroku Postgres. I loaded the data into the Heroku Database and can connect to it using the URI that I was provided. For security reasons, I am not posting my personal Heroku URI, but this will not affect the notebook or what is displayed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "my_URI = 'postgres://uri'\n",
    "%reload_ext sql\n",
    "%sql $my_URI\n",
    "engine = sqlalchemy.create_engine(my_URI)\n",
    "connection = engine.connect()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Table Descriptions\n",
    "\n",
    "Here is a list of the tables in the database.  Each table links to the documentation on the [FEC page](http://www.fec.gov/finance/disclosure/ftpdet.shtml) for the dataset.\n",
    "\n",
    "- [`cand`](http://www.fec.gov/finance/disclosure/metadata/DataDictionaryCandidateMaster.shtml): Candidates table. Contains names and party affiliation.\n",
    "- [`comm`](http://www.fec.gov/finance/disclosure/metadata/DataDictionaryCommitteeMaster.shtml): Committees table. Contains committee names and types.\n",
    "- [`indiv_sample`](http://www.fec.gov/finance/disclosure/metadata/DataDictionaryContributionsbyIndividuals.shtml): Sample of individual contributions from Berkeley.\n",
    "- [`indiv_sample_top`](http://www.fec.gov/finance/disclosure/metadata/DataDictionaryContributionsbyIndividuals.shtml): Sample of individual contributions from the top contributors at Berkeley."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>cmte_id</th>\n",
       "        <th>amndt_ind</th>\n",
       "        <th>rpt_tp</th>\n",
       "        <th>transaction_pgi</th>\n",
       "        <th>image_num</th>\n",
       "        <th>transaction_tp</th>\n",
       "        <th>entity_tp</th>\n",
       "        <th>name</th>\n",
       "        <th>city</th>\n",
       "        <th>state</th>\n",
       "        <th>zip_code</th>\n",
       "        <th>employer</th>\n",
       "        <th>occupation</th>\n",
       "        <th>transaction_dt</th>\n",
       "        <th>transaction_amt</th>\n",
       "        <th>other_id</th>\n",
       "        <th>tran_id</th>\n",
       "        <th>file_num</th>\n",
       "        <th>memo_cd</th>\n",
       "        <th>memo_text</th>\n",
       "        <th>sub_id</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>C00404525</td>\n",
       "        <td>N</td>\n",
       "        <td>YE</td>\n",
       "        <td>P    </td>\n",
       "        <td>201601050300036659</td>\n",
       "        <td>15</td>\n",
       "        <td>IND</td>\n",
       "        <td>KURZWEIL, JACK</td>\n",
       "        <td>BERKELEY</td>\n",
       "        <td>CA</td>\n",
       "        <td>94703</td>\n",
       "        <td>RETIRED</td>\n",
       "        <td>RETIRED</td>\n",
       "        <td>10062015</td>\n",
       "        <td>500</td>\n",
       "        <td>None</td>\n",
       "        <td>SA01271666597</td>\n",
       "        <td>1043014</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>2012820161261113255</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>C00577130</td>\n",
       "        <td>A</td>\n",
       "        <td>M6</td>\n",
       "        <td>P    </td>\n",
       "        <td>201608159022353532</td>\n",
       "        <td>15E</td>\n",
       "        <td>IND</td>\n",
       "        <td>LENHOFF, BERNIE</td>\n",
       "        <td>BERKELEY</td>\n",
       "        <td>CA</td>\n",
       "        <td>947031658</td>\n",
       "        <td>ARBORICULTURAL SPECIALTIES</td>\n",
       "        <td>MANAGER</td>\n",
       "        <td>05042016</td>\n",
       "        <td>15</td>\n",
       "        <td>C00401224</td>\n",
       "        <td>VPF7BPFVV37</td>\n",
       "        <td>1094141</td>\n",
       "        <td>None</td>\n",
       "        <td>* EARMARKED CONTRIBUTION: SEE BELOW</td>\n",
       "        <td>4081720161312601930</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>C00193433</td>\n",
       "        <td>N</td>\n",
       "        <td>M8</td>\n",
       "        <td>None</td>\n",
       "        <td>201508149000826883</td>\n",
       "        <td>15</td>\n",
       "        <td>IND</td>\n",
       "        <td>MURRA, LAURA S MS.</td>\n",
       "        <td>BERKELEY</td>\n",
       "        <td>CA</td>\n",
       "        <td>94708</td>\n",
       "        <td>NONE</td>\n",
       "        <td>NONE</td>\n",
       "        <td>07012015</td>\n",
       "        <td>60</td>\n",
       "        <td>None</td>\n",
       "        <td>4356366</td>\n",
       "        <td>1021594</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>4081720151248947285</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>C00042366</td>\n",
       "        <td>A</td>\n",
       "        <td>30G</td>\n",
       "        <td>P    </td>\n",
       "        <td>201612080200695092</td>\n",
       "        <td>15E</td>\n",
       "        <td>IND</td>\n",
       "        <td>FISHER, BRIGITTE</td>\n",
       "        <td>BERKELEY</td>\n",
       "        <td>CA</td>\n",
       "        <td>947052825</td>\n",
       "        <td>NONE</td>\n",
       "        <td>NOT EMPLOYED</td>\n",
       "        <td>11042016</td>\n",
       "        <td>50</td>\n",
       "        <td>C00401224</td>\n",
       "        <td>SA011717509239506</td>\n",
       "        <td>1139429</td>\n",
       "        <td>None</td>\n",
       "        <td>* EARMARKED CONTRIBUTION: SEE BELOW</td>\n",
       "        <td>1060820170035181652</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>C00577130</td>\n",
       "        <td>A</td>\n",
       "        <td>M3</td>\n",
       "        <td>P    </td>\n",
       "        <td>201606169018323111</td>\n",
       "        <td>15E</td>\n",
       "        <td>IND</td>\n",
       "        <td>RISING, TODD</td>\n",
       "        <td>BERKELEY</td>\n",
       "        <td>CA</td>\n",
       "        <td>947021513</td>\n",
       "        <td>ADAM BEYDA, PSY.D</td>\n",
       "        <td>PSYCHOLOGICAL ASSISTANT</td>\n",
       "        <td>02212016</td>\n",
       "        <td>27</td>\n",
       "        <td>C00401224</td>\n",
       "        <td>VPF7BKE39K0</td>\n",
       "        <td>1077916</td>\n",
       "        <td>None</td>\n",
       "        <td>* EARMARKED CONTRIBUTION: SEE BELOW</td>\n",
       "        <td>4063020161300916281</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('C00404525', 'N', 'YE', 'P    ', 201601050300036659, '15', 'IND', 'KURZWEIL, JACK', 'BERKELEY', 'CA', '94703', 'RETIRED', 'RETIRED', '10062015', 500, None, 'SA01271666597', 1043014, None, None, 2012820161261113255),\n",
       " ('C00577130', 'A', 'M6', 'P    ', 201608159022353532, '15E', 'IND', 'LENHOFF, BERNIE', 'BERKELEY', 'CA', '947031658', 'ARBORICULTURAL SPECIALTIES', 'MANAGER', '05042016', 15, 'C00401224', 'VPF7BPFVV37', 1094141, None, '* EARMARKED CONTRIBUTION: SEE BELOW', 4081720161312601930),\n",
       " ('C00193433', 'N', 'M8', None, 201508149000826883, '15', 'IND', 'MURRA, LAURA S MS.', 'BERKELEY', 'CA', '94708', 'NONE', 'NONE', '07012015', 60, None, '4356366', 1021594, None, None, 4081720151248947285),\n",
       " ('C00042366', 'A', '30G', 'P    ', 201612080200695092, '15E', 'IND', 'FISHER, BRIGITTE', 'BERKELEY', 'CA', '947052825', 'NONE', 'NOT EMPLOYED', '11042016', 50, 'C00401224', 'SA011717509239506', 1139429, None, '* EARMARKED CONTRIBUTION: SEE BELOW', 1060820170035181652),\n",
       " ('C00577130', 'A', 'M3', 'P    ', 201606169018323111, '15E', 'IND', 'RISING, TODD', 'BERKELEY', 'CA', '947021513', 'ADAM BEYDA, PSY.D', 'PSYCHOLOGICAL ASSISTANT', '02212016', 27, 'C00401224', 'VPF7BKE39K0', 1077916, None, '* EARMARKED CONTRIBUTION: SEE BELOW', 4063020161300916281)]"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM indiv_sample LIMIT 5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>cand_id</th>\n",
       "        <th>cand_name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>H0CA03078</td>\n",
       "        <td>BERA, AMERISH</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>H0CA15148</td>\n",
       "        <td>HONDA, MIKE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>H2AZ09019</td>\n",
       "        <td>SINEMA, KYRSTEN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>H2CA00120</td>\n",
       "        <td>BROWNLEY, JULIA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>H2CA06259</td>\n",
       "        <td>HUFFMAN, JARED</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('H0CA03078', 'BERA, AMERISH'),\n",
       " ('H0CA15148', 'HONDA, MIKE'),\n",
       " ('H2AZ09019', 'SINEMA, KYRSTEN'),\n",
       " ('H2CA00120', 'BROWNLEY, JULIA'),\n",
       " ('H2CA06259', 'HUFFMAN, JARED')]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = '''\n",
    "SELECT cand_id, cand_name\n",
    "FROM cand\n",
    "WHERE cand_pty_affiliation = 'DEM'\n",
    "LIMIT 5\n",
    "'''\n",
    "\n",
    "%sql $query"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Large Donors\n",
    "\n",
    "I am interesting in looking at the individuals who donated large sums of money. In this query, I am using COUNT to see how many individuals donated over $3000 in a single transaction. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(4,)]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "SELECT COUNT(*)\n",
    "FROM indiv_sample\n",
    "WHERE transaction_amt > 3000\n",
    "\"\"\"\n",
    "%sql $query"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It is interesting to find that there are only 4 individuals who donated a sum of money over $3000 in a single transaction. Since there is a small number of individuals I can make a table that shows all of these individuals."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "4 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>committee_id</th>\n",
       "        <th>name</th>\n",
       "        <th>transaction_amt</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>C00586537</td>\n",
       "        <td>GUND, LOUISE LAIDLAW</td>\n",
       "        <td>25000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>C00000935</td>\n",
       "        <td>FIDDLER, JERRY</td>\n",
       "        <td>10000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>C00586537</td>\n",
       "        <td>LAPPEN, DAVID</td>\n",
       "        <td>5000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>C00401224</td>\n",
       "        <td>LUEVANO, ROSA</td>\n",
       "        <td>5000</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('C00586537', 'GUND, LOUISE LAIDLAW', 25000),\n",
       " ('C00000935', 'FIDDLER, JERRY', 10000),\n",
       " ('C00586537', 'LAPPEN, DAVID', 5000),\n",
       " ('C00401224', 'LUEVANO, ROSA', 5000)]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "SELECT \n",
    "    cmte_id AS committee_id,\n",
    "    name AS name,\n",
    "    transaction_amt AS transaction_amt\n",
    "FROM indiv_sample\n",
    "WHERE transaction_amt > 3000\n",
    "ORDER BY transaction_amt DESC, name;\n",
    "\"\"\"\n",
    "\n",
    "%sql $query"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Single transactions can be misleading. To best understand individual donors it will be far more insightful to aggregate the total sum of donations. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "20 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>total_transaction_amt</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>GUND, LOUISE LAIDLAW</td>\n",
       "        <td>25000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>FIDDLER, JERRY</td>\n",
       "        <td>10000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SHENKER, SCOTT</td>\n",
       "        <td>8000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>BERLEKAMP, ELWYN</td>\n",
       "        <td>5550</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ABRAMS, DENISE</td>\n",
       "        <td>5400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>LAPPEN, DAVID</td>\n",
       "        <td>5000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>LUEVANO, ROSA</td>\n",
       "        <td>5000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>BERNHARDT, ANTHONY</td>\n",
       "        <td>3200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>HUFF, GERALD</td>\n",
       "        <td>3200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>BIRD, KAREN</td>\n",
       "        <td>2700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>EPSTEIN, BOB</td>\n",
       "        <td>2700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>HAHN, SOPHIE</td>\n",
       "        <td>2700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>JOSEPH, DAVID</td>\n",
       "        <td>2700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>LITTMANN, NICOLE</td>\n",
       "        <td>2700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>LOGAN, JONATHAN</td>\n",
       "        <td>2700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>REINIS, JONATHAN ROY</td>\n",
       "        <td>2700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SEIBEL, PETER</td>\n",
       "        <td>2700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SHAPIRO, CARL</td>\n",
       "        <td>2700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>TAYLOR, JEROME</td>\n",
       "        <td>2700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>WILNER, DAVID</td>\n",
       "        <td>2700</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('GUND, LOUISE LAIDLAW', 25000),\n",
       " ('FIDDLER, JERRY', 10000),\n",
       " ('SHENKER, SCOTT', 8000),\n",
       " ('BERLEKAMP, ELWYN', 5550),\n",
       " ('ABRAMS, DENISE', 5400),\n",
       " ('LAPPEN, DAVID', 5000),\n",
       " ('LUEVANO, ROSA', 5000),\n",
       " ('BERNHARDT, ANTHONY', 3200),\n",
       " ('HUFF, GERALD', 3200),\n",
       " ('BIRD, KAREN', 2700),\n",
       " ('EPSTEIN, BOB', 2700),\n",
       " ('HAHN, SOPHIE', 2700),\n",
       " ('JOSEPH, DAVID', 2700),\n",
       " ('LITTMANN, NICOLE', 2700),\n",
       " ('LOGAN, JONATHAN', 2700),\n",
       " ('REINIS, JONATHAN ROY', 2700),\n",
       " ('SEIBEL, PETER', 2700),\n",
       " ('SHAPIRO, CARL', 2700),\n",
       " ('TAYLOR, JEROME', 2700),\n",
       " ('WILNER, DAVID', 2700)]"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = '''\n",
    "SELECT \n",
    "    name AS name,\n",
    "    SUM(transaction_amt) AS total_transaction_amt \n",
    "FROM indiv_sample \n",
    "GROUP BY name\n",
    "ORDER BY total_transaction_amt DESC, name\n",
    "LIMIT 20;\n",
    "'''\n",
    "%sql $query"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Donors by Occupation\n",
    "\n",
    "Another interesting statistic is to look at is the distribution of contributors by occupation."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>407</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(407,)]"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = '''\n",
    "SELECT \n",
    "    COUNT(DISTINCT occupation)\n",
    "FROM indiv_sample \n",
    "'''\n",
    "%sql $query"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are 407 unique occupations that donated to candidates. With this in mind, I can now create a query to look at the highest represented occupations. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "20 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>occupation</th>\n",
       "        <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>NOT EMPLOYED</td>\n",
       "        <td>373</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>RETIRED</td>\n",
       "        <td>306</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "        <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>PROFESSOR</td>\n",
       "        <td>58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ATTORNEY</td>\n",
       "        <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>NONE</td>\n",
       "        <td>33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SCIENTIST</td>\n",
       "        <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ENGINEER</td>\n",
       "        <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>PHONE CLERK</td>\n",
       "        <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>RETIRED TEACHER</td>\n",
       "        <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ARTIST</td>\n",
       "        <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>HOMEMAKER</td>\n",
       "        <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>PSYCHOLOGIST</td>\n",
       "        <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>PHYSICIAN</td>\n",
       "        <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>UNEMPLOYED</td>\n",
       "        <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>WRITER</td>\n",
       "        <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>TEACHER</td>\n",
       "        <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>LAWYER</td>\n",
       "        <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>PSYCHOTHERAPIST</td>\n",
       "        <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>PROFESSIONAL</td>\n",
       "        <td>15</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('NOT EMPLOYED', 373),\n",
       " ('RETIRED', 306),\n",
       " (None, 70),\n",
       " ('PROFESSOR', 58),\n",
       " ('ATTORNEY', 56),\n",
       " ('NONE', 33),\n",
       " ('SCIENTIST', 30),\n",
       " ('ENGINEER', 20),\n",
       " ('PHONE CLERK', 20),\n",
       " ('RETIRED TEACHER', 20),\n",
       " ('ARTIST', 19),\n",
       " ('HOMEMAKER', 19),\n",
       " ('PSYCHOLOGIST', 19),\n",
       " ('PHYSICIAN', 18),\n",
       " ('UNEMPLOYED', 18),\n",
       " ('WRITER', 18),\n",
       " ('TEACHER', 17),\n",
       " ('LAWYER', 16),\n",
       " ('PSYCHOTHERAPIST', 16),\n",
       " ('PROFESSIONAL', 15)]"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = '''\n",
    "SELECT \n",
    "    occupation, \n",
    "    COUNT(transaction_amt) AS count\n",
    "FROM indiv_sample\n",
    "GROUP BY occupation\n",
    "ORDER BY count DESC, occupation\n",
    "LIMIT 20\n",
    "'''\n",
    "%sql $query"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I can also look at the average donated amount per occupation. It is important to add a column with the number of transactions. From the table below, it is easy to see that all of the highest occupations only had one yet substantial donation."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "10 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>occupation</th>\n",
       "        <th>avg_amount</th>\n",
       "        <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>LOUISE GUND FOUNDATION</td>\n",
       "        <td>25000.00</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>INVESTOR/BOARD MEMBER</td>\n",
       "        <td>10000.00</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>MARRIAGE &amp; FAMILY THERAPIST</td>\n",
       "        <td>5000.00</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CHAIRMAN</td>\n",
       "        <td>2700.00</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>INVESTMENT MANAGEMENT</td>\n",
       "        <td>2700.00</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>LIVE THEATRE PRODUCER</td>\n",
       "        <td>2700.00</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>AIDS CASE MANAGER</td>\n",
       "        <td>2500.00</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>WEALTH MANAGER</td>\n",
       "        <td>1700.00</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>INVESTOR</td>\n",
       "        <td>1485.00</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ACCOUNTANT</td>\n",
       "        <td>1475.00</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('LOUISE GUND FOUNDATION', Decimal('25000.00'), 1),\n",
       " ('INVESTOR/BOARD MEMBER', Decimal('10000.00'), 1),\n",
       " ('MARRIAGE & FAMILY THERAPIST', Decimal('5000.00'), 1),\n",
       " ('CHAIRMAN', Decimal('2700.00'), 1),\n",
       " ('INVESTMENT MANAGEMENT', Decimal('2700.00'), 1),\n",
       " ('LIVE THEATRE PRODUCER', Decimal('2700.00'), 1),\n",
       " ('AIDS CASE MANAGER', Decimal('2500.00'), 1),\n",
       " ('WEALTH MANAGER', Decimal('1700.00'), 1),\n",
       " ('INVESTOR', Decimal('1485.00'), 2),\n",
       " ('ACCOUNTANT', Decimal('1475.00'), 2)]"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = '''\n",
    "SELECT \n",
    "    occupation AS occupation, \n",
    "    ROUND(AVG(transaction_amt),2) AS avg_amount,\n",
    "    COUNT(transaction_amt) AS count\n",
    "FROM indiv_sample \n",
    "GROUP BY occupation\n",
    "ORDER BY avg_amount DESC, occupation\n",
    "LIMIT 10\n",
    "'''\n",
    "%sql $query"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Since the previous statistic was not particularly informative I will have to revise my query and add a constraint of more than 10 transactions. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "10 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>occupation</th>\n",
       "        <th>amount</th>\n",
       "        <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ATTORNEY</td>\n",
       "        <td>423.95</td>\n",
       "        <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SOFTWARE ENGINEER</td>\n",
       "        <td>371.00</td>\n",
       "        <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CONSULTANT</td>\n",
       "        <td>343.08</td>\n",
       "        <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>STUDENT</td>\n",
       "        <td>317.55</td>\n",
       "        <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SCIENTIST</td>\n",
       "        <td>298.37</td>\n",
       "        <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ENGINEER</td>\n",
       "        <td>228.50</td>\n",
       "        <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ARTIST</td>\n",
       "        <td>216.26</td>\n",
       "        <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>PHYSICIAN</td>\n",
       "        <td>193.83</td>\n",
       "        <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>RETIRED</td>\n",
       "        <td>143.74</td>\n",
       "        <td>306</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>UNEMPLOYED</td>\n",
       "        <td>140.83</td>\n",
       "        <td>18</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('ATTORNEY', Decimal('423.95'), 56),\n",
       " ('SOFTWARE ENGINEER', Decimal('371.00'), 13),\n",
       " ('CONSULTANT', Decimal('343.08'), 12),\n",
       " ('STUDENT', Decimal('317.55'), 11),\n",
       " ('SCIENTIST', Decimal('298.37'), 30),\n",
       " ('ENGINEER', Decimal('228.50'), 20),\n",
       " ('ARTIST', Decimal('216.26'), 19),\n",
       " ('PHYSICIAN', Decimal('193.83'), 18),\n",
       " ('RETIRED', Decimal('143.74'), 306),\n",
       " ('UNEMPLOYED', Decimal('140.83'), 18)]"
      ]
     },
     "execution_count": 141,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = '''\n",
    "SELECT \n",
    "    occupation AS occupation, \n",
    "    avg_amount AS amount,\n",
    "    count AS count\n",
    "FROM\n",
    "    (SELECT \n",
    "    occupation AS occupation, \n",
    "    ROUND(AVG(transaction_amt),2) AS avg_amount,\n",
    "    COUNT(transaction_amt) AS count\n",
    "    FROM indiv_sample \n",
    "    GROUP BY occupation\n",
    "    ORDER BY avg_amount DESC, occupation)\n",
    "    AS results\n",
    "WHERE count > 10\n",
    "ORDER BY amount DESC\n",
    "LIMIT 10\n",
    "'''\n",
    "%sql $query"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is a better statistic, yet, since each individual can make multiple transactions it is still not as informative as I would like it. To achieve the average for a given occupation, I need to take the average of each individual's total amount. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "10 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>occupation</th>\n",
       "        <th>name</th>\n",
       "        <th>total_amount</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>LOUISE GUND FOUNDATION</td>\n",
       "        <td>GUND, LOUISE LAIDLAW</td>\n",
       "        <td>25000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>INVESTOR/BOARD MEMBER</td>\n",
       "        <td>FIDDLER, JERRY</td>\n",
       "        <td>10000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SCIENTIST</td>\n",
       "        <td>SHENKER, SCOTT</td>\n",
       "        <td>8000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>RETIRED</td>\n",
       "        <td>BERLEKAMP, ELWYN</td>\n",
       "        <td>5550</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ATTORNEY</td>\n",
       "        <td>ABRAMS, DENISE</td>\n",
       "        <td>5400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>MARRIAGE &amp; FAMILY THERAPIST</td>\n",
       "        <td>LAPPEN, DAVID</td>\n",
       "        <td>5000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>NOT EMPLOYED</td>\n",
       "        <td>LUEVANO, ROSA</td>\n",
       "        <td>5000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>RETIRED</td>\n",
       "        <td>BERNHARDT, ANTHONY</td>\n",
       "        <td>3200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SOFTWARE ENGINEER</td>\n",
       "        <td>HUFF, GERALD</td>\n",
       "        <td>3200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ACCOUNTANT</td>\n",
       "        <td>BIRD, KAREN</td>\n",
       "        <td>2700</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('LOUISE GUND FOUNDATION', 'GUND, LOUISE LAIDLAW', 25000),\n",
       " ('INVESTOR/BOARD MEMBER', 'FIDDLER, JERRY', 10000),\n",
       " ('SCIENTIST', 'SHENKER, SCOTT', 8000),\n",
       " ('RETIRED', 'BERLEKAMP, ELWYN', 5550),\n",
       " ('ATTORNEY', 'ABRAMS, DENISE', 5400),\n",
       " ('MARRIAGE & FAMILY THERAPIST', 'LAPPEN, DAVID', 5000),\n",
       " ('NOT EMPLOYED', 'LUEVANO, ROSA', 5000),\n",
       " ('RETIRED', 'BERNHARDT, ANTHONY', 3200),\n",
       " ('SOFTWARE ENGINEER', 'HUFF, GERALD', 3200),\n",
       " ('ACCOUNTANT', 'BIRD, KAREN', 2700)]"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = '''\n",
    "SELECT \n",
    "    occupation AS occupation,\n",
    "    name AS name,\n",
    "    SUM(transaction_amt) AS total_amount\n",
    "FROM indiv_sample \n",
    "GROUP BY name, occupation\n",
    "ORDER BY total_amount DESC, name\n",
    "LIMIT 10\n",
    "'''\n",
    "%sql $query"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "After computing the total amount I can compute the average total_amount for each occupation type. To filter out the occupations that have small counts, the query will constrain the number of contributors in that occupation to those greater than 5."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "10 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>occupation</th>\n",
       "        <th>amount</th>\n",
       "        <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SCIENTIST</td>\n",
       "        <td>1118.88</td>\n",
       "        <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ATTORNEY</td>\n",
       "        <td>593.53</td>\n",
       "        <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SOFTWARE ENGINEER</td>\n",
       "        <td>535.89</td>\n",
       "        <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CONSULTANT</td>\n",
       "        <td>374.27</td>\n",
       "        <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>STUDENT</td>\n",
       "        <td>349.30</td>\n",
       "        <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>NOT-EMPLOYED</td>\n",
       "        <td>331.67</td>\n",
       "        <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ARTIST</td>\n",
       "        <td>273.93</td>\n",
       "        <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>PHYSICIAN</td>\n",
       "        <td>268.38</td>\n",
       "        <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ENGINEER</td>\n",
       "        <td>253.89</td>\n",
       "        <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>PROFESSOR</td>\n",
       "        <td>240.06</td>\n",
       "        <td>32</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('SCIENTIST', Decimal('1118.88'), 8),\n",
       " ('ATTORNEY', Decimal('593.53'), 40),\n",
       " ('SOFTWARE ENGINEER', Decimal('535.89'), 9),\n",
       " ('CONSULTANT', Decimal('374.27'), 11),\n",
       " ('STUDENT', Decimal('349.30'), 10),\n",
       " ('NOT-EMPLOYED', Decimal('331.67'), 9),\n",
       " ('ARTIST', Decimal('273.93'), 15),\n",
       " ('PHYSICIAN', Decimal('268.38'), 13),\n",
       " ('ENGINEER', Decimal('253.89'), 18),\n",
       " ('PROFESSOR', Decimal('240.06'), 32)]"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = '''\n",
    "SELECT *\n",
    "    FROM\n",
    "    (SELECT \n",
    "        occupation AS occupation, \n",
    "        ROUND(AVG(total_amount),2) AS amount,\n",
    "        COUNT(occupation) AS count\n",
    "    FROM\n",
    "        (SELECT \n",
    "        occupation AS occupation,\n",
    "        name AS name,\n",
    "        SUM(transaction_amt) AS total_amount\n",
    "        FROM indiv_sample \n",
    "        GROUP BY name, occupation\n",
    "        ORDER BY total_amount DESC, name)\n",
    "        AS results\n",
    "    GROUP BY occupation\n",
    "    ORDER BY amount DESC)\n",
    "    AS result2\n",
    "WHERE count > 5\n",
    "LIMIT 10\n",
    "\n",
    "'''\n",
    "%sql $query"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Committees\n",
    "\n",
    "The comm table provides information about the various committees that individuals donate money to. The way the data is formatted only references the committee id (cmte_id) in the indiv_sample table. Therefore, I will need to perform a series of JOINs to get the more meaningful committee name (cmte_nm)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>cmte_id</th>\n",
       "        <th>cmte_nm</th>\n",
       "        <th>tres_nm</th>\n",
       "        <th>cmte_st1</th>\n",
       "        <th>cmte_st2</th>\n",
       "        <th>cmte_city</th>\n",
       "        <th>cmte_st</th>\n",
       "        <th>cmte_zip</th>\n",
       "        <th>cmte_dsgn</th>\n",
       "        <th>cmte_tp</th>\n",
       "        <th>cmte_pty_affiliation</th>\n",
       "        <th>cmte_filing_freq</th>\n",
       "        <th>org_tp</th>\n",
       "        <th>connected_org_nm</th>\n",
       "        <th>cand_id</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>C00000729</td>\n",
       "        <td>AMERICAN DENTAL ASSOCIATION POLITICAL ACTION COMMITTEE</td>\n",
       "        <td>HARRISON, THOMAS C. DR</td>\n",
       "        <td>1111 14TH STREET, NW</td>\n",
       "        <td>SUITE 1100</td>\n",
       "        <td>WASHINGTON</td>\n",
       "        <td>DC</td>\n",
       "        <td>20005</td>\n",
       "        <td>B</td>\n",
       "        <td>Q</td>\n",
       "        <td>UNK</td>\n",
       "        <td>M</td>\n",
       "        <td>M</td>\n",
       "        <td>AMERICAN DENTAL ASSOCIATION</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>C00000935</td>\n",
       "        <td>DCCC</td>\n",
       "        <td>WARD, KELLY C.</td>\n",
       "        <td>430 SOUTH CAPITOL STREET, SE</td>\n",
       "        <td>2ND FLOOR</td>\n",
       "        <td>WASHINGTON</td>\n",
       "        <td>DC</td>\n",
       "        <td>200034024</td>\n",
       "        <td>U</td>\n",
       "        <td>Y</td>\n",
       "        <td>DEM</td>\n",
       "        <td>M</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>C00001016</td>\n",
       "        <td>CARPENTERS LEGISLATIVE IMPROVEMENT COMMITTEE UNITED BROTHERHOOD OF CARPENTERS AND JOINERS</td>\n",
       "        <td>SILINS, ANDRIS</td>\n",
       "        <td>101 CONSTIUTION AVENUE, NW</td>\n",
       "        <td>10TH FLOOR WEST</td>\n",
       "        <td>WASHINGTON</td>\n",
       "        <td>DC</td>\n",
       "        <td>20001</td>\n",
       "        <td>B</td>\n",
       "        <td>Q</td>\n",
       "        <td>NNE</td>\n",
       "        <td>M</td>\n",
       "        <td>L</td>\n",
       "        <td>UNITED BROTHERHOOD OF CARPENTERS AND JOINERS OF AMERICA</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>C00003418</td>\n",
       "        <td>REPUBLICAN NATIONAL COMMITTEE</td>\n",
       "        <td>PARKER, ANTHONY W MR</td>\n",
       "        <td>310 FIRST STREET SE</td>\n",
       "        <td>None</td>\n",
       "        <td>WASHINGTON</td>\n",
       "        <td>DC</td>\n",
       "        <td>20003</td>\n",
       "        <td>U</td>\n",
       "        <td>Y</td>\n",
       "        <td>REP</td>\n",
       "        <td>M</td>\n",
       "        <td>None</td>\n",
       "        <td>HOUSE MAJORITY TRUST</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>C00009936</td>\n",
       "        <td>AMERICAN FEDERATION OF GOVT. EMPL. POLITICAL ACTION COMMITTEE</td>\n",
       "        <td>GAGE, JOHN MR.</td>\n",
       "        <td>80 F STREET, NW</td>\n",
       "        <td>None</td>\n",
       "        <td>WASHINGTON</td>\n",
       "        <td>DC</td>\n",
       "        <td>20001</td>\n",
       "        <td>B</td>\n",
       "        <td>Q</td>\n",
       "        <td>UNK</td>\n",
       "        <td>M</td>\n",
       "        <td>L</td>\n",
       "        <td>AMERICAN FEDERATION OF GOV&#x27;T EMPLOYEES</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('C00000729', 'AMERICAN DENTAL ASSOCIATION POLITICAL ACTION COMMITTEE', 'HARRISON, THOMAS C. DR', '1111 14TH STREET, NW', 'SUITE 1100', 'WASHINGTON', 'DC', '20005', 'B', 'Q', 'UNK', 'M', 'M', 'AMERICAN DENTAL ASSOCIATION', None),\n",
       " ('C00000935', 'DCCC', 'WARD, KELLY C.', '430 SOUTH CAPITOL STREET, SE', '2ND FLOOR', 'WASHINGTON', 'DC', '200034024', 'U', 'Y', 'DEM', 'M', None, None, None),\n",
       " ('C00001016', 'CARPENTERS LEGISLATIVE IMPROVEMENT COMMITTEE UNITED BROTHERHOOD OF CARPENTERS AND JOINERS', 'SILINS, ANDRIS', '101 CONSTIUTION AVENUE, NW', '10TH FLOOR WEST', 'WASHINGTON', 'DC', '20001', 'B', 'Q', 'NNE', 'M', 'L', 'UNITED BROTHERHOOD OF CARPENTERS AND JOINERS OF AMERICA', None),\n",
       " ('C00003418', 'REPUBLICAN NATIONAL COMMITTEE', 'PARKER, ANTHONY W MR', '310 FIRST STREET SE', None, 'WASHINGTON', 'DC', '20003', 'U', 'Y', 'REP', 'M', None, 'HOUSE MAJORITY TRUST', None),\n",
       " ('C00009936', 'AMERICAN FEDERATION OF GOVT. EMPL. POLITICAL ACTION COMMITTEE', 'GAGE, JOHN MR.', '80 F STREET, NW', None, 'WASHINGTON', 'DC', '20001', 'B', 'Q', 'UNK', 'M', 'L', \"AMERICAN FEDERATION OF GOV'T EMPLOYEES\", None)]"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select * from comm limit 5"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Using just the indiv_sample table I can SUM and GROUP BY to get the committees with the highest amount of money donated. However, this does not say anything about the specifics of the committee, which has its information stored in the comm table. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>committee_id</th>\n",
       "        <th>total_amount</th>\n",
       "        <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>C00401224</td>\n",
       "        <td>66228</td>\n",
       "        <td>799</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>C00575795</td>\n",
       "        <td>38002</td>\n",
       "        <td>252</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>C00577130</td>\n",
       "        <td>19920</td>\n",
       "        <td>239</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>C00000935</td>\n",
       "        <td>26040</td>\n",
       "        <td>153</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>C00042366</td>\n",
       "        <td>7271</td>\n",
       "        <td>71</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('C00401224', 66228, 799),\n",
       " ('C00575795', 38002, 252),\n",
       " ('C00577130', 19920, 239),\n",
       " ('C00000935', 26040, 153),\n",
       " ('C00042366', 7271, 71)]"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query ='''\n",
    "SELECT\n",
    "    cmte_id AS committee_id,\n",
    "    SUM(transaction_amt) AS total_amount,\n",
    "    COUNT(cmte_id) AS count\n",
    "FROM indiv_sample\n",
    "GROUP BY committee_id\n",
    "ORDER BY count DESC\n",
    "LIMIT 5\n",
    "'''\n",
    "%sql $query"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Expanding upon the previous query and crosslisting with the comm table will provide a table that includes how much monet and how many total donations each committee recieved. From here I will take the top 10 with highest total donations to look at.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "10 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>committee_name</th>\n",
       "        <th>party_affiliation</th>\n",
       "        <th>city</th>\n",
       "        <th>state</th>\n",
       "        <th>total_amount</th>\n",
       "        <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ACTBLUE</td>\n",
       "        <td>None</td>\n",
       "        <td>SOMERVILLE</td>\n",
       "        <td>MA</td>\n",
       "        <td>66228</td>\n",
       "        <td>799</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>HILLARY FOR AMERICA</td>\n",
       "        <td>DEM</td>\n",
       "        <td>NEW YORK</td>\n",
       "        <td>NY</td>\n",
       "        <td>38002</td>\n",
       "        <td>252</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>BERNIE 2016</td>\n",
       "        <td>DEM</td>\n",
       "        <td>BURLINGTON</td>\n",
       "        <td>VT</td>\n",
       "        <td>19920</td>\n",
       "        <td>239</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>DCCC</td>\n",
       "        <td>DEM</td>\n",
       "        <td>WASHINGTON</td>\n",
       "        <td>DC</td>\n",
       "        <td>26040</td>\n",
       "        <td>153</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>DSCC</td>\n",
       "        <td>DEM</td>\n",
       "        <td>WASHINGTON</td>\n",
       "        <td>DC</td>\n",
       "        <td>7271</td>\n",
       "        <td>71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>END CITIZENS UNITED</td>\n",
       "        <td>None</td>\n",
       "        <td>WASHINGTON</td>\n",
       "        <td>DC</td>\n",
       "        <td>1129</td>\n",
       "        <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>HILLARY VICTORY FUND</td>\n",
       "        <td>None</td>\n",
       "        <td>NEW YORK</td>\n",
       "        <td>NY</td>\n",
       "        <td>44163</td>\n",
       "        <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>MOVEON.ORG POLITICAL ACTION</td>\n",
       "        <td>None</td>\n",
       "        <td>WASHINGTON</td>\n",
       "        <td>DC</td>\n",
       "        <td>2836</td>\n",
       "        <td>37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CATHERINE CORTEZ MASTO FOR SENATE</td>\n",
       "        <td>DEM</td>\n",
       "        <td>LAS VEGAS</td>\n",
       "        <td>NV</td>\n",
       "        <td>6878</td>\n",
       "        <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>EMILY&#x27;S LIST</td>\n",
       "        <td>None</td>\n",
       "        <td>WASHINGTON</td>\n",
       "        <td>DC</td>\n",
       "        <td>7988</td>\n",
       "        <td>26</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('ACTBLUE', None, 'SOMERVILLE', 'MA', 66228, 799),\n",
       " ('HILLARY FOR AMERICA', 'DEM', 'NEW YORK', 'NY', 38002, 252),\n",
       " ('BERNIE 2016', 'DEM', 'BURLINGTON', 'VT', 19920, 239),\n",
       " ('DCCC', 'DEM', 'WASHINGTON', 'DC', 26040, 153),\n",
       " ('DSCC', 'DEM', 'WASHINGTON', 'DC', 7271, 71),\n",
       " ('END CITIZENS UNITED', None, 'WASHINGTON', 'DC', 1129, 47),\n",
       " ('HILLARY VICTORY FUND', None, 'NEW YORK', 'NY', 44163, 45),\n",
       " ('MOVEON.ORG POLITICAL ACTION', None, 'WASHINGTON', 'DC', 2836, 37),\n",
       " ('CATHERINE CORTEZ MASTO FOR SENATE', 'DEM', 'LAS VEGAS', 'NV', 6878, 27),\n",
       " (\"EMILY'S LIST\", None, 'WASHINGTON', 'DC', 7988, 26)]"
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = '''\n",
    "WITH cmte_count AS\n",
    "(\n",
    "SELECT\n",
    "    cmte_id AS committee_id,\n",
    "    SUM(transaction_amt) AS total_amount,\n",
    "    COUNT(cmte_id) AS count\n",
    "FROM indiv_sample\n",
    "GROUP BY committee_id\n",
    ")\n",
    "SELECT \n",
    "    cmte_nm AS committee_name, \n",
    "    cmte_pty_affiliation AS party_affiliation,\n",
    "    cmte_city AS city,\n",
    "    cmte_st AS state,\n",
    "    i.total_amount AS total_amount,\n",
    "    i.count AS count\n",
    "FROM comm c JOIN cmte_count i ON committee_id = cmte_id\n",
    "GROUP BY  c.cmte_nm, c.cmte_pty_affiliation, c.cmte_city, c.cmte_st, i.total_amount, i.count\n",
    "ORDER BY count DESC, committee_name\n",
    "LIMIT 10\n",
    "'''\n",
    "%sql $query"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Looking at the table, 5 out of the 10 top committee's are affiliated with the democratic party (DEM). I want to see from all committees that appear in indiv_sample, meaning recieved a donation from an individual in Berkeley, CA, what is the distribution of party affiliation."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "6 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>party_affiliation</th>\n",
       "        <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>DEM</td>\n",
       "        <td>58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "        <td>44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>REP</td>\n",
       "        <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>UNK</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>NNE</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>GRE</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('DEM', 58), (None, 44), ('REP', 8), ('UNK', 4), ('NNE', 2), ('GRE', 1)]"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = '''\n",
    "WITH cmte AS\n",
    "(\n",
    "  SELECT \n",
    "      cmte_id AS cmte_id\n",
    "  FROM indiv_sample \n",
    "  GROUP BY cmte_id\n",
    ")\n",
    "SELECT \n",
    "    cmte_pty_affiliation AS party_affiliation,\n",
    "    COUNT(*) AS count\n",
    "FROM comm c JOIN cmte i on c.cmte_id = i.cmte_id\n",
    "GROUP BY party_affiliation\n",
    "ORDER BY count DESC, party_affiliation\n",
    "LIMIT 10\n",
    "'''\n",
    "%sql $query"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Hypothesis Testing"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Is there a difference in the proportion of donations made by small donors (under \\$200) in Berkeley to Hillary Clinton's and Bernie Sanders' campaigns?**\n",
    "\n",
    "To formulate a null hypothesis, it is important to consider the how the data is generated. I know that the sample was taken at random from the collection of all donations from Berkeley donors. I can argue that Clinton attracts small donors at the same rate as Sanders. Then, given the proportion of donors in the combined sample of Clinton and Sanders donors, it is just by chance that the proportion for Sanders and the proportion for Clinton are not equal. \n",
    "\n",
    "**Permutation Test:**  In a permuation test, or randomization test, I will mix up the small and large donors, and then record the number of small donors to Clinton and to Sanders. After many permutations, it then becomes easier to examine the distribution of differences to see how likely it was to have observed our sample's difference. \n",
    "\n",
    "**Formal Hypothesis:**\n",
    "\n",
    "**$H_o$:** Clinton and Sanders attract the same proportion of small donors. Any observed difference in our sample is simply due to chance variation.\n",
    "\n",
    "**$H_A$:**  The difference is real. \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I have taken a simple random sample of 2000 rows of the original FEC data for individual contributions that only include contributions from Berkeley. This sample is stored in the table `indiv_sample`.\n",
    "\n",
    "The individual contributions of donors are linked to committees rather than candidates directly. Hillary's primary committee was called `HILLARY FOR AMERICA` and Bernie's was `BERNIE 2016`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n",
      "Done.\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>transaction_amt</th>\n",
       "        <th>cmte_nm</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>HAHN, SOPHIE</td>\n",
       "        <td>2700</td>\n",
       "        <td>HILLARY FOR AMERICA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>JOSEPH, DAVID</td>\n",
       "        <td>2700</td>\n",
       "        <td>HILLARY FOR AMERICA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>LITTMANN, NICOLE</td>\n",
       "        <td>2700</td>\n",
       "        <td>BERNIE 2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>LOGAN, JONATHAN</td>\n",
       "        <td>2700</td>\n",
       "        <td>BERNIE 2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>REINIS, JONATHAN ROY</td>\n",
       "        <td>2700</td>\n",
       "        <td>HILLARY FOR AMERICA</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('HAHN, SOPHIE', 2700, 'HILLARY FOR AMERICA'),\n",
       " ('JOSEPH, DAVID', 2700, 'HILLARY FOR AMERICA'),\n",
       " ('LITTMANN, NICOLE', 2700, 'BERNIE 2016'),\n",
       " ('LOGAN, JONATHAN', 2700, 'BERNIE 2016'),\n",
       " ('REINIS, JONATHAN ROY', 2700, 'HILLARY FOR AMERICA')]"
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "CREATE OR REPLACE VIEW contribs AS\n",
    "SELECT name, transaction_amt, c.cmte_nm\n",
    "FROM indiv_sample i JOIN comm c ON c.cmte_id = i.cmte_id\n",
    "WHERE c.cmte_nm = 'HILLARY FOR AMERICA' OR c.cmte_nm = 'BERNIE 2016'\n",
    "ORDER BY transaction_amt DESC, name\n",
    "\"\"\"\n",
    "%sql DROP VIEW IF EXISTS contribs\n",
    "%sql $query\n",
    "%sql SELECT * FROM contribs LIMIT 5"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I am going to create a table grouping the tranactions according to the candidate. Then I will create a column with the number of donations classified as small (under $200) and a column with the total number of donations. \n",
    "\n",
    "From here I will be able to calculate a statistic as follows:\n",
    "\n",
    "$$ \\frac {\\textrm{total donations under \\$200 to Hillary}} {\\textrm{total donations to Hillary}} - \n",
    "    \\frac {\\textrm{total donations under \\$200 to Bernie}} {\\textrm{total donations to Bernie}}. \n",
    "$$\n",
    "\n",
    "The calculation of the stat will be completed using pandas dataframe and python."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>cmte_nm</th>\n",
       "        <th>small</th>\n",
       "        <th>total</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>BERNIE 2016</td>\n",
       "        <td>212</td>\n",
       "        <td>239</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>HILLARY FOR AMERICA</td>\n",
       "        <td>225</td>\n",
       "        <td>252</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('BERNIE 2016', 212, 239), ('HILLARY FOR AMERICA', 225, 252)]"
      ]
     },
     "execution_count": 130,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = '''\n",
    "WITH marked AS (\n",
    "    SELECT *,\n",
    "    transaction_amt <= 200 AS small \n",
    "    FROM contribs\n",
    ")\n",
    "SELECT cmte_nm,\n",
    "    COUNT(CASE WHEN small THEN 1 END) AS small,\n",
    "    COUNT(*) AS total\n",
    "FROM marked\n",
    "GROUP BY cmte_nm\n",
    "ORDER BY cmte_nm\n",
    "'''\n",
    "\n",
    "df = pd.DataFrame(connection.execute(query).fetchall(), columns=['cmte_nm', 'small', 'total'])\n",
    "\n",
    "# Fill this in with the observed test statistic\n",
    "observed_stat = (df.iloc[1]['small']/df.iloc[1]['total']) - (df.iloc[0]['small']/df.iloc[0]['total'])\n",
    "\n",
    "%sql $query"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 136,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.0058278541542140516"
      ]
     },
     "execution_count": 136,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "observed_stat"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Even though the difference is quite small, this difference could still be considered large relative to the standard error of the statistic. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Bootstrapping\n",
    "\n",
    "1. Shuffle the observed values between the two samples. In this case, shuffle the contributions between Hillary and Bernie. \n",
    "2. Recompute the test statistic on the shuffled sample.\n",
    "3. Repeat.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n",
      "Done.\n",
      "10 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>row_id</th>\n",
       "        <th>transaction_amt</th>\n",
       "        <th>cmte_nm</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>2700</td>\n",
       "        <td>HILLARY FOR AMERICA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>2700</td>\n",
       "        <td>HILLARY FOR AMERICA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>2700</td>\n",
       "        <td>BERNIE 2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>2700</td>\n",
       "        <td>BERNIE 2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>2700</td>\n",
       "        <td>HILLARY FOR AMERICA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>6</td>\n",
       "        <td>2700</td>\n",
       "        <td>HILLARY FOR AMERICA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>7</td>\n",
       "        <td>2700</td>\n",
       "        <td>HILLARY FOR AMERICA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>8</td>\n",
       "        <td>2700</td>\n",
       "        <td>HILLARY FOR AMERICA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>9</td>\n",
       "        <td>2500</td>\n",
       "        <td>HILLARY FOR AMERICA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10</td>\n",
       "        <td>1000</td>\n",
       "        <td>HILLARY FOR AMERICA</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 2700, 'HILLARY FOR AMERICA'),\n",
       " (2, 2700, 'HILLARY FOR AMERICA'),\n",
       " (3, 2700, 'BERNIE 2016'),\n",
       " (4, 2700, 'BERNIE 2016'),\n",
       " (5, 2700, 'HILLARY FOR AMERICA'),\n",
       " (6, 2700, 'HILLARY FOR AMERICA'),\n",
       " (7, 2700, 'HILLARY FOR AMERICA'),\n",
       " (8, 2700, 'HILLARY FOR AMERICA'),\n",
       " (9, 2500, 'HILLARY FOR AMERICA'),\n",
       " (10, 1000, 'HILLARY FOR AMERICA')]"
      ]
     },
     "execution_count": 121,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sample = '''\n",
    "DROP VIEW IF EXISTS sample CASCADE;\n",
    "\n",
    "CREATE OR REPLACE VIEW sample AS \n",
    "    SELECT row_number() over () AS row_id, transaction_amt, cmte_nm\n",
    "    FROM contribs;\n",
    "\n",
    "SELECT * FROM sample LIMIT 10\n",
    "'''\n",
    "\n",
    "%sql $sample"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "My sample table contains 491 rows. I need to create a table called design that holds the random shuffles of this data. I will be performing 1000 reshuffles so therefore my new table will contain 491,000 rows, each with a trial_id and a row_id. \n",
    "\n",
    "- trial_id: the number of the trial from 1 to 1000\n",
    "- row_id: row number shuffled, between 1 and 491"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "10 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>trial_id</th>\n",
       "        <th>row_id</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>483</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>224</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>269</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>121</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>220</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>377</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>460</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>409</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 483),\n",
       " (1, 224),\n",
       " (1, 269),\n",
       " (1, 121),\n",
       " (1, 99),\n",
       " (1, 220),\n",
       " (1, 377),\n",
       " (1, 25),\n",
       " (1, 460),\n",
       " (1, 409)]"
      ]
     },
     "execution_count": 124,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "n_rows = 491\n",
    "n_trials = 1000\n",
    "\n",
    "query = \"\"\"\n",
    "CREATE VIEW design AS\n",
    "SELECT \n",
    "    generate_series(1, $n_trials) AS trial_id,\n",
    "    generate_series(1, $n_rows) AS row_id \n",
    "ORDER BY trial_id, RANDOM()\n",
    "LIMIT 491000\n",
    "\"\"\"\n",
    "import string\n",
    "query = string.Template(query).substitute(locals())\n",
    "\n",
    "seed = 0.42\n",
    "%sql drop view if exists design cascade\n",
    "%sql SET SEED TO $seed\n",
    "%sql $query\n",
    "%sql select * from design limit 10"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I am now able to construct the table with the shuffled transaction amounts (transaction_amt) along with the unshuffled committee names. Essentially, I am constructing a table with shuffled transaction values between the committees."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "10 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>trial_id</th>\n",
       "        <th>transaction_amt</th>\n",
       "        <th>cmte_nm</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>3</td>\n",
       "        <td>HILLARY FOR AMERICA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>37</td>\n",
       "        <td>HILLARY FOR AMERICA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>25</td>\n",
       "        <td>BERNIE 2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>100</td>\n",
       "        <td>BERNIE 2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>100</td>\n",
       "        <td>HILLARY FOR AMERICA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>38</td>\n",
       "        <td>HILLARY FOR AMERICA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>15</td>\n",
       "        <td>HILLARY FOR AMERICA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>270</td>\n",
       "        <td>HILLARY FOR AMERICA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>5</td>\n",
       "        <td>HILLARY FOR AMERICA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>10</td>\n",
       "        <td>HILLARY FOR AMERICA</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 3, 'HILLARY FOR AMERICA'),\n",
       " (1, 37, 'HILLARY FOR AMERICA'),\n",
       " (1, 25, 'BERNIE 2016'),\n",
       " (1, 100, 'BERNIE 2016'),\n",
       " (1, 100, 'HILLARY FOR AMERICA'),\n",
       " (1, 38, 'HILLARY FOR AMERICA'),\n",
       " (1, 15, 'HILLARY FOR AMERICA'),\n",
       " (1, 270, 'HILLARY FOR AMERICA'),\n",
       " (1, 5, 'HILLARY FOR AMERICA'),\n",
       " (1, 10, 'HILLARY FOR AMERICA')]"
      ]
     },
     "execution_count": 125,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = '''\n",
    "CREATE VIEW joined as\n",
    "WITH num AS (\n",
    "    SELECT MOD(row_number() over () - 1, $n_rows) + 1 AS row_in_group, *\n",
    "    FROM design\n",
    ")\n",
    "SELECT n.trial_id, s1.transaction_amt, s2.cmte_nm\n",
    "    FROM sample s1, sample s2, num n \n",
    "    WHERE n.row_id = s1.row_id\n",
    "    AND s2.row_id = n.row_in_group\n",
    "'''\n",
    "\n",
    "query = string.Template(query).substitute(locals())\n",
    "%sql drop view if exists joined cascade\n",
    "%sql SET SEED TO $seed\n",
    "%sql $query\n",
    "%sql select * from joined limit 10"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For the 1000 trials, I am then able to calculate the number of small donors by candidate. This results in 2000 rows, 2 per trial. \n",
    "\n",
    "I will then calculate the observed statistic, as I did previously, for each of the 1000 trials. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n",
      "2000 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>stats</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>-0.002325</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.005828</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>-0.010477</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>-0.043086</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.005828</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0.013980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>-0.018629</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>-0.002325</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>0.022133</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>0.005828</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      stats\n",
       "0 -0.002325\n",
       "1  0.005828\n",
       "2 -0.010477\n",
       "3 -0.043086\n",
       "4  0.005828\n",
       "5  0.013980\n",
       "6 -0.018629\n",
       "7 -0.002325\n",
       "8  0.022133\n",
       "9  0.005828"
      ]
     },
     "execution_count": 129,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = '''\n",
    "WITH marked AS (\n",
    "    SELECT  *,\n",
    "        transaction_amt <= 200 AS small \n",
    "    FROM joined\n",
    ")\n",
    "SELECT trial_id, cmte_nm,\n",
    "    count(CASE WHEN small THEN 1 END) AS small,\n",
    "    COUNT(*) AS total\n",
    "FROM marked\n",
    "GROUP BY trial_id, cmte_nm\n",
    "ORDER BY trial_id, cmte_nm\n",
    "'''\n",
    "\n",
    "query = string.Template(query).substitute(locals())\n",
    "%sql SET SEED TO $seed\n",
    "%sql $query\n",
    "\n",
    "df = pd.DataFrame(connection.execute(query).fetchall(),\n",
    "                      columns=['trial_id', 'cmte_nm', 'small', 'total'])\n",
    "\n",
    "trials = []\n",
    "i = 1\n",
    "while i < len(df):\n",
    "    trials.append((df.iloc[i]['small']/df.iloc[i]['total']) - (df.iloc[i-1]['small']/df.iloc[i-1]['total']))\n",
    "    i += 2\n",
    "trials = pd.DataFrame(trials, columns=['stats'])\n",
    "trials[0:10]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Using Seaborn's displot tool, I can plot the observed statistic on each of the 1000 trials and compare them with the actual statistic, represeted by the 2 vertical red lines. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAecAAAFXCAYAAACYx4YhAAAABHNCSVQICAgIfAhkiAAAAAlwSFlz\nAAALEgAACxIB0t1+/AAAIABJREFUeJzt3Xt0VOW9N/DvnlsymZlkcpncyYVAuCMEiyJyEfSltfpW\nRA7CaWiFetoubavLitXXY7W2gFpP1yq1trW3UzweQU57pFWpRVAqInIxQAIBCUkg98l9Lpnr3u8f\nIZFwSWYmM9l7Zr6ftVgkmWf2/PbOznxnP3s/zxYkSZJAREREiqGSuwAiIiIaiuFMRESkMAxnIiIi\nhWE4ExERKQzDmYiISGEYzkRERAqjkbuAAVarLeC2qalJ6OpyRrAauhy3+eilzZkOAOg8UhlQ+0C3\nebDLpWvjfj724nmbWyymaz4WlUfOGo1a7hLiDrf52OM2H3vc5mOP2/zqojKciYiIYhnDmYiISGEY\nzkRERArDcCYiIlIYhjMREZHCMJyJiIgUhuFMRESkMAxnIiIihWE4ExERKcyw03d6vV488cQTaGxs\nhMfjwbe//W1MmDABP/jBDyAIAiZOnIgf/vCHUKk+z3hRFPH000/j9OnT0Ol0+PGPf4zCwsKIrwgR\nEVGsGPbIeefOnTCbzXjttdfw29/+Fs8++yw2bdqEhx56CK+99hokScJ777035Dm7d++Gx+PBtm3b\n8Mgjj2Dz5s0RXQEiIqJYM2w4f/GLX8T3vvc9AIAkSVCr1aiqqsLcuXMBAAsXLsRHH3005DlHjhzB\nggULAACzZs1CZSUn4yciIgrGsN3aBoMBAGC32/Hd734XDz30EJ577jkIgjD4uM029G5SdrsdRqNx\n8Hu1Wg2fzweNZvgbYKWmJgU1Afpwd/OgyOA2HyVV/99NMNsxoLYhLPdSuw7UBdX+i/OKQnqdaMH9\nfOxxm19pxFtGNjc344EHHsCaNWtw55134oUXXhh8zOFwIDk5eUh7o9EIh8Mx+L0oiiMGM4Cgbhlm\nsZiCusUkjR63+eiliRIAoDPA7RjoNg92uZez2V1BtY/l/YD7+diL520+3IeSYVOzvb0d69atw1NP\nPYV58+YBAKZOnYqDBw/ihhtuwL59+3DjjTcOeU5ZWRn27t2L22+/HRUVFSgtLQ3DKhDFjvcrGgNq\nZzImwmZ3YfGsvAhXRERKM+w551/96lfo7e3FL3/5S5SXl6O8vBwPPfQQtmzZglWrVsHr9WLZsmUA\ngA0bNqCpqQm33XYbdDod7r33XmzatAmPP/74mKwIERFRrBAkSZLkLgIIrqssnrtB5MJtPnppc6YD\nAP78u78H1D7QI+eB5XYeCe3iy0CP5AfE8pE89/OxF8/bfLhubU5CQkREpDAMZyIiIoVhOBMRESkM\nw5mIiEhhGM5EREQKw3AmIiJSGIYzERGRwjCciYiIFIbhTEREpDAMZyIiIoVhOBMRESkMw5mIiEhh\nGM5EREQKM+z9nIlIfiPdNep2j29Iu1i+axRRvOCRMxERkcIwnImIiBSG4UxERKQwDGciIiKFYTgT\nEREpDMOZiIhIYRjORERECsNwJiIiUhiGMxERkcIwnImIiBSG4UxERKQwDGciIiKFYTgTEREpTEB3\npTp27Bh++tOfYuvWrXj44YfR3t4OAGhsbMR1112Hn/3sZ0PaL1++HEajEQCQn5+PTZs2hblsIiKi\n2DViOL/yyivYuXMn9Ho9AAwGcU9PD9auXYvHH398SHu32w1JkrB169YIlEtERBT7RuzWLigowJYt\nW674+ZYtW/DVr34VmZmZQ35eXV2Nvr4+rFu3DmvXrkVFRUX4qiUiIooDIx45L1u2DA0NDUN+1tHR\ngQMHDlxx1AwAiYmJWL9+PVauXIm6ujrcf//92LVrFzSa4V8qNTUJGo064MItFlPAbSk8uM1HSSUA\nAEzGxICfEkhblTB0ucH+noKpJ5TlR5tYXz8l4ja/UkDnnC+3a9cu3HHHHVCrrwzT4uJiFBYWQhAE\nFBcXw2w2w2q1IicnZ9hldnU5A359i8UEq9UWdN0UOm7z0UsTJQCAze4KqL3JmBhQW1Eautxgf0+B\n1jMglvcD7udjL563+XAfSkK6WvvAgQNYuHDhVR/bsWMHNm/eDABobW2F3W6HxWIJ5WWIiIjiUkjh\nXFtbi3Hjxg352YYNG9DU1IR77rkHNpsNq1evxsMPP4yNGzeO2KVNREREnwsoNfPz87F9+/bB7996\n660r2jz//PODX7/44othKI2IiCg+cRISIiIihWE4ExERKQzDmYiISGEYzkRERArDcCYiIlIYjnEi\nGqX3KxoDane7xxfhSogoVvDImYiISGEYzkRERArDcCYiIlIYhjMREZHCMJyJiIgUhuFMRESkMAxn\nIiIihWE4ExERKQzDmYiISGEYzkRERArDcCYiIlIYhjMREZHCMJyJiIgUhuFMRESkMAxnIiIihWE4\nExERKQzDmYiISGEYzkRERArDcCYiIlIYhjMREZHCMJyJiIgUhuFMRESkMAGF87Fjx1BeXg4AOHny\nJBYsWIDy8nKUl5fj7bffHtJWFEU89dRTWLVqFcrLy1FfXx/+qomIiGKYZqQGr7zyCnbu3Am9Xg8A\nqKqqwn333Yd169Zdtf3u3bvh8Xiwbds2VFRUYPPmzXj55ZfDWzVRnJAkCW6vH26PCLfXD1GUoE9Q\nQ5+ggVajgiAIcpdIRBEwYjgXFBRgy5Yt2LBhAwCgsrIStbW1eO+991BYWIgnnngCRqNxsP2RI0ew\nYMECAMCsWbNQWVkZodKJYpNflNDS4UTLmXaca+xGn9t/1XZatQqW1ETc6hWhVguQJIlhTRQjRgzn\nZcuWoaGhYfD7mTNnYuXKlZg+fTpefvllvPTSS3jssccGH7fb7UPCWq1Ww+fzQaMZ/qVSU5Og0agD\nLtxiMQXclsKD2/zqTMbEgNqpLgbntdpLkoSzDd348FgTnC4fACBRp0ZRTjL0CRok6tRQqwQ43T44\n+rzodXjQ1O6Ex+cHfMDfDtRjdmkmzKkGaDWBX04SaP0DYn0/iPX1UyJu8yuNGM6Xu+2225CcnDz4\n9bPPPjvkcaPRCIfDMfi9KIojBjMAdHU5A67BYjHBarUF3J5Gj9v82mx2V0DtREm6Znu704uDJ1vR\n2O6AWiVgcoEZk4vTYUxQQ6W69tFwn9uHxD9p4POL6La5sefwBXx6ug13LxyP+TNywlr/gFjeD7if\nj7143ubDfSgJ+mrt9evX4/jx4wCAAwcOYNq0aUMeLysrw759+wAAFRUVKC0tDfYliOJKU7sDO/fX\norHdgZz0JNw5vwhzp2Yhz2IcNpgBQJ+ggUYtIFGnxt0Lx2NqUSqcLh9+99Yp/PGdanh9V+8SJyJl\nC/rI+emnn8azzz4LrVaLjIyMwSPnDRs24KGHHsJtt92G/fv3495774UkSdi4cWPYiyaKFU3tDuw9\n2ggJwPwZ2RifmxzyeWODXovrJ2fia1+cjJf+cgL7jjXhfKsNDyyfgfSU4LquiUheAYVzfn4+tm/f\nDgCYNm0aXn/99SvaPP/884Nf/+hHPwpTeUSxq9HqwN5PGwEAt8zOQ57FEJblWsx6PPHVOdj67mns\nP9GCja8ewVNfux4pxoSwLJ+IIo+TkBDJoKXTib1HGyEAWFIWvmAeoNOqse72KVi+cDy6bG689L+V\n8PnFsL4GEUUOw5lojPW5ffjnsSZIkHBLWR5yM8IbzAMEQcAd8woxd0omzjb04LXdn0XkdYgo/BjO\nRGNs/4lm9Ln9mF1qiVgwDxAEAfd9aQryLUa8/2kj9h1riujrEVF4MJyJxpDHK6Kp3Ym8DAOmFaWO\nyWsm6NT4zooZMCRq8Oq7p9EaxLBFIpIHw5lojPhFCR6fH/oEDebPzB7T2bwsZj3Kl02Czy/hzx+c\nG7PXJaLQMJyJxoAoSnB7+sccL5iZg0Rd0KMYR+0LkzNRnJOMQ9VtqG3uHfPXJ6LAMZyJxsCp+i6I\nkgStWoXs9CRZahAEAf9ySwkAYPues5AuzlhGRMrDcCaKMKfLi2Nn2yFAgE4b+PzxkTCpIBUzS9Jx\n+kI3jtd0yFoLEV0bw5kowg5XW+HzS9BpVVDCTaPuWVwCQQB2fFADUeTRM5ESMZyJIqi5w4G6Fhsy\nUhKDulNUJOVbjJg/PQeNVgcOn26TuxwiugplvFsQxSBRknDoVH/4zZ2aJXM1Q33pxgIAwPsXpw8l\nImUZ+0tGicbY+xXBBdDiWXlhed26Zhu67R6Mz01GhsJuPJGTbsDkAjOqz3djcmEqkg06uUsiokvw\nyJkoAkRRwrGz7VAJwHUT0uUu56oWXfwQcuZCt8yVENHlGM5EEVDT2AOb04sJ+WaYkpR5VFpWaoEp\nSYuzjT3w86YYRIrCcCYKM78o4lhNB9QqATNLlHnUDABajQo3z8iBxyuivtUudzlEdAmGM1GYnTnf\nA6fLh0kFZiQlKvuyjoWzcgGwa5tIaRjORGHk84s4ca4DGrWA6ePT5C5nRFmpSchJT0JbVx+67W65\nyyGiixjORGFU09gDl8ePyQWpssyfHYrScWYAwNmGHpkrIaIBDGeiMBFFCVW1XVCpBEwZo9tBhkO+\nxQCNWsD5Vjvn2yZSCIYzUZjUt9hg7/NiQl4K9AnRcdQMAGq1CvmZRtj7vOjsZdc2kRIwnInCQJIk\nVNZ2QgAwrTh6jpoHFGaZAADnW20yV0JEAMOZKCya2h3osrlRmGNS7Ljm4eRd7Nqua7Gxa5tIARjO\nRGFQea4TADAjCq7QvhqNWoW8DANsTi+67R65yyGKewxnolGydvehtasPeRkGpJqUNYd2MAqy+7u2\n61vYtU0kN4Yz0Sidqu8CAEyNwnPNl8q3GKFSCTzvTKQADGeiUeiyuVHfYoPZqEN2WpLc5YyKVqNC\nboYB3XYPejghCZGsGM5Eo7D300ZIEjC5MBWCIMhdzqgVZhkBgHNtE8mM4UwUIq/Pjw8qGqHTqjA+\nN1nucsJiXKYRggBcaGM4E8kpoJkSjh07hp/+9KfYunUrTp06hWeffRZqtRo6nQ7PPfccMjIyhrRf\nvnw5jMb+T+D5+fnYtGlT+CsnktnHJ1thc3oxrTgNGnVsfM7VadXINOvR2tWHPrcvqiZTIYolI/7l\nvfLKK9i5cyf0ej0A4Cc/+Qn+/d//HVOmTMHrr7+OV155BY8//vhge7fbDUmSsHXr1shVTSQzSZKw\n+3ADVIKASQVmucsJqzyLAa1dfWhqd6AkL0Xucoji0ogf9wsKCrBly5bB7//jP/4DU6ZMAQD4/X4k\nJCQMaV9dXY2+vj6sW7cOa9euRUVFRZhLJpLfmQvduNBmR1lpBox6rdzlhFWepb/Xq9HqkLkSovg1\n4pHzsmXL0NDQMPh9ZmYmAODo0aN49dVX8V//9V9D2icmJmL9+vVYuXIl6urqcP/992PXrl3QaIZ/\nqdTUJGg06oALt1hMAbel8IjWbW4yBjf2OJD1/OOu0wCAFUtL0RDg+VnVxQvGgqknkLaXLzfY39Pl\nr2E0JMCo16K5wwmDIWFw+QOidT8IVKyvnxJxm18ppBNKb7/9Nl5++WX85je/QVra0BmRiouLUVhY\nCEEQUFxcDLPZDKvVipycnGGX2dXlDPj1LRYTrFaOxRxL0bzNbXZXUO1HWs9epwf7jzchJz0JmSYd\nTp0LbPnixWkxA63HZEwMqO3lyw3293S118hJT8JnDT2obehGZqp+yGPRuh8EIpr382gVz9t8uA8l\nQV/F8uabb+LVV1/F1q1bMW7cuCse37FjBzZv3gwAaG1thd1uh8ViCfZliBRr/4lm+EUJi2blxcTw\nqavJsxgAAI3t7NomkkNQ4ez3+/GTn/wEDocD3/nOd1BeXo6f//znAIANGzagqakJ99xzD2w2G1av\nXo2HH34YGzduHLFLmyhaSJKEDyqaoFGrcNP0bLnLiZicdANUAtBo5ZAqIjkElJr5+fnYvn07AOCT\nTz65apvnn39+8OsXX3wxDKURKU91fRfauvowb1p2zF0IdimtRoXMtCS0dDg5pIpIBrExOJNojLxf\n0QQAWDw7V+ZKIi8v42LXNq/aJhpzDGeiAPU6PDh6xoq8DAMmxMH4X553JpIPw5koQB8OXgiWG7MX\ngl0qxaDrH1LV7oAoSnKXQxRXGM5EAZAkCR8eb4ZGrcK8GL4Q7FKCICA3Iwken4iOnuCGoxHR6DCc\niQJwrqkXLZ1OlJVmwJAYuxeCXS4nvb9ru6mDXdtEY4nhTBSA/SeaAQA3zxh+Mp1Yk5OeBEEAmnje\nmWhMMZyJRuDx+nHwVBvMRh2mFqWN/IQYotOqkZGiR3u3C26vX+5yiOIGw5loBJ9+1o4+tw83Tc+B\nShX7F4JdLjcjCRKAlo7Ap9glotFhOBONYKBLe/6M+LgQ7HK5F8c7s2ubaOwwnImG0WVzo6quEyV5\nyYMXR8Wb9JRE6LQqNLU7IEkcUkU0FjgnH9EwPqpshiQB8+PsQrBLqQQBOekG1LfY0Ovw4v2KxqCe\nv3hWXoQqI4pdPHImugZJkvBRZQs0ahXmTs6UuxxZ5WYkAWDXNtFYYTgTXUOj1YHmDieuK0lHUhyN\nbb6aXI53JhpTDGeia/ikuhUA8IUp8X3UDAAGvRYpBh1aO53wi6Lc5RDFPIYz0VVIkoRPTrVBp1Xh\nupIMuctRhNwMA3x+CW1dfXKXQhTzGM5EV3G+1Y62rj5cV5KBBJ1a7nIU4fPzzhzvTBRpDGeiq/jk\nVH+X9lx2aQ/KSkuCSiXwojCiMcBwJrqMJEk4VN2GBJ0aM8any12OYmjUKmSl6tFlc6PP7ZO7HKKY\nxnAmukxtsw3tPS7MnpgBnZZd2pfK4WxhRGOC4Ux0mcEu7clZMleiPHkXzzs3c55toohiOBNdYqBL\nW5+gwbTi+LoDVSDMxgToE9ScypMowhjORJewdvehy+ZGWWkGtBr+eVxOuDiVp8vjR6fNLXc5RDGL\n7z5El6hrtgEA5k5hl/a18C5VRJHHcCa6SJQk1LfaYNRrMaUwVe5yFCsn/eJ5Z453JooYhjPRRW2d\nfehz+1FWaoFGzT+Na9EnaJCWnIC2Lie8Pk7lSRQJfAciuqiupRcAJx4JRG6GAaIEtHTy6JkoEhjO\nRABEUcL5VjsSdWpMKjDLXY7i5Vv6zzs3tNllroQoNgUUzseOHUN5eTkAoL6+HqtXr8aaNWvwwx/+\nEOJld6gRRRFPPfUUVq1ahfLyctTX14e/aqIwa+l0wuXxozDbBLWKn1lHkmHWI0GrRoPVziFVRBEw\n4rvQK6+8gieffBJud/+wiU2bNuGhhx7Ca6+9BkmS8N577w1pv3v3bng8Hmzbtg2PPPIINm/eHJnK\nicKorqX/Ku2ibJPMlUQHlSAgz2JAn9uPjl6X3OUQxZwRw7mgoABbtmwZ/L6qqgpz584FACxcuBAf\nffTRkPZHjhzBggULAACzZs1CZWVlOOslCju/KOF8qw36BDUsqXq5y4ka4zKNAICGNg6pIgo3zUgN\nli1bhoaGhsHvJUmCIAgAAIPBAJvNNqS93W6H0Wgc/F6tVsPn80GjGf6lUlOToNEEPo+xxcIjnLEW\nrdvcZEwc9vG65l54vCJmTshAikkf9HqOtPwBqot/N4G2D7Tt5cuNVP2XKy3U4p/Hm9HU4cCC2fnX\nbBdt+0201RsLuM2vNGI4X051yfk4h8OB5OTkIY8bjUY4HJ9/khZFccRgBoCursCv+rRYTLBabSM3\npLCJ5m1usw/f7XqqtgMAkJueBJvdFfR6jrT8AeLFc7OBtjcZEwNqe/lyI1X/1WSl6tHc4USL1QaD\nXnvVNtG030Tzfh6t4nmbD/ehJOgrX6ZOnYqDBw8CAPbt24frr79+yONlZWXYt28fAKCiogKlpaXB\nvgTRmPGLIi602ZGUqIHFHNoRZDwb6Nq+YOVV20ThFHQ4P/bYY9iyZQtWrVoFr9eLZcuWAQA2bNiA\npqYm3HbbbdDpdLj33nuxadMmPP7442Evmihcmtr7J9IoyjYNnq6hwOXzvDNRRATUrZ2fn4/t27cD\nAIqLi/Hqq69e0eb5558f/PpHP/pRmMojiqy65v6JR4pyeM4rFEa9FqmmBLR09H/I4c1CiMKDf0kU\nt3z+/i5to16L9GR2aYcq32KAKEm8EQZRGDGcKW41Wh3w+SV2aY/SuKz+XofzrfF5UQ9RJDCcKW4N\nTjzCLu1RSU9OgFGvxYU2O3x+3giDKBwYzhSXvD4RDW12JCf1nzOl0AmCgOIcE3x+CQ1Wdm0ThQPD\nmeJSg9UOvyihKCeZXdphUJTTP9/BwAV2RDQ6DGeKS3XNnEs7nMxGHVKMOjRYHfD4/HKXQxT1GM4U\ndzw+PxrbHTAbdTCzSzssBEFAcbYJoijxNpJEYcBwprhzodUOUZR41BxmA13btc28aptotBjOFHc+\nv0o7eYSWFIxkgw5pyQloanfA7WHXNtFoBH3jC6Jo5vb60dzuQKopAckGndzlxJyinGR09lpxvtWG\niePMAID3KxqDWsbiWXmRKI0oqvDImeLK+VY7RIkXgkXKwHataeJV20SjwXCmuMK5tCPLqNciJz0J\nbV196La75S6HKGoxnCluuDw+tHQ6kZ6cCFMSu7QjpfRid/aZC90yV0IUvRjOFDfOt9ghSTxqjrRx\nmUboE9Q419jL6TyJQsRwprhRe7FLu5DnmyNKpRIwId8Mj08cnOyFiILDcKa4YHd60drVh6w0PYx6\nrdzlxLyJ+SkA2LVNFCqGM8WFgaPm8bkc2zwWjHot8iwGtPe40NnrkrscoqjDcKaYJ0kSzjX1Qq0S\nUJjFLu2xwgvDiELHcKaY19HrRo/Dg/xMI3RatdzlxI08iwGGRA3ONfXC5fHJXQ5RVGE4U8yrbWKX\nthxUgoCpRWnw+SWcquuSuxyiqMLpOymm+UURtc29SNCqkZdhkLucuDNxXApOnOtA9fluTCtOC6jn\ngtN9EvHImWJcVW0nXB4/inJMUKkEucuJOxq1ClOLUuH1iag+z3PPRIFiOFNM+6iyBQC7tOVUWmCG\nTqPCqboueH2clIQoEAxnilm9Tg+OnrEixaBDRkqi3OXELZ1GjcmFqXB7/fiMV24TBYTnnCkqBXJe\nsvJcB3x+CaXjzBAEdmnLaUphKk7WdaKqrhOlBWZo1DwuIBoO/0IoJkmShDMXeqBWCSjJY5e23BJ0\nakwpTEWf24+TtZ1yl0OkeAxniklN7U7Y+7wozknm2GaFmDY+DYk6NSprO+F0eeUuh0jRGM4Uk05f\nPLdZWmCWuRIaoNOoMXtiBnx+CZ+eaZe7HCJFC+mc85///Gf85S9/AQC43W6cOnUK+/fvR3Jyf/fh\nH//4R7zxxhtIS0sDADzzzDMYP358mEomGp69z4vGNjvSkxN5IZjClOSnoPp8N2qaejG5MBXp/P0Q\nXVVI4Xz33Xfj7rvvBtAfvCtWrBgMZgCorKzEc889h+nTp4enSop5wU48MZyzDT2QwKNmJVIJAr4w\nORPvHrqAQ9VtWDZ3HC/WI7qKUXVrnzhxAmfPnsWqVauG/Lyqqgq/+c1vsHr1avz6178eVYFEwfD7\nRXzW0A2tRoUi3rdZkbLTkzAu04i2rr7Bu4UR0VCjGkr161//Gg888MAVP//yl7+MNWvWwGg04sEH\nH8TevXtxyy23DLus1NQkaDSBX7hjsfCNd6xFcpubjOHp3jxR044+tx+zSy1IMyeFtIxg1zPQ2lUX\njxCDWddA2l6+3EjVH06L54zDf797GoerrSgtTIc+IfS3onDvl3xvGXvc5lcK+S+it7cXtbW1uPHG\nG4f8XJIkfO1rX4PJ1L+xFy1ahJMnT44Yzl1dzoBf22IxwWq1BV80hSzS29xmH/09f/2ihMOnWqFW\nCZiQlxzyMoNdz0BfR5SkoNqbjIkBtb18uZGqP5wEALMmpuNwtRV7D5/HgutyQ15WOPdLvreMvXje\n5sN9KAm5W/vQoUOYN2/eFT+32+2444474HA4IEkSDh48yHPPNCbONfbA6fKhdJx5VEdiNDYmF6Yi\nIyURtc02NLTZ5S6HSFFCDufa2lrk5+cPfv/Xv/4V27Ztg8lkwsMPP4y1a9dizZo1mDBhAhYtWhSW\nYomuRRQlnDjXCZUgYFpxmtzlUABUgoB507MhCMDHJ1s57zbRJUI+vPjGN74x5Ps777xz8Ou77roL\nd911V+hVEQWptrkX9j4vSseZkZTIo+ZokWpKwIzx6The04Ejp9tw47RsuUsiUgROQkJRT5QkVJ7r\nhCAA08fzqDnazChJg9mow5kLPWi0OuQuh0gRGM4U9T670I0ehwcleSkw6rVyl0NBUqtUmD8zB4IA\nHKhsgcfrl7skItkxnCmq9bl9+PRMO7QaFWZPzJC7HApRenIiritJh9Ptwyen2uQuh0h2DGeKakdP\nW+HxiZg9MYNXaEe56ePTkZ6ciHNNvTjfGp9Da4gGMJwparV2OVHT1Iu05ARO1RkDVCoB82dmQ6US\n8HFVK1wen9wlEcmG4UxRSRQlHKxqBQDcMDVrcJYsim5mYwJmT8yAy+PHwapWSBcnWCGKN+wHpIgI\n540sruZYTQe67R5MyE+BxayP6GvR2JpSlIrzrXbUt9pR12JDcU7yyE8iijE8cqao02C140RNB4x6\nLeZMsshdDoWZShAwf0Y2NGoBB0+2wuli9zbFH4YzRRW704sPjzdDpRKwaHYuErSB3yyFokeyQYey\nSRZ4vCI+rmph9zbFHYYzRQ2/KOKDiiZ4vCJumJKJ9OSxv5sSjZ1J48zITktCg9WB+hZevU3xheFM\nUUGUJOw/0YKOXhdK8pIxIT9F7pIowgRBwI3TsqBWCfjkVBvcnJyE4gjDmRRPkiR8XNWKumYbLOZE\n3DA1CwKvzo4LyQYdZk5Ih8vjx5HTVrnLIRozDGdSNEmScLjairMNPUhLTsDSOfnQqLnbxpNpRWlI\nNSXgbEMPWjoDv+87UTTjuxwpliRJOHqmHafqu2A26nDr9fnQ8QKwuKNSCZg3LQsA8HFlC/wiby1J\nsY/hTIokihIOVLWiqrYTpiQtbr1+HBJ1HJYfrzLMekwuMKPX6cWpui65yyGKOIYzKY7PL+L9iiac\nbehBenKfhAevAAAcbUlEQVQCvnhDAe/RTLhuYgYStGocr+ng2GeKeQxnUhS314/dhxvQ0GZHTnoS\n/s/cAt7QggAACVo1ZpdmwOeXcPQMLw6j2MZwJsVwunz4+8HzaOvqQ1G2CUvm5EOr4S5Kn5uQn4K0\n5ASca+qFtbtP7nKIIobvfKQIPXYP3vm4Ht12DyYXmLHguhyoVRwuRUOpBAFfmJIJAPjkVBtnDqOY\nxXAm2XX2urDr4Hk4XD7MnpiBL0zJ5Dhmuqas1CQU5ZjQ0ePCuaZeucshigiGM8mqvceFdw9dgNvr\nx43TsjCjJJ3BTCMqK7VAJQio+KwdXh+HVlHsYTiTbKzdffjHoQvwekXMn5GN0nFmuUuiKGHUazG5\n0AyHy4e9n0b29qREcmA4kyzae/qw+1ADfH4RN8/MQUke58qm4Ewfnw6tRoW/fVTHoVUUcxjONOZ6\nHR7sOdIIn1/Egpk5KM5NlrskikKJOjWmF6fB3ufFrk/q5S6HKKwYzjSm+tw+vHekAS6PHzdMzUJR\nDoOZQje5MBUpRh3e/eQCuu1uucshChuGM40Zr0/EnqONsDm9mFGSjtICnmOm0dFqVPjKzcXw+ET8\n7aM6ucshChuGM40JSZLwUWULOnr678c8a0K63CVRjLh5Rg4s5kR8UNGE9h5OTEKxIeRwXr58OcrL\ny1FeXo7HH398yGN79uzBihUrsGrVKmzfvn3URVL0qz7fjfoWGzJT9Zg3LZvDpShsNGoV/u/8YvhF\niUfPFDNCmrTY7XZDkiRs3br1ise8Xi82bdqEHTt2QK/XY/Xq1ViyZAkyMjJGXSxFJ2t3H45UtyFR\np8bC63Kh4sxfFGbzpmXjrQP1+PB4C26/sRCZqUlyl0Q0KiEdOVdXV6Ovrw/r1q3D2rVrUVFRMfhY\nTU0NCgoKkJKSAp1Ohzlz5uDQoUNhK5iii8vjxwcVTRAlYMF1Oby7FEWESiXgKzcXQ5Qk7NxfJ3c5\nRKMWUjgnJiZi/fr1+N3vfodnnnkG3//+9+Hz9Y8ztNvtMJlMg20NBgPsdnt4qqWoMnCe2enyYdbE\nDOSkG+QuiWLYF6ZkIs9iwIGqFjR3OOQuh2hUQjqMKS4uRmFhIQRBQHFxMcxmM6xWK3JycmA0GuFw\nfP6H4XA4hoT1taSmJkGjUQdcg8Uy8jIpvILZ5iZjIk7Xd6KhzY48ixE3zcyNmvPMwe5bJmNiQO1U\nF9c/0PaBtr18uZGqX6kuXd+1t0/Fpv88hL8fasCj5dePenk0NrjNrxRSOO/YsQNnzpzB008/jdbW\nVtjtdlgsFgBASUkJ6uvr0d3djaSkJBw+fBjr168fcZldXc6AX99iMcFqtYVSOoUo2G3e2m7Hvk8b\noVELmDvFArsjesagBrtv2eyugNqJF++gFGh7kzExoLaXLzdS9SvVpes7IduIgiwj/lnRiFvn5CHf\nYgxqWXxvGXvxvM2H+1ASUrf2PffcA5vNhtWrV+Phhx/Gxo0b8c4772Dbtm3QarX4wQ9+gPXr1+Pe\ne+/FihUrkJWVFXLxFH0kScLHVS3w+ETMmWSBKUknd0kUJwRBwF0LxkMC8OY/a+UuhyhkIR0563Q6\nvPjii0N+VlZWNvj1kiVLsGTJktFVRlHrQFULGqwOZKcl8WYWNOauK0nH+NxkHDljRX2LDYXZ7DKl\n6MNJSCisHC4v/nv3Z9CoBcybnhU155kpdvQfPRcDAP73n+dkroYoNAxnCqudH9bB4fJh5oQMdmeT\nbKYVpWFifgqO1XSgpqlH7nKIgsZwprBp7nBgz9EGZJr1mFLI7mySjyAIWL5gPADgf3numaIQw5nC\nZvues/CLElbeMgFqFXctktfkwlRMKUxFVW0nzlzolrscoqDwHZTCoqq2E8dqOjC5wIyyUk7VSsrw\n+dEzzz1TdGE406j5RRGv7/kMAoB7l07kRWCkGBPyUzB9fBqqz3fjVF2n3OUQBYzhTKO271gzGq0O\n3DwzBwVZHLZCyjJw9PyXf9ZCujhhC5HS8S4ENCpOlw9/2XcOCTo17l44Xu5yiK5QnJOMWRMyUHG2\nHZW1nejovfaMaFeblW3xrLxIl0h0BR4506j87aM62Pu8uGNeIVKMCXKXQ3RVA+Oe/7LvHI+eKSow\nnClkrV1O/OPwBaQnJ+L/fGGc3OUQXVNBlgnXT85EXYsN9S3xOY8zRReGM4Xsjb01F4dOlUAbxB3F\niOSwYtF4qFUCjp5ph18U5S6HaFgMZwrJqfouHD1jxYT8FHxhcqbc5RCNKCs1CUvK8mHv8+J0Pcc9\nk7IxnClooijh9fc+AwCs5tApiiJ3zi+CTqPC8ZoOuD1+ucshuiaGMwXtwxPNuNBmx03Ts1Gckyx3\nOUQBM+q1mFGSDo9PxIlzHXKXQ3RNDGcKSp/bhz/vOwedVoUVi0rkLocoaJMLzTDqtaiu70KvwyN3\nOURXxXCmoLx1oB69Dg9uv7EQqSYOnaLoo1apUDbJAlECPjnVyqFVpEgMZwqYtbsP7x66gFRTApbN\nLZC7HKKQFWYZkZOehKZ2J8632uUuh+gKDGcK2Bvv18DnF7FycQkStBw6RdFLEATcMDULKkHAoVNt\n8Po4tIqUheFMAak614HD1W0oyU3GDVOz5C6HaNSSDTpMH58Gp9uHY2fb5S6HaAiGM41IlCT89s0T\nAHjXKYot08enwajX4lR9F7psbrnLIRrEcKYRHahswdmGHtw4NQsleSlyl0MUNhq1CnOnZkKSgI9O\nNMMv8uIwUgaGMw3L5fFhxwc10Gk4dIpiU77FiPG5yejodeNodZvc5RAB4C0j49b7FY0Btfv0jBU9\ndg9W3VaK9JTECFdFJI+5UzLR0uHE4VMtyDQnIC2Z+zrJi0fOdE29Dg+qaruQlKDBilsmyl0OUcTo\ntGrMm54NUQL2n2hh9zbJjuFMVyVJEj451QpRknD9lEzoE9jJQrEtz2LA1OI0dNncOPYZr94mefEd\nl67qfKsdTe1O5KQnoTDLKHc5RGNi/sxcnG+xobK2E9npScjNMAR8CmjA4ll5EaqO4gmPnOkKXp+I\nQ9VtUAnA3ClZHDpFcUOnVWPhrFyoBODD481wunxyl0RxiuFMVzhe0wGny4dpxWlIMerkLodoTGWk\nJGLOpEy4PH58eLwZIufeJhmE1K3t9XrxxBNPoLGxER6PB9/+9rexdOnSwcf/+Mc/4o033kBaWhoA\n4JlnnsH48ePDUzFFVEePCyfrOmFI1GBGSbrc5RDJYnKhGS2dTlxos+NETQeum5Ahd0kUZ0IK5507\nd8JsNuOFF15Ad3c37rrrriHhXFlZieeeew7Tp08PW6EUeX5RxP4TzZAkYN70bGjU7Fih+CQIAm6a\nno2/fVSHY2c7kJ6ciPxMXntBYyekd98vfvGL+N73vgeg/6petXroTRCqqqrwm9/8BqtXr8avf/3r\n0VdJY+L42Q502z0oHZeC3AyD3OUQySpBp8bi2XlQqwT883gzeuy89zONnZCOnA2G/jduu92O7373\nu3jooYeGPP7lL38Za9asgdFoxIMPPoi9e/filltuGXaZqalJ0GgCv9ORxWIKvnAaZDIOnWShrdOJ\nytpOmJK0WFQ2Drqr3HUqmG1++fKjSbD7VqDrqrp4YV0w2yaQtpcvN1L1K1W41/fSx03GRNxyvYTd\nn5zHB8eacM+SiSPekY3vTcHjNrtSyEOpmpub8cADD2DNmjW48847B38uSRK+9rWvwWTq39iLFi3C\nyZMnRwznri5nwK9tsZhgtdpCK5wAADa7a/Brn1/EPz6phyQBN07LgtvthdvtveI5wWzzS5cfbYLd\ntwJd14ELiwJtbzImBtT28uVGqn6lCuf6Xm2b56bpMbUoFSfrurDro1osLssb/EAUjnriXTy/nw/3\noSSkbu329nasW7cOjz76KO65554hj9ntdtxxxx1wOByQJAkHDx7kuWeF++RUG7rtHkwqMCMnnd3Z\nRJcrK7UgJz0JDVYHDnP+bRoDIR05/+pXv0Jvby9++ctf4pe//CUAYOXKlejr68OqVavw8MMPY+3a\ntdDpdJg3bx4WLVoU1qIpfM429OBsQw/SkhNw/SSL3OUQKZJKJWDRrFzsOnge1fXdMOq1mFqUJndZ\nFMNCCucnn3wSTz755DUfv+uuu3DXXXeFXBSNjS6bGwdPtkKrUWHRrFyoeXU20TXptGosmZOPdz6u\nx+FqKwyJWhRm81wpRQbfjeOUx+vHB582wi9KmD8jG6YkTjZCNBKjXoslZfnQqAV8eLwZ1u4+uUui\nGMVwjkM+v4j3K5rQ6/RialEqCrL46Z8oUOkpiVg4KxeiKGHPkUbYnBxiReHHcI4zoiTh92+fQkuH\nE+MyjSjjeWaioOVbjJg7NQturx+7DzfA5fHLXRLFGIZznPmfD2rwcVUrMlISseC6nGGHhBDRtU0q\nMGNacRpsTi/2Hm2E3y/KXRLFEIZzHNl18Dze+fg8slL1WDInj9NzEo1SWWkGirJNsHb3Yd+xZogi\nb5JB4cF35zggSRJ27q/F9r1nkWpKwMOrZiFRx1t5E42WIAiYPyMb2WlJuNBmx4GqFt7FisKC79Ax\n4lo3hJckCZ9+1o7Kc50w6rVYPDsXJ+s6x7g6otilVqtwS1ke3j10ATWNvdj23lncu3QC74NOo8Ij\n5xgmihIOnmxD5bn+ObOXzR3HIVNEEaDVqLB0Tj5SjDr84/AFvPlhrdwlUZRjOMcol6f/KtIzF7ph\nNuqwbG4BDHqt3GURxaxEnRq3XZ+PjJRE7Nxfh79+VCd3SRTFGM4xqNvmxtsH6tHS2T9c6ks3FiIp\nkWcwiCItKVGLDWtmIz05EX/Zdw7vfFwvd0kUpRjOMUSSJJw+3423DtTD3ufFjJJ0LJ6dC62Gv2ai\nsZKRoseja2Yj1ZSAN96vwa6D5+UuiaIQ37VjhMvjw96jjTh4shVqdf8k/bMnZvCiFCIZZJr12HAx\noLfvPYs3P6yFxKu4KQgM5ygnSRI+rmrBzg/r0GB1IDstCXfOL+KE/EQyy0pNwmP/WoaMlES8+WEt\ntu05y4CmgPFEZBRr7XLi1b+fRlVdF9QqAXMmWTC1KJVHy0QyunxY4+LZufjHoQa8e+gCzjX34sap\nWVCp+v9GF8/Kk6NEigIM5yjkdPnw1sd1+MehBvj8ImaMT8eE/GQOkyJSoKRELZbdMA67DzfgbEMP\nHH1eLJqVC51WLXdppGAMZ4W62qQioijhzIVuHDvbAbfXj6REDW6anoXCbBOPlokULFGnwbK5Bdh3\nrAmNVgd2HTyPJXPy5S6LFIzhHAVESUJtUy+One2Avc8LjVrA7IkZmFKUyvmxiaKEVtM/k9jh6jZU\n13fj7QP1KMlNwZTCVLlLIwViOCuYKEqoa7HheE0Heh0eqAQBkwrMmFmSDn0Cf3VE0UYlCJg7JQvJ\nSTocqm7DT1//FP93fjHuvKlo8Dw0EcBwViSP14/q8104WdsFe58XggBMzE/BjJJ0GDnLF1HUm1yY\nivSURHxyshVvfliLMxe6sf7LU5CWnCh3aaQQDGcFcbp82PtpA/5x6AJ6nV6oVf1HylOLUnmxF1GM\nsZj1+OF9c/H7t06h4mw7/t9vD+LuBeOxdE4+j6KJ4awEnb0uvHe0Ae9/2og+tx/6BDWmF6dhSlEq\nu6+JYphRr8V3VszA/hMt2LbnM/z3e5/hQFUL1txWigl5KXKXRzLiO79MJEnCZw092H34Ao6eaYco\nSUhO0uL2RYW4ZXY+PqlulbtEIhoDgiDg5pk5mFmSjtf3fIaPq1qxcesRzBifjrsWFKM4J1nuEkkG\nDOcx5vH6cfBkK9470oDzbXYAwLhMI26dk48bpmZx7CNRnEo26PBvd07D4ll5+Mu+czhxrgMnznVg\nWlEqFs7Kw+yJGRydEUcYzmPkfKsN+0+04EBVC+x9XqgEAddPsuDW68dhYn4KxykTEQCgdJwZj/1r\nGU7Vd+HND2tRVdeFqrouGPVa3Dg1C9dNyEDpODNvaBPjGM4R1Ovw4OOTrdh/ohkXLh4lJ2jVmD4+\nDZPGmWHQa9HU4UBTh0PmSolIaaYUpmJKYSoa2x3457EmfFTZgt1HGrD7SAMStGpMLjCjJC8F43OT\nUZSdfNXbwl5tMqPhcDpR5WA4h5nL48Pxmg58XNWKE+c64BclqFUCxmUaUZKXjDyLEWpeiUlEAcrL\nMODepRNxz+ISnLnQjeM1HThe04FjF/8NSEtOQE5aErLTDchJT0JOWhKcLh/0CWr2zEUhhnMYdPa6\nUFnbiYrP2lFZ2wmfXwQAFGaZcNOMbNwwNQtHz1hlrpKIlCaUI9upRWm4d+lEdNncONfUi3PNPahv\nsaG5wznYBX4prUaFFIMOyQYdUgw6pBj7/zcl6ThkS8EYziHosrlR09iDMw3dqKrtRHOHc/CxfIsB\nZaUWXD8pE/mZRhmrJKJYlmpKwJxJFsyZZBn8WZ/bh5ZOJ1o6nGjudAzOLtjZ60J7j2vI8wUBSE7q\nD+uB4C7MMiE7LYlDOBUgpN+AKIp4+umncfr0aeh0Ovz4xz9GYWHh4ON79uzBSy+9BI1GgxUrVuBf\n/uVfwlbwWHJ7/Wjv7kNLpxMX2uxosDpQ32JDR+/nO7lOq8LMknRMK0rDzJJ0ZKUlyVgxEcUzfYIG\nxTnJg8OvBmYcE0UJ9j4vehwe9NjdF//39P/v8Aw+f/+JFgD9wZ+bnoScdAMsZj1STQmD/1KMOqhV\nvBgt0kIK5927d8Pj8WDbtm2oqKjA5s2b8fLLLwMAvF4vNm3ahB07dkCv12P16tVYsmQJMjIywlr4\ncERJQmevC6IowS9Kg/8P/HN7/HB5fHB5/Bf/9X9t7/MO7rCdNhd67J4rlm3Ua3FdSTom5KdgQl4K\nxuem8KpJIlI0lUpA8sWu7XGX9OhJkgSXx48euwfdDjdMeh2aOxzX7CIH+o+4zcYEJCfpoNOqoNOq\nodN8/r9Wo4IEDHn/FUUJPlFCa6fzkvdkEaIoQYIAr88/pK108XW0atXg+XKVAKjVqiGvNfiaWjUS\nLn6v1aig06qg1Xxej1ajgu6y7zUaFVSC0P9PJUAl4OL/AoSB7wVhyPn6FKMOqjE6fx9SOB85cgQL\nFiwAAMyaNQuVlZWDj9XU1KCgoAApKf2z28yZMweHDh3Cl770pTCUG5g/7arGvmPNIT9foxZgNiZg\nSmEqMlP1yDTrkZ9pxLhMI1IMOl5cQUQxQRAE6BM00CdokJ2eNORq7YEu8o4eF7psbnTZ3Oi0ff51\nS6cTHq8fUsivDahVAtQqFVQqQK1SQaNVQSX01yVJEgyJWkjo/xAhSYBfFOHy+NHr9MLj7Q/0sTR3\nSia+9ZXpY/JaIYWz3W6H0fj5py+1Wg2fzweNRgO73Q6TyTT4mMFggN1uH3GZFotpxDaBtn907Vw8\nGtTSIm/lbZPlLmHUgvkdxcL6BirgdW3pv/hnZbgLGOVy4+l3BcTX+o52XQvyeTtLuYTUH2s0GuFw\nfD42VxRFaDSaqz7mcDiGhDURERENL6RwLisrw759+wAAFRUVKC0tHXyspKQE9fX16O7uhsfjweHD\nhzF79uzwVEtERBQHBEmSgu60H7ha+8yZM5AkCRs3bsTJkyfhdDqxatWqwau1JUnCihUr8K//+q+R\nqJ2IiCgmhRTOREREFDkcA0RERKQwDGciIiKFUewcbS6XC48++ig6OjpgMBjw3HPPIS0t7Yp2nZ2d\nWL16NXbu3ImEhISAn0dXCmTbbd++Ha+//jo0Gg2+/e1v45ZbboEkSVi4cCGKiooA9I99f+SRR2RY\ng+gRyix7Iz2HhhfqzIbLly8fHDqan5+PTZs2yVJ/NApkn+3r68N9992Hn/zkJygpKeF+PkBSqN//\n/vfSz3/+c0mSJOlvf/ub9Oyzz17RZt++fdJXvvIVafbs2ZLL5Qr4eXR1I227trY26Y477pDcbrfU\n29s7+HVdXZ30zW9+U46So9bf//536bHHHpMkSZI+/fRT6Vvf+tbgYx6PR7r11lul7u5uye12S3ff\nfbdktVqHfQ6NLJRt7nK5pK985StylRz1Rtpnjx8/Li1fvly66aabpLNnzwb0nHih2G7tS2chW7hw\nIQ4cOHBFG5VKhT/84Q8wm81BPY+ubqRtd/z4ccyePRs6nQ4mkwkFBQWorq5GVVUVWltbUV5ejvvv\nvx/nzp2To/yoEugsezqdbnCWveGeQyMLZZtXV1ejr68P69atw9q1a1FRUSFX+VFppH3W4/HgpZde\nwvjx4wN+TrxQRLf2G2+8gf/8z/8c8rP09PTByUsMBgNsNtsVz5s/f/4VP7t0hrJrPY9C2+bXmv3N\nYrHg3/7t3/ClL30Jhw8fxqOPPor/+Z//ifxKRLFQZtkb7jk0slC2eWJiItavX4+VK1eirq4O999/\nP3bt2sVtHqCR9tk5c+YE/Zx4oYi1XblyJVauHDr54IMPPjg405jD4UBycnJAy7p0hrJgnhdvQtnm\n15r9bcKECVCr1QCA66+/Hm1tbZAkiXOQDyOUWfaGew6NLJRtXlxcjMLCQgiCgOLiYpjNZlitVuTk\n5Ix5/dEolH2W+3k/xXZrl5WV4YMPPgAA7Nu376qfsML5PBp5282cORNHjhyB2+2GzWZDTU0NSktL\n8Ytf/GLwKLy6uho5OTkM5hGEMsvecM+hkYWyzXfs2IHNmzcDAFpbWwd7iigwoeyz3M/7KXYSkr6+\nPjz22GOwWq3QarV48cUXYbFY8Ic//AEFBQVYunTpYNslS5bgnXfeQUJCwjWfRyMLZJtv374d27Zt\ngyRJ+OY3v4lly5ahp6cHjz76KJxOJ9RqNZ566imUlJTIvTqKFsose1d7Drdz4ELZ5h6PB48//jia\nmpogCAK+//3vo6ysTO5ViRojbfMB5eXlePrpp4dcrR3v+7liw5mIiCheKbZbm4iIKF4xnImIiBSG\n4UxERKQwDGciIiKFYTgTEREpDMOZKE78/Oc/x+HDh4dts2fPHvzhD38Yo4qI6FoYzkRx4tChQ/D7\n/cO2qaqqgt1uH6OKiOha4m9ONKI40NLSgu9///twOp1QqVRYvHgxKisr8eSTT+IXv/gFenp68LOf\n/Qwul2twEpmJEyfi9ddfBwDk5uYiNzcXL7zwAgAgJSUFL774Im+/SjRGGM5EMWjHjh1YvHgxvvGN\nb+DgwYOorKzE9OnT8eCDD2LSpEn47ne/ix//+McoKSnBgQMHsHHjRvz1r3/FvffeCwBYsWLF4KxN\nM2fOxJ/+9CecPHkSN998s8xrRhQfGM5EMWjevHn4zne+g1OnTmHRokX46le/ivfff3/w8RdeeAF7\n9+7Frl27cOzYsSE3GhiwdOlSPPjgg7j11luxdOnSq94Fjogig+eciWLQnDlz8NZbb+Hmm2/G22+/\njW9961tDHl+zZg2OHz+O6dOnX/HYgK9//evYunUrCgoK8MILL+Dll18ei9KJCAxnopj0/PPP4803\n38Ty5cvx1FNP4eTJk1Cr1fD7/eju7kZdXR2+973vYdGiRdi/f//ghWID984F+m8r6nA48PWvfx1f\n//rXcfLkSTlXiSiu8MYXRDGoubkZjzzyCBwOB9RqNb7xjW+gubkZr7/+Op577jm8++672L17N4xG\nI2bNmoV33nkHe/fuRVVVFR577DHcd999mDBhAjZt2gSNRoOEhAQ888wzcXv7PqKxxnAmIiJSGHZr\nExERKQzDmYiISGEYzkRERArDcCYiIlIYhjMREZHCMJyJiIgUhuFMRESkMAxnIiIihfn/QsGUytzV\n/qEAAAAASUVORK5CYII=\n",
      "text/plain": [
       "<matplotlib.figure.Figure at 0x1173cf128>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "sns.distplot(trials['stats'])\n",
    "plt.axvline(x=observed_stat, color='r')\n",
    "plt.axvline(x= -observed_stat, color='r')\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To compute the $p$-value based on the test statistics generated through the shuffled samples.  I need to find the proportion of the 1000 test statistics that are at least as large in absolute value as the observed statistic."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.127"
      ]
     },
     "execution_count": 140,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stats = [abs(i) >= observed_stat for i in trials['stats']]\n",
    "p_value = 1 - (sum(stats)/len(trials['stats']))\n",
    "p_value"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Based on the p-value, I cannot reject the null hypothesis using a cutoff of 5%. The observed variation is large and therefore I cannot assume that the variation is due to chance. Rejecting the null hypothesis concludes that the proportion of small donors for Hillary Clinton and Bernie Sanders is not equal."
   ]
  }
 ],
 "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.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}