Filling a data frame by mutating sequence of dates

[budugulo]

# Toy Data
df <- structure(
  list(
    eid = 52249:52250, pid = c("2024004", "2024008"), 
    date_from = c("20231231", "20240128"),
    date_to = c("20240127","20240224")
  ), 
  row.names = c(NA, -2L), class = c("tbl_df", "tbl", "data.frame")
)

eid	pid	date_from	date_to
52249	2024004	20231231	20240127
52250	2024008	20240128	20240224

How to mutate a variable date by creating a sequence of dates by taking date_from and date_to into account? Below is an example of what is wanted:

> df_wanted |> head()
# A tibble: 6 × 5
    eid pid     date_from date_to  date      
  <int> <chr>   <chr>     <chr>    <date>    
1 52249 2024004 20231231  20240127 2023-12-31
2 52249 2024004 20231231  20240127 2024-01-01
3 52249 2024004 20231231  20240127 2024-01-02
4 52249 2024004 20231231  20240127 2024-01-03
5 52249 2024004 20231231  20240127 2024-01-04
6 52249 2024004 20231231  20240127 2024-01-05


> df_wanted |> tail()
# A tibble: 6 × 5
    eid pid     date_from date_to  date      
  <int> <chr>   <chr>     <chr>    <date>    
1 52250 2024008 20240128  20240224 2024-02-19
2 52250 2024008 20240128  20240224 2024-02-20
3 52250 2024008 20240128  20240224 2024-02-21
4 52250 2024008 20240128  20240224 2024-02-22
5 52250 2024008 20240128  20240224 2024-02-23
6 52250 2024008 20240128  20240224 2024-02-24

[vedoa]

Hi @budugulo
how about

library(dplyr)
library(tidyr)
df |> 
  rowwise() |>
  mutate(date = list(seq(date_from, date_to, by="1 day"))) |> 
  unnest_longer(date)

date_from - date_to have to be dates.

df |> 
  rowwise() |>
  mutate(date = list(seq(as.Date(date_from, format = "%Y%m%d"), as.Date(date_to, format = "%Y%m%d"), by="1 day"))) |> 
  unnest_longer(date)

if you need date_from and date_to to be chr.

[budugulo]

@vedoa Many thanks! Both works

Just for future reference, as you commented, the first solution works only when dates are provided in actual date format.

Here is another way of solving the problem:

df |> 
  mutate(
    date = map2(
      ymd(date_from), 
      ymd(date_to), 
      ~seq(.x, .y, by = "day")
    )
  ) |> 
  unnest(date)