CODEWITHSUNDEEP
rdatedplyrseq
Nevedha Ayyanar
Nevedha Ayyanar

Reputation: 865

Creating sequence of dates in a column in a dataframe in R

I am having a data frame in R as follows:

df <- data.frame("Type" = c("Item A","Item B"), "Frequency" = c("Quarterly","Other"), "Date" = as.Date(c("2021-02-05","2021-05-05")),"endDate" = as.Date("2021-12-12"), stringsAsFactors = F)

I am trying to generate the sequence of dates between Date and endDate as each row. I am using the code below to generate the sequence

df <- df %>%
  dplyr::mutate(id = 1:nrow(df),deliveryDate = ifelse(
    df$Frequency == "Quarterly", list(seq(as.Date(df$Date), as.Date(df$endDate), by = "3 month")),
    ifelse(df$Frequency == "Monthly", list(seq(as.Date(df$Date), as.Date(df$endDate), by = "month")),
           ifelse(df$Frequency %in% c("Other"),list(seq(as.Date(df$Date), as.Date(df$Date), by = "month")),df$Date)))) %>%
  tidyr::unnest(deliveryDate) %>%
  dplyr::group_by(Type) %>%
  dplyr::mutate(deliveryNumber = row_number()) %>%
  dplyr::select(deliveryNumber,Type, Frequency, deliveryDate) %>%

TO be more descriptive, the sequence of date will be generated based on the frequency of the type. So to handle that case, I used dplyr::mutate().

But I am getting an error as follows:

Error: Problem with `mutate()` input `deliveryDate`.
x 'from' must be of length 1
ℹ Input `deliveryDate` is `ifelse(...)`.

Can anyone help me solve this issue in R? Thanks in advance!!!

Upvotes: 1

Views: 1880

Answers (3)

AnilGoyal
AnilGoyal

Reputation: 26218

Use complete

df %>% group_by(Type) %>% mutate(DeliveryDate = Date,
                                 Frequency = case_when(Frequency %in% "Quarterly"~ "quarter",
                                                       Frequency %in% "Monthly" ~ "month",
                                                       Frequency %in% "Weekly" ~ "week",
                                                       TRUE ~ "month")) %>%
  complete(DeliveryDate = seq.Date(Date, endDate, by = Frequency)) %>%
  fill(Frequency, Date, endDate)

# A tibble: 12 x 5
# Groups:   Type [2]
   Type   DeliveryDate Frequency Date       endDate   
   <chr>  <date>       <chr>     <date>     <date>    
 1 Item A 2021-02-05   quarter   2021-02-05 2021-12-12
 2 Item A 2021-05-05   quarter   2021-02-05 2021-12-12
 3 Item A 2021-08-05   quarter   2021-02-05 2021-12-12
 4 Item A 2021-11-05   quarter   2021-02-05 2021-12-12
 5 Item B 2021-05-05   month     2021-05-05 2021-12-12
 6 Item B 2021-06-05   month     2021-05-05 2021-12-12
 7 Item B 2021-07-05   month     2021-05-05 2021-12-12
 8 Item B 2021-08-05   month     2021-05-05 2021-12-12
 9 Item B 2021-09-05   month     2021-05-05 2021-12-12
10 Item B 2021-10-05   month     2021-05-05 2021-12-12
11 Item B 2021-11-05   month     2021-05-05 2021-12-12
12 Item B 2021-12-05   month     2021-05-05 2021-12-12

Upvotes: 1

Onyambu
Onyambu

Reputation: 79198

You should consider a named vector:

library(tidyverse)

vec<-c(Quarterly = "3 months", Other = "month")

df %>% 
   rowwise() %>%
   mutate(deliveryDate = list(seq(Date,endDate, by = vec[Frequency]))) %>%
   unnest(deliveryDate)

# A tibble: 12 x 5
   Type   Frequency Date       endDate    deliveryDate
   <chr>  <chr>     <date>     <date>     <date>      
 1 Item A Quarterly 2021-02-05 2021-12-12 2021-02-05  
 2 Item A Quarterly 2021-02-05 2021-12-12 2021-05-05  
 3 Item A Quarterly 2021-02-05 2021-12-12 2021-08-05  
 4 Item A Quarterly 2021-02-05 2021-12-12 2021-11-05  
 5 Item B Other     2021-05-05 2021-12-12 2021-05-05  
 6 Item B Other     2021-05-05 2021-12-12 2021-06-05  
 7 Item B Other     2021-05-05 2021-12-12 2021-07-05  
 8 Item B Other     2021-05-05 2021-12-12 2021-08-05  
 9 Item B Other     2021-05-05 2021-12-12 2021-09-05  
10 Item B Other     2021-05-05 2021-12-12 2021-10-05  
11 Item B Other     2021-05-05 2021-12-12 2021-11-05  
12 Item B Other     2021-05-05 2021-12-12 2021-12-05

Upvotes: 2

Phil
Phil

Reputation: 8107

Here's one way. It's not clear what you want for "Other" as opposed to "month", so I set it here to "week".

Note that you don't need to reference the data frame when using mutate() since everything called in the function is set to the environment of the data frame. Also, look into using case_when() instead of using nested ifelse() calls.

library(tidyverse)

df %>% 
  mutate(Frequency2 = case_when(Frequency == "Quarterly" ~ "3 month",
                                Frequency == "Month" ~ "month",
                                TRUE ~ "week")) %>% 
  group_by(Type, Frequency2) %>% 
  nest() %>% 
  mutate(middates = map2(data, Frequency2, ~ seq.Date(min(.x$Date), max(.x$endDate), by = .y))) %>% 
  unnest(c(data, middates)) %>% 
  ungroup()

# A tibble: 36 x 6
   Type   Frequency Frequency2 Date       endDate    middates  
   <chr>  <chr>     <chr>      <date>     <date>     <date>    
 1 Item A Quarterly 3 month    2021-02-05 2021-12-12 2021-02-05
 2 Item A Quarterly 3 month    2021-02-05 2021-12-12 2021-05-05
 3 Item A Quarterly 3 month    2021-02-05 2021-12-12 2021-08-05
 4 Item A Quarterly 3 month    2021-02-05 2021-12-12 2021-11-05
 5 Item B Other     week       2021-05-05 2021-12-12 2021-05-05
 6 Item B Other     week       2021-05-05 2021-12-12 2021-05-12
 7 Item B Other     week       2021-05-05 2021-12-12 2021-05-19
 8 Item B Other     week       2021-05-05 2021-12-12 2021-05-26
 9 Item B Other     week       2021-05-05 2021-12-12 2021-06-02
10 Item B Other     week       2021-05-05 2021-12-12 2021-06-09
# ... with 26 more rows

Upvotes: 0

Related Questions