In R, how can I group by ID to fill in missing values using existing values?

Question

For example, suppose I have the following data frame:

Class<-c("A", "A", "B", "B", "C", "C")
ClassDate<-as.Date(c("2020-01-01", "2020-01-01", "2020-01-02", "2020-01-02", "2020-03-03", "2020-03-03"), "%Y-%m-%d", na.exclude=TRUE)
df<-data.frame(Class=Class, ClassDate=ClassDate)
df[2,2]<-NA
df[3,2]<-NA
df[5,2]<-NA
df

  Class  ClassDate
1     A 2020-01-01
2     A       <NA>
3     B       <NA>
4     B 2020-01-02
5     C       <NA>
6     C 2020-03-03

I'm trying to create the following:

  Class  ClassDate    NewDate
1     A 2020-01-01 2020-01-01
2     A       <NA> 2020-01-01
3     B       <NA> 2020-01-02
4     B 2020-01-02 2020-01-02
5     C       <NA> 2020-03-03
6     C 2020-03-03 2020-03-03

akrun · Answer 1 · 2021-03-02T01:42:56.170

We can do a group by fill after creating an identical column of 'ClassDate' as 'NewDate'

library(dplyr)
library(tidyr)
df %>% 
   mutate(NewDate = ClassDate) %>% 
   group_by(Class) %>% 
   fill(NewDate, .direction = 'downup') %>%
   ungroup

-output

# A tibble: 6 x 3
#  Class ClassDate  NewDate   
#  <chr> <date>     <date>    
#1 A     2020-01-01 2020-01-01
#2 A     NA         2020-01-01
#3 B     NA         2020-01-02
#4 B     2020-01-02 2020-01-02
#5 C     NA         2020-03-03
#6 C     2020-03-03 2020-03-03

In R, how can I group by ID to fill in missing values using existing values?

1 Answers1