02 Data cleaning and output.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# This notebook is used to transform the data and output into SQL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import csv\n",
    "import codecs\n",
    "import cerberus\n",
    "import schema\n",
    "import re\n",
    "import xml.etree.cElementTree as ET\n",
    "from pprint import pprint as pp\n",
    "\n",
    "#OSM_PATH = \"houston_texas.osm\"\n",
    "OSM_PATH = \"sample.osm\"\n",
    "\n",
    "NODES_PATH = \"nodes.csv\"\n",
    "NODE_TAGS_PATH = \"nodes_tags.csv\"\n",
    "WAYS_PATH = \"ways.csv\"\n",
    "WAY_NODES_PATH = \"ways_nodes.csv\"\n",
    "WAY_TAGS_PATH = \"ways_tags.csv\"\n",
    "\n",
    "SCHEMA = schema.schema\n",
    "\n",
    "# Make sure the fields order in the csvs matches the column order in the sql table schema\n",
    "NODE_FIELDS = ['id', 'lat', 'lon', 'user', 'uid', 'version', 'changeset', 'timestamp']\n",
    "NODE_TAGS_FIELDS = ['id', 'key', 'value', 'type']\n",
    "WAY_FIELDS = ['id', 'user', 'uid', 'version', 'changeset', 'timestamp']\n",
    "WAY_TAGS_FIELDS = ['id', 'key', 'value', 'type']\n",
    "WAY_NODES_FIELDS = ['id', 'node_id', 'position']\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "LOWER_COLON = re.compile(r'^([a-z]|_)+:([a-z]|_)+')\n",
    "PROBLEMCHARS = re.compile(r'[=\\+/&<>;\\'\"\\?%#$@\\,\\. \\t\\r\\n]')\n",
    "END_NUMBER = re.compile(r'[0-9]$')\n",
    "\n",
    "# This function is used to read tags\n",
    "def tag_value(tag,element):\n",
    "    '''\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\n",
    "        is not present.\n",
    "    ''' \n",
    "    m = PROBLEMCHARS.search(tag.attrib['k'])\n",
    "    n = END_NUMBER.search(tag.attrib['k'])\n",
    "    att={}\n",
    "    if (not m) and (not n): # Ignore the tag if the \"k\" value contains problematic characters\n",
    "        att['id']=element.attrib['id']\n",
    "        att['value']=tag.attrib['v']\n",
    "        k=tag.attrib['k'].lower() # change the k\" value to all lower cases\n",
    "        '''\n",
    "        if the tag \"k\" value contains a \":\" \n",
    "            the characters before the \":\" should be set as the tag type\n",
    "            the characters after the \":\" should be set as the tag key\n",
    "        if there are additional \":\" in the \"k\" value \n",
    "            ignore them and keep as part of the tag key. For example:\n",
    "        '''\n",
    "        m = LOWER_COLON.search(k)\n",
    "        if m:\n",
    "            words=k.split(':',1)\n",
    "            att['type']=words[0]\n",
    "            att['key']=words[1]\n",
    "        else:\n",
    "            att['key']=k\n",
    "            att['type']='regular'\n",
    "        '''\n",
    "        The tiger:zip_left will read the leftmost zipcode\n",
    "        The tiger:zip_right will read rightmose zipcode\n",
    "        '''\n",
    "        if att['key']=='zip_left':\n",
    "            try:\n",
    "                att['value']=int(att['value'])\n",
    "                # if the zipcode is not for Houston area, print it out\n",
    "                if att['value'] < 77000 or att['value'] > 77999:\n",
    "                    print 'This zipcode is out of Greater Houston Area: ' + att['value'] \n",
    "            except ValueError:\n",
    "                att['value']=re.split('[:;]',att['value']) [0]\n",
    "                \n",
    "        if att['key']=='zip_left':\n",
    "            try:\n",
    "                att['value']=int(att['value'])\n",
    "                # if the zipcode is not for Houston area, print it out\n",
    "                if att['value'] < 77000 or att['value'] > 77999:\n",
    "                    print 'This zipcode is out of Greater Houston Area: ' + att['value'] \n",
    "            except ValueError:\n",
    "                att['value']=re.split('[:;]',att['value']) [0]\n",
    "        '''\n",
    "        If the tag value is 'man_made':\n",
    "        Change the key to 'amenity', type to 'man_made'\n",
    "        '''\n",
    "        if att['key']=='man_made':\n",
    "            att['key']='amenity'\n",
    "            att['type']='man_made'\n",
    "        return att\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "street_type_re = re.compile(r'\\S+\\.?$', re.IGNORECASE)\n",
    "street_type_prefix = re.compile(r'^([NSWE])\\s', re.IGNORECASE)\n",
    "street_type_suffix = re.compile(r'\\s([NSWE])$', re.IGNORECASE)\n",
    "street_type_number = re.compile(r'\\s#?\\d+[a-zA-Z]?$', re.IGNORECASE)\n",
    "\n",
    "mapping = { \"Cir\": \"Circle\",\"Ct\": \"Court\",'Dr':'Drive','Ln':'Lane','Fwy':'Freeway',\n",
    "           'Pkwy':\"Parkway\",'Pky':\"Parkway\",\"Ave\": \"Avenue\", 'Blvd':'Boulevard','Hwy':'Highway',\n",
    "           'Rd':'Road','St':'Street','Pl':'Place','Trl':\"Trail\",'Blvd.':'Boulevard',\n",
    "           'Byp':'Bypass'}\n",
    "\n",
    "direction={'N':'North','S':'South','W':'West','E':'East'}\n",
    "\n",
    "def update_name(name, mapping):\n",
    "    #if the name begins with a abbreviated direction, correct it\n",
    "    p = street_type_prefix.search(name)\n",
    "    if p:\n",
    "        subwords=name.split()\n",
    "        subwords[0]=direction[subwords[0]]\n",
    "        name=' '.join(subwords)\n",
    "        \n",
    "    s = street_type_suffix.search(name)\n",
    "    n = street_type_number.search(name)\n",
    "    #If the name ends with a dirction or a number, check the second last word\n",
    "    if s or n:\n",
    "        #if the name ends with a abbreviated direction, correct it\n",
    "        d=None #abbrevated direction\n",
    "        number=None #ending number\n",
    "        if s:\n",
    "            subwords=name.split()\n",
    "            d=subwords[-1]\n",
    "            d=direction[d]\n",
    "            name=' '.join(subwords[0:-1])\n",
    "        #after removing the suffix, checking the number\n",
    "        if street_type_number.search(name):\n",
    "            number = name.split()[-1]\n",
    "            name=(' ').join(name.split()[:-1])\n",
    "        m=street_type_re.search(name)\n",
    "        if m:\n",
    "            subwords=name.split()\n",
    "            lastword=subwords[-1]\n",
    "            if lastword in mapping:\n",
    "                subwords[-1]=mapping[lastword]\n",
    "                name=' '.join(subwords)\n",
    "        if number:\n",
    "            name=' '.join([name,number])\n",
    "        if d:\n",
    "            name=' '.join([name,d])\n",
    "    else:\n",
    "        if street_type_re.search(name):\n",
    "            subwords=name.split()\n",
    "            lastword=subwords[-1]\n",
    "            if lastword in mapping:\n",
    "                subwords[-1]=mapping[lastword]\n",
    "                name=' '.join(subwords)\n",
    "    return name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'NODE_FIELDS' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-1-a454c585aa4e>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0;32mdef\u001b[0m \u001b[0mshape_element\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0melement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mnode_attr_fields\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mNODE_FIELDS\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mway_attr_fields\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mWAY_FIELDS\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      2\u001b[0m     \u001b[0;34m\"\"\"Clean and shape node or way XML element to Python dict\"\"\"\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      3\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      4\u001b[0m     \u001b[0mnode_attribs\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m{\u001b[0m\u001b[0;34m}\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      5\u001b[0m     \u001b[0mway_attribs\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m{\u001b[0m\u001b[0;34m}\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mNameError\u001b[0m: name 'NODE_FIELDS' is not defined"
     ]
    }
   ],
   "source": [
    "def shape_element(element, node_attr_fields=NODE_FIELDS, way_attr_fields=WAY_FIELDS):\n",
    "    \"\"\"Clean and shape node or way XML element to Python dict\"\"\"\n",
    "\n",
    "    node_attribs = {}\n",
    "    way_attribs = {}\n",
    "    way_nodes = []\n",
    "    tags=[]\n",
    "\n",
    "    # YOUR CODE HERE\n",
    "    if element.tag == 'node':\n",
    "        valid = True # If we should output this element\n",
    "        if len(element):\n",
    "            for tag in element.iter('tag'):\n",
    "                if tag.attrib['k'].lower()=='fixme':\n",
    "                    valid=False #discard the node with fixme attributes\n",
    "                    break \n",
    "                att=tag_value(tag,element)\n",
    "                if not att: # Ignore the tag if the \"k\" value contains problematic characters\n",
    "                    continue\n",
    "                if att['key']=='street':\n",
    "                    oldvalue=att['value']\n",
    "                    att['value']=update_name(oldvalue,mapping)           \n",
    "                tags.append(att)\n",
    "        if valid:           \n",
    "            for field in node_attr_fields:\n",
    "                node_attribs[field]=element.attrib[field]\n",
    "            return {'node': node_attribs, 'node_tags': tags}\n",
    "    elif element.tag == 'way':\n",
    "        valid = True # If we should output this element\n",
    "        if len(element):\n",
    "            n=0\n",
    "            for elem in element:\n",
    "                if elem.tag=='tag':\n",
    "                    if elem.attrib['k']=='fixme':\n",
    "                        valid=False #discard the way with fixme attributes\n",
    "                        break\n",
    "                    att=tag_value(elem,element)\n",
    "                    if not att: # Ignore the tag if the \"k\" value contains problematic characters\n",
    "                        continue\n",
    "                    if att['key']=='street':\n",
    "                        oldvalue=att['value']\n",
    "                        att['value']=update_name(oldvalue,mapping) \n",
    "                    tags.append(att)\n",
    "                '''\n",
    "                create way_nodes\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\n",
    "                '''\n",
    "                if elem.tag=='nd':\n",
    "                    att={}\n",
    "                    att['id']=element.attrib['id']\n",
    "                    id_number=element.attrib['id']\n",
    "                    att['node_id']=elem.attrib['ref']\n",
    "                    att['position']=n\n",
    "                    n+=1\n",
    "                    way_nodes.append(att)  \n",
    "        if valid:\n",
    "            '''\n",
    "            If both Tiger GPS address tag and name tag exist, ignore the Tiger GPS tag\n",
    "            If only Tiger GPS address tag exists, compile the street name\n",
    "            ''' \n",
    "            name_base,name_type,name_direction_prefix,name_direction_suffix=None,None,None,None\n",
    "            ifname = False\n",
    "            iftiger= False\n",
    "            remove=[] # This list collects the name tags from Tiger GPS\n",
    "            for i in tags:\n",
    "                k = i['key'] \n",
    "                if k == 'name':\n",
    "                    ifname= True\n",
    "                elif k == 'name_base':\n",
    "                    name_base=re.split('[:;]',i['value'])[0] \n",
    "                    iftiger= True\n",
    "                    remove.append(i)\n",
    "                elif k == 'name_type':\n",
    "                    name_type=re.split('[:;]',i['value'])[0] \n",
    "                    remove.append(i)\n",
    "                elif k == 'name_direction_prefix':\n",
    "                    name_direction_prefix=re.split('[:;]',i['value'])[0] \n",
    "                    remove.append(i)\n",
    "                elif k == 'name_direction_suffix':\n",
    "                    name_direction_suffix=re.split('[:;]',i['value'])[0] \n",
    "                    remove.append(i)\n",
    "            if (not ifname) and iftiger: \n",
    "                '''\n",
    "                when there is only Tiger GPS street name:\n",
    "                we compile the seperate items,\n",
    "                update the name and set the tpye to 'tiger'\n",
    "                '''\n",
    "                att={}\n",
    "                att['id']=id_number\n",
    "                att['key']='street'\n",
    "                streetlist=[name_direction_prefix,name_base,name_type,name_direction_suffix]\n",
    "                att['value']=' '.join(filter(None,streetlist))\n",
    "                att['value']= update_name(att['value'],mapping) \n",
    "                att['type']='tiger'\n",
    "                tags.append(att)\n",
    "            if ifname and iftiger: \n",
    "                '''\n",
    "                when there are both name and Tiger GPS street name:\n",
    "                remove the Tiger GPS items,\n",
    "                change the name tag to street tag and update the name\n",
    "                '''\n",
    "                for i in remove:\n",
    "                    tags.remove(i)\n",
    "                for i in tags:\n",
    "                    if i['key'] == 'name':\n",
    "                        i['key'] = 'street' \n",
    "                        i['type']= 'addr' \n",
    "                        i['value']= update_name(i['value'],mapping)                       \n",
    "            for field in way_attr_fields:\n",
    "                way_attribs[field]=element.attrib[field]\n",
    "            return {'way': way_attribs, 'way_nodes': way_nodes, 'way_tags': tags}\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def get_element(osm_file, tags=('node', 'way', 'relation')):\n",
    "    \"\"\"Yield element if it is the right type of tag\"\"\"\n",
    "\n",
    "    context = ET.iterparse(osm_file, events=('start', 'end'))\n",
    "    _, root = next(context)\n",
    "    for event, elem in context:\n",
    "        if event == 'end' and elem.tag in tags:\n",
    "            yield elem\n",
    "            root.clear()\n",
    "\n",
    "\n",
    "def validate_element(element, validator, schema=SCHEMA):\n",
    "    \"\"\"\n",
    "    Utilize a schema and validation library to ensure the transformed data is in the correct format\n",
    "    Raise ValidationError if element does not match schema\n",
    "    \"\"\"\n",
    "    if validator.validate(element, schema) is not True:\n",
    "        field, errors = next(validator.errors.iteritems())\n",
    "        message_string = \"\\nElement of type '{0}' has the following errors:\\n{1}\"\n",
    "        error_string = pprint.pformat(errors)\n",
    "        \n",
    "        raise Exception(message_string.format(field, error_string))\n",
    "\n",
    "\n",
    "class UnicodeDictWriter(csv.DictWriter, object):\n",
    "    \"\"\"\n",
    "    Write each data structure to the appropriate .csv files\n",
    "    Extend csv.DictWriter to handle Unicode input\n",
    "    \"\"\"\n",
    "\n",
    "    def writerow(self, row):\n",
    "        super(UnicodeDictWriter, self).writerow({\n",
    "            k: (v.encode('utf-8') if isinstance(v, unicode) else v) for k, v in row.iteritems()\n",
    "        })\n",
    "\n",
    "    def writerows(self, rows):\n",
    "        for row in rows:\n",
    "            self.writerow(row)\n",
    "\n",
    "\n",
    "# ================================================== #\n",
    "#               Main Function                        #\n",
    "# ================================================== #\n",
    "def process_map(file_in, validate):\n",
    "    \"\"\"Iteratively process each XML element and write to csv(s)\"\"\"\n",
    "\n",
    "    with codecs.open(NODES_PATH, 'w') as nodes_file, \\\n",
    "         codecs.open(NODE_TAGS_PATH, 'w') as nodes_tags_file, \\\n",
    "         codecs.open(WAYS_PATH, 'w') as ways_file, \\\n",
    "         codecs.open(WAY_NODES_PATH, 'w') as way_nodes_file, \\\n",
    "         codecs.open(WAY_TAGS_PATH, 'w') as way_tags_file:\n",
    "\n",
    "        nodes_writer = UnicodeDictWriter(nodes_file, NODE_FIELDS)\n",
    "        node_tags_writer = UnicodeDictWriter(nodes_tags_file, NODE_TAGS_FIELDS)\n",
    "        ways_writer = UnicodeDictWriter(ways_file, WAY_FIELDS)\n",
    "        way_nodes_writer = UnicodeDictWriter(way_nodes_file, WAY_NODES_FIELDS)\n",
    "        way_tags_writer = UnicodeDictWriter(way_tags_file, WAY_TAGS_FIELDS)\n",
    "\n",
    "        nodes_writer.writeheader()\n",
    "        node_tags_writer.writeheader()\n",
    "        ways_writer.writeheader()\n",
    "        way_nodes_writer.writeheader()\n",
    "        way_tags_writer.writeheader()\n",
    "\n",
    "        validator = cerberus.Validator()\n",
    "\n",
    "        for element in get_element(file_in, tags=('node', 'way')):\n",
    "            el = shape_element(element)\n",
    "            if el:\n",
    "                if validate is True:\n",
    "                    validate_element(el, validator)\n",
    "\n",
    "                if element.tag == 'node':\n",
    "                    nodes_writer.writerow(el['node'])\n",
    "                    node_tags_writer.writerows(el['node_tags'])\n",
    "                elif element.tag == 'way':\n",
    "                    ways_writer.writerow(el['way'])\n",
    "                    way_nodes_writer.writerows(el['way_nodes'])\n",
    "                    way_tags_writer.writerows(el['way_tags'])                   \n",
    "\n",
    "if __name__ == '__main__':\n",
    "    # Note: Validation is ~ 10X slower. \n",
    "    process_map(OSM_PATH, validate= False)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "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
}