Insert empty rows where values in a column are non-sequential in a data frame or tibble in R

Suppose I have to following synthetic data:

> set.seed(1)
> weeknumber = c(1:3, 6:8, 12:15)
> value = sample(10:20, length(weeknumber), replace = T)
> df = data.frame(weeknumber, value)
> df
   weeknumber value
1           1    12
2           2    14
3           3    16
4           6    19
5           7    12
6           8    19
7          12    20
8          13    17
9          14    16
10         15    10

How can I efficiently add additional empty rows in between values in the weeknumber column that are non-sequential? The desired output is the following:

   weeknumber value
1           1    12
2           2    14
3           3    16
3           4     0
5           5     0
6           6    19
7           7    12
8           8    19
9           9     0
10         10     0
11         11     0
12         12    20
13         13    17
14         14    16
15         15    10

It’s also alright if the new ‘0”s in the value column are also NA‘s.

>Solution :

You may use complete

library(tidyr)
complete(df, weeknumber = full_seq(weeknumber, 1), fill = list(value = 0))

#   weeknumber value
#        <dbl> <dbl>
# 1          1    18
# 2          2    13
# 3          3    16
# 4          4     0
# 5          5     0
# 6          6    10
# 7          7    11
# 8          8    16
# 9          9     0
#10         10     0
#11         11     0
#12         12    20
#13         13    11
#14         14    20
#15         15    12

If you need NA‘s instead of 0 use complete(df, weeknumber = full_seq(weeknumber, 1)).

Leave a Reply