[datatable-help] To apply different instructions in data table

[Frank S.]

Hello
everyone. I’ve a rather difficult
question the following data table with
grouped data (I really don’t know how to do it)a very long data. As an
example, the first 10 rows:

 

DT <-
data.table(ID=c(1,1,2,2,2,3,3,4,5,5),

       
start=as.Date(c("1985-01-01","1993-07-15","1993-05-17","1998-02-25","1997-10-28","2000-05-25","1995-09-02","1998-03-01","1992-02-26","1994-07-22")),

        end=as.Date(c("1992-05-01","1997-02-01","1997-10-20","1999-10-15","2003-08-25","2000-01-27","2002-04-15","2003-10-02","1997-03-17","2002-08-19")),

         
reason=(c("Q2","Vacancy","R3","Vacancy","Vacancy","Vacancy","Q3","R2","S2","R1")))


 

    ID     
start        end  reason

 1:  1
1985-01-01 1992-05-01      Q2

 2:  1
1993-07-15 1997-02-01 Vacancy

 3:  2
1993-05-17 1997-10-20      R3

 4:  2
1998-02-25 1999-10-15 Vacancy

 5:  2
1997-10-28 2003-08-25 Vacancy

 6:  3
2000-05-25 2000-01-27 Vacancy

 7:  3
1995-09-02 2002-04-15      Q3

 8:  4
1998-03-01 2003-10-02      R2

 9:  5
1992-02-26 1997-03-17      S2

10:  5 1994-07-22 2002-08-19      R1

 

I would like
to be able to construct an small function that allows to keep ONLY ONE
OBSERVATION PER subject in the following way:

 

1)  To keep his
associated oldest date in “start” column.

2)  With regard
to “end” column, we can have the following situations:

a)  ID does NOT
CONTAIN ”Vacancy” in any of his observations: Then I must keep the date closer
to the present time, and its corresponding reason (ID=4 and ID=5).

b)  ID contains “Vacancy”
in some of his observations: If Vacancy appears only once, I will keep its corresponding
end date, and reason will be Vacancy (ID=1 and ID=3). If vacancy appears two or
more times, then I will keep as “end” the oldest date among the rows which
contain “Vacancy” (ID=2).

 

    ID     
start        end  reason

 1:  1 1985-01-01
1997-02-01 Vacancy

 2:  2
1993-05-17 1999-10-15 Vacancy

 3:  3 1995-09-02
2000-01-27 Vacancy

 4:  4
1998-03-01 2003-10-02      R2

 5:  5
1992-02-26 2002-08-19      R1 Thanks in advance for any help!!    

 		 	   		  
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.r-forge.r-project.org/pipermail/datatable-help/attachments/20140914/4dc2f6e4/attachment-0001.html>