Download data from Social Explorer¶

4.1.png

Read, manage, and export data¶

Read data¶

clear
set more off
cd "C:\Users\Neo\Desktop\demo"
infile using "R12741611.dct", using("R12741611_SL050.txt")
compress



C:\Users\Neo\Desktop\demo


dictionary {
_first(2)
str50 FIPS "FIPS"
str40 GEOID "Geographic Identifier"
str244 NAME "Area Name"
str244 QName "Qualifying Name"
str244 STUSAB "State Postal Abbreviation"
str244 SUMLEV "Summary Level"
str244 GEOCOMP "Geographic Component"
str6 FILEID "File identification"
str244 LOGRECNO "Logical Record Number"
str1 US "US"
str1 REGION "Census Region"
str1 DIVISION "Census Division"
str2 STATECE "State (Census Code)"
str2 STATE "State (FIPS Code)"
str3 COUNTY "County of current residence"
str5 COUSUB "County Subdivision (FIPS)"
str5 PLACE "Place (FIPS Code)"
str7 PLACESE "Place (State FIPS + Place FIPS)"
str6 TRACT "Census Tract"
str1 BLKGRP "Block Group"
str5 CONCIT "Consolidated City"
str4 AIANHH "American Indian Area/Alaska Native Area/Hawaiian Home Land (Census)
> "
str5 AIANHHFP "American Indian Area/Alaska Native Area/Hawaiian Home Land (FIPS)
> "
str1 AIHHTLI "American Indian Trust Land/Hawaiian Home Land Indicator"
str3 AITSCE "American Indian Tribal Subdivision (Census)"
str5 AITS "American Indian Tribal Subdivision (FIPS)"
str5 ANRC "Alaska Native Regional Corporation (FIPS)"
str5 CBSA "Metropolitan and Micropolitan Statistical Area"
str3 CSA "Combined Statistical Area"
str5 METDIV "Metropolitan Division"
str1 MACC "Metropolitan Area Central City"
str1 MEMI "Metropolitan/Micropolitan Indicator Flag"
str5 NECTA "New England City and Town Area"
str3 CNECTA "New England City and Town Area"
str5 NECTADIV "New England City and Town Area Division"
str5 UA "Urban Area"
str5 UACP "Urban Area Central Place"
str2 CDCURR "Current Congressional District ***"
str3 SLDU "State Legislative District Upper"
str3 SLDL "State Legislative District Lower"
str6 VTD "Voting District"
str3 ZCTA3 "ZIP Code Tabulation Area (3-digit)"
str5 ZCTA5 "5-digit ZIP Code Tabulation Area"
str2 SUBMCD "Subminor Civil Division (FIPS)"
str5 SDELM "State-School District (Elementary)"
str5 SDSEC "State-School District (Secondary)"
str5 SDUNI "State-School District (Unified)"
str1 UR "Urban/Rural"
str1 PCI "Principal City Indicator"
str6 TAZ "Traffic Analysis Zone"
str5 UGA "Urban Growth Area"
str6 BTTR "Tribal Tract"
str1 BTBG "Tribal Block Group"
str5 PUMA5 "Public Use Microdata Area - 5% File"
str5 PUMA1 "Public Use Microdata Area - 1% File"
long A00001_001 "Total Population"
long A12001_001 "Population 25 Years and Over:"
long A12001_002 "Population 25 Years and Over: Less than High School"
long A12001_003 "Population 25 Years and Over: High School Graduate (Includes Eq
> uivalency)"
long A12001_004 "Population 25 Years and Over: Some College"
long A12001_005 "Population 25 Years and Over: Bachelor's Degree"
long A12001_006 "Population 25 Years and Over: Master's Degree"
long A12001_007 "Population 25 Years and Over: Professional School Degree"
long A12001_008 "Population 25 Years and Over: Doctorate Degree"
double A14008_001 "Average Household Income (In 2019 Inflation Adjusted Dollars)
> "
}

(3,220 observations read)

  variable FIPS was str50 now str5
  variable GEOID was str40 now str12
  variable NAME was str244 now str33
  variable QName was str244 now str42
  variable STUSAB was str244 now str2
  variable SUMLEV was str244 now str3
  variable GEOCOMP was str244 now str2
  variable FILEID was str6 now str5
  variable LOGRECNO was str244 now str7
  variable STATECE was str2 now str1
  variable COUSUB was str5 now str1
  variable PLACE was str5 now str1
  variable PLACESE was str7 now str1
  variable TRACT was str6 now str1
  variable CONCIT was str5 now str1
  variable AIANHH was str4 now str1
  variable AIANHHFP was str5 now str1
  variable AITSCE was str3 now str1
  variable AITS was str5 now str1
  variable ANRC was str5 now str1
  variable CBSA was str5 now str1
  variable CSA was str3 now str1
  variable METDIV was str5 now str1
  variable NECTA was str5 now str1
  variable CNECTA was str3 now str1
  variable NECTADIV was str5 now str1
  variable UA was str5 now str1
  variable UACP was str5 now str1
  variable CDCURR was str2 now str1
  variable SLDU was str3 now str1
  variable SLDL was str3 now str1
  variable VTD was str6 now str1
  variable ZCTA3 was str3 now str1
  variable ZCTA5 was str5 now str1
  variable SUBMCD was str2 now str1
  variable SDELM was str5 now str1
  variable SDSEC was str5 now str1
  variable SDUNI was str5 now str1
  variable TAZ was str6 now str1
  variable UGA was str5 now str1
  variable BTTR was str6 now str1
  variable PUMA5 was str5 now str1
  variable PUMA1 was str5 now str1
  (5,052,180 bytes saved)

Generate percentage of having high school and above education¶

gen highsch_pct = (A12001_001 - A12001_002) / A12001_001 * 100
sum highsch_pct



    Variable |        Obs        Mean    Std. Dev.       Min        Max
-------------+---------------------------------------------------------
 highsch_pct |      3,220    86.62676    6.578305   26.43979   98.88309

Generate total population and average household income¶

gen total_pop = A00001_001
gen mean_inc = A14008_001
sum total_pop mean_inc



    Variable |        Obs        Mean    Std. Dev.       Min        Max
-------------+---------------------------------------------------------
   total_pop |      3,220    101868.4    327344.6         66   1.01e+07
    mean_inc |      3,220    69170.73    18374.89   16881.37   181260.8

Export data to excel for merging with spatial data¶

preserve

rename FIPS fips
keep fips total_pop highsch_pct mean_inc
export excel using "social_explorer_data.xlsx", firstrow(variables) replace

restore



file social_explorer_data.xlsx saved

Download excel data from Social Explorer¶

You can also download excel data directly from Social Explorer and join the excel data to a spatial feature as follows

Join excel data to a spatial feature¶

Social Explorer -> Geodata -> U.S. County 1970 - 2019 -> COUNTY 2019 US SL050 Coast Clipped

Table of Contents