search

NYCPlanning / db-community-profiles

data pipelines for community profiles
https://nycplanning.github.io/db-community-profiles/
0 stars 0 forks source link

ACS/Decennial Floodplain Fields #43

Closed mgraber closed 3 years ago

mgraber commented 4 years ago

11

ACS-derived variables

Field Name ACS equivalent Description
fp_100_cost_burden fp_100_cost_burden_value / B25091_001E Percentage of owner-occupied household units considered cost-burdened within the 2015 Preliminary Flood Insurance Rate Maps (PFIRM) 1% annual chance floodplain. Owner-occupied households are characterized as cost-burdened if 30% or more of household income is spent on housing costs.
fp_500_cost_burden fp_500_cost_burden_value / B25091_001E Percentage of owner-occupied units considered cost-burdened, within the  2050s 1% annual chance floodplain*. Owner-occupied households are characterized as cost-burdened if 30% or more of household income is spent on housing costs.
fp_100_cost_burden_value B25091_019E + B25091_020E + B25091_021E + B25091_022E + B25091_008E + B25091_009E + B25091_010E + B25091_011E Count of households that are cost-burdened within the 2015 Preliminary Flood Insurance Rate Maps (PFIRM) 1% annual chance floodplain.
fp_500_cost_burden_value B25091_019E + B25091_020E + B25091_021E + B25091_022E + B25091_008E + B25091_009E + B25091_010E + B25091_011E Count of households that are cost-burdened within the  2050s 1% annual chance floodplain*.
fp_100_mhhi B19013_001E Median household income within the 2015 Preliminary Flood Insurance Rate Maps (PFIRM) 1% annual chance floodplain
fp_500_mhhi B19013_001E Median household income within the 2050s 1% annual chance floodplain*
fp_100_mortg_value B25091_002E Count of owner-occupied households with mortgages within the 2015 Preliminary Flood Insurance Rate Maps (PFIRM) 1% annual chance floodplain.
fp_500_mortg_value B25091_002E Count of owner-occupied households with mortgages within the  2050s 1% annual chance floodplain*.
fp_100_ownerocc B25003_002E / B25002_001E Proportion of households that are owner-occupied within the 2015 Preliminary Flood Insurance Rate Maps (PFIRM) 1% annual chance floodplain.
fp_500_ownerocc B25003_002E / B25002_001E Proportion of households that are owner-occupied
fp_100_ownerocc_value B25091_001E Count of owner-occupied households within the 2015 Preliminary Flood Insurance Rate Maps (PFIRM) 1% annual chance floodplain.
fp_500_ownerocc_value B25091_001E Count of owner-occupied households within the  2050s 1% annual chance floodplain*.
fp_100_permortg B25091_002E / B25091_001E Percentage of owner-occupied units with a mortgage within the 2015 Preliminary Flood Insurance Rate Maps (PFIRM) 1% annual chance floodplain. Mortgage refers to all forms of debt where the property is pledged as security for repayment of the debt. Mortgage status is an important determinant in the floodplain as any property with a federally-insured mortgage must carry a federal flood insurance policy.
fp_500_permortg B25091_002E / B25091_001E Percentage of owner-occupied household units with a mortgage within the 2050s 1% annual chance floodplain*. Mortgage refers to all forms of debt where the property is pledged as security for repayment of the debt. Mortgage status is an important determinant in the floodplain as any property with a federally-insured mortgage must carry a federal flood insurance policy.
fp_100_rent_burden fp_100_rent_burden_value / B25070_001E Percentage of renter-occupied household units considered cost-burdened within the 2015 Preliminary Flood Insurance Rate Maps (PFIRM) 1% annual chance floodplain. Renter-occupied households are characterized as cost-burdened if 30% or more of household income is spent on housing costs.
fp_500_rent_burden fp_500_rent_burden_value / B25070_001E Percentage of renter-occupied units considered cost-burdened, within the 2050s 1% annual chance floodplain*. Renter-occupied households are characterized as cost-burdened if 30% or more of household income is spent on housing costs.
fp_100_rent_burden_value B25070_007E + B25070_008E + B25070_009E + B25070_010E Count of households that are rent-burdened within the 2015 Preliminary Flood Insurance Rate Maps (PFIRM) 1% annual chance floodplain.
fp_500_rent_burden_value B25070_007E + B25070_008E + B25070_009E + B25070_010E Count of households that are rent-burdened within the  2050s 1% annual chance floodplain*.

Methodology

Block groups were selected for inclusion if the centroids of any one of their constituent blocks intersected with floodplain geographies. Estimates for selected socio-economic variables were obtained from American Community Survey (ACS) for these block groups. ACS estimates were summed to produce public use microdata area level profiles.

Example API call to extract census block group level ACS data by county https://api.census.gov/data/2018/acs/acs5?get=B00001_001E&for=block%20group:*&in=state:01%20county:025

Input tables

Provided by PopDiv Variable Table Year 100 500  
Total Occupied Housing Units B25003 ACS 2014-2018     Universe: Housing Units
Owner Occupied B25091 ACS 2014-2018 fp_100_ownerocc fp_500_ownerocc Universe: Owner-occupied housing units
Owner Occupied with Mortgage B25091 ACS 2014-2018 fp_100_permortg fp_500_permortg Universe: Owner-occupied housing units
Estimate: Housing Owner Cost Burdened (30% or more of income on rent) B25091 ACS 2014-2018 fp_100_cost_burden fp_500_cost_burden Universe: Owner-occupied housing units
Renter Occupied B25070 ACS 2014-2018     Universe: Renter-occupied housing units
Estimate: Rent Burdened Households (30% or more of income on rent) B25070 ACS 2014-2018 fp_100_rent_burden fp_500_rent_burden Universe: Renter-occupied housing units
Median Household Income B19013 ACS 2014-2018 fp_100_mhhi fp_500_mhhi Universe: Households

Note: to calculate estimates on Rent/Owner Cost Burdened Housholds, add 30.0 to 34.9 percent, 35.0 to 39.9 percent, 40.0 to 49.9 percent, 50.0 pecent or more & calculate associated MOE

Geography of census blocks, as related to the two floodplains

-- In RECIPES
CREATE TEMP TABLE AS cd_bctcb2010_centroids (
    SELECT 
    cd,
    (CASE
            WHEN LEFT(a.bctcb2010, 1) = '1' THEN '36061'||RIGHT(a.bctcb2010, 10)
            WHEN LEFT(a.bctcb2010, 1) = '2' THEN '36005'||RIGHT(a.bctcb2010, 10)
            WHEN LEFT(a.bctcb2010, 1) = '3' THEN '36047'||RIGHT(a.bctcb2010, 10)
            WHEN LEFT(a.bctcb2010, 1) = '4' THEN '36081'||RIGHT(a.bctcb2010, 10)
            WHEN LEFT(a.bctcb2010, 1) = '5' THEN '36085'||RIGHT(a.bctcb2010, 10)
    END) as block_fips, 
    (CASE
            WHEN LEFT(a.bctcb2010, 1) = '1' THEN '36061'||SUBSTRING(a.bctcb2010, 2, 7)
            WHEN LEFT(a.bctcb2010, 1) = '2' THEN '36005'||SUBSTRING(a.bctcb2010, 2, 7)
            WHEN LEFT(a.bctcb2010, 1) = '3' THEN '36047'||SUBSTRING(a.bctcb2010, 2, 7)
            WHEN LEFT(a.bctcb2010, 1) = '4' THEN '36081'||SUBSTRING(a.bctcb2010, 2, 7)
            WHEN LEFT(a.bctcb2010, 1) = '5' THEN '36085'||SUBSTRING(a.bctcb2010, 2, 7)
    END) as block_group_fips,
    ST_Centroid(b.wkb_geometry) as centroid_geom
    FROM dcp_bctcb2010_cd_puma a
    JOIN dcp_censusblocks.latest b
    ON a.bctcb2010 = b.bctcb2010
);
CREATE INDEX "bctcb_2010_geom_idx" ON cd_bctcb2010_centroids USING GIST (centroid_geom gist_geometry_ops_2d);

CREATE TEMP TABLE cd_blocks_floodplain AS (
        SELECT
              a.*,
              bool_or(CASE 
                       WHEN NOT a.centroid_geom && b.wkb_geometry THEN FALSE
                        ELSE ST_Intersects(a.centroid_geom,  b.wkb_geometry) 
              END) as in_100,
         bool_or(CASE
             WHEN NOT a.centroid_geom && c.wkb_geometry THEN FALSE
             ELSE ST_Intersects(a.centroid_geom,  c.wkb_geometry)
         END) as in_500 
FROM cd_bctcb2010_centroids a, fema_pfirms2015_100yr.latest b, fema_firms_500yr.latest c
GROUP BY a.cd, a.block_fips, a.block_group_fips, a.centroid_geom);

Decennial-derived variables

Field Name Census equivalent Description
fp_100_pop P001001 Population of the CD that lives within the 2015 Preliminary Flood Insurance Rate Maps (PFIRM) 1% annual chance floodplain
fp_500_pop P001001 Population (2010) of the CD that lives within the 2050s 1% annual chance floodplain*

Methodology

Floodplain geographies were defined in terms of 2010 census block geographies employing the centroid containment method, which selected 2010 census blocks if their centroids intersected the floodplain areas. If a block’s centroid lay external to the floodplain, the census block did not qualify for inclusion. For selected blocks residing in floodplain areas, selected demographic data were obtained and then aggregated to produce demographic profiles of floodplain areas that lay within community districts.

Example API call to extract census block level decennial data by county https://api.census.gov/data/2010/dec/sf1?get=P001001,NAME&for=block:*&in=state:01%20county:009

SPTKL commented 4 years ago 
import pandas as pd
import os
from census import Census

c = Census(os.environ['API_KEY'])

nyc_counties = ['005', '081', '085', '047', '061']
variables = [
 'B19013_001E', 'B25002_001E',
 'B25002_002E',  'B25070_001E',
 'B25070_007E', 'B25070_008E',
 'B25070_009E', 'B25070_010E',
 'B25091_001E', 'B25091_002E',
 'B25091_008E', 'B25091_009E',
 'B25091_010E', 'B25091_011E',
 'B25091_019E', 'B25091_020E',
 'B25091_021E', 'B25091_022E',
 'B19001_002E', 'B19001_003E',
 'B19001_004E', 'B19001_005E',
 'B19001_006E', 'B19001_007E',
 'B19001_008E', 'B19001_009E',
 'B19001_010E', 'B19001_011E',
 'B19001_012E', 'B19001_013E',
 'B19001_014E', 'B19001_015E',
 'B19001_016E', 'B19001_017E'
]

MHHI_bins = {'B19001_002E': [0, 9999],
 'B19001_003E': [10000, 14999],
 'B19001_004E': [15000, 19999],
 'B19001_005E': [20000, 24999],
 'B19001_006E': [25000, 29999],
 'B19001_007E': [30000, 34999],
 'B19001_008E': [35000, 39999],
 'B19001_009E': [40000, 44999],
 'B19001_010E': [45000, 49999],
 'B19001_011E': [50000, 59999],
 'B19001_012E': [60000, 74999],
 'B19001_013E': [75000, 99999],
 'B19001_014E': [100000, 124999],
 'B19001_015E': [125000, 149999],
 'B19001_016E': [150000, 199999],
 'B19001_017E': [200000, 9999999]}

dfs = []
for county in nyc_counties:
  dfs.append(pd.DataFrame(
      c.acs5.get(('NAME', ','.join(variables)), 
                 {'for': 'block group:*','in' : 'state:36 county:081'},year=2018)
    ))

df = pd.concat(dfs)
df_MHHI = df[['B19001_002E', 'B19001_003E',
                         'B19001_004E', 'B19001_005E',
                         'B19001_006E', 'B19001_007E',
                         'B19001_008E', 'B19001_009E',
                         'B19001_010E', 'B19001_011E',
                         'B19001_012E', 'B19001_013E',
                         'B19001_014E', 'B19001_015E',
                         'B19001_016E', 'B19001_017E']]
df['cost_burden_value'] = df.B25091_019E + df.B25091_020E + df.B25091_021E \
                          + df.B25091_022E + df.B25091_008E + df.B25091_009E \
                          + df.B25091_010E + df.B25091_011E
df['cost_burden'] = df.cost_burden_value / df.B25091_001E
df['mhhi'] = df_MHHI.apply(lambda row: get_median(MHHI_bins, row), axis=1)
df['mortg_value'] = df.B25091_002E
df['ownerocc'] = df.B25002_002E / df.B25002_001E
df['ownerocc_value'] = df.B25091_001E
df['permortg'] = df.B25091_002E / df.B25091_001E
df['rent_burden_value'] = df.B25070_007E + df.B25070_008E + \
                          df.B25070_009E + df.B25070_010E
df['rent_burden'] = df.rent_burden_value / df.B25070_001E