CODEWITHSUNDEEP
r
Wolf
Wolf

Reputation: 576

Quarterly Year over Year Growth Rate

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

Upvotes: 16

Views: 12984

Answers (4)

eipi10
eipi10

Reputation: 93821

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)]

Upvotes: 17

user20650
user20650

Reputation: 25854

Another base R solution. Requires that the date is in date format, so that the common months can be used as a grouping variable to which the function to calculate growth rate can be passed

# set date to a date objwct
df$date <- as.Date(df$date)

# order by date
df <- df[order(df$date), ]

# function to calculate differences
f <- function(x) c(NA, 100*diff(x)/x[-length(x)])

df$yoy <- ave(df$value, format(df$date, "%m"), FUN=f)
#          date value       yoy
# 1  2000-01-01  1592        NA
# 2  2000-04-01  1825        NA
# 3  2000-07-01  1769        NA
# 4  2000-10-01  1909        NA
# 5  2001-01-01  2022 27.010050
# 6  2001-04-01  2287 25.315068
# 7  2001-07-01  2169 22.611645
# 8  2001-10-01  2366 23.939235
# 9  2002-01-01  2001 -1.038576
# 10 2002-04-01  2087 -8.745081
# 11 2002-07-01  2099 -3.227294
# 12 2002-10-01  2258 -4.564666

or

c(rep(NA, 4,), 100* diff(df$value, lag=4) / head(df$value, -4))

Upvotes: 2

bgoldst
bgoldst

Reputation: 35314

df$yoy <- c(rep(NA,4),(df$value[5:nrow(df)]-df$value[1:(nrow(df)-4)])/df$value[1:(nrow(df)-4)]*100);
df;
##          date value       yoy
## 1  2000-01-01  1592        NA
## 2  2000-04-01  1825        NA
## 3  2000-07-01  1769        NA
## 4  2000-10-01  1909        NA
## 5  2001-01-01  2022 27.010050
## 6  2001-04-01  2287 25.315068
## 7  2001-07-01  2169 22.611645
## 8  2001-10-01  2366 23.939235
## 9  2002-01-01  2001 -1.038576
## 10 2002-04-01  2087 -8.745081
## 11 2002-07-01  2099 -3.227294
## 12 2002-10-01  2258 -4.564666

Upvotes: 5

Carbon
Carbon

Reputation: 3943

Here's a very simple solution:

YearOverYear<-function (x,periodsPerYear){
    if(NROW(x)<=periodsPerYear){
        stop("too few rows")
    }
    else{
        indexes<-1:(NROW(x)-periodsPerYear)
        return(c(rep(NA,periodsPerYear),(x[indexes+periodsPerYear]-x[indexes])/x[indexes]))
    }
}


> cbind(df,YoY=YearOverYear(df$value,4))
         date value         YoY
1  2000-01-01  1592          NA
2  2000-04-01  1825          NA
3  2000-07-01  1769          NA
4  2000-10-01  1909          NA
5  2001-01-01  2022  0.27010050
6  2001-04-01  2287  0.25315068
7  2001-07-01  2169  0.22611645
8  2001-10-01  2366  0.23939235
9  2002-01-01  2001 -0.01038576
10 2002-04-01  2087 -0.08745081
11 2002-07-01  2099 -0.03227294
12 2002-10-01  2258 -0.04564666

Upvotes: 8

Related Questions