[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.

[Eric-Liu-SANDAG]

Okay, so in hindsight, the implementation of this is actually not very simple...

The main culprit is the MGRA level balancing between actual hhp and the implied hhp from households by size. As the code currently is, the only work done is to apply tract level distributions from the ACS then balance actual/implied hhp. In this situation, the only controlling done is two dimensional, as we control along total households in each MGRA and along actual/implied hhp.

If we add in regional controls, then all of a sudden we add a third dimension to control along, namely the total number of households of each size. I've been thinking about it all morning, and it feels complex enough that I almost want to revive the work done for #179. I think the PuLP solvers could do this? As it is uniquely able to do complex constraints.

But the alternative would be to simply skip this issue or put it in the deep freezer. Maybe I could write a new check for tests.py that verifies we are close to the regional ACS distributions?

[GregorSchroeder]

Putting in the deep freeze.