get_geographic_data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# This needs to by run in a Python2 environment"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### This notebook describes how to get geographic or mapping data that relates to census information. This will be used to help us check that we have each census tract in DC represented in our data, and to relate data collected via the Uber API to census information. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This has great instructions, and a link to a notebook demo: https://pypi.python.org/pypi/cenpy/0.9.1\n",
    "There are lots of other census things you can select, including different map layers and different cities, etc. \n",
    "Check it out so you can get the most relevant data for your study. Here, I only use layer 14 (census tracts) for \n",
    "my city (D.C. -> `'STATE = 11'`)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import cenpy as c"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# initiate a connection with the database\n",
    "conn = c.base.Connection('2010sf1')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# initiate a connection with the tiger shapefile database\n",
    "conn.set_mapservice('tigerWMS_Census2010')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(ESRILayer) Census Tracts"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# look at census tracts in ESRIlayer 14\n",
    "conn.mapservice.layers[14]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>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</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>11</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</td>\n",
       "      <td>STATE</td>\n",
       "      <td>esriFieldTypeString</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>COUNTY</td>\n",
       "      <td>None</td>\n",
       "      <td>3</td>\n",
       "      <td>COUNTY</td>\n",
       "      <td>esriFieldTypeString</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>TRACT</td>\n",
       "      <td>None</td>\n",
       "      <td>6</td>\n",
       "      <td>TRACT</td>\n",
       "      <td>esriFieldTypeString</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>BASENAME</td>\n",
       "      <td>None</td>\n",
       "      <td>100</td>\n",
       "      <td>BASENAME</td>\n",
       "      <td>esriFieldTypeString</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>NAME</td>\n",
       "      <td>None</td>\n",
       "      <td>100</td>\n",
       "      <td>NAME</td>\n",
       "      <td>esriFieldTypeString</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>LSADC</td>\n",
       "      <td>None</td>\n",
       "      <td>2</td>\n",
       "      <td>LSADC</td>\n",
       "      <td>esriFieldTypeString</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>FUNCSTAT</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>FUNCSTAT</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</td>\n",
       "      <td>UR</td>\n",
       "      <td>esriFieldTypeString</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</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>14</th>\n",
       "      <td>CENTLAT</td>\n",
       "      <td>None</td>\n",
       "      <td>11</td>\n",
       "      <td>CENTLAT</td>\n",
       "      <td>esriFieldTypeString</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>CENTLON</td>\n",
       "      <td>None</td>\n",
       "      <td>12</td>\n",
       "      <td>CENTLON</td>\n",
       "      <td>esriFieldTypeString</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>INTPTLAT</td>\n",
       "      <td>None</td>\n",
       "      <td>11</td>\n",
       "      <td>INTPTLAT</td>\n",
       "      <td>esriFieldTypeString</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>INTPTLON</td>\n",
       "      <td>None</td>\n",
       "      <td>12</td>\n",
       "      <td>INTPTLON</td>\n",
       "      <td>esriFieldTypeString</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</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>19</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>20</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       MTFCC    esriFieldTypeString\n",
       "1          OID   None     NaN         OID    esriFieldTypeDouble\n",
       "2        GEOID   None      11       GEOID    esriFieldTypeString\n",
       "3        STATE   None       2       STATE    esriFieldTypeString\n",
       "4       COUNTY   None       3      COUNTY    esriFieldTypeString\n",
       "5        TRACT   None       6       TRACT    esriFieldTypeString\n",
       "6     BASENAME   None     100    BASENAME    esriFieldTypeString\n",
       "7         NAME   None     100        NAME    esriFieldTypeString\n",
       "8        LSADC   None       2       LSADC    esriFieldTypeString\n",
       "9     FUNCSTAT   None       1    FUNCSTAT    esriFieldTypeString\n",
       "10    AREALAND   None     NaN    AREALAND    esriFieldTypeDouble\n",
       "11   AREAWATER   None     NaN   AREAWATER    esriFieldTypeDouble\n",
       "12          UR   None       1          UR    esriFieldTypeString\n",
       "13  STGEOMETRY   None     NaN  STGEOMETRY  esriFieldTypeGeometry\n",
       "14     CENTLAT   None      11     CENTLAT    esriFieldTypeString\n",
       "15     CENTLON   None      12     CENTLON    esriFieldTypeString\n",
       "16    INTPTLAT   None      11    INTPTLAT    esriFieldTypeString\n",
       "17    INTPTLON   None      12    INTPTLON    esriFieldTypeString\n",
       "18       HU100   None     NaN       HU100    esriFieldTypeDouble\n",
       "19      POP100   None     NaN      POP100    esriFieldTypeDouble\n",
       "20    OBJECTID   None     NaN    OBJECTID       esriFieldTypeOID"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# look at variables associated with a layer\n",
    "conn.mapservice.layers[14].variables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# create a dataframe for DC (State 11)\n",
    "geodata = conn.mapservice.query(layer=14, where = 'STATE = 11')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>CENTLAT</th>\n",
       "      <th>CENTLON</th>\n",
       "      <th>COUNTY</th>\n",
       "      <th>FUNCSTAT</th>\n",
       "      <th>GEOID</th>\n",
       "      <th>HU100</th>\n",
       "      <th>INTPTLAT</th>\n",
       "      <th>...</th>\n",
       "      <th>LSADC</th>\n",
       "      <th>MTFCC</th>\n",
       "      <th>NAME</th>\n",
       "      <th>OBJECTID</th>\n",
       "      <th>OID</th>\n",
       "      <th>POP100</th>\n",
       "      <th>STATE</th>\n",
       "      <th>TRACT</th>\n",
       "      <th>UR</th>\n",
       "      <th>geometry</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>579629</td>\n",
       "      <td>0</td>\n",
       "      <td>80.02</td>\n",
       "      <td>+38.8915424</td>\n",
       "      <td>-076.9827573</td>\n",
       "      <td>001</td>\n",
       "      <td>S</td>\n",
       "      <td>11001008002</td>\n",
       "      <td>1644</td>\n",
       "      <td>+38.8915424</td>\n",
       "      <td>...</td>\n",
       "      <td>CT</td>\n",
       "      <td>G5020</td>\n",
       "      <td>Census Tract 80.02</td>\n",
       "      <td>23766</td>\n",
       "      <td>20740331304906</td>\n",
       "      <td>3031</td>\n",
       "      <td>11</td>\n",
       "      <td>008002</td>\n",
       "      <td>U</td>\n",
       "      <td>&lt;pysal.cg.shapes.Polygon object at 0x119cb3990&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>535254</td>\n",
       "      <td>0</td>\n",
       "      <td>78.09</td>\n",
       "      <td>+38.9015402</td>\n",
       "      <td>-076.9321955</td>\n",
       "      <td>001</td>\n",
       "      <td>S</td>\n",
       "      <td>11001007809</td>\n",
       "      <td>1339</td>\n",
       "      <td>+38.9015402</td>\n",
       "      <td>...</td>\n",
       "      <td>CT</td>\n",
       "      <td>G5020</td>\n",
       "      <td>Census Tract 78.09</td>\n",
       "      <td>23770</td>\n",
       "      <td>20740331304811</td>\n",
       "      <td>2922</td>\n",
       "      <td>11</td>\n",
       "      <td>007809</td>\n",
       "      <td>U</td>\n",
       "      <td>&lt;pysal.cg.shapes.Polygon object at 0x119cb3b10&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>543460</td>\n",
       "      <td>0</td>\n",
       "      <td>25.01</td>\n",
       "      <td>+38.9446515</td>\n",
       "      <td>-077.0317348</td>\n",
       "      <td>001</td>\n",
       "      <td>S</td>\n",
       "      <td>11001002501</td>\n",
       "      <td>1042</td>\n",
       "      <td>+38.9446515</td>\n",
       "      <td>...</td>\n",
       "      <td>CT</td>\n",
       "      <td>G5020</td>\n",
       "      <td>Census Tract 25.01</td>\n",
       "      <td>24466</td>\n",
       "      <td>20740331303268</td>\n",
       "      <td>2554</td>\n",
       "      <td>11</td>\n",
       "      <td>002501</td>\n",
       "      <td>U</td>\n",
       "      <td>&lt;pysal.cg.shapes.Polygon object at 0x119ccc390&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>300647</td>\n",
       "      <td>0</td>\n",
       "      <td>29</td>\n",
       "      <td>+38.9338479</td>\n",
       "      <td>-077.0299131</td>\n",
       "      <td>001</td>\n",
       "      <td>S</td>\n",
       "      <td>11001002900</td>\n",
       "      <td>1643</td>\n",
       "      <td>+38.9338479</td>\n",
       "      <td>...</td>\n",
       "      <td>CT</td>\n",
       "      <td>G5020</td>\n",
       "      <td>Census Tract 29</td>\n",
       "      <td>24471</td>\n",
       "      <td>20740331303383</td>\n",
       "      <td>3962</td>\n",
       "      <td>11</td>\n",
       "      <td>002900</td>\n",
       "      <td>U</td>\n",
       "      <td>&lt;pysal.cg.shapes.Polygon object at 0x119ce2490&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>603721</td>\n",
       "      <td>0</td>\n",
       "      <td>25.02</td>\n",
       "      <td>+38.9390574</td>\n",
       "      <td>-077.0301448</td>\n",
       "      <td>001</td>\n",
       "      <td>S</td>\n",
       "      <td>11001002502</td>\n",
       "      <td>2415</td>\n",
       "      <td>+38.9390574</td>\n",
       "      <td>...</td>\n",
       "      <td>CT</td>\n",
       "      <td>G5020</td>\n",
       "      <td>Census Tract 25.02</td>\n",
       "      <td>24472</td>\n",
       "      <td>20740331303283</td>\n",
       "      <td>5973</td>\n",
       "      <td>11</td>\n",
       "      <td>002502</td>\n",
       "      <td>U</td>\n",
       "      <td>&lt;pysal.cg.shapes.Polygon object at 0x119cee790&gt;</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   AREALAND  AREAWATER BASENAME      CENTLAT       CENTLON COUNTY FUNCSTAT  \\\n",
       "0    579629          0    80.02  +38.8915424  -076.9827573    001        S   \n",
       "1    535254          0    78.09  +38.9015402  -076.9321955    001        S   \n",
       "2    543460          0    25.01  +38.9446515  -077.0317348    001        S   \n",
       "3    300647          0       29  +38.9338479  -077.0299131    001        S   \n",
       "4    603721          0    25.02  +38.9390574  -077.0301448    001        S   \n",
       "\n",
       "         GEOID  HU100     INTPTLAT  \\\n",
       "0  11001008002   1644  +38.8915424   \n",
       "1  11001007809   1339  +38.9015402   \n",
       "2  11001002501   1042  +38.9446515   \n",
       "3  11001002900   1643  +38.9338479   \n",
       "4  11001002502   2415  +38.9390574   \n",
       "\n",
       "                        ...                        LSADC  MTFCC  \\\n",
       "0                       ...                           CT  G5020   \n",
       "1                       ...                           CT  G5020   \n",
       "2                       ...                           CT  G5020   \n",
       "3                       ...                           CT  G5020   \n",
       "4                       ...                           CT  G5020   \n",
       "\n",
       "                 NAME OBJECTID             OID  POP100  STATE   TRACT UR  \\\n",
       "0  Census Tract 80.02    23766  20740331304906    3031     11  008002  U   \n",
       "1  Census Tract 78.09    23770  20740331304811    2922     11  007809  U   \n",
       "2  Census Tract 25.01    24466  20740331303268    2554     11  002501  U   \n",
       "3     Census Tract 29    24471  20740331303383    3962     11  002900  U   \n",
       "4  Census Tract 25.02    24472  20740331303283    5973     11  002502  U   \n",
       "\n",
       "                                          geometry  \n",
       "0  <pysal.cg.shapes.Polygon object at 0x119cb3990>  \n",
       "1  <pysal.cg.shapes.Polygon object at 0x119cb3b10>  \n",
       "2  <pysal.cg.shapes.Polygon object at 0x119ccc390>  \n",
       "3  <pysal.cg.shapes.Polygon object at 0x119ce2490>  \n",
       "4  <pysal.cg.shapes.Polygon object at 0x119cee790>  \n",
       "\n",
       "[5 rows x 21 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "geodata.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In order for the rest of this processing to work in python3, we need to lose the column `'geometry'` because that cannot be read in python3 due to pysal libray not being python3 compatible. \n",
    "\n",
    "To make the maps later in CartoDB, I uploaded the zipped census file found at www.census.gov for the DC area. It contains the shapefiles needed to draw the census tract boundaries. To learn how to use it, check out http://cartodb.github.io/training/intermediate/uofm-workshop.html I found it super-useful!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "del geodata['geometry']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index([u'AREALAND', u'AREAWATER', u'BASENAME', u'CENTLAT', u'CENTLON',\n",
       "       u'COUNTY', u'FUNCSTAT', u'GEOID', u'HU100', u'INTPTLAT', u'INTPTLON',\n",
       "       u'LSADC', u'MTFCC', u'NAME', u'OBJECTID', u'OID', u'POP100', u'STATE',\n",
       "       u'TRACT', u'UR'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "geodata.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can pickle it and/or save as a .csv for later use in `'Mapping_points_across_DC'` ipython notebook"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "geodata.to_csv('./data/geodata.csv', index=False)\n",
    "geodata.to_pickle('./data/geodata.pkl')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Now move to Python3 and the notebook Mapping_points_across_DC.ipynb"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}