Labs/lab04.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "deletable": false,
    "editable": false
   },
   "outputs": [],
   "source": [
    "# Initialize Otter\n",
    "import otter\n",
    "grader = otter.Notebook(\"lab04.ipynb\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "nbgrader": {
     "grade": false,
     "grade_id": "intro",
     "locked": true,
     "schema_version": 2,
     "solution": false
    }
   },
   "source": [
    "# Lab 4: SQL\n",
    "\n",
    "In this lab, we are going to practice viewing, sorting, grouping, and merging tables with SQL. We will explore the money donated during the 2016 election using the [Federal Election Commission's public records](https://www.fec.gov/data/). You will be connecting to a `sqlite` database containing the data. The data we will be working with in this lab is quite small (16MB); however, it is a sample taken from a larger database that is more than a few GBs.\n",
    "\n",
    "\n",
    "### Due Date\n",
    "\n",
    "This assignment is due at **Tuesday, September 21** at 11:59 pm.\n",
    "\n",
    "### Collaboration Policy\n",
    "\n",
    "Data science is a collaborative activity. While you may talk with others about this assignment, we ask that you **write your solutions individually**. If you discuss the assignment with others, please **include their names** in the cell below."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Collaborators:** *list names here*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "nbgrader": {
     "grade": false,
     "grade_id": "imports",
     "locked": true,
     "schema_version": 2,
     "solution": false
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import sqlalchemy\n",
    "from ds100_utils import fetch_and_cache\n",
    "from pathlib import Path"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Connecting to SQLite"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Download the database to local disk drive; this will take about a minute."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "nbgrader": {
     "grade": false,
     "grade_id": "sql-setup1",
     "locked": true,
     "schema_version": 2,
     "solution": false
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Using cached version that was downloaded (UTC): Sat Mar 12 23:24:50 2022\n"
     ]
    }
   ],
   "source": [
    "db_url = 'https://s3.amazonaws.com/berkeley-ds100/fec_nyc.sqlite'\n",
    "db_filename = 'fec_nyc.db'\n",
    "data_dir = Path('data')\n",
    "fetch_and_cache(db_url, db_filename, data_dir = data_dir)\n",
    "db_path = data_dir / db_filename"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The following cell will connect to the above database using the SQL Alchemy library. This library is more general than the `sqlite3` module from the standard library because it can connect to several different database management systems, such as MySQL and PostgreSQL. It also supports an advanced feature for generating queries called an [object relational mapper](https://docs.sqlalchemy.org/en/latest/orm/tutorial.html) or ORM, which we won't discuss in this course but is quite useful for application development."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "engine = sqlalchemy.create_engine(f\"sqlite:///{db_path}\")\n",
    "connection = engine.connect()\n",
    "inspector = sqlalchemy.inspect(engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To check that everything is working we will list the available tables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['cand', 'comm', 'indiv_sample_nyc']"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "inspector.get_table_names()"
   ]
  },
  {
   "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](https://www.fec.gov/data/browse-data/) for the dataset.\n",
    "\n",
    "If you'd like, you can consult the FEC page for the descriptions of the tables to find out what the correspondence is.\n",
    "\n",
    "- [`cand`](https://www.fec.gov/campaign-finance-data/candidate-summary-file-description/): Candidates table. Contains names and party affiliation.\n",
    "- [`comm`](https://www.fec.gov/campaign-finance-data/committee-summary-file-description/): Committees table. Contains committee names and types.\n",
    "- [`indiv_sample_nyc`](https://www.fec.gov/campaign-finance-data/contributions-individuals-file-description/): All individual contributions from New York City.\n",
    "\n",
    "We can see the schemas of these tables with the following code."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CREATE TABLE \"cand\" (\n",
      "    cand_id character varying(9),\n",
      "    cand_name text,\n",
      "    cand_pty_affiliation character varying(3),\n",
      "    cand_election_yr integer,\n",
      "    cand_office_st character varying(2),\n",
      "    cand_office character(1),\n",
      "    cand_office_district integer,\n",
      "    cand_ici character(1),\n",
      "    cand_status character(1),\n",
      "    cand_pcc character varying(9),\n",
      "    cand_st1 text,\n",
      "    cand_st2 text,\n",
      "    cand_city text,\n",
      "    cand_st character varying(2),\n",
      "    cand_zip character varying(10)\n",
      ")\n",
      "CREATE TABLE \"comm\"(\n",
      "  \"cmte_id\" TEXT,\n",
      "  \"cmte_nm\" TEXT,\n",
      "  \"tres_nm\" TEXT,\n",
      "  \"cmte_st1\" TEXT,\n",
      "  \"cmte_st2\" TEXT,\n",
      "  \"cmte_city\" TEXT,\n",
      "  \"cmte_st\" TEXT,\n",
      "  \"cmte_zip\" TEXT,\n",
      "  \"cmte_dsgn\" TEXT,\n",
      "  \"cmte_tp\" TEXT,\n",
      "  \"cmte_pty_affiliation\" TEXT,\n",
      "  \"cmte_filing_freq\" TEXT,\n",
      "  \"org_tp\" TEXT,\n",
      "  \"connected_org_nm\" TEXT,\n",
      "  \"cand_id\" TEXT\n",
      ")\n",
      "CREATE TABLE indiv_sample_nyc (\n",
      "    cmte_id character varying(9),\n",
      "    amndt_ind character(1),\n",
      "    rpt_tp character varying(3),\n",
      "    transaction_pgi character(5),\n",
      "    image_num bigint,\n",
      "    transaction_tp character varying(3),\n",
      "    entity_tp character varying(3),\n",
      "    name text,\n",
      "    city text,\n",
      "    state character(2),\n",
      "    zip_code character varying(12),\n",
      "    employer text,\n",
      "    occupation text,\n",
      "    transaction_dt character varying(9),\n",
      "    transaction_amt integer,\n",
      "    other_id text,\n",
      "    tran_id text,\n",
      "    file_num bigint,\n",
      "    memo_cd text,\n",
      "    memo_text text,\n",
      "    sub_id bigint\n",
      ")\n"
     ]
    }
   ],
   "source": [
    "for exp in connection.execute('SELECT sql FROM sqlite_master;'):\n",
    "    print(exp[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also look at the tables themselves.\n",
    "\n",
    "The individual donations made by NY residents are shown below.\n",
    "\n",
    "We use `LIMIT 5` to avoid loading a huge table. Although our tables shouldn't get too large to display, this is generally good practice when working in the notebook environment. Jupyter notebooks don't handle very large outputs well. Also note the `\"\"\"` to define our query. This allows us to have a query span multiple lines."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\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>...</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",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>C00445015</td>\n",
       "      <td>N</td>\n",
       "      <td>Q1</td>\n",
       "      <td>P</td>\n",
       "      <td>15951128130</td>\n",
       "      <td>15</td>\n",
       "      <td>IND</td>\n",
       "      <td>SINGER, TRIPP MR.</td>\n",
       "      <td>NEW YORK</td>\n",
       "      <td>NY</td>\n",
       "      <td>...</td>\n",
       "      <td>ATLANTIC MAILBOXES, INC.</td>\n",
       "      <td>OWNER</td>\n",
       "      <td>01302015</td>\n",
       "      <td>1000</td>\n",
       "      <td></td>\n",
       "      <td>A-CF13736</td>\n",
       "      <td>1002485</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>4041420151241812398</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>C00510461</td>\n",
       "      <td>N</td>\n",
       "      <td>Q1</td>\n",
       "      <td>P</td>\n",
       "      <td>15951129284</td>\n",
       "      <td>15E</td>\n",
       "      <td>IND</td>\n",
       "      <td>SIMON, DANIEL A</td>\n",
       "      <td>NEW YORK</td>\n",
       "      <td>NY</td>\n",
       "      <td>...</td>\n",
       "      <td>N/A</td>\n",
       "      <td>RETIRED</td>\n",
       "      <td>03292015</td>\n",
       "      <td>400</td>\n",
       "      <td>C00401224</td>\n",
       "      <td>VN8JBDDJBA8</td>\n",
       "      <td>1002590</td>\n",
       "      <td></td>\n",
       "      <td>* EARMARKED CONTRIBUTION: SEE BELOW</td>\n",
       "      <td>4041420151241813640</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>C00422410</td>\n",
       "      <td>N</td>\n",
       "      <td>Q1</td>\n",
       "      <td>P</td>\n",
       "      <td>15970352211</td>\n",
       "      <td>15</td>\n",
       "      <td>IND</td>\n",
       "      <td>ABDUL RAUF, FEISAL</td>\n",
       "      <td>NEW YORK</td>\n",
       "      <td>NY</td>\n",
       "      <td>...</td>\n",
       "      <td>THE CORDOBA INITIATIVE</td>\n",
       "      <td>CHAIRMAN</td>\n",
       "      <td>03042015</td>\n",
       "      <td>250</td>\n",
       "      <td></td>\n",
       "      <td>VN8A3DBSYG6</td>\n",
       "      <td>1003643</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>4041620151241914560</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>C00510461</td>\n",
       "      <td>N</td>\n",
       "      <td>Q1</td>\n",
       "      <td>P</td>\n",
       "      <td>15951129280</td>\n",
       "      <td>15</td>\n",
       "      <td>IND</td>\n",
       "      <td>SCHWARZER, FRANK</td>\n",
       "      <td>NEW YORK</td>\n",
       "      <td>NY</td>\n",
       "      <td>...</td>\n",
       "      <td>METRO HYDRAULIC JACK CO</td>\n",
       "      <td>SALES</td>\n",
       "      <td>01162015</td>\n",
       "      <td>100</td>\n",
       "      <td></td>\n",
       "      <td>VN8JBDAP4C4</td>\n",
       "      <td>1002590</td>\n",
       "      <td></td>\n",
       "      <td>* EARMARKED CONTRIBUTION: SEE BELOW</td>\n",
       "      <td>4041420151241813630</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>C00510461</td>\n",
       "      <td>N</td>\n",
       "      <td>Q1</td>\n",
       "      <td>P</td>\n",
       "      <td>15951129281</td>\n",
       "      <td>15</td>\n",
       "      <td>IND</td>\n",
       "      <td>SCHWARZER, FRANK</td>\n",
       "      <td>NEW YORK</td>\n",
       "      <td>NY</td>\n",
       "      <td>...</td>\n",
       "      <td>METRO HYDRAULIC JACK CO</td>\n",
       "      <td>SALES</td>\n",
       "      <td>02162015</td>\n",
       "      <td>100</td>\n",
       "      <td></td>\n",
       "      <td>VN8JBDBRDG3</td>\n",
       "      <td>1002590</td>\n",
       "      <td></td>\n",
       "      <td>* EARMARKED CONTRIBUTION: SEE BELOW</td>\n",
       "      <td>4041420151241813632</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     cmte_id amndt_ind rpt_tp transaction_pgi    image_num transaction_tp  \\\n",
       "0  C00445015         N     Q1           P      15951128130             15   \n",
       "1  C00510461         N     Q1           P      15951129284            15E   \n",
       "2  C00422410         N     Q1           P      15970352211             15   \n",
       "3  C00510461         N     Q1           P      15951129280             15   \n",
       "4  C00510461         N     Q1           P      15951129281             15   \n",
       "\n",
       "  entity_tp                name      city state  ...  \\\n",
       "0       IND   SINGER, TRIPP MR.  NEW YORK    NY  ...   \n",
       "1       IND     SIMON, DANIEL A  NEW YORK    NY  ...   \n",
       "2       IND  ABDUL RAUF, FEISAL  NEW YORK    NY  ...   \n",
       "3       IND    SCHWARZER, FRANK  NEW YORK    NY  ...   \n",
       "4       IND    SCHWARZER, FRANK  NEW YORK    NY  ...   \n",
       "\n",
       "                   employer occupation transaction_dt transaction_amt  \\\n",
       "0  ATLANTIC MAILBOXES, INC.      OWNER       01302015            1000   \n",
       "1                       N/A    RETIRED       03292015             400   \n",
       "2    THE CORDOBA INITIATIVE   CHAIRMAN       03042015             250   \n",
       "3   METRO HYDRAULIC JACK CO      SALES       01162015             100   \n",
       "4   METRO HYDRAULIC JACK CO      SALES       02162015             100   \n",
       "\n",
       "    other_id      tran_id file_num  memo_cd  \\\n",
       "0               A-CF13736  1002485            \n",
       "1  C00401224  VN8JBDDJBA8  1002590            \n",
       "2             VN8A3DBSYG6  1003643            \n",
       "3             VN8JBDAP4C4  1002590            \n",
       "4             VN8JBDBRDG3  1002590            \n",
       "\n",
       "                             memo_text               sub_id  \n",
       "0                                       4041420151241812398  \n",
       "1  * EARMARKED CONTRIBUTION: SEE BELOW  4041420151241813640  \n",
       "2                                       4041620151241914560  \n",
       "3  * EARMARKED CONTRIBUTION: SEE BELOW  4041420151241813630  \n",
       "4  * EARMARKED CONTRIBUTION: SEE BELOW  4041420151241813632  \n",
       "\n",
       "[5 rows x 21 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "SELECT *\n",
    "FROM indiv_sample_nyc\n",
    "LIMIT 5\n",
    "\"\"\"\n",
    "pd.read_sql(query, engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Similarly, the database of candidates is given below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>cand_id</th>\n",
       "      <th>cand_name</th>\n",
       "      <th>cand_pty_affiliation</th>\n",
       "      <th>cand_election_yr</th>\n",
       "      <th>cand_office_st</th>\n",
       "      <th>cand_office</th>\n",
       "      <th>cand_office_district</th>\n",
       "      <th>cand_ici</th>\n",
       "      <th>cand_status</th>\n",
       "      <th>cand_pcc</th>\n",
       "      <th>cand_st1</th>\n",
       "      <th>cand_st2</th>\n",
       "      <th>cand_city</th>\n",
       "      <th>cand_st</th>\n",
       "      <th>cand_zip</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>H0AK00097</td>\n",
       "      <td>COX, JOHN R.</td>\n",
       "      <td>REP</td>\n",
       "      <td>2014</td>\n",
       "      <td>AK</td>\n",
       "      <td>H</td>\n",
       "      <td>0</td>\n",
       "      <td>C</td>\n",
       "      <td>N</td>\n",
       "      <td>C00525261</td>\n",
       "      <td>P.O. BOX 1092</td>\n",
       "      <td></td>\n",
       "      <td>ANCHOR POINT</td>\n",
       "      <td>AK</td>\n",
       "      <td>99556</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>H0AL02087</td>\n",
       "      <td>ROBY, MARTHA</td>\n",
       "      <td>REP</td>\n",
       "      <td>2016</td>\n",
       "      <td>AL</td>\n",
       "      <td>H</td>\n",
       "      <td>2</td>\n",
       "      <td>I</td>\n",
       "      <td>C</td>\n",
       "      <td>C00462143</td>\n",
       "      <td>PO BOX 195</td>\n",
       "      <td></td>\n",
       "      <td>MONTGOMERY</td>\n",
       "      <td>AL</td>\n",
       "      <td>36101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>H0AL02095</td>\n",
       "      <td>JOHN, ROBERT E JR</td>\n",
       "      <td>IND</td>\n",
       "      <td>2016</td>\n",
       "      <td>AL</td>\n",
       "      <td>H</td>\n",
       "      <td>2</td>\n",
       "      <td>C</td>\n",
       "      <td>N</td>\n",
       "      <td></td>\n",
       "      <td>1465 W OVERBROOK RD</td>\n",
       "      <td></td>\n",
       "      <td>MILLBROOK</td>\n",
       "      <td>AL</td>\n",
       "      <td>36054</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>H0AL05049</td>\n",
       "      <td>CRAMER, ROBERT E \"BUD\" JR</td>\n",
       "      <td>DEM</td>\n",
       "      <td>2008</td>\n",
       "      <td>AL</td>\n",
       "      <td>H</td>\n",
       "      <td>5</td>\n",
       "      <td></td>\n",
       "      <td>P</td>\n",
       "      <td>C00239038</td>\n",
       "      <td>PO BOX 2621</td>\n",
       "      <td></td>\n",
       "      <td>HUNTSVILLE</td>\n",
       "      <td>AL</td>\n",
       "      <td>35804</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>H0AL05163</td>\n",
       "      <td>BROOKS, MO</td>\n",
       "      <td>REP</td>\n",
       "      <td>2016</td>\n",
       "      <td>AL</td>\n",
       "      <td>H</td>\n",
       "      <td>5</td>\n",
       "      <td>I</td>\n",
       "      <td>C</td>\n",
       "      <td>C00464149</td>\n",
       "      <td>7610 FOXFIRE DRIVE</td>\n",
       "      <td></td>\n",
       "      <td>HUNTSVILLE</td>\n",
       "      <td>AL</td>\n",
       "      <td>35802</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     cand_id                  cand_name cand_pty_affiliation  \\\n",
       "0  H0AK00097               COX, JOHN R.                  REP   \n",
       "1  H0AL02087               ROBY, MARTHA                  REP   \n",
       "2  H0AL02095          JOHN, ROBERT E JR                  IND   \n",
       "3  H0AL05049  CRAMER, ROBERT E \"BUD\" JR                  DEM   \n",
       "4  H0AL05163                 BROOKS, MO                  REP   \n",
       "\n",
       "   cand_election_yr cand_office_st cand_office  cand_office_district cand_ici  \\\n",
       "0              2014             AK           H                     0        C   \n",
       "1              2016             AL           H                     2        I   \n",
       "2              2016             AL           H                     2        C   \n",
       "3              2008             AL           H                     5            \n",
       "4              2016             AL           H                     5        I   \n",
       "\n",
       "  cand_status   cand_pcc             cand_st1 cand_st2     cand_city cand_st  \\\n",
       "0           N  C00525261       P.O. BOX 1092            ANCHOR POINT      AK   \n",
       "1           C  C00462143           PO BOX 195             MONTGOMERY      AL   \n",
       "2           N             1465 W OVERBROOK RD              MILLBROOK      AL   \n",
       "3           P  C00239038          PO BOX 2621             HUNTSVILLE      AL   \n",
       "4           C  C00464149   7610 FOXFIRE DRIVE             HUNTSVILLE      AL   \n",
       "\n",
       "  cand_zip  \n",
       "0    99556  \n",
       "1    36101  \n",
       "2    36054  \n",
       "3    35804  \n",
       "4    35802  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "SELECT *\n",
    "FROM cand\n",
    "LIMIT 5\n",
    "\"\"\"\n",
    "pd.read_sql(query, engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And finally, the table of committees is given below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\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",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>C00000059</td>\n",
       "      <td>HALLMARK CARDS PAC</td>\n",
       "      <td>ERIN BROWER</td>\n",
       "      <td>2501 MCGEE</td>\n",
       "      <td>MD#288</td>\n",
       "      <td>KANSAS CITY</td>\n",
       "      <td>MO</td>\n",
       "      <td>64108</td>\n",
       "      <td>U</td>\n",
       "      <td>Q</td>\n",
       "      <td>UNK</td>\n",
       "      <td>M</td>\n",
       "      <td>C</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>C00000422</td>\n",
       "      <td>AMERICAN MEDICAL ASSOCIATION POLITICAL ACTION ...</td>\n",
       "      <td>WALKER, KEVIN</td>\n",
       "      <td>25 MASSACHUSETTS AVE, NW</td>\n",
       "      <td>SUITE 600</td>\n",
       "      <td>WASHINGTON</td>\n",
       "      <td>DC</td>\n",
       "      <td>20001</td>\n",
       "      <td>B</td>\n",
       "      <td>Q</td>\n",
       "      <td></td>\n",
       "      <td>M</td>\n",
       "      <td>M</td>\n",
       "      <td>AMERICAN MEDICAL ASSOCIATION</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>C00000489</td>\n",
       "      <td>D R I V E POLITICAL FUND CHAPTER 886</td>\n",
       "      <td>TOM RITTER</td>\n",
       "      <td>3528 W RENO</td>\n",
       "      <td></td>\n",
       "      <td>OKLAHOMA CITY</td>\n",
       "      <td>OK</td>\n",
       "      <td>73107</td>\n",
       "      <td>U</td>\n",
       "      <td>N</td>\n",
       "      <td></td>\n",
       "      <td>Q</td>\n",
       "      <td>L</td>\n",
       "      <td>TEAMSTERS LOCAL UNION 886</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>C00000547</td>\n",
       "      <td>KANSAS MEDICAL SOCIETY POLITICAL ACTION COMMITTEE</td>\n",
       "      <td>C. RICHARD BONEBRAKE, M.D.</td>\n",
       "      <td>623 SW 10TH AVE</td>\n",
       "      <td></td>\n",
       "      <td>TOPEKA</td>\n",
       "      <td>KS</td>\n",
       "      <td>66612</td>\n",
       "      <td>U</td>\n",
       "      <td>Q</td>\n",
       "      <td>UNK</td>\n",
       "      <td>Q</td>\n",
       "      <td>T</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>C00000638</td>\n",
       "      <td>INDIANA STATE MEDICAL ASSOCIATION POLITICAL AC...</td>\n",
       "      <td>VIDYA KORA, M.D.</td>\n",
       "      <td>322 CANAL WALK, CANAL LEVEL</td>\n",
       "      <td></td>\n",
       "      <td>INDIANAPOLIS</td>\n",
       "      <td>IN</td>\n",
       "      <td>46202</td>\n",
       "      <td>U</td>\n",
       "      <td>Q</td>\n",
       "      <td></td>\n",
       "      <td>Q</td>\n",
       "      <td>M</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     cmte_id                                            cmte_nm  \\\n",
       "0  C00000059                                 HALLMARK CARDS PAC   \n",
       "1  C00000422  AMERICAN MEDICAL ASSOCIATION POLITICAL ACTION ...   \n",
       "2  C00000489               D R I V E POLITICAL FUND CHAPTER 886   \n",
       "3  C00000547  KANSAS MEDICAL SOCIETY POLITICAL ACTION COMMITTEE   \n",
       "4  C00000638  INDIANA STATE MEDICAL ASSOCIATION POLITICAL AC...   \n",
       "\n",
       "                      tres_nm                     cmte_st1   cmte_st2  \\\n",
       "0                 ERIN BROWER                   2501 MCGEE     MD#288   \n",
       "1               WALKER, KEVIN     25 MASSACHUSETTS AVE, NW  SUITE 600   \n",
       "2                  TOM RITTER                  3528 W RENO              \n",
       "3  C. RICHARD BONEBRAKE, M.D.              623 SW 10TH AVE              \n",
       "4            VIDYA KORA, M.D.  322 CANAL WALK, CANAL LEVEL              \n",
       "\n",
       "       cmte_city cmte_st cmte_zip cmte_dsgn cmte_tp cmte_pty_affiliation  \\\n",
       "0    KANSAS CITY      MO    64108         U       Q                  UNK   \n",
       "1     WASHINGTON      DC    20001         B       Q                        \n",
       "2  OKLAHOMA CITY      OK    73107         U       N                        \n",
       "3         TOPEKA      KS    66612         U       Q                  UNK   \n",
       "4   INDIANAPOLIS      IN    46202         U       Q                        \n",
       "\n",
       "  cmte_filing_freq org_tp              connected_org_nm cand_id  \n",
       "0                M      C                                        \n",
       "1                M      M  AMERICAN MEDICAL ASSOCIATION          \n",
       "2                Q      L     TEAMSTERS LOCAL UNION 886          \n",
       "3                Q      T                                        \n",
       "4                Q      M                                        "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "SELECT *\n",
    "FROM comm\n",
    "LIMIT 5\n",
    "\"\"\"\n",
    "pd.read_sql(query, engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In CS61A or CS88, we saw basic queries similar to what we see below, which gives only the id and name of candidates from the Democratic party."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>cand_id</th>\n",
       "      <th>cand_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>H0AL05049</td>\n",
       "      <td>CRAMER, ROBERT E \"BUD\" JR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>H0AL07086</td>\n",
       "      <td>SEWELL, TERRYCINA ANDREA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>H0AL07094</td>\n",
       "      <td>HILLIARD, EARL FREDERICK JR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>H0AR01091</td>\n",
       "      <td>GREGORY, JAMES CHRISTOPHER</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>H0AR01109</td>\n",
       "      <td>CAUSEY, CHAD</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     cand_id                    cand_name\n",
       "0  H0AL05049    CRAMER, ROBERT E \"BUD\" JR\n",
       "1  H0AL07086     SEWELL, TERRYCINA ANDREA\n",
       "2  H0AL07094  HILLIARD, EARL FREDERICK JR\n",
       "3  H0AR01091   GREGORY, JAMES CHRISTOPHER\n",
       "4  H0AR01109                 CAUSEY, CHAD"
      ]
     },
     "execution_count": 10,
     "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",
    "pd.read_sql(query,engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "nbgrader": {
     "grade": false,
     "grade_id": "q3",
     "locked": true,
     "schema_version": 2,
     "solution": false
    }
   },
   "source": [
    "---\n",
    "## Questions\n",
    "\n",
    "For this lab we will use SQL to extract data out of the `indiv_sample_nyc` table. The schema for this table is given below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CREATE TABLE indiv_sample_nyc (\n",
      "    cmte_id character varying(9),\n",
      "    amndt_ind character(1),\n",
      "    rpt_tp character varying(3),\n",
      "    transaction_pgi character(5),\n",
      "    image_num bigint,\n",
      "    transaction_tp character varying(3),\n",
      "    entity_tp character varying(3),\n",
      "    name text,\n",
      "    city text,\n",
      "    state character(2),\n",
      "    zip_code character varying(12),\n",
      "    employer text,\n",
      "    occupation text,\n",
      "    transaction_dt character varying(9),\n",
      "    transaction_amt integer,\n",
      "    other_id text,\n",
      "    tran_id text,\n",
      "    file_num bigint,\n",
      "    memo_cd text,\n",
      "    memo_text text,\n",
      "    sub_id bigint\n",
      ")\n"
     ]
    }
   ],
   "source": [
    "for exp in connection.execute('SELECT sql FROM sqlite_master WHERE name=\"indiv_sample_nyc\";'):\n",
    "    print(exp[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's start by looking at 2016 election contributions made by Donald Trump, who was a NY resident during that year. The following SQL statement returns the `cmte_id`, `transaction_amt`, and `name` for every contribution made by any donor with \"DONALD\" and \"TRUMP\" in their name.\n",
    "\n",
    "Notice the `WHERE name LIKE '%TRUMP%' AND name LIKE '%DONALD%'` clause."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>cmte_id</th>\n",
       "      <th>transaction_amt</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>C00230482</td>\n",
       "      <td>2600</td>\n",
       "      <td>DONALD, TRUMP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>C00230482</td>\n",
       "      <td>2600</td>\n",
       "      <td>DONALD, TRUMP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>C00014498</td>\n",
       "      <td>9000</td>\n",
       "      <td>TRUMP, DONALD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>C00494229</td>\n",
       "      <td>2000</td>\n",
       "      <td>TRUMP, DONALD MR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>C00571869</td>\n",
       "      <td>2700</td>\n",
       "      <td>TRUMP, DONALD J.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>152</th>\n",
       "      <td>C00608489</td>\n",
       "      <td>5</td>\n",
       "      <td>DONALD J TRUMP FOR PRESIDENT INC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>153</th>\n",
       "      <td>C00608489</td>\n",
       "      <td>5</td>\n",
       "      <td>DONALD J TRUMP FOR PRESIDENT INC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>154</th>\n",
       "      <td>C00608489</td>\n",
       "      <td>5</td>\n",
       "      <td>DONALD J TRUMP FOR PRESIDENT INC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>155</th>\n",
       "      <td>C00608489</td>\n",
       "      <td>5</td>\n",
       "      <td>DONALD J TRUMP FOR PRESIDENT INC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156</th>\n",
       "      <td>C00608489</td>\n",
       "      <td>5</td>\n",
       "      <td>DONALD J TRUMP FOR PRESIDENT INC</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>157 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       cmte_id  transaction_amt                              name\n",
       "0    C00230482             2600                     DONALD, TRUMP\n",
       "1    C00230482             2600                     DONALD, TRUMP\n",
       "2    C00014498             9000                     TRUMP, DONALD\n",
       "3    C00494229             2000                  TRUMP, DONALD MR\n",
       "4    C00571869             2700                  TRUMP, DONALD J.\n",
       "..         ...              ...                               ...\n",
       "152  C00608489                5  DONALD J TRUMP FOR PRESIDENT INC\n",
       "153  C00608489                5  DONALD J TRUMP FOR PRESIDENT INC\n",
       "154  C00608489                5  DONALD J TRUMP FOR PRESIDENT INC\n",
       "155  C00608489                5  DONALD J TRUMP FOR PRESIDENT INC\n",
       "156  C00608489                5  DONALD J TRUMP FOR PRESIDENT INC\n",
       "\n",
       "[157 rows x 3 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query1 = \"\"\"\n",
    "SELECT \n",
    "    cmte_id,\n",
    "    transaction_amt,\n",
    "    name\n",
    "FROM indiv_sample_nyc\n",
    "WHERE name LIKE '%TRUMP%' AND name LIKE '%DONALD%'\n",
    "\"\"\"\n",
    "\n",
    "res = pd.read_sql(query1, engine)\n",
    "res"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Question 1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we look at the list above, it appears that some donations were not by Donald Trump himself, but instead by an entity called \"DONALD J TRUMP FOR PRESIDENT INC\". Fortunately, we see that our query only seems to have picked up one such anomalous name."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "TRUMP, DONALD J.                    133\n",
       "DONALD J TRUMP FOR PRESIDENT INC     15\n",
       "TRUMP, DONALD                         4\n",
       "DONALD, TRUMP                         2\n",
       "TRUMP, DONALD MR                      1\n",
       "TRUMP, DONALD J MR.                   1\n",
       "TRUMP, DONALD J MR                    1\n",
       "Name: name, dtype: int64"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "res['name'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": false,
    "editable": false
   },
   "source": [
    "Revise the above query so that the 15 donations made by \"DONALD J TRUMP FOR PRESIDENT INC\" do not appear. Your resulting table should have 142 rows. \n",
    "\n",
    "Hint: Copy the above query into the cell below as a starting point!\n",
    "\n",
    "Hint: Consider using `NOT LIKE`.\n",
    "\n",
    "<!--\n",
    "BEGIN QUESTION\n",
    "name: q1\n",
    "-->"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>cmte_id</th>\n",
       "      <th>transaction_amt</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>C00230482</td>\n",
       "      <td>2600</td>\n",
       "      <td>DONALD, TRUMP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>C00230482</td>\n",
       "      <td>2600</td>\n",
       "      <td>DONALD, TRUMP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>C00014498</td>\n",
       "      <td>9000</td>\n",
       "      <td>TRUMP, DONALD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>C00494229</td>\n",
       "      <td>2000</td>\n",
       "      <td>TRUMP, DONALD MR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>C00571869</td>\n",
       "      <td>2700</td>\n",
       "      <td>TRUMP, DONALD J.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>137</th>\n",
       "      <td>C00580100</td>\n",
       "      <td>9752</td>\n",
       "      <td>TRUMP, DONALD J.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>138</th>\n",
       "      <td>C00580100</td>\n",
       "      <td>2574</td>\n",
       "      <td>TRUMP, DONALD J.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>139</th>\n",
       "      <td>C00580100</td>\n",
       "      <td>23775</td>\n",
       "      <td>TRUMP, DONALD J.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>140</th>\n",
       "      <td>C00580100</td>\n",
       "      <td>2000000</td>\n",
       "      <td>TRUMP, DONALD J.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141</th>\n",
       "      <td>C00580100</td>\n",
       "      <td>2574</td>\n",
       "      <td>TRUMP, DONALD J.</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>142 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       cmte_id  transaction_amt              name\n",
       "0    C00230482             2600     DONALD, TRUMP\n",
       "1    C00230482             2600     DONALD, TRUMP\n",
       "2    C00014498             9000     TRUMP, DONALD\n",
       "3    C00494229             2000  TRUMP, DONALD MR\n",
       "4    C00571869             2700  TRUMP, DONALD J.\n",
       "..         ...              ...               ...\n",
       "137  C00580100             9752  TRUMP, DONALD J.\n",
       "138  C00580100             2574  TRUMP, DONALD J.\n",
       "139  C00580100            23775  TRUMP, DONALD J.\n",
       "140  C00580100          2000000  TRUMP, DONALD J.\n",
       "141  C00580100             2574  TRUMP, DONALD J.\n",
       "\n",
       "[142 rows x 3 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query_q1 = \"\"\"\n",
    "SELECT\n",
    "    cmte_id,\n",
    "    transaction_amt,\n",
    "    name\n",
    "FROM indiv_sample_nyc\n",
    "WHERE name LIKE '%DONALD%' and name LIKE '%TRUMP%' and name NOT LIKE '%INC%'\n",
    "\"\"\"\n",
    "\n",
    "\n",
    "res_q1 = pd.read_sql(query_q1, engine)\n",
    "res_q1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "deletable": false,
    "editable": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<p><strong><pre style='display: inline;'>q1</pre></strong> passed!</p>"
      ],
      "text/plain": [
       "q1 results: All test cases passed!"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grader.check(\"q1\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": false,
    "editable": false
   },
   "source": [
    "### Question 2\n",
    "\n",
    "When you printed the schema for the tables, you may have noticed that both the `cand` and `comm` tables have a `cand_id` column. Let's try joining these two tables on this column.\n",
    "\n",
    "List the first 5 candidate names (`cand_name`) in reverse lexicographic order by `cand_name`, along with their corresponding committee names. Only select rows that have a matching `cand_id` in both tables.\n",
    "\n",
    "Your output should look similar to:\n",
    "\n",
    "<table border=\"1\" class=\"dataframe\">\n",
    "  <thead>\n",
    "    <tr style=\"text-align: right;\">\n",
    "      <th></th>\n",
    "      <th>cand_name</th>\n",
    "      <th>cmte_nm</th>\n",
    "    </tr>\n",
    "  </thead>\n",
    "  <tbody>\n",
    "    <tr>\n",
    "      <th>0</th>\n",
    "      <td>ZUTLER, DANIEL PAUL MR</td>\n",
    "      <td>CITIZENS TO ELECT DANIEL P ZUTLER FOR PRESIDENT</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "      <th>1</th>\n",
    "      <td>ZUMWALT, JAMES</td>\n",
    "      <td>ZUMWALT FOR CONGRESS</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "      <th>2</th>\n",
    "      <td>ZUKOWSKI, ANDREW GEORGE</td>\n",
    "      <td>ZUKOWSKI FOR CONGRESS</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "      <th>3</th>\n",
    "      <td>ZUCCOLO, JOE</td>\n",
    "      <td>JOE ZUCCOLO FOR CONGRESS</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "      <th>4</th>\n",
    "      <td>ZORN, ROBERT ERWIN</td>\n",
    "      <td>CONSTITUTIONAL COMMITTEE</td>\n",
    "    </tr>\n",
    "  </tbody>\n",
    "</table>\n",
    "\n",
    "<!--\n",
    "BEGIN QUESTION\n",
    "name: q2\n",
    "-->"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>cand_name</th>\n",
       "      <th>cmte_nm</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ZUTLER, DANIEL PAUL MR</td>\n",
       "      <td>CITIZENS TO ELECT DANIEL P ZUTLER FOR PRESIDENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ZUMWALT, JAMES</td>\n",
       "      <td>ZUMWALT FOR CONGRESS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ZUKOWSKI, ANDREW GEORGE</td>\n",
       "      <td>ZUKOWSKI FOR CONGRESS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ZUCCOLO, JOE</td>\n",
       "      <td>JOE ZUCCOLO FOR CONGRESS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ZORN, ROBERT ERWIN</td>\n",
       "      <td>CONSTITUTIONAL COMMITTEE</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 cand_name                                          cmte_nm\n",
       "0   ZUTLER, DANIEL PAUL MR  CITIZENS TO ELECT DANIEL P ZUTLER FOR PRESIDENT\n",
       "1           ZUMWALT, JAMES                             ZUMWALT FOR CONGRESS\n",
       "2  ZUKOWSKI, ANDREW GEORGE                            ZUKOWSKI FOR CONGRESS\n",
       "3             ZUCCOLO, JOE                         JOE ZUCCOLO FOR CONGRESS\n",
       "4       ZORN, ROBERT ERWIN                         CONSTITUTIONAL COMMITTEE"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query_q2 = \"\"\"\n",
    "SELECT c1.cand_name, c2.cmte_nm\n",
    "FROM cand AS c1 INNER JOIN comm AS c2 \n",
    "ON c1.cand_id = c2.cand_id\n",
    "ORDER BY c1.cand_name DESC\n",
    "LIMIT 5\n",
    "\"\"\"\n",
    "\n",
    "\n",
    "res_q2 = pd.read_sql(query_q2, engine)\n",
    "res_q2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "deletable": false,
    "editable": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<p><strong><pre style='display: inline;'>q2</pre></strong> passed!</p>"
      ],
      "text/plain": [
       "q2 results: All test cases passed!"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grader.check(\"q2\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": false,
    "editable": false
   },
   "source": [
    "### Question 3\n",
    "\n",
    "As in the previous part, list the first 5 candidate names (`cand_name`) by reverse lexicographic order and their corresponding committee names (`cmte_nm`). This time, include all candidate names. If there is no matching `cand_id` in the `comm` table, then `cmte_nm` should be NULL (or `None` in the Python representation).\n",
    "\n",
    "Your output should look similar to:\n",
    "\n",
    "<table border=\"1\" class=\"dataframe\">\n",
    "  <thead>\n",
    "    <tr style=\"text-align: right;\">\n",
    "      <th></th>\n",
    "      <th>cand_name</th>\n",
    "      <th>cmte_nm</th>\n",
    "    </tr>\n",
    "  </thead>\n",
    "  <tbody>\n",
    "    <tr>\n",
    "      <th>0</th>\n",
    "      <td>ZUTLER, DANIEL PAUL MR</td>\n",
    "      <td>CITIZENS TO ELECT DANIEL P ZUTLER FOR PRESIDENT</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "      <th>1</th>\n",
    "      <td>ZUMWALT, JAMES</td>\n",
    "      <td>ZUMWALT FOR CONGRESS</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "      <th>2</th>\n",
    "      <td>ZUKOWSKI, ANDREW GEORGE</td>\n",
    "      <td>ZUKOWSKI FOR CONGRESS</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "      <th>3</th>\n",
    "      <td>ZUCCOLO, JOE</td>\n",
    "      <td>JOE ZUCCOLO FOR CONGRESS</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "      <th>4</th>\n",
    "      <td>ZORNOW, TODD MR</td>\n",
    "      <td>None</td>\n",
    "    </tr>\n",
    "  </tbody>\n",
    "</table>\n",
    "\n",
    "<!--\n",
    "BEGIN QUESTION\n",
    "name: q3\n",
    "-->"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>cand_name</th>\n",
       "      <th>cmte_nm</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ZUTLER, DANIEL PAUL MR</td>\n",
       "      <td>CITIZENS TO ELECT DANIEL P ZUTLER FOR PRESIDENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ZUMWALT, JAMES</td>\n",
       "      <td>ZUMWALT FOR CONGRESS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ZUKOWSKI, ANDREW GEORGE</td>\n",
       "      <td>ZUKOWSKI FOR CONGRESS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ZUCCOLO, JOE</td>\n",
       "      <td>JOE ZUCCOLO FOR CONGRESS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ZORNOW, TODD MR</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 cand_name                                          cmte_nm\n",
       "0   ZUTLER, DANIEL PAUL MR  CITIZENS TO ELECT DANIEL P ZUTLER FOR PRESIDENT\n",
       "1           ZUMWALT, JAMES                             ZUMWALT FOR CONGRESS\n",
       "2  ZUKOWSKI, ANDREW GEORGE                            ZUKOWSKI FOR CONGRESS\n",
       "3             ZUCCOLO, JOE                         JOE ZUCCOLO FOR CONGRESS\n",
       "4          ZORNOW, TODD MR                                             None"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query_q3 = \"\"\"\n",
    "SELECT c1.cand_name, c2.cmte_nm\n",
    "FROM cand AS c1 LEFT JOIN comm AS c2 \n",
    "ON c1.cand_id = c2.cand_id\n",
    "ORDER BY c1.cand_name DESC\n",
    "LIMIT 5\n",
    "\"\"\"\n",
    "\n",
    "\n",
    "res_q3 = pd.read_sql(query_q3, engine)\n",
    "res_q3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "deletable": false,
    "editable": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<p><strong><pre style='display: inline;'>q3</pre></strong> passed!</p>"
      ],
      "text/plain": [
       "q3 results: All test cases passed!"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grader.check(\"q3\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Question 4"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": false,
    "editable": false
   },
   "source": [
    "As a review of some of the key ideas from CS61A/CS88, let's refine our query above.\n",
    "\n",
    "We see that many of the contributions above were to the same committee. Create a new SQL query that returns the total amount that Donald Trump contributed to each committee. Your table should have four columns: `cmte_id`, `total_amount`, `num_donations`, and `cmte_nm`. The `total_amount` is the total amount contributed to that committee, `num_donations` is the total number of donations, and `cmte_nm` is the name of the committee. Note that `cmte_nm` is not available in `indiv_sample_nyc`, so you will have to somehow obtain information from the `comm` table.\n",
    "\n",
    "Your final table should be sorted in decreased order of `total_amount`.\n",
    "\n",
    "**Note**: This is a hard question! Don't be afraid to reference the walkthrough video if you're stuck.\n",
    "\n",
    "Hint: Use `GROUP BY` as covered in CS61A and CS88.\n",
    "\n",
    "Hint: Use `COUNT(*)` as covered in CS61A and CS88.\n",
    "\n",
    "Hint: Use `SUM()` as covered in CS61A and CS88.\n",
    "\n",
    "Hint: Use `ORDER BY` as covered in CS61A and CS88.\n",
    "\n",
    "Hint: Use `DESC` as covered in CS61A and CS88.\n",
    "\n",
    "Hint: Your `WHERE` clause should be the same as question 1a.\n",
    "\n",
    "Hint: You'll probably need either a join, or a subquery.\n",
    "\n",
    "<!--\n",
    "BEGIN QUESTION\n",
    "name: q4\n",
    "-->"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>cmte_id</th>\n",
       "      <th>total_amount</th>\n",
       "      <th>num_donations</th>\n",
       "      <th>cmte_nm</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>C00369033</td>\n",
       "      <td>1000</td>\n",
       "      <td>1</td>\n",
       "      <td>TEXANS FOR SENATOR JOHN CORNYN INC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>C00494229</td>\n",
       "      <td>2000</td>\n",
       "      <td>1</td>\n",
       "      <td>HELLER FOR SENATE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>C00554949</td>\n",
       "      <td>2600</td>\n",
       "      <td>1</td>\n",
       "      <td>FRIENDS OF DAVE BRAT INC.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>C00034033</td>\n",
       "      <td>5000</td>\n",
       "      <td>1</td>\n",
       "      <td>SOUTH CAROLINA REPUBLICAN PARTY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>C00136457</td>\n",
       "      <td>5000</td>\n",
       "      <td>1</td>\n",
       "      <td>NEW HAMPSHIRE REPUBLICAN STATE COMMITTEE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>C00230482</td>\n",
       "      <td>5200</td>\n",
       "      <td>2</td>\n",
       "      <td>GRASSLEY COMMITTEE INC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>C00571869</td>\n",
       "      <td>5400</td>\n",
       "      <td>2</td>\n",
       "      <td>DONOVAN FOR CONGRESS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>C00014498</td>\n",
       "      <td>9000</td>\n",
       "      <td>1</td>\n",
       "      <td>REPUBLICAN PARTY OF IOWA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>C00055582</td>\n",
       "      <td>10000</td>\n",
       "      <td>1</td>\n",
       "      <td>NY REPUBLICAN FEDERAL CAMPAIGN COMMITTEE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>C00580100</td>\n",
       "      <td>18633157</td>\n",
       "      <td>131</td>\n",
       "      <td>DONALD J. TRUMP FOR PRESIDENT, INC.</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     cmte_id  total_amount  num_donations  \\\n",
       "0  C00369033          1000              1   \n",
       "1  C00494229          2000              1   \n",
       "2  C00554949          2600              1   \n",
       "3  C00034033          5000              1   \n",
       "4  C00136457          5000              1   \n",
       "5  C00230482          5200              2   \n",
       "6  C00571869          5400              2   \n",
       "7  C00014498          9000              1   \n",
       "8  C00055582         10000              1   \n",
       "9  C00580100      18633157            131   \n",
       "\n",
       "                                    cmte_nm  \n",
       "0        TEXANS FOR SENATOR JOHN CORNYN INC  \n",
       "1                         HELLER FOR SENATE  \n",
       "2                 FRIENDS OF DAVE BRAT INC.  \n",
       "3           SOUTH CAROLINA REPUBLICAN PARTY  \n",
       "4  NEW HAMPSHIRE REPUBLICAN STATE COMMITTEE  \n",
       "5                    GRASSLEY COMMITTEE INC  \n",
       "6                      DONOVAN FOR CONGRESS  \n",
       "7                  REPUBLICAN PARTY OF IOWA  \n",
       "8  NY REPUBLICAN FEDERAL CAMPAIGN COMMITTEE  \n",
       "9       DONALD J. TRUMP FOR PRESIDENT, INC.  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query_q4 = \"\"\"\n",
    "SELECT\n",
    "    T1.cmte_id,\n",
    "    SUM(transaction_amt) AS total_amount,\n",
    "    COUNT(*) as num_donations,\n",
    "    T2.cmte_nm\n",
    "FROM indiv_sample_nyc AS T1 LEFT JOIN comm AS T2\n",
    "ON T1.cmte_id = T2.cmte_id\n",
    "WHERE T1.name LIKE '%DONALD%' and T1.name LIKE '%TRUMP%' and T1.name NOT LIKE '%INC%'\n",
    "GROUP BY T1.cmte_id\n",
    "ORDER BY total_amount\n",
    "\"\"\"\n",
    "\n",
    "\n",
    "res_q4 = pd.read_sql(query_q4, engine)\n",
    "res_q4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "deletable": false,
    "editable": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<p><strong><pre style='display: inline;'>q4</pre></strong> passed!</p>"
      ],
      "text/plain": [
       "q4 results: All test cases passed!"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grader.check(\"q4\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": false,
    "editable": false
   },
   "source": [
    "---\n",
    "\n",
    "To double-check your work, the cell below will rerun all of the autograder tests."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "deletable": false,
    "editable": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "q1 results: All test cases passed!\n",
       "\n",
       "q2 results: All test cases passed!\n",
       "\n",
       "q3 results: All test cases passed!\n",
       "\n",
       "q4 results: All test cases passed!"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grader.check_all()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": false,
    "editable": false
   },
   "source": [
    "## Submission\n",
    "\n",
    "Make sure you have run all cells in your notebook in order before running the cell below, so that all images/graphs appear in the output. The cell below will generate a zip file for you to submit. **Please save before exporting!**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "deletable": false,
    "editable": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "            <p>Your submission has been exported. Click <a href=\"lab04_2022_03_13T22_06_14_196342.zip\" download=\"lab04_2022_03_13T22_06_14_196342.zip\" target=\"_blank\">here</a>\n",
       "            to download the zip file.</p>\n",
       "            "
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Save your notebook first, then run this cell to export your submission.\n",
    "grader.export(pdf=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    " "
   ]
  }
 ],
 "metadata": {
  "celltoolbar": "Create Assignment",
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}