fbi_crime_data.ipynb

{
 "metadata": {
  "name": "",
  "signature": "sha256:fe68d809fedf4453f9fb8216ef32370d9156df7e7180a7c61af0d713a725bdff"
 },
 "nbformat": 3,
 "nbformat_minor": 0,
 "worksheets": [
  {
   "cells": [
    {
     "cell_type": "heading",
     "level": 1,
     "metadata": {},
     "source": [
      "Scraping Crime Data from the FBI"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "The [Uniform Crime Reports](http://www.ucrdatatool.gov/ranking.cfm) is an FBI program to collect standardized crime statistics across the United States. This notebook collects crime information from states and large cities, and compiles them into two CSV files."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import pandas as pd\n",
      "import requests\n",
      "\n",
      "from cStringIO import StringIO"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 11
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "City-Level Data"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "shortnames = {'Murder and nonnegligent manslaughter rate': 'Murder',\n",
      "              'Forcible rape rate': 'Rape',\n",
      "              'Robbery rate': 'Robbery',\n",
      "              'Aggravated assault rate': 'Assault',\n",
      "              'Property crime rate': 'Property',\n",
      "              'Burglary rate': 'Burglary',\n",
      "              'Larceny-theft rate': 'Larceny',\n",
      "              'Motor vehicle theft rate': 'Vehicular'}\n",
      "\n",
      "def city_data(year):\n",
      "    \"\"\"\n",
      "    Grab crime statistics by city, for a single year. Return as a CSV string\n",
      "    \"\"\"\n",
      "    url = 'http://www.ucrdatatool.gov/Search/Crime/Local/DownCrimeOneYearofDataLarge.cfm/LocalCrimeOneYearofData.csv'\n",
      "    form = {\"CrimeCrossId\": \"40,136,310,375,381,725,886,910,911,929,945,946,961,971,973,978,1010,1013,1028,1042,1056,1103,1116,1117,1135,1143,1146,1158,1177,1227,1233,1240,1245,1252,1261,1265,1267,1269,1272,1289,1306,1317,1322,1362,1380,1391,1434,1452,1471,1476,1477,1481,1486,1491,1496,1503,1524,1531,1540,1541,1550,1557,1562,1563,1574,1584,1596,1610,1630,1639,1642,1647,1652,1672,1750,1753,1763,1765,1767,1817,1820,1823,1825,1833,1874,1878,1888,22564,1918,1941,1969,2024,2089,2122,2141,2157,2194,2214,2244,2257,2277,2428,2456,2463,2487,2704,2724,2793,2811,2560,2976,2983,3045,3056,3081,3104,3182,3201,3204,3274,3310,3312,3436,3858,4037,4101,4134,4141,4411,4583,4701,4846,5032,5216,5303,5368,5453,5774,5789,5876,6134,6306,6430,6523,6612,6643,6970,22787,6985,7244,7391,7453,7506,7633,7655,7798,7948,8036,8055,8451,8621,8670,8713,8836,9144,9195,9539,9627,9672,9883,10027,10043,10291,10306,10518,10702,11291,11304,11386,11418,11472,11509,11763,11948,11956,12012,11017,11063,12262,12517,12520,12629,12750,12797,13003,13015,13168,13171,13183,13187,13222,13307,13794,13987,14155,14294,14305,14463,14477,14499,14532,15146,15319,15559,15574,15692,15826,15847,15954,15968,16061,16513,17184,17198,17736,17815,17847,18014,18293,18378,18383,18510,18552,18573,18576,18713,18733,18752,18763,18784,18843,18874,18902,18916,18965,19000,19016,19061,19109,19123,19144,19242,19262,19320,19366,19378,19411,19444,19449,19462,19466,19543,19575,19592,19632,19662,19680,19863,19941,20075,20085,20132,20133,20142,20220,20332,20430,20436,20501,20576,20718,20786,20816,20915,20930,20942,20959,21179,21256,21295\",\n",
      "            \"YearStart\": str(year),\n",
      "            \"YearEnd\": str(year),\n",
      "            \"DataType\":\"1,2,3,4\"}\n",
      "    return requests.post(url, data=form).content\n",
      "\n",
      "def parse_city(year):\n",
      "    \"\"\"\n",
      "    Download crime data for 1 year across cities, and parse into a DataFrame\n",
      "    \"\"\"\n",
      "    print year,\n",
      "    data = city_data(year)\n",
      "\n",
      "    cols = ['Agency', u'State', u'Population', 'Violent Crime rate', \n",
      "            'Murder and nonnegligent manslaughter rate', 'Forcible rape rate', \n",
      "            'Robbery rate', u'Aggravated assault rate', \n",
      "            'Property crime rate', u'Burglary rate', 'Larceny-theft rate', u'Motor vehicle theft rate']\n",
      "    \n",
      "    # seek to first line of data\n",
      "    data = data[data.find('Agency,'):]    \n",
      "    df = pd.read_csv(StringIO(data))\n",
      "        \n",
      "    df = df[cols] # focus on relevant columns...\n",
      "    df = df[df.Population > 100000] # ...and rows\n",
      "    df = df.rename_axis(shortnames, axis=1) # relabel\n",
      "    df['Year'] = year\n",
      "    return df\n"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 142
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "city = pd.concat(map(parse_city, range(1985, 2013)), ignore_index=True)\n",
      "city.to_csv('city_crime.csv', index=False)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "1985 "
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "1986 "
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "1987 "
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "1988 "
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "1989 "
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "1990 "
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "1991 "
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "1992 "
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "1993 "
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "1994 "
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "1995 "
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "1996 "
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "1997 "
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "1998 "
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "1999 "
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "2000 "
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "2001 "
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "2002 "
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "2003 "
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "2004 "
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "2005 "
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "2006 "
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "2007 "
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "2008 "
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "2009 "
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "2010 "
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "2011 "
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "2012\n"
       ]
      }
     ],
     "prompt_number": 119
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "city.head(20)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
        "<table border=\"1\" class=\"dataframe\">\n",
        "  <thead>\n",
        "    <tr style=\"text-align: right;\">\n",
        "      <th></th>\n",
        "      <th>Agency</th>\n",
        "      <th>State</th>\n",
        "      <th>Population</th>\n",
        "      <th>Violent Crime rate</th>\n",
        "      <th>Murder</th>\n",
        "      <th>Rape</th>\n",
        "      <th>Robbery</th>\n",
        "      <th>Assault</th>\n",
        "      <th>Property</th>\n",
        "      <th>Burglary</th>\n",
        "      <th>Larceny</th>\n",
        "      <th>Vehicular</th>\n",
        "      <th>Year</th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>0 </th>\n",
        "      <td>        Abilene Police Dept</td>\n",
        "      <td> TX</td>\n",
        "      <td> 111317</td>\n",
        "      <td>  318.9</td>\n",
        "      <td>  7.2</td>\n",
        "      <td>  32.3</td>\n",
        "      <td>   86.2</td>\n",
        "      <td>  193.1</td>\n",
        "      <td>  5530.2</td>\n",
        "      <td> 1458.0</td>\n",
        "      <td> 3697.5</td>\n",
        "      <td>  374.6</td>\n",
        "      <td> 1985</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>1 </th>\n",
        "      <td>     Akron City Police Dept</td>\n",
        "      <td> OH</td>\n",
        "      <td> 226704</td>\n",
        "      <td>  826.2</td>\n",
        "      <td>  7.5</td>\n",
        "      <td>  69.7</td>\n",
        "      <td>  225.4</td>\n",
        "      <td>  523.6</td>\n",
        "      <td>  5849.5</td>\n",
        "      <td> 1410.2</td>\n",
        "      <td> 4025.5</td>\n",
        "      <td>  413.8</td>\n",
        "      <td> 1985</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2 </th>\n",
        "      <td>    Albuquerque Police Dept</td>\n",
        "      <td> NM</td>\n",
        "      <td> 357051</td>\n",
        "      <td> 1149.7</td>\n",
        "      <td> 11.8</td>\n",
        "      <td>  66.7</td>\n",
        "      <td>  349.2</td>\n",
        "      <td>  722.0</td>\n",
        "      <td>  8136.9</td>\n",
        "      <td> 2572.7</td>\n",
        "      <td> 5023.1</td>\n",
        "      <td>  541.1</td>\n",
        "      <td> 1985</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>3 </th>\n",
        "      <td>     Alexandria Police Dept</td>\n",
        "      <td> VA</td>\n",
        "      <td> 108346</td>\n",
        "      <td>  740.2</td>\n",
        "      <td>  6.5</td>\n",
        "      <td>  48.0</td>\n",
        "      <td>  392.3</td>\n",
        "      <td>  293.5</td>\n",
        "      <td>  6339.0</td>\n",
        "      <td> 1343.8</td>\n",
        "      <td> 4378.6</td>\n",
        "      <td>  616.5</td>\n",
        "      <td> 1985</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>4 </th>\n",
        "      <td> Allentown City Police Dept</td>\n",
        "      <td> PA</td>\n",
        "      <td> 103483</td>\n",
        "      <td>  382.7</td>\n",
        "      <td>  3.9</td>\n",
        "      <td>  45.4</td>\n",
        "      <td>  184.6</td>\n",
        "      <td>  148.8</td>\n",
        "      <td>  5014.4</td>\n",
        "      <td> 1331.6</td>\n",
        "      <td> 3426.6</td>\n",
        "      <td>  256.1</td>\n",
        "      <td> 1985</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>5 </th>\n",
        "      <td>       Amarillo Police Dept</td>\n",
        "      <td> TX</td>\n",
        "      <td> 166747</td>\n",
        "      <td>  455.2</td>\n",
        "      <td> 10.2</td>\n",
        "      <td>  35.4</td>\n",
        "      <td>   97.2</td>\n",
        "      <td>  312.4</td>\n",
        "      <td>  6047.5</td>\n",
        "      <td> 1612.6</td>\n",
        "      <td> 4116.4</td>\n",
        "      <td>  318.4</td>\n",
        "      <td> 1985</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>6 </th>\n",
        "      <td>   Amherst Town Police Dept</td>\n",
        "      <td> NY</td>\n",
        "      <td> 108570</td>\n",
        "      <td>  210.9</td>\n",
        "      <td>  0.9</td>\n",
        "      <td>   2.8</td>\n",
        "      <td>   38.7</td>\n",
        "      <td>  168.6</td>\n",
        "      <td>  2441.7</td>\n",
        "      <td>  446.7</td>\n",
        "      <td> 1760.2</td>\n",
        "      <td>  234.9</td>\n",
        "      <td> 1985</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>7 </th>\n",
        "      <td>        Anaheim Police Dept</td>\n",
        "      <td> CA</td>\n",
        "      <td> 240283</td>\n",
        "      <td>  483.6</td>\n",
        "      <td>  6.7</td>\n",
        "      <td>  44.5</td>\n",
        "      <td>  250.1</td>\n",
        "      <td>  182.3</td>\n",
        "      <td>  6829.0</td>\n",
        "      <td> 2147.5</td>\n",
        "      <td> 3971.6</td>\n",
        "      <td>  710.0</td>\n",
        "      <td> 1985</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>8 </th>\n",
        "      <td>      Anchorage Police Dept</td>\n",
        "      <td> AK</td>\n",
        "      <td> 229579</td>\n",
        "      <td>  515.7</td>\n",
        "      <td>  6.1</td>\n",
        "      <td>  84.1</td>\n",
        "      <td>  161.6</td>\n",
        "      <td>  264.0</td>\n",
        "      <td>  5853.3</td>\n",
        "      <td> 1259.7</td>\n",
        "      <td> 3987.3</td>\n",
        "      <td>  606.3</td>\n",
        "      <td> 1985</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>9 </th>\n",
        "      <td>      Ann Arbor Police Dept</td>\n",
        "      <td> MI</td>\n",
        "      <td> 107832</td>\n",
        "      <td>  588.9</td>\n",
        "      <td>  3.7</td>\n",
        "      <td>  34.3</td>\n",
        "      <td>  132.6</td>\n",
        "      <td>  418.2</td>\n",
        "      <td>  7936.4</td>\n",
        "      <td> 1448.5</td>\n",
        "      <td> 6025.1</td>\n",
        "      <td>  462.8</td>\n",
        "      <td> 1985</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>10</th>\n",
        "      <td>      Arlington Police Dept</td>\n",
        "      <td> TX</td>\n",
        "      <td> 218931</td>\n",
        "      <td>  507.0</td>\n",
        "      <td>  8.2</td>\n",
        "      <td>  48.0</td>\n",
        "      <td>  136.6</td>\n",
        "      <td>  314.3</td>\n",
        "      <td>  8461.1</td>\n",
        "      <td> 1599.6</td>\n",
        "      <td> 6065.8</td>\n",
        "      <td>  795.7</td>\n",
        "      <td> 1985</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>11</th>\n",
        "      <td>        Atlanta Police Dept</td>\n",
        "      <td> GA</td>\n",
        "      <td> 436214</td>\n",
        "      <td> 2661.5</td>\n",
        "      <td> 33.2</td>\n",
        "      <td> 156.6</td>\n",
        "      <td> 1098.5</td>\n",
        "      <td> 1373.2</td>\n",
        "      <td> 10521.2</td>\n",
        "      <td> 2795.0</td>\n",
        "      <td> 6733.4</td>\n",
        "      <td>  992.9</td>\n",
        "      <td> 1985</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>12</th>\n",
        "      <td>         Aurora Police Dept</td>\n",
        "      <td> CO</td>\n",
        "      <td> 198033</td>\n",
        "      <td> 1136.2</td>\n",
        "      <td>  5.0</td>\n",
        "      <td>  44.9</td>\n",
        "      <td>  219.2</td>\n",
        "      <td>  867.0</td>\n",
        "      <td>  7400.8</td>\n",
        "      <td> 2000.2</td>\n",
        "      <td> 4916.9</td>\n",
        "      <td>  483.8</td>\n",
        "      <td> 1985</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>13</th>\n",
        "      <td>         Austin Police Dept</td>\n",
        "      <td> TX</td>\n",
        "      <td> 406469</td>\n",
        "      <td>  581.3</td>\n",
        "      <td> 12.8</td>\n",
        "      <td> 120.3</td>\n",
        "      <td>  264.7</td>\n",
        "      <td>  183.5</td>\n",
        "      <td>  9024.3</td>\n",
        "      <td> 2524.4</td>\n",
        "      <td> 5980.3</td>\n",
        "      <td>  519.6</td>\n",
        "      <td> 1985</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>14</th>\n",
        "      <td>    Bakersfield Police Dept</td>\n",
        "      <td> CA</td>\n",
        "      <td> 133983</td>\n",
        "      <td>  897.1</td>\n",
        "      <td> 12.7</td>\n",
        "      <td>  51.5</td>\n",
        "      <td>  447.1</td>\n",
        "      <td>  385.9</td>\n",
        "      <td>  8746.6</td>\n",
        "      <td> 2878.0</td>\n",
        "      <td> 5241.0</td>\n",
        "      <td>  627.7</td>\n",
        "      <td> 1985</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>15</th>\n",
        "      <td> Baltimore City Police Dept</td>\n",
        "      <td> MD</td>\n",
        "      <td> 771097</td>\n",
        "      <td> 2009.9</td>\n",
        "      <td> 27.6</td>\n",
        "      <td>  76.8</td>\n",
        "      <td> 1007.8</td>\n",
        "      <td>  897.7</td>\n",
        "      <td>  6565.1</td>\n",
        "      <td> 1799.0</td>\n",
        "      <td> 3985.5</td>\n",
        "      <td>  780.6</td>\n",
        "      <td> 1985</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>16</th>\n",
        "      <td>    Baton Rouge Police Dept</td>\n",
        "      <td> LA</td>\n",
        "      <td> 245830</td>\n",
        "      <td> 1761.8</td>\n",
        "      <td> 14.6</td>\n",
        "      <td>  51.3</td>\n",
        "      <td>  395.0</td>\n",
        "      <td> 1300.9</td>\n",
        "      <td> 10962.0</td>\n",
        "      <td> 3247.4</td>\n",
        "      <td> 6729.9</td>\n",
        "      <td>  984.8</td>\n",
        "      <td> 1985</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>17</th>\n",
        "      <td>       Beaumont Police Dept</td>\n",
        "      <td> TX</td>\n",
        "      <td> 126298</td>\n",
        "      <td>  772.0</td>\n",
        "      <td>  8.7</td>\n",
        "      <td> 103.7</td>\n",
        "      <td>  269.2</td>\n",
        "      <td>  390.3</td>\n",
        "      <td>  6500.5</td>\n",
        "      <td> 2323.9</td>\n",
        "      <td> 3669.9</td>\n",
        "      <td>  506.7</td>\n",
        "      <td> 1985</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>18</th>\n",
        "      <td>       Berkeley Police Dept</td>\n",
        "      <td> CA</td>\n",
        "      <td> 106768</td>\n",
        "      <td> 1090.2</td>\n",
        "      <td> 10.3</td>\n",
        "      <td>  40.3</td>\n",
        "      <td>  618.2</td>\n",
        "      <td>  421.5</td>\n",
        "      <td> 11272.1</td>\n",
        "      <td> 2744.3</td>\n",
        "      <td> 7713.9</td>\n",
        "      <td>  813.9</td>\n",
        "      <td> 1985</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>19</th>\n",
        "      <td>     Birmingham Police Dept</td>\n",
        "      <td> AL</td>\n",
        "      <td> 281973</td>\n",
        "      <td> 1188.1</td>\n",
        "      <td> 34.4</td>\n",
        "      <td> 100.4</td>\n",
        "      <td>  508.9</td>\n",
        "      <td>  544.4</td>\n",
        "      <td>  8768.9</td>\n",
        "      <td> 2543.9</td>\n",
        "      <td> 4979.9</td>\n",
        "      <td> 1245.2</td>\n",
        "      <td> 1985</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 123,
       "text": [
        "                        Agency State  Population  Violent Crime rate  Murder  \\\n",
        "0          Abilene Police Dept    TX      111317               318.9     7.2   \n",
        "1       Akron City Police Dept    OH      226704               826.2     7.5   \n",
        "2      Albuquerque Police Dept    NM      357051              1149.7    11.8   \n",
        "3       Alexandria Police Dept    VA      108346               740.2     6.5   \n",
        "4   Allentown City Police Dept    PA      103483               382.7     3.9   \n",
        "5         Amarillo Police Dept    TX      166747               455.2    10.2   \n",
        "6     Amherst Town Police Dept    NY      108570               210.9     0.9   \n",
        "7          Anaheim Police Dept    CA      240283               483.6     6.7   \n",
        "8        Anchorage Police Dept    AK      229579               515.7     6.1   \n",
        "9        Ann Arbor Police Dept    MI      107832               588.9     3.7   \n",
        "10       Arlington Police Dept    TX      218931               507.0     8.2   \n",
        "11         Atlanta Police Dept    GA      436214              2661.5    33.2   \n",
        "12          Aurora Police Dept    CO      198033              1136.2     5.0   \n",
        "13          Austin Police Dept    TX      406469               581.3    12.8   \n",
        "14     Bakersfield Police Dept    CA      133983               897.1    12.7   \n",
        "15  Baltimore City Police Dept    MD      771097              2009.9    27.6   \n",
        "16     Baton Rouge Police Dept    LA      245830              1761.8    14.6   \n",
        "17        Beaumont Police Dept    TX      126298               772.0     8.7   \n",
        "18        Berkeley Police Dept    CA      106768              1090.2    10.3   \n",
        "19      Birmingham Police Dept    AL      281973              1188.1    34.4   \n",
        "\n",
        "     Rape  Robbery  Assault  Property  Burglary  Larceny  Vehicular  Year  \n",
        "0    32.3     86.2    193.1    5530.2    1458.0   3697.5      374.6  1985  \n",
        "1    69.7    225.4    523.6    5849.5    1410.2   4025.5      413.8  1985  \n",
        "2    66.7    349.2    722.0    8136.9    2572.7   5023.1      541.1  1985  \n",
        "3    48.0    392.3    293.5    6339.0    1343.8   4378.6      616.5  1985  \n",
        "4    45.4    184.6    148.8    5014.4    1331.6   3426.6      256.1  1985  \n",
        "5    35.4     97.2    312.4    6047.5    1612.6   4116.4      318.4  1985  \n",
        "6     2.8     38.7    168.6    2441.7     446.7   1760.2      234.9  1985  \n",
        "7    44.5    250.1    182.3    6829.0    2147.5   3971.6      710.0  1985  \n",
        "8    84.1    161.6    264.0    5853.3    1259.7   3987.3      606.3  1985  \n",
        "9    34.3    132.6    418.2    7936.4    1448.5   6025.1      462.8  1985  \n",
        "10   48.0    136.6    314.3    8461.1    1599.6   6065.8      795.7  1985  \n",
        "11  156.6   1098.5   1373.2   10521.2    2795.0   6733.4      992.9  1985  \n",
        "12   44.9    219.2    867.0    7400.8    2000.2   4916.9      483.8  1985  \n",
        "13  120.3    264.7    183.5    9024.3    2524.4   5980.3      519.6  1985  \n",
        "14   51.5    447.1    385.9    8746.6    2878.0   5241.0      627.7  1985  \n",
        "15   76.8   1007.8    897.7    6565.1    1799.0   3985.5      780.6  1985  \n",
        "16   51.3    395.0   1300.9   10962.0    3247.4   6729.9      984.8  1985  \n",
        "17  103.7    269.2    390.3    6500.5    2323.9   3669.9      506.7  1985  \n",
        "18   40.3    618.2    421.5   11272.1    2744.3   7713.9      813.9  1985  \n",
        "19  100.4    508.9    544.4    8768.9    2543.9   4979.9     1245.2  1985  "
       ]
      }
     ],
     "prompt_number": 123
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "State-Level Data"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "url = 'http://www.ucrdatatool.gov/Search/Crime/State/DownCrimeStatebyState.cfm/CrimeStatebyState.csv'\n",
      "form = {\"StateId\":\"52,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51\",\n",
      "        \"YearStart\":\"1960\",\n",
      "        \"YearEnd\":\"2012\",\n",
      "        \"DataType\":\"1,2,3,4\"}\n",
      "\n",
      "def parse_state(txt):\n",
      "    cols = ['Year', 'Population', \n",
      "            'Violent Crime rate', \n",
      "            'Murder and nonnegligent manslaughter rate', \n",
      "            'Forcible rape rate', \n",
      "            'Robbery rate', \n",
      "            'Aggravated assault rate', \n",
      "            'Property crime rate', \n",
      "            'Burglary rate', \n",
      "            'Larceny-theft rate', \n",
      "            'Motor vehicle theft rate']\n",
      "\n",
      "    state = txt.split('\\n')[0].strip()\n",
      "    df = pd.read_csv(StringIO(txt), skiprows=2)\n",
      "    df = df[cols]\n",
      "    df['State'] = state\n",
      "    df = df.rename_axis(shortnames, axis=1)\n",
      "    df = df[df.Population.convert_objects(convert_numeric=True) > 0] # remove notes at end of document\n",
      "    \n",
      "    return df\n",
      "\n",
      "data = requests.post(url, data=form).content\n",
      "data = data.split('Estimated crime in')[1:]\n",
      "states = pd.concat(map(parse_state, data), ignore_index=True)\n",
      "states.to_csv('state_crime.csv', index=False)\n"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 161
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "states.tail(20)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
        "<table border=\"1\" class=\"dataframe\">\n",
        "  <thead>\n",
        "    <tr style=\"text-align: right;\">\n",
        "      <th></th>\n",
        "      <th>Year</th>\n",
        "      <th>Population</th>\n",
        "      <th>Violent Crime rate</th>\n",
        "      <th>Murder</th>\n",
        "      <th>Rape</th>\n",
        "      <th>Robbery</th>\n",
        "      <th>Assault</th>\n",
        "      <th>Property</th>\n",
        "      <th>Burglary</th>\n",
        "      <th>Larceny</th>\n",
        "      <th>Vehicular</th>\n",
        "      <th>State</th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>2731</th>\n",
        "      <td> 1993</td>\n",
        "      <td>  257782608</td>\n",
        "      <td> 747.1</td>\n",
        "      <td> 9.5</td>\n",
        "      <td> 41.1</td>\n",
        "      <td> 256.0</td>\n",
        "      <td> 440.5</td>\n",
        "      <td> 4740.0</td>\n",
        "      <td> 1099.7</td>\n",
        "      <td> 3033.9</td>\n",
        "      <td> 606.3</td>\n",
        "      <td> United States-Total</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2732</th>\n",
        "      <td> 1994</td>\n",
        "      <td>  260327021</td>\n",
        "      <td> 713.6</td>\n",
        "      <td> 9.0</td>\n",
        "      <td> 39.3</td>\n",
        "      <td> 237.8</td>\n",
        "      <td> 427.6</td>\n",
        "      <td> 4660.2</td>\n",
        "      <td> 1042.1</td>\n",
        "      <td> 3026.9</td>\n",
        "      <td> 591.3</td>\n",
        "      <td> United States-Total</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2733</th>\n",
        "      <td> 1995</td>\n",
        "      <td>  262803276</td>\n",
        "      <td> 684.5</td>\n",
        "      <td> 8.2</td>\n",
        "      <td> 37.1</td>\n",
        "      <td> 220.9</td>\n",
        "      <td> 418.3</td>\n",
        "      <td> 4590.5</td>\n",
        "      <td>  987.0</td>\n",
        "      <td> 3043.2</td>\n",
        "      <td> 560.3</td>\n",
        "      <td> United States-Total</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2734</th>\n",
        "      <td> 1996</td>\n",
        "      <td>  265228572</td>\n",
        "      <td> 636.6</td>\n",
        "      <td> 7.4</td>\n",
        "      <td> 36.3</td>\n",
        "      <td> 201.9</td>\n",
        "      <td> 391.0</td>\n",
        "      <td> 4451.0</td>\n",
        "      <td>  945.0</td>\n",
        "      <td> 2980.3</td>\n",
        "      <td> 525.7</td>\n",
        "      <td> United States-Total</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2735</th>\n",
        "      <td> 1997</td>\n",
        "      <td>  267783607</td>\n",
        "      <td> 611.0</td>\n",
        "      <td> 6.8</td>\n",
        "      <td> 35.9</td>\n",
        "      <td> 186.2</td>\n",
        "      <td> 382.1</td>\n",
        "      <td> 4316.3</td>\n",
        "      <td>  918.8</td>\n",
        "      <td> 2891.8</td>\n",
        "      <td> 505.7</td>\n",
        "      <td> United States-Total</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2736</th>\n",
        "      <td> 1998</td>\n",
        "      <td>  270248003</td>\n",
        "      <td> 567.6</td>\n",
        "      <td> 6.3</td>\n",
        "      <td> 34.5</td>\n",
        "      <td> 165.5</td>\n",
        "      <td> 361.4</td>\n",
        "      <td> 4052.5</td>\n",
        "      <td>  863.2</td>\n",
        "      <td> 2729.5</td>\n",
        "      <td> 459.9</td>\n",
        "      <td> United States-Total</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2737</th>\n",
        "      <td> 1999</td>\n",
        "      <td>  272690813</td>\n",
        "      <td> 523.0</td>\n",
        "      <td> 5.7</td>\n",
        "      <td> 32.8</td>\n",
        "      <td> 150.1</td>\n",
        "      <td> 334.3</td>\n",
        "      <td> 3743.6</td>\n",
        "      <td>  770.4</td>\n",
        "      <td> 2550.7</td>\n",
        "      <td> 422.5</td>\n",
        "      <td> United States-Total</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2738</th>\n",
        "      <td> 2000</td>\n",
        "      <td>  281421906</td>\n",
        "      <td> 506.5</td>\n",
        "      <td> 5.5</td>\n",
        "      <td> 32.0</td>\n",
        "      <td> 145.0</td>\n",
        "      <td> 324.0</td>\n",
        "      <td> 3618.3</td>\n",
        "      <td>  728.8</td>\n",
        "      <td> 2477.3</td>\n",
        "      <td> 412.2</td>\n",
        "      <td> United States-Total</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2739</th>\n",
        "      <td> 2001</td>\n",
        "      <td>  285317559</td>\n",
        "      <td> 504.5</td>\n",
        "      <td> 5.6</td>\n",
        "      <td> 31.8</td>\n",
        "      <td> 148.5</td>\n",
        "      <td> 318.6</td>\n",
        "      <td> 3658.1</td>\n",
        "      <td>  741.8</td>\n",
        "      <td> 2485.7</td>\n",
        "      <td> 430.5</td>\n",
        "      <td> United States-Total</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2740</th>\n",
        "      <td> 2002</td>\n",
        "      <td>  287973924</td>\n",
        "      <td> 494.4</td>\n",
        "      <td> 5.6</td>\n",
        "      <td> 33.1</td>\n",
        "      <td> 146.1</td>\n",
        "      <td> 309.5</td>\n",
        "      <td> 3630.6</td>\n",
        "      <td>  747.0</td>\n",
        "      <td> 2450.7</td>\n",
        "      <td> 432.9</td>\n",
        "      <td> United States-Total</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2741</th>\n",
        "      <td> 2003</td>\n",
        "      <td>  290788976</td>\n",
        "      <td> 475.8</td>\n",
        "      <td> 5.7</td>\n",
        "      <td> 32.3</td>\n",
        "      <td> 142.5</td>\n",
        "      <td> 295.4</td>\n",
        "      <td> 3591.2</td>\n",
        "      <td>  741.0</td>\n",
        "      <td> 2416.5</td>\n",
        "      <td> 433.7</td>\n",
        "      <td> United States-Total</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2742</th>\n",
        "      <td> 2004</td>\n",
        "      <td>  293656842</td>\n",
        "      <td> 463.2</td>\n",
        "      <td> 5.5</td>\n",
        "      <td> 32.4</td>\n",
        "      <td> 136.7</td>\n",
        "      <td> 288.6</td>\n",
        "      <td> 3514.1</td>\n",
        "      <td>  730.3</td>\n",
        "      <td> 2362.3</td>\n",
        "      <td> 421.5</td>\n",
        "      <td> United States-Total</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2743</th>\n",
        "      <td> 2005</td>\n",
        "      <td>  296507061</td>\n",
        "      <td> 469.0</td>\n",
        "      <td> 5.6</td>\n",
        "      <td> 31.8</td>\n",
        "      <td> 140.8</td>\n",
        "      <td> 290.8</td>\n",
        "      <td> 3431.5</td>\n",
        "      <td>  726.9</td>\n",
        "      <td> 2287.8</td>\n",
        "      <td> 416.8</td>\n",
        "      <td> United States-Total</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2744</th>\n",
        "      <td> 2006</td>\n",
        "      <td>  299398484</td>\n",
        "      <td> 479.3</td>\n",
        "      <td> 5.8</td>\n",
        "      <td> 31.6</td>\n",
        "      <td> 150.0</td>\n",
        "      <td> 292.0</td>\n",
        "      <td> 3346.6</td>\n",
        "      <td>  733.1</td>\n",
        "      <td> 2213.2</td>\n",
        "      <td> 400.2</td>\n",
        "      <td> United States-Total</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2745</th>\n",
        "      <td> 2007</td>\n",
        "      <td>  301621157</td>\n",
        "      <td> 471.8</td>\n",
        "      <td> 5.7</td>\n",
        "      <td> 30.6</td>\n",
        "      <td> 148.3</td>\n",
        "      <td> 287.2</td>\n",
        "      <td> 3276.4</td>\n",
        "      <td>  726.1</td>\n",
        "      <td> 2185.4</td>\n",
        "      <td> 364.9</td>\n",
        "      <td> United States-Total</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2746</th>\n",
        "      <td> 2008</td>\n",
        "      <td>  304059724</td>\n",
        "      <td> 458.6</td>\n",
        "      <td> 5.4</td>\n",
        "      <td> 29.8</td>\n",
        "      <td> 145.9</td>\n",
        "      <td> 277.5</td>\n",
        "      <td> 3214.6</td>\n",
        "      <td>  733.0</td>\n",
        "      <td> 2166.1</td>\n",
        "      <td> 315.4</td>\n",
        "      <td> United States-Total</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2747</th>\n",
        "      <td> 2009</td>\n",
        "      <td>  307006550</td>\n",
        "      <td> 431.9</td>\n",
        "      <td> 5.0</td>\n",
        "      <td> 29.1</td>\n",
        "      <td> 133.1</td>\n",
        "      <td> 264.7</td>\n",
        "      <td> 3041.3</td>\n",
        "      <td>  717.7</td>\n",
        "      <td> 2064.5</td>\n",
        "      <td> 259.2</td>\n",
        "      <td> United States-Total</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2748</th>\n",
        "      <td> 2010</td>\n",
        "      <td>  309330219</td>\n",
        "      <td> 404.5</td>\n",
        "      <td> 4.8</td>\n",
        "      <td> 27.7</td>\n",
        "      <td> 119.3</td>\n",
        "      <td> 252.8</td>\n",
        "      <td> 2945.9</td>\n",
        "      <td>  701.0</td>\n",
        "      <td> 2005.8</td>\n",
        "      <td> 239.1</td>\n",
        "      <td> United States-Total</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2749</th>\n",
        "      <td> 2011</td>\n",
        "      <td>  311587816</td>\n",
        "      <td> 387.1</td>\n",
        "      <td> 4.7</td>\n",
        "      <td> 27.0</td>\n",
        "      <td> 113.9</td>\n",
        "      <td> 241.5</td>\n",
        "      <td> 2905.4</td>\n",
        "      <td>  701.3</td>\n",
        "      <td> 1974.1</td>\n",
        "      <td> 230.0</td>\n",
        "      <td> United States-Total</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2750</th>\n",
        "      <td> 2012</td>\n",
        "      <td>  313914040</td>\n",
        "      <td> 386.9</td>\n",
        "      <td> 4.7</td>\n",
        "      <td> 26.9</td>\n",
        "      <td> 112.9</td>\n",
        "      <td> 242.3</td>\n",
        "      <td> 2859.2</td>\n",
        "      <td>  670.2</td>\n",
        "      <td> 1959.3</td>\n",
        "      <td> 229.7</td>\n",
        "      <td> United States-Total</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 162,
       "text": [
        "      Year  Population  Violent Crime rate  Murder  Rape  Robbery  Assault  \\\n",
        "2731  1993   257782608               747.1     9.5  41.1    256.0    440.5   \n",
        "2732  1994   260327021               713.6     9.0  39.3    237.8    427.6   \n",
        "2733  1995   262803276               684.5     8.2  37.1    220.9    418.3   \n",
        "2734  1996   265228572               636.6     7.4  36.3    201.9    391.0   \n",
        "2735  1997   267783607               611.0     6.8  35.9    186.2    382.1   \n",
        "2736  1998   270248003               567.6     6.3  34.5    165.5    361.4   \n",
        "2737  1999   272690813               523.0     5.7  32.8    150.1    334.3   \n",
        "2738  2000   281421906               506.5     5.5  32.0    145.0    324.0   \n",
        "2739  2001   285317559               504.5     5.6  31.8    148.5    318.6   \n",
        "2740  2002   287973924               494.4     5.6  33.1    146.1    309.5   \n",
        "2741  2003   290788976               475.8     5.7  32.3    142.5    295.4   \n",
        "2742  2004   293656842               463.2     5.5  32.4    136.7    288.6   \n",
        "2743  2005   296507061               469.0     5.6  31.8    140.8    290.8   \n",
        "2744  2006   299398484               479.3     5.8  31.6    150.0    292.0   \n",
        "2745  2007   301621157               471.8     5.7  30.6    148.3    287.2   \n",
        "2746  2008   304059724               458.6     5.4  29.8    145.9    277.5   \n",
        "2747  2009   307006550               431.9     5.0  29.1    133.1    264.7   \n",
        "2748  2010   309330219               404.5     4.8  27.7    119.3    252.8   \n",
        "2749  2011   311587816               387.1     4.7  27.0    113.9    241.5   \n",
        "2750  2012   313914040               386.9     4.7  26.9    112.9    242.3   \n",
        "\n",
        "      Property  Burglary  Larceny  Vehicular                State  \n",
        "2731    4740.0    1099.7   3033.9      606.3  United States-Total  \n",
        "2732    4660.2    1042.1   3026.9      591.3  United States-Total  \n",
        "2733    4590.5     987.0   3043.2      560.3  United States-Total  \n",
        "2734    4451.0     945.0   2980.3      525.7  United States-Total  \n",
        "2735    4316.3     918.8   2891.8      505.7  United States-Total  \n",
        "2736    4052.5     863.2   2729.5      459.9  United States-Total  \n",
        "2737    3743.6     770.4   2550.7      422.5  United States-Total  \n",
        "2738    3618.3     728.8   2477.3      412.2  United States-Total  \n",
        "2739    3658.1     741.8   2485.7      430.5  United States-Total  \n",
        "2740    3630.6     747.0   2450.7      432.9  United States-Total  \n",
        "2741    3591.2     741.0   2416.5      433.7  United States-Total  \n",
        "2742    3514.1     730.3   2362.3      421.5  United States-Total  \n",
        "2743    3431.5     726.9   2287.8      416.8  United States-Total  \n",
        "2744    3346.6     733.1   2213.2      400.2  United States-Total  \n",
        "2745    3276.4     726.1   2185.4      364.9  United States-Total  \n",
        "2746    3214.6     733.0   2166.1      315.4  United States-Total  \n",
        "2747    3041.3     717.7   2064.5      259.2  United States-Total  \n",
        "2748    2945.9     701.0   2005.8      239.1  United States-Total  \n",
        "2749    2905.4     701.3   1974.1      230.0  United States-Total  \n",
        "2750    2859.2     670.2   1959.3      229.7  United States-Total  "
       ]
      }
     ],
     "prompt_number": 162
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": []
    }
   ],
   "metadata": {}
  }
 ]
}