osm.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# OpenStreetMap Data Case Study"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Map parser\n",
    "Use mapparse.py to fnamed unique tags"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import xml.etree.cElementTree as ET\n",
    "from collections import defaultdict\n",
    "import csv, sqlite3\n",
    "import re\n",
    "import pprint\n",
    "\n",
    "OSMFILE = \"tampa_florida.osm\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Writing mapparser.py\n"
     ]
    }
   ],
   "source": [
    "#%%writefile mapparser.py\n",
    "#!/usr/bin/env python\n",
    "import xml.etree.ElementTree as ET\n",
    "\n",
    "\"\"\"\n",
    "Process the map file with iterative parsing to find what kinds of \n",
    "tags there are and how many. Outputs a dictionary where the keys\n",
    "are the tag names and the values are how many times the tag is in\n",
    "the map.\n",
    "\"\"\"\n",
    "\n",
    "def count_tags(filename):\n",
    "    tag_count = {}\n",
    "    for _, element in ET.iterparse(filename, events=(\"start\",)):\n",
    "        add_tag(element.tag, tag_count)\n",
    "    return tag_count\n",
    "\n",
    "def add_tag(tag, tag_count):\n",
    "    if tag in tag_count:\n",
    "        tag_count[tag] += 1\n",
    "    else:\n",
    "        tag_count[tag] = 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'bounds': 1,\n",
      " 'member': 31857,\n",
      " 'nd': 1957582,\n",
      " 'node': 1655566,\n",
      " 'osm': 1,\n",
      " 'relation': 1252,\n",
      " 'tag': 1131585,\n",
      " 'way': 182866}\n"
     ]
    }
   ],
   "source": [
    "tags = count_tags(OSMFILE)\n",
    "pprint.pprint(tags)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Tags\n",
    "Use tags.py to fnamed patterns in the tags."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Writing tags.py\n"
     ]
    }
   ],
   "source": [
    "#%%writefile tags.py\n",
    "#!/usr/bin/env python\n",
    "import re\n",
    "import xml.etree.ElementTree as ET\n",
    "\n",
    "\"\"\"\n",
    "Your task is to explore the data a bit more.\n",
    "Before you process the data and add it into MongoDB, you should\n",
    "check the \"k\" value for each \"<tag>\" and see if they can be valid keys in MongoDB,\n",
    "as well as see if there are any other potential problems.\n",
    "\n",
    "We have provided you with 3 regular expressions to check for certain patterns\n",
    "in the tags. As we saw in the quiz earlier, we would like to change the data model\n",
    "and expand the \"addr:street\" type of keys to a dictionary like this:\n",
    "{\"address\": {\"street\": \"Some value\"}}\n",
    "So, we have to see if we have such tags, and if we have any tags with problematic characters.\n",
    "Please complete the function 'key_type'.\n",
    "\"\"\"\n",
    "\n",
    "\n",
    "\n",
    "lower = re.compile(r'^([a-z]|_)*$')\n",
    "lower_colon = re.compile(r'^([a-z]|_)*:([a-z]|_)*$')\n",
    "problemchars = re.compile(r'[=\\+/&<>;\\'\"\\?%#$@\\,\\. \\t\\r\\n]')\n",
    "\n",
    "def key_type(element, keys):\n",
    "    if element.tag == \"tag\":\n",
    "        key = element.get(\"k\")\n",
    "        #print key\n",
    "        if problemchars.search(key):\n",
    "            keys['problemchars'] += 1\n",
    "            #print '--> problemchars'\n",
    "        elif lower_colon.search(key):\n",
    "            keys['lower_colon'] += 1\n",
    "            #print '--> lower_colon'\n",
    "        elif lower.search(key):\n",
    "            keys['lower'] += 1\n",
    "            #print '--> lower'\n",
    "        else:\n",
    "            keys['other'] += 1\n",
    "            #print '--> other'\n",
    "    return keys\n",
    "\n",
    "def process_map(filename):\n",
    "    keys = {\"lower\": 0, \"lower_colon\": 0, \"problemchars\": 0, \"other\": 0}\n",
    "    for _, element in ET.iterparse(filename):\n",
    "        keys = key_type(element, keys)\n",
    "\n",
    "    return keys\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'lower': 575997, 'lower_colon': 520908, 'other': 34675, 'problemchars': 5}\n"
     ]
    }
   ],
   "source": [
    "keys = process_map(OSMFILE)\n",
    "pprint.pprint(keys)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Users\n",
    "find user ids by using users.py"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Writing users.py\n"
     ]
    }
   ],
   "source": [
    "#%%writefile users.py\n",
    "#!/usr/bin/env python\n",
    "import xml.etree.ElementTree as ET\n",
    "\n",
    "\"\"\"\n",
    "Your task is to explore the data a bit more.\n",
    "The first task is a fun one - find out how many unique users\n",
    "have contributed to the map in this particular area!\n",
    "The function process_map should return a set of unique user IDs (\"uid\")\n",
    "\"\"\"\n",
    "\n",
    "\n",
    "def get_user(element):\n",
    "    return\n",
    "\n",
    "def process_map(filename):\n",
    "    users = set()\n",
    "    for _, element in ET.iterparse(filename):\n",
    "        if 'uid' in element.attrib:\n",
    "            users.add(element.get('uid'))\n",
    "\n",
    "    return users"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "set(['1',\n",
      "     '100042',\n",
      "     '1007528',\n",
      "     '1009527',\n",
      "     '1012362',\n",
      "     '101999',\n",
      "     '102411',\n",
      "     '102582',\n",
      "     '102999',\n",
      "     '103095',\n",
      "     '103107',\n",
      "     '103253',\n",
      "     '10344',\n",
      "     '103464',\n",
      "     '103574',\n",
      "     '1037425',\n",
      "     '104473',\n",
      "     '104519',\n",
      "     '104962',\n",
      "     '1051550',\n",
      "     '1058308',\n",
      "     '105839',\n",
      "     '1058666',\n",
      "     '10786',\n",
      "     '108295',\n",
      "     '1083136',\n",
      "     '1086615',\n",
      "     '1087647',\n",
      "     '1089595',\n",
      "     '109035',\n",
      "     '10927',\n",
      "     '109925',\n",
      "     '110046',\n",
      "     '110126',\n",
      "     '110263',\n",
      "     '1103752',\n",
      "     '110639',\n",
      "     '1108251',\n",
      "     '1110270',\n",
      "     '1110420',\n",
      "     '11126',\n",
      "     '1118605',\n",
      "     '113450',\n",
      "     '1139460',\n",
      "     '114161',\n",
      "     '114388',\n",
      "     '1149057',\n",
      "     '11515',\n",
      "     '115918',\n",
      "     '116044',\n",
      "     '1161073',\n",
      "     '1164',\n",
      "     '118021',\n",
      "     '1180961',\n",
      "     '118134',\n",
      "     '1185729',\n",
      "     '118613',\n",
      "     '118856',\n",
      "     '1198074',\n",
      "     '1199709',\n",
      "     '120468',\n",
      "     '1207207',\n",
      "     '1208453',\n",
      "     '121241',\n",
      "     '1214881',\n",
      "     '1219875',\n",
      "     '1220608',\n",
      "     '1221146',\n",
      "     '1224467',\n",
      "     '1224855',\n",
      "     '1226539',\n",
      "     '1228157',\n",
      "     '1228570',\n",
      "     '12290',\n",
      "     '1229602',\n",
      "     '1231281',\n",
      "     '1231442',\n",
      "     '1233147',\n",
      "     '1233790',\n",
      "     '1235909',\n",
      "     '123633',\n",
      "     '1237667',\n",
      "     '1240849',\n",
      "     '1241595',\n",
      "     '1242670',\n",
      "     '12434',\n",
      "     '1244807',\n",
      "     '1246157',\n",
      "     '1249205',\n",
      "     '12551',\n",
      "     '125718',\n",
      "     '125832',\n",
      "     '1260280',\n",
      "     '1260654',\n",
      "     '1262396',\n",
      "     '128017',\n",
      "     '128186',\n",
      "     '128400',\n",
      "     '1287482',\n",
      "     '1291124',\n",
      "     '12919',\n",
      "     '1295',\n",
      "     '129601',\n",
      "     '129614',\n",
      "     '12982',\n",
      "     '1306',\n",
      "     '13064',\n",
      "     '131059',\n",
      "     '13192',\n",
      "     '1322784',\n",
      "     '1323363',\n",
      "     '132444',\n",
      "     '1329572',\n",
      "     '1329605',\n",
      "     '1330847',\n",
      "     '13354',\n",
      "     '1346422',\n",
      "     '1351105',\n",
      "     '135163',\n",
      "     '135329',\n",
      "     '135584',\n",
      "     '1355899',\n",
      "     '136804',\n",
      "     '13712',\n",
      "     '1376118',\n",
      "     '1377922',\n",
      "     '1378381',\n",
      "     '13832',\n",
      "     '1393848',\n",
      "     '13957',\n",
      "     '139823',\n",
      "     '1408522',\n",
      "     '1418137',\n",
      "     '1420318',\n",
      "     '1424112',\n",
      "     '142776',\n",
      "     '1429220',\n",
      "     '14293',\n",
      "     '1430450',\n",
      "     '1431737',\n",
      "     '143523',\n",
      "     '143552',\n",
      "     '143581',\n",
      "     '1443767',\n",
      "     '1443840',\n",
      "     '144475',\n",
      "     '145231',\n",
      "     '146223',\n",
      "     '146680',\n",
      "     '147128',\n",
      "     '1472823',\n",
      "     '147510',\n",
      "     '1480053',\n",
      "     '14850',\n",
      "     '1486839',\n",
      "     '148776',\n",
      "     '1494110',\n",
      "     '1501130',\n",
      "     '1501513',\n",
      "     '15046',\n",
      "     '150845',\n",
      "     '15110',\n",
      "     '1513426',\n",
      "     '15188',\n",
      "     '152074',\n",
      "     '152289',\n",
      "     '1526712',\n",
      "     '1532579',\n",
      "     '1533581',\n",
      "     '1533973',\n",
      "     '1535212',\n",
      "     '15363',\n",
      "     '1536592',\n",
      "     '153669',\n",
      "     '153711',\n",
      "     '1538524',\n",
      "     '1539368',\n",
      "     '15398',\n",
      "     '15399',\n",
      "     '154164',\n",
      "     '1544270',\n",
      "     '154991',\n",
      "     '155066',\n",
      "     '1554107',\n",
      "     '15565',\n",
      "     '1562797',\n",
      "     '156352',\n",
      "     '156472',\n",
      "     '157279',\n",
      "     '1572870',\n",
      "     '157964',\n",
      "     '158194',\n",
      "     '158204',\n",
      "     '159484',\n",
      "     '1597155',\n",
      "     '160651',\n",
      "     '160949',\n",
      "     '1611',\n",
      "     '161269',\n",
      "     '161619',\n",
      "     '1617449',\n",
      "     '1618238',\n",
      "     '161951',\n",
      "     '161956',\n",
      "     '162340',\n",
      "     '1624486',\n",
      "     '1625302',\n",
      "     '163396',\n",
      "     '1635487',\n",
      "     '165',\n",
      "     '165061',\n",
      "     '1651798',\n",
      "     '1654744',\n",
      "     '1657236',\n",
      "     '1658142',\n",
      "     '1658796',\n",
      "     '16591',\n",
      "     '166129',\n",
      "     '1664257',\n",
      "     '167009',\n",
      "     '16703',\n",
      "     '167766',\n",
      "     '1679',\n",
      "     '1679807',\n",
      "     '168517',\n",
      "     '1687166',\n",
      "     '168862',\n",
      "     '169004',\n",
      "     '1691594',\n",
      "     '1694420',\n",
      "     '1694880',\n",
      "     '169588',\n",
      "     '1701777',\n",
      "     '1701968',\n",
      "     '170672',\n",
      "     '1707864',\n",
      "     '170813',\n",
      "     '1709796',\n",
      "     '1712071',\n",
      "     '1712104',\n",
      "     '1713378',\n",
      "     '1714220',\n",
      "     '1714996',\n",
      "     '171516',\n",
      "     '171863',\n",
      "     '172061',\n",
      "     '1720686',\n",
      "     '172147',\n",
      "     '1722904',\n",
      "     '1723831',\n",
      "     '1727935',\n",
      "     '1730020',\n",
      "     '1737031',\n",
      "     '173918',\n",
      "     '1741536',\n",
      "     '1741873',\n",
      "     '1743273',\n",
      "     '1745406',\n",
      "     '1746883',\n",
      "     '175106',\n",
      "     '1751737',\n",
      "     '1753914',\n",
      "     '175600',\n",
      "     '1761541',\n",
      "     '1764143',\n",
      "     '1770310',\n",
      "     '1774559',\n",
      "     '1775443',\n",
      "     '1775467',\n",
      "     '1778799',\n",
      "     '1781102',\n",
      "     '1781376',\n",
      "     '1781548',\n",
      "     '178186',\n",
      "     '1783785',\n",
      "     '17857',\n",
      "     '1787202',\n",
      "     '1792214',\n",
      "     '1792536',\n",
      "     '1795336',\n",
      "     '179540',\n",
      "     '179778',\n",
      "     '1802220',\n",
      "     '180306',\n",
      "     '18069',\n",
      "     '181135',\n",
      "     '18130',\n",
      "     '1814943',\n",
      "     '1818750',\n",
      "     '1818767',\n",
      "     '1819820',\n",
      "     '182051',\n",
      "     '1824494',\n",
      "     '1824703',\n",
      "     '1828084',\n",
      "     '1828630',\n",
      "     '1829683',\n",
      "     '1832883',\n",
      "     '183438',\n",
      "     '1836471',\n",
      "     '1836535',\n",
      "     '1836606',\n",
      "     '1848771',\n",
      "     '1851632',\n",
      "     '1852',\n",
      "     '1852804',\n",
      "     '186280',\n",
      "     '1869375',\n",
      "     '1869781',\n",
      "     '1870715',\n",
      "     '1870921',\n",
      "     '1874660',\n",
      "     '187663',\n",
      "     '1881064',\n",
      "     '1883292',\n",
      "     '1884523',\n",
      "     '1889256',\n",
      "     '1889474',\n",
      "     '1897995',\n",
      "     '190049',\n",
      "     '190058',\n",
      "     '1901542',\n",
      "     '190586',\n",
      "     '190869',\n",
      "     '1910146',\n",
      "     '1910302',\n",
      "     '191112',\n",
      "     '1911765',\n",
      "     '1917149',\n",
      "     '1917340',\n",
      "     '1922512',\n",
      "     '193314',\n",
      "     '1937241',\n",
      "     '193793',\n",
      "     '1943392',\n",
      "     '1943612',\n",
      "     '1945775',\n",
      "     '1946151',\n",
      "     '194751',\n",
      "     '19488',\n",
      "     '19492',\n",
      "     '1949804',\n",
      "     '1952163',\n",
      "     '1953942',\n",
      "     '195702',\n",
      "     '1960024',\n",
      "     '1962477',\n",
      "     '1963346',\n",
      "     '1964269',\n",
      "     '1965231',\n",
      "     '1965944',\n",
      "     '196617',\n",
      "     '1968073',\n",
      "     '1968361',\n",
      "     '1974064',\n",
      "     '1975220',\n",
      "     '1976131',\n",
      "     '1977566',\n",
      "     '1977824',\n",
      "     '1978092',\n",
      "     '198131',\n",
      "     '1989706',\n",
      "     '1996319',\n",
      "     '1996396',\n",
      "     '200922',\n",
      "     '2009571',\n",
      "     '2012449',\n",
      "     '2012536',\n",
      "     '201359',\n",
      "     '2013694',\n",
      "     '2014354',\n",
      "     '2020631',\n",
      "     '2021994',\n",
      "     '202615',\n",
      "     '2026961',\n",
      "     '2030462',\n",
      "     '2030703',\n",
      "     '203933',\n",
      "     '204091',\n",
      "     '2041119',\n",
      "     '2043489',\n",
      "     '2043803',\n",
      "     '2044446',\n",
      "     '2046201',\n",
      "     '2046386',\n",
      "     '2049113',\n",
      "     '2049202',\n",
      "     '204929',\n",
      "     '205286',\n",
      "     '2053369',\n",
      "     '205523',\n",
      "     '205715',\n",
      "     '2058761',\n",
      "     '2059117',\n",
      "     '2061537',\n",
      "     '2064884',\n",
      "     '2065004',\n",
      "     '206828',\n",
      "     '2069261',\n",
      "     '207530',\n",
      "     '207581',\n",
      "     '2077000',\n",
      "     '207745',\n",
      "     '2078243',\n",
      "     '2081420',\n",
      "     '2082406',\n",
      "     '2082922',\n",
      "     '2082952',\n",
      "     '2082955',\n",
      "     '2082956',\n",
      "     '2089542',\n",
      "     '2089916',\n",
      "     '2091991',\n",
      "     '2092270',\n",
      "     '2110575',\n",
      "     '2115749',\n",
      "     '21162',\n",
      "     '2118267',\n",
      "     '2118269',\n",
      "     '212111',\n",
      "     '2124804',\n",
      "     '2130955',\n",
      "     '2137840',\n",
      "     '213831',\n",
      "     '2142541',\n",
      "     '214436',\n",
      "     '2149259',\n",
      "     '214969',\n",
      "     '215415',\n",
      "     '217070',\n",
      "     '2171932',\n",
      "     '2172146',\n",
      "     '2177834',\n",
      "     '218114',\n",
      "     '2185972',\n",
      "     '2188478',\n",
      "     '21931',\n",
      "     '220142',\n",
      "     '2209894',\n",
      "     '2219338',\n",
      "     '2221437',\n",
      "     '2226712',\n",
      "     '2233024',\n",
      "     '22347',\n",
      "     '2237255',\n",
      "     '2239369',\n",
      "     '224299',\n",
      "     '224440',\n",
      "     '224610',\n",
      "     '2246679',\n",
      "     '224735',\n",
      "     '224769',\n",
      "     '2252736',\n",
      "     '2254403',\n",
      "     '2259393',\n",
      "     '225954',\n",
      "     '2260247',\n",
      "     '2265424',\n",
      "     '226792',\n",
      "     '2268702',\n",
      "     '22714',\n",
      "     '2272922',\n",
      "     '2285494',\n",
      "     '2292324',\n",
      "     '22925',\n",
      "     '231017',\n",
      "     '231539',\n",
      "     '2318',\n",
      "     '232126',\n",
      "     '2325966',\n",
      "     '2328196',\n",
      "     '233008',\n",
      "     '233248',\n",
      "     '23351',\n",
      "     '2339154',\n",
      "     '2347543',\n",
      "     '2349071',\n",
      "     '2359957',\n",
      "     '236361',\n",
      "     '2373351',\n",
      "     '237525',\n",
      "     '2376',\n",
      "     '2377377',\n",
      "     '2377845',\n",
      "     '238427',\n",
      "     '238898',\n",
      "     '2389411',\n",
      "     '2394474',\n",
      "     '2397957',\n",
      "     '240041',\n",
      "     '2406578',\n",
      "     '2407695',\n",
      "     '24126',\n",
      "     '2414506',\n",
      "     '24186',\n",
      "     '2420807',\n",
      "     '242247',\n",
      "     '2422685',\n",
      "     '243003',\n",
      "     '2431363',\n",
      "     '2434203',\n",
      "     '243857',\n",
      "     '2441744',\n",
      "     '2441939',\n",
      "     '24440',\n",
      "     '2445224',\n",
      "     '2460323',\n",
      "     '2466404',\n",
      "     '2471547',\n",
      "     '24748',\n",
      "     '2475423',\n",
      "     '2475956',\n",
      "     '2478005',\n",
      "     '247879',\n",
      "     '2480143',\n",
      "     '2487125',\n",
      "     '2491209',\n",
      "     '24942',\n",
      "     '249634',\n",
      "     '2497300',\n",
      "     '250203',\n",
      "     '250244',\n",
      "     '250487',\n",
      "     '2508151',\n",
      "     '2511706',\n",
      "     '2512300',\n",
      "     '2512685',\n",
      "     '251543',\n",
      "     '2525017',\n",
      "     '2527838',\n",
      "     '252811',\n",
      "     '2530185',\n",
      "     '2533093',\n",
      "     '2545951',\n",
      "     '2550164',\n",
      "     '2554698',\n",
      "     '25604',\n",
      "     '256900',\n",
      "     '2570392',\n",
      "     '25867',\n",
      "     '2589497',\n",
      "     '2590886',\n",
      "     '2608556',\n",
      "     '262151',\n",
      "     '26299',\n",
      "     '26348',\n",
      "     '2644101',\n",
      "     '2652868',\n",
      "     '2655992',\n",
      "     '26599',\n",
      "     '2675',\n",
      "     '2675120',\n",
      "     '2679756',\n",
      "     '2680',\n",
      "     '26818',\n",
      "     '2681832',\n",
      "     '2687819',\n",
      "     '26935',\n",
      "     '2701966',\n",
      "     '2708926',\n",
      "     '2709708',\n",
      "     '2716442',\n",
      "     '2722533',\n",
      "     '2726918',\n",
      "     '2739599',\n",
      "     '2748195',\n",
      "     '2753800',\n",
      "     '2755647',\n",
      "     '2755682',\n",
      "     '2758',\n",
      "     '2769087',\n",
      "     '2773196',\n",
      "     '27742',\n",
      "     '2786592',\n",
      "     '279194',\n",
      "     '2795667',\n",
      "     '2797125',\n",
      "     '28145',\n",
      "     '2815098',\n",
      "     '2817039',\n",
      "     '2820500',\n",
      "     '2823295',\n",
      "     '282922',\n",
      "     '2834724',\n",
      "     '2835928',\n",
      "     '2836458',\n",
      "     '28378',\n",
      "     '2847988',\n",
      "     '2851892',\n",
      "     '28559',\n",
      "     '285692',\n",
      "     '2859402',\n",
      "     '286075',\n",
      "     '2865221',\n",
      "     '2872296',\n",
      "     '28775',\n",
      "     '2877761',\n",
      "     '28783',\n",
      "     '288333',\n",
      "     '28852',\n",
      "     '288883',\n",
      "     '2901303',\n",
      "     '290271',\n",
      "     '2905914',\n",
      "     '290680',\n",
      "     '291016',\n",
      "     '2912297',\n",
      "     '29177',\n",
      "     '292007',\n",
      "     '292130',\n",
      "     '2922647',\n",
      "     '2925215',\n",
      "     '292665',\n",
      "     '2929338',\n",
      "     '2936864',\n",
      "     '2944689',\n",
      "     '2948842',\n",
      "     '2949079',\n",
      "     '2950542',\n",
      "     '2959379',\n",
      "     '2969074',\n",
      "     '297007',\n",
      "     '297093',\n",
      "     '297248',\n",
      "     '2973031',\n",
      "     '297482',\n",
      "     '2976052',\n",
      "     '2979589',\n",
      "     '298201',\n",
      "     '2985232',\n",
      "     '2987661',\n",
      "     '2990136',\n",
      "     '2991673',\n",
      "     '2993102',\n",
      "     '2997078',\n",
      "     '2999927',\n",
      "     '300002',\n",
      "     '300074',\n",
      "     '3003968',\n",
      "     '300459',\n",
      "     '3024482',\n",
      "     '302871',\n",
      "     '3029661',\n",
      "     '3048223',\n",
      "     '30521',\n",
      "     '30554',\n",
      "     '30587',\n",
      "     '305985',\n",
      "     '306962',\n",
      "     '307520',\n",
      "     '3078849',\n",
      "     '308090',\n",
      "     '310589',\n",
      "     '31075',\n",
      "     '3112662',\n",
      "     '31231',\n",
      "     '3125856',\n",
      "     '313192',\n",
      "     '313646',\n",
      "     '3138631',\n",
      "     '3158',\n",
      "     '3164012',\n",
      "     '316560',\n",
      "     '3171667',\n",
      "     '3178375',\n",
      "     '3179897',\n",
      "     '318164',\n",
      "     '3201403',\n",
      "     '3210444',\n",
      "     '3210937',\n",
      "     '3213981',\n",
      "     '3218651',\n",
      "     '322785',\n",
      "     '323955',\n",
      "     '323992',\n",
      "     '325346',\n",
      "     '3258663',\n",
      "     '3265371',\n",
      "     '3272286',\n",
      "     '3274142',\n",
      "     '3277198',\n",
      "     '328596',\n",
      "     '3293843',\n",
      "     '329453',\n",
      "     '32952',\n",
      "     '329764',\n",
      "     '329833',\n",
      "     '3306918',\n",
      "     '3307428',\n",
      "     '330773',\n",
      "     '331548',\n",
      "     '3336568',\n",
      "     '3336884',\n",
      "     '3339436',\n",
      "     '3341187',\n",
      "     '3343200',\n",
      "     '3343450',\n",
      "     '3345658',\n",
      "     '33458',\n",
      "     '334742',\n",
      "     '3354061',\n",
      "     '3362013',\n",
      "     '336460',\n",
      "     '3372065',\n",
      "     '3374094',\n",
      "     '33757',\n",
      "     '338198',\n",
      "     '33916',\n",
      "     '339581',\n",
      "     '339837',\n",
      "     '3402342',\n",
      "     '3406980',\n",
      "     '3422',\n",
      "     '3426010',\n",
      "     '3432400',\n",
      "     '343553',\n",
      "     '34434',\n",
      "     '3447145',\n",
      "     '346',\n",
      "     '3460649',\n",
      "     '3463496',\n",
      "     '3467560',\n",
      "     '3475198',\n",
      "     '3478684',\n",
      "     '3479270',\n",
      "     '3499729',\n",
      "     '3514083',\n",
      "     '3514319',\n",
      "     '351532',\n",
      "     '3516',\n",
      "     '352368',\n",
      "     '3524812',\n",
      "     '3526564',\n",
      "     '355242',\n",
      "     '355639',\n",
      "     '35585',\n",
      "     '3560',\n",
      "     '3572302',\n",
      "     '3573779',\n",
      "     '3582',\n",
      "     '358457',\n",
      "     '358883',\n",
      "     '360392',\n",
      "     '3604386',\n",
      "     '36080',\n",
      "     '36118',\n",
      "     '36121',\n",
      "     '3618405',\n",
      "     '362126',\n",
      "     '36223',\n",
      "     '364184',\n",
      "     '36443',\n",
      "     '365944',\n",
      "     '3663758',\n",
      "     '36694',\n",
      "     '3679897',\n",
      "     '3683645',\n",
      "     '36894',\n",
      "     '3692214',\n",
      "     '369357',\n",
      "     '369983',\n",
      "     '3704486',\n",
      "     '3705240',\n",
      "     '371121',\n",
      "     '37137',\n",
      "     '3726574',\n",
      "     '3732840',\n",
      "     '373434',\n",
      "     '37392',\n",
      "     '3753553',\n",
      "     '3756255',\n",
      "     '3756264',\n",
      "     '3756276',\n",
      "     '3756278',\n",
      "     '3756280',\n",
      "     '3756284',\n",
      "     '375630',\n",
      "     '3769434',\n",
      "     '377036',\n",
      "     '3771923',\n",
      "     '3771927',\n",
      "     '379381',\n",
      "     '3794668',\n",
      "     '3804751',\n",
      "     '3811392',\n",
      "     '3811739',\n",
      "     '381268',\n",
      "     '381516',\n",
      "     '3815770',\n",
      "     '381909',\n",
      "     '383200',\n",
      "     '383843',\n",
      "     '3840911',\n",
      "     '384470',\n",
      "     '3846187',\n",
      "     '38487',\n",
      "     '384894',\n",
      "     '384997',\n",
      "     '3851538',\n",
      "     '3860505',\n",
      "     '3861393',\n",
      "     '38617',\n",
      "     '3875933',\n",
      "     '3877019',\n",
      "     '3877473',\n",
      "     '38784',\n",
      "     '388157',\n",
      "     '389778',\n",
      "     '3908125',\n",
      "     '3913914',\n",
      "     '3920547',\n",
      "     '3922929',\n",
      "     '39330',\n",
      "     '393590',\n",
      "     '3936339',\n",
      "     '39457',\n",
      "     '396743',\n",
      "     '39688',\n",
      "     '3973469',\n",
      "     '398086',\n",
      "     '398300',\n",
      "     '3990',\n",
      "     '401102',\n",
      "     '401490',\n",
      "     '4017750',\n",
      "     '4018842',\n",
      "     '402624',\n",
      "     '40310',\n",
      "     '40397',\n",
      "     '4054',\n",
      "     '40611',\n",
      "     '4074666',\n",
      "     '4076720',\n",
      "     '408202',\n",
      "     '4082127',\n",
      "     '409545',\n",
      "     '409731',\n",
      "     '4099203',\n",
      "     '411672',\n",
      "     '41187',\n",
      "     '413047',\n",
      "     '414225',\n",
      "     '414318',\n",
      "     '4147210',\n",
      "     '4148813',\n",
      "     '4151894',\n",
      "     '415250',\n",
      "     '4162359',\n",
      "     '416346',\n",
      "     '417785',\n",
      "     '4192237',\n",
      "     '419228',\n",
      "     '4208806',\n",
      "     '4210803',\n",
      "     '4210985',\n",
      "     '42123',\n",
      "     '421504',\n",
      "     '4235848',\n",
      "     '42429',\n",
      "     '4257431',\n",
      "     '4261736',\n",
      "     '426815',\n",
      "     '4270327',\n",
      "     '42918',\n",
      "     '4292604',\n",
      "     '430251',\n",
      "     '43058',\n",
      "     '4310364',\n",
      "     '4314838',\n",
      "     '43158',\n",
      "     '4319107',\n",
      "     '4321324',\n",
      "     '434941',\n",
      "     '436365',\n",
      "     '436419',\n",
      "     '437322',\n",
      "     '437598',\n",
      "     '43972',\n",
      "     '439947',\n",
      "     '4418736',\n",
      "     '44200',\n",
      "     '44217',\n",
      "     '44221',\n",
      "     '443130',\n",
      "     '44583',\n",
      "     '44659',\n",
      "     '4470921',\n",
      "     '4476589',\n",
      "     '447903',\n",
      "     '4499',\n",
      "     '45027',\n",
      "     '4503344',\n",
      "     '451048',\n",
      "     '4510496',\n",
      "     '4516575',\n",
      "     '451693',\n",
      "     '4520785',\n",
      "     '45243',\n",
      "     '45439',\n",
      "     '454589',\n",
      "     '454771',\n",
      "     '4549329',\n",
      "     '456615',\n",
      "     '456653',\n",
      "     '4579860',\n",
      "     '4592476',\n",
      "     '45952',\n",
      "     '4609280',\n",
      "     '4615469',\n",
      "     '46166',\n",
      "     '461945',\n",
      "     '463079',\n",
      "     '463504',\n",
      "     '463545',\n",
      "     '464091',\n",
      "     '4647004',\n",
      "     '465572',\n",
      "     '466464',\n",
      "     '4683551',\n",
      "     '4697',\n",
      "     '470034',\n",
      "     '470089',\n",
      "     '4713657',\n",
      "     '472018',\n",
      "     '4725451',\n",
      "     '4725672',\n",
      "     '4729128',\n",
      "     '472957',\n",
      "     '4732',\n",
      "     '473471',\n",
      "     '4735674',\n",
      "     '474022',\n",
      "     '474051',\n",
      "     '4742102',\n",
      "     '4744872',\n",
      "     '4751775',\n",
      "     '4752218',\n",
      "     '47544',\n",
      "     '4758422',\n",
      "     '475877',\n",
      "     '4760851',\n",
      "     '4772327',\n",
      "     '4777783',\n",
      "     '4779475',\n",
      "     '4785239',\n",
      "     '4788292',\n",
      "     '4788987',\n",
      "     '47892',\n",
      "     '4790743',\n",
      "     '4790894',\n",
      "     '4791397',\n",
      "     '4791574',\n",
      "     '4794357',\n",
      "     '4794789',\n",
      "     '4801391',\n",
      "     '4803525',\n",
      "     '4804060',\n",
      "     '4813275',\n",
      "     '4833578',\n",
      "     '4847896',\n",
      "     '4851465',\n",
      "     '4857016',\n",
      "     '4861522',\n",
      "     '4883331',\n",
      "     '4887604',\n",
      "     '4895877',\n",
      "     '4895880',\n",
      "     '489713',\n",
      "     '4901820',\n",
      "     '49031',\n",
      "     '4903321',\n",
      "     '4905857',\n",
      "     '4907428',\n",
      "     '492311',\n",
      "     '492418',\n",
      "     '4928221',\n",
      "     '4930513',\n",
      "     '49351',\n",
      "     '4935228',\n",
      "     '4944637',\n",
      "     '4945357',\n",
      "     '4946781',\n",
      "     '4951',\n",
      "     '495567',\n",
      "     '4957623',\n",
      "     '4961744',\n",
      "     '4963402',\n",
      "     '4968009',\n",
      "     '496923',\n",
      "     '4971608',\n",
      "     '4977848',\n",
      "     '497797',\n",
      "     '497849',\n",
      "     '4981209',\n",
      "     '49891',\n",
      "     '4990152',\n",
      "     '4994101',\n",
      "     '4999904',\n",
      "     '5001079',\n",
      "     '5001819',\n",
      "     '5007506',\n",
      "     '5008032',\n",
      "     '5009628',\n",
      "     '5009653',\n",
      "     '5009674',\n",
      "     '5010113',\n",
      "     '5010181',\n",
      "     '5010314',\n",
      "     '5011580',\n",
      "     '5011990',\n",
      "     '5012448',\n",
      "     '5012793',\n",
      "     '5013088',\n",
      "     '5014702',\n",
      "     '5021362',\n",
      "     '502142',\n",
      "     '5023880',\n",
      "     '5030411',\n",
      "     '5030592',\n",
      "     '503649',\n",
      "     '5038565',\n",
      "     '5040662',\n",
      "     '5041321',\n",
      "     '5046769',\n",
      "     '5047926',\n",
      "     '5048349',\n",
      "     '5050789',\n",
      "     '5051929',\n",
      "     '5056255',\n",
      "     '5065661',\n",
      "     '5067891',\n",
      "     '5072908',\n",
      "     '50743',\n",
      "     '5074679',\n",
      "     '5080625',\n",
      "     '5080656',\n",
      "     '5084581',\n",
      "     '5084940',\n",
      "     '5086129',\n",
      "     '5088464',\n",
      "     '508894',\n",
      "     '5093330',\n",
      "     '5100070',\n",
      "     '5100758',\n",
      "     '5101541',\n",
      "     '5104090',\n",
      "     '51045',\n",
      "     '5106705',\n",
      "     '510836',\n",
      "     '5110222',\n",
      "     '5112112',\n",
      "     '5120701',\n",
      "     '5128370',\n",
      "     '5134325',\n",
      "     '5134665',\n",
      "     '5135636',\n",
      "     '5135948',\n",
      "     '5136014',\n",
      "     '5136683',\n",
      "     '5158027',\n",
      "     '5164',\n",
      "     '517163',\n",
      "     '5176713',\n",
      "     '5177691',\n",
      "     '5179081',\n",
      "     '5181629',\n",
      "     '5182055',\n",
      "     '5183399',\n",
      "     '5183596',\n",
      "     '5183611',\n",
      "     '5183618',\n",
      "     '5184107',\n",
      "     '5184319',\n",
      "     '5184361',\n",
      "     '5184516',\n",
      "     '5184568',\n",
      "     '5184614',\n",
      "     '5185577',\n",
      "     '5185857',\n",
      "     '5188896',\n",
      "     '5191371',\n",
      "     '519187',\n",
      "     '5192581',\n",
      "     '5198671',\n",
      "     '5200458',\n",
      "     '5200631',\n",
      "     '5200703',\n",
      "     '5200907',\n",
      "     '5201068',\n",
      "     '5201621',\n",
      "     '5201990',\n",
      "     '520239',\n",
      "     '5203065',\n",
      "     '5203599',\n",
      "     '52087',\n",
      "     '5210517',\n",
      "     '5210936',\n",
      "     '5211',\n",
      "     '5211641',\n",
      "     '5212422',\n",
      "     '5213676',\n",
      "     '5216534',\n",
      "     '5216583',\n",
      "     '5224491',\n",
      "     '5224956',\n",
      "     '5228143',\n",
      "     '522978',\n",
      "     '5234632',\n",
      "     '5235394',\n",
      "     '5237811',\n",
      "     '524500',\n",
      "     '5253671',\n",
      "     '5261447',\n",
      "     '5265',\n",
      "     '527637',\n",
      "     '527721',\n",
      "     '5279102',\n",
      "     '52797',\n",
      "     '528681',\n",
      "     '52898',\n",
      "     '5290627',\n",
      "     '52921',\n",
      "     '529266',\n",
      "     '5303865',\n",
      "     '53073',\n",
      "     '5311621',\n",
      "     '53127',\n",
      "     '5318237',\n",
      "     '532166',\n",
      "     '5322638',\n",
      "     '5326001',\n",
      "     '5329239',\n",
      "     '5333665',\n",
      "     '533465',\n",
      "     '5356334',\n",
      "     '5356397',\n",
      "     '5359',\n",
      "     '5360059',\n",
      "     '5360464',\n",
      "     '5376238',\n",
      "     '53848',\n",
      "     '5389220',\n",
      "     '542403',\n",
      "     '542550',\n",
      "     '5429251',\n",
      "     '5429292',\n",
      "     '5432649',\n",
      "     '5442248',\n",
      "     '5446055',\n",
      "     '544905',\n",
      "     '5451813',\n",
      "     '5451915',\n",
      "     '5452926',\n",
      "     '5453',\n",
      "     '5455941',\n",
      "     '5456532',\n",
      "     '5458434',\n",
      "     '5458583',\n",
      "     '5463108',\n",
      "     '5463165',\n",
      "     '5465466',\n",
      "     '5469664',\n",
      "     '547150',\n",
      "     '5474195',\n",
      "     '54759',\n",
      "     '547611',\n",
      "     '5477993',\n",
      "     '547810',\n",
      "     '548110',\n",
      "     '5485239',\n",
      "     '5486215',\n",
      "     '548814',\n",
      "     '548817',\n",
      "     '548838',\n",
      "     '5491509',\n",
      "     '5496112',\n",
      "     '549740',\n",
      "     '5509234',\n",
      "     '5517767',\n",
      "     '552488',\n",
      "     '5525357',\n",
      "     '5525381',\n",
      "     '5525586',\n",
      "     '5530641',\n",
      "     '554234',\n",
      "     '554739',\n",
      "     '5566',\n",
      "     '55744',\n",
      "     '557948',\n",
      "     '55916',\n",
      "     '55945',\n",
      "     '560392',\n",
      "     '5605937',\n",
      "     '5611009',\n",
      "     '5615685',\n",
      "     '5636384',\n",
      "     '564585',\n",
      "     '564990',\n",
      "     '5650408',\n",
      "     '565330',\n",
      "     '5654329',\n",
      "     '5655187',\n",
      "     '5659338',\n",
      "     '56597',\n",
      "     '5659851',\n",
      "     '5665613',\n",
      "     '5666287',\n",
      "     '5667194',\n",
      "     '5672740',\n",
      "     '567344',\n",
      "     '567601',\n",
      "     '5677293',\n",
      "     '567792',\n",
      "     '5691581',\n",
      "     '5702420',\n",
      "     '57158',\n",
      "     '5722240',\n",
      "     '5736775',\n",
      "     '573868',\n",
      "     '5739145',\n",
      "     '5739151',\n",
      "     '5743361',\n",
      "     '5745260',\n",
      "     '5746013',\n",
      "     '574654',\n",
      "     '5748',\n",
      "     '575489',\n",
      "     '575547',\n",
      "     '57577',\n",
      "     '575916',\n",
      "     '576690',\n",
      "     '577598',\n",
      "     '5777101',\n",
      "     '577963',\n",
      "     '57884',\n",
      "     '579545',\n",
      "     '580469',\n",
      "     '580516',\n",
      "     '5829972',\n",
      "     '584970',\n",
      "     '585369',\n",
      "     '5873841',\n",
      "     '5877493',\n",
      "     '5881340',\n",
      "     '5901499',\n",
      "     '590608',\n",
      "     '592318',\n",
      "     '5959805',\n",
      "     '596047',\n",
      "     '597064',\n",
      "     '5972779',\n",
      "     '598421',\n",
      "     '598555',\n",
      "     '599436',\n",
      "     '5999306',\n",
      "     '60110',\n",
      "     '60244',\n",
      "     '603433',\n",
      "     '603503',\n",
      "     '6037763',\n",
      "     '6058023',\n",
      "     '6061529',\n",
      "     '6064630',\n",
      "     '6069607',\n",
      "     '6081665',\n",
      "     '608293',\n",
      "     '6093323',\n",
      "     '6099473',\n",
      "     '6134757',\n",
      "     '6135253',\n",
      "     '6137407',\n",
      "     '6137831',\n",
      "     '614144',\n",
      "     '6143780',\n",
      "     '614447',\n",
      "     '6146672',\n",
      "     '6155257',\n",
      "     '616774',\n",
      "     '6167797',\n",
      "     '6172493',\n",
      "     '617465',\n",
      "     '6178640',\n",
      "     '618377',\n",
      "     '6184286',\n",
      "     '6186031',\n",
      "     '618879',\n",
      "     '6192772',\n",
      "     '619935',\n",
      "     '6199409',\n",
      "     '6201555',\n",
      "     '6203366',\n",
      "     '6205707',\n",
      "     '620875',\n",
      "     '6209114',\n",
      "     '621202',\n",
      "     '6221440',\n",
      "     '6228729',\n",
      "     '6236143',\n",
      "     '6248520',\n",
      "     '6265698',\n",
      "     '6272976',\n",
      "     '627906',\n",
      "     '62882',\n",
      "     '629997',\n",
      "     '630068',\n",
      "     '6301742',\n",
      "     '6302534',\n",
      "     '63107',\n",
      "     '631081',\n",
      "     '6311049',\n",
      "     '632378',\n",
      "     '6344501',\n",
      "     '634665',\n",
      "     '634801',\n",
      "     '635799',\n",
      "     '6364376',\n",
      "     '6367',\n",
      "     '6368023',\n",
      "     '6377631',\n",
      "     '638117',\n",
      "     '64433',\n",
      "     '645457',\n",
      "     '645538',\n",
      "     '6459672',\n",
      "     '6460266',\n",
      "     '6465525',\n",
      "     '6473380',\n",
      "     '647541',\n",
      "     '6478562',\n",
      "     '6478844',\n",
      "     '6482483',\n",
      "     '65148',\n",
      "     '653825',\n",
      "     '663559',\n",
      "     '66391',\n",
      "     '664245',\n",
      "     '665599',\n",
      "     '665694',\n",
      "     '665748',\n",
      "     '66590',\n",
      "     '671445',\n",
      "     '67236',\n",
      "     '67862',\n",
      "     '679231',\n",
      "     '67931',\n",
      "     '680121',\n",
      "     '680586',\n",
      "     '684153',\n",
      "     '6871',\n",
      "     '68982',\n",
      "     '693098',\n",
      "     '693455',\n",
      "     '694726',\n",
      "     '6975',\n",
      "     '699357',\n",
      "     '701297',\n",
      "     '703517',\n",
      "     '703657',\n",
      "     '7037',\n",
      "     '704753',\n",
      "     '7050',\n",
      "     '705430',\n",
      "     '70696',\n",
      "     '70742',\n",
      "     '7079',\n",
      "     '709444',\n",
      "     '709577',\n",
      "     '710342',\n",
      "     '71261',\n",
      "     '712900',\n",
      "     '714898',\n",
      "     '71631',\n",
      "     '7168',\n",
      "     '717239',\n",
      "     '718617',\n",
      "     '718821',\n",
      "     '722137',\n",
      "     '72235',\n",
      "     '725620',\n",
      "     '726972',\n",
      "     '7320',\n",
      "     '73405',\n",
      "     '735',\n",
      "     '73509',\n",
      "     '745841',\n",
      "     '74900',\n",
      "     '74937',\n",
      "     '75317',\n",
      "     '7591',\n",
      "     '76002',\n",
      "     '76077',\n",
      "     '76226',\n",
      "     '7630',\n",
      "     '764191',\n",
      "     '77109',\n",
      "     '775666',\n",
      "     '777165',\n",
      "     '7787',\n",
      "     '78656',\n",
      "     '78918',\n",
      "     '79933',\n",
      "     '80046',\n",
      "     '80180',\n",
      "     '80285',\n",
      "     '81285',\n",
      "     '82317',\n",
      "     '82820',\n",
      "     '82848',\n",
      "     '8287',\n",
      "     '834480',\n",
      "     '83501',\n",
      "     '837066',\n",
      "     '837425',\n",
      "     '84054',\n",
      "     '84681',\n",
      "     '84853',\n",
      "     '85218',\n",
      "     '85314',\n",
      "     '86',\n",
      "     '86504',\n",
      "     '8703',\n",
      "     '8748',\n",
      "     '8764',\n",
      "     '880537',\n",
      "     '88186',\n",
      "     '8909',\n",
      "     '89098',\n",
      "     '898509',\n",
      "     '90477',\n",
      "     '905898',\n",
      "     '906987',\n",
      "     '90943',\n",
      "     '91490',\n",
      "     '91499',\n",
      "     '9176',\n",
      "     '9189',\n",
      "     '92274',\n",
      "     '92286',\n",
      "     '92387',\n",
      "     '936345',\n",
      "     '93679',\n",
      "     '93788',\n",
      "     '943212',\n",
      "     '94578',\n",
      "     '950182',\n",
      "     '951623',\n",
      "     '952041',\n",
      "     '95293',\n",
      "     '95387',\n",
      "     '957',\n",
      "     '96069',\n",
      "     '9607',\n",
      "     '967832',\n",
      "     '97431',\n",
      "     '982168',\n",
      "     '99476',\n",
      "     '999706',\n",
      "     '99999',\n",
      "     '999995'])\n"
     ]
    }
   ],
   "source": [
    "users = process_map(OSMFILE)\n",
    "pprint.pprint(users)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Audit\n",
    "Audit and clean data with audit.py"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#%%writefile audit.py\n",
    "\n",
    "#!/usr/bin/env python\n",
    "import xml.etree.cElementTree as ET\n",
    "from collections import defaultdict\n",
    "import re\n",
    "\n",
    "\n",
    "\"\"\"\n",
    "Your task in this exercise has two steps:\n",
    "- audit the OSMFILE and change the variable 'mapping' to reflect the changes needed to fix\n",
    "    the unexpected street types to the appropriate ones in the expected list.\n",
    "    You have to add mappings only for the actual problems you find in this OSMFILE,\n",
    "    not a generalized solution, since that may and will depend on the particular area you are auditing.\n",
    "- write the update_name function, to actually fix the street name.\n",
    "    The function takes a string with street name as an argument and should return the fixed name\n",
    "    We have provided a simple test so that you see what exactly is expected\n",
    "\"\"\"\n",
    "\n",
    "street_type_re = re.compile(r'\\b\\S+\\.?$', re.IGNORECASE)\n",
    "direction_re = re.compile(r'\\w*(North|South|East|West|Northeast|Northwest|Southeast|Southwest|S|NE|W|N|E|SE|N.)$')\n",
    "\n",
    "#the mapping from abbreviated direction names to full direction names\n",
    "direction_mapping = {\"N\":\"North\",\n",
    "                    \"S\":\"South\",\n",
    "                    \"NE\":\"Northeast\",\n",
    "                    \"W\":\"West\",\n",
    "                    \"E\":\"East\",\n",
    "                    \"SE\": \"Southeast\"}\n",
    "\n",
    "#the expected street types in the data set\n",
    "expected = [\"Passage\",\"Cutoff\",\"Bridge\",\"Crossing\",\"Lane\",\"Way\",\"Run\",\"Loop\",\"Plaza\",\"Causeway\",\"Terrace\",\"Highway\",\"Bayway\",\"Circle\",\"Street\", \"Avenue\", \"Boulevard\", \"Drive\", \"Court\", \"Place\", \"Square\", \"Lane\", \"Road\",\n",
    "            \"Trail\", \"Parkway\", \"Commons\"]\n",
    "\n",
    "#the mapping from problem cities encountered to their corrected values\n",
    "city_mapping = {'St. Petersburg, FL':'St. Petersburg',\n",
    "                'St Petersburg ': 'St. Petersburg',\n",
    "                'St Pete Beach': 'St. Pete Beach',\n",
    "                'SPRING HILL': 'Spring Hill',\n",
    "                'sarasota': 'Sarasota',\n",
    "                'St Petersburg': 'St. Petersburg',\n",
    "                'lutz': 'Lutz',\n",
    "                'spring hill': 'Spring Hill',\n",
    "                'Zephyhills': 'Zephyrhills',\n",
    "                'port richey': 'Port Richey',\n",
    "                'Miakka': 'Old Myakka',\n",
    "                'Saint Petersburg': 'St. Petersburg',\n",
    "                'Seminole ': 'Seminole',\n",
    "                'Land O Lakes': \"Land O' Lakes\",\n",
    "                'Tampa ': 'Tampa',\n",
    "                'St Petersbug': 'St. Petersburg',\n",
    "                'hudson': 'Hudson',\n",
    "                'Land O Lakes, FL': \"Land O' Lakes\",\n",
    "                'Clearwarer Beach': 'Clearwater Beach',\n",
    "                'Palm Harbor, Fl.': 'Palm Harbor',\n",
    "                'tampa': 'Tampa'}\n",
    "\n",
    "#the expected cities in the data set\n",
    "cities = ['Indian Shores',\"Land O' Lakes\", 'Pinellas Park',\n",
    "          'Largo', 'Trinity', 'St. Petersburg', 'Bay Pines',\n",
    "          'Treasure Island', 'Indian Rocks Beach', 'Apollo Beach',\n",
    "          'Palm Harbor', 'Temple Terrace','Tampa', 'St. Pete Beach',\n",
    "          'Lakeland', 'Old Myakka', 'Plant City','Dunedin', 'South Highpoint', \n",
    "          'Madeira Beach', 'Gulfport', 'Lakewood Ranch', 'Longboat Key', 'Brandon',\n",
    "          'Clearwater Beach', 'Verna', 'Seminole', 'Dade City', 'Feather Sound', 'Redington Shores',\n",
    "          'Gandy', 'South Pasadena', 'Cortez Village', 'Safety Harbor', 'San Antonio',\n",
    "          'Anna Maria', 'Bradenton Beach', 'Palm Harbor',\n",
    "          'Wesley Chapel', 'Tarpon Springs', 'New Port Richey',\n",
    "          'St. Petersburg', 'Port Richey', 'Clearwater', 'Pasadena', 'Redington Beach',\n",
    "          'Holiday', 'Sarasota', 'Lutz', 'Wimauma', 'Parrish', 'Zephyrhills', 'Shady Hills',\n",
    "          'Thonotosassa', 'Belleair', 'Belleair Beach', 'Ellenton', 'Ruskin', 'Oldsmar',\n",
    "          'Valrico', 'Kenneth City', 'Hudson', 'Riverview',\n",
    "          'Bradenton', 'Odessa', 'Gibsonton', 'Lithia',\n",
    "          'Palmetto', 'Pass-a-Grille Beach', 'Spring Hill',\n",
    "          'Holmes Beach', 'Dover', 'Seffner', 'Sun City Center', 'Saint Leo']\n",
    "\n",
    "#the mapping from abbreviated street types to the full, corrected type\n",
    "mapping = { \"St\": \"Street\",\n",
    "            \"St.\": \"Street\",\n",
    "            \"Ave\": \"Avenue\",\n",
    "            \"Rd\": \"Road\",\n",
    "            \"Rd.\": \"Road\",\n",
    "            \"Dr\": \"Drive\",\n",
    "            \"Av\": \"Avenue\",\n",
    "            \"AVE\": \"Avenue\",\n",
    "            \"Blvd\": \"Boulevard\",\n",
    "            \"Cir\": \"Circle\",\n",
    "            \"Hwy\": \"Highway\",\n",
    "            \"Blvd.\": \"Boulevard\",\n",
    "            \"Pkwy\": \"Parkway\",\n",
    "            \"dr\": \"Drive\",\n",
    "            \"Dr.\": \"Drive\",\n",
    "            \"Ave.\": \"Avenue\",\n",
    "            \"Pl\": \"Place\",\n",
    "            \"Cswy\": \"Causeway\",\n",
    "            \"Plz\": \"Plaza\",\n",
    "            \"Ct\": \"Court\",\n",
    "            \"Pky\": \"Parkway\",\n",
    "            \"Ln\": \"Lane\",\n",
    "            \"st\": \"Street\",\n",
    "            \"road\": \"Road\",\n",
    "            \"drive\": \"Drive\",\n",
    "            \"lane\": \"Lane\"\n",
    "            }\n",
    "'''\n",
    "Correct the state name to 'FL'.\n",
    "'''\n",
    "def clean_state(state_name):\n",
    "    if state_name != 'FL':\n",
    "        state_name = 'FL'\n",
    "    return state_name\n",
    "\n",
    "'''\n",
    "Audit states that are not 'FL', and add to dictionary.\n",
    "'''\n",
    "def audit_state(state_types, state_name):\n",
    "    if state_name != 'FL':\n",
    "        state_types[state_name] += 1\n",
    "\n",
    "'''\n",
    "Audit streets and add ones that do not end in a value \n",
    "found in the expected list to a dictionary.\n",
    "'''\n",
    "def audit_street_type(street_types, street_name):\n",
    "    street_name = clean_street(street_name)\n",
    "    m = street_type_re.search(street_name)\n",
    "    if m:\n",
    "        street_type = m.group()\n",
    "        if street_type not in expected:\n",
    "            street_types[street_type].add(street_name)\n",
    "    return street_name\n",
    "\n",
    "'''\n",
    "Audit cities and add ones that are not in the expected list of\n",
    "cities or are not found in the ldictionary city_mapping, which maps\n",
    "known incorrect cities to correct ones, to a dictionary.\n",
    "'''\n",
    "def audit_city(invalid_cities, city_name):\n",
    "    if city_name not in cities and city_name not in city_mapping:\n",
    "        invalid_cities[city_name] += 1   \n",
    "\n",
    "        \n",
    "'''\n",
    "Correct incorrect city names with the city_mapping dictionary.\n",
    "'''        \n",
    "def clean_city(city_name):\n",
    "    if city_name not in cities and city_name in city_mapping:\n",
    "        city_name = city_mapping[city_name]\n",
    "    return city_name\n",
    "\n",
    "'''\n",
    "Audit zipcodes and add ones that are not of length 5 digits to\n",
    "a dictionary.\n",
    "'''\n",
    "def audit_zipcode(invalid_zipcodes, zipcode):\n",
    "    if not re.match(r'^\\d{5}$', zipcode):\n",
    "        invalid_zipcodes[zipcode] += 1\n",
    "    return zipcode\n",
    "\n",
    "'''\n",
    "Tests whether an element is a street.\n",
    "'''\n",
    "def is_street_name(elem):\n",
    "    return (elem.attrib['k'] == \"addr:street\")\n",
    "\n",
    "'''\n",
    "Tests whether an element is a state.\n",
    "'''\n",
    "def is_state(elem):\n",
    "    return (elem.attrib['k'] == \"addr:state\")\n",
    "\n",
    "'''\n",
    "Tests whether an element is a city.\n",
    "'''\n",
    "def is_city(elem):\n",
    "    return (elem.attrib['k'] == \"addr:city\")\n",
    "\n",
    "'''\n",
    "Tests whether an element is a zipcode.\n",
    "'''\n",
    "def is_zipcode(elem):\n",
    "    return 'zip' in elem.attrib['k']\n",
    "\n",
    "def audit(osmfile):\n",
    "    osm_file = open(osmfile, \"r\")\n",
    "    \n",
    "    street_types = defaultdict(set)\n",
    "    city_types = defaultdict(int)\n",
    "    zipcode_types = defaultdict(int)\n",
    "    state_types = defaultdict(int)\n",
    "    \n",
    "    for event, elem in ET.iterparse(osm_file, events=(\"start\",)):\n",
    "        if elem.tag == \"node\" or elem.tag == \"way\":\n",
    "            for tag in elem.iter(\"tag\"):\n",
    "                if is_street_name(tag):\n",
    "                    audit_street_type(street_types, tag.attrib['v'])\n",
    "                elif is_zipcode(tag):\n",
    "                    audit_zipcode(zipcode_types, tag.attrib['v'])\n",
    "                elif is_city(tag):\n",
    "                    audit_city(city_types, tag.attrib['v'])\n",
    "                elif is_state(tag):\n",
    "                    audit_state(state_types, tag.attrib['v'])\n",
    "\n",
    "    return street_types, zipcode_types, city_types, state_types\n",
    "\n",
    "\n",
    "'''\n",
    "Clean a street name by removing everything after a comma,\n",
    "# sign, the word 'Suite', changing abbreviated directions to full\n",
    "directions, moving directions from the end of the street to the \n",
    "beginning, and finally fixing incorrect street types with the\n",
    "'mapping' dictionary.\n",
    "'''\n",
    "def clean_street(street_name):\n",
    "    try:\n",
    "        comma_index = street_name.index(',')\n",
    "        street_name = remove(street_name,comma_index)\n",
    "    except:\n",
    "        pass\n",
    "        #print street_name\n",
    "    try:\n",
    "        pound_index = street_name.index('#')\n",
    "        street_name = remove(street_name, pound_index)\n",
    "    except:\n",
    "        pass\n",
    "    \n",
    "    try:\n",
    "        suite_index = street_name.index('Suite')\n",
    "        street_name = remove(street_name, suite_index)\n",
    "    except:\n",
    "        pass\n",
    "\n",
    "    end_direction = direction_re.search(street_name)\n",
    "    if end_direction:\n",
    "        street_name = street_name[:-len(end_direction.group(0))]\n",
    "        street_name = end_direction.group(0) + \" \" + street_name\n",
    "        try:\n",
    "            street_name = update_direction(street_name,direction_mapping)\n",
    "        except:\n",
    "            pass\n",
    "    \n",
    "    try:\n",
    "        name_array = street_name.split(' ')\n",
    "        last = name_array[-1]\n",
    "        name_array[-1] = mapping[last]\n",
    "        joined = ' '.join(name_array)\n",
    "        return joined\n",
    "    except:\n",
    "        return street_name\n",
    "\n",
    "'''\n",
    "Update a direction in a street name by using the provided dictionary.\n",
    "The direction must already be at the beginning of the street.\n",
    "'''\n",
    "def update_direction(name, mapping):\n",
    "    name_array = name.split(' ')\n",
    "    first = name_array[0]\n",
    "    name_array[0] = mapping[first]\n",
    "    return ' '.join(name_array)\n",
    "\n",
    "'''\n",
    "Used by clean_street() to remove everything after commas, #s,\n",
    "and 'Suite' by finding the index of these.\n",
    "'''\n",
    "def remove(name,index):\n",
    "    subname = name[:index]\n",
    "    return subname"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Boulevard of the Arts\n",
      "Main St m104\n",
      "6010 US-301\n",
      "US-301\n",
      "FL 60\n",
      "State Road 64\n",
      "U.S.19\n",
      "US 98 Bypass\n",
      "Corey Ave  St Pete Beach\n",
      "4th Street Notth\n",
      "North Avenue Republica de Cuba\n",
      "Avenue Republica de Cuba\n",
      "West Brandon Blvd (S.R. 60)\n",
      "N U.S. Hwy 41\n",
      "US 41\n",
      "N US Highway 41\n",
      "N US 41\n",
      "North Westshore Bolevard\n",
      "8492 Manatee Bay Dr Tampa\n",
      "Avenue B\n",
      "SR 580\n",
      "State Road 580\n",
      "Avenue F\n",
      "US 301\n",
      "US Highway 301\n",
      "S US Highway 301\n",
      "South US Highway 301\n",
      "us Highway 301\n",
      "CR 672\n",
      "US 98\n",
      "Sunshine skyway\n",
      "E FL 70\n",
      "Lakewood Main St Ste 102\n",
      "US 92\n",
      "12000 US Highway 92\n",
      "S Howard Av 105\n",
      "US-19\n",
      "W Swann Av Prkg\n",
      "US Highway 19\n",
      "US 19\n",
      "us 19\n",
      "3001 US Hwy 19\n",
      "U.S. 19\n",
      "US Hwy 19\n",
      "28519 State Road 54\n",
      "FL 54\n",
      "SR 54\n",
      "State Road 54\n",
      "SR 56\n",
      "FL 56\n",
      "University Square Mall\n",
      "Bay Esplanade\n",
      "State Road 52\n",
      "SR 52\n",
      "FL 52\n",
      "US 301 (FL)\n",
      "US 19 (FL)\n",
      "S Fort Harrison\n",
      "Broadway\n",
      "\n",
      "INVALID ZIPCODES:\n",
      "\n",
      "defaultdict(<type 'int'>, {'33548:33556': 1, '34234:34243': 4, '33701:33704': 4, '33760;33764': 1, '33540; 33849': 2, '34683:34689': 2, '33764; 33756': 8, '33765; 33755': 18, '33569;33547': 2, '33765; 33759; 33765': 26, '33597:33809': 6, '33602:33605': 2, '33602:33603': 12, '33547:33569': 8, '34652:34655': 2, '33701:33712': 16, '34234:34236': 8, '33760; 33764': 7, '33777; 33710': 18, '33543; 34639; 33543': 7, '33777;33710': 2, '34690:34691': 2, '33647; 33543': 5, '33541:33542': 5, '33610; 33617': 2, '34209; 34205': 7, '34653:34668': 2, '33765;33759;33765': 2, '33761:34677': 6, '34233:34238': 2, '33569:33594': 8, '34653; 34652': 7, '34669; 34667; 34667': 20})\n",
      "\n",
      "INVALID CITIES:\n",
      "\n",
      "defaultdict(<type 'int'>, {})\n",
      "\n",
      "INVALID STATES:\n",
      "\n",
      "defaultdict(<type 'int'>, {'florida': 1, 'f': 1, 'FLq': 1, 'fl': 16, 'Florida': 24, 'GA': 3, 'Fl': 3})\n"
     ]
    }
   ],
   "source": [
    "'''\n",
    "Audits the OSM file, finding incorrect streets, zip codes, cities,\n",
    "and states. Prints out the streets that are still incorrect after cleaning,\n",
    "and prints out the invalid zipcodes, cities, and states found.\n",
    "'''\n",
    "st_types, invalid_zips, invalid_cities, invalid_states = audit(OSMFILE)\n",
    "\n",
    "for st_type, ways in st_types.iteritems():\n",
    "    for name in ways:\n",
    "        try:\n",
    "            better_name = update_name(name, mapping)\n",
    "            #print name, \"=>\", better_name\n",
    "        except:\n",
    "            print name\n",
    "            \n",
    "print \"\\nINVALID ZIPCODES:\\n\"\n",
    "print invalid_zips\n",
    "print \"\\nINVALID CITIES:\\n\"\n",
    "print invalid_cities\n",
    "print \"\\nINVALID STATES:\\n\"\n",
    "print invalid_states"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Overwriting data.py\n"
     ]
    }
   ],
   "source": [
    "#%%writefile data.py\n",
    "#!/usr/bin/env python\n",
    "import audit\n",
    "\n",
    "\"\"\"\n",
    "After auditing is complete the next step is to prepare the data to be inserted into a SQL database.\n",
    "To do so you will parse the elements in the OSM XML file, transforming them from document format to\n",
    "tabular format, thus making it possible to write to .csv files.  These csv files can then easily be\n",
    "imported to a SQL database as tables.\n",
    "\n",
    "The process for this transformation is as follows:\n",
    "- Use iterparse to iteratively step through each top level element in the XML\n",
    "- Shape each element into several data structures using a custom function\n",
    "- Utilize a schema and validation library to ensure the transformed data is in the correct format\n",
    "- Write each data structure to the appropriate .csv files\n",
    "\n",
    "We've already provided the code needed to load the data, perform iterative parsing and write the\n",
    "output to csv files. Your task is to complete the shape_element function that will transform each\n",
    "element into the correct format. To make this process easier we've already defnameed a schema (see\n",
    "the schema.py file in the last code tab) for the .csv files and the eventual tables. Using the \n",
    "cerberus library we can validate the output against this schema to ensure it is correct.\n",
    "\n",
    "## Shape Element Function\n",
    "The function should take as input an iterparse Element object and return a dictionary.\n",
    "\n",
    "### If the element top level tag is \"node\":\n",
    "The dictionary returned should have the format {\"node\": .., \"node_tags\": ...}\n",
    "\n",
    "The \"node\" field should hold a dictionary of the following top level node attributes:\n",
    "- id\n",
    "- user\n",
    "- uid\n",
    "- version\n",
    "- lat\n",
    "- lon\n",
    "- timestamp\n",
    "- changeset\n",
    "All other attributes can be ignored\n",
    "\n",
    "The \"node_tags\" field should hold a list of dictionaries, one per secondary tag. Secondary tags are\n",
    "child tags of node which have the tag name/type: \"tag\". Each dictionary should have the following\n",
    "fields from the secondary tag attributes:\n",
    "- id: the top level node id attribute value\n",
    "- key: the full tag \"k\" attribute value if no colon is present or the characters after the colon if one is.\n",
    "- value: the tag \"v\" attribute value\n",
    "- type: either the characters before the colon in the tag \"k\" value or \"regular\" if a colon\n",
    "        is not present.\n",
    "\n",
    "Additionally,\n",
    "\n",
    "- if the tag \"k\" value contains problematic characters, the tag should be ignored\n",
    "- if the tag \"k\" value contains a \":\" the characters before the \":\" should be set as the tag type\n",
    "  and characters after the \":\" should be set as the tag key\n",
    "- if there are additional \":\" in the \"k\" value they and they should be ignored and kept as part of\n",
    "  the tag key. For example:\n",
    "\n",
    "  <tag k=\"addr:street:name\" v=\"Lincoln\"/>\n",
    "  should be turned into\n",
    "  {'id': 12345, 'key': 'street:name', 'value': 'Lincoln', 'type': 'addr'}\n",
    "\n",
    "- If a node has no secondary tags then the \"node_tags\" field should just contain an empty list.\n",
    "\n",
    "The fnameal return value for a \"node\" element should look something like:\n",
    "\n",
    "{'node': {'id': 757860928,\n",
    "          'user': 'uboot',\n",
    "          'uid': 26299,\n",
    "       'version': '2',\n",
    "          'lat': 41.9747374,\n",
    "          'lon': -87.6920102,\n",
    "          'timestamp': '2010-07-22T16:16:51Z',\n",
    "      'changeset': 5288876},\n",
    " 'node_tags': [{'id': 757860928,\n",
    "                'key': 'amenity',\n",
    "                'value': 'fast_food',\n",
    "                'type': 'regular'},\n",
    "               {'id': 757860928,\n",
    "                'key': 'cuisine',\n",
    "                'value': 'sausage',\n",
    "                'type': 'regular'},\n",
    "               {'id': 757860928,\n",
    "                'key': 'name',\n",
    "                'value': \"Shelly's Tasty Freeze\",\n",
    "                'type': 'regular'}]}\n",
    "\n",
    "### If the element top level tag is \"way\":\n",
    "The dictionary should have the format {\"way\": ..., \"way_tags\": ..., \"way_nodes\": ...}\n",
    "\n",
    "The \"way\" field should hold a dictionary of the following top level way attributes:\n",
    "- id\n",
    "-  user\n",
    "- uid\n",
    "- version\n",
    "- timestamp\n",
    "- changeset\n",
    "\n",
    "All other attributes can be ignored\n",
    "\n",
    "The \"way_tags\" field should again hold a list of dictionaries, following the exact same rules as\n",
    "for \"node_tags\".\n",
    "\n",
    "Additionally, the dictionary should have a field \"way_nodes\". \"way_nodes\" should hold a list of\n",
    "dictionaries, one for each nd child tag.  Each dictionary should have the fields:\n",
    "- id: the top level element (way) id\n",
    "- node_id: the ref attribute value of the nd tag\n",
    "- position: the index starting at 0 of the nd tag i.e. what order the nd tag appears within\n",
    "            the way element\n",
    "\n",
    "The fnameal return value for a \"way\" element should look something like:\n",
    "\n",
    "{'way': {'id': 209809850,\n",
    "         'user': 'chicago-buildings',\n",
    "         'uid': 674454,\n",
    "         'version': '1',\n",
    "         'timestamp': '2013-03-13T15:58:04Z',\n",
    "         'changeset': 15353317},\n",
    " 'way_nodes': [{'id': 209809850, 'node_id': 2199822281, 'position': 0},\n",
    "               {'id': 209809850, 'node_id': 2199822390, 'position': 1},\n",
    "               {'id': 209809850, 'node_id': 2199822392, 'position': 2},\n",
    "               {'id': 209809850, 'node_id': 2199822369, 'position': 3},\n",
    "               {'id': 209809850, 'node_id': 2199822370, 'position': 4},\n",
    "               {'id': 209809850, 'node_id': 2199822284, 'position': 5},\n",
    "               {'id': 209809850, 'node_id': 2199822281, 'position': 6}],\n",
    " 'way_tags': [{'id': 209809850,\n",
    "               'key': 'housenumber',\n",
    "               'type': 'addr',\n",
    "               'value': '1412'},\n",
    "              {'id': 209809850,\n",
    "               'key': 'street',\n",
    "               'type': 'addr',\n",
    "               'value': 'West Lexington St.'},\n",
    "              {'id': 209809850,\n",
    "               'key': 'street:name',\n",
    "               'type': 'addr',\n",
    "               'value': 'Lexington'},\n",
    "              {'id': '209809850',\n",
    "               'key': 'street:prefix',\n",
    "               'type': 'addr',\n",
    "               'value': 'West'},\n",
    "              {'id': 209809850,\n",
    "               'key': 'street:type',\n",
    "               'type': 'addr',\n",
    "               'value': 'Street'},\n",
    "              {'id': 209809850,\n",
    "               'key': 'building',\n",
    "               'type': 'regular',\n",
    "               'value': 'yes'},\n",
    "              {'id': 209809850,\n",
    "               'key': 'levels',\n",
    "               'type': 'building',\n",
    "               'value': '1'},\n",
    "              {'id': 209809850,\n",
    "\n",
    "               'key': 'building_id',\n",
    "               'type': 'chicago',\n",
    "               'value': '366409'}]}\n",
    "\"\"\"\n",
    "\n",
    "import csv\n",
    "import codecs\n",
    "import pprint\n",
    "import re\n",
    "import xml.etree.cElementTree as ET\n",
    "\n",
    "import cerberus\n",
    "\n",
    "import schema\n",
    "\n",
    "OSM_PATH = \"tampa_florida.osm\"\n",
    "\n",
    "NODES_PATH = \"nodes.csv\"\n",
    "NODE_TAGS_PATH = \"nodes_tags.csv\"\n",
    "WAYS_PATH = \"ways.csv\"\n",
    "WAY_NODES_PATH = \"ways_nodes.csv\"\n",
    "WAY_TAGS_PATH = \"ways_tags.csv\"\n",
    "\n",
    "LOWER_COLON = re.compile(r'^([a-z]|_)+:([a-z]|_)+')\n",
    "PROBLEMCHARS = re.compile(r'[=\\+/&<>;\\'\"\\?%#$@\\,\\. \\t\\r\\n]')\n",
    "\n",
    "SCHEMA = schema.schema\n",
    "\n",
    "# Make sure the fields order in the csvs matches the column order in the sql table schema\n",
    "NODE_FIELDS = ['id', 'lat', 'lon', 'user', 'uid', 'version', 'changeset', 'timestamp']\n",
    "NODE_TAGS_FIELDS = ['id', 'key', 'value', 'type']\n",
    "WAY_FIELDS = ['id', 'user', 'uid', 'version', 'changeset', 'timestamp']\n",
    "WAY_TAGS_FIELDS = ['id', 'key', 'value', 'type']\n",
    "WAY_NODES_FIELDS = ['id', 'node_id', 'position']\n",
    "\n",
    "def clean_tag(tag):\n",
    "    #print \"tag {}\".format(tag.attrib['k'])\n",
    "    if audit.is_street_name(tag):\n",
    "        print \"street name\"\n",
    "        tag.attrib['v'] = audit.clean_street(tag.attrib['v'])\n",
    "    elif audit.is_state(tag):\n",
    "        print 'state'\n",
    "        tag.attrib['v'] = audit.clean_state(tag.attrib['v'])\n",
    "    elif audit.is_city(tag):\n",
    "        print 'city'\n",
    "        tag.attrib['v'] = audit.clean_city(tag.attrib['v'])\n",
    "    return tag.attrib['v']\n",
    "\n",
    "\n",
    "def shape_element(element, node_attr_fields=NODE_FIELDS, way_attr_fields=WAY_FIELDS,\n",
    "                  problem_chars=PROBLEMCHARS, default_tag_type='regular'):\n",
    "    \"\"\"Clean and shape node or way XML element to Python dict\"\"\"\n",
    "\n",
    "    node_attribs = {}\n",
    "    way_attribs = {}\n",
    "    way_nodes = []\n",
    "    tags = []  # Handle secondary tags the same way for both node and way elements    \n",
    "    \n",
    "    if element.tag == 'node':\n",
    "        for attrib in element.attrib:\n",
    "            if attrib in NODE_FIELDS:\n",
    "                node_attribs[attrib] = element.attrib[attrib]\n",
    "\n",
    "        for child in element:\n",
    "            node_tag = {}\n",
    "            new_v = clean_tag(child)\n",
    "            if LOWER_COLON.match(child.attrib['k']):\n",
    "                node_tag['type'] = child.attrib['k'].split(':',1)[0]\n",
    "                node_tag['key'] = child.attrib['k'].split(':',1)[1]\n",
    "                node_tag['id'] = element.attrib['id']\n",
    "                node_tag['value'] = new_v #child.attrib['v']\n",
    "                tags.append(node_tag)\n",
    "            elif PROBLEMCHARS.match(child.attrib['k']):\n",
    "                continue\n",
    "            else:\n",
    "                node_tag['type'] = 'regular'\n",
    "                node_tag['key'] = child.attrib['k']\n",
    "                node_tag['id'] = element.attrib['id']\n",
    "                node_tag['value'] = new_v #child.attrib['v']\n",
    "                tags.append(node_tag)\n",
    "        \n",
    "        return {'node': node_attribs, 'node_tags': tags}\n",
    "        \n",
    "    elif element.tag == 'way':\n",
    "        for attrib in element.attrib:\n",
    "            if attrib in WAY_FIELDS:\n",
    "                way_attribs[attrib] = element.attrib[attrib]\n",
    "        print element.attrib\n",
    "        position = 0\n",
    "        for child in element:\n",
    "            way_tag = {}\n",
    "            way_node = {}\n",
    "            print child\n",
    "            if child.tag == 'tag':\n",
    "                new_v = clean_tag(child)\n",
    "                if LOWER_COLON.match(child.attrib['k']):\n",
    "                    way_tag['type'] = child.attrib['k'].split(':',1)[0]\n",
    "                    way_tag['key'] = child.attrib['k'].split(':',1)[1]\n",
    "                    way_tag['id'] = element.attrib['id']\n",
    "                    way_tag['value'] = new_v #child.attrib['v']\n",
    "                    tags.append(way_tag)\n",
    "                elif PROBLEMCHARS.match(child.attrib['k']):\n",
    "                    continue\n",
    "                else:\n",
    "                    way_tag['type'] = 'regular'\n",
    "                    way_tag['key'] = child.attrib['k']\n",
    "                    way_tag['id'] = element.attrib['id']\n",
    "                    way_tag['value'] = new_v #child.attrib['v']\n",
    "                    tags.append(way_tag)\n",
    "                    \n",
    "            elif child.tag == 'nd':\n",
    "                way_node['id'] = element.attrib['id']\n",
    "                way_node['node_id'] = child.attrib['ref']\n",
    "                way_node['position'] = position\n",
    "                position += 1\n",
    "                way_nodes.append(way_node)\n",
    "        \n",
    "    return {'way': way_attribs, 'way_nodes': way_nodes, 'way_tags': tags}\n",
    "\n",
    "\n",
    "# ================================================== #\n",
    "#               Helper Functions                     #\n",
    "# ================================================== #\n",
    "def get_element(osm_file, tags=('node', 'way', 'relation')):\n",
    "    \"\"\"Yield element if it is the right type of tag\"\"\"\n",
    "\n",
    "    context = ET.iterparse(osm_file, events=('start', 'end'))\n",
    "    _, root = next(context)\n",
    "    for event, elem in context:\n",
    "        if event == 'end' and elem.tag in tags:\n",
    "            yield elem\n",
    "            root.clear()\n",
    "\n",
    "\n",
    "def validate_element(element, validator, schema=SCHEMA):\n",
    "    \"\"\"Raise ValidationError if element does not match schema\"\"\"\n",
    "    if validator.validate(element, schema) is not True:\n",
    "        field, errors = next(validator.errors.iteritems())\n",
    "        message_string = \"\\nElement of type '{0}' has the following errors:\\n{1}\"\n",
    "        error_string = pprint.pformat(errors)\n",
    "        \n",
    "        raise Exception(message_string.format(field, error_string))\n",
    "\n",
    "\n",
    "class UnicodeDictWriter(csv.DictWriter, object):\n",
    "    \"\"\"Extend csv.DictWriter to handle Unicode input\"\"\"\n",
    "\n",
    "    def writerow(self, row):\n",
    "        super(UnicodeDictWriter, self).writerow({\n",
    "            k: (v.encode('utf-8') if isinstance(v, unicode) else v) for k, v in row.iteritems()\n",
    "        })\n",
    "\n",
    "    def writerows(self, rows):\n",
    "        for row in rows:\n",
    "            self.writerow(row)\n",
    "\n",
    "\n",
    "# ================================================== #\n",
    "#               Main Function                        #\n",
    "# ================================================== #\n",
    "def process_map(file_in, validate):\n",
    "    \"\"\"Iteratively process each XML element and write to csv(s)\"\"\"\n",
    "\n",
    "    with codecs.open(NODES_PATH, 'w') as nodes_file, \\\n",
    "         codecs.open(NODE_TAGS_PATH, 'w') as nodes_tags_file, \\\n",
    "         codecs.open(WAYS_PATH, 'w') as ways_file, \\\n",
    "         codecs.open(WAY_NODES_PATH, 'w') as way_nodes_file, \\\n",
    "         codecs.open(WAY_TAGS_PATH, 'w') as way_tags_file:\n",
    "\n",
    "        nodes_writer = UnicodeDictWriter(nodes_file, NODE_FIELDS)\n",
    "        node_tags_writer = UnicodeDictWriter(nodes_tags_file, NODE_TAGS_FIELDS)\n",
    "        ways_writer = UnicodeDictWriter(ways_file, WAY_FIELDS)\n",
    "        way_nodes_writer = UnicodeDictWriter(way_nodes_file, WAY_NODES_FIELDS)\n",
    "        way_tags_writer = UnicodeDictWriter(way_tags_file, WAY_TAGS_FIELDS)\n",
    "\n",
    "        nodes_writer.writeheader()\n",
    "        node_tags_writer.writeheader()\n",
    "        ways_writer.writeheader()\n",
    "        way_nodes_writer.writeheader()\n",
    "        way_tags_writer.writeheader()\n",
    "\n",
    "        validator = cerberus.Validator()\n",
    "\n",
    "        for element in get_element(file_in, tags=('node', 'way')):\n",
    "            el = shape_element(element)\n",
    "            if el:\n",
    "                if validate is True:\n",
    "                    validate_element(el, validator)\n",
    "\n",
    "                if element.tag == 'node':\n",
    "                    nodes_writer.writerow(el['node'])\n",
    "                    node_tags_writer.writerows(el['node_tags'])\n",
    "                elif element.tag == 'way':\n",
    "                    ways_writer.writerow(el['way'])\n",
    "                    way_nodes_writer.writerows(el['way_nodes'])\n",
    "                    way_tags_writer.writerows(el['way_tags'])\n",
    "\n",
    "\n",
    "if __name__ == '__main__':\n",
    "    # Note: Validation is ~ 10X slower. For the project consider using a small\n",
    "    # sample of the map when validating.\n",
    "    process_map(OSM_PATH, validate=True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "process_map('tampa_florida.osm',False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#%%writefile database.py\n",
    "'''\n",
    "Creates the database by reading in the CSV files from data.py\n",
    "'''\n",
    "\n",
    "\n",
    "import csv\n",
    "import sqlite3\n",
    "\n",
    "conn = sqlite3.connect('tampa.db')\n",
    "conn.text_factory = str\n",
    "cur = conn.cursor()\n",
    "\n",
    "cur.execute('CREATE TABLE nodes (id INTEGER PRIMARY KEY NOT NULL, lat REAL, lon REAL, user TEXT, uid INTEGER, version INTEGER, changeset INTEGER, timestamp TEXT);')\n",
    "with open('nodes.csv','rb') as fname:\n",
    "    dr = csv.DictReader(fname)\n",
    "    to_db = [(i['id'], i['lat'], i['lon'], i['user'], i['uid'], i['version'], i['changeset'], i['timestamp']) \\\n",
    "             for i in dr]\n",
    "\n",
    "cur.executemany(\"INSERT INTO nodes (id, lat, lon, user, uid, version, changeset, timestamp) \\\n",
    "                VALUES (?, ?, ?, ?, ?, ?, ?, ?);\", to_db)\n",
    "conn.commit()\n",
    "\n",
    "cur.execute(\"CREATE TABLE nodes_tags (id INTEGER, key TEXT, value TEXT, type TEXT, FOREIGN KEY (id) REFERENCES nodes(id));\")\n",
    "with open('nodes_tags.csv','rb') as fname:\n",
    "    dr = csv.DictReader(fname) \n",
    "    to_db = [(i['id'], i['key'], i['value'], i['type']) for i in dr]\n",
    "\n",
    "cur.executemany(\"INSERT INTO nodes_tags (id, key, value, type) VALUES (?, ?, ?, ?);\", to_db)\n",
    "conn.commit()\n",
    "\n",
    "cur.execute(\"CREATE TABLE ways (id INTEGER PRIMARY KEY NOT NULL, user TEXT, uid INTEGER, version TEXT, changeset INTEGER, timestamp TEXT);\")\n",
    "with open('ways.csv','rb') as fname:\n",
    "    dr = csv.DictReader(fname) \n",
    "    to_db = [(i['id'], i['user'], i['uid'], i['version'], i['changeset'], i['timestamp']) for i in dr]\n",
    "\n",
    "cur.executemany(\"INSERT INTO ways (id, user, uid, version, changeset, timestamp) VALUES (?, ?, ?, ?, ?, ?);\", to_db)\n",
    "conn.commit()\n",
    "\n",
    "cur.execute(\"CREATE TABLE ways_nodes (id INTEGER NOT NULL, node_id INTEGER NOT NULL, position INTEGER NOT NULL, FOREIGN KEY (id) REFERENCES ways(id), FOREIGN KEY (node_id) REFERENCES nodes(id));\")\n",
    "with open('ways_nodes.csv','rb') as fname:\n",
    "    dr = csv.DictReader(fname) \n",
    "    to_db = [(i['id'], i['node_id'], i['position']) for i in dr]\n",
    "\n",
    "cur.executemany(\"INSERT INTO ways_nodes (id, node_id, position) VALUES (?, ?, ?);\", to_db)\n",
    "conn.commit()\n",
    "\n",
    "cur.execute(\"CREATE TABLE ways_tags (id INTEGER NOT NULL, key TEXT NOT NULL, value TEXT NOT NULL, type TEXT, FOREIGN KEY (id) REFERENCES ways(id));\")\n",
    "with open('ways_tags.csv','rb') as fname:\n",
    "    dr = csv.DictReader(fname) \n",
    "    to_db = [(i['id'], i['key'], i['value'], i['type']) for i in dr]\n",
    "\n",
    "cur.executemany(\"INSERT INTO ways_tags (id, key, value, type) VALUES (?, ?, ?, ?);\", to_db)\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of nodes:\n",
      "1655566\n",
      "\n",
      "Number of ways:\n",
      "182866\n",
      "\n",
      "Number of unique users:\n",
      "1448\n",
      "\n",
      "Top 10 contributing users:\n",
      "[('coleman', 258302), ('woodpeck_fixbot', 235013), ('grouper', 187215), ('EdHillsman', 106677), ('NE2', 72924), ('David Hey', 60918), ('LnxNoob', 58364), ('KalininOV', 48825), ('westampa', 42145), ('bot-mode', 37656)]\n",
      "\n",
      "Number of users contributing once:\n",
      "\n",
      "Top 10 amenities:\n",
      "[('restaurant', 852), ('place_of_worship', 771), ('school', 553), ('fast_food', 396), ('bicycle_parking', 353), ('bench', 279), ('fuel', 235), ('fountain', 201), ('bank', 170), ('toilets', 148)]\n",
      "\n",
      "Top 5 places of worship:\n",
      "[('christian', 724), ('jewish', 4), ('bahai', 3), ('buddhist', 3), ('unitarian_universalist', 3)]\n",
      "\n",
      "Top 5 cuisines\n",
      "[('american', 93), ('pizza', 70), ('mexican', 41), ('italian', 28), ('seafood', 25)]\n",
      "\n",
      "Top 10 restaurants:\n",
      "[('Tijuana Flats', 8), (\"Applebee's\", 6), ('Bob Evans', 6), (\"Denny's\", 6), ('IHOP', 6), ('Outback Steakhouse', 6), ('Panera Bread', 6), (\"Chili's\", 5), ('Golden Corral', 5), ('Pizza Hut', 5)]\n"
     ]
    }
   ],
   "source": [
    "#%%writefile query.py\n",
    "'''\n",
    "The queries used in the project.\n",
    "'''\n",
    "\n",
    "print \"Number of nodes:\"\n",
    "print cur.execute('SELECT COUNT(*) FROM nodes').fetchone()[0]\n",
    "\n",
    "print \"\\nNumber of ways:\"\n",
    "print cur.execute('SELECT COUNT(*) FROM ways').fetchone()[0]\n",
    "\n",
    "print \"\\nNumber of unique users:\"\n",
    "print cur.execute('SELECT COUNT(DISTINCT(e.uid)) \\\n",
    "            FROM (SELECT uid FROM nodes UNION ALL SELECT uid FROM ways) e').fetchone()[0]\n",
    "\n",
    "print \"\\nTop 10 contributing users:\"\n",
    "users = []\n",
    "for row in cur.execute('SELECT e.user, COUNT(*) as num \\\n",
    "            FROM (SELECT user FROM nodes UNION ALL SELECT user FROM ways) e \\\n",
    "            GROUP BY e.user \\\n",
    "            ORDER BY num DESC \\\n",
    "            LIMIT 10'):\n",
    "    users.append(row)\n",
    "print users\n",
    "\n",
    "print \"\\nNumber of users contributing once:\"\n",
    "cur.execute('SELECT COUNT(*) FROM \\\n",
    "                (SELECT e.user, COUNT(*) as num \\\n",
    "                 FROM (SELECT user FROM nodes UNION ALL SELECT user FROM ways) e \\\n",
    "                 GROUP BY e.user \\\n",
    "                 HAVING num=1) u').fetchone()\n",
    "\n",
    "print \"\\nTop 10 amenities:\"\n",
    "amenities = []\n",
    "for row in cur.execute('SELECT value, COUNT(*) as num \\\n",
    "            FROM nodes_tags \\\n",
    "            WHERE key=\"amenity\" \\\n",
    "            GROUP BY value \\\n",
    "            ORDER BY num DESC \\\n",
    "            LIMIT 10'):\n",
    "    amenities.append(row)\n",
    "print amenities\n",
    "\n",
    "print \"\\nTop 5 places of worship:\"\n",
    "religions = []\n",
    "for row in cur.execute('SELECT nodes_tags.value, COUNT(*) as num \\\n",
    "            FROM nodes_tags \\\n",
    "                JOIN (SELECT DISTINCT(id) FROM nodes_tags WHERE value=\"place_of_worship\") i \\\n",
    "                ON nodes_tags.id=i.id \\\n",
    "            WHERE nodes_tags.key=\"religion\" \\\n",
    "            GROUP BY nodes_tags.value \\\n",
    "            ORDER BY num DESC \\\n",
    "            LIMIT 5;'):\n",
    "    religions.append(row)\n",
    "print religions\n",
    "\n",
    "print \"\\nTop 5 cuisines\"\n",
    "cuisines = []\n",
    "for row in cur.execute('SELECT nodes_tags.value, COUNT(*) as num \\\n",
    "            FROM nodes_tags \\\n",
    "                JOIN (SELECT DISTINCT(id) FROM nodes_tags WHERE value=\"restaurant\") i \\\n",
    "                ON nodes_tags.id=i.id \\\n",
    "            WHERE nodes_tags.key=\"cuisine\" \\\n",
    "            GROUP BY nodes_tags.value \\\n",
    "            ORDER BY num DESC \\\n",
    "            LIMIT 5'):\n",
    "    cuisines.append(row)\n",
    "print cuisines\n",
    "\n",
    "print \"\\nTop 10 restaurants:\"\n",
    "restaurants = []\n",
    "for row in cur.execute('SELECT value, COUNT(*) as num \\\n",
    "            FROM nodes_tags \\\n",
    "                JOIN (SELECT DISTINCT(id) FROM nodes_tags WHERE value=\"restaurant\") i \\\n",
    "                ON nodes_tags.id=i.id \\\n",
    "            WHERE key=\"name\"\\\n",
    "            GROUP BY value \\\n",
    "            ORDER BY num DESC \\\n",
    "            LIMIT 10'):\n",
    "    restaurants.append(row)\n",
    "print restaurants"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Find number of restaurants in database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('restaurant', 852)]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.execute('SELECT value, count(*) \\\n",
    "            FROM nodes_tags WHERE value=\"restaurant\"').fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Find how many restaurants have delivery information"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('no', 72), ('yes', 19)]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.execute('SELECT value, count(*) \\\n",
    "            FROM nodes_tags JOIN (SELECT DISTINCT(id) \\\n",
    "            FROM nodes_tags WHERE value=\"restaurant\") i \\\n",
    "                ON nodes_tags.id=i.id \\\n",
    "            WHERE key=\"delivery\"\\\n",
    "            GROUP BY value').fetchall()"
   ]
  }
 ],
 "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.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}