比较来自不同data.frame的日期

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

我必须比较两个具有共同ID的不同df的日期。让我在这里提供一个有效的例子:

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:

我们可以看到,我们有两个数据帧,其*有ID列,而列MONTH和YEAR标识精确的日期。现在我必须将df2的每一行与df1进行比较,并且输出让我知道日期是否在df1中的相同ID之前或之后。这应该是示例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.

非常感谢Andrea。

1 个解决方案

#1

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

你可以使用来自zoo package,merge和ifelse的as.yearmon的组合来完成它

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