First of all, I have to import the libraries
import pandas as pd
from bs4 import BeautifulSoup
import requestsNow, I am fetching the contents of the website using BeautifulSoup package.
source = requests.get('https://en.wikipedia.org/wiki/List_of_postal_codes_of_Canada:_M').text
soup = BeautifulSoup(source, 'lxml')
#print(soup.prettify())
table= soup.find('table', class_='wikitable sortable')
table_rows = table.find_all('tr')
#print(table_rows)
list = []
for tr in table_rows:
td= tr.find_all('td')
row = [pr.text for pr in td ]
list.append(row)Now, I am taking only those rows that have an Assigned "Bouorugh"
df = pd.DataFrame(list, columns=['PostalCode', 'Borough', 'Neighborhood'])[1:]
df = df[df['Borough'] != 'Not assigned']
df.head()
<style scoped>
.dataframe tbody tr th:only-of-type {
vertical-align: middle;
}
</style>
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
| PostalCode | Borough | Neighborhood | |
|---|---|---|---|
| 3 | M3A | North York | Parkwoods\n |
| 4 | M4A | North York | Victoria Village\n |
| 5 | M5A | Downtown Toronto | Harbourfront\n |
| 6 | M5A | Downtown Toronto | Regent Park\n |
| 7 | M6A | North York | Lawrence Heights\n |
Now, I am separating the Postal Codes which have no duplicates from those which have duplicates.
bool_series = df["PostalCode"].duplicated(keep= False )
df1=df[-bool_series]
print(df1.head(10))
df2= df[bool_series]
print(df2.head(10))
# display data
#df2= df[bool_series]
#df2= df2.pivot_table(index='PostalCode', aggfunc= False)
#df2
pd.unique(df['PostalCode']).shape PostalCode Borough Neighborhood
3 M3A North York Parkwoods\n
4 M4A North York Victoria Village\n
9 M7A Queen's Park Not assigned\n
11 M9A Etobicoke Islington Avenue\n
15 M3B North York Don Mills North\n
20 M6B North York Glencairn\n
34 M4C East York Woodbine Heights\n
35 M5C Downtown Toronto St. James Town\n
36 M6C York Humewood-Cedarvale\n
48 M4E East Toronto The Beaches\n
PostalCode Borough Neighborhood
5 M5A Downtown Toronto Harbourfront\n
6 M5A Downtown Toronto Regent Park\n
7 M6A North York Lawrence Heights\n
8 M6A North York Lawrence Manor\n
12 M1B Scarborough Rouge\n
13 M1B Scarborough Malvern\n
16 M4B East York Woodbine Gardens\n
17 M4B East York Parkview Hill\n
18 M5B Downtown Toronto Ryerson\n
19 M5B Downtown Toronto Garden District\n
(103,)
import numpy as np #Importing numpy for working with NaN valuesNow, I have repeatedly applied 'duplicated' function to get the dataframes with no duplicates of Postal Code. I could have written a function of my own, but since here I had to apply 'duplicated' function only a few times so, I refrained myself from writing a function of my own.
bool1= df2.duplicated('PostalCode', keep='last')
df3= df2[bool1]
df4 = df2[-bool1]
bool2 = df3.duplicated('PostalCode', keep='last')
df5 = df3[bool2]
df6 = df3[-bool2]
#print(df4)
#print(df5)
bool3 = df5.duplicated('PostalCode', keep='last')
df7= df5[bool3]
df8 = df5[-bool3]
bool4= df7.duplicated('PostalCode', keep='last')
df9 = df7[bool4]
df10 = df7[-bool4]
bool5= df9.duplicated('PostalCode', keep='last')
df11 = df9[bool5]
df12 = df9[-bool5]
bool6= df11.duplicated('PostalCode', keep='last')
df13 = df11[bool6]
df14 = df11[-bool6]
bool7= df13.duplicated('PostalCode', keep='last')
df15 = df13[bool7]
df16 = df13[-bool7]
bool8= df15.duplicated('PostalCode', keep='last')
df17 = df15[bool8]
df18 = df15[-bool8]
final1 = pd.merge(df18, df16, on= ['PostalCode', 'Borough'],how='outer')
final2 = pd.merge(final1, df16, on= ['PostalCode', 'Borough'],how='outer')
final3 = pd.merge(final2, df14, on= ['PostalCode', 'Borough'],how='outer')
final4= pd.merge(final3, df12, on= ['PostalCode', 'Borough'],how='outer')
final5= pd.merge(final4, df10, on= ['PostalCode', 'Borough'],how='outer')
final6= pd.merge(final5, df8, on= ['PostalCode', 'Borough'],how='outer')
final7= pd.merge(final6, df6, on= ['PostalCode', 'Borough'],how='outer')
final8= pd.merge(final7, df4, on= ['PostalCode', 'Borough'],how='outer')
final9= pd.merge(final8, df1, on= ['PostalCode', 'Borough'],how='outer')
final9 = final9.replace(np.nan, '', regex=True)
columnNumbers = [x for x in range(final9.shape[1])]
columnNumbers.remove(4) #removing column integer index 0
final9 = final9.iloc[:, columnNumbers]
final9
<style scoped>
.dataframe tbody tr th:only-of-type {
vertical-align: middle;
}
</style>
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
| PostalCode | Borough | Neighborhood_x | Neighborhood_y | Neighborhood_y | Neighborhood_x | Neighborhood_y | Neighborhood_x | Neighborhood_y | Neighborhood_x | Neighborhood_y | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | M9V | Etobicoke | Albion Gardens\n | Beaumond Heights\n | Humbergate\n | Jamestown\n | Mount Olive\n | Silverstone\n | South Steeles\n | Thistletown\n | |
| 1 | M8Y | Etobicoke | Humber Bay\n | King's Mill Park\n | Kingsway Park South East\n | Mimico NE\n | Old Mill South\n | The Queensway East\n | Royal York South East\n | Sunnylea\n | |
| 2 | M5V | Downtown Toronto | CN Tower\n | Bathurst Quay\n | Island airport\n | Harbourfront West\n | King and Spadina\n | Railway Lands\n | South Niagara\n | ||
| 3 | M9B | Etobicoke | Cloverdale\n | Islington\n | Martin Grove\n | Princess Gardens\n | West Deane Park\n | ||||
| 4 | M4V | Central Toronto | Deer Park\n | Forest Hill SE\n | Rathnelly\n | South Hill\n | Summerhill West\n | ||||
| 5 | M8Z | Etobicoke | Kingsway Park South West\n | Mimico NW\n | The Queensway West\n | Royal York South West\n | South of Bloor\n | ||||
| 6 | M9C | Etobicoke | Bloordale Gardens\n | Eringate\n | Markland Wood\n | Old Burnhamthorpe\n | |||||
| 7 | M6M | York | Del Ray\n | Keelesdale\n | Mount Dennis\n | Silverthorn\n | |||||
| 8 | M9R | Etobicoke | Kingsview Village\n | Martin Grove Gardens\n | Richview Gardens\n | St. Phillips\n | |||||
| 9 | M1V | Scarborough | Agincourt North\n | L'Amoreaux East\n | Milliken\n | Steeles East\n | |||||
| 10 | M1C | Scarborough | Highland Creek\n | Rouge Hill\n | Port Union\n | ||||||
| 11 | M1E | Scarborough | Guildwood\n | Morningside\n | West Hill\n | ||||||
| 12 | M3H | North York | Bathurst Manor\n | Downsview North\n | Wilson Heights\n | ||||||
| 13 | M5H | Downtown Toronto | Adelaide\n | King\n | Richmond\n | ||||||
| 14 | M2J | North York | Fairview\n | Henry Farm\n | Oriole\n | ||||||
| 15 | M5J | Downtown Toronto | Harbourfront East\n | Toronto Islands\n | Union Station\n | ||||||
| 16 | M1K | Scarborough | East Birchmount Park\n | Ionview\n | Kennedy Park\n | ||||||
| 17 | M6K | West Toronto | Brockton\n | Exhibition Place\n | Parkdale Village\n | ||||||
| 18 | M1L | Scarborough | Clairlea\n | Golden Mile\n | Oakridge\n | ||||||
| 19 | M6L | North York | Maple Leaf Park\n | North Park\n | Upwood Park\n | ||||||
| 20 | M1M | Scarborough | Cliffcrest\n | Cliffside\n | Scarborough Village West\n | ||||||
| 21 | M1P | Scarborough | Dorset Park\n | Scarborough Town Centre\n | Wexford Heights\n | ||||||
| 22 | M5R | Central Toronto | The Annex\n | North Midtown\n | Yorkville\n | ||||||
| 23 | M1T | Scarborough | Clarks Corners\n | Sullivan\n | Tam O'Shanter\n | ||||||
| 24 | M5T | Downtown Toronto | Chinatown\n | Grange Park\n | Kensington Market\n | ||||||
| 25 | M8V | Etobicoke | Humber Bay Shores\n | Mimico South\n | New Toronto\n | ||||||
| 26 | M8X | Etobicoke | The Kingsway\n | Montgomery Road\n | Old Mill North\n | ||||||
| 27 | M5A | Downtown Toronto | Harbourfront\n | Regent Park\n | |||||||
| 28 | M6A | North York | Lawrence Heights\n | Lawrence Manor\n | |||||||
| 29 | M1B | Scarborough | Rouge\n | Malvern\n | |||||||
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 73 | M6G | Downtown Toronto | Christie\n | ||||||||
| 74 | M1H | Scarborough | Cedarbrae\n | ||||||||
| 75 | M2H | North York | Hillcrest Village\n | ||||||||
| 76 | M4H | East York | Thorncliffe Park\n | ||||||||
| 77 | M1J | Scarborough | Scarborough Village\n | ||||||||
| 78 | M4J | East York | East Toronto\n | ||||||||
| 79 | M2K | North York | Bayview Village\n | ||||||||
| 80 | M3L | North York | Downsview West\n | ||||||||
| 81 | M9L | North York | Humber Summit\n | ||||||||
| 82 | M3M | North York | Downsview Central\n | ||||||||
| 83 | M4M | East Toronto | Studio District\n | ||||||||
| 84 | M2N | North York | Willowdale South\n | ||||||||
| 85 | M3N | North York | Downsview Northwest\n | ||||||||
| 86 | M4N | Central Toronto | Lawrence Park\n | ||||||||
| 87 | M5N | Central Toronto | Roselawn\n | ||||||||
| 88 | M9N | York | Weston\n | ||||||||
| 89 | M2P | North York | York Mills West\n | ||||||||
| 90 | M4P | Central Toronto | Davisville North\n | ||||||||
| 91 | M9P | Etobicoke | Westmount\n | ||||||||
| 92 | M2R | North York | Willowdale West\n | ||||||||
| 93 | M4R | Central Toronto | North Toronto West\n | ||||||||
| 94 | M7R | Mississauga | Canada Post Gateway Processing Centre\n | ||||||||
| 95 | M1S | Scarborough | Agincourt\n | ||||||||
| 96 | M4S | Central Toronto | Davisville\n | ||||||||
| 97 | M4W | Downtown Toronto | Rosedale\n | ||||||||
| 98 | M5W | Downtown Toronto | Stn A PO Boxes 25 The Esplanade\n | ||||||||
| 99 | M9W | Etobicoke | Northwest\n | ||||||||
| 100 | M1X | Scarborough | Upper Rouge\n | ||||||||
| 101 | M4Y | Downtown Toronto | Church and Wellesley\n | ||||||||
| 102 | M7Y | East Toronto | Business reply mail Processing Centre969 Easte... |
103 rows × 11 columns
Now, almost all the work is done with, but we have to combine the different Neighborhood columns into a single one.
final9.columns =['PostalCode', 'Borough', 'Neighborhood_a', 'Neighborhood_b',
'Neighborhood_c', 'Neighborhood_d', 'Neighborhood_e', 'Neighborhood_f',
'Neighborhood_g', 'Neighborhood_h', 'Neighborhood_i']final9['combined']=final9['Neighborhood_i']+','+final9['Neighborhood_h']+','+final9['Neighborhood_g']+','+final9['Neighborhood_f']+','+final9['Neighborhood_e']+','+final9['Neighborhood_d']+','+final9['Neighborhood_c']+','+final9['Neighborhood_b']+','+final9['Neighborhood_a']final9
final9= final9.loc[:,['PostalCode', 'Borough', 'combined']]
final9.columns=['PostalCode', 'Borough', 'Neighborhood']
final9.head()
<style scoped>
.dataframe tbody tr th:only-of-type {
vertical-align: middle;
}
</style>
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
| PostalCode | Borough | Neighborhood | |
|---|---|---|---|
| 0 | M9V | Etobicoke | ,Thistletown\n,South Steeles\n,Silverstone\n,M... |
| 1 | M8Y | Etobicoke | ,Sunnylea\n,Royal York South East\n,The Queens... |
| 2 | M5V | Downtown Toronto | ,South Niagara\n,Railway Lands\n,King and Spad... |
| 3 | M9B | Etobicoke | ,West Deane Park\n,Princess Gardens\n,Martin G... |
| 4 | M4V | Central Toronto | ,Summerhill West\n,South Hill\n,Rathnelly\n,Fo... |
def clean(x):
x=x.replace("\n","").replace(",,,,","").replace(",,,","").replace(",,","")
return x
final9['Neighborhood']= final9['Neighborhood'].apply(clean)
final9.head()
<style scoped>
.dataframe tbody tr th:only-of-type {
vertical-align: middle;
}
</style>
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
| PostalCode | Borough | Neighborhood | |
|---|---|---|---|
| 0 | M9V | Etobicoke | ,Thistletown,South Steeles,Silverstone,Mount O... |
| 1 | M8Y | Etobicoke | ,Sunnylea,Royal York South East,The Queensway ... |
| 2 | M5V | Downtown Toronto | ,South Niagara,Railway Lands,King and Spadina,... |
| 3 | M9B | Etobicoke | ,West Deane Park,Princess Gardens,Martin Grove... |
| 4 | M4V | Central Toronto | ,Summerhill West,South Hill,Rathnelly,Forest H... |
Here, we get to the desired form of the DataFrame. The only thing I could not do is to remove the 'commas' that appear in the beginning of the values in "Neighburhood'