Populate rows based on the date sequence in R

Question

I am having a data frame with a specific date range for each row.

  stuID stdID  roleStart    roleEnd
1     1      7 2010-11-18 2020-06-14
2     2      2 2012-08-13 2014-04-01
3     2      4 2014-04-01 2015-10-01
4     2      3 2015-10-01 2018-10-01
5     2      6 2018-10-01 2020-06-14
6     3      4 2014-03-03 2015-10-01

I need to generate the rows based on the weeks of the date. To be precise, I need to populate the rows based on week between two dates in the given data frame.

I tried to achieve this using the following piece of code

extendedData <- reshape2::melt(setNames(lapply(1:nrow(df), function(x) seq.Date(df[x, "roleStart"],
                                                                      df[x, "roleEnd"], by = "1 week")),df$stuID))

But when I execute this, I am getting the error message

Error in seq.int(0, to0 - from, by) : wrong sign in 'by' argument

This is the structure of the dataframe

'data.frame':   350 obs. of  4 variables:
 $ stuID    : int  1 2 2 2 2 3 3 3 4 4 ...
 $ stdID   :  int  7 2 4 3 6 4 3 6 1 2 ...
 $ roleStart: Date, format: "2010-11-18" "2012-08-13" "2014-04-01" "2015-10-01" ...
 $ roleEnd  : Date, format: "2020-06-14" "2014-04-01" "2015-10-01" "2018-10-01" ...

Can anyone say what's wrong with the code?

Thanks in advance!!

The error may suggest that you have an end date which occurs _after_ the start date, perhaps due to a typo. Try `seq(as.Date("2020-06-14"), as.Date("2020-06-01"), by = "1 week")`. Except from that, the code seems to work. Related, possible duplicate [Expand ranges defined by “from” and “to” columns](https://stackoverflow.com/questions/11494511/expand-ranges-defined-by-from-and-to-columns) — Henrik, Jun 14 '20 at 09:05

score 0 · Accepted Answer · answered Jun 14 '20 at 08:49

Here's a way to do this using tidyverse functions :

library(dplyr)

df %>%
  mutate(date = purrr::map2(roleStart, roleEnd, seq, by = 'week')) %>%
  tidyr::unnest(date)

As far as your code is concerned it works fine till this step i.e generating weekly dates

lapply(1:nrow(df), function(x) 
      seq.Date(df[x, "roleStart"], df[x, "roleEnd"], by = "1 week"))

I am not sure what you are trying to do with setNames and melt functions there.

Populate rows based on the date sequence in R

1 Answers1