/*
Purpose: Merge FIPS codes onto establishment-level data from 1850-1880 Census of Manufactures
Description: Links establishments to standardized county FIPS codes by:
             1. Loading ICPSR county crosswalk files for each census year
             2. Reconciling county name spelling differences between CMF and ICPSR data
             3. Handling special cases (county name changes, multi-county pages, etc.)
             4. Merging FIPS codes to establishment records
*/

if "`1'"==""{
	global CMF_pipeline "../.."
}


global icpsr_local "${CMF_pipeline}/4_county_assignment/input"
global input "${CMF_pipeline}/4_county_assignment/intermediate_files/counties_assigned"
global output "${CMF_pipeline}/4_county_assignment/output"


* Define program to generate state abbreviations
{
cap program drop define_stateabbrevs
program define define_stateabbrevs
	gen statename = ""
	replace statename = "AL" if statefip == 1
	replace statename = "AK" if statefip == 2
	replace statename = "AZ" if statefip == 4
	replace statename = "AR" if statefip == 5
	replace statename = "CA" if statefip == 6
	replace statename = "CO" if statefip == 8
	replace statename = "CT" if statefip == 9
	replace statename = "DE" if statefip == 10
	replace statename = "DC" if statefip == 11
	replace statename = "FL" if statefip == 12
	replace statename = "GA" if statefip == 13
	replace statename = "HI" if statefip == 15
	replace statename = "ID" if statefip == 16
	replace statename = "IL" if statefip == 17
	replace statename = "IN" if statefip == 18
	replace statename = "IA" if statefip == 19
	replace statename = "KS" if statefip == 20
	replace statename = "KY" if statefip == 21
	replace statename = "LA" if statefip == 22
	replace statename = "ME" if statefip == 23
	replace statename = "MD" if statefip == 24
	replace statename = "MA" if statefip == 25
	replace statename = "MI" if statefip == 26
	replace statename = "MN" if statefip == 27
	replace statename = "MS" if statefip == 28
	replace statename = "MO" if statefip == 29
	replace statename = "MT" if statefip == 30
	replace statename = "NE" if statefip == 31
	replace statename = "NV" if statefip == 32
	replace statename = "NH" if statefip == 33
	replace statename = "NJ" if statefip == 34
	replace statename = "NM" if statefip == 35
	replace statename = "NY" if statefip == 36
	replace statename = "NC" if statefip == 37
	replace statename = "ND" if statefip == 38
	replace statename = "OH" if statefip == 39
	replace statename = "OK" if statefip == 40
	replace statename = "OR" if statefip == 41
	replace statename = "PA" if statefip == 42
	replace statename = "RI" if statefip == 44
	replace statename = "SC" if statefip == 45
	replace statename = "SD" if statefip == 46
	replace statename = "TN" if statefip == 47
	replace statename = "TX" if statefip == 48
	replace statename = "UT" if statefip == 49
	replace statename = "VT" if statefip == 50
	replace statename = "VA" if statefip == 51
	replace statename = "WA" if statefip == 53
	replace statename = "WV" if statefip == 54
	replace statename = "WI" if statefip == 55
	replace statename = "WY" if statefip == 56
end
}

/* ============================================================================
   PROCESS 1850 DATA
   Strategy: Load ICPSR crosswalk, reconcile naming differences, handle 
            special cases, merge FIPS to establishments
   ============================================================================ */
* Prepare county crosswalks
use "$icpsr_local/02896-0007-Data.dta", clear
keep if level==1 //keeping only county-level data
replace fips=11001 if fips==11000
keep name fips
gen statefip = floor(fips/1000)
define_stateabbrevs
rename statename state
gen county = lower(name)
keep state county fips
order state county fips
* Adjust names to match establishment level data
	replace county = "calhoun-benton" 		if county == "calhoun/benton" & state == "AL"
	replace county = "morgan-cotaco" 		if county == "morgan/cotaco" & state == "AL"
	replace county = "mackinac-michilim"	if county == "mackinac/michilim" & state == "MI"
	replace county = "cass-van buren"		if county == "cass/van buren" & state == "MO"
	replace county = "henry-rives"			if county == "henry/rices" & state == "MO"
	replace county = "cass-davis"			if county == "cass/davis" & state == "TX"
	replace county = "arlington-alexand"	if county == "arlington/alexand" & state == "VA"
	replace state  = "TX"					if county == "navarro-ellis-tar"
	replace county = "henry-rives"			if county == "henry/rives" & state == "MO"
	replace county = "winston-hancock"		if county == "winston/hancock" & state == "AL"
	replace state = "VA" if state == "WV"
tempfile xwalk1850
save `xwalk1850', replace


use "${input}/1850_county_assigned.dta", clear
cap drop fips correct_county

replace county = lower(county)
order state county
* Adjust names to match ICPSR data
	replace county = "russell" if county == "rusell" & state == "AL"
	replace county = "benton" if county == "bento" & state == "MO"
	replace county = "cass-van buren" if county == "cass" & state == "MO"
	replace county = "franklin" if county == "frankin" & state == "VA"
	replace county = "winston-hancock" if county == "hancock" & state=="AL"
	replace county = "henry-rives"		if county=="henry" & state=="MO"
	replace county = "cumberland"		if county=="cumberl&" & state=="PA"
	replace county = "sussex"			if county=="de" & state=="DE"
	replace state = "WA"				if county=="lewis" & state=="OR"
	replace county = "carter"			if county=="not in haines" & state=="TN"
	replace county = "anderson"			if county=="anderson-wilson" & state=="TN"
	replace county = "robertson"		if file_name=="TN_5_illegible_00133_r_L.jpg"
	replace county = "wilson"			if file_name=="TN_5_illegible_00181_l_L.jpg"
	replace county = "mcminn"			if file_name=="TN_5_illegible_00120_r_L.jpg"
	replace county = "fayette"			if file_name=="TN_5_illegible_00046_r_L.jpg"
	replace county = "rutherford"		if file_name=="TN_5_illegible_00135_l_L.jpg"
	replace county = "white"			if file_name=="TN_5_illegible_00172_l_L.jpg"
	replace county = "gibson"			if file_name=="TN_5_illegible_00050_l_L.jpg"
	replace county = "mcnairy"			if file_name=="TN_5_illegible_00123_r_L.jpg"
	replace county = "van buren"		if file_name=="TN_5_illegible_00162_r_L.jpg"
	replace county = "dyer"				if file_name=="TN_5_illegible_00047_r_L.jpg"
	replace county = "wilson"			if file_name=="TN_5_illegible_00181_r_L.jpg"
	replace county = "morgan"			if file_name=="TN_5_illegible_00124_r_L.jpg"
	replace county = "maury"			if file_name=="TN_5_illegible_00119_r_L.jpg"
	replace county = "houghton"			if county=="the lake superior" & state=="MI"
	replace state = "VA" if state == "WV"
	replace state = "WA" if state=="OR" & county=="clark"
	
	*add counties to inconsistenly named filenames
	replace county = "stark" if file_name=="IL_1850__000409_2half_L.jpg "
	replace county = "shelby" if file_name=="IL_1850__000408_2half_L.jpg "


	
* Assign counties to specific establishments when there were multiple
* counties listed on a single image (adjustments from Ali)
* Barnstable - Middlesex, Nantucket - Worceter, and Anderson - Wilson: these just hadn't had a county imputed yet
	replace county = "barnstable" 	if file_name == "MA_5_barnstable - middlesex_00021_r_L.jpg"
	replace county = "hampshire" 	if file_name == "MA_5_barnstable - middlesex_00207_l_L.jpg" | file_name == "MA_5_barnstable - middlesex_00207_r_L.jpg"
	replace county = "norfolk" 		if file_name == "MA_5_nantucket - worceter_00045_r_L.jpg"
	replace county = "plymouth" 	if file_name == "MA_5_nantucket - worceter_00072_r_L.jpg"	
	replace county = "anderson" 	if file_name == "TN_5_anderson - wilson_00004_r_L.jpg"
	*Navarro-Ellis-Tar: this one is a bit arbitrary since in 1849, Navarro split into three, one keeping its original name, one being called Ellis, and the last being called Tarrant.
	*In the image, this page is listed as Ellis and Tarrant, so I put the firm name with "Tarrant" in it in Tarrant, and the other person's establishments in Ellis
	replace county = "tarrant" 		if file_name == "TX_5_navarro-ellis-tar_00031_l_L.jpg" & firm_name == "E H Tarrant"
	replace county = "ellis" 		if file_name == "TX_5_navarro-ellis-tar_00031_l_L.jpg" & firm_name == "M T Johnson" | firm_name == "M.T. Johnson"
	replace county = "providence" if inlist(file_name,  "RI_1850__0005_S.tif", "RI_1850__0006_S.tif", "RI_1850__0007_S.tif")
	replace county = "wyandot" if county == "wyander" & state == "OH"
	
merge m:1 state county using `xwalk1850'
//, keep(1 3) //some RI file_name have no county
drop if _merge ==2
*assert fips != . if county != ""
*assert county != ""
drop _merge
sort state county file_name firm_number
order fips state county file_name firm_number firm_name /*industry_raw1*/

tostring fips, replace format(%07.1f)  force
replace fips = subinstr(fips, ".0", "", .)
replace fips = "11001" if fips == "11001.2" | fips == "11001.1" | fips == "11001.3"

save "${output}/1850_county_fips_assigned.dta", replace

/* ============================================================================
   PROCESS 1860 DATA
   Strategy: Load ICPSR crosswalk, reconcile naming differences, handle 
            special cases, merge FIPS to establishments
   ============================================================================ */


use "${icpsr_local}/02896-0009-Data.dta", clear
keep if level==1 //keeping only county-level data
replace fips=11001 if fips==11000
keep name fips
gen statefip = floor(fips/1000)
define_stateabbrevs
rename statename state
gen county = lower(name)
keep state county fips
order state county fips
* Adjust names to match establishment level data
	replace county = "calhoun-benton" 	if county == "calhoun/benton" & state == "AL"
	* bend IL vs bond IL?
	replace county = "davis-geary"		if county == "davis/geary" & state == "KS"
	replace county = "miami/lykins"		if county == "miami/lykins" & state == "KS"
	replace county = "sagadahoc"		if county == "sagadahoc" & state == "ME"
	replace county = "mackinac-michilim"	if county == "mackinac/michilim" & state == "MI"
	* St Louis vs St. Louis Lake?
	replace county = "cass-van buren"	if county == "cass/van buren" & state == "MO"
	replace county = "henry-rives"		if county == "henry/rives" & state == "MO"
	replace county = "johnson-johnston" if county == "johnson/johnston" & state == "NE"
	replace county = "cass-davis"		if county == "cass/davis" & state == "TX"
	replace county = "bayfield-la point"	if county == "bayfield/la point" & state == "WI"
	replace county = "morgan-cotaco"	if county == "morgan/cotaco" & state == "AL"
	replace county = "miami-lykins"	if county == "miami/lykins" & state == "KS"
	replace county = "vernon-bad ax"	if county == "vernon/bad ax" & state == "WI"
	replace state = "VA" if state == "WV"
	replace county = "arlington-alexand" if county == "arlington/alexand" & state == "VA"

tempfile xwalk1860
save `xwalk1860', replace

use "${input}/1860_county_assigned.dta", clear
cap drop fips 
cap drop county_detailed correct_county
replace county = lower(county)
order state county
* Reconciling spelling differences--match the ICPSR files
	replace county="autauga" 	if county=="autaga" & state=="AL"
	replace county="yamhill"	if county=="yam hill" & state=="OR"
	replace county="morgan-cotaco"	if county=="morgan" & state=="AL"
	replace county = "henry-rives" if county == "henry" & state=="MO"
	replace county = "cumberland" if county == "cumberl&" & state=="PA"
	replace county = "bond"		if county == "bend" & state =="IL"
	replace county = "boone"	if county == "booner" & state == "IL"
	replace county = "de witt"	if county == "dw witt" & state == "IL"
	replace county = "sagadahoc" if county == "sagadohoc" & state == "ME"
	replace county = "new york" if county == "new york city" & state == "NY"
	replace state = "CO"		if county == "arapahoe" & state == "KS"
	replace county = "nemaha"	if county == "pimaka" & state == "NE"
	replace county = "indiana"	if county == "none" & file_name == "PA_6_none_10154_L.jpg"
	replace county = "schuylkill"	if county == "none" & file_name == "PA_6_none_10689_L.jpg"
	replace state = "VA" if state == "WV"
	replace county="new york" 	if file_name=="NY_6_f2_00201_l_L.jpg"
	replace county="onondaga" 	if file_name=="NY_6_f2_00438_l_L.jpg"
	replace county="steuben" 	if file_name=="NY_6_f3_00116_l_L.jpg"
	replace county="philadelphia" if file_name=="PA_6_none_11375_L.jpg" | file_name=="PA_6_none_10139_L.jpg" | file_name=="PA_6_none_11353_L.jpg" | file_name=="PA_6_none_10546_L.jpg" | file_name=="PA_6_none_10758_L.jpg" | file_name=="PA_6_none_11304_L.jpg"
	replace county = "dodge" if county == "dodge-platte" & state == "NE"
* Assign counties to specific establishments when there were multiple
* counties listed on a single image (adjustments from Ali)
	*St. Louis, Lake
	replace county = "st louis" if file_name == "MN_6_st louis, lake_00062_L.jpg" & firm_number <= 4
	replace county = "lake" 	if file_name == "MN_6_st louis, lake_00062_L.jpg" & (firm_number == 5 | firm_number == 6)
	*Clark, Cowlitz, Skamania
	replace county = "clark" 	if file_name == "WA_6_clark, cowlitz, skamania_00003_L.jpg" & firm_number <= 6
	replace county = "cowlitz" 	if file_name == "WA_6_clark, cowlitz, skamania_00003_L.jpg" & (firm_number == 7 | firm_number == 8) 
	replace county = "skamania" if file_name == "WA_6_clark, cowlitz, skamania_00003_L.jpg" & firm_number == 9
	*Note: If you look in the metadata/early-stage data, there's another image with multiple counties like this but it's actually a duplicate of this image and we've since taken it out
	*Bayfield-La Point (there's both a county name change and multiple counties on the actual page)
	* We will wait to add this name until a later date.
	gen county_detailed = "bayfield" if file_name == "WI_6_bayfield-la point_00206_r_L.jpg" & firm_number <= 4	//technically this was La Pointe County at the time so I could change this but idk if I need to
	replace county = "ashland" 	if file_name == "WI_6_bayfield-la point_00206_r_L.jpg" & firm_number >= 5 & firm_number <= 10
	replace county = "douglas" 	if file_name == "WI_6_bayfield-la point_00206_r_L.jpg" & firm_number >= 11
	replace county = "providence" if file_name == "RI_1860__0179_S.tif"
	**round6 data
	replace county = "litchfield" if file_name == "CT_6__00115_l_L.jpg"
	replace county = "douglas" if file_name == "NE_6_wood_00214_r_A.jpg"
	replace county = "shawano" if file_name == "WI_6_shewano_00285_r_L.jpg"
	replace county = "arlington-alexand" if county == "arlington/alexandria" & state == "VA"
	replace county = "greenbrier" if county == "greenbier" & state == "VA"
	
	*Note: WI_6_bayfield-la point: parts of La Pointe County became Douglas county in 1854 and Ashland county in 1860. The remaining area of La Pointe County was renamed Bayfield county in 1866 (see "La Pointe County, Wisconsin" on Wikipedia)
merge m:1 state county using `xwalk1860', keep(1 3)
br if fips == .
sort state county file_name firm_number
order fips state county file_name firm_number firm_name

tostring fips, replace format(%07.1f)  force
replace fips = subinstr(fips, ".0", "", .)
replace fips = "11001" if fips == "11001.2" | fips == "11001.1" | fips == "11001.3"

*save "estabs1860.dta", replace
save "${output}/1860_county_fips_assigned.dta", replace

/* ============================================================================
   PROCESS 1870 DATA
   Strategy: Load ICPSR crosswalk, reconcile naming differences, handle 
            special cases, merge FIPS to establishments
   ============================================================================ */

use "${icpsr_local}/02896-0011-Data.dta", clear

keep if level==1 //keeping only county-level data
replace fips=11001 if fips==11000
keep name fips
gen statefip = floor(fips/1000)
define_stateabbrevs
rename statename state
gen county = lower(name)
keep state county fips
order state county fips
* Adjust names to match establishment level data
	replace county = "hernando-benton" 	if county == "hernando/benton" & state == "FL"
	replace county = "orange-mosquito"		if county == "orange/mosquito" & state == "FL"
	replace county = "mackinac-michilim"	if county == "mackinac/michilim" & state == "MI"
	replace county = "wilkin-toombs"		if county == "wilkin/toombs" & state == "MN"
	replace county = "johnson-johnston"		if county == "johnson/johnston" & state == "NE"
	replace county = "cass-davis"			if county == "cass/davis" & state == "TX"
	replace county="morgan-cotaco"	if county == "morgan/cotaco" & state == "AL"
	replace county="calhoun-benton" if county == "calhoun/benton" & state == "AL"
	replace county="chilton-baker"	if county == "chilton/baker" & state == "AL"
	replace county= "bradford-new rive" if county == "bradford/new rive" & state=="FL"
	replace county= "cherokee-mcghee"	if county == "cherokee/mcghee" & state=="KS"
	replace county= "davis-geary"		if county == "davis/geary" & state=="KS"
	replace county= "miami-lykins"		if county == "miami/lykins" & state=="KS"
	replace county= "neosho-dorn"		if county == "neosho/dorn" & state=="KS"
	replace county= "cass-van buren"	if county == "cass/van buren" & state =="MO"
	replace county= "henry-rives"		if county == "henry/rives" & state=="MO"
	replace county= "lamar-sanford"		if county == "lamar/sanford" & state=="AL"
	replace county= "vernon-bad ax"		if county == "vernon/bad ax" & state=="WI"
	replace county= "bayfield-la point"	if county == "bayfield/la point" & state=="WI"
	replace county= "arlington-alexand" if county == "arlington/alexand" & state=="VA"
	replace county= "knox-leau qui co" if county == "knox/l'eau qui co" & state=="NE"
	
tempfile xwalk1870
save `xwalk1870', replace


use "${input}/1870_county_assigned.dta", clear
cap drop fips
cap drop correct_county county_detailed

replace county = lower(county)
* remove some trailing spaces
replace county = trim(county)
order state county
* Reconciling spelling differences--match the ICPSR files
	replace county="autauga" 		if county=="autaga" & state=="AL"
	replace county="yamhill"		if county=="yam hill" & state=="OR"
	replace county="san francisco"	if county=="san franciscof1" & state=="CA"
	replace county="cerro gordo"	if county=="cerrogordo" & state=="IA"
	replace county="de kalb"		if county=="dekalb" & state=="IN"
	replace county="de kalb"		if county=="dekalb" & state=="MO"
	replace county="ste genevieve"	if county=="st genevieve" & state=="MO"
	replace county="wasco"			if county=="waso" & state=="OR"
	replace county="nacogdoches"	if county=="nachodoches" & state=="TX"
	replace county="upshur"			if county=="upshure" & state=="TX"
	replace county="van zandt"		if county=="van zadt" & state=="TX"
	replace county="clarke"			if county=="clark" & state=="WA"
	replace county="monongalia"		if county=="monogolia" & state=="WV"
	replace county="humboldt"		if county=="hamboldt" & state=="CA"
	replace county="morgan-cotaco" 	if county=="morgan-cortaco" & state=="AL"
	replace county="cherokee-mcghee" if county=="cherokee" & state=="KS"
	replace county="davis-geary"	if county=="davis" & state=="KS"
	replace county="miami-lykins"	if county=="miami" & state=="KS"
	replace county="neosho-dorn"	if county=="neosho" & state=="KS"
	replace county="cass-van buren"	if county=="cass" & state=="MO"
	replace county="henry-rives"	if county=="henry" & state=="MO"
	replace county="monongalia"		if county=="monongolia" & state=="WV"
	replace county="calhoun-benton" if county=="calhoun" & state=="AL"
	replace county="chilton-baker"	if county=="baker" & state=="AL"
	replace county="vernon-bad ax"	if county=="vernon" & state=="WI"
	replace county="morgan-cotaco"	if county=="morgan" & state=="AL"
	replace county="lamar-sanford"	if county=="sanford" & state=="AL"
	replace county="bayfield-la point"	if county=="bayfield" & state=="WI"
	replace state="NV" 				if county=="rio virgin" & state=="UT"
	replace county = "marin"	if county == "mann" & state=="CA"
	replace county = "lake"		if county == "sake" & state=="CA"
	replace county = "arlington-alexand"	if county=="alexandria" & state=="VA"
	replace county = "dinwiddie"	if county=="petersburg" & state=="VA"
	replace county = "somerset"		if county=="somerest" & state=="NJ"
	replace state = "NY" if county == "kings" & file_name == "NY_7_kings_00492_L.jpg"
	replace state = "MS" if county == "tallahatchie" & file_name == "MS_7_tallahatchie_00178_r_L.jpg"
	replace county = "nevada" 		if county=="ca" & state=="CA"
	replace county = "otoe"			if county=="olai" & state=="NE"
	replace county = "butler"		if county=="bates" & state=="NE"
	replace county = "lake"			if county=="unknown" & state=="CA" & file_name == "CA_7_unknown_00022_L.jpg"
	replace county = "lake"			if county=="unknown" & state=="CA" & file_name == "CA_7_unknown_00107_L.jpg"
	replace county = "lake"			if county=="not specified" & state=="CA"
	replace county = "lake"			if county == "sake" & state=="CA"
	replace county = "monterey"		if county == "illegible (covered by additional note)" & state=="CA"
	replace county = "harrison"		if county == "county cut off" & state=="IA"
	replace county = "lehigh" if county == "l.jpgehigh"
	replace county = "mackinac-michilim" if county == "mackinac" & state == "MI"
* Specific adjustments from Patrick
	*other corrections (these need to go in the other file, the merging file)
	*there are no other establishments in kern county in 1870. The image appears to say kern county. This is supported by the post office, which resembles Havilah--the county seat in kern county. The county crosswalk should handle the rest. image CA_7_f2_00083
	replace county="kern" if state=="CA" & county=="f2" & firm_name=="Kim Rails Mining Co"
	*image CA_7_f2_00219
	replace county="san joaquin" if state=="CA" & county=="f2"
	*image CA_7_illegible_00046 and image CA_7_illegible_00284 have their counties recorded as illegible. Using the list of names of counties in California and comparing to other images, I identified the county names as calaveras and tuolumne respectively.
	replace county="tuolumne" if file_name=="CA_7_illegible _00284_L.jpg"
	replace county="calaveras" if file_name=="CA_7_illegible _00046_L.jpg"
	*there is no saete county CA. I think that what is transcribed as saete is actually sacto, and is an abbreviation of sacramento
	replace county="sacramento" if state=="CA" & county=="saete"
	*there is no sage county CA (thought there are a couple towns called sage).  The post office for the sage county establishments is "Independence". I looked at other counties with a post office by this name and Inyo county came up. I checked images, and Sage and Inyo look similar in cursive. I think sage is Inyo
	replace county="inyo" if state=="CA" & county=="sage"
	*several images list the county as unknown. One such image is CA_7_unknown_00286. Although there is nothing in the county name line of the image, the location is recorded as Cache Creek township. Image CA_7_unknown_00285 is also Cache Creek township and is listed as Yolo county. 
	replace county="yolo" if file_name=="CA_7_unknown_00286_L.jpg"
	
* Assign counties to specific establishments when there were multiple
* counties listed on a single image (adjustments from Ali)	
	*Bradford-New River (Bradford is the name of this county starting in 1858)/Hernando-Benton (same)/Orange-Mosquito (same)
	gen county_detailed = "bradford" if file_name == "FL_7_bradford-new rive_00010_r_L.jpg"
	replace county = "bradford-new rive" if file_name == "FL_7_bradford-new rive_00010_r_L.jpg"
	replace county_detailed = "hernando" if file_name == "FL_7_hernando-benton_00029_r_L.jpg"
	replace county_detailed = "orange" if file_name == "FL_7_orange-mosquito_00059_r_L.jpg"
	*These just didn't have a county entered for some reason (it's pretty clear in the image)
	replace county = "dubois" if file_name == "IN_7_Adams-Fulton_00526_S.jpg"
	replace county_detailed = "wilkin" if file_name == "MN_7_wilkin-toombs_00476_L.jpg"
	*NE_6_johnson-johnston_00220_l_A.jpg is a duplicate of NE_6_johnson-johnston_00122_l_A.jpg (which is listed as NE_6_Nemaha_00122_1half in the images, making it hard to find), so we should make sure to take care of that
	replace county_detailed = "johnson" if file_name == "NE_7_johnson-johnston_00156_r_A.jpg"
	*Cass-Davis: Originally it was called Cass, then its name changed to Davis, and now it's called Cass again
	replace county_detailed = "cass" if inlist(file_name, "TX_7_cass-davis_89_.jpg", "TX_7_cass-davis_90_.jpg", "TX_7_cass-davis_91_.jpg", "TX_7_cass-davis_92_.jpg")
	replace county = "st louis" if county == "stlouis" & state == "MO"
	replace county = "st clair" if county == "stclair" & state == "MO"
	replace county = "knox-leau qui co" if county == "knox" & state == "NE"
	replace county = "cape girardeau" if county == "capegirardeau"
	replace county = "adams" if county == "adams-bond" & state == "IL"
	replace county = "elko" if county == "elkoco" & state == "NV"
	replace county = "esmeralda" if county == "esmeraldaco" & state == "NV"
	replace county = "humboldt" if county == "humboldtco" & state == "NV"
	replace county = "lander" if county == "landerco" & state == "NV"
	replace county = "lyon" if county == "lyonco" & state == "NV"
	replace county = "lincoln" if county == "lincolnco" & state == "NV"
	replace county = "douglas" if county == "douglasco" & state == "NV"
	replace county = "nye" if county == "nyeco" & state == "NV"
	replace county = "ormsby" if county == "ormsbyco" & state == "NV"
	replace county = "storey" if county == "storeyco" & state == "NV"
	replace county = "washoe" if county == "washoeco" & state == "NV"
	replace county = "white pine" if county == "whitepineco" & state == "NV"
	replace county = "" if county == "nocounty" & (state == "NV" | state == "RI")
	merge m:1 state county using `xwalk1870', keep(1 3)
sort state county file_name firm_number
order fips state county file_name firm_number firm_name industry_raw
tostring fips, replace format(%07.1f)  force
replace fips = subinstr(fips, ".0", "", .)
replace fips = "11001" if fips == "11001.2" | fips == "11001.1" | fips == "11001.3"

save "${output}/1870_county_fips_assigned.dta", replace

/* ============================================================================
   PROCESS 1880 DATA
   Strategy: Load ICPSR crosswalk, reconcile naming differences, handle 
            special cases, merge FIPS to establishments
   ============================================================================ */
   
use "${icpsr_local}/02896-0015-Data.dta", clear
drop if fips == .
keep if level==1 //keeping only county-level data
replace fips=11001 if fips==11000
keep name fips
gen statefip = floor(fips/1000)
define_stateabbrevs
rename statename state
gen county = lower(name)
keep state county fips
order state county fips

tempfile xwalk1880
save `xwalk1880', replace

** Define program to clean county name manually
{
cap program drop define_cleancountyname
program define define_cleancountyname
	*case1 missing state
		*infer from file_name
	replace state = substr(file_name,1,2) if state ==""
	*case2 ends with blank
		* clackamas &  st john the bapti  
	replace county = substr(county,1,length(county) - 1) if regexm(county, " $")
	replace county = substr(county,2,length(county) - 1) if regexm(county, "^ ")
	*case3: multiple county in ICSPR
	replace county = "calhoun/benton" if state =="AL" & county == "calhoun"
	replace county = "chilton/baker" if state =="AL" & county == "chilton"
	replace county = "lamar/sanford" if state =="AL" & county == "lamar"
	replace county = "morgan/cotaco" if state =="AL" & county == "morgan"
	replace county = "dorsey/cleveland" if state =="AR" & county == "dorsey"
	replace county = "bradford/new rive" if state =="FL" & county == "bradford"
	replace county = "brevard/st lucie" if state =="FL" & county == "brevard"
	replace county = "orange/mosquito" if state =="FL" & county == "orange"
	replace county = "bartow/cass" if state =="GA" & county == "bartow"
	replace county = "lyon/buncombe" if state =="IA" & county == "lyon"
	replace county = "barbour/barber" if state =="KS" & county == "barbour"
	replace county = "cherokee/mcghee" if state =="KS" & county == "cherokee"
	replace county = "davis/geary" if state =="KS" & county == "davis"
	replace county = "miami/lykins" if state =="KS" & county == "miami"
	replace county = "neosho/dorn" if state =="KS" & county == "neosho"
	replace county = "mackinac/michilim" if state =="MI" & county == "mackinac"
	replace county = "kittson/pembina" if state =="MN" & county == "kittson"
	replace county = "wilkin/toombs" if state =="MN" & county == "wilkin"
	replace county = "cass/van buren" if state =="MO" & county == "cass"
	replace county = "henry/rives" if state =="MO" & county == "henry"
	replace county = "sumner/webster" if state =="MS" & county == "sumner"
	replace county = "blackbird/thurston" if state =="NE" & county == "blackbird"
	replace county = "johnson/johnston" if state =="NE" & county == "johnson"
	replace county = "knox/l'eau qui co" if state =="NE" & county == "knox"
	replace county = "cass/davis" if state =="TX" & county == "cass"
	replace county = "stephens/buchanan" if state =="TX" & county == "stephens"
	replace county = "arlington/alexand" if state =="VA" & county == "alexandria"
	replace county = "barron/dallas" if state =="WI" & county == "barron"
	replace county = "bayfield/la point" if state =="WI" & county == "bayfield"
	*case4: CMF/DDD one character typo
	replace county = "st francis" if state =="AR" & county == "st. francis"
	replace county = "sonoma" if state =="CA" & county == "somona"
	replace county = "tuolumne" if state =="CA" & county == "tuonumne"
	replace county = "o brien" if state =="IA" & county == "o'brien"
	replace county = "putnam" if state =="IN" & county == "putman"
	* MO_8_dakalb_00196_S.jpg looks like "dekalb". Should be typo
	replace county = "de kalb" if state =="MO" & county == "dekalb"
	replace county = "de kalb" if state =="MO" & county == "dakalb"
	replace county = "fillmore" if state =="NE" & county == "filmore"
	replace county = "cheshire" if state =="NH" & county == "chesire"
	replace county = "yamhill" if state =="OR" & county == "yam hill"
	replace county = "butler" if state =="PA" & county == "bulter"
	replace county = "phillips" if state =="AR" & county == "philips"
		*rutland file_name is VT but state was VT. 
	replace state = "VT" if state =="VA" & county == "rutland" 
	*case5: f2 & f3 *check manuscript 
	replace county = "pawnee" if file_name == "KS_8_f2_00338_L.jpg"
	replace county = "hampden" if file_name == "MA_1880_f2_000009_L.jpg"
	replace county = "vernon/bad ax" if file_name == "WI_1880_f3_000559_L.jpg"
	*Missing images:
		*Both establishment has other records in MA in SS5 & SS7
		*Assume not different establishment with the same name.
		*Assume not different establishment of one firm (in different county).
		*Glover Warren
	replace county = "worcester" if file_name == "MA_1880_f3_000534_L.jpg"
	replace county = "worcester" if file_name == "MA_1880_f3_000535_L.jpg.tif"
		*Hollingsworth John
	replace county = "plymouth" if file_name == "MA_1880_f3_000009_L.jpg"
	*case6: change character
	replace county = "king and queen" if county == "king & queen"
	*case7: multiple county in one manuscript
		*AR_8_independence, lonoke, madison, marion, montgomery, nevada, perry_00224_L.jpg
	replace county = "independence" if file_name == ///
		"AR_8_independence, lonoke, madison, marion, montgomery, nevada, perry_00224_L.jpg" & ///
		firm_name == "T.F. McPherson"
	replace county = "madison" if file_name == ///
		"AR_8_independence, lonoke, madison, marion, montgomery, nevada, perry_00224_L.jpg" & ///
		firm_name == "Mallonee & Co"
	replace county = "montgomery" if file_name == /// 
		"AR_8_independence, lonoke, madison, marion, montgomery, nevada, perry_00224_L.jpg" & ///
		firm_name == "J.H. Demby"
	replace county = "montgomery" if file_name == /// 
		"AR_8_independence, lonoke, madison, marion, montgomery, nevada, perry_00224_L.jpg" & ///
		firm_name == "Spurlock & Co"			
	*AR_8_pulaski, pike, randolph, washington, yell_00405_L.jpg
	replace county = "randolph" if file_name == /// 
		"AR_8_pulaski, pike, randolph, washington, yell_00405_L.jpg" & ///
		firm_name == "S.W. James & Sons"		
	replace county = "washington" if file_name == /// 
		"AR_8_pulaski, pike, randolph, washington, yell_00405_L.jpg" & ///
		firm_name == "J.H. Cats"		
	replace county = "yell" if file_name == /// 
		"AR_8_pulaski, pike, randolph, washington, yell_00405_L.jpg" & ///
		firm_name == "Toomer & Cotton"	
	*AR_8_benton, boone, crawford, dorsey_00042_L.jpg
	replace county = "benton" if file_name == /// 
		"AR_8_benton, boone, crawford, dorsey_00042_L.jpg" & ///
		firm_name == "Geo W Harris"
	replace county = "benton" if file_name == /// 
		"AR_8_benton, boone, crawford, dorsey_00042_L.jpg" & ///
		firm_name == "Hickman & Bates"
	replace county = "benton" if file_name == /// 
		"AR_8_benton, boone, crawford, dorsey_00042_L.jpg" & ///
		firm_name == "Nail A"
	replace county = "boone" if file_name == /// 
		"AR_8_benton, boone, crawford, dorsey_00042_L.jpg" & ///
		firm_name == "Wiliams & Oderholt"
	replace county = "crawford" if file_name == /// 
		"AR_8_benton, boone, crawford, dorsey_00042_L.jpg" & ///
		firm_name == "Simcs & Bro"
	replace county = "faulkner" if file_name == /// 
		"AR_8_benton, boone, crawford, dorsey_00042_L.jpg" & ///
		firm_name == "Frandenthal & Allen"
	drop if county == "autauga - winston"
	replace county = "marion" if inlist(file_name, "IN_1880_Huntington - Marion_000614_L.jpg", ///
	"IN_1880_Huntington - Marion_000615_L.jpg", "IN_1880_Huntington - Marion_000616_L.jpg", ///
	"IN_1880_Huntington - Marion_000617_L.jpg")
	replace county = "marshall" if file_name == "IN_1880_Huntington - Marion_000659_L.jpg"
	
	*case8: wrong state from file_name
	replace state = "CT" if inlist(file_name, "CT_8_new london_00322_r_L.jpg", "CT_8_hartford_00095_l_L.jpg")
	replace state = "MI" if regexm(file_name, "^MI_8_(oakland|newaygo)")
	replace state = "AL" if regexm(file_name,"^AL_8_(elmore|limestone)")
	*case9: others
	replace state = "NV" if state == "NE" & inlist(county, "washoeco", "storeyco", "lyonco", "lincolnco", "eurekaco", "esmeraldaco", "douglasco", "unknownco")
	*stanley, ND is the county seat of mountraille. stanley,ND is not stanley, SD
	replace county = "mountraille" if state =="ND" & county == "stanley"
	replace county = "elko" if county == "elkoco" & state == "NV"
	replace county = "esmeralda" if county == "esmeraldaco" & state == "NV"
	replace county = "humboldt" if county == "humboldtco" & state == "NV"
	replace county = "lander" if county == "landerco" & state == "NV"
	replace county = "lyon" if county == "lyonco" & state == "NV"
	replace county = "lincoln" if county == "lincolnco" & state == "NV"
	replace county = "douglas" if county == "douglasco" & state == "NV"
	replace county = "nye" if county == "nyeco" & state == "NV"
	replace county = "ormsby" if county == "ormsbyco" & state == "NV"
	replace county = "storey" if county == "storeyco" & state == "NV"
	replace county = "washoe" if county == "washoeco" & state == "NV"
	replace county = "white pine" if county == "whitepineco" & state == "NV"
		replace county = "washoe" if county == "washoeco" & state == "NV"
	replace county = "eureka" if county == "eurekaco" & state == "NV"
	replace county = "white pine" if county == "whitepineco" & state == "NV"
	replace county = "rook" if county == "rookco" & state == "NV"
	replace county = "ripley" if file_name == "IN_1880_Randolph - Vigo_000541_L.jpg"

	replace county = "columbia" if file_name == "FL_1880_Alachua - Washington_000028_L.jpg"
	replace county = "lafayette" if file_name == "FL_1880_Alachua - Washington_000082_L.jpg"
	replace county = "madison" if file_name == "FL_1880_Alachua - Washington_000104_L.jpg"
	replace county = "marion" if file_name == "FL_1880_Alachua - Washington_000114_L.jpg"
	replace county = "marion" if file_name == "FL_1880_Alachua - Washington_000115_L.jpg"
	replace county = "orange" if file_name == "FL_1880_Alachua - Washington_000126_L.jpg"
	replace county = "putnam" if file_name == "FL_1880_Alachua - Washington_000135_L.jpg"
	replace county = "sumter" if file_name == "FL_1880_Alachua - Washington_000142_L.jpg"
	replace county = "walton" if file_name == "FL_1880_Alachua - Washington_000161_L.jpg"

	replace county = "miami" if file_name == "OH_8_miami, montgomery, ottawa, pike, ross, stark_00010_L.jpg"
	replace county = "morrow" if file_name == "OH_8_tuscarawas, wayne, soiota_00283_L.jpg"
	replace county = "laurens" if file_name == "SC_8_laurens, oconee, pickens_001_A.jpg"
	replace county = "marlboro" if file_name == "SC_8_malboro_032_A.jpg"
	replace county = "tuscaloosa" if county == "tuskaloosa" & state == "AL" 
	replace county = "orange/mosquito" if county == "orange" & state == "FL"
	replace county = "roop" if county == "rook" & state == "NV" 

end
}

/* ============================================================================
   PROCESS 1880 Special Schedules DATA
   ============================================================================ */
tempfile nomatch

foreach file in "general_schedule" "SS1" "SS2" "SS3" "SS4" "SS5" "SS6" "SS7" "SS8" "SS9" "SS10" "SS11" "SS12"{
	di "Merge `file' with fips code"
	use "${input}/1880_`file'_county_assigned" , clear
	replace county = lower(county)
	define_cleancountyname
	merge m:1 state county using `xwalk1880', keep(1 3) nogen
	
	order state county fips
	sort fips
	
	replace county = subinstr(county,"/","-",.)
	tostring fips, replace format(%07.1f)  force
	replace fips = subinstr(fips, ".0", "", .)
	replace fips = "11001" if fips == "11001.2" | fips == "11001.1" | fips == "11001.3"
	
	save "${output}/1880_`file'_county_fips_assigned.dta", replace
}