0
Error in seq.Date(as.Date(retail$Valid_from), as.Date(retail$Valid_to),  : 
  'from' must be of length 1

I have tried both the methods as mentioned in the question :

I basically want to repeat the quantity for each day in a given date range :

HSD_RSP            Valid_from   Valid_to
70                 1/1/2018     15/1/2018
80                 1/16/2018    1/31/2018
.
.
.

Method 1 :

byDay = ddply(retail, .(HSD_RSP), transform, 
              day=seq(as.Date(retail$Valid_from), as.Date(retail$Valid_to), by="day"))

Method 2 :

dt <- data.table(retail)
dt <- dt[,seq(as.Date(Valid_from),as.Date(Valid_to),by="day"),
         by=list(HSD_RSP)]

HSD_RSP      final_date
70             1/1/2018
70           2/1/2018
70           3/1/2018
70           4/1/2018
.
.
.

output of

dput(head(retail))

structure(list(HSD_RSP = c(61.68, 62.96, 63.14, 60.51, 60.34, 
61.63), Valid_from = structure(c(1483315200, 1484524800, 1487116800, 
1491004800, 1491523200, 1492300800), class = c("POSIXct", "POSIXt"
), tzone = "UTC"), Valid_to = structure(c(1484438400, 1487030400, 
1490918400, 1491436800, 1492214400, 1493510400), class = c("POSIXct", 
"POSIXt"), tzone = "UTC")), row.names = c(NA, -6L), class = c("tbl_df", 
"tbl", "data.frame"))
Dharight
  • 71
  • 1
  • 8
  • In which format are your dates? In your example the valid_to column has 2 different formats. – phiver Sep 25 '19 at 08:21

2 Answers2

2

Convert to date, create a sequence of dates between Valid_from and Valid_to and unnest

library(tidyverse)

df %>%
  mutate_at(vars(starts_with("Valid")), as.Date, "%m/%d/%Y") %>%
  mutate(Date = map2(Valid_from, Valid_to, seq, by = "1 day")) %>%
  unnest(Date) %>%
  select(-Valid_from, -Valid_to)

#  HSD_RSP   Date      
#     <int> <date>    
# 1      70 2018-01-01
# 2      70 2018-01-02
# 3      70 2018-01-03
# 4      70 2018-01-04
# 5      70 2018-01-05
# 6      70 2018-01-06
# 7      70 2018-01-07
# 8      70 2018-01-08
# 9      70 2018-01-09
#10      70 2018-01-10
# … with 21 more rows

data

df <- structure(list(HSD_RSP = c(70L, 80L), Valid_from = structure(1:2, 
.Label = c("1/1/2018", "1/16/2018"), class = "factor"), Valid_to = 
structure(1:2, .Label = c("1/15/2018", "1/31/2018"), class = "factor")),
class = "data.frame", row.names = c(NA, -2L))
Ronak Shah
  • 377,200
  • 20
  • 156
  • 213
  • Hi Ronak, I am getting the same error somehow , I feel the issue is with the data frame : HSD_RSP Valid_from Valid_to 1 61.7 2017-01-02 00:00:00 2017-01-15 00:00:00 – Dharight Sep 25 '19 at 09:41
  • @Dhavaldesai Can you try `df %>% mutate_at(vars(starts_with("Valid")), as.Date) %>% mutate(Date = map2(Valid_from, Valid_to, seq, by = "1 day")) %>% unnest(Date) %>% select(-Valid_from, -Valid_to)` – Ronak Shah Sep 25 '19 at 09:44
  • your code works when the valid_from and valid_to dates have a single value , I require these sequences to be created every 15 days for the entire year . It would be great if you can help me out – Dharight Sep 25 '19 at 09:53
  • @Dhavaldesai It's unclear to me. From your input data it looks like you already have dates for every 15 days. Please check the `df` I have in **data** section of my answer and compare it with yours. If your data is different please update your post with `dput(head(df))`. Also be sure in what format you have your input data, in your post you have shown it in `"%m/%d/%Y"` format whereas in comment you also showed time component in it. – Ronak Shah Sep 25 '19 at 10:05
  • have made the edit to the answer , can you have a look – Dharight Sep 25 '19 at 10:24
  • @Dhavaldesai doesn't `retail %>% mutate_at(vars(starts_with("Valid")), as.Date) %>% mutate(Date = map2(Valid_from, Valid_to, seq, by = "1 day")) %>% unnest(Date) %>% dplyr::select(-Valid_from, -Valid_to)` already give you the answer. It creates a sequence of dates between each `Valid_from` and `Valid_to`. Isn't that what you want? – Ronak Shah Sep 25 '19 at 10:36
  • But I keep getting this Error in mutate_impl(.data, dots) : Evaluation error: wrong sign in 'by' argument. – Dharight Sep 25 '19 at 10:49
  • @Dhavaldesai Not able to reproduce, the code seem to work on your sample data shared. how big is your data ? Can you share complete dataframe? `dput(retail)` ? – Ronak Shah Sep 25 '19 at 10:54
1

Using Ronak Shah's data structure, using data.table:

library(data.table)     
dt <- as.data.table(df1)
dt[, .(final_date = seq(as.Date(Valid_from, "%m/%d/%Y"), as.Date(Valid_to, "%m/%d/%Y"), by = "day")),
   by = HSD_RSP]

    HSD_RSP final_date
 1:      70 2018-01-01
 2:      70 2018-01-02
 3:      70 2018-01-03
 4:      70 2018-01-04
 ....

data:

df <- structure(list(HSD_RSP = c(70L, 80L), Valid_from = structure(1:2, 
.Label = c("1/1/2018", "1/16/2018"), class = "factor"), Valid_to = 
structure(1:2, .Label = c("1/15/2018", "1/31/2018"), class = "factor")),
class = "data.frame", row.names = c(NA, -2L))
phiver
  • 23,048
  • 14
  • 44
  • 56