mardi 19 janvier 2021

R compare date from one column with dates in many columns

I have a dataset with 8 columns, 7 columns out of 8 are date columns. I need to compare the date in one column say ReferenceDate with dates in other columns say DateCol1, DateCol2, DateCol3, DateCol4, DateCol5, DateCol6, DateCol7

ID   DateCol1   DateCol2   DateCol3   DateCol4   ReferenceDate DateCol5   DateCol6   DateCol7
12  2000-11-03  2007-05-17 2003-07-11 2014-03-19 2000-07-11    1999-10-06 2015-06-29 2014-07-06 
17  2015-12-16  2017-07-26 2015-01-13 2020-01-30 2015-03-08    2007-07-30 2020-05-21 2010-10-09 
19  2003-03-06  2011-02-23 2001-09-18 2001-04-05 2013-05-17    1999-10-02 2004-08-26 2019-04-15 
23  2002-10-06  2019-03-12 1999-04-19 2008-04-03 2006-11-20    2000-11-15 2010-07-22 1999-05-27 
22  2019-05-19  2014-11-17 2001-03-24 2003-07-03 2001-04-02    2017-06-03 2016-09-21 2013-07-13

My goal is to create a column Yes/No value that indicates if the the date in ReferenceDate column was earlier or later than any of the dates in the 7 other date columns.

ID   DateCol1   DateCol2   DateCol3   DateCol4   ReferenceDate DateCol5   DateCol6   DateCol7    Status
12  2000-11-03  2007-05-17 2003-07-11 2014-03-19 2000-07-11    1999-10-06 2015-06-29 2014-07-06  Yes (DateCol5 earlier than Reference Date)
17  2015-12-16  2017-07-26 2015-01-13 2020-01-30 2015-03-08    2007-07-30 2020-05-21 2010-10-09  Yes (DateCol5 earlier than Reference Date)
19  2003-03-06  1981-02-23 2001-09-18 2001-04-05 2013-05-17    1999-10-02 2004-08-26 2019-04-15  Yes (DateCol2 earlier than Reference Date)
23  1992-10-06  2019-03-12 1999-04-19 2008-04-03 2006-11-20    2000-11-15 2010-07-22 1999-05-27  Yes (DateCol1 earlier than Reference Date)
22  2019-05-19  2014-11-17 2001-03-24 2003-07-03 2001-04-02    2017-06-03 2016-09-21 2013-07-13  No

I suppose I could do this using a lot of nested ifelse, but I would just go insane. I need some help how to accomplish this more efficiently. Thanks in advance.

Aucun commentaire:

Enregistrer un commentaire