Consider a fragmented dataset like this:
ID Date Value
1 1 2012-01-01 5065
4 1 2012-01-04 1508
5 1 2012-01-05 9489
6 1 2012-01-06 7613
7 2 2012-01-07 6896
8 2 2012-01-08 2643
11 3 2012-01-02 7294
12 3 2012-01-03 8726
13 3 2012-01-04 6262
14 3 2012-01-05 2999
15 3 2012-01-06 10000
16 3 2012-01-07 1405
18 3 2012-01-09 8372
Notice that observations are missing for (2,3,9,10,17). What I would like, is to fill out some of these gaps in the dataset with "Value" = 0, like so:
ID Date Value
1 1 2012-01-01 5920
2 1 2012-01-02 0
3 1 2012-01-03 0
4 1 2012-01-04 8377
5 1 2012-01-05 7810
6 1 2012-01-06 6452
7 2 2012-01-07 3483
8 2 2012-01-08 5426
9 2 2012-01-09 0
11 3 2012-01-02 7854
12 3 2012-01-03 1948
13 3 2012-01-04 7141
14 3 2012-01-05 5402
15 3 2012-01-06 6412
16 3 2012-01-07 7043
17 3 2012-01-08 0
18 3 2012-01-09 3270
The point is that the zeros only should be inserted if there is a past observation for the same (grouped) ID. I would like to avoid any loops, as the full dataset is quite large.
Any suggestions? To reproduce the dataframe:
df <- data.frame(matrix(0, nrow = 18, ncol = 3,
dimnames = list(NULL, c("ID","Date","Value"))) )
df[,1] = c(1,1,1,1,1,1,2,2,2,3,3,3,3,3,3,3,3,3)
df[,2] = seq(as.Date("2012-01-01"),
as.Date("2012-01-9"),
by=1)
df[,3] = sample(1000:10000,18,replace=T)
df = df[-c(2,3,9,10,17),]
There are already some solid answers here, but I would recommend checking out the package padr.
library(dplyr)
library(padr)
df %>%
pad(start_val = as.Date("2012-01-01"),
end_val = as.Date("2012-01-09"),
group = "ID") %>%
fill_by_value(Value)
The package gives some pretty intuitive functions for summarizing Date columns as well.