Employment growth and house price trends

Let's examine house price and employment trends. And yes, le'ts try out that crazy bivariate tilegrid map.

house prices / economy / R / dataviz

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).

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

###############################################################################
#### Load state data (download spreadsheet in a data directory)
###############################################################################
df<-read_excel("data/states.xls", 
               sheet = "State Indices",
               range="B6:BB522" )

# make dates
df$date<-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")

 Share!