# Prep historical racial regimes data
# The data aggregation is done in R, but the factors are computed in Stata

# Bring in MSAs
msas = read.csv('raw_data/msas/list1_2020.csv')
msas$msa = str_pad(msas$CBSA.Code,width = 5,pad = '0')
msas$mettype = str_replace(msas$Metropolitan.Micropolitan.Statistical.Area,pattern = ' Statistical Area','')

msas$st = str_pad(msas$FIPS.State.Code,width = 2,pad = '0')
msas$cnty = str_pad(msas$FIPS.County.Code, width = 3, pad = '0')
msas = filter(msas, mettype != '', st != '72')
msas = select(msas, st,cnty,mettype, msa)


# Bring in historical county crosswalk
county_hist = read_csv('raw_data/county_hist/eglp_county_crosswalk_endyr_2010.csv')
county_hist_1930 = filter(county_hist, Year == 1930)
county_hist_1860 = filter(county_hist, Year == 1860)


# Bring in slavery data
slavery_raw = read_csv(paste0('raw_data/nhgis_racial_oppression/',nhgis_slavery_name,'_csv/',nhgis_slavery_name,'_ds14_1860_county.csv'))

slavery = left_join(slavery_raw, county_hist_1860, by = c('STATEA' = 'NHGISST','COUNTYA' = 'NHGISCTY'))

slavery = mutate(slavery, 
                 st = str_pad(NHGISST_2010,3,pad = '0') %>% substr(1,2),
                 cnty = NHGISCTY_2010,
                 pop_slave_1860 = weight * AH3003,
                 pop_tot_1860 = weight * AG3001)

slavery = slavery %>% group_by(st,cnty) %>% summarise(
  pop_slave_1860 = sum(pop_slave_1860, na.rm = T),
  pop_tot_1860 = sum(pop_tot_1860, na.rm = T)
)

slavery_st = slavery %>% group_by(st) %>% summarise(
  pop_slave_1860 = sum(pop_slave_1860, na.rm = T),
  pop_tot_1860 = sum(pop_tot_1860, na.rm = T)
)


# Now bring in sharecropping
sharec_raw = read_csv(paste0('raw_data/nhgis_racial_oppression/',nhgis_sharecrop_name,'_csv/',nhgis_sharecrop_name,'_ds212_1930_county.csv'))
sharec = left_join(sharec_raw, county_hist_1930, by = c('STATEA' = 'NHGISST','COUNTYA' = 'NHGISCTY'))
sharec = mutate(sharec, 
                 st = str_pad(NHGISST_2010,3,pad = '0') %>% substr(1,2),
                 cnty = NHGISCTY_2010,
                 cropfarms_tot = weight * AB96008 + weight * AB96016,
                 cropfarms_black =  weight * AB96016)
sharec = sharec %>% group_by(st,cnty) %>% summarise(
  cropfarms_tot = sum(cropfarms_tot, na.rm = T),
  cropfarms_black = sum(cropfarms_black, na.rm = T)
)

sharec_st = sharec %>% group_by(st) %>% summarise(
  cropfarms_tot = sum(cropfarms_tot, na.rm = T),
  cropfarms_black = sum(cropfarms_black, na.rm = T)
)


racial_oppr = full_join(slavery, sharec, by = c('st','cnty'))

racial_oppr = left_join(racial_oppr, msas, by = c('st','cnty'))

racial_oppr_st = left_join(slavery_st, sharec_st, by = 'st')


# Merge on MSA definitions
racial_oppr = racial_oppr %>% mutate(msafull = case_when(mettype == 'Metropolitan' ~ msa,
                                      mettype == 'Micropolitan' ~ paste('nm',st,sep = '_'),
                                      is.na(mettype) ~ paste('nm',st,sep = '_'))) %>% select(-msa) %>% rename(msa = msafull)


# Clean up sharecropping and slavery data at county level
racial_oppr = mutate(racial_oppr, 
                    pop_slave_1860 = case_when(
                      !(st %in% c('11','17','18','19','20','35','36','39','40','42')) ~ pop_slave_1860, # Nebraska, New Jersey, Utah are correct. DC and Oklahoma are not in data so should drop entirely
                      (st %in% c('11','17','18','19','20','35','36','39','40','42')) ~ 0
                      ),
                    pop_tot_1860 = case_when(
                    !(st %in% c('11','40')) ~ pop_tot_1860, # DC and Oklahoma are not in data so should drop entirely
                    (st %in% c('11','40')) ~  0
                      ),
                    cropfarms_tot = case_when(
                      st %in% c('01','05','10','11','12','13','21','22','24','28','37','40','45','47','48','51','54') ~ cropfarms_tot,
                      !(st %in% c('01','05','10','11','12','13','21','22','24','28','37','40','45','47','48','51','54')) ~ 0
                    ),
                    
                    cropfarms_black = case_when(
  st %in% c('01','05','10','11','12','13','21','22','24','28','37','40','45','47','48','51','54') ~ cropfarms_black,
  !(st %in% c('01','05','10','11','12','13','21','22','24','28','37','40','45','47','48','51','54')) ~ 0
                    )


)


# Clean up sharecropping and slavery data at state level
racial_oppr_st = mutate(racial_oppr_st, 
                     pop_slave_1860 = case_when(
                       !(st %in% c('11','17','18','19','20','35','36','39','40','42')) ~ pop_slave_1860, # Nebraska, New Jersey, Utah are correct. DC and Oklahoma are not in data so should drop entirely
                       (st %in% c('11','17','18','19','20','35','36','39','40','42')) ~ 0
                     ),
                     pop_tot_1860 = case_when(
                       !(st %in% c('11','40')) ~ pop_tot_1860, # DC and Oklahoma are not in data so should drop entirely
                       (st %in% c('11','40')) ~  0
                     ),
                     cropfarms_tot = case_when(
                       st %in% c('01','05','10','11','12','13','21','22','24','28','37','40','45','47','48','51','54') ~ cropfarms_tot,
                       !(st %in% c('01','05','10','11','12','13','21','22','24','28','37','40','45','47','48','51','54')) ~ 0
                     ),
                     cropfarms_black = case_when(
                       st %in% c('01','05','10','11','12','13','21','22','24','28','37','40','45','47','48','51','54') ~ cropfarms_black,
                       !(st %in% c('01','05','10','11','12','13','21','22','24','28','37','40','45','47','48','51','54')) ~ 0
                     )
                     
                     
)


# Get full population of black and white by msa
drom = racial_oppr %>% group_by(msa) %>% summarise(
  pop_slave_1860 = sum(pop_slave_1860, na.rm = T),
  pop_tot_1860 = sum(pop_tot_1860, na.rm = T),
  slave_pct = case_when(
    pop_tot_1860 > 0 ~ pop_slave_1860 / pop_tot_1860,
    pop_tot_1860 == 0 ~ 0),
  cropfarms_tot = sum(cropfarms_tot, na.rm = T),
  cropfarms_black = sum(cropfarms_black, na.rm = T),  
  cropfarms_pctblack = case_when( 
    cropfarms_tot > 0 ~ cropfarms_black/cropfarms_tot,
    cropfarms_tot == 0 ~ 0,
  )
)



# Bring in existing HRR from Baker
stxw = read_csv('raw_data/msa_to_state_xwalk/msa_to_states_xwalk.csv') %>% mutate(st = str_pad(st, 2,pad = '0')) 
bakerhrr = read_csv('raw_data/baker_hrr/hrr_baker.csv') %>% mutate(st = str_pad(st, 2,pad = '0')) %>% rename(hrrbaker= hrr)
bakerhrr = left_join(stxw, bakerhrr, by = 'st')
bakerhrr$hrrbaker = replace(bakerhrr$hrrbaker, is.na(bakerhrr$hrrbaker), -1.75) # Places outside the South get minimum possible value

bakhrr = bakerhrr %>% select(-state)
bakhrr[is.na(bakhrr)] = 0 # States outside the south get 0 for the state-level components

# Merge Baker results onto main data
drom = left_join(drom, bakhrr, by = c('msa'))
drom = filter(drom, msa != 'nm_NA', msa != 'nc_NA')


# Output metro-level data
write_csv(drom, 'intermediate_data/predictors/racialoppr_msa.csv')


# Now do state-level data
stbaker = filter(bakerhrr, !duplicated(st)) %>% select(-region,-state)
stbaker[is.na(stbaker)] = 0

racial_oppr_st = left_join(racial_oppr_st, stbaker, by = 'st')

racial_oppr_st = mutate(racial_oppr_st, 
                      slave_pct = case_when(
                        pop_tot_1860 > 0 ~ pop_slave_1860 / pop_tot_1860,
                        pop_tot_1860 == 0 ~ 0),
                      cropfarms_pctblack = case_when( # Using 1 instead of 0 because of small slices that get caught
                        cropfarms_tot > 0 ~ cropfarms_black/cropfarms_tot,
                        cropfarms_tot == 0 ~ 0
                        )
)

racial_oppr_st = filter(racial_oppr_st, !is.na(st), st != '11')

# Output
write_csv(racial_oppr_st, 'intermediate_data/predictors/racialoppr_st.csv')


