Today I beat Airbnb’s Website Filter

It highlights the difference between machine reading and human reading. Not really a big deal, but was fun. I succeeded with:

“You can find us if you put nicoandstephie in your web browser and tack on the dot and the com at the end.”

My failed attempts:

http://www.nicoandstephie.com – (website hidden)

You can find us at nicoandstephie do. T com. – You can find (email hidden).

You can look up nicoandstephie in you web browser and add dot com. – You can look up nicoandstephie in you web browser and (website hidden).

Advertisements

USA Job Point Generator

import pandas as pd
import numpy as np
import psycopg2
import os, os.path
import itertools

#load the lodes dataset, setting id columns to strings
lodes = pd.read_csv('/path/all_lodes.csv', 
                    dtype = {'statefp10': str, 'countyfp10': str, 'tractce10': str, 'blockce': str, 'blockid10': str})

conn_string = "host='localhost' dbname='' user='' password='' port=5432"
conn=psycopg2.connect(conn_string)
cur = conn.cursor()

#select the state
state = '06'
#select the county
county = '041'
#name the table that will hold the job points
target_table = "marin_jobs"
#name the table that will hold the census blocks for the county
create_blocks = "marin_blocks"

#this is to run a block level generation of jobs for a marin county


lodes_test2 = lodes_test2[['blockid10','cns01','cns02','cns03','cns04','cns05','cns06','cns07','cns08','cns09','cns10','cns11','cns12','cns13','cns14','cns15','cns16','cns17','cns18','cns19','cns20']]
lodes_test2 = lodes_test2.reset_index(drop=True)

def jobs_table(x):
    'set the target table for the job point generation'
    
    print ('creating table {} to hold job points...'.format(target_table))
    
    cur.execute("drop table if exists {};". format(x))
    conn.commit()
    
    cur.execute("create table {} (blockid10 varchar(17), job_type varchar(10), the_geom geometry);" .format(x))
    conn.commit()
    
    print ('done')
    
jobs_table(target_table)


def blocks_table(x):
    'isolates the census blocks for the county you want to calculate job points'
    
    print ('creating table {} from the tiger geometries...'.format(create_blocks))
    
    cur.execute("drop table if exists {}".format(x))
    conn.commit()
    
    cur.execute("create table {} as (select * from tabblock where statefp10 = '{}' and countyfp10 = '{}');".format (x, state, county))
    conn.commit()
    
    cur.execute("alter table {} rename column geoid to geoid10".format(x))
    conn.commit()
    
    print ('done')

blocks_table(create_blocks)


def create_job_points():
    'iterate cell by cell, check if value > 0, then create points for appropriate census block with the right job type'
    
    print ('creating job points for state {}, county {}...'.format(state, county))
    
    columns = ['cns01','cns02','cns03','cns04','cns05','cns06','cns07','cns08',
               'cns09','cns10','cns11','cns12','cns13','cns14','cns15','cns16',
               'cns17','cns18','cns19','cns20']
    rows = range(0,len(lodes_test2))
    for r in rows:
        for i in columns:
            if lodes_test2[i][r] > 0:
                #print block
                block = str(lodes_test2['blockid10'][r])
                job = i
                job_x_block = (i + 'x' + block)
                num_job =  int(lodes_test2[i][r])
                
                cur.execute("drop table if exists {};" .format(job_x_block))
                conn.commit()
                
                cur.execute("""CREATE TABLE %s 
                (blockid10 varchar(17) DEFAULT '%s',
                job_type varchar(10) DEFAULT '%s',
                the_geom geometry);""" % (job_x_block, block, job))
                conn.commit()
                
                cur.execute("""INSERT INTO {}(the_geom)
                SELECT RandomPointsInPolygon(geom, {})
                FROM {} WHERE geoid10 = '{}';""" .format(job_x_block, num_job, create_blocks, block))
                conn.commit()
                
                cur.execute("insert into {} select * from {};" .format(target_table, job_x_block))
                conn.commit()
                
                cur.execute("drop table if exists {}" .format(job_x_block))
                conn.commit()
                
    print ('done')

create_job_points()

Census Loader


import numpy as np
import pandas as pd
import psycopg2
import os, os.path
import itertools

conn_string = "host='localhost' dbname='' user='' password='' port="
conn=psycopg2.connect(conn_string)
cur = conn.cursor()


#for each census folder
#create a template table for data insertion

#unzip every file
def unzip():
    os.chdir('/Users/ntapia/Downloads/2013_Tiger')
    folders = []
    for folder in os.listdir('.'):
        folders.append(folder)
    for folder in folders[1:]:
        os.chdir('/Users/ntapia/Downloads/2013_Tiger/' + folder)
        for item in os.listdir('.'):
            if 'zip' in item:
                print (item)
            os.system('unzip '+ item)

def add_table(x):
    os.chdir('/Users/ntapia/Downloads/2013_Tiger/%s' % x)
    print ('/Users/ntapia/Downloads/2013_Tiger/%s' % x)
    for file_ in os.listdir('.'):
            if '.shp' in file_ and 'xml' not in file_:
                cur.execute("drop table if exists %s;" % item)
                conn.commit()
                
                cur.execute("drop table if exists %s;" % (file_[:-4]))
                conn.commit()
            
                os.system('shp2pgsql -W "latin1" -s 4269 /Users/ntapia/Downloads/2013_Tiger/%s/%s %s | psql -h localhost -p 5432 -d census' % (x, file_, file_[:-4]))
                    
                cur.execute("CREATE TABLE %s (LIKE %s);" % (item, file_[:-4]))
                conn.commit()
                    
                cur.execute("drop table if exists %s;" % (file_[:-4]))
                conn.commit()
                
                
                
def load_data(x):
    os.chdir('/Users/ntapia/Downloads/2013_Tiger/%s' % x)
    for file_ in os.listdir('.'):
        if '.shp' in file_ and 'xml' not in file_:
                cur.execute("drop table if exists %s;" % (file_[:-4]))
                conn.commit()
            
                os.system('shp2pgsql -W "latin1" -s 4269 /Users/ntapia/Downloads/2013_Tiger/%s/%s %s | psql -h localhost -p 5432 -d census' % (x, file_, file_[:-4]))
                    
                cur.execute("insert into %s select * from %s;" % (item, file_[:-4]))
                conn.commit()
                    
                cur.execute("drop table if exists %s;" % (file_[:-4]))
                conn.commit()
    print ('done')
    

    
os.chdir('/Users/ntapia/Downloads/2013_Tiger')
census_folders = []
for table in os.listdir('.'):
     if '.DS' not in table:
         census_folders.append(table)

unzip()
            
for item in census_folders:
    add_table(item)

for item in census_folders:
    load_data(item)