/* Purpose: Assign counties to establishments Description: Merges county information from multiple sources (metadata files, file names, and manually collected data) to ensure all establishments have county assignments. Processes data for years 1850, 1860, 1870, and 1880 (including special schedules). */ // Set up directory paths based on username if "`1'"==""{ global CMF_pipeline "../.." } global input "${CMF_pipeline}\3_reshape\output" global interm "${CMF_pipeline}\4_county_assignment\intermediate_files" global output "${interm}\counties_assigned" // Prepare county merge file with trimmed file names use "$interm/county_to_merge", clear replace file_name = ustrtrim(file_name) tempfile county_to_merge save `county_to_merge' /* ============================================================================ PROCESS 1850, 1860, 1870 DATA Strategy: Extract county from file name, merge with master county file, then override with manually corrected counties where available ============================================================================ */ foreach y in 1850 1860 1870 { use "$input/`y'_reshaped.dta", clear gduplicates drop file_name firm_number, force cap drop file_name7 cap drop _merge // Extract county from file name (3rd component after splitting by "_") split file_name, p("_") gen gen_county = file_name3 drop file_name1-file_name6 capture confirm variable file_name7 file_name8, exact if !_rc { drop file_name7 file_name8 } // Merge with master county file fmerge m:1 file_name using `county_to_merge' drop if _merge == 2 // Fill in missing counties using file name extraction replace merged_county = gen_county if missing(merged_county) & !missing(gen_county) drop county _merge gen_county collected_county rename merged_county county // Apply manually corrected counties preserve import delimited "$interm/all_correct_county.csv", clear varn(1) replace file_name = ustrtrim(file_name) keep if year == `y' replace correct_county = lower(correct_county) tempfile correct_county save "`correct_county'" count assert `r(N)' > 0 restore merge m:1 file_name using "`correct_county'", keep(1 3) nogen assert county != correct_county | correct_county == "" replace county = correct_county if correct_county != "" // Special handling for Nevada in 1870 if `y' == 1870{ preserve import delimited "$interm/NV_correct_county.csv", clear varn(1) keep if year == `y' replace file_name = ustrtrim(file_name) tempfile NV save "`NV'" count assert `r(N)' > 0 restore merge m:1 file_name using "`NV'", keep(1 3) replace state = "NV" if _m == 3 drop _m } save "$output/`y'_county_assigned.dta", replace } /* ============================================================================ PROCESS 1880 DATA (GENERAL AND SPECIAL SCHEDULES) Strategy: First try to get counties from metadata, then fill gaps using file names, then apply manually collected and corrected counties ============================================================================ */ // Prepare 1880 county metadata file cd "${interm}" use estabs_entered_vs_metadata.dta, clear keep if year == 1880 drop file_name ren file_name_orig_e file_name replace file_name = file_name_orig_m if file_name == "" keep file_name county_m replace file_name = ustrtrim(file_name) duplicates drop file_name, force tempfile 1880_counties_from_metadata save `1880_counties_from_metadata' // Process each 1880 schedule: merge metadata counties, then fill gaps with file name counties foreach file in "SS1" "SS2" "SS3" "SS4" "SS5" "SS6" "SS7" "SS8" "SS9" "SS10" "SS11" "SS12" "general_schedule"{ use "$input/`file'_reshaped.dta", clear // Extract county from file name split file_name, p("_") gen gen_county = file_name3 drop file_name1-file_name5 capture confirm variable file_name6 file_name7 file_name8, exact if !_rc { drop file_name6 file_name7 file_name8 } // Merge with metadata counties merge m:1 file_name using `1880_counties_from_metadata' drop if _merge == 2 replace county_m = gen_county if missing(county_m) & !missing(gen_county) drop county gen_county _merge ren county_m county save "$output/1880_`file'_county_assigned.dta", replace } // Apply manually collected and corrected counties to each 1880 schedule foreach file in "SS1" "SS2" "SS3" "SS4" "SS5" "SS6" "SS7" "SS8" "SS9" "SS10" "SS11" "SS12" "general_schedule"{ // Handle naming inconsistency for general schedule local file_old "`file'" if "`file'" == "general_schedule"{ local file_old "S3" } // Merge manually collected counties if available cap import excel "$interm/1880_unmatched_counties_collected.xlsx", sheet(`file_old') firstrow clear if !_rc { di "Add unmatched collected" ren image_file_name file_name keep file_name collected_county duplicates drop file_name, force replace file_name = ustrtrim(file_name) tempfile county_sheet save `county_sheet' use "$output/1880_`file'_county_assigned.dta", clear gduplicates drop file_name firm_number, force merge m:1 file_name using `county_sheet', keep(1 3) replace county = collected_county if _merge == 3 drop collected_county _merge } else { di "no unmatched collected to add" use "$output/1880_`file'_county_assigned.dta", clear } // Apply manually corrected counties preserve import delimited "$interm/all_correct_county.csv", clear varn(1) keep if year == 1880 replace correct_county = lower(correct_county) replace file_name = ustrtrim(file_name) tempfile correct_county save "`correct_county'" restore merge m:1 file_name using "`correct_county'", keep(1 3) nogen assert county != correct_county | correct_county == "" replace county = correct_county if correct_county != "" // Apply Nevada corrections preserve import delimited "$interm/NV_correct_county.csv", clear varn(1) replace file_name = ustrtrim(file_name) keep if year == 1880 tempfile NV save "`NV'" count assert `r(N)' > 0 restore merge m:1 file_name using "`NV'", keep(1 3) replace state = "NV" if _m == 3 drop _m // Verify all establishments have required identifiers assert file_name != "" assert firm_number != . save "$output/1880_`file'_county_assigned.dta", replace }