R: How to expand date intervals to single dates

Question

I have a dataset about start and end dates, and I want to expand them to consecutive dates in rows. The dataset looks like this (df1):

id   deg     from             to
1     1      2010-03-01       2010-03-05
1     1      2010-03-20       2010-03-25
1     2      2010-06-01       2010-06-05

And this is the result I want (df2):

id   deg     date            
1     1      2010-03-01       
1     1      2010-03-02       
1     1      2010-03-03
1     1      2010-03-04
1     1      2010-03-05
1     1      2010-03-20 
1     1      2010-03-21 
1     1      2010-03-22 
1     1      2010-03-23
1     1      2010-03-24
1     1      2010-03-25       
1     2      2010-06-01  
1     2      2010-06-02 
1     2      2010-06-03 
1     2      2010-06-04 
1     2      2010-06-05

Here's the different codes I've tried:

df2 = df1 %>%
  mutate(id= 1:nrow(.)) %>%
  rowwise() %>%
  do(data.frame(id=.$id, date=seq.Date(.$from, .$to, by="days")))

But it keeps showing the error: wrong sign in 'by' argument

Thank you all in advance!

Your code seems to work. Did you convert the `from` and `to` columns to a date? — cazman, Sep 23 '21 at 20:51
Then, it is working fine for me. You need to change the `mutate(id= row_number())` — akrun, Sep 23 '21 at 20:53
Hi all, thank you very much for the solutions. I just found out the reason. In the original dataset, there are some rows that have their from to to dates switched, so the end date is earlier than the start date, and that gives the error. — rachel3765, Sep 24 '21 at 00:16

score 0 · Accepted Answer · answered Sep 23 '21 at 20:51

Here is a tidyverse solution:

library(tidyverse)

df %>% 
  rowwise() %>% 
  mutate(new = list(seq(as.Date(from), as.Date(to), by = 1))) %>% 
  select(id, deg, new) %>% 
  unnest()

This gives us:

# A tibble: 16 x 3
      id   deg new       
   <int> <int> <date>    
 1     1     1 2010-03-01
 2     1     1 2010-03-02
 3     1     1 2010-03-03
 4     1     1 2010-03-04
 5     1     1 2010-03-05
 6     1     1 2010-03-20
 7     1     1 2010-03-21
 8     1     1 2010-03-22
 9     1     1 2010-03-23
10     1     1 2010-03-24
11     1     1 2010-03-25
12     1     2 2010-06-01
13     1     2 2010-06-02
14     1     2 2010-06-03
15     1     2 2010-06-04
16     1     2 2010-06-05

Data:

structure(list(id = c(1L, 1L, 1L), deg = c(1L, 1L, 2L), from = structure(c(14669L, 
14688L, 14761L), class = c("IDate", "Date")), to = structure(c(14673L, 
14693L, 14765L), class = c("IDate", "Date"))), row.names = c(NA, 
-3L), .internal.selfref = <pointer: 0x000001e0b8dc1ef0>, class = c("tbl_df", 
"tbl", "data.frame"))

score 0 · Answer 2 · answered Sep 23 '21 at 20:55

We may do this in summarise without the need to use do

library(dplyr)
df1 %>%
    rowwise %>%
    summarise(id, deg = deg, date = seq(from, to, by = 1), .groups = "drop")

-output

# A tibble: 16 x 3
      id   deg date      
   <int> <int> <date>    
 1     1     1 2010-03-01
 2     1     1 2010-03-02
 3     1     1 2010-03-03
 4     1     1 2010-03-04
 5     1     1 2010-03-05
 6     1     1 2010-03-20
 7     1     1 2010-03-21
 8     1     1 2010-03-22
 9     1     1 2010-03-23
10     1     1 2010-03-24
11     1     1 2010-03-25
12     1     2 2010-06-01
13     1     2 2010-06-02
14     1     2 2010-06-03
15     1     2 2010-06-04
16     1     2 2010-06-05

data

df1 <- structure(list(id = c(1L, 1L, 1L), deg = c(1L, 1L, 2L), from = structure(c(14669, 
14688, 14761), class = "Date"), to = structure(c(14673, 14693, 
14765), class = "Date")), row.names = c(NA, -3L), class = "data.frame")

R: How to expand date intervals to single dates

2 Answers2

data