examples/soql_queries.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Example 02: SoSQL Queries\n",
    "\n",
    "Constructing custom queries to conserve bandwith and computational resources"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Setup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "# Note: we don't need Pandas\n",
    "# Filters allow you to accomplish many basic operations automatically\n",
    "\n",
    "from sodapy import Socrata"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Find Some Data\n",
    "\n",
    "As in the first example, I'm using the Santa Fe political contribution dataset.\n",
    "\n",
    "`https://opendata.socrata.com/dataset/Santa-Fe-Contributors/f92i-ik66.json`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "socrata_domain = 'opendata.socrata.com'\n",
    "socrata_dataset_identifier = 'f92i-ik66'\n",
    "\n",
    "# If you choose to use a token, run the following command on the terminal (or add it to your .bashrc)\n",
    "# $ export SODAPY_APPTOKEN=<token>\n",
    "socrata_token = os.environ.get(\"SODAPY_APPTOKEN\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "client = Socrata(socrata_domain, socrata_token)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Use Metadata to Plan Your Query\n",
    "You've probably looked through the column names and descriptions in the web UI,\n",
    "but it can be nice to have them right in your workspace as well."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['NAME2',\n",
       " 'AMOUNT-2',\n",
       " 'PARTY',\n",
       " 'NAME',\n",
       " 'JOB',\n",
       " 'COMPANY',\n",
       " 'UPDATE',\n",
       " 'DATE',\n",
       " 'RECIPIENT',\n",
       " 'AMOUNT',\n",
       " 'STREET',\n",
       " 'CITY']"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "metadata = client.get_metadata(socrata_dataset_identifier)\n",
    "[x['name'] for x in metadata['columns']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'cachedContents': {'average': '2433.2075',\n",
       "  'largest': '6100',\n",
       "  'non_null': 800,\n",
       "  'null': 0,\n",
       "  'smallest': '1250',\n",
       "  'sum': '1946566',\n",
       "  'top': [{'count': 426, 'item': '2300'},\n",
       "   {'count': 72, 'item': '4600'},\n",
       "   {'count': 34, 'item': '1500'},\n",
       "   {'count': 33, 'item': '2000'},\n",
       "   {'count': 20, 'item': '1250'},\n",
       "   {'count': 14, 'item': '2100'},\n",
       "   {'count': 12, 'item': '1300'},\n",
       "   {'count': 11, 'item': '3300'},\n",
       "   {'count': 10, 'item': '1750'},\n",
       "   {'count': 10, 'item': '2500'},\n",
       "   {'count': 7, 'item': '1400'},\n",
       "   {'count': 6, 'item': '1800'},\n",
       "   {'count': 5, 'item': '1550'},\n",
       "   {'count': 4, 'item': '2800'},\n",
       "   {'count': 4, 'item': '2050'},\n",
       "   {'count': 4, 'item': '1450'},\n",
       "   {'count': 4, 'item': '1950'},\n",
       "   {'count': 4, 'item': '2200'},\n",
       "   {'count': 4, 'item': '1350'},\n",
       "   {'count': 4, 'item': '1700'}]},\n",
       " 'dataTypeName': 'number',\n",
       " 'fieldName': 'amount',\n",
       " 'format': {'aggregate': 'sum'},\n",
       " 'id': 2303155,\n",
       " 'name': 'AMOUNT',\n",
       " 'position': 10,\n",
       " 'renderTypeName': 'number',\n",
       " 'tableColumnId': 1263296,\n",
       " 'width': 142}"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "meta_amount = [x for x in metadata['columns'] if x['name'] == 'AMOUNT'][0]\n",
    "meta_amount"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Efficiently Query for Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Restrict rows to above-average donations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'2433.2075'"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get the average from the metadata. Note that it's a string by default\n",
    "meta_amount['cachedContents']['average']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total number of non-null results: 800\n",
      "Number of results downloaded: 143\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[{'amount': '6100',\n",
       "  'amount_2': 'Donation of $6,100 to Presidential elections 2008',\n",
       "  'city': 'Santa Fe NM',\n",
       "  'company': 'Thornburg Companies',\n",
       "  'date': 'Q3/2008',\n",
       "  'job': 'Finance',\n",
       "  'name': 'Garrett Thornburg',\n",
       "  'name2': 'Garrett Thornburg',\n",
       "  'party': 'Democrat',\n",
       "  'recipient': 'Barack Obama',\n",
       "  'street': '150 WASHINGTON AVE',\n",
       "  'update': 'Updated'},\n",
       " {'amount': '5600',\n",
       "  'amount_2': 'Donation of $5,600 to Presidential elections 2008',\n",
       "  'city': 'Santa Fe NM',\n",
       "  'company': 'FOREST REALTY',\n",
       "  'date': 'Q3/2007',\n",
       "  'job': 'REAL ESTATE',\n",
       "  'name': 'Michael Daly',\n",
       "  'name2': 'Michael Daly',\n",
       "  'party': 'Democrat',\n",
       "  'recipient': 'Bill Richardson',\n",
       "  'street': '305 BROWNELL HOWLAND RD',\n",
       "  'update': 'Updated'},\n",
       " {'amount': '5100',\n",
       "  'amount_2': 'Donation of $5,100 to Presidential elections 2008',\n",
       "  'city': 'Santa Fe NM',\n",
       "  'company': 'James Currey Publishers',\n",
       "  'date': 'Q3/2008',\n",
       "  'job': 'Publisher',\n",
       "  'name': 'Douglas Johnson',\n",
       "  'name2': 'Douglas Johnson',\n",
       "  'party': 'Democrat',\n",
       "  'recipient': 'Barack Obama',\n",
       "  'street': '48 WOODS LOOP',\n",
       "  'update': 'Updated'}]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Use the 'where' argument to filter the data before downloading it\n",
    "results = client.get(socrata_dataset_identifier, where=\"amount >= 2433\")\n",
    "print(\"Total number of non-null results: {}\".format(meta_amount['cachedContents']['non_null']))\n",
    "print(\"Number of results downloaded: {}\".format(len(results)))\n",
    "results[:3]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Restrict columns and order rows\n",
    "Often, you know which columns you want, so you can further simplify the download.\n",
    "\n",
    "It can also be valuable to have results in order, so that you can quickly grab the\n",
    "largest or smallest."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'amount': '1250', 'job': 'QA Architect'},\n",
       " {'amount': '1250', 'job': 'Artist'},\n",
       " {'amount': '1250', 'job': 'investor'}]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "results = client.get(socrata_dataset_identifier,\n",
    "                     where=\"amount < 2433\",\n",
    "                     select=\"amount, job\",\n",
    "                     order=\"amount ASC\")\n",
    "results[:3]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Perform basic operations\n",
    "You can even accomplish some basic analytics operations like finding sums.\n",
    "\n",
    "If you're planning on doing further processing, note that the numeric outputs\n",
    "are strings by default."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'recipient': 'Bill Richardson', 'sum_amount': '1020748'},\n",
       " {'recipient': 'Barack Obama', 'sum_amount': '602865'},\n",
       " {'recipient': 'Hillary Clinton', 'sum_amount': '185887'},\n",
       " {'recipient': 'John McCain', 'sum_amount': '60741'},\n",
       " {'recipient': 'John Edwards', 'sum_amount': '25550'},\n",
       " {'recipient': 'Christopher Dodd', 'sum_amount': '14200'},\n",
       " {'recipient': 'Rudy Giuliani', 'sum_amount': '13800'},\n",
       " {'recipient': 'Ron Paul', 'sum_amount': '11500'},\n",
       " {'recipient': 'Mitt Romney', 'sum_amount': '4600'},\n",
       " {'recipient': 'Joe Biden', 'sum_amount': '4600'},\n",
       " {'recipient': 'Fred Thompson', 'sum_amount': '2075'}]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "results = client.get(socrata_dataset_identifier,\n",
    "                     group=\"recipient\",\n",
    "                     select=\"sum(amount), recipient\",\n",
    "                     order=\"sum(amount) DESC\")\n",
    "results"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Break download into managable chunks\n",
    "Sometimes you do want all the data, but it would be too big for one download.\n",
    "\n",
    "By default, all queries have a limit of 1000 rows, but you can manually set it\n",
    "higher or lower. If you want to loop through results, just use `offset`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'amount': '6100', 'name': 'Garrett Thornburg'},\n",
       " {'amount': '5600', 'name': 'Michael Daly'},\n",
       " {'amount': '5100', 'name': 'Douglas Johnson'},\n",
       " {'amount': '5100', 'name': 'Lynn Mortensen'},\n",
       " {'amount': '5100', 'name': 'Ted Flicker'},\n",
       " {'amount': '4906', 'name': 'Jere Smith'}]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "results = client.get(socrata_dataset_identifier, limit=6, select=\"name, amount\")\n",
    "results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "> Loop number: 0\n",
      "{'amount': '6100', 'name': 'Garrett Thornburg'}\n",
      "{'amount': '5600', 'name': 'Michael Daly'}\n",
      "{'amount': '5100', 'name': 'Douglas Johnson'}\n",
      "\n",
      "> Loop number: 1\n",
      "{'amount': '5100', 'name': 'Lynn Mortensen'}\n",
      "{'amount': '5100', 'name': 'Ted Flicker'}\n",
      "{'amount': '4906', 'name': 'Jere Smith'}\n"
     ]
    }
   ],
   "source": [
    "loop_size = 3\n",
    "num_loops = 2\n",
    "\n",
    "for i in range(num_loops):\n",
    "    results = client.get(socrata_dataset_identifier,\n",
    "                         select=\"name, amount\",\n",
    "                         limit=loop_size,\n",
    "                         offset=loop_size * i)\n",
    "    print(\"\\n> Loop number: {}\".format(i))\n",
    "    \n",
    "    # This simply formats the output nicely\n",
    "    for result in results:\n",
    "        print(result)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Query strings\n",
    "All of the queries above were made with method parameters,\n",
    "but you could also pass all the parameters at once in a\n",
    "SQL-like format"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'amount': '1995', 'name': 'Shelley Silverstein'},\n",
       " {'amount': '1974', 'name': 'Marvin Godner'},\n",
       " {'amount': '1954', 'name': 'Stuart Ashman'},\n",
       " {'amount': '1950', 'name': 'Hope Curtis'},\n",
       " {'amount': '1950', 'name': 'David Harwell'}]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "select \n",
    "    name, \n",
    "    amount\n",
    "where\n",
    "    amount > 1000\n",
    "    and amount < 2000\n",
    "limit\n",
    "    5\n",
    "\"\"\"\n",
    "\n",
    "results = client.get(socrata_dataset_identifier, query=query)\n",
    "results"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Free text search\n",
    "My brother just got a dog named Slider, so we were curious about how many other New York City dogs had that name.\n",
    "\n",
    "Searches with `q` match anywhere in the row, which allows you to quickly search through data with several free text columns of interest."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'animalname': 'SLIDER', 'breedname': 'American Pit Bull Terrier/Pit Bull'},\n",
       " {'animalname': 'SLIDER', 'breedname': 'Cavalier King Charles Spaniel'},\n",
       " {'animalname': 'SLIDER ', 'breedname': 'Shih Tzu'},\n",
       " {'animalname': 'SLIDER', 'breedname': 'Wheaton Terrier'}]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nyc_dogs_domain = 'data.cityofnewyork.us'\n",
    "nyc_dogs_dataset_identifier = 'nu7n-tubp'\n",
    "\n",
    "nyc_dogs_client = Socrata(nyc_dogs_domain, socrata_token)\n",
    "results = nyc_dogs_client.get(nyc_dogs_dataset_identifier,\n",
    "                              q=\"Slider\",\n",
    "                              select=\"animalname, breedname\")\n",
    "results"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Going Further\n",
    "\n",
    "There's plenty more to do! Check out [Queries using SODA](https://dev.socrata.com/docs/queries/) for additional functionality"
   ]
  }
 ],
 "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.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}