# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python [conda env:DAND]
#     language: python
#     name: conda-env-DAND-py
# ---

# # Wrangling Vancouver OpenStreetMap

# *Map area chosen - [Vancouver, British Columbia, Canada](https://www.openstreetmap.org/export#map=12/49.2576/-123.1241)*
#
# I've been thinking about visiting Vancouver for a while. It's a beautiful city and has been consistently rated as one of the best places to live in. Exploring, wrangling and analyzing Vancouver's OpenStreetMap (OSM) data would be a good way for me to get to know the city better.

# I used the MongoDB NoSQL database instead of SQL for storing and analyzing the cleaned data as I have quite a bit of experience working with SQL databases, and I wanted to test my new MongoDB skills that I acquired through the Udacity course.

# ## Acquiring and Preparing the Data

# I used the [Overpass API](http://overpass-api.de/query_form.html) form to download a square area of xml data that covered almost all of Vancouver city. The uncompressed file size was 246.7 MB.

# Given how large the original file was, in order to facilitate faster processing of data during exploration, cleanup and analysis, I created two smaller xml datasets. One represeting 1%, and the other 10% of the entire dataset. This helped speed up the overall data processing, especially with respect to testing and debugging the code.

# To better understand the OSM xml data structure, I read through the general background and introductory information available on the [OSM Wiki](http://wiki.openstreetmap.org/).

# ## Data Exploration

# First, I determined how many of the different tags were present in the entire dataset:
#
#     All tags:
#
#     {'member': 22031,
#      'meta': 1,
#      'nd': 1334436,
#      'node': 1077939,
#      'note': 1,
#      'osm': 1,
#      'relation': 2385,
#      'tag': 357077,
#      'way': 189196}

# Then, I displayed examples of all OSM xml elements to get a feel for how the data is structured in this particular dataset. A node element example:
#
#     <node changeset="6532773" id="25250640" lat="49.2028485" lon="-122.9483348" timestamp="2010-12-03T23:19:25Z" uid="355617" user="pnorman" version="17">
#         <tag k="highway" v="traffic_signals" />
#       </node>

# Next, I explored the attributes and tags of two main elements - node and way. Output below:
#
#     Attributes and tags of node and way elements:
#
#     {'node_attr': {'changeset': 1077939,
#                    'id': 1077939,
#                    'lat': 1077939,
#                    'lon': 1077939,
#                    'timestamp': 1077939,
#                    'uid': 1077939,
#                    'user': 1077939,
#                    'version': 1077939},
#      'way_attr': {'changeset': 189196,
#                   'id': 189196,
#                   'timestamp': 189196,
#                   'uid': 189196,
#                   'user': 189196,
#                   'version': 189196}}
#
#     {'node_tag': {'tag': 47048}, 'way_tag': {'nd': 1334436, 'tag': 303477}}

# Curious to know what the top tag keys were, I wrote a code to display the most frequently occuring keys and their values selected at random, which yielded:
#
#     ['building',
#      'highway',
#      'name',
#      'addr:housenumber',
#      'addr:street',
#      'source',
#      'lanes',
#      'amenity',
#      'oneway',
#      'is_in']
#      
#     'building' values in element:
#
#     ['yes', 'house', 'yes', 'yes', 'yes']
#
#     'highway' values in element:
#
#     ['footway', 'bus_stop', 'service', 'service', 'steps']
#
#     'name' values in element:
#
#     ['Sussex Avenue', 'Delano Pizza', 'Freedom', 'Bay 5', 'West 17th Street']
#
#     'addr:housenumber' values in element:
#
#     ['236', '331', '313', '1652', '422']
#
#     'addr:street' values in element:
#
#     ['Sutherland Avenue',
#      'Marine Drive',
#      'East Keith Road',
#      'East 7th Street',
#      'East 12th Street']
#
#     'source' values in element:
#
#     ['Geobase_Import_2009',
#      'PGS',
#      'Geobase_Import_2009',
#      'Geobase_Import_2009',
#      'GeobaseNHN_Import_2009']
#
#     'lanes' values in element:
#
#     ['2', '1', '1', '2', '1']
#
#     'amenity' values in element:
#
#     ['parking', 'restaurant', 'drinking_water', 'parking', 'parking']
#
#     'oneway' values in element:
#
#     ['yes', 'yes', 'yes', 'no', 'no']
#
#     'is_in' values in element:
#
#     ['Vancouver, BC',
#      'Vancouver,British Columbia',
#      'Vancouver, BC',
#      'Vancouver,British Columbia',
#      'New Westminster,British Columbia']

# ## Problems Encountered

# First, I audited the keys so that it can be properly processed or converted to a structure that is compatible with the MongoDB database. There were quite a few keys that did not meet the standard criteria of all letters being lowercase, no colons and other special characters with the exception of underscore. These outliers either had capital letters, one or more colons, or other undesirable characters. All of these problematic keys needed to be fixed.
#
#     tag key types with counts:
#
#     {'caps': 59,
#      'lower_num': 313920,
#      'lower_num_colon': 39066,
#      'lower_num_one_colon_all_caps': 17,
#      'lower_num_one_colon_caps': 4002,
#      'lower_num_one_colon_multi_caps': 2,
#      'others': 0,
#      'problemchars': 11}

# Second, the street types were audited. I found a lot of abbreviations used that needed to be standardized, like - St, Ave, Dr etc.

# Next, I checked for the phone number formatting to ensure it meets the recommendation from the [Translation Bureau](http://www.btb.termiumplus.gc.ca/tpv2guides/guides/wrtps/index-eng.html?lang=eng&lettr=indx_catlog_t&page=9W7A26eVMyvE.html) - NNN-NNN-NNNN. I encountered quite a few different formats that needed cleaning up like - +1 NNN-NNN-NNNN, +1 NNN NNN NNNN, NNNNNNNNNN etc.

# After reviewing the wikis for [Canada](https://en.wikipedia.org/wiki/Postal_codes_in_Canada) and [British Columbia](https://en.wikipedia.org/wiki/List_of_V_postal_codes_of_Canada) postal codes, I set out to audit the postal codes in the data set for non-conformance. There were quite a few postal codes that did not conform to the standard. Some of these had just numbers and no alphabets, some started with 'BC', and some with no space.

# ## Data Wrangling

# The next step that was completed was to address all the problems that surfaced while auditing the dataset as described in the previous step. Followed by conversion of the cleaned data to a JSON file, which can then be imported to MongoDB.

# I created functions to clean up the keys, street names, phone numbers and postal codes. Also, the algorithm was tested and validated using the audit functions. This validated code was then used to wrangle the xml data and then convert the data to a JSON format to enable data import to MongoDB.

# ## Analyzing the data

# I ran a few queries to analyze the data and help answer some of the questions I was interested in.

# What's the dataset size (no. of rows)?
#
#     > db.vancouverosm.count()
#     1267135

# What's the number of node and way elements in the dataset?
#
#     > db.vancouverosm.aggregate([{"$group":{"_id":"$type", "count":{"$sum":1}}},{"$sort":{"_id":1}}])
#     { "_id" : "node", "count" : 1077939 }
#     { "_id" : "way", "count" : 189196 }

# How many users have contributed to this OSM dataset?
#
#     > db.vancouverosm.distinct("created.user").length
#     1006

# How many changes were made to the dataset each year? What was the trend of the no. of changes made over the past few years -
#
#     > db.vancouverosm.aggregate([{$group:{_id: {$substr: ["$created.timestamp", 0, 4]}, count: {$sum: 1}}}, {$sort: {"_id": 1}}])
#     { "_id" : "2007", "count" : 96 }
#     { "_id" : "2008", "count" : 2990 }
#     { "_id" : "2009", "count" : 13682 }
#     { "_id" : "2010", "count" : 51353 }
#     { "_id" : "2011", "count" : 69455 }
#     { "_id" : "2012", "count" : 92236 }
#     { "_id" : "2013", "count" : 12107 }
#     { "_id" : "2014", "count" : 94953 }
#     { "_id" : "2015", "count" : 623976 }
#     { "_id" : "2016", "count" : 288582 }
#     { "_id" : "2017", "count" : 17705 }

# How many different cities were there in this OSM dataset?
#
#     > db.vancouverosm.aggregate([{$match: {"address.city": {"$exists": true}}},{$group: {_id: "$address.city", count: {$sum: 1}}}, {$sort: {"_id": 1}},{'$project':{'count':0}}])
#     { "_id" : "Burnaby" }
#     { "_id" : "Burnaby, BC" }
#     { "_id" : "District of North Vancouver" }
#     { "_id" : "New Westminster" }
#     { "_id" : "North Vancouver" }
#     { "_id" : "North Vancouver City" }
#     { "_id" : "Richmond" }
#     { "_id" : "Richmond BC" }
#     { "_id" : "Richmond, B.C." }
#     { "_id" : "Ricmond" }
#     { "_id" : "Township of Langley" }
#     { "_id" : "Vancouver" }
#     { "_id" : "Vancouver, BC" }
#     { "_id" : "Vancovuer" }
#     { "_id" : "West Vancouver" }
#     { "_id" : "north vancouver" }
#     { "_id" : "vancouver" }

# What are the most common forwarding sortation areas (FSA)?
#
#     > db.vancouverosm.aggregate([{$match:{"address.postcode":{"$exists":true}}}, {$group:{_id: {$substr: ["$address.postcode", 0, 3]}, count: {$sum: 1}}}, {$sort: {count: -1}},{$limit: 10}])
#     { "_id" : "V6M", "count" : 72 } - Vancouver
#     { "_id" : "V5R", "count" : 67 } - Vancouver
#     { "_id" : "V6B", "count" : 61 } - Vancouver
#     { "_id" : "V6A", "count" : 55 } - Vancouver
#     { "_id" : "V6S", "count" : 47 } - Vancouver
#     { "_id" : "V5Z", "count" : 47 } - Vancouver
#     { "_id" : "V5H", "count" : 47 } - Burnaby
#     { "_id" : "V5N", "count" : 44 } - Vancouver
#     { "_id" : "V6H", "count" : 43 } - Vancouver
#     { "_id" : "V6J", "count" : 39 } - Vancouver

# How many apartments, houses, commercial buildings and university buildings were there in this dataset?
#
#     > db.vancouverosm.aggregate([{$match:{"building.building":{"$exists":true},"building.building":{"$in":["commercial","apartments","university","house"]}}}, {$group:{"_id":"$building.building","count":{$sum:1}}},{$sort: {"_id": 1}}])
#     { "_id" : "apartments", "count" : 253 }
#     { "_id" : "commercial", "count" : 77 }
#     { "_id" : "house", "count" : 81 }
#     { "_id" : "university", "count" : 3 }
#     
#     > db.vancouverosm.aggregate([{$match:{"building":{"$exists":true, "$type":"string"},"building":{"$in":["commercial","apartments","university","house"]}}}, {$group:{"_id":"$building","count":{$sum:1}}},{$sort: {"_id": 1}}])
#     { "_id" : "apartments", "count" : 1277 }
#     { "_id" : "commercial", "count" : 297 }
#     { "_id" : "house", "count" : 6863 }
#     { "_id" : "university", "count" : 2 }

# Counts of some interesting places, shops, amenities -
#
#     > db.vancouverosm.aggregate([{"$match":{"amenity":{"$exists":1},"amenity":{"$in":["bar","cafe","cinema","fountain","restaurant","theatre"]}}},{"$group":{"_id":"$amenity", "count":{"$sum":1}}},{"$sort":{"_id":1}}])
#     { "_id" : "bar", "count" : 101 }
#     { "_id" : "cafe", "count" : 473 }
#     { "_id" : "cinema", "count" : 13 }
#     { "_id" : "fountain", "count" : 37 }
#     { "_id" : "restaurant", "count" : 937 }
#     { "_id" : "theatre", "count" : 31 }
#     
#     > db.vancouverosm.aggregate([{"$match":{"shop":{"$exists":1},"shop":{"$in":["art","books","deli","music","outdoor","tea"]}}},{"$group":{"_id":"$shop", "count":{"$sum":1}}},{"$sort":{"_id":1}}])
#     { "_id" : "art", "count" : 44 }
#     { "_id" : "books", "count" : 31 }
#     { "_id" : "deli", "count" : 15 }
#     { "_id" : "music", "count" : 8 }
#     { "_id" : "outdoor", "count" : 12 }
#     { "_id" : "tea", "count" : 12 }
#     
#     > db.vancouverosm.aggregate([{"$match":{"tourism":{"$exists":1},"tourism":{"$in":["artwork","attraction","gallery","hostel","hotel","motel","museum","picnic_site","viewpoint"]}}},{"$group":{"_id":"$tourism", "count":{"$sum":1}}},{"$sort":{"_id":1}}])
#     { "_id" : "artwork", "count" : 71 }
#     { "_id" : "attraction", "count" : 34 }
#     { "_id" : "gallery", "count" : 6 }
#     { "_id" : "hostel", "count" : 16 }
#     { "_id" : "hotel", "count" : 93 }
#     { "_id" : "motel", "count" : 14 }
#     { "_id" : "museum", "count" : 16 }
#     { "_id" : "picnic_site", "count" : 11 }
#     { "_id" : "viewpoint", "count" : 66 }
#

# ## Possible Improvements

# After exploring and auditing the dataset, one important improvement that can be made to the data is to cleanup the 'opening_hours' tag. As you can see below, the formating of opening_hours is all over the place, and it would be hard to analyze this non-standard data.
#
#     > db.vancouverosm.aggregate([{$match: {"opening_hours": {"$exists": true}}},{"$project":{"opening_hours":1}},{"$sample":{"size":10}},{"$project":{"_id":0}}])
#     { "opening_hours" : "Mo-fr 7-19;sa 9-18;su 10-18" }
#     { "opening_hours" : "24/7" }
#     { "opening_hours" : "Mo-Th 11:00-21:00; Fr-Su 11:00-22:00" }
#     { "opening_hours" : "Su-Th 10:00-02:00; Fr-Sa 10:00-05:00" }
#     { "opening_hours" : "Mo-Su 10:00-23:00" }
#     { "opening_hours" : "Mo-Su 07:00-19:00" }
#     { "opening_hours" : "M-F 7-7, Sat 10-6, Sun 11-5" }
#     { "opening_hours" : "12:00-21:00" }
#     { "opening_hours" : "Mo-fr 6-20;sa-su 7-19" }
#     { "opening_hours" : "Mo-Fr 11:30-14:30,17:30-24:00; Sa-Su 12:00-15:00,17:30-24:00" }
#     
# But, there would be difficulties while making this improvement. First, in deciding the standard format that is acceptable. Then, to identify all of the non-conforming formats and come up with an algorithm to clean up the data, which in my opinion will be complex and time consuming.
#
# If the clean up is successful, then we can ask some interesting questions like - what places are open 24/7 or how late are the bars and restaurants open?
#
# Another tag that can be cleaned up is 'addr:city'. As you might have noticed in one of the query results above to answer the question - 'How many different cities were there in this OSM dataset?', this data has not been standardized, and hence is not efficient for performing data analysis without manual interference. Once the abnormalities are identified, we can clean up the data and answer some interesting questions like - how many restaurants are there in these cities?

# ## References not mentioned above:
#
# https://docs.python.org/2/library/xml.etree.elementtree.html?highlight=elementtree#module-xml.etree.ElementTree
#
# https://docs.mongodb.com/
#
# http://www.regexr.com/

StarcoderdataJupyterScriptsDedupFiltered