I am trying to impute missing value NA with interpolation by multiple groups.
I just subset a simple example:
Year ST CC ID MP PS
2002 15 3 3 NA 1.5
2003 15 3 3 NA 1.5
2004 15 3 3 193 1.5
2005 15 3 3 193 1.5
2006 15 3 3 348 1.5
2007 15 3 3 388 1.5
2008 15 3 3 388 1.5
1999 53 33 1 NA 3.4
2000 53 33 1 NA 3.4
2002 53 33 1 NA 2.9
2003 53 33 1 NA 2.6
2004 53 33 1 NA 2.6
2005 53 33 1 170 3.8
2006 53 33 1 170 3.0
2007 53 33 1 330 4.2
2008 53 33 1 330 5.0
I used na.approx() but got wrong. It seems that my data has missing value in the first observation within each group.
setDT(dt)[, MP_interpolate := na.approx(MP, na.rm = T), .(Year, ST, CC, ID)]
setDT(dt)[, MP_interpolate := if(length(na.omit(MP))<2) MP else na.approx(MP, na.rm=TRUE), .(Year, ST, CC, ID)]
I also tried package imputeTS but not work.
Both are incorrect. Does it mean using interpolation to impute missing is not a good idea?
I'm not sure which imputation way is better (Do not want to use mean or median). I'm thinking about using PS trend to impute MP. (Just my thought, not question)
You could try imputeTS::na_kalman in an ave. This also extrapolates, what you probably want.
library(imputeTS)
dt$MP.imp <- with(dt, ave(MP, ST, CC, ID, FUN=na_kalman))
# Year ST CC ID MP PS MP.imp
# 1 2001 15 3 3 NA 1.5 193.0000
# 2 2002 15 3 3 NA 1.5 193.0000
# 3 2003 15 3 3 NA 1.5 193.0000
# 4 2004 15 3 3 193 1.5 193.0000
# 5 2005 15 3 3 193 1.5 193.0000
# 6 2006 15 3 3 348 1.5 348.0000
# 7 2007 15 3 3 388 1.5 388.0000
# 8 2008 15 3 3 388 1.5 388.0000
# 9 1999 53 33 1 NA 3.4 170.2034
# 10 2000 53 33 1 NA 3.4 166.3867
# 11 2002 53 33 1 NA 2.9 164.4496
# 12 2003 53 33 1 NA 2.6 165.0018
# 13 2004 53 33 1 NA 2.6 168.6527
# 14 2005 53 33 1 170 3.8 170.0000
# 15 2006 53 33 1 170 3.0 170.0000
# 16 2007 53 33 1 330 4.2 330.0000
# 17 2008 53 33 1 330 5.0 330.0000
You probably want to include year in the ave, it wouldn't have worked otherwise in your sample data.