Interpolating data above the maximum value in a panel with R

Question

I have a panel with missing values that I need to interpolate.

a <- data.frame(id= c(1,1,1,1,1,1,1,2,2,2,2,2,2,2), year=1:7, index=c(1,NA,NA,NA,3,NA,NA, 2,NA,NA,NA,5,NA,NA))

The issue is that I don't have the final value, so I would like to use the same interpolation line to predict values above the last observation reported. Additionally, the interpolation should be "by id", rather than considering the first observation of the next id as part of the line.

I've tried the basic interpolation but it does stop at the latest values, or predicts using the next id:

a <- na.approx(a)

output I get:

       id year    index
 [1,]  1    1 1.000000
 [2,]  1    2 1.500000
 [3,]  1    3 2.000000
 [4,]  1    4 2.500000
 [5,]  1    5 3.000000
 [6,]  1    6 2.666667
 [7,]  1    7 2.333333
 [8,]  2    1 2.000000
 [9,]  2    2 2.750000
[10,]  2    3 3.500000
[11,]  2    4 4.250000
[12,]  2    5 5.000000
[13,]  2    6       NA
[14,]  2    7       NA

desired output:

        id year    index
 [1,]  1    1 1.000000
 [2,]  1    2 1.500000
 [3,]  1    3 2.000000
 [4,]  1    4 2.500000
 [5,]  1    5 3.000000
 [6,]  1    6 3.500000
 [7,]  1    7 4.000000
 [8,]  2    1 2.000000
 [9,]  2    2 2.750000
[10,]  2    3 3.500000
[11,]  2    4 4.250000
[12,]  2    5 5.000000
[13,]  2    6 5.750000
[14,]  2    7 6.500000
    

Answer 1

With lm you can get the slope that this simple interpolation is using, and then use that slope to generate new values with predict. But maybe there's a simpler solution

mod <- lm(index ~ year, a)

a[,2] <- predict(mod, newdata=data.frame(year=a$year))

EDIT 1

No, for each id we will run a different lm. To do that we select the part of a with a unique id inside a loop, and run the lm only with that part:

for(i in unique(a$id)){
  ai = a[a$id==i,]
  mod = lm(index ~ year, ai)
  a[a$id==i,3] = predict(mod, newdata=data.frame(year=ai$year))}

Answer 2

Assuming the following data which is modified from the question in light of the comments, we define a function which returns NA if all index values are NA and na.spline otherwise and then apply it per id:

library(zoo)

a <- data.frame(id= c(1,1,1,1,1,1,1,2,2,2,2,2,2,2), year=1:7, 
  index=c(NA,NA,NA,NA,NA,NA,NA, 2,NA,NA,NA,5,NA,NA))

na_spline <- function(x) if (all(is.na(x))) NA else na.spline(x)
transform(a, index = ave(index, id, FUN = na_spline))
##    id year index
## 1   1    1    NA
## 2   1    2    NA
## 3   1    3    NA
## 4   1    4    NA
## 5   1    5    NA
## 6   1    6    NA
## 7   1    7    NA
## 8   2    1  2.00
## 9   2    2  2.75
## 10  2    3  3.50
## 11  2    4  4.25
## 12  2    5  5.00
## 13  2    6  5.75
## 14  2    7  6.50

Answer 3

Does this work:

library(dplyr)
library(tidyr)
library(purrr)
a %>% mutate(index = replace_na(index, 0)) %>% 
      mutate(index = accumulate(index, ~ .5 + .x))
  year index
1    1   1.0
2    2   1.5
3    3   2.0
4    4   2.5
5    5   3.0
6    6   3.5
7    7   4.0
> 

Answer 4

It looks like the values follow a linear pattern. You could then use fit=lm(index~year, data=a) and then predict the values using a$index=fit$coef[2]*a$year+fit$coef[1].