P3_postcodes.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## P3: OpenStreetMap Data Case Study. Dubai and Abu-Dhabi. Postcodes."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "After the review of the project  https://review.udacity.com/#!/reviews/293667 I've created a special notebook \n",
    "\n",
    "for preprocessing the field \"address: postcode\" in the  .osm file. At first, we should find the range of values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Import python libraries\n",
    "import re\n",
    "import numpy as np\n",
    "import scipy\n",
    "import json\n",
    "import codecs\n",
    "import matplotlib.pyplot as plt\n",
    "import xml.etree.cElementTree as ET\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Fuction for counting postcodes and getting values\n",
    "def zip_codes(filename):\n",
    "    count = 0\n",
    "    data = set()\n",
    "\n",
    "    for event, elem in ET.iterparse(filename, events=(\"start\",)):\n",
    "        if elem.tag == 'node' or elem.tag == 'way':\n",
    "            for tag in elem.iter('tag'):\n",
    "                if tag.attrib['k'] == \"addr:postcode\":\n",
    "                    count += 1\n",
    "                    data.add( tag.attrib['v'] )\n",
    "                                     \n",
    "    return count, data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The next step is to set up the working directory and files for preprocessing:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "FILEDIR = \"/Users/olgabelitskaya/large-repo/\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "SAMPLE_FILE = FILEDIR + \"sample_dubai_abu-dhabi.osm\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "FILE = FILEDIR + \"dubai_abu-dhabi.osm\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "FILE0 = FILEDIR + \"dubai_abu-dhabi0.osm\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "JSON_FILE = FILEDIR + \"dubai_abu-dhabi.osm.json\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "JSON_FILE0 = FILEDIR + \"dubai_abu-dhabi0.osm.json\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Applying the function 'zip_codes':"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "z = zip_codes(FILE)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The number of the unique values:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "96"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(z[1])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The number of notes with postcodes:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "116"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "z[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Discovering problems in the data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "All postcodes: ['0' '0000' '000000' '000001' '00962' '00971' '103711' '108100' '1111'\n",
      " '111695' '113431' '114692' '115443' '119417' '11999' '121641' '1234'\n",
      " '12345' '1243' '125939' '128358' '16095' '20268' '20661' '20767' '2157'\n",
      " '22436' '23117' '231992' '232144' '232574' '24857' '24976' '2504' '25494'\n",
      " '2574' '26268' '263076' '2666' '277' '28676' '28818' '32923' '34121'\n",
      " '34238' '3541' '38126' '38495' '38575' '392189' '41318' '41974' '42324'\n",
      " '42524' '44263' '444786' '44548' '4599' '46477' '473828' '47602' '47612'\n",
      " '500368' '502227' '52799' '5280 dubai' '53577' '549' '57566' '60884'\n",
      " '64649' '6834' '71444' '7770' '77947' '7819' '79506' '811' '81730' '8845'\n",
      " '8988' '9292' '97717' '9978' 'Muhaisnah 4' 'P O BOX 3766'\n",
      " 'P. O. Box 123234' 'P. O. Box 31166' 'P.O. Box 4605'\n",
      " 'P.O. Box 5618, Abu Dhabi, U.A.E' 'P.O. Box 6446' 'P.O. Box 9770'\n",
      " 'PO Box 114822' 'PO Box 118737' 'PO Box 43377' 'PO Box 6770']\n",
      "Expected: ['000001' '00962' '00971' '103711' '108100' '1111' '111695' '113431'\n",
      " '114692' '115443' '119417' '11999' '121641' '1234' '12345' '1243' '125939'\n",
      " '128358' '16095' '20268' '20661' '20767' '2157' '22436' '23117' '231992'\n",
      " '232144' '232574' '24857' '24976' '2504' '25494' '2574' '26268' '263076'\n",
      " '2666' '277' '28676' '28818' '32923' '34121' '34238' '3541' '38126'\n",
      " '38495' '38575' '392189' '41318' '41974' '42324' '42524' '44263' '444786'\n",
      " '44548' '4599' '46477' '473828' '47602' '47612' '500368' '502227' '52799'\n",
      " '53577' '549' '57566' '60884' '64649' '6834' '71444' '7770' '77947' '7819'\n",
      " '79506' '811' '81730' '8845' '8988' '9292' '97717' '9978']\n",
      "Unexpected: ['0' '0000' '000000' '5280 dubai' 'Muhaisnah 4' 'P O BOX 3766'\n",
      " 'P. O. Box 123234' 'P. O. Box 31166' 'P.O. Box 4605'\n",
      " 'P.O. Box 5618, Abu Dhabi, U.A.E' 'P.O. Box 6446' 'P.O. Box 9770'\n",
      " 'PO Box 114822' 'PO Box 118737' 'PO Box 43377' 'PO Box 6770']\n"
     ]
    }
   ],
   "source": [
    "znp = np.array(sorted(z[1]))\n",
    "print \"All postcodes:\", znp\n",
    "expected = np.append(znp[3:65], znp[66:84])\n",
    "print \"Expected:\", expected\n",
    "unexpected0 = np.append(znp[:3], znp[84:])\n",
    "unexpected = np.insert(unexpected0, 3, znp[65])\n",
    "print \"Unexpected:\", unexpected"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Mapping the right values:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'0': 'NA',\n",
       " '0000': 'NA',\n",
       " '000000': 'NA',\n",
       " '5280 dubai': '5280',\n",
       " 'Muhaisnah 4': 'NA',\n",
       " 'P O BOX 3766': '3766',\n",
       " 'P. O. Box 123234': '123234',\n",
       " 'P. O. Box 31166': '31166',\n",
       " 'P.O. Box 4605': '4605',\n",
       " 'P.O. Box 5618, Abu Dhabi, U.A.E': '5618',\n",
       " 'P.O. Box 6446': '6446',\n",
       " 'P.O. Box 9770': '9770',\n",
       " 'PO Box 114822': '114822',\n",
       " 'PO Box 118737': '118737',\n",
       " 'PO Box 43377': '43377',\n",
       " 'PO Box 6770': '6770'}"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "correction = {'0': 'NA', '0000': 'NA', '000000': 'NA', '5280 dubai': '5280', \n",
    "           'Muhaisnah 4': 'NA', 'P O BOX 3766': '3766', 'P. O. Box 123234': '123234', \n",
    "           'P. O. Box 31166': '31166', 'P.O. Box 4605': '4605', \n",
    "           'P.O. Box 5618, Abu Dhabi, U.A.E': '5618', 'P.O. Box 6446': '6446', \n",
    "           'P.O. Box 9770': '9770', 'PO Box 114822': '114822', 'PO Box 118737': '118737', \n",
    "           'PO Box 43377': '43377', 'PO Box 6770': '6770'}\n",
    "correction"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Creating the function for updating:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Function for updating values:\n",
    "def update_name(name, correction):    \n",
    "    if name not in correction.keys():\n",
    "        raise Exception(name)\n",
    "    else:\n",
    "        unexpect = name        \n",
    "    replace = correction[unexpect]    \n",
    "    if not replace:\n",
    "        raise Exception(unexpect)\n",
    "    updated_name = re.sub(unexpect, replace, name)\n",
    "    return updated_name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'43377'"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check the function\n",
    "update_name('PO Box 43377', correction)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's create the list of dictionaries from the .json file:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "DICT = []\n",
    "for line in open(JSON_FILE, 'r+'):\n",
    "   DICT.append(json.loads(line))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2124505"
      ]
     },
     "execution_count": 114,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(DICT)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{u'created': {u'changeset': u'20943519',\n",
       "  u'timestamp': u'2014-03-06T05:32:37Z',\n",
       "  u'uid': u'1770239',\n",
       "  u'user': u'Jennings Anderson',\n",
       "  u'version': u'5'},\n",
       " u'id': u'31475480',\n",
       " u'pos': [25.1527723, 55.1958039],\n",
       " u'source': u'Bing',\n",
       " u'type': u'node'}"
      ]
     },
     "execution_count": 146,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "DICT[1200]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can apply the function for updating and check the results."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 188,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Apply the function 'update_name'\n",
    "for i in range(len(DICT)):\n",
    "    if DICT[i].get('address') != None:\n",
    "        if DICT[i]['address'].get('postcode') != None:\n",
    "            value = DICT[i].get('address').get('postcode')\n",
    "            if value in unexpected:\n",
    "                DICT[i]['address']['postcode'] = update_name(value, correction)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 190,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[u'115443', u'34121', u'811', u'811', u'42524', u'473828', u'473828', u'473828', u'473828', u'3766', u'549', u'6656', u'24976', u'6834', u'500368', u'2666', u'47602', u'232574', u'32923', u'9292', u'444786', u'125939', u'128358', u'119417', u'113431', u'77947', u'41318', u'38495', u'1243', u'28676', u'5618', u'121641', u'42324', u'20268', u'2157', u'5280', u'111695', u'53577', u'53577', u'22436', u'232144', u'81730', u'23117', u'44548', u'47612', u'24857', u'97717', u'60884', u'2574', u'57566', u'NA', u'114692', u'7770', u'20661', u'NA', u'392189', u'46477', u'38575', u'NA', u'4599', u'38126', u'231992', u'103711', u'103711', u'00971', u'7819', u'2504', u'26268', u'64649', u'00962', u'NA', u'NA', u'1234', u'4758', u'44263', u'263076', u'9978', u'71444', u'12345', u'79506', u'108100', u'8988', u'123234', u'125939', u'118737', u'28818', u'31166', u'6770', u'3541', u'114822', u'9770', u'1111', u'811', u'25494', u'41974', u'811', u'811', u'43377', u'16095', u'6834', u'000001', u'8845', u'502227', u'6446', u'52799', u'277', u'20268', u'4605', u'24857', u'20767', u'34238', u'22436', u'22436', u'23117', u'71444', u'24857', u'11999', u'11999']\n"
     ]
    }
   ],
   "source": [
    "# Check the correction of the postcodes\n",
    "postcodes = []\n",
    "for element in DICT:\n",
    "    address = element.get('address')\n",
    "    if address != None:\n",
    "        postcode = address.get('postcode')\n",
    "        if postcode != None:\n",
    "            postcodes.append(postcode)\n",
    "print postcodes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally, let's create a new .json file, insert into the MongoDB collection and compare results."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 192,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Create new file\n",
    "with open(FILEDIR + \"dubai_abu-dhabi_postcode.osm.json\", 'w') as f:\n",
    "    for line in DICT:\n",
    "        json.dump(line, f)\n",
    "        f.write('\\n')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 218,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%load_ext rpy2.ipython"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 219,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%R m <- mongo(\"openstreetmap_correct\", verbose = FALSE)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 220,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%R stream_in(file(\"/Users/olgabelitskaya/large-repo/dubai_abu-dhabi_postcode.osm.json\"), \n",
    "             handler = function(df){m$insert(df)})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 225,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Open databases before correction and after\n",
    "from pymongo import MongoClient\n",
    "client = MongoClient('localhost:27017')\n",
    "database = client['test']\n",
    "dubai_abu_dhabi = database['openstreetmap']\n",
    "dubai_abu_dhabi_correct = database['openstreetmap_correct']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 226,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{u'_id': u'811', u'count': 5},\n",
       " {u'_id': u'473828', u'count': 4},\n",
       " {u'_id': u'22436', u'count': 3},\n",
       " {u'_id': u'24857', u'count': 3},\n",
       " {u'_id': u'11999', u'count': 2},\n",
       " {u'_id': u'125939', u'count': 2},\n",
       " {u'_id': u'000000', u'count': 2},\n",
       " {u'_id': u'71444', u'count': 2},\n",
       " {u'_id': u'23117', u'count': 2},\n",
       " {u'_id': u'20268', u'count': 2},\n",
       " {u'_id': u'53577', u'count': 2},\n",
       " {u'_id': u'103711', u'count': 2},\n",
       " {u'_id': u'6834', u'count': 2},\n",
       " {u'_id': u'20767', u'count': 1},\n",
       " {u'_id': u'277', u'count': 1},\n",
       " {u'_id': u'00971', u'count': 1},\n",
       " {u'_id': u'502227', u'count': 1},\n",
       " {u'_id': u'00962', u'count': 1},\n",
       " {u'_id': u'16095', u'count': 1},\n",
       " {u'_id': u'25494', u'count': 1},\n",
       " {u'_id': u'P O BOX 3766', u'count': 1},\n",
       " {u'_id': u'P.O. Box 9770', u'count': 1},\n",
       " {u'_id': u'PO Box 114822', u'count': 1},\n",
       " {u'_id': u'34238', u'count': 1},\n",
       " {u'_id': u'PO Box 6770', u'count': 1},\n",
       " {u'_id': u'119417', u'count': 1},\n",
       " {u'_id': u'231992', u'count': 1},\n",
       " {u'_id': u'108100', u'count': 1},\n",
       " {u'_id': u'8845', u'count': 1},\n",
       " {u'_id': u'4758', u'count': 1},\n",
       " {u'_id': u'115443', u'count': 1},\n",
       " {u'_id': u'64649', u'count': 1},\n",
       " {u'_id': u'44263', u'count': 1},\n",
       " {u'_id': u'46477', u'count': 1},\n",
       " {u'_id': u'26268', u'count': 1},\n",
       " {u'_id': u'28676', u'count': 1},\n",
       " {u'_id': u'2504', u'count': 1},\n",
       " {u'_id': u'7819', u'count': 1},\n",
       " {u'_id': u'38126', u'count': 1},\n",
       " {u'_id': u'44548', u'count': 1},\n",
       " {u'_id': u'9978', u'count': 1},\n",
       " {u'_id': u'4599', u'count': 1},\n",
       " {u'_id': u'1234', u'count': 1},\n",
       " {u'_id': u'20661', u'count': 1},\n",
       " {u'_id': u'2574', u'count': 1},\n",
       " {u'_id': u'3541', u'count': 1},\n",
       " {u'_id': u'47602', u'count': 1},\n",
       " {u'_id': u'81730', u'count': 1},\n",
       " {u'_id': u'57566', u'count': 1},\n",
       " {u'_id': u'121641', u'count': 1},\n",
       " {u'_id': u'111695', u'count': 1},\n",
       " {u'_id': u'0000', u'count': 1},\n",
       " {u'_id': u'5280 dubai', u'count': 1},\n",
       " {u'_id': u'47612', u'count': 1},\n",
       " {u'_id': u'32923', u'count': 1},\n",
       " {u'_id': u'2157', u'count': 1},\n",
       " {u'_id': u'12345', u'count': 1},\n",
       " {u'_id': u'60884', u'count': 1},\n",
       " {u'_id': u'0', u'count': 1},\n",
       " {u'_id': u'113431', u'count': 1},\n",
       " {u'_id': u'42324', u'count': 1},\n",
       " {u'_id': u'8988', u'count': 1},\n",
       " {u'_id': u'1111', u'count': 1},\n",
       " {u'_id': u'38495', u'count': 1},\n",
       " {u'_id': u'P.O. Box 4605', u'count': 1},\n",
       " {u'_id': u'41318', u'count': 1},\n",
       " {u'_id': u'263076', u'count': 1},\n",
       " {u'_id': u'PO Box 118737', u'count': 1},\n",
       " {u'_id': u'128358', u'count': 1},\n",
       " {u'_id': u'41974', u'count': 1},\n",
       " {u'_id': u'77947', u'count': 1},\n",
       " {u'_id': u'2666', u'count': 1},\n",
       " {u'_id': u'232574', u'count': 1},\n",
       " {u'_id': u'9292', u'count': 1},\n",
       " {u'_id': u'28818', u'count': 1},\n",
       " {u'_id': u'Muhaisnah 4', u'count': 1},\n",
       " {u'_id': u'24976', u'count': 1},\n",
       " {u'_id': u'P.O. Box 5618, Abu Dhabi, U.A.E', u'count': 1},\n",
       " {u'_id': u'38575', u'count': 1},\n",
       " {u'_id': u'232144', u'count': 1},\n",
       " {u'_id': u'000001', u'count': 1},\n",
       " {u'_id': u'97717', u'count': 1},\n",
       " {u'_id': u'PO Box 43377', u'count': 1},\n",
       " {u'_id': u'549', u'count': 1},\n",
       " {u'_id': u'P.O. Box 6446', u'count': 1},\n",
       " {u'_id': u'42524', u'count': 1},\n",
       " {u'_id': u'P. O. Box 123234', u'count': 1},\n",
       " {u'_id': u'500368', u'count': 1},\n",
       " {u'_id': u'1243', u'count': 1},\n",
       " {u'_id': u'P. O. Box 31166', u'count': 1},\n",
       " {u'_id': u'7770', u'count': 1},\n",
       " {u'_id': u'6656', u'count': 1},\n",
       " {u'_id': u'444786', u'count': 1},\n",
       " {u'_id': u'79506', u'count': 1},\n",
       " {u'_id': u'114692', u'count': 1},\n",
       " {u'_id': u'392189', u'count': 1},\n",
       " {u'_id': u'52799', u'count': 1},\n",
       " {u'_id': u'34121', u'count': 1}]"
      ]
     },
     "execution_count": 226,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create a list of zipcodes without correction\n",
    "zipcodes = dubai_abu_dhabi.aggregate( [ \n",
    "    { \"$match\" : { \"address.postcode\" : { \"$exists\" : 1} } }, \n",
    "    { \"$group\" : { \"_id\" : \"$address.postcode\", \"count\" : { \"$sum\" : 1} } },  \n",
    "    { \"$sort\" : { \"count\" : -1}}\n",
    "] )\n",
    "list(zipcodes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 227,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{u'_id': u'811', u'count': 5},\n",
       " {u'_id': u'473828', u'count': 4},\n",
       " {u'_id': u'NA', u'count': 3},\n",
       " {u'_id': u'22436', u'count': 3},\n",
       " {u'_id': u'24857', u'count': 3},\n",
       " {u'_id': u'71444', u'count': 2},\n",
       " {u'_id': u'11999', u'count': 2},\n",
       " {u'_id': u'23117', u'count': 2},\n",
       " {u'_id': u'20268', u'count': 2},\n",
       " {u'_id': u'53577', u'count': 2},\n",
       " {u'_id': u'103711', u'count': 2},\n",
       " {u'_id': u'125939', u'count': 2},\n",
       " {u'_id': u'6834', u'count': 2},\n",
       " {u'_id': u'34238', u'count': 1},\n",
       " {u'_id': u'20767', u'count': 1},\n",
       " {u'_id': u'277', u'count': 1},\n",
       " {u'_id': u'502227', u'count': 1},\n",
       " {u'_id': u'16095', u'count': 1},\n",
       " {u'_id': u'25494', u'count': 1},\n",
       " {u'_id': u'9770', u'count': 1},\n",
       " {u'_id': u'114822', u'count': 1},\n",
       " {u'_id': u'31166', u'count': 1},\n",
       " {u'_id': u'108100', u'count': 1},\n",
       " {u'_id': u'28818', u'count': 1},\n",
       " {u'_id': u'12345', u'count': 1},\n",
       " {u'_id': u'8845', u'count': 1},\n",
       " {u'_id': u'4758', u'count': 1},\n",
       " {u'_id': u'00962', u'count': 1},\n",
       " {u'_id': u'64649', u'count': 1},\n",
       " {u'_id': u'44263', u'count': 1},\n",
       " {u'_id': u'26268', u'count': 1},\n",
       " {u'_id': u'2504', u'count': 1},\n",
       " {u'_id': u'7819', u'count': 1},\n",
       " {u'_id': u'38126', u'count': 1},\n",
       " {u'_id': u'9978', u'count': 1},\n",
       " {u'_id': u'4599', u'count': 1},\n",
       " {u'_id': u'46477', u'count': 1},\n",
       " {u'_id': u'392189', u'count': 1},\n",
       " {u'_id': u'20661', u'count': 1},\n",
       " {u'_id': u'231992', u'count': 1},\n",
       " {u'_id': u'2574', u'count': 1},\n",
       " {u'_id': u'3541', u'count': 1},\n",
       " {u'_id': u'81730', u'count': 1},\n",
       " {u'_id': u'57566', u'count': 1},\n",
       " {u'_id': u'6446', u'count': 1},\n",
       " {u'_id': u'43377', u'count': 1},\n",
       " {u'_id': u'111695', u'count': 1},\n",
       " {u'_id': u'5280', u'count': 1},\n",
       " {u'_id': u'47612', u'count': 1},\n",
       " {u'_id': u'32923', u'count': 1},\n",
       " {u'_id': u'44548', u'count': 1},\n",
       " {u'_id': u'2157', u'count': 1},\n",
       " {u'_id': u'60884', u'count': 1},\n",
       " {u'_id': u'113431', u'count': 1},\n",
       " {u'_id': u'42324', u'count': 1},\n",
       " {u'_id': u'118737', u'count': 1},\n",
       " {u'_id': u'3766', u'count': 1},\n",
       " {u'_id': u'121641', u'count': 1},\n",
       " {u'_id': u'8988', u'count': 1},\n",
       " {u'_id': u'38495', u'count': 1},\n",
       " {u'_id': u'41318', u'count': 1},\n",
       " {u'_id': u'263076', u'count': 1},\n",
       " {u'_id': u'128358', u'count': 1},\n",
       " {u'_id': u'4605', u'count': 1},\n",
       " {u'_id': u'41974', u'count': 1},\n",
       " {u'_id': u'77947', u'count': 1},\n",
       " {u'_id': u'2666', u'count': 1},\n",
       " {u'_id': u'232574', u'count': 1},\n",
       " {u'_id': u'9292', u'count': 1},\n",
       " {u'_id': u'24976', u'count': 1},\n",
       " {u'_id': u'1111', u'count': 1},\n",
       " {u'_id': u'28676', u'count': 1},\n",
       " {u'_id': u'38575', u'count': 1},\n",
       " {u'_id': u'119417', u'count': 1},\n",
       " {u'_id': u'232144', u'count': 1},\n",
       " {u'_id': u'00971', u'count': 1},\n",
       " {u'_id': u'5618', u'count': 1},\n",
       " {u'_id': u'79506', u'count': 1},\n",
       " {u'_id': u'114692', u'count': 1},\n",
       " {u'_id': u'000001', u'count': 1},\n",
       " {u'_id': u'97717', u'count': 1},\n",
       " {u'_id': u'123234', u'count': 1},\n",
       " {u'_id': u'549', u'count': 1},\n",
       " {u'_id': u'42524', u'count': 1},\n",
       " {u'_id': u'47602', u'count': 1},\n",
       " {u'_id': u'1234', u'count': 1},\n",
       " {u'_id': u'500368', u'count': 1},\n",
       " {u'_id': u'1243', u'count': 1},\n",
       " {u'_id': u'7770', u'count': 1},\n",
       " {u'_id': u'6656', u'count': 1},\n",
       " {u'_id': u'444786', u'count': 1},\n",
       " {u'_id': u'115443', u'count': 1},\n",
       " {u'_id': u'52799', u'count': 1},\n",
       " {u'_id': u'6770', u'count': 1},\n",
       " {u'_id': u'34121', u'count': 1}]"
      ]
     },
     "execution_count": 227,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create a list of zipcodes without correction\n",
    "correct_zipcodes = dubai_abu_dhabi_correct.aggregate( [ \n",
    "    { \"$match\" : { \"address.postcode\" : { \"$exists\" : 1} } }, \n",
    "    { \"$group\" : { \"_id\" : \"$address.postcode\", \"count\" : { \"$sum\" : 1} } },  \n",
    "    { \"$sort\" : { \"count\" : -1}}\n",
    "] )\n",
    "list(correct_zipcodes)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Correction is successful."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The alternative way to update zip codes is to change the file for converting osm format into json:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "- without correction https://github.com/OlgaBelitskaya/nd002_p3/blob/master/osm_json.py"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- with correction https://github.com/OlgaBelitskaya/nd002_p3/blob/master/osm_json_correct.py"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "z0 = zip_codes(FILE0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "119"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "z0[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "All postcodes: ['0' '0000' '000000' '000001' '00962' '00971' '103711' '108100' '1111'\n",
      " '111695' '113431' '114692' '115443' '119417' '11999' '121641' '1234'\n",
      " '12345' '1243' '125939' '128358' '16095' '20268' '20661' '20767' '2157'\n",
      " '22436' '23117' '231992' '232144' '24857' '24976' '2504' '25494' '2574'\n",
      " '26268' '263076' '2666' '277' '28676' '28818' '32923' '33500' '34121'\n",
      " '34238' '35004' '3541' '38126' '38495' '38575' '392189' '41318' '41974'\n",
      " '42324' '42524' '44263' '444786' '44548' '4599' '46477' '473828' '4758'\n",
      " '47602' '47612' '500368' '502227' '52799' '5280 dubai' '53577' '549'\n",
      " '57566' '60884' '64649' '6656' '6834' '71444' '7770' '77947' '7819'\n",
      " '79506' '811' '81730' '8845' '8988' '9292' '97717' '9978' 'Muhaisnah 4'\n",
      " 'P O BOX 3766' 'P. O. Box 123234' 'P. O. Box 31166' 'P.O. Box 4605'\n",
      " 'P.O. Box 5618, Abu Dhabi, U.A.E' 'P.O. Box 6446' 'P.O. Box 9770'\n",
      " 'PO Box 114822' 'PO Box 118737' 'PO Box 43377' 'PO Box 6770']\n",
      "Expected: ['000001' '00962' '00971' '103711' '108100' '1111' '111695' '113431'\n",
      " '114692' '115443' '119417' '11999' '121641' '1234' '12345' '1243' '125939'\n",
      " '128358' '16095' '20268' '20661' '20767' '2157' '22436' '23117' '231992'\n",
      " '232144' '24857' '24976' '2504' '25494' '2574' '26268' '263076' '2666'\n",
      " '277' '28676' '28818' '32923' '33500' '34121' '34238' '35004' '3541'\n",
      " '38126' '38495' '38575' '392189' '41318' '41974' '42324' '42524' '44263'\n",
      " '444786' '44548' '4599' '46477' '473828' '4758' '47602' '47612' '500368'\n",
      " '502227' '52799' '53577' '549' '57566' '60884' '64649' '6656' '6834'\n",
      " '71444' '7770' '77947' '7819' '79506' '811' '81730' '8845' '8988' '9292'\n",
      " '97717' '9978']\n",
      "Unexpected: ['0' '0000' '000000' '5280 dubai' 'Muhaisnah 4' 'P O BOX 3766'\n",
      " 'P. O. Box 123234' 'P. O. Box 31166' 'P.O. Box 4605'\n",
      " 'P.O. Box 5618, Abu Dhabi, U.A.E' 'P.O. Box 6446' 'P.O. Box 9770'\n",
      " 'PO Box 114822' 'PO Box 118737' 'PO Box 43377' 'PO Box 6770']\n"
     ]
    }
   ],
   "source": [
    "znp0 = np.array(sorted(z0[1]))\n",
    "print \"All postcodes:\", znp0\n",
    "expected0 = np.append(znp0[3:67], znp0[68:87])\n",
    "print \"Expected:\", expected0\n",
    "unexpected00 = np.append(znp0[:3], znp0[87:])\n",
    "unexpected0 = np.insert(unexpected00, 3, znp0[67])\n",
    "print \"Unexpected:\", unexpected0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "'P.O. Box 9770' in correction.keys()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'9770'"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "correction['P.O. Box 9770']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# osm_json_correct.py\n",
    "\n",
    "# Strings with chars that will cause problems as keys\n",
    "problemchars = re.compile(r'[=\\+/&<>;\\'\"\\?%#$@\\,\\. \\t\\r\\n]')\n",
    "\n",
    "# Function for creating nodes\n",
    "def shape_element(element):\n",
    "# Create the empty dictionary for the data in the osm string\n",
    "    node = {}\n",
    "    if element.tag == \"node\" or element.tag == \"way\":\n",
    "        # Create the empty dictionary for the 'address' attributes and the list for the 'nd' attribute\n",
    "        address = {}\n",
    "        nd = []\n",
    "        # Add the type and the id of the element\n",
    "        node[\"type\"] = element.tag\n",
    "        node[\"id\"] = element.attrib[\"id\"]\n",
    "        # Add the tag 'visible'\n",
    "        if \"visible\" in element.attrib.keys():\n",
    "            node[\"visible\"] = element.attrib[\"visible\"]\n",
    "        # Add the geoposition\n",
    "        if \"lat\" in element.attrib.keys():\n",
    "            node[\"pos\"] = [float(element.attrib['lat']), float(element.attrib['lon'])]\n",
    "        # Add the set of the attributes\n",
    "        node[\"created\"] = {\"version\": element.attrib['version'],\n",
    "                            \"changeset\": element.attrib['changeset'],\n",
    "                            \"timestamp\": element.attrib['timestamp'],\n",
    "                            \"uid\": element.attrib['uid'],\n",
    "                            \"user\": element.attrib['user']}\n",
    "        # Analize the problemchars and add address attributes\n",
    "        for tag in element.iter(\"tag\"):\n",
    "            p = problemchars.search(tag.attrib['k'])\n",
    "            if p:\n",
    "                print \"problemchars: \", p.group()\n",
    "                continue\n",
    "            elif tag.attrib['k'][:5] == \"addr:\":\n",
    "                if \":\" in tag.attrib['k'][5:]:\n",
    "                    continue\n",
    "                else:\n",
    "                    # Correction the postcodes\n",
    "                    if tag.attrib['k'] == \"addr:postcode\":\n",
    "                        if tag.attrib['v'] in correction.keys():\n",
    "                            address[tag.attrib['k'][5:]] = update_name(tag.attrib['v'], \n",
    "                                                                       correction)\n",
    "                        else:\n",
    "                            address[tag.attrib['k'][5:]] = tag.attrib['v']\n",
    "                    else:\n",
    "                        address[tag.attrib['k'][5:]] = tag.attrib['v']\n",
    "            else:\n",
    "                node[tag.attrib['k']] = tag.attrib['v']\n",
    "        if address != {}:\n",
    "            node['address'] = address\n",
    "        # Add the 'node_ref' attribute\n",
    "        for tag2 in element.iter(\"nd\"):\n",
    "            nd.append(tag2.attrib['ref'])\n",
    "        if nd != []:\n",
    "            node['node_refs'] = nd\n",
    "        return node\n",
    "    # Skip elements without the tags 'node' or 'way'\n",
    "    else:\n",
    "        return None\n",
    "    \n",
    "# Function for creating the .json file\n",
    "def process_map(file_in, pretty = False):\n",
    "    # Setup the format for output files\n",
    "    file_out = \"{0}.json\".format(file_in)\n",
    "    # Create the empty data\n",
    "    data = []\n",
    "    # Open the osm file and read strings\n",
    "    with codecs.open(file_out, \"w\") as fo:\n",
    "        for _, element in ET.iterparse(file_in):\n",
    "            # Apply the created function 'shape_element'\n",
    "            el = shape_element(element)\n",
    "            if el:\n",
    "                data.append(el)\n",
    "                # Write the element into the json file\n",
    "                if pretty:\n",
    "                    fo.write(json.dumps(el, indent=2)+\"\\n\")\n",
    "                else:\n",
    "                    fo.write(json.dumps(el) + \"\\n\")\n",
    "    return data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "problemchars:   \n",
      "problemchars:   \n",
      "problemchars:   \n",
      "problemchars:   \n",
      "problemchars:   \n",
      "problemchars:   \n",
      "problemchars:   \n",
      "problemchars:   \n",
      "problemchars:   \n",
      "problemchars:   \n",
      "problemchars:   \n",
      "problemchars:   \n",
      "problemchars:   \n",
      "problemchars:   \n",
      "problemchars:   \n",
      "problemchars:   \n",
      "problemchars:  .\n",
      "problemchars:  .\n",
      "problemchars:  .\n",
      "problemchars:  .\n"
     ]
    }
   ],
   "source": [
    "# Create a json file\n",
    "DATA0 = process_map(FILE0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['115443', '34121', '811', '811', '42524', '473828', '473828', '473828', '473828', '3766', '549', '6656', '24976', '6834', '500368', '2666', '47602', '232574', '32923', '9292', '444786', '125939', '128358', '119417', '113431', '77947', '41318', '38495', '1243', '28676', '5618', '121641', '42324', '20268', '2157', '5280', '111695', '53577', '53577', '22436', '232144', '81730', '23117', '44548', '47612', '24857', '97717', '60884', '2574', '57566', 'NA', '114692', '7770', '20661', 'NA', '392189', '46477', '38575', 'NA', '4599', '38126', '231992', '103711', '103711', '00971', '7819', '2504', '26268', '64649', '00962', 'NA', 'NA', '1234', '4758', '44263', '263076', '9978', '71444', '12345', '79506', '108100', '8988', '35004', '33500', '123234', '125939', '118737', '28818', '31166', '6770', '3541', '114822', '9770', '1111', '811', '25494', '41974', '811', '811', '43377', '16095', '6834', '000001', '8845', '502227', '6446', '52799', '277', '20268', '4605', '24857', '20767', '34238', '22436', '22436', '23117', '71444', '24857', '11999', '11999']\n"
     ]
    }
   ],
   "source": [
    "# Check the correction of the postcodes\n",
    "postcodes0 = []\n",
    "for element in DATA0:\n",
    "    address = element.get('address')\n",
    "    if address != None:\n",
    "        postcode = address.get('postcode')\n",
    "        if postcode != None:\n",
    "            postcodes0.append(postcode)\n",
    "print postcodes0"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Correction is successful as well."
   ]
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "hide_input": false,
  "kernelspec": {
   "display_name": "Python [conda root]",
   "language": "python",
   "name": "conda-root-py"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.12"
  },
  "latex_envs": {
   "bibliofile": "biblio.bib",
   "cite_by": "apalike",
   "current_citInitial": 1,
   "eqLabelWithNumbers": true,
   "eqNumInitial": 0
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}