search

PublicI / broadband-rollout

0 stars 0 forks source link

Calculcate median household income for census blocks #7

Closed JoeYerardi closed 6 years ago

JoeYerardi commented 6 years ago

Aliya and John want to know whether there's a meaningful difference in the economic situation of blocks that first gained access to broadband between 2012 and 2014 and those that first gained access to broadband between 2014 and 2016.

As the Census Bureau doesn't publish median household income at the block level, we're fucked. Just kidding! I guess I have to aggregate this up to the tract level and use ACS estimates?

Sighs audibly

But how do I deal with the fact that there are almost certainly census tracts that contain blocks that first gained access to broadband in 2014 and blocks that first gained access to broadband in 2016 and blocks that already had broadband as of 2012?

Sighs audibly again

JoeYerardi commented 6 years ago

I've emailed the Census Bureau about this and will call IPUMS once they're awake over there in Minnesota.

JoeYerardi commented 6 years ago

The Census Bureau says this data is available at the block group level. I'll download it from NHGIS.

JoeYerardi commented 6 years ago

Data has been uploaded to the PostgreSQL server.

JoeYerardi commented 6 years ago

Data has been joined to the existing tables in the PostgreSQL database.

JoeYerardi commented 6 years ago

With a big assist from @chriszs, I did this. I think. I emailed my findings to Aliya. Leaving this open pending review when Chris returns to the office.

JoeYerardi commented 6 years ago

Record counts

censusblocks: 11,166,336 population: 11,155,486 block_pops: 11,155,486 income: 220,332 block_pops_incomes: 11,157,039

deployment_2012: 37,304,270 deployment_2014: 59,109,554 deployment_2016: 68,415,540

deployment_pops_12: 1,820,936 deployment_pops_14: 5,588,651 deployment_pops_16: 6,001,805

deployment_pops_12_14: 5,588,651 deployment_pops_14_16: 6,001,805