rm(list=ls()) library(utils) #for save.csv library(data.table) #importing from the web using fread library(foreign) #read.dta, inport data library(tidyr) #unite and separate paste0 library(lfe) #for lm with fixed effect (felm) library(readstata13) #read dta files writen in STATa13 library(stargazer) library(zoo) #as.yearqtr library(psych) #describe setwd("") #========================================================================= # Appendix - Section B.2 - Narrow replication with original data #========================================================================== #---------------------- # Table 6 #---------------------- ss_data = read.csv("data/Original_Data/sample_call_final_ss.csv") ss_data = ss_data[which(ss_data$quarter == 4),] ss_data = ss_data[!duplicated(ss_data[,c("cert","date")]),] ss_data = subset(ss_data,select = c(year,rssdid,cert,herfdepcty,assets)) colnames(ss_data)[colnames(ss_data)=="rssdid"] = "rssd_id" ss_data = ss_data[complete.cases(ss_data),] table6_data = ss_data table6_data = table6_data[order(table6_data$rssd_id),] table6_data = data.table(table6_data) table6_data[,l1_herfdepcty := shift(herfdepcty), by = c("rssd_id")] colnames(table6_data)[colnames(table6_data)=="herfdepcty"] = "herfdepcty_t" colnames(table6_data)[colnames(table6_data)=="l1_herfdepcty"] = "herfdepcty" cra_short = read.csv("data/Original_Data/cra_short_ss.csv") table6_data <- merge(cra_short,table6_data,by = c("rssd_id","year"),all=F) table6_data = table6_data[which(table6_data$year>=1997),] table6_data = table6_data[order(table6_data$year),] ff_tar_yearend = read.csv("data/Original_Data/ff_tar_yearend_ss.csv") ff_tar_yearend = ff_tar_yearend[,5:7] table6_data <- merge(table6_data,ff_tar_yearend,by = c("year"),all=F) table6_data = table6_data[order(table6_data$year),] cpi_fred_year = read.csv("data/Original_Data/cpi_fred_year_ss.csv") table6_data <- merge(table6_data,cpi_fred_year,by = c("year"),all=F) colnames(table6_data)[colnames(table6_data)=="rssd_id"] = "bankid" table6_data$fips = 1000*table6_data$state+table6_data$county table6_data$fipsyear = 10000*table6_data$fips+table6_data$year table6_data$fipsbank = 100000*table6_data$bankid+table6_data$fips table6_data = table6_data[order(table6_data$fips),] avgherfdepcty = read.csv("data/Original_Data/avgherfdepcty_ss.csv") table6_data <- merge(table6_data,avgherfdepcty,by = c("fips"),all=F) table6_data$d1_ffm_herfdepcty=table6_data$d1_fftar_yearend*table6_data$herfdepcty table6_data$d1_ffm_avgherfdepcty=table6_data$d1_fftar_yearend*table6_data$avgherfdepcty table6_data$tot_amt=table6_data$small_loan_amt+table6_data$medium_loan_amt+table6_data$large_loan_amt table6_data$log_tot_amt=log(table6_data$small_loan_amt+table6_data$medium_loan_amt+table6_data$large_loan_amt) table6_data = table6_data[order(table6_data$fipsbank),] table6_data = table6_data[order(table6_data$year),] cra_bank_county = table6_data table6_data$tot_amt_2010 = table6_data$tot_amt/table6_data$cpi table6_data = table6_data[order(table6_data$fipsbank),] table6_data = table6_data[order(table6_data$year),] table6_data <- data.table(table6_data) table6_data[,avg_tot_amt := mean(tot_amt_2010), by = c("fipsbank")] table6_data = table6_data[which(table6_data$tot_amt_2010>=100),] table6_data$zerolower = ifelse(table6_data$year>=2009,1,0) table6_data$bankidzero = paste0(table6_data$bankid,table6_data$zerolower) table6_data$fipszero = paste0(table6_data$fips,table6_data$zerolower) table6_data$fipsbankzero = paste0(table6_data$fipsbank,table6_data$zerolower) table6_data = table6_data[complete.cases(table6_data$log_tot_amt,table6_data$d1_ffm_herfdepcty, table6_data$d1_ffm_avgherfdepcty,table6_data$herfdepcty ,table6_data$assets),] fipsyear_count <- aggregate(x = table6_data$fipsyear, by = list(unique.values = table6_data$fipsyear), FUN = length) fipsyear_count1 = fipsyear_count[which(fipsyear_count$x >= 2),] fipsbank_count <- aggregate(x = table6_data$fipsbank, by = list(unique.values = table6_data$fipsbank), FUN = length) fipsbank_count1 = fipsbank_count[which(fipsbank_count$x >= 2),] bankid_count <- aggregate(x = table6_data$bankid, by = list(unique.values = table6_data$bankid), FUN = length) bankid_count1 = bankid_count[which(bankid_count$x >= 2),] year_count <- aggregate(x = table6_data$year, by = list(unique.values = table6_data$year), FUN = length) year_count1 = year_count[which(year_count$x >= 2),] fips_count <- aggregate(x = table6_data$fips, by = list(unique.values = table6_data$fips), FUN = length) fips_count1 = fips_count[which(fips_count$x >= 2),] fipszero_count <- aggregate(x = table6_data$fipszero, by = list(unique.values = table6_data$fipszero), FUN = length) fipszero_count1 = fipszero_count[which(fipszero_count$x >= 2),] table6_data = table6_data[which(table6_data$fipsbank %in% fipsbank_count1$unique.values & table6_data$fipsyear %in% fipsyear_count1$unique.values & table6_data$bankid %in% bankid_count1$unique.values & table6_data$year %in% year_count1$unique.values& table6_data$fips %in% fips_count1$unique.values& table6_data$fipszero %in% fipszero_count1$unique.values),] table6_data$hhi = table6_data$herfdepcty table6_1 <- felm(log_tot_amt ~ d1_ffm_herfdepcty + hhi #+ avgherfdepcty | fipsyear + fipsbank + bankid + year + fipszero | 0 | bankid + fips , data = table6_data ,na.action = na.omit) table6_2 <- felm(log_tot_amt ~ d1_ffm_herfdepcty + d1_ffm_avgherfdepcty + hhi #+ avgherfdepcty | fipsbank + bankid + year + fipszero + fips | 0 | bankid + fips , data = table6_data ,na.action = na.omit) table6_3 <- felm(log_tot_amt ~ d1_ffm_herfdepcty + d1_ffm_avgherfdepcty + hhi | bankid + year + fips + fipszero | 0 | bankid + fips , data = table6_data ,na.action = na.omit) table6_4 <- felm(log_tot_amt ~ d1_ffm_herfdepcty + hhi | bankid + year + fips + fipszero | 0 | bankid + fips , data = table6_data ,na.action = na.omit) stargazer(table6_1, table6_2, table6_3, table6_4, type = "text", title= "DSS TABLE VI", model.numbers= T, add.lines = list(c("Time f.e.", "Y", "Y", "Y","Y"), c("Bank f.e.", "Y", "Y", "Y","Y"), c("County f.e.", "Y", "Y", "Y","Y"), c("County-bank f.e.", "Y", "Y", "N","N"), c("County-time f.e.", "Y", "N", "N","N")), omit = "hhi", dep.var.labels.include = FALSE, dep.var.caption = c("log (new lending)"), covariate.labels = c("$\\Delta FF X Bank-HHI$","$\\Delta FF X Branch-HHI$"), df = FALSE, notes.align = "l", digits = 3,no.space=TRUE, out = "results/Narrow2/DSS table 6.text", omit.stat = c("adj.rsq","ser")) #---------------------- # Table 7 #---------------------- table7_data = cra_bank_county table7_data = table7_data[order(table7_data$fipsbank),] table7_data = table7_data[order(table7_data$year),] table7_data = table7_data[order(table7_data$fips),] table7_data = table7_data[order(table7_data$year),] table7_data <- as.data.table(table7_data) table7_data[,fips_amt := sum(tot_amt), by = c("fips","year")] table7_data$bank_fips_amt = table7_data$tot_amt/table7_data$fips_amt table7_data$herfdepcty_amt=table7_data$bank_fips_amt*table7_data$herfdepcty herfdepcty_fips = subset(table7_data, select = c(fips,year,herfdepcty_amt)) herfdepcty_fips = herfdepcty_fips[complete.cases(herfdepcty_fips$herfdepcty_amt),] herfdepcty_fips <- aggregate(herfdepcty_fips$herfdepcty_amt, by = list(herfdepcty_fips$fips, herfdepcty_fips$year), FUN = "sum") colnames(herfdepcty_fips)= c("fips","year","herfdepcty_amt") table7_data = subset(table7_data, select = c(fips,year,tot_amt)) table7_data <- aggregate(table7_data$tot_amt, by = list(table7_data$fips, table7_data$year), FUN = "sum") colnames(table7_data)= c("fips","year","tot_amt") avgherfdepcty = read.csv("data/Original_Data/avgherfdepcty_ss.csv") table7_data <- merge(table7_data,avgherfdepcty,by = c("fips"),all=F) table7_data = table7_data[order(table7_data$fips),] table7_data = table7_data[order(table7_data$year),] table7_data <- merge(table7_data,herfdepcty_fips,by = c("fips","year"),all=F) table7_data = table7_data[order(table7_data$year),] ff_tar_yearend = read.csv("data/Original_Data/ff_tar_yearend_ss.csv") ff_tar_yearend = ff_tar_yearend[,5:7] table7_data <- merge(table7_data,ff_tar_yearend,by = c("year"),all=F) table7_data = table7_data[order(table7_data$fips),] table7_data = table7_data[order(table7_data$year),] bls_final = read.csv("data/Original_Data/bls_final_ss.csv") table7_data <- merge(table7_data,bls_final,by = c("fips","year"),all=F) table7_data$d1_ffm_herfdepcty_amt=table7_data$d1_fftar_yearend*table7_data$herfdepcty_amt table7_data$d1_ffm_avgherfdepcty=table7_data$d1_fftar_yearend*table7_data$avgherfdepcty table7_data$log_tot_amt=log(table7_data$tot_amt) table7_data$zerolower = ifelse(table7_data$year>=2009,1,0) table7_data$fipszero = paste0(table7_data$fips,table7_data$zerolower) table7_data = table7_data[complete.cases(table7_data$log_tot_amt,table7_data$d1_ffm_herfdepcty_amt, table7_data$d1_ffm_avgherfdepcty,table7_data$herfdepcty_amt, table7_data$d1_lnemp,table7_data$d1_lntotwage),] fips_count <- aggregate(x = table7_data$fips, by = list(unique.values = table7_data$fips), FUN = length) fips_count1 = fips_count[which(fips_count$x >= 2),] table7_data = table7_data[which(table7_data$fips %in% fips_count1$unique.values),] fipszero_count <- aggregate(x = table7_data$fipszero, by = list(unique.values = table7_data$fipszero), FUN = length) fipszero_count1 = fipszero_count[which(fipszero_count$x >= 2),] table7_data = table7_data[which(table7_data$fipszero %in% fipszero_count1$unique.values),] table7_1 <- felm(log_tot_amt ~ d1_ffm_herfdepcty_amt + herfdepcty_amt | fips + year + fipszero | 0 | fips , data = table7_data ,na.action = na.omit) table7_2 <- felm(log_tot_amt ~ d1_ffm_herfdepcty_amt + herfdepcty_amt + d1_ffm_avgherfdepcty | fips + year + fipszero | 0 | fips , data = table7_data ,na.action = na.omit) table7_3 <- felm(d1_lnemp ~ d1_ffm_herfdepcty_amt + herfdepcty_amt | fips + year + fipszero | 0 | fips , data = table7_data ,na.action = na.omit) table7_4 <- felm(d1_lnemp ~ d1_ffm_herfdepcty_amt + herfdepcty_amt + d1_ffm_avgherfdepcty | fips + year + fipszero | 0 | fips , data = table7_data ,na.action = na.omit) table7_5 <- felm(d1_lntotwage ~ d1_ffm_herfdepcty_amt + herfdepcty_amt | fips + year + fipszero | 0 | fips , data = table7_data ,na.action = na.omit) table7_6 <- felm(d1_lntotwage ~ d1_ffm_herfdepcty_amt + herfdepcty_amt + d1_ffm_avgherfdepcty | fips + year + fipszero | 0 | fips , data = table7_data ,na.action = na.omit) stargazer(table7_1, table7_2,table7_3, table7_4,table7_5, table7_6, type = "text", title= "Table VII", dep.var.caption = "", model.numbers= T, dep.var.labels.include = FALSE, add.lines = list(c("Time f.e.", "Y", "Y", "Y", "Y", "Y", "Y"), c("County f.e.", "Y", "Y", "Y", "Y", "Y", "Y")), covariate.labels = c("$\\Delta FF X County-HHI$","County-HHI","$\\Delta FF X Branch-HHI$"), df = FALSE, notes.align = "l", digits = 3,no.space=TRUE, out = "results/Narrow2/DSS table 7.text", column.labels = c("log(new lending)", "\\Delta Employment","\\Delta Wage Bill"), column.separate = c(2, 2, 2), omit.stat = c("adj.rsq","ser"))