OpenStreetMilwaukeeWI.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b WRANGLE OPEN STREET MAP DATA >"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#  WRANGLE OPENSTREETMAP DATA \n",
    "_____________________________________________________________________\n",
    "\n",
    "## MILWAUKEE, WI, UNITED STATES\n",
    "\n",
    "OpenStreetMap link to Milwaukee Map http://www.openstreetmap.org/relation/251075\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img src=\"bigphotoformilwaukee.jpg\" align='left'>\n",
    "### Milwaukee<br>\n",
    "<b>City in Wisconsin</b><br>\n",
    "*Google Search Info<br>\n",
    "Milwaukee is a city in the U.S. state of Wisconsin on Lake Michigan's western shore. It's known for its breweries, many of which offer tours chronicling its role in the beer industry. Overlooking the Menomonee River, the Harley-Davidson Museum displays classic motorcycles, including one of Elvis Presley’s. Nearby is the Milwaukee Public Museum, with its large-scale European Village and a recreation of old Milwaukee.<br>\n",
    "\n",
    "Population: 595,047 (2016)<br>\n",
    "Zip code: 532XX<br>\n",
    "Area code: Area code 414<br>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I chose Milwaukee because I grew up just north of the city.  I remember going to concerts, basketball games, and eating at some great restaurants downtown.  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In preparing to audit, clean, and explore the dataset, I gathered information from Udacity's Data Wrangling course.  I also gathered information from https://regexone.com/ when using regular expressions.  Most of the code seen in the street name/direction/postcode audits has been modified from Udacity's Data Wrangling quizzes, as well as code used when auditing problem characters and when validating and writing to CSV."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Explore Sample Dataset\n",
    "______________________________________________"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#import modules\n",
    "#write/explore sample dataset\n",
    "\n",
    "import xml.etree.ElementTree as ET\n",
    "import re\n",
    "import pprint\n",
    "\n",
    "OSM_FILE = \"Milwaukee_dataset.bz2.osm\"\n",
    "SAMPLE_FILE = \"milw_sample.osm\"\n",
    "\n",
    "#run code to write SAMPLE_FILE\n",
    "\n",
    "#%run sample.py"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To familiarize myself with the elements in the dataset, I referenced [http://wiki.openstreetmap.org/wiki/Elements](OpenStreetMap Wiki)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def count_tags(filename):\n",
    "    \"\"\"Count top level tags\"\"\"\n",
    "    tag_count = {}\n",
    "    osm_file = open(filename, \"r\")\n",
    "    for each, elem in ET.iterparse(osm_file):\n",
    "        if elem.tag in tag_count:\n",
    "            tag_count[elem.tag] += 1\n",
    "        else:\n",
    "            tag_count[elem.tag] = 1\n",
    "    return tag_count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'member': 637,\n",
       " 'nd': 32148,\n",
       " 'node': 24689,\n",
       " 'osm': 1,\n",
       " 'relation': 65,\n",
       " 'tag': 17134,\n",
       " 'way': 3244}"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sample_tags = count_tags(SAMPLE_FILE)\n",
    "sample_tags"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## PROBLEMS WITH DATA\n",
    "___________________________________________"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "After looking over the sample osm file, I came across a few potential problems that I'd like to change.  Although there doesn't seem to be very many abbreviated street names within the \"addr:street\" tag key, there are a few that can be changed to full street type names.  Also, within the same field, we can change all \"N\", \"S\", \"E\", or \"W\" to \"North\", \"South\", \"East\", or \"West\".\n",
    "\n",
    "I will also check that all postal codes start with \"53\" and are 5-digit codes.  Also, phone number formats may not be consistent and will need to be checked against a regular expression.  \n",
    "\n",
    "Also, since we will be writing the data into CSV files, we'll need to check for commas within the data.\n",
    "\n",
    "1.  Change abbreviated name/street types to non-abbreviated types (St=Street,Dr=Drive,etc.)\n",
    "\n",
    "2.  Change all abbreviated street directions (N, S, E, W) to non abbreviated directions (North, South, East, West.)\n",
    "\n",
    "3.  Check area codes (53XXX area code.)\n",
    "\n",
    "4.  Check for the same phone number format (+1-555-555-5555) across all phone data.\n",
    "\n",
    "5.  Check for commas and replace with underscore\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Street Types"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "from collections import defaultdict\n",
    "\n",
    "\n",
    "def print_sorted_dict(d):\n",
    "    keys = d.keys()\n",
    "    keys = sorted(keys, key=lambda s: s.lower())\n",
    "    for k in keys:\n",
    "        v = d[k]\n",
    "        print \"%s: %d\" % (k, v) \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def count_occur(reg_x, data_name, df_dict):\n",
    "    \"\"\"Count occurences of certain values within tags\"\"\"\n",
    "    m = reg_x.search(data_name)\n",
    "    if m:\n",
    "        data_group = m.group()\n",
    "        df_dict[data_group] += 1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*I did more generic functions here so that they could be used to audit/clean both street types and street directions.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def audit_data(file_name, is_func, reg_x):\n",
    "    osm_file = open(file_name, \"r\")\n",
    "    df_dict = defaultdict(int)\n",
    "    for event, elem in ET.iterparse(osm_file):\n",
    "        if is_func(elem):\n",
    "            count_occur(reg_x, elem.attrib['v'], df_dict)\n",
    "    print_sorted_dict(df_dict)\n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def is_street_name(elem):\n",
    "    \"\"\"Find street names within tags\"\"\"\n",
    "    return (elem.tag == \"tag\") and (elem.attrib['k'] == \"addr:street\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#regular expression to find street type\n",
    "street_type_re = re.compile(r'\\S+\\.?$', re.IGNORECASE)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Ave: 2\n",
      "Avenue: 91\n",
      "Boulevard: 1\n",
      "Court: 4\n",
      "Drive: 7\n",
      "Lane: 9\n",
      "Place: 22\n",
      "Rd: 1\n",
      "Road: 10\n",
      "Street: 149\n",
      "Way: 4\n",
      "Wells: 1\n"
     ]
    }
   ],
   "source": [
    "audit_data(SAMPLE_FILE, is_street_name, street_type_re)\n",
    "#audit_data(OSM_FILE, is_street_name, street_type_re)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It's interesting that \"Wells\" shows up as one of the street types.  I'm pretty sure that \"Wells\" is not a street type, so I searched for \"Wells\" by pulling up my sample file and using CTRL F to find the full name quickly.  The results showed \"W. Wells\", which I know is \"Wells Street\" in Milwaukee.  So, I will have to add \"Street\" to that value and change \"W.\" to \"West\" in order for the value to show \"West Wells Street\"."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# %load street_types.py\n",
    "expected = [\"Street\", \"Avenue\", \"Boulevard\", \"Drive\", \"Court\", \"Place\", \"Square\", \"Lane\", \"Road\", \n",
    "            \"Trail\", \"Parkway\", \"Commons\", \"Way\", \"Circle\", \"Trace\"]\n",
    "\n",
    "\n",
    "def audit_street_type(street_types, street_name):\n",
    "    \"\"\"Check street type in data against expected types\"\"\"\n",
    "    m = street_type_re.search(street_name)\n",
    "    if m:\n",
    "        street_type = m.group()\n",
    "        if street_type not in expected:\n",
    "            street_types[street_type].add(street_name)\n",
    "\n",
    "            \n",
    "def audit_street(osmfile):\n",
    "    osm_file = open(osmfile, \"r\")\n",
    "    street_types = defaultdict(set)\n",
    "    for event, elem in ET.iterparse(osm_file, events=(\"start\",)):\n",
    "\n",
    "        if elem.tag == \"node\" or elem.tag == \"way\":\n",
    "            for tag in elem.iter(\"tag\"):\n",
    "                if is_street_name(tag):\n",
    "                    audit_street_type(street_types, tag.attrib['v'])\n",
    "    osm_file.close()\n",
    "    return street_types"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'Ave': set(['W North Ave']),\n",
      " 'Rd': set(['N Green Bay Rd']),\n",
      " 'Wells': set(['W. Wells'])}\n"
     ]
    }
   ],
   "source": [
    "#What needs fixing?\n",
    "\n",
    "st_types = audit_street(SAMPLE_FILE)\n",
    "#st_types = audit_street(OSM_FILE)\n",
    "pprint.pprint(dict(st_types))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# %load update_street_type.py\n",
    "mapping = { \"St\": \"Street\",\n",
    "            \"St.\": \"Street\",\n",
    "            \"Ave\": \"Avenue\",\n",
    "            \"Ave.\": \"Avenue\",\n",
    "            \"PL\": \"Place\",\n",
    "            \"Rd\": \"Road\",\n",
    "            \"Rd.\": \"Road\",\n",
    "            \"Cir.\": \"Circle\",\n",
    "            \"Providence\": \"Providence Avenue\",\n",
    "            \"Wells\": \"Wells Street\"\n",
    "           }\n",
    "           \n",
    "                \n",
    "def update_type(name, mapping):\n",
    "    \"\"\"Replace abbreviated street type with full version using mapping\"\"\"\n",
    "    name = name\n",
    "    split_name = name.split(' ')\n",
    "    \n",
    "    for i in split_name:\n",
    "        if i in mapping.keys():\n",
    "            name = name.replace(i,mapping[i])\n",
    "\n",
    "    return name\n",
    "\n",
    "def change_name(st_types):\n",
    "    \"\"\"iterate through street types and use helper function update_name to update data\"\"\"\n",
    "    for st_type, ways in st_types.iteritems():\n",
    "            for name in ways:\n",
    "                better_name = update_type(name, mapping)\n",
    "                print name, \"=>\", better_name\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "N Green Bay Rd => N Green Bay Road\n",
      "W North Ave => W North Avenue\n",
      "W. Wells => W. Wells Street\n"
     ]
    }
   ],
   "source": [
    "change_name(st_types) #shows before and after update"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Street Direction"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#regular expression for cardinal directions\n",
    "street_direction_re = re.compile(r'^([nsew]{1}\\.? )|(north|south|east|west){1}[ ]{1}', re.IGNORECASE)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "East : 58\n",
      "N : 1\n",
      "North : 164\n",
      "South : 13\n",
      "W : 2\n",
      "W. : 1\n",
      "West : 58\n"
     ]
    }
   ],
   "source": [
    "audit_data(SAMPLE_FILE, is_street_name, street_direction_re)\n",
    "#audit_data(OSM_FILE, is_street_name, street_direction_re)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# %load street_directions.py\n",
    "d_expected = [\"North \", \"South \", \"East \", \"West \"]\n",
    "\n",
    "def audit_street_direction(street_directions, street_name):\n",
    "    m = street_direction_re.search(street_name)\n",
    "    if m:\n",
    "        street_direction = m.group()\n",
    "        if street_direction not in d_expected:\n",
    "            street_directions[street_direction].add(street_name)\n",
    "            \n",
    "            \n",
    "def audit_direction(osmfile):\n",
    "    \n",
    "    osm_file = open(osmfile, \"r\")\n",
    "    street_directions = defaultdict(set)\n",
    "    \n",
    "    for event, elem in ET.iterparse(osm_file, events=(\"start\",)):\n",
    "\n",
    "        if elem.tag == \"node\" or elem.tag == \"way\":\n",
    "            for tag in elem.iter(\"tag\"):\n",
    "                if is_street_name(tag):\n",
    "                    audit_street_direction(street_directions, tag.attrib['v'])\n",
    "                    \n",
    "    osm_file.close()\n",
    "    \n",
    "    return street_directions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'N ': set(['N Green Bay Rd']),\n",
      " 'W ': set(['W North Ave']),\n",
      " 'W. ': set(['W. Wells'])}\n"
     ]
    }
   ],
   "source": [
    "#What needs fixing?\n",
    "\n",
    "#st_directions = audit_direction(OSM_FILE)\n",
    "st_directions = audit_direction(SAMPLE_FILE)\n",
    "\n",
    "pprint.pprint(dict(st_directions))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# %load update_street_directions.py\n",
    "d_mapping = {\"N.\": \"North\",\n",
    "             \"N\": \"North\",\n",
    "             \"S.\": \"South\",\n",
    "             \"S\": \"South\",\n",
    "             \"E.\": \"East\",\n",
    "             \"E\": \"East\",\n",
    "             \"W.\": \"West\",\n",
    "             \"W\": \"West\",\n",
    "             \"SOUTH\": \"South\"}\n",
    "\n",
    "def update_direction(name, d_mapping):\n",
    "    \"\"\"Replace abbreviated street directions with full version using d_mapping\"\"\"\n",
    "    name = name\n",
    "    split_name = name.split(' ')\n",
    "    \n",
    "    for i in split_name:\n",
    "        if i in d_mapping.keys():\n",
    "            name = name.replace(i,d_mapping[i])\n",
    "\n",
    "    return name\n",
    "\n",
    "def change_direction(st_directions):\n",
    "    \"\"\"iterate through street directions and use helper function update_direction to update data\"\"\"\n",
    "    for st_direction, ways in st_directions.iteritems():\n",
    "            for name in ways:\n",
    "                better_name = update_direction(name, d_mapping)\n",
    "                print name, \"=>\", better_name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "N Green Bay Rd => North Green Bay Rd\n",
      "W. Wells => West Wells\n",
      "W North Ave => West North Ave\n"
     ]
    }
   ],
   "source": [
    "change_direction(st_directions) #show before and after update"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Postal Codes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# %load postcodes.py\n",
    "postal_codes = defaultdict(int)\n",
    "\n",
    "#regular expression for postal codes\n",
    "postal_code_re = re.compile(r'^[5][3]\\d{3}$')\n",
    "\n",
    "def is_postal_code(elem):\n",
    "    return (elem.tag == \"tag\") and (elem.attrib['k'] == \"addr:postcode\")\n",
    "\n",
    "def count_postal_code(postal_codes, postal_code):\n",
    "    \"\"\"Count number of each unconventional postal code\"\"\"\n",
    "    m = postal_code_re.search(postal_code)\n",
    "    if not m:\n",
    "\n",
    "        postal_codes[postal_code] += 1\n",
    "\n",
    "\n",
    "def audit_zip(file_name):\n",
    "    osm_file = open(file_name, \"r\")\n",
    "    for event, elem in ET.iterparse(osm_file):\n",
    "        if is_postal_code(elem):\n",
    "            count_postal_code(postal_codes, elem.attrib['v'])  \n",
    "            \n",
    "    osm_file.close()\n",
    "    return postal_codes\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'Milwaukee WI, 53222': 1}\n"
     ]
    }
   ],
   "source": [
    "fix_pc = audit_zip(SAMPLE_FILE)\n",
    "#fix_pc = audit_zip(OSM_FILE)\n",
    "\n",
    "pprint.pprint(dict(fix_pc))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# %load update_postcodes.py\n",
    "def change_zip(zip_code):\n",
    "    \"\"\"Isolate first 5 digits in value attribute\"\"\"\n",
    "    find_zip_re = re.compile(r'(53\\d{3})')\n",
    "    m = find_zip_re.search(zip_code)\n",
    "    if m:\n",
    "        new_zip = m.group()\n",
    "        return new_zip\n",
    "    else:\n",
    "        return zip_code\n",
    "    \n",
    "def update_postal_codes(fix_pc):\n",
    "    for zip_code in fix_pc.keys():\n",
    "        print zip_code, \"=>\", change_zip(zip_code)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Milwaukee WI, 53222 => 53222\n"
     ]
    }
   ],
   "source": [
    "update_postal_codes(fix_pc)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The problem with this way of cleaning postal codes is that it cuts out the last 4 digits of some codes that utilize the ZIP+4 system.  This may put the postal service at a disadvantage when someone uses an address from OSM.  However, the majority of postcode values are only 5 digits.  So, for the sake of conformity, we'll make sure they're all 5 digits."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Phone Numbers"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Proper Usage from http://wiki.openstreetmap.org/wiki/Key:phone\n",
    "<br>\n",
    "phone=number where the number should be in international (ITU-T E.164) format\n",
    "<br>\n",
    "phone=+[country_code] [area_code] [local_number], following the ITU-T E.123 and the DIN 5008 pattern\n",
    "<br>\n",
    "(phone=+[country_code]-[area_code]-[local_number], following the RFC 3966/NANP pattern)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# %load phone_numbers.py\n",
    "phone_re = re.compile(r'\\+1[\\s-]\\d{3}[\\s-]\\d{3}[\\s-]\\d{4}$')\n",
    "\n",
    "def is_phone_number(elem):\n",
    "    return (elem.tag == \"tag\") and (elem.attrib['k'] == \"contact:phone\")\n",
    "\n",
    "def find_phone_numbers(phone_number):\n",
    "    \"\"\"Find phone numbers that need fixing\"\"\"\n",
    "    m = phone_re.search(phone_number)\n",
    "    if not m:\n",
    "        return phone_number\n",
    "\n",
    "\n",
    "def audit_phone(file_name):\n",
    "    with open(file_name, \"r\") as osm_file:\n",
    "        num_list = []\n",
    "        for event, elem in ET.iterparse(osm_file):\n",
    "            if is_phone_number(elem):\n",
    "                contact_num = find_phone_numbers(elem.attrib['v'])\n",
    "                if contact_num != None:\n",
    "                    num_list.append(contact_num)\n",
    "        return num_list\n",
    "     "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['+1.414.291.3900',\n",
       " '+1.414.227.4039',\n",
       " '+1.414.327.0166',\n",
       " '+1.414.353.3212',\n",
       " '+1.414.352.9525',\n",
       " '+1.414.874.8400',\n",
       " '+1.414.902.4400']"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "num_list = audit_phone(SAMPLE_FILE)\n",
    "#num_list = audit_phone(OSM_FILE)\n",
    "\n",
    "num_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# %load update_phone.py\n",
    "#change periods to dashes\n",
    "def update_number(num):\n",
    "    new_num = num.replace(\".\",\"-\",3)\n",
    "    return new_num\n",
    "\n",
    "def change_numbers(num_list):\n",
    "    for num in num_list:\n",
    "        print num, \"=>\", update_number(num)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+1.414.291.3900 => +1-414-291-3900\n",
      "+1.414.227.4039 => +1-414-227-4039\n",
      "+1.414.327.0166 => +1-414-327-0166\n",
      "+1.414.353.3212 => +1-414-353-3212\n",
      "+1.414.352.9525 => +1-414-352-9525\n",
      "+1.414.874.8400 => +1-414-874-8400\n",
      "+1.414.902.4400 => +1-414-902-4400\n"
     ]
    }
   ],
   "source": [
    "change_numbers(num_list)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Although the sample shows only numbers with the irregular character \".\", there may be other numbers that do not conform to the pattern I set in the regular expression.  These could be changed using another function to address individual irregularities if need be.   "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Commas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#use this function when cleaning, before writing to csv\n",
    "\n",
    "def check_comma(elem):\n",
    "    \"Check for commas in element and replace with underscore\"\n",
    "    if ',' in elem:\n",
    "        elem = elem.replace(',', '_')\n",
    "        return elem\n",
    "    else:\n",
    "        return elem"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# %load problem_char.py\n",
    "#Find problem characters/audit data\n",
    "lower = re.compile(r'^([a-z]|_)*$')\n",
    "lower_colon = re.compile(r'^([a-z]|_)*:([a-z]|_)*$')\n",
    "problemchars = re.compile(r'[=\\+/&<>;\\'\"\\?%#$@\\,\\. \\t\\r\\n]')\n",
    "\n",
    "\n",
    "def key_type(element, keys):\n",
    "    \"\"\"Element keys with all lowercase letters: add to 'lower'\n",
    "    Element keys with lowercase letters and colon: add to 'lower_colon'\n",
    "    Element keys with problem characters: add to 'problemchars'\n",
    "    \"\"\"\n",
    "    if element.tag == \"tag\":\n",
    "        \n",
    "        if lower.search(element.attrib['k']):\n",
    "            keys['lower'] += 1\n",
    "        \n",
    "        elif lower_colon.search(element.attrib['k']):\n",
    "            keys['lower_colon'] += 1\n",
    "        elif problemchars.search(element.attrib['k']):\n",
    "            print element.attrib['k']\n",
    "            keys['problemchars'] += 1\n",
    "        \n",
    "        else:\n",
    "            keys['other'] += 1\n",
    "        \n",
    "    return keys\n",
    "\n",
    "def process_map(filename):\n",
    "    keys = {\"lower\": 0, \"lower_colon\": 0, \"problemchars\": 0, \"other\": 0}\n",
    "    for _, element in ET.iterparse(filename):\n",
    "        keys = key_type(element, keys)\n",
    "\n",
    "    return keys"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "service area\n",
      "name:Washington County Commuter Express\n",
      "name:Washington County Commuter Express\n",
      "name:Washington County Commuter Express\n"
     ]
    }
   ],
   "source": [
    "#Process map and print out Problem Characters in OSM_FILE\n",
    "\n",
    "keys = process_map(OSM_FILE)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'lower': 187930, 'lower_colon': 149320, 'other': 4884, 'problemchars': 4}\n"
     ]
    }
   ],
   "source": [
    "pprint.pprint(keys)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "## Clean Data and Write to CSV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# %load clean_to_csv.py\n",
    "#Modified code from Data Wrangling \"Preparing for Database SQL\" quiz\n",
    "\n",
    "import csv\n",
    "import codecs\n",
    "import pprint\n",
    "import re\n",
    "import xml.etree.cElementTree as ET\n",
    "\n",
    "import cerberus\n",
    "\n",
    "import schema\n",
    "\n",
    "\n",
    "OSM_FILE = \"Milwaukee_dataset.bz2.osm\"\n",
    "\n",
    "NODES_PATH = \"nodes.csv\"\n",
    "NODE_TAGS_PATH = \"nodes_tags.csv\"\n",
    "WAYS_PATH = \"ways.csv\"\n",
    "WAY_NODES_PATH = \"ways_nodes.csv\"\n",
    "WAY_TAGS_PATH = \"ways_tags.csv\"\n",
    "\n",
    "LOWER_COLON = re.compile(r'^([a-z]|_)+:([a-z]|_)+')\n",
    "PROBLEMCHARS = re.compile(r'[=\\+/&<>;\\'\"\\?%#$@\\,\\. \\t\\r\\n]')\n",
    "\n",
    "SCHEMA = schema.schema\n",
    "\n",
    "# Make sure the fields order in the csvs matches the column order in the sql table schema\n",
    "NODE_FIELDS = ['id', 'lat', 'lon', 'user', 'uid', 'version', 'changeset', 'timestamp']\n",
    "NODE_TAGS_FIELDS = ['id', 'key', 'value', 'type']\n",
    "WAY_FIELDS = ['id', 'user', 'uid', 'version', 'changeset', 'timestamp']\n",
    "WAY_TAGS_FIELDS = ['id', 'key', 'value', 'type']\n",
    "WAY_NODES_FIELDS = ['id', 'node_id', 'position']\n",
    "\n",
    "\n",
    "def shape_element(element, node_attr_fields=NODE_FIELDS, way_attr_fields=WAY_FIELDS,\n",
    "                  problem_chars=PROBLEMCHARS, default_tag_type='regular'):\n",
    "    \"\"\"Clean and shape node or way XML element to Python dict\"\"\"\n",
    "\n",
    "    node_attribs = {}\n",
    "    way_attribs = {}\n",
    "    way_nodes = []\n",
    "    tags = []  # Handle secondary tags the same way for both node and way elements\n",
    "    \n",
    "    count = 0 #way node position\n",
    "    \n",
    "    for i in element:\n",
    "        \n",
    "        if i.tag == 'tag':\n",
    "            \n",
    "            clean_v = None  #cleaned values: street names, postal codes, and phone numbers\n",
    "            tag_dict = {}\n",
    "            tag_dict['id'] = element.get('id')\n",
    "            key_elem = i.get('k')\n",
    "            value_elem = i.get('v')\n",
    "            \n",
    "            if not PROBLEMCHARS.search(key_elem):\n",
    "                \n",
    "                #clean street name\n",
    "                if key_elem == \"addr:street\":\n",
    "                    clean_v = update_type(value_elem, mapping) #clean type\n",
    "                    clean_v = update_direction(clean_v, d_mapping) #clean direction\n",
    "                             \n",
    "                #clean postcodes            \n",
    "                if key_elem == \"addr:postcode\":\n",
    "                    clean_v = change_zip(value_elem)\n",
    "                    \n",
    "                #clean phone numbers    \n",
    "                if key_elem == \"contact:phone\":\n",
    "                    clean_v = update_number(value_elem)\n",
    "                \n",
    "                if clean_v != None:\n",
    "                    clean_v = check_comma(clean_v)\n",
    "                    tag_dict['value'] = clean_v\n",
    "                    \n",
    "                else:\n",
    "                    tag_dict['value'] = value_elem\n",
    "                    \n",
    "                if LOWER_COLON.search(key_elem):\n",
    "                    key_type = key_elem.split(':')\n",
    "                    \n",
    "                    if len(key_type) > 2:\n",
    "                        tag_dict['key'] = ':'.join(key_type[1:])\n",
    "                        tag_dict['type'] = key_type[0]\n",
    "                        \n",
    "                    else:\n",
    "                        tag_dict['key'] = key_type[1]\n",
    "                        tag_dict['type'] = key_type[0]\n",
    "                        \n",
    "                else:\n",
    "                    tag_dict['key'] = key_elem\n",
    "                    tag_dict['type'] = default_tag_type\n",
    "            \n",
    "                \n",
    "            tags.append(tag_dict)\n",
    "            \n",
    "        if i.tag == 'nd':\n",
    "            node_dict = {}\n",
    "            node_dict['id'] = element.get('id')\n",
    "            node_dict['node_id'] = i.get('ref')\n",
    "            node_dict['position'] = count\n",
    "            count += 1\n",
    "            way_nodes.append(node_dict)\n",
    "        \n",
    "    \n",
    "    if element.tag == 'node':\n",
    "        for i in node_attr_fields:\n",
    "            node_attribs[i] = element.get(i)\n",
    "        return {'node': node_attribs, 'node_tags': tags}\n",
    "        \n",
    "    elif element.tag == 'way':\n",
    "        for i in way_attr_fields:\n",
    "            way_attribs[i] = element.get(i)\n",
    "        return {'way': way_attribs, 'way_nodes': way_nodes, 'way_tags': tags}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "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",
    "def validate_element(element, validator, schema=SCHEMA):\n",
    "    \"\"\"Raise ValidationError if element does not match schema\"\"\"\n",
    "    if validator.validate(element, schema) is not True:\n",
    "        field, errors = next(validator.errors.iteritems())\n",
    "        message_string = \"\\nElement of type '{0}' has the following errors:\\n{1}\"\n",
    "        error_string = pprint.pformat(errors)\n",
    "        \n",
    "        raise Exception(message_string.format(field, error_string))\n",
    "        \n",
    "        \n",
    "class UnicodeDictWriter(csv.DictWriter, object):\n",
    "    \"\"\"Extend csv.DictWriter to handle Unicode input\"\"\"\n",
    "\n",
    "    def writerow(self, row):\n",
    "        super(UnicodeDictWriter, self).writerow({\n",
    "            k: (v.encode('utf-8') if isinstance(v, unicode) else v) for k, v in row.iteritems()\n",
    "        })\n",
    "\n",
    "    def writerows(self, rows):\n",
    "        for row in rows:\n",
    "            self.writerow(row)\n",
    "            \n",
    "            "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def process_map(file_in, validate):\n",
    "    \"\"\"Iteratively process each XML element and write to csv(s)\"\"\"\n",
    "\n",
    "    with codecs.open(NODES_PATH, 'w') as nodes_file, \\\n",
    "         codecs.open(NODE_TAGS_PATH, 'w') as nodes_tags_file, \\\n",
    "         codecs.open(WAYS_PATH, 'w') as ways_file, \\\n",
    "         codecs.open(WAY_NODES_PATH, 'w') as way_nodes_file, \\\n",
    "         codecs.open(WAY_TAGS_PATH, 'w') as way_tags_file:\n",
    "\n",
    "        nodes_writer = UnicodeDictWriter(nodes_file, NODE_FIELDS)\n",
    "        node_tags_writer = UnicodeDictWriter(nodes_tags_file, NODE_TAGS_FIELDS)\n",
    "        ways_writer = UnicodeDictWriter(ways_file, WAY_FIELDS)\n",
    "        way_nodes_writer = UnicodeDictWriter(way_nodes_file, WAY_NODES_FIELDS)\n",
    "        way_tags_writer = UnicodeDictWriter(way_tags_file, WAY_TAGS_FIELDS)\n",
    "\n",
    "        nodes_writer.writeheader()\n",
    "        node_tags_writer.writeheader()\n",
    "        ways_writer.writeheader()\n",
    "        way_nodes_writer.writeheader()\n",
    "        way_tags_writer.writeheader()\n",
    "\n",
    "        validator = cerberus.Validator()\n",
    "\n",
    "        for element in get_element(file_in, tags=('node', 'way')):\n",
    "            el = shape_element(element)\n",
    "            if el:\n",
    "                if validate is True:\n",
    "                    validate_element(el, validator)\n",
    "\n",
    "                if element.tag == 'node':\n",
    "                    nodes_writer.writerow(el['node'])\n",
    "                    node_tags_writer.writerows(el['node_tags'])\n",
    "                elif element.tag == 'way':\n",
    "                    ways_writer.writerow(el['way'])\n",
    "                    way_nodes_writer.writerows(el['way_nodes'])\n",
    "                    way_tags_writer.writerows(el['way_tags'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#Clean and Write from OSM_FILE (Validate using SAMPLE_FILE)\n",
    "\n",
    "#process_map(SAMPLE_FILE, validate=True)\n",
    "#process_map(OSM_FILE, validate= False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## STATISTICS OF DATASET"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**File Sizes**\n",
    "\n",
    "```\n",
    "OSM_FILE .............  115 MB (120,673,495 bytes)\n",
    "SAMPLE_FILE .......... 5.67 MB (5,950,406 bytes)\n",
    "milw_osm.db .......... 60.0 MB (62,936,064 bytes)\n",
    "nodes.csv ............ 39.8 MB (41,784,324 bytes)\n",
    "nodes_tags.csv ....... 2.24 MB (2,350,587 bytes)\n",
    "ways.csv ............. 3.80 MB (3,987,210 bytes)\n",
    "ways_nodes.csv ....... 15.3 MB (16,146,938 bytes)\n",
    "ways_tags.csv ........ 9.51 MB (9,972,938 bytes)\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Import CSVs into milw_osm.db**\n",
    "\n",
    "\n",
    "```sqlite3\n",
    ".mode csv\n",
    ".import nodes.csv nodes\n",
    ".import nodes_tags.csv nodes_tags\n",
    ".import ways.csv ways\n",
    ".import ways_nodes.csv ways_nodes\n",
    ".import ways_tags.csv ways_tags\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Query Number of Nodes and Ways**\n",
    "\n",
    "\n",
    "```sqlite3\n",
    "SELECT count(*) FROM nodes;\n",
    "493767\n",
    "```\n",
    "\n",
    "\n",
    "```sqlite3\n",
    "SELECT count(*) FROM ways;\n",
    "64879\n",
    "```\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Number of Unique Users**\n",
    "\n",
    "```sqlite3\n",
    "SELECT COUNT(DISTINCT(user)) FROM (SELECT user FROM nodes UNION ALL SELECT user FROM ways);\n",
    "485\n",
    "```\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Count number of alternate names recorded**\n",
    "\n",
    "```sqlite3\n",
    "SELECT COUNT(*) FROM (SELECT key FROM nodes_tags UNION ALL SELECT key FROM ways_tags) WHERE key=\"alt_name\";\n",
    "213\n",
    "```\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Places with alternate names in Milwaukee (Limit 10)**\n",
    "\n",
    "It's interesting to see what \"alternate names\" are used to describe some of the businesses/buildings/streets in the Milwaukee area, although some of them seem to be the actual legal business name.\n",
    "\n",
    "```sqlite \n",
    "SELECT value FROM (SELECT key,value FROM nodes_tags UNION ALL SELECT key,value FROM ways_tags) WHERE key=\"alt_name\" LIMIT 10; \n",
    "\n",
    "\"Milwaukee County Mental Health Complex\"                                                        \n",
    "\"STD Clinic\"                                                                                    \n",
    "\"Kinko's\"                                                                                       \n",
    "\"The Polish Moon\"                                                                               \n",
    "\"Riverwest grocery Cooperative\"                                                                 \n",
    "\"Wisconsin Housing and Economic Development Authority\"                                          \n",
    "TWC                                                                                             \n",
    "\"Milwaukee's Original Haus Party\"                                                             \n",
    "\"Irish Fest\"                                                                                    \n",
    "\"Thomas A. Greene Memorial Museum\"\n",
    "\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Are there any areas in Milwaukee that allow access to horse riders?**\n",
    "\n",
    "```sqlite3\n",
    " SELECT value,COUNT(*) FROM (SELECT key,value FROM nodes_tags UNION ALL SELECT key,value FROM ways_tags) WHERE key=\"horse\" GROUP BY value;\n",
    " \n",
    " no,331\n",
    " unknown,4\n",
    " yes,1\n",
    "```\n",
    "I wonder what that 1 area in Milwaukee is?  I'm guessing it's at the lakefront.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## ADDITIONAL IMPROVEMENTS\n",
    "______________________________________________________\n",
    "\n",
    "-  Clean up more of the phone numbers that are not in the correct format\n",
    "-  Check county and take out any that are not Milwaukee County\n",
    "-  Check for valid websites"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "**PHONE NUMBERS**: If all phone numbers are in the correct format, they can be used by developers/users for auto dialing purposes.  However, not everyone will want the chosen format for one reason or another.  Can't please 'em all!\n",
    "```sqlite3\n",
    "SELECT value FROM (SELECT key,value FROM nodes_tags UNION ALL SELECT key,value FROM ways_tags) WHERE key=\"phone\" LIMIT 10;\n",
    "\n",
    "\"+1-(414) 604-7800\"\n",
    "\"(414) 875-4500\"\n",
    "\"+1-(414) 294-1200\"\n",
    "+1-414-393-4900\n",
    "\"+1-(414) 902-9500\"\n",
    "+1-414-267-0700\n",
    "\"+1-(414) 578-5100\"\n",
    "\"+1-(414) 393-4800\"\n",
    "+1-414-463-3878\n",
    "+1-414-267-0500\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**COUNTY**: Some counties listed may not be listed as Milwaukee County, although the zipcode may start with \"53\".  We can get rid of these values completely or keep them in for continued reference.  The counties listed may be surrounding areas with important landmarks that may help someone navigating the Milwaukee area, so it may be best to keep them in the dataset.\n",
    "\n",
    "```sqlite3\n",
    "SELECT value FROM (SELECT key,value FROM nodes_tags UNION ALL SELECT key,value FROM ways_tags) WHERE key=\"county\" AND value!=\"Milwaukee, WI\" LIMIT 10; \n",
    "\n",
    "\"Milwaukee, WI;Waukesha, WI\"\n",
    "\"Milwaukee, WI; Waukesha, WI\"\n",
    "\"Washington, WI\"\n",
    "\"Washington, WI\"\n",
    "\"Washington, WI\"\n",
    "\"Washington, WI\"\n",
    "\"Washington, WI\"\n",
    "\"Washington, WI\"\n",
    "\"Washington, WI\"\n",
    "\"Washington, WI\"\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**VALID WEBSITE**: Some websites may not be valid and therefore unuseable to those that are trying to contact local businesses/individuals.  However, if we get rid of all invalid web addrs programmatically using a specific 'http' format, we may get rid of some that could have otherwise been deciphered manually and converted to a valid web address.\n",
    "\n",
    "```sqlite3\n",
    "SELECT value FROM (SELECT key,value FROM nodes_tags UNION ALL SELECT key,value FROM ways_tags) WHERE key=\"website\" AND value NOT LIKE 'http%' LIMIT 20;\n",
    "\n",
    "www.millertimepub.com\n",
    "www.waterstreetbrewery.com\n",
    "www.elsas.com\n",
    "www.louiseswisconsin.com\n",
    "www.coastrestaurant.com/\n",
    "www.thecheesecakefactory.com\n",
    "www.uwcu.org\n",
    "mabaensch.com\n",
    "riverwestcoop.org\n",
    "trulyspokencycles.net\n",
    "outpostnaturalfoods.coop\n",
    "elreyfoods.publishpath.com\n",
    "chickenpalacewi.com\n",
    "aldi.us\n",
    "advanceautoparts.com\n",
    "napaonline.com\n",
    "walgreens.com\n",
    "bk.com\n",
    "radioshack.com\n",
    "nationalace.com     \n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "# Benefits and Issues\n",
    "\n",
    "While investigating the OSM dataset, I found some potential improvements.\n",
    "\n",
    "1.  Abbreviated street types can be changed to full street type name (Ave: Avenue, etc)\n",
    "    -  Benefits:\n",
    "        1.  All street types will be identified and searchable by standards\n",
    "        2.  The data will look clean and conformed to standards\n",
    "        3.  Types will be clearly stated with little chance for misinterpretation\n",
    "      \n",
    "    -  Anticipated Issues:\n",
    "        1.  Not all street types will be recognizable programmatically\n",
    "        2.  There is the potential for street name to be mistaken for street type and corrected in error\n",
    "        3.  Anyone searching the database using abbreviated street types may be unable to complete a successful search\n",
    "       <br> \n",
    "       <br>\n",
    "2.  Abbreviated street directions can be changed to full street direction name (N: North, etc.)\n",
    "    -  Benefits:\n",
    "        1.  All street directions will be identified and searchable by standards\n",
    "        2.  The data will look clean and conformed to standards\n",
    "        3.  Directions will be clearly stated with little chance for misinterpretation\n",
    "      \n",
    "    -  Anticipated Issues:\n",
    "        1.  If all \"N\", \"S\", \"E\", and \"W\" characters are changed to directions, there may be some changed in error (Highway E, County Road W)\n",
    "        2.  Any direction not abbreviated according to standards will be missed\n",
    "        3.  Anyone searching the database using abbreviated street types may be unable to complete a successful search\n",
    "        <br>\n",
    "        <br>\n",
    "3.  Postcodes can be checked for conformity (5-digit codes) and against Milwaukee area codes <-- Same with \"county\" (Milwaukee, WI)\n",
    "    -  Benefits:\n",
    "        1.  All postcodes will be identified and searchable by standards\n",
    "        2.  The data will look clean and conformed to standards\n",
    "        3.  Postcodes will be clearly stated with little chance for misinterpretation\n",
    "    \n",
    "    -  Anticipated Issues:\n",
    "        1.  If non Milwaukee postal code tags are taken out, other information may need to be taken out as well (street names, etc.)\n",
    "        2.  Some non Milwaukee area codes and corresponding information may be listed as a reference to important landmarks surrounding Milwaukee or for navigation purposes.  We may not want this taken out of our database. \n",
    "        3.  Some postcodes utilize a ZIP+4 system and cutting postcodes down to 5-digits may put any postal service utilizing OSM at a disadvantage.  \n",
    "        <br>\n",
    "        <br>\n",
    "4.  Phone numbers can be checked for conformity (+1-555-555-5555) and Milwaukee area code (414)\n",
    "    -  Benefits:\n",
    "        1.  All phone numbers may be used for autodialing applications with ease\n",
    "        2.  The data will look clean and conformed to standards\n",
    "        3.  Phone numbers will be clearly stated with little chance for misinterpretation\n",
    "        \n",
    "    -  Anticipated Issues:\n",
    "        1.  Numbers without Milwaukee area codes may be cell phone numbers or businesses with multiple locations, so we may not want to take those numbers out of the database.\n",
    "        2.  There may be errors when dealing with numbers less than 10 digits, as it may be difficult to determine area code\n",
    "        3.  Those that are unfamiliar with the chosen format may be unable to search the database using phone numbers\n",
    "        <br>\n",
    "        <br>\n",
    "5.  Web Addresses may be checked for validity and conformity (https?.\\..)\n",
    "    -  Benefits:\n",
    "        1.  All websites will be identified and searchable by standards\n",
    "        2.  The data will look clean and conformed to standards\n",
    "        3.  Websites will be clearly stated with little chance for misinterpretation\n",
    "        \n",
    "    -  Anticipated Issues:\n",
    "        1.  It may be unclear as to whether a website uses http or https if not specified\n",
    "        2.  If we exclude websites that do not conform to standards we have set, there may be a loss of significant information\n",
    "        3.  Those that are unfamiliar with the chosen format may not be able to search using websites\n",
    "        <br>\n",
    "        <br>\n",
    "\n",
    "## Conclusion\n",
    "\n",
    "In conclusion, we have investigated our sample and made a few improvements.  After creating our SQL database, we've performed a few queries to record data statistics and discover additional potential improvements to our dataset.  We have also discovered a few interesting bits of information about Milwaukee, such as the fact that there is one area with access for horse riders!  \n",
    "      "
   ]
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python [Root]",
   "language": "python",
   "name": "Python [Root]"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}