[FEATURE] Create regional controls for household characteristics

[Eric-Liu-SANDAG]

Is your feature request related to a problem? Please describe

Currently, we create two MGRA level household characteristics: (1) Households split by income category and (2) Households split by household size. These two characteristics lack regional controls, so they are currently only controlled to MGRA level total households and total household population. It's possible that the lack of regional controls will results in weird looking data at larger geographies, such as the CPA, City, or Region level.

Describe the solution you'd like

In case this happens, implement regional controls for the household characteristics. These could be done by taking ACS or PUMS data, aggregating to the region, and scaling the numbers to regional total households

Describe alternatives you've considered

N/A

Additional context

N/A

[GregorSchroeder]

@Eric-Liu-SANDAG is this necessary now that you have implemented the census tract controlling?

[Eric-Liu-SANDAG]

It is not currently an issue, but likely something we should implement to be extra safe.

At the region level, HH characteristics in our Estimates look like:

-- SQL Script to get regional household characteristics

-- Configuration ----------------------------------------------------------------------------------
DECLARE @run_id INTEGER = 82;

-- Households by size -----------------------------------------------------------------------------
SELECT 
    [hh_characteristics].[run_id], 
    [year], 
    [metric],
    SUM([value]) AS [value]
FROM [outputs].[hh_characteristics]
LEFT JOIN [inputs].[mgra]
    ON [mgra].[run_id] = [hh_characteristics].[run_id]
    AND [mgra].[mgra] = [hh_characteristics].[mgra]
WHERE [hh_characteristics].[run_id] = @run_id
    AND [metric] LIKE 'Household Size - %'
GROUP BY [hh_characteristics].[run_id], [year], [metric]
ORDER BY [hh_characteristics].[run_id], [year], [metric]

-- Households by income category ------------------------------------------------------------------
SELECT 
    [hh_characteristics].[run_id], 
    [year], 
    [metric],
    SUM([value]) AS [value]
FROM [outputs].[hh_characteristics]
LEFT JOIN [inputs].[mgra]
    ON [mgra].[run_id] = [hh_characteristics].[run_id]
    AND [mgra].[mgra] = [hh_characteristics].[mgra]
WHERE [hh_characteristics].[run_id] = @run_id
    AND [metric] LIKE 'Income Category - %'
GROUP BY [hh_characteristics].[run_id], [year], [metric]
ORDER BY [hh_characteristics].[run_id], [year], [metric]

And in the ACS look like:

-- SQL to get regional household characteristics from the ACS

-- Configuration ----------------------------------------------------------------------------------
DECLARE @min_year INTEGER = 2020;
DECLARE @max_year INTEGEr = 2023;

-- Households by size -----------------------------------------------------------------------------
SELECT
    [year],
    [household_size],
    SUM([value]) AS [hh]
FROM (
    SELECT
        [tables].[year],
        -- The '%' wildcard matches both 'Family households' and 'Nonfamily
        -- households'
        CASE
            WHEN [label] LIKE 'Estimate%1-person household' THEN 1
            WHEN [label] LIKE 'Estimate%2-person household' THEN 2
            WHEN [label] LIKE 'Estimate%3-person household' THEN 3
            WHEN [label] LIKE 'Estimate%4-person household' THEN 4
            WHEN [label] LIKE 'Estimate%5-person household' THEN 5
            WHEN [label] LIKE 'Estimate%6-person household' THEN 6
            WHEN [label] LIKE 'Estimate%7-or-more person household' THEN 7
            ELSE NULL  -- NULL values for Margin of Error fields removed in subsequent WHERE clause
        END AS [household_size],
        [value]
    FROM [acs].[detailed].[values]
    LEFT JOIN [acs].[detailed].[geography]
        ON [values].[geography_id] = [geography].[geography_id]
    LEFT JOIN [acs].[detailed].[variables]
        ON [values].[variable] = [variables].[variable]
        AND [values].[table_id] = [variables].[table_id]
    LEFT JOIN [acs].[detailed].[tables]
        ON [values].[table_id] = [tables].[table_id]
    WHERE
        [tables].[name] = 'B11016'
        AND [tables].[product] = '5Y'
        AND [tables].[year] BETWEEN @min_year AND @max_year
) AS [b11016]
WHERE [household_size] IS NOT NULL
GROUP BY [year], [household_size]
ORDER BY [year], [household_size]

-- Households by income category ------------------------------------------------------------------
SELECT
    [year],
    [income_category],
    SUM([value]) AS [hh]
FROM (
    SELECT
        [tables].[year],
        -- REPLACE is used here since between 2018 and 2019, the [label] format
        -- changed slightly. In 2018, there was no ':', but starting in 2019, a ':'
        -- was added. We remove the ':' so that all string matching is done to the
        -- same string
        CASE
            WHEN REPLACE([label], ':', '') IN (
                'Estimate!!Total!!Less than $10,000',
                'Estimate!!Total!!$10,000 to $14,999')
                THEN 'Less than $15,000'
            WHEN REPLACE([label], ':', '') IN (
                'Estimate!!Total!!$15,000 to $19,999',
                'Estimate!!Total!!$20,000 to $24,999',
                'Estimate!!Total!!$25,000 to $29,999')
                THEN '$15,000 to $29,999'
            WHEN REPLACE([label], ':', '') IN (
                'Estimate!!Total!!$30,000 to $34,999',
                'Estimate!!Total!!$35,000 to $39,999',
                'Estimate!!Total!!$40,000 to $44,999')
                THEN '$30,000 to $44,999'
            WHEN REPLACE([label], ':', '') IN (
                'Estimate!!Total!!$45,000 to $49,999',
                'Estimate!!Total!!$50,000 to $59,999')
                THEN '$45,000 to $59,999'
            WHEN REPLACE([label], ':', '') = 'Estimate!!Total!!$60,000 to $74,999'
                THEN '$60,000 to $74,999'
            WHEN REPLACE([label], ':', '') = 'Estimate!!Total!!$75,000 to $99,999'
                THEN '$75,000 to $99,999'
            WHEN REPLACE([label], ':', '') = 'Estimate!!Total!!$100,000 to $124,999'
                THEN '$100,000 to $124,999'
            WHEN REPLACE([label], ':', '') = 'Estimate!!Total!!$125,000 to $149,999'
                THEN '$125,000 to $149,999'
            WHEN REPLACE([label], ':', '') = 'Estimate!!Total!!$150,000 to $199,999'
                THEN '$150,000 to $199,999'
            WHEN REPLACE([label], ':', '') = 'Estimate!!Total!!$200,000 or more'
                THEN '$200,000 or more'
            ELSE NULL  -- NULL values for Margin of Error fields removed in subsequent WHERE clause
        END AS [income_category],
        [value]
    FROM [acs].[detailed].[values]
    LEFT JOIN [acs].[detailed].[geography]
        ON [values].[geography_id] = [geography].[geography_id]
    LEFT JOIN [acs].[detailed].[variables]
        ON [values].[variable] = [variables].[variable]
        AND [values].[table_id] = [variables].[table_id]
    LEFT JOIN [acs].[detailed].[tables]
        ON [values].[table_id] = [tables].[table_id]
    WHERE
        [tables].[name] = 'B19001'
        AND [tables].[product] = '5Y'
        AND [tables].[year] BETWEEN @min_year AND @max_year
) AS [b19001]
WHERE [income_category] IS NOT NULL
GROUP BY [year], [income_category]
ORDER BY [year], [income_category]

Results:
Estimates Program - hh_characteristics_regional_comparison_with_acs.xlsx

IMO, our results align very well with the ACS, but this could change so we should probably implement this as well for intercensal

[GregorSchroeder]

In agreement, especially since implementation is relatively simple.