R - dataframe with sequence from another dataframe

Admin 1
Viewed 127

I have a dataframe like this:

source_data <-
  data.frame(
    id = c(seq(1,3)),
    start = c(as.Date("2020-04-04"), as.Date("2020-04-02"), as.Date("2020-04-03")),
    end = c(as.Date("2020-04-08"), as.Date("2020-04-05"), as.Date("2020-04-05"))
  )

I want to create a date sequence for each id = crate each day between start and end dates and put it to another dataframe. So the result should look like this:

result <-
  data.frame(
    id = c(1, 1, 1, 1, 1, 2, 2, 2, 2, 3, 3, 3),

    date = c(
      as.Date("2020-04-04"),
      as.Date("2020-04-05"),
      as.Date("2020-04-06"),
      as.Date("2020-04-07"),
      as.Date("2020-04-08"),

      as.Date("2020-04-02"),
      as.Date("2020-04-03"),
      as.Date("2020-04-04"),
      as.Date("2020-04-05"),

      as.Date("2020-04-03"),
      as.Date("2020-04-04"),
      as.Date("2020-04-05")
    )
  )

I started with this date sequence, but how to join my source_data dataframe there?

solution <-
  data.frame(
    date = seq(min(source_data$start), max(source_data$end), by = 1)
  )
seq dataframe r date
3 Answers

We can use map2 to create the sequence between each corresponding 'start', 'end' dates and then unnest the list column

library(dplyr)
library(purrr)
library(tidyr)
source_data %>%
    transmute(id, date = map2(start, end, seq, by = '1 day')) %>%
    unnest(c(date))
# A tibble: 12 x 2
#      id date      
#   <int> <date>    
# 1     1 2020-04-04
# 2     1 2020-04-05
# 3     1 2020-04-06
# 4     1 2020-04-07
# 5     1 2020-04-08
# 6     2 2020-04-02
# 7     2 2020-04-03
# 8     2 2020-04-04
# 9     2 2020-04-05
#10     3 2020-04-03
#11     3 2020-04-04
#12     3 2020-04-05

Or using data.table

library(data.table)
setDT(source_data)[, .(date = seq(start, end, by = '1 day')), by = id]

Additional option with base R

lst1 <- Map(seq, source_data$start, source_data$end, MoreArgs = list(by = '1 day'))
data.frame(id = rep(source_data$id, lengths(lst1)), date = do.call(c, lst1))
user_37322710

Another base R solution

result <- do.call(rbind,
                  c(make.row.names = FALSE,
                    lapply(split(source_data,source_data$id), 
                           function(v) with(v,data.frame(id = id, date = seq(start,end,by = 1))))))

which yields

> result
   id       date
1   1 2020-04-04
2   1 2020-04-05
3   1 2020-04-06
4   1 2020-04-07
5   1 2020-04-08
6   2 2020-04-02
7   2 2020-04-03
8   2 2020-04-04
9   2 2020-04-05
10  3 2020-04-03
11  3 2020-04-04
12  3 2020-04-05
user_121587570

additional option

library(dplyr)
source_data %>% 
  rowwise() %>% 
  mutate(out = list(seq.Date(start, end, "day"))) %>% 
  unnest(out) %>% 
  select(-c(start, end))
user_120254830
Related