r - Recursively fill dates in dplyr mutate - Stack Overflow

admin管理员组

文章数量:1432351

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)))

本文标签： rRecursively fill dates in dplyr mutateStack Overflow