/* Purpose: Apply string cleaning codes to the manually cleaned and adjusted CMF microdata - Standardizes case, whitespace, and merges with crosswalk files for industry, materials, and product categories - Concatenates multiple clean fields and attributes into single summary strings - Optimizes storage and drops unnecessary variables */ clear all if "`1'"==""{ global CMF_pipeline "../.." } global appended_files "${CMF_pipeline}/1_manual_checking/output" global string_cleaning "${CMF_pipeline}/2_string_cleaning" global string_output "${string_cleaning}/output" * cleaning product_name /* Loop through decennial datasets 1850-1870 * */ foreach y in 1850 1860 1870 { di "\nProcessing CMF_`y' dataset..." // Load the manually checked file and standardize the 'product_name' use "${appended_files}/CMF_`y'", clear replace product_name = lower(product_name) replace product_name = trim(product_name) replace product_name = stritrim(product_name) cap gen index = _n tempfile CMF_`y' save `CMF_`y'', replace // Merge with product_name crosswalk to assign industry categories use "${string_cleaning}/crosswalks/product_name_appended_w_discs.dta", clear merge 1:m product_name using `CMF_`y'' drop if _merge == 1 // remove intermediate and ambiguous flags drop product_name_intermediate correct_RA correct_RA_unsure comments unsure disc M F _merge ren product_name industry_raw // rename clean fields to industry_clean1-5 forval x = 1/5 { cap ren product_name`x' industry_clean`x' } ren total_freq industry_freq order industry_raw industry_clean* sort index drop index // Clean and merge material kinds replace materials_kinds = lower(materials_kinds) replace materials_kinds = trim(materials_kinds) replace materials_kinds = stritrim(materials_kinds) cap gen index = _n tempfile CMF_`y' save `CMF_`y'', replace use "${string_cleaning}/crosswalks/material_kind_all.dta", clear merge 1:m materials_kinds using `CMF_`y'' drop if _merge == 1 drop _merge ren materials_kinds materials_kinds_raw sort index drop index // Clean and merge production kinds replace production_kinds = lower(production_kinds) replace production_kinds = trim(production_kinds) replace production_kinds = stritrim(production_kinds) cap gen index = _n tempfile CMF_`y' save `CMF_`y'', replace use "${string_cleaning}/crosswalks/product_kind_all.dta", clear merge 1:m production_kinds using `CMF_`y'' drop if _merge == 1 ren production_kinds production_kinds_raw order production_kinds_raw product_kind_clean* sort index drop index _merge // Concatenate multiple clean fields & attributes into single summary strings foreach var in industry material_kind product_kind { if inlist("`var'", "material_kind", "product_kind") { // For material_kind & product_kind, include attributes gen `var'_new = `var'_clean1 forval i = 2/9 { replace `var'_clean`i' = trim(`var'_clean`i') replace `var'_clean`i' = stritrim(`var'_clean`i') // append if non-empty replace `var'_new = `var'_new + " // " + `var'_clean`i' if `var'_clean`i' != "" } // Now condense attribute fields similarly replace `var'_attribute1 = trim(`var'_attribute1) replace `var'_attribute1 = stritrim(`var'_attribute1) gen `var'_attr = `var'_attribute1 forval i = 2/7 { replace `var'_attribute`i' = trim(`var'_attribute`i') replace `var'_attribute`i' = stritrim(`var'_attribute`i') replace `var'_attr = `var'_attr + "//" + `var'_attribute`i' if `var'_attribute`i' != "" } drop `var'_clean* `var'_attribute* } else { // For industry, only clean fields gen `var'_new = `var'_clean1 forval i = 2/5 { replace `var'_new = `var'_new + " // " + `var'_clean`i' if `var'_clean`i' != "" } drop `var'_clean* } } // Rename new fields back to original variable names ren industry_new industry ren material_kind_new material_kind ren product_kind_new product_kind // Final cleanup: compress storage and drop unused variables compress // If no industry_raw, clear maker/shop/etc to missing foreach var in maker shop manufacture factory { replace `var' = . if industry_raw == "" } // Drop auxiliary counts and notes drop industry_freq estab_num material_enumerator_notes material_freq /// product_enumerator_notes product_freq product_freq2 compress // Save cleaned dataset save "${string_output}/CMF_`y'", replace } // 1880 General Schedule (S3) industry cleaning use "${appended_files}/1880_S3.dta", clear replace product_name = lower(product_name) replace product_name = trim(product_name) replace product_name = stritrim(product_name) cap gen index = _n tempfile CMF_1880_S3 save `CMF_1880_S3', replace use "${string_cleaning}/crosswalks/product_name_appended_w_discs.dta", clear merge 1:m product_name using `CMF_1880_S3' drop if _merge == 1 drop product_name_intermediate correct_RA correct_RA_unsure comments unsure disc M F _merge ren product_name industry_raw forval x = 1/4 { ren product_name`x' industry_clean`x' } cap ren product_name5 industry_clean5 ren total_freq industry_freq order industry_raw industry_clean* sort index drop index // Replace placeholder "." with empty string replace industry_clean3 = "" if industry_clean3 == "." replace industry_clean4 = "" if industry_clean4 == "." gen industry_new = industry_clean1 forval i = 2/5 { replace industry_new = industry_new + " // " + industry_clean`i' if industry_clean`i' != "" } drop industry_clean* ren industry_new industry save "${string_output}/CMF_1880_S3.dta", replace // Pass through most special schedules without cleaning foreach ss in 1 2 3 4 5 6 7 9 10 11 { use "${appended_files}/1880_SS`ss'.dta", clear save "${string_output}/CMF_1880_SS`ss'.dta", replace } // Schedule 8: clean product strings use "${appended_files}/1880_SS8.dta", clear // rename raw cheese kind field ren cheesefackindsofcheese_clean production_kinds replace production_kinds = lower(production_kinds) replace production_kinds = trim(production_kinds) replace production_kinds = stritrim(production_kinds) gen index = _n merge m:1 production_kinds using "${string_cleaning}/crosswalks/product_kind_all.dta" sort index drop if _merge == 2 drop index _merge product_kind_attribute2 product_kind_clean3 product_kind_attribute3 /// product_kind_clean4 product_kind_attribute4 product_kind_clean5 product_kind_attribute5 /// product_kind_clean6 product_kind_attribute6 product_kind_clean7 product_kind_attribute7 /// product_kind_clean8 product_kind_clean9 // restore original name ren production_kinds cheesefackindsofcheese_clean save "${string_output}/CMF_1880_SS8.dta", replace // Schedule 12: clean product strings use "${appended_files}/1880_SS12.dta", clear rename kindofrockquarried_clean production_kinds replace production_kinds = lower(production_kinds) replace production_kinds = trim(production_kinds) replace production_kinds = stritrim(production_kinds) gen index = _n merge m:1 production_kinds using "${string_cleaning}/crosswalks/product_kind_all.dta" sort index drop if _merge == 2 drop index _merge product_kind_clean2 product_kind_attribute2 product_kind_clean3 /// product_kind_attribute3 product_kind_clean4 product_kind_attribute4 /// product_kind_clean5 product_kind_attribute5 product_kind_clean6 /// product_kind_attribute6 product_kind_clean7 product_kind_attribute7 /// product_kind_clean8 product_kind_clean9 ren production_kinds kindofrockquarried_clean save "${string_output}/CMF_1880_SS12.dta", replace