-1

I've a panel dataset of several banks, each from 1997 to 2015, with annual observations s.t.:

CODE      COUNTRY      YEAR      LOANS_NET      ...other variables
671405      AT         1997       39028938
671405      AT         1998       41033237
671405      AT         1999       35735062
...
...
671405      AT         2015      130701872
...
30885R      DE         2004      200024673
...
...

Using R, I need to compute additional two columns:

1) LOANS_NET growth rate at 1-year horizon

2) LOANS_NET growth rate at 3-years horizon, which must be annualized, once calculated.

E.g.: 

Loan Growth 3-year = [Bank's(i) LOANS_NET Year(t) / Bank's(i) LOANS_NET Year(t-3)] -1

nb: data contains lots of missing values, code must consider that issue! :)

Dan
  • 21
  • 4

1 Answers1

2

@Dan Do you use any packages? I recommend you using zoo and data.table packages and transform dates in the following way:

DT[, YearNumeric := as.numeric(YEAR)]
DT[, PreviousYearLoanNet := .SD[match(YearNumeric - 1, .SD$YearNumeric), LOANS_NET], by=CODE]

Here, you create a column with previous (-1 year) loan values. Then you create a new column with growth:

DT[,Growth1Y:= (YEARLOANNET- PreviousYearLoanNet)/PreviousYearLoanNet]

And then you do whatever you want:) Cheers!

Milosz
  • 118
  • 8
  • thank you so much mate! Just one question: do you have any idea about doing the same purpose using plyr package? because I tried using codes reported [Here](http://stackoverflow.com/questions/19824601/how-calculate-growth-rate-in-long-format-data-frame) but the results are so different each other :S – Dan Dec 01 '16 at 11:38
  • What is different precisely? I don't have much experience with dplyr but I guess you can transform it with ddply function, right? – Milosz Dec 01 '16 at 12:32
  • I used this script to obtain 3-year growth rate column: data1<-transform(df, Growth=ave(Value, Category, FUN=function(x) c(NA,exp(diff(log(x),lag = 3)-1))) but the rates are really different if compared them with ones whose came out from your procedure. I suppose I made a mistake since I'm not sure the script right above is correct.. – Dan Dec 01 '16 at 12:48
  • The function that you use, uses geometric growth if I'm not mistaken, right? My procedure uses arithmetic growth, i.e. (t+1/t)/t. I also calculated it for 1 year, and you calculated 3 year geometric average growth rate. – Milosz Dec 01 '16 at 13:13
  • You're tremendously right, my fault. Thus I think it would be interesting analysing effects on y (the dependent variable I'm going to study) both with arithmetic and the geometric growth rates, one as control as the other. :) Thank you again – Dan Dec 01 '16 at 16:50
  • You're welcome! You can mark my answer as "solved" :) – Milosz Dec 01 '16 at 16:54