This is my first post, and I am seeking some guidance
The ACS 2015-2019 metadata file named 2019_DataProductList.xlsx states that there are 57 separate TableIDs for subject "X_26" (GROUP QUARTERS)
But how do I know in advance at what geographic region this information is avaialble? I did a test with "census tracts" for the state of Hawaii (State FIPS 15)
But if one actually downloads the pre-joined TIGER/Line shapefiles in geodatabase and shapefile format with “Selected Demographic and Economic Data” for census tracts in the state of Hawaii for the 2015-2019 American Community Survey (ACS) 5-Year Estimates there is only ONE TableID (B26001) with only one field/variable called B26001e (and the associated measurement error B26001m)!!
1. So I am so curious what happened to all other 56 TableIDs for the state of Hawaii at the census tract level in the 2015-2019 American Community Survey (ACS) 5-Year Estimates
2. How do I know in advance what TableIDs are provided for each subject area at the census tract level, census subdivision (CSD), county and state level in Hawaii?
3 There appear to be 29 subject areas in the 2019 American Community Survey (ACS) 5-Year Estimates. Fortunately 23 of the 29 subject areas had all of the TableIDs advertised in the 2019_DataProductList.xlsx metadata
But what about the remaining six subject areas? How does one know how many Table IDs are actually in the “Selected Demographic and Economic Data” for the state of Hawaii for the 2015-2019 American Community Survey (ACS) 5-Year Estimates for each Table ID at each level of analysis
I manually checked each of the table IDs in each of the 29 subject areas for the census tract data in Hawaii, but surely there is a summary list somewhere?
Hi Jason - the pre-joined do not have all the tables in them. that's what they mean by "Selected data." You can see what level the tables are available at using Appendix A in the 5-year appendices documentation found on the Census website here: https://www.census.gov/programs-surveys/acs/data/summary-file.2019.html I created an updated version of that with an added Appendix C that includes the table structure here: https://gisdiva.com/downloads/acs2019-5yr/
Thanks for providing such rapid and valuable feedback. That clears up a lot!
1. I am curious about whether there is a simple CSV file that has information about all the Census Designated Places (CDP) in a given state.
For Hawaii I can only find ACS information on blocks/block groups, census tracts, county subdivisions, counties, and the state
U.S. Census Bureau QuickFacts: Nanakuli CDP, Hawaii
It seems that some CDPs variables cannot be directly compared to the above geographies:
"Estimates are not comparable to other geographic levels due to methodology differences that may exist between different data sources. Some estimates presented here come from sample data, and thus have sampling errors that may render some apparent differences between geographies statistically indistinguishable."
I am hoping there is a way to compare CDPs over time? Say compare 2015-2019 5 year ACS with 2010-2014 5 year ACS (or 2019 1 year ACS with 2018 1 year ACS). Is there a file that has the data for different years for the CDPs?
2. I am unclear about the "Summary File Starting and Ending Positions" mentioned in Appendix A ("Sequence Tables (SEQ) in the 5-Year Summary Files").
For example the first TableID is B01001 with "7 to 55" followed by B01001A from "56 to 86"
There are indeed 49 variables/fields associated with B01001 (55-7+1).
But I am not sure which file I should use to find row 7? I have been using the table shells and table lists from below (and the excel files associated with B01001 begin at row 2 or 3, not row 7)
Table Shells and Table List (census.gov)
Apologies, but the documentation is for the Summary Files, so you need a better understanding of how the summary files work in order to completely understand all the information in the Appendices document I mentioned. You'll need to review the handbook on how to work with the ACS files to get a full understanding. See here:
1) Since all places in Hawaii are CDPs you would use Summary level 160 " State-Place" (see Appendix B). If you're using SQL you could do a simple query on the Geography table for al records with sumlevel = 160.
2) the starting and ending positions refer to the summary sequence files which are raw text files. This is explained in the handbook I linked above.
I don't know your skill level, but if you're not familiar working with raw text files and importing them into relational databases and linking them together, I would suggest you use the Excel file as a guide to look up the table numbers at data.census.gov and then export the data from there. then at data.census.gov you can modify the geography for the table by clicking the "Geos" icon at the top. then select by geography or summary level.
here's a link to the table B01001 for all CDPs in Hawaii (summary level 160):data.census.gov/.../table
Other folks on here have experience with many other Census data retrieval tools, so they may be able to point you to other useful resources. I'm "old school" and have been working with the raw summary files since the 1990 Census so I always load them into SQL and do my queries that way. hopefully I didn't confuse you!
I have ArcGIS Pro/ArGIS Online and Excel and SPSS (but no SAS). Therefore I would like to learn how to use read the ACS Summary File into Excel for beginners. I would like to consider the simplest possible case for now
Therefore, I am using the 2019 1 year data for sequence 1 in Hawaii (and later ideally sequence 1 in Hawaii for all geographies). I followed these instructions
Instructions on How to Read the ACS Summary File into Excel (census.gov)
I now have the following downloaded:
two excel files:
It is not clear to me what to do next from these instructions
Under the Excel Import Tool Instructions (on page 2 of 10) it states:
"Unzip the files to a single local directory. Open the template “Seq1” (in Excel) and follow the steps below
I am stuck here. I don't know how to Open the template “Seq1”. I don't even see Seq1?
sounds like you downloaded the wrong file? Try getting the data directly from the FTP site:
Also, FYI - you will need the 5 year data if you want CDP level data.
I'm afraid I can't help you much with the Excel/SPSS route. You should dig into the documentation provided by the Census Bureau to get a thorough understanding of the files and how they work together. Or perhaps someone else here has a script already developed to read the files into SPSS and can help you out. Sorry I'm not much help. If you were using SQL I could provide files for that.
OK. i am not familiar with SQL. Do I need to buy an SQL product ?
I went to the FTP site you listed above and found this. I previously downloaded
1_year_seq_by_state >> Hawaii >>
Is that correct, or do I need other folders from here as well?
1_year_by_state/01-Sep-2020 20:24- 1_year_comparison_profiles/08-Sep-2020 09:52- 1_year_entire_sf/01-Sep-2020 20:29- 1_year_geographic_comparison_tables/08-Sep-2020 09:47- 1_year_ranking/08-Sep-2020 10:15- 1_year_seq_by_state/01-Sep-2020 22:48- 5_year_by_state/18-Nov-2020 14:50- 5_year_entire_sf/18-Nov-2020 17:25- 5_year_seq_by_state/18-Nov-2020 19:42- 2019_1yr_Summary_FileTemplates.zip20-Aug-2020 17:452.0M 2019_5yr_Summary_FileTemplates.zip03-Dec-2020 07:541.6M
Dear Jami, I appreciate your offer to help with SQL. Accordingly I installed the Micrsoft 2019 SQL Server Express edition. Will this work with the American Community Survey data?
Yes that should work. You can download the SQL files here: https://gisdiva.com/downloads/acs2019-5yr/
Sorry I dont have Hawaii pulled out separately, and don't have time to pull that out and upload it as I'll be on traveling the next 2 days. So you'll need to download the big file along with the Readme file and the Excel file. You should only need Hawaii - look for the file ACS2019_5yr_hi.7z in the large zipfile. read the README file for more details.
Good luck. If all else fails, I suggest you get the tables you need form data.census.gov - easy to query and download then link to your shapefile via the GEOID. I kind of feel like you're getting more than you really need - but I am not sure what your project involves.
thanks so much for providing some help! I think I am overcomplicating things
Yes, downloading a specific tableID from data.census.gov and using an ArcGIS Pro join "via the GEOID" is very trivial. The US Census even provides a video tutorial and it is something our undergraduate students can do.
But I am trying understand how to load (any) raw summary file into SQL and do a query . So I unzipped your maine folder with 7zip (ACS2019_5yrs_me.zip) which contains ACS2019_5yr_me.bak
We spent the past few hours with our IT department trying to understand your instructions on how to restore the file
Using the free SQL Server (Express) 2019 together with SQL Server Management Studio (SSMS) 18.10 there is an option to restore BAK files I believe. The GUI steps are
2. restore database
4. search for folder (at the three dots) ... and add the folder
But here we cannot navigate to your folder. After multiple hours of trying the university help desk just said I should go back and ask you why about the solution. Maybe this is the problem and solution?
Cannot navigate to some folders when attempting to attach .MDF files to database in Management Studio (microsoft.com)
But our university really is unfamiliar with Microsoft SQL and system level/registry permissions so we are stuck. It is easier it seems to just click in the SQL Server Management Studio (SSMS) to backup a file but the below command also doesn't work for us
We also tried entering this directly:
RESTORE DATABASE @ACS2019_5yr_me FROM@C:\Users\jlevy\Desktop\jlevy\ + @ACS2019_5yr_me.bak
This was the error message:
Msg 137, Level 15, State 2, Line 2Must declare the scalar variable "@ACS2019_5yr_me".Completion time: 2021-12-05T11:50:12.2076973-10:00
We are open to any suggestions. Maybe it is simpler to access a summary file that does not have a bak extension? Maybe there is just a simple video to show how to access the summary files and do a query with SQL Server Managemetn Studio 18.10 and SQL Server 2019?
I probably forgot to say we are so happy you reached out and offered a sample file:
A typo (augh) in the above
I wanted to say "the SQL Server Management Studio (SSMS)" seems to have a simple GUI to "restore" bak files. But when trying to restore the file we are unable to access the folders we download from your website
maybe this is the issue and solution (but our help desk thinks there may be permissions challenges associated with your folders and asked me to reach out again)
The sequence files need to be combined "sideways" first. One table may require multiple sequence files, or a single sequence file can contain several tables. If you need row 7 of some table, you will need the sequence file that contains that table and row, and count across (not down) to the 7th value after the start of the data for the table. The "rows" in the sequence file are geographies.
Working with summary data sequence files is tricky. I would never use them for direct analysis. They almost always need substantial preprocessing to turn them back into usable tables.
Thanks for your reply. I don't need a specific row of a specific table
I am just trying to find step by step instructions to US Census data into SQL and do a query. Ideally with the free SQL Server (Express) 2019 together with SQL Server Management Studio (SSMS)
However I would be curious about the terminology of Table numbers/IDs now that you mentioned it.
Consider Subject/Topic Area Age and Sex (X01). From the metadata there are
So do I express this as follows:
I want to start documenting Table IDs since I was unable to find which "select Table IDs" are actually available in the twenty-nine topic/subject areas found in the 2015-2019 Selected Demographic and Economic Data for ArcGIS Pro so I had to document this myself
For X01 for example all of the 30 Table IDs found below were also found in the 2015-2019 Selected Demographic and Economic Data for ArcGIS Pro
I'm not sure i understand your question. The table shows availability of data tables for the 5-year and 1-year ACS releases. "1, 5" means that table is available in both 1- and 5-year ACS releases. "1" means that table is only available in the 1-year release. "Fields" is just the number of fields in each table.