COVID MUNI
7/02/2020
zipcity <- fread("C:/Users/dratnadiwakara2/Documents/OneDrive - Louisiana State University/Raw Data/Crosswalk Files/zip_code_mapping.csv")
tbill3month <- fread("C:/Users/dratnadiwakara2/Documents/OneDrive - Louisiana State University/Projects/COVID19/Muni/WTB3MS.csv")
tbill3month[,DATE:=as.Date(DATE)]
tbill3month[,week:=cut(DATE, "week")]
tbill3month[,week:=as.character(week)]
zhvi <- readRDS("C:/Users/dratnadiwakara2/Documents/OneDrive - Louisiana State University/Raw Data/Zillow Research Data/Zip_Zhvi_SingleFamilyResidence.rds")
zhvi <- data.table(zhvi)
zhvi <- zhvi[month>="2019-01-01"]
zhvi <- zhvi[,.(zhvi=mean(zhvi)),by=(zipcode)]
zhvi[,zipcode:=as.integer(zipcode)]
zri <- readRDS("C:/Users/dratnadiwakara2/Documents/OneDrive - Louisiana State University/Raw Data/Zillow Research Data/Zip_Zri_SingleFamilyResidenceRental.rds")
zri <- data.table(zri)
zri <- zri[month>="2019-01-01"]
zri <- zri[,.(zri=mean(mean_rent)),by=(zipcode)]
zri[,zipcode:=as.integer(zipcode)]
zip_county <- fread("C:/Users/dratnadiwakara2/Documents/OneDrive - Louisiana State University/Raw Data/Crosswalk Files/ZIP_COUNTY_092016.csv")
setorder(zip_county,ZIP,-RES_RATIO)
zip_county <- zip_county[!duplicated(zip_county$ZIP),c("ZIP","COUNTY")]
names(zip_county) <- c("zip","fips")
citydata <- fread("C:/Users/dratnadiwakara2/Documents/OneDrive - Louisiana State University/Raw Data/COVID19/cities_by_income_2.csv")
citydata[,highincome:=ifelse(income>=median(income),1,0)]
citydata <- merge(citydata,zhvi,by.x="lzip",by.y="zipcode",all.x=T)
citydata <- merge(citydata,zri,by.x="lzip",by.y="zipcode",all.x=T)
citydata <- merge(citydata,zip_county,by.x="lzip",by.y="zip",all.x=T)
spatial <- R.matlab::readMat('C:/Users/dratnadiwakara2/Documents/OneDrive - Louisiana State University/Raw Data/COVID19/mat/spatial_data.mat')
fips <- unlist(spatial$FIPS, use.names=FALSE)
pop <- unlist(spatial$Pop, use.names=FALSE)
land <- unlist(spatial$Land,use.names = FALSE)
acs2 <- data.frame(fips=as.numeric(fips),countypop=pop,countyland=land)
citydata <- merge(citydata,acs2,by="fips",all.x=T)
citydata[,popdensity:=citydata$countypop/citydata$countyland]1 Correlations
round(cor(citydata[,c("income","population","zhvi","zri","popdensity")],use = "complete.obs"),2)## income population zhvi zri popdensity
## income 1.00 -0.23 0.49 0.60 -0.01
## population -0.23 1.00 -0.03 -0.09 0.62
## zhvi 0.49 -0.03 1.00 0.95 0.15
## zri 0.60 -0.09 0.95 1.00 0.13
## popdensity -0.01 0.62 0.15 0.13 1.00CSSE <- fread("https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_confirmed_US.csv")
CSSE <- CSSE[CSSE$FIPS>=1000]
CSSE[,c("UID","iso2","iso3","code3","Admin2","Province_State","Country_Region","Lat","Long_","Combined_Key"):=list(NULL)]
CSSE <- melt(data = CSSE,id.vars = c("FIPS"))
names(CSSE) <- c("fips","date","cases")
CSSE[,date:=as.character(CSSE$date)]
CSSE[,date:=as.Date(CSSE$date,"%m/%d/%y")]
CSSE[,week:=cut(date, "week")]
setorder(CSSE,fips,week,date)
CSSE <- CSSE[!duplicated(CSSE[,c("fips","week")])]
CSSE[,c("date"):=list(NULL)]
CSSE[,week:=as.character(week)]spending <- fread("https://raw.githubusercontent.com/Opportunitylab/EconomicTracker/main/data/Affinity%20-%20County%20-%20Daily.csv")
spending[,Date:=as.Date(paste0(year,"-",month,"-",day))+7]
spending[,week:=as.character(cut(Date, "week"))]
spending <- spending[,c("countyfips","week","spend_all")]
spending <- spending[,.(spend_all=mean(spend_all,na.rm=T)),by=.(countyfips,week)]
mobility <- fread("https://raw.githubusercontent.com/Opportunitylab/EconomicTracker/main/data/Google%20Mobility%20-%20County%20-%20Daily.csv")
mobility[,Date:=as.Date(paste0(year,"-",month,"-",day))+7]
mobility[,week:=as.character(cut(Date, "week"))]
mobility <- mobility[,c("countyfips","week","gps_grocery_and_pharmacy","gps_away_from_home")]
mobility[,gps_grocery_and_pharmacy:=as.numeric(gps_grocery_and_pharmacy)]
mobility[,gps_away_from_home:=as.numeric(gps_away_from_home)]
mobility <- mobility[,.(grocery=mean(gps_grocery_and_pharmacy,na.rm=T),away=mean(gps_away_from_home,na.rm=T)),by=.(countyfips,week)]
uiclaims <- fread("https://raw.githubusercontent.com/Opportunitylab/EconomicTracker/main/data/UI%20Claims%20-%20County%20-%20Weekly.csv")
uiclaims[,Date:=as.Date(paste0(year,"-",month,"-",day_endofweek))+7]
uiclaims[,week:=as.character(cut(Date, "week"))]
uiclaims <- uiclaims[,c("countyfips","week","initial_claims_rate")]
uiclaims[,initial_claims_rate:=as.numeric(initial_claims_rate)]
uiclaims <- uiclaims[,.(initial_claims_rate=mean(initial_claims_rate,na.rm=T)),by=.(countyfips,week)]
womply <- fread("https://raw.githubusercontent.com/Opportunitylab/EconomicTracker/main/data/Womply%20Merchants%20-%20County%20-%20Daily.csv")
womply[,Date:=as.Date(paste0(year,"-",month,"-",day))+7]
womply[,week:=as.character(cut(Date, "week"))]
womply <- womply[,c("countyfips","week","merchants_all")]
womply[,merchants_all:=as.numeric(merchants_all)]
womply <- womply[,.(merchants_all=mean(merchants_all,na.rm=T)),by=.(countyfips,week)]muni <- fread("C:/Users/dratnadiwakara2/Documents/OneDrive - Louisiana State University/Projects/COVID19/Muni/large_small_cities_by_state.csv")
muni[,Date:=as.Date(Date,format="%m/%d/%Y")]
muni[,week:=cut(Date, "week")]
muni[,weekno:= as.factor(week)]
muni[,weekno:= as.numeric(weekno)-39]
muni[,week:=as.character(week)]
muni <- merge(muni,tbill3month,by="week")
muni[,excessyld:=ytm_avg-WTB3MS]
citydata[,c("V1","statefips","income","population"):=list(NULL)]
muni <- merge(muni,citydata,by=c("Ticker"))
muni <- merge(muni,CSSE,by=c("fips","week"),all.x=T)
muni[,casespp:=0.0000001+(cases/countypop)]
muni <- merge(muni,spending,by.x=c("fips","week"),by.y=c("countyfips","week"),all.x=T)
muni <- merge(muni,mobility,by.x=c("fips","week"),by.y=c("countyfips","week"),all.x=T)
muni <- merge(muni,uiclaims,by.x=c("fips","week"),by.y=c("countyfips","week"),all.x=T)
muni <- merge(muni,womply,by.x=c("fips","week"),by.y=c("countyfips","week"),all.x=T)2 YTM - 3 Month T-Bill by Maturity
munisum <- muni[maturity_id<6,.(yld=mean(excessyld,na.rm=T)),by=.(weekno,maturity_id)]
ggplot(munisum,aes(x=weekno,y=yld,color=factor(maturity_id),group=factor(maturity_id)))+geom_line()+theme_minimal()+theme(legend.position="bottom",axis.text.x=element_text(angle=90))
3 YTM - 3 Month T-Bill by Income
3.1 Maturity ID = 1
incomesplit <- muni[maturity_id==1]
munisum <- incomesplit[,.(yld=mean(excessyld,na.rm=T)),by=.(weekno,highincome)]
ggplot(munisum[weekno> (-19)],aes(x=weekno,y=yld,color=factor(highincome),group=factor(highincome)))+geom_line()+theme_minimal()+theme(legend.position="bottom",axis.text.x=element_text(angle=90))
3.2 Maturity ID = 2
incomesplit <- muni[maturity_id==2]
munisum <- incomesplit[,.(yld=mean(excessyld,na.rm=T)),by=.(weekno,highincome)]
ggplot(munisum[weekno> (-19)],aes(x=weekno,y=yld,color=factor(highincome),group=factor(highincome)))+geom_line()+theme_minimal()+theme(legend.position="bottom",axis.text.x=element_text(angle=90))
3.3 Maturity ID = 3
incomesplit <- muni[maturity_id==3]
munisum <- incomesplit[,.(yld=mean(excessyld,na.rm=T)),by=.(weekno,highincome)]
ggplot(munisum[weekno> (-19)],aes(x=weekno,y=yld,color=factor(highincome),group=factor(highincome)))+geom_line()+theme_minimal()+theme(legend.position="bottom",axis.text.x=element_text(angle=90))
3.4 Maturity ID = 4
incomesplit <- muni[maturity_id==4]
munisum <- incomesplit[,.(yld=mean(excessyld,na.rm=T)),by=.(weekno,highincome)]
ggplot(munisum[weekno> (-19)],aes(x=weekno,y=yld,color=factor(highincome),group=factor(highincome)))+geom_line()+theme_minimal()+theme(legend.position="bottom",axis.text.x=element_text(angle=90))
3.5 Maturity ID = 5
incomesplit <- muni[maturity_id==5]
munisum <- incomesplit[,.(yld=mean(excessyld,na.rm=T)),by=.(weekno,highincome)]
ggplot(munisum[weekno> (-19)],aes(x=weekno,y=yld,color=factor(highincome),group=factor(highincome)))+geom_line()+theme_minimal()+theme(legend.position="bottom",axis.text.x=element_text(angle=90))
4 Corss-sectional splits
4.1 By Income
This figure plots the \(\beta_w\) and the corresponding 95% confidence interval of the following regression.
\[ YTM_{c,w} = \Sigma_{w} \beta_w \times log(income)_c \times w + CityFE + Week FE \]
r <- list()
r[[1]] <- felm(excessyld~log(income)*factor(weekno)|Ticker+weekno|0|Ticker+weekno,data=muni[weekno>= (-20) & maturity_id==1])
r[[2]] <- felm(excessyld~log(income)*factor(weekno)|Ticker+weekno|0|Ticker+weekno,data=muni[weekno>= (-20) & maturity_id==5]
)
.coef_plot_2reg_line(r[[1]],"Maturity ID=1",r[[2]],"Maturity ID=5","log(income):factor(weekno)",-20)+geom_vline(xintercept = 2,color="darkred")+ylab(expression(beta[w]))
4.2 By House Price
This figure plots the \(\beta_w\) and the corresponding 95% confidence interval of the following regression.
\[ YTM_{c,w} = \Sigma_{w} \beta_w \times log(houseprice)_c \times w + CityFE + Week FE \]
r <- list()
r[[1]] <- felm(excessyld~log(zhvi)*factor(weekno)|Ticker+weekno|0|Ticker+weekno,data=muni[weekno>= (-16) & maturity_id==1])
r[[2]] <- felm(excessyld~log(zhvi)*factor(weekno)|Ticker+weekno|0|Ticker+weekno,data=muni[weekno>= (-16) & maturity_id==5])
.coef_plot_2reg_line(r[[1]],"Maturity ID=1",r[[2]],"Maturity ID=5","log(zhvi):factor(weekno)",-16)+geom_vline(xintercept = 2,color="darkred")+ylab(expression(beta[w]))
4.3 By Rent
This figure plots the \(\beta_w\) and the corresponding 95% confidence interval of the following regression.
\[ YTM_{c,w} = \Sigma_{w} \beta_w \times log(rent)_c \times w + CityFE + Week FE \]
r <- list()
r[[1]] <- felm(excessyld~log(zri)*factor(weekno)|Ticker+weekno|0|Ticker+weekno,data=muni[weekno>= (-20) & maturity_id==1])
r[[2]] <- felm(excessyld~log(zri)*factor(weekno)|Ticker+weekno|0|Ticker+weekno,data=muni[weekno>= (-20) & maturity_id==5])
.coef_plot_2reg_line(r[[1]],"Maturity ID=1",r[[2]],"Maturity ID=5","log(zri):factor(weekno)",-20)+geom_vline(xintercept = 2,color="darkred")+ylab(expression(beta[w]))
4.4 By Pop. Density
This figure plots the \(\beta_w\) and the corresponding 95% confidence interval of the following regression.
\[ YTM_{c,w} = \Sigma_{w} \beta_w \times log(popdensity)_c \times w + CityFE + Week FE \]
r <- list()
r[[1]] <- felm(excessyld~log(popdensity)*factor(weekno)|Ticker+weekno|0|Ticker+weekno,data=muni[weekno>= (-20) & maturity_id==1])
r[[2]] <- felm(excessyld~log(popdensity)*factor(weekno)|Ticker+weekno|0|Ticker+weekno,data=muni[weekno>= (-20) & maturity_id==5])
.coef_plot_2reg_line(r[[1]],"Maturity ID=1",r[[2]],"Maturity ID=5","log(popdensity):factor(weekno)",-20)+geom_vline(xintercept = 2,color="darkred")+ylab(expression(beta[w]))
4.5 By Cases
This figure plots the \(\beta_w\) and the corresponding 95% confidence interval of the following regression.
\[ YTM_{c,w} = \Sigma_{w} \beta_w \times log(casespp)_c \times w + CityFE + Week FE\]
r <- list()
r[[1]] <- felm(excessyld~log(casespp)*factor(weekno)|Ticker+weekno|0|Ticker+weekno,data=muni[weekno>= (-3) & maturity_id==1])
r[[2]] <- felm(excessyld~log(casespp)*factor(weekno)|Ticker+weekno|0|Ticker+weekno,data=muni[weekno>= (-3) & maturity_id==5])
.coef_plot_2reg_line(r[[1]],"Maturity ID=1",r[[2]],"Maturity ID=5","log(casespp):factor(weekno)",-3)+geom_vline(xintercept = 2,color="darkred")+ylab(expression(beta[w]))
4.6 By Spending
This figure plots the \(\beta_w\) and the corresponding 95% confidence interval of the following regression.
\[ YTM_{c,w} = \Sigma_{w} \beta_w \times log(casespp)_c \times w + CityFE + Week FE\]
r <- list()
r[[1]] <- felm(excessyld~spend_all*factor(weekno)|Ticker+weekno|0|Ticker+weekno,data=muni[weekno>= (-2) & maturity_id==1])
r[[2]] <- felm(excessyld~spend_all*factor(weekno)|Ticker+weekno|0|Ticker+weekno,data=muni[weekno>= (-2) & maturity_id==5])
.coef_plot_2reg_line(r[[1]],"Maturity ID=1",r[[2]],"Maturity ID=5","spend_all:factor(weekno)",-2)+geom_vline(xintercept = 2,color="darkred")+ylab(expression(beta[w]))
4.7 By Mobility
This figure plots the \(\beta_w\) and the corresponding 95% confidence interval of the following regression.
\[ YTM_{c,w} = \Sigma_{w} \beta_w \times away \times w + CityFE + Week FE\]
r <- list()
r[[1]] <- felm(excessyld~away*factor(weekno)|Ticker+weekno|0|Ticker+weekno,data=muni[weekno>= (-2) & maturity_id==1])
r[[2]] <- felm(excessyld~away*factor(weekno)|Ticker+weekno|0|Ticker+weekno,data=muni[weekno>= (-2) & maturity_id==5])
.coef_plot_2reg_line(r[[1]],"Maturity ID=1",r[[2]],"Maturity ID=5","away:factor(weekno)",-2)+geom_vline(xintercept = 2,color="darkred")+ylab(expression(beta[w]))
4.8 By UI Claims
This figure plots the \(\beta_w\) and the corresponding 95% confidence interval of the following regression.
\[ YTM_{c,w} = \Sigma_{w} \beta_w \times uiclaims \times w + CityFE + Week FE\]
r <- list()
r[[1]] <- felm(excessyld~initial_claims_rate*factor(weekno)|Ticker+weekno|0|Ticker+weekno,data=muni[weekno>= (-6) & maturity_id==1])
r[[2]] <- felm(excessyld~initial_claims_rate*factor(weekno)|Ticker+weekno|0|Ticker+weekno,data=muni[weekno>= (-6) & maturity_id==5])
.coef_plot_2reg_line(r[[1]],"Maturity ID=1",r[[2]],"Maturity ID=5","initial_claims_rate:factor(weekno)",-6)+geom_vline(xintercept = 2,color="darkred")+ylab(expression(beta[w]))
4.9 By Frac. Bus. Open
This figure plots the \(\beta_w\) and the corresponding 95% confidence interval of the following regression.
\[ YTM_{c,w} = \Sigma_{w} \beta_w \times fracopen \times w + CityFE + Week FE\]
r <- list()
r[[1]] <- felm(excessyld~merchants_all*factor(weekno)|Ticker+weekno|0|Ticker+weekno,data=muni[weekno>= (-5) & maturity_id==1])
r[[2]] <- felm(excessyld~merchants_all*factor(weekno)|Ticker+weekno|0|Ticker+weekno,data=muni[weekno>= (-5) & maturity_id==5])
.coef_plot_2reg_line(r[[1]],"Maturity ID=1",r[[2]],"Maturity ID=5","merchants_all:factor(weekno)",-5)+geom_vline(xintercept = 2,color="darkred")+ylab(expression(beta[w]))