CODEWITHSUNDEEP
r
Anshul S
Anshul S

Reputation: 281

Filter dates by particular column value

I am trying to filter dates with a particular offsetdate value from calendar days dataframe.

Dataframe calendardays:

calendardays <- as.data.frame(as.Date(seq(as.Date("2019-01-03"), as.Date("2022-12-31"), by="days")))
colnames(calendardays)<- c("Date")

DF2:

     LC    Custcode       Date      offsetdate
    RM11    IN007543    2019-10-03  2
    WK15    IN007543    2019-10-03  3

In DF2, for each row there is a offsetdate which means that I want to find x no. of dates from the Date which is mentioned in DF2 corresponding to that LC CustCode from Dataframe calendardays.

Output:

     LC    Custcode       Date      
    RM11    IN007543    2019-10-03
    RM11    IN007543    2019-10-04
    WK15    IN007543    2019-10-03
    WK15    IN007543    2019-10-04
    WK15    IN007543    2019-10-05

Upvotes: 1

Views: 54

Answers (2)

G. Grothendieck
G. Grothendieck

Reputation: 269441

Expand DF2 using ix and then increment the Date field. No packages are used.

ix <- rep(1:nrow(DF2), DF2$offsetdate) # 1,1,2,2,2
DF2rep <- transform(DF2[ix, ], Date = Date + sequence(DF2$offsetdate) - 1)[-4]

This already gives the output shown in the question but if you need to check whether the dates also appear in calendardays this will only keep dates in DF2rep that are also in calendardays.

DF2rep[DF2rep$Date %in% calendardays$Date, ]

Note

DF2 in reproducible form is assumed to be:

Lines <- "     LC    Custcode       Date      offsetdate
    RM11    IN007543    2019-10-03  2
    WK15    IN007543    2019-10-03  3"
DF2 <- read.table(text = Lines, header = TRUE)
DF2$Date <- as.Date(DF2$Date)

Upvotes: 3

Ronak Shah
Ronak Shah

Reputation: 388817

Why do we need calendardays ? We can directly expand DF2 using tidyverse

library(tidyverse)

DF2 %>%
  mutate(Date = as.Date(Date), 
         seq_day = map2(Date, Date + offsetdate, seq, by = "1 day")) %>%
  unnest(seq_day) %>%
  select(-Date, -offsetdate)

#  LC    Custcode seq_day   
#  <fct> <fct>    <date>    
#1 RM11  IN007543 2019-10-03
#2 RM11  IN007543 2019-10-04
#3 RM11  IN007543 2019-10-05
#4 WK15  IN007543 2019-10-03
#5 WK15  IN007543 2019-10-04
#6 WK15  IN007543 2019-10-05
#7 WK15  IN007543 2019-10-06

Upvotes: 1

Related Questions