Suppose you have

aa <- tibble(date = ymd(c('20240101','20240102',NA, NA, NA)))

How do you fill the NA values using the dplry mutate statement?

This does not work. It only fill the first NA value.

library(lubridate)
aa %>% 
  mutate(date = if_else(!is.na(date), date, dplyr::lag(date) + lubridate::days(1)))

Suppose you have

aa <- tibble(date = ymd(c('20240101','20240102',NA, NA, NA)))

How do you fill the NA values using the dplry mutate statement?

This does not work. It only fill the first NA value.

library(lubridate)
aa %>% 
  mutate(date = if_else(!is.na(date), date, dplyr::lag(date) + lubridate::days(1)))

• r
• dplyr

Share Improve this question Follow asked Nov 19, 2024 at 1:22 JFDJFD 34922 silver badges1313 bronze badges 2

• 1 Are the set dates guaranteed to be sequential and without gaps? Or is there a chance to encounter something like tibble(date = ymd(c('20240101','20240103','20240104', NA, NA, '20240110'))) ? – margusl Commented Nov 19, 2024 at 8:14
• mutate(aa, date = seq.Date(first(date), first(date) + n() - 1, 1)) might be the easiest. – Friede Commented Nov 19, 2024 at 9:56

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 2

Here's one way:

aa |>
  group_by(grp = cumsum(!is.na(date))) |>
  mutate(
    date = first(date) + row_number() - 1
  ) |>
  ungroup() |>
  select(-grp)
# # A tibble: 5 × 1
#   date      
#   <date>    
# 1 2024-01-01
# 2 2024-01-02
# 3 2024-01-03
# 4 2024-01-04
# 5 2024-01-05

We can use accumulate (or Reduce from base R) to iterate over date with the anonymous function shown. Here y is the current date and x is the previous value of the function. coalesce returns its first non-NA argument.

library(purrr)
aa %>%
  mutate(date = accumulate(date, \(x, y) coalesce(y, x + 1)))

Other approaches are possible if we can make additional assumptions. If we knew that the dates always increase by 1, i.e. no gaps, then we can find the index, i, of the first non-NA date and use that:

aa %>% {
  i <- which.min(is.na(.$date))
  mutate(., date = date[i] + row_number() - i)
}

If, in addition, we knew that the first date is not NA then i must be 1 so it simplifies to

aa %>%
  mutate(date = first(date) + row_number() - 1)

Note

The input used:

library(dplyr)
library(lubridate)

aa <- tibble(date = ymd(c('20240101','20240102',NA, NA, NA)))