P3-Wrangle-OpenStreetMap-Data/P3-Wrangle-OpenStreetMap-Main/OpenStreepMap-SF-Wrangle.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Wrangling OSM: San Francisco Edition"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**San Franciso, Ca**\n",
    "\n",
    "We will observe San Francisco, Ca region in [OpenStreetMap.org](https://www.openstreetmap.org) and use data munging techniques, such as assessing the quality of the data for validity, accuracy, completeness, consistency and uniformity, to clean the selected OpenStreetMap data.\n",
    "\n",
    "![San Francisco, CA](Images/BayBridge.jpg)\n",
    "\n",
    "\n",
    "**Why we select the San Francisco Region**\n",
    "\n",
    "[San Francisco, Ca](https://www.openstreetmap.org/search?query=san%20francisco#map=12/37.7444/-122.4382) is the current city I reside in. My motivations for using San Francisco in this data wrangling procedure is to both show that I exist, and indicate I am a proficient analyst looking to grow with an excellent organization.\n",
    "\n",
    "Below is an image of the [Salesforce Tower](http://www.salesforcetower.com/). This tower represents the heights I want to reach to. Though with time and proper construction, I will stand tall in this city.\n",
    "\n",
    "![Salesforce Tower](Images/sf_tower.jpg)\n",
    "\n",
    "**Project Outcomes:**\n",
    "\n",
    "- Assess the quality of the data for validity, accuracy, completeness, consistency and uniformity.\n",
    "- Parse and gather data from popular file formats such as .csv, .json, .xml, and .html\n",
    "- Process data from multiple files or very large files that can be cleaned programmatically.\n",
    "- Learn how to store, query, and aggregate data using MongoDB or SQL.\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "**Notes: **\n",
    "- I will be using SQL as my data schema for this project.\n",
    "- Documentation of OpenStreetMap XLM data can be found [here](https://wiki.openstreetmap.org/wiki/OSM_XML)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### CLICK HERE TO TOGGLE ON/OFF CODE \n",
    "&darr; &darr; &darr; &darr;  &darr; &darr; &darr; &darr;  &darr; &darr; &darr; &darr;  &darr; &darr; &darr; &darr;  &darr; &darr; &darr; &darr;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<script>\n",
       "code_show=true; \n",
       "function code_toggle() {\n",
       " if (code_show){\n",
       " $('div.input').hide();\n",
       " } else {\n",
       " $('div.input').show();\n",
       " }\n",
       " code_show = !code_show\n",
       "} \n",
       "$( document ).ready(code_toggle);\n",
       "</script>\n",
       "<form action=\"javascript:code_toggle()\"><input type=\"submit\" value=\"Click here to toggle on/off the raw code.\"></form>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from IPython.display import HTML\n",
    "\n",
    "HTML('''<script>\n",
    "code_show=true; \n",
    "function code_toggle() {\n",
    " if (code_show){\n",
    " $('div.input').hide();\n",
    " } else {\n",
    " $('div.input').show();\n",
    " }\n",
    " code_show = !code_show\n",
    "} \n",
    "$( document ).ready(code_toggle);\n",
    "</script>\n",
    "<form action=\"javascript:code_toggle()\"><input type=\"submit\" value=\"Click here to toggle on/off the raw code.\"></form>''')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Import"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#Import Libraries\n",
    "\n",
    "###XML library ElementTree\n",
    "import xml.etree.cElementTree as ET\n",
    "\n",
    "###Print\n",
    "import pprint\n",
    "\n",
    "###Regular expression\n",
    "import re\n",
    "\n",
    "###CSV and Dictionary \n",
    "from collections import defaultdict\n",
    "import csv\n",
    "import codecs\n",
    "\n",
    "###Data validation\n",
    "import cerberus\n",
    "import validators\n",
    "import phonenumbers\n",
    "\n",
    "##3Schema format\n",
    "import schema_guidline\n",
    "import schema\n",
    "\n",
    "###Maths and data \n",
    "import numpy as np\n",
    "\n",
    "###String\n",
    "import string\n",
    "import schema_guidline\n",
    "\n",
    "#SQL \n",
    "import sqlite3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "OSM_FILE = \"C:/Users/rmald_000/Downloads/Udacity-DataAnalyst-Downloads/Large-Files/P3/san-francisco_california.osm\"\n",
    "SAMPLE_FILE = \"san-francisco_california_sample.osm\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "'''\n",
    "Function: get_elements_sample takes in parameters osm_file and tags.\n",
    "This function gets a specified element from an osm file\n",
    "\n",
    "The with statement as output is the procedure of getting every 100th element fromt the main osm file\n",
    "'''\n",
    "\n",
    "\n",
    "k = 100 # Parameter: take every k-th top level element\n",
    "\n",
    "def get_elements_sample(osm_file, tags=('node', 'way', 'relation')):\n",
    "    \"\"\"Yield element if it is the right type of tag\n",
    "\n",
    "    Reference:\n",
    "    http://stackoverflow.com/questions/3095434/inserting-newlines-in-xml-file-generated-via-xml-etree-elementtree-in-python\n",
    "    \"\"\"\n",
    "    context = iter(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",
    "with open(SAMPLE_FILE, 'wb') as output:\n",
    "    output.write(bytes('<?xml version=\"1.0\" encoding=\"UTF-8\"?>\\n', 'UTF-8'))\n",
    "    output.write(bytes('<osm>\\n  ', 'UTF-8'))\n",
    "\n",
    "    # Write every 10th top level element\n",
    "    for i, element in enumerate(get_elements_sample(OSM_FILE)):\n",
    "        if i % k == 0:\n",
    "            output.write(ET.tostring(element, encoding='utf-8'))\n",
    "\n",
    "    output.write(bytes('</osm>', 'UTF-8'))\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We first import several libraries. The most important of these libraries is the [Element Tree library](https://docs.python.org/2/library/xml.etree.elementtree.html).\n",
    "\n",
    "We then recognize two local OSM files; We recognize the San Francisco(SF) OSM file and the subset file to the SF OSM file. The subset file is where we will fill every 1000 element of the main SF file to the subset SF file\n",
    "\n",
    "Thereafter, we proceed with obtaining every 100th k element from the main SF OSM file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "sf = SAMPLE_FILE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#Get San Francisco Tree\n",
    "tree = ET.parse(sf)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#Get root of 'tree'\n",
    "root = tree.getroot()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "OSM Tag:\n",
      "osm\n"
     ]
    }
   ],
   "source": [
    "#Check the tags for each element\n",
    "####Release 'break' to see full list of top level tags\n",
    "for event, element in ET.iterparse(sf, events=(\"start\",)):\n",
    "    print(\"OSM Tag:\")\n",
    "    print(element.tag)\n",
    "    break"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We parse the subset SF file (sf) to get the root of this osm file.\n",
    "\n",
    "We then confirm the procedure worked by printing out the OSM Tag."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Defining Node Tags"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "'''\n",
    "Function count_tags counts the number of top, parent, and child tags\n",
    "parameter: OSM File\n",
    "'''\n",
    "def count_tags(filename):\n",
    "    tree = ET.parse(filename)\n",
    "    root = tree.getroot()\n",
    "    \n",
    "    tag_count = {}\n",
    "    \n",
    "    #Get count of all tags. If no tag is in there, add to dictionary with\n",
    "    ##Count as 1\n",
    "    for event, element in ET.iterparse(filename,events=(\"start\",)):\n",
    "        element_tag = element.tag\n",
    "        if element_tag not in tag_count:\n",
    "            tag_count[element_tag] = 1\n",
    "        else:\n",
    "            tag_count[element_tag] += 1\n",
    "            \n",
    "    return(tag_count)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of tags:\n",
      " {'member': 452, 'way': 8110, 'osm': 1, 'node': 65712, 'nd': 76351, 'relation': 62, 'tag': 19888}\n"
     ]
    }
   ],
   "source": [
    "#Implement count_tags function\n",
    "count_of_tags = count_tags(sf)\n",
    "\n",
    "print(\"Number of tags:\\n\",count_of_tags)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We obtain a dictionary of tags and their respective counts. However, what are these tags? \n",
    "\n",
    "The following is a brief definition of the element and tag meanings in our dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Element and Tag meanings:**\n",
    "\n",
    "nd:\n",
    "\n",
    "bounds: Boundary\n",
    "\n",
    "member: \n",
    "\n",
    "way: defining linear features and area boundaries\n",
    "\n",
    "osm: OpenStreetMap\n",
    "\n",
    "node: defining points in space\n",
    "\n",
    "tag: defining specific features of map elements\n",
    "\n",
    "relation:  explain how other elements work together\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Tag Types and Potential Issues"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Tag Types"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#Regular expresion lists for lower, lower_colon, and problem characters\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]')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#Function key_type that has parameters element and key\n",
    "##returns key\n",
    "####Counts the element keys in tree if they are part of the three previously mentioned regular expression categories\n",
    "def key_type(element,key):\n",
    "    if element.tag ==\"tag\":\n",
    "        for tag in element.iter(\"tag\"):\n",
    "            attrib_k = tag.attrib[\"k\"]\n",
    "            if re.search(lower,attrib_k):\n",
    "                key[\"lower\"] +=1\n",
    "            elif re.search(lower_colon,attrib_k):\n",
    "                key[\"lower_colon\"] +=1\n",
    "            \n",
    "            elif re.search(problemchars,attrib_k):\n",
    "                key[\"problemchars\"] +=1\n",
    "                \n",
    "            else:\n",
    "                key[\"other\"] += 1\n",
    "    return(key)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#Process map function that takes in OSM file\n",
    "###Categorize element keys and place them in dictionary\n",
    "def process_map(filename):\n",
    "    keys = {\"lower\":0,\"lower_colon\":0,\"problemchars\":0,\"other\":0}\n",
    "    #Implement key_type function for reach element's key\n",
    "    for event, element in ET.iterparse(filename):\n",
    "        keys = key_type(element,keys)\n",
    "    return(keys)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "map_processed = process_map(sf)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'lower': 14007, 'lower_colon': 5700, 'other': 178, 'problemchars': 3}\n"
     ]
    }
   ],
   "source": [
    "pprint.pprint(map_processed)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Tag issues\n",
    "\n",
    "We observe 3 tag issues, as seen in the above dictionary. problemchars is 3. \n",
    "\n",
    "Should we consider looking into 'other' tags? No because the `other` tag is a tag, and not a warning to the user about the tag type.\n",
    "\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Find Users"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#Find user function that takes in OSM file\n",
    "###Return a set and list of OSM contributors, where set is the unique list\n",
    "def find_users(filename):\n",
    "    users_set = set()\n",
    "    users_list = []\n",
    "    element_osm = [\"node\",\"way\",\"relation\"]\n",
    "    for event, element in ET.iterparse(filename):\n",
    "        if element.tag == \"node\" or element.tag == \"way\" or element.tag==\"relation\":\n",
    "            for osm_el in element_osm:\n",
    "                for el in element.iter(osm_el):\n",
    "                    user_id = el.attrib[\"uid\"]\n",
    "                    \n",
    "                    users_list.append(user_id)\n",
    "                    if user_id not in users_set:\n",
    "                        users_set.add(user_id)\n",
    "                    else:\n",
    "                        pass\n",
    "    return(users_set,users_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'1185712', '2035254', '2967601', '1860668', '4225299', '3916314', '25510', '1214881', '5007769', '94578', '290680', '2115749', '4902', '160138', '5359', '106828', '338351', '3125856', '4845203', '1494110', '3817650', '360392', '652230', '2318', '2560523', '2763448', '171119', '123633', '432824', '58305', '1403907', '3793530', '4325299', '2219338', '1836535', '45611', '5239832', '320343', '1861330', '53936', '1835512', '1731525', '2880297', '4148813', '416346', '169004', '2985232', '33757', '120468', '3282935', '322404', '190493', '665694', '3151684', '46220', '1546527', '2012966', '1556219', '2209894', '2819599', '72002', '28145', '383461', '25604', '60612', '174586', '119881', '3483725', '5475233', '3478684', '2332032', '2052349', '4518035', '81497', '2747838', '2229097', '3569951', '2512300', '21931', '2601744', '4893098', '2137840', '2240804', '5400451', '6585', '3215437', '567792', '8287', '4899678', '1745338', '606225', '5367179', '3696217', '2306749', '4115', '5667998', '2688775', '2886207', '214969', '4460614', '138911', '3526564', '3769434', '2593828', '6367', '100744', '190869', '398300', '3601534', '330773', '257555', '338843', '3095', '485507', '3397838', '107257', '2257567', '1260280', '828519', '199600', '100467', '950182', '1110270', '2773196', '83557', '5448688', '103253', '3272286', '2086526', '706170', '2655992', '37067', '3696204', '2393795', '411298', '38784', '3528573', '1597155', '3389275', '1758880', '5210438', '521541', '1212745', '381253', '3415463', '341519', '2176692', '2639622', '25867', '3696207', '573196', '550560', '1980320', '27781', '2724074', '1733620', '4288424', '1711265', '4386661', '162110', '2050600', '1282393', '4892902', '37137', '24555', '1775756', '130277', '5133699', '1529343', '14179', '1722904', '2682147', '500006', '1708763', '55774', '242352', '355242', '1535346', '23762', '2848193', '2015224', '2323512', '105946', '25996', '40397', '564585', '48060', '941449', '1829683', '2554698', '88961', '593205', '11154', '618377', '1355899', '1249504', '3397796', '305985', '4213591', '3397814', '4510966', '1249130', '4884078', '2032360', '4543547', '291298', '6975', '93800', '35935', '163803', '589596', '12671', '3696230', '429728', '682', '1213894', '2136684', '2508151', '703517', '1797586', '1210008', '2944689', '29017', '475877', '1883329', '3397846', '5222544', '36121', '2953895', '455164', '74705', '35195', '296793', '305001', '1240849', '2038932', '194231', '31231', '595221', '2100843', '1297360', '103340', '4503285', '3905761', '3521724', '1917271', '8909', '7168', '2580175', '1893220', '104461', '1083211', '290016', '2876693', '4314582', '371711', '665748', '3346582', '4742311', '202256', '1850735', '70092', '192581', '529836', '2370549', '5187925', '2240409', '1751737', '158274', '3178375', '73268', '2886662', '1660455', '3415446', '321578', '148800', '711243', '982633', '81024', '2328400', '2412184', '93285', '945595', '2697733', '5183376', '4896980', '4893041', '123364', '2847988', '1323941', '160949', '2049447', '25663', '401490', '1529919', '135163', '361207', '837425', '1247632', '3029661', '5853065', '532783', '206785', '13192', '829302', '116029', '2883452', '70696', '2764994', '3558911', '125457', '93788', '2946756', '136211', '597941', '349023', '2422893', '1980326', '1018369', '3624816', '297064', '6106546', '4967074', '3057995', '2061773', '1376118', '210173', '588517', '1408707', '2045423', '352170', '207745', '670691', '56597', '47892', '347787', '120160', '5184971', '4928324', '1586627', '626299', '583866', '2177', '231723', '3397844', '236172', '24452', '42429', '22946', '35254', '3696439', '109968', '4681914', '74275', '5236034', '2033336', '4109236', '235835', '88771', '190690', '479256', '303224', '3397534', '4801391', '2406578', '1577172', '3049198', '85177', '450292', '2925215', '81909', '201724', '4353543', '144449', '5185806', '1796809', '35372', '4651319', '178186', '533465', '5733618', '1211007', '4732', '1778256', '3260615', '3990', '2441939', '363927', '1240864', '2363939', '1814324', '53073', '473980', '5148403', '383858', '1750383', '445671', '623324', '540553', '3526347', '1850730', '594525', '278150', '12055', '431056', '429804', '190676', '2411764', '2820500', '2226712', '3233629', '3762030', '501715', '3845071', '2475974', '1212907', '145401', '186357', '3324338', '3021901', '1519895', '183737', '2254670', '1692237', '126019', '5193764', '1942315', '901335', '192522', '4847361', '26599', '2835928', '83660', '4138943', '231017', '1707766', '2021999', '2017972', '32952', '232126', '3207726', '2049452', '3516', '110263', '199837', '2733735', '908886', '2488002', '271668', '2334541', '1431737', '3397835', '564990', '5211', '635896', '53964', '2575095', '1295', '104519', '4971651', '13840', '82807', '3812920', '174995', '3469870', '613191', '4541740', '366306', '462373', '2262733', '1710478', '2076173', '5193438', '1430178', '4157731', '27454', '3696212', '286975', '2823295', '4951', '54598', '329833', '148776', '78656', '2903274', '1533136', '676820', '5083629', '902727', '154749', '3397837', '1916022', '138169', '3886701', '4724512', '364', '195150', '722137', '660287', '116769', '5147743', '3913914', '2524341', '371121', '143643', '6113775', '1697', '4706158', '1476146', '548288', '574654', '2859289', '667422', '1940279', '4007051', '6112777', '643201', '1926148', '195702', '1762578', '1346983', '1887210', '2411', '91499', '173918', '74292', '1306', '6144079', '933797', '1980416', '38090', '510836', '5204588', '473717', '621202', '1817244', '2406567', '1624486', '3189680', '79330', '3665135', '5201908', '5084247', '209032', '2219985', '2132368', '3396807', '31906', '482974', '238419', '3995305', '2377377', '1194938', '2814663', '51581', '44389', '2715689', '2590582', '3344856', '4892650', '1964364', '1611887', '1195290', '2644101', '1213904', '4095244', '4523751', '19235', '111529', '545896', '6008115', '698697', '4835230', '54585', '161619', '620663', '5355691', '1098862', '1249205', '2262304', '860575', '152074', '3877019', '1884370', '2016549', '775006', '128186', '999', '1211556', '2376998', '1616228', '192954', '627528', '638133', '3224809', '574053', '117995', '701297', '29639', '52797', '1679', '637707', '4991053', '3158', '2915075', '25981', '14351', '224058', '2052335', '674313', '530149', '397988', '272522', '4725451', '916555', '902185', '2905914', '161951', '131476', '87731', '38487', '292665', '5486800', '1664082', '16478', '1720031', '1353050', '4158534', '911915', '3397845', '584325', '3671294', '14293', '1425289', '3681372', '743575', '2715687', '171503', '1550640', '5257421', '1168707', '46789', '482916', '672542', '1087647', '1330847', '2181142', '714898', '2045275', '35667', '3181637', '1944474', '415728', '3696208', '522320', '2433512', '507503', '257972', '492311', '4224929', '644152', '92286', '1583864', '95132', '5094350', '634337', '696201', '735', '30360', '28775', '20587', '2884337', '2022156', '372674', '5495681', '2511706', '22925', '3616263', '143856', '120664', '632124', '322034', '373434', '671338', '2941385', '416100', '1934489', '1529374', '1781831', '3830366', '1393741', '436419', '31385', '1168909', '135329', '4624663', '1886770', '153669', '745333', '19532', '86066', '2411240', '296587', '1779875', '1951224', '74228', '223681', '1980274', '145231', '1154880', '365883', '77582', '3397848', '1802220', '1959811', '3109007', '1733584', '732991', '107681', '2245260', '1722605', '5074162', '3587922', '2012449', '3081437', '156109', '113624', '1976641', '481533', '147510', '27824', '3479270', '404841', '336460', '11711', '2760218', '4846473', '2813257', '131996', '28559', '3397842', '3129859', '318696', '2389416', '5024957', '760282', '262151', '172061', '5116622', '171516', '1309137', '4635105', '2866692', '28923', '3885962', '239998', '296869', '65950', '67236', '1044834', '2032478', '185205', '402624', '252811', '243490', '2748195', '4319107', '37753', '381909', '79401', '412560', '441116', '2474344'}\n"
     ]
    }
   ],
   "source": [
    "user_search = find_users(sf)\n",
    "\n",
    "#Get set of OSM contributors\n",
    "print(user_search[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We have the unique set of users in this sample of San Francisco, Ca data. However, we want to know the contribution rate of some user, and what the top contributor is?\n",
    "\n",
    "\n",
    "Below is a function for computing the stats of top contributors"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#user_stats function takes in a list of users(user_search[1])\n",
    "###outputs total contribution, contribution rate, mean, mean of contribution rate\n",
    "### standard deviation, and standard deviation of rate of contributions, in order.\n",
    "def user_stats(user_list):\n",
    "    #Make set of users\n",
    "    users_unique = set(user_list)\n",
    "    #Dictionary count\n",
    "    user_contribution_count = {}\n",
    "    #Count of contributions\n",
    "    total_contributions = 0\n",
    "    stdev = None\n",
    "    \n",
    "    contribution_rate = {}\n",
    "    \n",
    "    #Calculate count per user\n",
    "    for user in user_list:\n",
    "        if user not in user_contribution_count:\n",
    "            user_contribution_count[user] = 1\n",
    "        else:\n",
    "            user_contribution_count[user] +=1\n",
    "    #calculate total count        \n",
    "    for key, value in user_contribution_count.items():\n",
    "        total_contributions +=value\n",
    "    #Contribution rate     \n",
    "    for key, value in user_contribution_count.items():\n",
    "        contribution_rate[key] = value / total_contributions\n",
    "    #Get mean of contributions and rate of contributions    \n",
    "    mean_val = np.mean(list(user_contribution_count.values() ) )\n",
    "    mean_val_rate = np.mean(list(contribution_rate.values() ) )\n",
    "    \n",
    "    #Get std.dev of contributions and rate of contributions \n",
    "    stdev_val = np.std(list(user_contribution_count.values() ) )\n",
    "    stdev_val_rate =  np.std(list(contribution_rate.values() ) )\n",
    "    \n",
    "    return(total_contributions,contribution_rate,mean_val,mean_val_rate ,stdev_val,stdev_val_rate)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "user_statistics = user_stats(user_search[1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Contribution rate\n",
      "{'1185712': 5.413892046992583e-05, '2035254': 1.3534730117481457e-05, '1860668': 1.3534730117481457e-05, '4225299': 5.413892046992583e-05, '3916314': 1.3534730117481457e-05, '25510': 0.0008391532672838504, '3397534': 1.3534730117481457e-05, '5007769': 1.3534730117481457e-05, '94578': 0.2029262086513995, '290680': 6.767365058740728e-05, '2115749': 0.0038573980834822152, '4902': 1.3534730117481457e-05, '160138': 0.012668507389962644, '5359': 0.0002571598722321477, '106828': 0.00016241676140977748, '3125856': 1.3534730117481457e-05, '4845203': 0.006266580044393915, '3817650': 8.120838070488874e-05, '360392': 1.3534730117481457e-05, '652230': 1.3534730117481457e-05, '2318': 0.001272264631043257, '2560523': 2.7069460234962914e-05, '171119': 1.3534730117481457e-05, '123633': 0.0005278544745817769, '432824': 2.7069460234962914e-05, '58305': 1.3534730117481457e-05, '1403907': 2.7069460234962914e-05, '3793530': 1.3534730117481457e-05, '2219338': 0.054856261166152345, '1836535': 8.120838070488874e-05, '1861330': 1.3534730117481457e-05, '53936': 1.3534730117481457e-05, '2967601': 1.3534730117481457e-05, '1835512': 0.0001894862216447404, '1731525': 1.3534730117481457e-05, '2880297': 1.3534730117481457e-05, '4148813': 2.7069460234962914e-05, '416346': 0.0005278544745817769, '169004': 0.022345839423961886, '2985232': 0.0004466460938768881, '33757': 0.00013534730117481457, '120468': 0.0006632017757565914, '3282935': 1.3534730117481457e-05, '322404': 4.060419035244437e-05, '190493': 0.00024362514211466623, '665694': 4.060419035244437e-05, '2209894': 0.00012181257105733311, '46220': 4.060419035244437e-05, '2012966': 1.3534730117481457e-05, '1214881': 0.00014888203129229604, '2819599': 2.7069460234962914e-05, '158274': 6.767365058740728e-05, '72002': 6.767365058740728e-05, '28145': 6.767365058740728e-05, '383461': 0.000798549076931406, '2411': 6.767365058740728e-05, '25604': 0.0009068269178712576, '60612': 5.413892046992583e-05, '174586': 1.3534730117481457e-05, '119881': 0.0006496670456391099, '3483725': 0.0001894862216447404, '5475233': 1.3534730117481457e-05, '3478684': 4.060419035244437e-05, '2332032': 1.3534730117481457e-05, '4518035': 4.060419035244437e-05, '81497': 0.0002571598722321477, '2747838': 0.00010827784093985166, '3569951': 2.7069460234962914e-05, '2512300': 0.017216176709436416, '21931': 4.060419035244437e-05, '2137840': 1.3534730117481457e-05, '4893098': 0.003667911861837475, '2240804': 2.7069460234962914e-05, '6144079': 1.3534730117481457e-05, '5400451': 1.3534730117481457e-05, '6585': 6.767365058740728e-05, '3215437': 1.3534730117481457e-05, '1247632': 0.00014888203129229604, '933797': 0.011748145741973906, '567792': 0.0006090628552866656, '4899678': 5.413892046992583e-05, '1745338': 0.00012181257105733311, '606225': 1.3534730117481457e-05, '950182': 1.3534730117481457e-05, '5367179': 2.7069460234962914e-05, '3696217': 1.3534730117481457e-05, '2306749': 1.3534730117481457e-05, '4115': 1.3534730117481457e-05, '5667998': 1.3534730117481457e-05, '2688775': 1.3534730117481457e-05, '135329': 1.3534730117481457e-05, '2886207': 5.413892046992583e-05, '214969': 0.00016241676140977748, '138911': 2.7069460234962914e-05, '3526564': 0.00027069460234962914, '3769434': 5.413892046992583e-05, '2593828': 1.3534730117481457e-05, '6367': 1.3534730117481457e-05, '190869': 1.3534730117481457e-05, '398300': 2.7069460234962914e-05, '4732': 8.120838070488874e-05, '3601534': 1.3534730117481457e-05, '330773': 0.00014888203129229604, '74228': 1.3534730117481457e-05, '257555': 1.3534730117481457e-05, '338843': 1.3534730117481457e-05, '3095': 0.00012181257105733311, '485507': 1.3534730117481457e-05, '3397838': 1.3534730117481457e-05, '2257567': 5.413892046992583e-05, '1260280': 1.3534730117481457e-05, '338351': 1.3534730117481457e-05, '828519': 1.3534730117481457e-05, '199600': 0.00013534730117481457, '100467': 1.3534730117481457e-05, '1110270': 0.00010827784093985166, '83557': 2.7069460234962914e-05, '5448688': 0.00013534730117481457, '103253': 0.0001894862216447404, '3272286': 0.006077093822749175, '2086526': 1.3534730117481457e-05, '706170': 0.006415462075686211, '3696204': 1.3534730117481457e-05, '37067': 0.0004466460938768881, '2393795': 1.3534730117481457e-05, '38784': 0.00016241676140977748, '5495681': 0.00024362514211466623, '1597155': 0.00017595149152725895, '3389275': 1.3534730117481457e-05, '1758880': 1.3534730117481457e-05, '5210438': 1.3534730117481457e-05, '2655992': 2.7069460234962914e-05, '1212745': 1.3534730117481457e-05, '381253': 4.060419035244437e-05, '3415463': 1.3534730117481457e-05, '1494110': 0.00013534730117481457, '341519': 1.3534730117481457e-05, '2176692': 1.3534730117481457e-05, '2511706': 0.025215202208867957, '2639622': 0.00020302095176222187, '25867': 1.3534730117481457e-05, '3696207': 1.3534730117481457e-05, '573196': 5.413892046992583e-05, '411298': 4.060419035244437e-05, '27781': 4.060419035244437e-05, '2724074': 2.7069460234962914e-05, '1733620': 1.3534730117481457e-05, '4288424': 1.3534730117481457e-05, '1711265': 6.767365058740728e-05, '4892902': 0.002896432245141032, '162110': 0.0005819933950517027, '2050600': 8.120838070488874e-05, '1282393': 2.7069460234962914e-05, '37137': 2.7069460234962914e-05, '24555': 2.7069460234962914e-05, '2370549': 1.3534730117481457e-05, '1775756': 5.413892046992583e-05, '5133699': 4.060419035244437e-05, '1722904': 1.3534730117481457e-05, '14179': 5.413892046992583e-05, '2682147': 4.060419035244437e-05, '500006': 1.3534730117481457e-05, '1708763': 0.00020302095176222187, '55774': 0.0076200530561420605, '242352': 0.0007308754263439987, '355242': 0.0006496670456391099, '1535346': 1.3534730117481457e-05, '23762': 1.3534730117481457e-05, '2848193': 1.3534730117481457e-05, '2015224': 0.007971956039196579, '105946': 1.3534730117481457e-05, '25996': 1.3534730117481457e-05, '1850730': 0.0003383682529370364, '120664': 1.3534730117481457e-05, '40397': 2.7069460234962914e-05, '564585': 0.0005007850143468139, '48060': 1.3534730117481457e-05, '1980320': 1.3534730117481457e-05, '941449': 0.0034242867197228086, '1829683': 0.08999242055113421, '2763448': 1.3534730117481457e-05, '88961': 2.7069460234962914e-05, '593205': 4.060419035244437e-05, '11154': 0.0033836825293703642, '3233629': 5.413892046992583e-05, '618377': 1.3534730117481457e-05, '1355899': 1.3534730117481457e-05, '431056': 4.060419035244437e-05, '671338': 1.3534730117481457e-05, '100744': 5.413892046992583e-05, '2814663': 1.3534730117481457e-05, '3397796': 6.767365058740728e-05, '305985': 1.3534730117481457e-05, '4213591': 0.0006902712359915543, '3397814': 6.767365058740728e-05, '4510966': 8.120838070488874e-05, '1249130': 0.0004195766336419252, '4884078': 0.0018542580260949596, '2032360': 1.3534730117481457e-05, '4543547': 1.3534730117481457e-05, '291298': 4.060419035244437e-05, '6975': 1.3534730117481457e-05, '93800': 1.3534730117481457e-05, '35935': 1.3534730117481457e-05, '163803': 1.3534730117481457e-05, '589596': 0.000473715554111851, '3696230': 6.767365058740728e-05, '429728': 2.7069460234962914e-05, '682': 4.060419035244437e-05, '1213894': 1.3534730117481457e-05, '416100': 8.120838070488874e-05, '2136684': 4.060419035244437e-05, '4325299': 0.0003112987927020735, '2508151': 0.00010827784093985166, '3830366': 1.3534730117481457e-05, '1797586': 1.3534730117481457e-05, '501715': 2.7069460234962914e-05, '1210008': 1.3534730117481457e-05, '2944689': 1.3534730117481457e-05, '29017': 4.060419035244437e-05, '1883329': 1.3534730117481457e-05, '3397846': 1.3534730117481457e-05, '2328400': 2.7069460234962914e-05, '5222544': 1.3534730117481457e-05, '36121': 0.0019083969465648854, '2953895': 0.0013399382816306643, '5183376': 2.7069460234962914e-05, '74705': 4.060419035244437e-05, '35195': 0.0008391532672838504, '296793': 1.3534730117481457e-05, '1168909': 8.120838070488874e-05, '305001': 6.767365058740728e-05, '1240849': 0.12003952141194305, '5239832': 5.413892046992583e-05, '2038932': 1.3534730117481457e-05, '2412184': 2.7069460234962914e-05, '2100843': 5.413892046992583e-05, '595221': 0.0002977640625845921, '4892650': 0.0028558280547885877, '93285': 5.413892046992583e-05, '1297360': 2.7069460234962914e-05, '103340': 0.00027069460234962914, '4503285': 4.060419035244437e-05, '3905761': 1.3534730117481457e-05, '3521724': 5.413892046992583e-05, '1917271': 5.413892046992583e-05, '8909': 4.060419035244437e-05, '7168': 2.7069460234962914e-05, '2580175': 1.3534730117481457e-05, '1893220': 6.767365058740728e-05, '290016': 1.3534730117481457e-05, '1083211': 2.7069460234962914e-05, '2876693': 2.7069460234962914e-05, '4314582': 1.3534730117481457e-05, '371711': 1.3534730117481457e-05, '2847988': 0.0028828975150235504, '665748': 4.060419035244437e-05, '3346582': 0.0001894862216447404, '4742311': 0.0004195766336419252, '202256': 0.0006496670456391099, '1850735': 2.7069460234962914e-05, '70092': 2.7069460234962914e-05, '550560': 0.00012181257105733311, '2411240': 1.3534730117481457e-05, '192581': 0.0006632017757565914, '3324338': 2.7069460234962914e-05, '5187925': 1.3534730117481457e-05, '2240409': 4.060419035244437e-05, '1751737': 1.3534730117481457e-05, '3178375': 0.00023009041199718478, '73268': 6.767365058740728e-05, '2049447': 4.060419035244437e-05, '2886662': 4.060419035244437e-05, '1660455': 5.413892046992583e-05, '3415446': 1.3534730117481457e-05, '148800': 1.3534730117481457e-05, '1951224': 5.413892046992583e-05, '321578': 0.0003112987927020735, '982633': 4.060419035244437e-05, '711243': 4.060419035244437e-05, '126019': 1.3534730117481457e-05, '81024': 0.0007038059661090358, '2697733': 1.3534730117481457e-05, '945595': 1.3534730117481457e-05, '4896980': 0.0029370364354934764, '529836': 2.7069460234962914e-05, '123364': 0.00012181257105733311, '1323941': 1.3534730117481457e-05, '160949': 9.47431108223702e-05, '25663': 1.3534730117481457e-05, '401490': 1.3534730117481457e-05, '1529919': 0.00013534730117481457, '135163': 6.767365058740728e-05, '361207': 0.0006496670456391099, '837425': 0.00017595149152725895, '172061': 2.7069460234962914e-05, '901335': 1.3534730117481457e-05, '206785': 1.3534730117481457e-05, '3029661': 0.007687726706729468, '5853065': 1.3534730117481457e-05, '532783': 0.0005413892046992583, '829302': 4.060419035244437e-05, '13192': 1.3534730117481457e-05, '116029': 0.002152022088679552, '2883452': 1.3534730117481457e-05, '70696': 1.3534730117481457e-05, '365883': 0.00010827784093985166, '2764994': 6.767365058740728e-05, '3558911': 1.3534730117481457e-05, '83660': 9.47431108223702e-05, '125457': 2.7069460234962914e-05, '2946756': 1.3534730117481457e-05, '136211': 4.060419035244437e-05, '4386661': 9.47431108223702e-05, '349023': 1.3534730117481457e-05, '1980326': 1.3534730117481457e-05, '1018369': 0.00040604190352444374, '698697': 1.3534730117481457e-05, '3624816': 1.3534730117481457e-05, '6106546': 1.3534730117481457e-05, '26599': 1.3534730117481457e-05, '54585': 0.00012181257105733311, '4967074': 0.0005819933950517027, '3057995': 0.00324833522819555, '1707766': 2.7069460234962914e-05, '210173': 0.002909966975258513, '588517': 1.3534730117481457e-05, '620663': 1.3534730117481457e-05, '1408707': 1.3534730117481457e-05, '2045423': 4.060419035244437e-05, '79330': 8.120838070488874e-05, '352170': 1.3534730117481457e-05, '207745': 0.00014888203129229604, '1098862': 4.060419035244437e-05, '521541': 1.3534730117481457e-05, '56597': 1.3534730117481457e-05, '4928324': 1.3534730117481457e-05, '347787': 1.3534730117481457e-05, '120160': 0.0003383682529370364, '194231': 0.00012181257105733311, '1586627': 1.3534730117481457e-05, '626299': 1.3534730117481457e-05, '583866': 1.3534730117481457e-05, '2177': 4.060419035244437e-05, '130277': 1.3534730117481457e-05, '231723': 1.3534730117481457e-05, '3397844': 2.7069460234962914e-05, '42429': 0.0011369173298684423, '3696439': 1.3534730117481457e-05, '22946': 0.0025309945319690326, '109968': 2.7069460234962914e-05, '74275': 6.767365058740728e-05, '5236034': 1.3534730117481457e-05, '2033336': 1.3534730117481457e-05, '4109236': 2.7069460234962914e-05, '190676': 8.120838070488874e-05, '235835': 1.3534730117481457e-05, '88771': 1.3534730117481457e-05, '190690': 1.3534730117481457e-05, '479256': 0.00013534730117481457, '4801391': 2.7069460234962914e-05, '1529343': 5.413892046992583e-05, '2406578': 2.7069460234962914e-05, '1577172': 0.00012181257105733311, '3049198': 0.00012181257105733311, '85177': 0.00027069460234962914, '3151684': 6.767365058740728e-05, '450292': 1.3534730117481457e-05, '2925215': 1.3534730117481457e-05, '81909': 6.767365058740728e-05, '1546527': 2.7069460234962914e-05, '201724': 0.0009338963781062205, '4353543': 1.3534730117481457e-05, '5185806': 2.7069460234962914e-05, '1796809': 0.00023009041199718478, '35372': 1.3534730117481457e-05, '4651319': 1.3534730117481457e-05, '178186': 1.3534730117481457e-05, '533465': 1.3534730117481457e-05, '5733618': 1.3534730117481457e-05, '3587922': 2.7069460234962914e-05, '1211007': 1.3534730117481457e-05, '1778256': 1.3534730117481457e-05, '3260615': 0.000473715554111851, '3990': 2.7069460234962914e-05, '2441939': 4.060419035244437e-05, '363927': 1.3534730117481457e-05, '1240864': 1.3534730117481457e-05, '1814324': 1.3534730117481457e-05, '53073': 0.0002977640625845921, '473980': 2.7069460234962914e-05, '5148403': 1.3534730117481457e-05, '383858': 0.0004195766336419252, '1750383': 1.3534730117481457e-05, '445671': 4.060419035244437e-05, '623324': 0.0002165556818797033, '3526347': 0.0004466460938768881, '2488002': 1.3534730117481457e-05, '594525': 4.060419035244437e-05, '278150': 2.7069460234962914e-05, '12055': 2.7069460234962914e-05, '2411764': 1.3534730117481457e-05, '429804': 1.3534730117481457e-05, '2820500': 1.3534730117481457e-05, '3762030': 1.3534730117481457e-05, '2226712': 0.0733582372367495, '3845071': 0.0005143197444642954, '2475974': 4.060419035244437e-05, '1212907': 4.060419035244437e-05, '145401': 0.00017595149152725895, '271668': 6.767365058740728e-05, '186357': 1.3534730117481457e-05, '3021901': 1.3534730117481457e-05, '1519895': 0.0003112987927020735, '183737': 8.120838070488874e-05, '2254670': 2.7069460234962914e-05, '1692237': 0.00014888203129229604, '5193764': 1.3534730117481457e-05, '1942315': 1.3534730117481457e-05, '4847361': 0.0010421742190460722, '192522': 1.3534730117481457e-05, '2323512': 9.47431108223702e-05, '4138943': 8.120838070488874e-05, '2835928': 0.011856423582913757, '231017': 1.3534730117481457e-05, '2017972': 1.3534730117481457e-05, '2021999': 1.3534730117481457e-05, '32952': 1.3534730117481457e-05, '232126': 1.3534730117481457e-05, '3207726': 0.0004466460938768881, '2049452': 0.0004195766336419252, '3516': 1.3534730117481457e-05, '93788': 4.060419035244437e-05, '110263': 0.00010827784093985166, '199837': 1.3534730117481457e-05, '2733735': 2.7069460234962914e-05, '5355691': 5.413892046992583e-05, '597941': 6.767365058740728e-05, '908886': 1.3534730117481457e-05, '2334541': 1.3534730117481457e-05, '1431737': 1.3534730117481457e-05, '3397835': 2.7069460234962914e-05, '564990': 6.767365058740728e-05, '156109': 0.0003789724432894808, '5211': 1.3534730117481457e-05, '635896': 0.00048725028422933245, '53964': 1.3534730117481457e-05, '2575095': 2.7069460234962914e-05, '1295': 0.0013128688213957015, '104519': 1.3534730117481457e-05, '4971651': 0.0002571598722321477, '13840': 9.47431108223702e-05, '82807': 5.413892046992583e-05, '3812920': 1.3534730117481457e-05, '174995': 5.413892046992583e-05, '3469870': 1.3534730117481457e-05, '613191': 1.3534730117481457e-05, '4541740': 1.3534730117481457e-05, '366306': 1.3534730117481457e-05, '462373': 0.0008256185371663689, '2262733': 6.767365058740728e-05, '1710478': 2.7069460234962914e-05, '2076173': 1.3534730117481457e-05, '1430178': 1.3534730117481457e-05, '27454': 6.767365058740728e-05, '35254': 1.3534730117481457e-05, '3696212': 1.3534730117481457e-05, '286975': 1.3534730117481457e-05, '2823295': 0.005738725569812138, '4951': 0.00012181257105733311, '329833': 1.3534730117481457e-05, '52797': 1.3534730117481457e-05, '148776': 2.7069460234962914e-05, '2903274': 1.3534730117481457e-05, '1533136': 6.767365058740728e-05, '2554698': 0.0016106328839802933, '676820': 1.3534730117481457e-05, '5083629': 1.3534730117481457e-05, '902727': 0.00020302095176222187, '297064': 1.3534730117481457e-05, '1916022': 1.3534730117481457e-05, '3397837': 1.3534730117481457e-05, '2052349': 1.3534730117481457e-05, '138169': 4.060419035244437e-05, '3886701': 1.3534730117481457e-05, '364': 8.120838070488874e-05, '195150': 0.00013534730117481457, '722137': 0.00048725028422933245, '660287': 0.0009745005684586649, '1679': 0.0005007850143468139, '3189680': 6.767365058740728e-05, '637707': 0.00010827784093985166, '116769': 0.0003383682529370364, '5147743': 1.3534730117481457e-05, '3913914': 1.3534730117481457e-05, '2524341': 1.3534730117481457e-05, '371121': 0.005549239348167398, '143643': 0.0004331113637594066, '6113775': 2.7069460234962914e-05, '1697': 1.3534730117481457e-05, '638133': 1.3534730117481457e-05, '4706158': 1.3534730117481457e-05, '1476146': 2.7069460234962914e-05, '548288': 0.00010827784093985166, '574654': 0.00024362514211466623, '2061773': 1.3534730117481457e-05, '2859289': 0.0005955281251691842, '667422': 1.3534730117481457e-05, '1940279': 6.767365058740728e-05, '4007051': 8.120838070488874e-05, '3397842': 5.413892046992583e-05, '643201': 2.7069460234962914e-05, '2052335': 1.3534730117481457e-05, '1376118': 0.0009068269178712576, '1926148': 1.3534730117481457e-05, '195702': 0.00039250717340696224, '1762578': 0.0005413892046992583, '1346983': 5.413892046992583e-05, '1887210': 1.3534730117481457e-05, '91499': 0.00269341129337881, '1249504': 0.004710086080883547, '173918': 1.3534730117481457e-05, '74292': 8.120838070488874e-05, '2760218': 0.0011369173298684423, '1306': 0.001448216122570516, '1980416': 2.7069460234962914e-05, '5204588': 1.3534730117481457e-05, '510836': 0.05209517622218613, '473717': 9.47431108223702e-05, '2229097': 0.00013534730117481457, '621202': 0.0003789724432894808, '1817244': 0.00012181257105733311, '2406567': 2.7069460234962914e-05, '1624486': 1.3534730117481457e-05, '3665135': 1.3534730117481457e-05, '5201908': 1.3534730117481457e-05, '5084247': 1.3534730117481457e-05, '209032': 1.3534730117481457e-05, '2219985': 1.3534730117481457e-05, '2132368': 8.120838070488874e-05, '3396807': 1.3534730117481457e-05, '2363939': 1.3534730117481457e-05, '31906': 1.3534730117481457e-05, '482974': 1.3534730117481457e-05, '238419': 1.3534730117481457e-05, '3995305': 1.3534730117481457e-05, '2377377': 0.01476639055817227, '1194938': 6.767365058740728e-05, '51581': 1.3534730117481457e-05, '44389': 1.3534730117481457e-05, '2590582': 1.3534730117481457e-05, '3344856': 0.0011775215202208867, '1611887': 1.3534730117481457e-05, '1964364': 1.3534730117481457e-05, '1195290': 1.3534730117481457e-05, '916555': 1.3534730117481457e-05, '2644101': 0.005765795030047101, '1213904': 0.000473715554111851, '4095244': 2.7069460234962914e-05, '2905914': 0.005170266904877917, '2601744': 2.7069460234962914e-05, '19235': 0.00013534730117481457, '111529': 4.060419035244437e-05, '545896': 1.3534730117481457e-05, '6008115': 1.3534730117481457e-05, '4835230': 1.3534730117481457e-05, '5193438': 1.3534730117481457e-05, '161619': 6.767365058740728e-05, '2262304': 1.3534730117481457e-05, '1249205': 1.3534730117481457e-05, '860575': 2.7069460234962914e-05, '4157731': 1.3534730117481457e-05, '152074': 8.120838070488874e-05, '3877019': 0.0007173406962265172, '1884370': 0.0002571598722321477, '2016549': 1.3534730117481457e-05, '775006': 2.7069460234962914e-05, '38090': 1.3534730117481457e-05, '128186': 2.7069460234962914e-05, '3528573': 1.3534730117481457e-05, '999': 1.3534730117481457e-05, '1211556': 1.3534730117481457e-05, '2376998': 2.7069460234962914e-05, '1616228': 0.00014888203129229604, '192954': 1.3534730117481457e-05, '3224809': 1.3534730117481457e-05, '574053': 1.3534730117481457e-05, '117995': 4.060419035244437e-05, '701297': 4.060419035244437e-05, '29639': 2.7069460234962914e-05, '12671': 2.7069460234962914e-05, '54598': 5.413892046992583e-05, '670691': 0.00010827784093985166, '4991053': 1.3534730117481457e-05, '3158': 0.0010827784093985166, '2915075': 0.007403497374262357, '25981': 0.0006902712359915543, '14351': 1.3534730117481457e-05, '224058': 0.00010827784093985166, '674313': 1.3534730117481457e-05, '530149': 1.3534730117481457e-05, '397988': 0.0012993340912782199, '272522': 1.3534730117481457e-05, '78656': 1.3534730117481457e-05, '4725451': 1.3534730117481457e-05, '902185': 1.3534730117481457e-05, '161951': 2.7069460234962914e-05, '131476': 2.7069460234962914e-05, '1556219': 1.3534730117481457e-05, '87731': 1.3534730117481457e-05, '2715689': 1.3534730117481457e-05, '38487': 0.00016241676140977748, '292665': 4.060419035244437e-05, '47892': 6.767365058740728e-05, '5486800': 1.3534730117481457e-05, '16478': 1.3534730117481457e-05, '5184971': 1.3534730117481457e-05, '1720031': 1.3534730117481457e-05, '1353050': 2.7069460234962914e-05, '4158534': 1.3534730117481457e-05, '911915': 1.3534730117481457e-05, '3397845': 6.767365058740728e-05, '584325': 0.0002571598722321477, '3671294': 1.3534730117481457e-05, '1425289': 5.413892046992583e-05, '3681372': 4.060419035244437e-05, '8287': 2.7069460234962914e-05, '743575': 1.3534730117481457e-05, '2715687': 0.0053056142060527315, '296869': 8.120838070488874e-05, '171503': 0.00012181257105733311, '1664082': 4.060419035244437e-05, '5257421': 4.060419035244437e-05, '1168707': 2.7069460234962914e-05, '46789': 0.0009880352985761464, '482916': 0.0007444101564614802, '672542': 1.3534730117481457e-05, '144449': 1.3534730117481457e-05, '1087647': 1.3534730117481457e-05, '1330847': 0.005495100427697471, '2181142': 8.120838070488874e-05, '714898': 2.7069460234962914e-05, '2045275': 1.3534730117481457e-05, '35667': 0.0001894862216447404, '703517': 6.767365058740728e-05, '3181637': 2.7069460234962914e-05, '1944474': 1.3534730117481457e-05, '415728': 1.3534730117481457e-05, '3696208': 1.3534730117481457e-05, '522320': 4.060419035244437e-05, '154749': 4.060419035244437e-05, '2433512': 1.3534730117481457e-05, '507503': 2.7069460234962914e-05, '257972': 1.3534730117481457e-05, '492311': 2.7069460234962914e-05, '4224929': 0.0008797574576362947, '644152': 9.47431108223702e-05, '92286': 2.7069460234962914e-05, '1583864': 4.060419035244437e-05, '95132': 2.7069460234962914e-05, '5094350': 8.120838070488874e-05, '634337': 5.413892046992583e-05, '696201': 2.7069460234962914e-05, '735': 1.3534730117481457e-05, '30360': 1.3534730117481457e-05, '4724512': 2.7069460234962914e-05, '28775': 0.005562774078284879, '20587': 0.00036543771317199935, '2884337': 1.3534730117481457e-05, '2022156': 4.060419035244437e-05, '372674': 5.413892046992583e-05, '475877': 1.3534730117481457e-05, '22925': 0.001123382599750961, '236172': 0.0011775215202208867, '3616263': 0.00010827784093985166, '143856': 1.3534730117481457e-05, '632124': 1.3534730117481457e-05, '322034': 0.00013534730117481457, '627528': 1.3534730117481457e-05, '373434': 1.3534730117481457e-05, '2941385': 1.3534730117481457e-05, '1934489': 0.0027069460234962913, '1529374': 1.3534730117481457e-05, '1781831': 4.060419035244437e-05, '1393741': 1.3534730117481457e-05, '436419': 0.0005007850143468139, '31385': 2.7069460234962914e-05, '1886770': 2.7069460234962914e-05, '153669': 0.00733582372367495, '745333': 1.3534730117481457e-05, '19532': 0.00014888203129229604, '86066': 1.3534730117481457e-05, '4681914': 1.3534730117481457e-05, '296587': 1.3534730117481457e-05, '1779875': 6.767365058740728e-05, '223681': 1.3534730117481457e-05, '1980274': 1.3534730117481457e-05, '145231': 4.060419035244437e-05, '1154880': 1.3534730117481457e-05, '77582': 2.7069460234962914e-05, '3397848': 1.3534730117481457e-05, '455164': 0.001421146662335553, '1802220': 2.7069460234962914e-05, '540553': 1.3534730117481457e-05, '1959811': 1.3534730117481457e-05, '3109007': 2.7069460234962914e-05, '1733584': 8.120838070488874e-05, '732991': 0.0004331113637594066, '107681': 1.3534730117481457e-05, '2245260': 9.47431108223702e-05, '1722605': 0.00010827784093985166, '5074162': 0.0001894862216447404, '2012449': 1.3534730117481457e-05, '2773196': 2.7069460234962914e-05, '320343': 1.3534730117481457e-05, '3081437': 2.7069460234962914e-05, '113624': 1.3534730117481457e-05, '1976641': 1.3534730117481457e-05, '481533': 0.0006361323155216285, '147510': 0.0035325645606626606, '1550640': 4.060419035244437e-05, '27824': 2.7069460234962914e-05, '3479270': 6.767365058740728e-05, '404841': 0.0008526879974013319, '45611': 1.3534730117481457e-05, '336460': 8.120838070488874e-05, '11711': 1.3534730117481457e-05, '4846473': 1.3534730117481457e-05, '2813257': 1.3534730117481457e-05, '131996': 0.0013264035515131829, '28559': 1.3534730117481457e-05, '4893041': 0.0038032591630122897, '3129859': 1.3534730117481457e-05, '318696': 0.00013534730117481457, '2389416': 2.7069460234962914e-05, '5024957': 1.3534730117481457e-05, '760282': 0.00014888203129229604, '262151': 0.00010827784093985166, '303224': 1.3534730117481457e-05, '5116622': 0.0008526879974013319, '171516': 1.3534730117481457e-05, '1309137': 1.3534730117481457e-05, '4635105': 1.3534730117481457e-05, '2866692': 1.3534730117481457e-05, '28923': 2.7069460234962914e-05, '3885962': 0.000473715554111851, '37753': 1.3534730117481457e-05, '104461': 1.3534730117481457e-05, '31231': 1.3534730117481457e-05, '239998': 0.00013534730117481457, '2422893': 2.7069460234962914e-05, '65950': 0.001245195170808294, '67236': 0.0006902712359915543, '1044834': 1.3534730117481457e-05, '2032478': 1.3534730117481457e-05, '185205': 1.3534730117481457e-05, '24452': 0.002720480753613773, '402624': 0.0011775215202208867, '252811': 1.3534730117481457e-05, '4523751': 0.0002842293324671106, '243490': 0.0003519029830545179, '4460614': 1.3534730117481457e-05, '2748195': 0.01867792756212441, '14293': 0.02015321314492989, '107257': 2.7069460234962914e-05, '6112777': 1.3534730117481457e-05, '4319107': 5.413892046992583e-05, '412560': 1.3534730117481457e-05, '381909': 0.0062530453142764335, '79401': 0.0002571598722321477, '441116': 0.0017730496453900709, '4624663': 1.3534730117481457e-05, '2474344': 0.00020302095176222187}\n"
     ]
    }
   ],
   "source": [
    "#Contribution rate\n",
    "print(\"Contribution rate\")\n",
    "print(user_statistics[1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Most contributions from User: 94578 \n",
      "with a total contribution rate of: 0.2029262086513995\n"
     ]
    }
   ],
   "source": [
    "max_user = None\n",
    "max_user_value = 0\n",
    "for key,value in user_statistics[1].items():\n",
    "    if value >max_user_value:\n",
    "        max_user_value = value\n",
    "        max_user = key\n",
    "print(\"Most contributions from User:\",max_user,\"\\nwith a total contribution rate of:\",max_user_value)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Average number of Contributions\n",
      "95.5808538163 \n",
      "\n",
      "Total count of Contributions\n",
      "73884 \n",
      "\n",
      "Average rate of Contributions\n",
      "0.0012936610608 \n",
      "\n",
      "Standard deviation of Contributions\n",
      "738.607732077 \n",
      "\n",
      "Standard deviation of the rate of Contributions\n",
      "0.00999685631635 \n",
      "\n"
     ]
    }
   ],
   "source": [
    "#Average number of contributions\n",
    "print(\"Average number of Contributions\")\n",
    "print(user_statistics[2],\"\\n\")\n",
    "\n",
    "#Total count of contributions\n",
    "print(\"Total count of Contributions\")\n",
    "print(user_statistics[0],\"\\n\")\n",
    "\n",
    "#average rate of contributions\n",
    "print(\"Average rate of Contributions\")\n",
    "print(user_statistics[3],\"\\n\")\n",
    "\n",
    "#Stdev of number of contributions\n",
    "print(\"Standard deviation of Contributions\")\n",
    "print(user_statistics[4],\"\\n\")\n",
    "\n",
    "#Stdev of rate of contributions\n",
    "print(\"Standard deviation of the rate of Contributions\")\n",
    "print(user_statistics[5],\"\\n\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Auditing Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We determine the integrity of our data. We look into the street key and values entries to determine if they are correctly entered."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#Regular expression for street type for ignoring b,repitition of S, and some other character with 0 or 1 repititions\n",
    "\n",
    "street_type_re = re.compile(r'St\\.|St{2,}|Rd|Rd.|Ave{3,}', re.IGNORECASE)\n",
    "#Expected street spelling list\n",
    "expected = [\"Street\", \"Avenue\", \"Boulevard\", \"Drive\", \"Court\", \"Place\", \"Square\", \"Lane\", \"Road\", \n",
    "            \"Trail\", \"Parkway\", \"Commons\"]\n",
    "#Map street abbr. to actual word\n",
    "mapping = { \"St\": \"Street\",\n",
    "            \"St.\": \"Street\", \"Ave\":\"Avenue\",\"Rd\":\"Road\"\n",
    "            }\n",
    "\n",
    "mapping_abbrev = { 'W ': 'West ', 'S ': 'South ', 'N ': 'North ', 'E ': 'East ',\n",
    "                   'W. ': 'West ', 'S. ': 'South', 'N. ': 'North ', 'E. ': 'East '\n",
    "                 }\n",
    "mapping_zipcodes = ['95356','95307','95358']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#Create audit_street_type function with parameters street_types,street_name\n",
    "## to obtain mispelled streets\n",
    "def audit_street_type(street_types,street_name):\n",
    "    street_name = street_name.strip(\"{\")\n",
    "    street_name = street_name.strip(\"}\")\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] = (street_name)\n",
    "            \n",
    "#Audit state type\n",
    "state_types = []\n",
    "def audit_state_type(state_types,state):\n",
    "    if len(state) !=2:\n",
    "        state_types.append(state)\n",
    "        \n",
    "zipcode_types = []\n",
    "def audit_zipcode_type(zipcode_types,zipcode):\n",
    "    if len(zipcode) != 5:\n",
    "        zipcode_types.append(zipcode)\n",
    "        \n",
    "housenumber_type_re = re.compile(r'^\\d+(-?\\d)*$')\n",
    "\n",
    "housephone_number_types = []\n",
    "\n",
    "#Audit housephone values\n",
    "def audit_housephone(housephone_number_types, number):\n",
    "    m = housenumber_type_re.search(number)\n",
    "    if not m:\n",
    "        housephone_number_types.append(number)\n",
    "\n",
    "phone_number_types = []\n",
    "\n",
    "#Audit phone entires. We use phonenumbers library to verify #'s\n",
    "def audit_phone(phone_number_types,number):\n",
    "    if number.startswith(\"+\"):\n",
    "        number = number[1:]\n",
    "    z = phonenumbers.parse(number,\"US\")\n",
    "    v = phonenumbers.is_possible_number(z)\n",
    "    if not v:\n",
    "        phone_number_types.append(number)\n",
    "#Audit website values. We use validators to confirm url entry\n",
    "website_types = []\n",
    "def audit_website(website_types,website):\n",
    "    if not website.startswith('http'):\n",
    "        website = 'http://' + website\n",
    "    if not validators.url(website):\n",
    "        website_types.append(website)\n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#identify street in element's key\n",
    "def is_street_name(elem):\n",
    "    return (elem.attrib['k'] == \"addr:street\")\n",
    "\n",
    "\n",
    "def is_street_name(elem):\n",
    "    return (elem.tag == \"tag\") and (elem.attrib['k'] == \"addr:street\")\n",
    "\n",
    "def is_state(elem):\n",
    "    return (elem.tag == \"tag\") and (elem.attrib['k'] == \"addr:state\" or elem.attrib['k'] == \"is_in:state_code\")\n",
    "\n",
    "def is_postcode(elem):\n",
    "    return (elem.tag == \"tag\") and (elem.attrib['k'] == \"addr:postcode\")\n",
    "\n",
    "def is_housenumber(elem):\n",
    "    return (elem.tag == \"tag\") and (elem.attrib['k'] == \"addr:housenumber\")\n",
    "\n",
    "def is_phone(elem):\n",
    "    return (elem.tag == \"tag\") and (elem.attrib['k'] == 'phone')\n",
    "\n",
    "def is_website(elem):\n",
    "    return (elem.tag == \"tag\") and (elem.attrib['k'] == \"website\" or elem.attrib['k'] == \"url\" or \\\n",
    "                                    (elem.attrib['k'] == \"source\" and elem.attrib['v'].startswith(\"http\")))\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def printer(type_of):\n",
    "    pprint.pprint(type_of)\n",
    "\n",
    "#audit function that takes in osm file\n",
    "def audit(osmfile):\n",
    "    #Read in osmfile\n",
    "    osm_file = open(osmfile,'r',encoding=\"utf-8\")\n",
    "    #Create empty defaultdict \n",
    "    street_types = defaultdict(set)\n",
    "    \n",
    "    #go through each element in osm file. If it is a node or a tag, and has child tags, then\n",
    "    ##we audit the street type and tag attribute v\n",
    "    for event, element in ET.iterparse(osm_file,events =(\"start\",) ):\n",
    "        if element.tag == \"node\" or element.tag==\"way\":\n",
    "            for tag in element.iter(\"tag\"):\n",
    "                if is_street_name(tag):\n",
    "                    audit_street_type(street_types, tag.attrib['v'])\n",
    "    osm_file.close()\n",
    "    return(street_types)\n",
    "\n",
    "\n",
    "#Additional audits for several variables, all in one. \n",
    "def audit_additional(file):\n",
    "    for event, elem in ET.iterparse(file):\n",
    "        if is_state(elem):\n",
    "            audit_state_type(state_types, elem.attrib['v'])\n",
    "        elif is_postcode(elem):\n",
    "            audit_zipcode_type(zipcode_types, elem.attrib['v'])\n",
    "        elif is_housenumber(elem):\n",
    "            audit_housephone(housephone_number_types, elem.attrib['v'])\n",
    "        elif is_phone(elem):\n",
    "            audit_phone(phone_number_types, elem.attrib['v'])\n",
    "        elif is_website(elem):\n",
    "            audit_website(website_types, elem.attrib['v'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#Function update_name that has parameters name and mapping\n",
    "def update_name(name, mapping):\n",
    "    m = street_type_re.search(name)\n",
    "    if m:\n",
    "        street_type = m.group()\n",
    "        #If the name exist in mapping keys, it means that's a problem and we should fix it.\n",
    "        if street_type in mapping.keys():\n",
    "            name = re.sub(street_type, mapping[street_type], name)\n",
    "    return(name)\n",
    "\n",
    "def update_state(state, state_list):\n",
    "    if state in state_list:\n",
    "        state = \"CA\"\n",
    "    return(state)\n",
    "\n",
    "\n",
    "#postcode_type_re = re.compile(r'[0-9]+')\n",
    "def update_postcode(postcode_types):\n",
    "    if type(postcode_types) is type([]):\n",
    "        pc_list = []\n",
    "        for postcode in postcode_types:\n",
    "            pc_list.append(postcode.split(\" \")[1])\n",
    "\n",
    "            return(pc_list)\n",
    "    elif type(postcode_types) is type(''):\n",
    "        if len(postcode_types) !=5:\n",
    "            postcode = postcode_types.split(\" \")\n",
    "            return(postcode[1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "defaultdict(<class 'set'>, {'rd': '3rd Street'})\n"
     ]
    }
   ],
   "source": [
    "st_types = audit(sf)\n",
    "print(st_types)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'rd': '3rd Street'}\n"
     ]
    }
   ],
   "source": [
    "dict_st_stypes = dict(st_types)\n",
    "print(dict_st_stypes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "3rd Street\n"
     ]
    }
   ],
   "source": [
    "#For every way in st_types\n",
    "for st_type, ways in st_types.items():\n",
    "    #For every name in way\n",
    "    print(ways)\n",
    "    better_name = update_name(ways, mapping)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'3rd Street'"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "better_name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "3rd Street\n",
      "Turned into\n",
      "3rd Street\n",
      "3rd Street\n",
      "Turned into\n",
      "3rd Street\n",
      "3rd Street\n",
      "Turned into\n",
      "3rd Street\n",
      "3rd Street\n",
      "Turned into\n",
      "3rd Street\n"
     ]
    }
   ],
   "source": [
    "st_types_set = dict(st_types)\n",
    "for event, element in ET.iterparse(sf, events=(\"start\",)):\n",
    "    for tag in element.iter(\"tag\"):\n",
    "        if(tag.attrib['k']==\"addr:street\"):\n",
    "            for key, val in st_types.items():\n",
    "                if tag.attrib['v'] == val:\n",
    "                    print(tag.attrib['v'])\n",
    "                    tag.attrib['v'] = better_name\n",
    "                    print(\"Turned into\")\n",
    "                    print(tag.attrib['v'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The above code lists 4 values of \"3rd Street\" updated to some new value. However, it's still the same. \n",
    "\n",
    "We need to see the condition on how this false negative is picked up in our algorithm.\n",
    "\n",
    "If we look closely, we observe the regular expressions \"street_type_re\" enabling our algorithm to pick up \"3rd Street\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'k': 'ref', 'v': '35'}\n",
      "{'k': 'exit_to', 'v': 'Strobridge Avenue'}\n",
      "{'k': 'highway', 'v': 'motorway_junction'}\n",
      "{'k': 'source', 'v': 'PGS'}\n",
      "{'k': 'source', 'v': 'PGS'}\n",
      "{'k': 'ref', 'v': '26'}\n",
      "{'k': 'exit_to', 'v': 'Tennyson Road'}\n",
      "{'k': 'highway', 'v': 'motorway_junction'}\n",
      "{'k': 'created_by', 'v': 'JOSM'}\n",
      "{'k': 'created_by', 'v': 'JOSM'}\n",
      "{'k': 'barrier', 'v': 'gate'}\n",
      "{'k': 'created_by', 'v': 'JOSM'}\n",
      "{'k': 'created_by', 'v': 'JOSM'}\n",
      "{'k': 'highway', 'v': 'stop'}\n",
      "{'k': 'highway', 'v': 'turning_circle'}\n",
      "{'k': 'ref', 'v': '35'}\n",
      "{'k': 'exit_to', 'v': 'Strobridge Avenue'}\n",
      "{'k': 'highway', 'v': 'motorway_junction'}\n",
      "{'k': 'ref', 'v': '35'}\n",
      "{'k': 'exit_to', 'v': 'Strobridge Avenue'}\n",
      "{'k': 'highway', 'v': 'motorway_junction'}\n"
     ]
    }
   ],
   "source": [
    "#Print out the first 10 tags \n",
    "##If you want to print out more, take off break portion\n",
    "i = 0\n",
    "for event, element in ET.iterparse(sf, events=(\"start\",)):\n",
    "    #----Break area-----\n",
    "    i+=1\n",
    "    if i ==10:\n",
    "        break\n",
    "    #----------------\n",
    "    #Print tag of elements\n",
    "    for tag in element.iter(\"tag\"):\n",
    "        print(tag.attrib)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "State Types: \n",
      "[]\n",
      "Zipcode Types: \n",
      "[]\n",
      "Problematic House Numbers: \n",
      "[]\n",
      "Problematic Phone Numbers: \n",
      "[]\n",
      "Problematic Website addresses: \n",
      "[]\n",
      "None\n"
     ]
    }
   ],
   "source": [
    "    print(\"State Types: \")\n",
    "    printer(state_types)\n",
    "    \n",
    "    print(\"Zipcode Types: \")\n",
    "    printer(zipcode_types)\n",
    "    \n",
    "    print(\"Problematic House Numbers: \")\n",
    "    printer(housephone_number_types)\n",
    "    \n",
    "    print(\"Problematic Phone Numbers: \")\n",
    "    printer(phone_number_types)\n",
    "    print(\"Problematic Website addresses: \")\n",
    "    printer(website_types)\n",
    "\n",
    "    \n",
    "audit_2 = audit_additional(sf)\n",
    "print(audit_2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Observing 'v' and 'k' tags within each element, we see that Street Types are not our only concern. I.e., We need to inspect other features in our data to determine data integrity.\n",
    "\n",
    "We audit for phone, state, zipcode, and website address features in our data."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We observe there aren't any issues with the additional values. Though this osm file is small, we can anticipate a lower frequency of errors/issues occuring within our data.\n",
    "\n",
    "**Note:**\n",
    "\n",
    "In our 50+MB San Francisco, Ca OSM File, we shoul anticipate more errors.\n",
    "\n",
    "$ $\n",
    "\n",
    "Again, we observe no incorrect information typed into the Phone Number, Website, and House Number, State values. This is good! We continue to converting our osm data into csv files."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Export to CSV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "\n",
    "NODES_PATH = \"CSVFiles/nodes.csv\"\n",
    "NODE_TAGS_PATH = \"CSVFiles/nodes_tags.csv\"\n",
    "WAYS_PATH = \"CSVFiles/ways.csv\"\n",
    "WAY_NODES_PATH = \"CSVFiles/ways_nodes.csv\"\n",
    "WAY_TAGS_PATH = \"CSVFiles/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_guidline.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']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "state_in_postcode_re = re.compile(r'^([A-Z]){2}\\s{1}', re.IGNORECASE)\n",
    "    #Clean and shape node or way XML element to a dictionary\n",
    "\n",
    "#shape_element function with parameters:\n",
    "###element, node_attr_fields,way_attr_fields,problem_chars,default_tag_type\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",
    "    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 the top tag is node we then collect the node attributes and tags\n",
    "    if element.tag == 'node':\n",
    "        #for every attribute in element\n",
    "        for attr in element.attrib:\n",
    "            #and the attribute is in NODE_FIELDS\n",
    "            if attr in NODE_FIELDS:\n",
    "                #Collect the attributes into node_attrib dictionary\n",
    "                node_attribs[attr] = element.attrib[attr]\n",
    "        #for the child in element        \n",
    "        for child in element:\n",
    "            fill_in_dict = {}\n",
    "            #If any character from the \"k\" attribute in LOWER_COLON\n",
    "            if LOWER_COLON.match(child.attrib[\"k\"]):\n",
    "                #Store values in fill_in_dict\n",
    "                fill_in_dict[\"type\"] = child.attrib[\"k\"].split(\":\")[0]\n",
    "                fill_in_dict[\"key\"] = child.attrib[\"k\"].split(\":\")[1]\n",
    "                fill_in_dict[\"id\"] = element.attrib[\"id\"]\n",
    "                if is_street_name(child):\n",
    "                    fill_in_dict[\"value\"] = update_name(child.attrib[\"v\"],mapping)\n",
    "                else:\n",
    "                    fill_in_dict[\"value\"] = child.attrib[\"v\"]\n",
    "                tags.append(fill_in_dict)\n",
    "            #Ignore \"k\" attribute value if it is a PROBLEMCHARS\n",
    "            elif PROBLEMCHARS.match(child.attrib[\"k\"]):\n",
    "                continue\n",
    "            #If anything else, just save the \"k\" attribute's values\n",
    "            else:\n",
    "                fill_in_dict[\"type\"] =default_tag_type\n",
    "                if(is_postcode(child)):\n",
    "                    fill_in_dict['value'] = update_postcode(child.attrib['k'])\n",
    "                else:\n",
    "                    fill_in_dict[\"key\"] = child.attrib['k']\n",
    "                fill_in_dict[\"id\"] = element.attrib['id']\n",
    "                fill_in_dict[\"value\"] = child.attrib['v']\n",
    "                tags.append(fill_in_dict)\n",
    "        return {'node': node_attribs, 'node_tags': tags}\n",
    "    #If the top element tag is \"way\"    \n",
    "    elif element.tag ==\"way\":\n",
    "        #For every attribute in element\n",
    "        for attrib in element.attrib:\n",
    "            #and the attribute is in \"WAY_FIELDS\"\n",
    "            if attrib in WAY_FIELDS:\n",
    "                #Store the attribute in element attribute\n",
    "                way_attribs[attrib] = element.attrib[attrib]\n",
    "        #Let position be 0\n",
    "        position = 0\n",
    "        \n",
    "        #For every child in element\n",
    "        for child in element:\n",
    "            way_tag = {}\n",
    "            way_node = {}\n",
    "            #If the child is a tag\n",
    "            if child.tag == 'tag':\n",
    "                \n",
    "                #If the child attribute \"k\" matched any item in LOWER_COLON\n",
    "                if LOWER_COLON.match(child.attrib['k']):\n",
    "                    #Store the chil attribute values\n",
    "                    \n",
    "                    \n",
    "                    way_tag['type'] = child.attrib['k'].split(':',1)[0] #Get first part of K value\n",
    "                    way_tag['key'] = child.attrib['k'].split(':',1)[1] #get second part of k value\n",
    "                    way_tag['id'] = element.attrib['id'] #get id\n",
    "                    if(is_street_name(child)):\n",
    "                        way_tag['value'] = update_name(child.attrib['v'],mapping) #get value 'v'\n",
    "                    else:\n",
    "                        way_tag['value'] = child.attrib['v'] \n",
    "                    tags.append(way_tag) #save way_tag dictiionary\n",
    "                #Ignore weird \"k\" values in the child's attribute\n",
    "                elif PROBLEMCHARS.match(child.attrib['k']):\n",
    "                    continue\n",
    "                #If the child's attribute \"k\" is anything else, just save value\n",
    "                else:\n",
    "                    way_tag['type'] = 'regular'\n",
    "                    if(is_postcode(child)):\n",
    "                        way_tag['key'] = update_postcode(child.attrib['k'])\n",
    "                    else:\n",
    "                         way_tag['key'] = child.attrib['k']\n",
    "                    way_tag['id'] = element.attrib['id']\n",
    "                    way_tag['value'] = child.attrib['v']\n",
    "                    tags.append(way_tag)\n",
    "            #If the child's tag is \"nd\", store values\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",
    "        return {'way': way_attribs, 'way_nodes': way_nodes, 'way_tags': tags}\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "\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"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "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"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "\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",
    "        #Write out csv documents\n",
    "        nodes_writer = csv.DictWriter(nodes_file, NODE_FIELDS)\n",
    "        node_tags_writer = csv.DictWriter(nodes_tags_file, NODE_TAGS_FIELDS)\n",
    "        ways_writer = csv.DictWriter(ways_file, WAY_FIELDS)\n",
    "        way_nodes_writer = csv.DictWriter(way_nodes_file, WAY_NODES_FIELDS)\n",
    "        way_tags_writer = csv.DictWriter(way_tags_file, WAY_TAGS_FIELDS)\n",
    "        \n",
    "        #write a header to respective documents\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",
    "        #data integrity validation\n",
    "        \n",
    "        \n",
    "        \n",
    "        validator = cerberus.Validator()\n",
    "\n",
    "        for element in get_element(file_in, tags=('node', 'way')):\n",
    "            el = shape_element(element)\n",
    "        \n",
    "            \n",
    "            if el:\n",
    "                if validate is True:\n",
    "                    validate_element(el, validator)\n",
    "\n",
    "                if element.tag == 'node':\n",
    "                    if el['node']['user']=='Юкатан':\n",
    "                        el['node']['user'] = 'Hokatah'\n",
    "                    try:\n",
    "                        nodes_writer.writerow(el['node'])\n",
    "                    except Exception:\n",
    "                        print(el['node'])\n",
    "                    node_tags_writer.writerows(el['node_tags'])\n",
    "                elif element.tag == 'way':\n",
    "                    if el['way']['user']=='Юкатан':\n",
    "                        el['way']['user'] = 'Hokatah'\n",
    "                    ways_writer.writerow(el['way'])\n",
    "                    way_nodes_writer.writerows(el['way_nodes'])\n",
    "                    way_tags_writer.writerows(el['way_tags'])\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(sf, validate=True)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<span style= \"color:red\">The above code has some issues, so I resolved it through data cleaning. mention it in text, hokatah </soan>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Benefit and costs of updating data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Tampered with raw data\n",
    "- Potentially could have induced bias into our data\n",
    "- Typing errors in data cleaning process\n",
    "- and more!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data Insights through SQL"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We intiate light analysis through SQLite3.\n",
    "\n",
    "In this next stage, we aim to create a database <span style =\"color:green\">openstreemap.db</span>. In this database, we have five tables. These five tables are:\n",
    "\n",
    "- nodes\n",
    "- nodes_tags\n",
    "- ways\n",
    "- ways_tags\n",
    "- ways_nodes\n",
    "\n",
    "Below is the schema for the above tables\n",
    "\n",
    "[SQL Schema](https://gist.github.com/swwelch/f1144229848b407e0a5d13fcb7fbbd6f)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Nodes Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "db = sqlite3.connect(\"Database/openstreetmap.db\")\n",
    "cursor = db.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x2656fc37c00>"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "create_nodes_table = '''\n",
    "CREATE TABLE nodes (\n",
    "id  INTEGER PRIMARY KEY NOT NULL,\n",
    "lat REAL,\n",
    "lon REAL,\n",
    "user TEXT,\n",
    "uid INTEGER,\n",
    "version INTEGER,\n",
    "changeset INTEGER,\n",
    "timestamp TEXT\n",
    ");'''\n",
    "cursor.execute(create_nodes_table)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "with open(NODES_PATH,'r') as source:\n",
    "    diction = csv.DictReader(source)\n",
    "    insert_query_nodes = '''INSERT INTO nodes(id, lat, lon, user, uid, version, changeset, timestamp) VALUES (?, ?, ?, ?, ?, ?, ?, ?);'''\n",
    "    write_into_nodes = [(i['id'], i['lat'],i['lon'], i['user'], i['uid'], i['version'], i['changeset'], i['timestamp']) for i in diction]\n",
    "    \n",
    "cursor.executemany(insert_query_nodes, write_into_nodes)\n",
    "db.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[(281266,), (30029498,)]\n"
     ]
    }
   ],
   "source": [
    "query = \"SELECT id FROM nodes LIMIT 2;\"\n",
    "cursor.execute(query)\n",
    "rows = cursor.fetchall()\n",
    "print(rows)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x2656fc37c00>"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "create_nodes_tags_table = '''\n",
    "CREATE TABLE nodes_tags(\n",
    "id INTEGER references nodes(id),\n",
    "key TEXT,\n",
    "value TEXT,\n",
    "type TEXT\n",
    "                                \n",
    ");'''\n",
    "cursor.execute(create_nodes_tags_table)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "with open(NODE_TAGS_PATH,'r') as source2:\n",
    "    diction2 = csv.DictReader(source2)\n",
    "    insert_query_nodes_tags = '''INSERT INTO nodes_tags(id, key, value, type) VALUES (?,?,?,?);'''\n",
    "    write_into_nodes_tags = [(i['id'], i['key'], i['value'], i['type']) for i in diction2]\n",
    "cursor.executemany(insert_query_nodes_tags,write_into_nodes_tags)\n",
    "db.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[(30029498,), (30029498,)]\n"
     ]
    }
   ],
   "source": [
    "query = \"SELECT id FROM nodes_tags LIMIT 2;\"\n",
    "cursor.execute(query)\n",
    "rows = cursor.fetchall()\n",
    "print(rows)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Ways Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x2656fc37c00>"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "create_ways_table = '''\n",
    "CREATE TABLE ways(\n",
    "id INTEGER PRIMARY KEY NOT NULL,\n",
    "user TEXT,\n",
    "uid INTEGER,\n",
    "version TEXT,\n",
    "changeset INTEGER,\n",
    "timestamp TEXT\n",
    ");'''\n",
    "cursor.execute(create_ways_table)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x2656fc37c00>"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "with open(WAYS_PATH,'r') as source3:\n",
    "    diction3 = csv.DictReader(source3)\n",
    "    insert_query_ways = '''INSERT INTO ways(id,user,uid,version,changeset,timestamp) VALUES(?,?,?,?,?,?);'''\n",
    "    write_into_ways = [(i['id'],i['user'],i['uid'],i['version'],i['changeset'],i['timestamp']) for i in diction3]\n",
    "cursor.executemany(insert_query_ways,write_into_ways)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[(5072896, 'dchiles', 153669, '6', 15865774, '2013-04-25T21:11:21Z'),\n",
      " (6319364, 'Natfoot', 567792, '5', 33117782, '2015-08-05T04:59:42Z')]\n"
     ]
    }
   ],
   "source": [
    "query = '''SELECT * FROM ways LIMIT 2'''\n",
    "cursor.execute(query)\n",
    "rows = cursor.fetchall()\n",
    "pprint.pprint(rows)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Ways_Tags Table\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x2656fc37c00>"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "create_ways_tags_table = '''\n",
    "CREATE TABLE ways_tags(\n",
    "id INTEGER NOT NULL,\n",
    "key TEXT NOT NULL,\n",
    "value TEXT NOT NULL,\n",
    "type TEXT,\n",
    "FOREIGN KEY (id) REFERENCES ways(id)\n",
    ");\n",
    "'''\n",
    "cursor.execute(create_ways_tags_table)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x2656fc37c00>"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "with open(WAY_TAGS_PATH,'r') as source4:\n",
    "    diction4 = csv.DictReader(source4)\n",
    "    insert_query_ways_tags = '''INSERT INTO ways_tags(id,key,value,type) VALUES(?,?,?,?);'''\n",
    "    write_into_ways_tags = [(i['id'],i['key'],i['value'],i['type']) for i in diction4]\n",
    "cursor.executemany(insert_query_ways_tags,write_into_ways_tags)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[(5072896, 'bicycle', 'no', 'regular'), (5072896, 'highway', 'path', 'regular')]\n"
     ]
    }
   ],
   "source": [
    "query = '''SELECT * FROM ways_tags LIMIT 2'''\n",
    "cursor.execute(query)\n",
    "rows = cursor.fetchall()\n",
    "pprint.pprint(rows)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Ways Nodes Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x2656fc37c00>"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "create_ways_nodes_table = '''\n",
    "CREATE TABLE ways_nodes(\n",
    "id INTEGER NOT NULL,\n",
    "node_id INTEGER NOT NULL,\n",
    "position INTEGER NOT NULL,\n",
    "FOREIGN KEY (id) REFERENCES ways(id)\n",
    "FOREIGN KEY (node_id) REFERENCES nodes(id)\n",
    "\n",
    ");'''\n",
    "cursor.execute(create_ways_nodes_table)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x2656fc37c00>"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "with open(WAY_NODES_PATH,'r') as source5:\n",
    "    diction5 = csv.DictReader(source5)\n",
    "    insert_query_ways_nodes = '''INSERT INTO ways_nodes(id,node_id,position) VALUES(?,?,?);'''\n",
    "    write_into_ways_nodes = [(i['id'],i['node_id'],i['position']) for i in diction5]\n",
    "cursor.executemany(insert_query_ways_nodes,write_into_ways_nodes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[(5072896, 34351369, 0), (5072896, 34351379, 1)]\n"
     ]
    }
   ],
   "source": [
    "query = '''SELECT * FROM ways_nodes LIMIT 2;'''\n",
    "cursor.execute(query)\n",
    "rows = cursor.fetchall()\n",
    "pprint.pprint(rows)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Additional Questions from our OpenStreetMap Database"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Top Contributor"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[(94578, 'andygol', 13176),\n",
      " (1240849, 'ediyes', 8534),\n",
      " (1829683, 'Luis36995', 6410),\n",
      " (2226712, 'dannykath', 4778),\n",
      " (510836, 'Rub21', 3794),\n",
      " (2219338, 'RichRico', 3613),\n",
      " (2511706, 'calfarome', 1676),\n",
      " (14293, 'KindredCoda', 1458),\n",
      " (169004, 'oldtopos', 1366),\n",
      " (2748195, 'karitotp', 1264),\n",
      " (2512300, 'samely', 1140),\n",
      " (2377377, 'abel801', 988),\n",
      " (160138, 'DanHomerick', 825),\n",
      " (2835928, 'nikhilprabhakar', 772),\n",
      " (933797, 'oba510', 766),\n",
      " (2015224, 'Jothirnadh', 530),\n",
      " (55774, 'nmixter', 515),\n",
      " (3029661, 'saikabhi', 505),\n",
      " (153669, 'dchiles', 489),\n",
      " (2915075, 'hmkandrey', 483),\n",
      " (706170, 'BharataHS', 417),\n",
      " (4845203, 'Ludmila Gladkova', 407),\n",
      " (28775, 'StellanL', 392),\n",
      " (381909, 'JessAk71', 391),\n",
      " (3272286, 'manings', 389),\n",
      " (371121, 'AndrewSnow', 388),\n",
      " (2823295, 'ramyaragupathy', 382),\n",
      " (2644101, 'Chetan_Gowda', 377),\n",
      " (2905914, 'pratikyadav', 352),\n",
      " (2715687, 'anonymoussparrow', 351),\n",
      " (1249504, 'EranChazan', 301),\n",
      " (147510, 'woodpeck_fixbot', 261),\n",
      " (2115749, 'srividya_c', 249),\n",
      " (11154, 'beej71', 233),\n",
      " (3057995, 'oini', 220),\n",
      " (941449, 'lyzidiamond', 218),\n",
      " (210173, 'osmmaker', 215),\n",
      " (1934489, 'brentengust', 199),\n",
      " (91499, 'paulmach', 197),\n",
      " (24452, 'Speight', 195),\n",
      " (2847988, 'jinalfoflia', 190),\n",
      " (22946, 'David Muir Sharnoff', 182),\n",
      " (116029, 'Gregory Arenius', 150),\n",
      " (36121, 'Chris Lawrence', 141),\n",
      " (441116, 'immle', 128),\n",
      " (2554698, 'ruthmaben', 101),\n",
      " (1306, 'PlaneMad', 95),\n",
      " (1295, 'robert', 94),\n",
      " (2953895, 'warneke7', 94),\n",
      " (65950, 'davetoo', 91),\n",
      " (455164, 'Adityo', 89),\n",
      " (2318, 'Latze', 86),\n",
      " (397988, 'groove', 86),\n",
      " (42429, '42429', 83),\n",
      " (131996, 'nvk', 83),\n",
      " (236172, 'saikofish', 82),\n",
      " (402624, 'bdiscoe', 80),\n",
      " (3344856, 'mestergaard', 75),\n",
      " (2760218, 'blacktopol', 73),\n",
      " (46789, 'jraller', 71),\n",
      " (3158, 'Eric Fischer', 70),\n",
      " (4847361, 'lowlamaps', 68),\n",
      " (201724, 'mk408', 67),\n",
      " (660287, 'Teslawire', 64),\n",
      " (1376118, 'ChrisZontine', 62),\n",
      " (35195, 'will l', 61),\n",
      " (25510, 'Apo42', 60),\n",
      " (22925, 'ELadner', 59),\n",
      " (383461, 'phoenix23', 58),\n",
      " (25604, 'db48x', 55),\n",
      " (5116622, 'Mari G', 55),\n",
      " (404841, 'jstratm', 54),\n",
      " (462373, 'Davidazus', 54),\n",
      " (482916, 'bill42', 54),\n",
      " (242352, 'BK_man', 53),\n",
      " (67236, 'jfire', 51),\n",
      " (81024, 'wallclimber21', 51),\n",
      " (25981, 'Michal Migurski', 50),\n",
      " (120468, 'Gone', 49),\n",
      " (4213591, 'timmmichaud', 49),\n",
      " (4224929, 'mcmm', 49),\n",
      " (192581, 'P Dill', 45),\n",
      " (481533, 'dbaron', 45),\n",
      " (361207, 'Ben Discoe', 44),\n",
      " (355242, 'Bryce C Nesbitt', 43),\n",
      " (2859289, 'Alps Pierrat', 41),\n",
      " (532783, 'Eureka gold', 40),\n",
      " (1762578, 'Hiker Dan', 40),\n",
      " (4967074, 'KevinGillette', 39),\n",
      " (202256, 'Telecas', 37),\n",
      " (3877019, 'piligab', 37),\n",
      " (162110, 'tahongawaka', 36),\n",
      " (567792, 'Natfoot', 36),\n",
      " (722137, 'OSMF Redaction Account', 36),\n",
      " (119881, 'claysmalley', 35),\n",
      " (635896, 'bigwebguy', 35),\n",
      " (416346, 'Brian@Brea', 33),\n",
      " (436419, 'wvdp', 33),\n",
      " (1213904, 'mpmckenna8', 33),\n",
      " (1679, 'andrewpmk', 32),\n",
      " (564585, 'wegavision', 32),\n",
      " (3260615, 'SFBay1234', 32),\n",
      " (37067, 'barte', 31),\n",
      " (383858, 'digital0533', 31),\n",
      " (589596, 'lxbarth', 31),\n",
      " (2049452, 'rthfer', 31),\n",
      " (3526347, 'SikeMo', 30),\n",
      " (3885962, 'Hokatah', 30),\n",
      " (123633, 'stevea', 29),\n",
      " (1249130, 'w1n5ton', 29),\n",
      " (2985232, 'aarthy', 29),\n",
      " (3207726, 'beddy', 29),\n",
      " (156109, 'Falcorian', 28),\n",
      " (143643, 'Gijsrooy', 26),\n",
      " (195702, 'Sergey Kozyr', 26),\n",
      " (243490, 'T99', 26),\n",
      " (116769, 'probabble', 25),\n",
      " (120160, 'dbuccolo', 25),\n",
      " (1018369, 'sys49152', 25),\n",
      " (53073, 'Aaron Lidman', 22),\n",
      " (1519895, 'GwennDragon', 22),\n",
      " (1850730, 'Morgan Herlocker', 22),\n",
      " (321578, 'rabbitface', 21),\n",
      " (621202, 'dpaschich', 21),\n",
      " (103340, 'Davin', 20),\n",
      " (595221, 'matthieun', 19),\n",
      " (4325299, 'fjas510', 19),\n",
      " (81497, 'RaffyO', 18),\n",
      " (85177, 'edisonlow', 18),\n",
      " (1884370, 'Nick L', 18),\n",
      " (5359, 'user_5359', 17),\n",
      " (574654, 'Tom_Holland', 17),\n",
      " (584325, 'bhousel', 17),\n",
      " (4523751, 'mrspie', 17),\n",
      " (4971651, 'William Pigeon', 17),\n",
      " (20587, 'balrog-kun', 16),\n",
      " (79401, 'Alexander Avtanski', 16),\n",
      " (190493, 'vonvonvon', 16),\n",
      " (623324, 'Anton Lavrov', 16),\n",
      " (902727, 'javbw', 15),\n",
      " (1796809, 'doug_sfba', 15),\n",
      " (3526564, 'yurasi', 15),\n",
      " (103253, 'gormur', 14),\n",
      " (732991, 'Nathan K', 14),\n",
      " (1708763, 'Brobdingnag', 13),\n",
      " (2474344, 'PH_Mapper', 13),\n",
      " (3178375, 'LeTopographeFou', 13),\n",
      " (3346582, 'GigglingMapper', 13),\n",
      " (35667, 'encleadus', 12),\n",
      " (38487, 'jumbanho', 12),\n",
      " (106828, 'WimM', 12),\n",
      " (145401, 'jnstahl', 12),\n",
      " (214969, 'AndrewBuck', 12),\n",
      " (2639622, 'RationalTangle', 12),\n",
      " (3483725, 'nextuniverse', 12),\n",
      " (5495681, 'JoanL', 12),\n",
      " (19532, 'gaku', 11),\n",
      " (330773, 'Roadrunner21', 11),\n",
      " (760282, 'tsangkenneth', 11),\n",
      " (1214881, 'ryandrake', 11),\n",
      " (1692237, 'mikwat', 11),\n",
      " (1835512, 'nereocystis', 11),\n",
      " (38784, 'Tom Morris', 10),\n",
      " (195150, 'Jesus arango', 10),\n",
      " (239998, 'Sundance', 10),\n",
      " (322034, 'jknewl', 10),\n",
      " (479256, 'geodreieck4711', 10),\n",
      " (837425, 'Jonathan ZHAO', 10),\n",
      " (1247632, 'ipsofacto', 10),\n",
      " (2229097, 'dougletterman', 10),\n",
      " (5074162, 'Slam Garbage', 10),\n",
      " (19235, 'glennon', 9),\n",
      " (33757, 'Minh Nguyen', 9),\n",
      " (123364, 'Tronikon', 9),\n",
      " (171503, 'diskus69', 9),\n",
      " (194231, 'Chris Bell in California', 9),\n",
      " (1529919, 'jrpepper', 9),\n",
      " (1597155, 'poornibadrinath', 9),\n",
      " (1616228, 'flockfinder', 9),\n",
      " (54585, 'Tom Brown', 8),\n",
      " (199600, 'peastman', 8),\n",
      " (207745, 'NE2', 8),\n",
      " (224058, 'sethoscope', 8),\n",
      " (365883, 'morganvenable', 8),\n",
      " (548288, 'WayneSchlegel', 8),\n",
      " (1722605, 'jimhu', 8),\n",
      " (1817244, 'Helmchen42', 8),\n",
      " (3049198, 'ryanderso', 8),\n",
      " (3616263, 'Crabkilla', 8),\n",
      " (5448688, 'HashTagAJay', 8),\n",
      " (4951, 'PeterEastern', 7),\n",
      " (262151, 'ToeBee', 7),\n",
      " (318696, 'n76', 7),\n",
      " (473717, 'thegpshiker', 7),\n",
      " (550560, 'Seandebasti', 7),\n",
      " (1577172, 'it_was_catch22', 7),\n",
      " (1745338, '-MegaByte-', 7),\n",
      " (2209894, 'Spanholz', 7),\n",
      " (2245260, 'IndyMapper', 7),\n",
      " (2747838, 'ssjazz', 7),\n",
      " (364, 'Edward', 6),\n",
      " (3095, 'Alan', 6),\n",
      " (13840, 'mcdmx', 6),\n",
      " (74292, 'Sarah M', 6),\n",
      " (110263, 'werner2101', 6),\n",
      " (183737, 'tkmedia', 6),\n",
      " (190676, 'highbuilder', 6),\n",
      " (336460, 'robgeb', 6),\n",
      " (416100, 'AlistairA', 6),\n",
      " (670691, 'Harry Cutts', 6),\n",
      " (1494110, 'KristenK', 6),\n",
      " (1733584, 'bugplanet', 6),\n",
      " (2181142, 'lyiu', 6),\n",
      " (2508151, 'ridixcr', 6),\n",
      " (2411, 'Marc', 5),\n",
      " (4732, 'iandees', 5),\n",
      " (27454, 'Popolon', 5),\n",
      " (28145, 'amillar', 5),\n",
      " (47892, 'richlv', 5),\n",
      " (79330, 'Jason Scheirer', 5),\n",
      " (83660, 'Ashish Vijayaram', 5),\n",
      " (152074, 'beweta', 5),\n",
      " (158274, 'garmercman', 5),\n",
      " (160949, 'eric22', 5),\n",
      " (161619, 'FvGordon', 5),\n",
      " (271668, 'David Sharnoff', 5),\n",
      " (305001, 'voschix', 5),\n",
      " (564990, 'mavl', 5),\n",
      " (703517, 'Iowa Kid', 5),\n",
      " (1110270, 'afdreher', 5),\n",
      " (1779875, 'jeremiak', 5),\n",
      " (2050600, 'fogfish', 5),\n",
      " (2132368, 'icdawg', 5),\n",
      " (2262733, 'BigD65', 5),\n",
      " (2323512, 'Brian Reavis', 5),\n",
      " (2764994, 'burritojustice', 5),\n",
      " (3151684, 'Yoshinion', 5),\n",
      " (3189680, 'angier1212', 5),\n",
      " (3397814, 'fhmh', 5),\n",
      " (3397845, 'szymanska', 5),\n",
      " (4138943, 'wyleyr', 5),\n",
      " (4386661, 'balalaika', 5),\n",
      " (4510966, 'erchpito', 5),\n",
      " (5094350, 'osmcrc', 5),\n",
      " (6585, 'Martin Atkins', 4),\n",
      " (14179, 'oakland', 4),\n",
      " (54598, 'adbrown', 4),\n",
      " (60612, 'Ratpick', 4),\n",
      " (72002, 'Matt Heberger', 4),\n",
      " (74275, 'balcoath', 4),\n",
      " (135163, 'MikeN', 4),\n",
      " (290680, 'wheelmap_visitor', 4),\n",
      " (372674, 'edgehillnet', 4),\n",
      " (573196, 'nithinkamath', 4),\n",
      " (644152, 'npdoty', 4),\n",
      " (1168909, 'ErikPottinger', 4),\n",
      " (1194938, 'pdgoodman', 4),\n",
      " (1529343, 'camilleanne', 4),\n",
      " (1533136, 'meghanh', 4),\n",
      " (1660455, 'juergenb22', 4),\n",
      " (1711265, 'meliot', 4),\n",
      " (1775756, 'ets_2016', 4),\n",
      " (1836535, 'GITNE', 4),\n",
      " (1893220, 'luckiegurl37', 4),\n",
      " (2100843, 'allandaly', 4),\n",
      " (2257567, 'RMap1', 4),\n",
      " (2886207, 'pinakographos', 4),\n",
      " (3397796, 'MiyagiMachine', 4),\n",
      " (3521724, 'headwatersolver', 4),\n",
      " (3696230, 'stkashani', 4),\n",
      " (4225299, 'Alx303', 4),\n",
      " (4899678, 'eavrdfsdcwad', 4),\n",
      " (682, 'SteveC', 3),\n",
      " (2177, 'bron', 3),\n",
      " (21931, 'amm', 3),\n",
      " (29017, 'FrankT', 3),\n",
      " (46220, \"Ethan O'Connor\", 3),\n",
      " (73268, 'drewda', 3),\n",
      " (81909, 'jlev', 3),\n",
      " (82807, 'jerememonteau', 3),\n",
      " (93285, 'skela', 3),\n",
      " (93788, 'coleman', 3),\n",
      " (100744, 'fennecfoxen', 3),\n",
      " (136211, 'AGWA', 3),\n",
      " (138169, 'Barry Parr', 3),\n",
      " (145231, 'woodpeck_repair', 3),\n",
      " (154749, 'wiedmann', 3),\n",
      " (174995, 'invliD', 3),\n",
      " (322404, 'trevyn', 3),\n",
      " (411298, 'Yury Yatsynovich', 3),\n",
      " (431056, 'mbiker', 3),\n",
      " (522320, 'forkbender', 3),\n",
      " (593205, 'msdess', 3),\n",
      " (597941, 'JeffM8', 3),\n",
      " (634337, 'Snackdaddy', 3),\n",
      " (637707, 'velonautian', 3),\n",
      " (711243, 'Joseph Churchill', 3),\n",
      " (982633, 'joesmith1234', 3),\n",
      " (1098862, 'xj25vm', 3),\n",
      " (1185712, 'Vanuan', 3),\n",
      " (1212907, 'lcwong', 3),\n",
      " (1346983, 'knotthere', 3),\n",
      " (1425289, 'lucioperca', 3),\n",
      " (1550640, 'Freddi2812', 3),\n",
      " (1583864, 'andybaumgar', 3),\n",
      " (1664082, 'MonoSim', 3),\n",
      " (1917271, 'Adam Alpern', 3),\n",
      " (1940279, 'theillustratedlife', 3),\n",
      " (1951224, 'zoverax', 3),\n",
      " (2022156, 'Mehi', 3),\n",
      " (2441939, 'thevirginian', 3),\n",
      " (2475974, 'Dilys', 3),\n",
      " (2682147, 'danrademacher', 3),\n",
      " (2886662, 'Xander Kittah', 3),\n",
      " (3233629, 'Kim Mason', 3),\n",
      " (3397842, 'Tiara Richard', 3),\n",
      " (3479270, 'nammala', 3),\n",
      " (3769434, 'manoharuss', 3),\n",
      " (4007051, 'upendrakarukonda', 3),\n",
      " (4503285, 'sjdunbar', 3),\n",
      " (4518035, 'BenoitMenez', 3),\n",
      " (5133699, 'godzilla94552', 3),\n",
      " (5239832, 'BharataHS_laimport', 3),\n",
      " (5257421, 'soundtcr', 3),\n",
      " (8287, 'migurski', 2),\n",
      " (8909, 'mvexel', 2),\n",
      " (12055, 'aude', 2),\n",
      " (12671, 'sladen', 2),\n",
      " (24555, 'fmarier', 2),\n",
      " (27781, 'Rodolphe Devillers', 2),\n",
      " (27824, 'phut', 2),\n",
      " (28923, 'California Bear', 2),\n",
      " (29639, 'bielebog', 2),\n",
      " (31385, 'Skippern', 2),\n",
      " (37137, 'Derick Rethans', 2),\n",
      " (40397, 'Zartbitter', 2),\n",
      " (74705, 'Bike Mapper', 2),\n",
      " (77582, 'Byung Kyu Park', 2),\n",
      " (83557, 'Esperanza36', 2),\n",
      " (88961, 'Shmias', 2),\n",
      " (92286, 'Paul Johnson', 2),\n",
      " (95132, 'Emers', 2),\n",
      " (109968, 'InsomniaZZZ', 2),\n",
      " (111529, 'ReinerMeyer', 2),\n",
      " (117995, 'tbm', 2),\n",
      " (125457, 'Orblivion', 2),\n",
      " (128186, 'malcolmh', 2),\n",
      " (131476, 'DMaximus', 2),\n",
      " (138911, 'emancdf', 2),\n",
      " (161951, 'DLichti', 2),\n",
      " (172061, 'CloCkWeRX', 2),\n",
      " (278150, 'GeoTomDe', 2),\n",
      " (291298, 'pkm', 2),\n",
      " (296869, 'Zian Choy', 2),\n",
      " (381253, 'MichaelGSmith', 2),\n",
      " (398300, 'baditaflorin', 2),\n",
      " (429728, 'bnewey', 2),\n",
      " (432824, 'snwnj', 2),\n",
      " (473980, 'earthiq', 2),\n",
      " (492311, 'zacmccormick', 2),\n",
      " (501715, 'rkuris', 2),\n",
      " (507503, 'caior', 2),\n",
      " (529836, 'farad', 2),\n",
      " (594525, 'esuor', 2),\n",
      " (665694, 'aarp65', 2),\n",
      " (696201, 'denimboy', 2),\n",
      " (701297, 'erjiang', 2),\n",
      " (714898, 'Md Alamgir', 2),\n",
      " (775006, 'ORDG', 2),\n",
      " (829302, 'Claudev8', 2),\n",
      " (1083211, 'Theo D O Lite', 2),\n",
      " (1168707, 'animeigo', 2),\n",
      " (1297360, 'eepsmedia', 2),\n",
      " (1403907, 'VMukhtarov', 2),\n",
      " (1476146, 'Alecs01', 2),\n",
      " (1546527, 'dirtysouth', 2),\n",
      " (1707766, 'Christine_E', 2),\n",
      " (1710478, 'SpannedObjurgations', 2),\n",
      " (1781831, 'Bob12345', 2),\n",
      " (1802220, 'jonesydesign', 2),\n",
      " (1850735, 'rgupta0747', 2),\n",
      " (1886770, 'BHSPitMonkey', 2),\n",
      " (1980416, 'olikami', 2),\n",
      " (2045423, 'Dave Guarino', 2),\n",
      " (2136684, 'senordanimal', 2),\n",
      " (2240409, 'Ryan Scott', 2),\n",
      " (2240804, 'Paul Everett', 2),\n",
      " (2254670, 'Andrew Ash', 2),\n",
      " (2328400, 'jaapweel', 2),\n",
      " (2376998, 'Tim_the_toolman', 2),\n",
      " (2389416, 'lmitchell', 2),\n",
      " (2406567, 'eddietejeda', 2),\n",
      " (2406578, 'colindt', 2),\n",
      " (2412184, 'youngbasedallah', 2),\n",
      " (2422893, 'volki123', 2),\n",
      " (2575095, 'enveloper', 2),\n",
      " (2655992, 'steverumizen', 2),\n",
      " (2724074, 'WorldWatchOne', 2),\n",
      " (2733735, 'jnmiller', 2),\n",
      " (2876693, 'sayreot', 2),\n",
      " (3081437, 'fproulx', 2),\n",
      " (3109007, 'mrkooll', 2),\n",
      " (3181637, 'scottjohnson3', 2),\n",
      " (3397844, 'warrenwong', 2),\n",
      " (3478684, 'GoWestTravel', 2),\n",
      " (3569951, '00crashtest', 2),\n",
      " (3587922, 'Fishytodd', 2),\n",
      " (3681372, 'NWR6', 2),\n",
      " (4095244, 'askklein', 2),\n",
      " (4109236, 'liammc2000', 2),\n",
      " (4724512, 'leftcoastnaturalist', 2),\n",
      " (4801391, 'Claumires', 2),\n",
      " (4893098, 'saikabhi_sfimport', 2),\n",
      " (5185806, 'jmtyndall', 2),\n",
      " (6113775, 'n_LH', 2),\n",
      " (735, 'blackadder', 1),\n",
      " (999, 'mikelmaron', 1),\n",
      " (1697, 'nickb', 1),\n",
      " (3516, 'BiIbo', 1),\n",
      " (3990, 'YannC', 1),\n",
      " (4115, 'Andy Allan', 1),\n",
      " (4902, 'mbuege', 1),\n",
      " (5211, 'emvee', 1),\n",
      " (6367, 'mackerski', 1),\n",
      " (6975, 'ulfl', 1),\n",
      " (11711, 'Octoferret', 1),\n",
      " (13192, 'delphiN', 1),\n",
      " (14351, 'WBSKI', 1),\n",
      " (16478, 'Raymond', 1),\n",
      " (23762, 'Heptazane', 1),\n",
      " (25867, 'adamos', 1),\n",
      " (25996, 'pingoo', 1),\n",
      " (26599, 'cdavila', 1),\n",
      " (28559, 'Stemby', 1),\n",
      " (30360, 'novakyu', 1),\n",
      " (31231, 'Andre68', 1),\n",
      " (31906, 'ago1024', 1),\n",
      " (32952, 'DennisL', 1),\n",
      " (35254, 'Zer', 1),\n",
      " (35372, 'suspet', 1),\n",
      " (35935, '13 digits', 1),\n",
      " (37753, 'Max Spring', 1),\n",
      " (38090, 'ZekeFarwell', 1),\n",
      " (44389, 'kollokollo', 1),\n",
      " (45611, 'Srittau', 1),\n",
      " (48060, 'Sat', 1),\n",
      " (51581, 'dandv', 1),\n",
      " (52797, 'hofoen', 1),\n",
      " (53936, 'elsaxo', 1),\n",
      " (53964, '_Mathieu_', 1),\n",
      " (58305, 'fiveisalive', 1),\n",
      " (70092, 'tcarobruce', 1),\n",
      " (70696, 'xybot', 1),\n",
      " (74228, 'nateirwin', 1),\n",
      " (86066, 'Joel Franusic', 1),\n",
      " (87731, 'Serguei Trouchelle', 1),\n",
      " (88771, 'mjn', 1),\n",
      " (93800, 'Alan Vogt', 1),\n",
      " (100467, 'johnnyishellarad', 1),\n",
      " (104461, 'brunosan', 1),\n",
      " (104519, 'Rupert Swarbrick', 1),\n",
      " (105946, 'ElliottPlack', 1),\n",
      " (107257, 'FrViPofm', 1),\n",
      " (107681, 'maxugglan', 1),\n",
      " (113624, 'mojodna', 1),\n",
      " (120664, 'Chris Clemson', 1),\n",
      " (126019, 'Jarrattp', 1),\n",
      " (130277, 'Teuxe', 1),\n",
      " (135329, 'ajashton', 1),\n",
      " (143856, 'axelbaker', 1),\n",
      " (144449, 'mpetrotta', 1),\n",
      " (148776, 'meridianeum', 1),\n",
      " (148800, 'AndreasPr', 1),\n",
      " (163803, 'jjmcgaffey', 1),\n",
      " (171516, 'Caboosey', 1),\n",
      " (173918, 'njaard', 1),\n",
      " (174586, 'NAParish', 1),\n",
      " (178186, 'mdk', 1),\n",
      " (185205, 'magnunor', 1),\n",
      " (186357, 'liucougar', 1),\n",
      " (190690, 'posiki', 1),\n",
      " (190869, 'elbatrop', 1),\n",
      " (192522, 'thecrazyknitter', 1),\n",
      " (192954, 'ptancredi', 1),\n",
      " (206785, 'Tom O', 1),\n",
      " (231017, 'swimdb', 1),\n",
      " (231723, 'sfllaw', 1),\n",
      " (235835, 'lizhenry', 1),\n",
      " (238419, 'g246020', 1),\n",
      " (252811, 'rickmastfan67', 1),\n",
      " (257555, 'rene78', 1),\n",
      " (257972, 'Peakfinder', 1),\n",
      " (272522, 'ramayer', 1),\n",
      " (286975, 'Jesper Jurcenoks', 1),\n",
      " (290016, 'atsiatas', 1),\n",
      " (296587, 'bmhr', 1),\n",
      " (296793, 'kouker', 1),\n",
      " (297064, 'HeyIdiot', 1),\n",
      " (303224, 'Mignon', 1),\n",
      " (305985, 'ingalls', 1),\n",
      " (329833, 'pankdm', 1),\n",
      " (338351, 'sfodoug', 1),\n",
      " (338843, 'TorCguy', 1),\n",
      " (341519, 'lcmortensen', 1),\n",
      " (347787, 'farsak', 1),\n",
      " (349023, 'jdhall', 1),\n",
      " (352170, 'dspring', 1),\n",
      " (360392, 'maxerickson', 1),\n",
      " (363927, 'Brian H Wilson', 1),\n",
      " (366306, 'derricknehrenberg', 1),\n",
      " (371711, 'knockpenny', 1),\n",
      " (401490, 'svance92', 1),\n",
      " (412560, 'Meersbrook', 1),\n",
      " (415728, 'sycamore_island', 1),\n",
      " (429804, 'hazzardsb', 1),\n",
      " (450292, 'aguynamedben', 1),\n",
      " (475877, 'RoadGeek_MD99', 1),\n",
      " (482974, 'ogremage', 1),\n",
      " (485507, 'Sam Maurer', 1),\n",
      " (521541, 'domob', 1),\n",
      " (530149, 'Mapper1', 1),\n",
      " (540553, 'ki6uoc', 1),\n",
      " (545896, 'shitonfools', 1),\n",
      " (574053, 'Walk and walk around', 1),\n",
      " (583866, 'deinefreunde', 1),\n",
      " (588517, 'M_Aloisius', 1),\n",
      " (606225, 'fizzymagic', 1),\n",
      " (613191, 'conf88', 1),\n",
      " (620663, 'Peter Shank', 1),\n",
      " (626299, 'Andrew Kraut', 1),\n",
      " (627528, 'emilyann5', 1),\n",
      " (632124, 'duxxa', 1),\n",
      " (638133, 'LoicSf', 1),\n",
      " (643201, 'lbacud', 1),\n",
      " (652230, 'hichmont', 1),\n",
      " (667422, 'Alex-7', 1),\n",
      " (672542, 'Nearo', 1),\n",
      " (674313, 'snowey', 1),\n",
      " (676820, 'shivanshu', 1),\n",
      " (698697, 'powell789', 1),\n",
      " (743575, 'pescorosso', 1),\n",
      " (745333, 'ustroetz', 1),\n",
      " (828519, 'Cjlanda', 1),\n",
      " (860575, 'MarcelHerault', 1),\n",
      " (901335, 'Sasharef', 1),\n",
      " (902185, 'synack440', 1),\n",
      " (908886, 'masterleep', 1),\n",
      " (916555, 'NLgis', 1),\n",
      " (945595, 'jo-mey', 1),\n",
      " (950182, 'RitterR', 1),\n",
      " (1044834, 'Jesse Phillips', 1),\n",
      " (1087647, 'Phil Scherer', 1),\n",
      " (1154880, 'AntonBryl', 1),\n",
      " (1195290, 'cjacky', 1),\n",
      " (1210008, 'mnorelli', 1),\n",
      " (1211007, 'OYR', 1),\n",
      " (1211556, 'dbaupp', 1),\n",
      " (1212745, 'YraFyra', 1),\n",
      " (1213894, 'Bradley Cleveland', 1),\n",
      " (1240864, 'Howpper', 1),\n",
      " (1260280, 'jacobbraeutigam', 1),\n",
      " (1282393, 'Reno Editor', 1),\n",
      " (1309137, 'bobnewey', 1),\n",
      " (1323941, 'dnomadb', 1),\n",
      " (1355899, 'isabellekh', 1),\n",
      " (1393741, 'pollardld', 1),\n",
      " (1408707, 'YongYang', 1),\n",
      " (1430178, 'Jonathon Conte', 1),\n",
      " (1431737, 'ma-rt-in', 1),\n",
      " (1529374, 'Davis Kitchel', 1),\n",
      " (1556219, 'Thibaut75011', 1),\n",
      " (1611887, 'Scott13208', 1),\n",
      " (1720031, 'BerkelBear', 1),\n",
      " (1722904, 'Alan Trick', 1),\n",
      " (1731525, 'hmyers909', 1),\n",
      " (1733620, 'kdingman', 1),\n",
      " (1750383, 'ljjwfr', 1),\n",
      " (1751737, 'Skybunny', 1),\n",
      " (1758880, 'DongyangCA', 1),\n",
      " (1778256, 'theojapa', 1),\n",
      " (1797586, 'jschill', 1),\n",
      " (1814324, 'VrindaM', 1),\n",
      " (1860668, 'Walkabout44', 1),\n",
      " (1861330, 'RBF', 1),\n",
      " (1883329, 'FactChecker', 1),\n",
      " (1887210, 'Aaronmap', 1),\n",
      " (1926148, 'codesliced', 1),\n",
      " (1942315, 'RhinoCreature', 1),\n",
      " (1959811, 'ub91', 1),\n",
      " (1964364, 'Eseqko', 1),\n",
      " (1976641, 'Gpriceless', 1),\n",
      " (1980274, 'dhalli1', 1),\n",
      " (1980320, 'kingpenguin', 1),\n",
      " (1980326, 'jmadeline', 1),\n",
      " (2012449, 'Dami_Tn', 1),\n",
      " (2012966, 'emejim', 1),\n",
      " (2016549, 'NPLC', 1),\n",
      " (2017972, 'Mr0grog', 1),\n",
      " (2021999, 'david_ozer', 1),\n",
      " (2032360, 'Drew1sh', 1),\n",
      " (2032478, 'SarahStierch', 1),\n",
      " (2033336, 'AnnieM', 1),\n",
      " (2035254, 'Devin Swisher', 1),\n",
      " (2045275, 'anaris328', 1),\n",
      " (2052335, 'James Fahlbusch', 1),\n",
      " (2052349, 'Michael Webster', 1),\n",
      " (2061773, 'sorchah', 1),\n",
      " (2076173, 'Kasey Klimes', 1),\n",
      " (2086526, 'RyJoh', 1),\n",
      " (2137840, 'rza31', 1),\n",
      " (2176692, 'David Strauss', 1),\n",
      " (2306749, 'shvrm', 1),\n",
      " (2332032, 'thatmm', 1),\n",
      " (2334541, '0101010', 1),\n",
      " (2363939, 'MenloJim', 1),\n",
      " (2370549, 'rstoeber', 1),\n",
      " (2393795, 'Daniel Specht', 1),\n",
      " (2411240, 'pluton_od', 1),\n",
      " (2411764, 'wilmette88', 1),\n",
      " (2433512, '75287578', 1),\n",
      " (2488002, 'Michael Steffen', 1),\n",
      " (2524341, 'UI Guy', 1),\n",
      " (2580175, 'bravo7265', 1),\n",
      " (2590582, 'MannequinBaby', 1),\n",
      " (2593828, 'pizza4days', 1),\n",
      " (2601744, 'sctrojan79', 1),\n",
      " (2688775, 'Warren T', 1),\n",
      " (2697733, 'Gizmot', 1),\n",
      " (2763448, 'Daniel_Caldwell', 1),\n",
      " (2773196, 'NickBolten', 1),\n",
      " (2813257, 'CamRobDen', 1),\n",
      " (2814663, 'Ballard OpenStreetMap', 1),\n",
      " (2819599, 'CaseyFrost', 1),\n",
      " (2820500, 'a6y', 1),\n",
      " (2866692, 'glotto', 1),\n",
      " (2880297, 'LimaMoon', 1),\n",
      " (2883452, 'vbgt', 1),\n",
      " (2884337, 'cercis14', 1),\n",
      " (2903274, 'trosm', 1),\n",
      " (2925215, 'houston_mapper1', 1),\n",
      " (2941385, 'cart0', 1),\n",
      " (2944689, 'mihaii_telenav', 1),\n",
      " (2967601, 'alpinekid', 1),\n",
      " (3125856, 'cowdog', 1),\n",
      " (3129859, 'kmarseglia', 1),\n",
      " (3215437, 'Isabel Wang', 1),\n",
      " (3224809, 'Ken Johnson 2', 1),\n",
      " (3282935, 'Blaxcarab', 1),\n",
      " (3324338, 'janp2001', 1),\n",
      " (3389275, 'cmdoptesc', 1),\n",
      " (3397534, 'nemo92', 1),\n",
      " (3397835, 'KaylanHager', 1),\n",
      " (3397837, 'bpace1994', 1),\n",
      " (3397838, 'jtbrown1806', 1),\n",
      " (3397846, 'Cherry24', 1),\n",
      " (3397848, 'JM7323', 1),\n",
      " (3415446, 'cylexx', 1),\n",
      " (3415463, 'bjkosm', 1),\n",
      " (3469870, 'Fan Kong', 1),\n",
      " (3528573, 'ulrich_', 1),\n",
      " (3558911, 'Joel S', 1),\n",
      " (3601534, 'egavril', 1),\n",
      " (3624816, 'cyeager89', 1),\n",
      " (3665135, 'William Carman', 1),\n",
      " (3671294, 'cynmar', 1),\n",
      " (3696204, 'dsherrell', 1),\n",
      " (3696208, 'pmeehan', 1),\n",
      " (3696212, 'jaylina', 1),\n",
      " (3696217, 'Kristymarie42', 1),\n",
      " (3696439, 'Audey Seawright', 1),\n",
      " (3762030, 'Stephanie Landa', 1),\n",
      " (3793530, 'Jenn Kaplan', 1),\n",
      " (3812920, 'Olivia De Lancie', 1),\n",
      " (3830366, 'Asif Haque', 1),\n",
      " (3886701, 'stepps00', 1),\n",
      " (3905761, 'cplank', 1),\n",
      " (3913914, 'Airlobster', 1),\n",
      " (3916314, 'dschulle', 1),\n",
      " (3995305, 'Travl', 1),\n",
      " (4157731, 'Sandeep Venkatesh', 1),\n",
      " (4158534, 'livets123', 1),\n",
      " (4314582, 'MappedReduced', 1),\n",
      " (4319107, 'Ty8769', 1),\n",
      " (4353543, 'IbolyaTarsoly', 1),\n",
      " (4460614, 'keikoanl', 1),\n",
      " (4541740, 'Frank Vandevoorde', 1),\n",
      " (4543547, 'sea2sky', 1),\n",
      " (4624663, 'Doug777', 1),\n",
      " (4635105, 'jcdoll', 1),\n",
      " (4651319, 'jveeb', 1),\n",
      " (4681914, 'Casey Bell', 1),\n",
      " (4706158, 'brianshotwell', 1),\n",
      " (4725451, 'DrSeamons', 1),\n",
      " (4835230, 'KengS', 1),\n",
      " (4846473, 'chaoman', 1),\n",
      " (4892650, 'manings_sfheight', 1),\n",
      " (5007769, 'UltraNivlac', 1),\n",
      " (5024957, 'aquaplumb', 1),\n",
      " (5084247, 'liesliesliesyea', 1),\n",
      " (5147743, 'Jason Roy', 1),\n",
      " (5148403, 'Darragh_M', 1),\n",
      " (5183376, 'das-gus', 1),\n",
      " (5184971, 'mllefoo', 1),\n",
      " (5187925, 'aihardin', 1),\n",
      " (5193438, 'Keykeys', 1),\n",
      " (5193764, 'facecraft', 1),\n",
      " (5204588, 'Tom_Servo', 1),\n",
      " (5210438, 'KingDedede', 1),\n",
      " (5222544, 'gnomad83', 1),\n",
      " (5236034, 'Jenny Redo', 1),\n",
      " (5367179, 'Rene415', 1),\n",
      " (5400451, 'Alex Kapranoff', 1),\n",
      " (5475233, 'MrJamesonNeat', 1),\n",
      " (5486800, 'primes', 1),\n",
      " (5667998, 'ArgonHunter', 1),\n",
      " (5853065, 'McKinley D', 1),\n",
      " (6008115, 'm2kw6s', 1),\n",
      " (6106546, 'dharber', 1),\n",
      " (6144079, 'AdaLowe', 1)]\n"
     ]
    }
   ],
   "source": [
    "query = '''SELECT uid, user, COUNT(uid) FROM nodes GROUP BY uid ORDER BY COUNT(uid) DESC;'''\n",
    "cursor.execute(query)\n",
    "rows = cursor.fetchall()\n",
    "pprint.pprint(rows)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Recalling the most contributing statistic from our python code, we confirm that user <span style=\"color:red\">94578, andygol,</span> was the user that contributed most to SF Map entries\n",
    "\n",
    "nmixter's contribution ratio was"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0.20051132213294376 percent\n"
     ]
    }
   ],
   "source": [
    "print(13176/65712,\"percent\") "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "From the fact that the total OpenStreetMap South Modesto entries was 11607, as seem below"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[(65712,)]\n"
     ]
    }
   ],
   "source": [
    "query = '''SELECT COUNT(uid) FROM nodes;'''\n",
    "cursor.execute(query)\n",
    "rows = cursor.fetchall()\n",
    "pprint.pprint(rows)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Top Contributor andygol's track"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What locations did andygol contribute to?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[(37.8166239, -122.1905843),\n",
      " (37.6197213, -122.0889768),\n",
      " (37.6166747, -122.0922437),\n",
      " (37.7852169, -122.183983),\n",
      " (37.7961488, -122.1754563)]\n"
     ]
    }
   ],
   "source": [
    "query = '''SELECT lat, lon FROM nodes WHERE user = 'andygol' LIMIT 5;'''\n",
    "cursor.execute(query)\n",
    "rows = cursor.fetchall()\n",
    "pprint.pprint(rows)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I was curious who the user nmixter was. So, I found his profile on OpenStreetMap. In [Andygol's profile](https://www.openstreetmap.org/user/andygol), we observe that we has made 20,000+ entries over his 8 years as an OSM contributor. \n",
    "\n",
    "His SF contributions were created several months ago, and he still continues to expand out into Russia and San Jose entries. I.e., Andygol may be no SF native, but just a high contributor on OSM.\n",
    "\n",
    "If you go to this [link](https://www.mapbox.com/about/team/andrey-golovin/), you can actually find out that the OSM contributions are a part of his career and hobbies.\n",
    "\n",
    "\n",
    "$ $\n",
    "\n",
    "Now, let's continue to the nodes_tags table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Node Tags Table Insights"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Verifying Street names from previous example"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('Cutting Boulevard',), ('Center Street',), ('Montgomery Street',), ('Beach Street',), ('Sutter Street',), ('San Pablo Avenue',), ('Shattuck Avenue',), ('Cedar Street',), ('Rose Street',), ('Eunice Street',), ('Berryman Street',), ('Northgate Avenue',), ('Shasta Road',), ('Spruce Street',), ('Cragmont Avenue',), ('Regal Road',), ('Grant Street',), ('Union Street',), ('Los Angeles Avenue',), ('Union Landing Boulevard',), ('Post Street',), ('Fort Mason Center',), ('Steiner Street',), ('Berryman Street',), ('The Alameda',), ('Yosemite Road',), ('Montrose Road',), ('Martin Luther King Jr Way',), ('Martin Luther King Jr Way',), ('Vine Street',), ('Keeler Avenue',), ('El Camino Real',), ('Decoto Road',), ('Alvarado-Niles Road',), ('Alvarado-Niles Road',), ('Decoto Road',), ('Hamilton Street',), ('University Avenue',), ('Telegraph Avenue',), ('Gateview Court',), ('Mariner Drive',), ('El Camino Real',), ('George Avenue',), ('Avenue of the Palms',), ('Decoto Road',), ('14th Street',), ('Walter Street',), ('El Camino Real',), ('El Camino Real',), ('Broadway',), ('Broadway',), ('9th Street',), ('Josephine Street',), ('Josephine Street',), ('Milvia Street',), ('Bonita Avenue',), ('Cedar Street',), ('Townsend Street',), ('Lombard Street',), ('Cedar Street',), ('25th Street',), ('Grant Street',), ('Valencia Street',), ('Kearny Street',), ('Broadway',), ('Polk Street',), ('Dwight Way',), ('Taraval Street',), ('Mission St',), ('Castro Street',), ('Castro Street',), ('Jefferson Street',), ('Church Street',), ('University Avenue',), ('10th Avenue',), ('Michael Drive',), ('7th Avenue',), ('Arguello Street',), ('Alden Street',), ('4th Street',), ('Main Street',), ('Heller Street',), ('Hopkins Avenue',), ('Roosevelt Avenue',), ('Fulton Street',), ('El Camino Real',), ('Upland Road',), ('Upland Road',), ('Saint Francis Street',), ('Ruby Street',), ('Reese Street',), ('Valota Road',), ('Kentfield Avenue',), ('Kentfield Avenue',), ('Oxford Street',), ('Woodside Road',), ('Wheeler Avenue',), ('Jefferson Court',), ('Oak Avenue',), ('Roosevelt Avenue',), ('Main Street',), ('College Avenue',), ('Howard Street',), ('Grand Avenue',), ('Santa Cruz Avenue',), ('Blanding Avenue',), ('Sanchez Street',), ('Jersey Street',), ('24th Street',), ('25th Street',), ('Clipper Street',), ('Guerrero Street',), ('Church Street',), ('24th Street',), ('Washington Street',), ('Stryker Street',), ('Sanchez Street',), ('Sanchez Street',), ('Sanchez Street',), ('24th Street',), ('27th Street',), ('Orange Street',), ('Virginia Avenue',), ('Macdonald Avenue',), ('Church Street',), ('South 17th Street',), ('Potrero Avenue',), ('College Avenue',), ('27th Street',), ('Duncan Street',), ('Valley Street',), ('29th Street',), ('Manor Plaza',), ('29th Street',), ('25th Street',), ('3rd Street',), ('Fair Oaks Street',), ('Fair Oaks Street',), ('Oxford Street',), ('West Portal Avenue',), ('Dolores Street',), ('Day Street',), ('West Portal Avenue',), ('Taraval Street',), ('Mission Street',), ('Larkin Street',), ('9th Avenue',), ('Irving Street',), ('Sansome Street',), ('Lawton Street',), ('Irving Street',), ('12th Avenue',), ('Judah Street',), ('9th Avenue',), ('7th Avenue',), ('Dolores Street',), ('Funston Avenue',), ('12th Avenue',), ('10th Avenue',), ('Dolores Street',), ('Funston Avenue',), ('Geary Street',), ('Carl Street',), ('Carl Street',), ('Willard Street',), ('3rd Avenue',), ('14th Street',), ('Carl Street',), ('Carl Street',), ('3rd Avenue',), ('Lincoln Way',), ('Bayshore Highway',), ('Irving Street',), ('3rd Avenue',), ('Carl Street',), ('9th Street',), ('Irving Street',), ('34th Avenue',), ('Irving Street',), ('Irving Street',), ('34th Avenue',), ('22nd Street',), ('MacArthur Boulevard',), ('5th Avenue',), ('Taraval Street',), ('48th Avenue',), ('Terrace Way',), ('Ocean Avenue',), ('Powell Street',), ('Dore Street',), ('Lincoln Way',), ('17th Street',), ('Corbett Avenue',), ('San Pablo Avenue',), ('Balboa Street',), ('Natoma Street',), ('Judah Street',), ('Mission Street',), ('Oak Street',), ('Cole Street',), ('Masonic Avenue',), ('West 25th Avenue',), ('El Cerrito Plaza',), ('Page Street',), ('Page Street',), ('Webster Street',), ('Divisadero Street',), ('Golden Gate Avenue',), ('Ellis Street',), ('Mission Blvd #302',), ('Grant Avenue',), ('Divisadero Street',), ('Green Street',), ('Divisadero Street',), ('West Tower Avenue',), ('Geary Street',), ('Divisadero Street',), ('Waller Street',), ('Waller Street',), ('Pierce Street',)]\n"
     ]
    }
   ],
   "source": [
    "query = '''SELECT value FROM nodes_tags WHERE key = 'street';'''\n",
    "cursor.execute(query)\n",
    "rows = cursor.fetchall()\n",
    "print(rows)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There were no necessary corrections for our street names in the initial audit of our street_names. The above output is a confirmation that our data is validated for the right entries and proper export to a csv file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('94804',), ('94109',), ('94104',), ('94709',), ('94587',), ('94117',), ('94066',), ('94587',), ('94587',), ('94587',), ('94587',), ('94063',), ('94609',), ('94063',), ('94555',), ('94063',), ('94063',), ('94063',), ('94063',), ('94107',), ('94063',), ('94109',), ('94704',), ('94116',), ('94110',), ('94114',), ('94114',), ('94618',), ('94103',), ('94610',), ('94501',), ('94610',), ('94044',), ('94704',), ('94127',), ('94127',), ('94116',), ('94105',), ('94109',), ('94122',), ('94122',), ('94104',), ('94122',), ('94122',), ('94122',), ('94122',), ('94122',), ('94122',), ('94122',), ('94122',), ('94122',), ('94122',), ('94109',), ('94117',), ('94117',), ('94117',), ('94122',), ('94117',), ('94117',), ('94122',), ('94122',), ('94010',), ('94122',), ('94122',), ('94117',), ('94103',), ('94122',), ('94122',), ('94122',), ('94122',), ('94121',), ('94602',), ('94122',), ('94116',), ('94549',), ('94127',), ('94103',), ('94122',), ('94117',), ('94114',), ('94706',), ('94103',), ('94105',), ('94530',), ('94541',), ('94111',), ('94501',)]\n"
     ]
    }
   ],
   "source": [
    "query = '''SELECT value FROM nodes_tags WHERE key = 'postcode';'''\n",
    "cursor.execute(query)\n",
    "rows = cursor.fetchall()\n",
    "print(rows)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Additionally, we observe no zipcode issues factored into our export file"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Initial Discoveries\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('ref',),\n",
      " ('exit_to',),\n",
      " ('highway',),\n",
      " ('source',),\n",
      " ('created_by',),\n",
      " ('barrier',),\n",
      " ('traffic_signals',),\n",
      " ('sound',),\n",
      " ('turn_restrictions',),\n",
      " ('crossing',),\n",
      " ('noexit',),\n",
      " ('ele',),\n",
      " ('name',),\n",
      " ('is_in',),\n",
      " ('place',)]\n"
     ]
    }
   ],
   "source": [
    "query = '''SELECT DISTINCT(key) FROM nodes_tags LIMIT 15;'''\n",
    "cursor.execute(query)\n",
    "rows = cursor.fetchall()\n",
    "pprint.pprint(rows)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We observe the first 15 types of keys from the nodes_tags table. These keys allow us to observe the listed attributes/descriptions of the SF region.\n",
    "\n",
    "For example, below is the first 5 listed highways in the SF region data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[(30029498, 'highway', 'motorway_junction', 'regular'),\n",
      " (33154450, 'highway', 'motorway_junction', 'regular'),\n",
      " (53019140, 'highway', 'stop', 'regular'),\n",
      " (53021162, 'highway', 'turning_circle', 'regular'),\n",
      " (53027114, 'highway', 'turning_circle', 'regular')]\n"
     ]
    }
   ],
   "source": [
    "query = \"SELECT * FROM nodes_tags WHERE key ='highway' LIMIT 5\"\n",
    "cursor.execute(query)\n",
    "rows = cursor.fetchall()\n",
    "pprint.pprint(rows)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "and below is the sport key(s) associated with our nodes_tags table:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[(585968188, 'sport', 'gymnastics', 'regular'),\n",
      " (1337779817, 'sport', 'martial_arts', 'regular')]\n"
     ]
    }
   ],
   "source": [
    "query = \"SELECT * FROM nodes_tags WHERE key ='sport'\"\n",
    "cursor.execute(query)\n",
    "rows = cursor.fetchall()\n",
    "pprint.pprint(rows)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It's weird how there is only two sport entries, namely gymnastics and martial arts. I assume this value implies an OSM user indicated only two sports in SF.\n",
    "\n",
    "\n",
    "$ $ \n",
    "\n",
    "Let's see the location(latitude,longitude) for these 'gymnastic' and 'martial_arts' value. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[(585968188,\n",
      "  37.758916,\n",
      "  -122.43792,\n",
      "  'Jarrattp',\n",
      "  126019,\n",
      "  1,\n",
      "  3346849,\n",
      "  '2009-12-11T06:26:06Z',\n",
      "  585968188,\n",
      "  'sport',\n",
      "  'gymnastics',\n",
      "  'regular'),\n",
      " (1337779817,\n",
      "  37.4667705,\n",
      "  -122.2080786,\n",
      "  'peastman',\n",
      "  199600,\n",
      "  3,\n",
      "  11812253,\n",
      "  '2012-06-06T04:54:26Z',\n",
      "  1337779817,\n",
      "  'sport',\n",
      "  'martial_arts',\n",
      "  'regular')]\n"
     ]
    }
   ],
   "source": [
    "query = \"SELECT * FROM nodes JOIN nodes_tags ON nodes.id = nodes_tags.id WHERE nodes_tags.value = 'gymnastics' OR nodes_tags.value = 'martial_arts';\"\n",
    "cursor.execute(query)\n",
    "rows = cursor.fetchall()\n",
    "pprint.pprint(rows)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Okay, We observe user <span style=\"color:red\">Jarrattp and peastman </span> created these gymnastic and martial arts values, respectively.\n",
    "\n",
    "$ $\n",
    "\n",
    "Jarrattp's node for the gymastic entry is located at (37.758916, -122.43792)\n",
    "\n",
    "![Gynmnastic's Location](Images/gymnastics_location.JPG)\n",
    "\n",
    "$ $\n",
    "\n",
    "Peastman's node for the martial arts entry is located at (37.4667705, -122.2080786) \n",
    "\n",
    "$ $\n",
    "\n",
    "![Martial Arts Location](Images/martial_arts_location.JPG)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "db.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Conclusion"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Input Recommendations**\n",
    "We observed the sparse osm file southmodesto.osm needed to be audited before fully importing and creating a database off of it. Integrity in our data should be the overall theme for this and several other datasets.\n",
    "\n",
    "To either avoid mishaps of entered data, here are some recommendations:\n",
    "\n",
    "- Create a input guideline for users.\n",
    "- Set conditions on entry boxes to avoid incorrect submissions \n",
    "- Inputing missing values from other values within the same node.\n",
    "- Using third party data  to cross validate and improve the dataset.\n",
    "- Create a preliminary warning \"Are you sure this is the correct input\" before final submission\n",
    "\n",
    "With these ideas being implemented, we provide additional ease in importing and transforming data. I.e., With these above conditions being implemented, the data analyst's dirty work(data wrangle) becomes more tolerable. \n",
    "\n",
    "<span style = \"color:purple\"> **Note:**\n",
    "If administrators continue data wrangling procedures without the implementation of the above recommendations, the administrator needs to ensure that the data cleaning does not create data loss of important user entries </span>\n",
    "\n",
    "**Consequences**\n",
    "\n",
    "The potential problems of implementing these solutions is user satisfaction of using services. If a user keeps on receiving notifications or warnings in submission process, the likelyhood of the user using are services diminishes.\n",
    "\n",
    "We can solve these potential problems when implementing the above solutions by providing examples along the entry locations. \n",
    "In putting in your birth in an online application, there usually is a provided example of how to enter your birthdate like so:\n",
    "\n",
    "[___ENTER HERE____] <span style=\"color:red\">(Ex. MM/DD/YYYY)</span>\n",
    "\n",
    "User instructions with our recommended solutions provide a balance between user actions and administrative needs. Therefore, the likelyhood of a diminished retention rate would not change significantly.\n",
    "\n",
    "\n",
    "With these above ideas being implemented, we provide a more sound import process of our data into our required data format."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# End"
   ]
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python [conda root]",
   "language": "python",
   "name": "conda-root-py"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}