Sydney_osm_wrangling.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data wrangling submission"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The goal of this submission is to choose an area of the world in https://www.openstreetmap.org, assess the quality of data for validity, accuracy, completeness, consistency and uniformity, clean it, and import to MongoDB. \n",
    "\n",
    "A custom Sydney area was selected on and downloaded as OSM XML from [Mapzen](https://mapzen.com/) to perform the exercise. The file structure is described in the [relevant openstreetmap wiki](https://wiki.openstreetmap.org/wiki/OSM_XML). "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Auditing the data "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First, let's open the file and analyse how many different element types are in there. Parcing with ElementTree should be used since the dataset is quite large for processing in memory. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 269,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'bounds': 1,\n",
      " 'member': 25877,\n",
      " 'nd': 356990,\n",
      " 'node': 279383,\n",
      " 'osm': 1,\n",
      " 'relation': 2020,\n",
      " 'tag': 242420,\n",
      " 'way': 50169}\n"
     ]
    }
   ],
   "source": [
    "import xml.etree.cElementTree as ET\n",
    "import pprint\n",
    "\n",
    "filename='sydney.osm'\n",
    "\n",
    "tags = {}\n",
    "\n",
    "for event, elem in ET.iterparse(filename):  \n",
    "    if elem.tag in tags: \n",
    "        tags[elem.tag] += 1\n",
    "    else:                \n",
    "        tags[elem.tag] = 1\n",
    "\n",
    "pprint.pprint(tags)  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As expected, there are a lot of nodes and tags which we will have to analyse. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As the data will be eventually loaded in MongoDB, we need to ensure that there are no problematic characters that cannot be used within keys in MongoDB. In other words, for tags: "
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "tag k=\"key\" v=\"value\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "we should check key values for problematic characters. Also, let's see how many tag keys contain colons (such as addr:street).  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 270,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Problematic key: ' Payments Accepted ', value  Cash, Credit Card, Invoice\n",
      "Problematic key: ' old Fox Name ', value  007: License To Thrill\n",
      "Problematic key: ' Old Fox Name ', value  Star Dressing Room\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "{'lower': 202807, 'lower_colon': 37014, 'other': 2596, 'problemchars': 3}"
      ]
     },
     "execution_count": 270,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Regular expressions module\n",
    "import re\n",
    "\n",
    "# Expression to detect problematic characters, characters in lowercase without and with colon \n",
    "lower = re.compile(r'^([a-z]|_)*$')\n",
    "lower_colon = re.compile(r'^([a-z]|_)*:([a-z]|_)*$')\n",
    "problemchars = re.compile(r'[=\\+/&<>;\\'\"\\?%#$@\\,\\. \\t\\r\\n]')\n",
    "\n",
    "def key_type(element, keys):\n",
    "    if element.tag == \"tag\":\n",
    "        check_k = element.attrib['k']\n",
    "        if re.search(lower, check_k): \n",
    "            keys['lower'] += 1     \n",
    "        elif re.search(lower_colon, check_k): \n",
    "            keys['lower_colon'] += 1        \n",
    "        elif re.search(problemchars, check_k): \n",
    "            print \"Problematic key: '\", check_k, \"', value \", element.attrib['v']\n",
    "            keys['problemchars'] += 1        \n",
    "        else:\n",
    "            keys['other'] += 1\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",
    "        # _ is a throwaway value here \n",
    "        keys = key_type(element, keys)\n",
    "    return keys\n",
    "\n",
    "process_map(filename)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We will ignore the 'old fox name' tags because their values do not really represent old names of the places, and we will change the tag 'Payments Accepted' to the key 'payment:credit_cards' with value 'yes' as specified in the OSM Wiki. We will ignore the cash and invoice values because the ability to pay with a credit card is a key information in this case. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 271,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Create a function to process the problematic payments tag for future reference\n",
    "def process_bad_payment(t_key, t_val): \n",
    "    if (t_key.lower().find('payment') >= 0):\n",
    "        t_key = \"payment:credit_cards\"\n",
    "        t_val = \"yes\"\n",
    "    return t_key, t_val"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Street names and types"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As the data has been added by people, we should expect variations in how streets are called and abbreviated. Indeed, this was evident to me when I used OpenStreetView-based apps for navigation. An example of tags which should be checked and cleaned is: tag k=\"addr:street\" v=\"Missenden Str.\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We will do the following to analyse how the streets names are written and to clean the data: \n",
    "1. Parse through the file, searching for tags with attribute k having value \"addr:street\".\n",
    "2. Create a dictionary with all possible endings in the street names.\n",
    "3. Create a mapping to make the abbreviations consistent and update the data. \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 272,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2000澳洲: 1\n",
      "2026澳洲: 1\n",
      "?: 1\n",
      "Adventure: 1\n",
      "Androtis: 1\n",
      "Arcade: 1\n",
      "Avenue: 301\n",
      "Berith: 25\n",
      "Boulevard: 4\n",
      "Boulevarde: 4\n",
      "Broadway: 25\n",
      "Circuit: 9\n",
      "City: 1\n",
      "Crescent: 22\n",
      "Drive: 21\n",
      "Edward: 5\n",
      "Esplanade: 8\n",
      "Fitzroy: 1\n",
      "Gardens: 14\n",
      "Head: 1\n",
      "Highway: 60\n",
      "Jones: 1\n",
      "Lane: 61\n",
      "Leichhardt: 1\n",
      "Market: 3\n",
      "marrickville: 1\n",
      "North: 1\n",
      "Offramp: 1\n",
      "Parade: 92\n",
      "Place: 46\n",
      "Plaza: 1\n",
      "Point: 2\n",
      "Promanade: 1\n",
      "Road: 1069\n",
      "Shaw: 3\n",
      "South: 1\n",
      "Square: 2\n",
      "St: 12\n",
      "St.: 1\n",
      "street: 4\n",
      "Street: 3502\n",
      "Street): 1\n",
      "Sydney: 1\n",
      "Terrace: 2\n",
      "underpass: 1\n",
      "Wales: 1\n",
      "Way: 3\n",
      "West: 1\n",
      "Wolli: 74\n",
      "Wollit: 1\n",
      "None\n"
     ]
    }
   ],
   "source": [
    "# Using defaultdict to allow for default (zero) values \n",
    "from collections import defaultdict\n",
    "\n",
    "# Sequence of non-whitespace characters \\S+ optionally followed by period (to catch st./sqr./etc.), \n",
    "# which should appear in the string ending ($)\n",
    "street_type_re = re.compile(r'\\S+\\.?$', re.IGNORECASE)\n",
    "\n",
    "# For int, the default value is zero. A defaultdict will never raise a KeyError. \n",
    "\n",
    "street_types = defaultdict(int)\n",
    "\n",
    "def audit_street_type(street_types, street_name):\n",
    "    m = street_type_re.search(street_name)\n",
    "    if m:\n",
    "        street_type = m.group()\n",
    "        street_types[street_type] += 1\n",
    "\n",
    "def print_sorted_dict(d):\n",
    "    keys = d.keys()\n",
    "    # Sort the keys alphabetically independently from upper/lower case\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",
    "\n",
    "def is_street_name(elem):\n",
    "    return (elem.tag == \"tag\") and (elem.attrib['k'] == \"addr:street\")\n",
    "\n",
    "def audit(filename):\n",
    "    for event, elem in ET.iterparse(filename):\n",
    "        # If this is a tag with street name\n",
    "        if is_street_name(elem):\n",
    "            # Update the counter for the corresponding street type \n",
    "            audit_street_type(street_types, elem.attrib['v'])    \n",
    "    print_sorted_dict(street_types)    \n",
    "\n",
    "# Running the main function on our dataset\n",
    "s_types = audit(filename)\n",
    "pprint.pprint(s_types)  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This looks like a mess - some names figure zip codes, chinese symbols, and suburbs. For some results, transformation is already clear, whereas for other we should look at values to understand what is the content. Let's take a closer look by defining a list with expected street names ending and by modifying the audit_street_type function to include full values with unexpected endings. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 273,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{u'2000\\u6fb3\\u6d32': set([u'Playfair St & Argyle Street, The Rocks NSW 2000\\u6fb3\\u6d32']),\n",
      " u'2026\\u6fb3\\u6d32': set([u'70A Campbell Parade, Bondi Beach NSW 2026\\u6fb3\\u6d32']),\n",
      " '?': set(['?']),\n",
      " 'Adventure': set(['Victoria Adventure']),\n",
      " 'Androtis': set(['Androtis']),\n",
      " 'Arcade': set(['Tramway Arcade']),\n",
      " 'Berith': set(['Berith']),\n",
      " 'Boulevarde': set(['The Boulevarde']),\n",
      " 'City': set(['City']),\n",
      " 'Edward': set(['Edward']),\n",
      " 'Esplanade': set(['The Esplanade']),\n",
      " 'Fitzroy': set(['Fitzroy']),\n",
      " 'Head': set(['Henry Head']),\n",
      " 'Jones': set(['Jones']),\n",
      " 'Leichhardt': set(['Leichhardt']),\n",
      " 'Market': set(['Sydney Fish Market']),\n",
      " 'North': set(['Ocean Street North']),\n",
      " 'Offramp': set(['King Street Offramp']),\n",
      " 'Plaza': set(['Elizabeth Plaza']),\n",
      " 'Promanade': set(['The Promanade']),\n",
      " 'Shaw': set(['Shaw']),\n",
      " 'South': set(['Alfred Street South']),\n",
      " 'St': set(['2 Lackey St',\n",
      "            '360 Victoria St',\n",
      "            'Cnr / Playfair & Argyle St',\n",
      "            'Comer St',\n",
      "            'Henderson St',\n",
      "            'Kenwyn St',\n",
      "            'Lackey St',\n",
      "            'Liverpool St',\n",
      "            'Margaret St',\n",
      "            'Phillips St']),\n",
      " 'St.': set(['Phillip St.']),\n",
      " 'Street)': set(['Holt Street (enter via Gladstone Street)']),\n",
      " 'Sydney': set(['The Wharf, Cowper Wharf Road, Woolloomooloo, Sydney']),\n",
      " 'Wales': set(['New South Wales']),\n",
      " 'West': set(['Lyons Road West']),\n",
      " 'Wolli': set(['Wolli']),\n",
      " 'Wollit': set(['Wollit']),\n",
      " 'marrickville': set(['Addison road, nr East street, marrickville']),\n",
      " 'street': set(['Chalmers street',\n",
      "                'George street',\n",
      "                'Plowman street',\n",
      "                'george street']),\n",
      " 'underpass': set(['Pacific Highway underpass'])}\n"
     ]
    }
   ],
   "source": [
    "# List with expected names\n",
    "expected = [\"Avenue\", \"Boulevard\", \"Broadway\", \"Circuit\", \"Crescent\", \"Terrace\", \"Way\", \n",
    "            \"Drive\", \"Highway\", \"Lane\", \"Parade\", \"Place\", \"Road\", \"Street\", \"Square\", \n",
    "            \"Gardens\", \"Point\"]\n",
    "\n",
    "street_types = defaultdict(set)\n",
    "    \n",
    "# Let's change our function to addy full values for unexpected street names \n",
    "def audit_street_type(street_types, street_name):\n",
    "    # Use the regular expression defined previously\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",
    "# And rewrite the audit function so it returns street types\n",
    "def audit(filename):\n",
    "    for event, elem in ET.iterparse(filename):\n",
    "        # If this is a tag with street name\n",
    "        if is_street_name(elem):\n",
    "            # Update the counter for the corresponding street type \n",
    "            audit_street_type(street_types, elem.attrib['v'])    \n",
    "    return street_types   \n",
    "        \n",
    "# Running the main function on our dataset\n",
    "st_types = audit(filename)\n",
    "pprint.pprint(dict(st_types)) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Except for abbreviations which we could clearly map to correct naming, there are some peculiarities: \n",
    "1. There are clarifications in parenthesis which should be removed from the name. \n",
    "2. There are non-English and non-alphanumeric symbols which should be removed (except for '&' which is a valid symbol).  \n",
    "3. If there is one comma in the street name, we should only keep value before the comma (e.g. \"Playfair St & Argyle Street, The Rocks NSW 2000\" should be transformed into \"Playfair St & Argyle Street\"). \n",
    "4. If there are two commas (or more) in the street name, we should only keep value after the first and before the second comma (e.g. \"The Wharf, Cowper Wharf Road, Woolloomooloo, Sydney\" should be \"Cowper Wharf Road\". \n",
    "5. In a few cases, 'Street' shoud be added to the name - this includes the following values: Berit, Edward, Fitzroy, Jones, Shaw, Wolli. \n",
    "6. Finally, 'King Street Offramp' should be transformed to 'King Street', 'Pacific Highway underpass' should be 'Pacific Highway', and 'nr East Street' should be just 'East Street' (there is no North East Street in Marickville). \n",
    "\n",
    "Notably, some street names are valid while being unusual - this includes: \n",
    "- Tramway Arcade\n",
    "- The Boulevarde\n",
    "- The Esplanade\n",
    "- Ocean Street North\n",
    "- Elizabeth Plaza\n",
    "- The Promanade\n",
    "- Alfred Street South\n",
    "\n",
    "Now, let's update the expected list, address points 1-6, and create a function to transform abbreviations via mapping. We will delete values if mapping cannot be done. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 274,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "City => None\n",
      "Addison road, nr East street, marrickville => East Street\n",
      "Shaw => Shaw Street\n",
      "Tramway Arcade => Tramway Arcade\n",
      "Wollit => None\n",
      "Lyons Road West => None\n",
      "Phillip St. => Phillip Street\n",
      "Chalmers street => Chalmers Street\n",
      "Plowman street => Plowman Street\n",
      "George street => George Street\n",
      "george street => George Street\n",
      "The Wharf, Cowper Wharf Road, Woolloomooloo, Sydney => Cowper Wharf Road\n",
      "New South Wales => None\n",
      "The Promanade => The Promanade\n",
      "Henry Head => None\n",
      "Pacific Highway underpass => Pacific Highway\n",
      "Fitzroy => Fitzroy Street\n",
      "70A Campbell Parade, Bondi Beach NSW 2026澳洲 => 70A Campbell Parade\n",
      "Holt Street (enter via Gladstone Street) => Holt Street\n",
      "Victoria Adventure => None\n",
      "? => None\n",
      "Jones => Jones Street\n",
      "King Street Offramp => King Street\n",
      "Elizabeth Plaza => Elizabeth Plaza\n",
      "Liverpool St => Liverpool Street\n",
      "Henderson St => Henderson Street\n",
      "Phillips St => Phillips Street\n",
      "Comer St => Comer Street\n",
      "Lackey St => Lackey Street\n",
      "2 Lackey St => 2 Lackey Street\n",
      "Kenwyn St => Kenwyn Street\n",
      "Margaret St => Margaret Street\n",
      "Cnr / Playfair & Argyle St => Cnr Playfair & Argyle Street\n",
      "360 Victoria St => 360 Victoria Street\n",
      "Playfair St & Argyle Street, The Rocks NSW 2000澳洲 => Playfair St & Argyle Street\n",
      "Ocean Street North => Ocean Street North\n",
      "Alfred Street South => Alfred Street South\n",
      "Wolli => Wolli Street\n",
      "Leichhardt => None\n",
      "The Esplanade => The Esplanade\n",
      "The Boulevarde => The Boulevarde\n",
      "Androtis => None\n",
      "Edward => Edward Street\n",
      "Berith => None\n",
      "Sydney Fish Market => None\n"
     ]
    }
   ],
   "source": [
    "# Updated list with expected names\n",
    "expected = [\"Avenue\", \"Boulevard\", \"Broadway\", \"Circuit\", \"Crescent\", \"Terrace\", \"Way\", \n",
    "            \"Drive\", \"Highway\", \"Lane\", \"Parade\", \"Place\", \"Road\", \"Street\", \"Square\", \n",
    "            \"Gardens\", \"Point\", \n",
    "            \"Arcade\", \"Boulevarde\", \"Esplanade\", \"North\", \"Plaza\", \"Promanade\", \"South\"\n",
    "           ]\n",
    "\n",
    "# Dictionary with mapping \n",
    "mapping = { \"street\": \"Street\", \n",
    "            \"St.\": \"Street\", \n",
    "            \"St\": \"Street\"\n",
    "            }\n",
    "\n",
    "# P.5: For these names, 'Street' should be added\n",
    "add_street = [\"Berit\", \"Edward\", \"Fitzroy\", \"Jones\", \"Shaw\", \"Wolli\"]\n",
    "\n",
    "def name_clean(name): \n",
    "    # Capitalise the first letter \n",
    "    name = name[:1].upper() + name[1:]\n",
    "    # P.1: If there is parenthesis - return the part before parenthesis\n",
    "    name = re.sub(r'\\s\\([^)]*\\)', '', name)\n",
    "    # P.2: Remove non-English and non-alphanumeric excluding '&' symbol, commas, and spaces (\\s)\n",
    "    name = re.sub('[^0-9a-zA-Z&,\\s]+', '', name)\n",
    "    # P.3: If there is one comma, return symbols before the comma\n",
    "    name_split = name.split(',')\n",
    "    if len(name_split) == 2: name = name_split[0].strip()\n",
    "    # P.4: If there are two commas or more, return value after first and before second\n",
    "    if len(name_split) > 2: name = name_split[1].strip()\n",
    "    # Removing all excess whitespaces \n",
    "    name = ' '.join(name.split())\n",
    "    return name\n",
    "\n",
    "def name_corr(name):\n",
    "    # P.6: Handling specific cases\n",
    "    if name == \"King Street Offramp\": name = \"King Street\"\n",
    "    if name == \"Pacific Highway underpass\": name = \"Pacific Highway\"\n",
    "    if name == \"nr East street\": name = \"East Street\"\n",
    "    # P.5: Adding 'Street' where required \n",
    "    if name in add_street: \n",
    "        return name + \" Street\"\n",
    "    else: \n",
    "        return name\n",
    "\n",
    "# After performing name_clean and name_corr, we should check if street name is in expected\n",
    "# If not - map; we will return None if mapping cannot be done \n",
    "def name_map(name, mapping):\n",
    "    m = street_type_re.search(name)\n",
    "    if m:\n",
    "        street_type = m.group()     \n",
    "        if street_type in expected: \n",
    "            return name\n",
    "        else: \n",
    "            if street_type in mapping:\n",
    "                name = name.replace(street_type, mapping[street_type])          \n",
    "            else: \n",
    "                name = None\n",
    "    else: \n",
    "        name = None        \n",
    "    return name \n",
    "\n",
    "# Combine all the functions\n",
    "def process_st_name(name):\n",
    "    name = name_clean(name)\n",
    "    name = name_corr(name)\n",
    "    name = name_map(name, mapping)  \n",
    "    return name \n",
    "\n",
    "# Interating through s_types which we creaated previously to check the outcome\n",
    "for st_type, ways in st_types.iteritems():\n",
    "    for name in ways:\n",
    "        print name, \"=>\", process_st_name(name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Looks good! We can use this function for further processing. Although it was written based on analysis of one specific area, it can be used for cleaning other areas as well. However, we would have to analyse all the peculiarities for a new area which are not fixed by the points considered above. People are generally creative - so, no one knows what to expect. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Also, let's use %timeit function to check how long would it take to run process_st_name for each street name. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 275,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "100000 loops, best of 3: 14 µs per loop\n"
     ]
    }
   ],
   "source": [
    "%timeit process_st_name(name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Maximum speed values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Another point which I noticed was that maximum speed values were incorrect several times when I tried using OpenStreetView-based apps. As per wiki, maximum speed values are specified in the [maxspeed tag](http://wiki.openstreetmap.org/wiki/Key:maxspeed). \n",
    "Let's examine possible values in our dataset:   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 276,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Non-numeric value:  40 mph\n",
      "Non-numeric value:  50 mph\n",
      "Non-numeric value:  30 mph\n",
      "Non-numeric value:  sign\n",
      "Non-numeric value:  sign\n",
      "Non-numeric value:  10;10 mph\n",
      "Non-numeric value:  10 mph\n",
      "Too low/high:  0\n"
     ]
    }
   ],
   "source": [
    "# Create an empty list for further cleaning\n",
    "speeds_to_clean = []\n",
    "\n",
    "def is_speed(elem):\n",
    "    return (elem.tag == \"tag\") and (elem.attrib['k'] == \"maxspeed\")\n",
    "\n",
    "# Checking the values. Speeds between 5-120 are valid, and 'signals' is a valid value too\n",
    "def audit_speeds(filename):\n",
    "    for event, elem in ET.iterparse(filename):\n",
    "        if is_speed(elem):\n",
    "            try: \n",
    "                speedval = int(elem.attrib['v'])\n",
    "                # Print value if it is below 5 or above 120 kmph\n",
    "                if (int(speedval) < 5) or (int(speedval) > 120): \n",
    "                    print \"Too low/high: \", speedval \n",
    "                    speeds_to_clean.append(speedval)\n",
    "            except ValueError: \n",
    "                # Non-numeric value\n",
    "                if elem.attrib['v'] != 'signals': \n",
    "                    print \"Non-numeric value: \", elem.attrib['v']\n",
    "                    speeds_to_clean.append(elem.attrib['v'])\n",
    "\n",
    "# Analyse the dataset\n",
    "speed_analysis = audit_speeds(filename)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are just a few minor issues: \n",
    "1. Mph is not correct because speed in Australia is measured in kmph, and the speed values above are really in kmph rather than mph. So, we should just delete 'mph' from the values. \n",
    "2. 'Sign' should be replaced to 'signals' to be consistent with other records. \n",
    "3. '10;10' should be changed to 10. \n",
    "4. 0 should be changed to None. \n",
    "\n",
    "Let's fix this: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 295,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "40 mph => 40\n",
      "50 mph => 50\n",
      "30 mph => 30\n",
      "sign => signals\n",
      "sign => signals\n",
      "10;10 mph => 10\n",
      "10 mph => 10\n",
      "0 => None\n"
     ]
    }
   ],
   "source": [
    "# Function to correct speeds\n",
    "def process_speed(speed): \n",
    "    try: \n",
    "        # P.4 - zeros should be changed to None \n",
    "        speed = int(speed)\n",
    "        if speed == 0: speed = None\n",
    "    except ValueError: \n",
    "        # P.1 - remove 'mph'\n",
    "        speed = speed.replace(' mph','')\n",
    "        # Try to convert to int again \n",
    "        try: \n",
    "            speed = int(speed)\n",
    "        except ValueError: \n",
    "            # Handling P.2 & P.3\n",
    "            if (speed == 'sign') or (speed == 'sign'): \n",
    "                speed = 'signals'\n",
    "            elif speed == '10;10': \n",
    "                speed = 10\n",
    "            else: \n",
    "                speed = None\n",
    "    return speed\n",
    "\n",
    "# Try on problematic values \n",
    "for speed in speeds_to_clean: \n",
    "    print speed, \"=>\", process_speed(speed)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Similarly, let's see how fast this function is: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 278,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The slowest run took 8.73 times longer than the fastest. This could mean that an intermediate result is being cached.\n",
      "1000000 loops, best of 3: 329 ns per loop\n"
     ]
    }
   ],
   "source": [
    "%timeit process_speed(speed)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Cycleways"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "One more point I would like to look at is cycleway tags. As a cyclist who uses Bike Maps and similar applications, I had been consistently disappointed in the quality of routes which I was routed to by these apps. For example, I noticed that some dedicated or joint cycleway had never popped up - instead, apps showed routes through usual roads making my trips less pleasant. \n",
    "\n",
    "By reading OSM wiki, I have figured out that the relevant tags are [\"cycleway\"](http://wiki.openstreetmap.org/wiki/Key:cycleway) and [\"bicycle\"](http://wiki.openstreetmap.org/wiki/Bicycle). Wiki also includes recommended tag values and statistics on their use. This information would be handy to create lists of expected values. Now, let's analyse the data.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 279,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unexpected cycleway tag values:\n",
      "{'?dismount': 1,\n",
      " 'designated': 3,\n",
      " 'designated; track': 2,\n",
      " 'dismount': 1,\n",
      " 'doorzone': 2,\n",
      " 'parallel': 7}\n",
      "Unexpected bicycle tag values:\n",
      "{'designated;yes': 1,\n",
      " 'designated||': 9,\n",
      " 'private': 3,\n",
      " 'proposed': 5,\n",
      " 'stupid': 13,\n",
      " 'survey required': 14,\n",
      " 'unsurveyed': 2,\n",
      " 'y': 1,\n",
      " 'yes;dismount': 1,\n",
      " '|designated|||': 1,\n",
      " '|designated||||': 1,\n",
      " '||designated|||': 1}\n"
     ]
    }
   ],
   "source": [
    "# Possible tags are cycleway, cycleway:right, cycleway:left\n",
    "cycleway_tags = ['cycleway', 'cycleway:left', 'cycleway:right']\n",
    "\n",
    "# Tags and expected values for bicycle and cycleway tags as specified on OSM Wiki\n",
    "cycleway_expected = ['lane', 'opposite_lane', 'opposite', 'shared_lane', 'share_busway', 'shared', \n",
    "                     'track', 'opposite_track', 'asl', 'shoulder', 'separate', 'no', 'yes', \n",
    "                     'right', 'crossing', 'segregated', 'none', 'sidepath', 'both', 'unmarked_lane', \n",
    "                     'left'] \n",
    "bicycle_expected = ['yes', 'no', 'designated', 'use_sidepath', 'permissive','destination', 'dismount']\n",
    "\n",
    "# A dictionary to check unexpected values and their frequency\n",
    "cycleway_unexp = defaultdict(int)\n",
    "bicycle_unexp = defaultdict(int)\n",
    "\n",
    "def is_cycleway(elem):\n",
    "    return (elem.tag == \"tag\") and (elem.attrib['k'] in cycleway_tags)\n",
    "\n",
    "def is_bicycle(elem): \n",
    "    return (elem.tag == \"tag\") and (elem.attrib['k'] == \"bicycle\")\n",
    "\n",
    "# Checking the values. Speeds between 5-120 are valid, and 'signals' is a valid value too\n",
    "def audit_cycleways(filename):\n",
    "    for event, elem in ET.iterparse(filename):\n",
    "        if is_cycleway(elem): \n",
    "            if elem.attrib['v'] not in cycleway_expected:\n",
    "                cycleway_unexp[elem.attrib['v']] += 1\n",
    "        if is_bicycle(elem): \n",
    "            if elem.attrib['v'] not in bicycle_expected:\n",
    "                bicycle_unexp[elem.attrib['v']] += 1\n",
    "                \n",
    "# Analyse the dataset\n",
    "audit_cycleways(filename)\n",
    "print \"Unexpected cycleway tag values:\"\n",
    "pprint.pprint(dict(cycleway_unexp))\n",
    "print \"Unexpected bicycle tag values:\"\n",
    "pprint.pprint(dict(bicycle_unexp))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In fact, both tags can have user-defined values as wiki only recommends values rather than prescribes them. However, some of these tags definitely require cleaning. Let's clean the values using common sense. In fact, one universal function can be created for both tag values. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 280,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Cycleway tag values\n",
      "?dismount => dismount\n",
      "doorzone => doorzone\n",
      "designated => designated\n",
      "dismount => dismount\n",
      "designated; track => designated\n",
      "parallel => parallel\n",
      "\n",
      "Bicycle tag values\n",
      "survey required => surveyrequired\n",
      "proposed => proposed\n",
      "yes;dismount => yes\n",
      "||designated||| => designated\n",
      "private => private\n",
      "unsurveyed => unsurveyed\n",
      "stupid => yes\n",
      "designated;yes => designated\n",
      "y => yes\n",
      "designated|| => designated\n",
      "|designated||| => designated\n",
      "|designated|||| => designated\n"
     ]
    }
   ],
   "source": [
    "# Create a joint cycleways / bicycle tags list for future reference \n",
    "bicycle_way_tags = ['cycleway', 'cycleway:left', 'cycleway:right', 'bicycle']\n",
    "\n",
    "def process_bicycle(name): \n",
    "    # If there is semicolon - return the part before it\n",
    "    name = name.split(\";\")[0]\n",
    "    # Remove non-English and non-alphanumeric\n",
    "    name = re.sub('[^0-9a-zA-Z]+', '', name)\n",
    "    # Other improvements \n",
    "    if name == 'y': name = 'yes'\n",
    "    if name == 'stupid': name = 'yes'\n",
    "    return name \n",
    "\n",
    "print \"Cycleway tag values\"\n",
    "for element in cycleway_unexp:\n",
    "    print element, \"=>\", process_bicycle(element)\n",
    "\n",
    "print \"\\nBicycle tag values\"\n",
    "for element in bicycle_unexp:\n",
    "    print element, \"=>\", process_bicycle(element)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Use %timeit again to check the speed: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 281,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The slowest run took 4.42 times longer than the fastest. This could mean that an intermediate result is being cached.\n",
      "100000 loops, best of 3: 2.97 µs per loop\n"
     ]
    }
   ],
   "source": [
    "%timeit process_bicycle('name')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "All right, let's move on. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Preparation for MongoDB"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In order to import data to MongoDB, we will need to transform it to csv, tsv, or json file. Let's create json as it is the most appropriate default import format. \n",
    "\n",
    "We will parse through the file, returning dictionaries for 'nodes' and 'ways' using the following logic:\n",
    "\n",
    "### If the element top level tag is \"node\"\n",
    "The dictionary returned should have the format {\"type\" : \"node\", \"node\": .., \"node_tags\": ...}. The \"node\" field should hold a dictionary of the following top level node attributes: id, user, uid, version, lat, lon, timestamp, changeset. All other attributes will be ignored. \n",
    "\n",
    "The \"node_tags\" field should hold a list of dictionaries, one per secondary tag. Secondary tags are child tags of node which have the tag name/type: \"tag\". Each dictionary should have the following fields from the secondary tag attributes:\n",
    "- id: the top level node id attribute value\n",
    "- key: the full tag \"k\" attribute value if no colon is present or the characters after the colon if one is.\n",
    "- value: the tag \"v\" attribute value\n",
    "- type: either the characters before the colon in the tag \"k\" value or \"regular\" if a colon is not present.\n",
    "\n",
    "Additionally,\n",
    "- if a node has no secondary tags then the \"node_tags\" field should just contain an empty list.\n",
    "- if the tag \"k\" value contains problematic characters, the tag should be ignored\n",
    "- if the tag \"k\" value contains a \":\" the characters before the \":\" should be set as the tag type\n",
    "  and characters after the \":\" should be set as the tag key\n",
    "- if there are additional \":\" in the \"k\" value they and they should be ignored and kept as part of\n",
    "  the tag key. For example:"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "tag k=\"addr:street:name\" v=\"Lincoln\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "will be turned into"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "{'id': 12345, 'key': 'street:name', 'value': 'Lincoln', 'type': 'addr'}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### If the element top level tag is \"way\":\n",
    "The dictionary should have the format {\"type\" : \"way\", \"way\": ..., \"way_tags\": ..., \"way_nodes\": ...}. The \"way\" field should hold a dictionary of the following top level way attributes: id, user, uid, version, timestamp, changeset. \n",
    "\n",
    "All other attributes will be ignored\n",
    "\n",
    "The \"way_tags\" field should hold a list of dictionaries, following the exact same rules as for \"node_tags\".\n",
    "\n",
    "Additionally, the dictionary should have a field \"way_nodes\". \"way_nodes\" should hold a list of\n",
    "dictionaries, one for each nd child tag.  Each dictionary should have the fields:\n",
    "- id: the top level element (way) id\n",
    "- node_id: the ref attribute value of the nd tag\n",
    "- position: the index starting at 0 of the nd tag i.e. what order the nd tag appears within the way element"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### The approach to processing the osm file \n",
    "\n",
    "We will define a function shape_element that processes each element from the OSM XML. Within this function, all the above-stated rules will be applied."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 282,
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# Import libraries which will be necessary to perform transformations \n",
    "import string \n",
    "from datetime import datetime\n",
    "\n",
    "# Fields which will be processed\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",
    "# Function to process tags\n",
    "def process_tag(tag, id_input): \n",
    "    tag_dict = {}\n",
    "\n",
    "    # Working with tags \n",
    "    tag_val = tag.attrib['v']\n",
    "    tag_key = tag.attrib['k']\n",
    "    \n",
    "    # Correct street names \n",
    "    if tag_key == \"addr:street\": \n",
    "        tag_val = process_st_name(tag_val)   \n",
    "    # Correct maximum speed values \n",
    "    if tag_key == \"maxspeed\": \n",
    "        tag_val = process_speed(tag_val)\n",
    "    # Correct bicycle-related tags\n",
    "    if (tag_key in bicycle_way_tags): \n",
    "        tag_val = process_bicycle(tag_val)  \n",
    " \n",
    "    # Filling tag dictionary \n",
    "    tag_dict['id'] = id_input\n",
    "    tag_dict['value'] = tag_val  \n",
    "    \n",
    "    # If there are no problem chars \n",
    "    if not re.search(problemchars, tag_key): \n",
    "        key_split = tag_key.split(':',1)   # to split keys    \n",
    "        if key_split[0] == tag_key: \n",
    "            tag_dict['key'] = tag_key\n",
    "            tag_dict['type'] = 'regular' \n",
    "        else: \n",
    "            tag_dict['key'] = key_split[1] \n",
    "            tag_dict['type'] = key_split[0]   \n",
    "    # If there are problem chars - check if this is a payments tag and process accordingly\n",
    "    else: \n",
    "        tag_key_corr, tag_val_corr = process_bad_payment(tag_key, tag_val)\n",
    "        if tag_key_corr != tag_key: \n",
    "            tag_dict['key'] = tag_key_corr\n",
    "            tag_dict['value'] = tag_val_corr  \n",
    "            tag_dict['type'] = 'regular'   \n",
    "    return tag_dict\n",
    "\n",
    "# Function to clean and shape node or way XML element to Python dict\n",
    "def shape_element(element, node_attr_fields=NODE_FIELDS, way_attr_fields=WAY_FIELDS,\n",
    "                  problem_chars=problemchars, default_tag_type='regular'):\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",
    "    # Filling values for 'node'\n",
    "    if element.tag == 'node':\n",
    "        node_id = element.attrib['id']\n",
    "        for field_e in NODE_FIELDS: \n",
    "            # Fixing timestamp\n",
    "            if field_e == 'timestamp':\n",
    "                node_attribs[field_e] = datetime.strptime(element.attrib[field_e], '%Y-%m-%dT%H:%M:%SZ')\n",
    "            else:\n",
    "                node_attribs[field_e] = element.attrib[field_e]\n",
    "            \n",
    "        for tag in element.iter(\"tag\"):\n",
    "            tag_dict = process_tag(tag, node_id)\n",
    "            tags.append(tag_dict)\n",
    "\n",
    "    # Filling values for 'way'\n",
    "    if element.tag == 'way':\n",
    "        way_id = element.attrib['id']\n",
    "        for field_e in WAY_FIELDS: \n",
    "            if field_e == 'timestamp':\n",
    "                way_attribs[field_e] = datetime.strptime(element.attrib[field_e], '%Y-%m-%dT%H:%M:%SZ')\n",
    "            else:\n",
    "                way_attribs[field_e] = element.attrib[field_e]\n",
    "            \n",
    "        count = 0 \n",
    "        for nd in element.iter(\"nd\"): \n",
    "            way_nodes_dict = {}\n",
    "            way_nodes_dict['id'] = way_id\n",
    "            way_nodes_dict['node_id'] = nd.attrib['ref']\n",
    "            way_nodes_dict['position'] = count\n",
    "            way_nodes.append(way_nodes_dict) \n",
    "            count+=1\n",
    "        for tag in element.iter(\"tag\"):\n",
    "            tag_dict = process_tag(tag, way_id)\n",
    "            tags.append(tag_dict)\n",
    "    \n",
    "    if element.tag == 'node':\n",
    "        return {'type' : 'node', 'node': node_attribs, 'node_tags': tags}\n",
    "    elif element.tag == 'way':\n",
    "        return {'type' : 'way', 'way': way_attribs, 'way_nodes': way_nodes, 'way_tags': tags}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we will parse the XML, shape the elements, and write to a json file. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 283,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Execution time: 30.745000124 seconds\n"
     ]
    }
   ],
   "source": [
    "import json  \n",
    "# BSON will be used for compatibility \n",
    "from bson import json_util\n",
    "\n",
    "# Iteratively process each XML element \n",
    "def process_map(file_in, pretty = False):  \n",
    "    file_out = \"{0}.json\".format(file_in)\n",
    "    with open(file_out, \"wb\") as fo:\n",
    "        for _, element in ET.iterparse(file_in):\n",
    "            el = shape_element(element)\n",
    "            if el:\n",
    "                fo.write(json.dumps(el, default=json_util.default) + \"\\n\")\n",
    "\n",
    "# Using time function to check the execution time \n",
    "import time\n",
    "start_time = time.time()\n",
    "                \n",
    "# Now process elements\n",
    "process_map(filename)  \n",
    "\n",
    "# Printing the execution time \n",
    "print(\"Execution time: %s seconds\" % (time.time() - start_time))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Overview of the data "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's get an overview on the source OSM XML file and on the generated JSON. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 284,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The source XML size: 68.83 MB\n",
      "Produced JSON file: 112.32 MB\n"
     ]
    }
   ],
   "source": [
    "import os \n",
    "# Convert from bytes to megabytes and show two decimal points only \n",
    "print 'The source XML size: {:0.2f} MB'.format(os.path.getsize(filename)/1.0e6) \n",
    "print 'Produced JSON file: {:0.2f} MB'.format(os.path.getsize(filename + \".json\")/1.0e6)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Importing to with MongoDB"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We will use the produced JSON file to perform import. I have installed a local instance of MongoDB, and added the MongoDB folder in the PATH Windows environment. By examining [MongoDB python client manual](https://docs.mongodb.com/getting-started/python/client/) and [this Stackoverflow post](https://stackoverflow.com/questions/4760215/running-shell-command-from-python-and-capturing-the-output) explaining the subprocess library, we can write some more code to import files from Python."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 285,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Dropping collection: sydney\n",
      "Executing:  mongoimport -h 127.0.0.1:27017 --db openstreetmap --collection sydney --file D:\\Data_science\\Nanodegree\\4.Data_wrangling\\Submission\\sydney.osm.json\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 285,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import signal  \n",
    "import subprocess\n",
    "from pymongo import MongoClient\n",
    "\n",
    "db_name = 'openstreetmap'\n",
    "\n",
    "# Connect to Mongo DB\n",
    "client = MongoClient('localhost:27017')  \n",
    "# Database 'openstreetmap' will be created if it does not exist\n",
    "db = client[db_name]  \n",
    "\n",
    "# Preparing for mongoimport\n",
    "collection = filename[:filename.find('.')]  # name before the file extention\n",
    "\n",
    "workdir = \"D:\\Data_science\\Nanodegree\\\\4.Data_wrangling\\Submission\\\\\"\n",
    "json_file = filename + '.json'\n",
    "\n",
    "# Command for importing \n",
    "mongoimport_cmd = 'mongoimport -h 127.0.0.1:27017 ' + \\\n",
    "                  '--db ' + db_name + \\\n",
    "                  ' --collection ' + collection + \\\n",
    "                  ' --file ' + workdir + json_file\n",
    "\n",
    "if collection in db.collection_names():  \n",
    "    print 'Dropping collection: ' + collection\n",
    "    db[collection].drop()\n",
    "\n",
    "print \"Executing: \", mongoimport_cmd\n",
    "subprocess.call(mongoimport_cmd.split())  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "### Examining the data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "The mongoimport command was executed successfully - now we can connect to the database and perform a statistical overview of the dataset. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 286,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "sydney_db = db[collection]  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First, let's check the number of created documents: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 287,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of documents: 329552\n"
     ]
    }
   ],
   "source": [
    "docnum = sydney_db.find().count()  \n",
    "print \"Number of documents:\", docnum"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, let's calculate the number of unique users. First, let's do this separately for nodes and ways with the help of aggregation framework: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 288,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "node : [{u'count': 1036, u'_id': u'uniquenodeusers'}]\n",
      "way : [{u'count': 724, u'_id': u'uniquewayusers'}]\n"
     ]
    }
   ],
   "source": [
    "# Create a function to return results of the cursor execution\n",
    "def agg_pipeline(db, pipeline):\n",
    "    return [doc for doc in sydney_db.aggregate(pipeline)]\n",
    "\n",
    "# Create a pipeline for nodes and ways \n",
    "for elem_type in ['node', 'way']: \n",
    "    pipeline = [{\"$match\" : {'type': elem_type}}, \n",
    "                { \"$group\" : { \"_id\" : \"$\"+elem_type+\".uid\",\"count\" : { \"$sum\" : 1}}}, \n",
    "                { \"$group\" : { \"_id\" : \"unique\"+elem_type+\"users\", \"count\" : { \"$sum\" : 1}}}\n",
    "                ] \n",
    "    results = agg_pipeline(sydney_db, pipeline)\n",
    "    print elem_type, \":\", results "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Actually, if we stored the uid on the 'parent' level of each document, we would be able to check unique users in both using just one db request. Since we have user ids stored within 'way' and 'node' elements, we will have to calculate this by two stages: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 289,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The number of unique users: 1156\n"
     ]
    }
   ],
   "source": [
    "# Create a list because we need unique values only\n",
    "unique_users = set()\n",
    "\n",
    "for elem_type in ['node', 'way']: \n",
    "    # Finding all the records of each type \n",
    "    results = sydney_db.find({\"type\":elem_type})\n",
    "    for elem in results:\n",
    "        # Adding unique uid's to the set\n",
    "        unique_users.add(elem[elem_type]['uid'])   \n",
    "    \n",
    "print \"The number of unique users:\", len(unique_users)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As a cyclist, I am curious to see how many bicycle pumps and bicycle parkings are installed in the area. In the current structure, values 'bicycle_pump' and 'bicycle_parking' could be found in node_tags - value. Let's see: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 290,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Bicycle pumps: 2\n",
      "Bicycle parking: 361\n"
     ]
    }
   ],
   "source": [
    "pipeline = [{\"$match\": {\"type\": \"node\", \"node_tags.value\": \"bicycle_pump\"}}, \n",
    "            {\"$group\" : { \"_id\" : \"Bicycle pumps\",\"count\" : { \"$sum\" : 1}}}\n",
    "           ]\n",
    "\n",
    "print \"Bicycle pumps:\", agg_pipeline(sydney_db, pipeline)[0]['count']\n",
    "\n",
    "pipeline = [{\"$match\": {\"type\": \"node\", \"node_tags.value\": \"bicycle_parking\"}}, \n",
    "            {\"$group\" : { \"_id\" : \"Bicycle parkings\",\"count\" : { \"$sum\" : 1}}}\n",
    "           ]\n",
    "\n",
    "print \"Bicycle parking:\", agg_pipeline(sydney_db, pipeline)[0]['count']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Well, there are not many pumps, but at least enough parking. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 291,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{u'_id': ObjectId('5935340956d783ab5dc244fc'),\n",
      " u'node': {u'changeset': u'37450437',\n",
      "           u'id': u'4027062082',\n",
      "           u'lat': u'-33.8833574',\n",
      "           u'lon': u'151.206954',\n",
      "           u'timestamp': datetime.datetime(2016, 2, 26, 1, 29, 55),\n",
      "           u'uid': u'39288',\n",
      "           u'user': u'rolandmwagner',\n",
      "           u'version': u'1'},\n",
      " u'node_tags': [],\n",
      " u'type': u'node'}\n",
      "{u'_id': ObjectId('5935340456d783ab5dc06591'),\n",
      " u'node': {u'changeset': u'24780098',\n",
      "           u'id': u'1833957343',\n",
      "           u'lat': u'-33.9530368',\n",
      "           u'lon': u'151.2417602',\n",
      "           u'timestamp': datetime.datetime(2014, 8, 16, 5, 4, 40),\n",
      "           u'uid': u'348784',\n",
      "           u'user': u'mjog',\n",
      "           u'version': u'3'},\n",
      " u'node_tags': [{u'id': u'1833957343',\n",
      "                 u'key': u'highway',\n",
      "                 u'type': u'regular',\n",
      "                 u'value': u'traffic_signals'}],\n",
      " u'type': u'node'}\n",
      "{u'_id': ObjectId('5935340956d783ab5dc2449c'),\n",
      " u'node': {u'changeset': u'37449596',\n",
      "           u'id': u'4027001746',\n",
      "           u'lat': u'-33.882998',\n",
      "           u'lon': u'151.2069506',\n",
      "           u'timestamp': datetime.datetime(2016, 2, 26, 0, 3, 18),\n",
      "           u'uid': u'39288',\n",
      "           u'user': u'rolandmwagner',\n",
      "           u'version': u'1'},\n",
      " u'node_tags': [],\n",
      " u'type': u'node'}\n"
     ]
    }
   ],
   "source": [
    "# Three most referenced nodes \n",
    "pipeline = [{\"$match\": {\"type\": \"way\"}}, \n",
    "            {\"$unwind\": \"$way_nodes\"},\n",
    "            {\"$group\" : { \"_id\" : \"$way_nodes.node_id\",\"count\" : { \"$sum\" : 1}}}, \n",
    "            {\"$sort\": {\"count\": -1}},\n",
    "            {\"$limit\": 3}\n",
    "           ]\n",
    "\n",
    "# Getting 3 most referenced nodes \n",
    "ref_nodes = agg_pipeline(sydney_db, pipeline)\n",
    "# Printing every node\n",
    "for node in ref_nodes: \n",
    "    pprint.pprint(sydney_db.find({'node.id': node['_id']})[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The results are somewhat expected - coordinates of the first and the third most referenced node are related to the Central train station; the second most popular is located in Eastern suburbs near Maroubra."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Other ideas about the dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Since the OpenStreetMaps is a collaborative project which relies on everyone's updates, it is imperative that the values are reviewed and updated periodically. Since we have timestamps of last updates in way and node attributes, we can analyse when were the records last updated. Let's sort and select 15 oldest records.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 292,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Id: 5935340b56d783ab5dc2f500 Age in days: 3934.51296771\n",
      "Id: 5935340b56d783ab5dc2f50b Age in days: 3934.38554873\n",
      "Id: 5935340b56d783ab5dc2f51b Age in days: 3929.48387049\n",
      "Id: 5935340b56d783ab5dc2f51f Age in days: 3929.47692604\n",
      "Id: 5935340b56d783ab5dc2f52d Age in days: 3928.57053715\n",
      "Id: 5935340b56d783ab5dc2f52f Age in days: 3928.52417141\n",
      "Id: 5935340b56d783ab5dc2f531 Age in days: 3928.52413669\n",
      "Id: 5935340b56d783ab5dc2f635 Age in days: 3882.783639\n",
      "Id: 5935340b56d783ab5dc2f63b Age in days: 3882.78338437\n",
      "Id: 5935340b56d783ab5dc2f648 Age in days: 3882.7546691\n",
      "Id: 5935340b56d783ab5dc2f652 Age in days: 3882.72943762\n",
      "Id: 5935340b56d783ab5dc2f657 Age in days: 3882.668639\n",
      "Id: 5935340b56d783ab5dc2f676 Age in days: 3880.5021691\n",
      "Id: 5935340b56d783ab5dc2f677 Age in days: 3880.49839595\n",
      "Id: 5935340b56d783ab5dc2f67d Age in days: 3880.43631262\n"
     ]
    }
   ],
   "source": [
    "# Pipeline to calculate ages. Difference is first calculated in milliseconds. \n",
    "# We will recalculate it to days by dividing on (1000(ms)*60(s)*60(m)*24(h))\n",
    "pipeline = [{\"$match\": {\"type\": \"way\"}},\n",
    "            {'$project': {'DiffMilliSec': {'$subtract': [datetime.now(), '$way.timestamp']}}}, \n",
    "            {'$project': {'_id': 1, 'AgeDays': {'$divide': ['$DiffMilliSec', 1000*60*60*24]}}}, \n",
    "            {\"$sort\": {\"AgeDays\": -1}},\n",
    "            {\"$limit\": 15}\n",
    "           ]\n",
    "\n",
    "results = agg_pipeline(sydney_db, pipeline)\n",
    "for result in results: \n",
    "    print \"Id:\", result['_id'], \"Age in days:\", result['AgeDays']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Some values were last updated more than 10 years ago - it would be valuable revisiting these items and checking if anything has changed. It would also be interesting to check the overall distribution of the age. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 293,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Populating the interactive namespace from numpy and matplotlib\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "<matplotlib.axes._subplots.AxesSubplot at 0x80534d30>"
      ]
     },
     "execution_count": 293,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAXsAAAD3CAYAAAD8O/QcAAAABHNCSVQICAgIfAhkiAAAAAlwSFlz\nAAALEgAACxIB0t1+/AAAFlNJREFUeJzt3X+MHOd93/E3yZN4ZHEkLsDSbALXQpHmCyIAHZcC7USi\nRcSyFapQ2RhRaghW6AilaEEAJVSIZZkU7BRUZQsWi1JB6eAUWZRZo4Jly64J0JIB2xJ5TcKGtgAK\nUb8y3QQxEgi4CPxx9oWUSF7/mDl3eTru7u3tL968X4CBnWdmdj7ziP7uc8/OziyZnp5GkrS4Le13\nAElS91nsJakCLPaSVAEWe0mqAIu9JFXAUL8DzGViYrLtS4RGR1dy6tRUJ+N0xKDmArO1a1CzDWou\nMFs75pOrVhtZcqV1i25kPzS0rN8R5jSoucBs7RrUbIOaC8zWjk7lWnTFXpL0ThZ7SaoAi70kVYDF\nXpIqwGIvSRVgsZekCmjpOvuIWAMcBz4MXACeBqaBV4F7M/NSRGwHdpTr92TmoYhYARwE1gCTwLbM\nnOj4WUiSGmo6so+Ia4A/Bf6pbNoL7M7MTcASYGtErAV2AjcAtwCPRsRy4B7gRLntM8Duzp+CJKmZ\nVqZxvgh8CfiHcnkD8FL5+jBwM7ARGM/M85l5BjgJrAduBL4za1tJUo81nMaJiE8AE5n5QkQ8VDYv\nycyZ2xlMAquBVcCZul3nap9pa2p0dOWCfjVWq420vW+7bnvgW5ctf/vxre/Yph+5WmW29gxqtkHN\nBWZrRydyNZuzvwuYjoibgd+gmIpZU7d+BDgNnC1fN2qfaWtqIfenqNVGmJiYbHv/TpmdYVByzcVs\n7RnUbIOaC8zWjvnkavSh0HAaJzM/mJk3ZeZm4BXgD4DDEbG53GQLcAQ4BmyKiOGIWA2so/jydhy4\ndda2kqQea+fSyweAP46IPweuBZ7LzDeAfRTF/HvArsw8B+wHfj0ijgJ3A3/cmdiSpPlo+RbH5eh+\nxk1zrB8Dxma1TQG3txtOktQZ/qhKkirAYi9JFWCxl6QKsNhLUgVY7CWpAiz2klQBFntJqgCLvSRV\ngMVekirAYi9JFWCxl6QKsNhLUgVY7CWpAiz2klQBFntJqgCLvSRVgMVekirAYi9JFdD0sYQRsYzi\ncYMBTAOfBK4BDgE/Ljfbn5nPRsR2YAdwAdiTmYciYgVwEFgDTALbMnOi42ciSbqiVp5BextAZt4Q\nEZuBR4BvA3sz8/GZjSJiLbATuB4YBo5GxHeBe4ATmfm5iPgYsBu4r6NnIUlqqGmxz8xvRsShcvE9\nwGlgAxARsZVidH8/sBEYz8zzwPmIOAmsB24EHiv3Pww83NlTkCQ108rInsy8EBEHgN8Ffg/4FeDJ\nzDweEbuAzwKvAGfqdpsEVgOr6tpn2hoaHV3J0NCylk9itlptpO19O2WuDIOQ60rM1p5BzTaoucBs\n7ehErpaKPUBmbouIB4G/BH4rM/++XPU88ATwMlCfaITir4Czde0zbQ2dOjXVaqx3qNVGmJiYbHv/\nTpmdYVByzcVs7RnUbIOaC8zWjvnkavSh0PRqnIi4MyIeKhengEvANyJiY9n2IeA4cAzYFBHDEbEa\nWAe8CowDt5bbbgGOtJRaktQxrYzsvwF8OSJeprgK537gp8ATEfE28AZwd2aejYh9FMV8KbArM89F\nxH7gQEQcBd4C7ujGiUiSrqyVL2h/Dvz+HKtumGPbMYrLNOvbpoDb2w0oSVq4lufsNT93ff57ly1/\n+/GtfUoiSf6CVpIqwZF9j9z2wLcuW37q07/dpySSqsiRvSRVgMVekirAYi9JFWCxl6QKsNhLUgVY\n7CWpAiz2klQBFntJqgCLvSRVgMVekirAYi9JFWCxl6QKsNhLUgVY7CWpAip3i+P6h4p4m2FJVdG0\n2EfEMopHDQYwDXwSOAc8XS6/CtybmZciYjuwA7gA7MnMQxGxAjgIrAEmgW2ZOdGFc5EkXUEr0zi3\nAWTmDcBu4BFgL7A7MzcBS4CtEbEW2EnxbNpbgEcjYjlwD3Ci3PaZ8j0kST3UygPHvxkRh8rF9wCn\ngZuBl8q2w8BHgIvAeGaeB85HxElgPXAj8Fjdtg83O+bo6EqGhpbN5zwuU6uNdHS7bujnsecyaHnq\nmW3+BjUXmK0dncjV0px9Zl6IiAPA7wK/B3w4M6fL1ZPAamAVcKZut7naZ9oaOnVqqqXwc6nVRpiY\nmGxp21a364Z+Hnu2+fRZr5lt/gY1F5itHfPJ1ehDoeWrcTJzG/BrFPP3K+pWjVCM9s+Wrxu1z7RJ\nknqoabGPiDsj4qFycQq4BPxVRGwu27YAR4BjwKaIGI6I1cA6ii9vx4FbZ20rSeqhVqZxvgF8OSJe\nBq4B7gdeA8Yi4try9XOZeTEi9lEU86XArsw8FxH7gQMRcRR4C7ijGyciSbqyVr6g/Tnw+3OsummO\nbccopnnq26aA29sNKElaOH9BK0kVYLGXpAqw2EtSBVjsJakCLPaSVAEWe0mqAIu9JFWAxV6SKsBi\nL0kVYLGXpAqw2EtSBVjsJakCLPaSVAEWe0mqAIu9JFWAxV6SKqClB44vVnd9/nuXLT/16d/uUxJJ\n6i5H9pJUAQ1H9hFxDfAUcB2wHNgD/BQ4BPy43Gx/Zj4bEduBHcAFYE9mHoqIFcBBYA0wCWzLzIlu\nnIgk6cqaTeN8HHgzM++MiF8CXgH+E7A3Mx+f2Sgi1gI7geuBYeBoRHwXuAc4kZmfi4iPAbuB+7pw\nHpKkBpoV+68Bz5Wvl1CM2jcAERFbKUb39wMbgfHMPA+cj4iTwHrgRuCxcv/DwMOthBodXcnQ0LL5\nnMdlarWRnu436MdqxaDlqWe2+RvUXGC2dnQiV8Nin5k/A4iIEYqiv5tiOufJzDweEbuAz1KM+M/U\n7ToJrAZW1bXPtDV16tTUPE7hcrXaCBMTk23t2+5+g36sZhbSZ91mtvkb1FxgtnbMJ1ejD4WmX9BG\nxLuB7wNfycyvAs9n5vFy9fPA+4CzQP1RRoDTs9pn2iRJPdaw2EfEu4AXgQcz86my+YWI2Fi+/hBw\nHDgGbIqI4YhYDawDXgXGgVvLbbcARzqcX5LUgmZz9p8BRoGHI2Jmvv0/Av8lIt4G3gDuzsyzEbGP\nopgvBXZl5rmI2A8ciIijwFvAHV05C0lSQ83m7O9j7qtnbphj2zFgbFbbFHD7QgJKkhbOH1VJUgVY\n7CWpAiz2klQBFntJqgCLvSRVgMVekirAYi9JFWCxl6QKsNhLUgVU+rGE8+VjDCVdrRzZS1IFWOwl\nqQIs9pJUARZ7SaoAi70kVYDFXpIqwGIvSRVgsZekCmj4o6qIuAZ4CrgOWA7sAf4aeBqYpnio+L2Z\neSkitgM7gAvAnsw8FBErgIPAGmAS2JaZE905FUnSlTQb2X8ceDMzNwG/A/wJsBfYXbYtAbZGxFpg\nJ8WzaW8BHo2I5cA9wIly22eA3d05DUlSI81ul/A14Lny9RKKUfsG4KWy7TDwEeAiMJ6Z54HzEXES\nWA/cCDxWt+3DrYQaHV3J0NCyVs/hHWq1kZ7s1+5xFrpvNwxannpmm79BzQVma0cncjUs9pn5M4CI\nGKEo+ruBL2bmdLnJJLAaWAWcqdt1rvaZtqZOnZpqMf471WojTExMtrXvfPdr9zgL3bfTFtJn3daN\nbJ26x9Gg9tug5gKztWM+uRp9KDT9gjYi3g18H/hKZn4VuFS3egQ4DZwtXzdqn2mTJPVYw2IfEe8C\nXgQezMynyuYfRcTm8vUW4AhwDNgUEcMRsRpYR/Hl7Thw66xtJUk91mzO/jPAKPBwRMzMt98H7IuI\na4HXgOcy82JE7KMo5kuBXZl5LiL2Awci4ijwFnBHV85CktRQszn7+yiK+2w3zbHtGDA2q20KuH0h\nASVJC+ePqiSpAiz2klQBFntJqgCLvSRVgMVekirAYi9JFWCxl6QKsNhLUgVY7CWpAiz2klQBFntJ\nqgCLvSRVgMVekiqg2S2O1SWdelqSJLXCkb0kVYDFXpIqwGmcBZg9FSNJg6qlYh8R7we+kJmbI+J9\nwCHgx+Xq/Zn5bERsB3YAF4A9mXkoIlYAB4E1wCSwLTMnOn4WkqSGmhb7iPgUcCfw87JpA7A3Mx+v\n22YtsBO4HhgGjkbEd4F7gBOZ+bmI+Biwm7kfcyh1jX+BSa2N7H8CfBT4Srm8AYiI2Eoxur8f2AiM\nZ+Z54HxEnATWAzcCj5X7HQYeRpLUc02LfWZ+PSKuq2s6BjyZmccjYhfwWeAV4EzdNpPAamBVXftM\nW1OjoysZGlrWyqZzqtVGerpfJ/Tz2INw/Ea6nW0h7z+o/TaoucBs7ehErna+oH0+M0/PvAaeAF4G\n6tOMAKeBs3XtM21NnTo11UasQq02wsTEZFv7trtfJ/Tz2Avps27rRbZ2339Q+21Qc4HZ2jGfXI0+\nFNq59PKFiNhYvv4QcJxitL8pIoYjYjWwDngVGAduLbfdAhxp43iSpAVqZ2R/D/BERLwNvAHcnZln\nI2IfRTFfCuzKzHMRsR84EBFHgbeAOzoVXJLUupaKfWb+LfCB8vUPgRvm2GYMGJvVNgXcvuCUkqQF\nWXQ/qrrtgW9dtuw9ZyTJ2yVIUiUsupH9YuAdMSV1miN7SaoAi70kVYDFXpIqwGIvSRVgsZekCrDY\nS1IFWOwlqQIs9pJUARZ7SaoAi70kVYC3S2jAZ5dKWiws9nUs7ouD/x2ld3IaR5IqwGIvSRVgsZek\nCmhpzj4i3g98ITM3R8SvAk8D0xQPFb83My9FxHZgB3AB2JOZhyJiBXAQWANMAtsyc6IL5yFJaqDp\nyD4iPgU8CQyXTXuB3Zm5CVgCbI2ItcBOimfT3gI8GhHLKR5OfqLc9hlgd+dPQZLUTCvTOD8BPlq3\nvAF4qXx9GLgZ2AiMZ+b5zDwDnATWAzcC35m1rSSpx5pO42Tm1yPiurqmJZk5Xb6eBFYDq4AzddvM\n1T7T1tTo6EqGhpa1smlTtdpIR96n2xrl7MU5DHI/dTvbQt5/UPttUHOB2drRiVztXGd/qe71CHAa\nOFu+btQ+09bUqVNTbcSa28TEZMfeq5sa5ez2OdRqIx05RjeendupbI20+/69yNaOQc0FZmvHfHI1\n+lBop9j/KCI2Z+YPgC3A94FjwCMRMQwsB9ZRfHk7Dtxart8CHGnjeOqj+gLug8+lq1c7xf4BYCwi\nrgVeA57LzIsRsY+imC8FdmXmuYjYDxyIiKPAW8AdnQquxaUbfxVI+v9aKvaZ+bfAB8rXrwM3zbHN\nGDA2q20KuH3BKRfAn85Lkj+qkqRK8EZouio4zSMtjCN7SaoAi70kVYDFXpIqwDn7RWAh89nOhUvV\n4MhekirAYi9JFWCxl6QKcM5easDvNLRYOLKXpApwZD8gvIfP5ewPqbMs9mqZUxrS1ctifxW6Wka9\nfjhIg8Nir6ve1fLhJ/WTX9BKUgU4slfbHFFLVw+LvS5jAZcWp7aLfUT8EDhbLv4N8AjwNDBN8bDx\nezPzUkRsB3YAF4A9mXloQYnVlA8JlzRbW8U+IoaBJZm5ua7tfwK7M/MHEfElYGtE/DmwE7geGAaO\nRsR3M/P8wqNLklrV7sj+vcDKiHixfI/PABuAl8r1h4GPABeB8bK4n4+Ik8B64H8vKLXUJ15OqqtV\nu8V+Cvgi8CTwryiK+5LMnC7XTwKrgVXAmbr9ZtobGh1dydDQsjajLT612khf9u20TmYZlPcapP6t\nN6i5wGzt6ESudov968DJsri/HhFvUozsZ4wApynm9EfmaG/o1KmpNmMtThMTkx3bt59fwC7kPAb1\nvTqZo1NqtZGBzAVma8d8cjX6UGj3Ovu7gMcBIuKXKUbwL0bE5nL9FuAIcAzYFBHDEbEaWEfx5a0k\nqYfaHdn/GfB0RByluPrmLuAfgbGIuBZ4DXguMy9GxD6Kwr8U2JWZ5zqQW5I0D20V+8x8C7hjjlU3\nzbHtGDDWznEkSZ3hj6rUM17/fzmv7FEvWex1VfKXvtL8eCM0SaoAR/ZXAUexkhbKkb0kVYDFXpIq\nwGkcVZ5XCV3Oq4QWJ4v9Iud8v5rx30g1OI0jSRXgyF594Wiyt5yakcVeWoT8MNVsFnupTj+LpKNv\ndZNz9pJUARZ7SaoAp3GkATWfaZ1uTj85vbQ4WOylBbAQ6mrhNI4kVYAje+kqMSi3dWj014x/6Qyu\nrhf7iFgK/DfgvcB54D9k5sluH1fqh/kUwk4eR2pmyfT0dFcPEBEfBf5tZn4iIj4APJSZWxvtMzEx\n2XYo/08gDa5vP76ViYnJjrxXp/+KqNVGOpatk+aTq1YbWXKldb2YxrkR+A5AZv5FRFzfg2NKGkC3\nPfCty5Y7eYXRfLaffdxeTj81ytnN4/ZiZP8k8PXMPFwu/x3wLzPzQlcPLEn6hV5cjXMWGKk/poVe\nknqrF8V+HLgVoJyzP9GDY0qS6vRizv554MMR8b+AJcAf9uCYkqQ6XZ+zlyT1n7+glaQKsNhLUgVY\n7CWpAhbFvXEG5ZYMEfFDiktNAf4GeAR4GpgGXgXuzcxLEbEd2AFcAPZk5qEuZno/8IXM3BwRv9pq\nnohYARwE1gCTwLbMnOhitvcBh4Afl6v3Z+azvc4WEdcATwHXAcuBPcBf0+d+u0KunzIYfbYMGAOC\noo8+CZxjAP6tXSHbNQxAv5X51gDHgQ+Xx32aLvXZYhnZ/ztgODN/E/g08HivA0TEMLAkMzeX//tD\nYC+wOzM3UVyJtDUi1gI7gRuAW4BHI2J5lzJ9CngSGC6b5pPnHuBEue0zwO4uZ9sA7K3rv2f7lO3j\nwJvle/8O8CcMRr/NlWtQ+uw2gMy8oXzfRxiMPrtStoHot/ID/E+Bfyqbutpni6XYX3ZLBqAft2R4\nL7AyIl6MiO+VvynYALxUrj8M3AxsBMYz83xmngFOAuu7lOknwEfrlueT5xd9Wrdtt7P9m4h4OSL+\nLCJG+pTta8DD5eslFKOpQei3K+Xqe59l5jeBu8vF9wCnGYw+a5St7/0GfBH4EvAP5XJX+2yxFPtV\nwJm65YsR0espqimK/3i3UPyp+N8pRvoz17ZOAqt5Z9aZ9o7LzK8Db9c1zSdPfXvHM86R7RjwR5n5\nQeD/Ap/tR7bM/FlmTpYF4DmKEVPf++0KuQaiz8p8FyLiAPAE8/+33+tsfe+3iPgEMJGZL9Q1d7XP\nFkuxH4RbMrwOHMzM6cx8HXgTeFfd+hGKUcXsrDPtvXBpHnnq23uR8fnMPD7zGnhfv7JFxLuB7wNf\nycyvMiD9NkeugekzgMzcBvwaxRz5iiYZ+pntxQHot7sofmz6A+A3KKZi1jQ5/oJyLZZiPwi3ZLiL\n8ruCiPhlik/eFyNic7l+C3CEYlSxKSKGI2I1sI7iy5he+NE88vyiT+u27aYXImJj+fpDFF9a9Txb\nRLwLeBF4MDOfKpv73m9XyDUofXZnRDxULk5RfDj+Vb/7rEG2b/S73zLzg5l5U2ZuBl4B/gA43M0+\nWxS/oK27Gmc95S0ZMvP/9DjDtRTfpP8Lim/THwT+kWIkcS3wGrA9My+W367fTfFh+5/LKY1u5boO\n+B+Z+YGImBnZNM0TESuBA8A/B94C7sjMN7qY7V9T/Jn9NvAGcHdmnu11toj4r8C/B+r//dwH7KOP\n/XaFXLuAx+h/n/0z4MvAWoorXT5P0U99/7d2hWw/ZQD+rdVl/AHF1O8luthni6LYS5IaWyzTOJKk\nBiz2klQBFntJqgCLvSRVgMVekirAYi9JFWCxl6QK+H/++UgVFluIPAAAAABJRU5ErkJggg==\n",
      "text/plain": [
       "<matplotlib.figure.Figure at 0x6bf2ac50>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Remove limit from the pipeline\n",
    "pipeline = [{\"$match\": {\"type\": \"way\"}},\n",
    "            {'$project': {'DiffMilliSec': {'$subtract': [datetime.now(), '$way.timestamp']}}}, \n",
    "            {'$project': {'_id': 1, 'AgeDays': {'$divide': ['$DiffMilliSec', 1000*60*60*24]}}}, \n",
    "            {\"$sort\": {\"AgeDays\": -1}}\n",
    "           ]\n",
    "\n",
    "results = agg_pipeline(sydney_db, pipeline)\n",
    "\n",
    "# Create and populate dictionary\n",
    "agedict = {}\n",
    "for result in results: \n",
    "    agedict[result['_id']] = result['AgeDays']\n",
    "    \n",
    "# Import pandas DataFrame to create a dataframe and plot it\n",
    "from pandas import DataFrame\n",
    "# Create a dataframe with rows as keys\n",
    "agedf = DataFrame.from_dict(agedict, orient='index')  \n",
    "\n",
    "# Plot data \n",
    "%pylab inline\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "\n",
    "# Plotting a histogram\n",
    "agedf[0].hist(bins = 80)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So, the information is not exactly fresh. In my opinion, records with age more than 1000 days (~ 3 years) should be revisited. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Conclusion"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this submission, I have downloaded information on several Sydney suburbs from openstreetmap in xml format, examined the data, came up with specific approaches to clean the data, performed wrangling and transformed it to json. Produced file was then imported to MongoDB, and additional data exploration was performed in the database. \n",
    "\n",
    "Below is the final wrap-up on approaches to clean the data, benefits, and anticipated issues: \n",
    "\n",
    "**1. Problematic payment tags cleaning** \n",
    "\n",
    "Payment tags with the key containing problematic characters and 'payment' in any case (lower or upper) are changed to a tag from the OpenStreetMap wiki \"payment:credit_cards\" with the value \"yes\". \n",
    "\n",
    "* Benefits\n",
    "   1. Problematic characters are corrected for further processing. \n",
    "   2. A specific tag in the dataset is changed to reflect that payment by credit cards are accepted. \n",
    "   \n",
    "   \n",
    "* Anticipated issues\n",
    "   1. Since the tags are processed to reflect payment:credit_cards=yes following up on the detection of a single case in the dataset, tags in incorrect format with other payment methods will have the same value. This may be incorrect - therefore, the process_bad_payment function should be improved if the code is used on other datasets. \n",
    "   \n",
    "**2. Skipping tags with problematic characters without 'payment' in their key** \n",
    "\n",
    "* Benefits\n",
    "   1. Problematic characters are corrected for further processing. \n",
    "   \n",
    "   \n",
    "* Anticipated issues\n",
    "   1. Relevant tags with useful data could be skipped. The code should be changed if one wants to investigate tags with problematic characters on other datasets.  \n",
    "   \n",
    "**3. Cleaning street names according to the audit analysis** \n",
    "\n",
    "* Benefits\n",
    "   1. Several universal transformations are performed to correct typical errors caused by manual input (cleaning non-English and non-alphanumeric symbols, removing clarifications, etc.). \n",
    "   2. Particular values are changed using knowledge of the reviewed area. \n",
    "   3. The process_st_name function is quite fast despite having a set of complex rules. It will take ~ 14 seconds to process a million records. \n",
    "   \n",
    "   \n",
    "* Anticipated issues\n",
    "   1. The approach to clean street names may be irrelevant in other areas. I recommend checking the results of applying the process_st_name function to elements of a new dataset prior to using it. \n",
    "   2. If better performance is required, one could consider rewriting the function without using regular expressions. \n",
    " \n",
    "**4. Cleaning maximum speed values** \n",
    "\n",
    "* Benefits\n",
    "   1. Several universal transformations performed to correct typical errors caused by manual input ('mph' in name, 'sign' to 'signals'). \n",
    "   2. The function is lightning fast (~ 3 seconds to process 10 million values). \n",
    "   \n",
    "   \n",
    "* Anticipated issues\n",
    "   1. If a value cannot be transformed to integer after removing 'mph', None will be returned (except for values '10;10', 'sign', and 'signals'. Auditing of values in a new dataset is recommended to modify the process_speed function if required. \n",
    "   \n",
    "**5. Cleaning cycling-related tags**\n",
    "\n",
    "* Benefits\n",
    "   1. Universal transformations performed to correct typical errors caused by manual input (removing problematic symbols, changing 'y' to 'yes', deleting a part after the first semicolon). \n",
    "   2. The function is fast (~ 3 seconds to process 1 million values). \n",
    "   \n",
    "   \n",
    "* Anticipated issues\n",
    "   1. If a new dataset is used, there may be some unexpected tag values. I recommend auditing values of a new dataset to modify the process_bicycle function if required. \n",
    "   \n",
    "Overally, the final process_map function took ~ 31 seconds to process the 68.83 Mb dataset and to produce 329552 records, which would be inappropriate for real-time use but satisfactory for an offline analysis. "
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}