Calculating growth rates and grouping by two variables

1
votes

I would like to calculate logarithmic growth rates and I am struggling to make it work with two variables in the by-clause of the data.table. I do have a data.table which covers production over time and I would like to calculate the logarithmic growth rate over time and per group.

library(zoo)
library(data.table)
library(ggplot2)
library(dplyr)
DT <- structure(list(Year.Quarter = structure(c(2015, 2015, 2015, 2015, 
                                          2015, 2015.25, 2015.25, 2015.25, 2015.25, 2015.25, 2015.5, 2015.5, 
                                          2015.5, 2015.5, 2015.5, 2015.75, 2015.75, 2015.75, 2015.75, 2015.75, 
                                          2016, 2016, 2016, 2016, 2016, 2016.25, 2016.25, 2016.25, 2016.25, 
                                          2016.25), class = "yearqtr")
                                        ,Group = structure(c(2L, 1L, 4L, 
                                                             3L, NA, 2L,   1L, 4L, 3L, NA, 2L, 1L, 4L, 3L, NA, 2L, 1L, 4L, 3L, NA, 2L, 1L, 4L, 3L, NA, 2L, 1L, 4L, 3L, NA), .Label = c("1", "2", "3", "4"), class = "factor")
                                        , Conventional.Prod = c(11.78, 7.31, 7.34, 9.44, 28.72, 11.32, 5.27, 7.47, 8.08, 27.14, 11.49, 
                                                                4.65, 7.63, 7.07, 25.93, 10.69, 3.68, 6.96, 6.72, 18.31, 9.28, 
                                                                 3.69, 6.86, 6.34, 19.14, 9.25, 3.69, 6.9, 6.16, 17.7)
                                       , Unconventional.Prod = c(15.22, 10.69, 7.66, 15.56, 30.28, 15.68, 10.73, 7.53, 15.92, 29.86, 
                                                        13.51, 10.35, 7.37, 15.93, 28.07, 13.31, 10.32, 7.04, 16.28, 
                                25.69, 12.72, 9.31, 7.14, 16.66, 25.86, 12.75, 9.31, 7.1, 16.84, 24.3))
                        , .Names = c("Year.Quarter", "Group", "Conventional.Prod", "Unconventional.Prod"), row.names = c(NA, -30L), class = c("data.table", 
                                                      "data.frame"))

DT[, .( Conventional.Prod
       , d.log.Conventional.Prod = log(Conventional.Prod, base = exp(1)) - shift(log(Conventional.Prod, base = exp(1)), n = 1L , fill = NA, type = "lag")
       , Log.Conventional.Prod = log(Conventional.Prod, base = exp(1))
       , Lag.Log.Conventional.Prod = shift(log(Conventional.Prod, base = exp(1)), n = 1L , fill = NA, type = "lag")
       ), by = list(Group, Year.Quarter)]

I don't know, why it's not grouped and ordered properly by the Group variable and why it's not possible to calculate the lagged value of the production. I don't think there's a problem with the factor variable, since ordering works just fine. 

DT[order(Group, Year.Quarter)]

 Year.Quarter Group Conventional.Prod Unconventional.Prod
 1:      2015 Q1     1              7.31               10.69
 2:      2015 Q2     1              5.27               10.73
 3:      2015 Q3     1              4.65               10.35
 4:      2015 Q4     1              3.68               10.32
 5:      2016 Q1     1              3.69                9.31
 6:      2016 Q2     1              3.69                9.31
 7:      2015 Q1     2             11.78               15.22
 8:      2015 Q2     2             11.32               15.68
 9:      2015 Q3     2             11.49               13.51
10:      2015 Q4     2             10.69               13.31
[...]
2
rdata.table

2 Answers

1
votes

You can do this:

setkey(DT, Group, Year.Quarter)

logG = function(x) c(NA, diff(log(x)))

DT[!is.na(Group), .(Year.Quarter, logG(Conventional.Prod), logG(Unconventional.Prod)), by='Group']

#     Group Year.Quarter           V2            V3
#  1:     1      2015 Q1           NA            NA
#  2:     1      2015 Q2 -0.327212911  0.0037348316
#  3:     1      2015 Q3 -0.125163143 -0.0360570369
#  4:     1      2015 Q4 -0.233954467 -0.0029027597
#  5:     1      2016 Q1  0.002713706 -0.1029946688
#  6:     1      2016 Q2  0.000000000  0.0000000000
#  7:     2      2015 Q1           NA            NA
#  8:     2      2015 Q2 -0.039832105  0.0297756625
#  9:     2      2015 Q3  0.014906019 -0.1489558630
# 10:     2      2015 Q4 -0.072168367 -0.0149145196
# 11:     2      2016 Q1 -0.141447178 -0.0453400745
# 12:     2      2016 Q2 -0.003237995  0.0023557137
# ...
0
votes

Expanding on the answer by @sirallen I do get the solution without any additional function and only with data.table tools.

setkey(DT, Group, Year.Quarter)
DT[, .(Year.Quarter, Conventional.Prod
       , d.log.Conventional.Prod = log(Conventional.Prod, base = exp(1)) - shift(log(Conventional.Prod, base = exp(1)), n = 1L , fill = NA, type = "lag")
       , Log.Conventional.Prod = log(Conventional.Prod, base = exp(1))
       , Lag.Log.Conventional.Prod = shift(log(Conventional.Prod, base = exp(1)), n = 1L , fill = NA, type = "lag")
       ), by = list(Group)]

It would be great if someone could explain why it doesn't work when grouping by two variables.