/* Purpose: Reshaping 1880 data */ if "`1'"==""{ global CMF_pipeline "../.." } global input "${CMF_pipeline}\2_string_cleaning\output" global output "${CMF_pipeline}\3_reshape\output" global intermediate "${CMF_pipeline}\3_reshape\interm" ******************************************************************************** *General Schedule* ******************************************************************************** use "$input/CMF_1880_S3.dta", clear *Drop useless observations drop if image_file_name=="" *Combine inconsistently named variables replace material_total_value=mat_total_value if mat_total_value!="" replace product_total_value=prod_total_value if prod_total_value!="" *Drop uneeded variables drop postoffice mat_total_value prod_total_value // There is one line with empty totals replace totals = "0" if totals == "" //Drop totals rows drop if totals != "0" //Drop two empty lines drop if estab_number_on_image == . //Fix two pairs of duplicates in terms of image_file_name estab_number_on_image estab_line replace estab_number_on_image = 18 if image_file_name == "dc_8_dist columbia_00009_r_L.jpg" /// & estab_number_on_image == 8 & estab_name == "Bensinger Samuel" drop if image_file_name == "MO_8_st louis city_00357_S.jpg" & estab_number_on_image == 24 /// & estab_name == "" //drop tag fix image_to_check file_group obs checked totals sort image_file_name estab_number_on_image estab_line replace estab_line = 1 if estab_line == 0 replace estab_line = estab_line[_n-1] +1 if estab_line == estab_line[_n-1] & image_file_name == image_file_name[_n-1] & estab_number_on_image == estab_number_on_image[_n-1] replace estab_line = estab_line[_n-1] +1 if estab_line != estab_line[_n-1] + 1 & image_file_name == image_file_name[_n-1] & estab_number_on_image == estab_number_on_image[_n-1] preserve keep image_file_name estab_number_on_image estab_line comment state post_office county /// id_number multiple_counties estab_name capital workers_adult_male workers_adult_female /// workers_children total_wages workers_max hours_may_nov hours_nov_may wage_daily_skilled /// wage_daily_unskilled months_fulltime months_34time months_23time months_12time months_idle /// material_total_value product_total_value industry industry_raw industry_freq maker shop manufacture factory save "$intermediate/S3_separated_vars.dta", replace restore drop comment state post_office county id_number multiple_counties estab_name capital /// workers_adult_male workers_adult_female workers_children total_wages workers_max /// hours_may_nov hours_nov_may wage_daily_skilled wage_daily_unskilled months_fulltime /// months_34time months_23time months_12time months_idle material_total_value /// product_total_value industry industry_raw industry_freq maker shop manufacture factory *Now make sure the image_file_name estab_number_on_image and estab_line are unique reshape wide p_water_river p_water_height p_water_wheel p_wat_wheel_kind p_wat_wheel_breadth /// p_wat_wheel_rpm p_wat_wheel_hp p_steam_boilers p_steam_engines p_steam_hp, /// i(image_file_name estab_number_on_image) j(estab_line) missings dropvars, force compress save "$output/general_schedule_reshaped.dta", replace use "$intermediate/S3_separated_vars.dta" replace estab_line= 1 if estab_number_on_image != estab_number_on_image[_n-1]& estab_line!=1 keep if estab_line == 1 drop estab_line compress drop if image_file_name =="NY_8_erie_00470_L.jpg" & estab_name == "" /// & estab_number_on_image == 1 save "$intermediate/S3_vars_merge.dta", replace use "$output/general_schedule_reshaped.dta" merge 1:1 image_file_name estab_number_on_image using "$intermediate/S3_vars_merge.dta" drop _merge rename (image_file_name estab_number_on_image estab_name) (file_name firm_number firm_name) *Fix encoding issue with some file_names replace file_name = ustrtrim(ustrnormalize(file_name, "nfd")) save, replace ******************************************************************************** *Special Schedule 1* ******************************************************************************** use "$input/CMF_1880_SS1.dta", clear preserve keep image_file_name estab_number_on_image estab_line county post_office state estab_name total_wages workers_adult_male workers_adult_female workers_children capital /// multiple_counties id_number comment workers_max hours_may_nov hours_nov_may wage_daily_skilled wage_daily_unskilled months_fulltime months_34time months_12time months_idle /// prod_misc_num_kind1 prod_misc_num_kind2 prod_misc_num_kind3 prod_misc_num_kind4 prod_misc_num_kind5 /// mat_value_lumber mat_value_iron_steel mat_value_other prod_corn_planters /// prod_cotton_planters prod_fertilizer_dist prod_grain_drills prod_grain_sowers prod_seed_sowers prod_transplanters prod_clod_crushers prod_cotton_choppers /// prod_cultivators prod_harrows prod_hoes_dozen prod_plows prod_shovels_dozen prod_rollers prod_corn_shuckers prod_fruit_gatherers prod_grain_cradles prod_harvesters /// prod_hand_rakes_dozen prod_hay_forks_dozen prod_hay_loaders_dozen prod_hay_tedders_dozen prod_horse_rakes prod_lawn_mowers prod_mowers prod_potato_diggers prod_reapers /// prod_reapers_mowers prod_scythes prod_scythes_snaths prod_sickles prod_clover_hullers prod_corn_huskers prod_corn_shellers prod_fanning_mills prod_separators prod_threshers /// prod_cane_mills prod_cider_wine_mills prod_feed_steamers_boilers prod_hay_straw_cutters prod_hay_presses prod_horsepowers prod_stalk_pullers prod_stone_gatherers prod_stump_pullers /// prod_syrup_evaporators prod_value_other material_total_value product_total_value drop if estab_name=="" save "$intermediate/SS1_separated_vars.dta", replace restore drop county post_office state estab_name total_wages workers_adult_male workers_adult_female workers_children capital /// multiple_counties id_number comment workers_max hours_may_nov hours_nov_may wage_daily_skilled wage_daily_unskilled months_fulltime months_34time months_12time months_idle /// mat_value_lumber mat_value_iron_steel mat_value_other prod_corn_planters /// prod_cotton_planters prod_fertilizer_dist prod_grain_drills prod_grain_sowers prod_seed_sowers prod_transplanters prod_clod_crushers prod_cotton_choppers /// prod_cultivators prod_harrows prod_hoes_dozen prod_plows prod_shovels_dozen prod_rollers prod_corn_shuckers prod_fruit_gatherers prod_grain_cradles prod_harvesters /// prod_hand_rakes_dozen prod_hay_forks_dozen prod_hay_loaders_dozen prod_hay_tedders_dozen prod_horse_rakes prod_lawn_mowers prod_mowers prod_potato_diggers prod_reapers /// prod_reapers_mowers prod_scythes prod_scythes_snaths prod_sickles prod_clover_hullers prod_corn_huskers prod_corn_shellers prod_fanning_mills prod_separators prod_threshers /// prod_cane_mills prod_cider_wine_mills prod_feed_steamers_boilers prod_hay_straw_cutters prod_hay_presses prod_horsepowers prod_stalk_pullers prod_stone_gatherers prod_stump_pullers /// prod_syrup_evaporators prod_value_other prod_misc_num_kind1 prod_misc_num_kind2 prod_misc_num_kind3 prod_misc_num_kind4 prod_misc_num_kind5 material_total_value product_total_value reshape wide p_water_river p_water_height p_water_wheel p_water_wheel_kind p_water_wheel_breadth p_water_wheel_rpm p_water_wheel_hp p_steam_boilers p_steam_engines p_steam_hp, i(image_file_name estab_number_on_image) j(estab_line) missings dropvars, force compress save "$output/SS1_reshaped.dta", replace use "$intermediate/SS1_separated_vars.dta" replace estab_line = 1 if estab_number_on_image != estab_number_on_image[_n-1]& estab_line != 1 keep if estab_line == 1 drop estab_line compress save "$intermediate/SS1_vars_merge.dta", replace use "$output/SS1_reshaped.dta" merge 1:1 image_file_name estab_number_on_image using"$intermediate/SS1_vars_merge.dta" drop _merge rename (image_file_name estab_number_on_image estab_name) (file_name firm_number firm_name) *Fix encoding issue with some file_names replace file_name = ustrtrim(ustrnormalize(file_name, "nfd")) save "$output/SS1_reshaped.dta", replace ******************************************************************************** *Special Schedule 2* ******************************************************************************** use "$input/CMF_1880_SS2.dta", clear //drop tag image_to_check new_tag //Deal with duplicate file_name, firm_number, firm_line *Need to come back to this foreach var of varlist p_*{ replace `var'= "" if `var' == "." | `var' == " " } duplicates drop sort image_file_name estab_number_on_image estab_line replace estab_line = 1 if estab_line == 0 replace estab_line = estab_line[_n-1] +1 if estab_line == estab_line[_n-1] & image_file_name == image_file_name[_n-1] & estab_number_on_image == estab_number_on_image[_n-1] replace estab_line = estab_line[_n-1] +1 if estab_line != estab_line[_n-1] + 1 & image_file_name == image_file_name[_n-1] & estab_number_on_image == estab_number_on_image[_n-1] //These establishments were entered twice with different names drop if image_file_name == "NJ_8_morris_00274_L.jpg" & estab_name == "Muir Josiah F." drop if image_file_name == "NJ_8_morris_00275_L.jpg" & estab_name == "Brunt Elarn" preserve keep image_file_name estab_number_on_image estab_line county state capital multiple_counties id_number comment /// estab_name total_wages workers_adult_male workers_adult_female workers_children workers_max hours_may_nov /// hours_nov_may wage_daily_unskilled months_fulltime months_34time months_12time months_idle wage_superintendent /// wage_machinist wage_machine_tender wage_rag_engineer wage_foreman_loft wage_foreman_fin wage_skilled_other /// eng_Kingsland eng_Jordan eng_Gould eng_name_num_other1 eng_name_num_other2 eng_name_num_other3 /// eng_name_num_other4 eng_name_num_other5 eng_name_num_other6 material_total_value product_total_value /// mat_rags mat_old_paper mat_cotton_waste mat_manilla_stock mat_straw mat_corn_stock mat_esparto_grass /// mat_poplar_wood mat_other_wood mat_value_chemical mat_value_other mat_value_pulp prod_printing_paper prod_writing_paper /// prod_wrapping_paper prod_binder_board prod_wallpaper prod_colored_paper prod_bank_note_paper prod_tissue_paper prod_other_paper save "$intermediate/SS2_separated_vars.dta", replace restore drop county state capital multiple_counties id_number comment /// estab_name total_wages workers_adult_male workers_adult_female workers_children workers_max hours_may_nov /// hours_nov_may wage_daily_unskilled months_fulltime months_34time months_12time months_idle wage_superintendent /// wage_machinist wage_machine_tender wage_rag_engineer wage_foreman_loft wage_foreman_fin wage_skilled_other /// eng_Kingsland eng_Jordan eng_Gould eng_name_num_other1 eng_name_num_other2 eng_name_num_other3 /// eng_name_num_other4 eng_name_num_other5 eng_name_num_other6 material_total_value product_total_value /// mat_rags mat_old_paper mat_cotton_waste mat_manilla_stock mat_straw mat_corn_stock mat_esparto_grass /// mat_poplar_wood mat_other_wood mat_value_chemical mat_value_other mat_value_pulp prod_printing_paper prod_writing_paper /// prod_wrapping_paper prod_binder_board prod_wallpaper prod_colored_paper prod_bank_note_paper prod_tissue_paper prod_other_paper line_id reshape wide p_water_river p_water_height p_water_wheel p_water_wheel_kind p_water_wheel_breadth /// p_water_wheel_rpm p_water_wheel_hp p_steam_boilers p_steam_engines p_steam_hp eng_washers_num /// eng_washers_bar_len eng_washers_roll_diam eng_washers_capacity eng_beaters_num eng_beaters_bar_len /// eng_beaters_roll_diam eng_beaters_capacity mach_Fourdrinier mach_Fourdrinier_width mach_cylinder /// mach_cylinder_width, i(image_file_name estab_number_on_image) j(estab_line) missings dropvars, force compress save "$output/SS2_reshaped.dta", replace use "$intermediate/SS2_separated_vars.dta" replace estab_line = 1 if estab_number_on_image != estab_number_on_image[_n-1]& estab_line != 1 keep if estab_line == 1 drop estab_line compress save "$intermediate/SS2_vars_merge.dta", replace use "$output/SS2_reshaped.dta" merge 1:1 image_file_name estab_number_on_image using"$intermediate/SS2_vars_merge.dta" drop _merge rename (image_file_name estab_number_on_image estab_name) (file_name firm_number firm_name) *Fix encoding issue with some file_names replace file_name = ustrtrim(ustrnormalize(file_name, "nfd")) sleep 5000 save, replace ******************************************************************************** *Special Schedule 3* ******************************************************************************** use "$input/CMF_1880_SS3.dta", clear preserve keep image_file_name estab_number_on_image estab_line post_office county state capital /// multiple_counties id_number comment estab_name total_wages workers_adult_male /// workers_adult_female workers_children workers_max hours_may_nov hours_nov_may /// wage_daily_skilled wage_daily_unskilled months_fulltime months_34time months_12time /// months_idle machines_sewing machines_pegging machines_nailing material_total_value /// product_total_value mat_sole_leather mat_upper_leather mat_other_leather /// mat_value_other prod_boots prod_value_boots prod_shoes prod_value_shoes prod_value_ns drop if estab_name=="" save "$intermediate/SS3_separated_vars.dta", replace restore drop post_office county state capital /// multiple_counties id_number comment estab_name total_wages workers_adult_male /// workers_adult_female workers_children workers_max hours_may_nov hours_nov_may /// wage_daily_skilled wage_daily_unskilled months_fulltime months_34time months_12time /// months_idle machines_sewing machines_pegging machines_nailing material_total_value /// product_total_value mat_sole_leather mat_upper_leather mat_other_leather /// mat_value_other prod_boots prod_value_boots prod_shoes prod_value_shoes prod_value_ns reshape wide p_water_river p_water_height p_water_wheel p_water_wheel_kind p_water_wheel_breadth p_water_wheel_rpm p_water_wheel_hp /// p_steam_boilers p_steam_engines p_steam_hp, i(image_file_name estab_number_on_image) j(estab_line) missings dropvars, force compress save "$output/SS3_reshaped.dta", replace use "$intermediate/SS3_separated_vars.dta" replace estab_line = 1 if estab_number_on_image != estab_number_on_image[_n-1]& estab_line != 1 keep if estab_line == 1 drop estab_line compress save "$intermediate/SS3_vars_merge.dta", replace use "$output/SS3_reshaped.dta" merge 1:1 image_file_name estab_number_on_image using "$intermediate/SS3_vars_merge.dta" drop _merge rename (image_file_name estab_number_on_image estab_name) (file_name firm_number firm_name) sort file_name firm_number *Fix encoding issue with some file_names replace file_name = ustrtrim(ustrnormalize(file_name, "nfd")) sleep 5000 save, replace ******************************************************************************** *Special Schedule 4* ******************************************************************************** use "$input/CMF_1880_SS4.dta", clear duplicates drop sort image_file_name estab_number_on_image estab_line replace estab_number_on_image = estab_number_on_image[_n-1] if estab_number_on_image == . replace estab_line = estab_line[_n-1] + 1 if estab_line == . sort image_file_name estab_number_on_image estab_line estab_name replace estab_number_on_image = estab_number_on_image[_n-1] + 1 if /// image_file_name == image_file_name[_n-1] & estab_name != "" replace estab_number_on_image = estab_number_on_image[_n-1] if image_file_name == image_file_name[_n-1] /// & estab_name == "" & estab_line != 1 sort image_file_name estab_number_on_image estab_line replace estab_line = 1 if estab_line == 0 replace estab_line = estab_line[_n-1] +1 if estab_line == estab_line[_n-1] & image_file_name == image_file_name[_n-1] & estab_number_on_image == estab_number_on_image[_n-1] replace estab_line = estab_line[_n-1] +1 if estab_line != estab_line[_n-1] + 1 & image_file_name == image_file_name[_n-1] & estab_number_on_image == estab_number_on_image[_n-1] preserve keep image_file_name estab_number_on_image estab_line county state capital multiple_counties /// id_number comment estab_name total_wages workers_adult_male workers_adult_female /// workers_children workers_max hours_may_nov hours_nov_may wage_daily_skilled /// wage_daily_unskilled months_fulltime months_34time months_12time months_idle /// tanning_mat_hemlock_bark tanning_mat_hemlock_bark_source tanning_mat_oak_bark /// tanning_mat_oak_bark_source tanning_mat_hides tanning_mat_skins tanning_mat_total_value /// tanning_prod_leather tanning_prod_skins tanning_prod_total_value currying_mat_leather /// currying_mat_skins currying_mat_oil currying_mat_total_value /// currying_prod_leather currying_prod_skins currying_prod_total_value save "$intermediate/SS4_separated_vars.dta", replace restore drop county state capital multiple_counties /// id_number comment estab_name total_wages workers_adult_male workers_adult_female /// workers_children workers_max hours_may_nov hours_nov_may wage_daily_skilled /// wage_daily_unskilled months_fulltime months_34time months_12time months_idle /// tanning_mat_hemlock_bark tanning_mat_hemlock_bark_source tanning_mat_oak_bark /// tanning_mat_oak_bark_source tanning_mat_hides tanning_mat_skins tanning_mat_total_value /// tanning_prod_leather tanning_prod_skins tanning_prod_total_value currying_mat_leather /// currying_mat_skins currying_mat_oil currying_mat_total_value /// currying_prod_leather currying_prod_skins currying_prod_total_value reshape wide p_water_river p_water_height p_water_wheel p_water_wheel_kind p_water_wheel_breadth /// p_water_wheel_rpm p_water_wheel_hp p_steam_boilers p_steam_engines p_steam_hp, i(image_file_name estab_number_on_image) j(estab_line) missings dropvars, force compress save "$output/SS4_reshaped.dta", replace use "$intermediate/SS4_separated_vars.dta" replace estab_line = 1 if estab_number_on_image != estab_number_on_image[_n-1]& estab_line != 1 keep if estab_line == 1 drop estab_line compress save "$intermediate/SS4_vars_merge.dta", replace use "$output/SS4_reshaped.dta" merge 1:1 image_file_name estab_number_on_image using"$intermediate/SS4_vars_merge.dta" rename (image_file_name estab_number_on_image estab_name) (file_name firm_number firm_name) *Fix encoding issue with some file_names replace file_name = ustrtrim(ustrnormalize(file_name, "nfd")) drop _merge sleep 5000 save, replace ******************************************************************************** *Special Schedule 5* ******************************************************************************** use "$input/CMF_1880_SS5.dta", clear //drop tag fix image_to_check obs seq image_file_group duplicates drop sort image_file_name estab_number_on_image estab_line replace estab_number_on_image = estab_number_on_image[_n-1] + 1 if /// image_file_name == image_file_name[_n-1] & estab_name != "" replace estab_number_on_image = estab_number_on_image[_n-1] if image_file_name == image_file_name[_n-1] /// & estab_name == "" & estab_line != 1 replace estab_line = estab_line[_n-1] + 1 if image_file_name == image_file_name[_n-1] /// & estab_number_on_image == estab_number_on_image[_n-1] & estab_name == "" replace estab_number_on_image = estab_number_on_image[_n-1] + 1 if /// image_file_name == image_file_name[_n-1] & estab_name != "" replace estab_number_on_image = estab_number_on_image[_n-1] if image_file_name == image_file_name[_n-1] /// & estab_name == "" & estab_line != 1 replace estab_number_on_image = estab_number_on_image[_n-1] + 1 if /// image_file_name == image_file_name[_n-1] & estab_name != "" preserve keep image_file_name estab_number_on_image estab_line county state capital multiple_counties /// id_number comment estab_name total_wages workers_adult_male workers_adult_female /// workers_children workers_max hours_may_nov hours_nov_may wage_daily_skilled /// wage_daily_unskilled months_fulltime months_34time months_12time months_idle /// saws_gangs saws_num_in_a_gang saws_circular saws_muley saws_band mat_value_logs /// mat_value_mill_supplies sawmill_prod_lumber sawmill_prod_laths sawmill_prod_shingles /// sawmill_prod_staves sawmill_prod_headings sawmill_prod_stock sawmill_prod_total_value /// sawmill_prod_value_other remanuf_cut remanuf_total_value remanuf_workers logs_source /// own_logging prop_logs_brought_in ship_in_own_vessel material_total_value save "$intermediate/SS5_separated_vars.dta", replace restore drop county state capital multiple_counties /// id_number comment estab_name total_wages workers_adult_male workers_adult_female /// workers_children workers_max hours_may_nov hours_nov_may wage_daily_skilled /// wage_daily_unskilled months_fulltime months_34time months_12time months_idle /// saws_gangs saws_num_in_a_gang saws_circular saws_muley saws_band mat_value_logs /// mat_value_mill_supplies sawmill_prod_lumber sawmill_prod_laths sawmill_prod_shingles /// sawmill_prod_staves sawmill_prod_headings sawmill_prod_stock sawmill_prod_total_value /// sawmill_prod_value_other remanuf_cut remanuf_total_value remanuf_workers logs_source /// own_logging prop_logs_brought_in ship_in_own_vessel material_total_value reshape wide p_water_river p_water_height p_water_wheel p_water_wheel_kind p_water_wheel_breadth /// p_water_wheel_rpm p_water_wheel_hp p_steam_boilers p_steam_engines p_steam_hp, /// i(image_file_name estab_number_on_image) j(estab_line) missings dropvars, force compress save "$output/SS5_reshaped.dta", replace use "$intermediate/SS5_separated_vars.dta" replace estab_line = 1 if estab_number_on_image != estab_number_on_image[_n-1] & estab_line != 1 keep if estab_line == 1 drop estab_line compress save "$intermediate/SS5_vars_merge.dta", replace use "$output/SS5_reshaped.dta" merge 1:1 image_file_name estab_number_on_image using"$intermediate/SS5_vars_merge.dta" drop _merge rename (image_file_name estab_number_on_image estab_name) (file_name firm_number firm_name) *Fix encoding issue with some file_names replace file_name = ustrtrim(ustrnormalize(file_name, "nfd")) sleep 5000 save, replace ******************************************************************************** *Special Schedule 6* ******************************************************************************** use "$input/CMF_1880_SS6.dta", clear duplicates drop sort image_file_name estab_number_on_image estab_line replace estab_number_on_image = estab_number_on_image[_n-1] + 1 if /// image_file_name == image_file_name[_n-1] & estab_name != "" replace estab_number_on_image = estab_number_on_image[_n-1] if image_file_name == image_file_name[_n-1] /// & estab_name == "" & estab_line != 1 replace estab_line = estab_line[_n-1] + 1 if image_file_name == image_file_name[_n-1] /// & estab_number_on_image == estab_number_on_image[_n-1] & estab_name == "" preserve keep image_file_name estab_number_on_image estab_line county state capital multiple_counties /// id_number comment post_office estab_name total_wages workers_adult_male workers_adult_female /// workers_children workers_max hours_may_nov hours_nov_may wage_daily_skilled wage_daily_unskilled /// months_fulltime months_34time months_12time months_idle material_total_value product_total_value /// mat_wood mat_value_other prod_common_brick prod_fire_brick prod_pressed_brick prod_tile_value /// prod_drain_pipe_value prod_value_other save "$intermediate/SS6_separated_vars.dta", replace restore drop county state capital multiple_counties /// id_number comment post_office estab_name total_wages workers_adult_male workers_adult_female /// workers_children workers_max hours_may_nov hours_nov_may wage_daily_skilled wage_daily_unskilled /// months_fulltime months_34time months_12time months_idle material_total_value product_total_value /// mat_wood mat_value_other prod_common_brick prod_fire_brick prod_pressed_brick prod_tile_value /// prod_drain_pipe_value prod_value_other reshape wide p_water_river p_water_height p_water_wheel p_water_wheel_kind /// p_water_wheel_breadth p_water_wheel_rpm p_water_wheel_hp p_steam_boilers p_steam_engines p_steam_hp /// , i(image_file_name estab_number_on_image) j(estab_line) missings dropvars, force compress save "$output/SS6_reshaped.dta", replace use "$intermediate/SS6_separated_vars.dta" replace estab_line = 1 if estab_number_on_image != estab_number_on_image[_n-1]& estab_line != 1 keep if estab_line == 1 drop estab_line compress save "$intermediate/SS6_vars_merge.dta", replace use "$output/SS6_reshaped.dta" merge 1:1 image_file_name estab_number_on_image using"$intermediate/SS6_vars_merge.dta" drop _merge rename (image_file_name estab_number_on_image estab_name) (file_name firm_number firm_name) *Fix encoding issue with some file_names replace file_name = ustrtrim(ustrnormalize(file_name, "nfd")) sleep 5000 save, replace ******************************************************************************** *Special Schedule 7* ******************************************************************************** use "$input/CMF_1880_SS7.dta", clear duplicates drop replace estab_number_on_image = estab_number_on_image[_n-1] if estab_number_on_image == . replace estab_line = estab_line[_n-1] + 1 if estab_line == . sort image_file_name estab_number_on_image estab_line replace estab_number_on_image = estab_number_on_image[_n-1] + 1 if /// image_file_name == image_file_name[_n-1] & estab_name != "" replace estab_number_on_image = estab_number_on_image[_n-1] if image_file_name == image_file_name[_n-1] /// & estab_name == "" & estab_line != 1 replace estab_line = estab_line[_n-1] + 1 if image_file_name == image_file_name[_n-1] /// & estab_number_on_image == estab_number_on_image[_n-1] & estab_name == "" preserve keep image_file_name estab_number_on_image estab_line county state post_office capital /// multiple_counties id_number comment estab_name total_wages workers_adult_male workers_adult_female /// workers_children workers_max hours_may_nov hours_nov_may wage_daily_skilled wage_daily_unskilled /// months_fulltime months_34time months_12time months_idle custom_or_market mat_wheat mat_value_wheat /// mat_other_grain mat_value_grain mat_value_mill_supplies prod_bbls_wheat prod_bbls_rye_flour /// prod_lbs_buckwheat_flour prod_lbs_barley_meal prod_lbs_corn_meal prod_lbs_feed prod_lbs_hominy /// prod_value_other material_total_value product_total_value save "$intermediate/SS7_separated_vars.dta", replace restore drop county state post_office capital multiple_counties id_number comment estab_name total_wages /// workers_adult_male workers_adult_female workers_children workers_max hours_may_nov hours_nov_may /// wage_daily_skilled wage_daily_unskilled months_fulltime months_34time months_12time /// months_idle custom_or_market mat_wheat mat_value_wheat mat_other_grain mat_value_grain /// mat_value_mill_supplies prod_bbls_wheat prod_bbls_rye_flour prod_lbs_buckwheat_flour /// prod_lbs_barley_meal prod_lbs_corn_meal prod_lbs_feed prod_lbs_hominy /// prod_value_other material_total_value product_total_value reshape wide p_water_river p_water_height p_water_wheel p_water_wheel_kind p_water_wheel_breadth /// p_water_wheel_rpm p_water_wheel_hp p_steam_boilers p_steam_engines p_steam_hp run_stone_number /// max_capacity_per_day elevator_capacity, i(image_file_name estab_number_on_image) j(estab_line) missings dropvars, force compress save "$output/SS7_reshaped.dta", replace use "$intermediate/SS7_separated_vars.dta" keep if estab_line == 1 drop estab_line drop if image_file_name == "NY_8_washington_00920_L.jpg" & estab_name == "" compress save "$intermediate/SS7_vars_merge.dta", replace use "$output/SS7_reshaped.dta" merge 1:1 image_file_name estab_number_on_image using"$intermediate/SS7_vars_merge.dta" drop _merge rename (image_file_name estab_number_on_image estab_name) (file_name firm_number firm_name) *Fix encoding issue with some file_names replace file_name = ustrtrim(ustrnormalize(file_name, "nfd")) sleep 5000 save, replace ******************************************************************************** *Special Schedule 8* ******************************************************************************** use "$input/CMF_1880_SS8.dta", clear //drop tag image_to_check //Deal with duplicate file_name, firm_number, firm_line *Come back to this later duplicates drop replace estab_number_on_image = estab_number_on_image[_n-1] if estab_number_on_image == . replace estab_line = estab_line[_n-1] + 1 if estab_line == . sort image_file_name estab_number_on_image estab_line replace estab_number_on_image = estab_number_on_image[_n-1] + 1 if /// image_file_name == image_file_name[_n-1] & estab_name != "" replace estab_number_on_image = estab_number_on_image[_n-1] if image_file_name == image_file_name[_n-1] /// & estab_name == "" & estab_line != 1 replace estab_line = estab_line[_n-1] + 1 if image_file_name == image_file_name[_n-1] /// & estab_number_on_image == estab_number_on_image[_n-1] & estab_name == "" replace estab_number_on_image = estab_number_on_image[_n-1] + 1 if /// image_file_name == image_file_name[_n-1] & estab_name != "" replace estab_number_on_image = estab_number_on_image[_n-1] if image_file_name == image_file_name[_n-1] /// & estab_name == "" & estab_line != 1 preserve keep image_file_name estab_number_on_image estab_line county state capital multiple_counties /// id_number comment post_office estab_name total_wages workers_adult_male /// workers_adult_female workers_children workers_max wage_daily_skilled wage_daily_unskilled /// total_annual_labor_value manuf_open_date manuf_end_date product_kind_clean1 product_kind_attribute1 /// product_kind_clean2 cows_num milk_cost milk_used cheese_made cheese_kinds /// cheese_milk_used cheese_sale_price cheese_cost cheese_furnish_cost butter_made butter_milk_used /// butter_price butter_cost butter_furnish_cost butter_buttermilk_skim_value /// cb_butter_made cb_cheese_made cb_butter_milk_used cb_cheese_milk_used /// cb_butter_sale_price cb_cheese_sale_price cb_butter_cost cb_cheese_cost /// cb_butter_furnish_cost cb_cheese_furnish_cost cb_buttermilk_skim_value condensed_milk_lbs /// condensed_milk_value cheesefackindsofcheese_clean product_enumerator_notes /// product_misc product_service product_is_units product_unsure product_freq product_comments /// product_freq_two save "$intermediate/SS8_separated_vars.dta", replace restore drop county state capital multiple_counties /// id_number comment post_office estab_name total_wages workers_adult_male /// workers_adult_female workers_children workers_max wage_daily_skilled wage_daily_unskilled /// total_annual_labor_value manuf_open_date manuf_end_date product_kind_clean1 product_kind_attribute1 /// product_kind_clean2 cows_num milk_cost milk_used cheese_made cheese_kinds /// cheese_milk_used cheese_sale_price cheese_cost cheese_furnish_cost butter_made butter_milk_used /// butter_price butter_cost butter_furnish_cost butter_buttermilk_skim_value /// cb_butter_made cb_cheese_made cb_butter_milk_used cb_cheese_milk_used /// cb_butter_sale_price cb_cheese_sale_price cb_butter_cost cb_cheese_cost /// cb_butter_furnish_cost cb_cheese_furnish_cost cb_buttermilk_skim_value condensed_milk_lbs /// condensed_milk_value cheesefackindsofcheese_clean product_enumerator_notes /// product_misc product_service product_is_units product_unsure product_freq product_comments /// product_freq_two reshape wide p_water_river p_water_height p_water_wheel p_water_wheel_kind p_water_wheel_breadth /// p_water_wheel_rpm p_water_wheel_hp p_steam_boilers p_steam_engines p_steam_hp, /// i(image_file_name estab_number_on_image) j(estab_line) missings dropvars, force compress save "$output/SS8_reshaped.dta", replace use "$intermediate/SS8_separated_vars.dta" replace estab_line = 1 if estab_number_on_image != estab_number_on_image[_n-1]& estab_line != 1 keep if estab_line == 1 drop estab_line compress save "$intermediate/SS8_vars_merge.dta", replace use "$output/SS8_reshaped.dta" merge 1:1 image_file_name estab_number_on_image using"$intermediate/SS8_vars_merge.dta" drop _merge rename (image_file_name estab_number_on_image estab_name) (file_name firm_number firm_name) *Fix encoding issue with some file_names replace file_name = ustrtrim(ustrnormalize(file_name, "nfd")) sleep 5000 save, replace ******************************************************************************** *Special Schedule 9* ******************************************************************************** use "$input/CMF_1880_SS9.dta", clear //drop tag image_to_check set sortrngstate 2025 //Deal with duplicate file_name, firm_number, firm_line *Come back to this later duplicates drop replace estab_number_on_image = estab_number_on_image[_n-1] if estab_number_on_image == . replace estab_line = estab_line[_n-1] + 1 if estab_line == . sort image_file_name estab_number_on_image estab_line replace estab_number_on_image = estab_number_on_image[_n-1] + 1 if /// image_file_name == image_file_name[_n-1] & estab_name != "" replace estab_number_on_image = estab_number_on_image[_n-1] if image_file_name == image_file_name[_n-1] /// & estab_name == "" & estab_line != 1 replace estab_line = estab_line[_n-1] + 1 if image_file_name == image_file_name[_n-1] /// & estab_number_on_image == estab_number_on_image[_n-1] & estab_name == "" preserve keep image_file_name estab_number_on_image estab_line county state capital multiple_counties /// id_number comment post_office estab_name total_wages workers_adult_male /// workers_children workers_max hours_may_nov hours_nov_may wage_daily_skilled /// wage_daily_unskilled months_fulltime months_34time months_12time months_idle /// material_total_value product_total_value mat_beeves mat_beeves_weight mat_beeves_value /// mat_sheep mat_sheep_weight mat_sheep_value mat_hogs mat_hogs_weight mat_hogs_value /// mat_total_animal_value mat_total_other_value prod_beef_fresh prod_beef_canned /// prod_beef_salted prod_mutton_fresh prod_pork_fresh prod_pork_salted prod_bacon_ham /// prod_lard prod_value_other save "$intermediate/SS9_separated_vars.dta", replace restore drop county state capital multiple_counties /// id_number comment post_office estab_name total_wages workers_adult_male /// workers_children workers_max hours_may_nov hours_nov_may wage_daily_skilled /// wage_daily_unskilled months_fulltime months_34time months_12time months_idle /// material_total_value product_total_value mat_beeves mat_beeves_weight mat_beeves_value /// mat_sheep mat_sheep_weight mat_sheep_value mat_hogs mat_hogs_weight mat_hogs_value /// mat_total_animal_value mat_total_other_value prod_beef_fresh prod_beef_canned /// prod_beef_salted prod_mutton_fresh prod_pork_fresh prod_pork_salted prod_bacon_ham /// prod_lard prod_value_other reshape wide p_water_river p_water_height p_water_wheel p_water_wheel_kind /// p_water_wheel_breadth p_water_wheel_rpm p_water_wheel_hp p_steam_boilers /// p_steam_engines p_steam_hp, i(image_file_name estab_number_on_image) j(estab_line) missings dropvars, force compress save "$output/SS9_reshaped.dta", replace use "$intermediate/SS9_separated_vars.dta" replace estab_line = 1 if estab_number_on_image != estab_number_on_image[_n-1]& estab_line != 1 keep if estab_line == 1 drop estab_line compress save "$intermediate/SS9_vars_merge.dta", replace use "$output/SS9_reshaped.dta" merge 1:1 image_file_name estab_number_on_image using"$intermediate/SS9_vars_merge.dta" drop _merge rename (image_file_name estab_number_on_image estab_name) (file_name firm_number firm_name) *Fix encoding issue with some file_names replace file_name = ustrtrim(ustrnormalize(file_name, "nfd")) sleep 5000 save, replace ******************************************************************************** *Special Schedule 10* ******************************************************************************** use "$input/CMF_1880_SS10.dta", clear //Drop totals drop totals //Convert firm_number and firm_line to numbers destring estab_number_on_image, replace destring estab_line, replace //Save all current variable names to label foreach var of varlist _all{ label variable `var' "`var'" } *Now fix some special cases replace estab_number_on_image=1 if image_file_name=="NE_1880_RookCo_Nos9and10_001" & solar_vats=="11" replace estab_number_on_image=2 if image_file_name=="NE_1880_RookCo_Nos9and10_001" & solar_vats=="2" replace estab_line=1 if image_file_name=="NE_1880_RookCo_Nos9and10_001" & solar_vats=="11" replace estab_line=1 if image_file_name=="NE_1880_RookCo_Nos9and10_001" & solar_vats=="2" replace estab_number_on_image=1 if image_file_name=="NE_1880_NyeCo_Nos9and10_003" & solar_agg_area=="30" replace estab_number_on_image=2 if image_file_name=="NE_1880_NyeCo_Nos9and10_003" & solar_agg_area=="100" replace estab_line=1 if image_file_name=="NE_1880_NyeCo_Nos9and10_003" & solar_agg_area=="30" replace estab_line=1 if image_file_name=="NE_1880_NyeCo_Nos9and10_003" & solar_agg_area=="100" replace estab_number_on_image=1 if image_file_name=="LA_8_iberia_00091_r_L.jpg" replace estab_line=1 if image_file_name=="LA_8_iberia_00091_r_L.jpg" replace estab_number_on_image=1 if image_file_name=="NE_1880_HumboldtCo_Nos9and10_002" replace estab_line=1 if image_file_name=="NE_1880_HumboldtCo_Nos9and10_002" sort image_file_name estab_number_on_image estab_line replace estab_line = 1 if estab_line == 0 replace estab_line = estab_line[_n-1] +1 if estab_line == estab_line[_n-1] & image_file_name == image_file_name[_n-1] & estab_number_on_image == estab_number_on_image[_n-1] replace estab_line = estab_line[_n-1] +1 if estab_line != estab_line[_n-1] + 1 & image_file_name == image_file_name[_n-1] & estab_number_on_image == estab_number_on_image[_n-1] preserve keep image_file_name estab_number_on_image estab_line county state capital multiple_counties id_number comment estab_name total_wages workers_adult_male workers_adult_female /// workers_children workers_max hours_may_nov hours_nov_may wage_daily_skilled wage_daily_unskilled p_water_river p_water_height p_water_wheel p_water_wheel_kind p_water_wheel_breadth /// p_water_wheel_rpm p_water_wheel_hp p_steam_boilers p_steam_engines p_steam_hp months_fulltime months_34time months_12time months_idle save "$intermediate/SS10_separated_vars.dta", replace restore drop county state capital multiple_counties id_number comment estab_name total_wages workers_adult_male workers_adult_female /// workers_children workers_max hours_may_nov hours_nov_may wage_daily_skilled wage_daily_unskilled p_water_river p_water_height p_water_wheel p_water_wheel_kind p_water_wheel_breadth /// p_water_wheel_rpm p_water_wheel_hp p_steam_boilers p_steam_engines p_steam_hp months_fulltime months_34time months_12time months_idle reshape wide product_total_value boiling_blocks boiling_boilers boiling_boiler_capacity boiling_kettles boiling_kettle_capacity boiling_pans boiling_pan_capacity /// boiling_mat_coal boiling_mat_coal_value boiling_mat_wood boiling_mat_wood_value boiling_mat_value_other /// boiling_mat_total_value solar_vats solar_agg_area solar_mat_total_value prod_salt, i(image_file_name estab_number_on_image) j(estab_line) missings dropvars, force compress save "$output/SS10_reshaped.dta", replace use "$intermediate/SS10_separated_vars.dta" replace estab_line = 1 if estab_number_on_image != estab_number_on_image[_n-1]& estab_line != 1 keep if estab_line == 1 drop estab_line compress save "$intermediate/SS10_vars_merge.dta", replace use "$output/SS10_reshaped.dta" merge 1:1 image_file_name estab_number_on_image using"$intermediate/SS10_vars_merge.dta" drop _merge rename (image_file_name estab_number_on_image estab_name) (file_name firm_number firm_name) *Fix encoding issue with some file_names replace file_name = ustrtrim(ustrnormalize(file_name, "nfd")) sleep 5000 save, replace ******************************************************************************** *Special Schedule 11* ******************************************************************************** use "$input/CMF_1880_SS11.dta", clear drop totals //Convert firm_number and firm_line to numbers destring estab_number_on_image, replace destring estab_line, replace //Fix the estab numbers and lines replace estab_number_on_image=1 if image_file_name=="PA_8_chester_00233_L.jpg" replace estab_line=1 if image_file_name=="PA_8_chester_00233_L.jpg" replace estab_number_on_image=1 if image_file_name=="PA_8_westmoreland_00714_L.jpg" & estab_name=="Enelt Jacob" replace estab_line=1 if image_file_name=="PA_8_westmoreland_00714_L.jpg" & estab_name=="Enelt Jacob" replace estab_number_on_image=2 if image_file_name=="PA_8_westmoreland_00714_L.jpg" & estab_name=="David Smith" replace estab_line=1 if image_file_name=="PA_8_westmoreland_00714_L.jpg" & estab_name=="David Smith" //Save all current variable names to label foreach var of varlist _all{ label variable `var' "`var'" } drop estab_line rename (image_file_name estab_number_on_image estab_name) (file_name firm_number firm_name) *Fix encoding issue with some file_names replace file_name = ustrtrim(ustrnormalize(file_name, "nfd")) *Fix firm number that is mysteriously wrong replace firm_number=5 if file_name=="NC_8_person_00420_L.jpg" & firm_number==6 replace firm_number=1 if file_name =="NC_8_surry_00742_L.jpg" & firm_number==2 replace firm_number=1 if file_name == "NE_1880_WashoeCo_Sched3_010" & firm_number==9 sleep 5000 save "$output/SS11_reshaped.dta", replace ******************************************************************************** *Special Schedule 12* ******************************************************************************** use "$input/CMF_1880_SS12.dta", clear //drop tag image_to_check drop product_kind_attribute1 product_kind_clean1 product_freq2 preserve keep image_file_name estab_number_on_image estab_line county state capital multiple_counties /// id_number comment post_office estab_name total_wages workers_adult_male workers_children workers_max /// hours_may_nov hours_nov_may wage_daily_skilled wage_daily_unskilled months_fulltime months_34time /// months_12time months_idle mach_num_kind1 mach_num_kind2 mach_num_kind3 mach_num_kind4 /// mach_num_kind5 mach_num_kind6 material_total_value product_total_value kindofrockquarried_clean /// product_kind_clean product_kind_attribute product_enumerator_notes product_misc product_service /// product_is_units product_unsure product_freq product_comments product_freq_two quarry_open_year /// area_excavated rock_kind rock_dressed principal_market transportation_mode steam_power_drainage /// persons_killed persons_perm_disabled persons_temp_disabled mach_steam_drill mach_steam_crane /// mat_coal mat_wood prod_marketed drop if estab_name=="" save "$intermediate/SS12_separated_vars.dta", replace restore drop county state capital multiple_counties /// id_number comment post_office estab_name total_wages workers_adult_male workers_children workers_max /// hours_may_nov hours_nov_may wage_daily_skilled wage_daily_unskilled months_fulltime months_34time /// months_12time months_idle mach_num_kind1 mach_num_kind2 mach_num_kind3 mach_num_kind4 /// mach_num_kind5 mach_num_kind6 material_total_value product_total_value kindofrockquarried_clean /// product_kind_clean product_kind_attribute product_enumerator_notes product_misc product_service /// product_is_units product_unsure product_freq product_comments product_freq_two quarry_open_year /// area_excavated rock_kind rock_dressed principal_market transportation_mode steam_power_drainage /// persons_killed persons_perm_disabled persons_temp_disabled mach_steam_drill mach_steam_crane /// mat_coal mat_wood prod_marketed reshape wide p_water_river p_water_height p_water_wheel p_water_wheel_kind p_water_wheel_breadth p_water_wheel_rpm /// p_water_wheel_hp p_steam_boilers p_steam_engines p_steam_hp, i(image_file_name estab_number_on_image) j(estab_line) missings dropvars, force compress save "$output/SS12_reshaped.dta", replace use "$intermediate/SS12_separated_vars.dta" replace estab_line = 1 if estab_number_on_image != estab_number_on_image[_n-1]& estab_line != 1 keep if estab_line == 1 drop estab_line compress save "$intermediate/SS12_vars_merge.dta", replace use "$output/SS12_reshaped.dta" merge 1:1 image_file_name estab_number_on_image using"$intermediate/SS12_vars_merge.dta" drop _merge rename (image_file_name estab_number_on_image estab_name) (file_name firm_number firm_name) *Fix encoding issue with some file_names replace file_name = ustrtrim(ustrnormalize(file_name, "nfd")) sleep 5000 save, replace