Skip to Content
Home » Questions » R ggplot highlight table of quarterly figures has displaced figures

R ggplot highlight table of quarterly figures has displaced figures

Submitted by SlowLearner on February 4, 2012

I am using geom_tile to create a table of quarterly figures, which is contained in a data frame. This seems to work without problems except when the data series begins in the middle of the year, typically in the April to June quarter. If I plot the data frame at the beginning of the calendar year, there is no problem: I get a well-formed highlight table.

However, to achieve that I have also excluded some of my data. If I specify a start date part way through the year, the data gets dispaced/jumbled up, like so:

Q. How can I plot the table such that even when the series begins in mid-year it somehow pads any missing data for the year in which it starts? That is, if my data begins in the July to September quarter, how can I force ggplot to put that first data point in the third column, leaving two blank tiles to the left?

Q. As a follow-up or perhaps the same question asked in a different way, how can I specify which quarter should be in the first column? Ideally I would like to be able to specify that the Jul-Sep quarter of each year is in the first column of the table and that the January to March quarter is in the fourth column.

Example follows:

require(lubridate)
require(ggplot2)
 
set.seed(12345)
df <- data.frame(date=seq(as.Date("2003/06/01"), by="month", length.out=103),myval=runif(103, min=-1, max=1))
df$date <- (df$date + months(1)) - days(1) # get last day of month
df$year <- as.numeric(format(as.Date(df$date), format="%Y"))
df$month <- as.numeric(format(as.Date(df$date), format="%m"))
# create quarterly label
df$qtr <- ifelse(df$month==3,"Jan-Mar",ifelse(df$month==6,"Apr-Jun",ifelse(df$month==9,"Jul-Sep",ifelse(df$month==12,"Oct-Dec",""))))
qtr <- df[ df$month %in% c(3,6,9,12), ] # extract quarter-end figures
 
p <- ggplot(qtr[qtr$date>='2004-01-01',], aes(x=qtr,y=year(date), fill = myval, label = sprintf("%1.1f%%", 100*myval))) + 
  scale_y_date(major="years", format="%Y") +
  scale_y_reverse(breaks=2003:2012, labels=2003:2012, expand=c(0,0)) + 
  geom_tile() + geom_text(size=geomtextsize,colour = "black") +
  scale_fill_gradient2(low = "blue", high = "red",,midpoint=0) +
  scale_x_discrete(expand=c(0,0))
 
print(p)

EDIT:
An image showing the final improved version incorporating Vincent's suggestions and the code used to generate it follow below.

set.seed(12345)
df <- data.frame(date=seq(as.Date("2003/06/01"), by="month", length.out=103),myval=runif(103, min=-1, max=1))
df$date <- (df$date + months(1)) - days(1) # get last day of month
df$year <- as.numeric(format(as.Date(df$date), format="%Y"))
df$month <- as.numeric(format(as.Date(df$date), format="%m"))
# create quarterly label
df$qtr <- ifelse(df$month==3,"Jan-Mar",ifelse(df$month==6,"Apr-Jun",ifelse(df$month==9,"Jul-Sep",ifelse(df$month==12,"Oct-Dec",""))))
df$qtr[ df$qtr=="" ] <- NA
df$display_year <- ifelse( df$month < 4, df$year - 1, df$year )
df$display_year <- paste( df$display_year, df$display_year + 1, sep="-" )
df$qtr <- ordered(df$qtr, levels=c("Apr-Jun", "Jul-Sep", "Oct-Dec", "Jan-Mar"))
qtr <- df[ df$month %in% c(3,6,9,12), ]
qtr$display_year <- factor( qtr$display_year, levels = sort( unique(qtr$display_year), decreasing=TRUE ) )
 
p <- ggplot(qtr, aes(x=qtr,y=display_year, fill = myval, label = sprintf("%1.1f%%", 100*myval))) + 
scale_y_discrete(expand=c(0,0)) +
geom_tile() + geom_text(size=geomtextsize,colour = "black") +
scale_fill_gradient2(low = "blue", high = "red",,midpoint=0) +
scale_x_discrete(expand=c(0,0))
p