Filtering data in two data frame based on date-CodePudding

I have two data frames A and B

I want a subset of frame B where the start data is greater than End date of Dataframe A.

Ideally result should be

No Idea on how to get it.

CodePudding user response：

Here is a solution using dplyr. Note that I am using the date format MM/DD/YYYY, and the dates in your tables are DD/MM/YYYY.

library(dplyr)
library(lubridate)

df1 <- data.frame(
  Name = c('A', 'B', 'C'),
  Limit = c(100, 200, 300),
  EndDate = lubridate::mdy(c('12-13-2022', '01-30-2023', '02-28-2023'))
)



df2 <- data.frame(
  Name = c('A', 'B', 'B', 'C', 'C'),
  Limit = c(50, 150, 10, 250, 50),
  StartDate = lubridate::mdy(c('01-01-2022', '02-01-2022', '03-01-2023', '03-01-2023', '04-01-2023'))
)

df2 %>% 
  left_join(df1, by = "Name") %>% 
  filter(StartDate > EndDate) %>% 
  select(Name, Limit = Limit.x, StartDate)

Which gives:

  Name Limit  StartDate
1    B    10 2023-03-01
2    C   250 2023-03-01
3    C    50 2023-04-01