Use dplyr in R to identify any instance one column matches any row in another column, grouped by id

user_12487630 0
Viewed 70

I have a dataset with two dates (date A and date B). I want to find rows where date A matches any date B within the same id variable. 

id    date A        date B        
1     01/20/2018    02/25/2019
1     01/20/2018    01/20/2018
2     03/18/2017    03/20/2019
2     03/18/2017    04/19/2018  
2     03/20/2019    03/20/2019
2     03/20/2019    04/19/2018 
3     08/01/2019    09/06/2019

I want the output to produce a boolean variable 'date_match': 

id    date A        date B       date_match       
1     01/20/2018    02/25/2019   TRUE
1     01/20/2018    01/20/2018   TRUE
2     03/18/2017    03/20/2019   FALSE
2     03/18/2017    04/19/2018   FALSE
2     03/20/2019    03/20/2019   TRUE
2     03/20/2019    04/19/2018   TRUE 
3     08/01/2019    09/06/2019   FALSE

Thanks!

dplyr r
2 Answers

Using dplyr, you can try:

df %>%
 group_by(id) %>%
 mutate(date_match = date_A %in% date_B)

     id date_A     date_B     date_match
  <int> <chr>      <chr>      <lgl>     
1     1 01/20/2018 02/25/2019 TRUE      
2     1 01/20/2018 01/20/2018 TRUE      
3     2 03/18/2017 03/20/2019 FALSE     
4     2 03/18/2017 04/19/2018 FALSE     
5     2 03/20/2019 03/20/2019 TRUE      
6     2 03/20/2019 04/19/2018 TRUE      
7     3 08/01/2019 09/06/2019 FALSE
user_59645570

With data.table

library(data.table)
setDT(df)[, date_match := date_A %chin% date_B, id]
user_37322710
Related