Fill time series data frame with dummy zero

Question

I suppose this is a pretty simple questions, none the less I can't get my head around it.

Consider this data frame:

df <- data.frame(            
week = c(1,1,1,2,3,3,3),     
id = c(12,13,14,12,12,13,14),
x = sample(c(100:200), 7))   
df                           
#>   week id   x
#> 1    1 12 126
#> 2    1 13 166
#> 3    1 14 129
#> 4    2 12 128
#> 5    3 12 136
#> 6    3 13 120
#> 7    3 14 115

I would like to create a dummy value 0 or NA for x for week 2 where there are no observations for id 13 and 14. In other words: so that x = 0 for week 2 id 13 and 14. Preferably with dplyr.

Any ideas on how to do this?

Try [complete](https://rdrr.io/cran/tidyr/man/complete.html), this post is a duplicate, will try to find the link. — zx8754, Jun 12 '18 at 11:34
I have reopened this because the answer at the purported duplicate can be simplified substantially. — G. Grothendieck, Jun 12 '18 at 11:48

G. Grothendieck · Accepted Answer · 2018-06-12T14:11:45.007

Although this question was marked as a duplicate group by used in there is not actually needed here. Also we have added zoo and several base answers.

1) complete (tidyr) complete in tidyr:

library(tidyr)

complete(DF, week, id)

giving:

# A tibble: 9 x 3
   week    id     x
  <int> <int> <int>
1     1    12   126
2     1    13   166
3     1    14   129
4     2    12   128
5     2    13    NA
6     2    14    NA
7     3    12   136
8     3    13   120
9     3    14   115

2) read.zoo/fortify.zoo (zoo) Create a zoo object splitting on week and then convert it back to data.frame using melt=TRUE:

library(zoo)

z <- read.zoo(DF, index = "id", split = "week")
fortify.zoo(z, melt = TRUE, names = names(DF)[c(2:1, 3)])

giving:

  id week   x
1 12    1 126
2 13    1 166
3 14    1 129
4 12    2 128
5 13    2  NA
6 14    2  NA
7 12    3 136
8 13    3 120
9 14    3 115

3) reshape (base) Reshape to wide form and then back to long form:

wide <- reshape(DF, idvar = "week", timevar = "id", dir = "wide")
long <- reshape(wide, dir = "long")

names(long) <- names(DF)

4) tapply/as.data.frame.table (base)

as.data.frame.table(tapply(DF[[3]], DF[-3], c), responseName = names(DF)[3])

5) expand.grid/merge (base)

with(DF, merge(expand.grid(week = unique(week), id = unique(id)), DF, all = TRUE))

Fill time series data frame with dummy zero

1 Answers1