I'm looking for a resource or some ideas about handling the metadata that indent levels provide in ACS row names. The use of indents to represent different categories often creates repeated names for rows, under different parent categories.
Row names are often structured in a nested way, e.g. in table B01001, Sex by Age, the first several rows are
So Row 3 is named just named "Under 5 years" with an indent. That's the same as Row 27, under Female. The Excel table downloads and templates don't provide the names for parent categories in the data row - and categories can go at least a couple of layers deep. CSV's do the same, with multiple spacing instead of tabs. This issue can make working with individual rows from downloaded data tricky.
When you download csv files in a ZIP format, you do get metadata and data files with complete data column names like "Estimate!!Total:!!Male:!!Under 5 years", which can be converted into something legible. But that's an extra step for each distinct table you download. I don't know of a way to do it if you want to work with metadata for all tables.
Another inelegant solution would be to find a cell's indent level in files downloaded directly from the UI and infer that the previous indent level(s) represents the parent category/categories. That's doable in data analytic software, even in Excel with VBA, but relying on typography to infer data categories doesn't seem like the best idea.
So, does anyone know of a resource that would provide columns named something like "Male: Under 5 years" directly for a all tables, or a set of tables? Or to be complete, a name like "Total: Male: Under 5 years" might be better in general.
Does anyone have any better ideas or resources? Am I missing something?
Thanks -
Jon
That's weird, the indents are not in the Excel versions of the table shells lists. They were included in the text version of the file, which for some reason the Bureau has removed from their site.
Someone on the SCD email list just mentioned that all versions of the table-shells file are available on the FTP site at https://www2.census.gov/programs-surveys/acs/summary_file/2022/table-based-SF/documentation…
For what it is worth, I have an R function that downloads the table and metadata via the API and attaches the row labels preserving the "indent" (as you call it) structure. Tidycensus doesn't seem to take care of this as far as I can tell.
Dave Dorer
The shells file has indent levels in its own column.
I don't see it. Am I looking in the wrong place, e.g., https://www2.census.gov/programs-surveys/acs/tech_docs/table_shells/2022/ ?
That's weird, the indents are not in the Excel versions of the table shells lists. They were included in the text version of the file, which for some reason the Bureau has removed from their site. I fortunately created an excel version of that one, it's here: https://mcdc.missouri.edu/data/acs2022/acs2022_5yr_table_shells.xlsx
Thanks, exactly what I needed.
Someone on the SCD email list just mentioned that all versions of the table-shells file are available on the FTP site at https://www2.census.gov/programs-surveys/acs/summary_file/2022/table-based-SF/documentation/ -- you'll need the pipe-delimited text version to see the indents.
A couple other sources you could use:
FWIW, when we add the data to NHGIS, we generally try to use the API endpoints to get the concatenated labels, but unfortunately, we start processing the 5-year data during the 2-day embargo period before the public data release, and new endpoints aren't available until then. This year we got started on 2022 5-year processing by using a 2022 1-year variables list and adding in 2021 5-year labels for the 10 5-year tables that aren't in 1-year data.Also: the API list is apparently randomly ordered, so it may take a little extra effort to select and order the labels for your variables of interest.
TidyCensus does a nice job of aggregating the various subparts with the load_variables function, for example
x <- load_variables(2022, "acs5")
You get output like