<jupyter_start><jupyter_text>Import necessary libraries<jupyter_code>import requests # library to handle requests
import pandas as pd # library for data analsysis
import numpy as np # library to handle data in a vectorized manner
import random # library for random number generation
from bs4 import BeautifulSoup # library for web scrapping  

!conda install -c conda-forge geopy --yes 
from geopy import geocoder
from geopy.geocoders import Nominatim # module to convert an address into latitude and longitude values

# libraries for displaying images
from IPython.display import Image 
from IPython.core.display import HTML 
    
# tranforming json file into a pandas dataframe library
from pandas.io.json import json_normalize

#!conda install -c conda-forge folium=0.5.0 --yes
import folium # plotting library

print('Folium installed')
print('Libraries imported.')

!pip -q install geopy
print('geopy installed...')


# install the Geocoder
# !pip -q install geocoder
# import geocoder<jupyter_output>Collecting package metadata: done
Solving environment: / 
The environment is inconsistent, please check the package plan carefully
The following packages are causing the inconsistency:

  - defaults/osx-64::anaconda==5.0.1=py36h6e48e2d_1
-<jupyter_text>Define Foursquare Credentials and VersionMake sure that you have created a Foursquare developer account and have your credentials handy<jupyter_code>CLIENT_ID = 'R01LINGO2WC45KLRLKT3ZHU2QENAO2IPRK2N2ELOHRNK4P3K' # your Foursquare ID
CLIENT_SECRET = '<KEY>' # your Foursquare Secret

VERSION = '20180604'
LIMIT = 30

print('Your credentails:')
print('CLIENT_ID: ' + CLIENT_ID)
print('CLIENT_SECRET:' + CLIENT_SECRET)<jupyter_output>Your credentails:
CLIENT_ID: R01LINGO2WC45KLRLKT3ZHU2QENAO2IPRK2N2ELOHRNK4P3K
CLIENT_SECRET:4JT1TWRMXMPLX5IOKNBAFU3L3ARXK4D5JJDPFK1CLRZM2ZVW<jupyter_text>Preprocessing  The preprocessing will be divided into two parts  - **Part 1**: Preprocessing a real world data set from Kaggle showing the London Crimes from 2008 to 2016 - **Part 2**: Scraping the co-ordinates of the different Boroughs in London from a Wikipedia page   Part 1: Preprocessing a real world data set from Kaggle showing the London Crimes from 2008 to 2016  London Crime Data About this file- lsoa_code: code for Lower Super Output Area in Greater London.- borough: Common name for London borough.- major_category: High level categorization of crime- minor_category: Low level categorization of crime within major category.- value: monthly reported count of categorical crime in given borough- year: Year of reported counts, 2008-2016- month: Month of reported counts, 1-12Data set URL: https://www.kaggle.com/jboysen/london-crime  Read in the dataset<jupyter_code># Read in the data 
df = pd.read_csv("london_crime_by_lsoa.csv")
# View the top rows of the dataset
df.head()<jupyter_output><empty_output><jupyter_text>Accessing the most recent crime rates (2016)<jupyter_code># Taking only the most recent year (2016) and dropping the rest
df.drop(df.index[df['year'] != 2016], inplace = True)

# Removing all the entires where crime values are null  
df = df[df.value != 0]

# Reset the index and dropping the previous index
df = df.reset_index(drop=True)
# Shape of the data frame
df.shape
# View the top of the dataset 
df.head()<jupyter_output><empty_output><jupyter_text>Change the column names<jupyter_code>df.columns = ['LSOA_Code', 'Borough','Major_Category','Minor_Category','No_of_Crimes','Year','Month']
df.head()
# View the information of the dataset 
df.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 392042 entries, 0 to 392041
Data columns (total 7 columns):
LSOA_Code         392042 non-null object
Borough           392042 non-null object
Major_Category    392042 non-null object
Minor_Category    392042 non-null object
No_of_Crimes      392042 non-null int64
Year              392042 non-null int64
Month             392042 non-null int64
dtypes: int64(3), object(4)
memory usage: 20.9+ MB<jupyter_text>Total number of crimes in each Borough<jupyter_code>df['Borough'].value_counts()<jupyter_output><empty_output><jupyter_text>The total crimes per major category<jupyter_code>df['Major_Category'].value_counts()<jupyter_output><empty_output><jupyter_text>Pivoting the table to view the no. of crimes for each major category in each Borough<jupyter_code>London_crime = pd.pivot_table(df,values=['No_of_Crimes'],
                               index=['Borough'],
                               columns=['Major_Category'],
                               aggfunc=np.sum,fill_value=0)
London_crime.head()
# Reset the index
London_crime.reset_index(inplace = True)
# Total crimes per Borough
London_crime['Total'] = London_crime.sum(axis=1)
London_crime.head()<jupyter_output><empty_output><jupyter_text>Removing the multi index so that it will be easier to merge<jupyter_code>London_crime.columns = London_crime.columns.map(''.join)
London_crime.head()<jupyter_output><empty_output><jupyter_text>Renaming the columns<jupyter_code>London_crime.columns = ['Borough','Burglary', 'Criminal Damage','Drugs','Other Notifiable Offences',
                        'Robbery','Theft and Handling','Violence Against the Person','Total']
London_crime.head()
# Shape of the data set 
London_crime.shape
# View the Columns in the data frame
# London_crime.columns.tolist()<jupyter_output><empty_output><jupyter_text>Descriptive statistics of the data<jupyter_code>London_crime.describe()<jupyter_output><empty_output><jupyter_text>Part 2: Scraping the co-ordinates of the different Boroughs in London from a Wikipedia page **Using Beautiful soup to scrap the latitude and longitiude of the boroughs in London**URL: https://en.wikipedia.org/wiki/List_of_London_boroughs<jupyter_code># getting data from internet
wikipedia_link='https://en.wikipedia.org/wiki/List_of_London_boroughs'
raw_wikipedia_page= requests.get(wikipedia_link).text

# using beautiful soup to parse the HTML/XML codes.
soup = BeautifulSoup(raw_wikipedia_page,'xml')
print(soup.prettify())

# extracting the raw table inside that webpage
table = soup.find_all('table', {'class':'wikitable sortable'})
print(table)<jupyter_output>[<table class="wikitable sortable" style="font-size:100%" width="100%">
<tbody><tr>
<th>Borough
</th>
<th>Inner
</th>
<th>Status
</th>
<th>Local authority
</th>
<th>Political control
</th>
<th>Headquarters
</th>
<th>Area (sq mi)
</th>
<th>Population (2013 est)<sup class="reference" id="cite_ref-1"><a href="#cite_note-1">[1]</a></sup>
</th>
<th>Co-ordinates
</th>
<th><span style="background:#67BCD3"> Nr. in map </span>
</th></tr>
<tr>
<td><a href="/wiki/London_Borough_of_Barking_and_Dagenham" title="London Borough of Barking and Dagenham">Barking and Dagenham</a> <sup class="reference" id="cite_ref-2"><a href="#cite_note-2">[note 1]</a></sup>
</td>
<td>
</td>
<td>
</td>
<td><a href="/wiki/Barking_and_Dagenham_London_Borough_Council" title="Barking and Dagenham London Borough Council">Barking and Dagenham London Borough Council</a>
</td>
<td><a href="/wiki/Labour_Party_(UK)" title="Labour Party (UK)">Labour</a>
</td>
<td><a class="new" href="/w/index.php?title=Barking_Town_Hall&amp;actio[...]<jupyter_text>Converting the table into a data frame<jupyter_code>London_table = pd.read_html(str(table[0]), index_col=None, header=0)[0]
London_table.head()<jupyter_output><empty_output><jupyter_text>The second table on the site contains the addition Borough i.e. City of London<jupyter_code># Read in the second table 
London_table1 = pd.read_html(str(table[1]), index_col=None, header=0)[0]

# Rename the columns to match the previous table to append the tables.

London_table1.columns = ['Borough','Inner','Status','Local authority','Political control',
                         'Headquarters','Area (sq mi)','Population (2013 est)[1]','Co-ordinates','Nr. in map']

# View the table
London_table1<jupyter_output><empty_output><jupyter_text>Append the data frame together<jupyter_code># A continuous index value will be maintained 
# across the rows in the new appended data frame. 

London_table = London_table.append(London_table1, ignore_index = True) 
London_table.head()<jupyter_output><empty_output><jupyter_text>Check if the last row was appended correctly<jupyter_code>London_table.tail()<jupyter_output><empty_output><jupyter_text>View the information of the data set<jupyter_code>London_table.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 33 entries, 0 to 32
Data columns (total 10 columns):
Borough                     33 non-null object
Inner                       15 non-null object
Status                      5 non-null object
Local authority             33 non-null object
Political control           33 non-null object
Headquarters                33 non-null object
Area (sq mi)                33 non-null float64
Population (2013 est)[1]    33 non-null int64
Co-ordinates                33 non-null object
Nr. in map                  33 non-null int64
dtypes: float64(1), int64(2), object(7)
memory usage: 2.7+ KB<jupyter_text>Removing Unnecessary string in the Data set<jupyter_code>London_table = London_table.replace('note 1','', regex=True) 
London_table = London_table.replace('note 2','', regex=True) 
London_table = London_table.replace('note 3','', regex=True) 
London_table = London_table.replace('note 4','', regex=True) 
London_table = London_table.replace('note 5','', regex=True) 

# View the top of the data set
London_table.head()<jupyter_output><empty_output><jupyter_text>Check the type of the newly created table<jupyter_code>type(London_table)
# Shape of the data frame
London_table.shape<jupyter_output><empty_output><jupyter_text>Check if the Borough in both the data frames match.<jupyter_code>set(df.Borough) - set(London_table.Borough)<jupyter_output><empty_output><jupyter_text>These 3 Boroughs don't match because of the unnecessary symobols present "[]"   Find the index of the Boroughs that didn't match<jupyter_code>print("The index of first borough is",London_table.index[London_table['Borough'] == 'Barking and Dagenham []'].tolist())
print("The index of second borough is",London_table.index[London_table['Borough'] == 'Greenwich []'].tolist())
print("The index of third borough is",London_table.index[London_table['Borough'] == 'Hammersmith and Fulham []'].tolist())<jupyter_output>The index of first borough is [0]
The index of second borough is [9]
The index of third borough is [11]<jupyter_text>Changing the Borough names to match the other data frame<jupyter_code>London_table.iloc[0,0] = 'Barking and Dagenham'
London_table.iloc[9,0] = 'Greenwich'
London_table.iloc[11,0] = 'Hammersmith and Fulham'<jupyter_output><empty_output><jupyter_text>Check if the Borough names in both data sets match<jupyter_code>set(df.Borough) - set(London_table.Borough)<jupyter_output><empty_output><jupyter_text>The Borough names in both data frames match   We can combine both the data frames together<jupyter_code>Ld_crime = pd.merge(London_crime, London_table, on='Borough')
Ld_crime.head()
# Geocoder starts here
# Defining a function to use --> get_latlng()'''
def get_latlng(arcgis_geocoder):
    
    # Initialize the Location (lat. and long.) to "None"
    lat_lng_coords = None
    
    # While loop helps to create a continous run until all the location coordinates are geocoded
    while(lat_lng_coords is None):
        g = geocoder.arcgis('{}, London, United Kingdom'.format(arcgis_geocoder))
        lat_lng_coords = g.latlng
    return lat_lng_coords
# Geocoder ends here
sample = get_latlng('Barnet')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.104673219520607, 'fraction_numerical': 0.023366097603035006, 'mean_word_length': 5.404748757592491, 'pattern_counts': {'":': 0, '<': 147, '<?xml version=': 0, '>': 147, 'https://': 3, 'lorem ipsum': 0, 'www.': 1, 'xml': 1}, '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': '24', 'dir': 'jupyter-structured-clean-dedup', 'id': '594782', 'license': 'MIT', 'n_tokens_mistral': 4018, 'n_tokens_neox': 3737, 'n_words': 1057, 'path': 'Applied Data Science Capstone/Untitled-Copy1.ipynb', 'repo_name': 'ipiyushsonar/coursera-labs', 'size': '11641'}