0
0 Comments

I am trying to fill NA values in a column with other non-NA values within the same group in R. So my data looks something like this:

df
       id year pop
1  E1 2000  NA
2  E2 2000  NA
3  E2 2001  NA
4  E2 2003 120
5  E2 2005 125
6  E3 1999 115
7  E3 2001 300
8  E3 2003  NA
9  E4 2004  10
10 E4 2005  NA
11 E4 2008  NA
12 E4 2009   9
13 E5 2002  12
14 E5 2003  80

And I want NA values to have either the last non-NA or the next non-NA value of 

pop
within the same group of 
id
. To look something like this:

    df.desired
   id year pop
1  E1 2000  NA
2  E2 2000 120
3  E2 2001 120
4  E2 2003 120
5  E2 2005 125
6  E3 1999 115
7  E3 2001 300
8  E3 2003 300
9  E4 2004  10
10 E4 2005  10
11 E4 2008   9
12 E4 2009   9
13 E5 2002  12
14 E5 2003  80

I tried different things with both 

zoo::na.locf()
and 
dplyr::fill()
but I keep having two main issues: 1. I get errors because entire groups only have NA (like 
id == "E1"
here) and 2. I can only choose either the last or the naxt non-NA value. These are some examples of what I’ve tried:

library(tidyverse)
library(zoo)
    df.desired <- df %>%
group_by(id) %>%
      arrange(year)%>%
      mutate(pop_imputated = pop)%>%
      fill(pop_imputated)%>%
      ungroup()


df.desired <- df %>%
  group_by(id) %>%
  arrange(year)%>%
  mutate(pop_imputated = zoo::na.locf(pop))%>%
  fill(pop_imputated)%>%
  ungroup()

Any ideas? Thanks a lot!

Anonymous Asked question May 13, 2021