cd "" // set working directory // 2020 --> 2010 CROSSWALK // from NHGIS import delimited "raw/nhgis_blk2020_blk2010_gj_37.csv", clear // Create Geographic Identifiers for linking into other data gen state = substr(gjoin2020, 2,2) gen county = substr(gjoin2020,5,3) gen tract = substr(gjoin2020,9,6) gen block = substr(gjoin2020,15,19) gen geoid = state + county + tract + block drop state county tract block destring geoid, gen(geocode) format %20.0g geocode save "raw/nhgis_crosswalk_2020_2010.dta", replace // BRING IN PL 94-171 DATA FROM OTHER STATES * Downloaded from biglocalnews.org // CENSUS 2020 DATA Co-Op import delimited "raw/2020_blocks_other/GA_pl94171_2020_blocks_wide.csv", clear format %20.0g geocode merge 1:m geocode using "raw/nhgis_crosswalk_2020_2010.dta" keep if _merge == 3 drop _merge save "raw/2020_blocks_other/non_nc_blocks.dta", replace foreach x in SC TN VA { import delimited "raw/2020_blocks_other/`x'_pl94171_2020_blocks_wide.csv", clear format %20.0g geocode merge 1:m geocode using "raw/nhgis_crosswalk_2020_2010.dta" keep if _merge == 3 drop _merge append using "raw/2020_blocks_other/non_nc_blocks.dta", force save "raw/2020_blocks_other/non_nc_blocks.dta", replace } use "raw/2020_blocks_other/non_nc_blocks.dta", clear * reweight non-NC data to 2010 foreach x in p0010001 p0010002 p0010003 p0010004 p0010005 p0010006 p0010007 p0010008 p0010009 p0010010 p0010011 p0010012 p0010013 p0010014 p0010015 p0010016 p0010017 p0010018 p0010019 p0010020 p0010021 p0010022 p0010023 p0010024 p0010025 p0010026 p0010027 p0010028 p0010029 p0010030 p0010031 p0010032 p0010033 p0010034 p0010035 p0010036 p0010037 p0010038 p0010039 p0010040 p0010041 p0010042 p0010043 p0010044 p0010045 p0010046 p0010047 p0010048 p0010049 p0010050 p0010051 p0010052 p0010053 p0010054 p0010055 p0010056 p0010057 p0010058 p0010059 p0010060 p0010061 p0010062 p0010063 p0010064 p0010065 p0010066 p0010067 p0010068 p0010069 p0010070 p0010071 p0020001 p0020002 p0020003 p0020004 p0020005 p0020006 p0020007 p0020008 p0020009 p0020010 p0020011 p0020012 p0020013 p0020014 p0020015 p0020016 p0020017 p0020018 p0020019 p0020020 p0020021 p0020022 p0020023 p0020024 p0020025 p0020026 p0020027 p0020028 p0020029 p0020030 p0020031 p0020032 p0020033 p0020034 p0020035 p0020036 p0020037 p0020038 p0020039 p0020040 p0020041 p0020042 p0020043 p0020044 p0020045 p0020046 p0020047 p0020048 p0020049 p0020050 p0020051 p0020052 p0020053 p0020054 p0020055 p0020056 p0020057 p0020058 p0020059 p0020060 p0020061 p0020062 p0020063 p0020064 p0020065 p0020066 p0020067 p0020068 p0020069 p0020070 p0020071 p0020072 p0020073 p0030001 p0030002 p0030003 p0030004 p0030005 p0030006 p0030007 p0030008 p0030009 p0030010 p0030011 p0030012 p0030013 p0030014 p0030015 p0030016 p0030017 p0030018 p0030019 p0030020 p0030021 p0030022 p0030023 p0030024 p0030025 p0030026 p0030027 p0030028 p0030029 p0030030 p0030031 p0030032 p0030033 p0030034 p0030035 p0030036 p0030037 p0030038 p0030039 p0030040 p0030041 p0030042 p0030043 p0030044 p0030045 p0030046 p0030047 p0030048 p0030049 p0030050 p0030051 p0030052 p0030053 p0030054 p0030055 p0030056 p0030057 p0030058 p0030059 p0030060 p0030061 p0030062 p0030063 p0030064 p0030065 p0030066 p0030067 p0030068 p0030069 p0030070 p0030071 p0040001 p0040002 p0040003 p0040004 p0040005 p0040006 p0040007 p0040008 p0040009 p0040010 p0040011 p0040012 p0040013 p0040014 p0040015 p0040016 p0040017 p0040018 p0040019 p0040020 p0040021 p0040022 p0040023 p0040024 p0040025 p0040026 p0040027 p0040028 p0040029 p0040030 p0040031 p0040032 p0040033 p0040034 p0040035 p0040036 p0040037 p0040038 p0040039 p0040040 p0040041 p0040042 p0040043 p0040044 p0040045 p0040046 p0040047 p0040048 p0040049 p0040050 p0040051 p0040052 p0040053 p0040054 p0040055 p0040056 p0040057 p0040058 p0040059 p0040060 p0040061 p0040062 p0040063 p0040064 p0040065 p0040066 p0040067 p0040068 p0040069 p0040070 p0040071 p0040072 p0040073 p0050001 p0050002 p0050003 p0050004 p0050005 p0050006 p0050007 p0050008 p0050009 p0050010 pop100 hu100 h0010001 h0010002 h0010003 { replace `x' = `x' * parea } collapse (sum) p0010001 p0010002 p0010003 p0010004 p0010005 p0010006 p0010007 p0010008 p0010009 p0010010 p0010011 p0010012 p0010013 p0010014 p0010015 p0010016 p0010017 p0010018 p0010019 p0010020 p0010021 p0010022 p0010023 p0010024 p0010025 p0010026 p0010027 p0010028 p0010029 p0010030 p0010031 p0010032 p0010033 p0010034 p0010035 p0010036 p0010037 p0010038 p0010039 p0010040 p0010041 p0010042 p0010043 p0010044 p0010045 p0010046 p0010047 p0010048 p0010049 p0010050 p0010051 p0010052 p0010053 p0010054 p0010055 p0010056 p0010057 p0010058 p0010059 p0010060 p0010061 p0010062 p0010063 p0010064 p0010065 p0010066 p0010067 p0010068 p0010069 p0010070 p0010071 p0020001 p0020002 p0020003 p0020004 p0020005 p0020006 p0020007 p0020008 p0020009 p0020010 p0020011 p0020012 p0020013 p0020014 p0020015 p0020016 p0020017 p0020018 p0020019 p0020020 p0020021 p0020022 p0020023 p0020024 p0020025 p0020026 p0020027 p0020028 p0020029 p0020030 p0020031 p0020032 p0020033 p0020034 p0020035 p0020036 p0020037 p0020038 p0020039 p0020040 p0020041 p0020042 p0020043 p0020044 p0020045 p0020046 p0020047 p0020048 p0020049 p0020050 p0020051 p0020052 p0020053 p0020054 p0020055 p0020056 p0020057 p0020058 p0020059 p0020060 p0020061 p0020062 p0020063 p0020064 p0020065 p0020066 p0020067 p0020068 p0020069 p0020070 p0020071 p0020072 p0020073 p0030001 p0030002 p0030003 p0030004 p0030005 p0030006 p0030007 p0030008 p0030009 p0030010 p0030011 p0030012 p0030013 p0030014 p0030015 p0030016 p0030017 p0030018 p0030019 p0030020 p0030021 p0030022 p0030023 p0030024 p0030025 p0030026 p0030027 p0030028 p0030029 p0030030 p0030031 p0030032 p0030033 p0030034 p0030035 p0030036 p0030037 p0030038 p0030039 p0030040 p0030041 p0030042 p0030043 p0030044 p0030045 p0030046 p0030047 p0030048 p0030049 p0030050 p0030051 p0030052 p0030053 p0030054 p0030055 p0030056 p0030057 p0030058 p0030059 p0030060 p0030061 p0030062 p0030063 p0030064 p0030065 p0030066 p0030067 p0030068 p0030069 p0030070 p0030071 p0040001 p0040002 p0040003 p0040004 p0040005 p0040006 p0040007 p0040008 p0040009 p0040010 p0040011 p0040012 p0040013 p0040014 p0040015 p0040016 p0040017 p0040018 p0040019 p0040020 p0040021 p0040022 p0040023 p0040024 p0040025 p0040026 p0040027 p0040028 p0040029 p0040030 p0040031 p0040032 p0040033 p0040034 p0040035 p0040036 p0040037 p0040038 p0040039 p0040040 p0040041 p0040042 p0040043 p0040044 p0040045 p0040046 p0040047 p0040048 p0040049 p0040050 p0040051 p0040052 p0040053 p0040054 p0040055 p0040056 p0040057 p0040058 p0040059 p0040060 p0040061 p0040062 p0040063 p0040064 p0040065 p0040066 p0040067 p0040068 p0040069 p0040070 p0040071 p0040072 p0040073 p0050001 p0050002 p0050003 p0050004 p0050005 p0050006 p0050007 p0050008 p0050009 p0050010 pop100 hu100 h0010001 h0010002 h0010003, by(gjoin2010) * rename to match the recoding done in R for NC extract renvars *100, subst(100 2020) // renaming pop100 and hu100 pop2020 and hu2020 drop p001* * race by ethnicity rename p0020002 hisp2020 rename p0020003 nh2020 rename p0020004 nh_white2020 rename p0020005 nh_black2020 rename p0020006 nh_amin2020 rename p0020007 nh_asian2020 rename p0020008 nh_paci2020 rename p0020009 nh_other2020 rename p0020010 nh_twomore2020 drop p002* * race by ethnicity adults drop p003* rename p0040001 pop18_2020 rename p0040002 pop18_hisp2020 rename p0040003 pop18_nh2020 rename p0040004 pop18_nh_white2020 rename p0040005 pop18_nh_black2020 rename p0040006 pop18_nh_amin2020 rename p0040007 pop18_nh_asian2020 rename p0040008 pop18_nh_paci2020 rename p0040009 pop18_nh_other2020 rename p0040010 pop18_nh_twomore2020 drop p004* * GQ rename p0050001 gq2020 rename p0050002 gq_inst2020 rename p0050003 gq_inst_corrad2020 rename p0050004 gq_inst_corrjuv2020 rename p0050005 gq_inst_nh2020 rename p0050006 gq_inst_other2020 rename p0050007 gq_noninst2020 rename p0050008 gq_noninst_dorm2020 rename p0050009 gq_noninst_mil2020 rename p0050010 gq_noninst_other2020 * Housing rename h0010002 hu_occ2020 rename h0010003 hu_vac2020 drop h001* save "raw/2020_blocks_other/non_nc_blocks_CLEANED.dta", replace // BRING IN NC BLOCK DATA * bring in NC block data import delimited "raw/2020_blocks.csv", clear format %20.0g geocode keep geocode *2020 merge 1:m geocode using "raw/nhgis_crosswalk_2020_2010.dta" * reweight to 2010 foreach x in pop2020 hu2020 hisp2020 nh2020 nh_white2020 nh_black2020 nh_amin2020 nh_asian2020 nh_paci2020 nh_other2020 nh_twomore2020 pop18_2020 pop18_hisp2020 pop18_nh2020 pop18_nh_white2020 pop18_nh_black2020 pop18_nh_amin2020 pop18_nh_asian2020 pop18_nh_paci2020 pop18_nh_other2020 pop18_nh_twomore2020 hu_occ2020 hu_vac2020 gq2020 gq_inst2020 gq_inst_corrad2020 gq_inst_corrjuv2020 gq_inst_nh2020 gq_inst_other2020 gq_noninst2020 gq_noninst_dorm2020 gq_noninst_mil2020 gq_noninst_other2020 { replace `x' = `x' * parea } * bring in non-NC blocks with portions in NC in 2010 append using "raw/2020_blocks_other/non_nc_blocks_CLEANED.dta" * create totals for 2010 collapse (sum) pop2020 hu2020 hisp2020 nh2020 nh_white2020 nh_black2020 nh_amin2020 nh_asian2020 nh_paci2020 nh_other2020 nh_twomore2020 pop18_2020 pop18_hisp2020 pop18_nh2020 pop18_nh_white2020 pop18_nh_black2020 pop18_nh_amin2020 pop18_nh_asian2020 pop18_nh_paci2020 pop18_nh_other2020 pop18_nh_twomore2020 hu_occ2020 hu_vac2020 gq2020 gq_inst2020 gq_inst_corrad2020 gq_inst_corrjuv2020 gq_inst_nh2020 gq_inst_other2020 gq_noninst2020 gq_noninst_dorm2020 gq_noninst_mil2020 gq_noninst_other2020, by(gjoin2010) * generate block geocode gen state = substr(gjoin2010, 2,2) gen county = substr(gjoin2010,5,3) gen tract = substr(gjoin2010,9,6) gen blockcode = substr(gjoin2010,15,19) gen block = state + county + tract + blockcode drop state county tract blockcode frame change default drop if block == "" order block save "raw/NC_blocks_2020_in_2010.dta", replace // BRINGING IN BLOCK ASSIGNMENT FILES FROM NC LEGISLATURE * NC Congressional Districts frame create baf frame change baf import delimited "raw/baf2019/C-Goodwin-A-1-TC.csv", encoding(ISO-8859-2) stringcols(1) clear frame change default frlink 1:1 block, frame(baf) frget district, from(baf) drop baf collapse (sum) *2020, by(district) rename district cd save "raw/nc_cd_2020_nhgis.dta", replace * create 2010 and 2020 nc congressional district file merge 1:1 cd using "raw/nc_cd_2010.dta" drop _merge order hu2020, before(hu_occ2020) order hu*2020, after(gq_noninst_other2020) export delimited "NC_cd_2010_2020.csv", replace save "NC_cd_2010_2020.dta", replace ***** NC House Districts use "raw/NC_blocks_2020_in_2010.dta", clear frame change baf import delimited "raw/baf2019/HB 1020 H Red Comm CSBK-25.csv", encoding(ISO-8859-2) stringcols(1) clear frame change default frlink 1:1 block, frame(baf) frget district, from(baf) drop baf collapse (sum) *2020, by(district) rename district nc_house save "raw/nc_house_2020_nhgis.dta", replace * create 2010 and 2020 nc_house file merge 1:1 nc_house using "raw/nc_house_2010.dta" order hu2020, before(hu_occ2020) order hu*2020, after(gq_noninst_other2020) export delimited "NC_house_2010_2020.csv", replace save "NC_house_2010_2020.dta", replace ****** NC Senate Districts use "raw/NC_blocks_2020_in_2010.dta", clear frame change baf import delimited "raw/baf2019/Senate Consensus Nonpartisan Map v3.csv", encoding(ISO-8859-2) stringcols(1) clear frame change default frlink 1:1 block, frame(baf) frget district, from(baf) drop baf frame drop baf // done with block assignment files for now collapse (sum) *2020, by(district) rename district nc_senate save "raw/nc_senate_2020_nhgis.dta", replace * create 2010 and 2020 nc_senate file merge 1:1 nc_senate using "raw/nc_senate_2010.dta" drop _merge order hu2020, before(hu_occ2020) order hu*2020, after(gq_noninst_other2020) export delimited "NC_senate_2010_2020.csv", replace save "NC_senate_2010_2020.csv", replace