CODEWITHSUNDEEP
rdataframe
Gaurav Bansal
Gaurav Bansal

Reputation: 5660

Fill in missing dates an forward fill in R

I have some data in R that looks like the following:

        date periodNumber   value
1 2020-02-02            0    50
2 2020-02-09            0    35
3 2020-02-09            1    42
4 2020-02-16            0    61
5 2020-02-16            1    58
6 2020-02-16            2    48

I want to extend this data to fill in the missing dates, as well as extend the final date (2020-02-16) out by a week. I further want to forward fill in value appropriately. How can I do this? The following is what I want the final data frame to look like:

        date  periodNumber  value
1  2020-02-02            0   50
2  2020-02-03            0   50
3  2020-02-04            0   50
4  2020-02-05            0   50
5  2020-02-06            0   50
6  2020-02-07            0   50
7  2020-02-08            0   50
8  2020-02-09            0   35
9  2020-02-10            0   35
10 2020-02-11            0   35
11 2020-02-12            0   35
12 2020-02-13            0   35
13 2020-02-14            0   35
14 2020-02-15            0   35
15 2020-02-09            1   42
16 2020-02-10            1   42
17 2020-02-11            1   42
18 2020-02-12            1   42
19 2020-02-13            1   42
20 2020-02-14            1   42
21 2020-02-15            1   42
22 2020-02-16            0   61
23 2020-02-17            0   61
24 2020-02-18            0   61
25 2020-02-19            0   61
26 2020-02-20            0   61
27 2020-02-21            0   61
28 2020-02-22            0   61
29 2020-02-16            1   58
30 2020-02-17            1   58
31 2020-02-18            1   58
32 2020-02-19            1   58
33 2020-02-20            1   58
34 2020-02-21            1   58
35 2020-02-22            1   58
36 2020-02-16            2   48
37 2020-02-17            2   48
38 2020-02-18            2   48
39 2020-02-19            2   48
40 2020-02-20            2   48
41 2020-02-21            2   48
42 2020-02-22            2   48

Upvotes: 0

Views: 276

Answers (1)

Duck
Duck

Reputation: 39595

Try with complete() from tidyr:

library(dplyr)
library(tidyr)
#Code
new <- df1 %>%
  mutate(date=as.Date(date)) %>%
  complete(periodNumber,date=seq(min(date),max(date),by='1 day')) %>%
  fill(value) %>% select(2,1,3)

Output:

# A tibble: 45 x 3
   date       periodNumber value
   <date>            <int> <int>
 1 2020-02-02            0    50
 2 2020-02-03            0    50
 3 2020-02-04            0    50
 4 2020-02-05            0    50
 5 2020-02-06            0    50
 6 2020-02-07            0    50
 7 2020-02-08            0    50
 8 2020-02-09            0    35
 9 2020-02-10            0    35
10 2020-02-11            0    35
# ... with 35 more rows

Upvotes: 1

Related Questions