Data

The data I’m going to use are estimates of weekly U.S. average 30-year fixed mortgage rates from the Primary Mortgage Market Survey from Freddie Mac. These data can be easily downloaded from the St. Louis Fred database here.

I have the data saved in a simple text file with a column for data, the mortgage rate, and helper columns week, month, and year, where week is the week number starting with the first week of the year.

Code for table

Now we’ll load the data, do some data manipulations and make our table. We’re going to add some additional styling to the table.

# load libraries
library(tidyverse,quietly=T)
library(xtable,quietly=T)
library(data.table,quietly=T)
library(htmlTable,quietly=T)

# load data on weekly mortgage rates:
pmms30yr <- fread("data/pmms30yr.txt")
pmms30yr$date<-as.Date(pmms30yr$date, format="%m/%d/%Y")

# create month name variable "mname"
pmms30yr[,mname:=as.character(date,format="%b")]

#Compute averages by year/month
pm<-pmms30yr[,list(rate=round(mean(rate,na.rm=T),2)),by=c("year","mname")]

# "spread" rates over month in a wide data frame and coerce to data.frame
pms<-data.frame(spread(pm,mname,rate))

# drop year column
pms2 <- pms[,-1]

# use the first column (year) as rownames 
rownames(pms2) <- pms[,1]

# reorder the columns to by month (Jan, Feb, etc) instead of alphabetically (Apr, Aug, etc.)
pms2<-pms2[,unique(pmms30yr[year>1971]$mname)]

# Compute annual averages
pm.a<-pmms30yr[,list(Avg=round(mean(rate,na.rm=T),2)),by=c("year")]

# Add annual averages to data
pm3<-cbind(pms2,pm.a[,2,with=F])
pm3<-format(pm3,digits=3)

# Apply conditiional formatting to 2016 December and Annual averages to reflect fact that
# data is incomplete for those dates

my.format<-function(x){paste0("<span style='color:darkgray; font-style:italic'>",x,"*</span>")}

# overwrite values with styling using <span> and CSS
pm3[47,12:13]<-lapply(pm3[47,12:13],my.format)

#replace 

#create htmlTable

htmlTable(
  caption= # use CSS styling for title
    "<span style='text-align: left; font-size:x-large; font-weight:bold'>30-year Fixed Mortgage Rates in Percentage Points</span>",pm3, 
  # right align numbers
  align="right",
  # apply zebra striping
  col.rgroup = c("none", "#F7F7F7"),
  # group columns by quarter
  cgroup = c("1st Quarter", "2nd Quarter","3rd Quarter","4th Quarter","Annual"),
  n.cgroup = c(3,3,3,3,1),
  # increase spacing for table
  css.cell = "padding-left: .5em; padding-right: .1em;",
  # group data by decade
  tspanner=c("1970s","1980s","1990s","2000s","2010s"),
  n.tspanner=c(9,rep(10,3),8),
  # add a footnote
  tfoot="Source: Primary Mortgage Market Survey, Average of weekly rates\nData through 12/27/2016, <span style='color:darkgray; font-style:italic'>*based on year-to-date values</span>"
          )

Data tables are viz too

Data tables are a data visualization too. Artful tables can achieve as much or more than fancy statistical graphs. Check back in this space as I explore more ways to construct tables and deploy them together with other data visualization techniques I’ve been exploring here..