Introduction - Education First¶

Team Member Names: Carl Shan Bharathkumar Gunasekaran Haroon Rasheed Paul Mohammed Sumedh Sawant¶

How can we help parents to choose elementary schools for their children? We’re interested in visualizing the statistical data from National Center for Educational Statistics with geo-data to help parents determine the best location to live in and send their children to school.

We will help parents assess the optimal location through analyzing a variety of factors including:

1. Crime Rates
2. HS Graduation Rate
3. Population
4. Average Income
5. Average House Price
6. Average County Test Scores
7. County Schools Total Funding
8. Health Care Costs
9. Percent of College Degree Holders
10. Literacy Rate

After ranking each of these on a scale of 1-10 (10: Most Important), we'll generate a heat map of the state they're interested in living in and dynamically color it according to how closely each county accords with their preferences.

The heatmap will be displayed within a web browser.

Example: http://nbviewer.ipython.org/5490713

Step 0: Data Extraction & Cleaning¶

Below is our initial code that extracts, cleans and coerces the data into Numpy data structures

In [219]:
## Setting up tools
import csv
import codecs
import pandas as pd
import numpy as np
import os
from pandas import DataFrame, Series
from itertools import islice
from bs4 import BeautifulSoup

dtype = {'FIPS':np.object}


Extracting Literacy rates, and College degree rates¶

In [220]:
## Extracting Literacy rates, and College degree rates
f1 = codecs.open(os.path.abspath("data/LiteracyCollegeDegreeData.csv"), encoding='iso-8859-1')
df1 = pd.read_csv(f1, dtype=dtype)


Extracting Crime, Motor Vehicle Mortality, High School Graduation and Smoking Rates¶

In [221]:
## Extracting Crime, motor vehicle mortality, average income and smoking rates
f2 = codecs.open(os.path.abspath("data/CrimeRate.csv"), encoding='iso-8859-1')
data = list()
n = 0
for row in islice(f2, None):
if n == 0:
pass
elif n == 1:
else:
data.append(row.split(','))
n = n + 1

df2 = DataFrame(data, columns = header[0])
df2 = df2[['FIPS', 'County', 'State', 'Violent Crime Rate', '% AFGR', 'MV Mortality Rate', '% Smokers']]


Extracting Population and Household Income¶

In [222]:
f2 = codecs.open(os.path.abspath("data/PopIncome.csv"), encoding='iso-8859-1')
data1 = list()
n = 0
for row in islice(f2, None):
if n == 0:
pass
elif n == 1:
else:
data1.append(row.split(','))
n = n + 1

df3 = DataFrame(data1, columns = header1[0])
df3 = df3[['FIPS', 'County', 'State', 'Population', 'Household Income']]


Extracting county health rankings¶

In [223]:
## Extracting county health rankings

dtype = {'FIPS':np.object}
ff = codecs.open(os.path.abspath("data/CountyHealthRankings.csv"), encoding='iso-8859-1')
data = list()
n = 0
for row in islice(ff, None):
if n == 0:
pass
elif n == 1:
else:
data.append(row.split(','))
n = n + 1
health = DataFrame(data, columns = header[0])


Merge all DataFrames into a final DataFrame¶

In [224]:
dataframe = pd.merge(df2, df3)
df_final = pd.merge(df1, dataframe, on = 'FIPS')

# Deleting redundant or unnecessary columns, then cleaning up the dataframe by renaming the columns
for col in ('State_y', 'County_y', 'Population_x', '95%CI-Low(College)', '95%CI-Low(Illiterate)', '95%CI-High(Illiterate)', 'Quartile'):
del df_final[col]

df_final = df_final.rename(columns={'Population_y': 'Population', 'State_x': 'State', 'County_x': 'County'})
df_final['# of Ranked Counties'] = health['# of Ranked Counties']
df_final['Health Outcome Rank'] = health['Health Outcome Rank']


Function to return a subset of final DataFrame (df_final) with counties of a specific state¶

In [225]:
def get_state(state):
'''
Returns subset of df_final that is only the counties in the state
state should be a state's FIP code, e.g., '06' for California
'''
if len(state) > 2:
fip = state[:2]
else:
fip = state
return df_final[df_final['FIPS'].map(lambda x: x[:2] == fip)]


Rank all counties of a state with respect to each of the factors¶

In [226]:
# False is for 'good' metrics and True is for 'bad' metrics.
ascending = {'NumberWentToSomeCollege': False, 'PercentWithCollegeDegree': False, '95%CI-High(College)': False, 'Illiterate': True, '% Smokers':True, 'MV Mortality Rate': True, '% AFGR': False, 'Violent Crime Rate': True, 'Population': False, 'Household Income':False, '# of Ranked Counties': False, 'Health Outcome Rank': False}

def getStateCountyMetricRankings(stateFips):
'''
Returns a dataframe with all the metrics ranked for all the counties in this state.
stateFips should be a FIPS code for a state with just the first two characters that are relevant.
'''
if len(stateFips) > 2:
stateFips = stateFips[:2]
finalDataFrame = None
for metric in ascending:
rankedColumn = get_state(stateFips)[metric].rank(ascending=ascending[metric])
if finalDataFrame is None:
finalDataFrame = DataFrame(index = rankedColumn.index)
finalDataFrame[metric] = rankedColumn
else:
finalDataFrame[metric] = rankedColumn
return finalDataFrame


Name:
State:

Step 1: Rank your options on a scale of 1 to 10 based on your priorities.¶

Ranking a county characteristic as number 1 signifies that it is very important to you in choosing where to live. Ranking something as number 10 signifies that it is the least important factor to you.

Rank 1:
Rank 2:

Rank 3:
Rank 4:

Rank 5:
Rank 6:

Rank 7:
Rank 8:

Rank 9:
Rank 10:
In [227]:
translationMap = {"Crime Rate": "Violent Crime Rate", "HS Grad Rate": "% AFGR", \
"Healthcare Cost": "Health Outcome Rank", "College Degree Holders": "PercentWithCollegeDegree", "Population": "Population", \
"Income": "Household Income", "Literacy Rate": "Illiterate", "MV Mortality Rate": "MV Mortality Rate", \
"Percent of Smokers": "% Smokers", "Number attended College": "NumberWentToSomeCollege"}

ranks = [rank1, rank2, rank3, rank4, rank5, rank6, rank7, rank8, rank9, rank10]

weights = {}

# Populate the weights of the metrics.
for i,rank in enumerate(ranks):
translatedDFMetric = translationMap[rank]
weights[translatedDFMetric] = i + 1

print weights

{'Household Income': 3, 'Illiterate': 5, 'NumberWentToSomeCollege': 9, 'Violent Crime Rate': 1, '% Smokers': 6, 'MV Mortality Rate': 8, 'PercentWithCollegeDegree': 2, '% AFGR': 10, 'Population': 7, 'Health Outcome Rank': 4}


Step 2: Generate Weighted Sums per County¶

In [228]:
stateDF = get_state(state)
stateCountyMetrics = getStateCountyMetricRankings(state)

# Key is County FIPS, Value is the score
rankScores = {}

for index in stateDF.index:
countyFip = stateDF.ix[index].ix['FIPS']
for metric in weights:
weight = weights[metric]
metricValue = stateCountyMetrics.ix[index].ix[metric]
if countyFip not in rankScores:
rankScores[countyFip] = 0
rankScores[countyFip] += weight*metricValue


Step 3: Assign Colors to Counties¶

Function to convert numbers between 0 and num_counties to color range Green to Red¶

In [229]:
num_counties = len(stateDF)

def rgb_to_hex(rgb):
return '#%02x%02x%02x' % rgb

def generate_rgb(val, maxval):
f = float(val) / (maxval)
r, g, b = int((1-f)*255), int(f*255), 0
return rgb_to_hex((g, r, b))

colors = []
for val in xrange(0, num_counties+1):
colors.append(generate_rgb(val, num_counties))


Sort the counties and assign a color to each county. Highest ranked county gets Green and lowest ranked county gets Red¶

In [230]:
from collections import defaultdict

counties_rating = rankScores

counties_sorted = sorted(counties_rating, key=counties_rating.get, reverse=False)
counties_colors = defaultdict(int)

i = 0
while (i < num_counties):
try:
counties_colors[counties_sorted[i]] = colors[i]
i = i + 1
except:
i = i + 1
continue


SVG File for all counties in USA¶

In [236]:
from IPython.display import SVG
SVG(filename="counties.svg")

Out[236]: