Untitled

############################################
# Replicate Table 1B (Panel B): Annual sample firm characteristics
# Self-contained script (fresh R session)
#
# Key choices:
# - Close-election cycles: [-1, 0, +1, +2]
# - Merge by firm HQ state and fiscal year
# - Annual Compustat sample 1976-2021
# - Require nonmissing R&D expense and annual controls
# - Require AT >= 5 ($5m cutoff; AT is in millions)
# - Compute HHI and HHI^2 from 3-digit SIC-year sales shares
# - Exclude utilities (4900-4999) and financials (6000-6999)
# - No winsorization for Table 1B summary (matched published SDs better)
############################################

# -----------------------------
# Package checks
# -----------------------------
stopifnot(requireNamespace("readxl", quietly = TRUE))
stopifnot(requireNamespace("dplyr", quietly = TRUE))
stopifnot(requireNamespace("tidyr", quietly = TRUE))
stopifnot(requireNamespace("stringr", quietly = TRUE))
stopifnot(requireNamespace("tibble", quietly = TRUE))

library(readxl)
library(dplyr)
library(tidyr)
library(stringr)
library(tibble)

# -----------------------------
# Paths (edit if needed)
# -----------------------------
gub_path  <- "/Users/admin/Downloads/GubDataRecreate2.xlsx"
comp_path <- "/Users/admin/Downloads/CompustatOutput.xlsx"

# -----------------------------
# Helpers
# -----------------------------
clean_comp_names <- function(nms) {
  close_paren <- regexpr(")", nms, fixed = TRUE)
  ifelse(
    startsWith(nms, "(") & close_paren > 1,
    substring(nms, 2, close_paren - 1),
    nms
  )
}

panel_b_summary <- function(df) {
  tibble(
    Variable = c("R&D intensity", "q", "Cash flow"),
    N = c(nrow(df), nrow(df), nrow(df)),
    Mean = c(mean(df$rd_intensity, na.rm = TRUE),
             mean(df$q, na.rm = TRUE),
             mean(df$cash_flow, na.rm = TRUE)),
    Median = c(median(df$rd_intensity, na.rm = TRUE),
               median(df$q, na.rm = TRUE),
               median(df$cash_flow, na.rm = TRUE)),
    SD = c(sd(df$rd_intensity, na.rm = TRUE),
           sd(df$q, na.rm = TRUE),
           sd(df$cash_flow, na.rm = TRUE))
  ) %>%
    mutate(
      Mean = round(Mean, 4),
      Median = round(Median, 4),
      SD = round(SD, 4)
    )
}

# -----------------------------
# 1) Gubernatorial close elections (margin <= 5)
# -----------------------------
gub <- read_excel(gub_path, sheet = 1) %>%
  rename(year = Year, state = State, margin = Margin) %>%
  transmute(
    year = as.integer(year),
    state = str_to_upper(str_trim(as.character(state))),
    margin = as.numeric(margin)
  ) %>%
  filter(!is.na(year), !is.na(state), !is.na(margin)) %>%
  filter(margin <= 5) %>%
  distinct(state, year, .keep_all = TRUE)

# -----------------------------
# 2) Build close-election cycles [-1,0,+1,+2]
# -----------------------------
close_cycles <- gub %>%
  rename(election_year = year) %>%
  crossing(rel = c(-1L, 0L, 1L, 2L)) %>%
  mutate(
    year = election_year + rel,
    close_election_cycle = 1L,
    close_election_dummy = as.integer(rel == 0L)
  ) %>%
  group_by(state, year) %>%
  summarise(
    close_election_cycle = 1L,
    close_election_dummy = as.integer(any(close_election_dummy == 1L)),
    .groups = "drop"
  )

# -----------------------------
# 3) Load and clean Compustat annual data
# -----------------------------
comp_raw <- read_excel(comp_path, sheet = 1)
names(comp_raw) <- clean_comp_names(names(comp_raw))

comp <- comp_raw %>%
  mutate(
    gvkey  = as.character(gvkey),
    fyear  = as.integer(fyear),
    state  = str_to_upper(str_trim(as.character(state))),
    sic    = suppressWarnings(as.numeric(sic)),
    at     = as.numeric(at),
    ceq    = as.numeric(ceq),
    dlc    = as.numeric(dlc),
    dltt   = as.numeric(dltt),
    ppent  = as.numeric(ppent),
    txdb   = as.numeric(txdb),
    dp     = as.numeric(dp),
    ebitda = as.numeric(ebitda),
    ib     = as.numeric(ib),
    sale   = as.numeric(sale),
    xrd    = as.numeric(xrd),
    csho   = as.numeric(csho),
    prcc_f = as.numeric(prcc_f)
  ) %>%
  filter(
    # Standard Compustat annual industrial/consolidated screens
    is.na(indfmt) | indfmt == "INDL",
    is.na(datafmt) | datafmt == "STD",
    is.na(consol) | consol == "C",
    !is.na(fyear), fyear >= 1976, fyear <= 2021
  )

# -----------------------------
# 4) Construct annual firm variables (Appendix A1 formulas)
#    Using stricter missing handling (matched published moments better)
# -----------------------------
comp_vars <- comp %>%
  arrange(gvkey, fyear) %>%
  group_by(gvkey) %>%
  mutate(
    has_price = !is.na(prcc_f),
    first_price_year = suppressWarnings(min(fyear[has_price], na.rm = TRUE)),
    first_price_year = ifelse(is.infinite(first_price_year), NA_real_, first_price_year),
    list_age_years = fyear - first_price_year
  ) %>%
  ungroup() %>%
  mutate(
    year = fyear,
    sic3 = floor(sic / 10),
    rd_intensity = ifelse(!is.na(at) & at > 0, xrd / at, NA_real_),
    q = ifelse(!is.na(at) & at > 0,
               ((prcc_f * csho) + at - ceq - txdb) / at,
               NA_real_),
    cash_flow = ifelse(!is.na(at) & at > 0,
                       (ib + dp) / at,
                       NA_real_),
    ln_sales = ifelse(!is.na(sale) & sale > 0, log1p(pmax(sale, 0)), NA_real_),
    ln_age = ifelse(!is.na(list_age_years) & list_age_years >= 0, log1p(list_age_years), NA_real_),
    tangibility = ifelse(!is.na(at) & at > 0, ppent / at, NA_real_),
    profitability = ifelse(!is.na(at) & at > 0, ebitda / at, NA_real_),
    leverage = ifelse(!is.na(at) & at > 0,
                      (dltt + dlc) / at,
                      NA_real_)
  )

# -----------------------------
# 5) Compute HHI and HHI^2 (3-digit SIC-year, sales shares)
# -----------------------------
hhi_df <- comp_vars %>%
  filter(!is.na(year), !is.na(sic3), !is.na(sale), sale >= 0) %>%
  group_by(year, sic3) %>%
  mutate(ind_sales = sum(sale, na.rm = TRUE)) %>%
  filter(ind_sales > 0) %>%
  mutate(market_share = sale / ind_sales) %>%
  summarise(
    herfindahl = sum(market_share^2, na.rm = TRUE),
    herfindahl2 = herfindahl^2,
    .groups = "drop"
  )

comp_vars <- comp_vars %>%
  left_join(hhi_df, by = c("year", "sic3"))

# -----------------------------
# 6) Merge close-election cycles to Compustat firm-years
# -----------------------------
annual_close <- comp_vars %>%
  left_join(close_cycles, by = c("state", "year")) %>%
  filter(close_election_cycle == 1)

# -----------------------------
# 7) Build Panel B final sample (annual empirical analysis sample)
# -----------------------------
t1b_panelB <- annual_close %>%
  filter(
    year >= 1976, year <= 2021,
    !is.na(at), at >= 5,              # $5m cutoff; AT in millions
    !is.na(xrd),                      # nonmissing R&D expense
    !is.na(rd_intensity),
    !is.na(q),
    !is.na(cash_flow),
    !is.na(ln_age),
    !is.na(ln_sales),
    !is.na(tangibility),
    !is.na(profitability),
    !is.na(leverage),
    !is.na(herfindahl),
    !is.na(herfindahl2),
    !(sic >= 4900 & sic <= 4999),     # utilities
    !(sic >= 6000 & sic <= 6999)      # financials
  )

# -----------------------------
# 8) Table 1B (Panel B) summary
# -----------------------------
table1b_panelB <- panel_b_summary(t1b_panelB)

cat("Final Panel B sample N:", nrow(t1b_panelB), "\n")
print(table1b_panelB)

# -----------------------------
# 9) Comparison vs paper (diagnostic output)
# -----------------------------
paper_table1b <- tibble(
  Variable = c("R&D intensity", "q", "Cash flow"),
  N_paper = c(26262L, 26262L, 26262L),
  Mean_paper = c(0.0876, 2.2201, -0.0389),
  Median_paper = c(0.0356, 1.5143, 0.0652),
  SD_paper = c(0.1503, 2.4729, 0.3727)
)

comparison <- table1b_panelB %>%
  left_join(paper_table1b, by = "Variable") %>%
  mutate(
    dN = N - N_paper,
    dMean = round(Mean - Mean_paper, 4),
    dMedian = round(Median - Median_paper, 4),
    dSD = round(SD - SD_paper, 4)
  )

cat("\nComparison vs paper Table 1B (Panel B):\n")
print(comparison)