Quarterly Year over Year Growth Rate

Question

I have been trying to calculate the growth rate comparing quarter 1 from one year to quarter 1 for the following year.
In excel the formula would look like this ((B6-B2)/B2)*100.

What is the best way to accomplish this in R? I know how to get the differences from period to period, but cannot accomplish it with 4 time periods' difference.

Here is the code:

date <- c("2000-01-01","2000-04-01", "2000-07-01",
          "2000-10-01","2001-01-01","2001-04-01",
          "2001-07-01","2001-10-01","2002-01-01",
          "2002-04-01","2002-07-01","2002-10-01")
value <- c(1592,1825,1769,1909,2022,2287,2169,2366,2001,2087,2099,2258)
df <- data.frame(date,value)

Which will produce this data frame:

    date       value
1   2000-01-01  1592
2   2000-04-01  1825
3   2000-07-01  1769
4   2000-10-01  1909
5   2001-01-01  2022
6   2001-04-01  2287
7   2001-07-01  2169
8   2001-10-01  2366
9   2002-01-01  2001
10  2002-04-01  2087
11  2002-07-01  2099
12  2002-10-01  2258

Answer 1

Here's an option using the dplyr package:

# Convert date column to date format
df$date = as.POSIXct(df$date)

library(dplyr)  
library(lubridate)

In the code below, we first group by month, which allows us to operate on each quarter separately. The arrange function just makes sure that the data within each quarter is ordered by date. Then we add the yearOverYear column using mutate which calculates the ratio of the current year to the previous year for each quarter.

df = df %>% group_by(month=month(date)) %>%
  arrange(date) %>%
  mutate(yearOverYear=value/lag(value,1))

         date value month yearOverYear
1  2000-01-01  1592     1           NA
2  2001-01-01  2022     1    1.2701005
3  2002-01-01  2001     1    0.9896142
4  2000-04-01  1825     4           NA
5  2001-04-01  2287     4    1.2531507
6  2002-04-01  2087     4    0.9125492
7  2000-07-01  1769     7           NA
8  2001-07-01  2169     7    1.2261164
9  2002-07-01  2099     7    0.9677271
10 2000-10-01  1909    10           NA
11 2001-10-01  2366    10    1.2393924
12 2002-10-01  2258    10    0.9543533  

If you prefer to have the data frame back in overall date order after adding the year-over-year values:

df = df %>% group_by(month=month(date)) %>%
  arrange(date) %>%
  mutate(yearOverYear=value/lag(value,1)) %>%
  ungroup() %>% arrange(date)

Or using data.table

library(data.table) # v1.9.5+
setDT(df)[, .(date, yoy = (value-shift(value))/shift(value)*100), 
            by = month(date)
        ][order(date)]