Ranking and Geo Compare tables, I usually use data.census.gov API but there are some great single measure comparisons, anyone use these tables.

OK, using data.census.gov against Geo compare tables, I was able to discover something I thought might be true (and very disappointing). In data.census.gov they create over 1,000 tables. Because these tables contain many elements, when they produce these tables the privacy filters are very aggressive at the lower levels because the breakout of all these fields gives very small numbers. So the records (all fields) for a location may be blocked (-9999 etc.) for a specific table.  Therefore, if you want to just pull a single data element from that table at a lower level, you will get a -9999 even if that single element at that level itself is not low enough to be blocked.  It's because it's pulling from that file which has already been filtered based on all the fields. If you could pull the single data element from a full dataset \ database with filtering on it would give you the data at the lower level. However, the ONLY way to get the data at lower levels (for me it's county) is to use data.census.gov you will miss a great deal of lower-level data.  You can see this by pulling https://api.census.gov/data/2021/acs/acs1/subject?get=S1201_C06_002E,NAME&for=county:* and then using https://www.census.gov/acs/www/data/data-tables-and-tools/geographic-comparison-tables/ table GCT1201.  

API

"-999999999.0","Harrison County, Mississippi","28","047"],
["-999999999.0","Jackson County, Mississippi","28","059"],
["-999999999.0","Madison County, Mississippi","28","089"],

This is why the Geo comparison tables have data for counties for males over 15 never married, that you can't pull (in any way from data,cenus.gov or the API).

Therefore, there is a great deal of data that should be available (not within the privacy restrictions) but can never be pulled because of this. Yes, to put ACS into a database would be large, but by today's standards and power not an issue. I'm fairly sure (although not positive) that using R or other methods of getting the data will also not produce this data at lower levels like county. The ranking and Geo tabes are actually pulling from the full data set (for 1 field) and therefore can get all this lower-level data (with of course a privacy filter) up to the point where it's actually too low to release for that specific field.

Because the ranking and Geo tables have limited fields, you have to use data,census.gov or the API to pull data (whatever you can get) there's a lot you're missing. 

Tom

OK, using data.census.gov against Geo compare tables, I was able to discover something I thought might be true (and very disappointing). In data.census.gov they create over 1,000 tables. Because these tables contain many elements, when they produce these tables the privacy filters are very aggressive at the lower levels because the breakout of all these fields gives very small numbers. So the records (all fields) for a location may be blocked (-9999 etc.) for a specific table.  Therefore, if you want to just pull a single data element from that table at a lower level, you will get a -9999 even if that single element at that level itself is not low enough to be blocked.  It's because it's pulling from that file which has already been filtered based on all the fields. If you could pull the single data element from a full dataset \ database with filtering on it would give you the data at the lower level. However, the ONLY way to get the data at lower levels (for me it's county) is to use data.census.gov you will miss a great deal of lower-level data.  You can see this by pulling https://api.census.gov/data/2021/acs/acs1/subject?get=S1201_C06_002E,NAME&for=county:* and then using https://www.census.gov/acs/www/data/data-tables-and-tools/geographic-comparison-tables/ table GCT1201.  

API

"-999999999.0","Harrison County, Mississippi","28","047"],
["-999999999.0","Jackson County, Mississippi","28","059"],
["-999999999.0","Madison County, Mississippi","28","089"],

This is why the Geo comparison tables have data for counties for males over 15 never married, that you can't pull (in any way from data,cenus.gov or the API).

Therefore, there is a great deal of data that should be available (not within the privacy restrictions) but can never be pulled because of this. Yes, to put ACS into a database would be large, but by today's standards and power not an issue. I'm fairly sure (although not positive) that using R or other methods of getting the data will also not produce this data at lower levels like county. The ranking and Geo tabes are actually pulling from the full data set (for 1 field) and therefore can get all this lower-level data (with of course a privacy filter) up to the point where it's actually too low to release for that specific field.

Because the ranking and Geo tables have limited fields, you have to use data,census.gov or the API to pull data (whatever you can get) there's a lot you're missing. 

Tom