Fix errors and bugs in the given code, without changing its intended functionality. ONLY return the fixed code and nothing else. Enclose the entire code in a single code block.



# Description =============================================================================================================
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
# Generate Input files for Stata
#
# Details:
# - This generate the input files containing BDS data by bins and real GDP by state in chained 2017 dollars.
#
# Computer: Apple MacOS M3 Ultra
# Language: R (v4.4.0)
#
# Date: Oct 12, 2024
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #

# SETUP -----------------------------------------------------------------------------------------------------------
rm(list=ls())
library(openxlsx)
library(tidyverse)
library(censusapi)
library(readxl)

setwd("~/OneDrive - National University of Singapore/Projects/Convergence/Code_local/Convergence_Establishment")

#naics
#,"empszfi","FAGE EMPSZES
variable_list = str_to_upper(c("JOB_CREATION","EMP","YEAR",
                               "EMPSZES",
                               "estabs_entry_rate", 
                               "estabs_exit_rate",
                               "job_creation",
                               "job_creation_births",
                               "job_creation_continuers",
                               "job_destruction",
                               "job_destruction_deaths",
                               "job_destruction_continuers",
                               "net_job_creation",
                               "firmdeath_emp",
                               "firm",
                               "estab",
                               "estabs_entry",
                               "estabs_exit",
                               "firmdeath_firms",
                               "firmdeath_estabs",
                               "job_creation_rate",
                               "job_creation_rate_births",
                               "job_destruction_rate",
                               "job_destruction_rate_deaths",
                               "net_job_creation_rate",
                               "reallocation_rate",
                               "denom"))

output = getCensus(
  name = 'timeseries/bds',
  key = 'ec7899b137456a03ace7c9e79cd8d8834007a0f3',
  vars = c(variable_list),
  region = "state:*"
)


# State GDP Data --------------------------------------------------------------------------------------------------------

real_gdp_by_state_63_23 <- read_excel("./data/gdp/spliced_real_gdp_63_23_chained_2017_millions.xlsx")

real_gdp_by_state_63_23 <- real_gdp_by_state_63_23 |>
  rename(state = GeoName) |>
  pivot_longer(
    cols = -c("statefips", "state"), names_to = "year", values_to = "real_gdp",
    names_transform = list(year = as.numeric)
  ) |> 
  mutate(real_gdp = real_gdp*1000000)

real_gdp_by_state <- real_gdp_by_state_63_23 |>
  filter(!state %in% c("United States","District of Columbia")) |>
  mutate(
    year = as.numeric(year),
  ) |>
  filter(year <= 2019, year >= 1963)

population_by_state <- read_dta("~/Library/CloudStorage/OneDrive-NationalUniversityofSingapore/Projects/Convergence/Code_local/Convergence_Establishment/data/state_pop.dta")

# Generate Data to push out ---------------------------------------------------------------------------------------

# bds_estab_out = output |> 
#   mutate(across(everything(),\(x) as.numeric(x))) |> 
#   rename(statefips = state,
#          year = YEAR,
#          emp = EMP,
#          estab = ESTAB,
#          empszes = EMPSZES) |> 
#   select(statefips, year, empszes, emp, estab) |> 
#   filter(empszes %in% c('212','220','230','240','249','254','260')) |> 
#   left_join(real_gdp_by_state |> 
#               select(statefips, year, real_gdp), by = c('statefips','year')) 

# write_csv(bds_estab_out, "./BDS/bds_estab_out.csv")

#  Prepare Rankings ---------------------------------------------------------------------------------------

real_gdp_by_state <- real_gdp_by_state |>
  left_join(population_by_state, by = c("statefips", "year")) |>
  filter(!state %in% c("United States", "District of Columbia")) |>
  mutate(
    real_gdp_per_capita = real_gdp / population,
    log_real_gdp_per_capita = log(real_gdp_per_capita)
  ) |>
  select(-c("population")) |>
  filter(year <= 2020, year >= 1963)

year_check = 1980

# Filter data for the respective year
data_year <- real_gdp_by_state |>
  filter(year == year_check) |>
  mutate(
    ranking_year = ifelse(rank(real_gdp_per_capita) <= n() / 2, "init_poor", "init_rich"),
    ranking_year_25_75 = case_when(
      rank(real_gdp_per_capita) <= n() / 4 ~ "init_poor_25",
      rank(real_gdp_per_capita) >= n() * 3 / 4 ~ "init_rich_75",
      T ~ "init_med_25_75"
    ),
    ranking_year_20_80 = case_when(
      rank(real_gdp_per_capita) <= n() * 2 / 10 ~ "init_poor_20",
      rank(real_gdp_per_capita) >= n() * 8 / 10 ~ "init_rich_80",
      T ~ "init_med_20_80"
    )
  )
# Merge the ranking back into the main dataset
real_gdp_by_state <- real_gdp_by_state |>
  left_join(data_year %>% select(state, ranking_year, ranking_year_25_75, ranking_year_20_80), by = "state")

bds_estab_out = output |> 
  mutate(across(everything(),\(x) as.numeric(x))) |> 
  rename(statefips = state,
         year = YEAR,
         emp = EMP,
         estab = ESTAB,
         empszes = EMPSZES) |> 
  select(statefips, year, empszes, emp, estab) |> 
  filter(empszes %in% c('212','220','230','240','249','254','260')) |> 
  left_join(real_gdp_by_state |> 
              select(statefips, state, year, real_gdp, real_gdp_per_capita, ranking_year, ranking_year_25_75, ranking_year_20_80), by = c('statefips','year')) |> 
  filter(year>=1978, year <=2019) |> 
  relocate(state, .after = statefips)


# Generate Empshare, Binfrac, Chen's Measure ----------------------------------------------------------------------

bds_estab_out <- bds_estab_out |> 
  group_by(state,year) |> 
  mutate(size_group = case_when(
    empszes %in% c(212,220,230) ~ "small",
    empszes %in% c(240) ~ "medium",
    empszes %in% c(249,254,260) ~ "large"
  )) |> 
  group_by(state, statefips, year, size_group, real_gdp, real_gdp_per_capita, ranking_year, ranking_year_25_75, ranking_year_20_80) |> 
  summarise(across(c("emp","estab"), \(x) sum(x, na.rm = T))) |> 
  group_by(state, year) |> 
  mutate(emp_share = emp/sum(emp, na.rm = TRUE),
         bin_frac = estab/sum(estab, na.rm = TRUE))


# - Construct similar graph of mean change for
# - Small empshare
# - Medium empshare
# - Large empshare
# - Chen's right tail measure