demo.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Grabbing data with cenpy"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Cenpy (`sen - pie`) is a package that exposes APIs from the US Census Bureau and makes it easy to pull down and work with Census data in Pandas. First, notice that there are two core modules in the package, `base` and `explorer`, which each do different things. First, let's look at `explorer`. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "import cenpy as c\n",
    "import pandas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "On import, `explorer` requests all currently available APIs from the Census Bureau's [API listing](http://www.census.gov/data/developers/data-sets.html). In future, it will can also read a `JSON` collection describing the databases from disk, if asked.\n",
    "\n",
    "Explorer has two functions, `available` and `explain`. `available` will provide a list of the identifiers of all the APIs that `cenpy` knows about. If run with `verbose=True`, `cenpy` will also include the title of the database as a dictionary. It's a good idea to *not* process this directly, and instead use it to explore currently available APIs. Here, I'll just show the first five entries:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'ACSSF5Y2013': '2009-2013 American Community Survey 5-Year Estimates',\n",
       " 'POPESTagespecialPR': 'Vintage 2014 Population Estimates: Puerto Rico Commonwealth and Municipios Annual Resident Population Estimates by Age Groups and Sex',\n",
       " 'POPESTprm2014': 'Vintage 2014 Population Estimates: Puerto Rico Municipios Total Population',\n",
       " 'ftd15ImpExpHist': '2015 International Trade: Historical Imports and Exports',\n",
       " 'ftdImpExpHist': '2014 International Trade: Historical Imports and Exports'}"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "{k:v for i, (k,v) in enumerate(c.explorer.available(verbose=True).items()) if i < 5}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `explain` command provides the title and full description of the datasource. If run in verbose mode, the function returns the full `json` listing of the API. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'2011 American Community Survey: 5-Year Estimates': 'The American Community Survey (ACS) is an ongoing survey that provides data every year -- giving communities the current information they need to plan investments and services. The ACS covers a broad range of topics about social, economic, demographic, and housing characteristics of the U.S. population.  Summary files include the following geographies: nation, all states (including DC and Puerto Rico), all metropolitan areas, all congressional districts (114th congress), all counties, all places, and all tracts and block groups.  Summary files contain the most detailed cross-tabulations, many of which are published down to block groups. The data are population and housing counts. There are over 64,000 variables in this dataset.'}"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c.explorer.explain('2011acs5')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To actually connect to a database resource, you create a `Connection`. A `Connection` works like a *very* simplified connection from the `sqlalchemy` world. The `Connection` class has a method, `query` that constructs a query string and requests it from the Census server. This result is then parsed into JSON and returned to the user.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "conn = c.base.Connection('DecennialSF12010')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Connection to 2010 Decennial: Summary File 1 (ID: http://api.census.gov/data/id/DecennialSF12010)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "That may have taken longer than you'd've expected. This is because, when the `Connection` constructor is called, it populates the connection object with a bit of metadata that makes it possible to construct queries without referring to the census handbooks. \n",
    "\n",
    "For instance, a connection's `variables` represent all available search parameters for a given dataset. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>concept</th>\n",
       "      <th>label</th>\n",
       "      <th>predicateOnly</th>\n",
       "      <th>predicateType</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>AIANHH</th>\n",
       "      <td>Geographic Summary Level</td>\n",
       "      <td>GEO PLACE HOLDER</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AIANHHCC</th>\n",
       "      <td>Geographic Characteristics</td>\n",
       "      <td>GEO PLACE HOLDER</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AIANHHFP</th>\n",
       "      <td>Geographic Characteristics</td>\n",
       "      <td>GEO PLACE HOLDER</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AIHHTLI</th>\n",
       "      <td>Geographic Characteristics</td>\n",
       "      <td>GEO PLACE HOLDER</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AITS</th>\n",
       "      <td>Geographic Characteristics</td>\n",
       "      <td>GEO PLACE HOLDER</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             concept             label predicateOnly  \\\n",
       "AIANHH      Geographic Summary Level  GEO PLACE HOLDER           NaN   \n",
       "AIANHHCC  Geographic Characteristics  GEO PLACE HOLDER           NaN   \n",
       "AIANHHFP  Geographic Characteristics  GEO PLACE HOLDER           NaN   \n",
       "AIHHTLI   Geographic Characteristics  GEO PLACE HOLDER           NaN   \n",
       "AITS      Geographic Characteristics  GEO PLACE HOLDER           NaN   \n",
       "\n",
       "         predicateType  \n",
       "AIANHH             NaN  \n",
       "AIANHHCC           NaN  \n",
       "AIANHHFP           NaN  \n",
       "AIHHTLI            NaN  \n",
       "AITS               NaN  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.variables.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This dataframe is populated just like the census's table describing the variables on the corresponding [api website](http://api.census.gov/data/2010/sf1/variables.html). Fortunately, this means that you can modify and filter this dataframe just like you can regular pandas dataframes, so working out what the exact codes to use in your query is easy. \n",
    "\n",
    "I've added a function, `varslike`, that globs variables that fit a regular expression pattern. It can use the builtin python `re` module, in addition to the `fnmatch` module. It also can use any filtering function you want. \n",
    "\n",
    "So, you can extract the rows of the variables using the `df.ix` method on the list of columns that match your expression:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>concept</th>\n",
       "      <th>label</th>\n",
       "      <th>predicateOnly</th>\n",
       "      <th>predicateType</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>H011A0001</th>\n",
       "      <td>H11A. TOTAL POPULATION IN OCCUPIED HOUSING UNI...</td>\n",
       "      <td>Population in occupied housing units with a ho...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>H011A0002</th>\n",
       "      <td>H11A. TOTAL POPULATION IN OCCUPIED HOUSING UNI...</td>\n",
       "      <td>Owned with a mortgage or a loan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>H011A0003</th>\n",
       "      <td>H11A. TOTAL POPULATION IN OCCUPIED HOUSING UNI...</td>\n",
       "      <td>Owned free and clear</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>H011A0004</th>\n",
       "      <td>H11A. TOTAL POPULATION IN OCCUPIED HOUSING UNI...</td>\n",
       "      <td>Renter occupied</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>H011B0001</th>\n",
       "      <td>H11B. TOTAL POPULATION IN OCCUPIED HOUSING UNI...</td>\n",
       "      <td>Population in occupied housing units with a ho...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>H011B0002</th>\n",
       "      <td>H11B. TOTAL POPULATION IN OCCUPIED HOUSING UNI...</td>\n",
       "      <td>Owned with a mortgage or a loan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>H011B0003</th>\n",
       "      <td>H11B. TOTAL POPULATION IN OCCUPIED HOUSING UNI...</td>\n",
       "      <td>Owned free and clear</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>H011B0004</th>\n",
       "      <td>H11B. TOTAL POPULATION IN OCCUPIED HOUSING UNI...</td>\n",
       "      <td>Renter occupied</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                     concept  \\\n",
       "H011A0001  H11A. TOTAL POPULATION IN OCCUPIED HOUSING UNI...   \n",
       "H011A0002  H11A. TOTAL POPULATION IN OCCUPIED HOUSING UNI...   \n",
       "H011A0003  H11A. TOTAL POPULATION IN OCCUPIED HOUSING UNI...   \n",
       "H011A0004  H11A. TOTAL POPULATION IN OCCUPIED HOUSING UNI...   \n",
       "H011B0001  H11B. TOTAL POPULATION IN OCCUPIED HOUSING UNI...   \n",
       "H011B0002  H11B. TOTAL POPULATION IN OCCUPIED HOUSING UNI...   \n",
       "H011B0003  H11B. TOTAL POPULATION IN OCCUPIED HOUSING UNI...   \n",
       "H011B0004  H11B. TOTAL POPULATION IN OCCUPIED HOUSING UNI...   \n",
       "\n",
       "                                                       label predicateOnly  \\\n",
       "H011A0001  Population in occupied housing units with a ho...           NaN   \n",
       "H011A0002                    Owned with a mortgage or a loan           NaN   \n",
       "H011A0003                               Owned free and clear           NaN   \n",
       "H011A0004                                    Renter occupied           NaN   \n",
       "H011B0001  Population in occupied housing units with a ho...           NaN   \n",
       "H011B0002                    Owned with a mortgage or a loan           NaN   \n",
       "H011B0003                               Owned free and clear           NaN   \n",
       "H011B0004                                    Renter occupied           NaN   \n",
       "\n",
       "          predicateType  \n",
       "H011A0001           NaN  \n",
       "H011A0002           NaN  \n",
       "H011A0003           NaN  \n",
       "H011A0004           NaN  \n",
       "H011B0001           NaN  \n",
       "H011B0002           NaN  \n",
       "H011B0003           NaN  \n",
       "H011B0004           NaN  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.variables.ix[conn.varslike('H011[AB]')]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Likewise, the different levels of geographic scale are determined from the metadata in the overall API listing and recorded. \n",
    "\n",
    "However, many Census products have multiple possible geographical indexing systems, like the deprecated `fips` code system and the new *Geographical Names Information System*, `gnis`. Thus, the `geographies` property is a dictionary of dataframes, where each key is the name of the identifier system and the value is the dataframe describing the identifier system. \n",
    "\n",
    "For the 2010 census, only `fips` and `gnis` systems are available. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dict_keys(['fips', 'gnis'])"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.geographies.keys()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>geoLevelId</th>\n",
       "      <th>name</th>\n",
       "      <th>optionalWithWCFor</th>\n",
       "      <th>requires</th>\n",
       "      <th>wildcard</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>040</td>\n",
       "      <td>state</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>050</td>\n",
       "      <td>county</td>\n",
       "      <td>state</td>\n",
       "      <td>[state]</td>\n",
       "      <td>[state]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>060</td>\n",
       "      <td>county subdivision</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[state, county]</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>067</td>\n",
       "      <td>subminor civil subdivision</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[state, county, county subdivision]</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>101</td>\n",
       "      <td>block</td>\n",
       "      <td>tract</td>\n",
       "      <td>[state, county, tract]</td>\n",
       "      <td>[tract]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>140</td>\n",
       "      <td>tract</td>\n",
       "      <td>county</td>\n",
       "      <td>[state, county]</td>\n",
       "      <td>[county]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>150</td>\n",
       "      <td>block group</td>\n",
       "      <td>tract</td>\n",
       "      <td>[state, county, tract]</td>\n",
       "      <td>[tract]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>160</td>\n",
       "      <td>place</td>\n",
       "      <td>state</td>\n",
       "      <td>[state]</td>\n",
       "      <td>[state]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>230</td>\n",
       "      <td>alaska native regional corporation</td>\n",
       "      <td>state</td>\n",
       "      <td>[state]</td>\n",
       "      <td>[state]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>280</td>\n",
       "      <td>american indian area/alaska native area/hawaii...</td>\n",
       "      <td>state</td>\n",
       "      <td>[state]</td>\n",
       "      <td>[state]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>281</td>\n",
       "      <td>tribal subdivision</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[state, american indian area]</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>320</td>\n",
       "      <td>metropolitan statistical area/micropolitan sta...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[state]</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>323</td>\n",
       "      <td>metropolitan division</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[state, metropolitan statistical area/micropol...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>340</td>\n",
       "      <td>combined statistical area</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[state]</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>500</td>\n",
       "      <td>congressional district</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[state]</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>510</td>\n",
       "      <td>county</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[state, congressional district]</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>511</td>\n",
       "      <td>tract</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[state, congressional district, county]</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>521</td>\n",
       "      <td>county subdivision</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[state, congressional district, county]</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>531</td>\n",
       "      <td>place</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[state, congressional district]</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>550</td>\n",
       "      <td>american indian area/alaska native area/hawaii...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[state, congressional district]</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>560</td>\n",
       "      <td>alaska native regional corporation</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[state, congressional district]</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>610</td>\n",
       "      <td>state legislative district (upper chamber)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[state]</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>620</td>\n",
       "      <td>state legislative district (lower chamber)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[state]</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>871</td>\n",
       "      <td>zip code tabulation area</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[state]</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   geoLevelId                                               name  \\\n",
       "0         040                                              state   \n",
       "1         050                                             county   \n",
       "2         060                                 county subdivision   \n",
       "3         067                         subminor civil subdivision   \n",
       "4         101                                              block   \n",
       "5         140                                              tract   \n",
       "6         150                                        block group   \n",
       "7         160                                              place   \n",
       "8         230                 alaska native regional corporation   \n",
       "9         280  american indian area/alaska native area/hawaii...   \n",
       "10        281                                 tribal subdivision   \n",
       "11        320  metropolitan statistical area/micropolitan sta...   \n",
       "12        323                              metropolitan division   \n",
       "13        340                          combined statistical area   \n",
       "14        500                             congressional district   \n",
       "15        510                                             county   \n",
       "16        511                                              tract   \n",
       "17        521                                 county subdivision   \n",
       "18        531                                              place   \n",
       "19        550  american indian area/alaska native area/hawaii...   \n",
       "20        560                 alaska native regional corporation   \n",
       "21        610         state legislative district (upper chamber)   \n",
       "22        620         state legislative district (lower chamber)   \n",
       "23        871                           zip code tabulation area   \n",
       "\n",
       "   optionalWithWCFor                                           requires  \\\n",
       "0                NaN                                                NaN   \n",
       "1              state                                            [state]   \n",
       "2                NaN                                    [state, county]   \n",
       "3                NaN                [state, county, county subdivision]   \n",
       "4              tract                             [state, county, tract]   \n",
       "5             county                                    [state, county]   \n",
       "6              tract                             [state, county, tract]   \n",
       "7              state                                            [state]   \n",
       "8              state                                            [state]   \n",
       "9              state                                            [state]   \n",
       "10               NaN                      [state, american indian area]   \n",
       "11               NaN                                            [state]   \n",
       "12               NaN  [state, metropolitan statistical area/micropol...   \n",
       "13               NaN                                            [state]   \n",
       "14               NaN                                            [state]   \n",
       "15               NaN                    [state, congressional district]   \n",
       "16               NaN            [state, congressional district, county]   \n",
       "17               NaN            [state, congressional district, county]   \n",
       "18               NaN                    [state, congressional district]   \n",
       "19               NaN                    [state, congressional district]   \n",
       "20               NaN                    [state, congressional district]   \n",
       "21               NaN                                            [state]   \n",
       "22               NaN                                            [state]   \n",
       "23               NaN                                            [state]   \n",
       "\n",
       "    wildcard  \n",
       "0        NaN  \n",
       "1    [state]  \n",
       "2        NaN  \n",
       "3        NaN  \n",
       "4    [tract]  \n",
       "5   [county]  \n",
       "6    [tract]  \n",
       "7    [state]  \n",
       "8    [state]  \n",
       "9    [state]  \n",
       "10       NaN  \n",
       "11       NaN  \n",
       "12       NaN  \n",
       "13       NaN  \n",
       "14       NaN  \n",
       "15       NaN  \n",
       "16       NaN  \n",
       "17       NaN  \n",
       "18       NaN  \n",
       "19       NaN  \n",
       "20       NaN  \n",
       "21       NaN  \n",
       "22       NaN  \n",
       "23       NaN  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.geographies['fips']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>geoLevelId</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>170</td>\n",
       "      <td>consolidated city</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>050</td>\n",
       "      <td>county</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>060</td>\n",
       "      <td>county subdivision</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>160</td>\n",
       "      <td>place</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>040</td>\n",
       "      <td>state</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>067</td>\n",
       "      <td>subminor civil subdivision</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  geoLevelId                        name\n",
       "0        170           consolidated city\n",
       "1        050                      county\n",
       "2        060          county subdivision\n",
       "3        160                       place\n",
       "4        040                       state\n",
       "5        067  subminor civil subdivision"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.geographies['gnis']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that some geographies in the `fips` system have a **required** filter to prevent drawing too much data. This will get passed to the `query` method later. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So, let's just grab the housing information from the 2010 Census Short Form. Using the variables table above, we picked out a subset of the fields we wanted. Since the variables table is indexed by the identifiers, we can grab the indexes of the filtered dataframe as query parameters. \n",
    "\n",
    "In addition, adding the `NAME` field smart-fills the table with the name of the geographic entity being pulled from the Census."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "cols = conn.varslike('H00[012]*', engine='fnmatch')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "cols.append('NAME')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['H00010001',\n",
       " 'H0020001',\n",
       " 'H0020002',\n",
       " 'H0020003',\n",
       " 'H0020004',\n",
       " 'H0020005',\n",
       " 'H0020006',\n",
       " 'NAME']"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cols"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now the query. The query is constructed just like the API query, and works as follows. \n",
    "\n",
    "1. cols - list of columns desired from the database, maps to census API's `get=`\n",
    "2. geo_unit - string denoting the unit of study to pull, maps to census API's `in=`\n",
    "3. geo_filter - dictionary containing groupings of geo_units, if required, maps to `for=`\n",
    "    \n",
    "To be specific, a fully query tells the server *what* columns to pull of *what* underlying geography from *what* aggregation units. It's structured using these heterogeneous datatypes so it's easy to change the smallest units quickly, while providing sufficient granularity to change the filters and columns as you go. \n",
    "\n",
    "This query below grabs the names, population, and housing estimates from the ACS, as well as their standard errors from census designated places in Arizona. \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/mnt/data/Dropbox/dev/cenpy/cenpy/remote.py:167: FutureWarning: convert_objects is deprecated.  Use the data-type specific converters pd.to_datetime, pd.to_timedelta and pd.to_numeric.\n",
      "  df[cols] = df[cols].convert_objects(convert_numeric=convert_numeric)\n"
     ]
    }
   ],
   "source": [
    "data = conn.query(cols, geo_unit = 'place:*', geo_filter = {'state':'04'})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Once constructed, the query executes as fast as your internet connection will move. This query has:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(451, 10)"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "28 columns and 451 rows. So, rather fast. \n",
    "\n",
    "For validity and ease of use, we store the last executed query to the object. If you're dodgy about your census API key never being shown in plaintext, never print this property!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'http://api.census.gov/data/2010/sf1?get=H00010001,H0020001,H0020002,H0020003,H0020004,H0020005,H0020006,NAME&for=place:*&in=state:04'"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.last_query"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So, you have a dataframe with the information requested, plus the fields specified in the `geo_filter` and `geo_unit`. \n",
    "\n",
    "So, the following is a dataframe of the data requested. I've filtered it to only look at data where the population is larger than 40 thousand people.\n",
    "\n",
    "Pretty neat!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>H00010001</th>\n",
       "      <th>H0020001</th>\n",
       "      <th>H0020002</th>\n",
       "      <th>H0020003</th>\n",
       "      <th>H0020004</th>\n",
       "      <th>H0020005</th>\n",
       "      <th>H0020006</th>\n",
       "      <th>NAME</th>\n",
       "      <th>state</th>\n",
       "      <th>place</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>63</th>\n",
       "      <td>94404</td>\n",
       "      <td>94404</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>94404</td>\n",
       "      <td>Chandler city</td>\n",
       "      <td>04</td>\n",
       "      <td>12000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146</th>\n",
       "      <td>74907</td>\n",
       "      <td>74907</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>74907</td>\n",
       "      <td>Gilbert town</td>\n",
       "      <td>04</td>\n",
       "      <td>27400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>148</th>\n",
       "      <td>90505</td>\n",
       "      <td>90505</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>90505</td>\n",
       "      <td>Glendale city</td>\n",
       "      <td>04</td>\n",
       "      <td>27820</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>224</th>\n",
       "      <td>201173</td>\n",
       "      <td>201173</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>201173</td>\n",
       "      <td>Mesa city</td>\n",
       "      <td>04</td>\n",
       "      <td>46000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>266</th>\n",
       "      <td>64818</td>\n",
       "      <td>64818</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>64818</td>\n",
       "      <td>Peoria city</td>\n",
       "      <td>04</td>\n",
       "      <td>54050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>268</th>\n",
       "      <td>590149</td>\n",
       "      <td>590149</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>590149</td>\n",
       "      <td>Phoenix city</td>\n",
       "      <td>04</td>\n",
       "      <td>55000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>328</th>\n",
       "      <td>124001</td>\n",
       "      <td>124001</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>124001</td>\n",
       "      <td>Scottsdale city</td>\n",
       "      <td>04</td>\n",
       "      <td>65000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>366</th>\n",
       "      <td>52586</td>\n",
       "      <td>52586</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>52586</td>\n",
       "      <td>Surprise city</td>\n",
       "      <td>04</td>\n",
       "      <td>71510</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>375</th>\n",
       "      <td>73462</td>\n",
       "      <td>73462</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>73462</td>\n",
       "      <td>Tempe city</td>\n",
       "      <td>04</td>\n",
       "      <td>73000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>394</th>\n",
       "      <td>229762</td>\n",
       "      <td>229762</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>229762</td>\n",
       "      <td>Tucson city</td>\n",
       "      <td>04</td>\n",
       "      <td>77000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     H00010001  H0020001  H0020002  H0020003  H0020004  H0020005  H0020006  \\\n",
       "63       94404     94404         0         0         0         0     94404   \n",
       "146      74907     74907         0         0         0         0     74907   \n",
       "148      90505     90505         0         0         0         0     90505   \n",
       "224     201173    201173         0         0         0         0    201173   \n",
       "266      64818     64818         0         0         0         0     64818   \n",
       "268     590149    590149         0         0         0         0    590149   \n",
       "328     124001    124001         0         0         0         0    124001   \n",
       "366      52586     52586         0         0         0         0     52586   \n",
       "375      73462     73462         0         0         0         0     73462   \n",
       "394     229762    229762         0         0         0         0    229762   \n",
       "\n",
       "                NAME state  place  \n",
       "63     Chandler city    04  12000  \n",
       "146     Gilbert town    04  27400  \n",
       "148    Glendale city    04  27820  \n",
       "224        Mesa city    04  46000  \n",
       "266      Peoria city    04  54050  \n",
       "268     Phoenix city    04  55000  \n",
       "328  Scottsdale city    04  65000  \n",
       "366    Surprise city    04  71510  \n",
       "375       Tempe city    04  73000  \n",
       "394      Tucson city    04  77000  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data[data['H00010001'] > 40000]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And, just in case you're liable to forget your FIPS codes, the explorer module can look up some fips codes listings for you."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "reading http://www2.census.gov/geo/docs/reference/codes/files/st04_az_places.txt\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>730</td>\n",
       "      <td>Aguila CDP</td>\n",
       "      <td>Census Designated Place</td>\n",
       "      <td>S</td>\n",
       "      <td>Maricopa County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>870</td>\n",
       "      <td>Ajo CDP</td>\n",
       "      <td>Census Designated Place</td>\n",
       "      <td>S</td>\n",
       "      <td>Pima County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>940</td>\n",
       "      <td>Ak Chin CDP</td>\n",
       "      <td>Census Designated Place</td>\n",
       "      <td>S</td>\n",
       "      <td>Pima County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>1090</td>\n",
       "      <td>Ak-Chin Village CDP</td>\n",
       "      <td>Census Designated Place</td>\n",
       "      <td>S</td>\n",
       "      <td>Pinal County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>1170</td>\n",
       "      <td>Alamo Lake CDP</td>\n",
       "      <td>Census Designated Place</td>\n",
       "      <td>S</td>\n",
       "      <td>La Paz County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>1560</td>\n",
       "      <td>Ali Chuk CDP</td>\n",
       "      <td>Census Designated Place</td>\n",
       "      <td>S</td>\n",
       "      <td>Pima County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>1570</td>\n",
       "      <td>Ali Chukson CDP</td>\n",
       "      <td>Census Designated Place</td>\n",
       "      <td>S</td>\n",
       "      <td>Pima County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>1620</td>\n",
       "      <td>Ali Molina CDP</td>\n",
       "      <td>Census Designated Place</td>\n",
       "      <td>S</td>\n",
       "      <td>Pima County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>1920</td>\n",
       "      <td>Alpine CDP</td>\n",
       "      <td>Census Designated Place</td>\n",
       "      <td>S</td>\n",
       "      <td>Apache County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>1990</td>\n",
       "      <td>Amado CDP</td>\n",
       "      <td>Census Designated Place</td>\n",
       "      <td>S</td>\n",
       "      <td>Santa Cruz County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>2270</td>\n",
       "      <td>Anegam CDP</td>\n",
       "      <td>Census Designated Place</td>\n",
       "      <td>S</td>\n",
       "      <td>Pima County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>2410</td>\n",
       "      <td>Antares CDP</td>\n",
       "      <td>Census Designated Place</td>\n",
       "      <td>S</td>\n",
       "      <td>Mohave County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>2430</td>\n",
       "      <td>Anthem CDP</td>\n",
       "      <td>Census Designated Place</td>\n",
       "      <td>S</td>\n",
       "      <td>Maricopa County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>2830</td>\n",
       "      <td>Apache Junction city</td>\n",
       "      <td>Incorporated Place</td>\n",
       "      <td>A</td>\n",
       "      <td>Maricopa County, Pinal County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>3320</td>\n",
       "      <td>Arivaca CDP</td>\n",
       "      <td>Census Designated Place</td>\n",
       "      <td>S</td>\n",
       "      <td>Pima County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>3380</td>\n",
       "      <td>Arivaca Junction CDP</td>\n",
       "      <td>Census Designated Place</td>\n",
       "      <td>S</td>\n",
       "      <td>Pima County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>3530</td>\n",
       "      <td>Arizona City CDP</td>\n",
       "      <td>Census Designated Place</td>\n",
       "      <td>S</td>\n",
       "      <td>Pinal County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>3915</td>\n",
       "      <td>Arizona Village CDP</td>\n",
       "      <td>Census Designated Place</td>\n",
       "      <td>S</td>\n",
       "      <td>Mohave County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>4020</td>\n",
       "      <td>Arlington CDP</td>\n",
       "      <td>Census Designated Place</td>\n",
       "      <td>S</td>\n",
       "      <td>Maricopa County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>4440</td>\n",
       "      <td>Ash Fork CDP</td>\n",
       "      <td>Census Designated Place</td>\n",
       "      <td>S</td>\n",
       "      <td>Yavapai County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>4710</td>\n",
       "      <td>Avenue B and C CDP</td>\n",
       "      <td>Census Designated Place</td>\n",
       "      <td>S</td>\n",
       "      <td>Yuma County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>4720</td>\n",
       "      <td>Avondale city</td>\n",
       "      <td>Incorporated Place</td>\n",
       "      <td>A</td>\n",
       "      <td>Maricopa County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>4880</td>\n",
       "      <td>Avra Valley CDP</td>\n",
       "      <td>Census Designated Place</td>\n",
       "      <td>S</td>\n",
       "      <td>Pima County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>4930</td>\n",
       "      <td>Aztec CDP</td>\n",
       "      <td>Census Designated Place</td>\n",
       "      <td>S</td>\n",
       "      <td>Yuma County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>5140</td>\n",
       "      <td>Bagdad CDP</td>\n",
       "      <td>Census Designated Place</td>\n",
       "      <td>S</td>\n",
       "      <td>Yavapai County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>5450</td>\n",
       "      <td>Bear Flat CDP</td>\n",
       "      <td>Census Designated Place</td>\n",
       "      <td>S</td>\n",
       "      <td>Gila County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>5490</td>\n",
       "      <td>Beaver Dam CDP</td>\n",
       "      <td>Census Designated Place</td>\n",
       "      <td>S</td>\n",
       "      <td>Mohave County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>5495</td>\n",
       "      <td>Beaver Valley CDP</td>\n",
       "      <td>Census Designated Place</td>\n",
       "      <td>S</td>\n",
       "      <td>Gila County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>5770</td>\n",
       "      <td>Benson city</td>\n",
       "      <td>Incorporated Place</td>\n",
       "      <td>A</td>\n",
       "      <td>Cochise County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>5970</td>\n",
       "      <td>Beyerville CDP</td>\n",
       "      <td>Census Designated Place</td>\n",
       "      <td>S</td>\n",
       "      <td>Santa Cruz County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>421</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>82120</td>\n",
       "      <td>Wheatfields CDP</td>\n",
       "      <td>Census Designated Place</td>\n",
       "      <td>S</td>\n",
       "      <td>Gila County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>422</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>82155</td>\n",
       "      <td>Whetstone CDP</td>\n",
       "      <td>Census Designated Place</td>\n",
       "      <td>S</td>\n",
       "      <td>Cochise County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>423</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>82270</td>\n",
       "      <td>Whispering Pines CDP</td>\n",
       "      <td>Census Designated Place</td>\n",
       "      <td>S</td>\n",
       "      <td>Gila County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>424</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>82390</td>\n",
       "      <td>Whitecone CDP</td>\n",
       "      <td>Census Designated Place</td>\n",
       "      <td>S</td>\n",
       "      <td>Navajo County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>425</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>82425</td>\n",
       "      <td>White Hills CDP</td>\n",
       "      <td>Census Designated Place</td>\n",
       "      <td>S</td>\n",
       "      <td>Mohave County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>426</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>82450</td>\n",
       "      <td>White Mountain Lake CDP</td>\n",
       "      <td>Census Designated Place</td>\n",
       "      <td>S</td>\n",
       "      <td>Navajo County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>427</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>82530</td>\n",
       "      <td>Whiteriver CDP</td>\n",
       "      <td>Census Designated Place</td>\n",
       "      <td>S</td>\n",
       "      <td>Navajo County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>428</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>82660</td>\n",
       "      <td>Why CDP</td>\n",
       "      <td>Census Designated Place</td>\n",
       "      <td>S</td>\n",
       "      <td>Pima County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>429</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>82740</td>\n",
       "      <td>Wickenburg town</td>\n",
       "      <td>Incorporated Place</td>\n",
       "      <td>A</td>\n",
       "      <td>Maricopa County, Yavapai County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>430</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>82810</td>\n",
       "      <td>Wide Ruins CDP</td>\n",
       "      <td>Census Designated Place</td>\n",
       "      <td>S</td>\n",
       "      <td>Apache County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>431</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>82880</td>\n",
       "      <td>Wikieup CDP</td>\n",
       "      <td>Census Designated Place</td>\n",
       "      <td>S</td>\n",
       "      <td>Mohave County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>432</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>82950</td>\n",
       "      <td>Wilhoit CDP</td>\n",
       "      <td>Census Designated Place</td>\n",
       "      <td>S</td>\n",
       "      <td>Yavapai County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>433</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>83090</td>\n",
       "      <td>Willcox city</td>\n",
       "      <td>Incorporated Place</td>\n",
       "      <td>A</td>\n",
       "      <td>Cochise County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>434</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>83160</td>\n",
       "      <td>Williams city</td>\n",
       "      <td>Incorporated Place</td>\n",
       "      <td>A</td>\n",
       "      <td>Coconino County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>435</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>83388</td>\n",
       "      <td>Williamson CDP</td>\n",
       "      <td>Census Designated Place</td>\n",
       "      <td>S</td>\n",
       "      <td>Yavapai County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>436</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>83475</td>\n",
       "      <td>Willow Canyon CDP</td>\n",
       "      <td>Census Designated Place</td>\n",
       "      <td>S</td>\n",
       "      <td>Pima County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>437</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>83570</td>\n",
       "      <td>Willow Valley CDP</td>\n",
       "      <td>Census Designated Place</td>\n",
       "      <td>S</td>\n",
       "      <td>Mohave County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>438</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>83720</td>\n",
       "      <td>Window Rock CDP</td>\n",
       "      <td>Census Designated Place</td>\n",
       "      <td>S</td>\n",
       "      <td>Apache County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>439</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>83790</td>\n",
       "      <td>Winkelman town</td>\n",
       "      <td>Incorporated Place</td>\n",
       "      <td>A</td>\n",
       "      <td>Gila County, Pinal County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>440</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>83930</td>\n",
       "      <td>Winslow city</td>\n",
       "      <td>Incorporated Place</td>\n",
       "      <td>A</td>\n",
       "      <td>Navajo County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>441</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>83960</td>\n",
       "      <td>Winslow West CDP</td>\n",
       "      <td>Census Designated Place</td>\n",
       "      <td>S</td>\n",
       "      <td>Coconino County, Navajo County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>442</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>84000</td>\n",
       "      <td>Wintersburg CDP</td>\n",
       "      <td>Census Designated Place</td>\n",
       "      <td>S</td>\n",
       "      <td>Maricopa County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>443</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>84140</td>\n",
       "      <td>Wittmann CDP</td>\n",
       "      <td>Census Designated Place</td>\n",
       "      <td>S</td>\n",
       "      <td>Maricopa County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>444</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>84350</td>\n",
       "      <td>Woodruff CDP</td>\n",
       "      <td>Census Designated Place</td>\n",
       "      <td>S</td>\n",
       "      <td>Navajo County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>445</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>84980</td>\n",
       "      <td>Yarnell CDP</td>\n",
       "      <td>Census Designated Place</td>\n",
       "      <td>S</td>\n",
       "      <td>Yavapai County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>446</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>85260</td>\n",
       "      <td>York CDP</td>\n",
       "      <td>Census Designated Place</td>\n",
       "      <td>S</td>\n",
       "      <td>Greenlee County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>447</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>85330</td>\n",
       "      <td>Young CDP</td>\n",
       "      <td>Census Designated Place</td>\n",
       "      <td>S</td>\n",
       "      <td>Gila County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>448</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>85400</td>\n",
       "      <td>Youngtown town</td>\n",
       "      <td>Incorporated Place</td>\n",
       "      <td>A</td>\n",
       "      <td>Maricopa County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>449</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>85470</td>\n",
       "      <td>Yucca CDP</td>\n",
       "      <td>Census Designated Place</td>\n",
       "      <td>S</td>\n",
       "      <td>Mohave County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>450</th>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>85540</td>\n",
       "      <td>Yuma city</td>\n",
       "      <td>Incorporated Place</td>\n",
       "      <td>A</td>\n",
       "      <td>Yuma County</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>451 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      0  1      2                        3                        4  5  \\\n",
       "0    AZ  4    730               Aguila CDP  Census Designated Place  S   \n",
       "1    AZ  4    870                  Ajo CDP  Census Designated Place  S   \n",
       "2    AZ  4    940              Ak Chin CDP  Census Designated Place  S   \n",
       "3    AZ  4   1090      Ak-Chin Village CDP  Census Designated Place  S   \n",
       "4    AZ  4   1170           Alamo Lake CDP  Census Designated Place  S   \n",
       "5    AZ  4   1560             Ali Chuk CDP  Census Designated Place  S   \n",
       "6    AZ  4   1570          Ali Chukson CDP  Census Designated Place  S   \n",
       "7    AZ  4   1620           Ali Molina CDP  Census Designated Place  S   \n",
       "8    AZ  4   1920               Alpine CDP  Census Designated Place  S   \n",
       "9    AZ  4   1990                Amado CDP  Census Designated Place  S   \n",
       "10   AZ  4   2270               Anegam CDP  Census Designated Place  S   \n",
       "11   AZ  4   2410              Antares CDP  Census Designated Place  S   \n",
       "12   AZ  4   2430               Anthem CDP  Census Designated Place  S   \n",
       "13   AZ  4   2830     Apache Junction city       Incorporated Place  A   \n",
       "14   AZ  4   3320              Arivaca CDP  Census Designated Place  S   \n",
       "15   AZ  4   3380     Arivaca Junction CDP  Census Designated Place  S   \n",
       "16   AZ  4   3530         Arizona City CDP  Census Designated Place  S   \n",
       "17   AZ  4   3915      Arizona Village CDP  Census Designated Place  S   \n",
       "18   AZ  4   4020            Arlington CDP  Census Designated Place  S   \n",
       "19   AZ  4   4440             Ash Fork CDP  Census Designated Place  S   \n",
       "20   AZ  4   4710       Avenue B and C CDP  Census Designated Place  S   \n",
       "21   AZ  4   4720            Avondale city       Incorporated Place  A   \n",
       "22   AZ  4   4880          Avra Valley CDP  Census Designated Place  S   \n",
       "23   AZ  4   4930                Aztec CDP  Census Designated Place  S   \n",
       "24   AZ  4   5140               Bagdad CDP  Census Designated Place  S   \n",
       "25   AZ  4   5450            Bear Flat CDP  Census Designated Place  S   \n",
       "26   AZ  4   5490           Beaver Dam CDP  Census Designated Place  S   \n",
       "27   AZ  4   5495        Beaver Valley CDP  Census Designated Place  S   \n",
       "28   AZ  4   5770              Benson city       Incorporated Place  A   \n",
       "29   AZ  4   5970           Beyerville CDP  Census Designated Place  S   \n",
       "..   .. ..    ...                      ...                      ... ..   \n",
       "421  AZ  4  82120          Wheatfields CDP  Census Designated Place  S   \n",
       "422  AZ  4  82155            Whetstone CDP  Census Designated Place  S   \n",
       "423  AZ  4  82270     Whispering Pines CDP  Census Designated Place  S   \n",
       "424  AZ  4  82390            Whitecone CDP  Census Designated Place  S   \n",
       "425  AZ  4  82425          White Hills CDP  Census Designated Place  S   \n",
       "426  AZ  4  82450  White Mountain Lake CDP  Census Designated Place  S   \n",
       "427  AZ  4  82530           Whiteriver CDP  Census Designated Place  S   \n",
       "428  AZ  4  82660                  Why CDP  Census Designated Place  S   \n",
       "429  AZ  4  82740          Wickenburg town       Incorporated Place  A   \n",
       "430  AZ  4  82810           Wide Ruins CDP  Census Designated Place  S   \n",
       "431  AZ  4  82880              Wikieup CDP  Census Designated Place  S   \n",
       "432  AZ  4  82950              Wilhoit CDP  Census Designated Place  S   \n",
       "433  AZ  4  83090             Willcox city       Incorporated Place  A   \n",
       "434  AZ  4  83160            Williams city       Incorporated Place  A   \n",
       "435  AZ  4  83388           Williamson CDP  Census Designated Place  S   \n",
       "436  AZ  4  83475        Willow Canyon CDP  Census Designated Place  S   \n",
       "437  AZ  4  83570        Willow Valley CDP  Census Designated Place  S   \n",
       "438  AZ  4  83720          Window Rock CDP  Census Designated Place  S   \n",
       "439  AZ  4  83790           Winkelman town       Incorporated Place  A   \n",
       "440  AZ  4  83930             Winslow city       Incorporated Place  A   \n",
       "441  AZ  4  83960         Winslow West CDP  Census Designated Place  S   \n",
       "442  AZ  4  84000          Wintersburg CDP  Census Designated Place  S   \n",
       "443  AZ  4  84140             Wittmann CDP  Census Designated Place  S   \n",
       "444  AZ  4  84350             Woodruff CDP  Census Designated Place  S   \n",
       "445  AZ  4  84980              Yarnell CDP  Census Designated Place  S   \n",
       "446  AZ  4  85260                 York CDP  Census Designated Place  S   \n",
       "447  AZ  4  85330                Young CDP  Census Designated Place  S   \n",
       "448  AZ  4  85400           Youngtown town       Incorporated Place  A   \n",
       "449  AZ  4  85470                Yucca CDP  Census Designated Place  S   \n",
       "450  AZ  4  85540                Yuma city       Incorporated Place  A   \n",
       "\n",
       "                                   6  \n",
       "0                    Maricopa County  \n",
       "1                        Pima County  \n",
       "2                        Pima County  \n",
       "3                       Pinal County  \n",
       "4                      La Paz County  \n",
       "5                        Pima County  \n",
       "6                        Pima County  \n",
       "7                        Pima County  \n",
       "8                      Apache County  \n",
       "9                  Santa Cruz County  \n",
       "10                       Pima County  \n",
       "11                     Mohave County  \n",
       "12                   Maricopa County  \n",
       "13     Maricopa County, Pinal County  \n",
       "14                       Pima County  \n",
       "15                       Pima County  \n",
       "16                      Pinal County  \n",
       "17                     Mohave County  \n",
       "18                   Maricopa County  \n",
       "19                    Yavapai County  \n",
       "20                       Yuma County  \n",
       "21                   Maricopa County  \n",
       "22                       Pima County  \n",
       "23                       Yuma County  \n",
       "24                    Yavapai County  \n",
       "25                       Gila County  \n",
       "26                     Mohave County  \n",
       "27                       Gila County  \n",
       "28                    Cochise County  \n",
       "29                 Santa Cruz County  \n",
       "..                               ...  \n",
       "421                      Gila County  \n",
       "422                   Cochise County  \n",
       "423                      Gila County  \n",
       "424                    Navajo County  \n",
       "425                    Mohave County  \n",
       "426                    Navajo County  \n",
       "427                    Navajo County  \n",
       "428                      Pima County  \n",
       "429  Maricopa County, Yavapai County  \n",
       "430                    Apache County  \n",
       "431                    Mohave County  \n",
       "432                   Yavapai County  \n",
       "433                   Cochise County  \n",
       "434                  Coconino County  \n",
       "435                   Yavapai County  \n",
       "436                      Pima County  \n",
       "437                    Mohave County  \n",
       "438                    Apache County  \n",
       "439        Gila County, Pinal County  \n",
       "440                    Navajo County  \n",
       "441   Coconino County, Navajo County  \n",
       "442                  Maricopa County  \n",
       "443                  Maricopa County  \n",
       "444                    Navajo County  \n",
       "445                   Yavapai County  \n",
       "446                  Greenlee County  \n",
       "447                      Gila County  \n",
       "448                  Maricopa County  \n",
       "449                    Mohave County  \n",
       "450                      Yuma County  \n",
       "\n",
       "[451 rows x 7 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c.explorer.fips_table('place', in_state='AZ')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### GEO & Tiger Integration"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The Census TIGER geometry API is substantively different from every other API, in that it's an ArcGIS REST API. But, I've tried to expose a consistent interface. It works like this:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import cenpy.tiger as tiger"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'name': 'AIANNHA', 'type': 'MapServer'},\n",
       " {'name': 'CBSA', 'type': 'MapServer'},\n",
       " {'name': 'Hydro_LargeScale', 'type': 'MapServer'},\n",
       " {'name': 'Hydro', 'type': 'MapServer'},\n",
       " {'name': 'Labels', 'type': 'MapServer'},\n",
       " {'name': 'Legislative', 'type': 'MapServer'},\n",
       " {'name': 'Places_CouSub_ConCity_SubMCD', 'type': 'MapServer'},\n",
       " {'name': 'PUMA_TAD_TAZ_UGA_ZCTA', 'type': 'MapServer'},\n",
       " {'name': 'Region_Division', 'type': 'MapServer'},\n",
       " {'name': 'School', 'type': 'MapServer'},\n",
       " {'name': 'Special_Land_Use_Areas', 'type': 'MapServer'},\n",
       " {'name': 'State_County', 'type': 'MapServer'},\n",
       " {'name': 'tigerWMS_ACS2013', 'type': 'MapServer'},\n",
       " {'name': 'tigerWMS_ACS2014', 'type': 'MapServer'},\n",
       " {'name': 'tigerWMS_ACS2015', 'type': 'MapServer'},\n",
       " {'name': 'tigerWMS_ACS2016', 'type': 'MapServer'},\n",
       " {'name': 'tigerWMS_Census2010', 'type': 'MapServer'},\n",
       " {'name': 'tigerWMS_Current', 'type': 'MapServer'},\n",
       " {'name': 'tigerWMS_ECON2012', 'type': 'MapServer'},\n",
       " {'name': 'tigerWMS_PhysicalFeatures', 'type': 'MapServer'},\n",
       " {'name': 'Tracts_Blocks', 'type': 'MapServer'},\n",
       " {'name': 'Transportation_LargeScale', 'type': 'MapServer'},\n",
       " {'name': 'Transportation', 'type': 'MapServer'},\n",
       " {'name': 'TribalTracts', 'type': 'MapServer'},\n",
       " {'name': 'Urban', 'type': 'MapServer'},\n",
       " {'name': 'USLandmass', 'type': 'MapServer'}]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tiger.available()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In some cases, it makes quite a bit of sense to \"attach\" a map server to your connection. In the case of the US Census 2010 we've been using, there is an obvious data product match in `tigerWMS_Census2010`. So, let's attach it to the connection."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "conn.set_mapservice('tigerWMS_Census2010')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<cenpy.tiger.TigerConnection at 0x7fdb464bbfd0>"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.mapservice"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "neat! this is the same as calling: \n",
    "\n",
    "`tiger.TigerConnection('tigerWMS_Census2010')`\n",
    "\n",
    "but this attaches that object it to the connection you've been using. The connection also updates with this information:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Connection to 2010 Decennial: Summary File 1(ID: http://api.census.gov/data/id/DecennialSF12010)\n",
       "With MapServer: Census 2010 WMS"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "An ESRI MapServer is a big thing, and `cenpy` doesn't support all of its features. Since `cenpy` is designed to support retreival of data from the US Census, we only support `GET` statements for defined geographic units, and ignore the vaious other functionalities in the service. \n",
    "\n",
    "To work with a service, note that any map server is composed of layers:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{0: (ESRILayer) Public Use Microdata Areas,\n",
       " 1: (ESRILayer) Public Use Microdata Areas Labels,\n",
       " 2: (ESRILayer) Traffic Analysis Districts,\n",
       " 3: (ESRILayer) Traffic Analysis Districts Labels,\n",
       " 4: (ESRILayer) Traffic Analysis Zones,\n",
       " 5: (ESRILayer) Traffic Analysis Zones Labels,\n",
       " 6: (ESRILayer) Urban Growth Areas,\n",
       " 7: (ESRILayer) Urban Growth Areas Labels,\n",
       " 8: (ESRILayer) ZIP Code Tabulation Areas,\n",
       " 9: (ESRILayer) ZIP Code Tabulation Areas Labels,\n",
       " 10: (ESRILayer) Tribal Census Tracts,\n",
       " 11: (ESRILayer) Tribal Census Tracts Labels,\n",
       " 12: (ESRILayer) Tribal Block Groups,\n",
       " 13: (ESRILayer) Tribal Block Groups Labels,\n",
       " 14: (ESRILayer) Census Tracts,\n",
       " 15: (ESRILayer) Census Tracts Labels,\n",
       " 16: (ESRILayer) Census Block Groups,\n",
       " 17: (ESRILayer) Census Block Groups Labels,\n",
       " 18: (ESRILayer) Census Blocks,\n",
       " 19: (ESRILayer) Census Blocks Labels,\n",
       " 20: (ESRILayer) Unified School Districts,\n",
       " 21: (ESRILayer) Unified School Districts Labels,\n",
       " 22: (ESRILayer) Secondary School Districts,\n",
       " 23: (ESRILayer) Secondary School Districts Labels,\n",
       " 24: (ESRILayer) Elementary School Districts,\n",
       " 25: (ESRILayer) Elementary School Districts Labels,\n",
       " 26: (ESRILayer) Estates,\n",
       " 27: (ESRILayer) Estates Labels,\n",
       " 28: (ESRILayer) County Subdivisions,\n",
       " 29: (ESRILayer) County Subdivisions Labels,\n",
       " 30: (ESRILayer) Subbarrios,\n",
       " 31: (ESRILayer) Subbarrios Labels,\n",
       " 32: (ESRILayer) Consolidated Cities,\n",
       " 33: (ESRILayer) Consolidated Cities Labels,\n",
       " 34: (ESRILayer) Incorporated Places,\n",
       " 35: (ESRILayer) Incorporated Places Labels,\n",
       " 36: (ESRILayer) Census Designated Places,\n",
       " 37: (ESRILayer) Census Designated Places Labels,\n",
       " 38: (ESRILayer) Alaska Native Regional Corporations,\n",
       " 39: (ESRILayer) Alaska Native Regional Corporations Labels,\n",
       " 40: (ESRILayer) Tribal Subdivisions,\n",
       " 41: (ESRILayer) Tribal Subdivisions Labels,\n",
       " 42: (ESRILayer) Federal American Indian Reservations,\n",
       " 43: (ESRILayer) Federal American Indian Reservations Labels,\n",
       " 44: (ESRILayer) Off-Reservation Trust Lands,\n",
       " 45: (ESRILayer) Off-Reservation Trust Lands Labels,\n",
       " 46: (ESRILayer) State American Indian Reservations,\n",
       " 47: (ESRILayer) State American Indian Reservations Labels,\n",
       " 48: (ESRILayer) Hawaiian Home Lands,\n",
       " 49: (ESRILayer) Hawaiian Home Lands Labels,\n",
       " 50: (ESRILayer) Alaska Native Village Statistical Areas,\n",
       " 51: (ESRILayer) Alaska Native Village Statistical Areas Labels,\n",
       " 52: (ESRILayer) Oklahoma Tribal Statistical Areas,\n",
       " 53: (ESRILayer) Oklahoma Tribal Statistical Areas Labels,\n",
       " 54: (ESRILayer) State Designated Tribal Statistical Areas,\n",
       " 55: (ESRILayer) State Designated Tribal Statistical Areas Labels,\n",
       " 56: (ESRILayer) Tribal Designated Statistical Areas,\n",
       " 57: (ESRILayer) Tribal Designated Statistical Areas Labels,\n",
       " 58: (ESRILayer) American Indian Joint-Use Areas,\n",
       " 59: (ESRILayer) American Indian Joint-Use Areas Labels,\n",
       " 60: (ESRILayer) 113th Congressional Districts,\n",
       " 61: (ESRILayer) 113th Congressional Districts Labels,\n",
       " 62: (ESRILayer) 111th Congressional Districts,\n",
       " 63: (ESRILayer) 111th Congressional Districts Labels,\n",
       " 64: (ESRILayer) 2013 State Legislative Districts - Upper,\n",
       " 65: (ESRILayer) 2013 State Legislative Districts - Upper Labels,\n",
       " 66: (ESRILayer) 2013 State Legislative Districts - Lower,\n",
       " 67: (ESRILayer) 2013 State Legislative Districts - Lower Labels,\n",
       " 68: (ESRILayer) 2010 State Legislative Districts - Upper,\n",
       " 69: (ESRILayer) 2010 State Legislative Districts - Upper Labels,\n",
       " 70: (ESRILayer) 2010 State Legislative Districts - Lower,\n",
       " 71: (ESRILayer) 2010 State Legislative Districts - Lower Labels,\n",
       " 72: (ESRILayer) Voting Districts,\n",
       " 73: (ESRILayer) Voting Districts Labels,\n",
       " 74: (ESRILayer) Census Divisions,\n",
       " 75: (ESRILayer) Census Divisions Labels,\n",
       " 76: (ESRILayer) Census Regions,\n",
       " 77: (ESRILayer) Census Regions Labels,\n",
       " 78: (ESRILayer) Urbanized Areas,\n",
       " 79: (ESRILayer) Urbanized Areas Labels,\n",
       " 80: (ESRILayer) Urban Clusters,\n",
       " 81: (ESRILayer) Urban Clusters Labels,\n",
       " 82: (ESRILayer) Combined New England City and Town Areas,\n",
       " 83: (ESRILayer) Combined New England City and Town Areas Labels,\n",
       " 84: (ESRILayer) New England City and Town Area Divisions,\n",
       " 85: (ESRILayer) New England City and Town Area  Divisions Labels,\n",
       " 86: (ESRILayer) Metropolitan New England City and Town Areas,\n",
       " 87: (ESRILayer) Metropolitan New England City and Town Areas Labels,\n",
       " 88: (ESRILayer) Micropolitan New England City and Town Areas,\n",
       " 89: (ESRILayer) Micropolitan New England City and Town Areas Labels,\n",
       " 90: (ESRILayer) Combined Statistical Areas,\n",
       " 91: (ESRILayer) Combined Statistical Areas Labels,\n",
       " 92: (ESRILayer) Metropolitan Divisions,\n",
       " 93: (ESRILayer) Metropolitan Divisions Labels,\n",
       " 94: (ESRILayer) Metropolitan Statistical Areas,\n",
       " 95: (ESRILayer) Metropolitan Statistical Areas Labels,\n",
       " 96: (ESRILayer) Micropolitan Statistical Areas,\n",
       " 97: (ESRILayer) Micropolitan Statistical Areas Labels,\n",
       " 98: (ESRILayer) States,\n",
       " 99: (ESRILayer) States Labels,\n",
       " 100: (ESRILayer) Counties,\n",
       " 101: (ESRILayer) Counties Labels}"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.mapservice.layers"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "These layers are what actually implement query operations. For now, let's focus on the same \"class\" of units we were using before, Census Designated Places:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(ESRILayer) Census Designated Places"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.mapservice.layers[36]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A query function is implemented both at the mapservice level and the layer level. At the mapservice level, a layer ID is required in order to complete the query. \n",
    "\n",
    "Mapservice queries are driven by SQL. So, to grab all of the geodata that fits the CDPs we pulled before, you could start to construct it like this. \n",
    "\n",
    "First, just like the main connection, each layer has a set of variables: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>alias</th>\n",
       "      <th>domain</th>\n",
       "      <th>length</th>\n",
       "      <th>name</th>\n",
       "      <th>type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>MTFCC</td>\n",
       "      <td>None</td>\n",
       "      <td>5.0</td>\n",
       "      <td>MTFCC</td>\n",
       "      <td>esriFieldTypeString</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>OID</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>OID</td>\n",
       "      <td>esriFieldTypeDouble</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>GEOID</td>\n",
       "      <td>None</td>\n",
       "      <td>7.0</td>\n",
       "      <td>GEOID</td>\n",
       "      <td>esriFieldTypeString</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>STATE</td>\n",
       "      <td>None</td>\n",
       "      <td>2.0</td>\n",
       "      <td>STATE</td>\n",
       "      <td>esriFieldTypeString</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>PLACE</td>\n",
       "      <td>None</td>\n",
       "      <td>5.0</td>\n",
       "      <td>PLACE</td>\n",
       "      <td>esriFieldTypeString</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>BASENAME</td>\n",
       "      <td>None</td>\n",
       "      <td>100.0</td>\n",
       "      <td>BASENAME</td>\n",
       "      <td>esriFieldTypeString</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>NAME</td>\n",
       "      <td>None</td>\n",
       "      <td>100.0</td>\n",
       "      <td>NAME</td>\n",
       "      <td>esriFieldTypeString</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>LSADC</td>\n",
       "      <td>None</td>\n",
       "      <td>2.0</td>\n",
       "      <td>LSADC</td>\n",
       "      <td>esriFieldTypeString</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>FUNCSTAT</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>FUNCSTAT</td>\n",
       "      <td>esriFieldTypeString</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>PLACECC</td>\n",
       "      <td>None</td>\n",
       "      <td>2.0</td>\n",
       "      <td>PLACECC</td>\n",
       "      <td>esriFieldTypeString</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>AREALAND</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>AREALAND</td>\n",
       "      <td>esriFieldTypeDouble</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>AREAWATER</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>AREAWATER</td>\n",
       "      <td>esriFieldTypeDouble</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>UR</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>UR</td>\n",
       "      <td>esriFieldTypeString</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>CBSAPCI</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>CBSAPCI</td>\n",
       "      <td>esriFieldTypeString</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>NECTAPCI</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NECTAPCI</td>\n",
       "      <td>esriFieldTypeString</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>STGEOMETRY</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>STGEOMETRY</td>\n",
       "      <td>esriFieldTypeGeometry</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>CENTLAT</td>\n",
       "      <td>None</td>\n",
       "      <td>11.0</td>\n",
       "      <td>CENTLAT</td>\n",
       "      <td>esriFieldTypeString</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>CENTLON</td>\n",
       "      <td>None</td>\n",
       "      <td>12.0</td>\n",
       "      <td>CENTLON</td>\n",
       "      <td>esriFieldTypeString</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>INTPTLAT</td>\n",
       "      <td>None</td>\n",
       "      <td>11.0</td>\n",
       "      <td>INTPTLAT</td>\n",
       "      <td>esriFieldTypeString</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>INTPTLON</td>\n",
       "      <td>None</td>\n",
       "      <td>12.0</td>\n",
       "      <td>INTPTLON</td>\n",
       "      <td>esriFieldTypeString</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>PLACENS</td>\n",
       "      <td>None</td>\n",
       "      <td>8.0</td>\n",
       "      <td>PLACENS</td>\n",
       "      <td>esriFieldTypeString</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>HU100</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>HU100</td>\n",
       "      <td>esriFieldTypeDouble</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>POP100</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>POP100</td>\n",
       "      <td>esriFieldTypeDouble</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>OBJECTID</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>OBJECTID</td>\n",
       "      <td>esriFieldTypeOID</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         alias domain  length        name                   type\n",
       "0        MTFCC   None     5.0       MTFCC    esriFieldTypeString\n",
       "1          OID   None     NaN         OID    esriFieldTypeDouble\n",
       "2        GEOID   None     7.0       GEOID    esriFieldTypeString\n",
       "3        STATE   None     2.0       STATE    esriFieldTypeString\n",
       "4        PLACE   None     5.0       PLACE    esriFieldTypeString\n",
       "5     BASENAME   None   100.0    BASENAME    esriFieldTypeString\n",
       "6         NAME   None   100.0        NAME    esriFieldTypeString\n",
       "7        LSADC   None     2.0       LSADC    esriFieldTypeString\n",
       "8     FUNCSTAT   None     1.0    FUNCSTAT    esriFieldTypeString\n",
       "9      PLACECC   None     2.0     PLACECC    esriFieldTypeString\n",
       "10    AREALAND   None     NaN    AREALAND    esriFieldTypeDouble\n",
       "11   AREAWATER   None     NaN   AREAWATER    esriFieldTypeDouble\n",
       "12          UR   None     1.0          UR    esriFieldTypeString\n",
       "13     CBSAPCI   None     1.0     CBSAPCI    esriFieldTypeString\n",
       "14    NECTAPCI   None     1.0    NECTAPCI    esriFieldTypeString\n",
       "15  STGEOMETRY   None     NaN  STGEOMETRY  esriFieldTypeGeometry\n",
       "16     CENTLAT   None    11.0     CENTLAT    esriFieldTypeString\n",
       "17     CENTLON   None    12.0     CENTLON    esriFieldTypeString\n",
       "18    INTPTLAT   None    11.0    INTPTLAT    esriFieldTypeString\n",
       "19    INTPTLON   None    12.0    INTPTLON    esriFieldTypeString\n",
       "20     PLACENS   None     8.0     PLACENS    esriFieldTypeString\n",
       "21       HU100   None     NaN       HU100    esriFieldTypeDouble\n",
       "22      POP100   None     NaN      POP100    esriFieldTypeDouble\n",
       "23    OBJECTID   None     NaN    OBJECTID       esriFieldTypeOID"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.mapservice.layers[36].variables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Our prior query grabbed the places in AZ. So, we could use a SQL query that focuses on that. \n",
    "\n",
    "I try to pack the geometries into containers that people are used to using. Without knowing if GEOS is installed on a user's computer, I use `PySAL` as the target geometry type. \n",
    "\n",
    "If you do have GEOS, that means you can use Shapely or GeoPandas. So, to choose your backend, you can use the following two arguments to this query function. the `pkg` argument will let you choose the three types of python objects to output to. \n",
    "\n",
    "Pysal is default. If you select Shapely, the result will just be a pandas dataframe with Shapely geometries instead of pysal geometries. If you choose geopandas (or throw a gpize) option, cenpy will try to convert the pandas dataframe into a GeoPandas dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "geodata = conn.mapservice.query(layer=36, where='STATE = 04')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>AREALAND</th>\n",
       "      <th>AREAWATER</th>\n",
       "      <th>BASENAME</th>\n",
       "      <th>CBSAPCI</th>\n",
       "      <th>CENTLAT</th>\n",
       "      <th>CENTLON</th>\n",
       "      <th>FUNCSTAT</th>\n",
       "      <th>GEOID</th>\n",
       "      <th>HU100</th>\n",
       "      <th>INTPTLAT</th>\n",
       "      <th>...</th>\n",
       "      <th>NECTAPCI</th>\n",
       "      <th>OBJECTID</th>\n",
       "      <th>OID</th>\n",
       "      <th>PLACE</th>\n",
       "      <th>PLACECC</th>\n",
       "      <th>PLACENS</th>\n",
       "      <th>POP100</th>\n",
       "      <th>STATE</th>\n",
       "      <th>UR</th>\n",
       "      <th>geometry</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>13352095</td>\n",
       "      <td>0</td>\n",
       "      <td>Topawa</td>\n",
       "      <td>N</td>\n",
       "      <td>+31.8078220</td>\n",
       "      <td>-111.8304860</td>\n",
       "      <td>S</td>\n",
       "      <td>0474680</td>\n",
       "      <td>135</td>\n",
       "      <td>+31.8078220</td>\n",
       "      <td>...</td>\n",
       "      <td>N</td>\n",
       "      <td>19866</td>\n",
       "      <td>280403717476697</td>\n",
       "      <td>74680</td>\n",
       "      <td>U1</td>\n",
       "      <td>02582880</td>\n",
       "      <td>299</td>\n",
       "      <td>04</td>\n",
       "      <td>R</td>\n",
       "      <td>&lt;pysal.cg.shapes.Polygon object at 0x7fdb443d6...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>42998662</td>\n",
       "      <td>2536</td>\n",
       "      <td>Dilkon</td>\n",
       "      <td>N</td>\n",
       "      <td>+35.3606097</td>\n",
       "      <td>-110.3155452</td>\n",
       "      <td>S</td>\n",
       "      <td>0419280</td>\n",
       "      <td>361</td>\n",
       "      <td>+35.3529051</td>\n",
       "      <td>...</td>\n",
       "      <td>N</td>\n",
       "      <td>19926</td>\n",
       "      <td>280401240982653</td>\n",
       "      <td>19280</td>\n",
       "      <td>U1</td>\n",
       "      <td>02408670</td>\n",
       "      <td>1184</td>\n",
       "      <td>04</td>\n",
       "      <td>R</td>\n",
       "      <td>&lt;pysal.cg.shapes.Polygon object at 0x7fdb443d6...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>13063355</td>\n",
       "      <td>14833</td>\n",
       "      <td>Rio Verde</td>\n",
       "      <td>N</td>\n",
       "      <td>+33.7266382</td>\n",
       "      <td>-111.6761487</td>\n",
       "      <td>S</td>\n",
       "      <td>0460250</td>\n",
       "      <td>1647</td>\n",
       "      <td>+33.7265919</td>\n",
       "      <td>...</td>\n",
       "      <td>N</td>\n",
       "      <td>19939</td>\n",
       "      <td>280401230483010</td>\n",
       "      <td>60250</td>\n",
       "      <td>U1</td>\n",
       "      <td>02409181</td>\n",
       "      <td>1811</td>\n",
       "      <td>04</td>\n",
       "      <td>R</td>\n",
       "      <td>&lt;pysal.cg.shapes.Polygon object at 0x7fdb5b918...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>16150048</td>\n",
       "      <td>0</td>\n",
       "      <td>Sacaton Flats Village</td>\n",
       "      <td>N</td>\n",
       "      <td>+33.0558972</td>\n",
       "      <td>-111.6589922</td>\n",
       "      <td>S</td>\n",
       "      <td>0461800</td>\n",
       "      <td>168</td>\n",
       "      <td>+33.0558972</td>\n",
       "      <td>...</td>\n",
       "      <td>N</td>\n",
       "      <td>19830</td>\n",
       "      <td>280403850591491</td>\n",
       "      <td>61800</td>\n",
       "      <td>U2</td>\n",
       "      <td>02612143</td>\n",
       "      <td>541</td>\n",
       "      <td>04</td>\n",
       "      <td>R</td>\n",
       "      <td>&lt;pysal.cg.shapes.Polygon object at 0x7fdb5b8c0...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4067735</td>\n",
       "      <td>0</td>\n",
       "      <td>Aguila</td>\n",
       "      <td>N</td>\n",
       "      <td>+33.9375158</td>\n",
       "      <td>-113.1664832</td>\n",
       "      <td>S</td>\n",
       "      <td>0400730</td>\n",
       "      <td>304</td>\n",
       "      <td>+33.9375158</td>\n",
       "      <td>...</td>\n",
       "      <td>N</td>\n",
       "      <td>19887</td>\n",
       "      <td>280403717476713</td>\n",
       "      <td>00730</td>\n",
       "      <td>U1</td>\n",
       "      <td>02582720</td>\n",
       "      <td>798</td>\n",
       "      <td>04</td>\n",
       "      <td>R</td>\n",
       "      <td>&lt;pysal.cg.shapes.Polygon object at 0x7fdb5b8d8...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   AREALAND  AREAWATER               BASENAME CBSAPCI      CENTLAT  \\\n",
       "0  13352095          0                 Topawa       N  +31.8078220   \n",
       "1  42998662       2536                 Dilkon       N  +35.3606097   \n",
       "2  13063355      14833              Rio Verde       N  +33.7266382   \n",
       "3  16150048          0  Sacaton Flats Village       N  +33.0558972   \n",
       "4   4067735          0                 Aguila       N  +33.9375158   \n",
       "\n",
       "        CENTLON FUNCSTAT    GEOID  HU100     INTPTLAT  \\\n",
       "0  -111.8304860        S  0474680    135  +31.8078220   \n",
       "1  -110.3155452        S  0419280    361  +35.3529051   \n",
       "2  -111.6761487        S  0460250   1647  +33.7265919   \n",
       "3  -111.6589922        S  0461800    168  +33.0558972   \n",
       "4  -113.1664832        S  0400730    304  +33.9375158   \n",
       "\n",
       "                         ...                         NECTAPCI OBJECTID  \\\n",
       "0                        ...                                N    19866   \n",
       "1                        ...                                N    19926   \n",
       "2                        ...                                N    19939   \n",
       "3                        ...                                N    19830   \n",
       "4                        ...                                N    19887   \n",
       "\n",
       "               OID  PLACE PLACECC   PLACENS  POP100 STATE UR  \\\n",
       "0  280403717476697  74680      U1  02582880     299    04  R   \n",
       "1  280401240982653  19280      U1  02408670    1184    04  R   \n",
       "2  280401230483010  60250      U1  02409181    1811    04  R   \n",
       "3  280403850591491  61800      U2  02612143     541    04  R   \n",
       "4  280403717476713  00730      U1  02582720     798    04  R   \n",
       "\n",
       "                                            geometry  \n",
       "0  <pysal.cg.shapes.Polygon object at 0x7fdb443d6...  \n",
       "1  <pysal.cg.shapes.Polygon object at 0x7fdb443d6...  \n",
       "2  <pysal.cg.shapes.Polygon object at 0x7fdb5b918...  \n",
       "3  <pysal.cg.shapes.Polygon object at 0x7fdb5b8c0...  \n",
       "4  <pysal.cg.shapes.Polygon object at 0x7fdb5b8d8...  \n",
       "\n",
       "[5 rows x 24 columns]"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "geodata.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To join the geodata to the other data, use pandas functions:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "newdata = pd.merge(data, geodata, left_on='place', right_on='PLACE')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>H00010001</th>\n",
       "      <th>H0020001</th>\n",
       "      <th>H0020002</th>\n",
       "      <th>H0020003</th>\n",
       "      <th>H0020004</th>\n",
       "      <th>H0020005</th>\n",
       "      <th>H0020006</th>\n",
       "      <th>NAME_x</th>\n",
       "      <th>state</th>\n",
       "      <th>place</th>\n",
       "      <th>...</th>\n",
       "      <th>NECTAPCI</th>\n",
       "      <th>OBJECTID</th>\n",
       "      <th>OID</th>\n",
       "      <th>PLACE</th>\n",
       "      <th>PLACECC</th>\n",
       "      <th>PLACENS</th>\n",
       "      <th>POP100</th>\n",
       "      <th>STATE</th>\n",
       "      <th>UR</th>\n",
       "      <th>geometry</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>304</td>\n",
       "      <td>304</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>304</td>\n",
       "      <td>Aguila CDP</td>\n",
       "      <td>04</td>\n",
       "      <td>00730</td>\n",
       "      <td>...</td>\n",
       "      <td>N</td>\n",
       "      <td>19887</td>\n",
       "      <td>280403717476713</td>\n",
       "      <td>00730</td>\n",
       "      <td>U1</td>\n",
       "      <td>02582720</td>\n",
       "      <td>798</td>\n",
       "      <td>04</td>\n",
       "      <td>R</td>\n",
       "      <td>&lt;pysal.cg.shapes.Polygon object at 0x7fdb5b8d8...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2175</td>\n",
       "      <td>2175</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2175</td>\n",
       "      <td>Ajo CDP</td>\n",
       "      <td>04</td>\n",
       "      <td>00870</td>\n",
       "      <td>...</td>\n",
       "      <td>N</td>\n",
       "      <td>24756</td>\n",
       "      <td>280401254189026</td>\n",
       "      <td>00870</td>\n",
       "      <td>U1</td>\n",
       "      <td>02407704</td>\n",
       "      <td>3304</td>\n",
       "      <td>04</td>\n",
       "      <td>M</td>\n",
       "      <td>&lt;pysal.cg.shapes.Polygon object at 0x7fdb40ae8...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>Ak Chin CDP</td>\n",
       "      <td>04</td>\n",
       "      <td>00940</td>\n",
       "      <td>...</td>\n",
       "      <td>N</td>\n",
       "      <td>20442</td>\n",
       "      <td>280403717476626</td>\n",
       "      <td>00940</td>\n",
       "      <td>U1</td>\n",
       "      <td>02582721</td>\n",
       "      <td>30</td>\n",
       "      <td>04</td>\n",
       "      <td>R</td>\n",
       "      <td>&lt;pysal.cg.shapes.Polygon object at 0x7fdb41dab...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>256</td>\n",
       "      <td>256</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>256</td>\n",
       "      <td>Ak-Chin Village CDP</td>\n",
       "      <td>04</td>\n",
       "      <td>01090</td>\n",
       "      <td>...</td>\n",
       "      <td>N</td>\n",
       "      <td>22362</td>\n",
       "      <td>280401260231698</td>\n",
       "      <td>01090</td>\n",
       "      <td>U1</td>\n",
       "      <td>02407705</td>\n",
       "      <td>862</td>\n",
       "      <td>04</td>\n",
       "      <td>M</td>\n",
       "      <td>&lt;pysal.cg.shapes.Polygon object at 0x7fdb414e2...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>31</td>\n",
       "      <td>31</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>31</td>\n",
       "      <td>Alamo Lake CDP</td>\n",
       "      <td>04</td>\n",
       "      <td>01170</td>\n",
       "      <td>...</td>\n",
       "      <td>N</td>\n",
       "      <td>23031</td>\n",
       "      <td>280403717388977</td>\n",
       "      <td>01170</td>\n",
       "      <td>U2</td>\n",
       "      <td>02582722</td>\n",
       "      <td>25</td>\n",
       "      <td>04</td>\n",
       "      <td>R</td>\n",
       "      <td>&lt;pysal.cg.shapes.Polygon object at 0x7fdb4101d...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 34 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   H00010001  H0020001  H0020002  H0020003  H0020004  H0020005  H0020006  \\\n",
       "0        304       304         0         0         0         0       304   \n",
       "1       2175      2175         0         0         0         0      2175   \n",
       "2         11        11         0         0         0         0        11   \n",
       "3        256       256         0         0         0         0       256   \n",
       "4         31        31         0         0         0         0        31   \n",
       "\n",
       "                NAME_x state  place  \\\n",
       "0           Aguila CDP    04  00730   \n",
       "1              Ajo CDP    04  00870   \n",
       "2          Ak Chin CDP    04  00940   \n",
       "3  Ak-Chin Village CDP    04  01090   \n",
       "4       Alamo Lake CDP    04  01170   \n",
       "\n",
       "                         ...                          NECTAPCI  OBJECTID  \\\n",
       "0                        ...                                 N     19887   \n",
       "1                        ...                                 N     24756   \n",
       "2                        ...                                 N     20442   \n",
       "3                        ...                                 N     22362   \n",
       "4                        ...                                 N     23031   \n",
       "\n",
       "               OID  PLACE PLACECC   PLACENS POP100 STATE  UR  \\\n",
       "0  280403717476713  00730      U1  02582720    798    04   R   \n",
       "1  280401254189026  00870      U1  02407704   3304    04   M   \n",
       "2  280403717476626  00940      U1  02582721     30    04   R   \n",
       "3  280401260231698  01090      U1  02407705    862    04   M   \n",
       "4  280403717388977  01170      U2  02582722     25    04   R   \n",
       "\n",
       "                                            geometry  \n",
       "0  <pysal.cg.shapes.Polygon object at 0x7fdb5b8d8...  \n",
       "1  <pysal.cg.shapes.Polygon object at 0x7fdb40ae8...  \n",
       "2  <pysal.cg.shapes.Polygon object at 0x7fdb41dab...  \n",
       "3  <pysal.cg.shapes.Polygon object at 0x7fdb414e2...  \n",
       "4  <pysal.cg.shapes.Polygon object at 0x7fdb4101d...  \n",
       "\n",
       "[5 rows x 34 columns]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "newdata.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So, that's how you get your geodata in addition to your regular data!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "## OK, that's one API, does it work for others?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We'll try the Economic Census"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "conn2 = c.base.Connection('CBP2012')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Alright, let's look at the available columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "filt2 = [True if 'Statistics' in x else False for x in conn2.variables['concept']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>concept</th>\n",
       "      <th>label</th>\n",
       "      <th>predicateOnly</th>\n",
       "      <th>predicateType</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>EMP</th>\n",
       "      <td>Employer Statistics</td>\n",
       "      <td>Total Number of Employees</td>\n",
       "      <td>NaN</td>\n",
       "      <td>int</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EMPSZES</th>\n",
       "      <td>Employer Statistics</td>\n",
       "      <td>Employment size of establishment</td>\n",
       "      <td>NaN</td>\n",
       "      <td>string</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EMPSZES_TTL</th>\n",
       "      <td>Employer Statistics</td>\n",
       "      <td>Title of Employment size of establishment</td>\n",
       "      <td>NaN</td>\n",
       "      <td>string</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EMP_F</th>\n",
       "      <td>Employer Statistics</td>\n",
       "      <td>Flag for Number of employees</td>\n",
       "      <td>NaN</td>\n",
       "      <td>string</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EMP_N</th>\n",
       "      <td>Employer Statistics</td>\n",
       "      <td>Noise flag for Total Mid-March Employees</td>\n",
       "      <td>NaN</td>\n",
       "      <td>int</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EMP_N_F</th>\n",
       "      <td>Employer Statistics</td>\n",
       "      <td>Flag for Noise field for Total Mid-March Emplo...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>string</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ESTAB</th>\n",
       "      <td>Employer Statistics</td>\n",
       "      <td>Total number of Establishments</td>\n",
       "      <td>NaN</td>\n",
       "      <td>int</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ESTAB_F</th>\n",
       "      <td>Employer Statistics</td>\n",
       "      <td>Flag for Total number of Establishments</td>\n",
       "      <td>NaN</td>\n",
       "      <td>int</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>LFO</th>\n",
       "      <td>Employer Statistics</td>\n",
       "      <td>Legal form of organization</td>\n",
       "      <td>NaN</td>\n",
       "      <td>string</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>LFO_TTL</th>\n",
       "      <td>Employer Statistics</td>\n",
       "      <td>Title of legal form of organization</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PAYANN</th>\n",
       "      <td>Employer Statistics</td>\n",
       "      <td>Total Annual Payroll</td>\n",
       "      <td>NaN</td>\n",
       "      <td>int</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PAYANN_F</th>\n",
       "      <td>Employer Statistics</td>\n",
       "      <td>Flag for Total Annual Payroll</td>\n",
       "      <td>NaN</td>\n",
       "      <td>string</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PAYANN_N</th>\n",
       "      <td>Employer Statistics</td>\n",
       "      <td>Noise flag for Total Annual Payroll</td>\n",
       "      <td>NaN</td>\n",
       "      <td>int</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PAYANN_N_F</th>\n",
       "      <td>Employer Statistics</td>\n",
       "      <td>Flag for Noise field for Total Annual Payroll</td>\n",
       "      <td>NaN</td>\n",
       "      <td>string</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PAYQTR1</th>\n",
       "      <td>Employer Statistics</td>\n",
       "      <td>Total First Quarter Payroll</td>\n",
       "      <td>NaN</td>\n",
       "      <td>int</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PAYQTR1_F</th>\n",
       "      <td>Employer Statistics</td>\n",
       "      <td>Flag for Total First Quarter Payroll</td>\n",
       "      <td>NaN</td>\n",
       "      <td>string</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PAYQTR1_N</th>\n",
       "      <td>Employer Statistics</td>\n",
       "      <td>Noise flag for Total First Quarter Payroll Data</td>\n",
       "      <td>NaN</td>\n",
       "      <td>int</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PAYQTR1_N_F</th>\n",
       "      <td>Employer Statistics</td>\n",
       "      <td>Flag for Noise field for Total First Quarter P...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>string</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>YEAR</th>\n",
       "      <td>Employer Statistics</td>\n",
       "      <td>Year</td>\n",
       "      <td>NaN</td>\n",
       "      <td>string</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         concept  \\\n",
       "EMP          Employer Statistics   \n",
       "EMPSZES      Employer Statistics   \n",
       "EMPSZES_TTL  Employer Statistics   \n",
       "EMP_F        Employer Statistics   \n",
       "EMP_N        Employer Statistics   \n",
       "EMP_N_F      Employer Statistics   \n",
       "ESTAB        Employer Statistics   \n",
       "ESTAB_F      Employer Statistics   \n",
       "LFO          Employer Statistics   \n",
       "LFO_TTL      Employer Statistics   \n",
       "PAYANN       Employer Statistics   \n",
       "PAYANN_F     Employer Statistics   \n",
       "PAYANN_N     Employer Statistics   \n",
       "PAYANN_N_F   Employer Statistics   \n",
       "PAYQTR1      Employer Statistics   \n",
       "PAYQTR1_F    Employer Statistics   \n",
       "PAYQTR1_N    Employer Statistics   \n",
       "PAYQTR1_N_F  Employer Statistics   \n",
       "YEAR         Employer Statistics   \n",
       "\n",
       "                                                         label predicateOnly  \\\n",
       "EMP                                  Total Number of Employees           NaN   \n",
       "EMPSZES                       Employment size of establishment           NaN   \n",
       "EMPSZES_TTL          Title of Employment size of establishment           NaN   \n",
       "EMP_F                             Flag for Number of employees           NaN   \n",
       "EMP_N                 Noise flag for Total Mid-March Employees           NaN   \n",
       "EMP_N_F      Flag for Noise field for Total Mid-March Emplo...           NaN   \n",
       "ESTAB                           Total number of Establishments           NaN   \n",
       "ESTAB_F                Flag for Total number of Establishments           NaN   \n",
       "LFO                                 Legal form of organization           NaN   \n",
       "LFO_TTL                    Title of legal form of organization           NaN   \n",
       "PAYANN                                    Total Annual Payroll           NaN   \n",
       "PAYANN_F                         Flag for Total Annual Payroll           NaN   \n",
       "PAYANN_N                   Noise flag for Total Annual Payroll           NaN   \n",
       "PAYANN_N_F       Flag for Noise field for Total Annual Payroll           NaN   \n",
       "PAYQTR1                            Total First Quarter Payroll           NaN   \n",
       "PAYQTR1_F                 Flag for Total First Quarter Payroll           NaN   \n",
       "PAYQTR1_N      Noise flag for Total First Quarter Payroll Data           NaN   \n",
       "PAYQTR1_N_F  Flag for Noise field for Total First Quarter P...           NaN   \n",
       "YEAR                                                      Year           NaN   \n",
       "\n",
       "            predicateType  \n",
       "EMP                   int  \n",
       "EMPSZES            string  \n",
       "EMPSZES_TTL        string  \n",
       "EMP_F              string  \n",
       "EMP_N                 int  \n",
       "EMP_N_F            string  \n",
       "ESTAB                 int  \n",
       "ESTAB_F               int  \n",
       "LFO                string  \n",
       "LFO_TTL               NaN  \n",
       "PAYANN                int  \n",
       "PAYANN_F           string  \n",
       "PAYANN_N              int  \n",
       "PAYANN_N_F         string  \n",
       "PAYQTR1               int  \n",
       "PAYQTR1_F          string  \n",
       "PAYQTR1_N             int  \n",
       "PAYQTR1_N_F        string  \n",
       "YEAR               string  "
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn2.variables[filt2]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To show the required predicates, we can construct yet another filter. Note that *required* means that the query **will fail** if these are not passed as keyword arguments. They don't have to specify a single value, though, so they can be left as a wild card, like we did with `place:*` in the prior query:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>concept</th>\n",
       "      <th>label</th>\n",
       "      <th>predicateOnly</th>\n",
       "      <th>predicateType</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>COUNTY</th>\n",
       "      <td>Selectable Geographies</td>\n",
       "      <td>FIPS County Code</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CSA</th>\n",
       "      <td>Geographic Characteristics</td>\n",
       "      <td>Combined Statistical Area</td>\n",
       "      <td>NaN</td>\n",
       "      <td>int</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EMP</th>\n",
       "      <td>Employer Statistics</td>\n",
       "      <td>Total Number of Employees</td>\n",
       "      <td>NaN</td>\n",
       "      <td>int</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EMPSZES</th>\n",
       "      <td>Employer Statistics</td>\n",
       "      <td>Employment size of establishment</td>\n",
       "      <td>NaN</td>\n",
       "      <td>string</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EMPSZES_TTL</th>\n",
       "      <td>Employer Statistics</td>\n",
       "      <td>Title of Employment size of establishment</td>\n",
       "      <td>NaN</td>\n",
       "      <td>string</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EMP_F</th>\n",
       "      <td>Employer Statistics</td>\n",
       "      <td>Flag for Number of employees</td>\n",
       "      <td>NaN</td>\n",
       "      <td>string</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EMP_N</th>\n",
       "      <td>Employer Statistics</td>\n",
       "      <td>Noise flag for Total Mid-March Employees</td>\n",
       "      <td>NaN</td>\n",
       "      <td>int</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EMP_N_F</th>\n",
       "      <td>Employer Statistics</td>\n",
       "      <td>Flag for Noise field for Total Mid-March Emplo...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>string</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ESTAB</th>\n",
       "      <td>Employer Statistics</td>\n",
       "      <td>Total number of Establishments</td>\n",
       "      <td>NaN</td>\n",
       "      <td>int</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ESTAB_F</th>\n",
       "      <td>Employer Statistics</td>\n",
       "      <td>Flag for Total number of Establishments</td>\n",
       "      <td>NaN</td>\n",
       "      <td>int</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FOOTID_GEO</th>\n",
       "      <td>Geographic Characteristics</td>\n",
       "      <td>FootID of Geography</td>\n",
       "      <td>NaN</td>\n",
       "      <td>int</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FOOTID_NAICS</th>\n",
       "      <td>Industry classification</td>\n",
       "      <td>FootID of NAICS code</td>\n",
       "      <td>NaN</td>\n",
       "      <td>int</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GEOTYPE</th>\n",
       "      <td>Selectable Geographies</td>\n",
       "      <td>Geography type</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GEO_ID</th>\n",
       "      <td>Geographic Characteristics</td>\n",
       "      <td>ID of Geography</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GEO_TTL</th>\n",
       "      <td>Geographic Characteristics</td>\n",
       "      <td>Title of Geography</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>LFO</th>\n",
       "      <td>Employer Statistics</td>\n",
       "      <td>Legal form of organization</td>\n",
       "      <td>NaN</td>\n",
       "      <td>string</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>LFO_TTL</th>\n",
       "      <td>Employer Statistics</td>\n",
       "      <td>Title of legal form of organization</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MD</th>\n",
       "      <td>Geographic Characteristics</td>\n",
       "      <td>Metro Division</td>\n",
       "      <td>NaN</td>\n",
       "      <td>int</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MSA</th>\n",
       "      <td>Selectable Geographies</td>\n",
       "      <td>Metropolitan/Micropolitan Statistical Area Code</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NAICS2012</th>\n",
       "      <td>Industry classification</td>\n",
       "      <td>NAICS Industry Code</td>\n",
       "      <td>NaN</td>\n",
       "      <td>string</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NAICS2012_TTL</th>\n",
       "      <td>Industry classification</td>\n",
       "      <td>Title of NAICS code</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PAYANN</th>\n",
       "      <td>Employer Statistics</td>\n",
       "      <td>Total Annual Payroll</td>\n",
       "      <td>NaN</td>\n",
       "      <td>int</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PAYANN_F</th>\n",
       "      <td>Employer Statistics</td>\n",
       "      <td>Flag for Total Annual Payroll</td>\n",
       "      <td>NaN</td>\n",
       "      <td>string</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PAYANN_N</th>\n",
       "      <td>Employer Statistics</td>\n",
       "      <td>Noise flag for Total Annual Payroll</td>\n",
       "      <td>NaN</td>\n",
       "      <td>int</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PAYANN_N_F</th>\n",
       "      <td>Employer Statistics</td>\n",
       "      <td>Flag for Noise field for Total Annual Payroll</td>\n",
       "      <td>NaN</td>\n",
       "      <td>string</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PAYQTR1</th>\n",
       "      <td>Employer Statistics</td>\n",
       "      <td>Total First Quarter Payroll</td>\n",
       "      <td>NaN</td>\n",
       "      <td>int</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PAYQTR1_F</th>\n",
       "      <td>Employer Statistics</td>\n",
       "      <td>Flag for Total First Quarter Payroll</td>\n",
       "      <td>NaN</td>\n",
       "      <td>string</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PAYQTR1_N</th>\n",
       "      <td>Employer Statistics</td>\n",
       "      <td>Noise flag for Total First Quarter Payroll Data</td>\n",
       "      <td>NaN</td>\n",
       "      <td>int</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PAYQTR1_N_F</th>\n",
       "      <td>Employer Statistics</td>\n",
       "      <td>Flag for Noise field for Total First Quarter P...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>string</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ST</th>\n",
       "      <td>Selectable Geographies</td>\n",
       "      <td>FIPS State Code</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>YEAR</th>\n",
       "      <td>Employer Statistics</td>\n",
       "      <td>Year</td>\n",
       "      <td>NaN</td>\n",
       "      <td>string</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>for</th>\n",
       "      <td>Census API Geography Specification</td>\n",
       "      <td>Census API FIPS 'for' clause</td>\n",
       "      <td>True</td>\n",
       "      <td>fips-for</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>in</th>\n",
       "      <td>Census API Geography Specification</td>\n",
       "      <td>Census API FIPS 'in' clause</td>\n",
       "      <td>True</td>\n",
       "      <td>fips-in</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                          concept  \\\n",
       "COUNTY                     Selectable Geographies   \n",
       "CSA                    Geographic Characteristics   \n",
       "EMP                           Employer Statistics   \n",
       "EMPSZES                       Employer Statistics   \n",
       "EMPSZES_TTL                   Employer Statistics   \n",
       "EMP_F                         Employer Statistics   \n",
       "EMP_N                         Employer Statistics   \n",
       "EMP_N_F                       Employer Statistics   \n",
       "ESTAB                         Employer Statistics   \n",
       "ESTAB_F                       Employer Statistics   \n",
       "FOOTID_GEO             Geographic Characteristics   \n",
       "FOOTID_NAICS              Industry classification   \n",
       "GEOTYPE                    Selectable Geographies   \n",
       "GEO_ID                 Geographic Characteristics   \n",
       "GEO_TTL                Geographic Characteristics   \n",
       "LFO                           Employer Statistics   \n",
       "LFO_TTL                       Employer Statistics   \n",
       "MD                     Geographic Characteristics   \n",
       "MSA                        Selectable Geographies   \n",
       "NAICS2012                 Industry classification   \n",
       "NAICS2012_TTL             Industry classification   \n",
       "PAYANN                        Employer Statistics   \n",
       "PAYANN_F                      Employer Statistics   \n",
       "PAYANN_N                      Employer Statistics   \n",
       "PAYANN_N_F                    Employer Statistics   \n",
       "PAYQTR1                       Employer Statistics   \n",
       "PAYQTR1_F                     Employer Statistics   \n",
       "PAYQTR1_N                     Employer Statistics   \n",
       "PAYQTR1_N_F                   Employer Statistics   \n",
       "ST                         Selectable Geographies   \n",
       "YEAR                          Employer Statistics   \n",
       "for            Census API Geography Specification   \n",
       "in             Census API Geography Specification   \n",
       "\n",
       "                                                           label  \\\n",
       "COUNTY                                          FIPS County Code   \n",
       "CSA                                    Combined Statistical Area   \n",
       "EMP                                    Total Number of Employees   \n",
       "EMPSZES                         Employment size of establishment   \n",
       "EMPSZES_TTL            Title of Employment size of establishment   \n",
       "EMP_F                               Flag for Number of employees   \n",
       "EMP_N                   Noise flag for Total Mid-March Employees   \n",
       "EMP_N_F        Flag for Noise field for Total Mid-March Emplo...   \n",
       "ESTAB                             Total number of Establishments   \n",
       "ESTAB_F                  Flag for Total number of Establishments   \n",
       "FOOTID_GEO                                   FootID of Geography   \n",
       "FOOTID_NAICS                                FootID of NAICS code   \n",
       "GEOTYPE                                           Geography type   \n",
       "GEO_ID                                           ID of Geography   \n",
       "GEO_TTL                                       Title of Geography   \n",
       "LFO                                   Legal form of organization   \n",
       "LFO_TTL                      Title of legal form of organization   \n",
       "MD                                                Metro Division   \n",
       "MSA              Metropolitan/Micropolitan Statistical Area Code   \n",
       "NAICS2012                                    NAICS Industry Code   \n",
       "NAICS2012_TTL                                Title of NAICS code   \n",
       "PAYANN                                      Total Annual Payroll   \n",
       "PAYANN_F                           Flag for Total Annual Payroll   \n",
       "PAYANN_N                     Noise flag for Total Annual Payroll   \n",
       "PAYANN_N_F         Flag for Noise field for Total Annual Payroll   \n",
       "PAYQTR1                              Total First Quarter Payroll   \n",
       "PAYQTR1_F                   Flag for Total First Quarter Payroll   \n",
       "PAYQTR1_N        Noise flag for Total First Quarter Payroll Data   \n",
       "PAYQTR1_N_F    Flag for Noise field for Total First Quarter P...   \n",
       "ST                                               FIPS State Code   \n",
       "YEAR                                                        Year   \n",
       "for                                 Census API FIPS 'for' clause   \n",
       "in                                   Census API FIPS 'in' clause   \n",
       "\n",
       "              predicateOnly predicateType  \n",
       "COUNTY                  NaN           NaN  \n",
       "CSA                     NaN           int  \n",
       "EMP                     NaN           int  \n",
       "EMPSZES                 NaN        string  \n",
       "EMPSZES_TTL             NaN        string  \n",
       "EMP_F                   NaN        string  \n",
       "EMP_N                   NaN           int  \n",
       "EMP_N_F                 NaN        string  \n",
       "ESTAB                   NaN           int  \n",
       "ESTAB_F                 NaN           int  \n",
       "FOOTID_GEO              NaN           int  \n",
       "FOOTID_NAICS            NaN           int  \n",
       "GEOTYPE                 NaN           NaN  \n",
       "GEO_ID                  NaN           NaN  \n",
       "GEO_TTL                 NaN           NaN  \n",
       "LFO                     NaN        string  \n",
       "LFO_TTL                 NaN           NaN  \n",
       "MD                      NaN           int  \n",
       "MSA                     NaN           NaN  \n",
       "NAICS2012               NaN        string  \n",
       "NAICS2012_TTL           NaN           NaN  \n",
       "PAYANN                  NaN           int  \n",
       "PAYANN_F                NaN        string  \n",
       "PAYANN_N                NaN           int  \n",
       "PAYANN_N_F              NaN        string  \n",
       "PAYQTR1                 NaN           int  \n",
       "PAYQTR1_F               NaN        string  \n",
       "PAYQTR1_N               NaN           int  \n",
       "PAYQTR1_N_F             NaN        string  \n",
       "ST                      NaN           NaN  \n",
       "YEAR                    NaN        string  \n",
       "for                    True      fips-for  \n",
       "in                     True       fips-in  "
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn2.variables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Like before, geographies are shown with their requirements. Here, the only geography is the `fips` geography. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dict_keys(['fips'])"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn2.geographies.keys()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>geoLevelDisplay</th>\n",
       "      <th>geoLevelId</th>\n",
       "      <th>name</th>\n",
       "      <th>optionalWithWCFor</th>\n",
       "      <th>requires</th>\n",
       "      <th>wildcard</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>01</td>\n",
       "      <td>us</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>02</td>\n",
       "      <td>state</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>03</td>\n",
       "      <td>03</td>\n",
       "      <td>county</td>\n",
       "      <td>state</td>\n",
       "      <td>[state]</td>\n",
       "      <td>[state]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>08,09</td>\n",
       "      <td>08</td>\n",
       "      <td>metropolitan statistical area/micropolitan sta...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  geoLevelDisplay geoLevelId  \\\n",
       "0             NaN         01   \n",
       "1             NaN         02   \n",
       "2              03         03   \n",
       "3           08,09         08   \n",
       "\n",
       "                                                name optionalWithWCFor  \\\n",
       "0                                                 us               NaN   \n",
       "1                                              state               NaN   \n",
       "2                                             county             state   \n",
       "3  metropolitan statistical area/micropolitan sta...               NaN   \n",
       "\n",
       "  requires wildcard  \n",
       "0      NaN      NaN  \n",
       "1      NaN      NaN  \n",
       "2  [state]  [state]  \n",
       "3      NaN      NaN  "
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn2.geographies['fips']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, we'll do some fun with error handling and passing of additional arguments to the query. Any \"extra\" required predicates beyond `get`, `for` and `in` are added at the end of the query as keyword arguments. These are caught and introduced into the query following the API specifications. \n",
    "\n",
    "First, though, let's see what happens when we submit a malformed query!\n",
    "\n",
    "Here, we can query for every column in the dataset applied to places in California (`fips = 06`). The dataset we're working with, the Economic Census, requires an `OPTAX` field, which identifies the \"type of operation or tax status code\" along which to slice the data. Just like the other arguments, we will map them to keywords in the API string, and a wildcard represents a slice of all possible values. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "cols = conn2.varslike('ESTAB*', engine='fnmatch')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/mnt/data/Dropbox/dev/cenpy/cenpy/remote.py:167: FutureWarning: convert_objects is deprecated.  Use the data-type specific converters pd.to_datetime, pd.to_timedelta and pd.to_numeric.\n",
      "  df[cols] = df[cols].convert_objects(convert_numeric=convert_numeric)\n"
     ]
    }
   ],
   "source": [
    "data2 = conn2.query(cols=cols, geo_unit='county:*', geo_filter={'state':'06'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ESTAB</th>\n",
       "      <th>ESTAB_F</th>\n",
       "      <th>state</th>\n",
       "      <th>county</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>635</td>\n",
       "      <td>None</td>\n",
       "      <td>06</td>\n",
       "      <td>999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>36700</td>\n",
       "      <td>None</td>\n",
       "      <td>06</td>\n",
       "      <td>001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>43</td>\n",
       "      <td>None</td>\n",
       "      <td>06</td>\n",
       "      <td>003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>801</td>\n",
       "      <td>None</td>\n",
       "      <td>06</td>\n",
       "      <td>005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4615</td>\n",
       "      <td>None</td>\n",
       "      <td>06</td>\n",
       "      <td>007</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ESTAB ESTAB_F state county\n",
       "0    635    None    06    999\n",
       "1  36700    None    06    001\n",
       "2     43    None    06    003\n",
       "3    801    None    06    005\n",
       "4   4615    None    06    007"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data2.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And so you get the table of employment by County & NAICS code for employment and establishments in California counties. Since we're using counties as our unit of analysis, we could grab the geodata for counties."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "conn2.set_mapservice('State_County')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "But, there are quite a few layers in this MapService:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "71"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(conn2.mapservice.layers)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Oof. If you ever want to check out the web interface to see what it looks like, you can retrieve the URLs of most objects using:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'http://tigerweb.geo.census.gov/arcgis/rest/services/TIGERweb/State_County/MapServer'"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn2.mapservice._baseurl"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Anyway, we know counties don't really change all that much. So, let's just pick a counties layer and pull it down for California:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "geodata2= conn2.mapservice.query(layer=1,where='STATE = 06')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "newdata2 = pd.merge(data2, geodata2, left_on='county', right_on='COUNTY')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ESTAB</th>\n",
       "      <th>ESTAB_F</th>\n",
       "      <th>state</th>\n",
       "      <th>county</th>\n",
       "      <th>AREALAND</th>\n",
       "      <th>AREAWATER</th>\n",
       "      <th>BASENAME</th>\n",
       "      <th>CENTLAT</th>\n",
       "      <th>CENTLON</th>\n",
       "      <th>COUNTY</th>\n",
       "      <th>...</th>\n",
       "      <th>GEOID</th>\n",
       "      <th>INTPTLAT</th>\n",
       "      <th>INTPTLON</th>\n",
       "      <th>LSADC</th>\n",
       "      <th>MTFCC</th>\n",
       "      <th>NAME</th>\n",
       "      <th>OBJECTID</th>\n",
       "      <th>OID</th>\n",
       "      <th>STATE</th>\n",
       "      <th>geometry</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>36700</td>\n",
       "      <td>None</td>\n",
       "      <td>06</td>\n",
       "      <td>001</td>\n",
       "      <td>1914242789</td>\n",
       "      <td>212979931</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>+37.6506226</td>\n",
       "      <td>-121.9176449</td>\n",
       "      <td>001</td>\n",
       "      <td>...</td>\n",
       "      <td>06001</td>\n",
       "      <td>+37.6471385</td>\n",
       "      <td>-121.9124880</td>\n",
       "      <td>06</td>\n",
       "      <td>G4020</td>\n",
       "      <td>Alameda County</td>\n",
       "      <td>2098</td>\n",
       "      <td>27590141293924</td>\n",
       "      <td>06</td>\n",
       "      <td>&lt;pysal.cg.shapes.Polygon object at 0x7fdb28b50...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>43</td>\n",
       "      <td>None</td>\n",
       "      <td>06</td>\n",
       "      <td>003</td>\n",
       "      <td>1912292633</td>\n",
       "      <td>12557304</td>\n",
       "      <td>Alpine</td>\n",
       "      <td>+38.5971043</td>\n",
       "      <td>-119.8206026</td>\n",
       "      <td>003</td>\n",
       "      <td>...</td>\n",
       "      <td>06003</td>\n",
       "      <td>+38.6217831</td>\n",
       "      <td>-119.7983522</td>\n",
       "      <td>06</td>\n",
       "      <td>G4020</td>\n",
       "      <td>Alpine County</td>\n",
       "      <td>1317</td>\n",
       "      <td>27590289634197</td>\n",
       "      <td>06</td>\n",
       "      <td>&lt;pysal.cg.shapes.Polygon object at 0x7fdb291d4...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>801</td>\n",
       "      <td>None</td>\n",
       "      <td>06</td>\n",
       "      <td>005</td>\n",
       "      <td>1539933575</td>\n",
       "      <td>29470568</td>\n",
       "      <td>Amador</td>\n",
       "      <td>+38.4466174</td>\n",
       "      <td>-120.6516693</td>\n",
       "      <td>005</td>\n",
       "      <td>...</td>\n",
       "      <td>06005</td>\n",
       "      <td>+38.4435501</td>\n",
       "      <td>-120.6538563</td>\n",
       "      <td>06</td>\n",
       "      <td>G4020</td>\n",
       "      <td>Amador County</td>\n",
       "      <td>2724</td>\n",
       "      <td>27590143912562</td>\n",
       "      <td>06</td>\n",
       "      <td>&lt;pysal.cg.shapes.Polygon object at 0x7fdb27aee...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4615</td>\n",
       "      <td>None</td>\n",
       "      <td>06</td>\n",
       "      <td>007</td>\n",
       "      <td>4238423334</td>\n",
       "      <td>105325812</td>\n",
       "      <td>Butte</td>\n",
       "      <td>+39.6665788</td>\n",
       "      <td>-121.6007017</td>\n",
       "      <td>007</td>\n",
       "      <td>...</td>\n",
       "      <td>06007</td>\n",
       "      <td>+39.6659588</td>\n",
       "      <td>-121.6019188</td>\n",
       "      <td>06</td>\n",
       "      <td>G4020</td>\n",
       "      <td>Butte County</td>\n",
       "      <td>2237</td>\n",
       "      <td>27590417130535</td>\n",
       "      <td>06</td>\n",
       "      <td>&lt;pysal.cg.shapes.Polygon object at 0x7fdb28006...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>891</td>\n",
       "      <td>None</td>\n",
       "      <td>06</td>\n",
       "      <td>009</td>\n",
       "      <td>2641820834</td>\n",
       "      <td>43806026</td>\n",
       "      <td>Calaveras</td>\n",
       "      <td>+38.2044678</td>\n",
       "      <td>-120.5546688</td>\n",
       "      <td>009</td>\n",
       "      <td>...</td>\n",
       "      <td>06009</td>\n",
       "      <td>+38.1838996</td>\n",
       "      <td>-120.5614415</td>\n",
       "      <td>06</td>\n",
       "      <td>G4020</td>\n",
       "      <td>Calaveras County</td>\n",
       "      <td>347</td>\n",
       "      <td>27590202403841</td>\n",
       "      <td>06</td>\n",
       "      <td>&lt;pysal.cg.shapes.Polygon object at 0x7fdb2a353...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   ESTAB ESTAB_F state county    AREALAND  AREAWATER   BASENAME      CENTLAT  \\\n",
       "0  36700    None    06    001  1914242789  212979931    Alameda  +37.6506226   \n",
       "1     43    None    06    003  1912292633   12557304     Alpine  +38.5971043   \n",
       "2    801    None    06    005  1539933575   29470568     Amador  +38.4466174   \n",
       "3   4615    None    06    007  4238423334  105325812      Butte  +39.6665788   \n",
       "4    891    None    06    009  2641820834   43806026  Calaveras  +38.2044678   \n",
       "\n",
       "        CENTLON COUNTY                        ...                          \\\n",
       "0  -121.9176449    001                        ...                           \n",
       "1  -119.8206026    003                        ...                           \n",
       "2  -120.6516693    005                        ...                           \n",
       "3  -121.6007017    007                        ...                           \n",
       "4  -120.5546688    009                        ...                           \n",
       "\n",
       "   GEOID     INTPTLAT      INTPTLON LSADC  MTFCC              NAME OBJECTID  \\\n",
       "0  06001  +37.6471385  -121.9124880    06  G4020    Alameda County     2098   \n",
       "1  06003  +38.6217831  -119.7983522    06  G4020     Alpine County     1317   \n",
       "2  06005  +38.4435501  -120.6538563    06  G4020     Amador County     2724   \n",
       "3  06007  +39.6659588  -121.6019188    06  G4020      Butte County     2237   \n",
       "4  06009  +38.1838996  -120.5614415    06  G4020  Calaveras County      347   \n",
       "\n",
       "              OID STATE                                           geometry  \n",
       "0  27590141293924    06  <pysal.cg.shapes.Polygon object at 0x7fdb28b50...  \n",
       "1  27590289634197    06  <pysal.cg.shapes.Polygon object at 0x7fdb291d4...  \n",
       "2  27590143912562    06  <pysal.cg.shapes.Polygon object at 0x7fdb27aee...  \n",
       "3  27590417130535    06  <pysal.cg.shapes.Polygon object at 0x7fdb28006...  \n",
       "4  27590202403841    06  <pysal.cg.shapes.Polygon object at 0x7fdb2a353...  \n",
       "\n",
       "[5 rows x 23 columns]"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "newdata2.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And that's all there is to it! Geodata and tabular data from the Census APIs in one place.\n",
    "\n",
    "File an issue if you have concerns!"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [conda env:py3]",
   "language": "python",
   "name": "conda-env-py3-py"
  },
  "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": 0
}