filling in missing value from lagged value in R

Question

I have a dataframe like this:

ID     year  mstat change lint
8049    1995    1   NA    NA
8049    1996    1   0     1994
8049    1997    1   NA    NA
8049    1998    1   0     1996
8049    1999    1   NA    NA
8049    2000    1   0     1998
8049    2001    1   NA      NA
8049    2002    1   0     2000
8049    2003    NA  NA    NA
8049    2004    NA  NA    NA
8049    2005    NA  NA    NA
8049    2006    NA  NA    NA
8049    2007    NA  NA    NA
8049    2008    1   0     2002
8049    2009    NA  NA    NA
8049    2010    1   0     2008
8049    2011    NA  NA    NA
8049    2012    2   1     2008
8049    2013    NA  NA    NA

mstat=marital status,
lint=last interview date
change=change in marital status

For a particular individual, the last interview date in year=2008 is 2002. and the change in marital status in year=2008 is 0. It means that marital status from 2003 through 2007 should be the same as marital status in 2002 or in 2008. How can I fill in mstat from 2003 through 2007 using 2002/2008 mstat for this individual as well as for the entire dataset in R? (For different individuals the missing values are in different years. )

My desired output is (in the mstat_updated column, from 2003 through 2007, is 1.)

 ID   year mstat change lint mstat_updated
8049   2000 1   0       1998    1
8049   2001 1   NA      NA      1
8049   2002 1   0       2000    1
8049   2003 NA  NA      2002    1
8049   2004 NA  NA     2002     1
8049   2005 NA  NA      2002    1
8049   2006 NA  NA      2002    1
8049   2007 NA  NA      2002    1
8049   2008 1   0       2002    1
8049   2009 NA  NA      2008    1
8049   2010 1   0       2008    1
8049   2011 NA  NA      2010    NA
8049   2012 2   1       2010    2

After trying out the code as Ben suggested I get the following table where all the change values are TRUE.

df %>%
  fill(lint, .direction = 'up') %>%
  group_by(lint) %>%
  mutate(anychange = any(change > 0),
         mstat_updated = ifelse(anychange, mstat, last(mstat)))




ID    year  mstat change lint anychange mstat_updated
8049    1992    1   1   1991    TRUE    1
8049    1993    1   0   1992    TRUE    1
8049    1994    1   0   1993    TRUE    1
8049    1995    NA  NA  1994    TRUE    NA
8049    1996    1   0   1994    TRUE    1
8049    1997    NA  NA  1996    TRUE    NA
8049    1998    1   0   1996    TRUE    1
8049    1999    NA  NA  1998    TRUE    NA
8049    2000    1   0   1998    TRUE    1
8049    2001    NA  NA  2000    TRUE    NA
8049    2002    1   0   2000    TRUE    1
8049    2003    NA  NA  2002    TRUE    NA
8049    2004    NA  NA  2002    TRUE    NA
8049    2005    NA  NA  2002    TRUE    NA
8049    2006    NA  NA  2002    TRUE    NA
8049    2007    NA  NA  2002    TRUE    NA
8049    2008    1   0   2002    TRUE    1
8049    2009    NA  NA  2008    TRUE    NA
8049    2010    1   0   2008    TRUE    1
8049    2011    NA  NA  2008    TRUE    NA
8049    2012    2   1   2008    TRUE    2
8049    2013    NA  NA  2012    TRUE    NA
8049    2014    3   1   2012    TRUE    3
8049    2015    NA  NA  2014    TRUE    NA
8049    2016    3   0   2014    TRUE    3


 head(df,20L)
       ID    year  mstat change  lint anychange mstat_updated
                   
 1 8049  1993      1      0  1992        TRUE      1
 2 8049  1994      1      0  1993        TRUE      1
 3 8049  1995     NA     NA  1994        TRUE      NA
 4 8049  1996      1      0  1994        TRUE      1
 5 8049  1997     NA     NA  1996        TRUE     NA
 6 8049  1998      1      0  1996        TRUE      1
 7 8049  1999     NA     NA  1998        TRUE     NA
 8 8049  2000      1      0  1998        TRUE      1
 9 8049  2001     NA     NA  2000        TRUE      NA
10 8049  2002      1      0  2000        TRUE      1
11 8049  2003     NA     NA  2002        TRUE      NA
12 8049  2004     NA     NA  2002        TRUE      NA
13 8049  2005     NA     NA  2002        TRUE      NA
14 8049  2006     NA     NA  2002        TRUE      NA
15 8049  2007     NA     NA  2002        TRUE      NA
16 8049  2008      1      0  2002        TRUE       1
17 8049  2009     NA     NA  2008        TRUE       NA
18 8049  2010      1      0  2008        TRUE       1
19 8049  2011     NA     NA  2008        TRUE       NA
20 8049  2012      2      1  2008        TRUE       2


dput() gives the following structure

  structure(list(ID = structure(c(4143L, 4143L, 4143L, 4143L, 4143L, 
   4143L, 4143L, 4143L, 4143L, 4143L), .Label = c("1", "2", "3", 
  .............
structure(15:24, .Label = c("1979", 
"1980", "1981", "1982", "1983", "1984", "1985", "1986", "1987", 
"1988", "1989", "1990", "1991", "1992", "1993", "1994", "1995", 
"1996", "1997", "1998", "1999", "2000", "2001", "2002", "2003", 
"2004", "2005", "2006", "2007", "2008", "2009", "2010", "2011", 
"2012", "2013", "2014", "2015", "2016"), class = "factor"), mstat = c(1L, 
1L, NA, 1L, NA, 1L, NA, 1L, NA, 1L), change = c(0L, 0L, NA, 0L, 
 NA, 0L, NA, 0L, NA, 0L), lint = c(1992, 1993, 1994, 1994, 1996, 
 1996, 1998, 1998, 2000, 2000), anychange = c(TRUE, TRUE, TRUE, 
 TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE), mstat_updated = c(1L, 
 1L, NA, 1L, NA, 1L, NA, 1L, NA, 1L)), class = c("tbl_df", "tbl", 
 "data.frame"), row.names = c(NA, -10L))

So, after following Ben's absolutely helpful suggestions, I finally got what I wanted.

First, I fixed the lint year by year (it is probably not efficient but it worked fine anyways) where lint was not correct in the data (e.g. as Ben pointed out 2012 data - the last interview lint is 2008, but there was data from 2010 that was more recent). My example codes are:

df$lint_up<-ifelse(df$year==2012 & df$lint==2008 & lag(df$lint,2)==2008, 2010, df$lint)

 df$lint_up<-ifelse(df$year==2004  & df$lint==2000 & lag(df$lint,2)==2000, 2002, df$lint)

 df$lint_up<-ifelse(df$year==2000  & df$lint==1996 & lag(df$lint,2)==1996, 1998, df$lint)

Then I fill in the lint_updated (lint_up) variable:

mk<-df%>%
  group_by(ID)%>%
  fill(lint_up,.direction = 'up')

Now, for mstat_updated, I followed mainly Ben's suggestion but I group_by both ID and lint_up:

abc<-mk %>%
  group_by(lint_up, ID) %>%
  mutate(mstat_updated = ifelse(is.na(mstat) & any(last(change==0)), 
  last(mstat), mstat))

user_3460670 · Answer

Edited 10/19/19:

Thanks for adding additional information. I hope this is closer.

One tidyverse approach is to use fill in the NA values for last interview lint years. Using .direction = 'up' you will fill in based on the most recent year data.

Then, would group_by last interview year (lint), to make changes based on those years alone. This would allow you to change years 2003-2007 based on 2008 mstat.

One other issue I found is with 2012 data - the last interview lint is 2008, but it seems there was data from 2010 that was more recent.

With the most recent edit, now will do the following:

Filter records where there is data
Create a new variable last_year_data - which is similar to lint but would catch 2010 where there is data, but lint missed
This will change NA to 0 in change variable - this will catch the any(change) to identify where mstat_updated should not be updated

I think this will address some of concerns - but may bring up others. If this isn't working, I don't mind either deleting my answer (in case others might more likely respond) - or you could try posting a new/revised question.

library(dplyr)

df %>%
  filter(!is.na(mstat) & (mstat >= 0)) %>%
  mutate(last_year_data = lag(year)) %>%
  right_join(df) %>% 
  fill(last_year_data, .direction = 'up') %>%
  group_by(last_year_data) %>%
  mutate(mstat_updated = last(mstat),
         change = replace_na(change, 0)) %>%
  mutate(mstat_updated = ifelse(any(change > 0) & is.na(mstat), mstat, mstat_updated))

# A tibble: 34 x 7
# Groups:   last_year_data [20]
      ID  year mstat change  lint last_year_data mstat_updated
                           
 1  8049  1983     0      0  1982           1983             0
 2  8049  1984     0      0  1983           1983             0
 3  8049  1985     0      0  1984           1984             0
 4  8049  1986     0      0  1985           1985             0
 5  8049  1987     0      0  1986           1986             0
 6  8049  1988     0      0  1987           1987             0
 7  8049  1989     0      0  1988           1988             0
 8  8049  1990     0      0  1989           1989             0
 9  8049  1991     0      0  1990           1990             0
10  8049  1992     1      1  1991           1991             1
11  8049  1993     1      0  1992           1992             1
12  8049  1994     1      0  1993           1993             1
13  8049  1995    NA      0  1994           1994             1
14  8049  1996     1      0  1994           1994             1
15  8049  1997    NA      0  1996           1996             1
16  8049  1998     1      0  1996           1996             1
17  8049  1999    NA      0  1998           1998             1
18  8049  2000     1      0  1998           1998             1
19  8049  2001    NA      0  2000           2000             1
20  8049  2002     1      0  2000           2000             1
21  8049  2003    NA      0  2002           2002             1
22  8049  2004    -5      0  2002           2002             1
23  8049  2005    NA      0  2002           2002             1
24  8049  2006    -5      0  2002           2002             1
25  8049  2007    NA      0  2002           2002             1
26  8049  2008     1      0  2002           2002             1
27  8049  2009    NA      0  2008           2008             1
28  8049  2010     1      0  2008           2008             1
29  8049  2011    NA      0  2008           2010            NA
30  8049  2012     2      1  2008           2010             2
31  8049  2013    NA      0  2012           2012            NA
32  8049  2014     3      1  2012           2012             3
33  8049  2015    NA      0  2014           2014             3
34  8049  2016     3      0  2014           2014             3

filling in missing value from lagged value in R

1 Answers