CPI line chart

library(ggrepel,quietly=T,warn.conflicts=F) # for the labels (avoid overplotting)


# dd is a list of dates, we'll set i = max date for labels
xlim<-c(min(cpi6$date),max(cpi6$date))
dd<-unique(cpi6$date)
i<- length(dd)

ggplot(data=cpi6[ 
  #drop some items for clarity
  ! (item.name %in% c("Other goods and services","Services",
                      "Education and communication"))],
  aes(x=date,y=cpi00,color=item.name))+
  geom_line()+
  theme_minimal()+   theme(legend.justification=c(0,0), legend.position="none")+
  scale_y_log10(limits=c(90,250),breaks=c(90,100,120,140,160,180,200,250))+
  geom_line(data=cpi.ed[ year>1999],size=1.5,color="red")+
  scale_x_date(labels= date_format("%b-%Y"),
               limits = as.Date(c('2000-01-01','2018-12-31')))+
  geom_text_repel(
    data = cpi6[date==dd[i] & 
                  ! (item.name %in% c("Other goods and services","Services",
                                      "Education and communication"))],
    aes(label = item.name), size = 3.5, nudge_x = 1) +
  geom_text(
    data = cpi.ed[date==dd[i]],color="red",
    aes(label = item.name),
    size = 3.5,nudge_x = 1,nudge_y=.01) +
  labs(x="", y="Consumer Price Index (log scale, Jan 2000=100, SA)",
       title="Consumer Prices",
       subtitle="by major category",
       caption="@lenkiefer Source: U.S. Bureau of Labor Statistics")+
  theme(plot.title=element_text(size=18),
        plot.caption=element_text(hjust=0,vjust=1),
        plot.margin=unit(c(0.25,0.25,0.25,0.25),"cm"))

Household debt line chart

ggplot(data=filter(df2,type!="Total" & type !="Mortgage"),
       aes(x=date,y=debt,color=type,label=type))+
  geom_line(size=1.2)+theme_minimal()+
  geom_text(data=tail(filter(df2,type!="Total" & type !="Mortgage"),5),
            nudge_x=10,nudge_y=.025)+
  coord_cartesian(xlim=c(as.Date("2003-01-01"),as.Date("2017-12-31")), y=c(0,1.5))+
  theme(plot.title=element_text(size=14),
        plot.caption=element_text(hjust=0,vjust=1),
        legend.position="none")+
  labs(title="Household debt",subtitle="Trillions of dollars",x="",y="",
       caption="@lenkiefer Source: FRBNY Consumer Credit Panel/Equifax")

Inflation line chart

The CPI line chart compares price changes over a long horizon. Let’s focus on more recent changes. The code below generates a plot of monthly price inflation by category.

# stack education with full data
cpi7<-rbind(cpi6,cpi.ed,fill=T)

# construct monthly inflation metrics
cpi7 <- cpi7[order(series_id,date),]
cpi7<-cpi7[, cpi.inf:= value/shift(value,1,fill=NA)-1, by=series_id]

# plot data
ggplot(data=cpi7[ 
  #drop some items for clarity
  ! (item.name %in% c("Other goods and services","Services",
                      "Education and communication"))],
  aes(x=date,y=cpi.inf,color=item.name))+
  geom_line()+
  geom_hline(yintercept=0,linetype=2,color="darkgray")+
  scale_y_continuous(label=scales::percent)+
  theme_minimal()+   theme(legend.justification=c(0,0), legend.position="none")+
  scale_x_date(labels= date_format("%Y"),
               limits = as.Date(c('2000-01-01','2017-12-31')))+
  geom_text_repel(
    data = cpi7[date==dd[i] & 
                  ! (item.name %in% c("Other goods and services","Services",
                                      "Education and communication"))],
    aes(label = item.name), size = 3.5, nudge_x = 1) +
  labs(x="", y="Consumer Price Inflation (Monthly % change, SA)",
       title="Consumer Price Inflation (m/m %)",
       subtitle="by major category",
       caption="@lenkiefer Source: U.S. Bureau of Labor Statistics")+
  theme(plot.title=element_text(size=18),
        plot.caption=element_text(hjust=0,vjust=1),
        plot.margin=unit(c(0.25,0.25,0.25,0.25),"cm"))

Wow, that’s pretty ugly, transportation is volatile and it’s hard to see what’s going on in the plot. Let’s use faceting to make a small multiple (also, let’s drop volatile transportation/commodities and recreation so we have 6 categories). Let’s also zoom in on trends since 2010.

# plot data
ggplot(data=cpi7[ 
  #drop some items for clarity
  ! (item.name %in% c("Other goods and services","Services",
                      "Education and communication", "Transportation",
                      "Commodities","Recreation"))],
  aes(x=date,y=cpi.inf,color=item.name))+
  geom_line()+
  geom_hline(yintercept=0,linetype=2,color="darkgray")+
  scale_y_continuous(label=scales::percent)+
  theme_minimal()+   theme(legend.justification=c(0,0), legend.position="none")+
  scale_x_date(labels= date_format("%Y"),
               limits = as.Date(c('2010-01-01','2017-12-31')))+
  facet_wrap(~item.name)+
  labs(x="", y="Consumer Price Inflation (monthly % change, SA)",
       title="Consumer Price Inflation (m/m %)",
       subtitle="by major category",
       caption="@lenkiefer Source: U.S. Bureau of Labor Statistics")+
  theme(plot.title=element_text(size=18),
        plot.caption=element_text(hjust=0,vjust=1),
        plot.margin=unit(c(0.25,0.25,0.25,0.25),"cm"))

Getting better. Still quite a bit of noise in some of the monthly measures. Let’s try looking at year-over-year percent changes to smooth out some of that noise.

# get 12 month inflation
cpi7<-cpi7[, cpi.inf12:= value/shift(value,12,fill=NA)-1, by=series_id]

# plot data
ggplot(data=cpi7[ 
  #drop some items for clarity
  ! (item.name %in% c("Other goods and services","Services",
                      "Education and communication", "Transportation",
                      "Commodities","Recreation"))],
  aes(x=date,y=cpi.inf12,color=item.name))+
  geom_line()+
  geom_hline(yintercept=0,linetype=2,color="darkgray")+
  scale_y_continuous(label=scales::percent)+
  theme_minimal()+   theme(legend.justification=c(0,0), legend.position="none")+
  scale_x_date(labels= date_format("%Y"),
               limits = as.Date(c('2010-01-01','2017-12-31')))+
  facet_wrap(~item.name)+
  labs(x="", y="Consumer Price Inflation (12-month % change, SA)",
       title="Consumer Price Inflation (y/y %)",
       subtitle="by major category",
       caption="@lenkiefer Source: U.S. Bureau of Labor Statistics")+
  theme(plot.title=element_text(size=18),
        plot.caption=element_text(hjust=0,vjust=1),
        plot.margin=unit(c(0.25,0.25,0.25,0.25),"cm"))

Now we have a clearer picture of recent trends. Overall inflation has been rising over the past year or so (thanks largely to recovering energy prices). Both Medical Care and Education are rising at a level well above general inflation, but seem to be trending down. Housing, which measures rents and owner’s equivalent rent not house prices (see for example question 2 here), is on the upswing.

State delinquency trends

Let’s look at consumer delinquency trends by state. These data are in another worksheet in the same spreadsheet. Our mastery of a couple readxl tricks is paying off bigtime!

# data is range A3: BH10, we'll get the date values from row 3
# the row titles are in column A

df.statedq<-read_excel("data/HHD_C_Report_2017Q1.xlsx", 
               sheet = "Page 23 Data",  # name of sheet
               range="A3:BF15" )        # range where data lives

## we've got blank columns in positions 2 & 3 (drop them)
## then we'll gather the data and rename X__1 as "state"

df.state3<-df.statedq %>% gather(datec,dqdebt,c(2:58)) %>% rename(state=X__1)

#now create a date variable using the datec string:

df.state3<- df.state3 %>% 
  mutate(year=2000+as.numeric(substr(datec,1,2)),
         month=as.numeric(substr(datec,5,5))*3) %>%
  # create date variable
  mutate(date=as.Date(ISOdate(year,month,1))) %>% 
  # drop unneeede variables
  select(state,date,dqdebt)

#plot delinquency rates

ggplot(data=df.state3,aes(x=date,y=dqdebt,color=state,
                          label=round(dqdebt,1)))+
  geom_line(size=1.2)+theme_minimal()+
  geom_text_repel(data=filter(df.state3,date=="2017-03-01"),
                  nudge_x=10,nudge_y=.025)+
    facet_wrap(~state,ncol=3)  + 
     theme(plot.title=element_text(size=14),
           plot.caption=element_text(hjust=0))+
  coord_cartesian(xlim=c(as.Date("2003-01-01"),as.Date("2017-12-31")))+
  theme(legend.position="none")+
labs(title="Percent of Balance 90+ Days Late by State",
     subtitle="Based on the population with a credit report",
     x="",y="Percent of Balance 90+ Days Late",
     caption="@lenkiefer Source: FRBNY Consumer Credit Panel/Equifax")

After rising very high during the Great Recession and its aftermath, delinquency rates are coming down. Let’s try to rework this line chart as a dot chart.

State delinquency dot chart

First, we’ll compute the max and min delinquency rate for each state, and then plot the 2017Q1 value as a dot with markers to indicate the min/max. We’ll also add the U.S. state="ALL"" in our data as another reference. I’ve done these calculations before, usually the data.table way, but we’ll do it the dplyr way here.

df.state3b<-df.state3 %>% 
  # group by states
  group_by(state) %>%  
  # compute state specific min/max
  mutate(dq.max=max(dqdebt),
         dq.min=min(dqdebt)) %>% 
  # ungroup
  ungroup() %>%
  # compute US average, 
  group_by(date) %>%
  mutate(dq.US=dqdebt[state=="ALL"]) %>%
  ungroup() %>%
  # compute max and min
  mutate(dq.USmax=max(dqdebt[state=="ALL"]),
         dq.USmin=min(dqdebt[state=="ALL"])
         )

ggplot(data=filter(df.state3b,date=="2017-03-01"),
       aes(x=dqdebt,y=state,
           color=(dqdebt-dq.min)/(dq.max-dq.min),
           label=state))+
  geom_point(size=3)+
  geom_segment(aes(xend=dq.min,x=dq.max,yend=state),size=2,alpha=0.5)+
  scale_color_viridis(option="D",direction=-1,
                      name="Delinquency rate relative\nto state min/max\n1=max,0=min max",
                      end=0.95,limits=c(0,1))+
    theme_minimal()+
  theme(legend.position="top",
        plot.title=element_text(size=14),
        plot.caption=element_text(hjust=0),
        legend.key.width=unit(3,"line")) +
  geom_vline(aes(xintercept=dq.US),linetype=2,color="black")+
  labs(title="Percent of Balance 90+ Days Late by State: 2017Q1",
     subtitle="Based on the population with a credit report",
     y="",x="Percent of Balance 90+ Days Late",
     caption="@lenkiefer Source: FRBNY Consumer Credit Panel/Equifax\nlines extend from 2003Q1-2017Q1 (state specific) min to max, dotted line U.S. average")

Improving the plot with reordering

Ooh, I learned something else I should share. The data below are ordered according alphabetically, which isn’t the best way. Let’s reorder the data based on the current value. We can do it easily thanks to the forcats fct_reorder function.

ggplot(data=filter(df.state3b,date=="2017-03-01"),
       aes(x=dqdebt,
           # use fct_reorder, we want the US (state=="ALL") at the bottom,
           # so use ifelse() statement
           y=forcats::fct_reorder(state,ifelse(state=="ALL",-100,dqdebt)),
           color=(dqdebt-dq.min)/(dq.max-dq.min),
           label=state))+
  geom_point(size=3)+
  geom_segment(aes(xend=dq.min,x=dq.max,yend=state),size=2,alpha=0.5)+
  scale_color_viridis(option="D",direction=-1,
                      name="Delinquency rate relative\nto state min/max\n1=max,0=min max",
                      end=0.95,limits=c(0,1))+
    theme_minimal()+
  theme(legend.position="top",
        plot.title=element_text(size=14),
        plot.caption=element_text(hjust=0),
        legend.key.width=unit(3,"line")) +
  geom_vline(aes(xintercept=dq.US),linetype=2,color="black")+
  labs(title="Percent of Balance 90+ Days Late by State: 2017Q1",
     subtitle="Based on the population with a credit report",
     y="",x="Percent of Balance 90+ Days Late",
     caption="@lenkiefer Source: FRBNY Consumer Credit Panel/Equifax\nlines extend from 2003Q1-2017Q1 (state specific) min to max, dotted line U.S. average")

In this plot we can compare more easily than with the small multiple lines. Notice that the range of variation is quite different depending on which state you are looking at. Nevada (NV) had delinquency rates reach over 20 percent. Ohio (OH) on the other hand, never saw delinquency rates over 10 percent. We can also see that while the level of delinquency has declined, it’s not at a minimum for any of the states tracked.