CODEWITHSUNDEEP
rpanel-data
user49017
user49017

Reputation: 137

Extending a panel data column using projected growth rates from an other column

Suppose I have panel data on a variable up to 2016, and I have projected gross growth rates (1 + the growth rate) of that variable for the years 2017 and 2018. How do I extend the variable of interest into 2018 using the projected gross growth rates?

Here is an example of the data I have: 

Country Year var g
A 2016 5 1.01
B 2016 6 0.98
C 2016 7 1.05
A 2017 NA 1.06
B 2017 NA 0.97
C 2017 NA 1.09
A 2018 NA 1.04
B 2018 NA 1.02
C 2018 NA 0.91

I want to replace the NAs in 2017 and 2018 by the project var using g. So, in 2017, for Country A, var would be 5*1.06=5.3.

The resulting data frame should be something like this: 

Country Year var g
A 2016 5 1.01
B 2016 6 0.98
C 2016 7 1.05
A 2017 5.3 1.06
B 2017 5.82 0.97
C 2017 7.63 1.09
A 2018 5.512 1.04
B 2018 5.9364 1.02
C 2018 6.9433 0.91

Upvotes: 0

Views: 44

Answers (1)

Evan Friedland
Evan Friedland

Reputation: 3194

Give this a try:

df <- data.frame(Country = rep(c("A","B","C"), 3), Year = rep(2016:2018, each = 3), var = c(5,6,7,NA,NA,NA,NA,NA,NA), g = c(1.01,0.98,1.05,1.06,0.97,1.09,1.04,1.02,0.91))

x <- split(df, df$Country)
for(l in 1:length(x)){ # list length
  for(i in 1:nrow(x[[l]])){ # matrix rows
    # because I'm using i + 1, we want to stop if we are on the final i
    if(i != nrow(x[[l]])) x[[l]]$var[i+1] <- x[[l]]$var[i] * x[[l]]$g[i+1]
  }
}
df2 <- do.call(rbind,x) # recombine the list by row
df2 <- df2[order(df2[,"Year"]),] # order so that it is sorted by year again
rownames(df2) <- NULL # we can remove the names with 

df2
#Country Year    var    g
#1       A 2016 5.0000 1.01
#2       B 2016 6.0000 0.98
#3       C 2016 7.0000 1.05
#4       A 2017 5.3000 1.06
#5       B 2017 5.8200 0.97
#6       C 2017 7.6300 1.09
#7       A 2018 5.5120 1.04
#8       B 2018 5.9364 1.02
#9       C 2018 6.9433 0.91

Upvotes: 1

Related Questions