ID Date
A 01/03/2020
A 30/03/2020
A 01/04/2020
A 15/04/2020
B 30/03/2020
B 01/04/2020
B 15/04/2020
C 15/04/2020
DT2 needs to be filtered so only rows with IDs form DT1 remain and the date needs to be bigger than or equal to the date next to the corresponding ID of DT2
ID Date
A 01/04/2020
A 15/04/2020
B 15/04/2020
Filtering for the IDs goes like this:
DT2 <- filter(DT2, ID %in% DT1$ID)
But how do you add an AND-statement to filter for dates bigger than or equal to the date next to the corresponding ID?
a <- "
ID Date
A 01/04/2020
B 05/04/2020
"
DT1 <- read.table(text=a, header=TRUE, stringsAsFactors=FALSE)
DT1$Date <- as.Date(DT1$Date, format="%d/%m/%Y")
DT1
b <- "
ID Date
A 01/03/2020
A 30/03/2020
A 01/04/2020
A 15/04/2020
B 30/03/2020
B 01/04/2020
B 15/04/2020
C 15/04/2020
"
DT2 <- read.table(text=b, header=TRUE, stringsAsFactors=FALSE)
DT2$Date <- as.Date(DT2$Date, format="%d/%m/%Y")
DT2
library(dplyr)
inner_join(DT2, DT1, by="ID") %>%
filter(Date.x >= Date.y) %>%
select(-Date.y) %>%
rename(Date = Date.x) -> DT3
DT3