2.3 Attaching tract data to our latitudes and longitudes
Now that we know where our street addresses are in space, we need to figure out what census tract they’re in. Or, more specifically, which census tract they were in as of 2013.
We’ll be using geographic information from the US Census Bureau, specifically its TIGER/Line Shapefiles product. If you aren’t familiar with shapefiles, they’re more or less a data format for geographic information that you can open up in applications like QGIS (which is what we’ll be doing our work in).
Each year the Census Bureau releases a new set of shapefiles, and they have also produced detailed documentation about what set of shapefiles to use depending on where/when your data came from.
Our data came from the 2013 American Community Survey (5-year, 2007-2013), so we’ll be using the 2013 shapefiles. Specifically the ones for at the census tract level in Wisconsin.
2.3.1 Joining attributes by location
JOINING ATTRIBUTES BY LOCATION
After we do our join, we can see that our new Attribute Table lists all sorts of geographic data along with our original information.
To save this to a CSV, right-click and select Export as…. Pick Comma Separated Values (CSV) from the filetype menu, and save it as 2013-addresses-tracts.csv. We specifically call it 2013 because even though these addresses are from the entire dataset, this is the census tract they were in during 2013.
Why didn’t we grab addresses from the entire dataset instead of just addresses with 2013 potholes? I thought we might need the other addresses later in case we decide to analyze the other years, so it seems reasonable to do them all at once.
Now that we have this CSV of addresses, we can open them in pandas like so:
| address | city | state | Latitude | Longitude | Accuracy Score | Accuracy Type | Number | Street | City_1 | State_1 | County | Zip | Country | Source | GEOID |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 8900 N 124TH ST | Milwaukee | Wisconsin | 43.17790 | -88.06365 | 1 | range_interpolation | 8900 | N 124th St | Milwaukee | WI | Waukesha County | 53224 | US | TIGER/Line® dataset from the US Census Bureau | 55133200202 |
| 2200 N TERRACE AV | Milwaukee | Wisconsin | 43.05671 | -87.88080 | 1 | rooftop | 2200 | N Terrace Ave | Milwaukee | WI | Milwaukee County | 53202 | US | City of Milwaukee | 55079186900 |
| 1000 N PROSPECT AV | Milwaukee | Wisconsin | 43.04328 | -87.89869 | 1 | range_interpolation | 1000 | N Prospect Ave | Milwaukee | WI | Milwaukee County | 53202 | US | TIGER/Line® dataset from the US Census Bureau | 55079186900 |
| 1700 N PROSPECT AV | Milwaukee | Wisconsin | 43.05211 | -87.89049 | 1 | rooftop | 1700 | N Prospect Ave | Milwaukee | WI | Milwaukee County | 53202 | US | City of Milwaukee | 55079186900 |
| 2298 N TERRACE AV | Milwaukee | Wisconsin | 43.05931 | -87.87922 | 1 | range_interpolation | 2298 | N Terrace Ave | Milwaukee | WI | Milwaukee County | 53202 | US | TIGER/Line® dataset from the US Census Bureau | 55079186900 |
Lots of extra columns there, so when we read it in later we’ll probably only take a few of the columns.