examples/basic_queries.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Example 01: Basic Queries\n",
    "\n",
    "Retrieving data from Socrata databases using sodapy"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Setup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "from sodapy import Socrata"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Find some data\n",
    "\n",
    "Though any organization can host their own data with Socrata's tools, Socrata also hosts several open datasets themselves:\n",
    "\n",
    "https://opendata.socrata.com/browse\n",
    "\n",
    "The following search options can help you find some great datasets for getting started:\n",
    "* Limit to data sets (pre-analyzed stuff is great, but if you're using sodapy you probably want the raw numbers!)\n",
    "* Sort by \"Most Accessed\"\n",
    "\n",
    "[Here's](https://opendata.socrata.com/browse?limitTo=datasets&sortBy=most_accessed&utf8=%E2%9C%93&page=1) a link that applies those filters automatically.\n",
    "\n",
    "Click on a few listings until you find one that looks interesting. Then click API and extract the following bits of data from the displayed url.\n",
    "\n",
    "https://<**opendata.socrata.com**>/dataset/Santa-Fe-Contributors/<**f92i-ik66**>.json\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![Socrata Interface](socrata_interface.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Enter the information from those sections here\n",
    "socrata_domain = 'opendata.socrata.com'\n",
    "socrata_dataset_identifier = 'f92i-ik66'\n",
    "\n",
    "# App Tokens can be generated by creating an account at https://opendata.socrata.com/signup\n",
    "# Tokens are optional (`None` can be used instead), though requests will be rate limited.\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\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Get all the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Domain: opendata.socrata.com\n",
      "Session: <requests.sessions.Session object at 0x7f8a0363d518>\n",
      "URI Prefix: https://\n"
     ]
    }
   ],
   "source": [
    "client = Socrata(socrata_domain, socrata_token)\n",
    "print(\"Domain: {domain:}\\nSession: {session:}\\nURI Prefix: {uri_prefix:}\".format(**client.__dict__))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>amount</th>\n",
       "      <th>amount_2</th>\n",
       "      <th>city</th>\n",
       "      <th>company</th>\n",
       "      <th>date</th>\n",
       "      <th>job</th>\n",
       "      <th>name</th>\n",
       "      <th>name2</th>\n",
       "      <th>party</th>\n",
       "      <th>recipient</th>\n",
       "      <th>street</th>\n",
       "      <th>update</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6100</td>\n",
       "      <td>Donation of $6,100 to Presidential elections 2008</td>\n",
       "      <td>Santa Fe NM</td>\n",
       "      <td>Thornburg Companies</td>\n",
       "      <td>Q3/2008</td>\n",
       "      <td>Finance</td>\n",
       "      <td>Garrett Thornburg</td>\n",
       "      <td>Garrett Thornburg</td>\n",
       "      <td>Democrat</td>\n",
       "      <td>Barack Obama</td>\n",
       "      <td>150 WASHINGTON AVE</td>\n",
       "      <td>Updated</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5600</td>\n",
       "      <td>Donation of $5,600 to Presidential elections 2008</td>\n",
       "      <td>Santa Fe NM</td>\n",
       "      <td>FOREST REALTY</td>\n",
       "      <td>Q3/2007</td>\n",
       "      <td>REAL ESTATE</td>\n",
       "      <td>Michael Daly</td>\n",
       "      <td>Michael Daly</td>\n",
       "      <td>Democrat</td>\n",
       "      <td>Bill Richardson</td>\n",
       "      <td>305 BROWNELL HOWLAND RD</td>\n",
       "      <td>Updated</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5100</td>\n",
       "      <td>Donation of $5,100 to Presidential elections 2008</td>\n",
       "      <td>Santa Fe NM</td>\n",
       "      <td>James Currey Publishers</td>\n",
       "      <td>Q3/2008</td>\n",
       "      <td>Publisher</td>\n",
       "      <td>Douglas Johnson</td>\n",
       "      <td>Douglas Johnson</td>\n",
       "      <td>Democrat</td>\n",
       "      <td>Barack Obama</td>\n",
       "      <td>48 WOODS LOOP</td>\n",
       "      <td>Updated</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5100</td>\n",
       "      <td>Donation of $5,100 to Presidential elections 2008</td>\n",
       "      <td>Santa Fe NM</td>\n",
       "      <td>Academy for Educational Developm</td>\n",
       "      <td>Q3/2008</td>\n",
       "      <td>Chief of Party</td>\n",
       "      <td>Lynn Mortensen</td>\n",
       "      <td>Lynn Mortensen</td>\n",
       "      <td>Democrat</td>\n",
       "      <td>Barack Obama</td>\n",
       "      <td>4 AVILA RD</td>\n",
       "      <td>Updated</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5100</td>\n",
       "      <td>Donation of $5,100 to Presidential elections 2008</td>\n",
       "      <td>Santa Fe NM</td>\n",
       "      <td>Self employed</td>\n",
       "      <td>Q1/2008</td>\n",
       "      <td>Sculptor</td>\n",
       "      <td>Ted Flicker</td>\n",
       "      <td>Ted Flicker</td>\n",
       "      <td>Democrat</td>\n",
       "      <td>Barack Obama</td>\n",
       "      <td>164 TANO RD</td>\n",
       "      <td>Updated</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  amount                                           amount_2         city  \\\n",
       "0   6100  Donation of $6,100 to Presidential elections 2008  Santa Fe NM   \n",
       "1   5600  Donation of $5,600 to Presidential elections 2008  Santa Fe NM   \n",
       "2   5100  Donation of $5,100 to Presidential elections 2008  Santa Fe NM   \n",
       "3   5100  Donation of $5,100 to Presidential elections 2008  Santa Fe NM   \n",
       "4   5100  Donation of $5,100 to Presidential elections 2008  Santa Fe NM   \n",
       "\n",
       "                            company     date             job  \\\n",
       "0               Thornburg Companies  Q3/2008         Finance   \n",
       "1                     FOREST REALTY  Q3/2007     REAL ESTATE   \n",
       "2           James Currey Publishers  Q3/2008       Publisher   \n",
       "3  Academy for Educational Developm  Q3/2008  Chief of Party   \n",
       "4                     Self employed  Q1/2008        Sculptor   \n",
       "\n",
       "                name              name2     party        recipient  \\\n",
       "0  Garrett Thornburg  Garrett Thornburg  Democrat     Barack Obama   \n",
       "1       Michael Daly       Michael Daly  Democrat  Bill Richardson   \n",
       "2    Douglas Johnson    Douglas Johnson  Democrat     Barack Obama   \n",
       "3     Lynn Mortensen     Lynn Mortensen  Democrat     Barack Obama   \n",
       "4        Ted Flicker        Ted Flicker  Democrat     Barack Obama   \n",
       "\n",
       "                    street   update  \n",
       "0       150 WASHINGTON AVE  Updated  \n",
       "1  305 BROWNELL HOWLAND RD  Updated  \n",
       "2            48 WOODS LOOP  Updated  \n",
       "3               4 AVILA RD  Updated  \n",
       "4              164 TANO RD  Updated  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "results = client.get(socrata_dataset_identifier)\n",
    "df = pd.DataFrame.from_dict(results)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Success! Let's do some minimal cleaning and analysis just to justify the bandwidth used."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['amount'] = df['amount'].astype(float)"
   ]
  },
  {
   "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>sum</th>\n",
       "      <th>mean</th>\n",
       "      <th>size</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>recipient</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Bill Richardson</th>\n",
       "      <td>1020748.0</td>\n",
       "      <td>2442.0</td>\n",
       "      <td>418.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Barack Obama</th>\n",
       "      <td>602865.0</td>\n",
       "      <td>2491.0</td>\n",
       "      <td>242.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Hillary Clinton</th>\n",
       "      <td>185887.0</td>\n",
       "      <td>2324.0</td>\n",
       "      <td>80.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>John McCain</th>\n",
       "      <td>60741.0</td>\n",
       "      <td>2025.0</td>\n",
       "      <td>30.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>John Edwards</th>\n",
       "      <td>25550.0</td>\n",
       "      <td>2555.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       sum    mean   size\n",
       "recipient                                \n",
       "Bill Richardson  1020748.0  2442.0  418.0\n",
       "Barack Obama      602865.0  2491.0  242.0\n",
       "Hillary Clinton   185887.0  2324.0   80.0\n",
       "John McCain        60741.0  2025.0   30.0\n",
       "John Edwards       25550.0  2555.0   10.0"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "by_candidate = df.groupby('recipient').amount.aggregate([np.sum, np.mean, np.size]).round(0)\n",
    "by_candidate.sort_values('sum', ascending=False).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Multiple Data Sources\n",
    "\n",
    "That was much less annoying than downloading a CSV, though you can always save the dataframe to a CSV if you'd like. Where sodapy really shines though is in grabbing different data sources and mashing them together.\n",
    "\n",
    "For example, let's compare 311 calls between [New York City](https://data.cityofnewyork.us/Social-Services/311-Service-Requests-from-2010-to-Present/erm2-nwe9) and [Chattanooga, TN](https://data.chattlibrary.org/Government/311-Service-Requests/9iep-6yhz). Socrata makes it so easy, you'd be crazy _not_ to do it!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1000, 48)\n",
      "(1000, 28)\n"
     ]
    }
   ],
   "source": [
    "nyc_domain = 'data.cityofnewyork.us'\n",
    "nyc_dataset_identifier = 'fhrw-4uyv'\n",
    "nyc_client = Socrata(nyc_domain, socrata_token)\n",
    "nyc_results = nyc_client.get(nyc_dataset_identifier)\n",
    "nyc_df = pd.DataFrame.from_dict(nyc_results)\n",
    "print(nyc_df.shape)\n",
    "\n",
    "chatt_domain = 'data.chattlibrary.org'\n",
    "chatt_dataset_identifier = 'sf89-4qcw'\n",
    "chatt_client = Socrata(chatt_domain, socrata_token)\n",
    "chatt_results = chatt_client.get(chatt_dataset_identifier)\n",
    "chatt_df = pd.DataFrame.from_dict(chatt_results)\n",
    "print(chatt_df.shape)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>nyc</th>\n",
       "      <th>chatt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>False</th>\n",
       "      <td>0.93</td>\n",
       "      <td>0.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>True</th>\n",
       "      <td>0.07</td>\n",
       "      <td>0.02</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        nyc  chatt\n",
       "False  0.93   0.98\n",
       "True   0.07   0.02"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# extract tree-related complaints\n",
    "tree_related = pd.concat([\n",
    "    nyc_df.complaint_type.str.contains(r'[T|t]ree').value_counts(),\n",
    "    chatt_df.description.str.contains(r'[T|t]ree').value_counts()\n",
    "], axis=1, keys=['nyc', 'chatt'])\n",
    "tree_related.div(tree_related.sum()).round(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Looks like trees are a higher percentage of NYC complaints than Chattanooga's.\n",
    "\n",
    "Note that we can only talk about percentages, since our query results got truncated to 1,000 rows.\n",
    "\n",
    "What if we want to be smarter about what we ask for, so that we can get 100% of the subset of data\n",
    "we're most interested in? That's the subject of a future example, so stay tuned!\n",
    "\n",
    "If you want to find more data sets, here's Socrata's data finder:\n",
    "\n",
    "https://www.opendatanetwork.com/search"
   ]
  }
 ],
 "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
}