<jupyter_start><jupyter_text>Capstone project for Coursera IBM Data ScienceThis will be used for the Coursera IBM Data Science Capstone Project<jupyter_code># Needed for geocoder.
#!conda install conda-forge::geocoder --yes
import pandas as pd
import numpy as np<jupyter_output><empty_output><jupyter_text>Table of Contents1. Phase 1: Scrape and Transform2. Phase 2: Apply Latitude and Longitude  Phase 1: Scrape and Transform  1. Scrape postal codes and neighborhoods from WikipediaOut of all the tables, the first one with the text "Borough" has the data we need.<jupyter_code>postal_codes_url = "https://en.wikipedia.org/wiki/List_of_postal_codes_of_Canada:_M"
postal_codes_raw = pd.read_html(io=postal_codes_url, match="Borough")[0]    # Grab the first table
postal_codes_raw.head()<jupyter_output><empty_output><jupyter_text>2. Remove 'Not assigned' entries<jupyter_code># Verify that All Neighbourhoods that are 'Not assigned' also do not have a Borough
q = postal_codes_raw[(postal_codes_raw['Neighbourhood'] == 'Not assigned') & (postal_codes_raw['Neighbourhood'] != postal_codes_raw['Borough'])]
q
postal_codes_filter = postal_codes_raw[postal_codes_raw['Borough'] != 'Not assigned']
postal_codes_filter.head()<jupyter_output><empty_output><jupyter_text>3. Consolidate by Postcode<jupyter_code># Group by columns
keycolumns=['Postcode','Borough']
# Preserve the keys with a multiindex
mi = pd.MultiIndex.from_frame(postal_codes_filter[keycolumns])
# Create an independent dataframe with the key as the index and only the neighborhood as the column
postal_codes = postal_codes_filter\
                    .copy()\
                    .set_index(mi)\
                    .drop(columns=keycolumns)
# Concatenate the neighborhoods with a comma
postal_codes_clean = postal_codes.groupby(keycolumns).aggregate(lambda x: x.str.cat(sep=','))
# Move the index back to columns
postal_codes_clean.reset_index(inplace=True,level='Borough')
postal_codes_clean.sort_values('Postcode',inplace=True)
postal_codes_clean.head()<jupyter_output><empty_output><jupyter_text>4. And the answer is:<jupyter_code>postal_codes_clean.shape<jupyter_output><empty_output><jupyter_text>Phase 2: Attach geocoding **Note:** The geocoder package refused to work. Looks like it depends on an API Key that costs money  1. Get the Geocoded Postal Codes<jupyter_code>geocode_url = "https://cocl.us/Geospatial_data"
geodata = pd.read_csv(geocode_url)
geodata.head()<jupyter_output><empty_output><jupyter_text>2. Attach geocoding to Neighbourhoods<jupyter_code># Rename columns, make it an index for the join, sort it
geodata_clean =geodata\
                .rename(columns={'Postal Code': 'Postcode'})\
                .set_index('Postcode')\
                .sort_values('Postcode')
geodata_clean.head()
print(postal_codes_clean.shape)
print(geodata_clean.shape)
postal_codes_final = postal_codes_clean.join(geodata_clean, how='left').reset_index()
postal_codes_final.head()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09946236559139784, 'fraction_numerical': 0.004368279569892473, 'mean_word_length': 6.02122641509434, 'pattern_counts': {'":': 0, '<': 29, '<?xml version=': 0, '>': 29, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '398811', 'license': 'Unlicense', 'n_tokens_mistral': 1021, 'n_tokens_neox': 988, 'n_words': 234, 'path': 'JupyterNoteBooks/Coursera IBM Data Science Capstone NB 03.ipynb', 'repo_name': 'ChaimFrenkel/coursera-ibm-data-capstone', 'size': '2976'}