/* Purpose: Clean various variables in all years. */ if "`1'"==""{ global CMF_pipeline "../.." } global input "${CMF_pipeline}/6_power_machine_cleaning/output" global output "${CMF_pipeline}/8_variable_cleaning/output" global input_1880 "${CMF_pipeline}/7_standardize_1880/output" use "${input}/1850_power_machine_vars_cleaned.dta", clear //6 rows have non-numeric values in hands_male //15 rows have illegible hands_male values (marked by "?") replace hands_male = "4" if file_name == "VT_5_windsor_00240_r_L.jpg" & /// hands_male == "Hand" replace hands_male = "7" if file_name == "NY_5_new york_00556_l_L.jpg" & /// hands_male == "Hand" replace hands_male = "3" if file_name == "NC_5_johnston_00070_r_L.jpg" & /// hands_male == "Raw Turpent" replace hands_male = "12" if hands_male == "12 Men" replace hands_male = "12" if hands_male == "12 Boys" replace hands_male = "0.33" if hands_male == "31-Dec" //1 row has non-numeric value in hands_female //2 rows have string "6 mo" or "9 Mos" in hands_female //3 rows have illegible hands_female values (marked by "?") replace hands_female = "" if hands_female == " " replace hands_female = "" if file_name == "NY_5_orleans_00163_r_L.jpg" & /// hands_female == "Hand" //2 rows have string "Owned" in avg_wage_male //1 row has non-numeric value in avg_wage_male //29 rows have illegible avg_wage_male values (marked by "?") replace avg_wage_male = "210" if avg_wage_male == "2+Y1+Q15423" //7 rows have non-numeric values in avg_wage_female //1 row has string "6 mos" in avg_wage_female //4 rows have illegible avg_wage_female values (marked by "?") replace avg_wage_female = "" if avg_wage_female == " " replace avg_wage_female = "" if avg_wage_female == "s" replace avg_wage_female = "" if avg_wage_female == "t" replace materials_value1 = "960" if file_name == "NY_5_genesee_00336_r_L.jpg" & /// firm_number == 8 replace prod_qty1 = "333" if file_name == "NY_5_genesee_00336_r_L.jpg" & /// firm_number == 8 replace prod_unitofmeasure1 = "bbs" if file_name == "NY_5_genesee_00336_r_L.jpg" & /// firm_number == 8 replace materials_qty2 = "" if file_name == "NY_5_genesee_00336_r_L.jpg" & /// firm_number == 8 replace power_kind2 = "2 runs of stone" if file_name == "NY_5_genesee_00336_r_L.jpg" & /// firm_number == 8 replace avg_wage_male = "60" if file_name == "NY_5_genesee_00336_r_L.jpg" & /// firm_number == 8 replace avg_wage_female = "" if file_name == "NY_5_genesee_00336_r_L.jpg" & /// firm_number == 8 replace prod_unitofmeasure1 = "bbl" if file_name == "NY_5_orange_00156_r_L.jpg" & /// firm_number == 3 replace avg_wage_female = "" if file_name == "NY_5_orange_00156_r_L.jpg" & /// firm_number == 3 replace avg_wage_female = "" if file_name == "WI_5_walworth_00097_r_L.jpg" replace avg_wage_female = "" if file_name == "NY_5_new york_00600_l_L.jpg" & /// avg_wage_female == "pairs" *Now work on capital replace capital = "" if capital == " " | capital == "." replace capital = "380" if capital == "R 380" replace capital = "250" if capital == "P 250" replace capital = "400" if capital == "R 400" replace capital = "6000" if capital == "6000 Real" replace capital = "2000" if capital == "2000 Pers" replace capital = "20000" if capital == "20000 R." replace capital = "2000" if capital == "2000 P." replace capital = "650" if capital == "500 & 150" replace capital = "3000" if capital == "2000 & 1000" *Now clean production values foreach var of varlist production_values*{ replace `var' = "" if `var' == "." | `var' == " " } replace prod_qty4 = "50" if production_values4 == "544-50" replace production_values4 = "544" if production_values4 == "544-50" *Now go with production quantity gen tag=. foreach var of varlist prod_qty*{ replace `var' = "" if `var' == "." | `var' == " " destring `var', gen(temp_`var') force replace tag = 1 if temp_`var' == . & `var' != "" replace tag = . if strpos(`var', "?") drop temp_`var' order tag } replace prod_qty1 = "700" if tag == 1 & file_name == "AL_5_montgomery_00064_r_L.jpg" replace prod_qty2 = "30" if tag == 1 & file_name == "AL_5_montgomery_00064_r_L.jpg" replace production_kinds_raw1 = "plank" if tag == 1 & file_name == "AL_5_montgomery_00064_r_L.jpg" replace production_kinds_raw2 = "corn" if tag == 1 & file_name == "AL_5_montgomery_00064_r_L.jpg" //one establishment reported "60 to 80", "40 to 50" in product quantities. We will take the median. replace prod_qty1 = "70" if prod_qty1 == "60-80" replace prod_qty2 = "45" if prod_qty2 == "40-50" replace prod_qty1 = "Not Known" if prod_qty1 == "Muknoon" replace prod_qty1 = "25" if prod_qty1 == "about 25" replace prod_qty2 = "450000" if prod_qty2 == "4500 M" replace prod_qty1 = "200" if prod_qty1 == "200 B" replace prod_qty1 = "4500" if prod_qty1 == "B 4500" replace prod_qty2 = "10" if prod_qty2 == "Iron 10" replace prod_qty2 = "15" if prod_qty2 == "Iron 15" replace prod_qty2 = "12" if prod_qty2 == "Iron 12" replace prod_qty3 = "18" if prod_qty3 == "Iron 18" replace production_kinds_raw1 = "Miscellaneous work" if prod_qty1 == "Miscellaneous" replace production_kinds_raw3 = "Miscellaneous" if prod_qty3 == "Miscellaneous" replace prod_qty1 = "106" if prod_qty1 == "6 & 100" replace production_kinds_raw1 = "Miscellaneous " + production_kinds_raw1 if prod_qty1 == "Miscella" | prod_qty1 == "Mis" replace production_kinds_raw1 = "Hides & Skins " + production_kinds_raw1 if prod_qty1 == "Hides & Skins" //take the median and then round up replace prod_qty1 = "6" if prod_qty1 == "4 to 7" replace prod_qty1 = "6" if prod_qty1 == "5 to 6" //the unit is "feet" so we'll keep the decimal place replace prod_qty2 = "13.6" if prod_qty2 == "13-6" replace prod_qty3 = "50.4" if prod_qty3 == "50-4" //the unit is "bbls" so we'll keep the decimal place replace prod_qty1 = "633.33" if prod_qty1 == "6000/33.33" replace prod_qty1 = "1533.33" if prod_qty1 == "1500/33.3" replace prod_qty1 = "1030" if prod_qty1 == "1000/30" replace prod_qty2 = "905" if prod_qty2 == "875/30" replace prod_qty1 = "831.25" if prod_qty1 == "800/31.25" replace prod_qty1 = "25" if prod_qty1 == "20 or 30" drop tag *Now for materials quantity foreach var of varlist materials_qty*{ replace `var' = "" if `var' == "." | `var' == " " } replace materials_unitofmeasure1 = "m" if materials_qty1 == "37m" replace materials_qty1 = "37" if materials_qty1 == "37m" replace materials_unitofmeasure2 = "m" if materials_qty2 == "20m" replace materials_qty2 = "20" if materials_qty2 == "20m" replace materials_unitofmeasure3 = "m" if materials_qty3 == "10m" replace materials_qty3 = "10" if materials_qty3 == "10m" replace materials_unitofmeasure1 = "m" if materials_qty1 == "30m" replace materials_qty1 = "30" if materials_qty1 == "30m" replace materials_unitofmeasure1 = "m" if materials_qty1 == "15m" replace materials_qty1 = "15" if materials_qty1 == "15m" replace materials_unitofmeasure3 = "m" if materials_qty3 == "100m" replace materials_qty3 = "100" if materials_qty3 == "100m" replace materials_unitofmeasure4 = "m" if materials_qty4 == "50m" replace materials_qty4 = "50" if materials_qty4 == "50m" replace materials_unitofmeasure1 = "m" if materials_qty1 == "12m" replace materials_qty1 = "12" if materials_qty1 == "12m" replace materials_unitofmeasure2 = "m" if materials_qty2 == "30m" replace materials_qty2 = "30" if materials_qty2 == "30m" replace materials_unitofmeasure1 = "B." if materials_qty1 == "B.800" replace materials_qty1 = "800" if materials_qty1 == "B.800" replace materials_unitofmeasure2 = "b." if materials_qty2 == "B.1000" replace materials_qty2 = "1000" if materials_qty2 == "B.1000" replace materials_qty3 = "50" if materials_qty3 == "C 50" replace materials_unitofmeasure1 = "M" if materials_qty1 == "4500 M" replace materials_qty1 = "4500" if materials_qty1 == "4500 M" replace materials_unitofmeasure1 = "D.W." if materials_qty1 == "1000 L. W" replace materials_qty1 = "1000" if materials_qty1 == "1000 L. W" replace materials_qty2 = "5000" if materials_qty2 == "5000 C" replace materials_qty1 = "5000" if materials_qty1 == "Grain 5000" replace materials_kinds_raw1 = "sides sole, upper leather" if materials_qty1 == "sides" *Now do materials_value merge 1:1 file_name firm_number using "${CMF_pipeline}/8_variable_cleaning/crosswalks/materials_value_1850.dta", keep(1 3) foreach var of varlist materials_value*{ replace `var'=new_`var' if _m==3 } drop new_materials_value* _m *Now add in the stuff from the value checking step merge 1:1 file_name firm_number using "${CMF_pipeline}/8_variable_cleaning/crosswalks/1850_crosswalk.dta", keep(1 3) nogen foreach var of varlist correct*{ if "`var'"=="correct_county"{ continue } di "`var'" local orig= subinstr("`var'","correct_","",.) di "`orig'" replace `orig'=`var' if `var'!="" replace `orig'="" if `orig'=="missing" drop `var' } merge 1:1 file_name firm_number using "${CMF_pipeline}/8_variable_cleaning/crosswalks/1850_crosswalk_large.dta", keep(1 3) nogen foreach var of varlist correct*{ if "`var'"=="correct_county"{ continue } di "`var'" local orig= subinstr("`var'","correct_","",.) di "`orig'" replace `orig'=`var' if `var'!="" replace `orig'="" if `orig'=="missing" drop `var' } save "${output}/1850_vars_cleaned.dta", replace *Now move on to 1860 use "${input}/1860_power_machine_vars_cleaned.dta", clear *Start with labor data replace hands_male = "" if hands_male == " " | hands_male == "." replace hands_male = "4" if hands_male == "Birch Lumber" replace hands_male = "2" if hands_male == "2 Boys" replace hands_male = "10" if hands_male == "10 boys" replace hands_male = "8" if hands_male == "8 Boys" replace hands_male = "1" if hands_male == "1 Fire" //4 rows have non-numeric values in hands_female //12 row has illegible hands_female values (marked by "?") replace hands_female = "" if hands_female == " " | hands_female == "." replace hands_male = "4.5 setts" if file_name == "NY_6_tompkins_00166_r_L.jpg" & /// firm_number == 4 replace hands_female = "" if hands_female == "setts" replace hands_female = "5" if hands_female == "5 in" replace hands_female = "3" if hands_female == "3 in" replace hands_female = "40" if hands_female == "4 B" //17 rows have string "On shares" in avg_wage_male //2 rows have non-numeric values in avg_wage_male //47 rows have illegible avg_wage_male values (marked by "?") replace avg_wage_male = "" if avg_wage_male == " " | avg_wage_male == "." replace avg_wage_male = "35" if avg_wage_male == "Lath" replace avg_wage_male = "210" if avg_wage_male == "210 Boys" //4 rows have non-numeric values in avg_wage_female //10 rows have illegible avg_wage_female values (marked by "?") //5 rows have string "Board" or "No Board" in avg_wage_female replace avg_wage_female = "" if avg_wage_female == " " | avg_wage_female == "." replace production_kinds_raw1 = "Pine blocks" if file_name == "CA_6_tuolumne_00369_r_A.jpg" & /// avg_wage_female == "Pine Blocks" replace avg_wage_female = "" if file_name == "CA_6_tuolumne_00369_r_A.jpg" & /// avg_wage_female == "Pine Blocks" replace materials_qty1 = "150" if file_name == "NH_6_grafton_00080_l_L.jpg" & firm_number == 7 replace materials_qty2 = "200" if file_name == "NH_6_grafton_00080_l_L.jpg" & firm_number == 7 replace avg_wage_female = "" if avg_wage_female == "tons" replace avg_wage_female = "" if avg_wage_female == "ft" *Now capital: replace capital = "" if capital == " " | capital == "." replace capital = "" if capital == " " replace capital = "500" if capital == "500B" *Production Values foreach var of varlist production_values*{ replace `var' = "" if `var' == "." | `var' == " " } *Prod quantity gen tag=. foreach var of varlist prod_qty*{ replace `var' = "" if `var' == "." | `var' == " " destring `var', gen(temp_`var') force replace tag = 1 if temp_`var' == . & `var' != "" replace tag = . if strpos(`var', "?") drop temp_`var' order tag } replace prod_qty1 = "260" if prod_qty1 == "60 & 200" replace prod_qty1 = "230" if prod_qty1 == "80 & 150" replace prod_qty1 = "177" if prod_qty1 == "27 & 150" replace prod_qty1 = "100" if prod_qty1 == "50 & 50" replace prod_qty1 = "288" if prod_qty1 == "144 & 144" replace prod_qty1 = "600" if prod_qty1 == "300 & 300" replace prod_qty1 = "200" if prod_qty1 == "100 & 100" replace prod_qty1 = "155" if prod_qty1 == "75 & 80" replace prod_qty1 = "10" if prod_qty1 == "Ironing 10" replace prod_qty1 = "40" if prod_qty1 == "Ironing 40" //the enumerator put the weight of the steam boat hulls in parentheses (e.g. 5(1900 tons)) //so we'll move the weight to the product notes variable replace product_comments1 = "each 1900 tons" if prod_qty1 == "5(1900)" replace product_comments1 = "each 225 tons" if prod_qty1 == "1(225)" replace product_comments1 = "each 1100 tons" if prod_qty1 == "3(1100)" | prod_qty1 == "1(1100)" replace product_comments1 = "each 1500 tons" if prod_qty1 == "2(1500)" replace product_comments2 = "each 176 tons" if prod_qty2 == "1(176)" replace product_comments3 = "each 150 tons" if prod_qty3 == "3(150)" replace product_comments4 = "each 100 tons" if prod_qty4 == "1(100)" replace product_comments5 = "each 636 tons" if prod_qty5 == "1(636)" replace prod_unitofmeasure1 = "" if tag == 1 & file_name == "IN_6_floyd_00219_S.jpg" replace prod_unitofmeasure2 = "" if tag == 1 & file_name == "IN_6_floyd_00219_S.jpg" replace prod_unitofmeasure3 = "" if tag == 1 & file_name == "IN_6_floyd_00219_S.jpg" replace prod_unitofmeasure4 = "" if tag == 1 & file_name == "IN_6_floyd_00219_S.jpg" replace prod_unitofmeasure5 = "" if tag == 1 & file_name == "IN_6_floyd_00219_S.jpg" replace prod_qty1 = regexs(0) if regexm(prod_qty1, "[0-9]") & tag == 1 & file_name == "IN_6_floyd_00219_S.jpg" replace prod_qty2 = "1" if prod_qty2 == "1(176)" replace prod_qty3 = "3" if prod_qty3 == "3(150)" replace prod_qty4 = "1" if prod_qty4 == "1(100)" replace prod_qty5 = "1" if prod_qty5 == "1(636)" replace prod_unitofmeasure1 = "m" if prod_qty1 == "1500 M" replace prod_qty1 = "1500" if prod_qty1 == "1500 M" replace prod_qty2 = "500" if prod_qty2 == "500 C" replace prod_qty1 = "3456" if prod_qty1 == "3456 S" replace prod_qty2 = "2400" if prod_qty2 == "2400 K" replace prod_qty3 = "1400" if prod_qty3 == "1400 B" replace prod_qty4 = "2400" if prod_qty4 == "2400 H" replace prod_qty1 = "1000000" if prod_qty1 == "1 Million" replace prod_unitofmeasure1 = "m" if prod_qty1 == "125 M" replace prod_qty1 = "125" if prod_qty1 == "125 M" replace prod_unitofmeasure2 = "m" if prod_qty2 == "25 M" replace prod_qty2 = "25" if prod_qty2 == "25 M" replace prod_qty1 = "50" if prod_qty1 == "20 & 30" replace prod_qty1 = "14" if prod_qty1 == "14 H" replace prod_qty2 = "300" if prod_qty2 == "250, 50" replace prod_qty1 = "700" if prod_qty1 == "about 600 to 800 " replace prod_qty2 = "250" if prod_qty2 == "200 to 300" replace prod_qty5 = "750" if prod_qty5 == "B.750" replace prod_qty1 = "25000" if prod_qty1 == "20000 to 30000" replace production_kinds_raw1 = "2 horse carriages" if prod_qty1 == "15-2" replace prod_qty1 = "15" if prod_qty1 == "15-2" replace production_kinds_raw1 = "2 horse wagons" if prod_qty1 == "8-2" replace prod_qty1 = "8" if prod_qty1 == "8-2" replace production_kinds_raw2 = "1 horse wagons" if prod_qty2 == "4-1" replace prod_qty2 = "4" if prod_qty2 == "4-1" replace production_kinds_raw3 = "2 horse sleighs" if prod_qty3 == "3-2" replace prod_qty3 = "3" if prod_qty3 == "3-2" replace production_kinds_raw4 = "1 horse sleighs" if prod_qty4 == "2-1" replace prod_qty4 = "2" if prod_qty4 == "2-1" replace production_kinds_raw1 = "102 inches tin pans" if prod_qty1 == "5000-102" replace prod_qty1 = "5000" if prod_qty1 == "5000-102" replace production_kinds_raw2 = "6 inches tin pans" if prod_qty2 == "2000-6" replace prod_qty2 = "2000" if prod_qty2 == "2000-6" replace production_kinds_raw3 = "12 inches tin pans" if prod_qty3 == "400-12" replace prod_qty3 = "400" if prod_qty3 == "400-12" replace production_kinds_raw4 = "2 inches tin pans" if prod_qty4 == "500-2" replace prod_qty4 = "500" if prod_qty4 == "500-2" replace production_kinds_raw1 = "2 horse wagons" if prod_qty1 == "2-2" replace prod_qty1 = "2" if prod_qty1 == "2-2" replace production_kinds_raw2 = "1 horse wagons" if prod_qty2 == "13-1" replace prod_qty2 = "13" if prod_qty2 == "13-1" replace production_kinds_raw3 = "2 horse wagons" if prod_qty3 == "2-2" replace prod_qty3 = "2" if prod_qty3 == "2-2" replace production_kinds_raw2 = "2 horse wagons" if prod_qty2 == "4-2" replace prod_qty2 = "4" if prod_qty2 == "4-2" replace production_kinds_raw1 = "2 horse wagons" if prod_qty1 == "19-2" replace prod_qty1 = "19" if prod_qty1 == "19-2" replace production_kinds_raw2 = "1 horse wagons" if prod_qty2 == "8-1" replace prod_qty2 = "8" if prod_qty2 == "8-1" replace production_kinds_raw4 = "1 horse cutters" if prod_qty4 == "8-1" replace prod_qty4 = "8" if prod_qty4 == "8-1" replace production_kinds_raw2 = "1 horse wagons" if prod_qty2 == "5-1" replace prod_qty2 = "5" if prod_qty2 == "5-1" replace production_kinds_raw1 = "2 horse wagons" if prod_qty1 == "30-2" replace prod_qty1 = "30" if prod_qty1 == "30-2" replace prod_qty2 = "7" if prod_qty2 == "5 & 2" replace prod_qty1 = "5425" if prod_qty1 == "2175 & 3250" replace prod_qty1 = "4800" if prod_qty1 == "1600 & 3200" replace prod_qty1 = "9" if prod_qty1 == "9 Horse" replace prod_qty2 = "102" if prod_qty2 == "102 Horse" replace prod_qty2 = "360" if prod_qty2 == "40 & 320" replace prod_qty1 = "21" if prod_qty1 == "18 to 23" replace prod_qty1 = "39" if prod_qty1 == "38 to 40" replace prod_qty2 = "150" if prod_qty2 == "50 & 100" replace prod_qty3 = "700" if prod_qty3 == "600 & 100" replace prod_qty4 = "700" if prod_qty4 == "600 & 100" replace production_kinds_raw1 = "Willow breads" if prod_qty1 == "Willow" replace prod_qty1 = "" if prod_qty1 == "Willow" drop tag *Material Quantity gen tag=. foreach var of varlist materials_qty*{ replace `var' = "" if `var' == "." | `var' == " " destring `var', gen(temp_`var') force replace tag = 1 if temp_`var' == . & `var' != "" replace tag = . if strpos(`var', "?") drop temp_`var' order tag } replace materials_kinds_raw1 = "Iron" if materials_qty1 == "Iron" replace materials_kinds_raw2 = "Timber" if materials_qty2 == "Timber" replace materials_qty1 = "87.5" if materials_qty1 == "75 to 100" replace materials_qty2 = "13.5" if materials_qty2 == "12 to 15" replace materials_kinds_raw1 = "Timber" if materials_qty1 == "Timber" replace materials_unitofmeasure1 = "B" if materials_qty1 == "5000B" replace materials_qty1 = "5000" if materials_qty1 == "5000B" replace materials_kinds_raw1 = "iron" if tag == 1 & file_name == "KY_6__00200_r_L.jpg" replace materials_kinds_raw2 = "coal" if tag == 1 & file_name == "KY_6__00200_r_L.jpg" replace materials_unitofmeasure1 = "ton" if tag == 1 & file_name == "KY_6__00200_r_L.jpg" replace materials_unitofmeasure2 = "bu" if tag == 1 & file_name == "KY_6__00200_r_L.jpg" replace materials_qty2 = regexs(2) if (regexm(materials_qty1, "([0-9]+)[, ]+([0-9]+)$")) & tag == 1 & file_name == "KY_6__00200_r_L.jpg" replace materials_qty1 = regexs(1) if (regexm(materials_qty1, "([0-9]+)[, ]+([0-9]+)$")) & tag == 1 & file_name == "KY_6__00200_r_L.jpg" replace materials_qty3 = "400" if materials_qty3 == "400 C" replace materials_unitofmeasure1 = "B" if file_name == "KY_6_casey_00034_r_L.jpg" & tag == 1 & materials_unitofmeasure1 == "" replace materials_unitofmeasure2 = "B" if file_name == "KY_6_casey_00034_r_L.jpg" & tag == 1 & materials_unitofmeasure2 == "" replace materials_qty1 = subinstr(materials_qty1, " B", "", .) if file_name == "KY_6_casey_00034_r_L.jpg" & tag == 1 replace materials_qty2 = subinstr(materials_qty2, " B", "", .) if file_name == "KY_6_casey_00034_r_L.jpg" & tag == 1 replace materials_qty2 = "20" if materials_qty2 == "20 C" replace materials_qty1 = "1000000" if materials_qty1 == "1 Million" replace materials_unitofmeasure1 = "M" if materials_qty1 == "125 M" replace materials_unitofmeasure2 = "M" if materials_qty2 == "25 M" replace materials_unitofmeasure3 = "M" if materials_qty3 == "10 M" replace materials_qty1 = "125" if materials_qty1 == "125 M" replace materials_qty2 = "25" if materials_qty2 == "25 M" replace materials_qty3 = "10" if materials_qty3 == "10 M" replace materials_kinds_raw1 = "stav and calf" if materials_qty1 == "350, 200 Stav, 150 Calf" replace materials_qty1 = "350" if materials_qty1 == "350, 200 Stav, 150 Calf" replace materials_qty1 = "1000" if materials_qty1 == "ME_6_cumberland_00120_r_A.jpg" replace materials_unitofmeasure3 = "bbls" if materials_qty3 == "bbls" replace materials_qty3 = "50" if materials_qty3 == "bbls" replace materials_qty1 = "180000" if materials_qty1 == "180000c" replace materials_qty1 = "478" if materials_qty1 == "478m" replace materials_kinds_raw1 = "Loggs cypris" if materials_qty1 == "Loggs" replace materials_qty1 = "25000" if materials_qty1 == "20000 to 30000" replace materials_qty1 = "2.75" if materials_qty1 == "2.5-3" replace materials_unitofmeasure1 = "lbs" if materials_qty1 == "25-5" replace materials_qty1 = "25.3" if materials_qty1 == "25-5" replace materials_qty5 = "1800" if materials_qty4 == "8 & 1800" replace materials_unitofmeasure5 = "lbs" if materials_qty4 == "8 & 1800" replace materials_kinds_raw5 = "tallow" if materials_qty4 == "8 & 1800" replace materials_unitofmeasure4 = "bbls" if materials_qty4 == "8 & 1800" replace materials_kinds_raw4 = "oil" if materials_qty4 == "8 & 1800" replace materials_qty4 = "8" if materials_qty4 == "8 & 1800" replace materials_qty2 = "5095" if materials_qty2 == "5 to 5100" replace materials_unitofmeasure3 = "tons" if materials_qty3 == "4000, 150" replace materials_qty3 = "155.34" if materials_qty3 == "4000, 150" drop tag *Materials value with a crosswalk merge 1:1 file_name firm_number using "${CMF_pipeline}/8_variable_cleaning/crosswalks/materials_value_1860.dta", keep(1 3) foreach var of varlist materials_value*{ replace `var'=new_`var' if _m==3 } drop new_materials_value* _m *Now add in the stuff from the value checking step merge 1:1 file_name firm_number using "${CMF_pipeline}/8_variable_cleaning/crosswalks/1860_crosswalk.dta", keep(1 3) nogen foreach var of varlist correct*{ if "`var'"=="correct_county"{ continue } di "`var'" local orig= subinstr("`var'","correct_","",.) di "`orig'" replace `orig'=`var' if `var'!="" replace `orig'="" if `orig'=="missing" drop `var' } merge 1:1 file_name firm_number using "${CMF_pipeline}/8_variable_cleaning/crosswalks/1860_crosswalk_large.dta", keep(1 3) nogen foreach var of varlist correct*{ if "`var'"=="correct_county"{ continue } di "`var'" local orig= subinstr("`var'","correct_","",.) di "`orig'" replace `orig'=`var' if `var'!="" replace `orig'="" if `orig'=="missing" drop `var' } save "${output}/1860_vars_cleaned.dta", replace *Do 1870 use "${input}/1870_power_machine_vars_cleaned.dta", clear *Labor variables //40 rows have non-numeric values in hands_male //3 rows have string "No Hand" in hands_male and these got changed into empty values //43 rows have illegible hands_male values (marked by "?") replace hands_male = "" if hands_male == " " | hands_male == "." replace hands_male = "18" if hands_male == "Bursn Hicks & Smith" replace machine_description2 = hands_male if hands_male == "Run of Burrs" replace hands_male = "" if hands_male == "Run of Burrs" replace hands_male = "9" if hands_male == "Mabon Jna R" replace machine_description1 = "Saws Eng" if hands_male == "Circular" replace machine_description2 = "Circular" if hands_male == "Circular" replace hands_female = "" if hands_male == "Circular" replace hands_male = "5" if hands_male == "Circular" replace hands_male = "" if hands_male == "No Hand" replace hands_male = "3" if hands_male == "3 Manufacturing" replace hands_male = "4" if hands_male == "4 Hands" replace hands_male = "105" if hands_male == "14 Whites, 15 Chinese, 16 Indians, 60 Whites" replace hands_male = "1" if hands_male == "1 Hand" replace hands_male = "2" if hands_male == "2 Hands" replace hands_male = "32" if hands_male == "25 below, 7 above" replace hands_male = "3" if hands_male == "3 Above" replace hands_male = "40" if hands_male == "Above 5, under 35" replace hands_male = "42" if hands_male == "40 + ?" replace machine_description1 = hands_male if hands_male == "2 Run Stone" replace hands_male = "" if hands_male == "2 Run Stone" replace hands_male = "4" if hands_male == "1 Repairing" replace hands_male = "1" if hands_male == "1 Engine" replace hands_male = "1" if hands_male == "1 Moulder" replace hands_male = "3" if hands_male == "custom 2, private 1" replace hands_male = "2" if hands_male == "1 private, 1.5 custom" replace hands_male = "213" if hands_male == "?13" replace hands_male = "2" if hands_male == "1 & 0" replace hands_male = "3" if hands_male == "2 & 0" replace hands_male = "5" if hands_male == "2 & 3" //6 rows have non-numeric values in hands_female //6 rows have illegible hands_female values (marked by "?") replace hands_female = "" if hands_female == " " | hands_female == "." replace hands_female = "" if hands_female == "no" | hands_female == "Miners" replace hands_female = "" if hands_female == "Agr Impl" | hands_female == "Gang" replace hands_female = "" if hands_female == "Tannery" | hands_female == "Wagon Maker" //7 rows have non-numeric value in hands_children //5 rows have illegible hands_children values (marked by "?") replace hands_children = "" if hands_children == " " | hands_children == "." replace hands_children = "" if hands_children == "Gin" | hands_children == "Furs" replace hands_children = "" if hands_children == "Custom Mill" | hands_children == "Grist" replace hands_children = "" if hands_children == "Saw" | hands_children == "B Wheat" replace hands_children = "" if strpos(hands_children, "VA") //8 rows have string "self" in tot_wages //2 rows have string "shares" in tot_wages //6 rows have non-numeric values in tot_wages //125 rows have illegible tot_wages values (marked by "?") replace tot_wages = "" if tot_wages == " " | tot_wages == "." replace tot_wages = "" if tot_wages == "None" | tot_wages == "Custom" replace tot_wages = "250" if tot_wages == "s" replace tot_wages = "15000" if tot_wages == "t" replace tot_wages = "89478" if tot_wages == "89-478" replace tot_wages = "400" if tot_wages == "14?0" replace tot_wages = "222290" if tot_wages == "222-290" replace tot_wages = "700" if tot_wages == "350 private, 350 custom" replace tot_wages = "540" if tot_wages == "custom 300, private 240" *Capital replace capital = "" if capital == " " | capital == "." replace capital = "5" if capital == "S." replace capital = "18500" if capital == "Real 8500, Personal 10000" replace capital = "9000" if capital == "3000 private, 3000 custom" replace capital = "2500" if capital == "private 500, custom 2000" replace capital = "3000" if capital == "Real 3000" replace capital = "1000" if capital == "Pers 1000" | capital == "Personal 1000" replace capital = "4000" if capital == "Real 4000" replace capital = "300" if capital == "Real 300" replace capital = "2000" if capital == "Real 2000" replace capital = "5000" if capital == "Personal 5000" replace capital = "31000" if capital == "Real 31000" replace capital = "700" if capital == "Pers 700" replace capital = "1500" if capital == "Personal 1500" replace capital = "3000" if capital == "Pers. 3000" replace capital = "500" if capital == "Pers. 500" replace capital = "1000" if capital == "Pers. 1000" replace capital = "2000" if capital == "Pers. 2000" replace capital = "23000" if capital == "Real 23000" replace capital = "6500" if capital == "custom 4000, private 2500" replace power_kind1 = "Steam" if capital == "Steam" replace horsepower_number1 = "12" if capital == "Steam" replace machine_description1 = "Cupola" if capital == "Steam" replace hands_male = "20" if capital == "Steam" cap replace hands_male_clean = 20 if capital == "Steam" replace capital = "" if capital == "Steam" replace machine_description1 = "Eng" if capital == "1 Eng" replace machine_number1 = "1" if capital == "1 Eng" replace capital = "" if capital == "1 Eng" replace power_kind1 = "Hand" if capital == "Hand" replace capital = "" if capital == "Hand" replace capital = "1800" if capital == "Grist 1800" *production values foreach var of varlist production_values*{ replace `var' = "" if `var' == "." | `var' == " " } replace production_values2 = "12000" if production_values2 == "11490, 12000" replace production_values1 = "" if production_values1 == "Free" replace production_values1="337613" if file_name=="SC_7_charleston_0300_l_A.jpg" & firm_number==1 *the corresponding production quantity was 1500 ft so the production value was *calculated to be 15 replace production_values1 = "15" if production_values1 == "10 per 1000 ft" replace production_values4 = "" if production_values2 == "2800 & 3075" replace production_values2 = "2800" if production_values2 == "2800 & 3075" replace production_values1 = "21600" if production_values1 == "21600+AA30383" *Production quantity foreach var of varlist prod_qty*{ replace `var' = "" if `var' == "." | `var' == " " } //Here we didn't take the median because the manuscript has "no." written above //"500" and "kg?" above "10000" replace prod_qty1 = "500" if prod_qty1 == "500-10000" replace prod_qty1 = "156.25" if prod_qty1 == "15 6.25" replace prod_unitofmeasure1 = "days" if prod_qty1 == "275 days at 3 perday" replace prod_qty1 = "275" if prod_qty1 == "275 days at 3 perday" replace production_kinds_raw2 = "Repairs" if prod_qty2 == "Repairs" replace prod_qty2 = "" if prod_qty2 == "Repairs" replace prod_qty2 = "1000" if prod_qty2 == "1000(Estimated)" //Here we didn't take the median because there was a calculated firm total on the //manuscript and based on the page total, the numbers should be 1200 and 900 replace prod_qty1 = "1200" if prod_qty1 == "200-1200" replace prod_qty2 = "900" if prod_qty2 == "300-900" replace prod_qty3 = "1400" if prod_qty3 == "1200, 200" replace prod_qty1 = "380" if prod_qty1 == "380m" replace prod_qty1 = "7" if prod_qty1 == "6 to 8" replace prod_qty2 = "13" if prod_qty2 == "10 to 15" replace prod_qty1 = "500" if prod_qty1 == "500 or 50000" replace prod_qty1 = "13" if prod_qty1 == "10 to 15" replace prod_qty1 = "10" if prod_qty1 == "8 to 12" replace prod_qty2 = "5" if prod_qty2 == "4 to 6" replace prod_qty2 = "7" if prod_qty2 == "5 to 8" replace prod_qty2 = "3" if prod_qty2 == "2 to 3" replace prod_qty1 = "1000000" if prod_qty1 == "1 million" replace prod_qty1 = "426" if prod_qty1 == "106 & 320" replace prod_unitofmeasure1 = "bbls" if prod_qty1 == "19200 or 600" replace prod_qty1 = "600" if prod_qty1 == "19200 or 600" replace prod_qty1 = "140" if prod_qty1 == "140m" replace prod_qty1 = "3" if prod_qty1 == "Three" replace prod_qty1 = "770" if prod_qty1 == "140-1400" replace prod_qty2 = "350" if prod_qty2 == "100-600" replace prod_qty4 = "33" if prod_qty4 == "25 & 8" replace prod_unitofmeasure1 = "lbs" if prod_qty1 == "322882.5 lbs" replace prod_qty1 = "322882.5" if prod_qty1 == "322882.5 lbs" replace prod_qty3 = "18000" if prod_qty3 == "18000 9750" replace prod_unitofmeasure1 = "lbs" if prod_qty1 == "53000 or 768" replace prod_qty1 = "53000" if prod_qty1 == "53000 or 768" replace prod_unitofmeasure2 = "lbs" if prod_qty2 == "1500 or 394" replace prod_qty2 = "1500" if prod_qty2 == "1500 or 394" replace prod_qty3 = "" if prod_qty3 == "Misel" replace prod_qty1 = "56" if prod_qty1 == "11-100" replace prod_qty1 = "9" if prod_qty1 == "8-10" replace prod_qty1 = "70" if prod_qty1 == "50/2" replace prod_qty2 = "50" if prod_qty2 == "40/10" replace prod_qty1 = "85" if prod_qty1 == "75/10" replace prod_qty2 = "40" if prod_qty2 == "30/10" replace prod_qty1 = "138" if prod_qty1 == "75-120/40" replace production_kinds_raw6 = "All others" if prod_qty1 == "All Others" replace production_kinds_raw1 = "2 storey houses" if prod_qty1 == "4-2" replace prod_unitofmeasure1 = "" if prod_qty1 == "4-2" replace prod_qty1 = "4" if prod_qty1 == "4-2" replace production_kinds_raw2 = "1 storey houses" if prod_qty2 == "1-1" replace prod_unitofmeasure2 = "" if prod_qty2 == "1-1" replace prod_qty2 = "1" if prod_qty2 == "1-1" replace production_kinds_raw1 = "2 Horse wagons" if prod_qty1 == "4-2 Ho" replace prod_qty1 = "4" if prod_qty1 == "4-2 Ho" replace production_kinds_raw2 = "1 Horse wagons" if prod_qty2 == "3-1 H St" replace prod_qty2 = "3" if prod_qty2 == "3-1 H St" replace prod_qty2 = "14" if prod_qty2 == "13-15" replace prod_qty3 = "65" if prod_qty3 == "60-70" replace prod_qty4 = "4" if prod_qty4 == " 4-Mar" replace prod_qty5 = "55" if prod_qty5 == "50-60" replace production_kinds_raw1 = "meals after grinding" if prod_qty1 == "After Grinding" replace prod_qty1 = "" if prod_qty1 == "After Grinding" replace prod_qty1 = "40" if prod_qty1 == "40-7" replace prod_qty2 = "100" if prod_qty2 == "100-4" replace prod_qty3 = "500" if prod_qty3 == "500-75" replace prod_qty4 = "50" if prod_qty4 == "50-300" replace prod_qty5 = "50" if prod_qty5 == "50-12" replace prod_unitofmeasure1 = "" if prod_qty1 == "1650 or 1000" replace prod_qty1 = "1650" if prod_qty1 == "1650 or 1000" replace prod_unitofmeasure1 = "" if prod_qty1 == "240000 or 8000" replace prod_qty1 = "240000" if prod_qty1 == "240000 or 8000" replace production_kinds_raw4 = "Custom" if prod_qty4 == "Custom" replace prod_qty4 = "" if prod_qty4 == "Custom" replace prod_qty1 = "2060" if prod_qty1 == "1357 & 703" replace prod_qty1 = "850" if prod_qty1 == "8-900" *materials qty gen tag=. foreach var of varlist materials_qty*{ replace `var' = "" if `var' == "." | `var' == " " destring `var', gen(temp_`var') force replace tag = 1 if temp_`var' == . & `var' != "" replace tag = . if strpos(`var', "?") drop temp_`var' order tag } replace materials_qty6 = "5" if materials_qty3 == "100, 5" replace materials_unitofmeasure6 = "gallon" if materials_qty3 == "100, 5" replace materials_kinds_raw6 = "oil" if materials_qty3 == "100, 5" replace materials_unitofmeasure3 = "lbs" if materials_qty3 == "100, 5" replace materials_kinds_raw3 = "paints" if materials_qty3 == "100, 5" replace materials_qty3 = "100" if materials_qty3 == "100, 5" replace materials_qty1 = "125000" if materials_qty1 == "125000`" replace materials_qty4 = "248484" if materials_qty4 == "82828, 165656" replace materials_qty1 = "252" if materials_qty1 == "3 to 500" replace materials_qty1 = "135" if materials_qty1 == "135 or 2500" replace materials_qty3 = "125" if materials_qty3 == "125 or 624" replace materials_qty1 = "700000" if materials_qty1 == "700000 or 2200" replace materials_qty1 = "74" if materials_qty1 == "74 or 1250" replace materials_qty2 = "13" if materials_qty2 == "13 or 100" replace materials_qty1 = "70" if materials_qty1 == "70 or 1200" replace materials_qty2 = "13" if materials_qty2 == "130 or 105" replace materials_qty3 = "11" if materials_qty3 == "10 to 11" replace materials_qty1 = subinstr(materials_qty1, " million", "000000", .) if tag == 1 & file_name == "MA_7_bristol_00137_L.jpg" replace materials_kinds_raw6 = "japan" if materials_qty3 == "15 & 6" replace materials_qty6 = "6" if materials_qty3 == "15 & 6" replace materials_unitofmeasure6 = "gallon" if materials_qty3 == "15 & 6" replace materials_kinds_raw3 = "oil" if materials_qty3 == "15 & 6" replace materials_qty3 = "15" if materials_qty3 == "15 & 6" replace materials_kinds_raw6 = "wood" if materials_qty1 == "50, 30" replace materials_qty6 = "30" if materials_qty1 == "50, 30" replace materials_unitofmeasure6 = "cds" if materials_qty1 == "50, 30" replace materials_kinds_raw1 = "coal" if materials_qty1 == "50, 30" replace materials_unitofmeasure1 = "t" if materials_qty1 == "50, 30" replace materials_qty1 = "50" if materials_qty1 == "50, 30" replace materials_qty1 = "7000" if materials_qty1 == "6000, 1000" replace materials_qty4 = "5100" if materials_qty4 == "1100, 4000" replace materials_unitofmeasure1 = "sides" if materials_qty1 == "1000, 44" replace materials_qty1 = "44" if materials_qty1 == "1000, 44" replace materials_qty1 = subinstr(materials_qty1, "P. V. ", "", .) if tag == 1 replace materials_qty1 = subinstr(materials_qty1, "P.V.", "", .) if tag == 1 replace materials_qty1 = subinstr(materials_qty1, "P.V ", "", .) if tag == 1 replace materials_qty1 = "15000" if materials_qty1 == "15m" replace materials_qty1 = "16000" if materials_qty1 == "16m" replace materials_qty1 = subinstr(materials_qty1, "C.", "", .) if tag == 1 replace materials_qty5 = "38" if materials_qty5 == "25_50" replace materials_kinds_raw2 = "wheat corn meal" if materials_qty1 == "700-100" replace materials_kinds_raw1 = "corn" if materials_qty1 == "700-100" replace materials_qty2 = "100" if materials_qty1 == "700-100" replace materials_qty1 = "700" if materials_qty1 == "700-100" replace materials_qty6 = "100" if materials_qty3 == "500 & 100" replace materials_kinds_raw6 = "maple" if materials_qty3 == "500 & 100" replace materials_kinds_raw3 = "pine" if materials_qty3 == "500 & 100" replace materials_qty3 = "500" if materials_qty3 == "500 & 100" replace materials_qty5 = "8" if materials_qty5 == "8 0r 10" replace materials_qty1 = "1750" if materials_qty1 == "500-3000" replace materials_qty1 = "212000000" if materials_qty1 == "21/2000000" replace materials_unitofmeasure1 = "ft" if materials_qty1 == "ft" replace materials_qty3 = "35" if materials_qty3 == "30-40" replace materials_qty3 = "50" if materials_qty3 == "40-60" replace materials_qty6 = "3590" if materials_qty6 == "3500 & 90" replace materials_qty7 = "105" if materials_qty7 == "30 & 75" replace materials_qty8 = "205" if materials_qty8 == "80 & 125" replace materials_qty3 = "42" if materials_qty3 == "22 + 20 " replace materials_unitofmeasure1 = "bush" if materials_qty1 == "bush" replace materials_unitofmeasure2 = "sides" if materials_qty2 == "100/200" replace materials_qty2 = "100" if materials_qty2 == "100/200" replace materials_qty1 = "800" if materials_qty1 == "800-80" replace materials_kinds_raw4 = "oats" if materials_qty2 == "500 & 100" replace materials_kinds_raw2 = "corn" if materials_qty2 == "500 & 100" replace materials_qty4 = "100" if materials_qty2 == "500 & 100" replace materials_unitofmeasure4 = "bush" if materials_qty2 == "500 & 100" replace materials_qty2 = "500" if materials_qty2 == "500 & 100" replace materials_qty3 = "54" if materials_qty3 == "50 &4" replace materials_qty3 = "0.375" if materials_qty3 == "0.125 & 0.25" replace materials_unitofmeasure3 = "ft" if materials_qty3 == "14 ft Full" replace materials_qty3 = "14" if materials_qty3 == "14 ft Full" drop tag *Materials value with a crosswalk merge 1:1 file_name firm_number using "${CMF_pipeline}/8_variable_cleaning/crosswalks/materials_value_1870.dta", keep(1 3) foreach var of varlist materials_value*{ replace `var'=new_`var' if _m==3 } drop new_materials_value* _m *Now add in the stuff from the value checking step merge 1:1 file_name firm_number using "${CMF_pipeline}/8_variable_cleaning/crosswalks/1870_crosswalk.dta", keep(1 3) nogen foreach var of varlist correct*{ if "`var'"=="correct_county"{ continue } di "`var'" local orig= subinstr("`var'","correct_","",.) di "`orig'" replace `orig'=`var' if `var'!="" replace `orig'="" if `orig'=="missing" drop `var' } merge 1:1 file_name firm_number using "${CMF_pipeline}/8_variable_cleaning/crosswalks/1870_crosswalk_large.dta", keep(1 3) nogen foreach var of varlist correct*{ if "`var'"=="correct_county"{ continue } di "`var'" local orig= subinstr("`var'","correct_","",.) di "`orig'" replace `orig'=`var' if `var'!="" replace `orig'="" if `orig'=="missing" drop `var' } save "${output}/1870_vars_cleaned.dta", replace *Now add numeric cleaning to the 1880 data use "${input_1880}/1880_general_schedule_standardized", clear merge 1:1 file_name firm_number using "${CMF_pipeline}/8_variable_cleaning/crosswalks/1880_crosswalk.dta", keep(1 3) nogen foreach var of varlist correct*{ if "`var'"=="correct_county"{ continue } di "`var'" local orig= subinstr("`var'","correct_","",.) di "`orig'" replace `orig'=`var' if `var'!="" replace `orig'="" if `orig'=="missing" drop `var' } merge 1:1 file_name firm_number using "${CMF_pipeline}/8_variable_cleaning/crosswalks/1880_crosswalk_large.dta", keep(1 3) nogen foreach var of varlist correct*{ if "`var'"=="correct_county"{ continue } di "`var'" local orig= subinstr("`var'","correct_","",.) di "`orig'" replace `orig'=`var' if `var'!="" replace `orig'="" if `orig'=="missing" drop `var' } save "${output}/1880_general_schedule_vars_cleaned.dta", replace *Now go through each of the special schedules foreach ss in SS1 SS2 SS3 SS4 SS5 SS6 SS7 SS8 SS9 SS10 SS11 SS12{ use "${input_1880}/1880_`ss'_standardized", clear if !inlist("`ss'","SS10","SS11"){ merge 1:1 file_name firm_number firm_name using "${CMF_pipeline}/8_variable_cleaning/crosswalks/1880_ss_crosswalk.dta", keep(1 3) nogen missings dropvars, force if "`ss'"=="SS5"{ rename correct_product_total_value correct_sawmill_prod_total_value } di "`ss'" foreach var of varlist correct*{ if "`var'"=="correct_county"{ continue } di "`var'" local orig= subinstr("`var'","correct_","",.) di "`orig'" replace `orig'=`var' if `var'!="" replace `orig'="" if `orig'=="missing" drop `var' } if !inlist("`ss'","SS2","SS3","SS5"){ merge 1:1 file_name firm_number using "${CMF_pipeline}/8_variable_cleaning/crosswalks/1880_crosswalk_large.dta", keep(1 3) nogen missings dropvars, force di "`ss'" foreach var of varlist correct*{ if "`var'"=="correct_county"{ continue } di "`var'" local orig= subinstr("`var'","correct_","",.) di "`orig'" replace `orig'=`var' if `var'!="" replace `orig'="" if `orig'=="missing" drop `var' } } } save "${output}/1880_`ss'_vars_cleaned.dta", replace }