// BRING IN 2010 TO 2020 CROSSWALK - from NHGIS - https://www.nhgis.org/geographic-crosswalks
import delimited "raw/nhgis_blk2010_blk2020_gj_v0_37.csv", clear
gen gisjoin = gjoin2010
merge m:1 gisjoin using "raw/nc_blk_2010.dta"

gen first3 = substr(gisjoin,1,3)
tab first3 if _merge !=3

/* 
first3	Freq.	Percent	Cum.
			
G13	291,085	29.15	29.15
G37	10	0.00	29.15
G45	181,837	18.21	47.35
G47	240,108	24.04	71.39
G51	285,697	28.61	100.00
			
Total	998,737	100.00

br if first3 == "G37" & _merge !=3
all have no population
*/
drop if _merge !=3

* reweight to 2020 geos
foreach x in pop2010 white2010 black2010 amin2010 asian2010 nhpi2010 other2010 mult2010 hisp2010 pop182010 gq2010 gq_inst2010 gq_inst_cfa2010 gq_inst_juv2010 gq_inst_nh2010 gq_inst_oth2010 gq_noninst2010 gq_noninst_dorm2010 gq_noninst_mil2010 gq_noninst_oth2010 hu2010 {
	replace `x' = `x' * parea
}

collapse (sum) pop2010 white2010 black2010 amin2010 asian2010 nhpi2010 other2010 mult2010 hisp2010 pop182010 gq2010 gq_inst2010 gq_inst_cfa2010 gq_inst_juv2010 gq_inst_nh2010 gq_inst_oth2010 gq_noninst2010 gq_noninst_dorm2010 gq_noninst_mil2010 gq_noninst_oth2010 hu2010, by(*2020)

// Create Geographic Identifiers
gen state = substr(gjoin2020, 2,2)
gen county = substr(gjoin2020,5,3)
gen tract = substr(gjoin2020,9,6)
gen block = substr(gjoin2020,15,19)
renvars state county tract block, postf(_2020)
order *2020

// align with fips codes from -- https://www.census.gov/geographies/reference-files/2020/demo/popest/2020-fips.html
frame create fips
frame change fips

import excel "raw/all-geocodes-v2020.xlsx",  sheet("all-geocodes-v2019") cellrange(A5:G43843) firstrow clear
keep if SummaryLevel == "050" // keep only counties

rename StateCodeFIPS state_2020
rename CountyCodeFIPS county_2020
rename AreaName coname
frame change default

frlink m:1 state_2020 county_2020, frame(fips)
frget coname, from(fips)
order coname
drop fips
frame drop fips

save "raw/nc_blk_2010_in_2020.dta", replace