CODEWITHSUNDEEP
rdatetimedataframecomparetime-series
Rufyyyyy
Rufyyyyy

Reputation: 41

Compare dates from different data.frame

I have to compare some dates from two different df that have ID in common. Let me provide a valid example here:

df1
ID MONTH YEAR
1  1     1997
2  7     2004
3  6     2017
4  8     2003
5  11    1990
6  3     1999

df2
ID MONTH YEAR
1  4     2003
1  8     2006
3  12    2000
4  4     2015
5  1     1993
6  1     1991

As we can see we got two dataframes where there are ID column in common while columns MONTH and YEAR identify a precise date. Now i have to compare each row of df2, with df1, and as output let me know if the date is before or after the same ID in df1 obviously. This should be the example OUTPUT:

df3
ID STATUS
1  After
1  After
2  None
3  Before
4  After
5  After
6  Before

I hope that i explained it well,

Thanks a lot, Andrea.

Upvotes: 0

Views: 412

Answers (1)

Jilber Urbina
Jilber Urbina

Reputation: 61154

You can do it using a combination of as.yearmon from zoo package, merge and ifelse

library(zoo)
df1$date1  <- as.yearmon(with(df1, paste0(YEAR, "-",MONTH))) #setting date in df1
df2$date2  <- as.yearmon(with(df2, paste0(YEAR, "-",MONTH)))#setting date in df2
tmp <- merge(df1, df2, by="ID", all = TRUE) # combining both data.frames
tmp$STATUS <- ifelse(is.na(tmp$date1)|is.na(tmp$date2), "None",
                     ifelse(tmp$date1 > tmp$date2, "Before", "After")) # doing logical comparison 
df3 <- tmp[, c("ID", "STATUS")] # building desired data.frame
df3
  ID STATUS
1  1  After
2  1  After
3  2   None
4  3 Before
5  4  After
6  5  After
7  6 Before

Upvotes: 2

Related Questions