BON2_DEMOGRAPHICS.ipynb

{
    "nbformat_minor": 1, 
    "cells": [
        {
            "source": "## The Battle of the Neighborhoods - Week 2", 
            "cell_type": "markdown", 
            "metadata": {
                "collapsed": true
            }
        }, 
        {
            "source": "### Part 2 Web scrapping of Population and Demographics data of New York city from Wikipedia", 
            "cell_type": "markdown", 
            "metadata": {}
        }, 
        {
            "source": "#### A : POPULATION DATA\n\nWeb scrapping of Population data from wikipedia page - https://en.wikipedia.org/wiki/New_York_City\n\n#### Download all the dependencies that is needed.", 
            "cell_type": "markdown", 
            "metadata": {}
        }, 
        {
            "execution_count": 17, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [
                {
                    "output_type": "stream", 
                    "name": "stdout", 
                    "text": "Fetching package metadata .............\nSolving package specifications: .\n\n# All requested packages already installed.\n# packages in environment at /opt/conda/envs/DSX-Python35:\n#\ngeopy                     1.17.0                     py_0    conda-forge\nLibraries imported.\n"
                }
            ], 
            "source": "import numpy as np # library to handle data in a vectorized manner\n\nimport pandas as pd # library for data analsysis\npd.set_option('display.max_columns', None)\npd.set_option('display.max_rows', None)\n\nimport json # library to handle JSON files\n\n!conda install -c conda-forge geopy --yes # uncomment this line if you haven't completed the Foursquare API lab\nfrom geopy.geocoders import Nominatim # convert an address into latitude and longitude values\n\nimport requests # library to handle requests\nfrom pandas.io.json import json_normalize # tranform JSON file into a pandas dataframe\n\n# Matplotlib and associated plotting modules\nimport matplotlib.cm as cm\nimport matplotlib.colors as colors\nimport matplotlib.pyplot as plt\n\n# conda install -c anaconda beautiful-soup --yes\nfrom bs4 import BeautifulSoup # package for parsing HTML and XML documents\n\nimport csv # implements classes to read and write tabular data in CSV form\n\nprint('Libraries imported.')"
        }, 
        {
            "source": "#### Web scrapping of Population data from wikipedia page using BeautifulSoup.\n\nBeautiful Soup is a Python package for parsing HTML and XML documents (including having malformed markup, i.e. non-closed tags, so named after tag soup). It creates a parse tree for parsed pages that can be used to extract data from HTML, which is useful for web scraping.", 
            "cell_type": "markdown", 
            "metadata": {}
        }, 
        {
            "execution_count": 25, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [], 
            "source": "website_url = requests.get('https://en.wikipedia.org/wiki/Demographics_of_New_York_City').text\nsoup = BeautifulSoup(website_url,'lxml')\ntable = soup.find('table',{'class':'wikitable sortable'})\n#print(soup.prettify())\n\nheaders = [header.text for header in table.find_all('th')]\n\ntable_rows = table.find_all('tr')        \nrows = []\nfor row in table_rows:\n   td = row.find_all('td')\n   row = [row.text for row in td]\n   rows.append(row)\n\nwith open('BON2_POPULATION1.csv', 'w') as f:\n   writer = csv.writer(f)\n   writer.writerow(headers)\n   writer.writerows(row for row in rows if row)"
        }, 
        {
            "source": "#### Load data from CSV", 
            "cell_type": "markdown", 
            "metadata": {}
        }, 
        {
            "execution_count": 26, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [
                {
                    "output_type": "stream", 
                    "name": "stdout", 
                    "text": "Data downloaded!\n"
                }
            ], 
            "source": "Pop_data=pd.read_csv('BON2_POPULATION1.csv')\nPop_data.drop(Pop_data.columns[[7,8,9,10,11]], axis=1,inplace=True)\nprint('Data downloaded!')"
        }, 
        {
            "source": "#### Remove whitespaces and rename columns", 
            "cell_type": "markdown", 
            "metadata": {}
        }, 
        {
            "execution_count": 27, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [
                {
                    "execution_count": 27, 
                    "metadata": {}, 
                    "data": {
                        "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>NewYorkCitysfiveboroughsvte</th>\n      <th>Jurisdiction</th>\n      <th>Population</th>\n      <th>Landarea</th>\n      <th>Density</th>\n      <th>persons_sq_mi</th>\n      <th>persons_sq_km</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>Manhattan\\n</td>\n      <td>\\n  New York\\n</td>\n      <td>1,664,727\\n</td>\n      <td>22.83\\n</td>\n      <td>59.13\\n</td>\n      <td>72,033\\n</td>\n      <td>27,826\\n</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>The Bronx\\n</td>\n      <td>\\n  Bronx\\n</td>\n      <td>1,471,160\\n</td>\n      <td>42.10\\n</td>\n      <td>109.04\\n</td>\n      <td>34,653\\n</td>\n      <td>13,231\\n</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>Brooklyn\\n</td>\n      <td>\\n  Kings\\n</td>\n      <td>2,648,771\\n</td>\n      <td>70.82\\n</td>\n      <td>183.42\\n</td>\n      <td>37,137\\n</td>\n      <td>14,649\\n</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>Queens\\n</td>\n      <td>\\n   Queens\\n</td>\n      <td>2,358,582\\n</td>\n      <td>108.53\\n</td>\n      <td>281.09\\n</td>\n      <td>21,460\\n</td>\n      <td>8,354\\n</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>Staten Island\\n</td>\n      <td>\\n   Richmond\\n</td>\n      <td>479,458\\n</td>\n      <td>58.37\\n</td>\n      <td>151.18\\n</td>\n      <td>8,112\\n</td>\n      <td>3,132\\n</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>City of New York</td>\n      <td>8,622,698</td>\n      <td>302.64</td>\n      <td>783.83</td>\n      <td>28,188</td>\n      <td>10,947\\n</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>State of New York</td>\n      <td>19,849,399</td>\n      <td>47,214</td>\n      <td>122,284</td>\n      <td>416.4</td>\n      <td>159\\n</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>Sources: [2] and see individual borough articl...</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n  </tbody>\n</table>\n</div>", 
                        "text/plain": "                       NewYorkCitysfiveboroughsvte\\n   Jurisdiction\\n  \\\n0                                        Manhattan\\n   \\n  New York\\n   \n1                                        The Bronx\\n      \\n  Bronx\\n   \n2                                         Brooklyn\\n      \\n  Kings\\n   \n3                                           Queens\\n    \\n   Queens\\n   \n4                                    Staten Island\\n  \\n   Richmond\\n   \n5                                   City of New York        8,622,698   \n6                                  State of New York       19,849,399   \n7  Sources: [2] and see individual borough articl...              NaN   \n\n  Population\\n Landarea\\n Density\\n persons_sq_mi persons_sq_km  \n0  1,664,727\\n    22.83\\n   59.13\\n      72,033\\n      27,826\\n  \n1  1,471,160\\n    42.10\\n  109.04\\n      34,653\\n      13,231\\n  \n2  2,648,771\\n    70.82\\n  183.42\\n      37,137\\n      14,649\\n  \n3  2,358,582\\n   108.53\\n  281.09\\n      21,460\\n       8,354\\n  \n4    479,458\\n    58.37\\n  151.18\\n       8,112\\n       3,132\\n  \n5       302.64     783.83    28,188      10,947\\n           NaN  \n6       47,214    122,284     416.4         159\\n           NaN  \n7          NaN        NaN       NaN           NaN           NaN  "
                    }, 
                    "output_type": "execute_result"
                }
            ], 
            "source": "Pop_data.columns = Pop_data.columns.str.replace(' ', '')\nPop_data.columns = Pop_data.columns.str.replace('\\'','')\nPop_data.rename(columns={'Borough':'persons_sq_mi','County':'persons_sq_km'}, inplace=True)\nPop_data"
        }, 
        {
            "execution_count": 30, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [
                {
                    "execution_count": 30, 
                    "metadata": {}, 
                    "data": {
                        "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>Borough</th>\n      <th>County</th>\n      <th>Estimate_2017</th>\n      <th>square_miles</th>\n      <th>square_km</th>\n      <th>persons_sq_mi</th>\n      <th>persons_sq_km</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>Manhattan</td>\n      <td>New York</td>\n      <td>1,664,727</td>\n      <td>22.83</td>\n      <td>59.13</td>\n      <td>72,033</td>\n      <td>27,826</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>The Bronx</td>\n      <td>Bronx</td>\n      <td>1,471,160</td>\n      <td>42.10</td>\n      <td>109.04</td>\n      <td>34,653</td>\n      <td>13,231</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>Brooklyn</td>\n      <td>Kings</td>\n      <td>2,648,771</td>\n      <td>70.82</td>\n      <td>183.42</td>\n      <td>37,137</td>\n      <td>14,649</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>Queens</td>\n      <td>Queens</td>\n      <td>2,358,582</td>\n      <td>108.53</td>\n      <td>281.09</td>\n      <td>21,460</td>\n      <td>8,354</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>Staten Island</td>\n      <td>Richmond</td>\n      <td>479,458</td>\n      <td>58.37</td>\n      <td>151.18</td>\n      <td>8,112</td>\n      <td>3,132</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>City of New York</td>\n      <td>8,622,698</td>\n      <td>302.64</td>\n      <td>783.83</td>\n      <td>28,188</td>\n      <td>10,947</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>State of New York</td>\n      <td>19,849,399</td>\n      <td>47,214</td>\n      <td>122,284</td>\n      <td>416.4</td>\n      <td>159</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>Sources: [2] and see individual borough articles</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n  </tbody>\n</table>\n</div>", 
                        "text/plain": "                                            Borough       County  \\\n0                                         Manhattan     New York   \n1                                         The Bronx        Bronx   \n2                                          Brooklyn        Kings   \n3                                            Queens       Queens   \n4                                     Staten Island     Richmond   \n5                                  City of New York    8,622,698   \n6                                 State of New York   19,849,399   \n7  Sources: [2] and see individual borough articles          NaN   \n\n  Estimate_2017 square_miles square_km persons_sq_mi persons_sq_km  \n0     1,664,727        22.83     59.13        72,033        27,826  \n1     1,471,160        42.10    109.04        34,653        13,231  \n2     2,648,771        70.82    183.42        37,137        14,649  \n3     2,358,582       108.53    281.09        21,460         8,354  \n4       479,458        58.37    151.18         8,112         3,132  \n5        302.64       783.83    28,188        10,947           NaN  \n6        47,214      122,284     416.4           159           NaN  \n7           NaN          NaN       NaN           NaN           NaN  "
                    }, 
                    "output_type": "execute_result"
                }
            ], 
            "source": "Pop_data.rename(columns = {'NewYorkCitysfiveboroughsvte\\n' : 'Borough',\n                   'Jurisdiction\\n':'County',\n                   'Population\\n':'Estimate_2017', \n                   'Landarea\\n':'square_miles',\n                    'Density\\n':'square_km'}, inplace=True)\nPop_data"
        }, 
        {
            "source": "#### Replace newline('\\n') from each string from left and right sides", 
            "cell_type": "markdown", 
            "metadata": {}
        }, 
        {
            "execution_count": 31, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [
                {
                    "execution_count": 31, 
                    "metadata": {}, 
                    "data": {
                        "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>Borough</th>\n      <th>County</th>\n      <th>Estimate_2017</th>\n      <th>square_miles</th>\n      <th>square_km</th>\n      <th>persons_sq_mi</th>\n      <th>persons_sq_km</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>Manhattan</td>\n      <td>New York</td>\n      <td>1,664,727</td>\n      <td>22.83</td>\n      <td>59.13</td>\n      <td>72,033</td>\n      <td>27,826</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>The Bronx</td>\n      <td>Bronx</td>\n      <td>1,471,160</td>\n      <td>42.10</td>\n      <td>109.04</td>\n      <td>34,653</td>\n      <td>13,231</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>Brooklyn</td>\n      <td>Kings</td>\n      <td>2,648,771</td>\n      <td>70.82</td>\n      <td>183.42</td>\n      <td>37,137</td>\n      <td>14,649</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>Queens</td>\n      <td>Queens</td>\n      <td>2,358,582</td>\n      <td>108.53</td>\n      <td>281.09</td>\n      <td>21,460</td>\n      <td>8,354</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>Staten Island</td>\n      <td>Richmond</td>\n      <td>479,458</td>\n      <td>58.37</td>\n      <td>151.18</td>\n      <td>8,112</td>\n      <td>3,132</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>City of New York</td>\n      <td>8,622,698</td>\n      <td>302.64</td>\n      <td>783.83</td>\n      <td>28,188</td>\n      <td>10,947</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>State of New York</td>\n      <td>19,849,399</td>\n      <td>47,214</td>\n      <td>122,284</td>\n      <td>416.4</td>\n      <td>159</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>Sources: [2] and see individual borough articles</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n  </tbody>\n</table>\n</div>", 
                        "text/plain": "                                            Borough       County  \\\n0                                         Manhattan     New York   \n1                                         The Bronx        Bronx   \n2                                          Brooklyn        Kings   \n3                                            Queens       Queens   \n4                                     Staten Island     Richmond   \n5                                  City of New York    8,622,698   \n6                                 State of New York   19,849,399   \n7  Sources: [2] and see individual borough articles          NaN   \n\n  Estimate_2017 square_miles square_km persons_sq_mi persons_sq_km  \n0     1,664,727        22.83     59.13        72,033        27,826  \n1     1,471,160        42.10    109.04        34,653        13,231  \n2     2,648,771        70.82    183.42        37,137        14,649  \n3     2,358,582       108.53    281.09        21,460         8,354  \n4       479,458        58.37    151.18         8,112         3,132  \n5        302.64       783.83    28,188        10,947           NaN  \n6        47,214      122,284     416.4           159           NaN  \n7           NaN          NaN       NaN           NaN           NaN  "
                    }, 
                    "output_type": "execute_result"
                }
            ], 
            "source": "Pop_data['Borough']=Pop_data['Borough'].replace(to_replace='\\n', value='', regex=True)\nPop_data['County']=Pop_data['County'].replace(to_replace='\\n', value='', regex=True)\nPop_data['Estimate_2017']=Pop_data['Estimate_2017'].replace(to_replace='\\n', value='', regex=True)\nPop_data['square_miles']=Pop_data['square_miles'].replace(to_replace='\\n', value='', regex=True)\nPop_data['square_km']=Pop_data['square_km'].replace(to_replace='\\n', value='', regex=True)\nPop_data['persons_sq_mi']=Pop_data['persons_sq_mi'].replace(to_replace='\\n', value='', regex=True)\nPop_data['persons_sq_km']=Pop_data['persons_sq_km'].replace(to_replace='\\n', value='', regex=True)\nPop_data"
        }, 
        {
            "source": "#### Shift data in the last two rows", 
            "cell_type": "markdown", 
            "metadata": {}
        }, 
        {
            "execution_count": 32, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [
                {
                    "execution_count": 32, 
                    "metadata": {}, 
                    "data": {
                        "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>Borough</th>\n      <th>County</th>\n      <th>Estimate_2017</th>\n      <th>square_miles</th>\n      <th>square_km</th>\n      <th>persons_sq_mi</th>\n      <th>persons_sq_km</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>Manhattan</td>\n      <td>New York</td>\n      <td>1,664,727</td>\n      <td>22.83</td>\n      <td>59.13</td>\n      <td>72,033</td>\n      <td>27,826</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>The Bronx</td>\n      <td>Bronx</td>\n      <td>1,471,160</td>\n      <td>42.10</td>\n      <td>109.04</td>\n      <td>34,653</td>\n      <td>13,231</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>Brooklyn</td>\n      <td>Kings</td>\n      <td>2,648,771</td>\n      <td>70.82</td>\n      <td>183.42</td>\n      <td>37,137</td>\n      <td>14,649</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>Queens</td>\n      <td>Queens</td>\n      <td>2,358,582</td>\n      <td>108.53</td>\n      <td>281.09</td>\n      <td>21,460</td>\n      <td>8,354</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>Staten Island</td>\n      <td>Richmond</td>\n      <td>479,458</td>\n      <td>58.37</td>\n      <td>151.18</td>\n      <td>8,112</td>\n      <td>3,132</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>NaN</td>\n      <td>City of New York</td>\n      <td>8,622,698</td>\n      <td>302.64</td>\n      <td>783.83</td>\n      <td>28,188</td>\n      <td>10,947</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>NaN</td>\n      <td>State of New York</td>\n      <td>19,849,399</td>\n      <td>47,214</td>\n      <td>122,284</td>\n      <td>416.4</td>\n      <td>159</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>NaN</td>\n      <td>Sources: [2] and see individual borough articles</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n  </tbody>\n</table>\n</div>", 
                        "text/plain": "         Borough                                            County  \\\n0      Manhattan                                          New York   \n1      The Bronx                                             Bronx   \n2       Brooklyn                                             Kings   \n3         Queens                                            Queens   \n4  Staten Island                                          Richmond   \n5            NaN                                  City of New York   \n6            NaN                                 State of New York   \n7            NaN  Sources: [2] and see individual borough articles   \n\n  Estimate_2017 square_miles square_km persons_sq_mi persons_sq_km  \n0     1,664,727        22.83     59.13        72,033        27,826  \n1     1,471,160        42.10    109.04        34,653        13,231  \n2     2,648,771        70.82    183.42        37,137        14,649  \n3     2,358,582       108.53    281.09        21,460         8,354  \n4       479,458        58.37    151.18         8,112         3,132  \n5     8,622,698       302.64    783.83        28,188        10,947  \n6    19,849,399       47,214   122,284         416.4           159  \n7           NaN          NaN       NaN           NaN           NaN  "
                    }, 
                    "output_type": "execute_result"
                }
            ], 
            "source": "Pop_data.loc[5:,['persons_sq_mi','persons_sq_km']] = Pop_data.loc[2:,['persons_sq_mi','persons_sq_km']].shift(1,axis=1)\nPop_data.loc[5:,['square_km','persons_sq_mi']] = Pop_data.loc[2:,['square_km','persons_sq_mi']].shift(1,axis=1)\nPop_data.loc[5:,['square_miles','square_km']] = Pop_data.loc[2:,['square_miles','square_km']].shift(1,axis=1)\nPop_data.loc[5:,['Estimate_2017','square_miles']] = Pop_data.loc[2:,['Estimate_2017','square_miles']].shift(1,axis=1)\nPop_data.loc[5:,['County','Estimate_2017']] = Pop_data.loc[2:,['County','Estimate_2017']].shift(1,axis=1)\nPop_data.loc[5:,['Borough','County']] = Pop_data.loc[2:,['Borough','County']].shift(1,axis=1)\nPop_data"
        }, 
        {
            "source": "#### Remove 'NAN'", 
            "cell_type": "markdown", 
            "metadata": {}
        }, 
        {
            "execution_count": 33, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [
                {
                    "execution_count": 33, 
                    "metadata": {}, 
                    "data": {
                        "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>Borough</th>\n      <th>County</th>\n      <th>Estimate_2017</th>\n      <th>square_miles</th>\n      <th>square_km</th>\n      <th>persons_sq_mi</th>\n      <th>persons_sq_km</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>Manhattan</td>\n      <td>New York</td>\n      <td>1,664,727</td>\n      <td>22.83</td>\n      <td>59.13</td>\n      <td>72,033</td>\n      <td>27,826</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>The Bronx</td>\n      <td>Bronx</td>\n      <td>1,471,160</td>\n      <td>42.10</td>\n      <td>109.04</td>\n      <td>34,653</td>\n      <td>13,231</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>Brooklyn</td>\n      <td>Kings</td>\n      <td>2,648,771</td>\n      <td>70.82</td>\n      <td>183.42</td>\n      <td>37,137</td>\n      <td>14,649</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>Queens</td>\n      <td>Queens</td>\n      <td>2,358,582</td>\n      <td>108.53</td>\n      <td>281.09</td>\n      <td>21,460</td>\n      <td>8,354</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>Staten Island</td>\n      <td>Richmond</td>\n      <td>479,458</td>\n      <td>58.37</td>\n      <td>151.18</td>\n      <td>8,112</td>\n      <td>3,132</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td></td>\n      <td>City of New York</td>\n      <td>8,622,698</td>\n      <td>302.64</td>\n      <td>783.83</td>\n      <td>28,188</td>\n      <td>10,947</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td></td>\n      <td>State of New York</td>\n      <td>19,849,399</td>\n      <td>47,214</td>\n      <td>122,284</td>\n      <td>416.4</td>\n      <td>159</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td></td>\n      <td>Sources: [2] and see individual borough articles</td>\n      <td></td>\n      <td></td>\n      <td></td>\n      <td></td>\n      <td></td>\n    </tr>\n  </tbody>\n</table>\n</div>", 
                        "text/plain": "         Borough                                            County  \\\n0      Manhattan                                          New York   \n1      The Bronx                                             Bronx   \n2       Brooklyn                                             Kings   \n3         Queens                                            Queens   \n4  Staten Island                                          Richmond   \n5                                                 City of New York   \n6                                                State of New York   \n7                 Sources: [2] and see individual borough articles   \n\n  Estimate_2017 square_miles square_km persons_sq_mi persons_sq_km  \n0     1,664,727        22.83     59.13        72,033        27,826  \n1     1,471,160        42.10    109.04        34,653        13,231  \n2     2,648,771        70.82    183.42        37,137        14,649  \n3     2,358,582       108.53    281.09        21,460         8,354  \n4       479,458        58.37    151.18         8,112         3,132  \n5     8,622,698       302.64    783.83        28,188        10,947  \n6    19,849,399       47,214   122,284         416.4           159  \n7                                                                   "
                    }, 
                    "output_type": "execute_result"
                }
            ], 
            "source": "Pop_data = Pop_data.fillna('')\nPop_data"
        }, 
        {
            "source": "#### Drop the last row", 
            "cell_type": "markdown", 
            "metadata": {}
        }, 
        {
            "execution_count": 34, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [
                {
                    "execution_count": 34, 
                    "metadata": {}, 
                    "data": {
                        "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>Borough</th>\n      <th>County</th>\n      <th>Estimate_2017</th>\n      <th>square_miles</th>\n      <th>square_km</th>\n      <th>persons_sq_mi</th>\n      <th>persons_sq_km</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>Manhattan</td>\n      <td>New York</td>\n      <td>1,664,727</td>\n      <td>22.83</td>\n      <td>59.13</td>\n      <td>72,033</td>\n      <td>27,826</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>The Bronx</td>\n      <td>Bronx</td>\n      <td>1,471,160</td>\n      <td>42.10</td>\n      <td>109.04</td>\n      <td>34,653</td>\n      <td>13,231</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>Brooklyn</td>\n      <td>Kings</td>\n      <td>2,648,771</td>\n      <td>70.82</td>\n      <td>183.42</td>\n      <td>37,137</td>\n      <td>14,649</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>Queens</td>\n      <td>Queens</td>\n      <td>2,358,582</td>\n      <td>108.53</td>\n      <td>281.09</td>\n      <td>21,460</td>\n      <td>8,354</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>Staten Island</td>\n      <td>Richmond</td>\n      <td>479,458</td>\n      <td>58.37</td>\n      <td>151.18</td>\n      <td>8,112</td>\n      <td>3,132</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td></td>\n      <td>City of New York</td>\n      <td>8,622,698</td>\n      <td>302.64</td>\n      <td>783.83</td>\n      <td>28,188</td>\n      <td>10,947</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td></td>\n      <td>State of New York</td>\n      <td>19,849,399</td>\n      <td>47,214</td>\n      <td>122,284</td>\n      <td>416.4</td>\n      <td>159</td>\n    </tr>\n  </tbody>\n</table>\n</div>", 
                        "text/plain": "         Borough             County Estimate_2017 square_miles square_km  \\\n0      Manhattan           New York     1,664,727        22.83     59.13   \n1      The Bronx              Bronx     1,471,160        42.10    109.04   \n2       Brooklyn              Kings     2,648,771        70.82    183.42   \n3         Queens             Queens     2,358,582       108.53    281.09   \n4  Staten Island           Richmond       479,458        58.37    151.18   \n5                  City of New York     8,622,698       302.64    783.83   \n6                 State of New York    19,849,399       47,214   122,284   \n\n  persons_sq_mi persons_sq_km  \n0        72,033        27,826  \n1        34,653        13,231  \n2        37,137        14,649  \n3        21,460         8,354  \n4         8,112         3,132  \n5        28,188        10,947  \n6         416.4           159  "
                    }, 
                    "output_type": "execute_result"
                }
            ], 
            "source": "i = Pop_data[((Pop_data.County == 'Sources: [2] and see individual borough articles'))].index\nPop_data.drop(i)"
        }, 
        {
            "source": "#### Save dataframe as csv file", 
            "cell_type": "markdown", 
            "metadata": {}
        }, 
        {
            "execution_count": 35, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [], 
            "source": "Pop_data.to_csv('BON2_POPULATION.csv',index=False)"
        }, 
        {
            "source": "#### B : DEMOGRAPHICS DATA\n\nWe will web scrap Demographics data from wikipedia page - https://en.wikipedia.org/wiki/New_York_City", 
            "cell_type": "markdown", 
            "metadata": {}
        }, 
        {
            "source": "#### Web scrapping of Demographics data from wikipedia page using BeautifulSoup.\n\nBeautiful Soup is a Python package for parsing HTML and XML documents (including having malformed markup, i.e. non-closed tags, so named after tag soup). It creates a parse tree for parsed pages that can be used to extract data from HTML, which is useful for web scraping.", 
            "cell_type": "markdown", 
            "metadata": {}
        }, 
        {
            "execution_count": 36, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [], 
            "source": "website_url = requests.get('https://en.wikipedia.org/wiki/New_York_City').text\nsoup = BeautifulSoup(website_url,'lxml')\ntable = soup.find('table',{'class':'wikitable sortable collapsible'})\n#print(soup.prettify())\n\nheaders = [header.text for header in table.find_all('th')]\n\ntable_rows = table.find_all('tr')        \nrows = []\nfor row in table_rows:\n   td = row.find_all('td')\n   row = [row.text for row in td]\n   rows.append(row)\n\nwith open('NYC_DEMO.csv', 'w') as f:\n   writer = csv.writer(f)\n   writer.writerow(headers)\n   writer.writerows(row for row in rows if row)"
        }, 
        {
            "source": "#### Load data from CSV", 
            "cell_type": "markdown", 
            "metadata": {}
        }, 
        {
            "execution_count": 37, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [
                {
                    "output_type": "stream", 
                    "name": "stdout", 
                    "text": "Data downloaded!\n"
                }
            ], 
            "source": "Demo_data=pd.read_csv('NYC_DEMO.csv')\nprint('Data downloaded!')"
        }, 
        {
            "execution_count": 38, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [
                {
                    "execution_count": 38, 
                    "metadata": {}, 
                    "data": {
                        "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>Racial composition</th>\n      <th>2010[237]</th>\n      <th>1990[239]</th>\n      <th>1970[239]</th>\n      <th>1940[239]</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>White</td>\n      <td>44.0%</td>\n      <td>52.3%</td>\n      <td>76.6%</td>\n      <td>93.6%\\n</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>\u2014Non-Hispanic</td>\n      <td>33.3%</td>\n      <td>43.2%</td>\n      <td>62.9%[240]</td>\n      <td>92.0%\\n</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>Black or African American</td>\n      <td>25.5%</td>\n      <td>28.7%</td>\n      <td>21.1%</td>\n      <td>6.1%\\n</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>Hispanic or Latino (of any race)</td>\n      <td>28.6%</td>\n      <td>24.4%</td>\n      <td>16.2%[240]</td>\n      <td>1.6%\\n</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>Asian</td>\n      <td>12.7%</td>\n      <td>7.0%</td>\n      <td>1.2%</td>\n      <td>\u2212\\n</td>\n    </tr>\n  </tbody>\n</table>\n</div>", 
                        "text/plain": "                 Racial composition 2010[237] 1990[239]   1970[239]  \\\n0                             White     44.0%     52.3%       76.6%   \n1                     \u2014Non-Hispanic     33.3%     43.2%  62.9%[240]   \n2         Black or African American     25.5%     28.7%       21.1%   \n3  Hispanic or Latino (of any race)     28.6%     24.4%  16.2%[240]   \n4                             Asian     12.7%      7.0%        1.2%   \n\n  1940[239]\\n  \n0     93.6%\\n  \n1     92.0%\\n  \n2      6.1%\\n  \n3      1.6%\\n  \n4         \u2212\\n  "
                    }, 
                    "output_type": "execute_result"
                }
            ], 
            "source": "Demo_data"
        }, 
        {
            "source": "#### Remove whitespaces and rename columns", 
            "cell_type": "markdown", 
            "metadata": {}
        }, 
        {
            "execution_count": 39, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [
                {
                    "execution_count": 39, 
                    "metadata": {}, 
                    "data": {
                        "text/plain": "Index(['Racial composition', '2010[237]', '1990[239]', '1970[239]',\n       '1940[239]\\n'],\n      dtype='object')"
                    }, 
                    "output_type": "execute_result"
                }
            ], 
            "source": "Demo_data.columns"
        }, 
        {
            "execution_count": 40, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [
                {
                    "execution_count": 40, 
                    "metadata": {}, 
                    "data": {
                        "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>Racial composition</th>\n      <th>2010</th>\n      <th>1990</th>\n      <th>1970</th>\n      <th>1940</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>White</td>\n      <td>44.0%</td>\n      <td>52.3%</td>\n      <td>76.6%</td>\n      <td>93.6%\\n</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>\u2014Non-Hispanic</td>\n      <td>33.3%</td>\n      <td>43.2%</td>\n      <td>62.9%[240]</td>\n      <td>92.0%\\n</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>Black or African American</td>\n      <td>25.5%</td>\n      <td>28.7%</td>\n      <td>21.1%</td>\n      <td>6.1%\\n</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>Hispanic or Latino (of any race)</td>\n      <td>28.6%</td>\n      <td>24.4%</td>\n      <td>16.2%[240]</td>\n      <td>1.6%\\n</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>Asian</td>\n      <td>12.7%</td>\n      <td>7.0%</td>\n      <td>1.2%</td>\n      <td>\u2212\\n</td>\n    </tr>\n  </tbody>\n</table>\n</div>", 
                        "text/plain": "                 Racial composition   2010   1990        1970     1940\n0                             White  44.0%  52.3%       76.6%  93.6%\\n\n1                     \u2014Non-Hispanic  33.3%  43.2%  62.9%[240]  92.0%\\n\n2         Black or African American  25.5%  28.7%       21.1%   6.1%\\n\n3  Hispanic or Latino (of any race)  28.6%  24.4%  16.2%[240]   1.6%\\n\n4                             Asian  12.7%   7.0%        1.2%      \u2212\\n"
                    }, 
                    "output_type": "execute_result"
                }
            ], 
            "source": "Demo_data.rename(columns = {'2010[237]' : '2010',\n                   '1990[239]':'1990',\n                   '1970[239]':'1970', \n                   '1940[239]\\n':'1940',\n                    }, inplace=True)\nDemo_data"
        }, 
        {
            "execution_count": 41, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [
                {
                    "execution_count": 41, 
                    "metadata": {}, 
                    "data": {
                        "text/plain": "Index(['Racial composition', '2010', '1990', '1970', '1940'], dtype='object')"
                    }, 
                    "output_type": "execute_result"
                }
            ], 
            "source": "Demo_data.columns"
        }, 
        {
            "execution_count": 42, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [], 
            "source": "Demo_data.columns = Demo_data.columns.str.replace(' ', '')"
        }, 
        {
            "source": "#### Replace newline('\\n') from each string from left and right sides", 
            "cell_type": "markdown", 
            "metadata": {}
        }, 
        {
            "execution_count": 44, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [
                {
                    "execution_count": 44, 
                    "metadata": {}, 
                    "data": {
                        "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>Racialcomposition</th>\n      <th>2010</th>\n      <th>1990</th>\n      <th>1970</th>\n      <th>1940</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>White</td>\n      <td>44.0%</td>\n      <td>52.3%</td>\n      <td>76.6%</td>\n      <td>93.6%</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>\u2014Non-Hispanic</td>\n      <td>33.3%</td>\n      <td>43.2%</td>\n      <td>62.9%[240]</td>\n      <td>92.0%</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>Black or African American</td>\n      <td>25.5%</td>\n      <td>28.7%</td>\n      <td>21.1%</td>\n      <td>6.1%</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>Hispanic or Latino (of any race)</td>\n      <td>28.6%</td>\n      <td>24.4%</td>\n      <td>16.2%[240]</td>\n      <td>1.6%</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>Asian</td>\n      <td>12.7%</td>\n      <td>7.0%</td>\n      <td>1.2%</td>\n      <td>\u2212</td>\n    </tr>\n  </tbody>\n</table>\n</div>", 
                        "text/plain": "                  Racialcomposition   2010   1990        1970    1940\n0                             White  44.0%  52.3%       76.6%  93.6% \n1                     \u2014Non-Hispanic  33.3%  43.2%  62.9%[240]  92.0% \n2         Black or African American  25.5%  28.7%       21.1%   6.1% \n3  Hispanic or Latino (of any race)  28.6%  24.4%  16.2%[240]   1.6% \n4                             Asian  12.7%   7.0%        1.2%      \u2212 "
                    }, 
                    "output_type": "execute_result"
                }
            ], 
            "source": "Demo_data= Demo_data.replace('\\n',' ', regex=True)\nDemo_data"
        }, 
        {
            "source": "#### Strip '[240]' from third column - 1970", 
            "cell_type": "markdown", 
            "metadata": {}
        }, 
        {
            "execution_count": 45, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [
                {
                    "execution_count": 45, 
                    "metadata": {}, 
                    "data": {
                        "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>Racialcomposition</th>\n      <th>2010</th>\n      <th>1990</th>\n      <th>1970</th>\n      <th>1940</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>White</td>\n      <td>44.0%</td>\n      <td>52.3%</td>\n      <td>76.6%</td>\n      <td>93.6%</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>\u2014Non-Hispanic</td>\n      <td>33.3%</td>\n      <td>43.2%</td>\n      <td>62.9%</td>\n      <td>92.0%</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>Black or African American</td>\n      <td>25.5%</td>\n      <td>28.7%</td>\n      <td>21.1%</td>\n      <td>6.1%</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>Hispanic or Latino (of any race)</td>\n      <td>28.6%</td>\n      <td>24.4%</td>\n      <td>16.2%</td>\n      <td>1.6%</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>Asian</td>\n      <td>12.7%</td>\n      <td>7.0%</td>\n      <td>1.2%</td>\n      <td>\u2212</td>\n    </tr>\n  </tbody>\n</table>\n</div>", 
                        "text/plain": "                  Racialcomposition   2010   1990   1970    1940\n0                             White  44.0%  52.3%  76.6%  93.6% \n1                     \u2014Non-Hispanic  33.3%  43.2%  62.9%  92.0% \n2         Black or African American  25.5%  28.7%  21.1%   6.1% \n3  Hispanic or Latino (of any race)  28.6%  24.4%  16.2%   1.6% \n4                             Asian  12.7%   7.0%   1.2%      \u2212 "
                    }, 
                    "output_type": "execute_result"
                }
            ], 
            "source": "Demo_data['1970'] = Demo_data['1970'].str.rstrip('[240]')\nDemo_data"
        }, 
        {
            "source": "#### Save dataframe as csv file", 
            "cell_type": "markdown", 
            "metadata": {}
        }, 
        {
            "execution_count": 46, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [], 
            "source": "Demo_data.to_csv('BON2_DEMOGRAPHICS.csv',index=False)"
        }
    ], 
    "metadata": {
        "kernelspec": {
            "display_name": "Python 3.5", 
            "name": "python3", 
            "language": "python"
        }, 
        "language_info": {
            "mimetype": "text/x-python", 
            "nbconvert_exporter": "python", 
            "version": "3.5.5", 
            "name": "python", 
            "file_extension": ".py", 
            "pygments_lexer": "ipython3", 
            "codemirror_mode": {
                "version": 3, 
                "name": "ipython"
            }
        }
    }, 
    "nbformat": 4
}