Some very untidy data

If you just go to the Census HVS main page you can find a list of historical tables that are stored in .xlsx spreadsheets.

We were interested in several of those tables yesterday, but the main one was Table 8: Table 8. Quarterly Estimates of the Housing Inventory: 1965 to Present .xlsx. If you open that spreadsheet up you’d see something like this:

This is set up just like spreadsheets tend to be. It’s got merged cells, data skips rows and all the usual things that make our lives more challenging.

Prepare for data

In the first step, we create a variable tickers that stores a list of FRED mnemonics for the variables we want. Then we prepare a list of variable names. Finally we create a lookup table matching mnemonics with variable names.

#####################################################################################
## Step 0: Load Libraries ##
#####################################################################################
library(tidyverse)
library(tidyquant)

#####################################################################################
## Step 1: Prepare for data  ##
#####################################################################################

# Get HVS data
# First, list the Mnemonics from FRED
tickers <- c('ETOTALUSQ176N',    # All housing units
             'EVACANTUSQ176N',   # Vacant
             'EYRVACUSQ176N',    # Year-round vacant
             'ERENTUSQ176N',     # Vacant for rent
             'ESALEUSQ176N',     # Vacant for sale
             'ERNTSLDUSQ176N',   # Vacant rented or sold
             'EOFFMARUSQ176N',   # Vacant held off market
             'EOCCUSEUSQ176N',   # Vacant occasional use
             'EUREUSQ176N',      # Vacant usual residence elsewhere
             'EOTHUSQ176N',      # Vacant other
             'ESEASONUSQ176N',   # Vacant seasonal
             'EOCCUSQ176N',      # Occupied
             'EOWNOCCUSQ176N',   # Owner occupied
             'ERNTOCCUSQ176N',   # Renter occupied
             'RRVRUSQ156N',      # Rental vacancy rate
             'RHVRUSQ156N'       # Homeowner vacancy rate
)

# Next, list human readable variable names
myvars <- c('All housing units',
            'Vacant',
            'Year-round vacant',
            'Vacant for rent',
            'Vacant for sale',
            'Vacant rented or sold',
            'Vacant held off market',
            'Vacant occasional use',
            'Vacant usual residence elsewhere',
            'Vacant other',
            'Vacant seasonal',
            'Occupied',
            'Owner occupied',
            'Renter occupied',
            'Rental vacancy rate',
            'Homeowner vacancy rate'
)

# Create a lookup dataset
mylookup<-data.frame(symbol=tickers,var=myvars)
knitr::kable(mylookup)

Pull data

This is super simple thanks to tidyquant. We simple have to feed the tq_get() function our list of mnemonics and tell it to read from FRED. We do this by setting get='economic data' in the function call.

#####################################################################################
## Step 2: Pull data  ##
#####################################################################################

tickers %>% tq_get(get="economic.data", 
                   from="2001-04-01"   # we start from April 2001 due to break in HVS
                   ) -> df
# Take a look:
knitr::kable(head(df))

Nice.

Organize the data

Finally, we do a little bit of additional processing. Because tidyquant focuses on financial data, the default variable name is price. We’ll change the name to a more generic one value and create a variable called share that is the ratio of each variable to total housing units.

#####################################################################################
## Step 3: Organize data  ##
#####################################################################################

df<-merge(df,mylookup,by="symbol") %>% rename(value=price)
df %>% group_by(date) %>% 
  # create a share variable that is the ratio of units to All housing units
  mutate(share=value/value[var=="All housing units"])  %>% 
  ungroup() %>% arrange(date,-value) -> df
knitr::kable(tail(df,16))

Now that we have our variables it’s easy to make plots or proceed with other analysis. Below, I’m going to share the code I used for my small multiple plots.

Recently, I’ve liked to plot small multiples of a time series. But I also like to include all the time series on each plot, with the other series as a faint gray line. I think this kind of graph helps make comparisons easier, particularly if a panel plot goes over several rows/columns.

Here’s how I made it:

# Make a caption:
mycaption<- "@lenkiefer Source: U.S. Bureau of the Census, Housing Inventory Estimate, retrieved from FRED, Federal Reserve Bank of St. Louis; https://fred.stlouisfed.org/series/EOFFMARUSQ176N, September 18, 2017."
# Wrap caption 90 characters:
mycaption <- paste0(strwrap(mycaption, 90), sep="", collapse="\n")

# Make plot
ggplot(data=
         # We're only going to plot 3 series
         filter(df,var %in% c('Vacant for rent',
                              "Vacant rented or sold",
                              'Vacant for sale',
                              'Vacant held off market')),
       aes(x=date,y=share,label=var))+
  
  # Plot the gray lines
  geom_path(data=filter(df,var %in% c('Vacant for rent',
                                      "Vacant rented or sold",
                                      'Vacant for sale',
                                      'Vacant held off market')) %>%
  #overwrite the var variable with NULL so each line shows up on each panel.
              mutate(var=NULL), 
  inherit.aes=F,color="darkgray",  # tell this plot to ignore the aes statement
  alpha=0.5,                       # make the lines faint
  aes(x=date,y=share,group=symbol))+
  geom_line(size=1.05,color="royalblue")+ # draw the real plot
#  guides(color=F)+                        # ditch 
  theme_minimal()+
  facet_wrap(~var)+
  scale_y_continuous(labels=scales::percent,breaks=seq(0,1,.01))+
  theme(plot.caption=element_text(hjust=0),
        strip.text=element_text(color="royalblue") )+
  labs(x="",y="",title="U.S. Housing Inventory: Year-round vacant units as share of total housing stock", caption=mycaption)

Data wrangling got you down? Take solace in great new tools.

Sometimes data wrangling can be a real bummer. But folks are constantly making new tools that make it easier. Tidyquant is one such tool. We only scratched the surface of what it can do, but it has already made our live a little easier and more tidy. How could it work for you?