Plotting house price trends with FRED and R

IN THIS POST I AM going to share some useful code to create some custom plots using the St Louis Federal Reserve Economic Database (FRED). While the FRED page has some nice chart customization options, I’m going to import the data into R with the quantmod package and draw the plots.

I find myself doing these types of things often enough that I thought you might find these bits o’ code useful.

The example I’m going to use is a comparison of house prices, rents, income and other prices. We talked more about these data in a post last year.

Here goes. Execute the code below and get the plot at bottom.

### Load libraries
library(tidyverse)
library(quantmod)
## Loading required package: xts
## Loading required package: zoo
## 
## Attaching package: 'zoo'
## The following objects are masked from 'package:base':
## 
##     as.Date, as.Date.numeric
## 
## Attaching package: 'xts'
## The following objects are masked from 'package:dplyr':
## 
##     first, last
## The following objects are masked from 'package:data.table':
## 
##     first, last
## Loading required package: TTR
## Version 0.4-0 included new data defaults. See ?getSymbols.
library(data.table)
library(viridis)  # for the colors
## Loading required package: viridisLite
library(htmlTable)  # for the table

#  Here are some variables and a brief description, for more see FRED

my.names <- data.table(var=c("A229RC0","CUSR0000SA0L2","CUUR0000SEHA","HPIPONM226S"),
                       name=c("income","cpi.less.shelter","cpi.rent","hpi"),
                       Description=c("Per capita disposable income",
                                     "CPI-U All items less shelter",
                                     "CPI-U Rent of primary residences",
                                     "FHFA Purchase-only house price index"),
                       Source=c("U.S. Bureau of Economic Analysis",
                                "U.S. Bureau of Labor Statistics",
                                "U.S. Bureau of Labor Statistics",
                                "Federal Housing Finance Agency (FHFA)"))

htmlTable(my.names, caption="Data description",tfoot="Accessed via: St Louis Federal Reserve Economic Database (FRED)")
Data description
var name Description Source
1 A229RC0 income Per capita disposable income U.S. Bureau of Economic Analysis
2 CUSR0000SA0L2 cpi.less.shelter CPI-U All items less shelter U.S. Bureau of Labor Statistics
3 CUUR0000SEHA cpi.rent CPI-U Rent of primary residences U.S. Bureau of Labor Statistics
4 HPIPONM226S hpi FHFA Purchase-only house price index Federal Housing Finance Agency (FHFA)
Accessed via: St Louis Federal Reserve Economic Database (FRED)

Load data

# Use Quantmod to load data
# helpful reference https://jeffreybreen.wordpress.com/tag/quantmod/

df= getSymbols('CUUR0000SEHA',src='FRED', auto.assign=F) 
## 'getSymbols' currently uses auto.assign=TRUE by default, but will
## use auto.assign=FALSE in 0.5-0. You will still be able to use
## 'loadSymbols' to automatically load data. getOption("getSymbols.env")
## and getOption("getSymbols.auto.assign") will still be checked for
## alternate defaults.
## 
## This message is shown once per session and may be disabled by setting 
## options("getSymbols.warning4.0"=FALSE). See ?getSymbols for details.
df = data.frame(date=time(df), coredata(df) )

df.hpi =getSymbols('HPIPONM226S',src='FRED', auto.assign=F) 
df.hpi = data.frame(date=time(df.hpi), coredata(df.hpi) )

df.inc=getSymbols('A229RC0',src='FRED', auto.assign=F) 
df.inc = data.frame(date=time(df.inc), coredata(df.inc) )

df2= getSymbols('CUSR0000SA0L2',src='FRED', auto.assign=F) 
df2 = data.frame(date=time(df2), coredata(df2) )

Now we’ll want to merge all our data together

# merge the data
df3<-merge(df,df.hpi,by="date")
df3<-merge(df3,df.inc,by="date")
df3<-merge(df3,df2,by="date")
dt<-data.table(df3)

# Gather the data to make it tidy

dt %>% gather(var,value,-date) %>% data.table() ->dt2

# Merge on variable names

dt2<-merge(dt2,my.names,by="var")

# Create and index with January 1991 = 100
# Count by var
dt2=dt2[,id:=1:.N, by=var]  # Index running from 1:N by group (var)
dt2=dt2[,var0:=100*value/sum(ifelse(id==1,value,0)),by=var] #create index

Make the plot

# Create caption
mycaption<- "@lenkiefer Source: House Prices: FHFA purchase-only house price index. Rent: U.S. Bureau of Labor Statistics (BLS) consumer price index rent of primary residence. Other Prices: BLS consumer price index all items less shelter. Income: U.S. Bureau of Economic Analysis per capita disposable personal income (Table 2.6.). All are seasonally adjusted."

# Wrap caption 120 characters:
mycaption <- paste0(strwrap(mycaption, 120), sep="", collapse="\n")


# Create Plot
ggplot(data=dt2,aes(x=date,y=var0,color=name,linetype=name))+
  geom_line(size=1.1)+
  scale_y_log10(breaks=c(100,125,150,175,200,250))+
  theme_minimal()+theme(plot.caption=element_text(hjust=0),
                        legend.position="top")+
  guides(linetype=F)+
  scale_color_viridis(name="Variable",discrete=T,end=0.8)+
  labs(x="",y="Index, January 1991=100 (log scale)",
       title="Comparing house prices to rent, income and other prices",
       caption=mycaption       )

This plot shows that while house prices are growing faster that incomes and other prices in recent years, if we look at things over a 26 year period (1991-2016) house prices haven’t risen quite as fast as incomes. Though the story can vary a lot based on specific regions.

Summary

This little bit of code demonstrates how to get data from FRED into R. It also shows how to use a mixture of tidyr and data.table() to manipulate data. And finally, we used some ggplot2 to create some plots.

I use parts of these steps on a regular basis to make graphs charting trends in the economy and housing markets. Maybe you can find it useful too.

 Share!