Fill missing values based on group

Question

I am trying to fill values based on group, in my case id. I would like to fill the missing values according to the available date info for each id.

   id  date
1   1 23-04
2   1 23-04
3   1  <NA>
4   1  <NA>
5   2 24-04
6   2  <NA>
7   2  <NA>
8   2  <NA>
9   3 23-04
10  3  <NA>
11  3  <NA>
12  3  <NA>
13  4  <NA>
14  4  <NA>
15  4  <NA>
16  4  <NA>

What I need is:

    id  date
1   1 23-04
2   1 23-04
3   1 23-04
4   1 23-04
5   2 24-04
6   2 24-04
7   2 24-04
8   2 24-04
9   3 23-04
10  3 23-04
11  3 23-04
12  3 23-04
13  4  <NA>
14  4  <NA>
15  4  <NA>
16  4  <NA>

I figured out a loop, but I would like to avoid it because my data has 23 millions rows:

for(i in 2:nrow(dta)){
  if(dta$id[i-1] == dta$id[i])
  {
    dta$date[i] = dta$date[i-1]
  }
}

I cannot figure out how to translate this into dplyr syntax:

dta = structure(list(id = structure(c(1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 
3L, 3L, 3L, 3L, 4L, 4L, 4L, 4L), .Label = c("1", "2", "3", "4"
), class = "factor"), date = structure(c(1L, 1L, 1L, 1L, 2L, 
2L, 2L, 2L, 1L, 1L, 1L, 1L, NA, NA, NA, NA), .Label = c("23-04", 
"24-04"), class = "factor")), .Names = c("id", "date"), row.names = c(NA, 
-16L), class = "data.frame")

Answer 1

In R this is usually solved using the na.locf (Last Observation Carried Forward) function from the zoo package.

See also here:

• Fill in NA based on the last non-NA value for each group in R
• Using dplyr window-functions to make trailing values

---

# test data
x <- read.table(text="id;date
1;23-04
1;23-04
1;NA
1;NA
2;24-04
2;NA
2;NA
2;NA
3;23-04
3;NA
3;NA
3;NA
4;NA
4;NA
4;NA
4;NA", header=TRUE, sep=";")

library("dplyr")
library("zoo")

x %>% group_by(id) %>% transmute(date=na.locf(date, na.rm=FALSE))
Source: local data frame [16 x 2]
Groups: id

   id  date
1   1 23-04
2   1 23-04
3   1 23-04
4   1 23-04
5   2 24-04
6   2 24-04
7   2 24-04
8   2 24-04
9   3 23-04
10  3 23-04
11  3 23-04
12  3 23-04
13  4    NA
14  4    NA
15  4    NA
16  4    NA

Another option are rolling self-joins supported by the data.table package (see here).

Answer 2

There is no need for packages. This is easily and efficiently done with basic syntax.

# Create data.table

dtk <- data.table( var=c(NA,NA,1,NA,NA,2,3,NA,NA,NA,NA,4,5,NA,NA,NA,NA,NA,6,7,NA,NA), group=c("A","A","A","A","A","A","A","A","A","B","B","B","B","B","B","B","B","B","B","B","B","B"))

# Backward fill

dtk[,order:= (1:.N)];
setorder(dtk,-order);
dtk[, var_backward_fill := var[1], by=  .( group , cumsum(!is.na(var))) ];
setorder(dtk, order);
dtk[,order:= NULL];

# Forward fill

dtk[, var_forward_fill  := var[1], by=  .( group , cumsum(!is.na(var))) ]