🔬 Research
American Community Survey Estimate Analyst: Table and Dataset Choice, Margins of Error for Derived Estimates, Coefficient of Variation Flags, and Statistical Difference Tests for Small Areas
Work with American Community Survey numbers the way the Census Bureau's own guidance expects: pick the right table and 1 year or 5 year dataset, combine tracts or categories with approximated margins of error, compute shares with the proportion formula, flag unreliable estimates with the coefficient of variation, test whether two estimates really differ at 90 percent confidence, and write it up in plain language with the caveats intact.
0Reviews
Prompt
Act as a demographic data analyst who works with American Community Survey estimates for housing nonprofits, planners, and reporters, and who never reports an ACS number without its margin of error or claims a difference the data cannot support. Inputs: - The question in plain words: [ResearchQuestion] - Geographies involved (tracts, places, county) and their approximate population: [Geographies] - ACS dataset and period (1 year or 5 year, which years): [SurveyPeriod] - Table IDs with each estimate and its margin of error, as pasted from data.census.gov: [TableEstimates] - What the result will be compared to: [ComparisonTarget] - Who will read the result: [Audience] - Output format: [Format] Generate: 1. A dataset check: 1 year estimates exist only for areas of 65,000 people or more, so tracts need 5 year data. Confirm the table universe fits ResearchQuestion and name any category, such as not computed, that must leave the denominator. 2. Derived estimates: sums across tracts or categories with MOE = square root of the sum of squared MOEs, and differences the same way. Show each step. 3. Shares: the proportion formula MOE(p) = square root of (MOE(x)^2 minus p^2 times MOE(y)^2), divided by y, for a numerator that is a subset of the denominator. If the value under the root is negative, use the ratio formula with plus instead and say so. 4. Reliability: standard error = MOE / 1.645 for published 90 percent MOEs, coefficient of variation = SE / estimate, and a plain flag for high CV estimates using the threshold the user's organization follows. 5. A difference test against ComparisonTarget: Z = (estimate 1 minus estimate 2) / square root of (SE1^2 + SE2^2), significant at 90 percent if the absolute value is above 1.645. 6. Cautions that apply: medians cannot be added across geographies, overlapping 5 year periods should not be compared, and approximated MOEs get less exact as more pieces are combined. 7. A plain language write up for Audience that gives every estimate with its MOE and states what the difference test does and does not show. Constraints: - Use only the numbers in TableEstimates. Do not recall or invent ACS figures. - Round only at the end. Report percentages to one decimal place with MOEs in percentage points. - Say "estimate" every time; never present ACS figures as counts. No em dashes.
Instructions
Replace every [bracket] with your details before running. Works on ChatGPT, Claude, and Gemini.
Generated Output
This image was generated using the prompt above.
Examples
Example Input
ResearchQuestion: are renters in our two neighborhood tracts more rent burdened (30 percent or more of income on gross rent) than renters countywide? Geographies: Census Tract 12.01 and Census Tract 12.02 (about 3,000 people each) in a county of about 400,000 SurveyPeriod: ACS 5 year estimates, 2020 to 2024 TableEstimates: B25070 Tract 12.01: total 1,240 (+/-110), 30.0 to 34.9 percent 95 (+/-48), 35.0 to 39.9 80 (+/-41), 40.0 to 49.9 120 (+/-52), 50.0 or more 310 (+/-78), not computed 40 (+/-25). Tract 12.02: total 980 (+/-96), 30.0 to 34.9 70 (+/-39), 35.0 to 39.9 55 (+/-33), 40.0 to 49.9 90 (+/-44), 50.0 or more 260 (+/-70), not computed 30 (+/-21). County, already combined: burdened 24,100 (+/-540) of 52,300 computed households (+/-620) ComparisonTarget: the county share from the same table and period Audience: a tenant association newsletter and a city council memo Format: worked math, then a short write up
Example Output
Dataset check Tracts are far below 65,000 people, so 5 year data is correct. B25070's universe is renter occupied housing units. Not computed units have no ratio, so they leave the denominator. Derived estimates (both tracts) Burdened = 95 + 80 + 120 + 310 + 70 + 55 + 90 + 260 = 1,080 MOE = sqrt(48^2 + 41^2 + 52^2 + 78^2 + 39^2 + 33^2 + 44^2 + 70^2) = sqrt(22,219) = +/-149 Total renters = 1,240 + 980 = 2,220, MOE sqrt(110^2 + 96^2) = +/-146 Not computed = 40 + 30 = 70, MOE sqrt(25^2 + 21^2) = +/-33 Computed households = 2,220 minus 70 = 2,150, MOE sqrt(146^2 + 32.6^2) = +/-150 Share burdened p = 1,080 / 2,150 = 0.502 MOE(p) = sqrt(149.1^2 minus 0.502^2 x 149.6^2) / 2,150 = sqrt(22,219 minus 5,648) / 2,150 = 128.7 / 2,150 = 0.060 Result: 50.2 percent +/-6.0 points. The value under the root was positive, so the proportion formula holds. Severe burden (50 percent or more): 570 +/-105, share 26.5 percent +/-4.5 points. Reliability SE = 0.060 / 1.645 = 0.036. CV = 0.036 / 0.502 = 7.2 percent. Reliable for this use under common thresholds. Tract 12.01 alone: 50.4 percent +/-8.1 points, CV 9.8 percent, so report the tracts combined. County 24,100 / 52,300 = 46.1 percent, MOE sqrt(540^2 minus 0.461^2 x 620^2) / 52,300 = +/-0.9 points, SE 0.0053. Difference test Z = (0.502 minus 0.461) / sqrt(0.0364^2 + 0.0053^2) = 0.0415 / 0.0368 = 1.13 1.13 is below 1.645, so the difference is not statistically significant at 90 percent confidence. Cautions Combined MOEs are approximations. Do not compare these 2020 to 2024 figures with 2018 to 2022 figures, since the periods overlap. Write up An estimated 50.2 percent of renter households in Tracts 12.01 and 12.02 spend 30 percent or more of income on rent (margin of error +/-6.0 points), compared with an estimated 46.1 percent countywide (+/-0.9). The neighborhood estimate is higher, but the gap is within the survey's margin of error, so the data does not show a statistically significant difference. About a quarter of neighborhood renters, an estimated 26.5 percent (+/-4.5), spend half or more of income on rent.