CODEWITHSUNDEEP
rdataframematching
user10301352
user10301352

Reputation: 81

How to compare dates and change values in another column by groups in R

I have two data frames, 

ID           DATE         EVENT
300-1-003    2019-07-14   4
300-1-004    2019-10-27   4
300-1-004    2019-10-29   4
300-1-008    2019-10-11   4


ID           DATE         EVENT
300-1-001    2019-10-22   0
300-1-002    2019-10-02   0
300-1-004    2019-10-27   0
300-1-004    2019-10-30   0
300-1-008    2019-10-11   0

I want to change the value in EVENT column in the first data frame to 1 if the DATE are the same for the same ID in both data frames.

I tried to do it with

df1$EVENT= ifelse(df1$DATE=df2$DATE & df1$ID=df2$ID, 1, 4)

Also have tried using filter to fit out the same date first.. 

df2_2= filter(df1$DATE=df2$DATE)

But I got error as below for the second method and the first method just doesn't work...

Error: unexpected '=' in "df2_2= filter(df1$DATE="

Both class(df1$DATE) and class(df2$DATE) are "Date". My desired result would be like 

ID           DATE         EVENT
300-1-003    2019-07-14   4
300-1-004    2019-10-27   1
300-1-004    2019-10-29   4
300-1-008    2019-10-11   4

I have searched how to compare dates.. I assume I could compare it this way? I have stuck for hours, I guess I just wouldn't figure out how to do it.. I would appreciate any help....

Upvotes: 2

Views: 539

Answers (1)

akrun
akrun

Reputation: 887981

We can do a join on the 'DATE' and 'ID' columns and assign (:=) the 'EVENT' in 'df1' to 1

library(data.table)
setDT(df1)[df2,  EVENT := 1, on = .(DATE, ID)]

Also, the ifelse can be changed to

with(df1, ifelse(DATE %in% df2$DATE & ID %in% df2$ID, 1, EVENT))

data

df1 <- structure(list(ID = c("300-1-003", "300-1-004", "300-1-004", 
"300-1-008"), DATE = c("2019-07-14", "2019-10-27", "2019-10-29", 
"2019-10-11"), EVENT = c(4L, 4L, 4L, 4L)), class = "data.frame", 
row.names = c(NA, -4L))

df2 <- structure(list(ID = c("300-1-001", "300-1-002", "300-1-004", 
"300-1-004", "300-1-008"), DATE = c("2019-10-22", "2019-10-02", 
"2019-10-27", "2019-10-30", "2019-10-11"), EVENT = c(0L, 0L, 
0L, 0L, 0L)), class = "data.frame", row.names = c(NA, -5L))

Upvotes: 1

Related Questions