# import libraries
import requests
import lxml.html as lh
import pandas as pd
import numpy as np

url = 'https://en.wikipedia.org/wiki/List_of_postal_codes_of_Canada:_M'

# create a handle page to handle the contents of the website
page = requests.get(url)

# store the contents of the website under doc
doc = lh.fromstring(page.content)

# parse data that are stored between <tr>...</tr> of HTML
tr_elements = doc.xpath('//tr')

# check the length of the first 10 rows
[len(T) for T in tr_elements[:10]]

# parse the first row as the header
tr_elements = doc.xpath('//tr')

# create an empty list
col = []
i = 0

# for each row, store each first element (header) and an empty list
for t in tr_elements[0]:
    i+=1
    name = t.text_content()
    print('%d:"%s"'%(i,name))
    col.append((name,[]))

# since the first row is the header, data is stored on the second row onwards
for j in range(1, len(tr_elements)):
    #T is our j'th row
    T = tr_elements[j]
    
    # if row is not of size 3, the //tr data is not from our table 
    if len(T)!= 3:
        break
    
    # i is the index of our column
    i = 0
    
    # iterate through each element of the row
    for t in T.iterchildren():
        data = t.text_content() 
        # check if row is empty
        if i > 0:
        # convert any numerical value to integers
            try:
                data = int(data)
            except:
                pass
        # append the data to the empty list of the i'th column
        col[i][1].append(data)
        # increment i for the next column
        i+=1

[len(C) for (title, C) in col]

Dict = {title:column for (title, column) in col}
df = pd.DataFrame(Dict)
df.head()

df.columns = ['Postcode', 'Borough', 'Neighborhood']
cols = df.columns.tolist()
cols

df = df.replace('\n', ' ', regex=True)
df.head()

df.drop(df.index[df['Borough'] == 'Not assigned'], inplace=True)
# reset the index and drop the previous index
df = df.reset_index(drop=True)
df.head(10)

df = df.groupby(['Postcode', 'Borough'])['Neighborhood'].apply(','.join).reset_index()
df.columns = ['Postcode', 'Borough', 'Neighborhood']
df.head(10)

df['Neighborhood'] = df['Neighborhood'].str.strip()

df.loc[df['Neighborhood'] == 'Not assigned', 'Neighborhood'] = df['Borough']

# shape of the dataframe
df.shape

# save this file as a csv
df.to_csv(r'df_can.csv')

link = 'http://cocl.us/Geospatial_data'
df1 = pd.read_csv(link)
df1.head()

# shape of the data
df1.shape

df1.columns = ['Postcode', 'Latitude', 'Longitude']

cols = df1.columns.tolist()
cols

df = pd.read_csv('df_can.csv', index_col=0)
df.head()

df_new = pd.merge(df, df1, on='Postcode')
df_new.head()

# save the file as a csv
df_new.to_csv(r'df_final.csv')

import numpy as np # library to handle data in a vectorized manner

import pandas as pd # library for data analsysis
pd.set_option('display.max_columns', None)
pd.set_option('display.max_rows', None)

import json # library to handle JSON files

#!conda install -c conda-forge geopy --yes # uncomment this line if you haven't completed the Foursquare API lab
from geopy.geocoders import Nominatim # convert an address into latitude and longitude values

import requests # library to handle requests
from pandas.io.json import json_normalize # tranform JSON file into a pandas dataframe

# Matplotlib and associated plotting modules
import matplotlib.cm as cm
import matplotlib.colors as colors

# import k-means from clustering stage
from sklearn.cluster import KMeans

#!conda install -c conda-forge folium=0.5.0 --yes # uncomment this line if you haven't completed the Foursquare API lab
import folium # map rendering library

print('Libraries imported.')

neighborhoods = pd.read_csv('df_final.csv', index_col=0)
neighborhoods.head()

print('The dataframe has {} boroughs and {} neighborhoods'.format(
    len(neighborhoods['Borough'].unique()), neighborhoods.shape[0]
))

address = 'Toronto, Canada'

geolocator = Nominatim(user_agent='can_explorer')
location = geolocator.geocode(address)
latitude = location.latitude
longitude = location.longitude
print('The geographical coordinates of Toronto are {}, {}'.format(latitude, longitude))

# create a map of Toronto 
map_toronto = folium.Map(location=[latitude, longitude], zoom_start=10)

# add markers to map
for lat, lng, borough, neighborhood in zip(neighborhoods['Latitude'], neighborhoods['Longitude'], neighborhoods['Borough'], neighborhoods['Neighborhood']):
    label = '{}, {}'.format(neighborhood, borough)
    label = folium.Popup(label, parse_html=True)
    folium.CircleMarker(
        [lat, lng],
        radius=5,
        popup=label,
        color='blue',
        fill=True,
        fill_color='#3186cc',
        fill_opacity=0.7,
        parse_html=False).add_to(map_toronto)  
    
map_toronto

# limit the number of venues returned by the foursquare API
LIMIT = 100

# define Foursquare Credentials
CLIENT_ID = '**********' 
CLIENT_SECRET = '**********'
VERSION = '20191219'

def getNearbyVenues(names, latitudes, longitudes, radius=500):
    
    venues_list=[]
    for name, lat, lng in zip(names, latitudes, longitudes):
        print(name)
            
        # create the API request URL
        url = 'https://api.foursquare.com/v2/venues/explore?&client_id={}&client_secret={}&v={}&ll={},{}&radius={}&limit={}'.format(
            CLIENT_ID, 
            CLIENT_SECRET, 
            VERSION, 
            lat, 
            lng, 
            radius, 
            LIMIT)
            
        # make the GET request
        results = requests.get(url).json()["response"]['groups'][0]['items']
        
        # return only relevant information for each nearby venue
        venues_list.append([(
            name, 
            lat, 
            lng, 
            v['venue']['name'], 
            v['venue']['location']['lat'], 
            v['venue']['location']['lng'],  
            v['venue']['categories'][0]['name']) for v in results])

    nearby_venues = pd.DataFrame([item for venue_list in venues_list for item in venue_list])
    nearby_venues.columns = ['Neighborhood', 
                  'Neighborhood Latitude', 
                  'Neighborhood Longitude', 
                  'Venue', 
                  'Venue Latitude', 
                  'Venue Longitude', 
                  'Venue Category']
    
    return(nearby_venues)

toronto_venues = getNearbyVenues(names=neighborhoods['Neighborhood'],
                                 latitudes=neighborhoods['Latitude'],
                                 longitudes=neighborhoods['Longitude'])

# glimpse of the new dataset
toronto_venues.head()

# dimensions of the new datset
toronto_venues.shape

toronto_venues.groupby('Neighborhood').count()

# one hot encoding
toronto_onehot = pd.get_dummies(toronto_venues[['Venue Category']], prefix='', prefix_sep='')

# add neighborhood column back to the dataframe
toronto_onehot['Neighborhood'] = toronto_venues['Neighborhood']

# move neighborhood column to the first column
fixed_columns = [toronto_onehot.columns[-1]] + list(toronto_onehot.columns[:-1])
toronto_onehot = toronto_onehot[fixed_columns]

toronto_onehot.head()

# dimensions of the new dataframe
toronto_onehot.shape

toronto_grouped = toronto_onehot.groupby('Neighborhood').mean().reset_index()
toronto_grouped