LET US TAKE A LOOK AT HOUSE PRICE AND EMPLOYMENT TRENDS.

House prices in the Unitest States have been increasing at a rapid pace, about 7 percent on an annual basis. How does that relate to employment growth? And how do those trends vary by geography. Let’s take a look.

Per usual, I will post R code and you can follow along.

## Data

Following recent posts (see here and here for example), we will use the Freddie Mac House Price Index an Excel spreadsheet can be downloaded here.

We’ll get employment data from the U.S. Bureau of Labor Statistics via FRED. See here for more on using the quantmod and tidyquant packages to work with FRED data.

The following code will wrangle our data (out of the spreadsheet and out of FRED).

###############################################################################
###############################################################################
library(tidyverse)
library(data.table)
library(tibbletime)
library(tidyquant)
library(magrittr)
library(viridis)
library(scales)
library(geofacet)
library(cowplot)

###############################################################################
###############################################################################
sheet = "State Indices",
range="B6:BB522" )

# make dates
dfdate<-seq.Date(as.Date("1975-01-01"),as.Date("2017-12-01"),by="1 month") df.state<-df %>% gather(geo,hpi,-date) %>% mutate(type="state",state=geo) df.state<- df.state %>% group_by(geo) %>% mutate(hpa=hpi/shift(hpi,12)-1, hpa1=hpi/shift(hpi,1)-1, hpilag12=shift(hpi,12,fill=NA), hpimax12=rollmax(hpi,13,align="right",fill=NA), hpimin12=-rollmax(-hpi,13,align="right",fill=NA)) %>% ungroup() df.state<-df.state %>% group_by(date) %>% mutate(us.hpa=hpa[geo=="United States not seasonally adjusted"], us.hpi=hpi[geo=="United States not seasonally adjusted"]) %>% mutate(up=ifelse(hpa>us.hpa,hpa,us.hpa), down=ifelse(hpa<=us.hpa,hpa,us.hpa), dlabel=paste(as.character(date,format="%B-%Y")," \n ") ) %>% ungroup() df.state <- filter(df.state, !( state %in% c("United States not seasonally adjusted","United States seasonally adjusted"))) dlist<-unique(filter(df.state,year(date)>1999)date)
df.state<- df.state %>% group_by(state) %>% mutate(id = row_number()) %>% ungroup()

# get hpa by state and year (December only)
df.state %>% mutate(year=year(date)) %>% filter(month(date)==12 & year>1989) %>% select(year,state,hpa) -> df.state2

#####################################################################################
## Step 2: go get data from FRED ##
#####################################################################################

tickers<-c(paste0(us_state_grid2$code,"NA"), # nonfarm employment is NA (monthly, SA) paste0(us_state_grid2$code,"POP"),     # resident population (annual)
paste0(us_state_grid2\$code,"BPPRIV")  # private building permits (monthly, NSA)
)

df<-tq_get(tickers,get="economic.data",from="1990-01-01")

#####################################################################################
## Organize the data ##
#####################################################################################
df<-mutate(df,
state = substr(symbol,1,2),
var   = substr(symbol,3,10))
# get population by year

df %>% filter(var=="POP") %>% mutate(year=year(date)) %>% group_by(state,year) %>% summarize(POP=sum(price)) -> df.pop
df %>% filter(var=="BPPRIV") %>% mutate(year=year(date)) %>% group_by(state,year) %>% summarize(PERMITS=sum(price)) -> df.permits
df %>% filter(var=="NA" & month(date)==12) %>% mutate(year=year(date)) %>% group_by(state,year) %>% summarize(EMP=sum(price)) -> df.emp
df3 <- merge(df.emp,
df.pop,
by=c("state","year")) %>% merge(df.permits, by=c("state","year"))
df3 %<>% group_by(state) %>% mutate(perm2pop=PERMITS/POP, EMPg=Delt(EMP,k=1), POPg=Delt(POP,k=1), PERMg=Delt(PERMITS,k=1), eg2permg=EMPg/PERMg) %>% ungroup()
df4<-left_join(df.state2,df3,by=c("state","year"))

# Get national data
# employment via FRED
df.us<-tq_get("PAYEMS",get="economic.data",from="1975-01-01") %>% mutate(eg=Delt(price,k=12))

# Get US house prices
df.hpius <- filter(df.state,state=="AK") %>% select(date,us.hpa,us.hpi)

df.us<-left_join(df.us,df.hpius,by="date")