<jupyter_start><jupyter_text>ExploratoryThis notebook includes all code for doing exploratory analysis.<jupyter_code>%matplotlib inline

import pandas as pd

from sqlalchemy import create_engine
#
#  Let's instantiate a database connection.
#  The database should have all of our data
#  now. And will be used as our power-house
#  for crunching data.
#
con = create_engine('postgres://analyst:foo@localhost:9001/brain')<jupyter_output><empty_output><jupyter_text>The Bay AreaThe San Francisco Bay Area is composed of 9 counties. The counties are adjacent to one another, and contain both major cities and smaller suburban areas. The list of counties is:* Santa Clara County* Alameda County* Contra Costa County* San Francisco* San Mateo County* Sonoma County* Solano County* Marin County* Napa CountyRelevant reference data can be found in the table `xwalk`.   Largest Number of WorkersLet's take a look at those tracts that have the largest number of people who say they work there.<jupyter_code>#
#  Let's fetch this data directly
#  from the database.
#
limit = 100
sql = '''
    SELECT 
        w_geocode,
        sum(s000) 
    FROM lodes 
    GROUP BY w_geocode
    ORDER BY sum DESC
    LIMIT {}
'''.format(limit)

d = pd.read_sql(sql, con=con)
#
#  It does seem that at least two
#  tracts (i.e. blocks) have many
#  more workers than other locations.
#  The decay here suggests that these
#  maybe outliers.
#
d.set_index('w_geocode').plot(kind='bar', figsize=(20,5))<jupyter_output><empty_output><jupyter_text>Insight: outliersTwo tracts seem to be outliers here:         * `060372074001016`    * `060373104003007`    Take care with these. Maybe Google, Facebook, or another one major technology company campus?  Total Population of the Bay AreaLet's take a look at the total population of the Bay Area. We will be using data from the 2010 census.* http://stackoverflow.com/questions/15033511/compute-a-confidence-interval-from-sample-data  NormalityWe will be interested in determining Confidence Intervals from this data.<jupyter_code>sql = '''
    SELECT
        blockid10,
        log(population) as population
    FROM daytime_population
    WHERE population > 0
'''
d = pd.read_sql(sql, con=con)
#
#  On a log scale, the county
#  population seems to be
#  (roughly) normally distrited. 
#
d.population.plot.hist(bins=30)
sql = '''
    SELECT
        blockid10,
        log(daytime_population) as daytime_population
    FROM daytime_population
    WHERE daytime_population > 0
'''
d = pd.read_sql(sql, con=con)
#
#  Here as well, but more so. 
#
d.daytime_population.plot.hist(bins=30)<jupyter_output><empty_output><jupyter_text>Check that Model Works<jupyter_code>sql = '''
SELECT
	distinct(substring(blockid10, 1, 5)) as city,
	sum(pop10)
FROM tracts
GROUP BY city;
'''
city = pd.read_sql(sql, con=con)
city.set_index('city')\
    .sort_values('sum', ascending=False)['sum']\
    .plot(kind='bar', figsize=(20,6))
sql = '''
SELECT
	distinct(substring(blockid10, 1, 5)) as city,
	sum(population)
FROM daytime_population
GROUP BY city;
'''
model = pd.read_sql(sql, con=con)
model.set_index('city')\
    .sort_values('sum', ascending=False)['sum']\
    .plot(kind='bar', figsize=(20,6))<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10148514851485149, 'fraction_numerical': 0.025371287128712873, 'mean_word_length': 4.406354515050167, 'pattern_counts': {'":': 0, '<': 17, '<?xml version=': 0, '>': 19, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '599569', 'license': 'MIT', 'n_tokens_mistral': 1128, 'n_tokens_neox': 1013, 'n_words': 336, 'path': 'notebooks/.ipynb_checkpoints/exploratory-checkpoint.ipynb', 'repo_name': 'luiscape/daytime-population', 'size': '3232'}