aggregating by census tract

See it on Tableau

the goal:
I want to find the median value of lead samples in Parts Per Billion by census tract. To do that I will need to find the census tract associated with each latitude and longitude and then find the median value of all observations within that tract.

the data:
I have a dataframe that looks like this:

lon lat PPB
-77.034018 38.9138584 0

the code:

step 1.

I will use FCC’s API. I can send it latitude and longitude data and it will return the census block (among other stuff). You really only need a few lines of code, but below I wrote a class so the user can get the data they want. The class gets initiated with two parameters latitude and longitude this gets inserted into the url payload courtesy of the requests module. The object r is created and then the json is extracted to a new object y. The other four functions in the class simply extract the data element(s) from the object y

import requests
import json

class censusData:

    def __init__(self,lat,lon,showall=True):
        url = ''
        payload = {'latitude': lat,'longitude': lon,'showall': showall}
        self.r  = requests.get(url, params=payload)
        self.y = self.r.json()

    def block(self):
        return str(self.y['Block']['FIPS'])

    def county(self):
        return str(self.y['County']['name'])

    def state(self):
        return str(self.y['State']['name'])

    def intersection(self):
        records = []
        for  b in self.y['Block']['intersection']:
            record = filter(lambda x: x.isdigit(), str(b))
        return records
    def data(self):
        return json.dumps(self.y)

step 2: using the class
I can call the api now simply like so:


but, how can we apply it to a large dataframe?
hint* use apply

import pandas as pd
from census import censusData
#bring in data
df =pd.read_csv("data.csv")
this is when using apply is your greatest friend.
I am applying my function to my data frame.
No need for a messy for loop.
df['Tract'] =df.apply(lambda x: censusData(df['lat'], df['lon']).block(), axis=1)

now I have a dateframe that looks like this:

lon lat PPB Tract
-77.034018 38.9138584 0 110010081002007

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s