CODEWITHSUNDEEP
r
Nathalie
Nathalie

Reputation: 1238

Keep rows before a date using ids

Having two dataframes with dates like this:

df1 <- data.frame(id = c(1,1,1,1,2,2), date=c("2019/12/11 20:30:12", "2019/12/12 09:20:12", "2019/12/12 11:30:40", "2019/12/13 20:12:34", "2019/12/11 12:20:12", "2019/12/11 19:20:12"), values = c(23,4,1,3,4,2))
df2 <- data.frame(id = c(1,2), date = c("2019/12/12 09:20:12", "2019/12/11 19:20:12"))

How is it possible to use the values of dates of the second dataframe to keep rows before this date into the first dataframe? Example of expected output:

data.frame(id = c(1,1,2,2), date=c("2019/12/11 20:30:12", "2019/12/12 09:20:12", "2019/12/11 12:20:12, "2019/12/11 19:20:12"), values = c(23,4,4,2))

Upvotes: 1

Views: 32

Answers (1)

akrun
akrun

Reputation: 887291

We can do a left_join and then filter

library(dplyr)
library(lubridate)
left_join(df1, df2, by = 'id') %>%
         filter(ymd_hms(date.x) <= ymd_hms(date.y)) %>%
         select(id, date = date.x, values)
#id                date values
#1  1 2019/12/11 20:30:12     23
#2  1 2019/12/12 09:20:12      4
#3  2 2019/12/11 12:20:12      4
#4  2 2019/12/11 19:20:12      2

Upvotes: 1

Related Questions