Fill missing values based on group 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  &lt;NA&gt;
4   1  &lt;NA&gt;
5   2 24-04
6   2  &lt;NA&gt;
7   2  &lt;NA&gt;
8   2  &lt;NA&gt;
9   3 23-04
10  3  &lt;NA&gt;
11  3  &lt;NA&gt;
12  3  &lt;NA&gt;
13  4  &lt;NA&gt;
14  4  &lt;NA&gt;
15  4  &lt;NA&gt;
16  4  &lt;NA&gt;



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  &lt;NA&gt;
14  4  &lt;NA&gt;
15  4  &lt;NA&gt;
16  4  &lt;NA&gt;



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")
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 &lt;- 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 %&gt;% group_by(id) %&gt;% 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).