Skip to content

Instantly share code, notes, and snippets.

@kshirsagarsiddharth

kshirsagarsiddharth/DB0201EN-Week3-1-4-Analyzing-v5-py.ipynb

Created November 28, 2019 15:57
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save kshirsagarsiddharth/b3e6bcd0a3acd32cf583240bf9c0a42c to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save kshirsagarsiddharth/b3e6bcd0a3acd32cf583240bf9c0a42c to your computer and use it in GitHub Desktop.
Download ZIP
Created on Cognitive Class Labs
Display the source blob
Display the rendered blob
Raw
DB0201EN-Week3-1-4-Analyzing-v5-py.ipynb
{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"<a href=\"https://cognitiveclass.ai\"><img src = \"https://ibm.box.com/shared/static/ugcqz6ohbvff804xp84y4kqnvvk3bq1g.png\" width = 300, align = \"center\"></a>\n",
"\n",
"<h1 align=center><font size = 5>Lab: Analyzing a real world data-set with SQL and Python</font></h1>"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Introduction\n",
"\n",
"This notebook shows how to store a dataset into a database using and analyze data using SQL and Python. In this lab you will:\n",
"1. Understand a dataset of selected socioeconomic indicators in Chicago\n",
"1. Learn how to store data in an Db2 database on IBM Cloud instance\n",
"1. Solve example problems to practice your SQL skills "
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Selected Socioeconomic Indicators in Chicago\n",
"\n",
"The city of Chicago released a dataset of socioeconomic data to the Chicago City Portal.\n",
"This dataset contains a selection of six socioeconomic indicators of public health significance and a “hardship index,” for each Chicago community area, for the years 2008 – 2012.\n",
"\n",
"Scores on the hardship index can range from 1 to 100, with a higher index number representing a greater level of hardship.\n",
"\n",
"A detailed description of the dataset can be found on [the city of Chicago's website](\n",
"https://data.cityofchicago.org/Health-Human-Services/Census-Data-Selected-socioeconomic-indicators-in-C/kn9c-c2s2), but to summarize, the dataset has the following variables:\n",
"\n",
"* **Community Area Number** (`ca`): Used to uniquely identify each row of the dataset\n",
"\n",
"* **Community Area Name** (`community_area_name`): The name of the region in the city of Chicago \n",
"\n",
"* **Percent of Housing Crowded** (`percent_of_housing_crowded`): Percent of occupied housing units with more than one person per room\n",
"\n",
"* **Percent Households Below Poverty** (`percent_households_below_poverty`): Percent of households living below the federal poverty line\n",
"\n",
"* **Percent Aged 16+ Unemployed** (`percent_aged_16_unemployed`): Percent of persons over the age of 16 years that are unemployed\n",
"\n",
"* **Percent Aged 25+ without High School Diploma** (`percent_aged_25_without_high_school_diploma`): Percent of persons over the age of 25 years without a high school education\n",
"\n",
"* **Percent Aged Under** 18 or Over 64:Percent of population under 18 or over 64 years of age (`percent_aged_under_18_or_over_64`): (ie. dependents)\n",
"\n",
"* **Per Capita Income** (`per_capita_income_`): Community Area per capita income is estimated as the sum of tract-level aggragate incomes divided by the total population\n",
"\n",
"* **Hardship Index** (`hardship_index`): Score that incorporates each of the six selected socioeconomic indicators\n",
"\n",
"In this Lab, we'll take a look at the variables in the socioeconomic indicators dataset and do some basic analysis with Python.\n"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Connect to the database\n",
"Let us first load the SQL extension and establish a connection with the database"
]
},
{
"cell_type": "code",
"execution_count": 16,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"The sql extension is already loaded. To reload it, use:\n",
" %reload_ext sql\n"
]
}
],
"source": [
"%load_ext sql"
]
},
{
"cell_type": "code",
"execution_count": 19,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"'Connected: mwt06885@BLUDB'"
]
},
"execution_count": 19,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# Remember the connection string is of the format:\n",
"# %sql ibm_db_sa://my-username:my-password@my-hostname:my-port/my-db-name\n",
"# Enter the connection string for your Db2 on Cloud database instance below\n",
"# i.e. copy after db2:// from the URI string in Service Credentials of your Db2 instance. Remove the double quotes at the end.\n",
"%sql ibm_db_sa://mwt06885:hjbgzhq%2Bgwfqrr83@dashdb-txn-sbox-yp-lon02-01.services.eu-gb.bluemix.net:50000/BLUDB"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Store the dataset in a Table\n",
"##### In many cases the dataset to be analyzed is available as a .CSV (comma separated values) file, perhaps on the internet. To analyze the data using SQL, it first needs to be stored in the database.\n",
"\n",
"##### We will first read the dataset source .CSV from the internet into pandas dataframe\n",
"\n",
"##### Then we need to create a table in our Db2 database to store the dataset. The PERSIST command in SQL \"magic\" simplifies the process of table creation and writing the data from a `pandas` dataframe into the table"
]
},
{
"cell_type": "code",
"execution_count": 20,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" * ibm_db_sa://mwt06885:***@dashdb-txn-sbox-yp-lon02-01.services.eu-gb.bluemix.net:50000/BLUDB\n",
" ibm_db_sa://pcc40744:***@dashdb-txn-sbox-yp-lon02-02.services.eu-gb.bluemix.net:50000/BLUDB\n"
]
},
{
"data": {
"text/plain": [
"'Persisted chicago_socioeconomic_data'"
]
},
"execution_count": 20,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"import pandas\n",
"chicago_socioeconomic_data = pandas.read_csv('https://data.cityofchicago.org/resource/jcxq-k9xf.csv')\n",
"%sql PERSIST chicago_socioeconomic_data"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"##### You can verify that the table creation was successful by making a basic query like:"
]
},
{
"cell_type": "code",
"execution_count": 21,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" * ibm_db_sa://mwt06885:***@dashdb-txn-sbox-yp-lon02-01.services.eu-gb.bluemix.net:50000/BLUDB\n",
" ibm_db_sa://pcc40744:***@dashdb-txn-sbox-yp-lon02-02.services.eu-gb.bluemix.net:50000/BLUDB\n",
"Done.\n"
]
},
{
"data": {
"text/html": [
"<table>\n",
" <tr>\n",
" <th>index</th>\n",
" <th>ca</th>\n",
" <th>community_area_name</th>\n",
" <th>percent_of_housing_crowded</th>\n",
" <th>percent_households_below_poverty</th>\n",
" <th>percent_aged_16_unemployed</th>\n",
" <th>percent_aged_25_without_high_school_diploma</th>\n",
" <th>percent_aged_under_18_or_over_64</th>\n",
" <th>per_capita_income_</th>\n",
" <th>hardship_index</th>\n",
" </tr>\n",
" <tr>\n",
" <td>0</td>\n",
" <td>1.0</td>\n",
" <td>Rogers Park</td>\n",
" <td>7.7</td>\n",
" <td>23.6</td>\n",
" <td>8.7</td>\n",
" <td>18.2</td>\n",
" <td>27.5</td>\n",
" <td>23939</td>\n",
" <td>39.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>1</td>\n",
" <td>2.0</td>\n",
" <td>West Ridge</td>\n",
" <td>7.8</td>\n",
" <td>17.2</td>\n",
" <td>8.8</td>\n",
" <td>20.8</td>\n",
" <td>38.5</td>\n",
" <td>23040</td>\n",
" <td>46.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>2</td>\n",
" <td>3.0</td>\n",
" <td>Uptown</td>\n",
" <td>3.8</td>\n",
" <td>24.0</td>\n",
" <td>8.9</td>\n",
" <td>11.8</td>\n",
" <td>22.2</td>\n",
" <td>35787</td>\n",
" <td>20.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>3</td>\n",
" <td>4.0</td>\n",
" <td>Lincoln Square</td>\n",
" <td>3.4</td>\n",
" <td>10.9</td>\n",
" <td>8.2</td>\n",
" <td>13.4</td>\n",
" <td>25.5</td>\n",
" <td>37524</td>\n",
" <td>17.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>4</td>\n",
" <td>5.0</td>\n",
" <td>North Center</td>\n",
" <td>0.3</td>\n",
" <td>7.5</td>\n",
" <td>5.2</td>\n",
" <td>4.5</td>\n",
" <td>26.2</td>\n",
" <td>57123</td>\n",
" <td>6.0</td>\n",
" </tr>\n",
"</table>"
],
"text/plain": [
"[(0, 1.0, 'Rogers Park', 7.7, 23.6, 8.7, 18.2, 27.5, 23939, 39.0),\n",
" (1, 2.0, 'West Ridge', 7.8, 17.2, 8.8, 20.8, 38.5, 23040, 46.0),\n",
" (2, 3.0, 'Uptown', 3.8, 24.0, 8.9, 11.8, 22.2, 35787, 20.0),\n",
" (3, 4.0, 'Lincoln Square', 3.4, 10.9, 8.2, 13.4, 25.5, 37524, 17.0),\n",
" (4, 5.0, 'North Center', 0.3, 7.5, 5.2, 4.5, 26.2, 57123, 6.0)]"
]
},
"execution_count": 21,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%sql SELECT * FROM chicago_socioeconomic_data limit 5;"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Problems\n",
"\n",
"### Problem 1\n",
"\n",
"##### How many rows are in the dataset?"
]
},
{
"cell_type": "code",
"execution_count": 24,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" * ibm_db_sa://mwt06885:***@dashdb-txn-sbox-yp-lon02-01.services.eu-gb.bluemix.net:50000/BLUDB\n",
" ibm_db_sa://pcc40744:***@dashdb-txn-sbox-yp-lon02-02.services.eu-gb.bluemix.net:50000/BLUDB\n",
"Done.\n"
]
},
{
"data": {
"text/html": [
"<table>\n",
" <tr>\n",
" <th>index</th>\n",
" <th>ca</th>\n",
" <th>community_area_name</th>\n",
" <th>percent_of_housing_crowded</th>\n",
" <th>percent_households_below_poverty</th>\n",
" <th>percent_aged_16_unemployed</th>\n",
" <th>percent_aged_25_without_high_school_diploma</th>\n",
" <th>percent_aged_under_18_or_over_64</th>\n",
" <th>per_capita_income_</th>\n",
" <th>hardship_index</th>\n",
" </tr>\n",
" <tr>\n",
" <td>0</td>\n",
" <td>1.0</td>\n",
" <td>Rogers Park</td>\n",
" <td>7.7</td>\n",
" <td>23.6</td>\n",
" <td>8.7</td>\n",
" <td>18.2</td>\n",
" <td>27.5</td>\n",
" <td>23939</td>\n",
" <td>39.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>1</td>\n",
" <td>2.0</td>\n",
" <td>West Ridge</td>\n",
" <td>7.8</td>\n",
" <td>17.2</td>\n",
" <td>8.8</td>\n",
" <td>20.8</td>\n",
" <td>38.5</td>\n",
" <td>23040</td>\n",
" <td>46.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>2</td>\n",
" <td>3.0</td>\n",
" <td>Uptown</td>\n",
" <td>3.8</td>\n",
" <td>24.0</td>\n",
" <td>8.9</td>\n",
" <td>11.8</td>\n",
" <td>22.2</td>\n",
" <td>35787</td>\n",
" <td>20.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>3</td>\n",
" <td>4.0</td>\n",
" <td>Lincoln Square</td>\n",
" <td>3.4</td>\n",
" <td>10.9</td>\n",
" <td>8.2</td>\n",
" <td>13.4</td>\n",
" <td>25.5</td>\n",
" <td>37524</td>\n",
" <td>17.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>4</td>\n",
" <td>5.0</td>\n",
" <td>North Center</td>\n",
" <td>0.3</td>\n",
" <td>7.5</td>\n",
" <td>5.2</td>\n",
" <td>4.5</td>\n",
" <td>26.2</td>\n",
" <td>57123</td>\n",
" <td>6.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>5</td>\n",
" <td>6.0</td>\n",
" <td>Lake View</td>\n",
" <td>1.1</td>\n",
" <td>11.4</td>\n",
" <td>4.7</td>\n",
" <td>2.6</td>\n",
" <td>17.0</td>\n",
" <td>60058</td>\n",
" <td>5.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>6</td>\n",
" <td>7.0</td>\n",
" <td>Lincoln Park</td>\n",
" <td>0.8</td>\n",
" <td>12.3</td>\n",
" <td>5.1</td>\n",
" <td>3.6</td>\n",
" <td>21.5</td>\n",
" <td>71551</td>\n",
" <td>2.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>7</td>\n",
" <td>8.0</td>\n",
" <td>Near North Side</td>\n",
" <td>1.9</td>\n",
" <td>12.9</td>\n",
" <td>7.0</td>\n",
" <td>2.5</td>\n",
" <td>22.6</td>\n",
" <td>88669</td>\n",
" <td>1.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>8</td>\n",
" <td>9.0</td>\n",
" <td>Edison Park</td>\n",
" <td>1.1</td>\n",
" <td>3.3</td>\n",
" <td>6.5</td>\n",
" <td>7.4</td>\n",
" <td>35.3</td>\n",
" <td>40959</td>\n",
" <td>8.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>9</td>\n",
" <td>10.0</td>\n",
" <td>Norwood Park</td>\n",
" <td>2.0</td>\n",
" <td>5.4</td>\n",
" <td>9.0</td>\n",
" <td>11.5</td>\n",
" <td>39.5</td>\n",
" <td>32875</td>\n",
" <td>21.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>10</td>\n",
" <td>11.0</td>\n",
" <td>Jefferson Park</td>\n",
" <td>2.7</td>\n",
" <td>8.6</td>\n",
" <td>12.4</td>\n",
" <td>13.4</td>\n",
" <td>35.5</td>\n",
" <td>27751</td>\n",
" <td>25.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>11</td>\n",
" <td>12.0</td>\n",
" <td>Forest Glen</td>\n",
" <td>1.1</td>\n",
" <td>7.5</td>\n",
" <td>6.8</td>\n",
" <td>4.9</td>\n",
" <td>40.5</td>\n",
" <td>44164</td>\n",
" <td>11.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>12</td>\n",
" <td>13.0</td>\n",
" <td>North Park</td>\n",
" <td>3.9</td>\n",
" <td>13.2</td>\n",
" <td>9.9</td>\n",
" <td>14.4</td>\n",
" <td>39.0</td>\n",
" <td>26576</td>\n",
" <td>33.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>13</td>\n",
" <td>14.0</td>\n",
" <td>Albany Park</td>\n",
" <td>11.3</td>\n",
" <td>19.2</td>\n",
" <td>10.0</td>\n",
" <td>32.9</td>\n",
" <td>32.0</td>\n",
" <td>21323</td>\n",
" <td>53.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>14</td>\n",
" <td>15.0</td>\n",
" <td>Portage Park</td>\n",
" <td>4.1</td>\n",
" <td>11.6</td>\n",
" <td>12.6</td>\n",
" <td>19.3</td>\n",
" <td>34.0</td>\n",
" <td>24336</td>\n",
" <td>35.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>15</td>\n",
" <td>16.0</td>\n",
" <td>Irving Park</td>\n",
" <td>6.3</td>\n",
" <td>13.1</td>\n",
" <td>10.0</td>\n",
" <td>22.4</td>\n",
" <td>31.6</td>\n",
" <td>27249</td>\n",
" <td>34.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>16</td>\n",
" <td>17.0</td>\n",
" <td>Dunning</td>\n",
" <td>5.2</td>\n",
" <td>10.6</td>\n",
" <td>10.0</td>\n",
" <td>16.2</td>\n",
" <td>33.6</td>\n",
" <td>26282</td>\n",
" <td>28.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>17</td>\n",
" <td>18.0</td>\n",
" <td>Montclaire</td>\n",
" <td>8.1</td>\n",
" <td>15.3</td>\n",
" <td>13.8</td>\n",
" <td>23.5</td>\n",
" <td>38.6</td>\n",
" <td>22014</td>\n",
" <td>50.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>18</td>\n",
" <td>19.0</td>\n",
" <td>Belmont Cragin</td>\n",
" <td>10.8</td>\n",
" <td>18.7</td>\n",
" <td>14.6</td>\n",
" <td>37.3</td>\n",
" <td>37.3</td>\n",
" <td>15461</td>\n",
" <td>70.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>19</td>\n",
" <td>20.0</td>\n",
" <td>Hermosa</td>\n",
" <td>6.9</td>\n",
" <td>20.5</td>\n",
" <td>13.1</td>\n",
" <td>41.6</td>\n",
" <td>36.4</td>\n",
" <td>15089</td>\n",
" <td>71.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>20</td>\n",
" <td>21.0</td>\n",
" <td>Avondale</td>\n",
" <td>6.0</td>\n",
" <td>15.3</td>\n",
" <td>9.2</td>\n",
" <td>24.7</td>\n",
" <td>31.0</td>\n",
" <td>20039</td>\n",
" <td>42.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>21</td>\n",
" <td>22.0</td>\n",
" <td>Logan Square</td>\n",
" <td>3.2</td>\n",
" <td>16.8</td>\n",
" <td>8.2</td>\n",
" <td>14.8</td>\n",
" <td>26.2</td>\n",
" <td>31908</td>\n",
" <td>23.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>22</td>\n",
" <td>23.0</td>\n",
" <td>Humboldt park</td>\n",
" <td>14.8</td>\n",
" <td>33.9</td>\n",
" <td>17.3</td>\n",
" <td>35.4</td>\n",
" <td>38.0</td>\n",
" <td>13781</td>\n",
" <td>85.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>23</td>\n",
" <td>24.0</td>\n",
" <td>West Town</td>\n",
" <td>2.3</td>\n",
" <td>14.7</td>\n",
" <td>6.6</td>\n",
" <td>12.9</td>\n",
" <td>21.7</td>\n",
" <td>43198</td>\n",
" <td>10.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>24</td>\n",
" <td>25.0</td>\n",
" <td>Austin</td>\n",
" <td>6.3</td>\n",
" <td>28.6</td>\n",
" <td>22.6</td>\n",
" <td>24.4</td>\n",
" <td>37.9</td>\n",
" <td>15957</td>\n",
" <td>73.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>25</td>\n",
" <td>26.0</td>\n",
" <td>West Garfield Park</td>\n",
" <td>9.4</td>\n",
" <td>41.7</td>\n",
" <td>25.8</td>\n",
" <td>24.5</td>\n",
" <td>43.6</td>\n",
" <td>10934</td>\n",
" <td>92.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>26</td>\n",
" <td>27.0</td>\n",
" <td>East Garfield Park</td>\n",
" <td>8.2</td>\n",
" <td>42.4</td>\n",
" <td>19.6</td>\n",
" <td>21.3</td>\n",
" <td>43.2</td>\n",
" <td>12961</td>\n",
" <td>83.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>27</td>\n",
" <td>28.0</td>\n",
" <td>Near West Side</td>\n",
" <td>3.8</td>\n",
" <td>20.6</td>\n",
" <td>10.7</td>\n",
" <td>9.6</td>\n",
" <td>22.2</td>\n",
" <td>44689</td>\n",
" <td>15.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>28</td>\n",
" <td>29.0</td>\n",
" <td>North Lawndale</td>\n",
" <td>7.4</td>\n",
" <td>43.1</td>\n",
" <td>21.2</td>\n",
" <td>27.6</td>\n",
" <td>42.7</td>\n",
" <td>12034</td>\n",
" <td>87.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>29</td>\n",
" <td>30.0</td>\n",
" <td>South Lawndale</td>\n",
" <td>15.2</td>\n",
" <td>30.7</td>\n",
" <td>15.8</td>\n",
" <td>54.8</td>\n",
" <td>33.8</td>\n",
" <td>10402</td>\n",
" <td>96.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>30</td>\n",
" <td>31.0</td>\n",
" <td>Lower West Side</td>\n",
" <td>9.6</td>\n",
" <td>25.8</td>\n",
" <td>15.8</td>\n",
" <td>40.7</td>\n",
" <td>32.6</td>\n",
" <td>16444</td>\n",
" <td>76.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>31</td>\n",
" <td>32.0</td>\n",
" <td>Loop</td>\n",
" <td>1.5</td>\n",
" <td>14.7</td>\n",
" <td>5.7</td>\n",
" <td>3.1</td>\n",
" <td>13.5</td>\n",
" <td>65526</td>\n",
" <td>3.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>32</td>\n",
" <td>33.0</td>\n",
" <td>Near South Side</td>\n",
" <td>1.3</td>\n",
" <td>13.8</td>\n",
" <td>4.9</td>\n",
" <td>7.4</td>\n",
" <td>21.8</td>\n",
" <td>59077</td>\n",
" <td>7.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>33</td>\n",
" <td>34.0</td>\n",
" <td>Armour Square</td>\n",
" <td>5.7</td>\n",
" <td>40.1</td>\n",
" <td>16.7</td>\n",
" <td>34.5</td>\n",
" <td>38.3</td>\n",
" <td>16148</td>\n",
" <td>82.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>34</td>\n",
" <td>35.0</td>\n",
" <td>Douglas</td>\n",
" <td>1.8</td>\n",
" <td>29.6</td>\n",
" <td>18.2</td>\n",
" <td>14.3</td>\n",
" <td>30.7</td>\n",
" <td>23791</td>\n",
" <td>47.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>35</td>\n",
" <td>36.0</td>\n",
" <td>Oakland</td>\n",
" <td>1.3</td>\n",
" <td>39.7</td>\n",
" <td>28.7</td>\n",
" <td>18.4</td>\n",
" <td>40.4</td>\n",
" <td>19252</td>\n",
" <td>78.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>36</td>\n",
" <td>37.0</td>\n",
" <td>Fuller Park</td>\n",
" <td>3.2</td>\n",
" <td>51.2</td>\n",
" <td>33.9</td>\n",
" <td>26.6</td>\n",
" <td>44.9</td>\n",
" <td>10432</td>\n",
" <td>97.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>37</td>\n",
" <td>38.0</td>\n",
" <td>Grand Boulevard</td>\n",
" <td>3.3</td>\n",
" <td>29.3</td>\n",
" <td>24.3</td>\n",
" <td>15.9</td>\n",
" <td>39.5</td>\n",
" <td>23472</td>\n",
" <td>57.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>38</td>\n",
" <td>39.0</td>\n",
" <td>Kenwood</td>\n",
" <td>2.4</td>\n",
" <td>21.7</td>\n",
" <td>15.7</td>\n",
" <td>11.3</td>\n",
" <td>35.4</td>\n",
" <td>35911</td>\n",
" <td>26.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>39</td>\n",
" <td>40.0</td>\n",
" <td>Washington Park</td>\n",
" <td>5.6</td>\n",
" <td>42.1</td>\n",
" <td>28.6</td>\n",
" <td>25.4</td>\n",
" <td>42.8</td>\n",
" <td>13785</td>\n",
" <td>88.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>40</td>\n",
" <td>41.0</td>\n",
" <td>Hyde Park</td>\n",
" <td>1.5</td>\n",
" <td>18.4</td>\n",
" <td>8.4</td>\n",
" <td>4.3</td>\n",
" <td>26.2</td>\n",
" <td>39056</td>\n",
" <td>14.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>41</td>\n",
" <td>42.0</td>\n",
" <td>Woodlawn</td>\n",
" <td>2.9</td>\n",
" <td>30.7</td>\n",
" <td>23.4</td>\n",
" <td>16.5</td>\n",
" <td>36.1</td>\n",
" <td>18672</td>\n",
" <td>58.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>42</td>\n",
" <td>43.0</td>\n",
" <td>South Shore</td>\n",
" <td>2.8</td>\n",
" <td>31.1</td>\n",
" <td>20.0</td>\n",
" <td>14.0</td>\n",
" <td>35.7</td>\n",
" <td>19398</td>\n",
" <td>55.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>43</td>\n",
" <td>44.0</td>\n",
" <td>Chatham</td>\n",
" <td>3.3</td>\n",
" <td>27.8</td>\n",
" <td>24.0</td>\n",
" <td>14.5</td>\n",
" <td>40.3</td>\n",
" <td>18881</td>\n",
" <td>60.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>44</td>\n",
" <td>45.0</td>\n",
" <td>Avalon Park</td>\n",
" <td>1.4</td>\n",
" <td>17.2</td>\n",
" <td>21.1</td>\n",
" <td>10.6</td>\n",
" <td>39.3</td>\n",
" <td>24454</td>\n",
" <td>41.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>45</td>\n",
" <td>46.0</td>\n",
" <td>South Chicago</td>\n",
" <td>4.7</td>\n",
" <td>29.8</td>\n",
" <td>19.7</td>\n",
" <td>26.6</td>\n",
" <td>41.1</td>\n",
" <td>16579</td>\n",
" <td>75.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>46</td>\n",
" <td>47.0</td>\n",
" <td>Burnside</td>\n",
" <td>6.8</td>\n",
" <td>33.0</td>\n",
" <td>18.6</td>\n",
" <td>19.3</td>\n",
" <td>42.7</td>\n",
" <td>12515</td>\n",
" <td>79.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>47</td>\n",
" <td>48.0</td>\n",
" <td>Calumet Heights</td>\n",
" <td>2.1</td>\n",
" <td>11.5</td>\n",
" <td>20.0</td>\n",
" <td>11.0</td>\n",
" <td>44.0</td>\n",
" <td>28887</td>\n",
" <td>38.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>48</td>\n",
" <td>49.0</td>\n",
" <td>Roseland</td>\n",
" <td>2.5</td>\n",
" <td>19.8</td>\n",
" <td>20.3</td>\n",
" <td>16.9</td>\n",
" <td>41.2</td>\n",
" <td>17949</td>\n",
" <td>52.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>49</td>\n",
" <td>50.0</td>\n",
" <td>Pullman</td>\n",
" <td>1.5</td>\n",
" <td>21.6</td>\n",
" <td>22.8</td>\n",
" <td>13.1</td>\n",
" <td>38.6</td>\n",
" <td>20588</td>\n",
" <td>51.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>50</td>\n",
" <td>51.0</td>\n",
" <td>South Deering</td>\n",
" <td>4.0</td>\n",
" <td>29.2</td>\n",
" <td>16.3</td>\n",
" <td>21.0</td>\n",
" <td>39.5</td>\n",
" <td>14685</td>\n",
" <td>65.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>51</td>\n",
" <td>52.0</td>\n",
" <td>East Side</td>\n",
" <td>6.8</td>\n",
" <td>19.2</td>\n",
" <td>12.1</td>\n",
" <td>31.9</td>\n",
" <td>42.8</td>\n",
" <td>17104</td>\n",
" <td>64.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>52</td>\n",
" <td>53.0</td>\n",
" <td>West Pullman</td>\n",
" <td>3.3</td>\n",
" <td>25.9</td>\n",
" <td>19.4</td>\n",
" <td>20.5</td>\n",
" <td>42.1</td>\n",
" <td>16563</td>\n",
" <td>62.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>53</td>\n",
" <td>54.0</td>\n",
" <td>Riverdale</td>\n",
" <td>5.8</td>\n",
" <td>56.5</td>\n",
" <td>34.6</td>\n",
" <td>27.5</td>\n",
" <td>51.5</td>\n",
" <td>8201</td>\n",
" <td>98.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>54</td>\n",
" <td>55.0</td>\n",
" <td>Hegewisch</td>\n",
" <td>3.3</td>\n",
" <td>17.1</td>\n",
" <td>9.6</td>\n",
" <td>19.2</td>\n",
" <td>42.9</td>\n",
" <td>22677</td>\n",
" <td>44.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>55</td>\n",
" <td>56.0</td>\n",
" <td>Garfield Ridge</td>\n",
" <td>2.6</td>\n",
" <td>8.8</td>\n",
" <td>11.3</td>\n",
" <td>19.3</td>\n",
" <td>38.1</td>\n",
" <td>26353</td>\n",
" <td>32.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>56</td>\n",
" <td>57.0</td>\n",
" <td>Archer Heights</td>\n",
" <td>8.5</td>\n",
" <td>14.1</td>\n",
" <td>16.5</td>\n",
" <td>35.9</td>\n",
" <td>39.2</td>\n",
" <td>16134</td>\n",
" <td>67.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>57</td>\n",
" <td>58.0</td>\n",
" <td>Brighton Park</td>\n",
" <td>14.4</td>\n",
" <td>23.6</td>\n",
" <td>13.9</td>\n",
" <td>45.1</td>\n",
" <td>39.3</td>\n",
" <td>13089</td>\n",
" <td>84.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>58</td>\n",
" <td>59.0</td>\n",
" <td>McKinley Park</td>\n",
" <td>7.2</td>\n",
" <td>18.7</td>\n",
" <td>13.4</td>\n",
" <td>32.9</td>\n",
" <td>35.6</td>\n",
" <td>16954</td>\n",
" <td>61.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>59</td>\n",
" <td>60.0</td>\n",
" <td>Bridgeport</td>\n",
" <td>4.5</td>\n",
" <td>18.9</td>\n",
" <td>13.7</td>\n",
" <td>22.2</td>\n",
" <td>31.3</td>\n",
" <td>22694</td>\n",
" <td>43.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>60</td>\n",
" <td>61.0</td>\n",
" <td>New City</td>\n",
" <td>11.9</td>\n",
" <td>29.0</td>\n",
" <td>23.0</td>\n",
" <td>41.5</td>\n",
" <td>38.9</td>\n",
" <td>12765</td>\n",
" <td>91.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>61</td>\n",
" <td>62.0</td>\n",
" <td>West Elsdon</td>\n",
" <td>11.1</td>\n",
" <td>15.6</td>\n",
" <td>16.7</td>\n",
" <td>37.0</td>\n",
" <td>37.7</td>\n",
" <td>15754</td>\n",
" <td>69.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>62</td>\n",
" <td>63.0</td>\n",
" <td>Gage Park</td>\n",
" <td>15.8</td>\n",
" <td>23.4</td>\n",
" <td>18.2</td>\n",
" <td>51.5</td>\n",
" <td>38.8</td>\n",
" <td>12171</td>\n",
" <td>93.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>63</td>\n",
" <td>64.0</td>\n",
" <td>Clearing</td>\n",
" <td>2.7</td>\n",
" <td>8.9</td>\n",
" <td>9.5</td>\n",
" <td>18.8</td>\n",
" <td>37.6</td>\n",
" <td>25113</td>\n",
" <td>29.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>64</td>\n",
" <td>65.0</td>\n",
" <td>West Lawn</td>\n",
" <td>5.8</td>\n",
" <td>14.9</td>\n",
" <td>9.6</td>\n",
" <td>33.6</td>\n",
" <td>39.6</td>\n",
" <td>16907</td>\n",
" <td>56.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>65</td>\n",
" <td>66.0</td>\n",
" <td>Chicago Lawn</td>\n",
" <td>7.6</td>\n",
" <td>27.9</td>\n",
" <td>17.1</td>\n",
" <td>31.2</td>\n",
" <td>40.6</td>\n",
" <td>13231</td>\n",
" <td>80.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>66</td>\n",
" <td>67.0</td>\n",
" <td>West Englewood</td>\n",
" <td>4.8</td>\n",
" <td>34.4</td>\n",
" <td>35.9</td>\n",
" <td>26.3</td>\n",
" <td>40.7</td>\n",
" <td>11317</td>\n",
" <td>89.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>67</td>\n",
" <td>68.0</td>\n",
" <td>Englewood</td>\n",
" <td>3.8</td>\n",
" <td>46.6</td>\n",
" <td>28.0</td>\n",
" <td>28.5</td>\n",
" <td>42.5</td>\n",
" <td>11888</td>\n",
" <td>94.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>68</td>\n",
" <td>69.0</td>\n",
" <td>Greater Grand Crossing</td>\n",
" <td>3.6</td>\n",
" <td>29.6</td>\n",
" <td>23.0</td>\n",
" <td>16.5</td>\n",
" <td>41.0</td>\n",
" <td>17285</td>\n",
" <td>66.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>69</td>\n",
" <td>70.0</td>\n",
" <td>Ashburn</td>\n",
" <td>4.0</td>\n",
" <td>10.4</td>\n",
" <td>11.7</td>\n",
" <td>17.7</td>\n",
" <td>36.9</td>\n",
" <td>23482</td>\n",
" <td>37.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>70</td>\n",
" <td>71.0</td>\n",
" <td>Auburn Gresham</td>\n",
" <td>4.0</td>\n",
" <td>27.6</td>\n",
" <td>28.3</td>\n",
" <td>18.5</td>\n",
" <td>41.9</td>\n",
" <td>15528</td>\n",
" <td>74.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>71</td>\n",
" <td>72.0</td>\n",
" <td>Beverly</td>\n",
" <td>0.9</td>\n",
" <td>5.1</td>\n",
" <td>8.0</td>\n",
" <td>3.7</td>\n",
" <td>40.5</td>\n",
" <td>39523</td>\n",
" <td>12.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>72</td>\n",
" <td>73.0</td>\n",
" <td>Washington Height</td>\n",
" <td>1.1</td>\n",
" <td>16.9</td>\n",
" <td>20.8</td>\n",
" <td>13.7</td>\n",
" <td>42.6</td>\n",
" <td>19713</td>\n",
" <td>48.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>73</td>\n",
" <td>74.0</td>\n",
" <td>Mount Greenwood</td>\n",
" <td>1.0</td>\n",
" <td>3.4</td>\n",
" <td>8.7</td>\n",
" <td>4.3</td>\n",
" <td>36.8</td>\n",
" <td>34381</td>\n",
" <td>16.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>74</td>\n",
" <td>75.0</td>\n",
" <td>Morgan Park</td>\n",
" <td>0.8</td>\n",
" <td>13.2</td>\n",
" <td>15.0</td>\n",
" <td>10.8</td>\n",
" <td>40.3</td>\n",
" <td>27149</td>\n",
" <td>30.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>75</td>\n",
" <td>76.0</td>\n",
" <td>O&#x27;Hare</td>\n",
" <td>3.6</td>\n",
" <td>15.4</td>\n",
" <td>7.1</td>\n",
" <td>10.9</td>\n",
" <td>30.3</td>\n",
" <td>25828</td>\n",
" <td>24.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>76</td>\n",
" <td>77.0</td>\n",
" <td>Edgewater</td>\n",
" <td>4.1</td>\n",
" <td>18.2</td>\n",
" <td>9.2</td>\n",
" <td>9.7</td>\n",
" <td>23.8</td>\n",
" <td>33385</td>\n",
" <td>19.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>77</td>\n",
" <td>None</td>\n",
" <td>CHICAGO</td>\n",
" <td>4.7</td>\n",
" <td>19.7</td>\n",
" <td>12.9</td>\n",
" <td>19.5</td>\n",
" <td>33.5</td>\n",
" <td>28202</td>\n",
" <td>None</td>\n",
" </tr>\n",
"</table>"
],
"text/plain": [
"[(0, 1.0, 'Rogers Park', 7.7, 23.6, 8.7, 18.2, 27.5, 23939, 39.0),\n",
" (1, 2.0, 'West Ridge', 7.8, 17.2, 8.8, 20.8, 38.5, 23040, 46.0),\n",
" (2, 3.0, 'Uptown', 3.8, 24.0, 8.9, 11.8, 22.2, 35787, 20.0),\n",
" (3, 4.0, 'Lincoln Square', 3.4, 10.9, 8.2, 13.4, 25.5, 37524, 17.0),\n",
" (4, 5.0, 'North Center', 0.3, 7.5, 5.2, 4.5, 26.2, 57123, 6.0),\n",
" (5, 6.0, 'Lake View', 1.1, 11.4, 4.7, 2.6, 17.0, 60058, 5.0),\n",
" (6, 7.0, 'Lincoln Park', 0.8, 12.3, 5.1, 3.6, 21.5, 71551, 2.0),\n",
" (7, 8.0, 'Near North Side', 1.9, 12.9, 7.0, 2.5, 22.6, 88669, 1.0),\n",
" (8, 9.0, 'Edison Park', 1.1, 3.3, 6.5, 7.4, 35.3, 40959, 8.0),\n",
" (9, 10.0, 'Norwood Park', 2.0, 5.4, 9.0, 11.5, 39.5, 32875, 21.0),\n",
" (10, 11.0, 'Jefferson Park', 2.7, 8.6, 12.4, 13.4, 35.5, 27751, 25.0),\n",
" (11, 12.0, 'Forest Glen', 1.1, 7.5, 6.8, 4.9, 40.5, 44164, 11.0),\n",
" (12, 13.0, 'North Park', 3.9, 13.2, 9.9, 14.4, 39.0, 26576, 33.0),\n",
" (13, 14.0, 'Albany Park', 11.3, 19.2, 10.0, 32.9, 32.0, 21323, 53.0),\n",
" (14, 15.0, 'Portage Park', 4.1, 11.6, 12.6, 19.3, 34.0, 24336, 35.0),\n",
" (15, 16.0, 'Irving Park', 6.3, 13.1, 10.0, 22.4, 31.6, 27249, 34.0),\n",
" (16, 17.0, 'Dunning', 5.2, 10.6, 10.0, 16.2, 33.6, 26282, 28.0),\n",
" (17, 18.0, 'Montclaire', 8.1, 15.3, 13.8, 23.5, 38.6, 22014, 50.0),\n",
" (18, 19.0, 'Belmont Cragin', 10.8, 18.7, 14.6, 37.3, 37.3, 15461, 70.0),\n",
" (19, 20.0, 'Hermosa', 6.9, 20.5, 13.1, 41.6, 36.4, 15089, 71.0),\n",
" (20, 21.0, 'Avondale', 6.0, 15.3, 9.2, 24.7, 31.0, 20039, 42.0),\n",
" (21, 22.0, 'Logan Square', 3.2, 16.8, 8.2, 14.8, 26.2, 31908, 23.0),\n",
" (22, 23.0, 'Humboldt park', 14.8, 33.9, 17.3, 35.4, 38.0, 13781, 85.0),\n",
" (23, 24.0, 'West Town', 2.3, 14.7, 6.6, 12.9, 21.7, 43198, 10.0),\n",
" (24, 25.0, 'Austin', 6.3, 28.6, 22.6, 24.4, 37.9, 15957, 73.0),\n",
" (25, 26.0, 'West Garfield Park', 9.4, 41.7, 25.8, 24.5, 43.6, 10934, 92.0),\n",
" (26, 27.0, 'East Garfield Park', 8.2, 42.4, 19.6, 21.3, 43.2, 12961, 83.0),\n",
" (27, 28.0, 'Near West Side', 3.8, 20.6, 10.7, 9.6, 22.2, 44689, 15.0),\n",
" (28, 29.0, 'North Lawndale', 7.4, 43.1, 21.2, 27.6, 42.7, 12034, 87.0),\n",
" (29, 30.0, 'South Lawndale', 15.2, 30.7, 15.8, 54.8, 33.8, 10402, 96.0),\n",
" (30, 31.0, 'Lower West Side', 9.6, 25.8, 15.8, 40.7, 32.6, 16444, 76.0),\n",
" (31, 32.0, 'Loop', 1.5, 14.7, 5.7, 3.1, 13.5, 65526, 3.0),\n",
" (32, 33.0, 'Near South Side', 1.3, 13.8, 4.9, 7.4, 21.8, 59077, 7.0),\n",
" (33, 34.0, 'Armour Square', 5.7, 40.1, 16.7, 34.5, 38.3, 16148, 82.0),\n",
" (34, 35.0, 'Douglas', 1.8, 29.6, 18.2, 14.3, 30.7, 23791, 47.0),\n",
" (35, 36.0, 'Oakland', 1.3, 39.7, 28.7, 18.4, 40.4, 19252, 78.0),\n",
" (36, 37.0, 'Fuller Park', 3.2, 51.2, 33.9, 26.6, 44.9, 10432, 97.0),\n",
" (37, 38.0, 'Grand Boulevard', 3.3, 29.3, 24.3, 15.9, 39.5, 23472, 57.0),\n",
" (38, 39.0, 'Kenwood', 2.4, 21.7, 15.7, 11.3, 35.4, 35911, 26.0),\n",
" (39, 40.0, 'Washington Park', 5.6, 42.1, 28.6, 25.4, 42.8, 13785, 88.0),\n",
" (40, 41.0, 'Hyde Park', 1.5, 18.4, 8.4, 4.3, 26.2, 39056, 14.0),\n",
" (41, 42.0, 'Woodlawn', 2.9, 30.7, 23.4, 16.5, 36.1, 18672, 58.0),\n",
" (42, 43.0, 'South Shore', 2.8, 31.1, 20.0, 14.0, 35.7, 19398, 55.0),\n",
" (43, 44.0, 'Chatham', 3.3, 27.8, 24.0, 14.5, 40.3, 18881, 60.0),\n",
" (44, 45.0, 'Avalon Park', 1.4, 17.2, 21.1, 10.6, 39.3, 24454, 41.0),\n",
" (45, 46.0, 'South Chicago', 4.7, 29.8, 19.7, 26.6, 41.1, 16579, 75.0),\n",
" (46, 47.0, 'Burnside', 6.8, 33.0, 18.6, 19.3, 42.7, 12515, 79.0),\n",
" (47, 48.0, 'Calumet Heights', 2.1, 11.5, 20.0, 11.0, 44.0, 28887, 38.0),\n",
" (48, 49.0, 'Roseland', 2.5, 19.8, 20.3, 16.9, 41.2, 17949, 52.0),\n",
" (49, 50.0, 'Pullman', 1.5, 21.6, 22.8, 13.1, 38.6, 20588, 51.0),\n",
" (50, 51.0, 'South Deering', 4.0, 29.2, 16.3, 21.0, 39.5, 14685, 65.0),\n",
" (51, 52.0, 'East Side', 6.8, 19.2, 12.1, 31.9, 42.8, 17104, 64.0),\n",
" (52, 53.0, 'West Pullman', 3.3, 25.9, 19.4, 20.5, 42.1, 16563, 62.0),\n",
" (53, 54.0, 'Riverdale', 5.8, 56.5, 34.6, 27.5, 51.5, 8201, 98.0),\n",
" (54, 55.0, 'Hegewisch', 3.3, 17.1, 9.6, 19.2, 42.9, 22677, 44.0),\n",
" (55, 56.0, 'Garfield Ridge', 2.6, 8.8, 11.3, 19.3, 38.1, 26353, 32.0),\n",
" (56, 57.0, 'Archer Heights', 8.5, 14.1, 16.5, 35.9, 39.2, 16134, 67.0),\n",
" (57, 58.0, 'Brighton Park', 14.4, 23.6, 13.9, 45.1, 39.3, 13089, 84.0),\n",
" (58, 59.0, 'McKinley Park', 7.2, 18.7, 13.4, 32.9, 35.6, 16954, 61.0),\n",
" (59, 60.0, 'Bridgeport', 4.5, 18.9, 13.7, 22.2, 31.3, 22694, 43.0),\n",
" (60, 61.0, 'New City', 11.9, 29.0, 23.0, 41.5, 38.9, 12765, 91.0),\n",
" (61, 62.0, 'West Elsdon', 11.1, 15.6, 16.7, 37.0, 37.7, 15754, 69.0),\n",
" (62, 63.0, 'Gage Park', 15.8, 23.4, 18.2, 51.5, 38.8, 12171, 93.0),\n",
" (63, 64.0, 'Clearing', 2.7, 8.9, 9.5, 18.8, 37.6, 25113, 29.0),\n",
" (64, 65.0, 'West Lawn', 5.8, 14.9, 9.6, 33.6, 39.6, 16907, 56.0),\n",
" (65, 66.0, 'Chicago Lawn', 7.6, 27.9, 17.1, 31.2, 40.6, 13231, 80.0),\n",
" (66, 67.0, 'West Englewood', 4.8, 34.4, 35.9, 26.3, 40.7, 11317, 89.0),\n",
" (67, 68.0, 'Englewood', 3.8, 46.6, 28.0, 28.5, 42.5, 11888, 94.0),\n",
" (68, 69.0, 'Greater Grand Crossing', 3.6, 29.6, 23.0, 16.5, 41.0, 17285, 66.0),\n",
" (69, 70.0, 'Ashburn', 4.0, 10.4, 11.7, 17.7, 36.9, 23482, 37.0),\n",
" (70, 71.0, 'Auburn Gresham', 4.0, 27.6, 28.3, 18.5, 41.9, 15528, 74.0),\n",
" (71, 72.0, 'Beverly', 0.9, 5.1, 8.0, 3.7, 40.5, 39523, 12.0),\n",
" (72, 73.0, 'Washington Height', 1.1, 16.9, 20.8, 13.7, 42.6, 19713, 48.0),\n",
" (73, 74.0, 'Mount Greenwood', 1.0, 3.4, 8.7, 4.3, 36.8, 34381, 16.0),\n",
" (74, 75.0, 'Morgan Park', 0.8, 13.2, 15.0, 10.8, 40.3, 27149, 30.0),\n",
" (75, 76.0, \"O'Hare\", 3.6, 15.4, 7.1, 10.9, 30.3, 25828, 24.0),\n",
" (76, 77.0, 'Edgewater', 4.1, 18.2, 9.2, 9.7, 23.8, 33385, 19.0),\n",
" (77, None, 'CHICAGO', 4.7, 19.7, 12.9, 19.5, 33.5, 28202, None)]"
]
},
"execution_count": 24,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%sql select * from chicago_socioeconomic_data;"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Double-click __here__ for the solution.\n",
"\n",
"<!-- Hint:\n",
"\n",
"%sql SELECT COUNT(*) FROM chicago_socioeconomic_data;\n",
"\n",
"Correct answer: 78\n",
"\n",
"-->"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Problem 2\n",
"\n",
"##### How many community areas in Chicago have a hardship index greater than 50.0?"
]
},
{
"cell_type": "code",
"execution_count": 26,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" * ibm_db_sa://mwt06885:***@dashdb-txn-sbox-yp-lon02-01.services.eu-gb.bluemix.net:50000/BLUDB\n",
" ibm_db_sa://pcc40744:***@dashdb-txn-sbox-yp-lon02-02.services.eu-gb.bluemix.net:50000/BLUDB\n",
"Done.\n"
]
},
{
"data": {
"text/html": [
"<table>\n",
" <tr>\n",
" <th>hardship_index</th>\n",
" </tr>\n",
" <tr>\n",
" <td>53.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>70.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>71.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>85.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>73.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>92.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>83.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>87.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>96.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>76.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>82.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>78.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>97.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>57.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>88.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>58.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>55.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>60.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>75.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>79.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>52.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>51.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>65.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>64.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>62.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>98.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>67.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>84.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>61.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>91.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>69.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>93.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>56.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>80.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>89.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>94.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>66.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>74.0</td>\n",
" </tr>\n",
"</table>"
],
"text/plain": [
"[(53.0,),\n",
" (70.0,),\n",
" (71.0,),\n",
" (85.0,),\n",
" (73.0,),\n",
" (92.0,),\n",
" (83.0,),\n",
" (87.0,),\n",
" (96.0,),\n",
" (76.0,),\n",
" (82.0,),\n",
" (78.0,),\n",
" (97.0,),\n",
" (57.0,),\n",
" (88.0,),\n",
" (58.0,),\n",
" (55.0,),\n",
" (60.0,),\n",
" (75.0,),\n",
" (79.0,),\n",
" (52.0,),\n",
" (51.0,),\n",
" (65.0,),\n",
" (64.0,),\n",
" (62.0,),\n",
" (98.0,),\n",
" (67.0,),\n",
" (84.0,),\n",
" (61.0,),\n",
" (91.0,),\n",
" (69.0,),\n",
" (93.0,),\n",
" (56.0,),\n",
" (80.0,),\n",
" (89.0,),\n",
" (94.0,),\n",
" (66.0,),\n",
" (74.0,)]"
]
},
"execution_count": 26,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%sql select hardship_index from chicago_socioeconomic_data where hardship_index > 50;"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"collapsed": true,
"jupyter": {
"outputs_hidden": true
}
},
"outputs": [],
"source": []
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Double-click __here__ for the solution.\n",
"\n",
"<!-- Hint:\n",
"\n",
"%sql SELECT COUNT(*) FROM chicago_socioeconomic_data WHERE hardship_index > 50.0;\n",
"Correct answer: 38\n",
"-->\n"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Problem 3\n",
"\n",
"##### What is the maximum value of hardship index in this dataset?"
]
},
{
"cell_type": "code",
"execution_count": 33,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" * ibm_db_sa://mwt06885:***@dashdb-txn-sbox-yp-lon02-01.services.eu-gb.bluemix.net:50000/BLUDB\n",
" ibm_db_sa://pcc40744:***@dashdb-txn-sbox-yp-lon02-02.services.eu-gb.bluemix.net:50000/BLUDB\n",
"Done.\n"
]
},
{
"data": {
"text/html": [
"<table>\n",
" <tr>\n",
" <th>1</th>\n",
" </tr>\n",
" <tr>\n",
" <td>98.0</td>\n",
" </tr>\n",
"</table>"
],
"text/plain": [
"[(98.0,)]"
]
},
"execution_count": 33,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%sql select max(hardship_index) from chicago_socioeconomic_data;"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Double-click __here__ for the solution.\n",
"\n",
"<!-- Hint:\n",
"\n",
"%sql SELECT MAX(hardship_index) FROM chicago_socioeconomic_data;\n",
"\n",
"Correct answer: 98.0\n",
"-->\n"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Problem 4\n",
"\n",
"##### Which community area which has the highest hardship index?\n"
]
},
{
"cell_type": "code",
"execution_count": 37,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" * ibm_db_sa://mwt06885:***@dashdb-txn-sbox-yp-lon02-01.services.eu-gb.bluemix.net:50000/BLUDB\n",
" ibm_db_sa://pcc40744:***@dashdb-txn-sbox-yp-lon02-02.services.eu-gb.bluemix.net:50000/BLUDB\n",
"Done.\n"
]
},
{
"data": {
"text/html": [
"<table>\n",
" <tr>\n",
" <th>community_area_name</th>\n",
" </tr>\n",
" <tr>\n",
" <td>Riverdale</td>\n",
" </tr>\n",
"</table>"
],
"text/plain": [
"[('Riverdale',)]"
]
},
"execution_count": 37,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%sql select community_area_name from chicago_socioeconomic_data where hardship_index = 98;"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Double-click __here__ for the solution.\n",
"\n",
"<!-- Hint:\n",
"\n",
"## We can use the result of the last query to as an input to this query:\n",
"%sql SELECT community_area_name FROM chicago_socioeconomic_data where hardship_index=98.0\n",
"\n",
"## or another option:\n",
"%sql SELECT community_area_name FROM chicago_socioeconomic_data ORDER BY hardship_index DESC NULLS LAST FETCH FIRST ROW ONLY;\n",
"\n",
"## or you can use a sub-query to determine the max hardship index:\n",
"%sql select community_area_name from chicago_socioeconomic_data where hardship_index = ( select max(hardship_index) from chicago_socioeconomic_data ) \n",
"\n",
"Correct answer: 'Riverdale'\n",
"-->"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Problem 5\n",
"\n",
"##### Which Chicago community areas have per-capita incomes greater than $60,000?"
]
},
{
"cell_type": "code",
"execution_count": 39,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" * ibm_db_sa://mwt06885:***@dashdb-txn-sbox-yp-lon02-01.services.eu-gb.bluemix.net:50000/BLUDB\n",
" ibm_db_sa://pcc40744:***@dashdb-txn-sbox-yp-lon02-02.services.eu-gb.bluemix.net:50000/BLUDB\n",
"Done.\n"
]
},
{
"data": {
"text/html": [
"<table>\n",
" <tr>\n",
" <th>community_area_name</th>\n",
" </tr>\n",
" <tr>\n",
" <td>Lake View</td>\n",
" </tr>\n",
" <tr>\n",
" <td>Lincoln Park</td>\n",
" </tr>\n",
" <tr>\n",
" <td>Near North Side</td>\n",
" </tr>\n",
" <tr>\n",
" <td>Loop</td>\n",
" </tr>\n",
"</table>"
],
"text/plain": [
"[('Lake View',), ('Lincoln Park',), ('Near North Side',), ('Loop',)]"
]
},
"execution_count": 39,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%sql select community_area_name from chicago_socioeconomic_data where per_capita_income_ > 60000;"
]
},
{
"cell_type": "code",
"execution_count": 41,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Requirement already satisfied: seaborn in /home/jupyterlab/conda/envs/python/lib/python3.6/site-packages (0.9.0)\n",
"Requirement already satisfied: pandas>=0.15.2 in /home/jupyterlab/conda/envs/python/lib/python3.6/site-packages (from seaborn) (0.25.3)\n",
"Requirement already satisfied: scipy>=0.14.0 in /home/jupyterlab/conda/envs/python/lib/python3.6/site-packages (from seaborn) (1.2.1)\n",
"Requirement already satisfied: numpy>=1.9.3 in /home/jupyterlab/conda/envs/python/lib/python3.6/site-packages (from seaborn) (1.16.2)\n",
"Requirement already satisfied: matplotlib>=1.4.3 in /home/jupyterlab/conda/envs/python/lib/python3.6/site-packages (from seaborn) (3.1.1)\n",
"Requirement already satisfied: pytz>=2017.2 in /home/jupyterlab/conda/envs/python/lib/python3.6/site-packages (from pandas>=0.15.2->seaborn) (2019.3)\n",
"Requirement already satisfied: python-dateutil>=2.6.1 in /home/jupyterlab/conda/envs/python/lib/python3.6/site-packages (from pandas>=0.15.2->seaborn) (2.8.1)\n",
"Requirement already satisfied: pyparsing!=2.0.4,!=2.1.2,!=2.1.6,>=2.0.1 in /home/jupyterlab/conda/envs/python/lib/python3.6/site-packages (from matplotlib>=1.4.3->seaborn) (2.4.5)\n",
"Requirement already satisfied: cycler>=0.10 in /home/jupyterlab/conda/envs/python/lib/python3.6/site-packages (from matplotlib>=1.4.3->seaborn) (0.10.0)\n",
"Requirement already satisfied: kiwisolver>=1.0.1 in /home/jupyterlab/conda/envs/python/lib/python3.6/site-packages (from matplotlib>=1.4.3->seaborn) (1.1.0)\n",
"Requirement already satisfied: six>=1.5 in /home/jupyterlab/conda/envs/python/lib/python3.6/site-packages (from python-dateutil>=2.6.1->pandas>=0.15.2->seaborn) (1.13.0)\n",
"Requirement already satisfied: setuptools in /home/jupyterlab/conda/envs/python/lib/python3.6/site-packages (from kiwisolver>=1.0.1->matplotlib>=1.4.3->seaborn) (41.6.0.post20191101)\n"
]
}
],
"source": [
"!pip install seaborn"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Double-click __here__ for the solution.\n",
"\n",
"<!-- Hint:\n",
"\n",
"%sql SELECT community_area_name FROM chicago_socioeconomic_data WHERE per_capita_income_ > 60000;\n",
"\n",
"Correct answer:Lake View,Lincoln Park, Near North Side, Loop\n",
"-->\n"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Problem 6\n",
"\n",
"##### Create a scatter plot using the variables `per_capita_income_` and `hardship_index`. Explain the correlation between the two variables."
]
},
{
"cell_type": "code",
"execution_count": 45,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" * ibm_db_sa://mwt06885:***@dashdb-txn-sbox-yp-lon02-01.services.eu-gb.bluemix.net:50000/BLUDB\n",
" ibm_db_sa://pcc40744:***@dashdb-txn-sbox-yp-lon02-02.services.eu-gb.bluemix.net:50000/BLUDB\n",
"Done.\n"
]
},
{
"ename": "AttributeError",
"evalue": "module 'seaborn' has no attribute 'joinplot'",
"output_type": "error",
"traceback": [
"\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
"\u001b[0;31mAttributeError\u001b[0m Traceback (most recent call last)",
"\u001b[0;32m<ipython-input-45-86e3f16b1b80>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m 4\u001b[0m \u001b[0mincome_vs_hardship\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mget_ipython\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mrun_line_magic\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'sql'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'select per_capita_income_,hardship_index from chicago_socioeconomic_data;'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 5\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 6\u001b[0;31m \u001b[0mplot\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0msns\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mjoinplot\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mx\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m'per_capita_income_'\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0my\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m'hardship_index'\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0mdata\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mincome_vs_hardship\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mDataFrame\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 7\u001b[0m \u001b[0mplot\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mshow\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
"\u001b[0;31mAttributeError\u001b[0m: module 'seaborn' has no attribute 'joinplot'"
]
}
],
"source": [
"\n",
"import matplotlib.pyplot as plt\n",
"%matplotlib inline\n",
"import seaborn as sns\n",
"income_vs_hardship = %sql select per_capita_income_,hardship_index from chicago_socioeconomic_data;\n",
"\n",
"plot = sns.joinplot(x = 'per_capita_income_',y = 'hardship_index',data = income_vs_hardship.DataFrame())\n",
"plot.show()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Double-click __here__ for the solution.\n",
"\n",
"<!-- Hint:\n",
"# if the import command gives ModuleNotFoundError: No module named 'seaborn'\n",
"# then uncomment the following line i.e. delete the # to install the seaborn package \n",
"# !pip install seaborn\n",
"import matplotlib.pyplot as plt\n",
"%matplotlib inline\n",
"import seaborn as sns\n",
"\n",
"income_vs_hardship = %sql SELECT per_capita_income_, hardship_index FROM chicago_socioeconomic_data;\n",
"plot = sns.jointplot(x='per_capita_income_',y='hardship_index', data=income_vs_hardship.DataFrame())\n",
"\n",
"Correct answer:You can see that as Per Capita Income rises as the Hardship Index decreases. We see that the points on the scatter plot are somewhat closer to a straight line in the negative direction, so we have a negative correlation between the two variables. \n",
"-->\n"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Conclusion\n",
"\n",
"##### Now that you know how to do basic exploratory data analysis using SQL and python visualization tools, you can further explore this dataset to see how the variable `per_capita_income_` is related to `percent_households_below_poverty` and `percent_aged_16_unemployed`. Try to create interesting visualizations!"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Summary\n",
"\n",
"##### In this lab you learned how to store a real world data set from the internet in a database (Db2 on IBM Cloud), gain insights into data using SQL queries. You also visualized a portion of the data in the database to see what story it tells."
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Copyright &copy; 2018 [cognitiveclass.ai](cognitiveclass.ai?utm_source=bducopyrightlink&utm_medium=dswb&utm_campaign=bdu). This notebook and its source code are released under the terms of the [MIT License](https://bigdatauniversity.com/mit-license/).\n"
]
}
],
"metadata": {
"kernelspec": {
"display_name": "Python",
"language": "python",
"name": "conda-env-python-py"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 3
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython3",
"version": "3.6.7"
},
"widgets": {
"state": {},
"version": "1.1.2"
}
},
"nbformat": 4,
"nbformat_minor": 4
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment