Unemployment Data - stlrda/211Dashboard-Workflows GitHub Wiki

BLS Unemployment Statistics

In its current state, the 211Dashboard reports two distinct types of unemployment data at the county level. The first type of data is updated monthly and includes the following features for both Missouri and Illinois counties:

• Total Population of the Labor Force
• Employed Population
• Unemployed Population
• Unemployed Rate

This data is presented monthly and can be found at the Bureau of Labor and Statistics' (BLS) site. Specifically, the data comes from this file, which reports unemployment statistics for all counties in the United States over the last 14 months.

MO Unemployment Claims

The second unemployment data source is data.mo.gov. Specifically, the 211Dashboard project is using the API provided at this site to pull in counts of new unemployment claims by week for each county in Missouri. (Note: This data is currently only available for the state of Missouri.)

Unemployment Data at the Zip Code Level

The HUD COUNTY_ZIP Crosswalk file is used to map unemployment data, which is collected at the county level, to the zip-code level. Specifically, the database table lkup_county_zip_mpg_gtwy_rgnl contains the COUNTY_ZIP mapping information. In this table, you'll find a geo_id column which corresponds to an individual county, a zip_cd (zip-code) column, and a res_ratio column. The res_ratio attribute gives the ratio of residential addresses found in a zip-code for an individual county. To derive the unemployment zip-code level estimates, we simply multiply the residential ratio by the unemployment attribute (i.e. unemployed). We understand that this method provides a rough estimate of unemployment data at the zip-code level, and we recognize the pitfalls associated with this method (e.g. failure to capture nuances in the data for zip-codes within a county).

The following SQL code block demonstrates this mapping method. It is important to note a very similar method is used to map census tract data to the zip-code level (HUD Crosswalk Mappings).

CREATE OR REPLACE VIEW uw211dashboard.public.cre_vu_unemployment_clms_map_2_zip
AS
(SELECT  zip_cd,
         period_end_date,
         SUM(claims_cnt)        AS  claims_cnt
   FROM (SELECT  ct.geo_id,
                 ct.period_end_date,
                 zo.zip_cd,
                 zo.res_ratio,
                 zo.res_ratio * ct.claims_cnt   AS  claims_cnt
           FROM  cre_vu_unemployment_clms            ct,
                 lkup_county_zip_mpg_gtwy_rgnl       zo
          WHERE  ct.geo_id = zo.geo_id
        )     zt
  GROUP  BY zip_cd, period_end_date
)
;