7,612
edits
Changes
Jump to navigation
Jump to search
American Community Survey (ACS) Data (view source)
Revision as of 20:16, 14 October 2019
, 20:16, 14 October 2019→Loading to a Dbase
The zip file from AFF was downloaded and extracted to E:\projects\agglomeration\ACS\AFFFiles
There are 13 files entitled ACS_17_5YR_table_with_ann.csv, where table is S1501, etc. The files have two header lines and are CSV, with quoted strings of the format "Abanda CDP, Alabama" for the display label. There are 5 columnsin the first file:
*Geo.id e.g., 1600000US0100100
*GEO.id2 e.g., 0100100
*HD02_VD01 (Margin of Error; Total) e.g., 158
Geo.id2 appears to match our Geoids. Note the leading zero, so these fields should be loaded as converted varchars(7) with front padding.
Checking Geo.id2 of 0103076:
AL Alabama 380 01 03076 02403132 0103076 Auburn Auburn city 25 C1 Y N G4110 A 152375113 2646161 +32.6077220 -085.4895446
1600000US0103076,0103076,"Auburn city, Alabama",61462,65
All files are renamed table.txt and have their second header line removed. The first three columns are common to all files, but then files can have multiple estimate and margin columns. Redundant ones are deleted. The state is also split out. The following are kept:
*B01003 - Population - Total, margin
*B02001 - Race - Total, WhiteAlone, BlackAlone, IndianAlone, AsianAlone, IslanderAlone, OtherAlone, TwoPlus
*B07201 - Moving - Total, Same1yr, DiffinUS1yr, DiffinMSA1yr, Abroad1yr
*B08303 - Driving - Total, Lt5, Btw5And9, Btw10And14, Btw..., Btw60And89, Gt90
*B19013 - HHIncome - MedHHInc2017
*B19053 - SelfEmployment - Total, NumWSelfEmpIncome, NumWOSelfEmpInc
*B19083 - GiniIndex - Gini, margin
*B25003 - Accomodation - Total, Owner, Renter
*B25105 - Housing - monthlyhousingcost
*B28011 - Internet - Total, InternetSub, DialUp, Broadband, Satellite, Other, NoSub, NoInternet
*S1501 - Education - Total25plus, Male25Plus, Female25Plus, Assoc25Plus, Bach25Plus, Grad25Plus, HSOrHigher, BachOrHigher, PCPovertyDueToEdu, MedEarnings
*S2301 - Labor - Total, LFPR, EmpPopRatio, UnEmpRate
