DuckDB_Exercise1.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "DuckDB_Exercise1.ipynb",
      "provenance": [],
      "collapsed_sections": [],
      "toc_visible": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "colab_type": "text",
        "id": "x4UIW8GUQaqt"
      },
      "source": [
        "# **SETUP**"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "XXvRwAVPGtiX",
        "colab_type": "text"
      },
      "source": [
        "First we need to install duckdb, all other libraries (e.g., sqlite, urllib,zipfile,pandas,...) used in our tutorial are already installed in the google cloud.\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "IFC0Acb9Gu8W",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "!pip install duckdb"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "XDQF0N7HJIQ_",
        "colab_type": "text"
      },
      "source": [
        "Now we download the Voter dataset and decompress it."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "9RXn51F_JMpn",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "import urllib\n",
        "import zipfile\n",
        "import os\n",
        "\n",
        "\n",
        "print(\"Downloading datasets\")\n",
        "if not os.path.isfile(\"ncvoter_sample.tsv.zip\"):\n",
        "\turllib.request.urlretrieve(\"https://zenodo.org/record/3466870/files/ncvoter_sample.tsv.zip?download=1\", \"ncvoter_sample.tsv.zip\")\n",
        "if not os.path.isfile(\"rprecinctvotes.tsv.zip\"):\n",
        "\turllib.request.urlretrieve(\"https://zenodo.org/record/3466870/files/precinct_votes.tsv?download=1\", \"precinctvotes.tsv\")\n",
        "if not os.path.isfile(\"voters_sqlite.db.zip\"):\n",
        "\turllib.request.urlretrieve(\"https://zenodo.org/record/3466870/files/voters_sqlite.db.zip?download=1\", \"voters_sqlite.db.zip\")\n",
        "\n",
        "print(\"Decompressing files\")\n",
        "if not os.path.isfile(\"ncvoter_sample.tsv\"):\n",
        "\twith zipfile.ZipFile(\"ncvoter_sample.tsv.zip\",\"r\") as zip_ref:\n",
        "\t\tzip_ref.extractall(\"./\")\n",
        "\n",
        "if not os.path.isfile(\"voters_sqlite.db\"):\n",
        "\twith zipfile.ZipFile(\"voters_sqlite.db.zip\",\"r\") as zip_ref:\n",
        "\t\tzip_ref.extractall(\"./\")\n",
        "\t\n",
        "print(\"Finished.\")\n"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ryMJZSckLmqG",
        "colab_type": "text"
      },
      "source": [
        "# **Loading The Data**"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "EJSvVxkOL6af",
        "colab_type": "text"
      },
      "source": [
        "We will first load the data in Pandas and measure how long it takes."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "nBPmYEMLNQ63",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "import time\n",
        "import pandas\n",
        "\n",
        "start = time.time()\n",
        "\n",
        "ncvoters_pandas = pandas.read_csv('ncvoter_sample.tsv', sep='\\t', low_memory=False, names=[\"county_id\" , \"county_desc\" , \"voter_reg_num\" , \"status_cd\" , \"voter_status_desc\" , \"reason_cd\" , \"voter_status_reason_desc\" , \"absent_ind\" , \"name_prefx_cd\" , \"last_name\" , \"first_name\" , \"midl_name\" , \"name_sufx_cd\" , \"full_name_rep\" , \"full_name_mail\" , \"house_num\" , \"half_code\" , \"street_dir\" , \"street_name\" , \"street_type_cd\" , \"street_sufx_cd\" , \"unit_designator\" , \"unit_num\" , \"res_city_desc\" , \"state_cd\" , \"zip_code\" , \"res_street_address\" , \"res_city_state_zip\" , \"mail_addr1\" , \"mail_addr2\" , \"mail_addr3\" , \"mail_addr4\" , \"mail_city\" , \"mail_state\" , \"mail_zipcode\" , \"mail_city_state_zip\" , \"area_cd\" , \"phone_num\" , \"full_phone_number\" , \"drivers_lic\" , \"race_code\" , \"race_desc\" , \"ethnic_code\" , \"ethnic_desc\" , \"party_cd\" , \"party_desc\" , \"sex_code\" , \"sex\" , \"birth_age\" , \"birth_place\" , \"registr_dt\" , \"precinct_abbrv\" , \"precinct_desc\" , \"municipality_abbrv\" , \"municipality_desc\" , \"ward_abbrv\" , \"ward_desc\" , \"cong_dist_abbrv\" , \"cong_dist_desc\" , \"super_court_abbrv\" , \"super_court_desc\" , \"judic_dist_abbrv\" , \"judic_dist_desc\" , \"nc_senate_abbrv\" , \"nc_senate_desc\" , \"nc_house_abbrv\" , \"nc_house_desc\" , \"county_commiss_abbrv\" , \"county_commiss_desc\" , \"township_abbrv\" , \"township_desc\" , \"school_dist_abbrv\" , \"school_dist_desc\" , \"fire_dist_abbrv\" , \"fire_dist_desc\" , \"water_dist_abbrv\" , \"water_dist_desc\" , \"sewer_dist_abbrv\" , \"sewer_dist_desc\" , \"sanit_dist_abbrv\" , \"sanit_dist_desc\" , \"rescue_dist_abbrv\" , \"rescue_dist_desc\" , \"munic_dist_abbrv\" , \"munic_dist_desc\" , \"dist_1_abbrv\" , \"dist_1_desc\" , \"dist_2_abbrv\" , \"dist_2_desc\" , \"confidential_ind\" , \"age\" , \"ncid\" , \"vtd_abbrv\" , \"vtd_desc\"])\n",
        "precinctvotes_pandas = pandas.read_csv('precinctvotes.tsv', sep='\\t', low_memory=False, names = [\"county\", \"precinct\", \"total_votes\", \"romney_percentage\"])\n",
        "\n",
        "end = time.time()\n",
        "print(\"Loading data in Pandas : \" +str(end - start))"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "OQCvdodnNpKl",
        "colab_type": "text"
      },
      "source": [
        "We do the same for SQLite"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "kU6-xxAWNth9",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "import sqlite3\n",
        "import os\n",
        "import csv\n",
        "\n",
        "CREATE_NCVOTERS_SQL = '''\n",
        "    CREATE TABLE IF NOT EXISTS ncvoters(county_id STRING, county_desc STRING, voter_reg_num STRING,status_cd STRING, voter_status_desc STRING, reason_cd STRING, voter_status_reason_desc STRING, absent_ind STRING, name_prefx_cd STRING,last_name STRING, first_name STRING, midl_name STRING, name_sufx_cd STRING, full_name_rep STRING,full_name_mail STRING, house_num STRING, half_code STRING, street_dir STRING, street_name STRING, street_type_cd STRING, street_sufx_cd STRING, unit_designator STRING, unit_num STRING, res_city_desc STRING,state_cd STRING, zip_code STRING, res_street_address STRING, res_city_state_zip STRING, mail_addr1 STRING, mail_addr2 STRING, mail_addr3 STRING, mail_addr4 STRING, mail_city STRING, mail_state STRING, mail_zipcode STRING, mail_city_state_zip STRING, area_cd STRING, phone_num STRING, full_phone_number STRING, drivers_lic STRING, race_code STRING, race_desc STRING, ethnic_code STRING, ethnic_desc STRING, party_cd STRING, party_desc STRING, sex_code STRING, sex STRING, birth_age INTEGER, birth_place STRING, registr_dt STRING, precinct_abbrv STRING, precinct_desc STRING,municipality_abbrv STRING, municipality_desc STRING, ward_abbrv STRING, ward_desc STRING, cong_dist_abbrv STRING, cong_dist_desc STRING, super_court_abbrv STRING, super_court_desc STRING, judic_dist_abbrv STRING, judic_dist_desc STRING, nc_senate_abbrv STRING, nc_senate_desc STRING, nc_house_abbrv STRING, nc_house_desc STRING,county_commiss_abbrv STRING, county_commiss_desc STRING, township_abbrv STRING, township_desc STRING,school_dist_abbrv STRING, school_dist_desc STRING, fire_dist_abbrv STRING, fire_dist_desc STRING, water_dist_abbrv STRING, water_dist_desc STRING, sewer_dist_abbrv STRING, sewer_dist_desc STRING, sanit_dist_abbrv STRING, sanit_dist_desc STRING, rescue_dist_abbrv STRING, rescue_dist_desc STRING, munic_dist_abbrv STRING, munic_dist_desc STRING, dist_1_abbrv STRING, dist_1_desc STRING, dist_2_abbrv STRING, dist_2_desc STRING, confidential_ind STRING, age STRING, ncid STRING, vtd_abbrv STRING, vtd_desc STRING);\n",
        "'''\n",
        "\n",
        "CREATE_PRECINCTVOTES_SQL = '''\n",
        "CREATE TABLE IF NOT EXISTS precinct_votes(county STRING, precinct STRING, total_votes INT, romney_percentage DOUBLE);\n",
        "'''\n",
        "\n",
        "start = time.time()\n",
        "\n",
        "if os.path.isfile(\"voters_sqlite_load.db\"): #Checks if sqlite database already exits\n",
        "\t\tos.system(\"rm voters_sqlite_load.db\") #If Exists deletes Database\n",
        "db_sqlite = sqlite3.connect('voters_sqlite_load.db') #Connects to a SQLite database instance called voters_sqlite.db\n",
        "sqlite_cursor = db_sqlite.cursor() # Creates a Cursor, that allows us to issue Create Table Stataments\n",
        "sqlite_cursor.execute(CREATE_NCVOTERS_SQL)\n",
        "sqlite_cursor.execute(CREATE_PRECINCTVOTES_SQL)\n",
        "\n",
        "with open('ncvoter_sample.tsv','r') as fin: \n",
        "    # csv.DictReader uses first line in file for column headings by default\n",
        "    fieldnames = [\"county_id\" , \"county_desc\" , \"county_desc\" , \"status_cd\" , \"voter_status_desc\" , \"reason_cd\" , \"voter_status_reason_desc\" , \"absent_ind\" , \"name_prefx_cd\" , \"last_name\" , \"first_name\" , \"midl_name\" , \"name_sufx_cd\" , \"full_name_rep\" , \"full_name_mail\" , \"house_num\" , \"half_code\" , \"street_dir\" , \"street_name\" , \"street_type_cd\" , \"street_sufx_cd\" , \"unit_designator\" , \"unit_num\" , \"res_city_desc\" , \"state_cd\" , \"zip_code\" , \"res_street_address\" , \"res_city_state_zip\" , \"mail_addr1\" , \"mail_addr2\" , \"mail_addr3\" , \"mail_addr4\" , \"mail_city\" , \"mail_state\" , \"mail_zipcode\" , \"mail_city_state_zip\" , \"area_cd\" , \"phone_num\" , \"full_phone_number\" , \"drivers_lic\" , \"race_code\" , \"race_desc\" , \"ethnic_code\" , \"ethnic_desc\" , \"party_cd\" , \"party_desc\" , \"sex_code\" , \"sex\" , \"birth_age\" , \"birth_place\" , \"registr_dt\" , \"precinct_abbrv\" , \"precinct_desc\" , \"municipality_abbrv\" , \"municipality_desc\" , \"ward_abbrv\" , \"ward_desc\" , \"cong_dist_abbrv\" , \"cong_dist_desc\" , \"super_court_abbrv\" , \"super_court_desc\" , \"judic_dist_abbrv\" , \"judic_dist_desc\" , \"nc_senate_abbrv\" , \"nc_senate_desc\" , \"nc_house_abbrv\" , \"nc_house_desc\" , \"county_commiss_abbrv\" , \"county_commiss_desc\" , \"township_abbrv\" , \"township_desc\" , \"school_dist_abbrv\" , \"school_dist_desc\" , \"fire_dist_abbrv\" , \"fire_dist_desc\" , \"water_dist_abbrv\" , \"water_dist_desc\" , \"sewer_dist_abbrv\" , \"sewer_dist_desc\" , \"sanit_dist_abbrv\" , \"sanit_dist_desc\" , \"rescue_dist_abbrv\" , \"rescue_dist_desc\" , \"munic_dist_abbrv\" , \"munic_dist_desc\" , \"dist_1_abbrv\" , \"dist_1_desc\" , \"dist_2_abbrv\" , \"dist_2_desc\" , \"confidential_ind\" , \"age\" , \"ncid\" , \"vtd_abbrv\" , \"vtd_desc\"]\n",
        "    dr = csv.DictReader(fin, delimiter = '\\t',fieldnames=fieldnames) \n",
        "    to_db = [(i[\"county_id\"],i[\"county_desc\"],i[\"county_desc\"],i[\"status_cd\"],i[\"voter_status_desc\"],i[\"reason_cd\"],i[\"voter_status_reason_desc\"],i[\"absent_ind\"],i[\"name_prefx_cd\"],i[\"last_name\"],i[\"first_name\"],i[\"midl_name\"],i[\"name_sufx_cd\"],i[\"full_name_rep\"],i[\"full_name_mail\"],i[\"house_num\"],i[\"half_code\"],i[\"street_dir\"],i[\"street_name\"],i[\"street_type_cd\"],i[\"street_sufx_cd\"],i[\"unit_designator\"],i[\"unit_num\"],i[\"res_city_desc\"],i[\"state_cd\"],i[\"zip_code\"],i[\"res_street_address\"],i[\"res_city_state_zip\"],i[\"mail_addr1\"],i[\"mail_addr2\"],i[\"mail_addr3\"],i[\"mail_addr4\"],i[\"mail_city\"],i[\"mail_state\"],i[\"mail_zipcode\"],i[\"mail_city_state_zip\"],i[\"area_cd\"],i[\"phone_num\"],i[\"full_phone_number\"],i[\"drivers_lic\"],i[\"race_code\"],i[\"race_desc\"],i[\"ethnic_code\"],i[\"ethnic_desc\"],i[\"party_cd\"],i[\"party_desc\"],i[\"sex_code\"],i[\"sex\"],i[\"birth_age\"],i[\"birth_place\"],i[\"registr_dt\"],i[\"precinct_abbrv\"],i[\"precinct_desc\"],i[\"municipality_abbrv\"],i[\"municipality_desc\"],i[\"ward_abbrv\"],i[\"ward_desc\"],i[\"cong_dist_abbrv\"],i[\"cong_dist_desc\"],i[\"super_court_abbrv\"],i[\"super_court_desc\"],i[\"judic_dist_abbrv\"],i[\"judic_dist_desc\"],i[\"nc_senate_abbrv\"],i[\"nc_senate_desc\"],i[\"nc_house_abbrv\"],i[\"nc_house_desc\"],i[\"county_commiss_abbrv\"],i[\"county_commiss_desc\"],i[\"township_abbrv\"],i[\"township_desc\"],i[\"school_dist_abbrv\"],i[\"school_dist_desc\"],i[\"fire_dist_abbrv\"],i[\"fire_dist_desc\"],i[\"water_dist_abbrv\"],i[\"water_dist_desc\"],i[\"sewer_dist_abbrv\"],i[\"sewer_dist_desc\"],i[\"sanit_dist_abbrv\"],i[\"sanit_dist_desc\"],i[\"rescue_dist_abbrv\"],i[\"rescue_dist_desc\"],i[\"munic_dist_abbrv\"],i[\"munic_dist_desc\"],i[\"dist_1_abbrv\"],i[\"dist_1_desc\"],i[\"dist_2_abbrv\"],i[\"dist_2_desc\"],i[\"confidential_ind\"],i[\"age\"],i[\"ncid\"],i[\"vtd_abbrv\"],i[\"vtd_desc\"]) for i in dr]\n",
        "sqlite_cursor.executemany(\"INSERT INTO ncvoters (county_id,county_desc,county_desc,status_cd,voter_status_desc,reason_cd,voter_status_reason_desc,absent_ind,name_prefx_cd,last_name,first_name,midl_name,name_sufx_cd,full_name_rep,full_name_mail,house_num,half_code,street_dir,street_name,street_type_cd,street_sufx_cd,unit_designator,unit_num,res_city_desc,state_cd,zip_code,res_street_address,res_city_state_zip,mail_addr1,mail_addr2,mail_addr3,mail_addr4,mail_city,mail_state,mail_zipcode,mail_city_state_zip,area_cd,phone_num,full_phone_number,drivers_lic,race_code,race_desc,ethnic_code,ethnic_desc,party_cd,party_desc,sex_code,sex,birth_age,birth_place,registr_dt,precinct_abbrv,precinct_desc,municipality_abbrv,municipality_desc,ward_abbrv,ward_desc,cong_dist_abbrv,cong_dist_desc,super_court_abbrv,super_court_desc,judic_dist_abbrv,judic_dist_desc,nc_senate_abbrv,nc_senate_desc,nc_house_abbrv,nc_house_desc,county_commiss_abbrv,county_commiss_desc,township_abbrv,township_desc,school_dist_abbrv,school_dist_desc,fire_dist_abbrv,fire_dist_desc,water_dist_abbrv,water_dist_desc,sewer_dist_abbrv,sewer_dist_desc,sanit_dist_abbrv,sanit_dist_desc,rescue_dist_abbrv,rescue_dist_desc,munic_dist_abbrv,munic_dist_desc,dist_1_abbrv,dist_1_desc,dist_2_abbrv,dist_2_desc,confidential_ind,age,ncid,vtd_abbrv,vtd_desc) VALUES (?, ?,?,?,?, ?,?,?,?, ?,?,?,?, ?,?,?,?, ?,?,?,?, ?,?,?,?, ?,?,?,?, ?,?,?,?, ?,?,?,?, ?,?,?,?, ?,?,?,?, ?,?,?,?, ?,?,?,?, ?,?,?,?, ?,?,?,?, ?,?,?,?, ?,?,?,?, ?,?,?,?, ?,?,?,?, ?,?,?,?, ?,?,?,?, ?,?,?,?, ?,?,?,?, ?);\", to_db)\n",
        "\n",
        "with open('precinctvotes.tsv','r') as fin: \n",
        "    # csv.DictReader uses first line in file for column headings by default\n",
        "    fieldnames = [\"county\", \"precinct\", \"total_votes\", \"romney_percentage\"]\n",
        "    dr = csv.DictReader(fin, delimiter = '\\t',fieldnames=fieldnames) \n",
        "    to_db = [(i[\"county\"], i[\"precinct\"], i[\"total_votes\"], i[\"romney_percentage\"]) for i in dr]\n",
        "sqlite_cursor.executemany(\"INSERT INTO precinct_votes (county, precinct,total_votes,romney_percentage) VALUES (?, ?,?,?);\", to_db)\n",
        "\n",
        "db_sqlite.commit()\n",
        "end = time.time()\n",
        "print(\"Loading data in SQLite : \" +str(end - start))"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "YKAtnEB2PuCp",
        "colab_type": "text"
      },
      "source": [
        "How do we load data in DuckDB? https://www.duckdb.org/docs/current/sql/copy.html"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ejriXVhQP1X2",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "import duckdb\n",
        "\n",
        "start = time.time()\n",
        "if os.path.isfile(\"voters_duck.db\"):\n",
        "  os.system(\"rm voters_duck.db\")\n",
        "db = duckdb.connect('voters_duck.db')\n",
        "duck_cursor = db.cursor()\n",
        "duck_cursor.execute(CREATE_NCVOTERS_SQL)\n",
        "duck_cursor.execute(CREATE_PRECINCTVOTES_SQL)\n",
        "# Fill queries here!\n",
        "duck_cursor.execute()\n",
        "duck_cursor.execute()\n",
        "\n",
        "end = time.time()\n",
        "print(\"Loading data in DuckDB : \" +str(end - start))"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "colab_type": "text",
        "id": "vyDSVNG1TmPi"
      },
      "source": [
        "# **Queries**"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "W4wqtjzlT30T",
        "colab_type": "text"
      },
      "source": [
        "Your task is to answer the next three queries on both sqlite and duckdb (because they have the same sql syntax ;-) ) and benchmark. After it, explain why the times to answer the queries differ in those 3 systems.\n",
        "\n",
        "**Query Q1**: How many people voted in the elections?\n",
        "\n",
        "*TIP_1 : First step is to issue a Query, how do you make the cursor issue a query? (Look on previous exercise or at https://www.duckdb.org/docs/current/python/api.html)*\n",
        "\n",
        "*TIP_2 : Now is time to write the query. You must check the if the voters have an 'Active' status (i.e., ncvoters.status_cd='A')*\n",
        "\n",
        "*TIP_3 : Finally, you have to print the results and compare them for correctness*\n",
        "\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "sy7O9XS0T9kJ",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "db_sqlite = sqlite3.connect('voters_sqlite.db') \n",
        "sqlite_cursor = db_sqlite.cursor() \n",
        "#Fill Query here!\n",
        "query_01 = \"\"\n",
        "def query_01_pandas_df(df):\n",
        "\tpandas_query = df[[\"county_id\"]][(df.status_cd == 'A')]\n",
        "\tresult = (pandas_query.county_id).count()\n",
        "\tprint (result)\n",
        "\n",
        "def query_01_pandas():\n",
        "\tquery_01_pandas_df(ncvoters_pandas)\n",
        "\n",
        "#Implement this method\n",
        "def query_01_sqlite():\n",
        "\treturn;\n",
        " \n",
        "#Implement this method\n",
        "def query_01_duckdb():\n",
        "\treturn;\n",
        "\n",
        " \n",
        "start = time.time()\n",
        "query_01_pandas()\n",
        "end = time.time()\n",
        "print(\"Query in Pandas : \" +str(end - start))\n",
        "\n",
        "start = time.time()\n",
        "query_01_sqlite()\n",
        "end = time.time()\n",
        "print(\"Query in SQLite : \" +str(end - start))\n",
        "\n",
        "start = time.time()\n",
        "query_01_duckdb()\n",
        "end = time.time()\n",
        "print(\"Query in DuckDB : \" +str(end - start))"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "AnE6A2olT954",
        "colab_type": "text"
      },
      "source": [
        "**Query Q2**: Count all the people from 70 to 80 and that voted in the election. (i.e., x ppl with 70 yo, y ppl with 71, ...)\n",
        "\n",
        "> Indented block\n",
        "\n",
        "\n",
        "\n",
        "*TIP : Group by query with having clause.*"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "oypnNM8qZel2",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "#Fill Query Here!\n",
        "query_02 = \"\"\n",
        "\n",
        "def query_02_pandas():\n",
        "\tresult = ncvoters_pandas.groupby('birth_age').agg({'county_id': 'count'})\n",
        "\tprint(result[(result.county_id.index > 70) & (result.county_id.index <= 80)])\n",
        "\n",
        "#Implement this method\n",
        "def query_02_sqlite():\n",
        "\treturn;\n",
        "\n",
        "#Implement this method\n",
        "def query_02_duckdb():\n",
        "\treturn;\n",
        " \n",
        "start = time.time()\n",
        "query_02_pandas()\n",
        "end = time.time()\n",
        "print(\"Query in Pandas : \" +str(end - start))\n",
        "\n",
        "start = time.time()\n",
        "query_02_sqlite()\n",
        "end = time.time()\n",
        "print(\"Query in SQLite : \" +str(end - start))\n",
        "\n",
        "start = time.time()\n",
        "query_02_duckdb()\n",
        "end = time.time()\n",
        "print(\"Query in DuckDB : \" +str(end - start))"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "colab_type": "text",
        "id": "zNEso9LqaXYw"
      },
      "source": [
        "**Query Q3**: \n",
        "\n",
        "**Part 1 -**What are the three counties with most male, white men over 40 and are they majority democrats or republicans?\n",
        "\n",
        "**Part 2 -** Return the data as a dataframe, so it can be used in a ML pipeline."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "0M2rSHRlamG3",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "#Fill Query Here!\n",
        "query_03_sql = \"\"\" \"\"\"\n",
        "\n",
        "def query_03_pandas():\n",
        "\tpandas_query = ncvoters_pandas[[\"county_desc\", \"status_cd\", \"sex_code\", \"race_code\", \"birth_age\"]][(ncvoters_pandas.status_cd == 'A')&(ncvoters_pandas.sex_code == 'M') &(ncvoters_pandas.race_code == 'W')].set_index(\"county_desc\").join(precinctvotes_pandas.set_index(\"county\"),on=\"county_desc\")\n",
        "\tres = pandas_query.groupby([\"county_desc\"]).agg({'status_cd': 'count','romney_percentage':'max'}).reset_index(drop=True).sort_values(\"status_cd\", ascending=False).iloc[0:3]\n",
        "\tprint(res)\n",
        " \n",
        " #Implement this method!\n",
        "def query_03_sqlite():\n",
        "\treturn;\n",
        "\n",
        " #Implement this method!\n",
        "def query_03_duckdb():\n",
        "\treturn;\n",
        "\n",
        "start = time.time()\n",
        "query_03_pandas()\n",
        "end = time.time()\n",
        "print(\"Query in Pandas : \" +str(end - start))\n",
        "\n",
        "start = time.time()\n",
        "query_03_sqlite()\n",
        "end = time.time()\n",
        "print(\"Query in SQLite : \" +str(end - start))\n",
        "\n",
        "start = time.time()\n",
        "query_03_duckdb()\n",
        "end = time.time()\n",
        "print(\"Query in DuckDB : \" +str(end - start))"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "colab_type": "text",
        "id": "AhBLdZ0XTnG7"
      },
      "source": [
        "# **Transactions**"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "nPxA70O0AbA-",
        "colab_type": "text"
      },
      "source": [
        "Here we will do a simple data cleaning task. \n",
        "\n",
        "Knowing that the minimum age to vote in the US is 18 years old and that the oldest person that in the US during these elections was 120 years old, can you detect any anomalies?\n",
        "\n",
        "Correct the data by removing dirty tuples from SQLite and DuckDB. "
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "bYS2uTs1BB-q",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "query_detect_anomalies = \"\"\"\n",
        "SELECT count(*) FROM ncvoters where birth_age < 18 or birth_age > 120\n",
        "\"\"\"\n",
        "\n",
        "duck_cursor.execute(query_detect_anomalies)\n",
        "result = duck_cursor.fetchone()\n",
        "print(result)\n"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "S4aL7_CkG2aK",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "#Fill query here\n",
        "drop_anomalies = \"\"\" \"\"\" \n",
        "\n",
        "def query_04_pandas():\n",
        "\tbirth_age_gt_18 = ncvoters_pandas['birth_age'] >= 18\n",
        "\tbirth_age_lt_120 =  ncvoters_pandas['birth_age'] <= 120\n",
        "\tresult = ncvoters_pandas[birth_age_gt_18 & birth_age_lt_120]\n",
        "\tresult.to_csv('result.tsv.tmp', sep='\\t')\n",
        "\n",
        "# Implement this method\n",
        "def query_04_sqlite():\n",
        "\treturn;\n",
        "\n",
        "# Implement this method\n",
        "def query_04_duckdb():\n",
        "\treturn;\n",
        " \n",
        "start = time.time()\n",
        "query_04_pandas()\n",
        "end = time.time()\n",
        "print(\"Transaction in Pandas : \" +str(end - start))\n",
        "\n",
        "start = time.time()\n",
        "query_04_sqlite()\n",
        "end = time.time()\n",
        "print(\"Transaction in SQLite : \" +str(end - start))\n",
        "\n",
        "start = time.time()\n",
        "query_04_duckdb()\n",
        "end = time.time()\n",
        "print(\"Transaction in DuckDB : \" +str(end - start))"
      ],
      "execution_count": 0,
      "outputs": []
    }
  ]
}