P3_OpenStreetMap/OpenStreetMap Project - Data Wrangling with MongoDB.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Introduction\n",
    "\n",
    "In this project, an [OpenStreetMap](https://www.openstreetmap.org) XML dataset of the Raleigh, North Carolina area is extracted from [Map Zen](https://mapzen.com/data/metro-extracts/#raleigh-north-carolina). \n",
    "\n",
    "First, I use data munging techniques, such as assessing the quality of the data for validity, accuracy, completeness, consistency and uniformity, to clean the OpenStreetMap data of the Raleigh, North Carolina area. Then the cleaned data is saved as JASON file, and imported into MongoDB for further analysis.\n",
    "\n",
    "# Data Process\n",
    "\n",
    "The OpenStreeMap(OSM) XML dataset of Raleigh is about 500 MB. To simplify the data clean process, we first take a systematic sample of elements from original region, and make sure the data process procedure work for the subsampled dataset. \n",
    "\n",
    "## Problems in the Data\n",
    "\n",
    "Through wrangling the sample dataset, I found some two problems:\n",
    "* Inconsistent street names (e.g., 'St.', 'St', and 'Street')\n",
    "* Different formats of postcodes (e.g., '27606-3188' and '27606')\n",
    "\n",
    "For the inconsistent stree names, I changed the abbreviations into full names (e.g. 'St.' or 'St' to 'Street').\n",
    "\n",
    "For the postcodes, the 5-digits format is applied to all records(e.g. '27606-3188' to '27606').\n",
    "\n",
    "The two processes are implemented programmatically in the code.\n",
    "\n",
    "After transforming the XML dataset into JSON format, we have the file with size about 518.5 MB.\n",
    "\n",
    "# Data Analysis\n",
    "\n",
    "## Tags Numbers\n",
    "\n",
    "To analyze the dataset, we first count the number of tags in the XML file, and get the following records:\n",
    "\n",
    "```\n",
    "{'bounds': 1,\n",
    " 'member': 8078,\n",
    " 'nd': 2498300,\n",
    " 'node': 2216984,\n",
    " 'osm': 1,\n",
    " 'relation': 814,\n",
    " 'tag': 835000,\n",
    " 'way': 226555}\n",
    "```\n",
    "\n",
    "Then we check if there are any potential problems for each `<tag>`. Generally, all the items fall into four categories: \n",
    "\n",
    "```python\n",
    "'''\n",
    "  \"lower\": for tags that contain only lowercase letters and are valid;\n",
    "  \"lower_colon\": for otherwise valid tags with a colon in their names;\n",
    "  \"problemchars\": for tags with problematic characters;\n",
    "  \"other\": for other tags that do not fall into the other three categories.\n",
    "'''\n",
    "lower = re.compile(r'^([a-z]|_)*$')\n",
    "lower_colon = re.compile(r'^([a-z]|_)*:([a-z]|_)*$')\n",
    "problemchars = re.compile(r'[=\\+/&<>;\\'\"\\?%#$@\\,\\. \\t\\r\\n]')\n",
    "```\n",
    "\n",
    "We get the number of each category as follow:\n",
    "```\n",
    "{'lower': 517443, 'lower_colon': 273752, 'other': 43805, 'problemchars': 0}\n",
    "```\n",
    "\n",
    "## Import into MongoDB\n",
    "\n",
    "After getting the JSON file, we import it into the MongoDB and do some basic statistics about the dataset.\n",
    "\n",
    "**Import into database:**\n",
    "\n",
    "* Start a running `mongod` instance at shell: \n",
    "> `$mongod`\n",
    "* Import data into the database at shell:\n",
    "> `mongoimport --db OpenStreetMap --collection Raleigh_NC --drop --file raleigh_north-carolina.osm.json`\n",
    "\n",
    "**Count the number of nodes and ways:**\n",
    "\n",
    "```pyton\n",
    "print 'Total number of documents: ', collection.find().count()\n",
    "print 'Number of node:', collection.find({'type': 'node'}).count()\n",
    "print 'Number of way:', collection.find({'type': 'way'}).count()\n",
    "```\n",
    "> Total number of documents:  2443539\n",
    "\n",
    "> Number of node: 2216984\n",
    "\n",
    "> Number of way: 226555\n",
    "\n",
    "This is consistent with the XML dataset records.\n",
    "\n",
    "**Number one contributor**\n",
    "\n",
    "```python\n",
    "a = collection.aggregate([{\"$group\": {\"_id\": \"$created.user\", \"count\":{\"$sum\":1}}}, {\"$sort\":{\"count\":-1}}, {\"$limit\":10}])\n",
    "pprint.pprint(list(a))\n",
    "\n",
    "[{u'_id': u'jumbanho', u'count': 1581327},\n",
    " {u'_id': u'bdiscoe', u'count': 127974},\n",
    " {u'_id': u'woodpeck_fixbot', u'count': 117842},\n",
    " {u'_id': u'bigal945', u'count': 103333},\n",
    " {u'_id': u'JMDeMai', u'count': 92466},\n",
    " {u'_id': u'yotann', u'count': 66844},\n",
    " {u'_id': u'runbananas', u'count': 42069},\n",
    " {u'_id': u'sandhill', u'count': 32089},\n",
    " {u'_id': u'MikeInRaleigh', u'count': 22836},\n",
    " {u'_id': u'FIM', u'count': 20200}]\n",
    "```\n",
    "\n",
    "**Number one contributor with UID**\n",
    "\n",
    "```python\n",
    "a = collection.aggregate([{\"$group\": {\"_id\": \"$created.uid\", \"count\":{\"$sum\":1}}}, {\"$sort\":{\"count\":-1}}, {\"$limit\":10}])\n",
    "pprint.pprint(list(a))\n",
    "\n",
    "[{u'_id': u'38487', u'count': 1581327},\n",
    " {u'_id': u'402624', u'count': 127974},\n",
    " {u'_id': u'147510', u'count': 117842},\n",
    " {u'_id': u'2294834', u'count': 103333},\n",
    " {u'_id': u'922360', u'count': 92466},\n",
    " {u'_id': u'522859', u'count': 66844},\n",
    " {u'_id': u'199905', u'count': 42069},\n",
    " {u'_id': u'1694880', u'count': 32089},\n",
    " {u'_id': u'2254200', u'count': 22836},\n",
    " {u'_id': u'398883', u'count': 20200}]\n",
    "```\n",
    "\n",
    "**Number of users appearing only once (having 1 post)**\n",
    "\n",
    "```python\n",
    "b = collection.aggregate([{\"$group\":{\"_id\":\"$created.user\", \"count\":{\"$sum\":1}}}, {\"$group\":{\"_id\":\"$count\", \"num_users\":{\"$sum\":1}}}, {\"$sort\":{\"_id\":1}}, {\"$limit\":1}])\n",
    "print list(b)\n",
    "\n",
    "[{u'num_users': 139, u'_id': 1}]\n",
    "\n",
    "```\n",
    "**No.1 appearing place names**\n",
    "\n",
    "```python\n",
    "a = collection.aggregate([{\"$match\":{\"name\":{\"$exists\":1}}}, {\n",
    "            \"$group\":{\"_id\":\"$name\",\"count\":{\"$sum\":1}}\n",
    "        }, {\"$sort\":{\"count\":-1}}, {\"$limit\":1}])\n",
    "pprint.pprint(list(a))\n",
    "\n",
    "[{u'_id': {u'en': u'Raleigh-Durham International Airport'}, u'count': 1}]\n",
    "```\n",
    "\n",
    "# Other Ideas About the Dataset\n",
    "\n",
    "Because there are so many format problems in the dataset, I would suggest the OSM to add some constraints for the user input content. For example, when enter the address, the address name should not contain any abbreviation. If there are some abbreviations, such as 'St', the website should point out the problem or even correct it automatically and ask the user to confirm it.\n",
    "\n",
    "In order to avoid incorrect or inconsistent data records, the website can implement some search-compare functions. When a user enter a record, such as address, the website can compare the entered address with the existing ones and check if they are consistent or not.\n",
    "\n",
    "Potential problems for the implementation is that the website may need a very powerful server and fast data search and comparision program to make sure the result can be reached very fast. Otherwise, either the user may need wait for a long time to enter a record, or the automatic correctness procedure cannot be implemented effectively.\n",
    "\n",
    "\n",
    "# Conclusion\n",
    "\n",
    "The OSM data of Raleigh, NC area is extracted and cleaned. The XML file is transformed into JSON file, and then imported into MongoDB. A series of queries and analysis is conducted for the JSON file in the MongoDB.\n",
    "\n",
    "# Appendices"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 1) Subsample the original dataset\n",
    "\n",
    "import xml.etree.cElementTree as ET  # Use cElementTree or lxml if too slow\n",
    "\n",
    "OSM_FILE = \"raleigh_north-carolina.osm\"  # Replace this with your osm file\n",
    "SAMPLE_FILE = \"raleigh_north-carolina_sample.osm\"\n",
    "\n",
    "k = 80 # Parameter: take every k-th top level element\n",
    "\n",
    "def get_element(osm_file, tags=('node', 'way', 'relation')):\n",
    "    \"\"\"Yield element if it is the right type of tag\n",
    "\n",
    "    Reference:\n",
    "    http://stackoverflow.com/questions/3095434/inserting-newlines-in-xml-file-generated-via-xml-etree-elementtree-in-python\n",
    "    \"\"\"\n",
    "    context = iter(ET.iterparse(osm_file, events=('start', 'end')))\n",
    "    _, root = next(context)\n",
    "    for event, elem in context:\n",
    "        if event == 'end' and elem.tag in tags:\n",
    "            yield elem\n",
    "            root.clear()\n",
    "\n",
    "\n",
    "with open(SAMPLE_FILE, 'wb') as output:\n",
    "    output.write('<?xml version=\"1.0\" encoding=\"UTF-8\"?>\\n')\n",
    "    output.write('<osm>\\n  ')\n",
    "\n",
    "    # Write every kth top level element\n",
    "    for i, element in enumerate(get_element(OSM_FILE)):\n",
    "        if i % k == 0:\n",
    "            output.write(ET.tostring(element, encoding='utf-8'))\n",
    "\n",
    "    output.write('</osm>')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[{'created': {'changeset': '7525913',\n",
      "              'timestamp': '2011-03-11T18:03:13Z',\n",
      "              'uid': '38487',\n",
      "              'user': 'jumbanho',\n",
      "              'version': '4'},\n",
      "  'id': '21592692',\n",
      "  'pos': [35.9746754, -78.9086172],\n",
      "  'type': 'node'},\n",
      " {'created': {'changeset': '6118030',\n",
      "              'timestamp': '2010-10-20T17:35:46Z',\n",
      "              'uid': '38487',\n",
      "              'user': 'jumbanho',\n",
      "              'version': '2'},\n",
      "  'id': '21595910',\n",
      "  'pos': [35.9323309, -78.9249163],\n",
      "  'type': 'node'},\n",
      " {'created': {'changeset': '9059469',\n",
      "              'timestamp': '2011-08-18T19:00:34Z',\n",
      "              'uid': '398883',\n",
      "              'user': 'FIM',\n",
      "              'version': '3'},\n",
      "  'id': '21595911',\n",
      "  'pos': [35.9293805, -78.9272018],\n",
      "  'type': 'node'},\n",
      " {'created': {'changeset': '9059469',\n",
      "              'timestamp': '2011-08-18T19:00:34Z',\n",
      "              'uid': '398883',\n",
      "              'user': 'FIM',\n",
      "              'version': '3'},\n",
      "  'id': '21595918',\n",
      "  'pos': [35.9291511, -78.9249346],\n",
      "  'type': 'node'},\n",
      " {'created': {'changeset': '9059469',\n",
      "              'timestamp': '2011-08-18T19:00:34Z',\n",
      "              'uid': '398883',\n",
      "              'user': 'FIM',\n",
      "              'version': '3'},\n",
      "  'id': '21595919',\n",
      "  'pos': [35.9294047, -78.9252983],\n",
      "  'type': 'node'},\n",
      " {'created': {'changeset': '6118030',\n",
      "              'timestamp': '2010-10-20T17:35:58Z',\n",
      "              'uid': '38487',\n",
      "              'user': 'jumbanho',\n",
      "              'version': '3'},\n",
      "  'id': '21595920',\n",
      "  'pos': [35.9289895, -78.9269356],\n",
      "  'type': 'node'},\n",
      " {'created': {'changeset': '9059469',\n",
      "              'timestamp': '2011-08-18T19:00:34Z',\n",
      "              'uid': '398883',\n",
      "              'user': 'FIM',\n",
      "              'version': '3'},\n",
      "  'id': '21595921',\n",
      "  'pos': [35.9292973, -78.9258812],\n",
      "  'type': 'node'},\n",
      " {'created': {'changeset': '6118030',\n",
      "              'timestamp': '2010-10-20T17:35:43Z',\n",
      "              'uid': '38487',\n",
      "              'user': 'jumbanho',\n",
      "              'version': '2'},\n",
      "  'id': '21595922',\n",
      "  'pos': [35.9285729, -78.9286658],\n",
      "  'type': 'node'},\n",
      " {'created': {'changeset': '6118030',\n",
      "              'timestamp': '2010-10-20T17:35:59Z',\n",
      "              'uid': '38487',\n",
      "              'user': 'jumbanho',\n",
      "              'version': '2'},\n",
      "  'id': '21595923',\n",
      "  'pos': [35.9288788, -78.9299995],\n",
      "  'type': 'node'},\n",
      " {'created': {'changeset': '21023776',\n",
      "              'timestamp': '2014-03-10T12:14:09Z',\n",
      "              'uid': '38487',\n",
      "              'user': 'jumbanho',\n",
      "              'version': '3'},\n",
      "  'id': '21595924',\n",
      "  'pos': [35.9291183, -78.9310836],\n",
      "  'type': 'node'}]\n"
     ]
    }
   ],
   "source": [
    "# 2) Wrangle the data and transform the shape of the data model\n",
    "import re\n",
    "import json\n",
    "import pprint\n",
    "import xml.etree.cElementTree as ET \n",
    "\n",
    "#dataset = 'raleigh_north-carolina_sample.osm'\n",
    "dataset = 'raleigh_north-carolina.osm'\n",
    "\n",
    "\n",
    "# For different types of tags\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",
    "CREATED = [ \"version\", \"changeset\", \"timestamp\", \"user\", \"uid\"]\n",
    "\n",
    "# For address and other tag types\n",
    "re_addr = re.compile(r'^addr:[^:]*$')\n",
    "re_xxx = re.compile(r'^[^:]*:[^:]*$')\n",
    "\n",
    "# Fix unexpected street type\n",
    "expected = [\"Street\", \"Avenue\", \"Boulevard\", \"Drive\", \"Court\", \"Place\", \n",
    "            \"Square\", \"Lane\", \"Road\",\"Trail\", \"Parkway\", \"Commons\"]\n",
    "\n",
    "# UPDATE THIS VARIABLE\n",
    "mapping = { \"St\": \"Street\",\n",
    "            \"St.\": \"Street\",\n",
    "            \"Rd\": \"Road\",\n",
    "           \"Rd.\": \"Road\",\n",
    "           \"Ct\": \"Court\",\n",
    "           \"Ct.\": \"Court\"\n",
    "            }\n",
    "\n",
    "\n",
    "def update_name(name, mapping):\n",
    "    '''Update name style in mapping dictionary.'''\n",
    "    tmp = name.split()\n",
    "    if tmp[-1] in mapping:\n",
    "        tmp[-1] = mapping[tmp[-1]]\n",
    "    name = \" \".join(tmp)\n",
    "\n",
    "    return name\n",
    "\n",
    "def shape_element(element):\n",
    "    '''Shape the element in XML into data model format.'''\n",
    "    node = {}\n",
    "    if element.tag == \"node\" or element.tag == \"way\" :\n",
    "        node['id'] = element.attrib['id']\n",
    "        node['type'] = element.tag\n",
    "        if 'visible' in element.attrib:\n",
    "            node['visible'] = element.attrib['visible']\n",
    "        # For items in CREATED\n",
    "        node['created'] = {}\n",
    "        for x in CREATED:\n",
    "            if x in element.attrib:\n",
    "                node['created'][x] = element.attrib[x]\n",
    "        if 'lon' in element.attrib:\n",
    "            node['pos'] = [float(element.attrib['lat']), float(element.attrib['lon'])]\n",
    "\n",
    "        for tag in element.iter('tag'):\n",
    "        # If not in problemchars\n",
    "        #if 'k' in element.attrib:\n",
    "          if not problemchars.match(tag.attrib['k']):\n",
    "            # If in addr:xxx form\n",
    "\n",
    "            if re_addr.match(tag.attrib['k']):\n",
    "\n",
    "                if 'address' not in node:\n",
    "                    node['address'] = {}\n",
    "                # print tag.attrib['k']\n",
    "                if tag.attrib['k'][5:] == 'street':\n",
    "                    node['address'][tag.attrib['k'][5:]] = update_name(tag.attrib['v'],\n",
    "                                                                      mapping)\n",
    "                elif tag.attrib['k'][5:] == 'postcode':\n",
    "                    node['address'][tag.attrib['k'][5:]] = tag.attrib['v'][0:5]\n",
    "                else:\n",
    "                    node['address'][tag.attrib['k'][5:]] = tag.attrib['v']\n",
    "            \n",
    "            # If not in xxx:xxx form\n",
    "            elif re_xxx.match(tag.attrib['k']):\n",
    "                tmp = tag.attrib['k'].index(':')\n",
    "                if tag.attrib['k'][:tmp] not in node:\n",
    "                    node[tag.attrib['k'][:tmp]] = {}\n",
    "                node[tag.attrib['k'][:tmp]][tag.attrib['k'][tmp+1:]] = tag.attrib['v']\n",
    "\n",
    "        tmp = []\n",
    "        for tag in element.iter('nd'):\n",
    "            if 'ref' in tag.attrib:\n",
    "                tmp.append(tag.attrib['ref'])\n",
    "        if len(tmp) > 0:\n",
    "            node['node_refs'] = tmp\n",
    "\n",
    "        return node\n",
    "    else:\n",
    "        return None\n",
    "\n",
    "\n",
    "def process_map(file_in, pretty = False):\n",
    "    # You do not need to change this file\n",
    "    file_out = \"{0}.json\".format(file_in)\n",
    "    data = []\n",
    "    with open(file_out, \"w\") as fo:\n",
    "        for _, element in ET.iterparse(file_in):\n",
    "            el = shape_element(element)\n",
    "            if el:\n",
    "                data.append(el)\n",
    "                if pretty:\n",
    "                    fo.write(json.dumps(el, indent=2)+\"\\n\")\n",
    "                else:\n",
    "                    fo.write(json.dumps(el) + \"\\n\")\n",
    "    return data\n",
    "\n",
    "\n",
    "if __name__ == \"__main__\":\n",
    "    data = process_map(dataset)\n",
    "    pprint.pprint(data[0:10])\n",
    "    \n",
    "    # Print sample data\n",
    "    #for x in data:\n",
    "    #    if 'address' in x:\n",
    "    #        pprint.pprint(x['address'])\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'bounds': 1,\n",
      " 'member': 8078,\n",
      " 'nd': 2498300,\n",
      " 'node': 2216984,\n",
      " 'osm': 1,\n",
      " 'relation': 814,\n",
      " 'tag': 835000,\n",
      " 'way': 226555}\n"
     ]
    }
   ],
   "source": [
    "# 3) Count tags in dataset\n",
    "import xml.etree.cElementTree as ET\n",
    "import pprint\n",
    "\n",
    "dataset = 'raleigh_north-carolina.osm'\n",
    "\n",
    "def count_tags(filename):\n",
    "        '''Iterative parsing to find tags and corresponding numbers.'''\n",
    "        tree = ET.parse(filename)\n",
    "        root = tree.getroot()\n",
    "        dict_tags = {}\n",
    "        return recursive_count(root, dict_tags)\n",
    "\n",
    "\n",
    "def recursive_count(root, dict_tag):\n",
    "    '''Recursively count the number of tags in a root.'''\n",
    "\n",
    "    if root.tag in dict_tag:\n",
    "        dict_tag[root.tag] += 1\n",
    "    else:\n",
    "        dict_tag[root.tag] = 1\n",
    "\n",
    "    if len(root) != 0:\n",
    "        for child in root:\n",
    "            recursive_count(child, dict_tag)\n",
    "    return dict_tag\n",
    "\n",
    "tags = count_tags(dataset)\n",
    "pprint.pprint(tags)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'lower': 517443, 'lower_colon': 273752, 'other': 43805, 'problemchars': 0}\n"
     ]
    }
   ],
   "source": [
    "# 4) Check if there are any potential problems for each \"<tag>\"\n",
    "'''\n",
    "  \"lower\", for tags that contain only lowercase letters and are valid,\n",
    "  \"lower_colon\", for otherwise valid tags with a colon in their names,\n",
    "  \"problemchars\", for tags with problematic characters, and\n",
    "  \"other\", for other tags that do not fall into the other three categories.\n",
    "'''\n",
    "import re\n",
    "\n",
    "lower = re.compile(r'^([a-z]|_)*$')\n",
    "lower_colon = re.compile(r'^([a-z]|_)*:([a-z]|_)*$')\n",
    "problemchars = re.compile(r'[=\\+/&<>;\\'\"\\?%#$@\\,\\. \\t\\r\\n]')\n",
    "\n",
    "#dataset = 'raleigh_north-carolina_sample.osm'\n",
    "dataset = 'raleigh_north-carolina.osm'\n",
    "\n",
    "def key_type(element, keys):\n",
    "    if element.tag == \"tag\":\n",
    "        if lower.match(element.attrib['k']):\n",
    "            keys['lower'] += 1\n",
    "        elif lower_colon.match(element.attrib['k']):\n",
    "            keys['lower_colon'] += 1\n",
    "        elif problemchars.match(element.attrib['k']):\n",
    "            keys['problemchars'] += 1\n",
    "        else:\n",
    "            # print element.attrib\n",
    "            keys['other'] += 1\n",
    "\n",
    "    return keys\n",
    "\n",
    "\n",
    "def check_map(filename):\n",
    "    keys = {\"lower\": 0, \"lower_colon\": 0, \"problemchars\": 0, \"other\": 0}\n",
    "    for _, element in ET.iterparse(filename):\n",
    "        keys = key_type(element, keys)\n",
    "\n",
    "    return keys\n",
    "\n",
    "keys = check_map(dataset)\n",
    "pprint.pprint(keys)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# 5) Using pymongo to connect MongoDB\n",
    "# Before this step, we need to\n",
    "# Start a running MongoD instance at shell: `mongod`\n",
    "# Import the JSON file into MongoDB at shell: \n",
    "# `mongoimport --db OpenStreetMap --collection Raleigh_NC --drop --file raleigh_north-carolina.osm.json `\n",
    "\n",
    "from pymongo import MongoClient\n",
    "import pprint\n",
    "\n",
    "client = MongoClient('localhost', 27017)\n",
    "db = client['OpenStreetMap']\n",
    "collection = db['Raleigh_NC']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " Total number of documents:  2443539\n",
      "Number of node: 2216984\n",
      "Number of way: 226555\n"
     ]
    }
   ],
   "source": [
    "# 6) Count different categories\n",
    "print 'Total number of documents: ', collection.find().count()\n",
    "print 'Number of node:', collection.find({'type': 'node'}).count()\n",
    "print 'Number of way:', collection.find({'type': 'way'}).count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[{u'_id': u'jumbanho', u'count': 1581327},\n",
      " {u'_id': u'bdiscoe', u'count': 127974},\n",
      " {u'_id': u'woodpeck_fixbot', u'count': 117842},\n",
      " {u'_id': u'bigal945', u'count': 103333},\n",
      " {u'_id': u'JMDeMai', u'count': 92466},\n",
      " {u'_id': u'yotann', u'count': 66844},\n",
      " {u'_id': u'runbananas', u'count': 42069},\n",
      " {u'_id': u'sandhill', u'count': 32089},\n",
      " {u'_id': u'MikeInRaleigh', u'count': 22836},\n",
      " {u'_id': u'FIM', u'count': 20200}]\n"
     ]
    }
   ],
   "source": [
    "# 8) Number one contributor\n",
    "a = collection.aggregate([{\"$group\": {\"_id\": \"$created.user\", \"count\":{\"$sum\":1}}}, {\"$sort\":{\"count\":-1}}, {\"$limit\":10}])\n",
    "pprint.pprint(list(a))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[{u'_id': u'38487', u'count': 1581327},\n",
      " {u'_id': u'402624', u'count': 127974},\n",
      " {u'_id': u'147510', u'count': 117842},\n",
      " {u'_id': u'2294834', u'count': 103333},\n",
      " {u'_id': u'922360', u'count': 92466},\n",
      " {u'_id': u'522859', u'count': 66844},\n",
      " {u'_id': u'199905', u'count': 42069},\n",
      " {u'_id': u'1694880', u'count': 32089},\n",
      " {u'_id': u'2254200', u'count': 22836},\n",
      " {u'_id': u'398883', u'count': 20200}]\n"
     ]
    }
   ],
   "source": [
    "# 9) Number one contributor with UID\n",
    "a = collection.aggregate([{\"$group\": {\"_id\": \"$created.uid\", \"count\":{\"$sum\":1}}}, {\"$sort\":{\"count\":-1}}, {\"$limit\":10}])\n",
    "pprint.pprint(list(a))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[{u'num_users': 139, u'_id': 1}]\n"
     ]
    }
   ],
   "source": [
    "# 10) Number of users appearing only once (having 1 post)\n",
    "b = collection.aggregate([{\"$group\":{\"_id\":\"$created.user\", \"count\":{\"$sum\":1}}}, {\"$group\":{\"_id\":\"$count\", \"num_users\":{\"$sum\":1}}}, {\"$sort\":{\"_id\":1}}, {\"$limit\":1}])\n",
    "print list(b)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[{u'_id': {u'en': u'Raleigh-Durham International Airport'}, u'count': 1}]\n"
     ]
    }
   ],
   "source": [
    "# 11) No.1 appearing place names\n",
    "a = collection.aggregate([{\"$match\":{\"name\":{\"$exists\":1}}}, {\n",
    "            \"$group\":{\"_id\":\"$name\",\"count\":{\"$sum\":1}}\n",
    "        }, {\"$sort\":{\"count\":-1}}, {\"$limit\":1}])\n",
    "pprint.pprint(list(a))"
   ]
  }
 ],
 "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.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}