<jupyter_start><jupyter_text>OpenStreetMap Data Wrangling using Python and SQL Map AreaAlbany, NY, United Stateshttp://www.openstreetmap.org/exportmap=11/42.7805/-73.8501 I chose this city, as I have been living in this area for last five years and would like an opportunity to contribute to its improvement in the openstreetmap.org project. I have chosen to explore the suburbs of Albany, NY rather than the downtown area. Problems encountered in the mapAfter downloading the dataset and running it against a provisional data.py file, I noticed the following main problems with the dataset, as follows:  Abbreviated and nonuniform street names ('St.', 'St', 'street', 'Ave', 'Ave.' etc).  Incomplete street names (Sparrowbush, Deltour etc.).  Inconsistent and incorrectly entered postal codes (12303-9998, 12180-8368 etc.).  Incorrect city names (Schenectary, Rexrford etc.).   Abbreviated and incomplete street namesThe osm file revealed basic street name inconsistencies caused due to abbreviations or use of small case letters. Some street names were incorrectly entered and were missing street type, for example Sparrowbush instead of Sparrowbush Road. An attempt was made in audit.py to fix these problems by replacing "St." with "Street", "road" with "Road", "rt" with "Route" etc. A list of expected street types like Street, Avenue, Circle etc. was created which do not need to be cleaned. If the last word of a street type was not in the expected list, they were stored in a separate dictionary. It gave an idea about what inconsistent street types are present in the data. The osm file was scanned for tags that were street names (type="addr:street") and the old abbreviated street name was mapped to a new better one using a mapping dictionary. A snippet of the code to update street name is given below.<jupyter_code>def update_name(name, mapping):    
    n = street_type_re.search(name) #get the street type
    if n:
        n = n.group()
    for m in mapping: # check if a better name exists for a street type in mapping dict
        if n == m:
            name = name[:-len(n)] + mapping[m] #replace old name with new
    return name<jupyter_output><empty_output><jupyter_text>Incorrect postal codesMost postal codes in the Capital District region are all five digits for this map, but some are 9 digits having trailing digits (12180-8368) after the first five digits. Wrote some code to drop the trailing digits and the  '-' after the five digit postal code. One particular postal code 1220y stood out as incorrectly entered. I needed to find out what address it belonged to in order to replace it with the correct code. It belonged to 1, State Street, Albany which has zip code 12207.<jupyter_code>def update_postcode(postcode):   #update postal code to remove trailing digits
    if len(postcode)==10 and postcode[5] == '-':
        return postcode[:5]
    elif postcode == "1220y":# update the one instance of incorrectly entered postal code for this extract
        return "12207"
    else:
        return postcode<jupyter_output><empty_output><jupyter_text>Incorrect City NamesWhile most city names in this dataset were fine, some were incorrectly spelled. Like Schenectary instead of Schenectady.<jupyter_code>def update_city(name, mapping_city):   
    for m in mapping_city:        #replace incorrect name with correct one from mapping dict
        if name == m:            
            name = mapping_city[m]
    return name<jupyter_output><empty_output><jupyter_text>Data Overview and StatisticsThe data from the OSM XML file was converted to tabular form which could be written into CSV files. These CSV files could easily be imported to SQL tables.File Sizes<jupyter_code>import os
print('The albany.osm file is {} MB'.format(os.path.getsize('albany.osm')/1.0e6))
print('The osm.db file is {} MB'.format(os.path.getsize('osm.db')/1.0e6))
print('The nodes.csv file is {} MB'.format(os.path.getsize('nodes.csv')/1.0e6))
print('The nodes_tags.csv file is {} MB'.format(os.path.getsize('nodes_tags.csv')/1.0e6))
print('The ways.csv file is {} MB'.format(os.path.getsize('ways.csv')/1.0e6))
print('The ways_tags.csv is {} MB'.format(os.path.getsize('ways_tags.csv')/1.0e6))
print('The ways_nodes.csv is {} MB'.format(os.path.getsize('ways_nodes.csv')/1.0e6))<jupyter_output>The albany.osm file is 95.825419 MB
The osm.db file is 67.355648 MB
The nodes.csv file is 34.323738 MB
The nodes_tags.csv file is 2.893896 MB
The ways.csv file is 2.874296 MB
The ways_tags.csv is 8.955983 MB
The ways_nodes.csv is 11.009704 MB
{'fraction_non_alphanumeric': 0.07970057243505063, 'fraction_numerical': 0.030603258476442095, 'mean_word_length': 4.288707799767171, 'pattern_counts': {'":': 1, '<': 16, '<?xml version=': 0, '>': 16, 'https://': 0, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '128750', 'license': 'Apache-2.0', 'n_tokens_mistral': 1414, 'n_tokens_neox': 1276, 'n_words': 583, 'path': 'p2/OpenStreetMap Data Wrangling.ipynb', 'repo_name': 'priyankaswadi/Udacity-Data-Analyst-Nanodegree', 'size': '4542'}