Data ask: County-level population tables by age and race, per year
March 5, 2023 7:30 AM Subscribe
I'm looking to understand population trends in my county that will affect education. I'd like to be able to plot estimated counts of entering kindergarteners by race over a reasonable enough time period to get a sense of trends, say 15-20 years.
I'm thinking the American Community Survey might be a good source, but I'm having a hard time getting what I want out of data.census.gov, which ideally would be a csv file containing multiple years of population estimates, stratified by race and age but not by sex. I also signed up for an IPUMS account, but that appears to be individual-level data -- which is not what I want. I'm hoping the hive mind here has a demography expert who can point me in a better direction. I'm a statistician but not a demographer.
My underlying motivation is to understand the effects that changing demographics are likely to have on existing equity issues where schools are concerned. About ten years ago, a local group published a report indicating that my county in particular is an abysmal one to live in if you are black, which I think was a big surprise to the white people here like me (and not at all surprising to black people here). More reading on related issues specific to my county but of more recent vintage would also be helpful, but my current purposes will be best served with actual data.
We do have a center for demography at the local U that has done population projections in the past for local school districts. I reached out to them a few weeks ago to ask when their next projections are likely to be released and they said it won't be this calendar year. I'm not sure they produce estimates by race anyway.
I'm thinking the American Community Survey might be a good source, but I'm having a hard time getting what I want out of data.census.gov, which ideally would be a csv file containing multiple years of population estimates, stratified by race and age but not by sex. I also signed up for an IPUMS account, but that appears to be individual-level data -- which is not what I want. I'm hoping the hive mind here has a demography expert who can point me in a better direction. I'm a statistician but not a demographer.
My underlying motivation is to understand the effects that changing demographics are likely to have on existing equity issues where schools are concerned. About ten years ago, a local group published a report indicating that my county in particular is an abysmal one to live in if you are black, which I think was a big surprise to the white people here like me (and not at all surprising to black people here). More reading on related issues specific to my county but of more recent vintage would also be helpful, but my current purposes will be best served with actual data.
We do have a center for demography at the local U that has done population projections in the past for local school districts. I reached out to them a few weeks ago to ask when their next projections are likely to be released and they said it won't be this calendar year. I'm not sure they produce estimates by race anyway.
Best answer: Yeah, the American Community Survey is basically your best choice. It gets summarized in a series of standard tables, with table B01001 having age/sex by location, and B01001A through B01001G breaking down the population by standard racial groups. I know you don't care about sex, but the Census Bureau likes to do standardized tables rather than giving you the opportunity to trade levels of detail.
You may or may not be aware, but official tabulations do not break out Hispanic/Latino people as a separate racial group, but rather an orthogonal category of 'ethnicity', where everybody has a race and everybody has an ethnicity (the latter being functionally Hispanic/Latino or not). There are two additional tables B01001H and B01001I (that last one ends with the capital letter i) that break out the Hispanic/Latino population as well as the White Not Hispanic/Latino population. Unfortunately, this doesn't cover everybody -- David Ortiz is both Black and Hispanic, for instance.
There's also a table series B17020 (with A through I variations) that does population by age and poverty status, which might be of some interest; it has 6 years and under as a group.
In any case, these standard tables do five year bins, and your best guess for a single year is to divide by 5, which is a pretty reasonable assumption at the kindergarten age. (It's not at other ages -- sadly, there are a lot fewer 84 year olds than 80 year olds, and college towns have a lot more 19 year olds than 15 year olds, although there are supplemental breakdowns to help with that). In theory, you could pull out an estimate for five year olds by doing a diff between B17020 and B01001 but I suspect that's pretty statistically hinky.
There are two series of ACS tables; one year and five year estimates. The one year estimate is pretty straightforward; the five year estimate is named after the trailing year, so the 2019 five year estimate actually contains estimates based on data collected in 2015-2019. The five year data obviously has a smaller margin of error; for instance, the estimate of male Black children aged 0-4 in 2019 is 967 ±476 from the one year and 1188 ±132 from the five year. As a statistician, you will be better than me at assessing what is more appropriate for your uses.
The one year data is only released for geographies with populations over 100K; that includes Dane county, plus if you were interested in subgeographies, the city of Madison proper as well as three Public Use Microdata Areas (PUMAs) as shown here and with more detail here. One PUMA covers central Madison, the other two cover the northeastern suburbs and eastern county and the southwestern suburbs and western county.
Data.census.gov only gives ACS data from 2010 and later; the ACS started in 2005, so you can get one year data going back that far, but it's a bit more of a pain; you can use the FTP site or API. The good news is that the tables are reasonably standardized.
As a statistician, you might be an R user; I'm aware of the existence of a number of R packages that will pull Census / ACS data for you, especially given table names and FIPS codes for geography (Dane Co: 55025) but I'm not enough of an R user myself to do anything more than vaguely gesture over that way.
With all of that being said, the other angle to come at single year age data is to look at vital statistics providers, which is probably the county / state, which presumably report births by year by some racial categories. While there is some in and out migration, the number of kids born a few years ago is a pretty good predictor of the number of kindergarten aged kids today, plus it provides a glimpse into the future.
posted by Superilla at 2:29 PM on March 5, 2023 [3 favorites]
You may or may not be aware, but official tabulations do not break out Hispanic/Latino people as a separate racial group, but rather an orthogonal category of 'ethnicity', where everybody has a race and everybody has an ethnicity (the latter being functionally Hispanic/Latino or not). There are two additional tables B01001H and B01001I (that last one ends with the capital letter i) that break out the Hispanic/Latino population as well as the White Not Hispanic/Latino population. Unfortunately, this doesn't cover everybody -- David Ortiz is both Black and Hispanic, for instance.
There's also a table series B17020 (with A through I variations) that does population by age and poverty status, which might be of some interest; it has 6 years and under as a group.
In any case, these standard tables do five year bins, and your best guess for a single year is to divide by 5, which is a pretty reasonable assumption at the kindergarten age. (It's not at other ages -- sadly, there are a lot fewer 84 year olds than 80 year olds, and college towns have a lot more 19 year olds than 15 year olds, although there are supplemental breakdowns to help with that). In theory, you could pull out an estimate for five year olds by doing a diff between B17020 and B01001 but I suspect that's pretty statistically hinky.
There are two series of ACS tables; one year and five year estimates. The one year estimate is pretty straightforward; the five year estimate is named after the trailing year, so the 2019 five year estimate actually contains estimates based on data collected in 2015-2019. The five year data obviously has a smaller margin of error; for instance, the estimate of male Black children aged 0-4 in 2019 is 967 ±476 from the one year and 1188 ±132 from the five year. As a statistician, you will be better than me at assessing what is more appropriate for your uses.
The one year data is only released for geographies with populations over 100K; that includes Dane county, plus if you were interested in subgeographies, the city of Madison proper as well as three Public Use Microdata Areas (PUMAs) as shown here and with more detail here. One PUMA covers central Madison, the other two cover the northeastern suburbs and eastern county and the southwestern suburbs and western county.
Data.census.gov only gives ACS data from 2010 and later; the ACS started in 2005, so you can get one year data going back that far, but it's a bit more of a pain; you can use the FTP site or API. The good news is that the tables are reasonably standardized.
As a statistician, you might be an R user; I'm aware of the existence of a number of R packages that will pull Census / ACS data for you, especially given table names and FIPS codes for geography (Dane Co: 55025) but I'm not enough of an R user myself to do anything more than vaguely gesture over that way.
With all of that being said, the other angle to come at single year age data is to look at vital statistics providers, which is probably the county / state, which presumably report births by year by some racial categories. While there is some in and out migration, the number of kids born a few years ago is a pretty good predictor of the number of kindergarten aged kids today, plus it provides a glimpse into the future.
posted by Superilla at 2:29 PM on March 5, 2023 [3 favorites]
Former IPUMS employee here-- in addition to the individual-level data, the IPUMS-NHGIS project warehouses aggregate data, including the American Community Survey.
posted by mcstayinskool at 2:55 PM on March 5, 2023 [1 favorite]
posted by mcstayinskool at 2:55 PM on March 5, 2023 [1 favorite]
« Older When you try your best, but you don’t succeed… | Windows 11 volume OSD stopped working Newer »
This thread is closed to new comments.
Alternatively, if you are willing to accept actual kindergarten enrollment by race in public schools in your district/county you have the option of using the Common Core of Data (CCD).
posted by oceano at 9:53 AM on March 5, 2023