How to add missing dates to a column with NA's to the other columns?

Question

         Date NewArea.km2 IDday
1  2018-03-01    152.3972     0
2  2018-03-15    152.3972     0
3  2018-04-01    152.3972     0
4  2018-04-15    152.3972     0
5  2018-05-01    152.3972     0
6  2018-05-15    152.3972     0
7  2018-06-01    152.3972     0
8  2018-06-10    152.3972     0
9  2018-06-15    152.3972     0
10 2018-06-20    152.3972     0
11 2018-06-21    152.3972     1
12 2018-06-22    152.3972     3
13 2018-06-23    152.3972     4
14 2018-06-24    152.3972     6
15 2018-06-25    152.3972     6
16 2018-06-26    152.3972    16
17 2018-06-27    152.3972    22
18 2018-06-28    152.3972    22
19 2018-06-29    152.3972    24
20 2018-06-30    152.3972    24
21 2018-07-01    152.3972    27

Here's a subset of the data I have. As you may have eluded to from the title, I would like to add rows to my data frame such that missing dates are also included (i.e. 2018-03-02, 2018-03-03, etc.) The other two columns ideally would fill up with NA's such that the new row 2 would read

2  2018-03-02  NA  NA

A few more related posts [here](https://stackoverflow.com/q/48633460/5325862), [here](https://stackoverflow.com/q/50192024/5325862), [here](https://stackoverflow.com/q/29152654/5325862) — camille, Dec 18 '19 at 15:39

score 2 · Accepted Answer · answered Dec 18 '19 at 15:14

2

We can use complete

library(tidyr)
df1 %>%
   complete(Date = seq(min(Date), max(date), by = '1 day'))

answered Dec 18 '19 at 15:14

akrun

874,273
37
540
662

score 1 · Answer 2 · answered Dec 18 '19 at 15:41

Here is a solution with base R

dfout <- `names<-`(data.frame(seq(range(df$Date)[1],range(df$Date)[2],by = "1 day"),NA,NA),names(df))
dfout[match(df$Date,dfout$Date),] <- df

such that

> head(dfout,20)
         Date NewArea.km2 IDday
1  2018-03-01    152.3972     0
2  2018-03-02          NA    NA
3  2018-03-03          NA    NA
4  2018-03-04          NA    NA
5  2018-03-05          NA    NA
6  2018-03-06          NA    NA
7  2018-03-07          NA    NA
8  2018-03-08          NA    NA
9  2018-03-09          NA    NA
10 2018-03-10          NA    NA
11 2018-03-11          NA    NA
12 2018-03-12          NA    NA
13 2018-03-13          NA    NA
14 2018-03-14          NA    NA
15 2018-03-15    152.3972     0
16 2018-03-16          NA    NA
17 2018-03-17          NA    NA
18 2018-03-18          NA    NA
19 2018-03-19          NA    NA
20 2018-03-20          NA    NA

DATA

df <- structure(list(Date = structure(c(17591, 17605, 17622, 17636, 
17652, 17666, 17683, 17692, 17697, 17702, 17703, 17704, 17705, 
17706, 17707, 17708, 17709, 17710, 17711, 17712, 17713), class = "Date"), 
    NewArea.km2 = c(152.3972, 152.3972, 152.3972, 152.3972, 152.3972, 
    152.3972, 152.3972, 152.3972, 152.3972, 152.3972, 152.3972, 
    152.3972, 152.3972, 152.3972, 152.3972, 152.3972, 152.3972, 
    152.3972, 152.3972, 152.3972, 152.3972), IDday = c(0L, 0L, 
    0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 1L, 3L, 4L, 6L, 6L, 16L, 
    22L, 22L, 24L, 24L, 27L)), row.names = c(NA, -21L), class = "data.frame")

How to add missing dates to a column with NA's to the other columns?

2 Answers2